Проверке данных в Excel я посвятил отдельное короткое видео, но не стоит недооценивать эту, казалось бы простую функцию. С помощью проверки данных можно решить множество разнообразных задач. Например, можно запретить ввод данных в ячейку, если в предыдущей ячейке строки находится определенное значение.
Рассмотрим простейший пример.
Есть таблица успеваемости. В первом столбце находится данные студента, во втором информаци о сданном зачете. Здесь может быть только одно из двух значений - "зачет" или "незачет" и поэтому для удобства ввода информации создадим выпадающий список с помощью того же инструмента проверки данных. Сами значения пропишем вручную в соответствующем поле.
Ну а в третий столбец будем вводить дату пересдачи. Соответственно, ячейки должны быть доступны для ввода информации только в том случае, если в предыдущей ячейке строки находится значение «незачет».
Для всего столбца (1) зададим соответствующую проверку данных (2) и также вручную пропишем необходимое условие (4).
Снимем галочку (5), позволяющую игнорировать пустые ячейки, чтобы условие распространялось и на них, а также пропишем сообщение об ошибке на соседней вкладке (6).
Теперь при выборе значения «зачет» ячейки столбца будут недоступны для ввода данных, а при выборе «незачет» можно будет ввести дату пересдачи.
Фактически мы использовали простейшую формулу для проверки значений ячейки. Ничего нам не мешает сделать проверку любой сложности и при этом использовать встроенные функции Excel.
Допустим, возникает необходимость контролировать ввод данных в определенном диапазоне и проверять их на дубликаты. То есть необходимо, чтобы столбец содержал только уникальные значения. Для этого можно приспособить функцию СЧËТЕСЛИ, которая подсчитывает количество определенных значений в заданном диапазоне. Чтобы было понятна логика проверки, давайте сначала создадим формулу на листе.
Допустим у нас уже есть столбец со значениями и необходимо посчитать, сколько раз каждое значение фигурирует в диапазоне.
Для этого создадим простейшую формулу с функцией СЧËТЕСЛИ. Выбираем весь диапазон, в котором будем искать дубликаты и фиксируем его с помощью клавиши F4, то есть делаем ссылки на диапазон абсолютными. Затем нам нужно указать ячейку, значение которой будет проверяться на наличие дубликатов. Указываем соответствующую ячейку этой же строки.
Ну а теперь просто раскопиуруем формулу по всему диапазону и в результате число будет указывать на то, сколько раз в диапазоне встречается то или иное значение.
Ну а теперь вернемся к нашей задаче. Фактически мы можем воспользоваться этой же формулой, но сделаем ее частью условия.
Если вычисленное значение равно единице, то это будет означать, что дубликатов в диапазоне нет и значение можно оставить. Иначе нам нужно будет вывести сообщение об ошибке, например - «Такое значение в столбце уже есть. Можно ввести только уникальное значение»
Проверяем таблицу и убеждаемся, что при вводе дубликата появится предупреждение.
Таким образом для создания условия проверки можно использовать формулу с любыми необходимыми вам условиями.
Ну и напоследок небольшая хитрость, которая позволит быстро найти в уже заполненном диапазоне ячейки со значениями, не удовлетворяющими условию.
Выделяем диапазон со значениями и точно также как и раньше задаем необходимую проверку, например, определим границы диапазона, в которых должны находиться значения.
Ну а теперь раскрываем меню Проверки данных и выбираем пункт, который позволит обвести красной линией данные, которые не удовлетворяют заданным условиям.
Эти значения можно изменить на подходящие. Ну и удалить линию можно через тоже самое меню.
Ссылки на мои ресурсы по Excel
★ Телеграм