Описание занятия
Видеоверсия
Текстовая версия
Записи в перечне слева «сырые», т.е. они не сгруппированы, содержат «маркера (т.е. указатель номера списка)» и саму запись, в списках справа — распределены. Количество в 3 списка взято в качестве примера, это количество может быть произвольным, равно как и название «списки». Особым плюсом можно считать то, что, изменяя маркер в «сыром перечне», можно изменять место расположение записи в сгруппированных списках.
Реализуем такие, автоматически заполняемые списки, подробно объясняя этапы решения задачи.
Суть решения
Видеоверсия
Текстовая версия
Принцип действия
Пользователи, которые уже поработали в Excel, очевидно заметят, что принцип действия таких динамических списков очень схож с таковым у функции ВПР, либо более продвинутого аналога данной функции – связки ИНДЕКС и ПОИСКПОЗ.
Действительно, нам нужно найти в левом столбце «маркер» списка, т.е. название и вернуть элемент такого списка уже из правого столбца. Однако, здесь есть один нюанс, который заключается в том, что функция ВПР, равно как и вышеупомянутая сборка-аналог вернут только первую запись, т.е. максимум мы сможем выбрать таким способом по одному элементу для каждого списка.
Соответственно, нам нужно сделать реализации чего-то на подобие множественного ВПР, когда значения будут выбираться все значения, а не только первое.
Основная идея реализации
Основу в реализации такого множественного выбора будет составлять чрезвычайно полезная функция ИНДЕКС, которая возвращает значение ячейки, которая находится на пересечении указанных строки и столбца. Столбце у нас известен из условия – это столбец со всеми значениями (правый в «сырой» таблице), а вот номер строки мы будем подставлять динамически.
Данную функции еще часто используют в связке с другой полезной функцией ПОИСКПОЗ, однако, сейчас нам нужна не связка, поскольку номер строки (а именно за поиск номера строки в функции ИНДЕКС отвечает функция ПОИСКПОЗ) мы найдем отдельно.
Решение:
Теория:
- Поиск номера строки
Видеоверсия
Текстовая версия
=ЕСЛИ($A2=S$1;СТРОКА(A2))Принцип работы данной формулы становится понятен, если взглянуть на следующий рисунок:
В заголовках записываем значений, которые соответствуют маркерам в первом столбце неупорядоченного списка, в нашем случае это: список1, список2, список3, затем сравниваем значение из заголовка с текущим маркером (в сравнении используются смешанные ссылки, чтобы не записывать одну и туже формулу для трех столбцов, а просто автозаполнением растянуть ее для 2-го и третьего списка), если условие выполняется, тогда возвращаем номер строки из первоначального неупорядоченного списка, если условие ложно, формула вернет ошибку.
Таким образом данную формулу нужно растянуть на столько столбцов, сколько должно быть маркеров (три, в нашем случае) и на не меньшее количество строк чем значений в неупорядоченном диапазоне. Если планируется дополнять диапазон, то данную формулу можно просто протянуть вниз с запасом там просто будут сообщения об ошибках, они никакого негативного влияния не сделают. Более того, это промежуточные вычисления, которые можно будет просто скрыть, при необходимости или вынести на отдельный лист.
- Упорядочивание промежуточных вычислений
Видеоверсия
Текстовая версия
Функция НАИМЕНЬШИЙ похожа на вычисление минимального, т.е. функцию МИН, за тем исключением, что позволяет найти не только минимальное, но и 2-е, 3-е и т.д. наименьше значение после минимального.
Как можно догадаться, вспомогательный числовой ряд будет использоваться для указания того, какое наименышее значение по порядку следует вернуть. Таким образом, конечная формула, сортирующая найденные номера строк будет выглядеть вот так:
=НАИМЕНЬШИЙ(I:I;$L2)Первым аргументом функции указана ссылка полностью на строку, если будете указывать на диапазон, не забудьте позаботиться о том, чтобы его зафиксировать (сделав абсолютную или смешанную ссылку). Второй аргумент – это смешанная ссылка на вспомогательный ряд, т.е., в первом случае, когда ссылка идет на цифру «1», формула вернет минимальное значение, потом — 2-е после минимального и т.д.
Общая картина вычислений выглядит так:
Как и предполагалось, мы просто записали ранее разбросанные значения с вычислением номера строки.
- Построение финальной формулы
Видеоверсия
Текстовая версия
Таким образом, пишем:
=ИНДЕКС($B:$B;M2;1)Здесь мы, как и с упорядочиванием, используем ссылку на столбец, однако, делаем его абсолютной ссылкой, поскольку формулу будем растягивать вправо и нужно чтобы столбец не менялся (в случае поиска наименьшего нам нужно было, чтобы столбец менялся).
В конечном итоге получаем вот такой результат:
Все хорошо, за исключением ошибки, которая находится в не заполненных ячейках. Ошибку легко скрыть, если «обернуть» конечную формулу в функцию ЕСЛИОШИБКА, указав, в качестве второго аргумента, пустую ячейку (просто двойные кавычки).
=ЕСЛИОШИБКА(ИНДЕКС($B:$B;M2;1);"")И вот такой результат:
- Последние штрихи
Видеоверсия
Текстовая версия
Вообще будет хорошей идеей в одном месте записать допустимые значения (например, пускай это будут заголовки упорядоченных списков), а в остальных местах, где они должны использоваться просто ссылаться на них.
Процесс создания выпадающего списка в Excel детально рассмотрен в этой статье, здесь мы заострять внимание на этом не будем.
Файл с примером
Уважаемые пользователи, файл с примером вы можете также скачать в ветке нашего форума, где задать вопрос, или просто обсудить материал.
Функции
| Формула | Применение в рамках данной задачи |
|---|---|
| ИНДЕКС (INDEX) | Основа формулы, используется для сопоставления заголовков таблицы и заголовков диапазонов исходного списка |
| НАИМЕНЬШИЙ (SMALL) | Используется для передачи первой ячейки плавающего диапазона в ВПР |
Вспомогательные, т.е., без которых можно обойтись:
| Формула | Применение в рамках данной задачи |
|---|---|
| ЕСЛИОШИБКА (IFERROR) | Используется для придания эстетического вида таблице, т.е. замене ошибок на пустые значения ячейки. |
Ссылки по теме
Создание выпадающего списка в Excel
Функции работы с данными в Excel
Как сделать так, чтобы ячейки в Excel не заполнялись нолями
