Удаляем в MS EXCEL пропуски в списке (формулы)

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

Пусть имеется список с пустыми ячейками (столбец А).

Задача

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

Решение

Для избавления от пустых ячеек запишем в ячейке B2 формулу массива и скопируем ее вниз (см .файл примера):
=ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14));СТРОКА(A1)));"")

Получим в соседнем столбце B список без пропусков. Формула работает одинакового и для текстовых значений и для чисел. Алгоритм работы формулы следующий:

  • ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14)) – если ячейка не пуста, эта часть формулы возвращает номер строки, тем самым формируется массив {2:"":4:5:6:"":"":9:10:"":"":13:14} Проверить результат можно выделив эту формула у нажав клавишу F9;
  • Функция НАИМЕНЬШИЙ() сортирует массив по возрастанию и, по очереди, в каждой строке возвращает элементы массива;
  • Для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ("A"&2) возвращает значение из ячейки А2.

Изменим немного формулу:
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ( ЕПУСТО($A$2:$A$14);"";$A$2:$A$14);СТРОКА(A1));"")

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

СОВЕТ:
Для удаления и выделения пустых строк и ячеек традиционными средствами EXCEL, пользуйтесь идеями из статей Удаляем пустые строки в таблице и Выделение группы ячеек. О том, что EXCEL понимает под пустыми ячейками, читайте в статье Подсчет пустых ячеек.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.7 (3 оценок)

Комментарии

Мейржан (не проверено)

Возможно ли пропуск пустых ячеек по определенным критериям

Creator

Если Вы имеете ввиду пропуск пустых ячеек в зависимости от соответствующего значения в соседнем столбце, то ответ - да. Нужно изменить формулу массива, добавив Ваш критерий (вместо функции ЕПУСТО() вставить другое выражение).

olr (не проверено)

подскажите, а как переделать формулу для строк, а не столбцов?

Creator

Для того, чтобы убрать пропуски из столбцов используйте формулу массива =ЕСЛИОШИБКА(ДВССЫЛ(АДРЕС(СТРОКА(B31);НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($B$31:$M$31);"";СТОЛБЕЦ($B$31:$M$31));СТОЛБЕЦ(A1))));"")

здесь предполагается, что горизонтальный массив с пропусками находится в диапазоне В31:М31, а формула введена в В32 и скопирована в ячейки правее.

olr (не проверено)

спасибо, за ответ. Но у меня не работает формула если данные находятся не другом листе. Почему?

Creator

Да, следует изменить формулу: =ЕСЛИОШИБКА(ДВССЫЛ(АДРЕС(СТРОКА(Лист1!B31);НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО(Лист1!$B$31:$M$31);"";СТОЛБЕЦ(Лист1!$B$31:$M$31));СТОЛБЕЦ(A1));;;"Лист1"));"")

Теперь файл примера содержит эту формулу.

olr (не проверено)

попробывал- значения подставляет, но не удаляет пустых ячеек...что не так?

Creator

В Вашем вопросе содержится ответ: действительно формулы не удаляют и не могут удалить пустые ячейки по определению. В статье идет речь о создании таблицы или нового диапазона, где нет пропусков, а исходная таблица НЕ изменяется. Чтобы удалить пустые ячейки используйте идеи статьи http://excel2.ru/articles/udalyaem-pustye-stroki-v-tablice

olr (не проверено)

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

Creator

Пришлите пожалуйста Ваш файл на creator@excel2.ru

Creator
Я посмотрел Ваш файл: пустая ячейка - это та, в которой ничего нет (а у вас в ячейке была формула и функция ЕПУСТО() не работала). Пропуск в строке - это жаргон, т.к. пропуск - это лишь ячейка, которая выглядит пустой, а пустая она или содержит пустой текст ""(как было у Вас) или вообще содержит значение которое скрыто условным форматированием или форматом ячейки. Об этом я писал в нескольких статьях, см http://excel2.ru/gruppy-statey/pustye-yacheyki
 
Константин (не проверено)

Подскажите как тогда можно действовать в такой ситуации? Пропустить "условно пустые" ячейки?

Creator

Отличный вопрос! Формула с сортировкой будет работать и в случае наличия в списке ячеек со значением Пустой текст (""). Вот эта формула: =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);"";$A$2:$A$14);СТРОКА(A1));"")


Не забываем, что Excel при сравнении числа и текстового значения с помощью операторов > или <  считает, что любое число "меньше" любого текстового значения, в том числе и значения Пустой текст. Именно по этому и работает формула с сортировкой.


Теперь файл примера содержит также решение для списка со значениями Пустой текст (см. лист Пустой текст).


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

Павел (не проверено)

Здравствуйте! Почему то у меня данная формула =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);"";$A$2:$A$14);СТРОКА(A1));"") работает, но значения почему то в перевернутом виде) в обратном порядке

Creator

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