Excel поиск значения по нескольким условиям в excel: Примеры функций ИНДЕКС и ПОИСКПОЗ по нескольким критериям Excel

Несколько условий для одного столбца

Лабораторная
работа (по
теме Функции для работы с БД в Excel
2007)

Тема:
Знакомство с функциями БДПРОИЗВЕД,
БСЧЕТ, БСЧЕТА, ДМАКС, ДМИН, БДСУММ,
ДСРЗНАЧ, ДСТАНДОТКЛ, ДСТАНДОТКЛП, ДБДИСП,
ДБДИСПП, БИЗВЛЕЧЬ

Теоретические
основы.

Для
всех вышеперечисленных функций характерны
одни и те же виды аргументов: база_данных,
поле
и условия.

Причем:

База_данных
— это диапазон ячеек, образующих список
или базу данных. База данных представляет
собой список связанных данных, в котором
строки данных являются записями, а
столбцы — полями. Верхняя строка списка
содержит заголовки всех столбцов.

Поле
— это столбец, используемый функцией.
Введите текст с заголовком столбца в
двойных кавычках, например «Возраст»
или «Урожай», или число (без кавычек),
задающее положение столбца в списке: 1
— для первого столбца, 2 — для второго
и т. д.

Условия
— это интервал ячеек, который содержит
задаваемые условия. В качестве значения
аргумента «условия» может использоваться
любой интервал, содержащий, по крайней
мере, один заголовок столбца и, по крайней
мере, одну ячейку с условием, расположенную
под заголовком столбца.

Примеры
условий

Важно.

1. Поскольку
   для обозначения формулы используется
   знак равенства (=), при вводе текста или
   значения в ячейку программой Microsoft
   Excel выполняются вычисления. Однако это
   может привести к непредвиденным
   результатам при фильтрации. Чтобы
   указать, оператор сравнения в виде
   знака равенства для текста или значения,
   введите условие в соответствующую
   ячейку диапазона как строковое выражение:
   

=»=строка»

строка
— это текст или значение, которые нужно
найти.

Например:

2. При
   отборе текстовых данных в Microsoft Excel не
   учитывается регистр букв. Однако для
   поиска с учетом регистра можно
   воспользоваться формулой. Пример см.
   в разделе Фильтрация
   текста с использованием функции поиска
   с учетом регистра.

Примеры
сложных условий отбора приведены в
следующих разделах.

• Несколько
  условий для одного столбца

• Несколько
  условий для нескольких столбцов, где
  все условия должны быть истинными

• Несколько
  условий для нескольких столбцов, где
  любые условия могут быть истинными

• Несколько
  наборов условий, где в каждом наборе
  имеются условия для нескольких столбцов

• Несколько
  наборов условий, где в каждом наборе
  имеются условия для одного столбца

• Условия
  отбора текстовых строк, содержащих
  определенную последовательность знаков

• Условия,
  являющиеся результатом выполнения
  формулы

• Фильтрация
  значений, превышающих среднее значение
  диапазона данных

• Фильтрация
  текста с использованием функции поиска
  с учетом регистра

Логическое
выражение:(Продавец
= «Белов» ИЛИ
Продавец = «Батурин»)

Чтобы
найти строки, отвечающие нескольким
условиям для одного столбца, введите
эти условия непосредственно одно под
другим в отдельных строках диапазона
условий.

В диапазоне данных (A6:C10) диапазон условий
отбора (B1:B3) отображает строки, содержащие
значения «Белов» или «Батурин» в столбце
«Продавец» (A8:C10).

Несколько условий для нескольких столбцов, где все условия должны быть истинными

Логическое
выражение: (Тип = «Фрукты» И Продажи
> 1 000)

Чтобы
найти строки, отвечающие нескольким
условиям для нескольких столбцов,
введите все эти условия в одной и той
же строке диапазона условий.

В
диапазоне данных (A6:C10) диапазон условий
отбора (A1:C2) отображает все строки,
содержащие значение «Фрукты» в столбце
«Тип» и значения больше 1000
р. в столбце «Продажи» (A9:C10).

Несколько условий для нескольких столбцов, где любые условия могут быть истинными

Логическое
выражение: (Тип = «Фрукты» ИЛИ
Продавец = «Белов»)

Чтобы
найти строки, отвечающие нескольким
условиям для нескольких столбцов, где
любое условие может быть истинным,
введите эти условия в разные строки
диапазона условий.

В
диапазоне данных (A6:C10) диапазон условий
отбора (A1:B3) отображает все строки,
содержащие значение «Фрукты» в столбце
«Тип» или значение «Белов» в столбце
«Продавец» (A8:C10).

Как выполнять ВПР по нескольким критериям и столбцам в Google Таблицах

Помимо MS Excel, Google Таблицы — это одно приложение, которое использует гораздо больше, чем любое другое приложение, для хранения и управления данными. Функции, которые он выполняет, сделали управление данными такой простой задачей всего за несколько кликов. В частности, функция ВПР также называется функцией вертикального поиска. Как и MS Excel, ВПР здесь позволяет искать нужные значения по вертикали и извлекать данные из других таблиц или листов. Однако в Google Таблицах синтаксис ВПР позволяет выполнять поиск только в одном столбце за раз. Если вы ищете способы ВПР нескольких критериев и столбцов в Google Sheets, вы попали в нужное место. В этой статье вы узнаете, как использовать функцию ВПР для нескольких критериев и столбцов в Google Таблицах. Кроме того, вы также узнаете больше о том, когда нам нужно использовать функцию ВПР для нескольких критериев в Google Таблицах. Начнем с синтаксиса обычной функции ВПР.

В этой статье вы узнаете о синтаксисе простой функции ВПР и о том, как выполнять ВПР по нескольким критериям и столбцам в Google Таблицах. Продолжайте читать, чтобы узнать об этом подробнее.

Каков синтаксис обычной функции ВПР?

Синтаксис обычной функции ВПР в Google Таблицах:

=VLOOKUP( search_key, range, index, [is_sorted])

Каждый из этих входов указывает,

• search_key: это значение, которое вы ищете в первом столбце диапазона. Это может быть ячейка, содержащая значение, или ячейка, которую можно использовать в качестве ссылки на ячейку, содержащую значение.

• диапазон: это диапазон ячеек, состоящий из ячеек, для которых функция ВПР ищет значения. Вы должны определить столбец, содержащий ключ поиска_, как первый столбец. Этот диапазон также должен содержать столбец, состоящий из целевого значения.

• индекс: индекс — это термин, используемый для обозначения номера столбца в диапазоне, который содержит целевое значение. Первый столбец диапазона имеет индекс 1, второй столбец имеет индекс 2 и так далее.

• is_sorted: это часть синтаксиса, которая указывает, должен ли столбец быть отсортирован или нет. Это можно сделать с помощью True или False.

Теперь, когда вы понимаете синтаксис простой функции ВПР, давайте продолжим разбираться, когда нам нужно выполнять ВПР по нескольким критериям в Google Таблицах.

Когда мы должны выполнять ВПР по нескольким критериям в Google Таблицах?

Могут быть разные ситуации и причины, по которым вам нужно выполнять ВПР по нескольким критериям в Google Таблицах. Некоторые из причин перечислены ниже:

• Его можно использовать в тех случаях, когда вам необходимо выполнить поиск по разным таблицам, чтобы найти результаты учащегося по определенному предмету, отделу и термину.
• Другая ситуация может быть, когда вам нужно получить два критерия, чтобы найти значение. Например, вы можете потребовать присутствия сотрудника, который успешно получил премию за производительность.
• Другой случай, когда вы можете использовать несколько критериев ВПР в Google Таблицах, — это когда у вас есть несколько данных в одном столбце. Например, ежедневная посещаемость в организации состоит из нескольких деталей, таких как присутствие, отсутствие, отпуск по болезни и т. д.

Его можно использовать во многих других ситуациях, чем мы перечислили здесь. Перейдем к следующему разделу статьи.

Как использовать функцию ВПР с несколькими критериями в Google Таблицах?

Вы можете использовать два метода для использования ВПР с несколькими критериями. Первый метод предполагает использование вспомогательного столбца для получения необходимых значений. Читайте дальше, чтобы увидеть, как этот метод работает для ВПР нескольких критериев в один столбец.

Примечание. Мы будем использовать данные, включающие результаты по математике и естественным наукам из трех семестров, составленные вместе со списком имен учащихся. Используя приведенные ниже методы, мы будем сортировать математические результаты для всех трех элементов отдельно.

Способ 1: Использование вспомогательного столбца

Использование вспомогательного столбца — это один из методов, который можно использовать для ВПР нескольких критериев в один столбец. Этот метод предполагает использование дополнительного столбца, который можно назвать вспомогательным столбцом. В этом случае мы вставим вспомогательный столбец непосредственно перед столбцом имени. Это позволит вспомогательному столбцу стать первым столбцом в диапазоне. Мы будем использовать вспомогательный столбец для объединения значений столбцов Name и Term. Следуйте инструкциям ниже,

Использование вспомогательного столбца:

1. Откройте электронную таблицу Google с необходимыми данными.

2. Вставьте вспомогательный столбец слева от столбца Имя, щелкнув правой кнопкой мыши заголовок того же столбца и выбрав Вставить столбец 1 слева.

3. В первой ячейке вспомогательного столбца введите формулу =C2&””&D2, чтобы объединить ячейки.

4. Нажмите клавишу Enter, чтобы увидеть результат комбинации, т.е. Джон, 1.

5. Перетащите угол ячейки, чтобы заполнить столбец той же формулой.

6. Выберите нужную ячейку, в которую вы хотите ввести значение, и примените формулу ВПР.

7. Введите search_key, который ссылается на ячейки H7 и I6, добавив амперсанды и разделители. Заблокируйте соответствующую ячейку и столбец, нажав клавишу F4.

8. Добавьте запятую (,), чтобы перейти к следующему диапазону синтаксиса. Выбираем столбцы, содержащие значения, для которых нам нужно найти значение ВПР.

9. Заблокируйте значения, нажав F4, и добавьте запятую (,), чтобы перейти к следующему индексу аргумента для ВПР нескольких критериев и столбцов в Google Таблицах.

10. В аргументе index введите номер столбца, который дает вам целевое значение. В данном случае это 4-й столбец. Поэтому введите 4 в значение индекса.

11. Добавьте запятую (,) для передачи аргумента is_sorted. Введите 0, чтобы получить точное совпадение.

12. Закройте скобку и нажмите Ctrl+Enter, чтобы получить нужное значение.

13. Перетащите угол ячейки и примените формулу, чтобы заполнить таблицу.

Этот метод объясняет, как выполнить ВПР несколько критериев и столбцов в Google Таблицах с помощью вспомогательного столбца. Теперь мы увидим, как выполнить ВПР с несколькими критериями в Google Sheets, используя ФОРМУЛУ МАССИВА.

Способ 2: Использование ФОРМУЛЫ МАССИВА

Другой метод — ВПР с несколькими критериями в Google Таблицах с использованием ФОРМУЛЫ МАССИВА. Оба метода работают одинаково, с той лишь разницей, что вам не нужен вспомогательный столбец в этом методе для ВПР нескольких критериев в один столбец. Вместо этого вам нужно будет построить вспомогательный столбец и значения диапазона с помощью формулы. Прочтите приведенные ниже шаги, чтобы понять процесс поиска значения с помощью ВПР с использованием ФОРМУЛЫ МАССИВА. Мы будем использовать тот же пример, который мы использовали в методе, упомянутом выше.

1. Начните с ввода = ФОРМУЛА МАССИВА в нужную ячейку.

2. Введите формулу ВПР.

3. Выберите и заблокируйте эталонные ячейки G7 и H6, нажав клавишу F4. Разделяйте их амперсандами и разделителями.

4. Добавьте запятую (,) и начните строить диапазон, открывая фигурную скобку.

5. Выберите первый столбец, который является столбцом «Имя», и заблокируйте ячейки, нажав клавишу F4.

6. Добавьте разделитель (&””&) и выберите столбец Термин, который мы объединим со столбцом Имя. Заблокируйте выбранные ячейки, нажав клавишу F4.

7. Добавьте запятую (,) и выберите третий математический столбец, который дает целевое значение, и заполните диапазон.

8. Закройте фигурную скобку и добавьте запятую (,), чтобы перейти к следующему аргументу.

9. Введите значение индекса столбца, которое даст вам целевое значение. Здесь мы введем 2, чтобы получить значения из столбца Math.

10. Добавьте запятую (,) и введите 0, чтобы получить точное совпадение, затем закройте скобки, чтобы закрыть формулу ВПР.

11. Снова закройте скобки, чтобы закрыть ФОРМУЛУ МАССИВА.

12. Нажмите клавишу Enter, чтобы получить результат.

13. Перетащите угол ячейки, чтобы получить результат через таблицу.

Вот как вы получаете нужные вам значения, когда вам нужно выполнить ВПР с несколькими критериями в Google Sheets, используя формулу массива.

***

Мы надеемся, что это руководство по ВПР для нескольких критериев и столбцов в Google Таблицах было полезным. Вы можете оставить свои вопросы и предложения по темам для будущих статей в разделе комментариев ниже.

Использование ВПР для нескольких столбцов

ВПР  – это одна из функций поиска и ссылок в Excel и Google Таблицах, используемая для поиска значений в указанном диапазоне по  «строке» . Он сравнивает их по строкам, пока не найдет совпадение. В этом руководстве мы рассмотрим, как использовать функцию ВПР для нескольких столбцов с несколькими критериями.

Синтаксис ВПР:  =ВПР (значение, массив_таблиц, индекс_столбцов, [диапазон_просмотра]) . В общем формате вы можете использовать его для поиска по одному столбцу за раз. Однако изменение формулы позволяет нам использовать функцию ВПР для просмотра нескольких столбцов.

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

Здесь у нас есть набор данных в ячейках B2:D7, который содержит данные о ценах на билеты на разные фильмы в разное время. В ячейке D11 вы вернете цену фильма в ячейке B11 на момент времени в ячейке C11. У B11 есть фильм «Мир Юрского периода 3D». Из данных в B2:C7 есть две записи для фильма в B11 — «Мир Юрского периода 3D». Билет на первое шоу в 15:00 стоит 6 долларов. Второе шоу в 20:00 стоит 8 долларов. В D11 вы будете искать шоу в 20:00 по формуле «=ВПР(B11,$B$3:$D$7,3,ЛОЖЬ)» . Но это не возвращает правильный результат. Чтобы найти точный результат для нескольких столбцов с помощью ВПР, вам потребуется внести некоторые изменения в формулу.

Использование функции ВПР для нескольких столбцов

1. Использование оператора конкатенации («&»):

   Оператор конкатенации («&»)  помогает использовать функцию ВПР для нескольких столбцов для выполнения нескольких условий. В предыдущем примере функция ВПР не смогла вернуть значение для второго экземпляра, удовлетворяющего определенным критериям (например, название фильма и время показа). С помощью оператора конкатенации мы можем указать оба критерия внутри формулы, которая будет извлекать правильную цену билета для нашего желаемого экземпляра. Тем не менее, мы должны убедиться, что поиск по диапазону установлен по умолчанию, то есть TRUE.

   Чтобы узнать правильную цену билета на «Мир Юрского периода 3D» в 20:00:

   • Выберите ячейку D11, щелкнув по ней.
   • Вставьте формулу  "=ВПР(B11&C11,$B$3:$D$7,3)" .

   • Нажмите Enter, чтобы применить формулу к ячейке D11.

   На этот раз возвращает результат $8.00. Глядя на данные в ячейке D7, вы можете подтвердить, что это правильная Цена на фильм «Мир Юрского периода 3D» в «20:00».

2. Использование ИНДЕКС/ПОИСКПОЗ:

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

   Чтобы решить проблему из предыдущего примера с ИНДЕКС()/ПОИСКПОЗ(), необходимо выполнить следующие шаги:

   • Выберите ячейку D11, щелкнув по ней.
   • Вставьте формулу  "=ИНДЕКС(D:D,ПОИСКПОЗ(1,(B:B=B11)*(C:C=C11),0)"  в ячейку D11.

   • Нажмите Ctrl + Shift + Enter , чтобы вставить формулу и превратить ее в формулу массива. Нажатие ввода приведет к ошибке #Н/Д. Вы должны нажать Ctrl + Shift + Enter , чтобы убедиться, что формула возвращает правильное число.

   Функция ИНДЕКС() возвращает значение из столбца D на основе соответствия функции ПОИСКПОЗ с критериями B:B = B11 и C:C = C11. «1» здесь относится к ИСТИННОМУ, поскольку возвращает номер строки, в которой все критерии ИСТИННЫ.

   VLOOKUP — очень эффективная функция поиска и поиска. Тем не менее, у него есть некоторые ограничения при поиске данных в нескольких столбцах на основе нескольких критериев. Однако вы можете преодолеть эти ограничения, используя некоторые модификации формулы. Использование оператора конкатенации («&») или комбинации функций ИНДЕКС() и ПОИСКПОЗ() позволяет извлекать значения из нескольких столбцов.

   Функция ВПР на основе нескольких критериев неприменима. Однако иногда проблема заключается в настройке ваших данных. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш сервис Excel Live Chat! Наши специалисты по Excel доступны 24 часа в сутки, 7 дней в неделю, чтобы ответить на любой ваш вопрос об Excel прямо на месте. Первый вопрос бесплатный.

Вам все еще нужна помощь с функцией ВПР? Ознакомьтесь с нашим подробным обзором руководств по функциям ВПР здесь .

excel — Поиск по нескольким критериям со значениями True/False if All Lookups True

Я думаю, что мой мозг запутался в значениях if/vlookup. Как мне получить значения True или False, например, для h4: H6? У меня есть список пользователей (столбец A) с ролями (столбец b) и требования курса для ролей (столбец c). Мне нужно знать, прошел ли пользователь все курсы для своих указанных ролей, а не только, прошел ли пользователь все курсы. У меня есть значения True/False для того, какие курсы они прошли. т. е. прошел ли пользователь 1 с ролью 1 все назначенные ему курсы для роли 1.

Как мне это сделать?

• Excel
• vlookup
• несколько условий

1

Вы можете использовать IfError для этого, если вы измените курсы на столбцы, а затем вернете значение для каждого. Так как это будет через ошибку, если один курс будет пустым, тогда он выйдет из строя. =ЕСЛИОШИБКА(ВПР(F3,A:D,3,ЛОЖЬ),ЕСЛИОШИБКА(ВПР(F3,A:D,2,ЛОЖЬ),»Курсы не завершены»))

Ответ

Следующая формула, помещенная в ячейку h4 и автозаполненная в ячейках ниже, должна дать желаемый результат.

 = НЕ(ИЛИ(СЧЁТЕСЧЕТЫ($A$3:$A,F3,$B$3:$B,G3),СЧЁТЕСЛИМН($A$3:$A,F3,$B$3:$B,G3,$D $3:$D, ЛОЖЬ)))
 

Объяснение

Первый СЧЁТЕСЛИМН проверяет, есть ли в наборе данных хотя бы одна строка с тем же пользователем и ролью, которые указаны в столбцах F и G. Второй СЧЁТЕСЛИМН проверяет, есть ли какие-либо совпадающие столбцы, в которых хотя бы один ЛОЖЬ появляется в столбце D (указывая, что курс не завершен).

Используя эти два условия, желаемая таблица истинности выглядит следующим образом:

* Обратите внимание, что этот сценарий невозможен.

Эта таблица истинности совпадает с таблицей истинности вентиля НЕ-ИЛИ. Оборачивая функции COUNTIF в оператор NOR (комбинация функций NOT и OR), вы получаете желаемый результат: только когда является совпадающим столбцом, но не является неполным курсом, функция возвращает значение TRUE. .

Скриншот конечного результата (в Google Sheets, но все должно быть идентично)

Используемые функции:

• НЕ
• ИЛИ
• СЧЁТЕСЛИ

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.