Как выбрать данные из таблицы Excel в другую таблицу по условию
Обновлено: 21.11.2024
Если вы работаете с большой таблицей и вам нужно найти в Excel уникальные значения, соответствующие конкретному запросу, то вам нужно использовать фильтр. Но иногда нам нужно выбрать все строки, которые содержат определенные значения по отношению к другим строкам. В этом случае следует использовать условное форматирование, которое относится к значениям ячеек с запросом. Для получения максимально эффективного результата в качестве запроса будем использовать выпадающий список. Это очень удобно, если вам нужно часто менять один и тот же тип запроса для отображения разных строк таблицы. Ниже мы подробно рассмотрим: как сделать выбор повторяющихся ячеек из выпадающего списка.
Выбор уникальных и повторяющихся значений в Excel
В этой таблице нам нужно выделить цветом все транзакции для конкретного клиента. Для переключения между клиентами мы будем использовать выпадающий список. Поэтому в первую очередь нужно подготовить контент для выпадающего списка. Нам нужны все имена клиентов из столбца А, без повторений.
Прежде чем выбрать уникальные значения в Excel, нам нужно подготовить данные для раскрывающегося списка:
В результате мы получили список данных с уникальными значениями (имена без повторений).
Пришло время создать выпадающий список, из которого мы будем выбирать имена клиентов в качестве запроса.
Прежде чем выбрать уникальные значения из списка, необходимо сделать следующее:
В результате в ячейке B1 мы создали выпадающий список имен клиентов.
Выбор ячеек из таблицы по условию в Excel:
Как работает выбор уникальных значений Excel? При выборе любого значения (имени) из выпадающего списка B1 все строки, содержащие это значение (имя), выделяются в таблице цветом. Чтобы убедиться в этом, в выпадающем списке B1 нужно выбрать другое имя. После этого остальные строки будут автоматически выделены цветом. Такую таблицу теперь легко читать и анализировать.
Принцип автоматического выделения строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет находить уникальные значения в таблице Excel. Если данные совпадают, то формула возвращается к значению ИСТИНА и для всей строки автоматически назначается новый формат. Для того, чтобы формат присваивался всей строке, а не только ячейке в столбце А, используем смешанную ссылку в формуле =$A4.
В этой статье я продемонстрирую несколько методов извлечения или фильтрации записей на основе двух условий, применяемых к одному столбцу в вашем наборе данных. Например, если вы используете формулу массива, результат мгновенно обновится при вводе новых начальных и конечных значений.
Остальные встроенные методы требуют немного больше ручной работы, чтобы применить новые условия, однако они работают быстро. Недостатком формулы массива является то, что она может работать медленно, если вы работаете с огромными объемами данных.
Я также написал статью на тот случай, если вам нужно найти записи, соответствующие одному условию в одном столбце и другому условию в другом столбце. В следующей статье показано, как создать формулу, использующую произвольное количество условий: Извлечь записи, в которых совпадают все критерии, если они не пусты
Эта статья Извлечение записей между двумя датами очень похожа на текущую статью, которую вы сейчас читаете. Даты Excel на самом деле представляют собой числа, отформатированные как даты в Excel. Если вы хотите найти текстовую строку в заданном диапазоне дат, прочитайте эту статью: Фильтрация записей на основе диапазона дат и текстовой строки
Я должен порекомендовать эту статью, если вы хотите выполнить поиск по шаблону по всем столбцам в наборе данных, он также возвращает все совпадающие записи. Если вы хотите извлекать записи на основе критериев, а не числового диапазона, прочитайте эту часть этой статьи.
Что находится на этой странице?
1. Извлечь все строки из диапазона на основе критериев диапазона
[Формула массива]
На рисунке выше показан набор данных в диапазоне ячеек B3:E12, параметры поиска находятся в D14:D16. Результаты поиска находятся в формате B20:E22.
Обновление от 20 сентября 2017 г., уменьшенная формула в ячейке A20.
Формула массива в ячейке A20:
=ИНДЕКС($B$3:$E$12, МАЛЕНЬКИЙ(ЕСЛИ((ИНДЕКС($B$3:$E$12, , $D$16) =$D$14), ПОИСКПОЗ(СТРОКА($B$3:$E $12), СТРОКА($B$3:$E$12)), ""), СТРОКИ(B20:$B$20)), СТОЛБЦЫ($A$1:A1))
1.1 Видео
Посмотрите это видео, чтобы узнать больше о формуле:
1.2 Как ввести эту формулу массива
- Выберите ячейку A20.
- Вставить приведенную выше формулу в ячейку или строку формул.
- Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
- Нажмите Enter один раз
- Отпустить все клавиши
В строке формул теперь отображается формула с начальной и конечной фигурными скобками, если вы правильно выполнили вышеуказанные шаги. Вот так:
Не вводите эти символы самостоятельно, они появляются автоматически.
Теперь скопируйте ячейку A20 и вставьте в диапазон ячеек A20:E22.
1.3 Объяснение формулы массива в ячейке A20
Вы можете продолжить, если выберете ячейку A19, перейдите на вкладку "Формулы" на ленте и нажмите левой кнопкой мыши на кнопку "Вычислить формулу".
Шаг 1. Фильтрация определенного столбца в диапазоне ячеек B3:E12
Функция ИНДЕКС в основном используется для получения одного значения из заданного диапазона ячеек, однако она также может возвращать весь столбец или строку из диапазона ячеек.
Это именно то, что я делаю здесь: номер столбца, указанный в ячейке D16, определяет, какой столбец нужно извлечь.
ИНДЕКС($B$3:$E$12, , $D$16, 1)
ИНДЕКС($B$3:$E$12, , 3, 1)
и возвращает C3:C12.
Получает значение в определенном диапазоне ячеек на основе номера строки и столбца.
Шаг 2. Проверьте, какие значения меньше или равны условию
Знак "меньше" и "равно" — это логические операторы, которые позволяют сравнивать значение со значением, в данном случае, если число меньше или равно другому числу.
Выход представляет собой логическое значение True или False. Их позиции в массиве соответствуют позициям в диапазоне ячеек.
ИНДЕКС($B$3:$E$12, , $D$16, 1) =$D$14)
Оба условия должны быть выполнены, звездочка позволяет нам умножить массивы, означающие И логику.
TRUE * TRUE равно FALSE, все остальные комбинации возвращают False. TRUE * FALSE равно FALSE и т. д.
Логические значения имеют числовые эквиваленты: ИСТИНА = 1, а ЛОЖЬ = 0 (ноль). Они преобразуются при выполнении арифметической операции в формуле.
Шаг 4. Создание числовой серии
Функция СТРОКА вычисляет номер строки ссылки на ячейку.
Шаг 5. Создайте числовую последовательность от 1 до n
Функция ПОИСКПОЗ возвращает относительное положение элемента в массиве или ссылке на ячейку, которое соответствует указанному значению в определенном порядке.
Шаг 4. Возврат соответствующего номера строки
Функция ЕСЛИ возвращает одно значение, если логическая проверка ИСТИНА, и другое значение, если логическая проверка ложна.
ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])
ЕСЛИ((ИНДЕКС($B$3:$E$12, , $D$16) =$D$14), ПОИСКПОЗ(СТРОКА($B$3:$E$12), СТРОКА($B$3:$E$12) ), "")
Шаг 5. Извлечение k-го наименьшего номера строки
Функция НАИМЕНЬШИЙ возвращает k-е наименьшее значение из группы чисел.
МАЛЕНЬКИЙ(ЕСЛИ((ИНДЕКС($B$3:$E$12, , $D$16) =$D$14), MATCH(СТРОКА($B$3:$E$12), СТРОКА($B$3:$E $12)), ""), ROWS(B20:$B$20))
Шаг 6. Возврат всей записи строки из диапазона ячеек
Функция ИНДЕКС возвращает значение из диапазона ячеек, которое вы указываете на основе номера строки и столбца.
ИНДЕКС($B$3:$E$12, МАЛЕНЬКИЙ(ЕСЛИ((ИНДЕКС($B$3:$E$12, , $D$16) =$D$14), MATCH(СТРОКА($B$3:$E$12) ), СТРОКА($B$3:$E$12)), ""), СТРОКИ(B20:$B$20)), СТОЛБЦЫ($A$1:A1))
ИНДЕКС($B$3:$E$12, 1, , 1)
2. Извлечение всех строк из диапазона на основе критериев диапазона - Excel 365
Обновление от 17 декабря 2020 г.: новая функция ФИЛЬТР теперь доступна для пользователей Excel 365. Формула в ячейке B20:
Это обычная формула, однако она возвращает массив значений и автоматически распространяется на ячейки ниже и справа. Microsoft называет это динамическим массивом и разлитым массивом.
Приведенная ниже формула массива предназначена для более ранних версий Excel. Она ищет значения, соответствующие критерию диапазона (ячейки D14 и D15). Формула позволяет изменить столбец для поиска в ячейке D16.
Эту формулу можно использовать с любым размером и формой набора данных. Для поиска в первом столбце введите 1 в ячейке D16.
2.1 Объяснение формулы массива
Шаг 1. Первое условие
Символ меньше чем и знак равенства являются логическими операторами, означающими, что они могут сравнивать значение со значением, а на выходе получается логическое значение.
Шаг 3 – Умножение массивов – Логика И
Шаг 4. Фильтрация значений
ФИЛЬТР($B$3:$E$12, (D3:D12 =D14))
3. Извлечь все строки из диапазона, соответствующие критериям, в один столбец [Формула массива]
Формула массива в ячейке B20 извлекает записи, в которых столбец E равен либо "Юг", либо "Восток".
Следующая формула массива в ячейке B20 предназначена для более ранних версий Excel, чем Excel 365:
=ИНДЕКС($B$3:$E$12, МАЛЕНЬКИЙ(ЕСЛИ(СЧЁТЕСЛИ($E$15:$E$16,$E$3:$E$12), MATCH(СТРОКА($B$3:$E$12), СТРОКА($B$3:$E$12)), ""), СТРОКА(B20:$B$20)), СТОЛБЦ($B$2:B2))
Чтобы ввести формулу массива, введите формулу в ячейку, затем одновременно нажмите и удерживайте клавиши CTRL + SHIFT, а затем один раз нажмите Enter. Отпустите все ключи.
В строке формул теперь отображается формула с начальной и конечной фигурными скобками, указывающими на то, что формула введена успешно. Не вводите фигурные скобки самостоятельно.
3.1 Объяснение формулы в ячейке B20
Шаг 1. Фильтрация определенного столбца в диапазоне ячеек $A$2:$D$11
Функция СЧЁТЕСЛИ позволяет идентифицировать ячейки в диапазоне $E$3:$E$12, который равен $E$15:$E$16.
Шаг 2. Возврат соответствующего номера строки
Функция ЕСЛИ имеет три аргумента, первый из которых должен быть логическим выражением. Если выражение оценивается как ИСТИНА, происходит одно (аргумент 2), а если ЛОЖЬ, происходит другое (аргумент 3).
Логическое выражение было вычислено на шаге 1 , ИСТИНА равна 1, а ЛОЖЬ равна 0 (ноль).
ЕСЛИ(СЧЁТЕСЛИ($E$15:$E$16,$E$3:$E$12), MATCH(СТРОКА($B$3:$E$12), СТРОКА($B$3:$E$12)), " ")
Шаг 3. Найдите k-й наименьший номер строки
МАЛЕНЬКИЙ(ЕСЛИ(СЧЁТЕСЛИ($E$15:$E$16,$E$3:$E$12), MATCH(СТРОКА($B$3:$E$12), СТРОКА($B$3:$E$12)) , ""), СТРОКИ(B20:$B$20))
Шаг 4. Возврат значения на основе номера строки и столбца
Функция ИНДЕКС возвращает значение на основе ссылки на ячейку и номеров столбцов/строк.
ИНДЕКС($B$3:$E$12, МАЛЕНЬКИЙ(ЕСЛИ(СЧЁТЕСЛИ($E$15:$E$16,$E$3:$E$12), MATCH(СТРОКА($B$3:$E$12), СТРОКА ($B$3:$E$12)), ""), СТРОКИ(B20:$B$20)), СТОЛБЦЫ($B$2:B2))
ИНДЕКС($B$3:$E$12, 3, СТОЛБЦЫ($B$2:B2))
и возвращает 3 в ячейке B20.
Рекомендуемое чтение
4. Извлечение всех строк из диапазона на основе нескольких условий — Excel 365
Обновление от 17 декабря 2020 г.: новая функция ФИЛЬТР теперь доступна для пользователей Excel 365. Формула в ячейке B20:
Это обычная формула, однако она возвращает массив значений. Прочитайте здесь, как это работает: фильтрация значений по критериям
Формула автоматически распространяется на ячейки ниже и справа. Microsoft называет это динамическим массивом и разлитым массивом.
4.1 Объяснение формулы массива
Шаг 1 –
Шаг 2 –
ФИЛЬТР($B$3:$E$12, СЧЁТЕСЛИ(E15:E16, E3:E12))
5. Извлечь все строки из диапазона, которые соответствуют критериям, в один столбец [Таблица, определенная Excel]
На изображении выше показан набор данных, преобразованный в таблицу, определенную в Excel, к третьему столбцу таблицы был применен числовой фильтр.
Вот инструкции по созданию таблицы Excel и фильтрации значений в столбце 3.
На изображении выше показана таблица, определенная в Excel. Вот как отфильтровать D между 4 и 6:
6. Извлечь все строки из диапазона, соответствующие критериям, в один столбец [Фильтр]
На изображении выше показаны отфильтрованные записи на основе двух условий: значения в столбце D больше или равны 4 или меньше или равны 6.
Вот как применить стрелки фильтра к набору данных.
Черные стрелки отображаются рядом с каждым заголовком.
Позволяет фильтровать записи на основе условий, применяемых к столбцу D.
7.Извлечь все строки из диапазона, соответствующие критериям, в один столбец
[Расширенный фильтр]
На изображении выше показан отфильтрованный набор данных в диапазоне ячеек B5:E15 с использованием расширенного фильтра, который является мощной функцией Excel.
Вот как применить фильтр:
- Создайте заголовки для столбца, который вы хотите отфильтровать, желательно выше или ниже вашего набора данных.
Ваши фильтры могут исчезнуть, если их разместить рядом с набором данных, потому что строки могут быть скрыты при применении фильтра. - Выберите весь набор данных, включая заголовки.
- Перейдите на вкладку "Данные" на ленте.
- Нажмите левой кнопкой мыши на кнопку "Дополнительно".
- Появится диалоговое окно.
- Выберите диапазон критериев C2:D3, как показано на изображении выше.
- Нажмите левой кнопкой мыши на кнопку OK.
- Первая строка (A1:D1) содержит заголовки. Каждый столбец должен иметь уникальный заголовок. Повторяющиеся заголовки вызовут проблемы при использовании расширенного фильтра.
- Последующие строки содержат данные.
- В базе данных нет пустых строк.
- В конце базы данных есть пустая строка, а справа пустой столбец.
- В этом примере ячейки F1:F2 являются диапазоном критериев.
- Заголовок в F1 точно соответствует заголовку (D1) в базе данных.
- Ячейка F2 содержит критерий. Используется оператор > (больше) с числом 500 (без знака $).
- Выберите ячейку в левом верхнем углу диапазона для извлеченных данных.
- Введите заголовки для столбцов, которые вы хотите извлечь.
- Они должны точно совпадать с заголовками столбцов в данных по орфографии и пунктуации.
- Порядок столбцов может быть другим
- Можно включить любой или все столбцы.
4. Применить расширенный фильтр Excel
Выполните следующие действия, чтобы применить расширенный фильтр
- Выберите ячейку в базе данных.
- На вкладке "Данные" ленты Excel нажмите "Дополнительно", чтобы открыть диалоговое окно "Расширенный фильтр"
- Вы можете отфильтровать список на месте или скопировать результаты в другое место.
- Excel должен автоматически определить диапазон списка. Если нет, вы можете выбрать ячейки на листе.
- Выберите диапазон критериев на листе.
- Если вы копируете в новое место, выберите начальную ячейку для копии
- Примечание. При копировании в другое место все ячейки ниже диапазона извлечения будут очищены при применении расширенного фильтра.
- Нажмите "ОК", чтобы закрыть диалоговое окно "Расширенный фильтр Excel".
Фильтровать уникальные записи
Расширенный фильтр Excel позволяет фильтровать данные на месте или в другом месте. При применении расширенного фильтра вы можете указать, что хотите видеть только уникальные элементы. При выборе этого параметра все дубликаты удаляются из отфильтрованного списка, и в результате получается уникальный список элементов.
Письменные инструкции по этому методу находятся под видео.
Фильтровать уникальные записи
Вы можете использовать расширенный фильтр Excel для извлечения списка уникальных элементов в базе данных. Например, получить список клиентов из списка заказов или составить список проданных товаров. В этом примере уникальный список копируется в другое место, а исходный список не изменяется.
Примечание. Список должен содержать заголовок, иначе первый элемент может дублироваться в результатах.
- Выберите ячейку в базе данных.
- На вкладке "Данные" ленты Excel нажмите "Дополнительно".
- В диалоговом окне "Расширенный фильтр" выберите "Копировать в другое место".
- Для диапазона списка выберите столбцы, из которых вы хотите извлечь уникальные значения.
- Оставьте диапазон критериев пустым.
- Выберите начальную ячейку для копирования в местоположение.
- Добавьте галочку в поле Только уникальные записи.
- Нажмите "ОК".
Дубликаты в уникальных записях
Иногда, если вы запускаете расширенный фильтр только для уникальных записей, вы можете увидеть дубликаты в результатах.
Это может произойти, если в данных есть числа, а Excel обнаруживает скрытые различия между числами.
Чтобы узнать больше об этой проблеме, перейдите на страницу Проблема удаления дубликатов. Вы увидите, почему перечислены дубликаты (точность с плавающей запятой) и как решить проблему с помощью функции ОКРУГЛ.
Видео: удаление дубликатов
В Excel 2007 и более поздних версиях есть инструмент «Удалить дубликаты». В отличие от параметра «Только уникальные записи» расширенного фильтра, при этом исходный список не остается без изменений, а полностью удаляются все повторяющиеся элементы из списка. Остается только первый экземпляр каждого элемента.
Вы можете скачать образец файла, который использовался в этом видео.
Извлечение данных на другой лист
Расширенный фильтр позволяет извлекать данные на другой лист. Посмотрите это видео, чтобы увидеть шаги, а письменные инструкции находятся под видео.
Извлечение данных на другой лист
Если ваши данные Excel находятся на одном листе, вы можете отправить отфильтрованные данные на другой лист, используя расширенный фильтр. В этом примере данные находятся на Листе1 и будут скопированы на Лист2.
- Перейдите к Sheet2 (см. шаги на видео выше)
- Выберите ячейку в неиспользуемой части листа (в данном примере ячейка C4).
- На вкладке "Данные" ленты Excel нажмите "Дополнительно".
- Выберите «Копировать в другое место».
- Нажмите в поле "Диапазон списка".
- Выберите Sheet1 и выберите базу данных.
- (необязательно) Щелкните поле диапазона критериев.
- Выберите диапазон критериев.
- Нажмите в поле Копировать в.
- Выберите ячейку на Листе 2, с которой должны начинаться результаты, или выберите заголовки, введенные на Листе 2.
- (необязательно) Установите флажок "Только уникальные значения".
- Нажмите "ОК".
Настройка диапазона критериев
В этих примерах показано, как настроить диапазоны критериев для одного критерия или нескольких критериев, используя текст, числа и подстановочные знаки. Вы также можете использовать формулы в диапазоне критериев — см. примеры на этой странице: Расширенные фильтры — сложные критерии
ВНИМАНИЕ! При вводе текстового критерия Excel интерпретирует его как "начинается с". Если вам нужно точное совпадение, см. пример точного совпадения ниже.
Расположение диапазона критериев
В примерах на этой странице рядом с данными показан диапазон критериев, но это не обязательно.
- Диапазон критериев может находиться на том же листе, что и данные, или на другом листе.
- Диапазон критериев может располагаться непосредственно над данными или в столбцах слева или справа от данных.
Если диапазон критериев находится на том же листе и в тех же строках, что и данные, ячейки критериев могут быть скрыты при применении расширенного фильтра "На месте".
Это не повлияет на расширенный фильтр — вы просто не сможете увидеть некоторые критерии.
И против ИЛИ
Если запись соответствует всем критериям в одной строке в области критериев, она пройдет через расширенный фильтр Excel.
Правило И
В этом примере есть одна строка правил критериев:
- имя клиента должно начинаться с "МегаМарт"
- И название продукта должно начинаться с «Cookies».
- И общее количество должно быть больше 500
ИЛИ правила
В этом примере есть 3 строки с правилами критериев.
Критерии в разных строках соединяются оператором ИЛИ. Во втором примере --
- имя клиента должно начинаться с "МегаМарт"
- ИЛИ название продукта должно начинаться с «Cookies»
- ИЛИ общее количество должно быть больше 500.
Правила И/ИЛИ
Используя несколько строк, вы можете комбинировать операторы И и ИЛИ, как в этом примере:
- имя клиента должно начинаться с "MegaMart" И название продукта должно начинаться с "Cookies"
- ИЛИ
Использование подстановочных знаков в критериях
Используйте подстановочные знаки для фильтрации текстовой строки в ячейке.
Подстановочный знак *
Подстановочный знак звездочка (*) представляет собой любое количество символов в этой позиции, включая ноль символов.
ПРИМЕЧАНИЕ. Поскольку Excel интерпретирует текстовые критерии как "начинается с", вам не нужно добавлять подстановочный знак * в конце строки
В этом примере критерий клиента: *o*r
Два имени клиента, FoodMart и Mart-o-rama, содержат букву "о", за которой следует буква "р" в любом месте после "о". Записи для этих двух клиентов отображаются в отфильтрованном списке.
? подстановочный знак
Подстановочный знак вопросительного знака (?) представляет один символ в этой позиции.
В этом примере любое название продукта, начинающееся с буквы c, за которой следует любой символ, а затем ke, будет проходить через расширенный фильтр Excel. И Coke, и Cake находятся в отфильтрованных результатах.
Подстановочный знак ~
Подстановочный знак тильда (~) позволяет искать символы, которые используются в качестве подстановочных знаков.
В первом примере звездочка находится в ячейке критерия — Good*Eats, поэтому любое имя клиента, начинающееся с Good, а затем любые символы, за которыми следует Eats, будет проходить через расширенный фильтр Excel.
Чтобы найти только имя клиента, начинающееся с Good*Eats, используйте тильду (~) перед звездочкой в ячейке критерия: Good~*Eats
Примеры критериев
Вот несколько других примеров критериев расширенного фильтра:
ПРИМЕЧАНИЕ. На этой странице есть дополнительные примеры критериев: Расширенные фильтры -- Комплексные критерии
Извлечение элементов в диапазоне
Чтобы извлечь список элементов в диапазоне, вы можете использовать два столбца для одного из полей. В этом примере для поля «Итого» используются два столбца.
Если вы вводите два критерия в одну и ту же строку в диапазоне критериев, вы создаете оператор AND. В этом примере все извлекаемые записи должны быть:
Извлечение элементов с определенным текстом
При использовании текста в качестве критерия с расширенным фильтром Excel Excel находит все элементы, начинающиеся с этого текста.
Например, если в качестве критерия ввести "Лед", Excel найдет "Лед", "Мороженое" и "Молоко-мороженое", поскольку все они начинаются с "Лед".
Чтобы извлечь только те записи, которые точно соответствуют тексту критерия, используйте следующий формат в диапазоне критериев (верхний или нижний регистр не повлияют на результаты):
="=ice"Где должен быть диапазон критериев?
Некоторые уроки Excel настаивают на том, чтобы диапазон критериев помещался непосредственно над данными. В этом нет необходимости — используйте удобное для вас место.
В примерах на этой странице диапазон критериев показан рядом с данными, но вам не нужно оставлять его там.
- Диапазон критериев может находиться на том же листе, что и данные, или на другом листе.
- Диапазон критериев может располагаться непосредственно над данными или в столбцах слева или справа от данных.
Если диапазон критериев находится на том же листе и в тех же строках, что и данные, ячейки критериев могут быть скрыты при применении расширенного фильтра "На месте".
Скрытые строки критериев не повлияют на расширенный фильтр — вы просто не сможете увидеть часть диапазона критериев.
Получить образец рабочей тетради
Загрузить сжатую книгу расширенных фильтров Excel с образцами данных и критериями.
Читайте также:
Рекомендуемые сообщения
Прочитайте этот пост и узнайте, как извлечь повторяющиеся записи:
В этой статье описывается, как фильтровать повторяющиеся строки с помощью формулы. На самом деле это массив […]
Узнайте, как фильтровать уникальные отдельные записи:
Содержание Фильтровать уникальные отдельные записи строк Фильтровать уникальные отдельные записи строк, но не пробелы Фильтровать уникальные отдельные строки […]
8. Файл Excel
Получить файл Excel
Извлечь все строки, содержащие значение между этой и той частью 21v45.xlsx
Еженедельный блог EMAIL
[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.
Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.
Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.
Статьи по теме
В этой статье показано, как извлекать записи/строки на основе двух условий, примененных к двум разным столбцам, которые можно легко расширить […]
В этой статье объясняются различные методы фильтрации строк/записей, содержащих заданную текстовую строку в любом из значений ячейки […]
Вопрос: я второй вопрос G: можно ли это сделать для более чем 3? т.е. (Вместо фамилии, отчества, имени) […]
Вопрос: у меня есть список, и я хочу отфильтровать все строки со значением (столбец C), которое […]
В этой статье описывается формула, которая возвращает все строки, содержащие хотя бы одну цифру от 0 (ноль) до 9. Что дальше […]
Вопрос: как фильтровать строки по датам? Ответ: В этом посте я опишу, как: Фильтровать строки с помощью массива […]
Содержание Извлечение отрицательных значений и смежных ячеек (формула массива) Извлечение отрицательных значений и смежных ячеек (фильтр Excel) Массив […]
Извлечение данных о времени необходимости – главный фактор использования любой электронной таблицы, Excel не является исключением. Вы сохраняете данные в табличной форме в Excel, чтобы извлечь их. Сегодня мы собираемся показать вам, как извлекать данные из таблицы на основе нескольких критериев.
Для начала давайте узнаем о наборе данных, который является основой наших примеров.
Здесь у нас есть таблица со списком фильмов с указанием жанра и исполнителя главной роли, а также года выпуска. Используя этот набор данных, мы будем получать данные на основе нескольких критериев.
Обратите внимание, что это базовая таблица для простоты. На практике вы можете столкнуться с гораздо большим и сложным набором данных.
Практическая рабочая тетрадь
Вы можете загрузить учебное пособие по следующей ссылке.
Извлечение данных из таблицы на основе нескольких критериев
Например, здесь мы укажем жанр и имя актера в качестве критериев, и на основе этих критериев мы извлечем название фильма.
1. Возврат одного значения
В этом разделе мы вернем одно значение. На основе критериев будет выбрано только одно значение. Давайте рассмотрим.
Я. Формула массива INDEX-MATCH
Можно использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ. ИНДЕКС возвращает значение в заданном месте диапазона. ПОИСКПОЗ находит позицию искомого значения в диапазоне.
Чтобы узнать об этих функциях, ознакомьтесь со следующими статьями: ИНДЕКС, ПОИСКПОЗ.
Давайте установим значение критерия. На данный момент Триллер в качестве Жанр и Хью Джекман в поле Актер.
Формула будет следующей
B4:B19 — это массив, из которого мы будем извлекать значение. А функция ПОИСКПОЗ устанавливает номер строки, которая должна быть извлечена.
Вы можете видеть, что мы указали 1 в качестве lookup_value в ПОИСКПОЗЕ. А lookup_array генерируется путем умножения логики сопоставления критериев.
Через $H$4=$C$4:$C$19 мы проверили жанр и $H$5=$D$4:$D$19 имя актера.
Он находит 1 среди массива результата умножения. И верните номер строки, а функция ИНДЕКС вернет название фильма.
Это формула массива, поэтому для ее выполнения нам нужно использовать CTRL + SHIFT + ENTER.
Измените значения критериев, и вы найдете обновленные значения.
II. ИНДЕКС-ПОИСКПОЗ Формула без массива
Мы можем сформировать формулу без массива, объединив ИНДЕКС и ПОИСКПОЗ.
Сначала посмотрим на формулу
Как видите, здесь мы использовали несколько функций ИНДЕКС. Внешняя функция ИНДЕКС выполняет извлечение, а внутренняя функция помогает определить номер строки.
Во внутреннем ИНДЕКСЕ мы проверили значение критерия. Здесь две логические операции умножались внутри ИНДЕКС и действовали как ссылка на массив.
Простой ENTER выполнит формулу.
Не стесняйтесь изменять значения критериев, вы найдете обновленные значения.
III. Комбинация ИНДЕКС-ПОИСКПОЗ-ЕСЛИ
В предыдущих разделах мы проверили условия и умножили их, чтобы заставить их действовать вместе. Мы можем отказаться от умножения, используя функцию ЕСЛИ.
ЕСЛИ выполняет логическую проверку и возвращает логическое значение (ИСТИНА или ЛОЖЬ) в качестве результата. Чтобы узнать об этой функции, посетите эту статью IF.
Наша формула будет следующей
Здесь у нас есть два условия для соответствия, поэтому два ЕСЛИ. Они работают как вложенные ЕСЛИ (один внутри другого).Внешняя функция ЕСЛИ сначала проверяет условие (независимо от последовательности), а второе условие (внутреннее ЕСЛИ) представляет собой if_true_value для первого ЕСЛИ.
Чтобы выполнить формулу, вам нужно использовать CTRL+SHIFT+ENTER.
Измените значения критериев, и вы найдете обновленное значение.
IV. Функция ПРОСМОТР
Мы можем использовать функцию ПРОСМОТР для выполнения нашей задачи извлечения данных на основе критериев.
Функция ПРОСМОТР выполняет поиск соответствия в диапазоне и возвращает соответствующее значение. Для получения дополнительной информации посетите сайт поддержки Microsoft.
Теперь давайте изучим формулу
Здесь мы установили 2 в качестве lookup_value. А две логические операции в виде деления 1 на них — это lookup_vector.
lookup_value сообщает формуле, что нужно сопоставить его с числовым значением в диапазоне. Как только совпадение будет найдено, значение будет получено из массива B4:B19.
Для выполнения не нужно нажимать CTRL + SHIFT + ENTER.
Измените значение критерия, чтобы увидеть, правильно ли работает формула для других значений.
Обратите внимание, что мы использовали 2 в качестве lookup_value. Это может быть любое число, начиная с 1.
2. Вернуть несколько значений
Я. Комбинация ИНДЕКС-МАЛЫЙ
Для извлечения нескольких данных на основе критериев можно использовать различные комбинации функций. Одной из комбинаций является комбинация ИНДЕКС – МАЛЕНЬКАЯ.
Функция НАИМЕНЬШИЙ возвращает значения в зависимости от их положения в списке, ранжированном по значению. Чтобы узнать больше, прочтите эту НЕБОЛЬШУЮ статью.
Наряду с этими двумя нам понадобится несколько вспомогательных функций: ЕСЛИ, СТРОКА и ЕСЛИОШИБКА. Дополнительные сведения см. в статьях: ЕСЛИ, СТРОКА, ЕСЛИОШИБКА.
Наша формула будет следующей
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$17,МАЛЕНЬКИЙ(ЕСЛИ(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), СТРОКА( $B$2:$B$17)),СТРОКА(1:1))-1,1),"")
Здесь каждая функция имеет свое назначение. Функция ИНДЕКС возвращает значение из массива B2:B17, а большая МАЛЕНЬКАЯ часть содержит номер строки, которую нужно извлечь.
IF внутри SMALL проверяет соответствие критериям. У нас есть два условия для сопоставления, поэтому мы перемножили обе логические операции для проверки критериев. А функция ROW выполняет итерацию по ячейкам столбца.
Тогда внешняя СТРОКА обозначает k-е значение функции МАЛЕНЬКИЙ. Вместе эти функции возвращают номер строки, а ИНДЕКС возвращает результат.
ЕСЛИОШИБКА, чтобы устранить любую ошибку, которая может возникнуть из-за формулы. Мы настроили его таким образом, что при ошибке будет предоставлена пустая ячейка.
Перетащите его вниз, и вы получите все значения, соответствующие критериям.
II. Комбинация ИНДЕКС-АГРЕГАТ
Функция АГРЕГАТ в Excel позволяет выполнять различные задачи. Одна функция для нескольких операций. Мы можем использовать эту функцию для возврата нескольких значений на основе нескольких критериев.
Давайте немного познакомимся с функцией. Функция АГРЕГАТ возвращает агрегированное вычисление, такое как СРЗНАЧ, СЧЁТ, МАКС и т. д.
Синтаксис функции АГРЕГАТ следующий:
function_number: этот номер указывает, какой расчет должен быть выполнен.
behavior_options: установите это с помощью числа. Это число указывает, как будет вести себя функция.
диапазон: диапазон, который вы хотите агрегировать.
Функция АГРЕГАТ выполняет несколько задач, поэтому в ней предопределено количество функций. Мы перечисляем несколько часто используемых номеров функций
| Функция | Function_number |
|---|---|
| СРЕДНЕЕ | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| МИН | 5 |
| ПРОДУКТ | 6 |
| СУММ | 9 |
| БОЛЬШОЙ | 14 |
| МАЛЕНЬКИЙ | 15 |
Чтобы узнать больше об этой функции, посетите сайт поддержки Microsoft.
Теперь давайте посмотрим на формулу, это будет комбинация ИНДЕКС и АГРЕГАТ.
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$17,ОБЪЕДИНИТЬ(15,6,ЕСЛИ(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3) ), СТРОКА($B$2:$B$17)),СТРОКА(1:1))-1,1),"")
Здесь мы использовали 15 в качестве function_number в AGGREGATE. Из приведенной выше таблицы вы можете увидеть 15 вызовов функции SMALL. Вы можете видеть, что за исключением использования АГРЕГАТ (а также номера функции и номера параметра поведения), формула точно такая же, как и предыдущая формула ИНДЕКС-МАЛЕНЬКИЙ.
Механизм тот же: ИНДЕКС содержит массив, который возвращает значения на основе совпадений, найденных в части формулы АГРЕГАТ.
6 для параметра поведения, который означает игнорирование значений ошибок.
Перетащите его вниз, и вы получите все значения, соответствующие критериям.
Не забудьте использовать CTRL+SHIFT+ENTER для выполнения формулы.
III. Комбинация ИНДЕКС-ПОИСКПОЗ-СЧЁТЕСЛИ
Для возврата нескольких значений на основе нескольких критериев мы можем использовать комбинацию ИНДЕКС, ПОИСКПОЗ и СЧЁТЕСЛИ.
СЧЁТЕСЛИ подсчитывает ячейки в диапазоне, соответствующем одному условию. Чтобы узнать больше об этой функции, посетите эту статью: СЧЁТЕСЛИ.
Наша формула будет следующей
=ЕСЛИОШИБКА(ИНДЕКС($B$4:$B$19,ПОИСКПОЗ(0,СЧЁТЕСЛИ(H5:$H$5,$B$4:$B$19)+ЕСЛИ($C$4:$C$19<>$H $4,1,0)+ЕСЛИ($D$4:$D$19<>$H$5,1,0),0)),"")
В функции ПОИСКПОЗ мы предоставили 0 в качестве lookup_array, а для lookup_range мы использовали часть ЕСЛИ, содержащую СЧЁТЕСЛИ.
Здесь функция СЧЁТЕСЛИ исключает любое значение, которое уже было получено. А две функции ЕСЛИ проверяют два условия. Мы добавили эти функции, чтобы они вместе образовывали lookup_range.
Часть ПОИСКПОЗ возвращает значение, если найдено 0. Значение здесь работает как номер строки для ИНДЕКС.
Перетащите его вниз, и вы получите все значения, соответствующие критериям.
IV. ФИЛЬТР
Если вы используете Excel 365, вы можете выполнить задачу с помощью одной встроенной функции под названием ФИЛЬТР.
Функция ФИЛЬТР фильтрует диапазон данных на основе заданных критериев и извлекает соответствующие записи. Чтобы узнать об этой функции, посетите эту статью: ФИЛЬТР.
Давайте рассмотрим формулу
B4:B19 — это массив, который необходимо отфильтровать. Затем мы предоставили условие, исходя из которого мы будем извлекать значения. Поскольку нам нужно проверить два критерия, мы их перемножили.
Здесь вам не нужно перетаскивать формулу вниз, за один раз это предоставит все значения и заполнит список.
Заключение
На сегодня все. Мы перечислили несколько методов извлечения данных из таблицы на основе нескольких критериев. Надеюсь, вы найдете это полезным. Не стесняйтесь комментировать, если что-то кажется трудным для понимания. Сообщите нам о других подходах, которые мы здесь пропустили.
Используйте расширенный фильтр в Excel, чтобы создать список уникальных элементов или извлечь определенные элементы на другой лист. Вы также можете использовать сложные критерии с расширенным фильтром, поэтому он полезен, когда простой автофильтр не может сделать то, что вам нужно.
Создать расширенный фильтр Excel
Посмотрите это видео, чтобы узнать, как настроить диапазон критериев и запустить расширенный фильтр. Письменные инструкции находятся под видео.
Создать расширенный фильтр Excel
Вот шаги по настройке данных и созданию расширенного фильтра.
1. Настройте базу данных
2. Настройте диапазон критериев (необязательно)
В диапазоне критериев для расширенного фильтра Excel можно установить правила для данных, которые должны оставаться видимыми после применения фильтра. Вы можете использовать один критерий или несколько.
ВНИМАНИЕ! Когда вы используете текстовые критерии, Excel интерпретирует их как "начинается с". Если вам нужно точное совпадение, см. пример точного совпадения ниже.
После применения расширенного фильтра Excel заказы на общую сумму более 500 долларов США останутся видимыми.
Другие операторы включают:
3. Настройте диапазон извлечения (необязательно)
Если вы планируете копировать данные в другое место (на тот же лист или на другой лист), вы можете указать столбцы, которые хотите извлечь.
ПРИМЕЧАНИЕ. Если вы хотите извлечь ВСЕ столбцы, вы можете оставить диапазон извлечения пустым для расширенного фильтра Excel.