Как удалить все остальные строки в Excel (или каждую N-ю строку)

Посмотреть видео - удалить все остальные строки в Excel

Иногда вам может потребоваться удалить каждую вторую строку (т. Е. Чередующиеся строки) или каждую третью / четвертую / пятую строку в Excel.

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

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

Есть способы сделать это лучше.

В этом уроке я покажу вам несколько способов удалить каждую вторую строку в Excel с помощью простой техники фильтрации. И если вам нравится использовать макрокод VBA, я также дал короткий код VBA, который делает это одним щелчком мыши. В этом учебном пособии методы показаны для всех версий Excel (2007, 2010, 2013 и 2016).

Хотя удаление каждой второй строки в Excel может быть довольно простым делом, удаление всех остальных столбцов может оказаться проблемой. Это потому, что вы не можете фильтровать столбцы в Excel (как вы можете фильтровать строки). В последнем разделе этого руководства я также покажу вам несколько способов удаления альтернативных столбцов (или каждого N-го столбца) в Excel.

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

Если бы вы могли каким-то образом отфильтровать все четные или нечетные строки, было бы очень легко удалить эти строки / записи.

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

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

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

1. В ячейке рядом с заголовком последнего столбца введите текст HelperColumn (или любой текст заголовка, который должен иметь вспомогательный столбец)
2. В ячейке под заголовком вспомогательного столбца введите следующую формулу: = ЕВРОПЕЙСКОЕ (СТРОКА ()). Скопируйте эту формулу для всех ячеек. Эта формула возвращает ИСТИНА для всех четных строк и ЛОЖЬ для всех НЕЧЕТНЫХ строк.
3. Выберите весь набор данных (включая ячейки, в которые мы ввели формулу на предыдущем шаге).
4. Перейдите на вкладку "Данные".
5. Щелкните значок фильтра. Это применит фильтр ко всем заголовкам в наборе данных.
6. Щелкните значок фильтра в ячейке HelperColumn.
7. Отмените выбор параметра ИСТИНА (поэтому будет выбран только параметр ЛОЖЬ)
8. Щелкните ОК. Это отфильтрует данные и покажет только те записи, в которых значение HelperColumn равно FALSE.
9. Выберите все отфильтрованные ячейки в вспомогательном столбце (за исключением заголовка)
10. Щелкните правой кнопкой мыши любую из выбранных ячеек и выберите «Удалить строку».
11. В открывшемся диалоговом окне нажмите ОК. Это удалит все видимые записи, и на данный момент вы будете видеть только строку заголовка.
12. Щелкните вкладку «Данные», а затем щелкните значок «Фильтр». Фильтр будет удален, и вы сможете увидеть все оставшиеся записи.

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

На этом этапе не беспокойтесь о значениях вспомогательных столбцов.. В полученных данных есть только строки для США, а все строки для Канады удалены. Теперь вы можете удалить вспомогательный столбец.

Если вы хотите удалить каждую вторую строку, начиная с первой строки, выберите параметр ИСТИНА на шаге 7 и снимите флажок ЛОЖЬ.

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

Хотя описанный выше метод хорош, у него есть два недостатка:

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

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

Удалите каждую N-ю строку, отфильтровав набор данных (используя формулу)

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

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

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

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

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

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

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

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

Формула, использованная на шаге 2, использует функцию МОД, которая дает остаток от деления одного числа на другое. Здесь я использовал функцию ROW, чтобы получить номер строки, и он разделен на 3 (потому что мы хотим удалить каждую третью строку).

Поскольку наш набор данных начинается со второй строки (т.е. строка № 2 содержит первую запись), я использую следующую формулу:= МОД (СТРОКА (); 3) = 1

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

Вы можете соответствующим образом скорректировать формулу. Например, если первая запись вашего набора данных начинается с третьей строки и далее, формула будет выглядеть так: = МОД (СТРОКА (); 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 Then Rng.Rows (i). Удалить конец, если следующий i End Sub

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

Если вместо этого вы хотите удалить каждую третью строку, вы можете использовать приведенный ниже код:

Sub Delete_Every_Third_Row () Dim Rng As Range Set Rng = Application.InputBox («Выбрать диапазон (исключая заголовки)», «Выбор диапазона», Type: = 8) For i = Rng.Rows.Count To 1 Step -3 If i Mod 3 = 0 Then Rng.Rows (i). Удалить конец, если следующий i End Sub

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

Вам нужно разместить этот код в штатном модуле редактора VB в Excel.

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

1. Скопируйте указанный выше код VBA.
2. Перейдите на вкладку Разработчик.
3. Щелкните Visual Basic.
4. В редакторе VB щелкните правой кнопкой мыши любой из объектов книги.
5. Перейдите во вкладку "Вставить" и нажмите "Модуль".
6. Вставьте в модуль указанный выше код VBA.
7. Закройте редактор VB.

Когда у вас есть код в редакторе VB, вы можете использовать следующие методы для запуска кода:

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

Вот подробное руководство о том, как запустить макрос в Excel.

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

Если у вас есть код VBA в книге, вам необходимо сохранить его как поле с поддержкой макросов (с расширением .XLSM).

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

Удалить все остальные столбцы (или каждый N-й столбец)

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

К сожалению, эта же методология не работает со столбцами, поскольку вы не можете фильтровать столбцы так, как фильтруете строки.

Итак, если вам нужно удалить все остальные столбцы (или каждый третий / четвертый / N-й столбец), вам нужно проявить немного больше творчества.

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

Удаление альтернативных столбцов с помощью формул и метода сортировки

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

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

Ниже приведены шаги по удалению всех остальных столбцов в Excel:

1. Вставьте строку над строкой заголовка (щелкните правой кнопкой мыши любую ячейку в заголовке, нажмите «Вставить», а затем выберите параметр «Вся строка»)
2. Введите следующую формулу в ячейку над крайним левым столбцом в наборе данных и скопируйте для всех ячеек: = МОД (КОЛОНКА (); 2)
3. Преобразуйте значения формулы в числа. Для этого скопируйте ячейки, щелкните правой кнопкой мыши и перейдите в меню «Специальная вставка» -> «Вставить значения».
4. Выберите весь набор данных (за исключением заголовков в столбце). В этом примере я выбрал B1: G14 (и НЕТ столбец A)
5. Перейдите на вкладку "Данные".
6. Щелкните значок сортировки
7. В диалоговом окне Сортировка внесите следующие изменения:
   • Нажмите кнопку «Параметры» и в открывшемся диалоговом окне нажмите «Сортировать слева направо», а затем нажмите «ОК».
   • В раскрывающемся списке Сортировать по выберите Строка 1. Это строка, которая содержит результаты формулы.
   • В раскрывающемся списке «Порядок» выберите «От наименьшего к наибольшему».
8. Нажмите ОК.

Вышеупомянутые шаги сортируют все столбцы и объединяют все альтернативные столбцы в одном месте (в конце).

Теперь вы можете выбрать все эти столбцы (для которых значение формулы равно 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 Then Rng.Columns (i). Удалить End If Next 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 If i Mod 3 = 0 Then Rng.Columns (i). Удалить End If Next i End Sub

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

Надеюсь, вы нашли это руководство по Excel полезным.