Как удалить каждую вторую строку в Excel
Обновлено: 06.07.2024
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Обзор
Эта статья содержит пример макроса Microsoft Visual Basic для приложений, который можно использовать для удаления каждой второй строки в выбранном диапазоне на листе Microsoft Excel.
Дополнительная информация
Microsoft предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий. Это включает, но не ограничивается, подразумеваемые гарантии товарного состояния или пригодности для конкретной цели. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, которые используются для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональность конкретной процедуры, но они не будут изменять эти примеры, чтобы обеспечить дополнительную функциональность или создавать процедуры в соответствии с вашими конкретными требованиями. Чтобы использовать следующий макрос, настройте книгу Excel следующим образом:
Создайте новую книгу Microsoft Excel.
На новом листе в диапазоне A1:A9 введите числа от 1 до 5, пропуская строки, как в следующем примере:
В меню "Инструменты" выберите пункт "Макрос", а затем щелкните "Редактор Visual Basic".
Примечание. В Microsoft Office Excel 2007 и более поздних версиях нажмите Visual Basic в группе «Код» на вкладке «Разработчик».
В меню "Вставка" нажмите "Модуль".
В новом модуле введите следующий макрос:
Переключитесь на рабочий лист, содержащий данные, и выберите диапазон A1:A9.
Чтобы запустить макрос, выберите пункт "Макрос" в меню "Инструменты" и нажмите "Макросы".
- В Excel 2007 и более поздних версиях нажмите "Макросы" в группе "Код" на вкладке "Разработчик".
- Чтобы отобразить вкладку "Разработчик" на ленте, нажмите кнопку Microsoft Office, выберите "Параметры Excel", выберите категорию "Популярные", установите флажок "Показать вкладку "Разработчик" на ленте и нажмите "ОК".
Выберите макрос Delete_Every_Other_Row и нажмите кнопку "Выполнить".
Этот макрос удалит каждую вторую строку, начиная со второй строки выделения.
Если у вас есть список данных, содержащий несколько столбцов, выберите только первый столбец данных, а затем запустите макрос.
Иногда может возникнуть необходимость удалить каждую вторую строку (т. е. чередующиеся строки) или каждую третью, четвертую или пятую строку в Excel.
Одним из вариантов использования этого может быть ситуация, когда у вас есть еженедельные данные, и вам нужны данные только за четные или нечетные недели. Другой может быть, когда вы получаете дамп данных из базы данных, и все полезные данные находятся только в каждой второй или третьей строке.
Хотя у вас всегда есть возможность вручную выбирать и удалять строки, это неэффективно, если у вас большой набор данных.
Есть лучшие способы сделать это.
В этом руководстве я покажу вам несколько способов удаления каждой второй строки в Excel с помощью простой техники фильтрации. И если вы не против использовать код макроса VBA, я также дал короткий код VBA, который делает это одним щелчком мыши. Методы показаны в этом руководстве для всех версий Excel (2007, 2010, 2013 и 2016)
Хотя удалить каждую вторую строку в Excel может быть довольно просто, удалить каждый второй столбец может быть непросто. Это связано с тем, что вы не можете фильтровать столбцы в Excel (как вы можете фильтровать строки). В последнем разделе этого руководства я также покажу вам несколько способов удаления альтернативных столбцов (или каждого N-го столбца) в Excel.
Это руководство охватывает:
Удалить каждую вторую строку путем фильтрации набора данных (с использованием формулы)
Если бы вы могли каким-то образом отфильтровать все четные или нечетные строки, удалить эти строки/записи было бы очень просто.
Хотя для этого нет встроенной функции, вы можете использовать вспомогательный столбец, чтобы сначала разделить строки на нечетные и четные, а затем отфильтровать на основе значения вспомогательного столбца.
Предположим, у вас есть набор данных, как показано ниже, в котором есть данные о продажах для каждого торгового представителя в двух регионах (США и Канада), и вы хотите удалить данные для Канады.
Ниже приведены шаги, которые нужно выполнить, чтобы сначала отфильтровать, а затем удалить все остальные строки (в которых есть данные по Канаде):
Вышеуказанные шаги фильтруют каждую вторую строку в наборе данных, а затем удаляют эти строки.
На этом этапе не беспокойтесь о значениях вспомогательного столбца. Результирующие данные содержат только строки для США, а все строки для Канады удалены. Теперь вы можете удалить вспомогательный столбец.
Если вы хотите удалить каждую вторую строку, начиная с первой строки и далее, выберите параметр ИСТИНА на шаге 7 и отмените выбор параметра ЛОЖЬ.
Примечание. Когда вы удаляете строки в Excel с помощью описанного выше метода, также удаляются все данные, которые могут быть у вас в целом (кроме данных в наборе данных). При использовании этого метода убедитесь, что слева и справа от удаляемого набора данных нет ничего.
Хотя описанный выше метод хорош, у него есть два недостатка:
- Необходимо добавить новый столбец (HelperColumn в нашем примере выше)
- Это может занять много времени, если вам нужно часто удалять альтернативные строки.
Удалить каждую N-ю строку путем фильтрации набора данных (с использованием формулы)
В приведенном выше методе я показал вам, как удалить каждую вторую строку (альтернативную строку) в Excel.
И вы можете использовать ту же логику для удаления каждой третьей или четвертой строки в Excel.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить каждую третью строку.
Действия по удалению каждой третьей строки почти такие же, как и в предыдущем разделе (для удаления чередующихся строк). Единственное отличие заключается в формуле, используемой на шаге 2.
Ниже приведены шаги для этого:
Вышеуказанные шаги удалят каждую третью строку из набора данных, и вы получите результирующие данные, как показано ниже.
На этом этапе не беспокойтесь о значениях вспомогательного столбца. Теперь вы можете удалить вспомогательный столбец.
В формуле, использованной на шаге 2, используется функция ОСТАТ, которая дает остаток от деления одного числа на другое. Здесь я использовал функцию ROW, чтобы получить номер строки и разделить его на 3 (потому что мы хотим удалить каждую третью строку).
Я приравниваю его к 1, и для каждой третьей строки формула MOD дает остаток равный 1.
Вы можете соответствующим образом изменить формулу. Например, если первая запись вашего набора данных начинается с третьей строки и далее, формула будет =MOD(ROW(),3)=2
Автоматическое удаление каждой второй строки (или N-й строки) с помощью макроса VBA (быстрый метод)
Если вам приходится удалять каждую вторую строку (или каждую n-ю строку) довольно часто, вы можете использовать код VBA и сделать его доступным на панели быстрого доступа. Это позволит вам быстро удалить чередующиеся строки одним щелчком мыши.
Ниже приведен код, который сначала предлагает вам выбрать диапазон, в котором вы хотите удалить альтернативные строки, а затем удалить каждую вторую строку в выбранном наборе данных.
Когда вы запускаете приведенный выше код, он сначала попросит вас выбрать диапазон ячеек. После того, как вы сделали выбор, он будет проходить по каждой строке и удалять каждую вторую строку.
Если вы хотите удалить каждую третью строку, вы можете использовать следующий код:
Куда поместить этот код макроса VBA?
Вам нужно поместить этот код в обычный модуль в редакторе VB в Excel.
Ниже приведены шаги, чтобы открыть редактор Vb, добавить модуль и поместить в него код:
После того как у вас есть код в редакторе VB, вы можете использовать следующие методы для запуска кода:
- Запустите макрос из редактора VB (нажав зеленую кнопку воспроизведения на панели инструментов редактора VB)
- Поместив курсор на любую строку кода и нажав клавишу F5
- Назначая макрос кнопке/фигуре
- Добавив макрос на панель быстрого доступа.
Если вам приходится делать это часто, вы можете добавить код VBA в личную книгу макросов. Таким образом, он всегда будет доступен для использования во всех книгах.
Если у вас есть код VBA в рабочей книге, вам необходимо сохранить его как поле с поддержкой макросов (с расширением .XLSM)
Примечание. Поскольку любые изменения, внесенные кодом VBA, необратимы, лучше сначала сделать резервную копию книги/листа, а затем запустить этот код.
Удалить каждый второй столбец (или каждый N-й столбец)
Удаление каждой альтернативной строки или каждой третьей/четвертой строки упрощается благодаря использованию параметра фильтра. Все, что вам нужно сделать, это использовать формулу, которая идентифицирует чередующиеся строки (или каждую третью/четвертую строку) и фильтрует эти строки.
К сожалению, тот же метод не работает со столбцами, поскольку вы не можете фильтровать столбцы так же, как фильтруете строки.
Итак, если вам нужно удалить каждый второй столбец (или каждый третий/четвертый/N-й столбец), вам нужно проявить немного изобретательности.
В этом разделе я покажу вам два метода, которые вы можете использовать для удаления каждого второго столбца в Excel (и вы можете использовать тот же метод для удаления каждого третьего/четвертого/N-го столбца, если хотите).
Удалить альтернативные столбцы с помощью формул и метода сортировки
Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить все остальные столбцы (кроме столбца заголовка A)
Хитрость здесь заключается в том, чтобы определить альтернативные столбцы с помощью формулы, а затем отсортировать столбцы на основе этой формулы. После того, как вы соберете отсортированные столбцы, вы можете выбрать и удалить их вручную.
Ниже приведены действия по удалению всех остальных столбцов в Excel:
Вышеуказанные шаги сортируют все столбцы и объединяют все альтернативные столбцы в одном месте (в конце).
Теперь вы можете выбрать все эти столбцы (для которых значение формулы равно 1) и удалить их.
Хотя это и не лучшее решение, оно все же делает свою работу.
Если вы хотите удалить каждый третий столбец или каждый четвертый столбец, вам необходимо соответствующим образом изменить формулу.
Удалить альтернативные столбцы с помощью VBA
Еще один быстрый способ удалить альтернативные столбцы — использовать приведенный ниже код VBA:
Приведенный выше код предлагает вам выбрать диапазон ячеек, в котором есть столбцы. Здесь вам нужно выбрать столбцы, кроме того, который имеет заголовок.
После того как вы укажете диапазон ячеек с данными, он будет использовать цикл For и удалять все остальные столбцы.
Если вы хотите удалить каждый третий столбец, вы можете использовать приведенный ниже код (и изменить его соответствующим образом, чтобы удалить N-й столбец)
Инструкции по размещению этого кода VBA и его использованию описаны в приведенном выше разделе под названием "Автоматическое удаление каждой второй строки (или N-й строки) с помощью макроса VBA (быстрый метод)"
При работе с данными в Excel у вас могут быть повторяющиеся или нежелательные строки после каждой соответствующей строки. Есть несколько способов удалить каждую вторую строку, сохранив только нужные данные.
Удалить каждую вторую строку с форматом ячейки и фильтром
Фильтрация — это один из способов удалить каждую вторую строку.
<р>1. Выберите строки для копирования. На ленте выберите Главная > Ячейки > Форматировать как таблицу и выберите требуемое форматирование.
Затем пустые строки будут удалены из оставшихся данных.
Удалить каждую вторую строку со вспомогательным столбцом
Еще один способ скопировать каждую альтернативную строку в Excel — использовать вспомогательный столбец. Вы также можете использовать это для копирования каждой n-й (3-й, 4-й и т. д.) строки.
<р>1. В пустом столбце рядом с данными введите ИСТИНА в первой строке и ЛОЖЬ в строке под ней.
Затем пустые строки будут удалены из оставшихся данных.
Удалить каждую вторую строку в Excel также можно, создав макрос с помощью VBA.
Как удалить каждую вторую строку в Google Таблицах
Вы можете удалить каждую вторую строку в Google Таблицах, используя любой из описанных выше методов точно таким же образом.
В этом руководстве подробно описано удаление каждой второй строки в Excel. Требованием времени может быть некоторая систематическая выборка или выборка, хранение данных за четные недели/месяцы или, возможно, просто сокращение половины слишком большого объема информации.
В этом руководстве мы демонстрируем удаление чередующихся строк с помощью функций, фильтрации и VBA. Мы используем результаты функций, чтобы сгруппировать N-е строки и удалить их. Пока мы говорим об удалении каждой 2-й строки, имеет смысл остановиться на удалении каждой N-й строки, и еще лучше приступить к работе. Давайте погрузимся!
Как удалить каждую вторую строку с помощью фильтрации на основе формулы
Когда вы думаете об удалении чередующихся строк, у вас может возникнуть ментальная карта: сохранить либо строки с четными номерами, либо строки с нечетными номерами. Это именно то, что мы будем делать здесь. Мы будем использовать функцию ISEVEN, чтобы обозначить каждую строку как четную или нечетную.
Затем мы отфильтруем данные, чтобы сгруппировать строки с четными номерами, выберем и удалим их, раскроем фильтр, оставив строки с нечетными номерами. Удаление каждой строки с четным номером равносильно удалению всех остальных строк. Теперь давайте покажем вам шаги:
- Добавьте заголовок столбца справа от набора данных.Этот столбец будет использоваться как вспомогательный столбец и будет удален позже. Добавление заголовка поможет с параметрами фильтрации.
-
Добавьте следующую формулу в новый столбец:
- Если формула заполнена до C22, остальные строки в столбце ISEVEN отображаются как TRUE для строк с четными номерами и FALSE для строк с нечетными номерами.
- Выберите любую ячейку в наборе данных, перейдите в группу Редактирование на вкладке Главная, выберите Сортировка и фильтрация, а затем выберите Фильтр из меню (или используйте сочетание клавиш Ctrl + Shift + L).
- После добавления фильтров нажмите стрелку фильтра столбца ISEVEN, чтобы развернуть параметры фильтрации. Снимите один из флажков; либо ИСТИНА, либо ЛОЖЬ. Мы снимем флажок FALSE.
- Нажмите ОК. Будут отображены все ИСТИННЫЕ строки (четные строки) набора данных.
- Выберите все ИСТИННЫЕ строки в наборе данных и удалите их.
- У вас останутся только заголовки столбцов, поскольку ИСТИННЫЕ строки были удалены, а ЛОЖНЫЕ строки не отображаются.
- Снова нажмите стрелку столбца ISEVEN и нажмите кнопку (Выбрать все)
- Нажмите ОК.
- Результаты функции ЕСЧЕТ будут пересчитаны, но не беспокойтесь об этом; все отображаемые строки являются изначально ЛОЖНЫМИ (нечетными) строками.
- Выделив любую ячейку из набора данных, нажмите Ctrl + Shift + L, чтобы удалить фильтры (или повторно выберите параметр Фильтр в меню ленты).
- Удалите столбец ISEVEN и при необходимости исправьте форматирование.
Там мы удалили каждую вторую строку (и подчистили бардак от черновой работы). Мы также можем использовать функцию MOD (вы увидите, как это делается в последнем сегменте этого руководства) вместо функции ISEVEN для удаления чередующихся строк.
Как удалить альтернативные строки с помощью VBA
Для тех, кто знаком с VBA, у нас есть метод удаления чередующихся строк на основе макросов. Для тех, кто не знаком с VBA, у вас есть шанс узнать что-то новое. С помощью VBA можно создавать пользовательские функции, чтобы ускорить и упростить ручную работу с Excel.
Мы создадим макрос Excel (удалив чередующиеся строки), закодировав его с помощью VBA. Затем макрос можно запустить из окна, в котором он был создан. Для повторного использования к макросу можно получить доступ из меню ленты для повторного выполнения автоматизированной задачи. Ниже приведены шаги по созданию макроса для удаления альтернативных строк:
- Откройте редактор VBA, нажмите кнопку Visual Basic в Разработчике. Если вы не включили Разработчик em>, нажмите Alt + F11, чтобы открыть редактор VBA. Вот как это выглядит:
- Выберите Вставка, затем выберите в меню Модуль.
- Откроется окно Модуль. В окне модуля скопируйте и вставьте следующий код:
- Чтобы немедленно запустить команду из этого окна, нажмите клавишу F5, затем закройте окно Module и редактор.
- Чтобы запустить команду позже, закройте окно Module и редактор VBA после ввода кода.
- Перейдите на вкладку Вид и нажмите кнопку Макросы (чтобы получить доступ к Макросам с помощью сочетания клавиш, нажмите Alt + F8).
- Выберите соответствующий макрос в диалоговом окне Макрос и нажмите командную кнопку Выполнить.
- Появится диалоговое окно для выбора диапазона, из которого необходимо удалить альтернативные строки. В диалоговом окне введите или выберите диапазон для удаления чередующихся строк.
- Линия марширующих муравьев представляет выбранный диапазон.
- Нажмите ОК.
Теперь строки будут удалены.
Вы можете заметить, что с макросами нет пути назад; никакое количество отмены Ctrl + Z не отменит это. Однако есть данные, в которых удалена каждая вторая строка.
Как удалить каждую N-ю строку
В приведенном выше сегменте мы рассказали, как изменить коды макросов, чтобы удалить каждую N-ю строку. Теперь мы научим вас, как удалить каждую 3-ю, 4-ю или любую N-ю строку, которую вы хотите удалить, объединив функции MOD и ROW вместе и отфильтровав результаты, чтобы собрать и удалить каждую N-ю строку.
Функция ОСТАТ возвращает остаток после деления числа на делитель. Функция ROW возвращает номер строки. Почему они работают вместе, вы узнаете в следующих шагах:
- Добавьте заголовок столбца в правую часть набора данных в качестве вспомогательного столбца, который мы будем использовать для формулы, а затем удалим.
-
Добавьте следующую формулу в новый столбец и заполните ее вместе с последней ячейкой набора данных.
Функция ROW была введена без аргумента, поэтому функция вернет номер строки листа, на котором вводится функция, т. е. здесь 3. Функция ОСТАТ берет номер строки и делит его на 3. Результат деления 3 на 3 равен 1, что является первым битом формулы. Последний бит формулы равен "=2".
Формула принимает вид 1=2. Это FALSE и окончательный результат формулы.
Как узнать, какие числа добавить в формулу?
3 — N-я удаляемая строка. Мы хотим удалить каждую 3-ю строку и ввели 3 здесь.
Сейчас у нас есть FALSE, FALSE, TRUE, FALSE, FALSE, TRUE и далее. Если мы отредактируем «2» в формуле на «1», у нас будет ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ, ИСТИНА и далее. Поскольку будут удалены все ИСТИННЫЕ строки, эта цепочка определяет, какие строки будут удалены.
- Выбрав любую ячейку в наборе данных, перейдите на вкладку Главная, раздел Редактирование, кнопку Сортировка и фильтр, Фильтр< /эм>. Вместо этого вы также можете использовать сочетание клавиш Ctrl + Shift + L.
- К столбцам будут применены фильтры. Щелкните фильтр столбца MOD & ROW. Снимите флажок FALSE в раскрывающемся меню.
- Нажмите ОК.
- Все строки с TRUE в результатах формулы будут расположены вместе.
- Выберите все отображаемые строки (кроме заголовков столбцов) и удалите их.
- Снова откройте параметры фильтра столбца MOD & ROW и установите флажок (Выбрать все), чтобы отобразить оставшиеся данные.
- Нажмите ОК.
- Удалите столбец MOD & ROW и удалите фильтры, нажав Ctrl + Shift + L, выбрав любую ячейку из данных.
- В этом примере была удалена каждая третья строка.
Это был наш подход к удалению чередующихся строк или каждой N-й строки. Мы надеемся, что предложили вам самые простые методы, а также дали вам возможность поработать с кодами и функциями, чтобы вы могли получить желаемые результаты. Попробуйте один из этих методов, когда вам нужно удалить каждую N-ю строку вместо взлома ручных навыков. Мы вернемся с новыми материалами из Excel city, чтобы ваши навыки ручного труда отдохнули, а навыки работы с Excel пригодились!
Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!
Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.
Читайте также: