Как сделать иерархический список в excel

Многоуровневый связанный список в EXCEL на основе таблицы

В статье Многоуровневый связанный список рассмотрен вариант 3-х уровневого списка. Элементы каждого уровня в нем располагаются на отдельных листах. Это не всегда удобно: при создании 4-х и 5-и уровневых списков — резко увеличивается число задействованных столбцов. В этой статье сформируем связанный список из единой таблицы.

Эта статья является обзорной, а не подробным изложением, т.к. пользователи, которые решаться на создание подобных «монстров» должны хорошо разбираться в Выпадающих списках (Dropdown List using Data Validation), создании имен (Names), динамических диапазонах (D ynamic Named Ranges ), формулах массива (Array Formula), понимать, что такое Связанный список (Dependent Drop-Down List) и др.

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

В этой таблице мы не будем оставлять пустых ячеек, будем повторять значения в столбцах более высокого уровня (например, слово Европа (уровень1) повторяется столько раз, сколько всего домов (уровень6)).

Выбирать значения будем как обычно с помощью Выпадающих списков .

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

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

Поясним картинку. Т.к. на материке Америке (Уровень 1) нет страны Германии (Уровень 2), то это несоответствие подсвечивается Условным форматированием . Это несоответствие появилось вследствие того, что пользователь перевыбрал значение в Уровне1 с Европа на Америка , а значение на следующем уровне, естественно, автоматически не поменялось. Это ограничение обходится в статье Связанный список в MS EXCEL на основе элемента управления формы .

Для функционирования всего этого используется несколько однотипных имен.

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

Существенным недостатком этого подхода является, то, что в отличие от Многоуровневого списка , где заполнялась целая таблица (по строкам),

здесь заполняется только одна (!) группа связанного списка.

Примечание . Пример будет работать начиная с версии MS EXCEL 2007, т.к. функция ЕСЛИОШИБКА() будет работать начиная с этой версии, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() .

Многоуровневый связанный список в EXCEL

Потребность в создании иерархических данных появляется при решении следующих задач:

Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
Город – Улица – Номер дома . При заполнении адреса проживания из списка городов нужно выбирать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

В этой статье рассмотрен Многоуровневый связанный список. Двухуровневый связанный список или просто Связанный список рассмотрен в статьях Связанный список и Расширяемый Связанный список. Материал статьи один из самых сложных на сайте Excel2.ru , поэтому необходимо для начала ознакомиться с вышеуказанными статьями. Многоуровневый связанный список будем реализовывать с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список .Создание Многоуровневого связанного списка рассмотрим на конкретном примере.

Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью Многоуровневый связанный список типа Предок-Родитель .

Постановка задачи

Имеется перечень Регионов . Для каждого Региона имеется свой перечень Стран . Для каждой Страны имеется свой перечень Городов .

Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего (раскрывающегося) списка нужную ему Страну из этого Региона . В другой соседней ячейке пользователь должен иметь возможность выбрать нужный ему Город из этой Страны (см. файл примера ).

В окончательном виде трехуровневый связанный список должен работать так:

Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка .

Затем выберем Страну «США» из Региона «Америка».

Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».

И, наконец, выберем Город «Атланта» из Страны «США».

Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».

Решение

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

Список Регионов и перечни Стран разместим на листе Страны .

Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран ( В1: L 1 ).

Это требование обеспечивается формулой (см. статьи о Транспонировании ). =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1: L 1 .

Список Стран и перечни Городов разместим на листе Города .

Откуда же возьмется перечень стран на листе Города ? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города . Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков . Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен ) . Именованный диапазон Диап_Стран образуем формулой:

Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран

Окончательная формула в столбце А на листе Города выглядит так:

сформирует необходимый нам список Стран .

Теперь создадим Динамический диапазон для формирования Выпадающего списка содержащего названия Регионов . Для этого необходимо:

нажать кнопку меню « Присвоить имя » ( Формулы/ Определенные имена/ Присвоить имя );
в поле Имя ввести Регионы ;
в поле Диапазон ввести формулу

Формула подсчитывает количество элементов в столбце А на листе Страны (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов . Пропуски в столбце А не допускаются.

Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:

Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:

Т.к. в формуле использована относительная адресация , то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица .

Аналогичным образом создадим именованную формулу для определения позиции, выбранной пользователем страны, в диапазоне Список_Стран =ПОИСКПОЗ(таблица!B5;Список_Стран;0) . Перед созданием формулы нужно сделать активной ячейку С5 на листе Таблица .

Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.

Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны , содержащего страны выбранного региона:

Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20

Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города , содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)

Создадим две последние именованные формулы Страны и Города : =СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион)) =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

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

Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22 , в поле Источник вводим: =Города )

На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города .

Для добавления новых Регионов и их Стран достаточно ввести новый Регион в столбец A (лист Страны ), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона .Для добавления новых Городов, на листе Города в строке 1 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны ). Под этим заголовком введите название города.

СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) — в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

Эксель: создание многоуровневого связанного списка

uchet-jkh.ru

Excel — один из наиболее популярных и мощных инструментов для работы с данными. Он позволяет создавать сложные таблицы, проводить анализ данных и создавать связанные списки. Связанный список — это структура данных, состоящая из элементов, каждый из которых может быть связан с произвольным числом других элементов. В Экселе связанный список может использоваться для упорядочивания данных, анализа и создания иерархических структур.

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

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

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

Определение и преимущества многоуровневого связанного списка

Многоуровневый связанный список представляет собой удобный и эффективный способ организации и хранения сложных данных, таких как директории файловой системы, деревья каталогов и т.д. Каждый узел списка может содержать данные и ссылки на другие уровни списка, что позволяет представлять иерархические отношения между данными.

Преимущества многоуровневого связанного списка:

Иерархическая организация данных: Многоуровневый связанный список позволяет представлять иерархические отношения между данными, что упрощает их структурирование и обработку.
Гибкость: Многоуровневый связанный список позволяет легко добавлять и удалять элементы, а также изменять их порядок, что делает эту структуру данных очень гибкой и удобной для работы с изменяющимися данными.
Эффективность: Благодаря использованию ссылок между узлами, многоуровневый связанный список обеспечивает быстрый доступ к элементам списка и позволяет эффективно использовать память.

Создание многоуровневых связанных списков в Excel

Для создания многоуровневых связанных списков в Excel вы можете использовать функцию «Выпадающий список» в комбинированных ячейках. Это позволяет пользователям выбирать значение из заранее заданных вариантов.

1. Создайте список значений, которые вы хотите использовать в качестве выбора. Это могут быть названия проектов, категории задач или любые другие данные.

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

3. Перейдите во вкладку «Данные» на ленте инструментов Excel и выберите «Проверка данных» в разделе «Инструменты данных».

4. В открывшемся окне «Проверка данных» выберите вкладку «Ограничения» и в разделе «Допустимые значения» выберите «Список».

5. В поле «Источник» введите диапазон ячеек, содержащих ваш список значений. Например, если ваш список находится в ячейках A1:A5, введите «A1:A5». Не забудьте добавить знак «$» перед номерами столбцов и/или строк, чтобы закрепить абсолютные ссылки на эти ячейки.

6. Нажмите на кнопку «ОК», чтобы завершить создание многоуровневого связанного списка.

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

Вы также можете создавать несколько уровней связанных списков, которые позволяют пользователю выбирать значения из разных списков. Для этого создайте дополнительные списки и примените ту же самую процедуру, указывая различные диапазоны ячеек в поле «Источник».

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

Примеры многоуровневых связанных списков

Представленные ниже примеры демонстрируют создание и использование многоуровневых связанных списков в программе Excel.

Пример 1:	В этом примере показана структура связанного списка, состоящего из трех уровней. Каждый уровень списка содержит несколько элементов, которые могут быть связаны с элементами других уровней. Такая иерархическая структура позволяет организовать и систематизировать информацию более удобным образом.
Пример 2:	В этом примере показана реализация древовидной структуры связанного списка. Корневой элемент списка имеет несколько потомков на первом уровне. Каждый потомок также может иметь своих потомков на следующих уровнях. Такая структура позволяет представлять иерархию данных и выполнять операции над ней, такие как добавление, удаление и поиск элементов.
Пример 3:	В этом примере показана реализация списка с множественными ссылками. Каждый элемент списка может быть связан с несколькими другими элементами, что обеспечивает большую гибкость и возможности поиска по различным критериям. Такая структура может использоваться, например, для представления отношений между объектами в базе данных.

Каждый из этих примеров демонстрирует возможности использования многоуровневых связанных списков в Excel для организации и структурирования данных разного типа. В зависимости от конкретных потребностей и задач, такие списки могут быть настроены и адаптированы под конкретные требования пользователя.

