ПРОВЕРКА ДАННЫХ
Проверку вводимых и введенных данных в Таблицах можно осуществлять двумя способами:
1) c помощью функций (для уже введенных в ячейки значений);
2) с помощью инструмента «Проверка данных» (на этапе ввода данных).
ФОРМУЛЫ ПРОВЕРКИ ДАННЫХ
Рассмотрим все формулы проверки данных на одном скриншоте. В столбцах слева направо: ячейка с введенными данными; результат работы формулы; название формулы; описание формулы.
Все формулы проверки данных возвращают логическое значение, то есть TRUE или FALSE, и ничего более.
Ведь значение в ячейке может или быть числом, или не быть.
Или быть текстом, или не быть.
Или быть ссылкой, или не быть.
Ну, вы поняли :) Все проверки подразумевают бинарный ответ.
Эти формулы полезны не сами по себе, а как вложения в формулу ЕСЛИ (IF) — они «отлавливают» ошибки и в зависимости от их наличия выдают в ячейке те или иные значения.
Давайте рассмотрим пару примеров (в столбце С формулы записаны одной строкой, но с автопереносом, чтобы уменьшить размер скриншота):
Небольшое пояснение. Мы начинаем с формулы ЕСЛИ (IF), ее синтаксис:
ЕСЛИ(выражение; аргумент, если выражение ИСТИННО; и аргумент, если оно ложно)
Итак, берем ЕСЛИ и вкладываем внутрь нашу формулу проверки данных, например ЕПУСТО, далее первый текст для случаев, когда ячейка пуста (ЕПУСТО возвращает ИСТИНУ), и другой для случаев, когда ячейка не пуста.
Формула в примере вернет «Да, ячейка пуста, сударь». Обратите внимание: можно выделить вложенную формулу, и тогда мы увидим ее значение (иногда для этого потребуется нажать на знак ? около формулы).
ПРОСТОЙ КЕЙС С ЕПУСТО (ISBLANK) И УСЛОВНЫМ ФОРМАТИРОВАНИЕМ
Иногда бывает нужно проверить, есть что-нибудь в ячейке или же она пуста. В таких случаях пригодится ЕПУСТО.
Если в выбранной ячейке не содержится никаких данных, то формула вернет ИСТИНУ, если данные есть — ЛОЖЬ.
На практике эту формулу можно использовать в условном форматировании. В примере она подсвечивает ячейки без оценок красным цветом:
ЕСЛИОШИБКА (IFERROR)
Сейчас мы рассмотрим крайне полезный инструмент. Если формула или значение, которое вложено в ЕСЛИОШИБКА, возвращает ошибку, то формула вернет то значение, которое мы зададим.
Нагляднее объяснять на скриншотах. Смотрите:
Мы умножаем число из первого столбца на число из второго. В третьей строке мы ожидаемо наблюдаем ошибку, ведь попытались умножить 25 на x (на текст).
Применим нашу формулу:
Посмотрите, ЕСЛИОШИБКА состоит из двух аргументов:
- первый — наша исходная формула;
- второй — то, что вернет формула при возникновении ошибки.
Мы пытаемся умножить 25 на х, ничего не получается; поэтому начинает работать вторая часть формулы — она умножает 25 на ячейку $B$5, и мы получаем 625.
КЕЙС С ЕСЛИОШИБКА И УМНОЖЕНИЕМ
Посмотрите на скриншот. В столбце А находятся значения, а во второй мы будем вводить формулу, задачей которой будет умножить значение слева от себя на 2 (=А2*2). Введем эту формулу в ячейку B2 и скопируем ее или протянем до ячейки B12.
В 7-й строке оказался текст — при попытке умножить его на число мы ожидаемо получаем ошибку. К тому же возникла ошибка в общей сумме в 13-й строке. Модернизируем первоначальную формулу до =ЕСЛИОШИБКА(А2*2;0) и попробуем еще раз.
Теперь 7-я строка в порядке и вместо ошибки формула возвращает 0. Это значит, что и сумма диапазона B2:B12 в ячейке B13 посчитается без проблем.
ИНСТРУМЕНТЫ ПРОВЕРКИ ДАННЫХ
СОЗДАЕМ ВЫПАДАЮЩИЙ СПИСОК
С помощью инструментов проверки данных мы можем создать выпадающий список. Сделать это очень просто, попробуем.
Сначала выделите ячейку или диапазон ячеек и кликните правой кнопкой мыши на выделенном. Выберите Проверка данных…
У нас есть два варианта. Первый — выбрать Значение из списка и перечислить элементы выпадающего списка в текстовом поле справа. Значения разделяются запятыми.
Второй — сослаться на диапазон, ячейки которого станут элементами выпадающего списка.
Для этого нужно выбрать Значения из диапазона.
После этого выберите диапазон на странице либо введите его вручную. Он может быть как горизонтальным (по столбцам), так и вертикальным (по строкам).
В список попадут только уникальные значения (еще один плюс Google Таблиц — в Excel же попадает все); то есть если в выбранном диапазоне 50 строк со словом «зарплата» и 50 строк со словом «аванс», то в списке будут только два элемента: «зарплата» и «аванс».
Вот так выглядит готовый список:
Обратите внимание: на выбранных ячейках появилась иконка списка. Ее можно убрать (см. скриншот).
КЕЙС «ЭЛЕМЕНТЫ ВЫПАДАЮЩЕГО СПИСКА ЗАВИСЯТ ОТ ТОГО, ЧТО ВЫБРАНО В ДРУГОМ ВЫПАДАЮЩЕМ СПИСКЕ»
В ячейке А2 у нас выпадающий список, диапазон для его элементов — Е2:H2.
Наша задача — сделать в ячейке B2 еще один выпадающий список: если в А2 выбрать сериал, то в списке ячейки B2 должны быть актеры этого сериала.
Напишем в ячейку E8 формулу, которая будет формировать значения выпадающего списка:
Получилось целых две функции, одна в другой, начнем с вложенной:
ПОИСКПОЗ (MATCH) будет искать положение запроса (сериала из ячейки А2, который мы выберем из выпадающего списка) в диапазоне Е2:H2. Например, на запрос «Во все тяжкие» формула вернет 1 — первое значение диапазона (слева направо).
Запомнили, идем дальше.
Формула СМЕЩ (OFFSET) помогает получить диапазон, двигаясь от выбранной ячейки/диапазона (подробнее ).
Аргументы формулы:
СМЕЩ(адрес ячейки/диапазона; число строк; число столбцов; [высота]; [ширина])
Мы начинаем с ячеек D2:D6 (первый аргумент), спускаемся на одну строку от начала диапазона (второй аргумент) и идем вправо на один столбец (так как вложенная формула при выбранном сериале «Во все тяжкие» возвращает 1, будет меняться запрос — будут меняться результат формулы ПОИСКПОЗ и, соответственно, количество столбцов, на которые мы сдвигаемся).
Вот результат:
Обратите внимание: формула находится только в ячейке Е8, но заполняет данными ячейки Е8:Е11. Если в какой-нибудь ячейке уже будут данные, формула вернет ошибку.
А сейчас просто используем диапазон Е8:E11 как диапазон данных для второго выпадающего списка.
У нас получатся два списка, второй из них интерактивен, ведь его элементы зависят от выбранного значения в первом списке:
ПРОВЕРКА ДАННЫХ ПРИ ВВОДЕ ДАННЫХ
Кроме создания списка с помощью инструментов проверки данных мы можем определять то, что можно ввести в ячейку.
Создадим условия значений для проверки данных, введенных в ячейку:
Попробуем задать правило, которое будет запрещать ввод в ячейку любых значений больше 0, равных 0 или текстовых:
Введем «1» и увидим:
Если вместо Запрещать ввод данных выбрать Показывать предупреждение, то значение удастся ввести, но у ячейки появится значок ошибки с текстом правила, отображающимся при наведении:
ПРОВЕРКА ДАННЫХ В УСЛОВНОМ ФОРМАТИРОВАНИИ
Функции проверки данных можно использовать в условном форматировании, чтобы подсветить верные или неверные значения — например, те, которые не являются ссылками (URL):
Для этого, выделив диапазон с ссылками, зайдите в Условное форматирование:
В появившемся окне с настройками выберите пункт Ваша формула, введите формулу =ISURL(A2) и выберите формат для оформления ячеек, в которых будут верные данные:
Аналогично можно обработать ячейки с адресами электронной почты с помощью функции ISEMAIL:
