Como criar uma tabela dinâmica usando categorias?

Question

Eu tenho esses dados no Excel e quero encontrar a soma de cada categoria, e quero que a soma seja alterada automaticamente ao adicionar mais valores. Alguma idéia de como fazer isso, se possível?

    

Answer 1

Seja qual for o método usado para somar, você vai querer usar um intervalo nomeado dinâmico ou (no Excel 2007 ou posterior) uma tabela de dados . Qualquer um deles aumentará seu intervalo de dados conforme você insere novos valores.

Tabela de dados

Para uma tabela de dados, basta selecionar uma célula dentro do seu intervalo de dados e, em seguida, ir para a guia Início | Formate como Tabela e selecione qualquer um dos formatos disponíveis.

OExceltentarádeterminarautomaticamenteseuintervalodedados.Supondoqueseusdadosestejamdispostoscomonoseuexemplo,semlinhasoucolunasvaziasecabeçalhosnapartesuperior,eledeveconseguirointervaloautomaticamente.Senão,vocêpodeselecioná-loaqui.

CliqueemOKesuatabeladedadosserácriada.

Umatabeladedadosofereceváriosrecursosinteressantes:

• Omelhor,paraosnossospropósitos,équeelecriaumintervalonomeado,porpadrãochamadoTabela1,queseexpandeautomaticamentesenovosdadosforemadicionadosnalinhadiretamenteabaixoounacolunapróximaaele.

• Osegundomelhor,paraosnossospropósitos,équevocêpodeusaresseintervalonomeadoemfórmulas.Emseuexemplo,supondoqueoscabeçalhos"Gas" e "Grocery" estejam na coluna D, podemos colocar a seguinte fórmula na coluna E: =SUMIF(Table1[Item],D4,Table1[Value]) Esta fórmula examinará a tabela de dados, Table1 e verá a% colunaItem para todos os valores que correspondem ao valor em D4 . Em seguida, será somado os valores respectivos da coluna Value .

Você também pode usar a Tabela1 como a fonte de uma Tabela Dinâmica, mas ela não será atualizada automaticamente à medida que novos dados forem adicionados à tabela de dados; você precisaria ir para o Pivot, clicar com o botão direito nele e escolher "Atualizar".

Intervalo Nomeado Dinâmico

Para ser claro, recomendo a opção Data Table. É mais limpo, mais fácil e há menos espaço para as coisas darem errado! No entanto, se você preferir, podemos observar um intervalo nomeado dinamicamente. Isso seria necessário em versões mais antigas do Excel, e há algumas situações em que você pode preferir.

Na verdade, vamos criar dois intervalos nomeados, usando uma fórmula muito semelhante para cada um. No Excel 2007 e versões posteriores, vá para a guia Fórmulas e selecione Gerenciador de nomes:

NajanelaNameManager,cliqueemNewparairparaacaixaNewName.Nósvamosprecisardedoisitensessenciaisaqui-umnomeerefere-seafórmula.Onomenãopodeterespaços;nonossocaso,provavelmentevamosquererusaroitem:

NacaixaRefere-sea,vamoscolocarumafórmula.Noteque,aodigitarisso,asteclasdesetacomeçarãoaselecionarascélulasnaplanilha,amenosquevocêaperteF2primeiro.Digiteoseguinte:=OFFSET(Sheet1!$A$2,0,0,COUNTA(SHEET1!$A:$A)-1,1)

AfunçãoOFFSETusadapararetornarumintervalodecélulas,iniciandodeumacéluladereferência-nessecaso,Sheet1!A2-e,emseguida,movendo-separabaixoporumdeterminadonúmerodelinhaseporumdeterminadonúmerodecolunas.Estamosusando0,0paraisso.Emseguida,eleretornaumintervalocomumacertaalturaeumadeterminadalargura.EstamosusandooCOUNTA(A:A)paracontarquantascélulasnacolunaAtêmvaloresdequalquertipoparaobteressaaltura,subtraindo1paraconsiderarofatodequenossosdadoscomeçamnalinha2eretornandoumintervalode1colunalargamente.

Crieumsegundointervalonomeado,omesmoqueoprimeiro,mascomonome"Valor" e a fórmula referente à coluna B: =OFFSET(Sheet1!$B$2,0,0,COUNTA(SHEET1!$B:$B)-1,1)

Agora, podemos usar esses intervalos nomeados como as origens de uma fórmula semelhante àquela que usamos no método Tabela de dados : =SUMIF(Item,D4,Value)

Você também pode usar intervalos nomeados dinâmicos para alimentar uma Tabela Dinâmica, mas, nesse caso, você deseja alterar o ,1) para ,1) para retornar o conjunto de dados inteiro.

Use apenas a coluna inteira

Todos esses métodos são bastante envolvidos, mas evitam usar muitos recursos para atingir a meta. Se você não está preocupado com isso, você sempre pode usar a opção simples: faça referência a coluna inteira em suas fórmulas.

Coloque uma fórmula como essa em suas células de totais: =SUMIF($A:$A,D4,$B:$B) Isso inclui todas as linhas nas colunas A e B. Isso funcionará, mas com menos panache do que as outras opções.