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

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

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

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

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

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

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

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

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

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

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

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

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

   

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

5. В любом из вариантов, указанных в предыдущем абзаце, пустые элементы будут скрыты. Выберите диапазон оставшихся ячеек. На вкладке «Главная» в блоке настроек «Буфер обмена» нажмите кнопку «Копировать».
6. Затем выберите пустую область на том же или другом листе. Выполняем щелчок правой кнопкой мыши. В контекстном списке действий, отображаемом во входных параметрах, выберите пункт «Значения».
7. Как видите, данные были введены без сохранения форматирования. Теперь вы можете удалить основной диапазон и вместо него ввести то, что мы получили в описанной выше процедуре, или вы можете продолжить работу с данными в новом месте. Все зависит от конкретных задач и личных приоритетов пользователя.

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

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

1. Прежде всего, нам нужно будет назвать диапазон, который преобразуется. Выберите область, щелкните правой кнопкой мыши. В активированном меню выберите пункт «Присвоить имя…».
2. Откроется окно именования. В поле «Имя» укажите удобное имя. Главное условие — в нем не должно быть зазоров. Например, мы назвали диапазон «C_blanks». Вам не нужно вносить никаких дополнительных изменений в это окно. Щелкните кнопку «ОК».
3. Выберите тот же диапазон размеров, что и для пустых ячеек в любом месте листа. Аналогично щелкните правой кнопкой мыши и, вызвав контекстное меню, перейдите к пункту «Назначить имя…».
4. В открывшемся окне, как и раньше, присвойте этой области имя. Мы решили назвать его «Пусто_Пусто».
5. Выделите первую ячейку условного диапазона «No_blanks», дважды щелкнув левой кнопкой мыши (у вас может быть другое имя). Вставляем в него формулу следующего вида:

   = ЕСЛИ (СТРОКА () — СТРОКА (WITHOUT_BLOWS) +1> RIGHE (WITH_blanks) -COUNTBLANK (WITH_Empty); «»; КОСВЕННО (АДРЕС (МАЛЫЙ ((IF (WITH_blanks «»; СТРОКА (WITH_blanks) + СТРОКА) (WITH_blanks))); ROW () — ROW (Without_blanks) +1); COLUMN (WITH_blanks); 4)))

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

6. Но, как видите, заполнена только одна ячейка. Чтобы завершить остальное, вам нужно скопировать формулу в остальную часть диапазона. Это можно сделать с помощью ручки заполнения. Поместите курсор в нижний правый угол ячейки, содержащей сложную функцию. Курсор должен превратиться в крест. Удерживая левую кнопку мыши, перетащите ее в конец диапазона «No_blanks».
7. Как видите, после этого действия у нас есть диапазон, в котором заполненные ячейки находятся в одной строке. Но мы не сможем выполнять различные действия с этими данными, так как они связаны формулой массива. Выберите весь диапазон «No_blanks». Нажмите кнопку «Копировать», расположенную на вкладке «Главная» панели инструментов «Буфер обмена».
8. Далее выбираем начальный массив данных. Щелкаем правой кнопкой мыши. В открывшемся списке в группе «Параметры вставки» щелкните значок «Значения».
9. После этих шагов данные будут вставлены в исходную область своего положения в виде сплошного диапазона без пустых ячеек. При желании теперь можно удалить массив, содержащий формулу.

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