Как оставить только строки в Excel

Обновлено: 29.06.2024

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

Например, рассмотрим следующие примеры:

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

Существует несколько способов оформления этого кота данных в Excel.

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

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

Это руководство охватывает:

Отфильтровать строки по значению/условию, а затем удалить

Один из самых быстрых способов удалить строки, которые содержат определенное значение или удовлетворяют заданному условию, — это отфильтровать их. Получив отфильтрованные данные, вы можете удалить все эти строки (при этом остальные строки останутся нетронутыми).

Фильтр Excel довольно универсален, и вы можете фильтровать по многим критериям (например, по тексту, числам, датам и цветам)

Давайте рассмотрим два примера, в которых вы можете фильтровать строки и удалять их.

Удалить строки, содержащие определенный текст

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

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

В этом случае вы можете отфильтровать все записи, в которых регионом является Средний Запад, а затем удалить все эти строки (при этом остальные строки останутся нетронутыми).

Ниже приведены шаги по удалению строк на основе значения (все записи Среднего Запада):

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

Несколько полезных советов, которые помогут ускорить процесс:

Control + Shift + L, чтобы применить или удалить фильтр.
Control + – (удерживайте клавишу Control и нажмите клавишу "минус"), чтобы удалить выбранные ячейки/строки.

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

Если у вас много категорий/регионов, вы можете ввести название в поле прямо над полем (с этими названиями регионов), и Excel покажет вам только те записи, которые соответствуют введенному тексту (как показано ниже). ). Когда у вас есть текст, на основе которого вы хотите отфильтровать, нажмите клавишу Enter.

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

Можно использовать методы, показанные далее в этом руководстве (метод сортировки или метод "Найти все")

Удалить строки на основе числового условия

Точно так же, как я использовал метод фильтра для удаления всех строк, содержащих текст Mid-West, вы также можете использовать числовое условие (или условие даты).

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

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

В Excel можно использовать множество числовых фильтров, например меньше/больше, равно/не равно, между, первые 10, выше или ниже среднего и т. д.

Примечание. Вы также можете использовать несколько фильтров. Например, вы можете удалить все строки, в которых значение продаж больше 200, но меньше 500. В этом случае вам нужно использовать два условия фильтрации. Диалоговое окно «Пользовательский автофильтр» позволяет использовать два критерия фильтрации (И и ИЛИ).

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

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

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

Вы не можете сделать это с помощью фильтрации, но вы можете сделать это с помощью сортировки.

Отсортировать набор данных, а затем удалить строки

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

Этот метод сортировки рекомендуется только в том случае, если вы хотите удалить ячейки со значениями, а не все строки.

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

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

Вышеуказанные шаги удалят все записи, в которых регионом является Средний Запад, но не удалит всю строку. Таким образом, если у вас есть какие-либо данные справа или слева от вашего набора данных, они останутся невредимыми.

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

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

Найти и выбрать ячейки на основе значения ячейки, а затем удалить строки

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

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

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

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

Вышеупомянутые шаги удалят все ячейки, в которых регион имеет значение Средний Запад.

Примечание. Поскольку функция "Найти и заменить" может обрабатывать подстановочные знаки, вы можете использовать их при поиске данных в Excel. Например, если вы хотите удалить все строки, в которых регионом является Средний Запад или Юго-Запад, вы можете использовать «* Запад» в качестве текста для поиска в диалоговом окне «Найти и заменить». Это даст вам все ячейки, где текст заканчивается словом West.

Удалить все строки с пустой ячейкой

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

Это параметр «Перейти к специальным ячейкам», который позволяет быстро выбрать все пустые ячейки. И как только вы выбрали все пустые ячейки, удалить их очень просто.

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

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

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

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

В диалоговом окне «Удалить» выберите параметр «Вся строка» и нажмите «ОК». Это удалит все строки, в которых есть пустые ячейки.

Если вам интересно узнать больше об этой технике, я написал подробное руководство о том, как удалять строки с пустыми ячейками. Он включает метод «Перейти к специальному», а также метод VBA для удаления строк с пустыми ячейками.

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

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

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

Этот код работает так же, как описанный выше метод Filter (за исключением того факта, что он выполняет все шаги в серверной части и экономит несколько кликов).

Ниже приведен код VBA, который это сделает.

Приведенный выше код использует метод автофильтра VBA, чтобы сначала отфильтровать строки на основе заданных критериев (то есть "Средний Запад"), а затем выбрать все отфильтрованные строки и удалить их.

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

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

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

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

Если ваши данные находятся в таблице Excel, используйте приведенный ниже код для удаления строк с определенным значением:

Поскольку VBA рассматривает таблицу Excel как объект списка (а не как диапазон), мне пришлось соответствующим образом изменить код.

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

Этот код необходимо поместить в модуль редактора VB Editor.

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

Откройте книгу, в которую вы хотите добавить этот код.
Используйте сочетание клавиш ALT + F11, чтобы открыть окно редактора VBA.
В этом окне редактора VBA слева находится панель «Проводник проектов» (в которой перечислены все рабочие книги и объекты рабочих листов). Щелкните правой кнопкой мыши любой объект в рабочей книге (в котором вы хотите, чтобы этот код работал), наведите курсор на «Вставить» и затем нажмите «Модуль». Это добавит объект модуля в книгу, а также откроет окно кода модуля справа
В окне модуля (которое появится справа) скопируйте и вставьте приведенный выше код.

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

Выберите любую строку кода и нажмите клавишу F5.
Нажмите кнопку "Выполнить" на панели инструментов редактора VB.
Назначьте макрос кнопке или фигуре и запустите его, нажав на него на листе.
Добавьте его на панель быстрого доступа и запустите код одним щелчком мыши.

В этой статье вы можете прочитать все о том, как запускать код макроса в Excel.

Примечание. Поскольку рабочая книга содержит код макроса VBA, его необходимо сохранить в формате с поддержкой макросов (xlsm).

Как удалить строки, не содержащие определенный текст в Excel?

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

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Удалить строки, не содержащие определенный текст, с помощью фильтра

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

В Excel функция "Фильтр" позволяет быстро отфильтровать строки, не содержащие определенного текста, а затем выбрать их для удаления.

<р>1.Выберите столбец, содержащий тексты, на основе которых вы будете удалять строки, и щелкните Данные > Фильтр. Смотрите скриншот:

<р>2. В заголовке столбца есть кнопка фильтра, нажмите ее, чтобы открыть раскрывающийся список, отметьте все, кроме определенного текста, который вы хотите использовать. Смотрите скриншот:

<р>3. Затем вы можете видеть только строку заголовка, а строки, не содержащие определенного текста, фильтруются. Смотрите скриншот:

<р>4. Выберите все из них, кроме строки заголовка, и щелкните правой кнопкой мыши, чтобы выбрать «Удалить строку» в контекстном меню. Смотрите скриншот:

<р>5. Затем снова нажмите «Данные» > «Фильтровать», после чего все строки, не содержащие определенный текст, будут удалены. Смотрите скриншот:

< td>

Удалить строки, не содержащие определенный текст, с помощью VBA

VBA очень удобен для сложных операций в Excel. Чтобы удалить строки, не содержащие определенного текста, выполните следующие действия.

<р>1. Нажмите Alt + F11, чтобы отобразить окно Microsoft Visual Basic для приложений.

<р>2. В окне нажмите «Вставить» > «Модуль», чтобы отобразить новое окно модуля, затем скопируйте следующий код VBA в окно модуля.

VBA: удалить всю строку, если ячейка содержит определенный текст.

<р>3. Нажмите кнопку «Выполнить», и появится диалоговое окно, в котором вы можете выбрать диапазон для работы. Смотрите скриншот:

Удалить строки, не содержащие определенный текст, с помощью Kutools for Excel

Если вы считаете, что вышеуказанные методы непросты для вас, вы можете использовать Kutools for ExcelВыбрать определенные ячейки функция.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас

<р>1. Выберите столбец, в котором вы хотите удалить строки, не содержащие определенный текст, и нажмите Kutools > Выбрать инструменты > Выбрать определенные ячейки. Смотрите скриншот:

<р>2. А в диалоговом окне «Выбор определенных ячеек» установите флажок «Вся строка» и выберите «Не содержит» и введите определенный текст в текстовое поле, также вы можете нажать кнопку диапазона в разделе «Определенный тип», чтобы выбрать ячейку, содержащую определенный текст.

<р>3. Затем нажмите «ОК», затем будут выбраны строки, не содержащие определенного текста, щелкните правой кнопкой мыши, чтобы выбрать «Удалить» в контекстном меню. Смотрите скриншот:

