Как сделать иерархию в excel

Обновлено: 08.07.2024

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

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

Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 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).

Как сделать дерево иерархии в Excel — Вокруг-Дом — 2021

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

Шаг 1

Шаг 2

Шаг 3

Шаг 4

Шаг 5

Шаг 6

Шаг 7

Шаг 8

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

Шаг 9

Как сделать телефонное дерево с помощью Microsoft Excel

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

Как сделать дерево решений в Excel

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

Как создать дерево решений в Excel 2007

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

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

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

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

На вкладке Вставка в группе Иллюстрации нажмите кнопку SmartArt.

В коллекции Выбор рисунка SmartArt щелкните Иерархияи дважды щелкните макет иерархии (например, Горизонтальная иерархия).

Для ввода текста выполните одно из следующих действий.

В области текста щелкните элемент [Текст] и введите содержимое.

Скопируйте текст из другого места или программы, в области текста щелкните элемент [Текст], а затем вставьте скопированное содержимое.

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

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

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

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

Щелкните графический элемент SmartArt, в который нужно добавить поле.

Щелкните существующее поле, ближайшее к месту вставки нового поля.

В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Создать рисунок щелкните стрелку под командой Добавить фигуру.

Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.

Выполните одно из указанных ниже действий.

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

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

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

Чтобы вставить поле на один уровень ниже выбранного поля, выберите команду Добавить фигуру под.

Новое поле будет добавлено после другого на том же уровне.

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

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

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

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

Чтобы добавить поле из области текста:

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

Введите нужный текст в новой фигуре и нажмите клавишу ВВОД. Чтобы добавить отступ для фигуры, нажмите клавишу 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, перемещая поля,меняя их размер,добавляя заливку или эффект и добавляя рисунок.

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

Щелкните иерархию графического элементов SmartArt, которую нужно анимировать.

На вкладке Анимация в группе Анимация нажмите кнопку Анимация ивыберите по ветви по одному.

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

Как сделать иерархическую диаграмму?

Откройте новый документ PowerPoint.
Перейдите на вкладку Вставка и щелкните SmartArt.
Перейдите к группе Иерархия и выберите нужный шаблон организационной диаграммы.
Щелкните фигуры, чтобы добавить текст.
При необходимости добавьте больше фигур (или людей).

Как создать иерархию в Excel?

Каковы 4 типа организационных структур?

Четыре типа организационных структур: функциональные, дивизиональные, плоские и матричные структуры.

Можете ли вы создать организационную диаграмму в Excel?

Как создать иерархию в Word?

Какие отношения требуются для создания иерархии?

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

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

Теперь, прежде чем я перейду к созданию и использованию настраиваемых списков, позвольте мне сначала объяснить, что в этом такого замечательного. Предположим, вам нужно ввести числа и названия месяцев с января по декабрь в столбец. Как бы ты это сделал? И нет, делать это вручную — не вариант.

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

Названия месяцев довольно общие, поэтому они доступны в Excel по умолчанию.

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

Вы тоже можете это сделать … С помощью настраиваемых списков в Excel

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

Как создавать собственные списки в Excel

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

Ниже приведены настраиваемые списки, которые уже встроены в Excel. Как видите, это в основном названия дней и месяцев, поскольку они фиксированы и не изменятся.

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

Ниже приведены шаги по созданию собственного настраиваемого списка в Excel:

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

Хотя здорово иметь возможность быстро получить эти пользовательские имена lits в Excel простым перетаскиванием, есть кое-что еще более интересное, что вы можете сделать с настраиваемыми списками (об этом следующий раздел).

Создайте свои собственные критерии сортировки с помощью настраиваемых списков

Вы не можете этого сделать! Если вы отсортируете в алфавитном порядке, он изменит алфавитный порядок (он даст вам высокий, низкий и средний, а не высокий, средний и низкий). Вот где действительно сияют индивидуальные списки.

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

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

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

Обратите внимание, что вам не обязательно сначала создавать настраиваемый список, чтобы использовать его при сортировке. Вы можете использовать описанные выше шаги, а на шаге 4, когда откроется диалоговое окно, вы можете создать список прямо в этом диалоговом окне.

Некоторые примеры использования настраиваемых списков

Ниже приведены некоторые из случаев, когда создание и использование настраиваемых списков может сэкономить ваше время:

Если у вас есть список, который вам нужно ввести вручную (или скопировать и вставить из другого источника), вы можете создать собственный список и использовать его вместо этого. Например, это могут быть названия отделов в вашей организации, названия продуктов или регионов / стран.
Если вы учитель, вы можете составить список имен своих учеников. Таким образом, когда вы будете ставить им оценку в следующий раз, вам не нужно будет беспокоиться о том, чтобы ввести имена учеников вручную или скопировать их с другого листа. Это также снижает вероятность ошибок.
Когда вам нужно отсортировать данные на основе критериев, которые не встроены в Excel. Как описано в предыдущем разделе, вы можете использовать свои собственные критерии сортировки, создав собственный список в Excel.

Это все, что вам нужно знать о создании настраиваемых списков в Excel .

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

Настраиваем параметры функции
Группируем данные по строкам
Группируем столбцы
Создаем многоуровневую группировку
Разгруппировываем данные
Заключение

Настраиваем параметры функции

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

Группируем данные по строкам

Для начала давайте рассмотрим, как можно сгруппировать строки:

Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию. Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK. Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.

Группируем столбцы

Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:

Создаем многоуровневую группировку

Возможности программы позволяют выполнять как одноуровневые, так и многоуровневые группировки. Вот как это делается:

Разгруппировываем данные

Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:

Заключение

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

Читайте также: