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

Учебное пособие научит вас, как удалять пустые места в Excel, чтобы придать вашим листам четкий и профессиональный вид.

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

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

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

С резервной копией, хранящейся в месте сохранения, выполните следующие действия, чтобы удалить пустые ячейки в Excel:

Выберите диапазон, в котором вы хотите удалить пробелы. Чтобы быстро выбрать все ячейки с данными, щелкните верхнюю левую ячейку и нажмите Ctrl + Shift + End. Это расширит выбор до последней использованной ячейки.
Нажмите F5 и нажмите Специальный… . Или перейти к Дом вкладка > Форматы группу и нажмите Найти и выбрать > Перейти к специальному:
в Перейти к специальному диалоговом окне выберите «Пробелы» и щелкните ХОРОШО. Это выделит все пустые ячейки в диапазоне.
Щелкните правой кнопкой мыши любую из выделенных заготовок и выберите «Удалить…» в контекстном меню:
В зависимости от расположения ваших данных выберите сдвинуть ячейки влево или же сдвинуть ячейки вверхи нажмите ХОРОШО. В этом примере мы выбираем первый вариант:

Вот и все. Вы успешно удалили пробелы в своей таблице:

Советы:

Если что-то пошло не так, не паникуйте и сразу же нажимайте Ctrl+Z, чтобы вернуть данные.
Если вы хотите только выделить пустые ячейки, а не удалить их, в этой статье вы найдете несколько различных способов: Как выделить и выделить пустые ячейки в Excel.

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

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

1. Удаляйте пустые строки и столбцы вместо ячеек

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

2. Не работает для таблиц Excel

Невозможно удалить какие-либо отдельные ячейки в таблице Excel (по сравнению с диапазоном), вам разрешено удалять только целые строки таблицы. Или вы можете сначала преобразовать таблицу в диапазон, а затем удалить пустые ячейки.

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

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

Как извлечь список данных, игнорируя пробелы

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

С исходным списком в A2: A11 введите приведенную ниже формулу массива в C2, нажмите Ctrl + Shift + Enter, чтобы завершить ее правильно, а затем скопируйте формулу вниз еще на несколько ячеек. Количество ячеек, в которые вы копируете формулу, должно быть равно или больше, чем количество элементов в вашем списке.

Формула для извлечения непустых ячеек:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$11, МАЛЕНЬКИЙ(ЕСЛИ(НЕ(НЕПУСТО($A$2:$A$11)), СТРОКА($A$1:$A$10),””), СТРОКА(A1) ))””)

На следующем снимке экрана показан результат:

Как работает формула

Хитрая на первый взгляд, при ближайшем рассмотрении логика формулы легко прослеживается. На простом английском языке формула в C2 выглядит следующим образом: вернуть первое значение в диапазоне A2:A11, если эта ячейка не пуста. В случае ошибки вернуть пустую строку (“”).

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

У вас есть функция ИНДЕКС, возвращающая значение из $A$2:$A$11 на основе указанного номера строки (не реального номера строки, а относительного номера строки в диапазоне). В более простом сценарии мы могли бы поместить INDEX($A$2:$A$11, 1) в C2, и это принесло бы нам значение в A2. Проблема в том, что нам нужно учесть еще 2 вещи:

Убедитесь, что A2 не пустой
Возвращает второе непустое значение в ячейке C3, третье непустое значение в ячейке C4 и так далее.

Обе эти задачи обрабатываются функцией SMALL(array,k):

МАЛЕНЬКИЙ(ЕСЛИ(НЕ(НЕПУСТО($A$2:$A$11)), СТРОКА($A$1:$A$10),””), СТРОКА(A1))

В нашем случае множество аргумент генерируется динамически следующим образом:

NOT(ISBLANK($A$2:$A$11)) определяет, какие ячейки в целевом диапазоне не являются пустыми, и возвращает для них TRUE, в противном случае FALSE. Полученный массив ИСТИНА и ЛОЖЬ отправляется на логическую проверку функции ЕСЛИ.
ЕСЛИ оценивает каждый элемент массива ИСТИНА/ЛОЖЬ и возвращает соответствующее число для ИСТИНА и пустую строку для ЛОЖЬ:
ЕСЛИ ({ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА}, СТРОКА ($ A $ 1: $ A $ 10), “”)

