Раскрывающиеся списки Excel и проверка данных, а также вставка, которая их удаляет

1. Создайте раскрывающийся список

Четыре шага, и люди ошибаются на первом.

1. Поместите разрешенные значения где-нибудь в реальном месте. Столбец на отдельном листе, по одному значению в строке, с заголовком. Не вводится в диалог.
2. Выделите ячейки, которые необходимо контролировать. Ячейки данных, а не заголовок.
3. Данные, Проверка данных, Разрешить: Список.
4. Нажмите «Источник», затем выберите список значений. Оставлять Раскрывающийся список внутри ячейки поставил галочку. ХОРОШО.

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

2. Где должен жить список и таблица-ловушка

Перед объяснением: ваши варианты хранятся в соответствующей таблице Excel под названием Products. Скажите, можете ли вы указать Источник на =Products[Product].

Вы не можете. Я попробовал это в Excel, и источник был полностью отклонен. Та же ссылка, завернутая в INDIRECT было принято:

Source:  =Products[Product]                    rejected
Source:  =INDIRECT("Products[Product]")        accepted

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

Есть еще два маршрута в то же место. Вы можете определить имя для столбца таблицы: «Формулы», «Определить имя», установить значение =Products[Product], а затем используйте =ThatName как Источник, который некоторым людям легче читать. Или, если вы не используете таблицу, определите имя в обычном диапазоне и помните, что фиксированный диапазон не увеличивается, поэтому новые параметры не появятся, пока кто-нибудь не отредактирует имя.

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

3. Три стиля оповещений и два, которые не являются правилами

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

Предупреждения и Информация заслуживают четкого названия: они не являются правилами. Это уведомления. Если для вашего столбца установлено значение «Предупреждение» и вы полагаетесь на него, чтобы сохранить данные в чистоте, у вас есть примечание, в котором вы думаете, что у вас есть ворота, и разница проявляется только тогда, когда вы позже подсчитываете отдельные значения.

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

4. Какая валидация не останавливается

Перед объяснением: в столбце есть раскрывающийся список в стиле Останавливаться. Кто-то копирует ячейку из электронного письма и вставляет ее. Скажите, что происходит.

Значение приземляется, сообщение не появляется, и правило проверки исчезло из этой ячейки. Не обойден ни на одну запись; ушел. Я создал три проверенные ячейки, вставил их в две разными способами, а затем спросил Excel, в каких ячейках все еще сохраняется правило:

before                                all three validated
after an ordinary paste (Ctrl+V)      that cell no longer has a rule
after Paste Special, Values only      that cell still has its rule
cells still carrying a rule           2 of 3

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

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

Три практических следствия.

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

Попросите людей вставить значения. Ctrl+Альт+В, затем V. В общей книге это стоит писать на самом листе, рядом с областью ввода, потому что все вставляют, и никто не читает инструкции в другом месте.

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

5. Зависимые раскрывающиеся списки

Общий запрос: выберите категорию в одной ячейке, а вторая ячейка предлагает только товары из этой категории.

Механизм представляет собой одну функцию. Создайте именованный диапазон для каждой группы, названный точно так же, как значение, которое его выбирает, затем укажите второй раскрывающийся список на INDIRECT первой ячейки.

Named range  Furniture   ->  Desk, Chair
Named range  Lighting    ->  Lamp

A2 validation:  List, Source =Categories
B2 validation:  List, Source =INDIRECT($A2)

INDIRECT превращает текст в ссылку, поэтому, когда A2 содержит «Мебель», источником второго раскрывающегося списка становится диапазон с именем «Мебель». Это читается как по волшебству, и это просто поиск имени.

Две вещи, которые нужно знать, прежде чем создавать его. Имена не могут содержать пробелы, поэтому название категории «Офисная мебель» должно быть Office_Furniture, и формула принимает вид =INDIRECT(SUBSTITUTE($A2," ","_")). И ничто не очищает B2 при изменении A2, поэтому строка может содержать категорию и продукт из другой категории. Если это имеет значение, добавьте условный формат, который отмечает несоответствие, используя тот же трюк с заблокированными столбцами, что и выделение всей строки.

6. Пользовательские правила: формула вместо списка

Разрешить: Пользовательский принимает формулу, которая должна возвращать TRUE, чтобы запись была принята. Оно записывается для верхней левой ячейки выделенного фрагмента и копируется вниз, точно так же, как правило условного форматирования, поэтому применяется та же логика блокировки.

Четверо, которые зарабатывают себе на жизнь.

=COUNTIF($A$2:$A$500, A2)=1        no duplicates in this column
=AND(A2>=TODAY(), A2<=TODAY()+90)  a date within the next quarter
=ISNUMBER(A2)                       a number, not text that looks like one
=LEN(A2)=8                          exactly eight characters, for a code

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

Одно ограничение, которое следует учитывать: пользовательское правило проверяется при редактировании ячейки, поэтому значение, которое позже становится недействительным из-за изменения другой ячейки, не проверяется повторно и не помечается. Валидация — это ворота, а не монитор.

7. Все равно найти то, что попало

Учитывая все вышеизложенное, проверенный столбец по-прежнему будет содержать значения, нарушающие его правило: записи, сделанные до существования правила, вставленные значения и все, что поступило в результате импорта.

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

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

Вот почему проверка ремней и подтяжек — это формула, а не кнопка. Один столбец, одна формула, а ответ — это число, которое можно записать на листе:

=SUMPRODUCT(--(COUNTIF(AllowedList, A2:A500)=0))    entries not in the list
=COUNTA(UNIQUE(A2:A500))                            distinct values, expected 4

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

8. Два флажка, которые удивляют людей

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

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

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

Пограничные случаи, которые незаметно нарушают проверку

Шесть, прошедшие первую сборку.

Список источников имеет конечные пробелы. «Север» в списке означает, что раскрывающийся список предлагает значение, которое никогда не будет соответствовать чему-либо еще в вашей книге. Очистите столбец списка, прежде чем указывать на него что-либо; процедуры TRIM и LEN применяется как к спискам опций, так и к данным.

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

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

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

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

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