Обвести недопустимые данные Excel

Обновлено: 02.07.2024

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

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

Начало работы

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

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

Перейдите на вкладку "Данные" и нажмите "Проверка данных". В небольшом списке вариантов выберите первый пункт «Проверка данных». Появится следующее диалоговое окно:

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

Я бы хотел, чтобы вы выбрали Десятичный формат. В диалоговом окне появится следующее. Выберите между и установите минимум 20 и максимум 50:

Теперь нажмите OK, и вы вернетесь в свою электронную таблицу. Попробуйте ввести что-нибудь — скажем, 22 или 35,5 — и вы увидите, что он этим доволен. Теперь попробуйте ввести что-то вроде 2, и вы получите сообщение об ошибке:

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

Разрешение списков в ячейках

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

Перейдите к ячейкам B1:B6 и введите синий, красный, зеленый, желтый, оранжевый и фиолетовый (или еще шесть слов, например шесть ваших любимых групп или шесть городов, которые вы хотели бы посетить).
Теперь перейдите к C1 — здесь появится наш список.
Откройте диалоговое окно "Проверка данных" и в раскрывающемся списке выберите "Список". Вы получите новую панель, назвав себя Source. Щелкните на этой панели, затем щелкните и перетащите ячейки B1:B6. Вы можете ввести это, но помните, что оно должно начинаться с =, что не совсем интуитивно понятно!
Нажмите "ОК".

Теперь вы увидите, что в вашей ячейке есть небольшой раскрывающийся треугольник. Нажмите на него, и вуаля! Ваш список появится!

Вот вопрос. Что произойдет, если вы измените один из цветов в исходных ячейках? Это автоматически обновляет список? Что произойдет, если вы выбрали Красный в ячейке C1, а затем измените значение Красный в исходной ячейке на Вишневый — обновляется ли ячейка C1 автоматически? Сыграй и узнай!

(На самом деле, это было три вопроса. Мои извинения! 🙂 )

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

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

Как лучше использовать эту функцию

Вы когда-нибудь регистрировались в веб-сервисе, где вы вводите всю свою информацию, а затем переходите к тому моменту, когда вам нужно ввести свой пароль (дважды), и он шипит и жалуется, что ваш пароль должен быть 10 символов с числом внутри и хотя бы одной заглавной буквой и бла-бла? Разве это не бесит? Почему они не могут сказать вам заранее?!

То же самое и с проверкой данных. Если вы не объясните людям, что можно и что нельзя делать в камере, они в конечном итоге будут рвать на себе волосы — и вы, как дизайнер электронной таблицы, получите все жалобы!

Итак, теперь мы собираемся обратить внимание на две другие вкладки в диалоговом окне "Проверка данных":

Сообщение или комментарий в ячейке

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

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

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

Предупреждающие сообщения

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

Информация — это вежливый кашель, чтобы привлечь ваше внимание. Если вы выберете это оповещение об ошибке, люди смогут переопределить его и ввести то, что им нравится.
Предупреждение! Это больше похоже на то, как вам грозят пальцем, — немного жестче, но все же позволяет людям игнорировать это и вводить то, что им нравится.
Стоп – это единственная функция, которая не позволяет вам делать что-либо, выходящее за рамки ваших ограничений. Вы не можете ничего вводить в эту ячейку, пока она не будет соответствовать правилам.

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

Сравните последнее с предупреждением, которое вы получаете по умолчанию:

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

Текст, введенный в панель заголовка предупреждения об ошибке, отображается на полосе в верхней части сообщения об ошибке. Выше по умолчанию используется Microsoft Excel , тогда как выше я установил Attention . Честно говоря, мало кто на самом деле замечает это, так что вам решать, заполнять ли этот фрагмент.

Обход неверных данных

Вместо того, чтобы установить проверку данных, а затем ввести данные, у вас могут быть данные, а затем установить проверку данных. Когда вы ретроспективно применяете проверку, стоит запустить быструю проверку, чтобы убедиться, что она ведет себя правильно. Вы можете сделать это через Circle Invalid Data. Нажмите «Проверка данных», и это второй вариант:

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

На данный момент у вас есть выбор:

Измените настройки проверки, чтобы включить эти параметры.
Измените данные в своих ячейках.
Нажмите «Проверка данных» > «Очистить круги проверки» (для меня это эквивалент Excel, когда вы засовываете пальцы в уши и кричите «ля-ля-ля, я вас не слышу», поскольку это действительно бессмысленно — зачем вам ограничения, если вы’ ты просто собираешься игнорировать их?)

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

Распространение проверки на другие ячейки

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

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

Вот оно! Работа выполнена!

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

(ага!)

Реальные проблемы с проверкой данных

Пользовательское тестирование (и предупреждение о списках)

Что бы вы ни делали, стоит попросить кого-нибудь поэкспериментировать с таблицей и ограничениями. Поощряйте их пытаться «сломать» его! Спросите их, что их расстраивает и легко ли было понять сообщение в ячейке и предупреждение об ошибке. Могут ли они ввести в ячейку что-то еще, чего вы не ожидали?

Существует распространенная проблема со списками , когда пользователи видят исходные ячейки и думают: "О, что это такое? Это немного случайно. Я приберусь и удалю их». Очевидно, что это может вызвать сильную головную боль! Предотвратите это, убедившись, что ваш список находится на другом рабочем листе, а затем скройте весь рабочий лист. Вы можете узнать, как это сделать здесь.

Разочарование 1: обведите неверные данные

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

Но иногда данные меняются, а это значит, что если у вас отображается Обвести неверные данные, может произойти одно из двух:

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

Как говорится, предупрежден — значит вооружен, так что, по крайней мере, теперь вы знаете об этих раздражениях!

Разочарование 2: проверка данных применяется к ячейкам с формулами

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

Другими словами, предположим, что у вас есть несколько ячеек с функцией ВПР, а затем примените к этим ячейкам проверку данных. Затем вы меняете что-то, что влияет на результат вашей ВПР. Фактически никаких ограничений проверки не возникает, и вы обнаружите, что что-то выходит за пределы диапазона, только когда вы запустите Обвести неверные данные. Самый раздражающий! Более быстрый способ привлечь внимание к этим неверным цифрам или результатам формул — использовать условное форматирование.

Попрактикуйтесь

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

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

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

Вот подробные шаги:

1.Выберите ячейки.

2.Перейдите на вкладку Данные.

3.Нажмите Проверка данных – Проверка данных…

4.Нажмите стрелку вниз рядом с Любое значение. В списке есть несколько предустановленных критериев, которые вы можете выбрать напрямую.

6.Теперь проверка установлена.Следующий шаг – снова нажмите Проверка данных и выберите Обвести неверные данные.

7. Вы можете видеть, что все данные меньше 70 и больше 89 обведены.

<р>8. Если вам больше не нужны эти красные круги, просто нажмите Проверка данных и нажмите Очистить данные проверки.

Подходящее чтение

Вам также может понравиться

Заявление об авторских правах. Любое копирование или использование любых публикаций на этом веб-сайте должно осуществляться с письменного разрешения или авторизации Myofficetricks.

Вот как использовать параметр «Обвести неверные данные» с проверкой данных в Excel:

Вы можете скачать файл здесь и следовать инструкциям. Если у вас есть предварительный просмотр, найдите стрелку загрузки в правом верхнем углу.

)

У меня есть диапазон данных, которым я хочу ограничить ввод данных. Я буду использовать проверку данных, чтобы она просто предоставила пользователю информационное окно, если он введет целое число от 5 до 20. Для этого я выделю диапазон, выберу проверку данных в группе данных на ленте и введите следующее:

И здесь я установлю его только для информации:

Итак, если я введу число от 5 до 20, мне будет выдано предупреждение, но ввод числа все же будет разрешен:

Теперь я собираюсь использовать СЛУЧМЕЖДУ для ввода большого количества чисел в этот диапазон данных, некоторые из которых будут недействительными. Excel позволит мне это сделать, но вопрос в том, как мне найти недопустимые числа:

Перейдите к параметру "Проверка данных" на вкладке "Данные" ленты и выберите "Обвести неверные данные":

Excel выделит все недопустимые значения в соответствии с установленным правилом проверки данных:

Тем не менее, одно предостережение. Если вы используете изменчивую функцию, такую как СЛУЧМЕЖДУ (которая пересчитывает каждый раз, когда в лист вносятся изменения), обратите внимание, как круги остаются на одном и том же месте, даже если значения изменились:

Убедитесь, что каждый раз при пересчете листа вам нужно снова запускать параметр "Обвести неверные данные", чтобы сбросить круги:

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

Как найти последнее совпадение в массиве с помощью ПРОСМОТРА в Excel

18 ноября 2015 г.

Что делает флажок «Игнорировать пробелы» при проверке данных в Excel

При работе в Excel у вас могут быть недопустимые данные.Давайте узнаем, как обвести недопустимые данные в Microsoft Excel.

При работе в Excel у вас могут быть недопустимые данные. Другими словами, он выходит за рамки критериев, которые вы указали в функции проверки данных Excel. Часто бывает полезно визуально идентифицировать неверные данные. К счастью, Excel предлагает простой способ сделать именно это. Давайте узнаем, как обвести недопустимые данные в Microsoft Excel.

Как обвести неверные данные в Excel

Представьте, что у вас есть столбец данных в Excel со значениями от 10 до 50. Вы применили к данным правила проверки данных. В этом наборе действительные данные существуют в диапазоне от 35 до 55. Другими словами, значения 10, 20 и 30 являются недопустимыми данными. Было бы полезно визуально показать эти недопустимые значения данных с помощью четкого визуального индикатора.

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

Чтобы обвести недопустимые данные в Excel, сначала перейдите на вкладку "Данные". Вы найдете это на ленте Excel, в серии меню и вкладок в верхней части экрана. В правой части вкладки "Данные" вы увидите раскрывающееся меню "Проверка данных".

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

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

Как видите, в Microsoft Excel легко обвести недопустимые данные. Это идеальный способ добавить визуальные эффекты в правила проверки данных.

Читайте также: