Excel выпадающий список убрать пустые строки
Как создать раскрывающийся список, игнорирующий пустые ячейки в Excel?
Допустим, у вас есть список значений с несколькими заполненными пустыми ячейками, когда вы создаете раскрывающийся список проверки данных с этим списком данных, вы обнаружите, что пустые ячейки добавляются в раскрывающийся список, даже если вы установили флажок Игнорировать Пустой вариант при его создании. На самом деле прямого метода создания раскрывающегося списка без пустых ячеек не существует. В этом руководстве мы покажем вам трюк с разделением значений и пустых ячеек на основе исходных данных и, наконец, создадим раскрывающийся список для извлечения данных.
Easily fill all blank cells with certain value in Excel
Click Kutools > Insert > Fill Blank Cells. Kutools for Excel's Fill Balnk Cells utility helps you not only fill all blank cells with certain value, but fill balnk cells based on the above value as you need. See screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More Download the free trial now
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Создать раскрывающийся список игнорировать пустые ячейки в Excel
Например, у вас есть данные ниже в диапазоне B2: B13, чтобы создать раскрывающийся список без пустых ячеек, сначала вы можете скопировать и вставить данные в новый столбец без пробелов. Затем создайте раскрывающийся список на основе этого нового списка значений.
1. Примените следующую формулу, чтобы скопировать и вставить только непустые значения ячеек, введите эту формулу: =LOOKUP("zzzzz",CHOOSE(,"",INDEX(B:B,SMALL(IF($B$1:$B$13<>"",ROW($B$1:$B$13)),ROWS($D$1:D1))))) в пустую ячейку D1, например, а затем нажмите Ctrl + Shift + Enter вместе, чтобы получить следующий результат:
Внимание: В приведенной выше формуле B1: B13 - это список данных, который вы хотите использовать. Вы можете изменить ссылку на ячейку по своему усмотрению.
2. Затем выберите ячейку D1 и перетащите маркер заполнения вниз в диапазон, который вы хотите содержать эту формулу, и все непустые значения ячеек будут извлечены. Смотрите скриншот:
3. Теперь создайте выпадающий список проверки данных с этим новым списком данных. Выберите ячейки, в раскрывающемся списке которых вы хотите разместить, затем нажмите Данные > проверка достоверности данных.
Имеем в качестве примера недельный график дежурств, который надо заполнить именами сотрудников, причем для каждого сотрудника максимальное количество рабочих дней (смен) ограничено. Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных:
Чтобы реализовать подобный вариант выпадающего списка выполним несколько простых шагов.
Шаг 1. Кто сколько работает?
Сначала давайте подсчитаем кто из наших сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к зеленой таблице еще один столбец, введем в него следующую формулу:
=СЧЁТЕСЛИ($B$2:$B$8;E2) или в англоязычной версии =COUNTIF($B$2:$B$8;E2)
Фактически, формула просто вычисляет сколько раз имя сотрудника встречалось в диапазоне с именами.
Шаг 2. Кто еще свободен?
Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников:
Шаг 3. Формируем список
Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи - на следующем шаге - с выпадающим списком. Для этого добавим еще один столбец и введем в него такую страшноватую на первый взгляд формулу:
При всей внешней жуткости вида, эта формула делает одну простую вещь - выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.
Шаг 4. Создаем именованный диапазон свободных сотрудников
- в Excel 2003 и старше идем в меню Вставка - Имя - Присвоить (Insert - Name - Define)
- в Excel 2007 и новее - жмем кнопку Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas)
и создаем новый именованный диапазон Имена по следующей формуле:
в англоязычной версии =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10))
Фактически, мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена.
Шаг 5. Создаем выпадающий список в ячейках
Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого
- в Excel 2003 и старше - откроем меню Данные - Проверка (Data - Validation),
- в Excel 2007 и новее - жмем кнопку Проверка данных (Data Validation) на вкладке Данные (Data)
В открывшемся окне выберем в списке допустимых значений вариант Список (List) и укажем Источник (Source) данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
Дурацкий вопрос, но в интернете не нашел, может подскажет кто нибудь. таблица умная с колонками в которых настройки выпадающих списков. как можно списки сделать только с содержимым. без пустых строк. без макросов. классический ексель. спасибо друзья
Скопировать выделенный диапазон без пустых строк
Нужно в Exel 2010 копировать данные в буфер обмена без пустых ячеек. Для Select. не могу найти.
Объединить данные в 1 столбец без повторений и пустых строк
Как можно объединить данные в 1 столбец без повторений и пустых строк? С помощью кода.
Дублировать список без пустых строк
программа, которая дублирует список без пустых строк можете скинуть код или ссылку на такую же.
Подсчитать количество пустых строк в текстовом файле. Сформировать новый файл, в котором пустых строк нет
Задано произвольный текстовый файл. Подсчитать количество пустых строк в этом файле. Сформировать.
Hugo121, я имею ввиду если раскрвывающийся список сделать к примеру с содердимым второго столбца этой таблицы. и чтобы он был без пустых строк
раскрывающийся список в ячейке вне таблицы
Добавлено через 6 минут
Кстати как Вы картинку прямо в пост ставите? Никак не пойму.
Hugo121, а телега это что? не телеграмм же. а как Вы так сделали. я так не выходит у меня: =СМЕЩ(E12;0;0;СЧЁТЗ(E12:E100);1)
Да, телеграмм. Там и из буфера картинку подхватывает, и файл просто мышью кинуть можно, и ссылка сразу видна.
А список вот в файле, сделано именно как описал.
Hugo121, я по работе в ватсапе переписываюсь. так же работает видимо. тоже в буфер скопировал, в ватсап вставил..удобно. формула работает. буду дальше думать. Спасибо
Выпадающий список без повторов.
Всем, привет. Помогите советом. На форме есть DBLookupComboBoxEh и ListBox. Вот у меня 2.
Выпадающий список без submit
Снова всем привет. И снова мои ламерские вопросы. Есть выпадающий список, что к нему.
Как сделать чтобы файл создавался без пустых строк?
FileStream Create создаёт текстовый файл с двумя пустыми строчками. Как сделать чтобы файл.
Выпадающий список Select без кнопки submit
Как сделать выпадающий список Select, чтобы при изменений опций, страница сразу перезагружалась без.
Выпадающий список с обновлением страницы без перезагрузки
Народ help me , помогите сделать выпадающий список на одной странице , есть у меня 21 район при.
PHP выпадающий список без HTML - возможно?
Есть таблица в SQL - "Item" - в ней поле "name" - можно ли в php 'name' вывести в выпадающий.
Если список значений содержит пропуски (пустые ячейки), то это может существенно затруднить его дальнейший анализ. С помощью формул уберем пустые ячейки из колонки с данными. Также напишем формулу, чтобы удалить нули из списка значений. В конце статьи научимся удалять вообще любое заданное значение из списка: символ, число, текстовую строку.
Пусть имеется список с пустыми ячейками (столбец А ).
Задача
Убрать пустые ячейки из списка, сформировав формулами список в соседнем столбце. То есть под словом "убрать" будем понимать не удаление значения из исходного списка, а формирование еще одного списка, но уже без лишних символов. Чтобы действительно убрать значения из списка нужно использовать макросы - программу на 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 ячейки пустые?
Например, мы копируем данные с веб-страницы, и нам нужно удалить недопустимые данные на основе пустых ячеек в определенном столбце. Обычно мы можем выбрать строку, а затем удалить ее вручную, но это займет много времени, если будет слишком много пустых ячеек. Здесь я расскажу о нескольких способах простого удаления строк, если ячейки в длинном списке в Excel пусты.
Kutools для Excel Выберите определенные ячейки Утилита предоставляет пользователям Excel простой выбор для выбора всей строки или всего столбца, если значения ячеек соответствуют определенному значению в Excel. Работать проще и понятнее! Полнофункциональная бесплатная 30-дневная пробная версия!
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Удалите строки, если ячейки пусты в Excel с помощью специальной команды Перейти
Если ваши данные указаны только в одном столбце в Excel, вы можете удалить строки, если ячейки в конкретном списке / столбце пустые, с помощью команды «Перейти к специальному», выполнив следующие действия:
1. Выберите столбец, в котором, если ячейки пусты, вы удалите строки этих пустых ячеек, и нажмите кнопку Главная > Найти и выбратьт> Перейти к специальному.
2. В диалоговом окне «Перейти к специальному» установите флажок Пробелы и нажмите OK кнопка. Смотрите скриншот выше:
Затем сразу выбираются все пустые ячейки в выбранном столбце.
3. Щелкните эти выбранные пустые ячейки правой кнопкой мыши и выберите Удалить из контекстного меню.
Шаг 4: В открывшемся диалоговом окне «Удалить» установите флажок Весь ряд и нажмите OK кнопку.
Теперь удаляются целые строки всех пустых ячеек в выбранном столбце.
Удалите строки, если ячейки пусты в Excel с помощью макроса VBA
В этом разделе мы предоставим вам макрос VBA для удаления строк, если ячейки в Excel пусты.
1. Нажмите другой + F11 одновременно клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Вставьте модуль, щелкнув Вставить > Модулии вставьте следующий макрос VBA в модуль.
VBA: удалить всю строку, если ячейки в столбце пусты
3. нажмите F5 ключ или щелкните Run кнопка для запуска этого макроса VBA
4. В открывшемся диалоговом окне KutoolsforExcel выберите и укажите столбец, в котором вы хотите удалить целые строки, если ячейки пусты, и нажмите кнопку OK кнопку.
Теперь, только если ячейки в определенном столбце пусты, строки, в которых находятся эти пустые ячейки, будут удалены сразу.
Удалять строки, если ячейки содержат определенное содержимое в длинном списке / столбце
Иногда вам может потребоваться удалить целые строки, если ячейки в указанном столбце содержат определенное содержимое. В этом случае вы можете применить Kutools for Excel Выбрать конкретные ячейки, чтобы легко решить эту проблему в Excel.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите столбец, в котором, если ячейки содержат указанное содержимое, вы удалите соответствующие строки, и щелкните Кутулс > Выберите > Выбрать определенные ячейки.
2. В открывшемся диалоговом окне Select Specific Cells см. Снимок экрана выше:
(1) Проверьте Целые строки вариант в Тип выбора раздел;
(2) Выберите Комплект из первого раскрывающегося списка в Конкретный тип раздел;
(3) Введите определенный контент в следующее поле.
(4) Щелкните значок Ok кнопку.
3. Затем появится диалоговое окно, в котором показано, сколько строк было выбрано. Щелкните значок OK чтобы закрыть его.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
4. Щелкните правой кнопкой мыши одну из выбранных строк и выберите Удалить из контекстного меню.
И тогда все выбранные строки удаляются сразу. Смотрите скриншот:
Демонстрация: удаление строк, если ячейки содержат определенное значение / данные в длинном списке / столбце
Читайте также:
- В текстовом редакторе word текст набранный до нажатия клавиши enter
- Toca race driver 3 как настроить графику
- Упражнения для работы над голосом использование компьютерной программы видимая речь
- Ipad 2 как установить старые версии программ
- Способ программирования при котором вся программа разбивается на группу компонентов