☰

Иерархический или рекурсивный подсчет в Excel (предпочтительно в сводной таблице)?

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table
Иерархический или рекурсивный подсчет в Excel (предпочтительно в сводной таблице)?

Я пытаюсь найти способ агрегировать данные в иерархическом наборе данных, желательно в сводной таблице, но другие методы тоже могут подойти. Рассмотрим набор данных (значительно упрощенный для примера), который выглядит как тот, что ниже. Из этих данных я пытаюсь построить набор функций, которые ответят на такие вопросы, как:

«Какой общий объем запасов фруктов у меня есть?»

«Сколько разных видов еды я продаю?»

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

КаждыйЭлементимеет (среди множества другой информации)Категория, который мы действительно можем считать "родителем". Но также обратите внимание, что в наборе данных все "родители" также имеют свои собственные родительские категории. В этом наборе данных одна выборочная "ветвь" иерархии будетЕда->Мясо->Курица.

Ответить на вопрос типа «Сколько разных видов фруктов я продаю» несложно, потому что это категория первого уровня. Я могу просто использовать функцию СЧЁТЕСЛИ и сказать «Сколько товаров относятся к категории «Фрукты»?» — и я получаю таблицу, которая выглядит так:

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

Легко - для первой строки вы просто видите, сколько раз "Яблоки" появляются как чья-то чужая Категория. (Поскольку это ноль, я знаю, что Яблоки не являются родительским элементом... это должно помочь, но я не уверен, как...) Теперь строка пять, "Фрукты", появляется как чья-то чужая Категория два раза - поскольку число НЕ ноль, я знаю, что это Категория, а не просто Элемент. Все хорошо и замечательно для математики первого уровня, но...

Это подводит меня к той части, которую я не смог решить...Как мне узнать, сколько ВСЕГО видов «еды» у меня есть? И учитывая, что мои фактические данные имеют гораздо больше уровней иерархии, мне нужно пройти вверх и вниз по дереву, чтобы выяснить, сколько всего детей в каждом из них. Функция СЧЁТЕСЛИ первого уровня сообщает мне, что есть три подкатегории Food (Fruit, Veg, & Meat) -- но на самом деле я хочу, чтобы она каким-то образом рекурсивно определяла, что Fruit, Veg, & Meatтакже может бытьКатегории и суммировать соответствующие числа для этих детей. В терминах Excel, то, что я действительно хочу, это иметь возможность построить еще один столбец, который рекурсивно/итеративно подсчитывает ОБЩЕЕ количество элементов во всем этом поддереве... в этом случае есть семь уникальных элементов, которые относятся к еде: 3 мяса, 2 овоща и 2 фрукта.

Некоторые осложняющие факторы:

  • Нет никакихявныйидентификатор в данных, сообщающий нам, является ли данный конкретный элемент категорией или это элемент нижнего уровня.

  • Каждый элемент знает только свою категорию/родительскую сущность - нет явных данных, чтобы сказать, есть ли у него потомки или нет. Другими словами: все элементы принадлежат к категории, но только некоторые элементы также являются категориями.

  • В реальных данных родительская связь может достигать 10 уровней в глубину, НО нет никаких гарантий, что глубина каждой ветви в иерархии будет одинаковой: некоторые элементы могут иметь глубину 3 уровня, а следующий — 8.

  • Корневой или конечный родительский элемент не имеет категории, но это единичный случай, который я могу легко обработать вручную.

  • Я полностью осознаю, что это было бы тривиальным упражнением на любом «реальном» языке программирования (Perl, Python и т. д.)... но в конечном итоге мне придется передать это кому-то, у кого нет опыта программирования, поэтому я очень-очень стараюсь втиснуть это в «стандартную» рабочую книгу Excel.

решение1

Во-первых, я полностью согласен с @Raystafarian, Excel — неподходящий инструмент для этого.

Однако, если вы действительно хотите сделать это здесь, вот решение с несколькими вспомогательными столбцами:

  • уровень: уровень фактического элемента в иерархии (корневые элементы имеют уровень 1, уровень дочерних элементов увеличивается)
    =IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1)
  • код уровня: код выполнения для каждого элемента, уникальный ВНУТРИ УРОВНЯ
    =CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1)
  • длинный код: объединенный код родителя и элемента
    =IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]]
  • имеет потомка: логическое значение, указывающее, есть ли у элемента потомок
    =COUNTIF([Category],[@Item])>0

В этой модели категория содержит все элементы и подкатегории, код которых начинается с той же последовательности, что и код родительского элемента (например, если fruitкод элемента равен aa, то все его (внучатые...) потомки имеют код, начинающийся с aa).

введите описание изображения здесь

Ответы на ваши вопросы:

«Какой общий объем запасов фруктов у меня есть?»

=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
просто согласно модели, все элементы с одинаковой начальной последовательностью. Я не считаю здесь только элементы, а не категории (вы продаете два типа фруктов applesи grapes, у вас нет продукта, который нужно fruitпродать). Если вы хотите посчитать также категории, то просто исключите вторую часть формулы.

«Сколько разных видов еды я продаю?»

=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
Очень похоже наSUMIF

Предупреждение

Это решение имеет два ограничения:

  • количество символов: в настоящее время он начинается с a, который имеет код 97, а последний поддерживаемый символ в CHARфункции - 255, поэтому наличие более 158 различных категорий на любом уровне приведет к ошибке (вы можете немного расширить его, используя символ с меньшим кодом для первой)
  • По мере увеличения базы данных ее производительность, вероятно, снизится (сложные вычисления). Возможно, вы захотите установить метод расчета «автоматически, за исключением таблиц данных» и выполнять расчеты вручную только тогда, когда это необходимо.

решение2

Я думаю, у Мате есть хороший ответ. Я бы сделал это с помощью списков:

введите описание изображения здесь

(Все формулы массива, поэтому Ctrl+ Shft+ Enter)

Итак, формулы (перетащите вниз)

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

Теперь, в зависимости от того, как вы решите их сортировать, вы определенно можете создать иерархию или использовать сводную таблицу.

Я бы, наверное, дал столбцам A и B имена, чтобы вы могли работать с именованными диапазонами.

Связанный контент