Logiss.ru

Ваша компьютерная помощь
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Включение фильтра данных для выделенного диапазона в Эксель

    Для начала нужно любым удобным способом выделить нужную область ячеек. Затем в главной вкладке в блоке инструментов “Редактирование” щелкаем по значку “Сортировка и фильтр”. В раскрывшемся перечне выбираем пункт “Фильтр”.

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

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

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

  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.

Как удалить пустые ячейки в Excel. 3 метода удаления пустых ячеек в Эксель

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

kak-udalit-pustye-yachejki-v-excel-3-metoda-udaleniya-pustyh-yacheek-v-excel

1

Случаи, в которых возможно удаление незаполненных ячеек

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

  • Во всей строке или столбце нет никакой информации.
  • Между ячейками нет логической связи.

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

Решение 1: удаляем выделением группы ячеек

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

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

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

Решение 2: применяем фильтрацию и условное форматирование

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

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

Рассмотрим последовательное описание фильтрации данных:

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

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

Теперь разберем, как произвести условное форматирование совместно с фильтрацией:

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

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

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

Дальнейшая работа с данными может производится по месту или с переносом ее в другую область листа.

Решение 3: применяем формулу

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

  1. Выделяем диапазон ячеек, нуждающихся в корректировке.
  2. Затем кликаем ПКМ и находим команду «Присвоить имя». Присваиваем имя выделенному столбцу, жмем «ОК».
  1. В любом месте на листе выбираем свободную зону, которая соответствовует размеру области, где проводится корректировка. Кликаем ПКМ и вводим другое имя.
  1. После необходимо активировать самую верхнюю ячейку свободной области и вписать в нее формулу: =ЕСЛИ(СТРОКА()-СТРОКА(Корректировка)+1>ЧСТРОК(Фамилии)-СЧИТАТЬПУСТОТЫ(Фамилии);””;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(Фамилии <>””;СТРОКА(Фамилии);СТРОКА()+ЧСТРОК(Фамилии)));СТРОКА()-СТРОКА(Корректировка)+1);СТОЛБЕЦ(Фамилии);4))).

Примечание! Имена для областей выбираются произвольно. В нашем примере это «Фамилии» и «Корректировка».

  1. Как только данные формулы будут введены, нажимаем сочетание клавиш «Ctrl+Shift+Enter». Это необходимо, потому как в формуле имеются массивы.

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

Заключение

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

5 вариантов, как удалить пустые строки в Excel

Pustie stroki 1 5 вариантов, как удалить пустые строки в Excel Доброго времени суток друзья!

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

Разрывы, которые возникают в таблице благодаря возникшим пустотам, могут помешать вам эффективно ее использовать. Возникают такие проблемы:

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

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

  1. С помощью сортировки информации;
  2. С помощью наложения фильтра;
  3. С помощью меню выделения группы ячеек;
  4. С помощью формулы массива;
  5. С помощью макроса (VBA).

Сортировка

Pustie stroki 2 5 вариантов, как удалить пустые строки в Excel

Это самый простой способ убрать с вашей рабочей области или вычисляемого диапазона чистые строки. Вам нужно выделить диапазон, выбрать на вкладке «Данные» кнопку «Сортировка» и в предоставленном диалоговом окне выбираем вариант сортировки. Программа всё отсортирует по полочкам, и вы можете удалить пустые строки. Будьте внимательны! При использовании сортировки в таблице, которая имеет в своем составе несколько столбцов, вы можете нарушить целостность информации, ваши данные могут сместиться на другие ряды. Для избегания этой проблемы при выборе сортировки, нужно выделить всю таблицу и применить сортировку для всего выделенного объема.

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

Фильтрация

Pustie stroki 3 5 вариантов, как удалить пустые строки в Excel

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

Выделение группы ячеек

Я бы и этот способ причислил к достаточно простым и лёгким. Для применения этого варианта вам нужен инструмент «Выделение группы ячеек». Удалить незаполненые строки возможно в несколько этапов:

  • выделение нужного диапазона;
  • выбираем вкладку «Главная», в блоке «Редактирование», кликаем «Найти и выделить» и в выпадающем меню выбираете «Выделение группы ячеек»;

Pustie stroki 4 5 вариантов, как удалить пустые строки в Excel

  • в диалоговом окне, в разделе «Выделить» отмечаем пункт «пустые ячейки»;

Pustie stroki 5 5 вариантов, как удалить пустые строки в Excel

  • переходим во вкладку «Главная» и нажимаем кнопку «Ячейки» и выбираем пункт «Удалить»;
  • в диалоговом окне выбираете пункт «удалить ячейки со сдвигом вверх» и все незаполненые строки будут удалены.

Формула массива

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

Итак, в чём же состоит этот способ по удалению пустых строк в Excel и как его применять:

  • для большей простоты дадим имена нашим диапазонам, для этого на вкладке «Формулы» нажимаете «Диспетчер имен» и присваиваете для двух диапазонов имена, например для изначальных данных с пустыми ячейками – «Пустые», а для обработанных – «БезПустых». Обязательное условие в том, что диапазоны должны иметь одинаковый результат, но вот размещение этих диапазонов могут и отличатся, это не критично;

Pustie stroki 6 5 вариантов, как удалить пустые строки в Excel

  • следующим шагом это введение в первую ячейку второго диапазона формулу:

=ЕСЛИ( СТРОКА() — СТРОКА (БезПустых) +1> ЧСТРОК( Пустые) — СЧИТАТЬПУСТОТЫ (Пустые);»«; ДВССЫЛ ( АДРЕС (НАИМЕНЬШИЙ ((ЕСЛИ (Пустые <>»»; СТРОКА (Пустые); СТРОКА()+ ЧСТРОК( Пустые))); СТРОКА() — СТРОКА (БезПустых)+1); СТОЛБЕЦ( Пустые);4))). Да я сам знаю, что формула страшная, сам, когда ее увидел, обалдел, тем не менее, она рабочая и исправно выполняет возложенные на нее обязанности;

  • после прописания формулы, вам нужно окончить введение формулы «гарячей» комбинацией клавишCtrl+Shift+Enter, это необходимо для того, что бы формула была введена как формула массива. Теперь используя возможность в Excel как авто заполнение (копирование формулы, протягивая за крестик в углу) вы получили исходный диапазон, но уже без пустых ячеек.

Pustie stroki 7 5 вариантов, как удалить пустые строки в Excel

Макрос

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

Pustie stroki 8 5 вариантов, как удалить пустые строки в Excel

Перейдите во вкладку «Сервис», выберите раздел «Макрос» и нажмите кнопку «Редактор Visual Basic» или нажав ALT+F11 и в появившемся диалоговом окне редактора нажимаем «Insert»«Module». В открывшимся, пустом модуле ставим код:

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

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

Удаление пустых строк через фильтр в Excel

Для этого понадобится выделить всю таблицу и включить фильтр во вкладке «Данные»-«Фильтр».

Включение фильтра для поиска и удаления пустых ячеек в Excel

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

Выделение через фильтр пустых строк в Excel

Выделяем их и вызвав меню (правая кнопка мыши) удаляем.

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

Отмена фильтрации в Ексель

Готовый результат после удаления пустых ячеек в Excel

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

Исключение пустых ячеек через формулу Считать пустоты в Эксель

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

Использование формулы считать пустоты в Эксель

Далее растягиваем формулу до конца.

Растягивание формулы для поиска пустых строк в Эксель

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

Выделение пустых строк через формулу Считать пустоты в Excel

Аналогично удаляем их и отключаем фильтрацию.

Удаление пустых строк через фильтр в Excel

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

Исключение строк с пустотами через сортировку в Ексель

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

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

Включение сортировки для удаления пустых строк в Excel

Далее выбираем свод значений, по которому будем искать нужный индикатор) и нажимаем на «Ок».

Читайте так же:
Как записать Kaspersky Rescue Disk 10 на флешку
Ссылка на основную публикацию
Adblock
detector