ROW($A$1:$A$10) требуется только для возврата массива чисел от 1 до 10 (поскольку в нашем диапазоне 10 ячеек), из которого IF может выбрать число для значений TRUE.

В результате мы получаем массив {1;””;3;””;5;6;””;8;””;10} и наша сложная МАЛЕНЬКАЯ функция превращается в вот такую простую:

МАЛЕНЬКИЙ({1;””;3;””;5;6;””;8;””;10}, СТРОКА(A1))

Как видите, множество аргумент содержит только номера непустых ячеек (заметьте, это относительные положения элементов в массиве, т.е. A2 — элемент 1, A3 — элемент 2 и т. д.).

в к аргумент, мы помещаем ROW(A1), который указывает функции SMALL возвращать наименьшее число из 1. Из-за использования относительной ссылки на ячейку номер строки увеличивается с шагом 1, когда вы копируете формулу вниз. Так, в С3, к изменится на ROW(A2), и формула вернет номер второй непустой ячейки и так далее.

Однако на самом деле нам не нужны непустые номера ячеек, нам нужны их значения. Итак, мы движемся вперед и вкладываем функцию МАЛЕНЬКИЙ в row_num аргумент ИНДЕКС, заставляющий его возвращать значение из соответствующей строки в диапазоне.

В качестве последнего штриха мы заключаем всю конструкцию в функцию ЕСЛИОШИБКА для замены ошибок пустыми строками. Ошибки неизбежны, потому что вы не можете знать, сколько непустых ячеек находится в целевом диапазоне, поэтому вы копируете формулу в большее количество ячеек.

Учитывая вышеизложенное, мы можем построить эту общую формулу для извлечения значений без учета пробелов:

{=ЕСЛИОШИБКА(ИНДЕКС(диапазонМАЛЕНЬКИЙ(ЕСЛИ(НЕ(НЕПУСТО(диапазон)), СТРОКА($A$1:$A$10),””), СТРОКА(A1))),””)}

Где «диапазон» — это диапазон ваших исходных данных. Обратите внимание, что ROW($A$1:$A$10) и ROW(A1) являются постоянными частями и никогда не меняются, независимо от того, где начинаются ваши данные и сколько ячеек они включают.

Как удалить пустые ячейки после последней ячейки с данными

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

Как найти последнюю использованную ячейку на листе

Чтобы перейти к последней ячейке на листе, которая содержит данные или форматирование, щелкните любую ячейку и нажмите Ctrl + End.

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

Удалить ячейки после последней ячейки с данными

Чтобы очистить все содержимое и форматирование после последней ячейки с данными, сделайте следующее:

Щелкните заголовок первого пустого столбца справа от ваших данных и нажмите Ctrl + Shift + End. Это выберет диапазон ячеек между вашими данными и последней использованной ячейкой на листе.
На Дом вкладка, в Редактирование группа, нажмите чистый > Очистить все. Или щелкните правой кнопкой мыши выделение и выберите «Удалить…» > «Весь столбец»:
Щелкните заголовок первой пустой строки под вашими данными и нажмите Ctrl + Shift + End.
Нажмите чистый > Очистить все на Дом вкладку или щелкните правой кнопкой мыши выделенный фрагмент и выберите «Удалить…» > «Вся строка».
Нажмите Ctrl + S, чтобы сохранить книгу.

Проверить используемый диапазон чтобы убедиться, что теперь он содержит только ячейки с данными и не содержит пробелов. Если сочетание клавиш Ctrl + End снова выбирает пустую ячейку, сохраните книгу и закройте ее. Когда вы снова открываете рабочий лист, последняя использованная ячейка должна быть последней ячейкой с данными.

Кончик. Учитывая, что Microsoft Excel 2007 и более поздних версий содержит более 1 000 000 строк и более 16 000 столбцов, вы можете уменьшить размер рабочей области, чтобы пользователи не могли непреднамеренно вводить данные в неправильные ячейки. Для этого вы можете просто удалить пустые ячейки из их представления, как описано в разделе Как скрыть неиспользуемые (пустые) строки и столбцы.

Вот как вы удаляете пробел в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!