Использование формул и функций для работы с многоуровневыми связанными списками

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

Вот несколько полезных формул и функций:

СОДЕРЖИМОЕ() — функция, которая возвращает значение ячейки в указанном диапазоне. В многоуровневых связанных списках она может использоваться для получения значений из разных уровней.
СОВПАДЕНИЕ() — функция, которая ищет значение в диапазоне и возвращает его позицию. Она может быть использована для нахождения позиции определенного элемента в списке.
ПОИСКПОЗ() — функция, которая ищет значение в диапазоне и возвращает позицию первого символа этого значения. Эта функция может быть полезна для определения позиции определенного элемента в списке.
СМЕЩЕНИЕ() — функция, которая возвращает ссылку на ячейку, смещенную относительно указанной ячейки. Она может использоваться для перехода к ячейке в другом уровне связанного списка.
УРОВЕНЬ() — функция, которая возвращает уровень ссылающейся ячейки. В многоуровневых связанных списках она может использоваться для определения уровня элемента списка.

Эти формулы и функции позволяют управлять и анализировать многоуровневые связанные списки в Excel, облегчая работу с данными и повышая эффективность.

Создание иерархии

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2007 Word 2007 Outlook 2007 PowerPoint 2007 Еще. Меньше

Если вы хотите проиллюстрировать иерархические отношения, которые прогрессируют по вертикали или по горизонтали, можно создать графический элемент SmartArt, использующий макет иерархии, например Иерархия с меткой. Иерархия представляет собой ряд упорядоченных групп людей или элементов в системе. Используя графический элемент SmartArt в Excel, Outlook, PowerPoint или Word, вы можете создать иерархию и включить ее в электронную почту, сообщение электронной почты, презентацию или документ.

Важно: Если вы хотите создать организациическую диаграмму,создайте графический элемент SmartArt с помощью макета Организацивая диаграмма.

Примечание: Снимки экрана, сделанные в этой статье, Office 2007 г. Если у вас другая версия, представление может немного отличаться, но если не указано иное, функции будут одинаковыми.

Создание иерархии

На вкладке Вставка в группе Иллюстрации нажмите кнопку SmartArt.
В коллекции Выбор рисунка SmartArt щелкните Иерархияи дважды щелкните макет иерархии (например, Горизонтальная иерархия).
Для ввода текста выполните одно из следующих действий.
В области текста щелкните элемент [Текст] и введите содержимое.
Скопируйте текст из другого места или программы, в области текста щелкните элемент [Текст], а затем вставьте скопированное содержимое.

Примечание: Если область текста не отображается, щелкните элемент управления.

Щелкните поле в графическом элементе SmartArt и введите свой текст.

Примечание: (ПРИМЕЧАНИЕ.) Для достижения наилучших результатов используйте этот вариант после добавления всех необходимых полей.

Добавление и удаление полей в иерархии

Добавление поля

Щелкните графический элемент SmartArt, в который нужно добавить поле.
Щелкните существующее поле, ближайшее к месту вставки нового поля.
В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Создать рисунок щелкните стрелку под командой Добавить фигуру. Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.
Выполните одно из указанных ниже действий.
- Чтобы вставить поле на том же уровне, что и выбранное поле, но после него, выберите команду Добавить фигуру после.
- Чтобы вставить поле на том же уровне, что и выбранное поле, но перед ним, выберите команду Добавить фигуру перед.
- Чтобы вставить поле на один уровень выше выбранного поля, выберите команду Добавить фигуру над.
  Новое поле займет место выбранного поля, а выбранное поле и все поля непосредственно под ним будут понижены на один уровень.
- Чтобы вставить поле на один уровень ниже выбранного поля, выберите команду Добавить фигуру под. Новое поле будет добавлено после другого на том же уровне.

Удаление поля

Чтобы удалить поле, щелкните его границу и нажмите клавишу DELETE.

Если вам нужно добавить поле в иерархию, поэкспериментируйте с ним до, после, сверху или под выбранным полем, чтобы получить нужное расположение.
Несмотря на то что в макетах иерархии, таких как Горизонтальная иерархия, нельзя автоматически соединить линией два поля верхнего уровня,вы можете сымитировать это, добавив поле в графический элемент SmartArt и нарисуя линию для соединения полей.
Чтобы добавить поле из области текста:
1. Поместите курсор в начало текста, куда вы хотите добавить фигуру.
2. Введите нужный текст в новой фигуре и нажмите клавишу ВВОД. Чтобы добавить отступ для фигуры, нажмите клавишу TAB, а чтобы сместить ее влево — клавиши SHIFT+TAB.

