☰

Contagem hierárquica ou recursiva no Excel (de preferência em uma tabela dinâmica)?

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table
Contagem hierárquica ou recursiva no Excel (de preferência em uma tabela dinâmica)?

Estou tentando encontrar uma maneira de agregar dados em um conjunto de dados hierárquico, de preferência dentro de uma tabela dinâmica, mas outros métodos também podem funcionar. Considere um conjunto de dados (bastante simplificado para o exemplo) semelhante ao mostrado abaixo. A partir desses dados, estou tentando construir um conjunto de funções que responderão a perguntas como:

"Quanto estoque total eu tenho de frutas?"

"Quantos tipos diferentes de comida eu vendo?"

Item     Category
=======  ========
Apples   Fruit
Bacon    Meat
Chicken  Meat
Corn     Veg
Food     
Fruit    Food
Grapes   Fruit
Meat     Food
Squash   Veg
Steak    Meat
Veg      Food

CadaItemtem (entre muitas outras informações) umCategoria, que podemos realmente considerar um "pai". Mas observe também que dentro do conjunto de dados, todos os “pais” também têm suas próprias categorias de pais. Neste conjunto de dados, um 'ramo' de amostra da hierarquia seriaComida->Carne->Frango.

Responder a perguntas como “Quantos tipos diferentes de frutas eu vendo” não é difícil, porque esta é uma categoria de primeiro nível. Posso simplesmente usar a função CONT.SE e dizer "Quantos itens pertencem à categoria "Frutas"?" -- e recebo uma tabela parecida com esta:

Item    Category    COUNTIF(categories,me)
Apples  Fruit       0
Bacon   Meat        0
Chicken Meat        0
Corn    Veg         0
Food    Food        3
Fruit   Food        2
Grapes  Fruit       0
Meat    Food        3
Squash  Veg         0
Steak   Meat        0
Veg     Food        2

Fácil - na primeira linha você vê quantas vezes "Maçãs" aparece como categoria de outra pessoa. (Como é zero, sei que Maçãs não são pais... isso deve ajudar, mas não tenho certeza de como...) Agora a linha cinco, "Fruta", aparece duas vezes como categoria de outra pessoa - já que o número NÃO é zero, sei que é uma categoria em vez de apenas um item. Tudo muito bem para o primeiro nível de matemática, mas...

Isso me leva à parte que não consegui resolver ...Como faço para descobrir quantos TOTAIS tipos de “comida” eu tenho? E como meus dados reais têm muito mais níveis de hierarquia, preciso subir e descer na árvore para descobrir quantos filhos há em cada um deles. A função CONT.SE de primeiro nível me diz que existem três subcategorias de alimentos (frutas, vegetais e carne) - mas o que eu realmente quero é de alguma forma determinar recursivamente que frutas, vegetais e carnetambém pode serCategorias e some os números correspondentes para essas crianças. Em termos de Excel, o que eu realmente quero é ser capaz de construir outra coluna que conte recursivamente/iterativamente o número TOTAL de itens em toda aquela subárvore... neste caso, existem sete itens únicos que pertencem a Alimentos: 3 carnes, 2 vegetais e 2 frutas.

Alguns fatores complicadores:

  • Não háexplícitoidentificador nos dados para nos dizer se esse item específico também é uma categoria ou se é um item de nível inferior.

  • Cada item sabe apenas qual é sua categoria/pai - não há dados explícitos para dizer se ele tem filhos ou não. Dito de outra forma: todos os Itens pertencem a uma Categoria, mas apenas alguns Itens também são Categorias.

  • Nos dados reais, o relacionamento pai pode atingir até 10 níveis de profundidade, MAS não há garantias de que a profundidade de cada ramo na hierarquia seja consistente: alguns itens podem ter 3 níveis de profundidade, enquanto o próximo pode ter 8.

  • O pai raiz ou final não vem com uma categoria, mas este é um caso único que posso resolver facilmente manualmente.

  • Estou plenamente ciente de que este seria um exercício trivial em qualquer linguagem de programação 'real' (Perl, Python, etc)... mas, em última análise, tenho que entregar isso a alguém que não tem experiência em programação, então estou tentando muito fazer com que isso se encaixe em uma pasta de trabalho "padrão" do Excel.

Responder1

A princípio concordo plenamente com @Raystafarian, o Excel não é a ferramenta certa para isso.

No entanto, se você realmente quiser fazer isso aqui, aqui está uma solução com algumas colunas auxiliares:

  • nível: nível do item real na hierarquia (os itens raiz têm nível 1, o nível dos filhos é aumentado)
    =IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1)
  • código de nível: código em execução para cada item, exclusivo DENTRO DOS NÍVEIS
    =CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1)
  • código longo: código concatenado do pai e do item
    =IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]]
  • has child: booleano informando se o item tem filho
    =COUNTIF([Category],[@Item])>0

Com este modelo, uma categoria contém todos os itens e subcategorias cujo código começa com a mesma sequência do código pai (por exemplo, se fruito código de for aa, então todos os (grand...) filhos dele terão um código começando com aa)

insira a descrição da imagem aqui

Respostas para suas perguntas:

"Quanto estoque total eu tenho de frutas?"

=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
apenas de acordo com o modelo, todos os itens com a mesma sequência inicial. Não conto aqui apenas itens e não categorias (você vende dois tipos de frutas applese grapesnão tem um produto chamado fruitpara vender). Se você quiser contar também categorias, basta excluir a segunda parte da fórmula.

"Quantos tipos diferentes de comida eu vendo?"

=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
Muito parecido comSUMIF

Aviso

Esta solução tem duas limitações:

  • número de caracteres: atualmente começa em a, que tem um código de 97, e o último caractere suportado na CHARfunção é 255, portanto, ter mais de 158 categorias diferentes em qualquer nível causará um erro (você pode expandi-lo um pouco usando o caractere com código menor para o primeiro)
  • à medida que seu banco de dados aumenta, provavelmente o desempenho diminuirá (cálculos complexos). Você pode definir o método de cálculo como "automático, exceto para tabelas de dados" e calculá-lo manualmente quando precisar.

Responder2

Acho que Máté tem uma boa resposta. A maneira que eu faria seria com listas:

insira a descrição da imagem aqui

(Todas as fórmulas de matriz, então Ctrl+ Shft+ Enter)

Então, as fórmulas (arraste para baixo)

D2 =INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($B$2:$B$12),COUNTIF($D$1:$D1,$A$2:$A$12),""),0))    
E2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$D$2,COUNTIF($E$1:$E1,$A$2:$A$12),""),0))
F2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$2,COUNTIF($F$1:$F1,$A$2:$A$12),""),0))
G2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$3,COUNTIF($G$1:$G1,$A$2:$A$12),""),0))
H2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$4,COUNTIF($H$1:$H1,$A$2:$A$12),""),0))

Agora, dependendo de como você decidir classificá-los, você pode definitivamente criar uma hierarquia ou usar uma tabela dinâmica.

Eu provavelmente daria nomes às colunas A e B para que você possa trabalhar com intervalos nomeados.

informação relacionada