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

44

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

Возможность и методы удаления

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

  • Весь столбец (строка) не заполнен;
  • Элементы в столбце (строке) не взаимосвязаны.

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

Метод 1: простое выделение и удаление

Данный метод, пожалуй, можно считать самым простым. Вот, что мы делаем:

  1. Любым удобным способом производим выделение области, в которой нам нужно найти и удалить все незаполненные ячейки. После того, как выделение выполнено, нажимаем клавишу F5.Выделение диапазона с пустыми ячейками в Эксель
  2. На экране отобразится окно перехода, в котором щелкаем “Выделить”.Переход к параметрам выделения в Эксель
  3. Мы окажемся в окне выделения групп ячеек. Здесь среди все вариантов выбираем “пустые ячейки”, поставив напротив отметку, после чего жмем OK.Выделение пустых ячеек в Excel
  4. В нашем диапазоне будут отмечены все пустые ячейки. Щелчком правой кнопки мыши по любому их них открываем меню, в котором выбираем команду “Удалить”.Удаление выделенных ячеек через контекстное меню в Эксель
  5. В появившемся окне останавливаемся на варианте “ячейки, со сдвигом вверх” и нажимаем OK.Удаление ячеек со сдвигом вверх в Excel
  6. В результате выполненных действий мы удалили все пустые ячейки, находящиеся в выделенной области.Результат удаления пустых ячеек со сдвигом вверх в Эксель
  7. Кликаем по любому элементу на листе, чтобы снять выделение.Результат удаления пустых ячеек в Excel

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

Метод 2: фильтр и условное форматирование

Этот метод несколько сложнее рассмотренного выше и предполагает применение фильтра или условного форматирования с дальнейшей фильтрацией (сортировкой) данных. Однако тут есть один нюанс – его можно применять только для работы с одним столбцом, в котором, к тому же, нет формул.

Фильтрация данных:

  1. Для начала нужно любым удобным способом выделить нужную область ячеек. Затем в главной вкладке в блоке инструментов “Редактирование” щелкаем по значку “Сортировка и фильтр”. В раскрывшемся перечне выбираем пункт “Фильтр”.Включение фильтра данных для выделенного диапазона в Эксель
  2. Внутри самой верхней ячейки выделенного диапазона появится характерный значок фильтра в виде небольшого квадрата с треугольником, направленным вниз. Щелкаем по нему. Откроется меню, в котором убираем галочку напротив пункта “Пустые” и жмем OK.Отключение отображения пустых ячеек с помощью фильтра в Excel
  3. В таблице останутся только ячейки, содержащие какие-либо данные.Отфильтрованный диапазон без пустых ячеек в Excel

Условное форматирование и фильтрация:

  1. Выполняем выделение диапазона ячеек. В главной вкладке в группе инструментов “Стили” нажимаем кнопку “Условное форматирование”. В раскрывшемся перечне останавливаемся на варианте “Правила выделения ячеек”, затем – выбираем “Больше”.Применение условного форматирования к диапазону ячеек в Excel
  2. На экране отобразится окно форматирования:
    • в поле слева указываем число 0;
    • в правом – щелкнув по текущему значению раскрываем список возможных цветовых схем и выбираем понравившийся вариант (или оставляем стандартный);Настройка условного форматирования в Эксель
    • по готовности жмем OK.Настройка условного форматирования в Excel
  3. Ко всем заполненным ячейкам будет применена цветовая схема, которую мы выбрали, в то время, как заливка пустых ячеек не изменилась. Повторно выделяем тот же самый диапазон (если выделение было снято) и включаем “Фильтр”.Активация фильтра для выделенного диапазона с условным форматирование в Эксель
  4. Кликаем по значку фильтра, в открывшемся меню наводим указатель мыши на строку “Фильтр по цвету”, после чего откроется еще одно подменю, где останавливаемся на варианте “Фильтр по цвету ячейки” или “Фильтр по цвету шрифта” (в данном случае, это неважно).Настройка фильтра по цвету в Эксель
  5. В результате, из всего выделенного диапазона мы оставили только заполненные ячейки.Включенный фильтр по цвету в Excel