Перемещение полей в иерархии

Чтобы переместить поле, щелкните его и перетащите на новое место.
Чтобы фигура перемещалась с очень маленьким шагом, удерживайте нажатой клавишу CTRL и нажимайте клавиши со стрелками.

Изменение макета иерархии

Щелкните правой кнопкой мыши иерархию, которую вы хотите изменить, и выберите изменить макет.
Щелкните Иерархияи сделайте одно из следующих:
- Чтобы показать иерархические отношения, которые выровна сверху вниз и сгруппировать по иерархии, щелкните Иерархия с меткой.
- Чтобы показать группы данных, встроенные сверху вниз, и иерархии внутри каждой группы, щелкните Иерархия таблиц.
- Чтобы показать иерархические отношения в группах, щелкните Иерархический список.
- Чтобы показать иерархические отношения, которые выровна по горизонтали, выберите горизонтальную иерархию.
- Чтобы показать иерархические отношения, которые выровна по горизонтали и помечены иерархией, щелкните Горизонтальная иерархия с подписи.

Примечание: Чтобы изменить макет SmartArt, можно также выбрать нужный параметр в разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Макеты. При выборе варианта макета можно предварительно просмотреть, как будет выглядеть графический элемент SmartArt.

Изменение цветов иерархии

Чтобы быстро оформление графического элементов SmartArt выглядело и выглядело как дизайнер, вы можете изменить цвета или применить стиль SmartArt к своей иерархии. Вы также можете добавить эффекты, такие как свечение, сглаживание или объемные эффекты.

К полям в графических элементах SmartArt можно применять цветовые вариации из цвета темы.

Щелкните графический элемент SmartArt, цвет которого нужно изменить.
В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Стили SmartArt нажмите кнопку Изменить цвета. Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.
Выберите нужную комбинацию цветов.

Совет: (ПРИМЕЧАНИЕ.) При наведении указателя мыши на эскиз можно просмотреть, как изменяются цвета в графическом элементе SmartArt.

Изменение цвета или стиля линии

В графическом элементе SmartArt щелкните правой кнопкой мыши границу линии или фигуры, которые вы хотите изменить, и выберите пункт Формат фигуры.
Чтобы изменить цвет границы, нажмите кнопку Цвет линии ,выберите цвет , а затем выберите нужный цвет.
Чтобы изменить тип границы фигуры, щелкните Тип линии и задайте нужные параметры.

Изменение цвета фона окна в иерархии

Щелкните правой кнопкой мыши границу фигуры и выберите команду Формат фигуры.
Щелкните область Заливка и выберите вариант Сплошная заливка.
Нажмите кнопку Цвет и выберите нужный цвет.
Чтобы указать степень прозрачности фонового цвета, переместите ползунок Прозрачность или введите число в поле рядом с ним. Значение прозрачности можно изменять от 0 (полная непрозрачность, значение по умолчанию) до 100 % (полная прозрачность).

Применение стиля SmartArt к иерархии

Стиль SmartArt — это сочетание различных эффектов, например стилей линий, рамок или трехмерных эффектов, которые можно применить к полям графического элемента SmartArt для придания им профессионального, неповторимого вида.

Щелкните графический элемент SmartArt, стиль SmartArt которого нужно изменить.
В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Стили SmartArt выберите стиль. Чтобы отобразить другие стили SmartArt, нажмите кнопку Дополнительно . Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.

(ПРИМЕЧАНИЕ.) При наведении указателя мыши на эскиз становится видно, как изменяется стиль SmartArt в рисунке SmartArt.
Вы также можете настроить графический элемент SmartArt, перемещая поля,меняя их размер,добавляя заливку или эффект и добавляя рисунок.

Анимировать иерархию

Если вы используете PowerPoint, вы можете анимировать иерархию, чтобы акцентировать внимание на каждом поле, каждой ветви или каждом уровне иерархии.

Щелкните иерархию графического элементов SmartArt, которую нужно анимировать.
На вкладке Анимация в группе Анимация нажмите кнопку Анимация ивыберите по ветви по одному.

Примечание: При копировании иерархии с примененной к ней анимацией на другой слайд также копируется анимация.

Электронная почта