Я пытаюсь найти способ агрегирования данных в иерархическом наборе данных, предпочтительно в сводной таблице, но другие методы также могут быть в порядке. Рассмотрим набор данных (значительно упрощенный для примера), который выглядит как приведенный ниже. Из этих данных я пытаюсь построить набор функций, которые будут отвечать на такие вопросы, как:
«Сколько всего инвентаря у меня есть для фруктов?"
«Сколько разных видов еды я продаю?"
Item Category
======= ========
Apples Fruit
Bacon Meat
Chicken Meat
Corn Veg
Food
Fruit Food
Grapes Fruit
Meat Food
Squash Veg
Steak Meat
Veg Food
Каждый элемент имеет (среди множества другой информации) категорию, которую мы можем считать "родителем". Но также обратите внимание, что в наборе данных все "родители" также имеют свои собственные родительские категории. В этом наборе данных одной выборочной "ветвью" иерархии будет « Пища-> Мясо-> Курица».
Ответить на вопрос типа "Сколько разных видов фруктов я продаю" несложно, потому что это категория первого уровня. Я могу просто использовать функцию COUNTIF и сказать:«Сколько предметов принадлежит категории" Фрукты "?"- и я получаю таблицу, которая выглядит так:
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
Легко - в первом ряду вы просто видите, сколько раз "Яблоки" появляются как чья-то категория. (Так как это ноль, я знаю, что Яблоки не являются родителями ... это должно помочь, но я не уверен, как ...) Теперь пятая строка, "Fruit", появляется как Чья-то Категория два раза - поскольку число НЕ равно нулю, я знаю, что это Категория, а не просто Предмет. Все хорошо для первого уровня математики, но ...
Это приводит меня к той части, которую я не смог решить ... Как я могу определить, сколько ВСЕХ видов "Еды" у меня есть? И учитывая, что мои фактические данные имеют гораздо больше уровней иерархии, мне нужно пройтись вверх и вниз по дереву, чтобы выяснить, сколько всего детей в каждом. Функция COUNTIF первого уровня говорит мне, что есть три подкатегории Food (Fruit, Veg, & Meat) - но я действительно хочу, чтобы как-то рекурсивно определить, что Fruit, Veg и Meat также могут быть Категории, и сумма до соответствующих номеров для этих детей. С точки зрения Excel, я действительно хочу иметь возможность построить еще один столбец, который рекурсивно / итеративно подсчитывает ИТОГО количество предметов во всем этом поддереве ... в этом случае есть семь уникальных предметов, которые относятся к еде: 3 мяса, 2 овоща и 2 фрукта.
Некоторые осложняющие факторы:
В данных нет явного идентификатора, который бы указывал нам, является ли этот конкретный элемент также категорией или это элемент нижнего уровня.
Каждый элемент знает только, что это за категория / родитель - нет явных данных, чтобы определить, есть ли у него дети или нет. Сказано иначе: все элементы принадлежат категории, но только некоторые элементы также являются категориями.
В реальных данных родительские отношения могут иметь глубину до 10 уровней, НО нет никаких гарантий, что глубина каждой ветви в иерархии последовательна: некоторые элементы могут иметь глубину 3 уровня, а следующая - 8.
Корневой или конечный родитель не имеет категории, но это единовременный случай, который я могу легко обработать вручную.
Я полностью осознаю, что это было бы тривиальным упражнением на любом "реальном" языке программирования (Perl, Python и т.д.)... но в конечном итоге я должен передать это кому-то, у кого нет опыта программирования, поэтому я изо всех сил стараюсь вписать это в "стандартную" книгу Excel.
