Как удалить пустые ячейки в excel
Перейти к содержимому

Как удалить пустые ячейки в excel

  • автор:

Удаление пустых ячеек из диапазона

Имеем диапазон ячеек с данными, в котором есть пустые ячейки: Задача — удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

  1. Выделяем исходный диапазон
  2. Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.

delete_blanks3.png

Способ 2. Формула массива

Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или — в Excel 2003 и старше — меню Вставка — Имя — Присвоить (Insert — Name — Define)

Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 — НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.

Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:

В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),»»,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>«»,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))

Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) — и мы получим исходный диапазон, но без пустых ячеек:

Способ 3. Пользовательская функция на VBA

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

Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert — Module) и скопируйте туда текст этой функции:

Function NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Each Rng In DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(N, 1) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End If End Function

Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:

  1. Выделите достаточный диапазон пустых ячеек, например F3:F10.
  2. Идем в меню Вставка — Функция (Insert — Function) или жмем на кнопку Вставить функцию(Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
  3. В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.

Ссылки по теме:

  • Удаление сразу всех пустых строк в таблице простым макросом
  • Удаление сразу всех пустых строк на листе с помощью надстройки PLEX
  • Быстрое заполнение всех пустых ячеек
  • Что такое макросы, куда вставлять код макросов на VBA

4 варианта удаления пустых строк в таблице Эксель

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

  • Все статьи (699)
  • EXCEL (420)
  • Python (18)
  • Unity (1)
  • WORD (117)
  • Бесплатные онлайн-курсы (4)
  • Бизнес (1)
  • Веб программирование (4)
  • Интернет магазины (1)
  • Интерьер (67)
  • Маркетинг (12)
  • Онлайн сервисы (1)
  • Профессии (20)
  • Развитие (10)
  • Управление (6)
  • Учимся рисовать (16)
  • Финансы (1)

Показать все категории

Как НЕ НАДО удалять пустые строки в Excel

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

  1. Выберите мышью пустую строку.
  2. Перейдите в раздел «Найти и выбрать».
  3. Нажмите на пункт «Перейти к спец».
  4. Нажмите ЛКМ на слово «Пробел». После этого пустота должна исчезнуть, и таблица преобразится.

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

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

Формула для удаления пустых строк в Excel

Для этого можно использовать специальную формулу, которая работает во всех версиях программы: = ЕСЛИ (СЧЁТ (A2: E2) = 0; «Пусто»; «»). A2 и E2 — первая и последняя ячейка строки, из которой нужно удалить пробелы. Точно так же выражение применяется к любому столбцу в таблице. Пользователь должен поместить курсор мыши в пустую ячейку и записать эту формулу в строке вверху главного меню. Кроме того, эту формулу нужно будет растянуть на все строки или столбцы таблицы, удерживая крестик ЛКМ в правом нижнем углу первой ячейки. После таких манипуляций пропадут пустые строки.

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

Power Query — это специальный инструмент, который присутствует в версиях Excel с 2013 года.

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

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

  1. Выберите с помощью манипулятора строку или столбец, в которых есть зазоры, требующие удаления.
  2. В интерфейсе Excel перейдите в раздел «Данные» вверху».
  3. В столбце открытых параметров нажмите кнопку «Получить данные».
  4. В контекстном меню типа щелкните строку «Из других источников», а затем выберите вариант «Из таблицы / диапазона». Теперь откроется небольшое меню, где вам нужно установить флажок рядом с опцией «Таблица с заголовком» и нажать «ОК».

Загрузите таблицу в редактор Power Query

  1. Изучите открывшийся интерфейс редактора Power Query.
  2. ЛКМ нажмите кнопку «Удалить линии» вверху раздела.
  3. В контекстном меню нажмите «Удалить пустые строки».

Удаление пустых строк в редакторе Power Query. Пошаговый алгоритм

  1. Нажмите кнопку «Закрыть и загрузить».
  2. Проверить результат. После выполнения описанных выше манипуляций окно редактора должно закрыться, а пустые ячейки в таблице исчезнут.

Окончательный результат. Внешний вид таблицы после реализации метода

Дополнительная информация! После реализации рассматриваемого метода формат отображения дат и чисел в таблице изменится.

Как удалить строки, если ячейка в определенном столбце пуста

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

  1. Нажмите ПКМ на имени столбца, в котором есть пробелы.
  2. В контекстном меню нажмите «Выбрать» и в следующем окне укажите «Пустые ячейки». После закрытия окна в выбранном столбце будут выделены только пробелы, а ячейки с информацией останутся неизменными.
  3. Щелкните правой кнопкой мыши любую выделенную ячейку и щелкните строку «Удалить». Откроется небольшое окно, в котором нужно поставить тумблер перед полем «Строки», затем нажать «ОК».
  4. Проверить результат. Строки с пустыми ячейками необходимо полностью удалить из таблицы. Как было сказано выше, недостатком метода является то, что ячейки с информацией также будут удалены.

Как удалить лишние строки под данными или скрыть их

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

  1. Выделите пустую ячейку с помощью ЛКМ и одновременно удерживайте кнопки «Ctrl + Shift + End». Эта команда выделит оставшиеся ячейки, в которых что-то действительно написано.
  2. Щелкните ячейку правой кнопкой мыши и выберите опцию «Удалить» в окне типа контекста».
  3. Проверьте, были ли удалены лишние пустоты. Выбранные ячейки со скрытыми данными необходимо удалить, как в предыдущем способе.

Иногда пользователь не хочет удалять информацию из скрытых ячеек таблицы. В этом случае их можно скрыть. Скрытие строк в Microsoft Office Excel выполняется по следующей инструкции:

  1. Выберите ячейку под последней строкой с данными и удерживайте «Ctrl + Shift + End», чтобы выделить оставшиеся лишние пробелы.
  2. После того, как вы выбрали все пробелы для одного из них, вам нужно щелкнуть правой кнопкой мыши и выбрать опцию «Скрыть».
  3. Вы можете удерживать кнопки «Ctrl + 9», чтобы скрыть ненужные ячейки.
  4. Удерживайте Ctrl + Shift + 9, если вам нужно снова сделать пробелы видимыми.

Как быстро удалить пустые строки в Excel с помощью VBA

VBA — это специальный язык программирования, предназначенный для автоматизации процессов в редакторах Microsoft Office. С его помощью можно будет быстро устранить пустые ячейки в Excel, скопировав необходимый программный код. Вам не нужно быть программистом, чтобы реализовать этот метод удаления. Далее мы рассмотрим несколько команд, реализованных на VBA в Excel.

Макрос 1. Удалить пустые строки в выбранном диапазоне

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

Программный код для удаления пустых строк в указанном диапазоне

Чтобы вставить макрос в Excel на языке программирования VBA и запустить его, вам необходимо:

  1. Перейдите в раздел «Разработчик», расположенный в списке параметров вверху главного меню.
  2. Нажмите кнопку «Записать макрос».
  3. Скопируйте код с изображения выше и введите его в соответствующее поле.
  4. Сохраните изменения, нажав «ОК».
  5. Для запуска программы нужно перейти во вкладку «Макросы», выбрать нужный код с названием созданных, выбрать диапазон, в котором вы хотите убрать пробелы и нажать «ОК».

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

Макрос 2. Удалить все пустые строки в Excel

С помощью написанного кода можно сразу удалить все пробелы, которые есть на активном листе Excel. Для этого используется следующий код:

Макрос для удаления всех пробелов в листе Excel

Макрос 3. Удалить строку, если ячейка пуста

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

Макрос для удаления лишних пустых строк со всего листа

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

Как добавить макрос в свою книгу

Для написания кодов в Excel создается рабочая книга. Альтернативный метод создания и запуска макросов обсуждался выше. Есть еще один способ — выполнить следующие действия:

  1. Откройте лист, на котором вы хотите удалить ячейки.
  2. Удерживайте клавиши Alt + F11, чтобы запустить встроенный редактор Visual Basic».
  3. В столбце параметров слева от открытого окна нажмите на слово «Вставить», затем перейдите на вкладку «Модуль».
  4. Вставьте требуемый программный код в появившееся окно.
  5. Нажмите F5, чтобы проверить работоспособность кода.
  6. Нажмите кнопку «Сохранить», чтобы добавить макрос в книгу.

Дополнительная информация! Чтобы активировать созданный макрос в следующий раз, вам необходимо войти в книгу, выбрать соответствующую таблицу, зажав «Alt + F8» на клавиатуре, выбрать нужный код и нажать кнопку «Выполнить».

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

Сортировка данных в таблице помогает устранить пробелы. Эта операция выполняется по алгоритму:

  1. Выберите требуемый диапазон ячеек LMC.
  2. Перейдите в раздел «Данные» вверху главного меню программы и нажмите кнопку «Заказать».

Сортировка таблицы значений в Excel

В следующем меню выберите столбец для сортировки содержащейся в нем информации и нажмите «ОК».

Выбор столбца для сортировки данных

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

Удаление пустых ячеек после сортировки таблицы

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

Процесс выполнения поставленной задачи разбит на следующие этапы:

  1. Таким же образом выделите таблицу и перейдите в раздел «Данные» вверху окна программы.
  2. Теперь вместо заказа нажмите кнопку «Фильтр».

Путь к фильтрации данных таблицы в Excel

В появившемся окне установите флажок рядом со значением «(Пусто)».

Выбор пустых строк для фильтрации

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

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

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

Как убрать пустые строки в Excel с помощью выделения группы ячеек

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

  1. Выделите всю таблицу с помощью ЛКМ.
  2. Щелкните значок увеличительного стекла на диаграмме вкладок в верхней части окна программы. Это меню «Найти и выбрать».
  3. В контекстном меню нажмите на строку «Выбрать группу ячеек…».

Выбор группы ячеек в Excel

  1. В появившемся окне поместите тумблер в поле «Пустые ячейки» и нажмите «ОК». Все пробелы в таблице выделены.

Выделение пустых ячеек в появившемся окне

  1. Удалите строку с помощью метода пробелов и проверьте результат.

Автоматическое выделение промежутков в таблице

Как удалить пустые строки в Экселе с помощью вспомогательного столбца

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

  1. Создайте еще один столбец справа от исходной таблицы и назовите его, например, «Пустая строка», чтобы не запутаться в будущем.
  2. Запишите формулу «Считать пустые», указав соответствующий интервал, в течение которого программа будет проверять наличие пустых строк.
  3. Когда обнаруживаются пустые строки, остается только выбрать и удалить их.

Подсчитайте пустоты в опорной колонне

Важно! Эта формула выглядит так: «= СЧИТАТЬПУСТОТЫ (A6: D6)». Вместо букв в скобках указан диапазон ячеек.

Заключение

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

Удалить неиспользуемые пустые конечные строки или столбцы

× The language used in menus and dialogs (Язык, используемый в меню и диалоговых окнах) in ASAP Utilities can be set to «Русский (Russian)».

Таблицы › 26. Удалить неиспользуемые пустые конечные строки или столбцы

С помощью этой утилиты можно удалить все неиспользуемые пустые строки и столбцы справа и снизу от диапазона данных.
Эта утилита может быть полезной в случаях, когда не удается сбросить последнюю ячейку с помощью инструмента ASAP Utilities «Сброс последней ячейки Excel».

Вы можете использовать эту утилиту, когда конец рабочего листа находится далеко внизу или справа от используемого в настоящий момент диапазона. Перейти к последней ячейке на активном листе можно при помощи сочетания клавиш Control + End .

В статье 244435 на официальном сайте Майкрософт описана эта проблема: «Microsoft Excel сохраняет только используемую часть каждого листа, то есть раздел, содержащий данные или форматирование. Иногда последняя ячейка листа может располагаться вне пределов диапазона используемых в настоящий момент данных. Из-за этой проблемы файл может оказаться большего размера, чем необходимо, на печать могут быть выведены лишние страницы, может появиться сообщение об ошибке, о нехватке памяти, или наблюдаться другое необычное поведение. Очистка лишних строк и столбцов для сброса последней ячейки может помочь в решении этой проблемы.
Перейти к последней ячейке на активном листе можно при помощи сочетания клавиш Control + End ».

Снимки экрана

Пример снимка экрана: 1 Before (Вот версия на английском языке.)
1 Before (Вот версия на английском языке.)
Пример снимка экрана: 2 After (Вот версия на английском языке.)
2 After (Вот версия на английском языке.)
ASAP Utilities makes YOU rock in Excel!

Practical tricks on how this can help you

Запуск этой утилиты

  • Нажмите кнопку ASAP Utilities › Таблицы › 26. Удалить неиспользуемые пустые конечные строки или столбцы
  • Укажите сочетание клавиш: ASAP Utilities › Избранное и сочетания клавиш › Редактировать избранное и сочетания клавиш.

Таблицы › 26 Удалить неиспользуемые пустые конечные строки или столбцы

Additional keywords for this tool:
слишком много строк, столбцы, избыточные строки, экономить бумагу, экономия

Удалить пустые строки и столбцы на листе

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

Используйте следующие методы Aspose.Cells для удаления пустых строк и столбцов:

  1. Чтобы удалить пустые строки, используйтеCells.DeleteBlankRows() метод. Обратите внимание, для пустых строк, которые будут удалены, требуется не толькоСтрока.IsBlank должно быть истинным, но также не должно быть видимого комментария, определенного для любой ячейки в этих строках, и сводной таблицы, диапазон которой пересекается с ними.
  2. Чтобы удалить пустые столбцы, используйтеCells.УдалитьБланкКолонкс() метод.

Добавить комментарий

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