<р>4. Теперь удаляются все строки, не содержащие определенного текста в диапазоне. Нажмите на нее, чтобы получить дополнительную информацию о выборе определенных ячеек.

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

Итак, как мне сократить большую электронную таблицу до удобного размера, сохранив только те строки, которые мне нужны, или удалив строки, которые мне определенно не нужны?

Ответ — инструмент «Фильтр» в Excel. (Примечание: приведенные ниже снимки экрана и инструкции относятся к Excel 2010)

Шаг 1. Определите, какой текст или значения вы хотите удалить (или оставить).

В данном случае я искал ключевые слова, относящиеся к определенному типу контент-маркетинга. Однако Google выдал сотни ключевых слов, связанных с пресс-релизами, которые меня не интересовали.

Шаг 2. Нажмите на вкладку "Главная", выберите "Сортировка и фильтр", а затем "Фильтровать"

См. синие стрелки ниже.

Шаг 3. Выберите столбец, по которому хотите выполнить фильтрацию, и нажмите «Текстовые фильтры»

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

В моем случае я хотел фильтровать не по определенному значению, а по всем значениям в столбце, связанным со словами «пресс-релиз». Это может быть начало, середина или конец поля ключевого слова.

Поэтому я нажал на текстовые фильтры.

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

Шаг 4. Выберите нужный тип текстового фильтра.

При нажатии на Текстовый фильтр появляется всплывающее окно выбора. Я знаю, что хочу использовать «Содержит» для моего фильтра пресс-релиза, потому что это может быть любое место в поле, где я нажимаю «Содержит». Но вы можете захотеть использовать Equals, особенно если вы ищете определенные значения или другие фильтры.

Шаг 5. Добавьте свои условия.

Вы можете выбрать до 2 условий и добавить подстановочные знаки. В этом случае я мог бы искать «пресс-релиз», но это оставило бы поля с «релизами» вместо «релиз». Поэтому я добавляю подстановочный знак * в конце пресс-релиза для поиска «пресс-релиз» и «пресс-релизы». Я также знаю, что мне не нужны ключевые слова с «новостями», поэтому я добавляю условие «ИЛИ» для «новостей». Но вам не нужно добавлять второе условие.

Обратите внимание, что по умолчанию для условия используется оператор И, а не ИЛИ. Поэтому, если вы обязательно нажмете ИЛИ, если вам нужны все строки, содержащие любое из ваших условий.

Шаг 6. Нажмите «ОК», чтобы отфильтровать и просмотреть результаты

Как видно из частичных результатов, фильтр сработал хорошо и отобразил все строки с такими условиями. Обратите внимание на маленький символ воронки в столбце, который я использовал для фильтрации, чтобы указать, что это фильтр.

Шаг 7. Действия с отфильтрованными результатами

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

Я выбрал все строки, которые хотел удалить, щелкнул правой кнопкой мыши и выбрал "Удалить строку".

Не забудьте сохранить названия столбцов!

Но теперь у меня есть пустая электронная таблица, потому что мои отфильтрованные строки исчезли.

Чтобы просмотреть оставшиеся строки, мне нужно отключить или очистить фильтр.

Есть несколько способов сделать это. На скриншоте ниже я щелкнул значок воронки в столбце, по которому я отфильтровал. Затем я нажал «Очистить фильтр по ключевому слову». Вы также можете дополнительно отфильтровать результаты (если вы не удалили их, как я!)

Результаты!

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

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

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

В этом посте мы рассмотрим 9 способов удаления пустых строк из данных Excel.

Удалить пустые строки вручную

Первый способ — ручной.

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

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

< бр />

Когда строки, которые мы хотим удалить, выбраны, мы можем щелкнуть правой кнопкой мыши и выбрать в меню Удалить.

Мы также можем удалять строки с помощью команды на ленте. Перейдите на вкладку «Главная» ➜ нажмите команду «Удалить» ➜, затем выберите «Удалить строки листа».

Есть также очень удобная комбинация клавиш для удаления строк (столбцов или ячеек). Нажмите Ctrl + – на клавиатуре.

Вот оно! Наших пустых строк больше нет.

Удалить пустые строки с помощью функции «Перейти к специальному»

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

Что делать, если в наших данных есть много пустых строк? Выбор вручную был бы мучением!

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

