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

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

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

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

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

1. Удалить не работает для электронных таблиц Excel.

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

1. Ваши действия могут повредить формулы и именованные диапазоны.

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

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

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

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

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

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

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

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

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

Читайте также: Как вставить таблицу в ячейку Excel

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

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

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

   

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

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

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

1. Прежде всего, нам нужно дать имя трансформируемой области. Выберите область, щелкните правой кнопкой мыши. В активированном меню выберите пункт «Присвоить имя…».
2. Откроется окно имени. В поле «Имя» даем любое подходящее имя. Главное условие — он не должен содержать пробелов. В качестве примера мы назвали диапазон «With_empty». Никаких других изменений в этом окне делать не нужно. Нажмите на кнопку «ОК».
3. Выберите в любом месте листа точно такой же размер пустых ячеек. Таким же образом щелкаем правой кнопкой мыши и вызвав контекстное меню переходим в пункт «Присвоить имя…».
4. В открывшемся окне, как и в предыдущий раз, присваиваем этой области любое имя. Мы решили назвать его «Без_пустого».
5. Двойным щелчком левой кнопки мыши выберите первую ячейку в условном диапазоне «Без_пустых» (можно назвать по-другому). Вставляем формулу следующего вида:=ЕСЛИ(СТРОКА()-СТРОКА(Без_пробелов)+1>НЕСТРОКА(С_пробелами)-СЧЁТНУЛЬ(С_пробелами),»»;ДВССЫЛ(АДРЕС(ЗАГРУЗИТЬ((ЕСЛИ(С_пустыми «»;СТРОКА(С_пробелами);СТРОКА() +СТРОКИ (С_пробелами)));СТРОКА()-СТРОКА(Без_пробелов)+1);СТОЛБОН(С_пробелами);4)))

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

   

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

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

Способ 4.

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

Однако матричные формулы с большими объемами данных работают довольно медленно. А на особо больших массивах в несколько тысяч строк Excel может даже «зависнуть». Поэтому в таких случаях проще и быстрее будет создать на листе дополнительный столбец для промежуточных расчетов. Зато производительность вырастет многократно.

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

Для этого проверяем выполнение 2-х условий:

• ячейка в столбце B не пуста,
• он не равен 0.

=ЕСЛИ((B3<>»»)+(B3<>0)=2;СЧЕТ(A$2:A2)+1;»»)

Если условия соблюдены, мы подсчитываем количество чисел в этом столбце над текущей позицией и увеличиваем результат на 1 (своего рода счетчик). То есть записываем, какая непустая позиция была найдена. Если ячейка в столбце B пуста, введите «».

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

Давайте перейдем ко второму шагу. Функция ВПР последовательно ищет значения начиная с 1 в столбце А и возвращает соответствующие значения из В.

=ОШИБКА(ПРОСМОТР(СТРОКА(A1);A$3:B$16,2,0);»»)

Чтобы получить последовательность чисел, используйте STRING().

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

Способ 5.

Предвидя, что многим может не понравиться торчащий с цифрами вспомогательный столбец А, предлагаю еще один способ избавиться от пустых ячеек. Здесь мы используем так называемый «левый ВПР». Поэтому вспомогательную колонку можно разместить в любом месте, где она не будет мешать. Чтобы не портить вид вашей таблицы, вы можете использовать любую другую колонку справа (например, Z). Мы используем B для визуализации результатов.

В позиции B2 запишем вспомогательную формулу

=ЕСЛИ(A2<>$D$1,1;»»)

D1 будет служить нам эталоном как совершенно пустой. Сравните столбец А последовательно с ним. Неопределенные значения игнорируются, а также содержимое нулевой длины «».

В С2 пишем:

=ОШИБКА(ИНДЕКС($A$2:$A$21,ПОИСКПОЗ(СТРОКА(1:1),$B$2:$B$21,0),1);»»)

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

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

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

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

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

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

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

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

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

1. Щелкните заголовок первого пустого столбца справа от таблицы и нажмите Ctrl + Shift + End. Это выберет область справа между вашими данными и последней использованной позицией листа.
2. На вкладке «Главная» нажмите «Очистить». Или щелкните правой кнопкой мыши на выделении и выберите Удалить.
3. Щелкните заголовок первой чистой строки под данными и нажмите Ctrl + Shift + End.
4. Выполните те же действия, что и в шаге 2.
5. Нажмите Ctrl + S, чтобы сохранить книгу.

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

Примечание. Учитывая, что Microsoft Excel 2007-2019 имеет более 1 000 000 строк и более 16 000 столбцов, вы можете уменьшить размер рабочей области, чтобы пользователи случайно не вводили данные не в те места.

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

1. Выберите строку под последней позицией данных (щелкните заголовок, чтобы выбрать всю строку).
2. Нажмите Ctrl + Shift + ↓ (стрелка вниз), чтобы расширить выделение до нижней части листа.
3. Нажмите Ctrl + 9, чтобы скрыть выбранные строки.

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

Точно так же вы скрываете неиспользуемые столбцы:

1. Выберите пустой столбец после последнего столбца данных.
2. Нажмите Ctrl + Shift + → (стрелка вправо), чтобы выбрать все остальные неиспользуемые столбцы внизу листа.
3. Нажмите Ctrl + 0, чтобы скрыть выбранные столбцы. Прозрачный!

Если позже вы решите показать все ячейки, выберите весь лист (используя Ctrl + A), затем нажмите Ctrl + Shift + 9, чтобы показать все строки, и Ctrl + Shift + 0, чтобы показать все столбцы.

Как удалить повторяющиеся строки в Excel?

Чтобы удалить одинаковые строки в Excel, выделите всю таблицу. Перейдите на вкладку «Данные» — «Работа с данными» — «Удалить дубликаты».

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

После нажатия кнопки «ОК» Excel создаст такой мини-отчет.

Как удалить каждую вторую строку в Excel?

Вы можете сократить таблицу с помощью макроса. Например вот так:

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

1. В конце таблицы создаем справочную колонку. Заполнить чередующимися данными. Например «ооооо» и т.д. Вводим значения в первые четыре ячейки. Затем мы их разделяем. «Прицепитесь» к черному крестику в правом нижнем углу и скопируйте буквы в конец ряда.
2. Установите «Фильтр». Фильтруем последний столбец по значению «y».
3. Выбираем все, что осталось после фильтрации и удаляем.
4. Убираем фильтр — останутся только ячейки с «о».

Вспомогательную колонку можно исключить и работать с «экономным столом».

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

Однажды пользователь скрыл часть информации в строках, чтобы она не отвлекала от работы. Думал, что потом данные еще понадобятся. Не обязательно — скрытые строки можно удалить: они влияют на формулы, мешают.

Ряды 5, 6, 7 скрыты в тренировочной таблице:

Мы удаляем их.

1. Перейдите в «Файл» — «Подробности» — «Проверить наличие проблем» — инструмент «Инспектор документов».
2. В открывшемся окне установите флажок «Скрытые строки и столбцы». Нажмите «Проверить».
3. Через несколько секунд программа выдаст результат проверки.
4. Нажмите «Удалить все». На экране появится уведомление.

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

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

Использование сортировки

Сортировка помогает переместить все ненужное вниз, потом его можно будет легко удалить. Для этого выделите всю таблицу и щелкните по ней правой кнопкой мыши. В раскрывающемся меню выберите «Сортировать”.

Затем отсортируйте от большего к меньшему или по алфавиту. В результате все лишнее съедет вниз. Затем вы просто выбираете нужный и удаляете строки в таблице Excel.

Использование макроса

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

1. откройте разработчик с помощью Alt+F11.
2. Создайте новый модуль для используемой электронной таблицы.
3. Вставить макрос:

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

Этот макрос выбирает только пустые ячейки. Но вы можете удалить их массово таким же образом. Для этого необходимо использовать тот же макрос, но заменить термин diapaz2.Select на diapaz2.Удалить. После внесения изменений все будет выбрано и удалено автоматически.

Использование функции выделения

1. Сначала выделите все ячейки в таблице. В разделе «Редактирование» (вкладка «Главная») нажмите на кнопку «Найти и выделить», значок которой имеет вид увеличительного стекла, и в открывшемся списке выберите пункт «Выделить группу ячеек… «элемент…”.
2. В открывшемся дополнительном окне поставьте галочку напротив опции «пустые ячейки» и подтвердите действие, нажав кнопку ОК.
3. Теперь можно приступить к удалению отмеченных ячеек, например, с помощью кнопки «Удалить», которая находится в разделе «Ячейки» (вкладка «Главная”).

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