Подстановка "из ниоткуда"
Это несложный, но интересный прием, позволяющий подставлять данные из небольших таблиц без использования ячеек вообще. Его суть в том, что можно «зашить» массив подстановочных значений прямо в формулу. Рассмотрим несколько способов это сделать.
Функция ВЫБОР
Если нужно подставить данные из одномерного массива по номеру, то можно использовать функцию ИНДЕКС или ее более простой и подходящий, в данном случае, аналог – функцию ВЫБОР (CHOOSE). Она выводит элемент массива по его порядковому номеру. Так, например, если нам нужно вывести название дня недели по его номеру, то можно использовать вот такую конструкцию
Это простой пример для начала, чтобы ухватить идею о том, что подстановочная таблица может быть вшита прямо в формулу. Теперь давайте рассмотрим пример посложнее, но покрасивее.
Массив констант в формуле
Предположим, что у нас есть список городов, куда с помощью функции ВПР (VLOOKUP) подставляются значения коэффициентов зарплаты из второго столбца желтой таблицы справа:
Хитрость в том, что можно заменить ссылку на диапазон с таблицей $E$3:$F$5 массивом констант прямо в формуле, и правая таблица будет уже не нужна. Чтобы не вводить данные вручную можно пойти на небольшую хитрость.
Выделите любую пустую ячейку. Введите с клавиатуры знак «равно» и выделите диапазон с таблицей – в строке формул должен отобразиться его адрес:
Выделите с помощью мыши ссылку E3:F5 в строке формул и нажмите клавишу F9 – ссылка превратится в массив констант:
Осталось скопировать получившийся массив и вставить его в нашу формулу с ВПР, а саму таблицу удалить за ненадобностью:
Массив констант с именем
Развивая идею предыдущего способа, можно попробовать еще один вариант – сделать именованный массив констант в оперативной памяти, который использовать затем в формуле. Для этого нажмите на вкладке Формулы (Formulas) кнопку Диспетчер Имен (NameManager). Затем нажмите кнопку Создать, придумайте и введите имя (пусть будет, например, Города) и в поле Диапазон (Reference) вставьте скопированный в предыдущем способе массив констант:
Нажмите ОК и закройте Диспетчер имен. Теперь добавленное имя можно смело использовать на любом листе книги в любой формуле – например, в нашей функции ВПР:
Компактно, красиво и, в некотором смысле, даже защищает от шаловливых ручек непрофессионалов :)
Спасибо за прием. Можно еще, чтобы точно из "ниоткуда", макросом припрятать имя "Города"
Прошу прощения, что обращаюсь не по теме- мой ридер не находит url-ссылку на ленту новостей вашего сайта, в общем не приходят сообщения о ваших новых статьях, она отключена?
Век живи – век учись
Николай, спасибо за статью!
Отлично!
Спасибо огромное, Николай, за новые знания!
В рассмотренном примере по названию города подставляются готовые коэффициенты.
А как сделать, чтобы подставлялась и формула и по ней рассчитывался результат? Скажем каждый город имеет свою формулу для расчета коэф. Москва - D1*2=2, Питер - D1/2=1,5...
Если таблица большая (92 строки и 5 столбцов данных), то не получается ее скрыть, через диспетчер имен. Есть ограничения?
спасибо за ответ.
Добавлю ещё один вопросец к не отвеченному. Можно ли как-то оперативно раскидать текст {"Москва",2;"Питер",1.5;"Самара",0.8} обратно по ячейкам таблицы?