При выполнении задач в приложении Excel может наступить необходимость удалить пустые ячейки. Они зачастую являются ненужным элементом и только увеличивают общий массив данных, чем запутывают пользователя. Определим способы, какими можно быстро удалить пустые элементы.
Алгоритмы удаления
Прежде всего, нужно разобраться, а действительно ли можно в конкретном массиве или таблице удалять пустые ячейки? Указанная процедура приводит к смещению данных, а это далеко не всегда является допустимым. По сути, элементы можно удалять только в двух случаях:
- Если строка (столбец) полностью является пустой (в таблицах);
- Если ячейки в строке и столбце логически не связаны друг с другом (в массивах).
Если пустых ячеек мало, то их вполне можно удалить с помощью обычного ручного способа удаления. Но, если таких незаполненных элементов большое количество, то в этом случае, данную процедуру нужно автоматизировать.
Способ 1: выделение групп ячеек
Наиболее простой способ удалить пустые элементы – это воспользоваться инструментом выделения групп ячеек.
- Выделяем диапазон на листе, над которым будем проводить операцию поиска и удаления пустых элементов. Жмем на функциональную клавишу на клавиатуре .
- Запускается небольшое окошко, которое называется . Жмем в нем кнопку .
- Открывается следующее окно – . Устанавливаем в нем переключатель в позицию . Выполняем щелчок по кнопке .
- Как видим, все пустые элементы указанного диапазона были выделены. Кликаем по любому из них правой кнопкой мыши. В запустившемся контекстном меню щелкаем по пункту .
- Открывается маленькое окошко, в котором нужно выбрать, что именно следует удалить. Оставляем настройки по умолчанию – . Жмем на кнопку .
После этих манипуляций все пустые элементы внутри указанного диапазона будут удалены.
Способ 2: условное форматирование и фильтрация
Удалить пустые ячейки можно также путем применения условного форматирования и последующей фильтрации данных. Этот метод сложнее предыдущего, но, тем не менее, некоторые пользователи предпочитают именно его. Кроме того, нужно сразу оговориться, что этот способ подходит только в том случае, если значения находятся в одном столбце и не содержат формулы.
- Выделяем диапазон, который собираемся обрабатывать. Находясь во вкладке , жмем на пиктограмму , которая, в свою очередь, располагается в блоке инструментов . Переходим в пункт открывшегося списка . В появившемся списке действий выбираем позицию .
- Открывается окошко условного форматирования. В левое поле вписываем цифру . В правом поле выбираем любой цвет, но можно оставить настройки по умолчанию. Щелкаем по кнопке .
- Как видим, все ячейки указанного диапазона, в которых находятся значения, были выделены в выбранный цвет, а пустые остались белыми. Опять выделяем наш диапазон. В этой же вкладке щелкаем по кнопке , расположенной в группе . В открывшемся меню жмем на кнопку .
- После этих действий, как видим, в верхнем элементе столбца появилась пиктограмма символизирующая фильтр. Жмем на неё. В открывшемся списке переходим в пункт . Далее в группе выбираем тот цвет, которым произошло выделение в результате условного форматирования.
Можно также сделать немного по-другому. Кликаем по значку фильтрации. В появившемся меню снимаем галочку с позиции . После этого щелкаем по кнопке .
- В любом из указанных в предыдущем пункте вариантов пустые элементы будут скрыты. Выделяем диапазон оставшихся ячеек. На вкладке в блоке настроек выполняем щелчок по кнопке .
- Затем выделяем любую пустую область на том же или на другом листе. Выполняем щелчок правой кнопкой мыши. В появившемся контекстном списке действий в параметрах вставки выбираем пункт .
- Как видим, произошла вставка данных без сохранения форматирования. Теперь можно удалить первичный диапазон, а на его место вставить тот, который мы получили в ходе вышеописанной процедуры, а можно продолжать работу с данными на новом месте. Тут все уже зависит от конкретных задач и личных приоритетов пользователя.
Способ 3: применение сложной формулы
Кроме того, убрать пустые ячейки из массива можно, применив сложную формулу, состоящую из нескольких функций.
- Прежде всего, нам нужно будет дать имя диапазону, который подвергается трансформации. Выделяем область, делаем щелчок правой кнопкой мышки. В активировавшемся меню выбираем пункт .
- Открывается окно присвоения наименования. В поле даем любое удобное название. Главное условие – в нем не должно быть пробелов. Для примера мы присвоили диапазону наименование . Больше никаких изменений в том окне вносить не нужно. Жмем на кнопку .
- Выделяем в любом месте на листе точно такой же по размерам диапазон пустых ячеек. Аналогично кликаем правой кнопкой мыши и, вызвав контекстное меню, переходим по пункту .
- В открывшемся окне, как и в предыдущий раз, присваиваем любое наименование данной области. Мы решили дать ей название .
- Выделяем двойным щелчком левой кнопки мышки первую ячейку условного диапазона (у вас он может назваться и по-другому). Вставляем в неё формулу следующего типа:
=ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых)+1>ЧСТРОК(С_пустыми)-СЧИТАТЬПУСТОТЫ(С_пустыми);»»;ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(С_пустыми «»;СТРОКА(С_пустыми);СТРОКА()+ЧСТРОК(С_пустыми)));СТРОКА()-СТРОКА(Без_пустых)+1);СТОЛБЕЦ(С_пустыми);4)))
Так как это формула массива, то для выведения расчета на экран нужно нажать комбинацию клавиш , вместо обычного нажатия кнопки .
- Но, как видим, заполнилась только одна ячейка. Для того, чтобы заполнились и остальные, нужно скопировать формулу на оставшуюся часть диапазона. Это можно сделать с помощью маркера заполнения. Устанавливаем курсор в нижний правый угол ячейки, содержащей комплексную функцию. Курсор должен преобразоваться в крестик. Зажимаем левую кнопку мыши и тянем его вниз до самого конца диапазона .
- Как видим, после этого действия мы имеем диапазон, в котором подряд расположены заполненные ячейки. Но выполнять различные действия с этими данными мы не сможем, так как они связаны формулой массива. Выделяем весь диапазон . Жмем на кнопку , которая размещена во вкладке в блоке инструментов .
- После этого выделяем первоначальный массив данных. Щелкаем правой кнопкой мыши. В открывшемся списке в группе жмем на пиктограмму ».
- После этих действий данные будут вставлены в изначальную область своего расположения цельным диапазоном без пустых ячеек. При желании массив, который содержит формулу, теперь можно удалить.
Существует несколько способов удаления пустых элементов в Microsoft Excel. Вариант с выделением групп ячеек наиболее простой и быстрый. Но ситуации бывают разные. Поэтому, как дополнительные способы, можно использовать варианты с фильтрованием и применением комплексной формулы.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.