Во-первых, нам нужно выбрать столбец наших данных, включая все пустые строки. Самый простой способ сделать это — выделить первую ячейку (в данном примере A1), затем, удерживая клавишу Shift, выбрать последнюю ячейку (в данном примере A14).

Теперь мы можем использовать команду «Перейти к специальному», чтобы выбрать только пустые ячейки. Перейдите на вкладку «Главная» ➜ нажмите команду «Найти и выбрать» ➜ выберите «Перейти к специальному» в меню.

Есть также удобное сочетание клавиш для меню "Перейти". Нажмите Ctrl + G, чтобы открыть меню «Перейти», затем нажмите кнопку «Специальные», чтобы открыть меню «Перейти к специальным».

Независимо от того, откроем ли мы меню «Перейти» и нажмем «Специальные» или перейдем непосредственно к меню «Перейти к специальным», мы попадем в одно и то же меню «Перейти к специальным».

Теперь все, что нам нужно сделать, это выбрать «Пробелы» в параметрах и нажать кнопку «ОК». Это выберет только пустые ячейки из нашего первоначального выбора столбца.

Теперь нам нужно удалить эти выбранные строки.

Используйте любой метод удаления строк из раздела "Удаление пустых строк вручную".
- Щелкните правой кнопкой мыши ➜ Удалить
- Вкладка "Главная" ➜ "Удалить" ➜ "Удалить строки листа".
- Ctrl + – сочетание клавиш
В меню "Удалить" выберите "Вся строка" и нажмите кнопку "ОК".

Как по волшебству, мы можем найти и удалить сотни пустых строк в наших данных за несколько секунд. Это особенно удобно, когда у нас много пустых строк, разбросанных по длинному набору данных.

Удалить пустые строки с помощью команды «Найти»

Этот метод будет очень похож на описанный выше метод удаления пустых строк с помощью перехода к специальному методу. Единственная разница в том, что мы будем выбирать пустые ячейки с помощью команды «Найти».

Как и раньше, нам нужно выбрать столбец в наших данных.

Перейдите на вкладку "Главная" ➜ нажмите команду "Найти и выбрать" ➜ выберите "Найти" в меню.

Есть также сочетание клавиш, которое мы можем использовать, чтобы открыть меню поиска. Нажмите Ctrl + F на клавиатуре.

В любом случае это откроет для нас меню "Найти и заменить".

Разверните дополнительные параметры в меню поиска.
Оставьте поле ввода «Найти» пустым.
Выберите параметр «Соответствовать всему содержимому ячейки».
Поиск на листе.
Посмотрите на значения.
Нажмите кнопку "Найти все", чтобы вернуть все пустые ячейки.

Появится список всех пустых ячеек, найденных в выбранном диапазоне, в нижней части меню "Найти".

Мы можем выбрать их все, нажав Ctrl + A. Затем мы можем закрыть меню «Найти», нажав кнопку «Закрыть». Теперь мы можем удалить все пустые ячейки, как и раньше.

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

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

< бр />

Во-первых, нам нужно добавить фильтры к нашим данным.

Выберите весь диапазон данных, включая пустые строки.
Перейдите на вкладку "Данные".
Нажмите кнопку "Фильтр" в разделе "Сортировка и фильтр".

Мы также можем добавить фильтры к диапазону, используя сочетание клавиш Ctrl + Shift + L.

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

Нажмите переключатель фильтра в одном из столбцов.
Используйте переключатель "Выбрать все", чтобы отменить выбор всех элементов.
Проверьте пробелы.
Нажмите кнопку ОК.

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

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

Затем мы можем нажать кнопку OK, когда Excel спросит нас, хотим ли мы удалить всю строку листа.

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

Мы можем использовать фильтры немного по-другому, чтобы избавиться от пустых строк. На этот раз мы будем фильтровать пробелы. Щелкните переключатель фильтра в одном из столбцов ➜ снимите флажок «Пробелы» ➜ нажмите кнопку «ОК».

< бр />

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

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

Подобно методу фильтрации, мы можем использовать параметр «Расширенные фильтры», чтобы получить копию наших данных за вычетом пустых строк.

Чтобы использовать функцию расширенных фильтров, нам потребуется выполнить небольшую настройку.

