Contagem hierárquica ou recursiva no excel (preferencialmente dentro de uma tabela dinâmica)?

Question

Estou tentando encontrar uma maneira de agregar dados em um conjunto de dados hierárquico, preferencialmente em uma tabela dinâmica, mas outros métodos também podem estar corretos. Considere um conjunto de dados (bastante simplificado para o exemplo) que se parece com o abaixo. A partir desses dados, estou tentando criar um conjunto de funções que responderão a perguntas como:

"Quanto estoque total eu tenho para Fruit?"

"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

Cada Item tem (entre muitas outras informações) uma Categoria , na qual podemos realmente pensar como um "pai". Mas observe também que, no conjunto de dados, todos os "pais" também têm suas próprias categorias pai. Neste conjunto de dados, uma amostra "ramificação" da hierarquia seria Comida- > Carne- > Frango.

Para responder à pergunta como "quantos tipos diferentes de frutas eu vendo" não é difícil, porque esta é a categoria de primeiro nível. Eu posso apenas usar a função COUNTIF e dizer "Quantos itens pertencem à categoria" Fruit "?" - e eu recebo uma tabela assim:

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ê verá quantas vezes "Maçãs" aparecem como a categoria de outra pessoa. (Desde que é zero, eu sei que as maçãs não são um pai ... isso deve ajudar, mas não sei como ...) Agora linha cinco, "Fruit", aparece como categoria de outra pessoa duas vezes - desde o número não é zero, eu sei que é uma categoria em vez de apenas um item. Tudo bem para o primeiro nível de matemática, mas ...

Isso me leva à parte que não consegui resolver ... Como descubro quantos tipos TOTAL de "Comida" eu tenho? E, dado que meus dados reais têm muito mais níveis hierárquicos, preciso subir e descer a árvore para descobrir quantas crianças totais existem em cada uma delas. A função COUNTIF do primeiro nível me diz que existem três subcategorias de Food (Fruit, Veg, & Meat) - mas o que eu realmente quero é de alguma maneira determinar recursivamente que Fruit, Veg e Meat também pode ser Categorias e somar os números correspondentes para essas crianças. Em termos de excel, o que eu realmente quero é ser capaz de construir outra coluna que recursivamente / iterativamente conte o número TOTAL de itens em toda a subárvore ... neste caso, existem sete itens únicos que pertencem ao Food: 3 carnes, 2 veg e 2 frutas.

Alguns fatores complicadores:

• Não há identificador explícito nos dados para nos informar se esse item específico também é uma categoria, ou se é um nível inferior item.

• Cada item só sabe o que é categoria / pai - não há dados explícitos para dizer se 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 receber até 10 níveis profundos, MAS não há garantias de que a profundidade de cada ramificação na hierarquia é consistente: alguns itens podem ter 3 níveis profundo, enquanto o próximo pode ser 8.

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

• Eu estou plenamente ciente de que isso seria um exercício trivial em qualquer linguagem de programação 'real' (Perl, Python, etc) ... mas no final eu tenho que passar isso para alguém que não tem programação experiência, por isso estou tentando muito muito difícil fazer isso se encaixar em uma pasta de trabalho do Excel "padrão".

Answer 1

No começo, eu concordo plenamente com o @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:

• level: nível do item real na hierarquia (itens raiz tem nível 1, o nível das crianças é aumentado)
  =IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1)
• código de nível: código em execução para cada item, exclusivo em NÍVEIS em =CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1)
• código longo: código concatenado de pai & item e =IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]]
• tem filho: 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 dos pais (por exemplo, se o código fruit for aa , então todos (grand ...) filhos de tem um código que começa com aa )

Respostasparasuasperguntas:

"How much total inventory do I have for Fruit?"

=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. Eu não vejo aqui apenas itens e categorias (você vende dois tipos de frutas apples e grapes , você não tem um produto chamado fruit para vender). Se você quiser contar também as categorias, exclua apenas a segunda parte da fórmula.

"How many different kinds of Food do I sell?"

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

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 função CHAR é 255, portanto, ter mais de 158 categorias diferentes em qualquer nível gerará um erro ( você pode expandir um pouco usando um caractere com código menor para o primeiro)
• à medida que seu banco de dados aumenta, provavelmente terá um desempenho reduzido (cálculos complexos), você pode definir o método de cálculo como "automático, exceto tabelas de dados" e calculá-lo manualmente quando precisar.

Answer 2

Eu acho que Máté tem uma boa resposta. O jeito que eu faria seria com listas:

(Todasasfórmulasdoarray,entãoCtrl+Shft+Enter)

Então,asfórmulas(arrastarparabaixo)

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ê decide classificá-los, você pode definitivamente criar uma hierarquia ou usar uma tabela dinâmica.

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