Поиск значения по нескольким условиям в excel: Поиск в excel по нескольким условиям в excel

Содержание

Поиск в Экселе – комбинация клавиш, какая формула соответствует, как искать по нужному слову, фразе, одному или нескольким значениям в Excel

Excel – программа для работы с электронными таблицами. Документы в этой программе могут быть объемными с множеством листов. Поэтому для удобного поиска нужного слова, фразы, таблицы, ячейки или формулы существует специальный инструмент, пользоваться которым несложно.

Поиск в Excel

Самый простой и быстрый способ искать по слову, фразе, формуле и т.д в документе это использовать комбинацию клавиш Ctrl и F одновременно. Появится следующее окно.

Здесь понадобится ввести искомое слово, фразу и т.д.

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

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

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

В поле можно использовать подстановочные знаки * и ?.

Обычно их применяют в таких ситуациях:

  1. * — может быть использована для замены букв. К примеру, если ввести «к*т», то найдутся все слова, которые начинаются на букву «к» и заканчиваются на «т», кит, кот и т.д. Звездочка заменяет как один символ, так и любое количество. Т.е. в нашем примере найдутся и слова кабинет, кабриолет и т.д.
  2. ? — такой подстановочный знак заменяет только одну букву. Например, «к?т», выйдут слова, которые состоят из трех букв «кот», «кит».

Настройки поиска

Если необходимо установить определенные настройки, то в окне необходимо кликнуть «Параметры».

Тут можно задать следующие настройки:

  • в строке «Искать» можно ограничить поиск только листом;
  • в строке «Просматривать» можно выбрать вариант просмотра по строкам или по столбцам;
  • строка «Область» позволяет выбрать искомые символы: формула либо значение;
  • отметив галкой «Учитывать регистр» производится нахождение искомой комбинации с учетом регистра;
  • если необходимо увидеть ячейку, которая содержит только заданные символы, нужно отметить «Ячейка целиком»;
  • во вкладке «Формат» можно выбрать искомое форматирование.

Нажимая кнопку «Заменить», можно сразу заменить найденное на указанные значения.

В строку «Найти» нужно ввести искомую комбинацию значений, а в строке «Заменить» те символы, на которые следует заменить найденные.

Поиск по указанному интервалу

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

Этим способом ограничивается диапазон для нахождения комбинации.

Порядок действий:

  1. Выделяем диапазон, для которого нужно определить значения.
  2. Запускаем поиск любым способом, описанным выше. Отличие лишь в том, что изначально для работы будет выделен определенный диапазон.

Поиск по нескольким условиям

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

Приведем пример поиска нескольких значений одновременно. В таблице указана разная цена различного гранита. Необходимо с помощью функции ВПР определить цену гранита 7.

Составим таблицу.

