0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро извлечь строки, соответствующие критериям в Excel?

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Четыре способа использования ВПР с несколькими условиями

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

Поэтому мы рассмотрим четыре способа создания функций подстановочных знаков с двумя условиями:

  1. Использование дополнительного столбца
  2. Использование функции SELECT для создания новой таблицы представления
  3. Использование функций ИНДЕКС и ПОИСК
  4. Использование функции ПРЕДЛОЖЕНИЯ СУММ.

Давайте начнем с самого простого.

Использование дополнительной колонки

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

Давайте рассмотрим классический пример. Имеется таблица с данными о продажах по месяцам и городам. Необходимо рассчитать значение продаж по двум условиям: Февраль и Самара.

Исходная-таблица

В своем традиционном виде функция возвращает значение, соответствующее одному условию, что не подходит для наших целей. Мы сможем решить эту проблему, объединив значения столбцов «Месяц» и «Город» в одно. Создание этой формулы требует, чтобы мы написали =B2&C2 в ячейке A2 и распространили эту формулу на ячейку A13. С помощью этой новой информации мы можем вычислить значение столбца A в нужном формате. Запишите формулу в ячейке G3:

Эта формула объединяет два условия в ячейках G1 и G2 в одну строку и осуществляет поиск в столбце A. Как только правильное условие найдено, формула возвращает значение четвертого столбца таблицы A1:D13, т.е. столбца Sales.

Читайте так же:
Как в Excel скопировать только форматирование из одного диапазона в другой?

Дополнительный-столбец

Использование функции ВЫБОР для создания новой таблицы просмотра

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

Использование функции SELECT предполагает создание новой таблицы для просмотра, в которой значения столбцов Month и City уже объединены. Наша формула будет выглядеть следующим образом:

Основной момент данной формулы заключается в части ВЫБОР(<1;2>;B2:B13&C2:C13;D2:D13), который делает две вещи:

  1. Объединяет значения в столбцах «Месяц» и «Город» в одну таблицу: Январь-Москва, февраль-Москва …
  2. Объединяет две таблицы в таблицу с двумя столбцами.

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

Массив-для-просмотра

Формула стала более понятной.

Пожалуйста, нажмите Ctrl+Shift+Enter после ввода формулы массива в последней строке, чтобы сообщить программе о наших намерениях. При нажатии этой комбинации клавиш программа помещает фигурные скобки в начало и конец формулы.

Использование функций ИНДЕКС и ПОИСКПОЗ

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

Вот как это выглядит.

Давайте разберем каждый компонент формулы.

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). Значение ячейки G1 последовательно сравнивается со значениями ячеек B2:B13 и возвращается TRUE, если значения совпадают, FALSE в противном случае. Такое же сравнение производится со значениями ячеек G2 и диапазона C2:C13. Далее мы сравниваем два массива, включающие значения TRUE и FALSE. 1 (TRUE) — это результат TRUE + FALSE. Давайте посмотрим на следующий рисунок, который поможет более наглядно объяснить принцип.

Массив-для-просмотра

Таким образом, мы можем определить, где находится строка, удовлетворяющая обоим условиям. Поскольку первая строка встречается в шестой строке, функция SEARCH находит позицию 1 в массиве и возвращает значение 6. Далее INDEX возвращает значение шестой строки в диапазоне D2:D13.

Использование СУММПРОИЗВ

U MPROIZE — одна из самых мощных формул в Excel. Ей посвящена даже отдельная статья. Наш четвертый способ использования множественных условий — написать формулу с функцией СУММПРОИЗВ. И выглядеть она будет следующим образом:

Читайте так же:
Как автоматически фильтровать строки на основе значения ячейки в Excel?

Принцип работы этой формулы аналогичен предыдущему подходу. Создается виртуальная таблица, в которой значения в ячейках G1 и G2 сравниваются с интервалами B2:B13 и C2:C13 соответственно. Затем эти две таблицы сравниваются и формируется массив из единиц и нулей, где единица присваивается строке, в которой совпадают два условия. Затем этот виртуальный массив умножается на интервал D2:D13. Поскольку наш виртуальный массив имеет только одну единицу в шестом ряду, формула даст результат 189.

