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

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

Наприклад, розглянемо такі приклади:

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

Існує кілька способів зняти цю коту даних у Excel.

Спосіб видалення рядків буде залежати від того, як структуровані ваші дані, і від того, яке значення клітини чи умова, на основі якої потрібно видалити ці рядки.

У цьому посібнику я покажу вам кілька способів видалення рядків у Excel на основі значення комірки або умови.

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

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

Фільтр Excel досить універсальний, і ви можете фільтрувати його за багатьма критеріями (такими як текст, цифри, дати та кольори)

Давайте розглянемо два приклади, де можна відфільтрувати рядки та видалити їх.

Видалити рядки, що містять певний текст

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

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

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

Нижче наведено кроки для видалення рядків на основі значення (усі записи Середнього Заходу):

1. Виберіть будь -яку клітинку в наборі даних, з якої потрібно видалити рядки
2. Натисніть на вкладку Дані
3. У групі "Сортування та фільтр" натисніть на значок фільтра. Це застосує фільтри до всіх комірок заголовків у наборі даних
4. Натисніть на значок фільтра у клітинці заголовка регіону (це маленька трикутна піктограма трикутника, розташована у верхньому правому куті комірки)
5. Скасуйте вибір усіх інших параметрів, окрім опції Середнього Заходу (швидкий спосіб це зробити, натиснувши опцію Вибрати все, а потім натиснувши опцію Середній Захід). Це відфільтрує набір даних і покаже лише записи для регіону Середнього Заходу.
6. Виберіть усі відфільтровані записи
7. Клацніть правою кнопкою миші будь-яку з виділених клітинок і натисніть «Видалити рядок»
8. У діалоговому вікні, що відкриється, натисніть кнопку OK. На цьому етапі ви не побачите записів у наборі даних.
9. Перейдіть на вкладку Дані та натисніть на значок фільтра. Це видалить фільтр, і ви побачите всі записи, крім видалених.

Наведені вище кроки спочатку фільтрують дані на основі значення комірки (або можуть бути іншими умовами, такими як після/перед датою або більшим/меншим за число). Отримавши записи, ви просто видаляєте їх.

Деякі корисні ярлики, які потрібно знати, щоб прискорити процес:

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

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

Якщо у вас багато категорій/регіонів, ви можете ввести ім’я у поле праворуч над полем (у якому є ці назви регіонів), і Excel покаже вам лише ті записи, які відповідають введеному тексту (як показано нижче). Після того, як у вас є текст, на основі якого ви хочете відфільтрувати, натисніть клавішу Enter.

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

Або

Ви можете використовувати методи, показані пізніше в цьому підручнику (використовуючи метод Сортування або Метод Знайти все)

Видалити рядки на основі числової умови

Так само, як я використав метод фільтрації, щоб видалити всі рядки, що містять текст Mid-West, ви також можете використовувати умову числа (або умову дати).

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

Нижче наведено кроки для цього.

1. Виберіть будь -яку клітинку в даних
2. Натисніть на вкладку Дані
3. У групі "Сортування та фільтр" натисніть на значок фільтра. Це застосує фільтри до всіх комірок заголовків у наборі даних
4. Натисніть на значок фільтра у клітинці заголовка Sales (це маленька піктограма трикутника, спрямованого вниз, у верхньому правому куті комірки)
5. Наведіть курсор на опцію Цифрові фільтри. Це покаже вам усі параметри фільтра, пов'язані з числами, в Excel.
6. Натисніть на опцію "Менше".
7. У діалоговому вікні "Спеціальний автофільтр", що відкриється, введіть у поле значення "200"
8. Натисніть OK. Це буде фільтрувати та відображати лише ті записи, де вартість продажу менша за 200
9. Виберіть усі відфільтровані записи
10. Клацніть правою кнопкою миші будь-яку клітинку та натисніть Видалити рядок
11. У діалоговому вікні, що відкриється, натисніть кнопку OK. На цьому етапі ви не побачите записів у наборі даних.
12. Перейдіть на вкладку Дані та натисніть на значок фільтра. Це видалить фільтр, і ви побачите всі записи, крім видалених.

У Excel можна використовувати багато числових фільтрів - наприклад, менше/більше, рівне/не дорівнює, між, топ -10, вище або нижче середнього тощо.

Примітка: Ви також можете використовувати кілька фільтрів. Наприклад, ви можете видалити всі рядки, де вартість продажу більша за 200, але менша за 500. У цьому випадку вам потрібно використати дві умови фільтрації. Діалогове вікно Спеціальний автофільтр дозволяє мати два критерії фільтрації (І, а також АБО).

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

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

Що робити, якщо я хочу лише видалити записи з набору даних, але хочу залишити ці дані недоторканими.

Ви не можете зробити це за допомогою фільтрації, але ви можете зробити це за допомогою сортування.

Сортуйте набір даних, а потім видаліть рядки

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

Цей метод сортування рекомендується лише тоді, коли потрібно видалити комірки зі значеннями, а не цілі рядки.

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

Нижче наведено кроки, щоб зробити це за допомогою сортування:

1. Виберіть будь -яку клітинку в даних
2. Натисніть на вкладку Дані
3. У групі Сортування та фільтр клацніть піктограму Сортувати.
4. У діалоговому вікні Сортування, що відкриється, виберіть Регіон у стовпці сортування.
5. У опції Сортувати по переконайтеся, що вибрано значення комірок
6. У варіанті замовлення виберіть від A до Z (або від Z до A, це не має значення).
7. Натисніть OK. Це дасть вам відсортований набір даних, як показано нижче (відсортований за стовпцем B).
8. Виберіть усі записи з регіоном Середній Захід (усі клітинки у рядках, а не лише стовпець області)
9. Після вибору клацніть правою кнопкою миші, а потім натисніть Видалити. Відкриється діалогове вікно Видалити.
10. Переконайтеся, що вибрано параметр «Змістити комірки вгору».
11. Натисніть OK.

Вищеописані кроки видалять усі записи, де регіон знаходився на Середньому Заході, але не видаляє весь рядок. Отже, якщо у вас є якісь дані праворуч чи ліворуч від набору даних, вони залишаться неушкодженими.

У наведеному вище прикладі я відсортував дані на основі значення клітинки, але ви також можете використовувати ті самі кроки для сортування за номерами, датами, кольором комірки чи кольором шрифту тощо.

Ось докладний посібник про те, як сортувати дані в Excel

Якщо ви хочете зберегти вихідний порядок набору даних, але видалити записи на основі критеріїв, вам потрібно мати спосіб відсортувати дані назад до вихідного. Для цього перед сортуванням даних додайте стовпець із серійними номерами. Після того, як ви видалите рядки/записи, просто відсортуйте за допомогою цього додаткового стовпця, який ви додали.

Знайдіть і виберіть клітинки на основі їх значення, а потім видаліть рядки

Excel має функцію "Знайти та замінити", яка може бути чудовою, коли потрібно знайти та виділити клітинки з певним значенням.

Вибравши ці клітинки, можна легко видалити рядки.

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

Нижче наведено кроки для цього.

1. Виберіть весь набір даних
2. Натисніть вкладку Домашня сторінка
3. У групі Редагування натисніть опцію «Знайти та вибрати», а потім натисніть Знайти (також можна скористатися комбінацією клавіш Control + F).
4. У діалоговому вікні «Знайти та замінити» введіть текст «Середній Захід» у полі «Знайти що:».
5. Натисніть Знайти все. Це миттєво покаже вам усі екземпляри тексту Середнього Заходу, які вдалося знайти Excel.
6. За допомогою комбінації клавіш Control + A виберіть усі клітинки, які знайшов Excel. Ви також зможете побачити всі вибрані клітинки в наборі даних.
7. Клацніть правою кнопкою миші будь-яку з виділених клітинок і натисніть Видалити. Відкриється діалогове вікно Видалити.
8. Виберіть опцію «Весь рядок»
9. Натисніть OK.

Вищезазначені кроки видалять усі клітинки, для яких регіон має значення Середній Захід.

Примітка. Оскільки функція "Знайти та замінити" може обробляти символи підстановки, їх можна використовувати під час пошуку даних у Excel. Наприклад, якщо ви хочете видалити всі рядки, де регіон знаходиться на Середньому Заході або Південному Заході, ви можете скористатись "*Захід"Як текст для пошуку в діалоговому вікні" Знайти та замінити ". Це дасть вам усі клітинки, де текст закінчується словом Захід.

Видалити всі рядки з порожнім осередком

Якщо ви хочете видалити всі рядки, де є порожні клітинки, ви можете легко це зробити за допомогою вбудованої функції Excel.

Це Перейти до спеціальних клітин опція - яка дозволяє швидко виділити всі порожні клітинки. І як тільки ви вибрали всі порожні клітинки, видалити їх дуже просто.

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

Нижче наведено кроки для цього.

1. Виберіть весь набір даних (у цьому випадку A1: D16).
2. Натисніть кнопку F5 ключ. Відкриється діалогове вікно "Перейти" (Ви також можете отримати це діалогове вікно з Домашнього -> Редагування -> Знайти та вибрати -> Перейти).
3. У діалоговому вікні "Перейти" натисніть кнопку Спеціальне. Відкриється діалогове вікно «Перейти до спеціального»
4. У діалоговому вікні "Перейти до спеціального" виберіть "Пробіли".
5. Натисніть OK.

Наведені вище кроки вибиратимуть усі порожні клітинки в наборі даних.

Вибравши порожні клітинки, клацніть правою кнопкою миші будь-яку з клітинок і натисніть Видалити.

У діалоговому вікні «Видалити» виберіть опцію «Весь рядок» і натисніть «ОК». Буде видалено всі рядки, у яких є порожні клітинки.

Якщо вам цікаво дізнатися більше про цю техніку, я написав детальний підручник про те, як видалити рядки з порожніми осередками. Він включає метод «Перейти до спеціального», а також метод VBA для видалення рядків із порожніми осередками.

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

Останній метод, який я вам покажу, включає трохи VBA.

Ви можете використовувати цей метод, якщо вам часто потрібно видаляти рядки на основі певного значення у стовпці. Ви можете додати код VBA один раз і додати його до книги "Особистий макрос". Таким чином, він буде доступний для використання у всіх ваших робочих книгах Excel.

Цей код працює так само, як метод Filter, описаний вище (за винятком того факту, що він виконує всі дії в бекенді та заощаджує деякі кліки).

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

Нижче наведено код VBA, який це зробить.

Sub DeleteRowsWithSpecificText () 'Джерело: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Поле: = 2, Критерії1: = "Середній Захід" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible). Видалити End Sub

У наведеному вище коді використовується метод автофільтрації VBA, щоб спочатку відфільтрувати рядки на основі зазначених критеріїв (тобто "Середній Захід"), потім виділити всі відфільтровані рядки та видалити його.

Зауважте, що я використав Зсув у наведеному вище коді, щоб переконатися, що мій рядок заголовка не видалений.

Наведений вище код не працює, якщо ваші дані є в таблиці 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: = 2, Критерії1: = "Середній Захід" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible).

Оскільки VBA розглядає таблицю Excel як об’єкт списку (а не діапазон), мені довелося відповідно змінити код.

Де розмістити код VBA?

Цей код потрібно розмістити в бекенді редактора VB в модулі.

Нижче наведено кроки, які покажуть вам, як це зробити:

1. Відкрийте книгу, до якої потрібно додати цей код.
2. Відкрийте вікно редактора VBA за допомогою комбінації клавіш ALT + F11.
3. У цьому вікні редактора VBA ліворуч є панель «Провідник проектів» (у якій перераховані всі книги та об’єкти робочих аркушів). Клацніть правою кнопкою миші будь-який об’єкт у книзі (у якому ви хочете, щоб цей код працював), наведіть курсор на «Вставити», а потім натисніть «Модуль». Це додасть об’єкт Module до книги, а також відкриє вікно коду модуля праворуч
4. У вікні модуля (яке з'явиться праворуч) скопіюйте та вставте вищевказаний код.

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

1. Виберіть будь -який рядок у коді та натисніть клавішу F5.
2. Натисніть кнопку Виконати на панелі інструментів у редакторі VB
3. Призначте макрос кнопці або фігурі та запустіть її, натиснувши на неї на самому аркуші
4. Додайте його на панель швидкого доступу та запустіть код одним клацанням миші.

У цій статті ви можете прочитати все про те, як запустити код макросу в Excel.

Примітка: Оскільки книга містить код макросу VBA, його потрібно зберегти у форматі з підтримкою макросів (xlsm).