Fórmula no excel

Question

Serão ao todo 596 colunas com fórmulas. Fazer as 596 eu sei, mas gostaria de saber se tem alguma forma de fazer sem ser uma a uma.

Answer 1

Você também pode usar o seguinte código M no Power Query para evitar a função INDIRETO encontrada na fórmula, que é uma função volátil. Nomeei a tabela principal como: TableBola

let
    Source = Excel.CurrentWorkbook(){[Name="TableBola"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source, {"Concurso", "Data Sorteio"}),
    #"Changed Type" = Table.TransformColumnTypes(
    #"Removed Columns",
        List.Transform(Table.ColumnNames(#"Removed Columns"), each {_, Int64.Type})),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
        DataCols = List.RemoveItems(Table.ColumnNames(#"Added Index"), {"Index"}),
        RowsPerBlock = 6, ColsPerRow = List.Count(DataCols),
    #"Unpivoted" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Bola", "Value"),
    #"Add Block" = Table.AddColumn(#"Unpivoted", "Block", each Number.IntegerDivide([Index], RowsPerBlock), Int64.Type),
    #"Add RowInBlock" = Table.AddColumn(#"Add Block", "RowInBlock", each Number.Mod([Index], RowsPerBlock), Int64.Type),
    #"Add ColIndex" = Table.AddColumn(#"Add RowInBlock", "ColIndex", each List.PositionOf(DataCols, [Bola]), Int64.Type),
    #"Add Pos" = Table.AddColumn(#"Add ColIndex", "Pos", each [RowInBlock] * ColsPerRow + [ColIndex], Int64.Type),
    #"Add BlockName" = Table.AddColumn(#"Add Pos", "BlockName", each "Col_" & Text.PadStart(Text.From([Block] + 1), 2, "0"), type text),
    #"SelectForPivot" = Table.SelectColumns(#"Add BlockName", {"Pos", "BlockName", "Value"}),
    #"Pivoted" = Table.Pivot(#"SelectForPivot", List.Distinct(#"SelectForPivot"[BlockName]), "BlockName", "Value"),
    #"Sorted" = Table.Sort(#"Pivoted", {{"Pos", Order.Ascending}}),
    #"Final" = Table.RemoveColumns(#"Sorted", {"Pos"}),
    BlockCols = Table.ColumnNames(#"Final"),
    #"UnpivotedCounts" = Table.Unpivot(#"Final", BlockCols, "Col", "Value"),
    #"FilteredTo1_20" = Table.SelectRows(
    #"UnpivotedCounts",
        each Value.Is([Value], type number) and [Value] >= 1 and [Value] <= 20),
    #"Grouped" = Table.Group(
    #"FilteredTo1_20",
        {"Value", "Col"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"PivotCounts" = Table.Pivot(
    #"Grouped", List.Distinct(#"Grouped"[Col]), "Col", "Count", List.Sum),
    AllValues = List.Numbers(1, 20),
    PresentValues = try List.Distinct(#"PivotCounts"[Value]) otherwise {},
    MissingValues = List.Difference(AllValues, PresentValues),
    CountColumnNames = List.RemoveItems(Table.ColumnNames(#"PivotCounts"), {"Value"}),
    ZeroRows =
        if List.Count(MissingValues) = 0 then
            #table({"Value"}, {})
        else
            Table.FromRecords(
                List.Transform(
                    MissingValues,
                    (v) => Record.Combine({
                        [Value = v],
                        Record.FromList(List.Repeat(0, List.Count(CountColumnNames)), CountColumnNames)
                    })
                )
            ),
    #"Combined" = Table.Combine({#"PivotCounts", ZeroRows}),
    #"NullsToZero" = Table.ReplaceValue(#"Combined", null, 0, Replacer.ReplaceValue, CountColumnNames),
    #"CountsOrdered" = Table.RenameColumns(
        Table.Sort(#"NullsToZero", {{"Value", Order.Ascending}}), {{"Value", "Number"}})
in
    #"CountsOrdered"

Answer 2

Olá,

Você pode tentar a seguinte fórmula em uma célula vazia da planilha "Histórico 6 em 6 concursos".

=LET(count; CONT.NÚM(Resultados!A2:A3577) / 6; seq; SEQUÊNCIA(20); HSTACK(VSTACK("Dezenas"; seq); VSTACK("Concs " & SEQUÊNCIA(; count; 1; 6) & " a " & SEQUÊNCIA(; count; 6; 6); CONT.SE(INDIRETO("Resultados!C" & SEQUÊNCIA(; count; 2; 6) & ":Q" & SEQUÊNCIA(; count; 7; 6)); seq))))

Se a fórmula funcionar para você, não se esqueça de marcar minha resposta como "Resposta Válida".

Att