Удаление строк на основе значения ячейки (или условия) в Excel (простое руководство)

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

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

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

Есть несколько способов избавиться от этой кошки данных в Excel.

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

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

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

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

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

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

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

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

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

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

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

1. Выберите любую ячейку в наборе данных, из которой вы хотите удалить строки
2. Перейдите на вкладку "Данные".
3. В группе «Сортировка и фильтр» щелкните значок «Фильтр». Это применит фильтры ко всем ячейкам заголовков в наборе данных.
4. Щелкните значок фильтра в ячейке заголовка региона (это небольшой значок треугольника, указывающий вниз, в правом верхнем углу ячейки).
5. Отмените выбор всех других опций, кроме опции Mid-West (быстрый способ сделать это - щелкнуть опцию Select All, а затем щелкнуть опцию Mid-West). Это отфильтрует набор данных и покажет вам только записи для региона Среднего Запада.
6. Выберите все отфильтрованные записи
7. Щелкните правой кнопкой мыши любую из выбранных ячеек и выберите «Удалить строку».
8. В открывшемся диалоговом окне нажмите ОК. На этом этапе вы не увидите никаких записей в наборе данных.
9. Щелкните вкладку «Данные» и щелкните значок «Фильтр». Фильтр будет удален, и вы увидите все записи, кроме удаленных.

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

Некоторые полезные ярлыки, которые нужно знать, чтобы ускорить процесс:

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

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

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

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

Или

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

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

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

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

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

1. Выберите любую ячейку в данных
2. Перейдите на вкладку "Данные".
3. В группе «Сортировка и фильтр» щелкните значок «Фильтр». Это применит фильтры ко всем ячейкам заголовков в наборе данных.
4. Щелкните значок «Фильтр» в ячейке заголовка «Продажи» (это небольшой значок треугольника, указывающий вниз, в правом верхнем углу ячейки).
5. Наведите курсор на параметр «Числовые фильтры». Это покажет вам все параметры фильтра, связанные с числами, в Excel.
6. Нажмите на опцию «Меньше чем».
7. В открывшемся диалоговом окне «Пользовательский автофильтр» введите значение «200» в поле.
8. Щелкните ОК. Это отфильтрует и покажет только те записи, в которых стоимость продажи меньше 200.
9. Выберите все отфильтрованные записи
10. Щелкните правой кнопкой мыши любую ячейку и выберите Удалить строку.
11. В открывшемся диалоговом окне нажмите ОК. На этом этапе вы не увидите никаких записей в наборе данных.
12. Щелкните вкладку «Данные» и щелкните значок «Фильтр». Фильтр будет удален, и вы увидите все записи, кроме удаленных.

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

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

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

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

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

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

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

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

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

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

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

1. Выберите любую ячейку в данных
2. Перейдите на вкладку "Данные".
3. В группе «Сортировка и фильтр» щелкните значок «Сортировка».
4. В открывшемся диалоговом окне «Сортировка» выберите «Регион» для сортировки по столбцу.
5. Убедитесь, что в поле «Сортировать по» выбрано значение «Значения ячеек».
6. В разделе «Порядок» выберите от А до Я (или от Я до А, не имеет значения).
7. Щелкните ОК. Это даст вам отсортированный набор данных, как показано ниже (отсортированный по столбцу B).
8. Выбрать все записи с регионом Средний Запад (все ячейки в строках, а не только столбец региона)
9. После выбора щелкните правой кнопкой мыши и выберите Удалить. Откроется диалоговое окно «Удалить».
10. Убедитесь, что выбран параметр «Сдвиг ячеек вверх».
11. Щелкните ОК.

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

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

Вот подробное руководство по сортировке данных в Excel.

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

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

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

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

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

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

1. Выбрать весь набор данных
2. Перейдите на вкладку "Главная"
3. В группе редактирования нажмите «Найти и выбрать», а затем нажмите «Найти» (вы также можете использовать сочетание клавиш Control + F).
4. В диалоговом окне «Найти и заменить» введите текст «Средний Запад» в поле «Найти:».
5. Нажмите «Найти все». Это мгновенно покажет вам все экземпляры текста Среднего Запада, которые удалось найти Excel.
6. Используйте сочетание клавиш Control + A, чтобы выбрать все ячейки, найденные Excel. Вы также сможете увидеть все выбранные ячейки в наборе данных.
7. Щелкните правой кнопкой мыши любую из выбранных ячеек и нажмите Удалить. Откроется диалоговое окно «Удалить».
8. Выберите вариант «Вся строка».
9. Щелкните ОК.

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

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

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

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

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

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

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

1. Выберите весь набор данных (в данном случае A1: D16).
2. нажмите F5 ключ. Это откроет диалоговое окно «Перейти к» (вы также можете получить это диалоговое окно из «Главная» -> «Редактирование» -> «Найти и выбрать» -> «Перейти»).
3. В диалоговом окне «Перейти» нажмите кнопку «Специальная». Откроется диалоговое окно «Перейти к специальному».
4. В диалоговом окне «Перейти к специальному» выберите «Пробелы».
5. Щелкните ОК.

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

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

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

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

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

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

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

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

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

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

Sub DeleteRowsWithSpecificText () 'Источник: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Поле: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible) .Delete End Sub

В приведенном выше коде используется метод VBA Autofilter, чтобы сначала отфильтровать строки на основе указанного критерия (который является «Средним Западом»), затем выбрать все отфильтрованные строки и удалить их.

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

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

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

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

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

Sub DeleteRowsinTables () 'Источник: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter Field: = 2, Criteria1: = "Средний Запад" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible). Удалить End Sub

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

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

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

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

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

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

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

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

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