Як видалити кожну іншу рядок в Excel (або кожну N -ту рядок)

Перегляд відео - Видалення всіх інших рядків у Excel

Іноді вам може знадобитися видалити кожен другий рядок (тобто альтернативні рядки) або кожен третій/четвертий/п'ятий рядок у Excel.

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

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

Є кращі способи зробити це.

У цьому посібнику я покажу вам кілька способів видалення кожного другого рядка в Excel за допомогою простої техніки фільтрації. І якщо вам добре користуватися кодом макросу VBA, я також дав короткий код VBA, який робить це одним натисканням кнопки. Методи показані в цьому посібнику для всіх версій Excel (2007, 2010, 2013 та 2016)

Хоча видалення кожного другого рядка в Excel може бути досить простим, видалити кожен інший стовпець може бути проблемою. Це тому, що ви не можете фільтрувати стовпці в Excel (спосіб фільтрації рядків). В останньому розділі цього підручника я також покажу вам деякі способи видалення альтернативних стовпців (або кожного N -го стовпця) у Excel.

Видалити кожну іншу рядок шляхом фільтрації набору даних (за допомогою формули)

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

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

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

Нижче наведено кроки спочатку фільтрування, а потім видалення кожного другого рядка (який містить дані для Канади):

1. Введіть текст у комірці, що прилягає до заголовка останнього стовпця HelperColumn (або будь -який текст заголовка, який ви хочете мати у стовпці -помічнику)
2. У клітинці під заголовком стовпця -помічника введіть таку формулу: = ISEVEN (ROW ()). Скопіюйте цю формулу для всіх клітинок. Ця формула повертає TRUE для всіх парних рядків та FALSE для всіх рядків ODD
3. Виберіть весь набір даних (включаючи клітинки, де ми ввели формулу на кроці вище).
4. Перейдіть на вкладку Дані
5. Натисніть на значок фільтра. Це застосує фільтр до всіх заголовків у наборі даних
6. Натисніть на значок фільтра у клітинці HelperColumn.
7. Зніміть прапорець з пункту ІСТИНА (тому вибрано лише параметр FALSE)
8. Натисніть OK. Це відфільтрує дані та покаже лише ті записи, де значення HelperColumn - FALSE.
9. Виберіть усі відфільтровані комірки у стовпці -помічнику (крім заголовка)
10. Клацніть правою кнопкою миші будь-яку з виділених клітинок і натисніть «Видалити рядок»
11. У діалоговому вікні, що відкриється, натисніть кнопку OK. Це видалить усі видимі записи, і ви побачите лише рядок заголовка.
12. Перейдіть на вкладку Дані, а потім - на значок фільтра. Це призведе до видалення фільтра, і ви зможете побачити всі записи, що залишилися.

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

На цьому етапі не турбуйтеся про значення допоміжних стовпців. Отримані дані містять лише рядки для США, а всі рядки Канади видаляються. Тепер можна видалити стовпець -помічник.

Якщо ви хочете видалити кожен другий рядок, починаючи з першого рядка, виберіть опцію ІСТИНА на кроці 7 і зніміть позначку з опції ЛОЖЬ.

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

Хоча вищезгаданий метод чудовий, він має два недоліки:

1. Вам потрібно додати новий стовпець ( HelperColumn у нашому прикладі вище)
2. Це може зайняти багато часу, якщо вам доведеться часто видаляти альтернативні рядки

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

Видалити кожен N -й рядок, відфільтрувавши набір даних (за допомогою формули)

У наведеному вище способі я показав вам, як видалити кожен інший рядок (альтернативний рядок) у Excel.

І ви можете використовувати ту ж логіку, щоб видалити кожен третій або четвертий рядок у Excel.

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

Кроки видалення кожного третього рядка майже такі ж, як і описані у вищевказаному розділі (для видалення альтернативних рядків). Єдина відмінність - у формулі, що використовується на кроці 2.

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

1. Введіть текст у клітинку, прилеглу до заголовка останнього стовпця HelperColumn (або будь -який текст заголовка, який ви хочете мати у стовпці -помічнику)
2. У клітинці під заголовком стовпця -помічника введіть таку формулу: = MOD (ROW (), 3) = 1. Ця формула повертає TRUE для кожного третього рядка та FALSE для кожного другого рядка.
3. Виберіть весь набір даних (включаючи клітинки, де ми ввели формулу на кроці вище).
4. Перейдіть на вкладку Дані
5. Натисніть на значок фільтра. Це застосує фільтр до всіх заголовків у наборі даних
6. Натисніть піктограму Фільтр у комірці HelperColumn.
7. Зніміть прапорець з пункту ІСТИНА (тому вибрано лише параметр FALSE)
8. Натисніть OK. Це відфільтрує дані та покаже лише ті записи, де значення HelperColumn - FALSE.
9. Виділіть усі клітинки у стовпці -помічнику
10. Клацніть правою кнопкою миші будь-яку з виділених клітинок і натисніть Видалити рядок
11. У діалоговому вікні, що відкриється, натисніть кнопку OK. Це видалить усі видимі записи, і ви побачите лише рядок заголовка.
12. Перейдіть на вкладку Дані, а потім - на значок фільтра. Це призведе до видалення фільтра, і ви зможете побачити всі записи, що залишилися.

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

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

Формула, що використовується на кроці 2, використовує функцію MOD - яка дає залишок, коли одне число ділиться на інше. Тут я використав функцію ROW, щоб отримати номер рядка, і він ділиться на 3 (тому що ми хочемо видалити кожен третій рядок).

Оскільки наш набір даних починається з другого рядка (тобто рядок №2 містить перший запис), я використовую таку формулу:= MOD (ROW (), 3) = 1

Я прирівнюю його до 1, і для кожного третього рядка формула MOD дасть залишок як 1.

Ви можете відповідно змінити формулу. Наприклад, якщо перший запис вашого набору даних починається з третього рядка, формула буде такою = MOD (ROW (), 3) = 2

Автоматично видаляти всі інші рядки (або N -й рядок) за допомогою макросу VBA (швидкий метод)

Якщо вам доводиться часто видаляти кожен другий рядок (або кожен n -й рядок), ви можете використовувати код VBA і мати його доступним на Панелі швидкого доступу. Це дозволить вам швидко видалити альтернативні рядки одним клацанням миші.

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

Sub Delete_Every_Other_Row () Dim Rng As Range Set Rng = Application.InputBox ("Виберіть діапазон (виключаючи заголовки)", "Вибір діапазону", Тип: = 8) Для i = Rng.Rows.Count To 1 Step -2 Якщо i Mod 2 = 0 Тоді Rng.Rows (i). Видалити End, якщо далі i End Sub

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

Якщо ви хочете видалити кожен третій рядок, ви можете скористатися наведеним нижче кодом:

Sub Delete_Every_Third_Row () Dim Rng As Range Set Rng = Application.InputBox ("Виберіть діапазон (виключаючи заголовки)", "Вибір діапазону", Тип: = 8) Для i = Rng.Rows.Count To 1 Step -3 Якщо i Mod 3 = 0 Тоді Rng.Rows (i). Видалити End, якщо далі i End Sub

Де розмістити цей код макросу VBA?

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

Нижче наведено кроки, щоб відкрити редактор Vb, додати модуль і розмістити в ньому код:

1. Скопіюйте вищевказаний код VBA.
2. Перейдіть на вкладку Розробник.
3. Натисніть на Visual Basic.
4. У редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги.
5. Перейдіть до пункту Вставити та натисніть Модуль.
6. У модулі вставте вищевказаний код VBA.
7. Закрийте редактор VB.

Отримавши код у редакторі VB, ви можете скористатися такими методами для запуску коду:

• Запустіть макрос з редактора VB (натиснувши зелену кнопку відтворення на панелі інструментів VB Editor)
• Розмістивши курсор на будь -якому рядку коду та використовуючи клавішу F5
• Призначивши макрос кнопці/фігурі
• Додавши макрос на панель швидкого доступу

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

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

Якщо у вашій книзі є код VBA, його потрібно зберегти як файл із підтримкою макросів (з розширенням .XLSM)

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

Видалити кожну іншу колонку (або кожну N -ту колонку)

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

На жаль, ця ж методологія не працюватиме зі стовпцями - оскільки ви не можете фільтрувати стовпці так, як ви фільтруєте рядки.

Отже, якщо вам доведеться видалити кожен інший стовпець (або кожен третій/четвертий/N -й стовпець), вам потрібно проявити трохи більше творчості.

У цьому розділі я покажу вам два методи, які можна використовувати для видалення кожного іншого стовпця в Excel (і ви можете використовувати той самий метод для видалення кожного третього/четвертого/N -го стовпця, якщо хочете).

Видалення альтернативних стовпців за допомогою формул та методу сортування

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

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

Нижче наведено кроки для видалення всіх інших стовпців у Excel:

1. Вставте рядок над рядком заголовка (клацніть правою кнопкою миші будь-яку клітинку в заголовку, клацніть Вставити, а потім-опцію Весь рядок)
2. Введіть таку формулу у клітинку над крайнім лівим стовпцем у наборі даних та скопіюйте для всіх клітинок: = MOD (Стовпчик (), 2)
3. Перетворіть значення формули в числа. Для цього скопіюйте клітинки, клацніть правою кнопкою миші та перейдіть до пункту Спеціальна вставка -> Вставити значення.
4. Виберіть весь набір даних (за винятком заголовків у стовпці). У цьому прикладі я вибрав B1: G14 (і НІ стовпець А)
5. Перейдіть на вкладку Дані
6. Натисніть на значок Сортування
7. У діалоговому вікні Сортування внесіть такі зміни:
   • Натисніть кнопку «Параметри» і у діалоговому вікні, що відкриється, клацніть «Сортувати зліва направо», а потім натисніть «ОК»
   • У спадному списку Сортувати по виберіть Рядок 1. Це рядок із результатами формули
   • У спадному меню Порядок виберіть Найменший до найбільшого.
8. Натисніть OK

Наведені вище кроки сортують усі стовпці та об’єднують усі альтернативні стовпці в одному місці (в кінці).

Тепер ви можете вибрати всі ці стовпці (для яких значення формули дорівнює 1) і видалити їх.

Незважаючи на те, що це не найкраще рішення, воно все одно дозволяє виконати роботу.

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

Пов’язаний підручник: Як сортувати в Excel (за рядками, стовпцями, кольорами, датами та числами)

Видалення альтернативних стовпців за допомогою VBA

Ще один швидкий спосіб видалення альтернативних стовпців - це використання коду VBA нижче:

Sub Delete_Every_Other_Column () Dim Rng As Range Set Rng = Application.InputBox ("Виберіть діапазон (виключаючи заголовки)", "Вибір діапазону", Тип: = 8) Для i = Rng.Columns.Count To 1 Step -2 Якщо i Mod 2 = 0 Тоді Rng. Стовпці (i). Видалити End, якщо далі i End Sub

Наведений вище код пропонує вам вибрати діапазон клітинок, який містить стовпці. Тут вам потрібно вибрати стовпці, виключаючи той, який має заголовок.

Після того, як ви вказали діапазон клітинок з даними, він буде використовувати цикл For Loop і видаляти всі інші стовпці.

Якщо ви хочете видалити кожен третій стовпець, ви можете скористатися наведеним нижче кодом (і внести відповідні зміни, щоб видалити N -й стовпець)

Sub Delete_Every_Third_Column () Dim Rng As Range Set Rng = Application.InputBox ("Виберіть діапазон (виключаючи заголовки)", "Вибір діапазону", Тип: = 8) Для i = Rng.Columns.Count To 1 Step -3 Якщо i Mod 3 = 0 Тоді Rng. Стовпці (i). Видалити End, якщо далі i End Sub

Кроки щодо того, де розмістити цей код VBA та як його використовувати, описані у вищенаведеному розділі під назвою - «Автоматично видаляти кожен інший рядок (або N -й рядок) за допомогою макросу VBA (швидкий метод)»

Сподіваюся, цей підручник з Excel був вам корисним.