Excel выбирает строки по условию

Обновлено: 21.11.2024

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

Например, рассмотрим следующие примеры:

1. У вас есть данные о торговых представителях, и вы хотите удалить все записи для определенного региона или продукта.
2. Вы хотите удалить все записи, в которых стоимость продажи меньше 100.
3. Вы хотите удалить все строки, в которых есть пустая ячейка.

Существует несколько способов оформления этого кота данных в Excel.

Метод, который вы выберете для удаления строк, будет зависеть от того, как структурированы ваши данные и какое значение ячейки или условие, на основании которого вы хотите удалить эти строки.

В этом руководстве я покажу вам несколько способов удаления строк в Excel на основе значения ячейки или условия.

Это руководство охватывает:

Отфильтровать строки по значению/условию, а затем удалить

Один из самых быстрых способов удалить строки, которые содержат определенное значение или удовлетворяют заданному условию, — это отфильтровать их. Получив отфильтрованные данные, вы можете удалить все эти строки (при этом остальные строки останутся нетронутыми).

Фильтр Excel довольно универсален, и вы можете фильтровать по многим критериям (например, по тексту, числам, датам и цветам)

Давайте рассмотрим два примера, в которых вы можете фильтровать строки и удалять их.

Удалить строки, содержащие определенный текст

Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить все строки, где регионом является Средний Запад (в столбце B).

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

В этом случае вы можете отфильтровать все записи, в которых регионом является Средний Запад, а затем удалить все эти строки (при этом остальные строки останутся нетронутыми).

Ниже приведены шаги по удалению строк на основе значения (все записи Среднего Запада):

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

Несколько полезных советов, которые помогут ускорить процесс:

1. Control + Shift + L, чтобы применить или удалить фильтр.
2. Control + – (удерживайте клавишу Control и нажмите клавишу "минус"), чтобы удалить выбранные ячейки/строки.

В приведенном выше примере у меня было только четыре отдельных региона, и я мог вручную выбирать и отменять их выбор в списке фильтров (на шаге 5 выше).

Если у вас много категорий/регионов, вы можете ввести название в поле прямо над полем (с этими названиями регионов), и Excel покажет вам только те записи, которые соответствуют введенному тексту (как показано ниже). ). Когда у вас есть текст, на основе которого вы хотите отфильтровать, нажмите клавишу Enter.

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

Можно использовать методы, показанные далее в этом руководстве (метод сортировки или метод "Найти все")

Удалить строки на основе числового условия

Точно так же, как я использовал метод фильтра для удаления всех строк, содержащих текст Mid-West, вы также можете использовать числовое условие (или условие даты).

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

Ниже приведены шаги для этого:

В Excel можно использовать множество числовых фильтров, например меньше/больше, равно/не равно, между, первые 10, выше или ниже среднего и т. д.

Примечание. Вы также можете использовать несколько фильтров. Например, вы можете удалить все строки, в которых значение продаж больше 200, но меньше 500. В этом случае вам нужно использовать два условия фильтрации. Диалоговое окно «Пользовательский автофильтр» позволяет использовать два критерия фильтрации (И и ИЛИ).

Так же, как и числовые фильтры, вы также можете фильтровать записи по дате. Например, если вы хотите удалить все записи за первый квартал, вы можете сделать это, выполнив те же действия, что и выше.Когда вы работаете с фильтрами даты, Excel автоматически показывает вам соответствующие фильтры (как показано ниже).

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

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

Вы не можете сделать это с помощью фильтрации, но вы можете сделать это с помощью сортировки.

Отсортировать набор данных, а затем удалить строки

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

Этот метод сортировки рекомендуется только в том случае, если вы хотите удалить ячейки со значениями, а не все строки.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить все записи, в которых регионом является Средний Запад.

Ниже приведены шаги, чтобы сделать это с помощью сортировки:

Вышеуказанные шаги удалят все записи, в которых регионом является Средний Запад, но не удалит всю строку. Таким образом, если у вас есть какие-либо данные справа или слева от вашего набора данных, они останутся невредимыми.

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

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

