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

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

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));"")

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

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

СписокСпропусками в формуле — это динамический диапазон , который образован формулой:

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

СОВЕТ:

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

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

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

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

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

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

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

Простой способ удалить пустые ячейки динамического выпадающего списка Excel

Всякий раз, когда я делаю зависимый динамический выпадающий список , я вижу кучу пустых ячеек в выпадающем списке , я ищу много тем, которые объясняют, как удалить их, добавляя два дополнительных диапазона, таких как explained her http:/ / blog.contextures.com / archives/2014/02/27/dynamic-list-with-blank-cells/

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

выпадающий список, содержащий пустую ячейку, все, что я сделал, это перешел к проверке данных и написал в источнике =MYCode , затем я назвал список, содержащий такие коды, как MyCode , и я проверил игнорировать пустой регистр (даже если он кажется бесполезным )

3 ответа

Вопрос в значительной степени говорит об этом, я думаю. Для получения подробной информации: У меня есть диапазон ячеек (F3:F2000), в которых могут быть заполнены имена. Я хочу иметь возможность подсчитать количество раз, когда было упомянуто имя. Я делаю это с =COUNTIF (. ), что не является.

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

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

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

$A$1 следует заменить на верхнюю ячейку вашего диапазона. $A$A следует заменить столбцом(столбцами), в котором находится диапазон.

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

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

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

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

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

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

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

Результатом будут ваши исходные данные без пробелов. Это разольется по соседним клеткам. Теперь вы можете ссылаться на эту единственную ячейку в качестве списка проверки данных, если добавите "#" в конец ссылки на ячейку. Это говорит excel включить все разлитые значения в список.

Выгоды:

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

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

В Excel я хочу удалить все пустые ячейки, чтобы все строки и столбцы имели текст. cell1 cell2 cell3 1 peter . Так, хочу как у него: cell1 cell2 1 peter Как я могу удалить пустые ячейки ? Я использую формат ods, это имеет отношение к делу?

Я надеюсь, что вы сможете помочь мне в этом вопросе. У меня есть файл Excel с 146 459 строками, и мне нужно удалить пустые ячейки, чтобы объединить мои данные. Вот образ того, что я имею в виду: Когда я выбираю все пробелы, мой ноутбук занимает около 2 минут, но затем, когда я пытаюсь удалить.