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

Из этого туториала Вы узнаете, как создавать собственные формулы для проверки данных в Excel и Google Таблицах.

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

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

1. Выделите требуемый диапазон, например: B3: B8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.

3. Выбирать Обычай в раскрывающемся списке Разрешить, а затем введите следующую формулу:

= EXACT (LEFT (B3,4); "FRU-")

В формуле используются 2 функции ТОЧНЫЙ а также ЛЕВЫЙ чтобы определить, идентичны ли первые 4 введенных в ячейку символа «FRU-»

4. Если вы хотите добавить вводное сообщение для своего пользователя, щелкните Входное сообщение.

5. Убедитесь, что установлен флажок «Показывать входное сообщение, когда ячейка выбрана», А затем введите название и сообщение, которое вам нужно.
6. Если вы хотите добавить предупреждение об ошибке, щелкните Предупреждение об ошибке.

7. Вы можете изменить стиль из Стоп либо Предупреждение или Информация если требуется, а затем введите свой заголовок и сообщение об ошибке.
8. Нажмите Ok.
9. Если вы использовали Вход Опция сообщения, на экране появится комментарий, информирующий пользователя о правиле.

10. Напечатайте «FRI-124» в ячейке B3. Если вы выбрали Стоппоявится следующее окно сообщения.

11. Если вы выбрали Предупреждение, то появится это окно сообщения. Это позволяет продолжить, если вы определите, что данные верны.

12. Если вы выбрали Информация, то появится это окно сообщения.

13. Если вы нажмете Ok, вам будет разрешено продолжить с неверными данными, введенными в ячейку.
14. Нажмите Отмена выйти из сообщения или Ok ввести текст в ячейку.

Проверка данных разрешает только прописные буквы в Excel

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

1. Выделите требуемый диапазон, например: B3: B8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. Выбирать Обычай в раскрывающемся списке Разрешить, а затем введите следующую формулу:

= ТОЧНО (B3; ВЕРХНИЙ (B3))

В формуле используются 2 функции ТОЧНЫЙ а также ВЕРХНИЙ чтобы определить, является ли текст, введенный в ячейку, в верхнем регистре. Ячейки со смесью чисел и текста считаются текстом, а числа игнорируются в правиле.

4. Нажмите Ok.
5. Наберите "fru-124" в ячейку B3.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.
8. Введите «123456» в ячейку B3.
9. Это будет разрешено, так как это число, а не текст.

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

Проверка данных разрешает текст только в верхнем регистре в Excel

Мы можем написать настраиваемую формулу в Data Validation, чтобы гарантировать, что данные в ячейке допускают только текст в верхнем регистре.

ПРИМЕЧАНИЕ: если вы вводите информацию в ячейку, которая начинается с текста, но содержит числа, Excel будет учитывать текст информации.

1. Выделите требуемый диапазон, например: B3: B8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. Выбирать Обычай в раскрывающемся списке Разрешить, а затем введите следующую формулу:

= И (ТОЧНЫЙ (B3; ВВЕРХ (B3)); ISTEXT (B3))

В формуле используются 4 функции А ТАКЖЕ, ТОЧНЫЙ, ВЕРХНИЙ а также ТЕКСТ чтобы определить, является ли текст, введенный в ячейки, в верхнем регистре А ТАКЖЕ чтобы определить, действительно ли введенная информация является текстом, а не чистым числом.

4. Нажмите Ok.
5. Наберите "fru-124" в ячейку B3.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.
8. Введите «123456» в ячейку B3.
9. Вы снова получите сообщение об ошибке.
10. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Проверка данных предотвращает пробелы в Excel

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

1. Выделите требуемый диапазон, например: B3: B8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. Выбирать Обычай в раскрывающемся списке Разрешить, а затем введите следующую формулу:

= B3 = ЗАМЕНА (B3, ””, “”)

Формула использует функцию ЗАМЕНА для проверки отсутствия пробелов.

4. Нажмите Ok.
5. Введите «FRU - 124» в ячейку B4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Проверка данных предотвращает дублирование в Excel

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

1. Выделите требуемый диапазон, например: B3: B8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= СЧЁТЕСЛИ ($ B $ 3: $ B $ 8; B3) <2

В формуле используется СЧЁТЕСЛИ функция и использование АБСОЛЮТЫ в диапазоне B3: B8, чтобы убедиться, что это список, который СЧЁТЕСЛИ функция смотрит, когда она проверяет, есть ли какие-либо повторяющиеся значения.

4. Нажмите Ok.
5. Введите «FRU-123» в ячейку D4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Проверка данных существует в списке в Excel

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

1. Выделите требуемый диапазон, например: D3: D8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке Разрешить выберите Пользовательский и введите следующую формулу:

= СЧЁТЕСЛИ ($ F $ 6: $ F $ 8; D3)> 0