Найти и выбрать ячейки на основе значения ячейки, а затем удалить строки

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

Выбрав эти ячейки, вы можете легко удалить строки.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить все строки, в которых регионом является Средний Запад.

Ниже приведены шаги для этого:

Вышеупомянутые шаги удалят все ячейки, в которых регион имеет значение Средний Запад.

Примечание. Поскольку функция "Найти и заменить" может обрабатывать подстановочные знаки, вы можете использовать их при поиске данных в Excel. Например, если вы хотите удалить все строки, в которых регионом является Средний Запад или Юго-Запад, вы можете использовать «* Запад» в качестве текста для поиска в диалоговом окне «Найти и заменить». Это даст вам все ячейки, где текст заканчивается словом West.

Удалить все строки с пустой ячейкой

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

Это параметр «Перейти к специальным ячейкам», который позволяет быстро выбрать все пустые ячейки. И как только вы выбрали все пустые ячейки, удалить их очень просто.

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

Ниже приведены шаги для этого:

Вышеуказанные шаги позволят выбрать все пустые ячейки в наборе данных.

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

В диалоговом окне «Удалить» выберите параметр «Вся строка» и нажмите «ОК». Это удалит все строки, в которых есть пустые ячейки.

Если вам интересно узнать больше об этой технике, я написал подробное руководство о том, как удалять строки с пустыми ячейками. Он включает метод «Перейти к специальному», а также метод VBA для удаления строк с пустыми ячейками.

Фильтрация и удаление строк на основе значения ячейки (с помощью VBA)

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

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

Этот код работает так же, как описанный выше метод Filter (за исключением того факта, что он выполняет все шаги в серверной части и экономит несколько кликов).

Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить все строки, в которых регионом является Средний Запад.

Ниже приведен код VBA, который это сделает.

Приведенный выше код использует метод автофильтра VBA, чтобы сначала отфильтровать строки на основе заданных критериев (то есть "Средний Запад"), а затем выбрать все отфильтрованные строки и удалить их.

Обратите внимание, что я использовал Offset в приведенном выше коде, чтобы убедиться, что моя строка заголовка не удалена.

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

Перед удалением строк появится подсказка, как показано ниже. Я нахожу это полезным, так как позволяет дважды проверить отфильтрованную строку перед удалением.

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

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

Поскольку VBA рассматривает таблицу Excel как объект списка (а не как диапазон), мне пришлось соответствующим образом изменить код.

Куда поместить код VBA?

Этот код необходимо поместить в модуль редактора VB Editor.

Ниже приведены шаги, которые покажут вам, как это сделать:

1. Откройте книгу, в которую вы хотите добавить этот код.
2. Используйте сочетание клавиш ALT + F11, чтобы открыть окно редактора VBA.
3. В этом окне редактора VBA слева находится панель «Проводник проектов» (в которой перечислены все рабочие книги и объекты рабочих листов). Щелкните правой кнопкой мыши любой объект в рабочей книге (в котором вы хотите, чтобы этот код работал), наведите курсор на «Вставить» и затем нажмите «Модуль». Это добавит объект модуля в книгу, а также откроет окно кода модуля справа
4. В окне модуля (которое появится справа) скопируйте и вставьте приведенный выше код.

После того, как у вас есть код в редакторе VB, вы можете запустить код, используя любой из следующих методов (убедитесь, что вы выбрали любую ячейку в наборе данных, для которой вы хотите запустить этот код):

1. Выберите любую строку кода и нажмите клавишу F5.
2. Нажмите кнопку "Выполнить" на панели инструментов редактора VB.
3. Назначьте макрос кнопке или фигуре и запустите его, нажав на него на листе.
4. Добавьте его на панель быстрого доступа и запустите код одним щелчком мыши.

В этой статье вы можете прочитать все о том, как запускать код макроса в Excel.

Примечание. Поскольку рабочая книга содержит код макроса VBA, его необходимо сохранить в формате с поддержкой макросов (xlsm).

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

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

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

Эта статья Извлечение записей между двумя датами очень похожа на текущую статью, которую вы сейчас читаете. Даты 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 Как ввести эту формулу массива

1. Выберите ячейку A20.
2. Вставить приведенную выше формулу в ячейку или строку формул.
3. Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
4. Нажмите Enter один раз
5. Отпустить все клавиши

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

Не вводите эти символы самостоятельно, они появляются автоматически.

Теперь скопируйте ячейку 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.

Вот как применить фильтр:

1. Создайте заголовки для столбца, который вы хотите отфильтровать, желательно выше или ниже вашего набора данных.
   Ваши фильтры могут исчезнуть, если их разместить рядом с набором данных, потому что строки могут быть скрыты при применении фильтра.
2. Выберите весь набор данных, включая заголовки.
3. Перейдите на вкладку "Данные" на ленте.
4. Нажмите левой кнопкой мыши на кнопку "Дополнительно".
   
5. Появится диалоговое окно.
   
6. Выберите диапазон критериев C2:D3, как показано на изображении выше.
7. Нажмите левой кнопкой мыши на кнопку OK.

Рекомендуемые сообщения

Прочитайте этот пост и узнайте, как извлечь повторяющиеся записи:

В этой статье описывается, как фильтровать повторяющиеся строки с помощью формулы. На самом деле это массив […]

Узнайте, как фильтровать уникальные отдельные записи:

Содержание Фильтровать уникальные отдельные записи строк Фильтровать уникальные отдельные записи строк, но не пробелы Фильтровать уникальные отдельные строки […]

8. Файл Excel

Получить файл Excel


Извлечь все строки, содержащие значение между этой и той частью 21v45.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

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

Статьи по теме

В этой статье показано, как извлекать записи/строки на основе двух условий, примененных к двум разным столбцам, которые можно легко расширить […]

В этой статье объясняются различные методы фильтрации строк/записей, содержащих заданную текстовую строку в любом из значений ячейки […]

Вопрос: я второй вопрос G: можно ли это сделать для более чем 3? т.е. (Вместо фамилии, отчества, имени) […]

Вопрос: у меня есть список, и я хочу отфильтровать все строки со значением (столбец C), которое […]

В этой статье описывается формула, которая возвращает все строки, содержащие хотя бы одну цифру от 0 (ноль) до 9. Что дальше […]

Вопрос: как фильтровать строки по датам? Ответ: В этом посте я опишу, как: Фильтровать строки с помощью массива […]

Содержание Извлечение отрицательных значений и смежных ячеек (формула массива) Извлечение отрицательных значений и смежных ячеек (фильтр Excel) Массив […]

Если есть диапазон данных с некоторыми оценками, именами и регионами, и вы просто хотите узнать значения, которые больше или равны 1 и меньше или равны 3 в столбце Оценка, а затем извлеките соответствующие строки в следующую таблицу, как показано на снимке экрана ниже. Как можно быстро извлечь эти строки, соответствующие критериям, в другое место в Excel, кроме как найти и скопировать их одну за другой?
Извлечь строки, соответствующие критериям, с помощью функции фильтра
Извлекайте строки, соответствующие критериям, с помощью Kutools for Excel

Извлечение строк, соответствующих критериям, с помощью функции фильтра

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

<р>1. Выберите диапазон, включающий заголовки, из которых вы хотите извлечь строки, нажмите «Данные» > «Фильтр», чтобы добавить значки «Фильтр» рядом с заголовками. Смотрите скриншот:

<р>2. Затем щелкните значок «Фильтр» рядом со столбцом «Оценка» (столбец, на основе которого вы можете извлекать строки) и выберите «Числовые фильтры» > «Пользовательский фильтр» в контекстном меню. Смотрите скриншот:

<р>3. В диалоговом окне «Пользовательский автофильтр» выберите критерии из раскрывающихся списков, введите диапазон чисел в текстовые поля и установите флажок И. Смотрите скриншот:

