☰

¿Conteo jerárquico o recursivo en Excel (preferiblemente dentro de una tabla dinámica)?

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table
¿Conteo jerárquico o recursivo en Excel (preferiblemente dentro de una tabla dinámica)?

Estoy tratando de encontrar una manera de agregar datos en un conjunto de datos jerárquico, preferiblemente dentro de una tabla dinámica, pero otros métodos también podrían estar bien. Considere un conjunto de datos (muy simplificado para el ejemplo) similar al siguiente. A partir de estos datos, intento crear un conjunto de funciones que respondan preguntas como:

"¿Cuánto inventario total tengo para frutas?"

"¿Cuántos tipos diferentes de alimentos vendo?"

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

CadaArtículotiene (entre mucha otra información) unaCategoría, al que realmente podemos considerar como un "padre". Pero también tenga en cuenta que dentro del conjunto de datos, todos los "padres" también tienen sus propias categorías de padres. En este conjunto de datos, una "rama" de muestra de la jerarquía seríaComida->Carne->Pollo.

Responder a la pregunta "¿Cuántos tipos diferentes de frutas vendo?" no es difícil, porque esta es una categoría de primer nivel. Puedo usar la función CONTAR.SI y decir "¿Cuántos artículos pertenecen a la categoría "Fruta"?" -- y obtengo una tabla que se parece a 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: en la primera fila verás cuántas veces aparece "Manzanas" como la categoría de otra persona. (Como es cero, sé que las manzanas no son un padre... esto debería ayudar, pero no estoy seguro de cómo...) Ahora la fila cinco, "Fruta", aparece como la categoría de otra persona dos veces, ya que el número NO es cero, sé que es una categoría en lugar de solo un artículo. Todo muy bien para el primer nivel de matemáticas, pero...

Esto me lleva a la parte que no he podido resolver...¿Cómo puedo saber cuántos tipos TOTALES de "Alimentos" tengo? Y dado que mis datos reales tienen muchos más niveles de jerarquía, necesito subir y bajar por el árbol para calcular cuántos niños en total hay en cada uno. La función CONTAR.SI de primer nivel me dice que hay tres subcategorías de alimentos (frutas, verduras y carnes), pero lo que realmente quiero es que de alguna manera determine de forma recursiva que frutas, verduras y carnestambién podría serCategorías y sumar los números correspondientes a esos niños. En términos de Excel, lo que realmente quiero es poder construir otra columna que cuente recursiva/iterativamente el número TOTAL de elementos en todo ese subárbol... en este caso, hay siete elementos únicos que pertenecen a Alimentos: 3 carnes, 2 verduras y 2 frutas.

Algunos factores que complican:

  • No hayexplícitoidentificador en los datos para indicarnos si ese elemento en particular también es una categoría o si es un elemento de nivel inferior.

  • Cada elemento solo sabe cuál es su categoría/padre; no hay datos explícitos que indiquen si tiene hijos o no. Dicho de otra manera: todos los Artículos pertenecen a una Categoría, pero sólo algunos Artículos también son Categorías.

  • En los datos reales, la relación principal puede tener hasta 10 niveles de profundidad, PERO no hay garantías de que la profundidad de cada rama en la jerarquía sea consistente: algunos elementos pueden tener 3 niveles de profundidad, mientras que el siguiente puede tener 8.

  • El padre raíz o último no viene con una categoría, pero este es un caso único que puedo manejar fácilmente de forma manual.

  • Soy plenamente consciente de que esto sería un ejercicio trivial en cualquier lenguaje de programación "real" (Perl, Python, etc.)... pero en última instancia tengo que entregárselo a alguien que no tiene experiencia en programación, así que Esforzándome mucho para que esto encaje en un libro de Excel "estándar".

Respuesta1

Al principio, estoy totalmente de acuerdo con @Raystafarian, Excel no es la herramienta adecuada para eso.

Sin embargo, si realmente quieres hacerlo aquí, aquí tienes una solución con algunas columnas auxiliares:

  • nivel: nivel del elemento real en la jerarquía (los elementos raíz tienen el nivel 1, el nivel de los niños aumenta)
    =IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1)
  • código de nivel: código de ejecución para cada elemento, único DENTRO DE LOS NIVELES
    =CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1)
  • código largo: código concatenado de padre y artículo
    =IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]]
  • tiene hijo: valor booleano que indica si el elemento tiene un hijo
    =COUNTIF([Category],[@Item])>0

Con este modelo, una categoría contiene todos los elementos y subcategorías cuyo código comienza con la misma secuencia que el código principal (por ejemplo, si fruitel código es aa, entonces todos (nietos...)hijos tienen un código que comienza con aa).

ingrese la descripción de la imagen aquí

Respuestas a tus preguntas:

"¿Cuánto inventario total tengo para frutas?"

=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
Sólo según el modelo, todos los artículos con la misma secuencia inicial. Aquí no cuento solo artículos, no categorías (vendes dos tipos de frutas applesy grapesno tienes un producto llamado fruita vender). Si desea contar también las categorías, simplemente excluya la segunda parte de la fórmula.

"¿Cuántos tipos diferentes de alimentos vendo?"

=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
Bastante similar conSUMIF

Advertencia

Esta solución tiene dos limitaciones:

  • número de caracteres: actualmente comienza desde a, que tiene un código de 97, y el último carácter admitido en CHARla función es 255, por lo que tener más de 158 categorías diferentes en cualquier nivel te dará un error (puedes expandirlo un poco usando el carácter con código más pequeño para el primero)
  • A medida que su base de datos aumenta, probablemente tendrá un rendimiento menor (cálculos complejos), es posible que desee configurar el método de cálculo en "automático, excepto para las tablas de datos" y calcularlo manualmente justo cuando lo necesite.

Respuesta2

Creo que Máté tiene una buena respuesta. La forma en que lo haría sería con listas:

ingrese la descripción de la imagen aquí

(Todas las fórmulas de matriz, entonces Ctrl+ Shft+ Enter)

Entonces, las fórmulas (arrastrar hacia abajo)

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))

Ahora, dependiendo de cómo decidas ordenarlos, definitivamente puedes crear una jerarquía o usar una tabla dinámica.

Probablemente le daría nombres a las columnas A y B para que pueda trabajar con rangos con nombres.

información relacionada