Формула будет в ячейке Е4, но можно использовать любую другую. Затем придерживаемся алгоритма:

  1. В ячейке ставим знак равенства и прописываем функцию: «=ВПР(».
  2. Аргумент 1 это Гранит7. Пишем «ВПР(«Гранит7»».
  3. Аргумент 2 – это ячейки А4:А20. Формула получается такой: «ВПР(«Гранит7»; А4:А20;».
  4. Аргумент 3, цифра 2, т.к. ищем значение во втором столбце. «ВПР(«Гранит7»; А4:А20;2».
  5. Аргумент 4, дает понять функции, какое значение нужно искать, точное или приблизительное. Оно может быть ИСТИНА или ЛОЖЬ. ИСТИНА – приблизительно совпавшие значения, а ЛОЖЬ – точные. Мы ищем точные. Выглядит запись так: «ВПР(«Гранит7»; А4:А20;2;ЛОЖЬ)» или «=ВПР(A10;A4:B20;2;ЛОЖЬ)».
  6. Кликаем по Enter и получаем искомое.

Аналогичным способом можно добавлять условия в более сложных таблицах.

К примеру, добавим в таблицу столбец с месяцем. Попробуем увидеть в каком месяце  Гранит17 стоит 1016.

Составляем формулу, добавляя новые значения. Аргументом 3 будет цифра 3.

Нажимаем ввод и получаем  месяц «май».

Функция поиск в Excel

Также для обнаружения можно использовать функции ПОИСК и НАЙТИ.

Сначала рассмотрим функции НАЙТИ. С ее помощью можно автоматически определить позицию в текстовой строке, она различает прописные и строчные буквы.

Пример: найдем положение буквы «а» в слове Гранит6. Введем формулу =НАЙТИ(«а»;A9;3) и получим 3, т.е. «а» третья буква.

Теперь рассмотрим функцию ПОИСК. В данной формуле можно использовать подстановочные символы (? — один, * — несколько). Например, найдем в той же ячейке положение цифры 6. Введем формулу =ПОИСК(«6»;A9), получаем 8.

Excel выделение цветом ячеек по условиям, Эксель условное форматирование

Как сделать «красиво в Excel»? Основные уловки

Ищем пропажу. В Excel пропали листы или лента, панель команд?

Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:

Основные возможности я описал в начале статьи, но на самом деле их масса. Подробнее о самых полезных

Содержание

  • Условное форматирование, где найти?
  • Excel выделение цветом ячеек по условиям. Простые условия
  • Выделение повторяющихся значений, в т.ч. по нескольким столбцам
  • Выделение цветом первых/последних значений. Опять же условное форматирование
  • Построение термальной диаграммы и гистограммы
  • Выделение цветом ячеек, содержащих определенный текст
  • Excel выделение цветом. Фильтр по цвету
  • Проверка условий форматирования
  • Похожие статьи

Условное форматирование, где найти?

Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование.

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите, возможностей здесь действительно много.

Теперь подробнее о самых полезных:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:

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

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

Чтобы выделить все повторяющиеся значения выберите соответствующее меню Повторяющиеся значения.

Далее снова появиться окошко с форматированием. Настройте как вам удобно. Можно выделить, например, только уникальные. Значения и курсивом (пользовательский формат)

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ(), т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже легко сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться, то Excel сочтет такие строки неповторяющимися (например, ИванИванычИванов).

Выделение цветом первых/последних значений. Опять же условное форматирование

Для этого зайдите в пункт Правила отбора первых и последних ячеек и выберите нужный пункт. Помимо того, что можно выделить первые/последние значения (в том числе и по процентам), можно использовать возможность выделить данные выше и ниже среднего (пользуюсь даже чаще). Очень удобно для просмотра результатов отличающихся от нормы или среднего!

Построение термальной диаграммы и гистограммы

Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том, что в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета, чем больше, тем краснее, например. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов зачастую — это наш глаз, соответственно, мозг, а не машина!

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

Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.

Выделение цветом ячеек, содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК(), но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

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

Excel выделение цветом. Фильтр по цвету

Помимо вышеперечисленных возможностей вы можете отфильтровать выделенные ячейки по цвету обычном фильтром. К моему удивлению об этом очень мало кто знает — видимо отголоски версии 2003 — там этой возможности не было.

Подробнее о фильтрах в этой статье.

Проверка условий форматирования

Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).

Неверный диапазон условного форматирования

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel. Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.

Подробнее о тормозах Excel и их причинах читайте здесь. Эта статья помогла не одной сотне людей 😉

Надеюсь был полезен, не прощаюсь!

Как сделать «красиво в Excel»? Основные уловки

Ищем пропажу. В Excel пропали листы или лента, панель команд?

Поиск по нескольким критериям Excel ИНДЕКС ПОИСКПОЗ или ФИЛЬТР

Как выполнить поиск в Excel с 2 или более критериями в столбцах с помощью функции ФИЛЬТР (Office 365) или функций ИНДЕКС и ПОИСКПОЗ. Получите образец файла, а затем просмотрите пошаговое видео или следуйте письменным инструкциям.

Автор: Дебра Далглиш

Поиск в Excel по 2 критериям

Существует множество способов поиска в Excel по двум критериям. На этой странице вы увидите, как выполнить поиск в Excel с двумя критериями. используя либо:

  1. ИНДЕКС/ПОИСКПОЗ функций (все версии Excel)
  2. или функция ФИЛЬТР (Excel 365)

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

После выбора решения ниже приведены подробные инструкции по настройке функции ФИЛЬТР или функций ИНДЕКС и ПОИСКПОЗ в Excel.

    Поиск в Excel по 2 критериям

    Использовать ФИЛЬТР или ИНДЕКС/ПОИСКПОЗ ?

    Видео: поиск в Excel с 2 критериями

    ИНДЕКС и ПОИСКПОЗ

    —Простой ИНДЕКС СОВПАДЕНИЕ

    —Как работает простая формула ПОИСКПОЗ ИНДЕКС

    ИНДЕКС/ПОИСКПОЗ 2 Критерий

    —ПОИСКПОЗ Верно или неверно

    —MATCH Оба Верно

    —Поиск по нескольким критериям  

    —Как работает сложная формула

    Функция ФИЛЬТР

    Получить образец файла

Следует ли использовать ФИЛЬТР или ИНДЕКС/ПОИСКПОЗ?

Чтобы помочь вам решить, какое решение использовать для поиска Microsoft Excel с несколькими критериями, вот основные различия между ними.

Функции ИНДЕКС/ПОИСКПОЗ
  • Эти две функции доступны во всех версиях Excel
  • Если имеется несколько результатов для критериев, возвращается первый результат из диапазона
  • Формула с несколькими критериями должна быть введена в виде массива, если в версии Excel нет динамических массивов
Функция ФИЛЬТР
  • Эта функция доступна, только если в вашей версии Excel есть динамические массивы (Office 365). Убедитесь, что у других людей, которым необходимо использовать книгу, также есть динамические массивы
  • Если для критерия имеется несколько результатов, результаты будут распределены по строкам ниже, чтобы отобразить все элементы.
  • Не нужно вводить массив
Другие решения

Кроме того, существуют другие способы поиска в Microsoft Excel с несколькими критериями. Взгляните на примеры на следующих страницах с примерами функций ПРОСМОТР и ВПР в Excel:

— Функция Excel ПРОСМОТР для нескольких критериев

— Формула Excel VLOOKUP для поиска по 2 столбцам

См. также дополнительные сведения о различных функциях поиска в Excel.

Видео: поиск в Excel по 2 критериям

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

1) Поиск в Excel с несколькими критериями : Показывает, как 9Функции 0024 ИНДЕКС и ПОИСКПОЗ работают вместе с одним критерием. Далее на отметке 1:50 формула меняется, для работы с 2-мя критериями. Чтобы следовать видео, загрузите образец файла.

2) Начало работы с функцией ФИЛЬТР Excel : Показывает, как работает функция ФИЛЬТР , с одним критерием. Третий пример на отметке 6:00 показывает функцию ФИЛЬТР с двумя критериями. Чтобы следовать видео, загрузите образец файла.

ИНДЕКС и ПОИСКПОЗ

Чтобы выполнить поиск в Excel по нескольким критериям, вы можете использовать функции ИНДЕКС и ПОИСКПОЗ.

  • Функция ИНДЕКС может возвращать значение из определенного места в списке
  • Функция ПОИСКПОЗ может найти положение элемента в списке.

Когда ИНДЕКС и ПОИСКПОЗ используются вместе, они создают гибкую и мощную формулу поиска.

Простой ИНДЕКС и ПОИСКПОЗ

Прежде чем использовать ИНДЕКС и ПОИСКПОЗ с несколькими критериями, давайте посмотрим, как они работают вместе в простой формуле.

В приведенной ниже формуле нам нужно найти «Свитер» в столбце B прайс-листа и получить его цену из столбца C на том же листе.

  • Критерий товара вводится в ячейку A7 – Свитер
  • Эта формула ИНДЕКС и ПОИСКПОЗ вводится в ячейку C7, чтобы получить цену на этот товар:

=ИНДЕКС($C$2:$C$4,ПОИСКПОЗ(A7,$B$2:$B$4,0))

Формула возвращает правильную цену свитера — 10 .

Как работает простая формула ПОИСКПОЗ ИНДЕКС

Вот как простая формула ИНДЕКС/ПОИСКПОЗ определяет правильную цену свитера.

  1. Сначала функция ПОИСКПОЗ находит позицию «Свитер» в ячейках B2:B4 и возвращает это число
  2. Затем функция ИНДЕКС возвращает цену из этой строки в ячейках C2:C4

Таким образом, комбинируя ИНДЕКС и ПОИСКПОЗ, вы можете найти расположение элемента в строке и вернуть цену из этой строки.

Вот подробности функции:

1) СПИЧКА

Синтаксис функции ПОИСКПОЗ имеет 3 аргумента:

  • lookup_value : Какое значение вы хотите найти в массиве поиска?
  • lookup_array : Где массив поиска?
  • [тип_соответствия] : (необязательно) Должен ли ПОИСКПОЗ найти точное совпадение (0) для искомого значения или близкое совпадение (1 или -1)?

В этом примере функция ПОИСКПОЗ ищет значение из ячейки A7 в диапазоне B2:B4 с типом точного соответствия.

  • Результат равен 1, так как «Свитер» находится в первой строке этого диапазона.
2) ИНДЕКС

Функция ИНДЕКС имеет 3 аргумента:

  • массив : Где находится массив?
  • row_num : Какая строка имеет возвращаемое значение?
  • [номер_столбца] : (необязательно) В каком столбце есть значение, которое вы хотите вернуть?

В этом примере функция ИНДЕКС возвращает 10 — цену из строки 1 в диапазоне C2:C4

ИНДЕКС/ПОИСКПОЗ 2 Критерии

В предыдущем примере совпадение основывалось на одном критерии — имени элемента. Для поиска следующего критерия есть 2 критерия — предмет и размер.

В этой таблице поиска цен каждый товар указан 3 раза — по одному разу для каждого размера. Мы хотим найти цену для определенного товара и размера.

  • Пункт : Куртка — введено в ячейку C13
  • Размер : Большой — вводится в ячейку D13

Цена большого жакета составляет 40, что должно быть результатом, если в ячейку E13 ввести формулу ИНДЕКС/ПОИСКПОЗ.

Примечание : Цифры в столбце А приведены только для информации — они не используются в формулах

ИНДЕКС/МАТЧ Формула 2 Критерии

Чтобы рассчитать цену на основе 2 критериев, введите этот массив * ИНДЕКС и формулу ПОИСКПОЗ в ячейку E13. Формула поясняется ниже.

Примечание . В Excel 365, в котором есть функции переполнения, вы можете просто нажать Enter. Функция массива не требуется

  • =ИНДЕКС( E2:E10 ,
    ПОИСКПОЗ(1,
    (C13=$C$2:$C$10) * (D13=$D$2:$D$10),0))

* Ввод массива — нажмите Ctrl + Shift + Enter вместо того, чтобы просто нажать клавишу Enter. Это автоматически добавит фигурные скобки вокруг формулы массива.

Как работает формула двух критериев

Вот как работает формула ИНДЕКС ПОИСКПОЗ с несколькими критериями.

ИНДЕКС Функция

Цены продуктов указаны в ячейках E2:E10 , и ИНДЕКС вернет цену из этого диапазона.

  • =ИНДЕКС( E2:E10 ,
Функция ПОИСКПОЗ — найти строку

Функция ПОИСКПОЗ сообщает ИНДЕКСу, какой номер строки использовать в этом диапазоне.

Примечание : этот номер может отличаться от номера строки рабочего листа.

1) В функции ПОИСКПОЗ первый аргумент искомое_значение равен 1

.

  • СПИЧКА(1 ,

2a) Для второго аргумента lookup_array , есть 2 теста:

  • — «Жакет» в столбце «Элемент» — ИСТИНА (1) или ЛОЖЬ (0)
    • (C13=$C$2:$C$10)
  • в столбце «Размер» указано значение «Большой» — ИСТИНА или ЛОЖЬ.
    • (D13=$D$2:$D$10)

2b) Затем эти результаты ИСТИНА и ЛОЖЬ перемножаются, чтобы получить нули и единицы

2c) В этом примере только 8-й продукт соответствует обоим критериям, куртка и большой, и возвращает 1

  • Если аргумент lookup_array оценивается в строке формул, на 8-й позиции массива стоит 1.

3) Третий аргумент в ПОИСКПОЗ, match_type , равен нулю, чтобы вернуть точное совпадение.

ИНДЕКС/ПОИСКПОЗ Результат

Таким образом, результатом функции ПОИСКПОЗ является 8, потому что она нашла точное совпадение для искомого значения 1 на 8-й позиции в массиве поиска

На основе этого результата ИНДЕКС возвращает цену для 8-го продукта в таблице поиска цен.

Функция ФИЛЬТР

Если в вашей версии Excel есть динамические массивы (Office 365), вы можете использовать новую функцию ФИЛЬТР, чтобы вернуть нужные вам результаты. Функция ФИЛЬТР позволяет вам возвращать результаты из диапазона на основе ваших критериев.

В этом примере имеется именованная таблица tblProducts с 4 столбцами — код, товар, размер и цена.

Ячейки критериев

В верхней части листа были введены два критерия для спецификаций продукта

  • ячейка А2 — артикул товара: Куртка
  • ячейка В2 — размер позиции для товара: Большой

Основываясь на этих критериях, нам нужны два результата — Цена продукта и буквенно-цифровой код продукта.

Получить цену продукта с функцией FILTER

Во-первых, в ячейке C2 мы введем формулу FILTER динамического массива для расчета цены продукта на основе критериев в ячейках A2 и B2.

Функция ФИЛЬТР имеет 3 аргумента:

  1. массив
  2. включает
  3. if_empty (необязательно)
1) Аргумент массива

Цены на товары хранятся в этом диапазоне:

  • в таблице с именем tblProduct
  • в столбце с именем Цена .

Итак, для начала формулы,

  • Введите знак равенства: =
  • Введите имя функции, ФИЛЬТР
  • Тип открывающей скобки — (
  • На рабочем листе щелкните вверху ячейки заголовка в столбце Цена

Excel автоматически введет ссылку на столбец «Цена» в структурированной таблице.

Формула должна выглядеть так:

  • =ФИЛЬТР(таблицаПродукты[Цена],
2) Включить аргумент

Для аргумента include формула должна проверять столбец Item, чтобы увидеть, соответствует ли он ячейке A2

  • (tblProducts[Item]=A2)

И формула должна проверить столбец Размер, чтобы увидеть, соответствует ли он ячейке B2

  • (tblProducts[Size]=B2)

Оба критерия находятся в аргументе включения, между ними стоит оператор умножения (*)

=ФИЛЬТР(tblProducts[Цена], (tblProducts[Item]=A2) * (tblProducts[Size]=B2))

Код продукта с ФИЛЬТРОМ

Формула FILTER для возврата кода продукта почти такая же, но для аргумента массива нам нужен результат из столбца Code:

=ФИЛЬТР(tblProducts[Код], (tblProducts[Элемент]=A2) * (tblProducts[Размер]=B2))

Получить образец файла

  • ИНДЕКС/СООТВЕТСТВИЕ : Чтобы посмотреть видео ИНДЕКС/ПОИСКПОЗ, загрузите заархивированный пример файла «Поиск по нескольким критериям». Рабочая книга имеет формат xlsx и не содержит макросов.
  • Примеры функций FILTER : Чтобы увидеть четыре примера FILTER, загрузите FILTER
    рабочая тетрадь с примерами функций     . Файл заархивирован и
    в формате Excel xlsx, без макросов.

Другие уроки

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

Сравните функции поиска — как выбрать лучшую функцию поиска в Excel. Сравните ВПР, ГПР, ПРОСМОТР, ИНДЕКС, ПОИСКПОЗ и СМЕЩЕНИЕ

Функция ВПР. Используйте ВПР для определения цены продукта или оценок учащихся. Узнайте, как найти и устранить проблемы с формулой ВПР с помощью устранения неполадок

.

Последнее обновление: 10 июля 2022 г., 15:05

7 Способы поиска значения на основе нескольких критериев или условий в Excel

Сценарий

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

Образец файла

Пример загрузки. :C10=D13)*(D3:D10))

2. СУММА (ФОРМУЛА МАССИВА)



ФОРМУЛА :

=СУММ((B3:B10=D12)*(C3:C10=D13)*(D3:D10))

Попадание CTRL+SHIFT+ENTER для подтверждения этой формулы. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

{=СУММ((B3:B10=D12)*(C3:C10=D13)*(D3:D10))}

3. СУММЕСЛИМН

.

ФОРМУЛА:

=СУММЕСЛИМН(D3:D10,B3:B10,D12,C3:C10,D13)

Эта формула не работает в Excel 2003 и более ранних версиях.

4. ИНДЕКС-СООТВЕТСТВИЕ



ФОРМУЛА :

=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,(B3:B10=D12)*(C3:C10=D13),0))

Нажмите 7 CTRL+905   для подтверждения этой формулы. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

{=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,(B3:B10=D12)*(C3:C10=D13),0)) }

5. ИНДЕКС-ПОИСКПОЗ (Версия 2)

ФОРМУЛА :

=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,ЕСЛИ(B3:B10=D12,ЕСЛИ(C3:C10=D13,1) ),0))

Нажмите  CTRL+SHIFT+ENTER  , чтобы подтвердить эту формулу. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {. ..}.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

{=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,ЕСЛИ(B3:B10=D12,ЕСЛИ(C3:C10=D13,1)) ,0))}

6. ПРОСМОТР



ФОРМУЛА :

=ПРОСМОТР(2,1/(B3:B10=D12)/(C3:C10=D13),(D3:D10))

ЕСЛИ НЕ НАЙДЕНО 7



ФОРМУЛА :

=ЕСЛИОШИБКА(ИНДЕКС(D3:D10,СООТВЕТСТВИЕ(1,(B3:B10=D12)*(C3:C10=D13),0)),»Нет совпадения»)

Эта формула возвращает «Нет соответствия», если значение не существует на основе условий.
Попадание CTRL+SHIFT+ENTER   для подтверждения этой формулы. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

{=ЕСЛИОШИБКА(ИНДЕКС(D3:D10,ПОИСКПОЗ(1,(B3:B10=D12)*(C3:C10=D13),0) )),»Нет совпадения»)}

Скачать книгу

Об авторе:

Deepanshu основал ListenData с простой целью — сделать аналитику простой для понимания и использования.

Читайте также: