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

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

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

Алгоритмы удаления

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

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

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

Способ 1: выделение групп ячеек

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

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

Запускается небольшое окошко, которое называется «Переход». Жмем в нем кнопку «Выделить…».

Как видим, все пустые элементы указанного диапазона были выделены. Кликаем по любому из них правой кнопкой мыши. В запустившемся контекстном меню щелкаем по пункту «Удалить…».

Открывается маленькое окошко, в котором нужно выбрать, что именно следует удалить. Оставляем настройки по умолчанию – «Ячейки, со сдвигом вверх». Жмем на кнопку «OK».

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

Способ 2: условное форматирование и фильтрация

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

Выделяем диапазон, который собираемся обрабатывать. Находясь во вкладке «Главная», жмем на пиктограмму «Условное форматирование», которая, в свою очередь, располагается в блоке инструментов «Стили». Переходим в пункт открывшегося списка «Правила выделения ячеек». В появившемся списке действий выбираем позицию «Больше…».

Открывается окошко условного форматирования. В левое поле вписываем цифру «0». В правом поле выбираем любой цвет, но можно оставить настройки по умолчанию. Щелкаем по кнопке «OK».

Как видим, все ячейки указанного диапазона, в которых находятся значения, были выделены в выбранный цвет, а пустые остались белыми. Опять выделяем наш диапазон. В этой же вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр», расположенной в группе «Редактирование». В открывшемся меню жмем на кнопку «Фильтр».

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

Можно также сделать немного по-другому. Кликаем по значку фильтрации. В появившемся меню снимаем галочку с позиции «Пустые». После этого щелкаем по кнопке «OK».

В любом из указанных в предыдущем пункте вариантов пустые элементы будут скрыты. Выделяем диапазон оставшихся ячеек. На вкладке «Главная» в блоке настроек «Буфер обмена» выполняем щелчок по кнопке «Копировать».

Затем выделяем любую пустую область на том же или на другом листе. Выполняем щелчок правой кнопкой мыши. В появившемся контекстном списке действий в параметрах вставки выбираем пункт «Значения».

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

Способ 3: применение сложной формулы

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

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

Открывается окно присвоения наименования. В поле «Имя» даем любое удобное название. Главное условие – в нем не должно быть пробелов. Для примера мы присвоили диапазону наименование «С_пустыми». Больше никаких изменений в том окне вносить не нужно. Жмем на кнопку «OK».

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

В открывшемся окне, как и в предыдущий раз, присваиваем любое наименование данной области. Мы решили дать ей название «Без_пустых».

Выделяем двойным щелчком левой кнопки мышки первую ячейку условного диапазона «Без_пустых» (у вас он может назваться и по-другому). Вставляем в неё формулу следующего типа:

Так как это формула массива, то для выведения расчета на экран нужно нажать комбинацию клавиш Ctrl+Shift+Enter, вместо обычного нажатия кнопки Enter.

Но, как видим, заполнилась только одна ячейка. Для того, чтобы заполнились и остальные, нужно скопировать формулу на оставшуюся часть диапазона. Это можно сделать с помощью маркера заполнения. Устанавливаем курсор в нижний правый угол ячейки, содержащей комплексную функцию. Курсор должен преобразоваться в крестик. Зажимаем левую кнопку мыши и тянем его вниз до самого конца диапазона «Без_пустых».

Как видим, после этого действия мы имеем диапазон, в котором подряд расположены заполненные ячейки. Но выполнять различные действия с этими данными мы не сможем, так как они связаны формулой массива. Выделяем весь диапазон «Без_пустых». Жмем на кнопку «Копировать», которая размещена во вкладке «Главная» в блоке инструментов «Буфер обмена».

После этого выделяем первоначальный массив данных. Щелкаем правой кнопкой мыши. В открывшемся списке в группе «Параметры вставки» жмем на пиктограмму «Значения».

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 11907 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Постановка задачи

Имеем диапазон ячеек с данными, в котором есть пустые ячейки:

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

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

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

Выделяются все пустые ячейки в диапазоне.

Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши —Удалить ячейки (Delete Cells) со сдвигом вверх.

Способ 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) и скопируйте туда текст этой функции:

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

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