Независимо от того, каким из способов выше мы воспользовались (фильтр или условное форматирование с последующей фильтрацией), дальнейшие шаги следующие:

  1. Нажимаем кнопку “Копировать” в группе инструментов “Буфер обмена” (вкладка “Главная”).Копирование диапазона ячеек в Эксель
  2. На этом же (или другом) листе отмечаем соразмерный диапазон, затем щелчком правой кнопки мыши по нему раскрываем меню, в котором кликаем по варианту вставки – “Значения” (значок в виде папки с цифрами 123). Тут есть один нюанс – выделять нужно диапазон, который располагается ниже самой нижней строки области с первоначальными данными.Вставка скопированных значений в Эксель
  3. Нам удалось вставить данные (без первоначального форматирования ячеек). Мы можем продолжить работу с ними здесь или перенести (скопировать) в любое другое удобное место.Вставленный диапазон ячеек в Excel без исходного форматирования

Сортировка вместо фильтрации:

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

Настройка сортировки по цвету в Эксель

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

Отсортированный по цвету диапазон ячеек в Excel

Метод 3: использование формулы

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

  1. Для начала нужно задать имя диапазону ячеек, с которым планируем работать. Для этого выделяем его, затем кликаем по нему правой кнопкой мыши и в контекстном меню выбираем команду “Присвоить имя”.Присвоение имени диапазону ячеек в Эксель
  2. В окне создания имени в поле “Имя” указываем любое название (без пробелов). Остальные поля оставляем без изменений и нажимаем OK.Создание имени для диапазона ячеек в Эксель
  3. В любом свободном месте производим выделение соразмерного диапазона ячеек (для удобства, если возможно, лучше это сделать в тех же строках другого столбца). Затем также в контекстном меню, вызываемом щелчком правой кнопки мыши по нему, выбираем пункт “Присвоить имя”.Присвоение имени диапазону ячеек в Excel
  4. Как и для первого диапазона, задаем имя (в нашем случае – “Без_пустых_ячеек”).Создание имени для диапазона ячеек в Excel
  5. Встаем в самую верхнюю ячейку диапазона “Без_пустых_ячеек” (в нашем случае – это D2) и пишем в нее следующую формулу:
    =ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых_ячеек)+1>ЧСТРОК(Диапазон_с_пустыми_ячейками)-СЧИТАТЬПУСТОТЫ(Диапазон_с_пустыми_ячейками);"";ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(Диапазон_с_пустыми_ячейками <>"";СТРОКА(Диапазон_с_пустыми_ячейками);СТРОКА()+ЧСТРОК(Диапазон_с_пустыми_ячейками)));СТРОКА()-СТРОКА(Без_пустых_ячеек)+1);СТОЛБЕЦ(Диапазон_с_пустыми_ячейками);4))).
    Формула для удаления пустых ячеек в ЭксельПримечание: в данном случае указаны имена диапазонов, заданные нами. Вам нужно заменить их на свои названия.
  6. Когда все готово, жмем сочетание клавиш Ctrl+Shift+Enter, так как это формула, содержащая массивы. Результат отобразится в выбранной ячейке. В строке формул мы увидим перед знаком “равно” символ “{“. Это означает, что мы все сделали правильно.Удаление пустых ячеек с помощью формулы в Excel
  7. Теперь нужно растянуть формулу на другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом. Когда появится Маркер заполнения (черный плюсик), зажав левую кнопку мыши тянем его до последней строки диапазона “Без_пустых_ячеек”.Копирование формулы с помощью Маркера заполнения в Эксель
  8. Мы получим столбец, в котором подряд расположились значения из ячеек исходного “Диапазона_с_пустыми_ячейками”.Результат удаления пустых ячеек с помощью формулы в Эксель
  9. Чтобы в дальнейшем иметь возможность работать с этими данными, не снимая выделение (или повторно выделив этот диапазон), щелкаем по нему правой кнопкой мыши и выбираем команду “Копировать” в открывшемся контекстном меню (или можно просто нажать комбинацию Ctrl+C).Копирование выделенного диапазона ячеек в Эксель
  10. В любом другом месте листа отмечаем соразмерный диапазон ячеек (например, в первоначальном месте), кликом правой кнопки мыши по выделенной области вызываем меню, в котором выбираем вариант вставки – “Значения”.Вставка значений без исходного форматирования из буфера обмена в Excel
  11. Столбец с формулами нам больше не нужен. Щелкаем по его обозначению на горизонтальной панели координат правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Удалить”.Удаление столбца в Эксель
  12. В результате проделанных действий мы получили тот же самый столбец, но уже без пустых ячеек.Диапазон без пустых ячеек в Excel

Заключение

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

ОСТАВЬТЕ ОТВЕТ

Введите свой комментарий
Пожалуйста, введите свое Имя