Вложенные списки в excel

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

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

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

Через меню Формулы/ Определенные имена/ Присвоить имя создадим Именованный диапазон Фамилии =Лист1!$A$2:$A$153 .

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

=СИМВОЛ(192+СТРОКА(Z1)-1)
где 192 – код русской буквы А.

=ЕСЛИОШИБКА(ИНДЕКС($C$4:$C$35;
НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(Фамилии;$C$4:$C$35&"*")>0;
СТРОКА($C$4:$C$35);"");СТРОКА(Z1))-СТРОКА($C$3));"")

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

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

Ячейке С1 присвоим имя Буква.

Для создания выпадающего списка, содержащего перечень фамилий, начинающихся на выбранную букву, создадим Именованный диапазон Вложенный_диапазон со следующей формулой:

=СМЕЩ($A$1;ПОИСКПОЗ(Буква&"*"; Фамилии;0);;
СЧЁТЕСЛИ(Фамилии;Буква&"*"))

Функция СЧЁТЕСЛИ() подсчитывает фамилии, начинающиеся с выбранной буквы. Результат соответствует высоте диапазона, возвращаемой функцией СМЕЩ() . Функция ПОИСКПОЗ() , принимающая в качестве аргумента [искомое_значение] подстановочные знаки, возвращает позицию первой встретившейся фамилии, начинающуюся с выбранной буквы. Результат соответствует смещению левой верхней ячейки диапазона относительно ячейки A1.

В Диспетчере имен должны отображать 3 имени:

Наконец, с помощью Проверки данных создадим в ячейке D1 связанный список, содержащий фамилии, начинающиеся с буквы из ячейки С1. Формула проста: =Вложенный_диапазон .

В итоге, мы должны получить работающий выпадающий список в ячейке D1. Выбрав в ячейке С1, например, букву «В», выпадающий список в ячейке D1 будет содержать фамилии из столбца А, но только те, которые начинаются с буквы «В».

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

Пример создания зависимого выпадающего списка в ячейке Excel

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

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

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

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

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

Список категорий и подкатегорий в зависимом выпадающем списке Excel

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

А конечный результат выглядит следующим образом:

Зависимый выпадающий список подкатегорий

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

Рабочая исходная таблица Excel

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

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

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название "Категория".
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать "Рабочий_Список". Этот диапазон мы будем использовать в формуле.

2. Создание раскрывающегося списка для категории

Это будет просто:

1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно "Проверка вводимых значений".
3. В качестве типа данных выберите "Список".
4. В качестве источника введите: =Категория (рисунок ниже).
5. Подтвердите с помощью OK.

Проверка вводимых значений – Категория.

Раскрывающийся список для категории.

3. Создание зависимого выпадающего списка для подкатегории

Сейчас будет весело. Создавать списки мы умеем – только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

Начнем с того, что мы уже умеем, то есть с создания раскрывающегося списка в ячейке B12. Поэтому выберите эту ячейку и нажмите "Данные" / "Проверка данных», а в качестве типа данных – "Список".

В источник списка введите следующую формулу:

Вид окна "Проверка вводимых значений":

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта – диапазон H12: H15 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне – это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

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

1. чтобы отобразить строки для уровня, щелкните соответствующие символы структуры .

2. Уровень 1 содержит сумму продаж по всем строкам с подробными данными.

3. Уровень 2 содержит сумму продаж по каждому месяцу в каждом регионе.

4. уровень 3 включает в себя строки данных — в этом случае строки от 17 до 20.

5. чтобы развернуть или свернуть данные в структуре, щелкните символы структуры и или нажмите клавиши Alt + Shift + = , чтобы развернуть и Alt + Shift + = для свертывания.

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

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

Вставка итоговых строк при помощи команды Промежуточные итоги

Используйте команду Итоги для вставки функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ непосредственно под или над каждой группой строк с подробными данными и для автоматического создания многоуровневой структуры. Подробнее об использовании функции "Итоги" читайте в статье Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Вставка собственных итоговых строк

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

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

Откроется диалоговое окно "Параметры".

В диалоговом окне Параметры снимите флажок суммарные строки под данными и нажмите кнопку ОК.

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

Автоматическое структурирование данных

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

На вкладке Данные в группе Структура щелкните стрелку рядом с пунктом Группировать, а затем выберите пункт Создать структуру.

Структурирование данных вручную

Важно: Группируя уровни структуры вручную, отобразите на экране все данные во избежание ошибок группировки строк.

Чтобы структурировать внешнюю группу (уровень 1), выделите все строки, которые будет содержать внешняя группа (например, строки с подробными сведениями и, если они были добавлены, суммарные строки).

1. Первая строка содержит надписи и не выделена.

2. так как это внешняя группа, выделите все строки с промежуточными и подробными данными.

3. не выделять общие итоги.

На вкладке Данные в группе Структура выберите Группировать. В диалоговом окне Группа выберите пункт строки, а затем нажмите кнопку ОК.

Совет: Если выделять целые строки, а не только ячейки, Excel автоматически группирует по строкам — диалоговое окно "Группировка" не открывается.

Рядом с группой на экране появятся знаки структуры.

Или структурируйте внутреннюю вложенную группу — строки с данными для определенного раздела данных.

Примечание: Если вы не хотите создавать внутренние группы, перейдите к шагу f, показанному ниже.

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

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

2. Этот раздел выбран и готов к группировке.

3. не выделять итоговую строку для данных, которые нужно сгруппировать.

На вкладке Данные в группе Структура выберите Группировать.

В диалоговом окне Группа выберите пункт строки, а затем нажмите кнопку ОК. Рядом с группой на экране появятся знаки структуры.

Совет: Если выделять целые строки, а не только ячейки, Excel автоматически группирует по строкам — диалоговое окно "Группировка" не открывается.

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

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

Вы также можете разгруппировать разделы структуры без удаления всего уровня. Удерживая нажатой клавишу SHIFT, щелкните или группы, а затем на вкладке данные в группе Структура нажмите кнопку Разгруппировать.

Важно: Если во время разгруппировки структуры подробные данные скрыты, строки с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых строк рядом со скрытыми строками. Затем на вкладке Главная в группе ячейки нажмите кнопку Формат, наведите указатель на пункт Скрыть & отобразить, а затем выберите команду отобразить строки.

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

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

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

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

Откроется диалоговое окно "Параметры".

В диалоговом окне Параметры снимите флажок суммарные столбцы справа от подробных данных и нажмите кнопку ОК.

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

Автоматическое структурирование данных

Выделите ячейку в диапазоне.

На вкладке Данные в группе Структура щелкните стрелку под пунктом Группировать, а затем выберите Создать структуру.

Структурирование данных вручную

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

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

1. столбец A со заголовками.

2. Выделите все столбцы детализации и промежуточные итоги. Обратите внимание, что если не выделять столбцы целиком, то при нажатии кнопки Группировать (на вкладке данные в группе Структура ) откроется диалоговое окно Группа и попросят выбрать строки или столбцы.

3. не выбирайте столбец общий итог.

На вкладке Данные в группе Структура выберите Группировать.

Над группой появится символ структуры.

Чтобы структурировать внутреннюю вложенную группу столбцов с подробными сведениями (уровень 2 или выше), выделите столбцы с подробными сведениями, расположенные рядом с столбцом, содержащим итоговый столбец.

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

2. Эти столбцы выделены и готовы к группировке. Обратите внимание, что если не выделять столбцы целиком, то при нажатии кнопки Группировать (на вкладке данные в группе Структура ) откроется диалоговое окно Группа и попросят выбрать строки или столбцы.

3. не выделять столбец сводки для данных, которые нужно сгруппировать.

На вкладке Данные в группе Структура выберите Группировать.

Рядом с группой на экране появятся знаки структуры.

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

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

Вы также можете разгруппировать разделы структуры без удаления всего уровня. Удерживая нажатой клавишу SHIFT, щелкните или группы, а затем на вкладке данные в группе Структура нажмите кнопку Разгруппировать.

Если во время разгруппировки структуры подробные данные скрыты, столбцы с подробными данными могут также не отображаться. Для отображения данных перетащите указатель через номера видимых столбцов рядом со скрытыми столбцами. На вкладке Главная в группе Ячейки нажмите кнопку Формат, выберите пункт Скрыть или отобразить, а затем нажмите кнопку Отображение столбцов

Если вы не видите символы структуры , и , выберите параметры > файлов> Дополнительно, а затем в разделе Параметры отображения для этого листа установите флажок Показывать символы структуры при применении структуры и нажмите кнопку ОК.

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

Отображение или скрытие подробных данных для группы

Чтобы отобразить подробные данные в группе, нажмите кнопку для группы или нажмите клавиши Alt + Shift + =.

Чтобы скрыть подробные данные группы, нажмите кнопку для группы или нажмите клавиши Alt + Shift +-.

Развертывание или свертывание структуры до определенного уровня

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

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

Отображение или скрытие всех структурированных данных

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

Чтобы скрыть все подробные данные, нажмите .

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

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

Автоматическое применение стиля к новым итоговым строкам или столбцам

На вкладке данные в группе Структура нажмите кнопку вызова диалогового окна.

Откроется диалоговое окно "Параметры".

Установите флажок Автоматические стили.

Назначение стиля существующей итоговой строке или столбцу

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

На вкладке данные в группе Структура нажмите кнопку вызова диалогового окна.

Откроется диалоговое окно "Параметры".

Установите флажок Автоматические стили и нажмите кнопку Применить стили.

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

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

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

Выделите диапазон итоговых строк.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Выделить группу ячеек.

Выберите пункт только видимые ячейки.

Нажмите кнопку ОК, а затем скопируйте данные.