<р>4. Затем нажмите OK, и диапазон данных будет отфильтрован по заданным вами критериям.

<р>5. Затем выберите отфильтрованные данные и нажмите Ctrl + G, чтобы открыть диалоговое окно «Перейти к», и нажмите «Специальный», чтобы перейти к диалоговому окну «Перейти к специальному», и установите флажок «Только видимые ячейки». Смотрите скриншот；

<р>6. Нажмите «ОК», и теперь в диапазоне выделены только видимые ячейки, и нажмите Ctrl + C, чтобы скопировать его, а затем перейдите к выбору ячейки, чтобы нажать Ctrl + V, чтобы вставить видимые ячейки. Смотрите скриншот:

Извлечение строк, соответствующих критериям, с помощью Kutools for Excel

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

Если вы хотите решить эту проблему с меньшим количеством шагов, вы можете использовать функцию Kutools for Excel «Выбрать определенные ячейки», чтобы выбрать строки, соответствующие критериям, а затем скопировать их в другое место.

<р>1. Выберите столбец, на основе которого вы извлекаете строки, и нажмите Kutools > Выбрать > Выбрать определенные ячейки. Смотрите скриншот:

<р>2. Затем в диалоговом окне «Выбрать определенные ячейки» установите флажок «Вся строка» в разделе «Тип выбора» и укажите критерии в разделе «Указать тип». Смотрите скриншот:

<р>3. Нажмите «ОК», чтобы закрыть диалоговое окно, и появится всплывающее диалоговое окно, напоминающее о выборе количества ячеек, просто нажмите «ОК», чтобы закрыть его.

<р>4. Теперь нажмите Ctrl + C, чтобы скопировать строки, и выберите ячейку, в которую нужно поместить извлеченные строки, и нажмите Ctrl + V, чтобы вставить их.


Если вы предпочитаете читать письменные инструкции, ниже приведен учебник.

Условное форматирование позволяет форматировать ячейку (или диапазон ячеек) на основе содержащегося в ней значения.

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

В качестве примера ниже у меня есть набор данных, в котором я выделил все строки, где имя торгового представителя — Боб.

В этом руководстве я покажу вам, как выделять строки на основе значения ячейки, используя условное форматирование и различные критерии.

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

Это руководство охватывает:

Выделение строк на основе текстовых критериев

Предположим, у вас есть набор данных, как показано ниже, и вы хотите выделить все записи, в которых имя торгового представителя — Боб.

Вот как это сделать:

При этом будут выделены все строки, в которых имя торгового представителя "Боб".

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

Как это работает?

Условное форматирование проверяет каждую ячейку на соответствие заданному нами условию, то есть =$C2="Боб"

Поэтому, когда он анализирует каждую ячейку в строке A2, он проверяет, имеет ли ячейка C2 имя Bob или нет. Если это так, эта ячейка выделяется, иначе нет.

Обратите внимание, что трюк здесь заключается в использовании знака доллара ($) перед алфавитом столбца ($C1). Сделав это, мы заблокировали столбец так, чтобы он всегда был C. Таким образом, даже когда ячейка A2 проверяется на наличие формулы, она будет проверять C2, а когда A3 проверяется на условие, она проверяет C3.

Это позволяет выделить всю строку с помощью условного форматирования.

Выделение строк на основе числовых критериев

В приведенном выше примере мы видели, как проверить имя и выделить всю строку.

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

Предположим, у меня есть те же данные (как показано ниже), и я хочу выделить все строки, в которых количество больше 15.

Вот как это сделать:

1. Выберите весь набор данных (в этом примере A2:F17).
2. Перейдите на вкладку "Главная".
3. В группе "Стили" нажмите "Условное форматирование".
4. Нажмите "Новые правила".
5. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
6. В поле формулы введите следующую формулу: =$D2>=15
7. Нажмите кнопку «Формат». В открывшемся диалоговом окне задайте цвет, которым вы хотите выделить строку.
8. Нажмите "ОК".

При этом будут выделены все строки, в которых количество больше или равно 15.

Аналогичным образом мы также можем использовать это, чтобы иметь критерии для даты.

Например, если вы хотите выделить все строки, в которых дата находится после 10 июля 2018 года, можно использовать следующую формулу даты:

Выделение строк на основе нескольких критериев (И/ИЛИ)

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

Например, если вы хотите выделить все строки, в которых имя торгового представителя "Боб" и количество больше 10, вы можете сделать это, выполнив следующие действия:

1. Выберите весь набор данных (в этом примере A2:F17).
2. Перейдите на вкладку "Главная".
3. В группе "Стили" нажмите "Условное форматирование".
4. Нажмите "Новые правила".
5. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
6. В поле формулы введите следующую формулу: =И($C2="Боб",$D2>10)
7. Нажмите кнопку «Формат». В открывшемся диалоговом окне задайте цвет, которым вы хотите выделить строку.
8. Нажмите "ОК".

В этом примере выделяются только те строки, в которых выполняются оба условия (это делается с помощью формулы AND).

Точно так же можно использовать условие ИЛИ. Например, если вы хотите выделить строки, где торговым представителем является Боб или количество больше 15, вы можете использовать следующую формулу:

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

Выделение строк разным цветом в зависимости от нескольких условий

Иногда может потребоваться выделить строки цветом в зависимости от условия.

Например, вы можете выделить все строки, в которых количество больше 20, зеленым цветом, а где количество больше 15 (но меньше 20) — оранжевым.

Для этого необходимо создать два правила условного форматирования и установить приоритет.

Вот как это сделать:

Вышеуказанные шаги сделают все строки с количеством больше 20 зеленым цветом, а с количеством больше 15 (но меньше 20 – оранжевым).

Порядок правил:

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

В приведенном выше примере условие зеленого цвета выше условия оранжевого цвета.

Если наоборот, то все строки будут окрашены только в оранжевый цвет.

Потому что строка, в которой количество больше 20 (скажем, 23), удовлетворяет обоим нашим условиям (=$D2>15 и =$D2>20). А так как Оранжевое условие находится вверху, ему отдается предпочтение.

Вы можете изменить порядок условий, используя кнопки Вверх/Вниз.

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

Выделить строки, в которых любая ячейка пуста

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

Вот как это сделать:

1. Выберите весь набор данных (в этом примере A2:F17).
2. Перейдите на вкладку "Главная".
3. В группе "Стили" нажмите "Условное форматирование".
4. Нажмите "Новые правила".
5. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
6. В поле формулы введите следующую формулу: =СЧЁТЕСЛИ($A2:$F2",")>0
7. Нажмите кнопку «Формат». В открывшемся диалоговом окне установите оранжевый цвет.
8. Нажмите "ОК".

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

Если какая-либо из ячеек пуста, выделяется вся строка.

По теме: Прочитайте это руководство, если вы хотите выделить только пустые ячейки.

Выделение строк на основе раскрывающегося списка

В рассмотренных примерах все условия задавались в диалоговом окне условного форматирования.

В этой части руководства я покажу вам, как сделать его динамическим (чтобы вы могли ввести условие в ячейку Excel, и оно автоматически выделило строки на его основе).

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

Вот шаги, чтобы создать это:

1. Создайте раскрывающийся список в ячейке A2. Здесь я использовал имена торговых представителей для создания выпадающего списка. Вот подробное руководство о том, как создать раскрывающийся список в Excel.
2. Выберите весь набор данных (в данном примере C2:H17).
3. Перейдите на вкладку "Главная".
4. В группе "Стили" нажмите "Условное форматирование".
5. Нажмите "Новые правила".
6. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
7. В поле формулы введите следующую формулу: =$E2=$A$2
8. Нажмите кнопку «Формат». В открывшемся диалоговом окне установите оранжевый цвет.
9. Нажмите "ОК".

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

Хотите узнать больше о том, как искать и выделять в Excel? Посмотрите видео ниже.

<Р>