Como obter uma lista desduplicada de rótulos de linha e seus valores somados no Excel 2010

Question

Eu tenho uma planilha do Excel com várias linhas neste formato:

Column A|Column B
label   | value
--------|--------

header (merged cells)
labelA  |12
labelB  |5
labelC  |9

header (merged cells)
labelA  |13
labelD  |8

É basicamente uma lista de rótulos e valores divididos em várias seções diferentes. Na realidade, existem 70 a 80 rótulos e todos os rótulos são usados de 5 a 10 vezes. Eu preciso criar uma saída em uma folha separada no seguinte formato:

Column A|Column B
label   | Total sum
--------|----------
labelA  |25
labelB  |5
labelC  |9
labelD  |8

Idealmente, a primeira planilha pode ser atualizada dinamicamente, com novas etiquetas refletindo imediatamente na segunda planilha.

Tenho certeza de que posso criar uma macro para isso, mas não quero adicionar macros se não precisar. Os dados não estão atualmente em uma tabela dinâmica. Existe alguma maneira de conseguir isso sem essas soluções?

    

Answer 1

Obter a soma para os diferentes rótulos é facilmente obtido usando uma fórmula SUMIF . Supondo que suas etiquetas estejam na Coluna A da sua planilha de resultados, com a primeira etiqueta na linha 2, a fórmula para B2 seria:

=SUMIF(SourceSheet!$A:$A,$A1,SourceSheet!$B:$B)

- amplie conforme necessário. Obter todos os marcadores automaticamente é um pouco mais complicado, já que você precisa desduplicá-los. A seguinte fórmula de matriz fará isso para os rótulos nas linhas de 1 a 100 da sua planilha de origem (ajuste o escopo conforme necessário, substituindo SourceSheet pelo nome correto da planilha):

=IFERROR(
   INDEX(SourceSheet!$A1:$A100,
     SMALL(
       IFERROR(
         IF(
           MATCH(SourceSheet!$A1:$A100,SourceSheet!$A1:$A100,0)<ROW(),
           "",
           MATCH(SourceSheet!$A1:$A100,SourceSheet!$A1:$A100,0)
         ),
       ""),
     ROW())
   ),
 "")

Observe que isso pertence a uma única linha no Excel (eu induzai a fórmula aqui para torná-la um pouco mais legível) e para que isso funcione, você precisa salvá-la como uma fórmula de matriz (selecione todo o intervalo em que deseja obter os resultados, insira a fórmula na barra de fórmulas - não na célula! - e salve-a com Ctrl + Shift + Enter ) .

Se a fórmula foi inserida corretamente, começando com valores como estes:

retornaráestesresultados:

Como você pode ver, isso retornará as linhas de cabeçalho da folha de origem também. Eles provavelmente podem ser filtrados, mas sem detalhes sobre seu conteúdo exato, não sei como.

    

Answer 2

A melhor coisa que você pode fazer é girar e, depois de adicionar novos dados, basta atualizar a tabela. Se você realmente não gosta de pivôs, pode usar sumif ou sumproduct . Antes de criar a fórmula, defina intervalos de nomes para suas colunas. Será mais fácil quando você criar a fórmula em outra planilha.

=sumif(ColumnA, "labelA", ColumnB)

ColumnA & ColumnB serão os nomes dos seus intervalos. É apenas uma amostra

=sumproduct((ColumnA="labelA")*(ColumnB))

Quanto aos novos marcadores, você pode adicioná-los manualmente na segunda planilha. Eu não sei como adicioná-los automaticamente, a menos que você faça uma macro. Espero que ajude. :)

    

Answer 3

Você pode começar com a função sumif . Para totalizar tudo para labelA , use esta função:

=sumif(Sheet1!A1:A9999, "labelA", Sheet1!B2:B9999)

Você precisaria adicionar uma função manualmente para cada novo rótulo. Não sei como adicionar automaticamente um total para um novo marcador.