Подстановка "из ниоткуда"

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

Функция ВЫБОР

Если нужно подставить данные из одномерного массива по номеру, то можно использовать функцию ИНДЕКС или ее более простой и подходящий, в данном случае, аналог – функцию ВЫБОР (CHOOSE). Она выводит элемент массива по его порядковому номеру. Так, например, если нам нужно вывести название дня недели по его номеру, то можно использовать вот такую конструкцию

lookup-from-array-constants1.png

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

Массив констант в формуле

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

lookup-from-array-constants2.png

Хитрость в том, что можно заменить ссылку на диапазон с таблицей $E$3:$F$5 массивом констант прямо в формуле, и правая таблица будет уже не нужна. Чтобы не вводить данные вручную можно пойти на небольшую хитрость.

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

lookup-from-array-constants3.png

Выделите с помощью мыши ссылку E3:F5 в строке формул и нажмите клавишу F9 – ссылка превратится в массив констант:

lookup-from-array-constants4.png

Осталось скопировать получившийся массив и вставить его в нашу формулу с ВПР, а саму таблицу удалить за ненадобностью:

lookup-from-array-constants5.png

Массив констант с именем

Развивая идею предыдущего способа, можно попробовать еще один вариант – сделать именованный массив констант в оперативной памяти, который использовать затем в формуле. Для этого нажмите на вкладке Формулы (Formulas) кнопку Диспетчер Имен (NameManager). Затем нажмите кнопку Создать, придумайте и введите имя (пусть будет, например, Города) и в поле Диапазон (Reference) вставьте скопированный в предыдущем способе массив констант:

lookup-from-array-constants6.png

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

lookup-from-array-constants7.png

Компактно, красиво и, в некотором смысле, даже защищает от шаловливых ручек непрофессионалов :)

Ссылки по теме


19.03.2015 19:14:27
Добрый день, Дмитрий
Спасибо за прием. Можно еще, чтобы точно из "ниоткуда", макросом припрятать имя "Города"
21.03.2015 08:43:36
Не за что. Только я Николай ;)
23.03.2015 16:04:25
Извините :oops:.
23.03.2015 18:03:22
Здравствуйте Николай!
Прошу прощения, что обращаюсь не по теме- мой ридер не находит url-ссылку на ленту новостей вашего сайта, в общем не приходят сообщения о ваших новых статьях, она отключена?
Массив констант в формуле позволяет разгрузить отчеты от кучи справочников. спасибо
25.03.2015 14:55:03
Действительно очень хороший приём. Учитывая, что большинство "обычных" пользователей Excel с ним незнакома, он поможет "спрятать" таблицу с данными для подстановки VLOOKUP
Век живи – век учись
Николай, спасибо за статью!
30.03.2015 11:52:41
Данный приём, на мой взгляд, противоречит "духу" Excel. Применять его надо крайне дозировано.
31.03.2015 17:42:00
Разобрался!
Отлично!
04.04.2015 20:48:57
Отличный, полезный и, как всегда, элегантный прием! Ранее просто прятала таблицы с данными в скрытых листах.
Спасибо огромное, Николай, за новые знания!
18.04.2015 06:52:52
Спасибо
13.05.2015 17:30:40
prosto super
20.05.2015 15:25:54
Добрый день, Николай.
В рассмотренном примере по названию города подставляются готовые коэффициенты.
А как сделать, чтобы подставлялась и формула и по ней рассчитывался результат? Скажем каждый город имеет свою формулу для расчета коэф. Москва - D1*2=2, Питер - D1/2=1,5...
04.06.2015 08:51:32
Либо ЕСЛИ либо ВПР внутри ВПРа
17.08.2015 11:16:01
Спасибо. Просто, элегантно и феерично... Что так "запросто" работать с массивом констант можно - даже не мечтал. Николай спасибо.
15.09.2015 23:33:58
Добрый день Николай.
Если таблица большая (92 строки и 5 столбцов данных), то не получается ее скрыть, через диспетчер имен. Есть ограничения?
спасибо за ответ.
12.03.2016 07:33:24
Подозреваю, что ограничением будет максимально допустимая длина формулы. В Excel 2003 - это было 1024 символа, в более новых версиях - чуть больше 8 тысяч знаков.
11.03.2016 14:55:50
Приветствую!
Добавлю ещё один вопросец к не отвеченному. Можно ли как-то оперативно раскидать текст {"Москва",2;"Питер",1.5;"Самара",0.8} обратно по ячейкам таблицы?
12.03.2016 07:32:22
Выделяете шесть ячеек (3 на 2), в строке формул пишете знак равно, потом вставляете массив констант и жмете Ctrl+Shift+Enter ;)
14.03.2016 12:26:22
22.11.2016 11:54:46
Здравствуйте Николай, большое спасибо за интересный способ, а можно использовать этот способ для создания выпадающего списка текстовых данных длиной массива более 500 символов, просто при создании массива констант с именем данные у меня получается сформировать данные в диапазоне ={"...":"...":"....} однако в значениях массива они отсутствуют "{...}".
Наверх