Как сделать многоуровневый список в эксель

Что такое маркированные и многоуровневые списки

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

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

Задачи

• Определение нового формата маркера
• Определение нового формата номера
• Определение нового многоуровневого списка
• Определение нового стиля списка
• Отключение автоматической нумерации списков
• Дополнительные сведения

Определение нового формата маркера

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

Изменение маркера на символ

1. Выделите текст или маркированный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный списоки выберите пункт Определить новый маркер.
3. Нажмите кнопку Символ и щелкните нужный символ.
4. Нажмите кнопку ОК.

Изменение маркера на рисунок

1. Выделите текст или маркированный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный списоки выберите пункт Определить новый маркер.
3. Щелкните Рисунок и выберите изображение из файла или с помощью поиска изображений Bing.
4. Нажмите кнопку Вставить.
5. Просмотрите, как будет выглядеть рисунок в качестве маркера, и нажмите кнопку ОК, чтобы добавить его, либо замените рисунок, повторив шаги 3–5.

Изменение шрифта маркера

1. Выделите текст или маркированный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный списоки выберите пункт Определить новый маркер.
3. Нажмите кнопку Шрифт и измените свойства шрифта, который будет использоваться для маркера. Вы можете выбрать параметры на вкладках Шрифт и Дополнительно:

Вкладка Шрифт

Изменение шрифта, начертания, размера, цвета, стиля и цвета подчеркивания, а также эффектов для маркера.

Вкладка Дополнительно

Настройка межзнаковых интервалов и возможностей OpenType для шрифта маркера.

Изменение выравнивания маркера

1. Выделите текст или маркированный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный списоки выберите пункт Определить новый маркер.
3. В группе «Выравнивание» выберите Слева, По центру или Справа.
4. Нажмите кнопку ОК.

Определение нового формата номера

1. Выделите текст или нумерованный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Нумерованный списоки выберите пункт Определить новый формат номера.
3. Чтобы изменить стиль, щелкните стрелку вниз рядом с полем Нумерация и выберите числа, буквы или другой формат последовательности.
4. Чтобы изменить размер, начертание и цвет шрифта, щелкните Шрифт и задайте параметры на вкладках Шрифт и Дополнительно:

Вкладка	Описание
Вкладка Шрифт	Изменение шрифта, начертания, размера, цвета, стиля и цвета подчеркивания, а также эффектов для маркера.
Вкладка Дополнительно	Настройка межзнаковых интервалов и возможностей OpenType для шрифта маркера.

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

1. Выделите текст или нумерованный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Многоуровневый списоки выберите пункт Определение нового многоуровневого списка.
3. Разверните диалоговое окно «Многоуровневый список», щелкнув Дополнительно в левом нижнем углу.
4. Выберите уровень списка, который нужно изменить, щелкнув его в списке. По умолчанию выбрано значение 1.
5. Укажите, к чему вы хотите применить изменения, щелкнув ко всему списку, до конца документа или к текущему абзацу.
6. Чтобы использовать существующий стиль в Word для каждого уровня в списке, выберите стиль в разделе «Связать уровень со стилем».
7. Выберите номер для отображения в коллекции. По умолчанию отображается Уровень 1.
8. Присвойте многоуровневому списку имя в разделе «Имя списка поля ListNum». Оно будет отображаться для всех полей ListNum.
9. Чтобы добавить к нумерованному списку дефис, круглую скобку или другой символ, введите его в поле Формат номера.
10. Чтобы изменить размер, начертание и цвет шрифта, щелкните Шрифт и задайте параметры на вкладках Шрифт и Дополнительно:

Вкладка	Описание
Вкладка Шрифт	Изменение шрифта, начертания, размера, цвета, стиля и цвета подчеркивания, а также эффектов для маркера.
Вкладка Дополнительно	Настройка межзнаковых интервалов и возможностей OpenType для шрифта маркера.

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

1. Выделите текст или нумерованный список, который вы хотите изменить.
2. На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Многоуровневый списоки выберите пункт Определение нового стиля списка.
3. Введите имя для нового стиля списка.
4. Выберите начальный номер для списка. Значение по умолчанию — 1.
5. Выберите уровень в списке, к которому нужно применить форматирование.
6. Укажите начертание, размер и цвет шрифта для стиля списка.
7. Щелкните стрелку вниз, чтобы изменить стиль нумерованного или маркированного списка.
8. Выберите символ для списка.
9. Выберите изображение для списка.
10. Сместите отступ влево или вправо.
11. Укажите, нужно ли применить эти изменения Только в этом документе или же В новых документах, использующих этот шаблон.
12. Чтобы настроить дополнительные параметры форматирования, щелкните Формат и выберите Шрифт, Нумерация, Текстовые эффекты или другие настройки.
13. Нажмите кнопку ОК.

Отключение автоматической нумерации списков

1. Следуйте инструкциям для используемой версии Word Word 2016, Word 2013 или Word 2010
   • Выберите Файл, а затем в левой области выберите Параметры.

• Нажмите кнопку Microsoft Office и выберите Параметры Word.

Многоуровневый (3+) связанный список (типа предок-родитель) в EXCEL

Для сложных иерархических структур с тремя и более уровнями создадим Многоуровневый связанный список типа Предок-Родитель. Теперь структуры типа: Регион-Страна-Город-Улица можно создавать в MS EXCEL.

В статье Многоуровневый связанный список в MS EXCEL мы уже создавали многоуровневый список. Но, у того списка было 2 недостатка:

• слишком сложные формулы;
• максимальное количество уровней — 3.

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

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

Решение

Выпадающие списки будем формировать с помощью одной большой Именованной формулы :

Эту формулу можно посмотреть через Диспетчер имен (имя формулы — Выбор ), но так как формула использует относительную адресацию в ссылках на ячейки Таблица!B$7 и Таблица!A8, то для ее просмотра нужно предварительно выделить ячейку В8 на листе Таблица. О том как работает эта формула см. в разделе статьи ниже.

ПРИМЕЧАНИЕ : Функция ЕСЛИ() нужна для формирования списка самого верхнего уровня иерархии — у него нет Родителя.

Для ввода формулы выделите ячейку B8 , вызовите Диспетчер имен , и введите формулу:

Опять выделите ячейку B8 , сформируйте выпадающий список на основе Проверки данных . В поле Тип данных укажите Список, в поле Источник введите формулу =Выбор .

С помощью Маркера заполнения скопируйте ячейку вправо и вниз (или вверх и вправо): выпадающие списки всех уровней будут сформированы автоматически.

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

Для наглядности различные уровни выделены Условным форматированием .

Заполнение таблицы на листе Списки нужно производить следующим образом:

• Столбец Номер уровня содержит числовые коды уровней. Самый верхний уровень (0) содержит название Регионов. Можно создать любое количество уровней;
• Столбец Родитель содержит названия из столбца Потомок более верхнего уровня;
• Столбец Потомок связывает значений из 2-х соседних уровней. Нужно иметь ввиду, что если мы начали заполнять, например, все страны Америки, то нужно полностью закончить этот список, затем переходить к следующему региону. Выполнение этого требования необходимо для правильной работы вышеуказанной формулы. Выпадающие списки формируются именно из этого столбца.

Как работает эта формула?

Рассмотрим как формируется выпадающий список в ячейке B8 на листе Таблица (заполняем Регионы).

• Выражение ЕСЛИ(Таблица!B$7=Списки!$F$5 ; проверяет заполняем ли мы столбец Регионы (самый верхний уровень с номером 0) или нет. В данном случае выражение вернет значение ИСТИНА;
• Так как предыдущее выражение =ИСТИНА, то работает первое условие функции ЕСЛИ() , т.е. выражение СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0))
• функция СМЕЩ() выбирает из столбца D (Потомки) все значения, у которых уровень равен 0. Из этих значений формируется выпадающий список.

Рассмотрим как формируется выпадающий список в ячейке С8 на листе Таблица (заполняем Страны).

В этом случае формула будет несколько другая, т.к. в ней сработают относительные ссылки:

• Выражение ЕСЛИ(Таблица!С$7=Списки!$F$5 ; в данном случае выражение вернет значение ЛОЖЬ;
• Так как предыдущее выражение =ЛОЖЬ, то работает второе условие функции ЕСЛИ() , т.е. выражение СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8))
• функция СМЕЩ() выбирает из столбца D (Потомки) все значения, у которых Родитель = Америка.
• Для этого выражение ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0) возвращает номер позиции первой ячейки в столбце Родитель, содержащей значение Америка. Это позиция номер 4. Именно с этой позиции, но из другого столбца, будет формироваться наш выпадающий список ;
• Теперь для выпадающего списка осталось указать его длину. Для этого используем выражение СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!В8) .

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

Теперь представим ситуацию, что после заполнения таблицы с помощью выпадающих списков , было изменено значение ячейки С10 . Например, вместо США выберем Мексика. Естественно, значение в ячейке D10 не изменится (в ней останется один из городов США). Чтобы подсказать пользователю об ошибке, используем Условное форматирование .

Формула в правиле Условного форматирования ищет в таблице на листе Списки пару Родитель-Потомок. Если такая пара не найдена, то ячейка выделяется красной заливкой.

Как сделать многоуровневый список в эксель

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Многоуровневый связанный список в 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).