Início / coding / Perguntas / 78479124
Accepted
Mark S.
Asked: 2024-05-14 23:28:09 +0800 CST

Sumifs em toda a gama de derramamentos de múltiplas colunas, Top x & Group permanecendo juntos usando fórmulas de derramamento

  • 772

Tudo, meu objetivo é ter uma saída resumida de fórmulas de derramamento que gere o x superior das linhas, mas a última linha agrupando todas as outras linhas para que os totais sejam vinculados à fonte de dados. (A primeira imagem mostra geralmente como está funcionando, a segunda mostra como eu gostaria que fosse. Tabela Markdown incluída para teste.) Se houver uma maneira melhor de formular o título, me avise e eu ajustarei.

A tabela de origem é gerada por fórmulas auxiliares em uma tabela dinâmica. Eu faço isso para poder obter o total que devo vincular antes de começar a combinar campos, para os quais as tabelas dinâmicas não têm uma solução limpa (para agrupar os x campos principais, você precisa selecioná-los manualmente, mas preciso que isso seja dinâmico).

Na imagem, a primeira tabela (à esquerda) contém todas as fórmulas de derramamento. Os números de departamento na parte superior e os IDs na lateral são gerados, assim como os valores.

A segunda tabela tenta pegar os 5 primeiros e depois somar tudo depois dos 5 primeiros em "Outros"; no entanto, como você pode ver, isso está obtendo dados para o ID 6, em vez de tudo maior que 6. Tentei a fórmula de algumas maneiras, mas não consegui somar todas as colunas de uma vez enquanto fazia isso. Uma solução que exige que eu copie a fórmula para colunas adicionais ou remova-as quando houver menos, pois não será estático.

Restrições: A primeira tabela é gerada a partir de fórmulas de derramamento, o número de linhas e colunas não é estático, portanto a segunda tabela precisa levar isso em consideração. Por exemplo, usei Sequence()para os cabeçalhos e linhas, e uma fórmula aleatória para os valores conterem alguns dados.

A fórmula funciona para tudo, menos para a linha "Outros":

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--(A2#=INDEX(I2#,r))*--(C1#=INDEX(K1#,1,c)) )))

Tentei modificar a fórmula em K2 abaixo, mas resultou em #N/A:

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--IF(I2#>5,(A2#>=INDEX(I2#,r)),(A2#=INDEX(I2#,r)))*--(C1#=INDEX(K1#,1,c)) )))

insira a descrição da imagem aqui

Esta é a saída desejada:

insira a descrição da imagem aqui

(Marcação para teste)

ID/Departamento Nome 1 2 3 4 5
1 Tom 2h00 3,25 4,50 5,75 7h00
2 Joe 2,75 4h00 5,25 6h50 7,75
3 Cristina 3,50 4,75 6h00 7h25 8h50
4 Marca 4,25 5,50 6,75 8h00 9h25
5 Bem 5h00 6,25 7h50 8,75 10h00
6 Jane 5,75 7h00 8h25 9h50 10,75
7 Kerry 6h50 7,75 9h00 10h25 11h50
8 Jack 7h25 8h50 9,75 11h00 12h25
9 Rosa 8h00 9h25 10h50 11h75 13h00
10 Michelle 8,75 10h00 11h25 12h50 13,75
11 Michael 9h50 10,75 12h00 13h25 14h50
12 Paulo 10h25 11h50 12h75 14h00 15h25
13 Jose 11h00 12h25 13h50 14,75 16h00
14 Shonda 11h75 13h00 14h25 15h50 16,75
15 Erik 12h50 13,75 15h00 16h25 17h50

Aqui está uma pergunta que me ajudou a criar a primeira fórmula

excel

2 respostas

  1. Best Answer

    Você também pode tentar usar o seguinte, com uma combinação de VSTACK()+ TAKE()+ BYCOL()/MMULT():

    insira a descrição da imagem aqui

    =VSTACK(
    TAKE(A1:G16, 6),
    HSTACK(
        6,
        "Others",
        BYCOL(
            DROP(A1:G16, 6, 2),
            LAMBDA(x, SUM(x))
        )
    )
)

    Ou, usandoMMULT()

    =VSTACK(
    TAKE(A1:G16, 6),
    HSTACK(
        6,
        "Others",
        MMULT(
            SEQUENCE(
                ,
                ROWS(A1:G16) -
                    6
            ) ^ 0,
            DROP(A1:G16, 6, 2)
        )
    )
)

    Ou, sugerimos usar Structured Referencesaquela Tablesque é uma opção muito melhor que a seguinte, porém, uma fórmula alternativa, que toma a Coluna A como base e a utiliza como referência para o lastRow. Mas meu dinheiro está apostado Tables, nunca irei para o seguinte, é apenas uma alternativa.

    =LET(
     _LastRow, MATCH(2,1/(A:A<>"")),
     _Data, A1:INDEX(A:G, _LastRow, ),
     VSTACK(TAKE(_Data,6), HSTACK(6, "Others", BYCOL(DROP(_Data,6,2), LAMBDA(x,SUM(x))))))
    • 1

  2. Por favor, tente isto ( _datarefere-se à célula com os dados gerados):

    =LET(
_data,[cell containing the spill formula],
_data_below_top_5,DROP(_data#,6,2),
_others_row, HSTACK("","Others",BYCOL(DROP(_data#,6,2),LAMBDA(_col,SUM(_col)))),
_table_before_adding_total,VSTACK(TAKE(_data#,6),_others_row),
_totals_row,HSTACK("","Total",BYCOL(DROP(_table_before_adding_total,1,2),LAMBDA(_col,SUM(_col)))),
_empty_row,MAKEARRAY(1,COLUMNS(_data#),LAMBDA(a,b,"")),
VSTACK(_table_before_adding_total,_empty_row,_totals_row)
)

    Aliás, BYCOL(array,SUM)embora funcione no Windows, não funciona no Mac Excel (Microsoft 365).

    • 0

relate perguntas