В Microsoft Excel можно навести полный порядок в данных не только стандартными способами: персональные формулы проверки позволяют легко задать свои уникальные правила. Я чаще всего применяю три главных формулы — они действительно спасают мои таблицы от хаоса.
Куда вводить формулы проверки данных и как настроить сообщения об ошибках
Прежде чем делиться формулами, коротко покажу, где их прописывать и как выставлять предупреждения.
Выделите ячейки, которые хотите контролировать, перейдите на вкладку «Данные» и откройте основную часть кнопки «Проверка данных». В появившемся окне на вкладке «Параметры» выберите в поле «Тип данных» пункт «Список», потом — «Выражение» или «Своя формула». Введите нужную формулу в соответствующее поле.
Когда формула готова, переходите на вкладку «Сообщение об ошибке»: выберите стиль «Стоп», придумайте заголовок и текст — они будут появляться, если кто-то попробует ввести что-то не то.
Проверка данных в Excel: что важно знать
Научитесь задавать правила, чтобы держать ввод данных под контролем.
Формула 1: Запрет дубликатов в столбце
Проверка данных легко спасает от повторов: к примеру, одинаковые табельные номера или номера счетов не смогут встретиться дважды.
Допустим, вы заносите данные в колонку Employee_ID — там должны быть только уникальные значения. Если кто-то случайно попробует ввести тот же ID, Excel мгновенно выдаст ошибку и не разрешит сохранить дубликат.
Для этого выделите нужный столбец.
Если работаете с форматированной таблицей Excel, кликните по первой ячейке столбца (без учёта заголовка) и нажмите Ctrl+Shift+Стрелка вниз. Или наведите мышкой на заголовок столбца, появится чёрная стрелка вниз — жмите, чтобы выделить всю колонку.
Дальше — снова «Проверка данных», вкладка «Параметры»: введите формулу (например, для столбца А):
Формула устроена так:
Перейдите на вкладку «Сообщение об ошибке»: стиль «Стоп», заголовок, например, Обнаружен повтор, текст — Такой ID сотрудника уже есть. Пожалуйста, введите уникальное значение.
Сохраняйте правило — и если кто-то попробует подсунуть дубликат в столбец, Excel сразу покажет предупреждение и не примет повтор.
Учтите: правило срабатывает только при новом вводе или редактировании ячеек после установки.
Формула 2: Блокировка будущих дат
Чтобы не допустить случайного ввода дат «из будущего» (например, при учёте отгрузок или событий, которые уже должны были произойти), используйте функцию TODAY: она разрешит прописывать только сегодняшние или прошедшие даты.
Например, в столбце B вы записываете даты отправки заказов — и только актуальные на текущий день или раньше.
Сначала проверьте, что у столбца выставлен нужный формат: выделяете столбец, жмите Ctrl+1, на вкладке «Число» выберите «Дата» и тип, который вам нужен — ОК.
Теперь с выделенными ячейками включите проверку данных, выберите «Своя формула» и впишите такой код (для столбца B):
Вот что делает эта формула:
Формула подхватывает сегодняшний день и не пропустит дату позднее. Но имейте в виду: ограничение сработает только при вводе или правке данных, старые записи не затрагиваются.
Затем во вкладке ошибки укажите стиль «Стоп», заголовок (например, Будущие даты запрещены) и комментарий (например, Дата должна быть сегодняшней или уже прошедшей. Проверьте свой ввод.).
Сохранили правило — попробуйте ввести будущую дату и посмотрите, как Excel защищает таблицу от таких ошибок.
Если хочется наоборот — запретить «старые» даты, просто поменяйте знак «меньше» на «больше» в формуле.
Зачем использовать формулу, а не стандартную проверку даты?
В Excel есть базовая проверка даты, но свои формулы жёстче и гибче: можно создавать сложные условия и подстраивать правила под конкретные нужды.
Проверяйте формулы Excel «на автомате» — есть простое решение!
Один щелчок — и ваши данные под надежной защитой от ошибок.
Формула 3: Жёсткий контроль формата ввода
Самая частая головная боль при очистке данных — разнобой форматов. Например, нужен столбец для 10-значных номеров: всё, что не подходит по длине или содержит буквы, должно моментально блокироваться. Для этого комбинируем формулы AND, LEN и ISNUMBER.
Допустим, в столбце B заносятся серийные номера: только из 10 цифр, никаких букв и лишних символов.
Чтобы добавить контроль, выделите столбец, выберите «Проверка данных», потом — «Своя формула» и введите нужный код для столбца B:
Данная формула требует:
Далее на вкладке ошибки выберите «Стоп», придумайте заголовок (например, Неверный формат номера) и пояснение (Допускается только число из 10 цифр, без букв и символов.)
Когда правило сохранено — попробуйте ввести, например, 9-значное число или 10-значный код с буквой: Excel не даст провести такую запись!
В этом примере показано, как ограничить только по числовому формату и количеству символов. Но с помощью индивидуальных формул можно выставить правила любой сложности — например, чтобы запись обязательно начиналась с буквы, а потом — ровно шесть цифр. Просто заранее попробуйте условия в Excel, а готовую формулу скопируйте в окно проверки данных.
Microsoft 365 Персональный
Microsoft 365 даёт вам Word, Excel и PowerPoint сразу на 5 устройствах, 1 ТБ в облаке OneDrive и ещё много всего.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru