ВПР (VLOOKUP) по нескольким условиям
Друзья, нас очень часто спрашивают — а как все-таки сделать ВПР по нескольким условиям. Давайте разбираться.
Объединяем условия и диапазоны для поиска
Как работает функция ВПР? Вы задаете условие поиска и функция ищет его в первом столбце выбранного диапазона данных, в случае успеха выводится тот столбец из таблицы, который вы указали.
Наша таблица — ниже, из нее нужно отобрать ячейку по двум условиям, “а” и “б”.
Для этого в функции ВПР нужно объединить оба условия поиска и диапазоны поиска. Объединим и выведем для наглядности.
Добавляем все в ВПР. Формула массива (arrayformula) нужна, чтобы “на лету” объединить столбцы.
Функция уже работает но в ней всего один столбец данных, добавляем к диапазону тот столбец, который будем выводить, C2:C3.
Конструкцией ниже мы создали массив из двух столбцов (объединенного A2:A3 и B2:B3 и C2:C3), обратная косая черта (“\”) — отделяет столбцы, фигурные скобки <> — создают массив.
Итак, формула:
Поиск по двум условиям, FILTER
ВПР по нескольким условиям возможен, но непрост, особенно для новичков, плюс из-за формулы массива это не самая быстрая формула.
Тоже самое можно сделать с помощью функции FILTER:
Если значений большего одного
FILTER, в отличии от ВПР выводит все значения, которые отвечают нашему условию, чтобы оставить одно (например верхнее), добавляем INDEX:
=INDEX(FILTER(C2:C4;A2:A4=E2;B2:B4=F2);1)
Мы много писали и про ВПР и про FILTER, все статьи — в оглавлении нашего канала.
ЛЕВЫЙ ВПР/VLOOKUP в Google Таблицах (когда искомое значение не в первом столбце)
Если в исходной таблице искомые данные стоят правее тех данных, которые вам нужно возвращать с помощью ВПР / VLOOKUP, можно воспользоваться сочетанием функций ИНДЕКС / INDEX и ПОИСКПОЗ / MATCH (так обычно это решается в Excel).
Но в Google Таблицах можно еще проще решить эту проблему, пересобрав таблицу в правильном порядке в массив внутри ВПР.
Напомню правила формирования массивов в формулах Google Таблиц. Массив берется в фигурные скобки, переход на новую строку — это точка с запятой, переход на новый столбец — обратная косая черта (для российских региональных настроек). То есть следующая формула будет возвращать таблицу 2×2, которую вы видите на скриншоте.
Таким образом, чтобы внутри функции ВПР создать виртуальную таблицу, в которой сначала будет идти столбец B из исходных данных, а потом столбец A, понадобится следующая конструкция:
Третий аргумент функции здесь равен двум, потому что нам нужно подтягивать данные из второго столбца нашей виртуальной таблицы.
А еще с помощью этой конструкции можно создать массив только из двух столбцов (столбца поиска и столбца, который будет возвращаться) и не использовать внутри ВПР таблицу из двадцати столбцов. Это может ускорить работу. То есть вместо ссылки вида =ВПР(A1; Данные!A:BZ; . ) вы используете =ВПР(A1; < Данные!A:A \ Данные!AX >; 2; 0)
Как использовать ЕСЛИОШИБКА с функцией ВПР в Google Таблицах
Вы можете использовать следующую формулу с ЕСЛИОШИБКА и ВПР в Google Таблицах, чтобы вернуть значение, отличное от #Н/Д, когда функция ВПР не находит определенное значение в диапазоне:
= IFERROR ( VLOOKUP ( " string " , A2:B11 , 2 , FALSE ) , " Does Not Exist " ) Эта конкретная формула ищет «строку» в диапазоне A2:B11 и пытается вернуть соответствующее значение во втором столбце этого диапазона.
Если он не находит «строку», он просто возвращает «Не существует» вместо значения #Н/Д.
В следующем примере показано, как использовать эту формулу на практике.
Пример: использование ЕСЛИОШИБКА с функцией ВПР в Google Таблицах
Предположим, у нас есть следующий набор данных, который показывает количество очков, набранных различными баскетбольными командами:
Предположим, мы используем следующую формулу ВПР , чтобы найти количество очков, связанных с командой «Рэпторс»:
= VLOOKUP ( " Raptors " , A2:B11 , 2 , FALSE ) На следующем снимке экрана показано, как использовать эту формулу:
Эта формула возвращает значение #Н/Д , поскольку в столбце «Команда» нет «Хищников».
Однако мы можем использовать следующую функцию ЕСЛИОШИБКА с функцией ВПР , чтобы вернуть значение «Не существует» вместо #Н/Д :
=IFERROR(VLOOKUP(" Raptors", A2:B11 , 2 , FALSE ), " Does Not Exist " ) На следующем снимке экрана показано, как использовать эту формулу:
Поскольку в столбце «Команда» «Хищников» нет, формула возвращает значение «Не существует» вместо значения #Н/Д .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Google Таблицах:
Как работает впр в гугл таблицах
Добрый день, уважаемые форумчане!
Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.
Вопрос:
Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?
Или
Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
Добрый день, уважаемые форумчане!
Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.
Вопрос:
Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?
Или
Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
Сообщение Добрый день, уважаемые форумчане!
Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.
Вопрос:
Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?
Или
Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
Спасибо. Автор — book
Дата добавления — 31.05.2019 в 11:37
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Замечаний: 0% ±
Excel 2010
У себя решил данный вопрос написанием функции ВПР через формулу массива
Если этот ваш комментарий:
Цитата book, 31.05.2019 в 11:37, в сообщении № 1 ( )
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
О том что нет автоматизации когда нужно допротягивать формулу.
В вашем примере я бы написал в столбец H2:
У себя решил данный вопрос написанием функции ВПР через формулу массива
Если этот ваш комментарий:
Цитата book, 31.05.2019 в 11:37, в сообщении № 1 ( )
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
О том что нет автоматизации когда нужно допротягивать формулу.
В вашем примере я бы написал в столбец H2:
Сообщение У себя решил данный вопрос написанием функции ВПР через формулу массива
Если этот ваш комментарий:
Цитата book, 31.05.2019 в 11:37, в сообщении № 1 ( )
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
О том что нет автоматизации когда нужно допротягивать формулу.
В вашем примере я бы написал в столбец H2:
Автор — Kashimirush
Дата добавления — 28.08.2019 в 09:14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Замечаний: 0% ±
Excel 2016
Вы правы — я говорил о том, что надо было бы протягивать формулу.
Ваша формула работает на примере. Хотя я пока не понял, как именно она работает. И раньше не сталкивался с такой конструкцией, где первым аргументом идет название столбца. Потом попробую на реальных данных.
Для решения своей задачи, мне пришлось таки добавить столбец «Группа статей бюджета» в исходные данные.
Еще раз спасибо.
Вы правы — я говорил о том, что надо было бы протягивать формулу.
Ваша формула работает на примере. Хотя я пока не понял, как именно она работает. И раньше не сталкивался с такой конструкцией, где первым аргументом идет название столбца. Потом попробую на реальных данных.
Для решения своей задачи, мне пришлось таки добавить столбец «Группа статей бюджета» в исходные данные.
Еще раз спасибо. book
Сообщение Спасибо Kashimirush !
Вы правы — я говорил о том, что надо было бы протягивать формулу.
Ваша формула работает на примере. Хотя я пока не понял, как именно она работает. И раньше не сталкивался с такой конструкцией, где первым аргументом идет название столбца. Потом попробую на реальных данных.
Для решения своей задачи, мне пришлось таки добавить столбец «Группа статей бюджета» в исходные данные.
Еще раз спасибо. Автор — book
Дата добавления — 28.08.2019 в 13:13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Замечаний: 0% ±
Excel 2010
book,
Работает так:
<> — Означает что внутри массив данных, через точку с запятой ставятся данные которые массив будет выводить в строки, можно было обойтись и без этих скобок, тогда надо было ставить формулу в ячейку Н3 — что не удобно т.к. это поле с данными , а его иногда нужно сортировать и т.д.
Допустим пример простой выведет 1 в первой строчке, 2 во второй , 3 в третьей и т.д. — поэтому первый аргумент в этой строчке название вашего столбца.
Далее начнем по порядку, простое использование ВПР, формула выглядела бы так: VLOOKUP(F3;A3:B8;2;0) — она искала бы в диапазоне А3:В8, только значение из ячейки F3, а вам нужно отслеживать все значения в диапазоне F3:F, если написать:
VLOOKUP(F3:F;A3:B8;2;0) — ячейка выдаст ошибку, для работы с массивом вводных аргументов требуется формулу записать как формулу массива для этого используется функция ArrayFormula.
Если записать формулу вида ArrayFormula(VLOOKUP(F3:F;A3:B8;2;0)) — она выдаст массив данных, в котором использует ВПР для всех значений в диапазоне F3:F.
Но для пустых ячеек и тех ячеек которые не содержат данные из диапазона A3:B8 — формула выдаст ошибку, поскольку ВПР ничего не найдет.
Поэтому для более удобной визуализации я скрыл ошибочные значения ВПРа функцией iferror (еслиошибка) — т.е. если ошибка в ВПР она возвращает в ячеку пробел, либо значение которое вы сами можете прописать в кавычках.
Понятно объяснил? ХД
Забегая вперед у функций массивов нет возможности отображения данных вручную, т.е. если попытаетесь вписать самостоятельно статью затрат — формула выдаст ошибку и удалит все выводимые значения.
book,
Работает так:
<> — Означает что внутри массив данных, через точку с запятой ставятся данные которые массив будет выводить в строки, можно было обойтись и без этих скобок, тогда надо было ставить формулу в ячейку Н3 — что не удобно т.к. это поле с данными , а его иногда нужно сортировать и т.д.
Допустим пример простой выведет 1 в первой строчке, 2 во второй , 3 в третьей и т.д. — поэтому первый аргумент в этой строчке название вашего столбца.
Далее начнем по порядку, простое использование ВПР, формула выглядела бы так: VLOOKUP(F3;A3:B8;2;0) — она искала бы в диапазоне А3:В8, только значение из ячейки F3, а вам нужно отслеживать все значения в диапазоне F3:F, если написать:
VLOOKUP(F3:F;A3:B8;2;0) — ячейка выдаст ошибку, для работы с массивом вводных аргументов требуется формулу записать как формулу массива для этого используется функция ArrayFormula.
Если записать формулу вида ArrayFormula(VLOOKUP(F3:F;A3:B8;2;0)) — она выдаст массив данных, в котором использует ВПР для всех значений в диапазоне F3:F.
Но для пустых ячеек и тех ячеек которые не содержат данные из диапазона A3:B8 — формула выдаст ошибку, поскольку ВПР ничего не найдет.
Поэтому для более удобной визуализации я скрыл ошибочные значения ВПРа функцией iferror (еслиошибка) — т.е. если ошибка в ВПР она возвращает в ячеку пробел, либо значение которое вы сами можете прописать в кавычках.
Понятно объяснил? ХД
Забегая вперед у функций массивов нет возможности отображения данных вручную, т.е. если попытаетесь вписать самостоятельно статью затрат — формула выдаст ошибку и удалит все выводимые значения. Kashimirush
Сообщение book,
Работает так:
<> — Означает что внутри массив данных, через точку с запятой ставятся данные которые массив будет выводить в строки, можно было обойтись и без этих скобок, тогда надо было ставить формулу в ячейку Н3 — что не удобно т.к. это поле с данными , а его иногда нужно сортировать и т.д.
Допустим пример простой выведет 1 в первой строчке, 2 во второй , 3 в третьей и т.д. — поэтому первый аргумент в этой строчке название вашего столбца.
Далее начнем по порядку, простое использование ВПР, формула выглядела бы так: VLOOKUP(F3;A3:B8;2;0) — она искала бы в диапазоне А3:В8, только значение из ячейки F3, а вам нужно отслеживать все значения в диапазоне F3:F, если написать:
VLOOKUP(F3:F;A3:B8;2;0) — ячейка выдаст ошибку, для работы с массивом вводных аргументов требуется формулу записать как формулу массива для этого используется функция ArrayFormula.
Если записать формулу вида ArrayFormula(VLOOKUP(F3:F;A3:B8;2;0)) — она выдаст массив данных, в котором использует ВПР для всех значений в диапазоне F3:F.
Но для пустых ячеек и тех ячеек которые не содержат данные из диапазона A3:B8 — формула выдаст ошибку, поскольку ВПР ничего не найдет.
Поэтому для более удобной визуализации я скрыл ошибочные значения ВПРа функцией iferror (еслиошибка) — т.е. если ошибка в ВПР она возвращает в ячеку пробел, либо значение которое вы сами можете прописать в кавычках.
Понятно объяснил? ХД
Забегая вперед у функций массивов нет возможности отображения данных вручную, т.е. если попытаетесь вписать самостоятельно статью затрат — формула выдаст ошибку и удалит все выводимые значения. Автор — Kashimirush
Дата добавления — 28.08.2019 в 15:44