Как проверить, соответствует ли значение ячейки списку в Excel?

Проверка данных в Excel: методы и особенности

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

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

Условие проверки

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

1. Щелкните по любой ячейке.
2. Перейдите на вкладку «Данные».
3. Нажмите на выделенный инструмент.
4. Выберите проверку данных.

1. Появится окно, в котором можно выбрать формат информации для проверки.

Рассмотрим эти форматы подробнее.

Любое значение

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

Целое число

Выбрав данный формат, вы можете изменить дополнительные параметры второго поля.

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

Если вы выбрали «более чем», вы должны указать минимально допустимый порог. Вот почему вы видите только одно поле, «Минимум», потому что «Максимум» неограничен.

Действительное

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

Список

Этот формат является наиболее интересным

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

1. Заполните несколько ячеек чем-нибудь. Неважно, что.

1. Щелкните по ячейке. Нажмите на вкладку, о которой вы знаете. Щелкните значок Управление данными. Выберите выбранный инструмент.

1. В поле «Тип данных» выберите «Список». Щелкните в колонке «Источник». Затем выберите нужный диапазон ячеек. Это гораздо удобнее, чем редактировать ссылку вручную. Нажмите кнопку «OK», чтобы продолжить.

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

Ячейки, содержащие только дату, должны использовать этот формат. Доступны те же дополнительные условия, что и для целых и вещественных чисел.

Время

То же самое относится и сюда. Только здесь указано время (не дата).

Длина текста

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

Существует довольно много вариантов. Некоторые формы и анкеты требуют такого заполнения.

Другой

В этом формате есть нечто уникальное. Это поле позволяет указать формулу для проверки соответствия информации определенному условию.

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

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

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

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

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

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

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

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

Давайте настроим проверку данных по-другому. Для списков автозаполнения доступны два варианта:

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

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

Excel для работы с финансовыми документами.

Алексей Вощак,
Партнер в Bridges Consulting

Эксель для бизнеса

Алексей Вощак,
Партнер в Bridges Consulting

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

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

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

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

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

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

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

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

Функция Если в Excel примеры с несколькими условиями

Пример 1. Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)

Функция ЕСЛИ в Excel позволяет использовать различные операторы для проверки состояния. Существует несколько операторов, которые вы можете использовать:

Ниже приведен простой пример использования функции при расчете оценок студентов. Если сумма баллов больше или равна «35», то формула возвращает “Сдал”, иначе возвращается “Не сдал”.

12.Набор горячих клавиш Excel, без которых вам не обойтись

Применение этих сочетаний клавиш в Excel ускорит работу и поможет в выполнении анализа данных, построении графиков и форматировании таблиц.
F4 — при вводе формулы, регулирует тип ссылок (относительные, фиксированные). Можно использовать для повтора последнего действия.
Shift+F2 — редактирование примечаний
Ctrl+; — ввод текущей даты (для некоторых компьютеров Ctrl+Shift+4)
Ctrl+’ — копирование значений ячейки, находящейся над текущей (для некоторых компьютеров работает комбинация Ctrl+Shift+2)
Alt+F8 — открытие редактора макросов
Alt+= — суммирование диапазона ячеек, находящихся сверху или слева от текущей ячейки
Ctrl+Shift+4 — определяет денежный формат ячейки
Ctrl+Shift+7 — установка внешней границы выделенного диапазона ячеек
Ctrl+Shift+0 — определение общего формата ячейки
Ctrl+Shift+F — комбинация открывает диалоговое окно форматирования ячеек
Ctrl+Shift+L — включение/ отключение фильтра
Ctrl+S — сохранение файла (сохраняйтесь как можно чаще, чтобы не потерять ценные данные).

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