Как пропустить пустые строки в excel формула
Обновлено: 04.07.2024
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача - удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.
Способ 2. Формула массива
Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)
Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.
Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:
В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))
Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:
Способ 3. Пользовательская функция на VBA
Если есть подозрение, что вам часто придется повторять процедуру удаления пустых ячеек из диапазонов, то лучше один раз добавить в стандартный набор свою функцию для удаления пустых ячеек, и пользоваться ей во всех последующих случаях.
Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:
Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:
Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить нули из списка значений. В конце статьи научимся удалять вообще любое заданное значение из списка: символ, число, текстовую строку.
Пусть имеется список с пустыми ячейками (столбец А ).
Задача
Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом "убрать" будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы - программу на VBA.
Решение
Для избавления от пустых ячеек запишем в ячейке B2 формулу массива и скопируем ее вниз (см. файл примера): =ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14));СТРОКА(A1)));"")
Получим в соседнем столбце B список со значениями из исходого, но уже без пропусков. Формула работает одинакового и для текстовых значений и для чисел. Алгоритм работы формулы следующий:
- ЕСЛИ(ЕПУСТО($A$2:$A$14);"";СТРОКА($A$2:$A$14)) – если ячейка не пуста, то эта часть формулы возвращает номер строки. То есть формируется массив номеров строк, НЕ содержащих пустоты На месте пустых ячеек в массиве будет символ "" (пустой текст), но можно его заменить в формуле на любую текстовую строку, например "ккк". Проверить результат можно выделив эту часть формулы и нажав клавишу F9 ;
- Функция НАИМЕНЬШИЙ() сортирует массив строк по возрастанию. В сортированном списке сначала будут идти номера строк затем значения "", т.к. в EXCEL считается, что любое текстовое значение больше любого числа (значение пустой текст - текстовое значение);
- Далее для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ("A"&2) возвращает значение из ячейки А2 . Для пустых ячеек будет формироваться ошибочные адреса ячеек, состоящие только из символа А. Это вызовет ошибку после применения функции ДВССЫЛ();
- Функция ЕСЛИОШИБКА() вместо ошибки будет возвращать "". Этот символ не отображается в ячейке и ячейка выглядит пустой.
На самом деле в соседнем столбце B список будет отличаться не только тем, что в нем значения будут идти подряд без пропусков. Если в исходном списке пустые ячейки действительно не содержали ничего, то в новом списке в пустых ячейках будут значения Пустой текст "". Хотя внешний вид пустых ячеек из обоих списков будет неотличим, но формулы увидят разницу. То есть, если Вы планируете делать дальнейшие манипуляции с новым списком, то имейте ввиду, что теперь пустые ячейки в конце списка теперь не совсем пусты - они содержат текстовое значение "". Подробнее про это специфическое значение читайте здесь .
Изменим немного формулу: =ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ( ЕПУСТО($A$2:$A$14);"";$A$2:$A$14);СТРОКА(A1));"")
Получим тот же список, но еще и сортированный по возрастанию (работает только для чисел).
Список также можно сформировать в столбце С другой формулой массива :
СписокСпропусками в формуле - это динамический диапазон , который образован формулой:
Длину списка с пропусками можно вычислить с помощью формулы:
СОВЕТ:
Для удаления и выделения пустых строк и ячеек традиционными средствами EXCEL, пользуйтесь идеями из статей Удаляем пустые строки в таблице и Выделение группы ячеек . О том, что EXCEL понимает под пустыми ячейками, читайте в статье Подсчет пустых ячеек .
Удаляем заданные символы из списка
Часто в списке присутствуют ненужные для дальнейшего анализа значения, например 0 (нуль).
Немного модернизировав вышеуказанную формулу получим универсальный подход для удаления вообще любого символа, числа или текстовой строки из исходного списка (см. файл примера, в котором на листе Произвольный символ приведена универсальнаяформула).
Пусть исходный список находится в диапазоне A12:A24, а в ячейке B6 содержится значение которое нужно удалить из ячеек списка.
Единственным отличием является выражение $A$12:$A$24=$B$6, которое заменило формулу с ЕПУСТО(. )
Теперь если значение в исходном списке не равно искомому значению, то вместо него будет выведено значение "" (в конце списка).
Часто, особенно при импорте данных в EXCEL, на листе могут формироваться таблицы с ПОЛНОСТЬЮ пустыми строками. Научимся быстро удалять эти ненужные строки, которые в дальнейшем могут затруднить работу с таблицей.
Представим, что у нас есть таблица с какими-то значениями, в которой есть полностью пустые строки.
Приведем методы, которые используются при удалении пустых строк. Зачем нужно удалять пустые строки можно прочитать в статье Советы по построению таблиц .
Сортировка
Самым простым способом удаления пустых строк является сортировка таблицы: выделяем диапазон, в котором содержится таблица ( А2:C17 ), затем вызываем команду меню Данные/ Сортировка и фильтр/ Сортировка от минимального к максимальному или просто /Сортировка .
При сортировке таблиц с несколькими столбцами нужно быть осторожным, чтобы ячейки не перескочили из своих строк в другие. Поэтому, для сортировки нужно выделять всю таблицу. После сортировки по возрастанию пустые строки окажутся внизу таблицы.
Если важен порядок строк до сортировки, то перед сортировкой нужно создать столбец с порядковой нумерацией строк, а после удаления пустых строк, заново отсортировать таблицу уже по этому столбцу.
Фильтр
Для нахождения пустых ячеек можно также воспользоваться Фильтром ( Главная/ Редактирование/ Сортировка и фильтр/ Фильтр ). Выбираем подходящий столбец, отображаем только пустые ячейки, выделяем строки не содержащие данных и удаляем их.
Выделение группы ячеек
Другим способом выделения пустых ячеек является использование инструмента Выделение группы ячеек .
- выделяем диапазон А2:C17 ;
- выбираем пункт меню Главная/ Редактирование/ Найти и выделить/ Выделение группы ячеек… ,
- выберите пункт пустые ячейки в разделе Выделить ;
- далее нажимаем маленькую стрелочку в меню Главная/ Ячейки/ Удалить ;
- выберем Удалить ячейки (удалить ячейки, со сдвигом вверх) .
СОВЕТ: Стоит помнить, что ячейки, содержащие формулы (даже если это ссылки на пустые ячейки), не считаются пустыми по определению.
Рассмотрим диапазон значений, в который регулярно заносятся новые данные.
Диапазон без пропусков и начиная с первой строки
В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(A:A))
Формула работает для числовых и текстовых диапазонов (см. Файл примера )
Значение из последней заполненной ячейки в столбце выведем с помощью функции ИНДЕКС() : =ИНДЕКС(A:A;СЧЁТЗ(A:A))
Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так: =ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))
Диапазон без пропусков в любом месте листа
Если список, в который вводятся значения расположен в диапазоне E8:E30 (т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом: =СЧЁТЗ(E9:E30)+СТРОКА(E8)
Формула СТРОКА(E8) возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции ИНДЕКС() : =ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))
Диапазон с пропусками (числа)
В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.
Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.
Так как в качестве просматриваемого массива указан целый столбец ( A:A ), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.
Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20 , можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))
Диапазон с пропусками (текст)
В случае необходимости определения номера строки последнего текстового значения (также при наличии пропусков), формулу нужно переделать: =ПОИСКПОЗ("*";$A:$A;-1)
Пустые ячейки, числа и текстовое значение Пустой текст ("") игнорируются.
Диапазон с пропусками (текст и числа)
Если столбец содержит и текстовые и числовые значения , то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ("*";$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))
Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.
Другим универсальным решением является формула массива : =МАКС(СТРОКА(A1:A20)*(A1:A20<>""))
После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER . Предполагается, что значения вводятся в диапазон A1:A20 . Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.
Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ("A"&МАКС(СТРОКА(A1:A20)*(A1:A20<>"")))
Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER .
СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .
Читайте также: