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

MulTEx » 9 Июль 2016       Дмитрий       7018 просмотров

Вызов команды:
MulTEx -группа Специальные —Работа со сводными —Подготовить таблицу — строки

Понять принцип работы и основные параметры настроек поможет видеоинструкция к команде:

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

Ну и конечно же, это можно сделать при помощи команды MulTEx Подготовить таблицу — строки.
Самая главная проблема в отчете выше — это так называемая группировка строк при формировании отчета в 1С.

На примере выгрузки выше к группировке строк относятся: Контрагент, Номенклатура и Регистратор(Документ).

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

Например, на картинке выше к Контрагенту относятся Автокомпонент-ТЗК и Азов-ТЗК. Это верхний уровень группировки. Следующий уровень — Номенклатура: Р86023 Pehapol разбавитель и PLP02004 разбавитель.

И последний, третий уровень — Регистратор(Документ), к нему относятся все светло-желтые строки — «Реализация товаров и услуг…».
Вторая проблема — данные по каждому товару разбиты на месяцы и в каждом месяце три отдельных категории значений: Количество (в ед. хранения), Цена продажи и Цена закупки. И при этом месяцы расположены не в строках, а в столбцах. Что так же затрудняет работу с таблицей формулами, не говоря о сводных таблицах.

Примечание: Заголовки в обеих таблицах выделены цветами специально, чтобы показать соответствие данных в обеих таблицах и как они распределяются после преобразования. В реальных отчетах такой расцветки нет.

И именно подобные преобразования и способна сделать команда MulTEx Подготовить таблицу — строки.

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

Чтобы более точно понять принцип работы всех настроек и команды в целом лучше применить некоторые определения для данных:

• Свойства строки — данные, которые характеризуют строку (Наименование, Артикул, Код, Цвет, Номенклатура, Документ и т.п.)
• Значения строки — как правило числовые значения(Количество, Цена, Сумма и т.п.), относящиеся к конкретному товару, документу и т.п. Т.е. непосредственно количественные данные нижнего уровня таблицы (строки с максимальным количеством отступов)

Настройки
После вызова команды появится окно настроек:

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

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

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

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

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

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

Чтобы добавить соответствие необходимо:

• либо дважды щелкнуть мышью по любой пустой строке поля
• либо нажать сочетание клавиш Ctrl+Plus(кнопка + на клавиатуре)
• либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Добавить

Чтобы изменить существующее соответствие необходимо:

• либо дважды щелкнуть мышью по строке поля с нужным соответствием(при этом в зависимости от того над каким пунктом был щелчок активируется либо Наименование, либо Кол-во отступов)
• либо нажать сочетание клавиш Ctrl+Right(стрелка вправо на клавиатуре)
• либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Изменить

Чтобы удалить существующее соответствие необходимо:

• либо нажать сочетание клавиш Ctrl+Delete
• либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Удалить

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

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

Заголовки переменных данных

По сути этот блок отвечает за числовые данные, которые необходимо отразить в результирующей таблице.

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

Заголовки расположены в ячейках(C8:E8) — указывается диапазон ячеек одной строки заголовка. Если нет повторяющихся заголовков значений, то данные указанных столбцов будут определяться как Значения строки. Если есть повторяющиеся заголовки значений, то значения ячеек указанных столбцов буду определяться как Свойства строки.

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

— Заголовки повторяются с шагом — указывается целое число больше нуля, определяющее с какой периодичностью повторяются данные. В рассматриваемой таблице данные расположены таким образом, что Количество (в ед.хранения), Цена продажи и Цена закупки повторяются для каждого месяца. Т.е. на один месяц три столбца данных. Значит шаг необходимо выставить равный 3.
— Заголовки в ячейках(F8:AO8) — указывается диапазон ячеек (или одна ячейка), одной строки заголовка, данные из столбцов которых необходимо записать построчно для каждого значения строки. Если нет подзаголовков значений, то данные указанных столбцов будут определяться как Значения строки. Если есть подзаголовки значений, то значения ячеек указанных столбцов буду определяться как Свойства строки.
— Подзаголовки в ячейках(F10:H10) — если указаны, то Значения строки будут взяты из столбцов, указанных в этом поле. Значения из столбцов поля Заголовки в ячейках будут определяться как Свойства строки и записываться в отдельный столбец(соответствующий названию столбца). Также, если подзаголовки указаны и шаг повторения больше 1, то из Заголовков в результирующую таблицу будет записываться одно значение на весь повторяющийся блок подзаголовков(в примере используется шаг — 3):


Определять последний столбец по заголовкам — если установлен, то для заголовков переменных данных достаточно будет указать только первую ячейку заголовка — остальные данные будут определены на основании строки заголовка. При этом если данные в различных заголовках и подзаголовках заканчиваются в различных столбцах — в качестве последнего столбца данных будет взят самый дальний столбец, т.е. наибольший из всех. Если данный пункт отключен, то данные из таблицы будут браться исключительно из указанных в полях столбцов.
Например, если в таблице выше подзаголовки записаны в столбцах F10:AL10, то в поле Подзаголовки в ячейках достаточно будет указать ячейку F10 — последняя ячейка(AL10) будет определена автоматически. Если флажок снят — то будут обработаны только те столбцы точек, которые указаны в поле Подзаголовки в ячейках.
Для чего это нужно: иногда в таблицах в конце присутствует блок итогов и не всегда эти итоги нужны в таблице для анализа. Если указать определение автоматически — то итоги так же попадут в результирующий лист и будут только мешать.
Записывать каждый подзаголовок в новую строку — если установлен, то подзаголовки будут переносится не блоком в одну строку, а каждая ячейках подзаголовка будет записана в новую строку. При этом все свойства строки будут продублированы для значения подзаголовка. См.пример применения >>
Переносить в новую таблицу только данные с непустыми ячейками — если установлен, то в результирующую таблицу будут перенесены только те строки ячеек полей, определенных как Значения строки, по которым в ячейке есть хоть какое-то значение.
Для чего это нужно: на примере таблицы выше: в ячейках от F13 до H19 нет данных. Если флажок установлен — то в результирующем отчете будет как минимум на 7 ненужных строк меньше. В большинстве случаев этот флажок лучше ставить, но бывают ситуации, когда даже пустые строки и ячейки важны для анализа(например, определить, по каким позициям операции отсутствуют).
Считать последнюю строку группы максимальным уровнем — применяется в случаях, когда структура исходной таблицы построена таким образом, что в каждой группе данных не всегда есть максимальный доступный уровень отступа:

На картинке видно, что для группы «Материалы и запчасти на Контр по ЛЗК» максимальный уровень отступов 16, для «Иные запчасти» — 14, а для «ОС до 40 тыс.руб» — 12. Если галочка Считать последнюю строку группы максимальным уровнем будет снята, то данные будут перенесены некорректно, т.к. даже если указать максимальный уровень 16, то данные с меньшим количеством отступов просто не попадут в итоговую таблицу в качестве Значения строки. Если же галочка Считать последнюю строку группы максимальным уровнем будет установлена — то даже если уровень будет меньше 16 программа определит, что это последний уровень в группе и перенесет именно эти строки в итоговый отчет как Значения строки. Шаблоны настроек
Создание нового шаблона
Создавать шаблоны настроек просто — достаточно заполнить левую часть формы для обработки какого-либо отчета, после чего перейти в правую часть, выбрать создать новый шаблон, вписать имя шаблона в соответствующее поле и нажать Сохранить текущие настройки в шаблон. Лучше сразу давать понятные имена шаблонам(выгрузка_1С_продажи, 1С8_остатки и т.п.). Имя шаблона не должно содержать пробелов, знаков препинания(кроме нижнего подчеркивания) и не должно начинаться с цифры. Если имя шаблона будет задано неверно — надстройка выдаст предупреждение и необходимо будет изменить имя в соответствии с требованиями.
По сути на этом создание шаблона заканчивается. Он успешно запоминается надстройкой и теперь будет доступен всегда.
Применение шаблона
Для применения шаблона необходимо выбрать нужный шаблон в списке шаблонов (правая часть формы) и нажать Применить настройки выбранного шаблона. Поля настроек (левая часть формы) будут заполнены в соответствии с шаблоном. Так же заполнить поля из шаблона можно двойным кликом левой кнопкой мыши на названии шаблона.
Изменение шаблона
Для изменения шаблона необходимо выбрать нужный шаблон в списке шаблонов (правая часть формы), при необходимости заполнить поля настроек (двойной клик мыши или Применить настройки выбранного шаблона). После этого необходимо внести требуемые настройки в левой части формы. Выбрать изменить выбранный шаблон и нажать Сохранить текущие настройки в шаблон. Настройки выбранного шаблона будут изменены.
Удаление шаблона
Для удаления шаблона необходимо выбрать шаблон из списка и нажать кнопку Удалить выбранный. Следует помнить, что данная операция необратима и шаблон будет удален без возможности восстановления.
Удаление всех шаблонов
Для удаления всех шаблонов из списка необходимо нажать кнопку Удалить все шаблоны. Следует помнить, что данная операция необратима и все шаблоны будут удалены без возможности восстановления.
Другие примеры таблиц и настроек

Пример обработки оборотно-сальдовой ведомости:
Для преобразования такой таблицы в плоскую необходимо будет применить такие настройки формы:
В результате получим такую таблицу:

• Пример отчета без повторяющихся заголовков:

  Настройки формы для преобразования такой таблицы в плоскую:

• В результате получим такую таблицу:
• Пример настроек с использование пункта Записывать каждый подзаголовок в новую строку. Наиболее актуально это для таблиц примерно следующего вида:
• Для преобразования в удобную таблицу необходимо применить следующие настройки:
• результатом будет такая таблица:
• Попробовав преобразовать пару различных таблиц можно достаточно быстро понять принцип.
• Так же см.:
  Подготовить таблицу — столбцы
  Формат ячеек как в источнике

Источник: https://www.excel-vba.ru/multex/podgotovit-tablicu-stroki/

Как сгруппировать в Excel

Выберите вкладку « Данные »> « Группа» > « Групповые строки» или просто выберите « Группировать» в зависимости от используемой версии Excel.

Слева от номеров строк появится тонкая линия, указывающая экстент сгруппированных строк.

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

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

Как вручную сгруппировать столбцы в Excel

Чтобы сделать столбцы группы Excel, шаги почти такие же, как для строк.

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

   

Выберите вкладку « Данные »> « Группа» > « Колонки группы» или выберите « Группировать» в зависимости от используемой версии Excel.

Тонкая линия появится над буквами столбца. Эта строка указывает экстент сгруппированных столбцов.

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

Строки были сгруппированы и теперь могут быть свернуты и развернуты по мере необходимости.

Как автоматически создать столбцы и строки в группе Excel

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

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

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

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

1. Выберите все строки, которые будут включены.

   

Выберите вкладку « Данные »> « Группа» > « Строки группы» или выберите « Группировать» в зависимости от используемой версии Excel.

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

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

Как автоматически создать многоуровневую иерархию

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

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

Как развернуть и свернуть группы

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

1. Чтобы свернуть все строки, выберите поле номера 1 в верхней части области слева от номеров строк.

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

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

Также возможно расширить и свернуть отдельные группы. Для этого выберите « Плюс» (+) или « Минус» (-), чтобы отобразить группу, которая свернута или развернута. Таким образом, группы на разных уровнях в иерархии можно просматривать по мере необходимости.

Источник: https://rus.4meahc.com/how-group-excel-64749

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

Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

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

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

В этой статье рассмотрен Многоуровневый связанный список. Двухуровневый связанный список или просто Связанный список рассмотрен в статьях Связанный список и Расширяемый Связанный список. Материал статьи один из самых сложных на сайте Excel2.

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

Создание Многоуровневого связанного списка рассмотрим на конкретном примере.

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

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

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

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

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

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

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

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

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

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

Решение

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

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

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

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

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

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

Откуда же возьмется перечень стран на листе Города? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города. Это чудесное перемещение организуем формулами.

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

Именованный диапазон Диап_Стран образуем формулой:

=СМЕЩ(страны!$B$2;;;90;СЧЁТЕСЛИ(страны!$B$1:$Z$1;»?*»))

• Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран
• =ЕСЛИ(ЕПУСТО(Диап_Стран);»»;—((СТОЛБЕЦ(Диап_Стран)-1)&ВЫБОР(ДЛСТР(СТРОКА(Диап_Стран)-1);»0″;»»)&
• СТРОКА(Диап_Стран)-1))

1. Окончательная формула в столбце А на листе Города выглядит так:
2. =ЕСЛИОШИБКА(ИНДЕКС(Диап_Стран;—ПРАВСИМВ(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1));2);—ЛЕВСИМВ(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1));
3. ДЛСТР(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1)))-2));»»)
4. сформирует необходимый нам список Стран.
5. Теперь создадим Динамический диапазон для формирования Выпадающего списка содержащего названия Регионов. Для этого необходимо:

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

=страны!$A$2:ИНДЕКС(страны!$A:$A;СЧЁТЗ(страны!$A:$A))

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

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

=города!$A$2:ИНДЕКС(города!$A:$A;СЧЁТЗ(города!$A:$A))

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

 =ПОИСКПОЗ(A5;Регионы;0)

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

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

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

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

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

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

• выделяем диапазон B5:B22 налисте Таблица;
• вызываем инструмент Проверка данных,
• устанавливаем тип данных Список,
• в поле Источник вводим: =Страны.

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

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

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

Под появившимся заголовком в строке 1 введите страны нового Региона.

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

СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке).

В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) — в одном столбце.

Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

Источник: https://excel2.ru/articles/mnogourovnevyy-svyazannyy-spisok-v-ms-excel

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

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

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

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

Шаг 1

Запустите Microsoft Excel. Нажмите вкладку «Вставить» в верхней части рабочей области. Нажмите кнопку «SmartArt» на ленте под вкладкой. Когда появится окно «Выберите рисунок SmartArt», нажмите ссылку «Иерархия» в левой части окна.

Шаг 2

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

Шаг 3

Нажмите кнопку «Текстовая панель» в левой части ленты, чтобы открыть текстовую панель. Если вы не видите кнопку «Текстовая панель», щелкните иерархию, чтобы включить ее, и откройте фиолетовую вкладку «Инструменты SmartArt» в верхней части рабочей области.

Шаг 4

Щелкните внутри каждой ссылки «[Текст]» на текстовой панели и введите дескриптор для этого параметра иерархии. Например, в верхнем поле [Текст] введите самого высокого члена иерархии, например «Президент» или «Люди».

Шаг 5

Нажмите на ссылку [Текст] ниже и введите следующий элемент иерархии, например «Вице-президент» или «Домашние животные».

Шаг 6

Измените внешний вид иерархии из стандартного синего Excel, нажав кнопку «Изменить цвета» на ленте. Наведите указатель мыши на отдельные параметры, чтобы увидеть изменение иерархии, и щелкните один из них, чтобы зафиксировать изменение.

Шаг 7

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

Шаг 8

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

Шаг 9

Нажмите на вкладку «Файл». Нажмите «Сохранить как». Введите имя для файла иерархии Excel и нажмите кнопку «Сохранить».

Источник: https://ru.computersm.com/97-how-to-make-a-hierarchy-tree-in-excel-35535