Её величество Иерархия. Классификация и способы хранение в MS Excel13.05.2023 21:31

Введение

Я работаю консультантом в области бюджетирования, финансовой отчетности. Уже много лет я интересуюсь иерархиями и эффективной работой с ними. Основной средой работы до недавнего времени был MS Excel, сейчас уже половина работы на Pandas. Оба этих инструмента по своей природе плоские (т.е. в основе — плоские таблицы). Тем не менее, каждый день мы встречаем иерархии и с ними приходится как-то работать. Давайте разбираться.

Содержание статьи

Термины

Когда мы начинаем работать со сложной системой, первый шаг — определение терминов. Во многом они из теории графов. Некоторые термины определены не строго, ряд моментов я посчитал очевидными. Исходил скорее из практической бизнес-потребности и здравого смысла.

Вершина/элемент — базовый объект иерархии. Между вершинами устанавливаются связи (ребра)
Ребро — направленная связь от одной вершины к другой. От родителя к ребенку
Дети/ребенок/дочерние вершины — потомок/потомки вершины (количество от 0 до N)
Родитель вершины — предок вершины (количество от 0 до 1)
Лист — часть вершин может хранить на себе данные, это и есть листовые вершины
Узел — часть вершин являются группировочными, они сами по себе не хранят данные. Они всегда расчетные (т.е. показатели на них рассчитываются агрегацией всех детей на всех уровнях ниже
Уровни — каждая вершина иерархии принадлежит определенному уровню
Корень — вершина на первом уровне. Вершина не имеющая родителей
Дерево — иерархия у которой один корень
Лес — иерархия у которой более одного корня

Ограничения

Давайте явно сформулируем ряд ограничений, которые будут для нас отправной точкой. Обращаю внимание, что наша цель — систематизация наиболее распространенных иерархий в финансах и бизнесе, а не всех вообще. Т.е. система ограничений может быть и другой.

1. Очевидные ограничения: нет циклов; у каждой вершины либо ноль, либо один родитель; все вершины уникальные (нет дубликатов)

2. Иерархия выровнена по левому краю (left balanced, сбалансированная слева). Т.е. вершины без родителей могут быть только на первом уровне

3. Уровень вершины определяется полным путем к этой вершине и не может быть установлен принудительно. Ребенок всегда должен быть на уровне +1 к родительскому (skip level not allowed, пропуск уровней не допускается)

4. Вершины без детей могут быть на разных уровнях (right unbalanced, допускается иерархия несбалансированная справа)

Классификация

Раньше я думал, что все иерархии одинаковые. Но это не так. Давайте рассмотрим две иерархии:

1. Есть множество сотрудников компании. У каждого сотрудника может быть от 0 до 1 начальников и от 0 до N подчиненных.

2. Есть справочник Продукция. Продукция иерархически сгруппирована (например, стол и стул попадут в узел Мебель и т.д.)

В целом это две иерархии. Но с другой стороны очевидно, что у них разная природа. Ключевое отличие заключается в том, что все сотрудники — это равноправные объекты, т.е. у них одинаковые атрибуты, и вообще, это экземпляры одного класса. А в справочнике Продукция, стул и мебель нельзя назвать равноправными объектами. Стул — это конкретный продукт (листовой элемент), а мебель — это группировка (узловой элемент).

Поэтому для удобства мы выделяем два класса иерархий: однородные иерархии и неоднородные иерархии. Однородные — когда все вершины являются представителями одного класса, неоднородные — когда выделяются отдельно узлы и листы (т.е. вершины разные по своей природе). Важно, что у однородной иерархии все вершины — листовые и могут хранить на себе одинаковый состав атрибутов и числовые показатели.

Далее посмотрим на неоднородные иерархии. В них есть ключевой вопрос, как отличить узел от листа. Долгое время я думал, что действует простое правило: «вершина без детей — это лист, вершина с детьми — это узел». Это правило часто используют для написания формул в Excel, которые автоматически определяют тип вершины (лист или узел). Но этот принцип не является аксиомой, есть и другие иерархии. Рассмотрим две:

1. Иерархия папок и файлов в Windows (объект «ссылка» не учитываем, иначе будет граф)

2. Справочник Продукция, разрабатываемый для какой-нибудь Excel модели

Папка может быть без файлов, но папка в любом случае является узловым элементом, который в данный момент не имеет детей. А вот в справочнике Продукция отсутствие детей — это достаточное условие, того, что перед нами листовой элемент. По крайней мере, так считается по умолчанию.

Таким образом, неоднородные иерархии разделяются на два типа, в зависимости от того выполняется ли правило «вершина без детей — это лист, вершина с детьми — это узел».

Если выполняется, то узлы и листы иерархии определяются автоматически по ее структуре. Для удобства будем называть это — Узлы автоматом неоднородной иерархии.

Если не выполняется, то для каждой вершины без детей существует неопределенность, поэтому надо хранить признак, является ли такая вершина листовой или узловой. А это будет — Узлы вручную.

Классификаций иерархий.

Прочие свойства иерархий

Ограничение на однородность детей

В некоторых случаях можно ввести ограничения на иерархию: узел может иметь только однородных детей (т.е. либо только узлы, либо только листы). Обычно данное ограничение не применяется, но могут быть исключения.

Режим сортировки

Важным свойством иерархии является сортировка ее вершин. Сортировка может иметь такие режимы:

1. Нет сортировки, дети каждого узла — это неупорядоченное множество

2. Сортировка есть. Она сделана вручную, т.е. не алгоритмически

3. Сортировка есть. Она установлена в виде правила (по убыванию/по возрастанию). Правило может быть установлено для:
   

   1. Всей иерархии в целом

   2. Каждого уровня отдельно

   3. Каждого узла отдельно

Форматы хранение в MS Excel

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

«Формат 1. Ребенок справа снизу»

Кол-во строк равно кол-ву элементов иерархии. Первый ребенок у любого узла действительно идет сразу вправо и вниз.

Обычно однородную иерархию в таком формате не хранят. А вот неоднородные хранят именно в таком виде. Если имеем дело с неоднородной иерархией с автоматическими узлами (наиболее частая ситуация), то столбец 7 на скрине рассчитывается формулой.

Плюсы:

• самый распространенный формат в среде Excel для неоднородных иерархий с автоматическими узлами (для ручных узлов тоже может быть использован)

• наглядная иерархия для восприятия человеком

• позволяет хранить сортировку вершин, атрибуты листов

Минусы:

Кстати, в Excel для сводных таблиц есть функционал показа иерархии в таком виде: «Show in Outline form».

«Формат 2. Путь к элементу»

В каждой строке полный путь к элементу.

Почти тоже самое, что и формат 1, но только для каждой вершины физически указан полный путь. Это сильно упрощает анализ, т.к. может быть обработано фильтрами, сводными таблицами в Excel. Часто этот формат используется как вспомогательный к формату 1. Минусом является то, что этот формат уже не наглядный, человеку сложнее его воспринимать. Плюс он более трудоемкий, если изменения иерархии вносит человек вручную. Поэтому обычно формат 2 получают автоматически из формата 1 для аналитических целей (макросом, питоном или ручным переводом).

«Формат 3. Путь к листу (все элементы)»

Полный путь указан только для листовых элементов.

Почти тоже самое, что и формат 2, но только полный путь указывается не для всех вершин иерархии, а только для листовых. Редко используется, т.к. нет очевидных преимуществ по сравнению с форматом 2. А вот минусы есть — более сложная нотация, сложность в заполнении таблицы человеком.

«Формат 4. Отступы/пробелы»

Уровень кодируется количеством отступов.

Этот формат похож на формат 1, но только тут играют роль не столбцы с уровнями, а количество отступов (табуляция или определенное количество пробелов). Формат часто встречается, однако с ним сложно работать. В процессе определения уровня для вершин часто возникают ошибки из-за несистемных отступов и человеческих ошибок. В Excel надо использовать макрофункцию, чтобы посчитать количество отступов в ячейке. Из плюсов — более компактное представление иерархии (один столбец, вместо нескольких).

Кстати, в Excel для сводных таблиц есть функционал показа иерархии в таком виде: «Show in Compact form». Там можно настраивать количество отступов, что удобно.

«Формат 5. Перечень элементов и их уровней»

Уровень указан числом введенным вручную.

Этот формат похож на формат 4, однако вместо отступов используются уже готовые уровни для каждой вершины (столбец — «Уровень»). Используется редко, т.к. сложен для восприятия человеком и плохо пригоден для анализа в Excel.

«Формат 6. Путь к листу (только листы)»

Каждая строчка — полный путь к листовому элементу.

Это самое плоское представление иерархии, которое можно себе представить. По сути получается из формата 3, только надо удалить все узловые элементы. Удобно анализировать сводными таблицами, фильтрами. Человеком воспринимается не очень, т.к. нет визуальной иерархии (ступенек с уровнями).

ВАЖНО! Это единственный формат хранения, где каждая строчка — листовой элемент и порядок строк не важен. В связи с этим, данный формат иерархии может быть добавлен в любую плоскую таблицу, к любому столбцу. Это позволяет подходить к иерархии как к группировке листовых элементов.

Т.е. если у меня есть таблица Продукты-Менеджеры, я могу добавить в нее две иерархии (иерархию Продуктов и иерархию Менеджеров), таблица останется плоской и ее можно будет анализировать через сводную таблицу и фильтрами.

«Формат 7. Элемент-родитель»

У каждого элемента либо один, либо ноль родителей.

Начинаем со списка всех вершин. Для каждой указывает непосредственного родителя (если он есть). Этот формат хорошо подходит для хранения однородных иерархий, т.к. позволяет удобно хранить список всех вершины и их атрибутов.

Для неоднородных иерархий плохо подходит, т.к. список узлов + листов одновременно получается разнородным. Такого я не встречал на практике.

»8. Ребенок справа»

Кол-во строк равно кол-ву листовых элементов иерархий. Первый ребенок у любого узла действительно идет сразу справа.

Немного похоже на формат 1, однако в каждой строчке располагается листовой элемент. Т.е. первый ребенок каждого узла расположен сразу справа.

Раньше я думал, что данный вид иерархий редко применяется, но недавно выяснили, что на формате 8 почти всегда основано строение многострочной шапки таблицы. Т.е. если вы видите таблицу в Excel и в ней шапка состоит из более, чем одной строки, то вероятнее всего, она использует формат иерархии 8 (естественно с учетом того, что шапка — это транспонированная таблица и ее надо читать сверху вниз).

Интересно, что развивая одну область знания (форматы иерархий) мы в какой-то момент поняли, что она пересекается с другой областью — структура многострочных шапок таблиц.

Кстати, в Excel для сводных таблиц есть функционал показа иерархии в таком виде: «Show in Tabular form».

»9. Совмещенные иерархии»

Для ведения в одной таблице более двух иерархий одновременно требуется сложная нотация. В столбцах 5, 6, 7 указана применимость элемента для каждой из иерархий.

Очень сложный формат. Суть в том, чтобы в единой таблице хранить две или более иерархии, которые очень похожи, но немного различаются (например, два справочника по двум компаниям группы, который почти унифицированы, но допускаются небольшие различия). Самое интересное, что такой формат встречается в реальности, но сложность его нотации настолько большая, что мы сделали только первичную проработку нотации ведения такой таблицы. Детали не привожу, ввиду их сырости.

Я видел попытки вести совмещенную иерархию, но там не было никакой утвержденной нотации. Поэтому расшифровать таблицу было крайне сложно.

Работа с иерархиями в Python, SQL

Мы часто получаем данные из Excel в Python для обработки. Это удобно и позволяет решить множество сложных задач, например, найти неточные дубликаты в справочнике (fuzzy match, неточное сопоставление текстовых строк). В части иерархий в Python мы пока не выработали единых подходов к хранению и обработке иерархий. Пока для каждой задаче разрабатываем подход индивидуально. Однако есть идеи по стандартизации этого процесса и созданию Python класса, который бы позволил структурировать эти операции.

В части иерархий в SQL отмечу вот эти две интересные статьи на Хабр:
https://habr.com/ru/articles/537062/
https://habr.com/ru/articles/733814/

Как я понял из статей, единого подхода по хранению иерархий в реляционных базах данных нет. Все изобретают свои способы, под свои задачи. Наверное самый эффективный способ — это использовать специальный язык запросов и тип баз данных (например, кубовые базы данных и запрос MDX), которые прямо предназначены для работы с иерархиями. Но это достаточно сложный путь.

Как это практически применять?

Есть два режима работы с иерархиями: 1) вам надо спроектировать иерархию в Excel 2) вам дали уже готовую иерархию и надо с ней работать. В любом случае следует нужно:

1. Вспомните классификацию иерархий. Определить класс иерархии

2. Подумайте какой режим сортировки наиболее логичен. Нужен ли он вообще?

3. Решите, есть ли ограничения на однородность детей. Обычно нет.

4. Важно решить, где и как оптимально хранить такую иерархию, чтобы с ней было удобно работать. Вспомните 8 форматов, которые мы разобрали выше

5. Сформулируйте контроли и проверки целостности иерархии. Попытайтесь их автоматизировать.

Заключение

Зачем все это? Я ставил перед собой несколько целей:

1. При работе со сложными системами половина проблем возникает ввиду отсутствия общей терминологии. Наличие единых терминов — критически важно для решения практических проблем с иерархиями

2. Задачи с иерархиями возникает каждый день, их надо как-то решать средствами Excel, иногда в связке с Python. Две наиболее частые проблемы — определения оптимального формата хранения иерархии в Excel и интерпретация уже созданных кем-то иерархий. Надеюсь моя классификация и перечень форматов хранения упростят эти процессы

3. Глубокое понимание иерархии позволит контролировать её целостность, автоматизировать проверки в Excel и Python, делать анализ и преобразование форматов

p.s. Цифровизация консалтинга

Систематизация подходов к работе с иерархиями проходит в рамках нашего первого шага по цифровизации. Его концепция в следующем:

«Все ключевые данные на проекте должны быть в 1NF (плоская таблица), либо должен существовать алгоритм их перевода в 1NF»

А ниже полный перечень направлений цифровизации по которым мы двигаемся вперед:

1. Данные, плоские таблицы, реляционные базы данных

2. Алгоритмы, Python, VBA

3. Иерархии

4. Графы

5. Математика, системное мышление, комбинаторика, теория множеств

6. Нейросети

>>> тут мы публикуем много интересных видео по этим темам (YouTube)

© Habrahabr.ru