В формуле используется СЧЁТЕСЛИ функция и использование АБСОЛЮТЫ в диапазоне F3: F8, чтобы убедиться, что это список, который СЧЁТЕСЛИ функция смотрит, когда она проверяет, что вводится правильный текст.

4. Нажмите Ok.
5. Введите «Одиночный» в ячейку D4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Проверка данных не существует в списке в Excel

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

1. Выделите требуемый диапазон, например: C3: C8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= СЧЁТЕСЛИ ($ F $ 6: $ F $ 8; C3) = 0

В формуле используется СЧЁТЕСЛИ функция и использование АБСОЛЮТЫ в диапазоне F3: F8, чтобы убедиться, что это тот список, который СЧЁТЕСЛИ функция смотрит, когда она проверяет, что вводится правильный текст.

4. Нажмите Ok.
5. Введите «Говядину» в ячейку C4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

В Excel вводятся только числа для проверки данных

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

1. Выделите требуемый диапазон, например: E3: E8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= ЕЧИСЛО (F3: F8)

В формуле используется функция ЕЧИСЛО, чтобы обеспечить ввод числа в ячейки диапазона.

4. Нажмите Ok.
5. Введите «девять» в ячейку F4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Проверка данных не превышает значения в Excel

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

1. Выделите требуемый диапазон, например: E3: E8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= E3 <= $ G $ 6

Формула an АБСОЛЮТНЫЙ в диапазоне G6, чтобы гарантировать, что это значение проверяется правилом при вводе данных в E3.

4. Нажмите Ok.
5. Введите «9» в ячейку E4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше собственное предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Проверка данных не превышает итогового значения в Excel

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

1. Выделите требуемый диапазон, например: F3: F8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= СУММ ($ F $ 3: $ F $ 8) <= $ H $ 6

В формуле используется СУММ функция и использование АБСОЛЮТЫ в диапазоне F3: F8, чтобы убедиться, что это тот список, который СУММ Функция проверяет, когда она проверяет, не превышает ли сумма диапазона значение, введенное в H6.

4. Нажмите Ok.
5. Введите «40» в ячейку F4.
6. Если вы использовали опцию «Предупреждение об ошибке», появится ваше собственное предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена», чтобы закрыть сообщение, или «Повторить», чтобы повторно ввести правильный текст в ячейку.

Даты рабочих дней для проверки данных только в Excel.

Мы можем использовать настраиваемую формулу, чтобы гарантировать, что вводятся только дни недели, когда даты используются в Excel.

1. Выделите требуемый диапазон, например: G3: G8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= ДЕНЬ НЕДЕЛИ (F3,2) <6

Функция дня недели проверяет, не является ли день, указанный в дате, субботой или воскресеньем.

4. Измените дату в G5, чтобы показать субботу (например, 9^th Май 2022 г.).
5. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

7. Нажмите «Отмена» или «Повторить», чтобы ввести альтернативную дату.

Дата проверки данных в будущем только в Excel

Мы можем создать настраиваемую формулу, чтобы пользователь мог вводить только дату в будущем.

1. Выделите требуемый диапазон, например: G3: G8.
2. На ленте выберите Данные> Инструменты данных> Проверка данных.
3. В раскрывающемся списке «Разрешить» выберите «Клиент» и введите следующую формулу:

= G3> СЕГОДНЯ ()

В формуле используется CЕГОДНЯ функция, чтобы проверить, больше ли дата, введенная в ячейку, чем сегодняшняя дата.

4. Измените дату в G5 на вчера.
5. Если вы использовали опцию «Предупреждение об ошибке», появится ваше настраиваемое предупреждение и сообщение об ошибке. Если вы не использовали эту опцию, появится стандартное предупреждение.

6. Нажмите «Отмена» или «Повторить», чтобы ввести альтернативную дату.

Проверка данных должна начинаться с в Google Таблицах

1. Выделите требуемый диапазон, например: B3: B8.
2. В меню выберите Данные> Проверка данных.

3. Диапазон ячеек уже будет заполнен.

4. Выбирать Пользовательская формула находится в раскрывающемся списке Критерии.

5. Введите формулу.

= EXACT (LEFT (B3,4); "FRU-")

6. Выберите либо Показать предупреждение или Отклонить ввод если данные недействительны.

7. При необходимости вы можете ввести текст справки по валидации.

8. Щелкните Сохранить.

9. Щелкните B3, чтобы увидеть текст справки по валидации.

10. Тип ФРИ-123
11. Если вы выбрали Показать предупреждениепоявится следующее сообщение.

12. В качестве альтернативы, если вы выбрали Отклонить ввод неверных данных, вы не сможете ввести данные, и на экране появится следующее сообщение.

Остальные примеры пользовательских формул в Google Таблицах работают точно так же.