Нам нужно настроить диапазон критериев фильтрации. Мы собираемся фильтровать только на основе одного столбца, поэтому нам нужен один заголовок столбца из наших данных (в этом примере F1). Под заголовком столбца нам нужны наши критерии (в этом примере F2), нам нужно ввести ="" в эту ячейку в качестве наших критериев.
Выберите диапазон данных для фильтрации.
Перейдите на вкладку "Данные".
Выберите «Дополнительно» в разделе «Сортировка и фильтрация».

Теперь нам нужно настроить меню расширенного фильтра.

Выберите Копировать в другое место.
Выберите диапазон данных для фильтрации. Это поле уже должно быть заполнено, если диапазон был выбран до открытия меню расширенных фильтров.
Добавьте критерии в диапазон критериев (в данном примере F1:F2).
Выберите место на листе для копирования отфильтрованных данных.
Нажмите кнопку ОК.

Теперь мы получаем копию наших данных в новом месте без пробелов.

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

Если мы используем Excel онлайн или Excel для Office 365, мы можем использовать одну из новых функций динамического массива, чтобы отфильтровать наши пустые строки.

На самом деле существует функция FILTER динамического массива, которую мы можем использовать.

Синтаксис функции FILTER

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

Функция FILTER для фильтрации пробелов

Вышеуказанная функция должна быть введена только в одну ячейку, и результаты будут распространяться на остальные ячейки по мере необходимости. Функция отфильтрует CarData в столбце Make и отфильтрует все пробелы.

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

Удалить пустые строки путем сортировки

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

Выберите диапазон данных.
Перейдите на вкладку "Данные".
Нажмите команду сортировки. Подойдет как восходящий, так и нисходящий порядок.

Теперь все наши пустые строки появятся внизу, и мы можем их игнорировать.

Если нам нужен исходный порядок сортировки наших данных, мы можем добавить индексный столбец перед сортировкой. Затем мы можем отсортировать пустые строки внизу и удалить их. Затем мы сортируем наши данные в исходном порядке на основе столбца индекса.

Удалить пустые строки с помощью Power Query

Power может легко удалить пустые строки в наших данных.

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

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

Перейдите на вкладку "Главная" в редакторе расширенных запросов.
Нажмите кнопку "Удалить строки".
Выберите в меню параметр "Удалить пустые строки".

При этом будет сгенерирован указанный выше M-код с использованием функции Table.SelectRows для выбора ненулевых строк. Это приведет к удалению только тех строк, в которых вся запись имеет нулевые значения.

< бр />

Мы также могли бы получить тот же результат, отфильтровав нулевые значения в наших данных. Щелкните правой кнопкой мыши любой из переключателей сортировки и фильтрации, затем снимите флажок с нулевого значения и нажмите кнопку ОК.

Запрос Power снова создаст шаг с функцией Table.SelectRows, возвращая ненулевые значения в определенном столбце.

Удалить пустые строки с помощью Power Automate

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

Мы можем использовать Power Automate для удаления пустых строк в наших таблицах Excel.

< бр />

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

< бр />

Для этого мы можем настроить небольшую автоматизацию Flow.

Мы будем использовать ручную кнопку для запуска потока, но мы можем использовать любое количество триггеров.
Затем нам нужно составить список строк, присутствующих в таблице, чтобы получить все строки данных из нашей таблицы Excel. Лучшим вариантом также было бы использовать фильтры Odata в разделе Показать дополнительные параметры для фильтрации пустых строк, но в настоящее время фильтрация пустых строк невозможна.
Поскольку мы не можем фильтровать пустые значения с помощью фильтров Odata, для этого нам нужно использовать действие операции с данными массива Filter. Мы можем отфильтровать значения из строк списка, присутствующих в табличном действии, и установить условие, что Make равно пустому (оставьте значение пустым). Это даст нам все строки с пустыми ячейками.
Теперь мы можем использовать действие "Удалить строку", чтобы удалить эти пустые строки. Мы можем выбрать наш столбец идентификатора в качестве ключевого столбца, а затем добавить поле идентификатора из действия массива фильтров. Это должно обернуть действие в Применить к каждому шагу, чтобы удалить все пробелы.

При запуске этой автоматизации все пустые строки в таблице будут удалены.

Выводы

Пустые строки в наших данных могут доставлять неудобства.

Удалить их легко, и у нас есть много вариантов.

Возможно, мой любимый способ – это сочетание клавиш "Перейти". Это быстро, просто и эффективно.

Я пропустил какие-либо методы? Дайте мне знать в комментариях ниже!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

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