Эта функция не может использоваться, если значения текста находятся в диапазоне D2:D13.

Я рекомендую прочитать статью о функции СУММПРОИЗ, чтобы понять, как работает эта формула.

Какой метод мне выбрать? Хотя все эти методы работают стабильно, я предпочитаю первый. В своей повседневной работе я предпочитаю работать с файлами, которые легко понять и можно изменить. Оба удовлетворяют первому подходу.

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

Вам также могут быть интересны следующие статьи

11 комментариев

А я пользовался только одной — самой простой.
Автору спасибо за обзор! Очень часто приходится пользоваться в работе этой функцией.

Как быстро извлечь строки, соответствующие критериям в Excel?

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

Что необходимо сделать

Точное совпадение значений по вертикали в списке

Это можно сделать с помощью функции VLOOKUP или сочетания функций INDEX и findpoz.

Читайте так же:
Как быстро перейти к концу столбца или строки в Excel?

Примеры ВРОТ

Пример 1 функции ВПР

Пример 2 функции ВПР

Более подробную информацию см. на этой странице.

Примеры индексов и совпадений

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

S EARCH (первое значение «Капуста» в массиве B2:B10) = INDEX(необходимо вернуть значение из C2:C10, которое совпадает со значением из C2:C10)

Формула ищет в C2:C10 первое значение, соответствующее значению «Ольга» (в B7) и возвращает значение в C7(100), которое является первым значением, соответствующим «Ольга».

Подыыывка значений по вертикали в списке с помощью приблизительного совпадения

Используйте для этого функцию VLVP.

Важно: Убедитесь, что значения в первой строке отсортированы в порядке возрастания.

Пример формулы ВЛП, которая ищет приблизительное совпадение

B ROT ищет студента, чье имя имеет 6 совпадений в диапазоне A2:B7. Строка для 6 просмотров не существует в таблице, поэтому BROT вычисляет следующее наибольшее совпадение, которое меньше 6. Он находит 5, связанных с именем Виктор, и возвращает His.

Более подробную информацию можно найти в этом уведомлении.

Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения

Для этого воспользуйтесь функциями SHIFT и HELP.

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

Пример функций OFFSET и MATCH

Ячейка в диапазоне (также называемая начальной ячейкой) — C1.

M ATCH(«Orange»;C2:C7;0) ищет «Orange» в диапазоне C2:C7. Необходимо исключить запускаемую ячейку из диапазона.

1 — это количество столбцов справа от начальной ячейки, из которой должно быть возвращено значение. В нашем примере возвращается значение в столбце D, Sales.

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию GGPU. См. пример ниже.

Пример формулы ГВП, которая ищет точное совпадение

D VIEWER вернет значение в строке 5 в указанном диапазоне для столбца «Продажи».

Функция D VIEWER предоставляет более подробную информацию.

Читайте так же:
Как быстро рассчитать сверхурочную работу и оплату в Excel?

Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения

Для этого воспользуйтесь функцией GGPU.

Важно: Убедитесь, что значения в первой строке отсортированы в порядке возрастания.

Пример формулы ГВП, которая ищет приблизительное совпадение

Этот пример ищет значение 11000 в строке 3 указанного диапазона. Не найдя 11000, он ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Для получения дополнительной информации посетите раздел о программе просмотра D.

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Эта надстройка больше не будет доступна в Excel 2010. Вместо него появился мастер функций, а также несколько подменю и функция справки (помощь).

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

Щелкните по ячейке в диапазоне.

На вкладке Формулы группы «Решения» в разделе Формулы нажмите кнопку Поиск.

Если команда Summaries недоступна, необходимо воспользоваться мастером в надстройке Subtotal.

Добавление дополнения «Мастер подстройки»

Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.

В окне Управление выберите Надстройки Excel и нажмите Перейти к.

Затем нажмите кнопку OK в поле Мастер подстрок диалогового окна Доступные надстройки.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector