Вложенный список в 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. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

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

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

Я даже не знаю, как это задавать.

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

Есть ли способ иметь "вложенный" список (а не раскрывающееся меню – просто список посещенных дат для каждого человека), так что один человек все еще потребляет только одну строку?

list excel nested

1 ответ

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

основной лист (ID, имя, контактная информация, телефон и т.д.) второй лист (ID, дата посещения)

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

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

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

Это будет считать весь ID во втором листе, который соответствует текущему идентификатору строки на вашем основном листе.

Примечания об использовании одной ячейки:

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

Если вы настаиваете на сохранении дат там, вы можете подсчитать количество посещений, посчитав общее количество запятых + 1 liek this =(LEN(G1) – LEN(SUBSTITUTE(G1,",","")))+1 Эта формула принимает длину всех дат, а длина дат с запятыми удаляется и вычитает их для получения ряда вхождений.

Заметки об использовании нескольких столбцов:

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

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