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) данных:
Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.
В некоторых случаях вы не разрешаете оставлять пустые ячейки в столбце, что означает, что необходимо ввести что-то в ячейки столбца. В этой статье я расскажу о функции проверки данных для выполнения этой работы в Excel.
Предотвращение дублирования данных в столбце с помощью Prevent Duplicate
Предотвращение оставления пустых ячеек в столбце с помощью проверки данных
Чтобы в столбце не оставались пустые ячейки, вам просто нужно использовать простую формулу в функции проверки данных.
1. Выберите столбец, в котором не должны оставаться пустые ячейки, щелкните Данные > проверка достоверности данных.
2. Под Настройки вкладка, выберите На заказ из Разрешить раскрывающийся список, затем введите эту формулу >=COUNTIF($F$1:$F1,"")=0 в Формула текстовое окно.
F1 - первая ячейка в выбранном столбце.
3. Нажмите OK. С этого момента, если вы оставите пустую ячейку и перейдете к следующей ячейке для ввода значения, появится предупреждающее окно, чтобы остановить вас.
Предотвращение дублирования данных в столбце с помощью Prevent Duplicate
Если вы хотите предотвратить дублирование ввода данных в столбец, вы можете попробовать Kutools for Excel Автора Prevent Duplicate утилита.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
Выберите столбец, в котором вы хотите предотвратить дублирование записи, нажмите Kutools > Prevent Typing > Prevent Duplicate .
Затем нажмите Да > OK чтобы закрыть напоминания.
С этого момента будет появляться предупреждение, чтобы прекратить повторный ввод.
Как игнорировать пустые или нулевые ячейки при условном форматировании в Excel?
Предположим, у вас есть список данных с нулевыми или пустыми ячейками, и вы хотите условно отформатировать этот список данных, но игнорировать пустые или нулевые ячейки, что бы вы сделали? В этой статье мы покажем вам, как использовать условное форматирование с игнорированием пустых или нулевых ячеек в Excel.
You may interest in:
Click Kutools > Insert > Fill Blank Cells. This Kutools for Excel's Fill Blank Cells utility can quickly fill all blank cells with value above or with a fixed value in selected range. See below screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. 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
После создания правил условного форматирования для списка данных вам необходимо добавить новое правило, чтобы игнорировать пустые ячейки в списке.
1. Продолжайте оставаться в Диспетчер правил условного форматирования диалоговое окно, затем щелкните Новое правило кнопка. Смотрите скриншот:
Внимание: Вы можете открыть Диспетчер правил условного форматирования диалоговое окно, нажав Условное форматирование > Управление правилами под Главная меню.
2. Затем он попадает в Новое правило форматирования диалоговое окно. Чтобы игнорировать пустые ячейки при условном форматировании, вы можете использовать два следующих метода.
Метод 1
- а. Выбрать Форматировать только ячейки, содержащие в Выберите тип правила коробка;
- б. Выбрать Пробелы в Форматировать только ячейки с раскрывающийся список;
- c. Не выбирайте какой-либо формат и нажмите OK кнопка. Смотрите скриншот:
Метод 2
- а. в Выберите тип правила выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать.;
- б. Скопируйте и вставьте формулу = ISBLANK (A2) = ИСТИНА в Формат значений, где эта формула истинна коробка;
- Внимание: здесь A2 в формуле - это первая ячейка выбранного диапазона. Например, ваш выбранный диапазон - B3: E12, вам нужно изменить A2 на B3 в формуле.
- c. Щелкните значок OK кнопку без указания формата.
3. Затем он возвращается в Диспетчер правил условного форматирования диалоговое окно. Независимо от того, какой метод вы используете для игнорирования пробелов, вам необходимо проверить Остановить, если истина в этом диалоговом окне, а затем щелкните значок OK кнопка. Смотрите скриншот:
Затем выбранные ячейки форматируются, за исключением пробелов.
Игнорировать нулевые ячейки при условном форматировании в Excel
Если у вас есть список данных в диапазоне B2: B12, и вы хотите отформатировать пять наименьших значений среди них, но игнорировать нулевые ячейки, сделайте следующее.
1. Выберите диапазон B2: B12, затем щелкните Условное форматирование > Новое правило под Главная меню.
2. в Изменить правило форматирования диалоговое окно, вам необходимо:
- 1). в Выберите тип правила выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать.;
- 2). Скопируйте и вставьте формулу =AND(B2<>0,B2<=SMALL(IF(B$2:B$12<>0,$B$2:$B$12),5)) в Формат значений, где эта формула истинна коробка;
- 3). Щелкните значок Формат кнопка для указания формата ячеек;
- 4). После указания формата щелкните значок OK кнопка. Смотрите скриншот:
Внимание: Вам необходимо изменить диапазон ячеек в формуле в соответствии с вашими потребностями.
После этого вы можете увидеть, что пять наименьших значений в выбранном списке форматируются немедленно, без форматирования нулевых значений.
При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов организации и позволить пользователю лишь выбирать значения из этого списка. Этот подход поможет ускорить процесс ввода и уменьшить количество опечаток.
Выпадающий список можно создать с помощью Проверки данных
В этой статье создадим Выпадающий список с помощью Проверки данных ( Данные/ Работа с данными/ Проверка данных ) с типом данных Список .
Выпадающий список можно сформировать по разному.
А. Простейший выпадающий список - ввод элементов списка непосредственно в поле Источник
Самым простым способом создания Выпадающего списка является ввод элементов списка непосредственно в поле Источник инструмента Проверка данных .
Предположим, в ячейке B 1 необходимо создать выпадающий список для ввода единиц измерений. Выделим ячейку B 1 и вызовем Проверку данных.
Если в поле Источник указать через точку с запятой единицы измерения шт;кг;кв.м;куб.м , то выбор будет ограничен этими четырьмя значениями.
Теперь смотрим, что получилось. Выделим ячейку B 1 . При выделении ячейки справа от ячейки появляется квадратная кнопка со стрелкой для выбора элементов из выпадающего списка .
Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B 1 ); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков. Преимущество : быстрота создания списка.
Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)
Элементы для выпадающего списка можно разместить в диапазоне на листе EXCEL, а затем в поле Источник инструмента Проверки данных указать ссылку на этот диапазон.
Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4
Преимущество : наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков. Недостатки : если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A 1: A 100 . Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.
Второй недостаток: диапазон источника должен располагаться на том же листе, что и выпадающий список , т.к. для правил Проверки данных нельзя использовать ссылки на другие листы или книги (это справедливо для EXCEL 2007 и более ранних).
Избавимся сначала от второго недостатка – разместим перечень элементов выпадающего списка на другом листе.
B. Ввод элементов списка в диапазон (на любом листе)
В правилах Проверки данных (также как и Условного форматирования ) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):
Пусть ячейки, которые должны содержать Выпадающий список , размещены на листе Пример,
а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).
Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона , другой – функции ДВССЫЛ() .
Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A 1: A 4 на листе Список ) . Для этого:
- выделяем А1:А4 ,
- нажимаем Формулы/ Определенные имена/ Присвоить имя
- в поле Имя вводим Список_элементов , в поле Область выбираем Книга ;
Теперь на листе Пример , выделим диапазон ячеек, которые будут содержать Выпадающий список .
- вызываем Проверку данных ;
- в поле Источник вводим ссылку на созданное имя: =Список_элементов .
Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10 . Однако, в этом случае Выпадающий список может содержать пустые строки.
Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон . Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))
Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек ( A:A ), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон ).
Используем функцию ДВССЫЛ()
Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример , выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных , в Источнике указываем =ДВССЫЛ("список!A1:A4") .
Недостаток : при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа .
Ввод элементов списка в диапазон ячеек, находящегося в другой книге
Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:
- в книге Источник.xlsx создайте необходимый перечень элементов;
- в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя , например СписокВнеш;
- откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
- выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ("[Источник.xlsx]лист1!СписокВнеш") ;
При работе с перечнем элементов, расположенным в другой книге, файл Источник . xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки .
Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на = ДВССЫЛ("[Источник.xlsx]лист1!$A$1:$A$4")
СОВЕТ: Если на листе много ячеек с правилами Проверки данных , то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.
Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.
В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).
Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список , содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список .
Читайте также: