В этом посте приглашенный автор Денис Батьянов объясняет, как найти данные в одной таблице Excel и извлечь их в другую, а также раскрывает все секреты функции вертикального просмотра.
При работе в Excel очень часто возникает необходимость найти данные в одной электронной таблице и извлечь их в другую. Если вы не знаете, как это сделать, в этой статье вы не только узнаете, как это сделать, но и получите несколько советов о том, как добиться максимальной производительности вашей системы. Мы рассмотрим большинство очень эффективных приемов работы с функцией БПФ.
Даже если вы используете БПФ уже много лет, есть вероятность, что эта статья покажется вам полезной и интересной. Например, будучи ИТ-специалистом, а затем руководителем ИТ-отдела, я использовал VLOOKUP в течение 15 лет, но понять все нюансы смог только сейчас, когда начал преподавать Excel людям на профессиональной основе.
VLOOKUP означает вертикальный просмотр. Аналогично, VLOOKUP - это Vertical LOOKUP. Уже из названия функции следует, что она ищет в строках (вертикально - прокрутка строк и фиксация столбца), а не в столбцах (горизонтально - прокрутка столбцов и фиксация строки). Следует отметить, что у ERS есть сестра, гадкий утенок, который никогда не станет лебедем, - функция HLOOKUP. HLOOKUP, в отличие от SOD, выполняет горизонтальный поиск, но концепция Excel (и вся концепция организации данных) предполагает, что в ваших таблицах мало столбцов и много строк. Поэтому во много раз чаще нам требуется поиск по строкам, чем по столбцам. Если вы слишком часто используете функцию PGR в Excel, очень вероятно, что вы что-то неправильно поняли в этой жизни.
Синтаксис
Функция БПФ имеет четыре параметра:
=EPR( ; ; [;] ), здесь:
- это искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (в подавляющем большинстве случаев);
- ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце, в котором будет искаться значение ;
Наверняка многие люди, которые знают функцию FFT как свои пять пальцев, могут почувствовать себя неловко, прочитав описание четвертой опции, потому что они привыкли видеть ее в несколько иной форме: обычно она относится к точному совпадению при поиске (FALSE или 0) или к поиску по диапазону (TRUE или 1).
Теперь напрягитесь и прочитайте следующий абзац несколько раз, пока не поймете, о чем идет речь. Там важно каждое слово. Примеры помогут разобраться в этом.
Как же конкретно работает формула ВПР
- Формула I вид. Если последний параметр опущен или указан как 1, VPR предполагает, что первый столбец отсортирован в порядке возрастания, поэтому поиск останавливается на строке, которая непосредственно предшествует строке, содержащей значение больше искомого. Если такая строка не найдена, возвращается последняя строка диапазона.
- Формула вида II. Если последний параметр указан как 0, VRP последовательно просматривает первый столбец массива и немедленно прекращает поиск, когда найдено первое точное совпадение с параметром , иначе возвращается код ошибки #N/D (#N/A).
что>
Схемы работы формул
ВПР тип I
ВПР тип II
Следствия для формул вида I
- Формулы можно использовать для разделения значений по диапазонам.
- Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.здесь>
- Если вы ищете значение, которое заведомо больше, чем может вместить первый столбец, вы можете легко найти последнюю строку таблицы, которая может оказаться весьма ценной.
- Это представление вернет ошибку #N/D, только если не найдет значение, меньшее или равное искомому.
- Довольно сложно понять, что формула возвращает недопустимые значения, если ваша таблица не отсортирована.
Следствия для формул вида II
Если искомое значение встречается более одного раза в первом столбце массива, формула выберет первую строку для последующего извлечения данных.
Производительность работы функции ВПР
Вы достигли кульминации статьи. Можно подумать, какая разница, укажу ли я в качестве последнего параметра ноль или единицу? Обычно, конечно, все используют ноль, потому что это довольно практично: вам не нужно беспокоиться о сортировке первого столбца массива, вы можете сразу увидеть, найдено ли значение или нет. Но если у вас на листе несколько тысяч формул VLOOKUP, вы заметите, что VLOOKUP типа II работает медленно. При этом все обычно начинают думать:
- Мне нужен более мощный компьютер;
- Мне нужна более быстрая формула; например, многие знают о формуле INDEX+MATCH, которая якобы на 5-10% быстрее.
И мало кто думает, что как только они начнут использовать IDP типа I и убедятся, что первый столбец отсортирован любым способом, скорость IDP увеличится в 57 раз. Я пишу словами - ПЯТЬДЕСЯТ СЕМЬ раз! Не на 57%, а на 5700%. Я проверил этот факт достаточно надежно.
Секрет такой высокой производительности заключается в том, что к отсортированному массиву может быть применен чрезвычайно эффективный алгоритм поиска, называемый бинарным поиском (метод чередования или дихотомии). То есть, IEO типа I применяет его, а IEO типа II ищет без какой-либо оптимизации. То же самое относится к функции MATCH, которая включает аналогичный параметр, и функции LOOKUP, которая работает только с отсортированными массивами и была включена в Excel для совместимости с Lotus 1-2-3.
Недостатки формулы
Недостатки VPR очевидны: во-первых, поиск осуществляется только в первом столбце указанной таблицы, а во-вторых, поиск осуществляется только справа от указанного столбца. И, как вы понимаете, может случиться так, что колонка, содержащая нужную информацию, находится слева от колонки, в которой мы собираемся искать. Этому препятствует уже упомянутый INDEX+MATCH, что делает его наиболее гибким решением для извлечения данных из таблиц, по сравнению с VLOOKUP.
Некоторые аспекты применения формулы в реальной жизни
Диапазонный поиск
Классической иллюстрацией для поиска диапазона является проблема определения скидки по размеру заказа.
Поиск текстовых строк
Конечно, VPR ищет не только цифры, но и текст. Обратите внимание, что формула не чувствительна к регистру. Если вы используете подстановочные знаки, вы можете выполнить нечеткий поиск. Есть два знака подстановки: "?" заменяет любой одиночный символ в текстовой строке, "*" заменяет любое количество произвольных символов.
Борьба с пробелами
Часто задаваемый вопрос - как справиться с проблемой лишних пробелов в поиске. Если справочную таблицу еще можно очистить от них, то первый параметр формулы VPR не всегда зависит от вас. Поэтому, если есть риск засорения ячеек лишними пробелами, вы можете использовать TRIM для их очистки.
Разный формат данных
Если первый параметр функции FFT ссылается на ячейку, содержащую число, которое хранится как текст, а первый столбец массива содержит числа в правильном формате, поиск завершится неудачей. Возможна и обратная ситуация. Эта проблема может быть легко решена путем преобразования параметра 1 в требуемый формат:
=VPR(--D7; Products!$A$2:$C$5; 3; 0) - если D7 содержит текст, а таблица содержит числа;
=VPR(D7 & ""); Products!$A$2:$C$5; 3; 0) - и наоборот.
Кстати, вы можете конвертировать текст в числа сразу несколькими способами, выбирайте на свой вкус:
- Двойное отрицание -D7.
- Умножить на единицу D7*1.
- Дополнение до нуля D7+0.
- Вычитание до первой степени D7^1.
Преобразование числа в текст осуществляется с помощью зажима пустой строки, который заставляет Excel преобразовать тип данных.
Как подавить выдачу #Н/Д
Это можно сделать очень просто с помощью функции IFERROR.
Например: =ESLIO ERROR( ERR(D7; Products!$A$2:$C$5; 3; 0); "").
Если ERR вернет код ошибки #N/D, ESLIO ERR перехватит его и заменит параметр 2 (в данном случае пустую строку), но если ошибки нет, функция сделает вид, что ошибки нет, а есть только ERR, которая вернула нормальный результат.
Массив где>
Мы часто забываем сделать ссылку на массив абсолютной, и массив "плывет" при вытаскивании. Не забывайте использовать $A$2:$C$5 вместо A2:C5.
Хорошей идеей будет разместить ссылочный массив на отдельном листе рабочей книги. Он не будет загроможден и станет более управляемым.
Еще лучшей идеей было бы объявить этот массив как именованный диапазон.
Многие пользователи используют конструкцию A:C при именовании массива, указывая столбцы полностью. Это правильный подход, потому что вам не нужно следить за тем, чтобы ваш массив содержал все необходимые строки. Если вы добавляете строки на лист с исходным массивом, диапазон, указанный как A:C, не нужно будет корректировать. Конечно, такая синтаксическая конструкция заставляет Excel выполнять немного больше работы, чем при указании точного диапазона, но эти накладные расходы можно игнорировать. Речь идет о сотых долях секунды.
А на пределе гениальности можно оформить стол как умный стол.
Использование функции СТОЛБЕЦ для указания колонки извлечения
Если таблица, в которую вы извлекаете данные с помощью VPR, имеет ту же структуру, что и справочная таблица, но содержит меньше строк, вы можете использовать функцию HUNDRED() в VPR для автоматического расчета номеров столбцов, которые нужно извлечь. Это сделает все формулы VPR одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что первый параметр имеет абсолютную координату столбца.
Создание составного ключа через &»»&
При необходимости одновременного поиска по нескольким столбцам необходимо выполнить поиск по составному ключу. Если бы возвращаемое значение было не текстовым (как здесь для поля "код"), а числовым, то подошла бы более удобная формула SUMMESLYMN (SUMIFS), а составной ключ столбца вообще не понадобился бы.
Это моя первая статья для Лайфхакера. Если вам понравилось, пожалуйста, заходите на мой сайт, а также я буду рад прочитать в комментариях ваши секреты использования функций VPR и тому подобное. Спасибо. :)
