Это шестая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.
Многоуровневый комиссионный план: функция ЕСЛИ или ВПР. Проблема: я рассчитываю комиссию на основе скользящей шкалы. Процент зависит от объема продаж (рис. 6.1).
Рис. 6.1. Комиссионный процент в зависимости от объема продаж
Скачать заметку в формате Word или pdf, примеры в формате Excel
Стратегия: вы можете решить задачу с помощью нескольких операторов ЕСЛИ или с использовав необычную формой функции ВПР. При использовании функции ЕСЛИ начните проверку с самого большого значения. Например, в ячейке F2 (рис. 6.2.) содержится значение $22810. Проверка F2 > 20000 возвратит значение ИСТИНА, но и проверка F2 > 1000 также вернет ИСТИНА. В формуле ниже выбрана правильная последовательность проверок. Результат ЕСЛИ умножается на выручку в F2. Это избавит вас от необходимости вводить F2 пять раз.
Рис. 6.2. Пять вложенных операторов ЕСЛИ
Использована формула:
=ЕСЛИ(F2>20000;0,02; ЕСЛИ(F2>15000;0,0125; ЕСЛИ(F2>10000;0,01; ЕСЛИ(F2>7500;0,0025; ЕСЛИ(F2>1000;0,001;0)))))*F2
Как бонусный план становится еще более сложным, вы должны увеличить количество вложений. Excel2013 позволяет вложить 32 оператора ЕСЛИ. Еще совсем недавно (в Excel 2003) можно было вложить только 7 операторов ЕСЛИ. Довольно быстро этот метод становится громоздким.
Это первый пример, когда полезна ВПР с третьим аргументом ИСТИНА (эквивалентно отсутствию аргумента). Использовать ВПР, вам придется в обратном порядке так, что наибольшее значение появляется в конце таблицы подстановки (рис. 6.3). Кроме того, нужно добавить первую строку для обработки продаж в диапазоне 0–$1000. (На самом деле, в зависимости от того, как вы хотите представить отрицательные значения, может потребоваться еще одна строка.)
Рис. 6.3. Таблица подстановки требует разместить значения от наименьшего к наибольшему
В таблице подстановки (L2:M8) продажи $22 810 отсутствуют. Используя типичную ВПР с третьим аргументом ЛОЖЬ, мы получили бы ошибку #Н/Д. Когда вы опускаете третий аргумент (или используете значение ИСТИНА), ВПР будет искать ближайшее значение, меньшее, чем $22 810. В нашем случае ВПР вернет значение 2% для значения $20 000 чуть меньшего, чем $22 810.
Дополнительные сведения: возможно, вы сталкивались с обратной ситуацией, когда вам требовалось найти значение в таблице подстановки чуть большее искомого. Вы не можете сделать это с помощью ВПР, но можете использовать функцию ПОИСКПОЗ (см. главу 5).
Поиск в двух направлениях с использованием ИНДЕКС и двух ПОИСКПОЗ. Проблема: мне нужно выполнить поиск кода продукта по столбцу и месяца по строке, а затем вернуть значение на пересечении этой строки и столбца.
Стратегия: вы можете использовать функцию ПОИСКПОЗ, чтобы найти строку, затем вторую ПОИСКПОЗ, чтобы найти столбец, а затем ИНДЕКС, чтобы выбрать правильное значение.
В этом примере, пользователь с помощью выпадающих меню в ячейках J2 и J3 выбирает артикул и месяц (рис. 6.4). Найдите объем продаж (в штуках) по артикулу А397 за апрель (рис. 6.5).
Рис. 6.4. Выберите артикул и месяц
Рис. 6.5. Нужно найти объем продаж (в штуках) по артикулу А397 за апрель
Ваша первая формула будет использовать ПОИСКПОЗ, чтобы найти строку в левом столбце таблицы. Формула в J5 =ПОИСКПОЗ(J2;$A$2:$A$20;0). Ответ 2 указывает, что A397 находится во второй строке таблицы подстановки. Вторая формула в J6 (рис. 6.6) =ПОИСКПОЗ(J3;B1:G1;0) ищет столбец в первой строке. Результат 4 указывает на то, что Апрель найден в четвертом столбце таблицы подстановки. Наконец, ИНДЕКС возвращает значение из таблицы B2:G20, беря столбец из J5 (второй) и строку из J6 (четвертую). В J7 формула =ИНДЕКС(B2:G20;J5;J6).
Рис. 6.6. ПОИСКПОЗ в J6 подобен ГПР
Избавьтесь от промежуточных вычислений, и создайте мега-формулу. Возьмите за основу формулу в J7 и замените ссылки на промежуточные вычисления, т.е. подставьте вместо J5 –> ПОИСКПОЗ(J2;$A$2:$A$20;0), а вместо J6 –> ПОИСКПОЗ(J3;B1:G1;0). Используйте для этого копирование из строки формул, не беря в буфер знак равенства. Вот что у вас получится: =ИНДЕКС(B2:G20;ПОИСКПОЗ(J2;$A$2:$A$20;0);ПОИСКПОЗ(J3;B1:G1;0)).
Рис. 6.7. Эта формула включила в себя три предыдущие
Поиск в двух направлениях с использованием ВПР и ПОИСКПОЗ. Воспользуемся предыдущим примером, но пойдем другим путем. Возьмем ВПР, чтобы найти нужную строку, а внутри нее используем ПОИСКПОЗ (в качестве третьего аргумента ВПР) для нахождения правильного столбца.
Начните с функции ВПР. Когда вы дойдете до ее третьего аргумента, вместо конкретного значения введите ПОИСКПОЗ(B2;A$7:G$7;0). Обратите внимание, что таблица подстановки A$7:G$7 здесь включает столбец A, так как ВПР отсчитывает столбцы от первого (включительно). В то же время в предыдущем примере, ни ИНДЕКС, ни ПОИСКПОЗ не включали столбец А. Формула в ячейке C5: =ВПР(B1;$A$8:$G$26;ПОИСКПОЗ(B2;A$7:G$7;0);ЛОЖЬ). См. также Использование ВПР для извлечения данных из таблицы с двумя параметрами.
Рис. 6.8. Использование ПОИСКПОЗ в качестве третьего аргумента ВПР
Поиск по двум столбцам. Проблема: мне нужно найти объем продаж (в штуках) по коду компании и номеру Центра (рис. 6.9).
Рис. 6.9. Поиск по Компании и Центру
Стратегия: есть три варианта решения этой проблемы: а) сцепленный ключ, (б) СУМЕСЛИМН, (с) СМЕЩ. Сцепленный ключ будет работать только если вы можете добавить новый столбец слева от столбца С. СУММЕСЛИМН будет работать только если возвращаемое значение является числовым. СМЕЩ будет работать только если столбцы Компания и Центр отсортированы, как показано выше.
Сцепленный ключ. Вставьте новый столбец между В и C. Вы хотите объединить содержимое столбца А, разделитель, и содержимое столбца В. Например, =А2&"–"&В2 подготовит ключ 100-1010 (рис. 6.10).
Рис. 6.10. Сцепленный ключ
Разделитель текста использовать необязательно. Однако, если вы не используете разделитель, у вас может получиться один и тот же ключ для двух разных связок Компания/Центр (рис. 6.11). Использование дефиса предотвратит эту двусмысленность. Если ваши данные уже содержат дефис, в качестве разделителя можно использовать что-то вроде вертикальной черты |.
Рис. 6.11. Использование дефиса предотвратит двусмысленность
После создания столбца со сцепленным ключом в таблице подстановки, вы можете использовать ВПР (рис. 6.12). Формула в G5: =ВПР(G2&"–"&G3;$C$2:$D$22;2;ЛОЖЬ).
Рис. 6.12. Два ключевых поля, объединенные вместе «на лету» (т.е. в самой формуле), могут использоваться в качестве первого аргумента функция ВПР
СУМЕСЛИМН. Если значение, которое вы пытаетесь вернуть, численное, вы можете использовать СУММЕСЛИМН, БДСУММ, СУММПРОИЗВ. Из этих трех, СУММЕСЛИМН самая простая, но она будет работать, лишь начиная с версии Excel 2007 (рис. 6.13).
Рис. 6.13. Решение с помощью СУММЕСЛИМН (здесь и далее, если рисунок кажется мелким, кликните на нем правой кнопкой мыши, и выберите Открыть картинку в новой вкладке)
Первый аргумент СУММЕСЛИМН – диапазон суммирования, в нашем случае С2:С22. Именно данные из этого диапазона войду в итоговую сумму при выполнении условий. Вот почему требуется, чтобы эти данные были числовыми. В противном случае, функция не будет работать. Далее у вас есть пары аргументов, которые определяют диапазон соответствия критерию и сам критерий (можно ввести до 127 пар). Первый диапазон критериев – Компании (А2:А22). Вы хотите найти записи, в которых значение в столбце Компания равно 200 (значение из F2). Следующий диапазон критериев – Центры (В2:В22). Вы хотите найти записи, в которых Центр совпадает со значением в F3. В нашем случае обоим критериям (компания = 200 и центр = 1030) отвечает единственная строка – 11. Только она даст вклад в сумму и СУММЕСЛИМН вернет единственное значение – 15 097 из ячейки D11. Формула в F5: =СУММЕСЛИМН($C$2:$C$22;$A$2:$A$22;F2;$B$2:$B$22;F3).
Если вы используете Excel 2003 или более ранней версии, попробуйте СУММПРОИЗВ (рис. 6.14). Формула в ячейке F5: =СУММПРОИЗВ((A2:A22=F2)*(B2:B22=F3)*(C2:C22)). Подробнее см. Удивительные функции СУММПРОИЗВ и СУММЕСЛИМН.
Рис. 6.14. Функция СУММПРОИЗВ работает в любой версии Excel
Если вы можете вместо вертикального диапазона критериев (F2:F3) использовать горизонтальный (например, F2:G2), вам подойдет и слегка устаревшая функция БДСУММ (рис. 6.15). Синтаксис функции: БДСУММ(база_данных;поле;условия). Укажите А1:С22 в качестве базы данных, в поле – 3 (результат извлечь из третьего столбца таблицы), условия – диапазон F1:G2.
Рис. 6.15. БДСУММ также справится с проблемой, но диапазон критериев должен иметь заголовок и располагаться горизонтально
Альтернативная стратегия: использовать функцию СМЕЩ. Некоторые специалисты могут сказать, что СМЕЩ является летучей (волатильной) функцией и, следовательно, замедляет расчеты. Однако, СМЕЩ часто решает задачи, в которых необходимо сослаться на диапазон, перемещение или изменение размеров, которые без этой функции решить непросто (подробнее см. Примеры использования функции СМЕЩ).
СМЕЩ используется для указания на диапазон. Расположение и размер диапазона вычисляется на лету в момент расчета формулы. СМЕЩ использует пять аргументов, по крайней мере один из которых (кроме первого) сам по себе основан на расчете (формуле). Поскольку СМЕЩ возвращает не значение, а ссылку на диапазон ячеек, функция обычно используется внутри другой функции, например, СУММ, или в данном случае внутри ВПР (рис. 6.16).
Рис. 6.16. ВПР с встроенной СМЕЩ
Синтаксис: СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]). Например, вы могли бы начать со ссылки на В1 (начало отсчета), спуститься вниз на N строк, сместиться вправо на 0 столбцов (выбрать новую точку отсчета – левую верхнюю ячейку диапазона, который будет возвращен функцией СМЕЩ), и выбрать сам диапазон в 7 строк в высоту и 2 столбца в ширину (желтая область на рисунке).
Функции ПОИСКПОЗ ищет первую строку в диапазоне А2:А22, в которой встречается искомая компания = 200, и возвращает значение 8. Это значение будет указывать функции СМЕЩ насколько сместиться по столбцу В, определяя левый верхний угол диапазона – В9. В ячейке F6 СЧЕТЕСЛИ выясняет, как много ячеек в столбце А, содержащих значение 200 (из ячейки F2). Функция СЧЕТЕСЛИ возвращает значение 7, которое будет использовано в четвертом аргументе функции СМЕЩ и определит высоту диапазона – (с 9-й по 15-ю строку). Можете составить единую формулу, избавившись от промежуточных вычислений:
=ВПР(F3;СМЕЩ($B$1;ПОИСКПОЗ(F2;A2:A22;0);0;СЧЁТЕСЛИ(A:A;F2);2);2;ЛОЖЬ)
Сумма всех поисков. Проблема: есть ли какие-то интересные трюки со старой функцией поиска – ПРОСМОТР, которые можно было бы использовать в этой главе, рассказывающей о расширенном поиска?
Допустим, вы хотите оценить общие премиальные выплаты за месяц (рис. 6.17). Вы пока не рассчитываете бонусы по каждому сотруднику, а лишь хотите знать общую сумму бонусов. Формула СУММ(ВПР) не будет работать, даже если вы используете Ctrl+Shift+Enter, чтобы ввести формулу массива. А вот СУММ(ПРОСМОТР) введенная с помощью Ctrl+Shift+Enter будет работать. Правда, функция ПРОСМОТР выполняет лишь приблизительный поиск (т.е., подобна ВПР с аргументом ИСТИНА). В ячейку G8 введите формулу =СУММ(ПРОСМОТР(C2:C26;G2:G6;H2:H6)), но не нажимайте Enter, а нажмите Ctrl+Shift+Enter, завершая ввод формулы массива (если вы не знакомы с формулами массива, см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).
Рис. 6.17. Одна формула делает много «просмотров», а затем все их суммирует
ВПР, ссылающаяся на другой лист. Проблема: таблица подстановки хранится на другом листе книги. Как сделать ВПР, указывающую на другой лист (рис. 6.18)?
Рис. 6.18. ВПР обращается к таблице подстановки, расположенной на другом листе
Стратегия: вы могли бы изучить синтаксис для ссылки на другие листы и ввести в формулу правильную последовательность символов. Однако, есть гораздо более простые способы ввести формулу без изучения синтаксиса. Рассмотрим три альтернативные стратегии.
Ссылки типа А2:С29 предполагает, что диапазон расположен на том же листе, что и формула. Чтобы сослаться на другой рабочий лист, официальный синтаксис требует набрать ‘имя листа’!А2:С29. Вы заключаете имя листа в апострофы, затем ставите восклицательный знак, и, наконец, даете ссылку на диапазон ячеек. Если вы введите формулу ВПР с указанным синтаксисом, то получите (рис. 6.19):
=ВПР(A2; ‘Таблица просмотра’!$A$1:$C$29; 3;ЛОЖЬ)
Рис. 6.19. Возьмите имя листа в апострофы и поставьте восклицательный знак
Синтаксис слегка изменится если лист, на который ссылаются, не имеет пробелов в имени. В таком случае, можно опустить в апострофы. Формула становится: =ВПР(A38;ТаблицаПросмотра!$A$1:$C$29;3;ЛОЖЬ). Обратите внимание, что при переименовании листа в формуле ВПР изменения происходят автоматически. Excel сам удаляет или добавляет апострофы, во всех формулах, указывающих на переименованный лист.
Альтернативная стратегия: если изучение синтаксиса вам кажется сложным (и неинтересным), используйте имя для таблицы подстановки. Это имя в дальнейшем можно вставить в ВПР (или любую другую функцию). Чтобы присвоить имя, выполните следующие действия:
- Перейдите на лист с таблицей подстановки.
- Выделите весь диапазон, включающий таблицу подстановки. В данном примере A2:С29.
- Щелкните в поле имя слева от строки формул.
- Введите имя и нажмите Enter. Не используйте пробелы в имени.
Рис. 6.20. Наименование диапазона, соответствующего таблице подстановки
Когда вы доберетесь до второго аргумента функции ВПР, начните вводить «Та…». Появится подсказка (рис. 6.21). Для выбора из списка используйте стрелку вниз для перемещения к строке выбора. Затем нажмите клавишу Tab, чтобы вставить этот фрагмент в формулу. Не пытайтесь использовать Enter. Так как формула еще не завершена, вы получите ошибку. Вместо этого можете дважды щелкнуть мышью на строке «ТабПодст».
Рис. 6.21. Автозаполнение предлагает варианты, включая именованный диапазон
В результате вы получите формулу: =ВПР(A2;ТабПодст;3;ЛОЖЬ). Она легче для восприятия, и не требует знания специальных синтаксических правил.
Помните, что одно и тоже имя можно использовать лишь один раз в книге. В принципе, вы можете создать именованный диапазон ТабПодст еще и на Лист1 (рис. 6.22), но он будет определен не во всей книге, а только на Лист1. В этом случае вы можете использовать оба имени диапазона, но опять, как и выше предварить имя нужно будет названием листа и восклицательным знаком: Лист1!ТабПодст. Чтобы не усложнять себе жизнь используйте только разные имена в одной книге.
Рис. 6.22. Два именованных диапазона ТабПодст в одной книге
Альтернативная стратегия: используйте сочетания клавиш, чтобы перейти на другой лист. Начните набирать формулу =ВПР(А2; (рис. 6.23). В этот момент нажмите одновременно Ctrl+PgDn, и вы перейдете на следующий лист. Вы можете повторно нажать Ctrl+PgDn, чтобы перейти к следующему листу. Когда вы окажетесь на нудном листе выберите мышкой диапазон таблицы подстановки, и продолжите набор формулы.
Рис. 6.23. Сочетание клавиш Ctrl+PgDn во время набора формулы позволяет перейти к следующему листу
Альтернативная стратегия: разместите рабочие листы бок о бок и используйте мышь, переходя из окна в окно, чтобы построить формулу. Для этого:
- Пройдите по меню Вид –> Новое окно; появится вторая такая же по содержанию книга Excel; к имени первого файла прибавится двоеточие и цифра 1, второго – цифра 2.
- Пройдите по меню Вид –> Упорядочить все.
- В открывшемся диалоговом окне Расположение окон выберите Рядом и Только окна текущей книги (последняя опция пригодится, если у вас было первоначально открыто несколько книг, а работать вы хотите только с одной книгой и ее дублем; рис. 6.24).
- Во втором окне перейдите на лист с таблицей подстановки; создайте формулу ВПР с помощью набора с клавиатуры и мыши.
- Активизируйте окно с :2 в названии; закройте окно кликнув на крестик в правом верхнем углу.
Рис. 6.24. Два окна одной книги рядом; в разных окнах активны разные листы книги
ВПР, ссылающаяся на другую книгу. Проблема: как создать ВПР, ссылающуюся на другую книгу?
В этом разделе для иллюстрации использованы два файла: AnotherWorkbook, LinkToMe.
Подводный камень: этот прием хорошо работает, когда вторая книга открыта. Когда книга закрыта, вы ограничены чтением 10 000 ячеек из закрытой книги. Это не так много, как кажется: если столбцов три, то это 3333 записей, а если столбцов 20, то лишь 499 записей. Проверьте, что ваша таблица подстановки не превышает лимит в 10К ячеек, прежде чем ссылаться на закрытую книгу.
Если вы только что создали книгу, содержащую таблицу подстановки, вы должны сохранить файл прежде, чем перейти к созданию ВПР. Ссылки на несохраненные книги не будут работать, если вы закроете книгу, из которой ссылаетесь, перед сохранением рабочей книги, на которую ссылаются.
Предположим, что у вас есть две открытые книги. Книга, в которой создается ВПР, является активной. Выполните следующие шаги:
- Начните создавать формулу =ВПР(А2;
- В этот момент нажмите Ctrl+Tab, чтобы переключиться на другую книгу. Обратите внимание, что вы все еще находитесь в режиме редактирования формулы; при этом активной стала книга, содержащая таблицу подстановки.
- Используйте мышь или клавиши со стрелками для выбора таблицы подстановки. Обратите внимание, что, как только вы выберите таблицу, Excel автоматически добавит знак доллара в этой части формулы (рис. 6.25). Майкрософт считает, что в 90% случаев ссылки на связанные книги должны быть абсолютными, поэтому он автоматически делает их такими. Не нажмите по привычке F
- Введите оставшуюся часть формулы: ;3;ЛОЖЬ).
- Нажмите Enter. Вы вернетесь в исходную книгу
Рис. 6.25. Выделите таблицу подстановки, и Excel автоматически укажет верный синтаксис
Рис. 6.26. Нажмите Enter, чтобы вернуться в исходную книгу
Давайте рассмотрим синтаксис формулы: =ВПР(A2;'[LinkToMe.xlsx]Product Table’!$A$2:$C$29;3;ЛОЖЬ).
Ссылка содержит шесть частей (рис. 6.27):
- Открывающий апостроф
- Имя файла в квадратных скобках
- Имя листа
- Закрывающий апостроф
- Восклицательный знак
- Ссылка на диапазон
Рис. 6.27. Ссылка на внешнюю книгу содержит шесть частей
Есть два иных варианта синтаксиса формулы. Если, ни имя файла, ни имя листа не содержать пробелов, цифр, или специальных символов, формула не содержит апострофов: =ВПР(А2;[LinkToMe.xlsx]ProductTable!$А$2:$С$29;3;ЛОЖЬ).
Если книга с таблицей подстановки закрыта, то Excel вставит в формулу ВПР путь к книге: =ВПР(A2;’D:\Dropbox\ДжеленВПР\Глава 6\[LinkToMe.xlsx]Product Table’!$A$2:$C$29;3;ЛОЖЬ)
Позже, при открытии книги, содержащей ссылки, Excel не будет автоматически обновлять ссылки. Открывая файл, вы должны выбрать Обновить (рис. 6.28).
Рис. 6.28. При открытии файла выберите Обновить
Если вас напрягает постоянные вопросы об обновлении, включите автоматическое обновление. Пройдите по меню ДАННЫЕ –> Подключения –> Изменить связи. В открывшемся окне Изменение связей, в верхней области выберите связь и нажмите внизу кнопку Запрос на обновление связей. В открывшемся окне переключитесь на опцию Не задавать вопрос и обновлять связи. Нажмите Оk. Сохраните файл.
Рис. 6.29. Можно изменить настройку и предупреждение при открытии файла появляться не будет
Подводный камень: не переименовывайте и перемещайте книгу, после того как вы создали ссылку на нее. Если вы это сделаете, то должны будете отредактировать ссылки в окне Изменение связей (см. рис. 6.29).
Вернуть последнюю запись. Проблема: некто регистрируется данные по группам. Одна группа – один столбец. Для каждой группы данные начинаются в 5-й строке. Существует различное количество данных в каждом столбце. Мне нужно получить последнюю запись в каждой группе.
Рис. 6.30. Нужно извлечь последнее значение в столбце
Стратегии: существует несколько способов решения задачи. Например, вы могли бы совместить СМЕЩ с СЧЁТЗ, но сейчас проблема будет решить с использованием ВПР с параметром ИСТИНА.
Вернемся к рис. 6.2 и 6.3, где ВПР ищет ставку комиссионного вознаграждения. В таблице подстановки были записи 1000, 7500, 10 000, 15 000 и 20 000. Когда у кого-то была продажа $12 345, то ВПР искала комиссию для ближайшего меньшего значения – $10 000.
Вы можете воспользоваться лазейкой в Excel. Сказать, что вы ищете число, которое больше, чем любое значение в таблице подстановки. Excel вернет последнюю не пустую запись в таблице. Например, в таблице ниже я задал поиск 9 999 999.
Рис. 6.31. ВПР возвращает последнее число
Обратите внимание, что в столбце G формула не сбилась из-за наличия пробела. В то же время, формула проигнорировала ZZZ в Н9 и #Н/Д в F10.
Если вы ищите последнюю текстовую запись в столбце, то используйте в качестве поисковой строки текст, который будет превосходить всё разумное в алфавитном порядке, например, ЯЯЯЯЯ (рис. 6.32). Столбцы F и H иллюстрируют проблему этого метода. Если среди значений есть, и текст, и числа, и значения ошибок, ВПР не будет работать. Точнее, вернет последнее текстовое значение.
Рис. 6.32. Поиск ЯЯЯЯЯ вернет последнюю текстовую запись в столбце
Что делать, если данные повернуто боком, и вам нужно получить последнее значение из каждой строки? Использовать ГПР вместо ВПР (рис. 6.33).
Рис. 6.33. Получить последнюю запись из каждой строки
Дополнительные сведения: вы не обязаны указывать ИСТИНА в качестве четвертого аргумента ВПР. Если опустить четвертый аргумент, Excel по умолчанию использует ИСТИНА.
Возвращение последнего из найденных значений. Проблема: функция ВПР возвращает первое найденное значение. Например, на рис. 6.34 для буквы А ВПР вернет значение 15. Мне нужно получить значение для последнего вхождения. На рис. 6.34 – значение 12 для А.
Рис. 6.34. Необходимо найти значение для последнего появления каждой буквы
Стратегия: используйте формулу =ПРОСМОТР(2;1/($A$1:$A$7=D2);$B$1:$B$7).
Рис. 6.35. Никто в вашем офисе не понимает, что вы делаете))
Во-первых, ПРОСМОТР – устаревшая функция, которая оставлена в Excel для обратной совместимости с Quattro Pro. Это необычная функция, имеющая три аргумента:
- искомое значение – значение, которое функция ПРОСМОТР ищет в первом векторе;
- просматриваемый вектор – диапазон, состоящий из одной строки или одного столбца; значения должны быть расположены в порядке возрастания: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат (текст в нижнем и верхнем регистрах считается эквивалентным);
- вектор результатов – диапазон, состоящий из одной строки или столбца; вектор результатов должен иметь тот же размер, что и просматриваемый вектор.
Функция ПРОСМОТР в отличие от ВПР не имеет четвертого аргумента – тип сравнения. Она всегда использует приблизительное совпадение (аналог ИСТИНА в ВПР). Как и любой иной приблизительный поиск, функция ПРОСМОТР дает корректные результаты если просматриваемый вектор (аналог таблицы подстановки в ВПР) отсортирован по возрастанию. Однако, так как в нашем примере вы пытаетесь обмануть Excel, таблицу А1:В7 сортировать не следует. (Пользователи применяют ПРОСМОТР вместо ВПР, как правило в случае, если поиск ведется внутри формулы массива, так как ВПР в этом случае не работает. Об этом см. ниже.)
За работой формулы =ПРОСМОТР(2;1/($A$1:$A$7=D2);$B$1:$B$7) можно проследить в окне Вычисление формулы (рис. 6.36). Чтобы открыть его выделите ячейку Е2 и пройдите по меню ГЛАВНАЯ –> Зависимости формул –> Вычислить формулу. Нажимайте кнопку Вычислить, и изучайте, как меняется содержимое окна Вычисление.
Рис. 6.36. Окно Вычисление формулы
Сравнение $A$1:$A$7=D2 сгенерит массив значений ЛОЖЬ/ИСТИНА (рис. 6.37). В нашем случае {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ). Далее формула делит единицу на этот массив. Excel считает, что ИСТИНА = 1, а ЛОЖЬ = 0. Так что 1/1 =1, а 1/0 возвращает ошибку #ДЕЛ/0! После выполнения деления у вас образовался массив, состоящий из единиц и ошибок #ДЕЛ/0! Приблизительный ВПР игнорирует текстовые записи и значения ошибок. Аналогично и ПРОСМОТР. Теперь самое интересное. Почему я ищу 2 (первый аргумент функции ПРОСМОТР)? Потому, что массив возвращает только единицы и ошибки #ДЕЛ/0! Так что двойка будет заведомо больше любого значения массива (вспомните трюк с 9999999 выше). В итоге ПРОСМОТР вернет последнее значение, соответствующее единице (ошибки игнорируются). Что нам и было нужно))
Рис. 6.37. Пошаговое вычисление формулы =ПРОСМОТР(2;1/($A$1:$A$7=D2);$B$1:$B$7)
Дополнительные сведения: эту формулу я подсмотрел на форуме MrExcel.com. Кроме того, я нашел там немало диких формул, которые я когда-либо видел. Я взял коллекцию этих формул и издал их в своей книге Excel Gurus Gone Wild.
Встраивание небольшой таблицы подстановки в формулу. Проблема: у меня небольшая таблица подстановки на 5 строк, скрытая в столбцах АА:АВ. Торговые представители, которые используют файл, могут случайно удалить строку в своих данных, не учитывая, что при этом удалиться часть таблицы подстановки. Как мне надежно спрятать таблицу подстановки?
Стратегия: переместите таблицу подстановки на новый лист и скройте его. Однако, если таблица подстановки небольшая и неизменная, вы можете встроить ее прямо в формулу ВПР. Выполните следующие действия:
- Выделите ячейку, содержащую формулу с ВПР (в примере ниже – С4).
- Нажмите F2, чтобы перейти в режим редактирования формулы.
- Выделите часть формулы, относящейся к таблице подстановки (рис. 6.38).
- Нажмите F Это приведет к замене ссылок на значения (рис. 6.39).
- Нажмите Enter, завершая редактирование формулы. Скопируйте формулу в другие ячейки.
Рис. 6.38. Выделите часть формулы, относящейся к таблице подстановки
Рис. 6.39. Нажмите F9. Excel вставит массив констант внутрь формулы
Пару слов о синтаксисе массивов. Фигурные скобки указывают на то, что это массив. Каждая точка с запятой означает переход в новый столбец. Каждое двоеточие означает переход на новую строку.
Впоследствии изменить массив (встроенную таблицу) довольно трудно. Вы можете попытаться разгадать эту загадку, глядя на точки с запятыми и двоеточия, а можете скопировать массив и поместить его обратно на лист Excel. В последнем случае выполните следующее:
- Выделите массив из формулы, включая фигурные скобки.
- Скопируйте символы из буфера обмена.
- Выберите в свободной части листа пустой диапазон из двух столбцов и пяти строк.
- В строке формул введите знак равенства. Нажмите Ctrl+V. Нажмите одновременно Ctrl+Shift+Ввод. Excel поместит массив обратно на лист как формулу массива. Он выглядит как обычная таблица, но нельзя изменять отдельные ячейки (рис. 6.40).
- Выделите таблицу целиком скопируйте ее в буфер, вставьте, как значения. Теперь вы можете редактировать отдельные ячейки в таблице.
Рис. 6.40. Таблица возвращена на лист, но ее всё еще нельзя редактировать
Функция ВЫБОР вместо ВПР. Проблема: мне надо выбрать из пяти вариантов. Я не люблю использовать кучу вложенных ЕСЛИ, но и таблицу подстановки размещать на листе не хотелось бы. Есть ли функция, которая позволит мне указать возможные значения внутри самой функции?
Стратегии: в этой ситуации, вы можете использовать функцию ВЫБОР (рис. 6.41).
Рис. 6.41. Функция ВЫБОР отлично подойдет для коротких списков
Первый аргумент функции – число от 1 до 254 (или ссылка на число). Затем необходимо указать значения для каждого возможного числа, введенные в виде отдельных аргументов. Например, =ВЫБОР(B6;"RO – без питания";"BB – только завтрак";"HB – завтрак и ужин";"FB – завтрак, обед и ужин";"AI – все включено";"UAI – ультра все включено").
Немного неудобно, что вы должны указать каждый вариант как отдельный аргумент. Я всегда хочу указать все аргументы одним диапазоном, например, В8:В13, но это не будет работать. Однако, если у вас где-то на листе уже есть список аргументов, вы не нуждаетесь в использовании функции ВЫБОР; примените ВПР или ИНДЕКС.
В нашем примере функция ВЫБОР возвращает описание типа питания на основе кода, взятого в ячейке В6. Обратите внимание, что ВЫБОР работает только если у вас цифровые коды (1, 2, 3…). Если у вас буквенные коды (а, б, в…), вам, вероятно, не обойтись без таблицы подстановки. Или вы можете преобразовать буквы в цифры, используя функцию КОДСИМВ(). Например, для строчных русских букв: =КОДСИМВ([ссылка на ячейку с буквой])-223, а для прописных английских: =КОДСИМВ()-64.
Если всё же у вас есть где-то на листе список названий, у вас может появиться искушение ввести формулу =ВЫБОР(В8;В9;В10;В11;В12;В13). Вместо этого, проще использовать =ИНДЕКС(B8:B13;B6). Функция возвратит 3-й пункт из списка в В8:В13 (рис. 6.42).
Рис. 6.42. Переключитесь на ИНДЕС, если список представлен где-то на листе
Я не хочу использовать ВПР. Проблема: я дочитал книгу до этой страницы, и не полюбил ВПР. Есть ли что-нибудь, что будет делать ВПР для меня?
Стратегия: возможны два варианта: PowerPivot и Easy-XL.
PowerPivot – это новая надстройка, которая появилась в версии Excel 2010. В некоторых пакетах Excel ее можно активировать, как и другие надстройки. Для тех пакетов, где PowerPivot не включена в дистрибутив, можно попытаться бесплатно скачать ее из Интернета. PowerPivot способна объединять данные в двух таблицах подобно функции ВПР, но своими методами. Недостатком является то, что вы получаете результирующую объединенную таблицу, которую можно использовать только на компьютерах с установленной надстройкой PowerPivot. Также не существует простого способа напечатать или создать PDF из данных PowerPivot (подробнее см. Обработка данных с помощью модуля PowerPivot).
Easy-XL – это сторонняя утилита, которая, в частности, включает механизмы подобные ВПР. Скачайте бесплатную 30-дневную пробную версию Easy-XL с сайта https://www.easy-xl.com/.
