Как убрать пустые ячейки в выпадающем списке excel

There is another way. Create a dynamically-expanding named range. Then use the range to define the data validation list.

To create your dynamically-expanding range, insert this in the named range box and give it a name:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

$A$1 should be replaced with the top cell of your range. $A$A should be replaced with the column(s) the range is in.

OFFSET points the named range at a range of cells. COUNTA() is in the fourth position of the OFFSET formula, which sets the height of the range. It counts the number of non-blank cells. As a result, when you add a value, the fourth value of the OFFSET formula increases and you get an expanding range.

Note, this does not work if your named range has blank cells interspersed.

OFFSET formula from excel-easy.com.

history 24 апреля 2013 г.

Группы статей
• Вывод отобранных значений в отдельный диапазон
• Отбор строк в таблице
• Пустые ячейки

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

Пусть имеется список с

пустыми

ячейками (столбец

А

).

Задача

Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом «убрать» будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы — программу на VBA.

Решение

Для избавления от пустых ячеек запишем в ячейке

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
  
  ;

• Функция НАИМЕНЬШИЙ() сортирует массив строк по возрастанию. В сортированном списке сначала будут идти номера строк затем значения «», т.к. в EXCEL считается, что любое текстовое значение больше любого числа (значение пустой текст — текстовое значение);
• Далее для функции ДВССЫЛ() формируются адреса ячеек с непустыми значениями. Например, ДВССЫЛ(«A»&2) возвращает значение из ячейки
  
  А2
  
  . Для пустых ячеек будет формироваться ошибочные адреса ячеек, состоящие только из символа А. Это вызовет ошибку после применения функции ДВССЫЛ();
• Функция ЕСЛИОШИБКА() вместо ошибки будет возвращать «». Этот символ не отображается в ячейке и ячейка выглядит пустой.

На самом деле в соседнем столбце

B

список будет отличаться не только тем, что в нем значения будут идти подряд без пропусков. Если в исходном списке пустые ячейки действительно не содержали ничего, то в новом списке в пустых ячейках будут значения Пустой текст «». Хотя внешний вид пустых ячеек из обоих списков будет неотличим, но формулы увидят разницу. То есть, если Вы планируете делать дальнейшие манипуляции с новым списком, то имейте ввиду, что теперь пустые ячейки в конце списка теперь не совсем пусты — они содержат текстовое значение «». Подробнее про это специфическое значение читайте

здесь

.

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

Получим тот же список, но еще и

сортированный

по возрастанию (работает только для чисел).

Список также можно сформировать в столбце С другой

формулой массива

:

=ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО(СписокСпропусками);»»;СТРОКА(СписокСпропусками));СТРОКА()-СТРОКА($C$1)));»»)

СписокСпропусками

в формуле — это

динамический диапазон

, который образован формулой:

=СМЕЩ($A$2;;;ДлинаСпискаСпропусками)

Длину списка с пропусками

можно вычислить с помощью формулы:

=ПОИСКПОЗ(ПОВТОР(«я»;10);$A$2:$A$14;1)

СОВЕТ:

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

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

и

Выделение группы ячеек

. О том, что EXCEL понимает под пустыми ячейками, читайте в статье

Подсчет пустых ячеек

.

Удаляем заданные символы из списка

Часто в списке присутствуют ненужные для дальнейшего анализа значения, например 0 (нуль).

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

Пусть исходный список находится в диапазоне A12:A24, а в ячейке B6 содержится значение которое нужно удалить из ячеек списка.

=ЕСЛИОШИБКА(ДВССЫЛ(«A»&НАИМЕНЬШИЙ(ЕСЛИ($A$12:$A$24=$B$6;»»;СТРОКА($A$12:$A$24));СТРОКА()-СТРОКА($A$11)));»»)

Единственным отличием является выражение $A$12:$A$24=$B$6, которое заменило формулу с ЕПУСТО(…)

Теперь если значение в исходном списке не равно искомому значению, то вместо него будет выведено значение «» (в конце списка).