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

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

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

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

Задача

Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом "убрать" будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы — программу на 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 включить все разлитые значения в список.

Выгоды:

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

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

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

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

Похожие вопросы:

Изнутри C# я пытаюсь прочитать данные с листа Excel в объект C#. Все работает нормально, за исключением одной маленькой детали-Excel данных из выпадающих списков. Каким-то образом результат в.

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

Я добавляю список проверки данных в существующую электронную таблицу Excel. Данные поступают с отдельного листа, называемого данными. Диапазон для данных — A4-A100; пока данные находятся только в.

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

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

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

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

Я хочу создать 2 зависимых выпадающих списка в excel, первое выпадающее значение решает, какое значение должно быть отображено для второго выпадающего списка. Используется следующая формула OFFSET.

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

Я хочу удалить пустые ячейки в excel тысяч строк и столбцов может ли кто-нибудь помочь мне в этом? Значения excel должны быть в порядке. Входной Сигнал Образца : желаемый результат :

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

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter китин

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter Автор — китин
Дата добавления — 10.06.2020 в 13:24

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

При таком решении поставщик в список для проверки добавляется, а выпадающий список приходиться корректировать ручками. Есть ли способ и в выпадающий добавлять? Новичёк

При таком решении поставщик в список для проверки добавляется, а выпадающий список приходиться корректировать ручками. Есть ли способ и в выпадающий добавлять? Автор — Новичёк
Дата добавления — 10.06.2020 в 14:34

Выпадающий список без пустых строк

Скопировать выделенный диапазон без пустых строк
Нужно в Exel 2010 копировать данные в буфер обмена без пустых ячеек. Для Select. не могу найти.

Объединить данные в 1 столбец без повторений и пустых строк
Как можно объединить данные в 1 столбец без повторений и пустых строк? С помощью кода.

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

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

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

раскрывающийся список в ячейке вне таблицы

http :// prntscr . com/s7iedtt
убрать пробелы

Добавлено через 6 минут
Кстати как Вы картинку прямо в пост ставите? Никак не пойму.

Вложения

Книга1 (8).xlsx (9.8 Кб, 86 просмотров)

Выпадающий список без повторов.
Всем, привет. Помогите советом. На форме есть DBLookupComboBoxEh и ListBox. Вот у меня 2.

Выпадающий список без submit
Снова всем привет. И снова мои ламерские вопросы. Есть выпадающий список, что к нему.

Как сделать чтобы файл создавался без пустых строк?
FileStream Create создаёт текстовый файл с двумя пустыми строчками. Как сделать чтобы файл.

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

Выпадающий список с обновлением страницы без перезагрузки
Народ help me , помогите сделать выпадающий список на одной странице , есть у меня 21 район при.

PHP выпадающий список без HTML — возможно?
Есть таблица в SQL — &quot;Item&quot; — в ней поле &quot;name&quot; — можно ли в php ‘name’ вывести в выпадающий.

Как строить выпадающие списки в Excel

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

Есть много способов, чтобы создавать выпадающие списки в Excel. Рассмотрим некоторые вместе с аналитиком Laba Александром Галабурдой.

Как работает выпадающий список в Excel

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

Чтобы создать такой выпадающий список, перейдите в раздел «Данные» на панели инструментов, в группе «Работа с данными» выберите пункт «Проверка данных».

Далее всплывает окно «Проверка вводимых значений».

Окно делится на 3 вкладки:

#1. Параметры

Здесь задаются основные параметры выпадающего списка в Excel:

• Тип данных. Можно выбрать тип данных, который будет содержать список: диапазон целых или действительных чисел, текстовые выражения, даты и время. Можно задать ограничения по длине текста и различные формулы.
• Игнорировать пустые значения — данный пункт означает, что Excel не будет проверять на правильность ячейки, в которых содержатся пустые значения.
• Список допустимых значений. Этот флажок отображается только в том случае, если выбран тип данных «Список». Если убрать флажок, в ячейке будет происходить проверка на соответствие значений списку, но раскрывающее поле с выпадающими значениями будет отсутствовать.
• Значение. Работает только с теми типами данных, в которых можно задать ограничения по числам или датам.
• Источник. Здесь перечисляются значения для проверки данных или задается формула.
• Распространить изменения на другие ячейки с тем же условием. Excel здесь находит все ячейки в книге, которые ссылаются на идентичное по свойствам условие и изменяет их согласно новых параметров. В случае, если флажок не будет установлен, условие будет изменено только для выделенных ячеек в таблице.
• Очистить все — удаляет установленную проверку данных с выделенных ячеек.

#2. Подсказка по вводу

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

#3. Сообщение об ошибке

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

Примеры выпадающих списков в Excel

#1. Стандартный

Выделяем ячейку (диапазон ячеек), где должен всплывать выпадающий список (в нашем примере это вкладка «Проект», диапазон ячеек A2:A25), переходим в раздел «Проверка данных» (описано выше), выбираем тип данных «Список», в поле «Источник» вставляем диапазон с источника.

Если вам нужно ввести подсказки и настроить вывод сообщения об ошибке, переходим в соответствующие разделы и прописываем необходимые свойства. Затем нажимаем «ОК».

Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.

#2. Список с подстановкой данных

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

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

Настроим проверку данных иначе. Для автодополнения списков рассмотрим 2 варианта:

#1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel.

Подробнее о том, что такое «Умные таблицы» и как с ними работать — на наших курсах.

«Excel для финансов»

Алексей Вощак
Ex-Partner at Bridges Consulting

«Excel для бизнеса»

Алексей Вощак
Ex-Partner at Bridges Consulting

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

Чтобы выпадающий список в Excel стал динамическим, выделяем любую из ячеек, где он находится, переходим в раздел «Проверка данных». Нам подтянется текущее условие проверки.

В строке с источником прописываем ссылку на столбец таблицы с использованием функции ДВССЫЛ: =ДВССЫЛ("Товары[Товар]"). Далее отмечаем «Распространить изменения на другие ячейки с тем же условием», и нажимаем «ОК».

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

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

Имя диапазона так же, как и в умной таблице, не должно содержать пробелы и знаки препинания. Выделяем диапазон ячеек с запасом пустых строк. Например, в нашем случае, мы понимаем, что в списке больше 25 значений содержаться не будет. Переходим во вкладку «Формулы», раздел «Определенные имена», меню «Диспетчер имен», нажимаем «Создать».

Называем будущий список, при необходимости корректируем диапазон значений.

Возвращаемся на лист «Проект», выделяем ячейку, в которой должен быть выпадающий список, переходим в меню «Проверка данных» и в поле Источник ссылаемся на созданный диапазон, нажимаем «ОК».

Общие рекомендации

• Присваивайте источникам с данными имена. Это позволит пополнять списки новыми данными без изменения формулы в проверке данных.
• Если в вашем отчете содержится несколько списков, выносите источники на отдельный лист. Это практично и не загромождает главную страницу отчета лишними данными.
• Сортируйте список по удобному для вас параметру — в больших массивах это позволит быстро найти нужную позицию.
• Не дублируйте в источниках значения выпадающего списка, иначе у вас будет несколько одинаковых значений.
• По умолчанию в выпадающем списке отображается всего 8 первых значений. Если нужно отобразить больше или реализовать функцию быстрого поиска, используйте элементы управления VBA или ActiveX.

Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!