Как использовать формулу проверки данных в Google таблицах?

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

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

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

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

Выполните следующие действия, чтобы использовать функцию;

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

Перейдите к Данные > Проверка данных.

Нажмите кнопку Плюс(+), чтобы добавить правило проверки.

Нажмите на выпадающий значок параметра Критерии.
При необходимости выберите диапазон.
Выберите “Пользовательская формула ” и настройте свою формулу.
Например, используйте “=ISODD(B7)” на коробке. Здесь A1 — это представление ячейки.
Вы можете выбрать внешний вид. Отметьте галочкой Показать текст справки по проверке. Введите любой текст (например, Число нечетное)
Наконец, нажмите кнопку Сохранить .

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

Если введенное значение нечетное, то сообщение об ошибке не отображается.

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

Вы можете настроить множество формул и использовать их в своих таблицах Google.

Проверка данных в Google таблицах с использованием пользовательской формулы

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

1. Относительные ссылки на ячейки

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

Например, если вы скопируете формулу =X1+Y1 из строки 1 в строку 2, формула станет =X2+Y2.

2. Абсолютные ссылки на ячейки

остаются постоянными при копировании формулы в другую ячейку. Независимо от того, из какой ячейки они скопированы, они остаются постоянными.
Это уместно всякий раз, когда вам нужно сохранить строку/столбец постоянными.
Знак доллара ($) играет жизненно важную роль в абсолютных ссылках на ячейки.
Ссылка на ячейку остается постоянной, если вы используете знак доллара при копировании.

Например;
=$A$1: Остается постоянной независимо от того, куда она копируется.
=$A1: Столбец остается постоянным, но строка изменяется.
=A$1: Только строка остается постоянной, а столбец изменяется.

Пример пользовательских формул в Google Sheet Data Validation

Давайте начнем с базового примера использования пользовательских формул при проверке данных в Google Sheets.

Вот несколько примеров пользовательских формул.

=isodd(A1)

Эта формула изменяет функцию ISODD на ISEVEN и допускает только четные числа.

=и(iseven(A1), B1=”Да”)

Эта формула допускает только четные числа в диапазоне A1:An если диапазон B1:Bn содержит “Да.” (Здесь n — количество выбранных диапазонов)

=$A$1=”Разрешить”

Эта формула удаляет слово Разрешить из одной ячейки (пусть A1 ) и затем вводит в другую ячейку в диапазоне проверки данных.

Возникнет ошибка, если у вас нет слова ‘Разрешить’ в A1.

Если вы введите слово ‘Разрешить’ в A1, вы можете ввести данные в диапазон проверки данных ячеек.

=$A$1=”Нет”

Если ячейка A1 равна “Нет,” тогда данные будут приняты для диапазона проверки данных.

Как упоминалось выше, $ представляет абсолютную ссылку ячейки на ячейку A1.

Если A1 равно чему-либо, отличному от “Нет,” он отклонит любой ввод.

В случае, если для параметра A1 установлено значение “Да,” и вы вводите данные в выделенные ячейки, вы увидите там было сообщение о проблеме в вашем окне.

Проверка данных пользовательской формулой с помощью функции VLOOKUP

VLOOKUP — это просто функция вертикального поиска.

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

Функция VLOOKUP в Google таблицах работает так же, как и в Excel.

Формат функции VLOOKUP =VLOOKUP(search_key, range, index, [is_sorted]).

Вот несколько правил использования функции VLOOKUP в Google таблицах;

Search_key может быть любой из ячеек столбца, кроме последней.
Диапазон предназначен для поиска по индексу, который представляет собой количество столбцов из извлеченных данных.
Индекс равен 1 для первого столбца диапазона, 2 для второго столбца диапазона и так далее.
Необязательный параметр отсортирован, и его значение будет равно true если столбец отсортирован, и значение false, если он не отсортирован в закрывающих скобках.

Например, мы можем использовать формулу VLOOKUP для поиска TV в столбце 3.

Когда результат соответствует значению поиска, он вернет значение из шестого столбца, чтобы получить значение 52.

Вставьте формулу ВПР в соответствии с вашими требованиями. Укажите ключ поиска, диапазон, индекс и т.д.
Мы используем False() в параметре is_sorted, потому что нам нужно точное совпадение.

После ввода формулы ВПР в ячейку нажмите кнопку enter.
Вы можете увидеть название элемента в виде TV в соответствующей ячейке.

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

Нижняя строка

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

Я надеюсь, что описанных выше процедур будет достаточно, чтобы использовать проверку данных Google Sheet с помощью пользовательской формулы.

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