Как разделить четные и нечетные строки на две таблицы в Excel

Обновлено: 21.11.2024

Иногда может возникнуть необходимость удалить каждую вторую строку (т. е. чередующиеся строки) или каждую третью, четвертую или пятую строку в 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)

Проверить, является ли текущая строка нечетной:

ISODD — это функция, которая проверяет, является ли число четным или нечетным. Возвращает TRUE для нечетных строк и FALSE для четных строк.

СТРОКА возвращает номер строки ячейки. Если вы оставите функцию ROW пустой, без аргумента, как в этом примере, она будет ссылаться на строку, в которую вы поместили функцию; однако вы также можете включить в функцию ссылку на диапазон, и тогда она вернет номер строки для этой ссылки на диапазон.

Проверить, является ли текущая строка четной:

ISEVEN – это функция, которая проверяет, является ли число четным или нечетным. Возвращает FALSE для нечетных строк и TRUE для четных строк.

Одна формула для проверки четных/нечетных строк

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

Если вы введете их в Excel и скопируете, это будет выглядеть так:

Добавьте немного логики

Если вы хотите сделать что-то конкретное с нечетными/четными строками, вы можете обернуть вышеуказанные функции внутри оператора IF следующим образом:

В этом примере выводится значение «Четное» для четных строк и «Нечетное» для нечетных строк, но вы можете сделать все, что захотите.

Формула четных и нечетных строк (Excel 2002 и более ранние версии)

В более ранних версиях Excel у вас не было функций ISODD и ISEVEN, и вам приходилось использовать функцию MOD() вместе с небольшой логикой типа программирования.

Функция СТРОКА возвращает строку ячейки. Если вы оставите функцию ROW пустой, без аргумента, она будет ссылаться на строку, в которую вы поместили функцию; однако вы также можете включить в функцию ссылку на диапазон, и тогда она вернет номер строки для этой ссылки на диапазон.

MOD — это функция, которая действительно творит чудеса, и, хотя она выглядит пугающе, она довольно проста. Функция ОСТАТ возвращает остаток после деления числа на другое число. В этом случае он разделит текущий номер строки на 2; если оно может разделить число на 2 без остатка, то вы знаете, что это четное число и, в данном случае, четная строка.

Логика формулы знает, что строка четная, потому что она берет результат функции ОСТАТ, остаток после деления, а затем проверяет, равен ли он 0 или нет. Если остатка нет, то это то же самое, что 0, который проверяется в части =0 формулы, и Excel выводит аргумент ИСТИНА из оператора ЕСЛИ.

Вывод простой формулы True/False

Вам не нужно использовать оператор IF, если вы просто хотите получить значения TRUE и FALSE в Excel.

Измените 0 на 1, если вы хотите, чтобы он возвращал false для четных строк.

Дополнительные примеры

Примечания

Это простые примеры, которые позволят вам в дальнейшем строить формулы, которые могут выполнять мощные функции в Excel, такие как увеличение чисел на основе строки, затенение строк с использованием условного форматирования и многое другое.

Загрузите прилагаемую книгу, чтобы получить эти примеры в Excel.

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Сложные структурированные ссылки (табличные формулы) в Excel
Учебник: Как использовать сложные структурированные ссылки, табличные формулы в Excel. Если вы еще этого не сделали.

Преобразование номера столбца в букву с помощью формулы в Excel
Учебник: Как получить букву столбца из числа в Excel с помощью простой формулы. Это .

Формула поиска регулярного выражения в Excel - Regex
Учебное пособие: Часть 1. Формула соответствия регулярному выражению в Excel (без VBA). Часть 2. Полная регистрация Excel.

Расчет отработанных часов — дневная или ночная смена и перерывы в Excel
Учебное пособие: Единая простая формула для расчета количества отработанных часов за дневную или ночную смену и т. д.

Скрыть формулы в Excel
Учебное пособие: как скрыть формулу в Excel, чтобы пользователь не мог ее увидеть, выбрать или изменить — th.

5 советов по оценке сложных формул в Excel
Учебник : 5 простых советов по оценке любой сложной формулы в Excel! Сочетание этих советов поможет вам в этом.

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

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

Сообщить о нарушении

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

Попробуйте этот макрос.

Sub SeparateOddEvenRows()
Dim R As Long, C As Long, Xo As Long, Xe As Long
Dim Data As Variant, Odd As Variant, Even As Variant
Data = Range ("A3").CurrentRegion
ReDim Odd(1 To (UBound(Data) + 1) / 2, 1 To UBound(Data, 2))
ReDim Even(1 To (UBound(Data)) + 1) / 2, 1 в UBound(данные, 2))
Для R = 1 в UBound(данные)
Если R Mod 2, то
Xo = Xo + 1
Иначе
Xe = Xe + 1
End If
For C = 1 To UBound(Data, 2)
If R Mod 2 Then
Odd(Xo, C) = Данные(R, C)
Еще
Четные(Xe, C) = Данные(R, C)
Конец, если
Следующий
Следующий
Ячейки (3, Columns.Count).End(xlToLeft).Offset(, 2).Resize(UBound(Odd), UBound(Odd, 2)) = Odd
Cells(3, Columns.Count).End( xlToLeft).Offset(, 2).Resize(UBound(Even), UBound(Even, 2)) = Even
End Sub

Если этот ответ отвечает на ваш вопрос, отметьте его как «Ответ».

Сообщить о нарушении

2 человека сочли этот ответ полезным

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Хорошо, кажется, я наконец-то разобрался со своим толстым черепом. Посмотрите, делает ли это то, что вам нужно.

Sub SeparateOddEvenRows()
Dim R As Long, C As Long, Xo As Long, Xe As Long, FirstRow As Long, NewCol As Long
Dim Data As Variant, Odd As Long, Even As Вариант
FirstRow = IIf(Len(Range("A1").Value), 1, Range("A1").End(xlDown).Row)
Data = Range("A3", Cells (Cells(Rows.Count, "A").End(xlUp).Row, _
Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Столбец))
ReDim Odd(1 To (UBound(Data) + 1) / 2, 1 To UBound(Data, 2))
ReDim Even(1 To (UBound(Data) + 1) / 2, 1 To UBound(Data, 2))
Для R = 1 To UBound(Data)
Если R Mod 2 Then
Xo = Xo + 1
Еще
Xe = Xe + 1
End If
For C = 1 To UBound(Data, 2)
If R Mod 2 Then
Odd(Xo, C) = Data(R , C)
Еще
Четное(Xe, C) = Данные(R, C)
Конец, если
Следующий
Следующий
Новый столбец = Cells(FirstRow , Columns.Count).End(xlToLeft).Offset(, 4).Column
Cells(FirstRow, NewCol).Resize(UBound(Odd), UBound(Odd, 2)) = Odd
Cells (Rows.Count, NewCol).End(xlUp).Offset(3).Resize(UBound(Even), UBound(Even, 2)) = Even
End Sub

Если этот ответ отвечает на ваш вопрос, отметьте его как «Ответ».

Сообщить о нарушении

Мне нужна ваша помощь в установке фильтров . Можем ли мы отфильтровать ячейки на основе их четных или нечетных символов.

т.е. в таблице 1-1000 я хочу отфильтровать 1,3,5,7,…
< /цитата>
Должен сказать, странная просьба.

Но, тем не менее, возможно в Excel.

Если вы хотите отфильтровать нечетные или четные строки только в списке значений,

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

Добавьте дополнительный столбец в конце ваших данных.

Введите =ISEVEN(B3), чтобы проверить, является ли число в ячейке B3 четным или нечетным.

Перетащите формулу, чтобы заполнить весь диапазон.

Теперь отфильтруйте значения TRUE или FALSE.

Это все. Вы отфильтровали четные или нечетные строки.

PS: если в ваших данных нет порядковых номеров, вы можете использовать =ISEVEN(ROW())

Дополнительные советы:

Вы можете отфильтровать каждую третью строку, используя =MOD(B3,3), а затем отфильтровать все нули. Помогите с формулой MOD.

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

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

Этот клик — это все, что нужно для более подробной информации и фильтров. наслаждайтесь.

Просто читайте только четные или нечетные слова.

Поделитесь этим советом со своими коллегами

Получите БЕСПЛАТНЫЕ советы по Excel и Power BI

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

Учись и будь классным.

20 комментариев

Задайте вопрос или скажите что-нибудь.

Отмечено фильтрами данных, iseven(), Learn Excel, формулами Microsoft Excel, MOD(), кратким советом, вопросами читателей, row(), скринкастами, полосами зебры.

Категория: руководства по Excel

Большое спасибо за визит. Моя цель — сделать вас великолепными в Excel и Power BI. Я делаю это, делясь видео, советами, примерами и загрузками на этом сайте. Здесь более 1000 страниц со всеми функциями Excel, Power BI, Dashboards и VBA. Идите вперед и потратьте несколько минут, чтобы быть УДИВИТЕЛЬНЫМ. Прочтите мою историю • БЕСПЛАТНАЯ книга советов по Excel

От простого к сложному, есть формула на все случаи жизни.Ознакомьтесь со списком прямо сейчас.

Календари, счета-фактуры, трекеры и многое другое. Все бесплатно, весело и фантастически.

Power Query, модель данных, DAX, фильтры, слайсеры, условные форматы и красивые диаграммы. Все здесь.

Все еще думаете о Power BI? Из этого руководства по началу работы вы узнаете, что такое Power BI, как его получить и как создать свой первый отчет с нуля.

БЕСПЛАТНЫЙ шаблон календаря и ежемесячного планировщика на 2022 год для печати

Счастливого Рождества и Нового 2022 года

Как писать сложные формулы Excel (подсказка: это очень похоже на LEGO)

Сортировка значений в стиле таблицы олимпийских медалей [Совет]

Как использовать значения даты и времени в Excel — 10 + 3 совета

Советы по теме

Как писать сложные формулы Excel (подсказка: это очень похоже на LEGO)

Сортировка значений в стиле таблицы олимпийских медалей [Совет]

Как использовать значения даты и времени в Excel — 10 + 3 совета

Что такое XLOOKUP? 13 примеров формул, чтобы действительно понять это

Двухуровневая проверка данных [трюк с Excel]

Выделение сроков выполнения в Excel. Отображение сроков выполнения, просроченных и выполненных заданий разными цветами

20 ответов на вопрос «Как отфильтровать только четные или нечетные строки? [Советы]”

На самом деле, вместо использования =ISEVEN(B3), как насчет использования =ISEVEN(ROW())

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

Мне нравится ежедневная доза Excel

Необходимо активировать надстройку Analysis ToolPak, чтобы использовать функции ISEVEN / ISODD. Альтернативой ISEVEN может быть:
=MOD(ROW(),2)=0

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

Просто для уточнения, хочет ли Ашиш фильтровать по четным или нечетным символам или строкам?

так много функций для изучения!

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

Если вы хотите удалить вместо фильтрации,

ЕСЛИ ваши данные находятся в столбце A Листа1,
Поместите это в столбец A Листа2 и перетащите вниз
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,, )
(Это для удаления четных строк)

Чтобы удалить нечетные строки:
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)

Если ваши пронумерованные ячейки не соответствовали строкам, ответ был бы еще проще:
=MOD([адрес ячейки],2), затем отфильтруйте по 0, чтобы увидеть четные значения, или по 1, чтобы увидеть шансы.
Иногда я делаю это еще проще. Я добавляю новый столбец под названием «Знак» и помещаю значение 1 в первую строку, скажем, в ячейку C2, если C1 содержит заголовок. Затем в C3 я помещаю формулу =-1 * C2, которую копирую и вставляю в остальные строки (таким образом, C4 имеет =-1 * C3 и так далее). Теперь я могу просто применить фильтр и выбрать +1 или -1, чтобы увидеть половину строк.

Еще один способ, который работает, если мне нужны три возможности: в C2 я помещаю значение 1, в C3 я помещаю значение 2, в C4 я помещаю значение 3, затем в C5 я помещаю формулу =C2, затем копирую C5 и вставьте во все оставшиеся строки (таким образом, C6 получает =C3, C7 получает =C4 и т. д.). Теперь я могу применить фильтр и выбрать значение 1, 2 или 3, чтобы увидеть треть строк.

Распространение этого подхода на более чем 3 случая оставлено читателю в качестве упражнения.

Читайте также:


Расширенный поиск в браузере

Домен — это имя программы для связи между компьютерами

Объединить Word и Excel

Как создавать одежду в фотошопе

Как скопировать слой в фотошопе