• Дата (Date): можно задать ограничения, например «дата не позже / не раньше определенной или в промежутке».
• Время (Time): аналогично.
• Длина текста (Text length): ограничение на количество символов (можно задать ограничение и снизу, и сверху, и интервал).
После сохранения проверки данных в ячейки нельзя будет ввести значения, не соответствующие заданному правилу, — если вы задали вариант сообщения об ошибке «Остановка» (Stop) в окне проверки.
Если же выбрать «Предупреждение» (Warning) или «Сообщение» (Information), то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Если в ячейках с проверкой уже есть неверные данные (например, они были в них до того, как вы настроили проверку, либо у вас проверка с предупреждением, а не остановкой), их можно обвести, чтобы точно увидеть, где есть ошибки. Для этого выберите команду «Обвести неверные данные» (Circle Invalid Data).
Для удаления обводки выберите «Удалить обводку неверных данных» (Clear Validation Circles) в том же выпадающем списке.
В Google Таблицах и в Excel только с 2023 года в рамках подписки Microsoft 365 (опция появилась в начале 2022-го, но ее долго не было у обычных пользователей) в проверке данных можно искать значение, то есть после ввода части текста список фильтруется до тех значений, которые соответствуют введенному фрагменту. Это очень удобно, когда значений в исходном списке тысячи, например это названия товаров.
Часто бывает, что проверка данных формируется на основе диапазона, к которому предполагается добавлять новые значения (список филиалов-городов, который может пополниться; сотрудников — можем нанять новых; и так далее).
Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?
Решения могут быть разные в зависимости от того, на разных ли листах данные.
Если данные на том же листе — превращаем справочник в таблицу (Ctrl + T или Ctrl + L, подробнее смотрите главу про таблицы) и ссылаемся на него.
Если же данные на другом листе и будут пополняться новыми, то нужно будет задать столбцу в таблице на другом листе имя. Сослаться напрямую, как в предыдущем примере (когда и проверка данных, и таблица-источник на одном листе), можно, но новые значения попадать в проверку не будут.
Выделяем столбец в таблице на другом листе и присваиваем ему имя (можно ввести его в поле «Имя» слева от строки формул и нажать Enter).
После этого в проверке данных остается нажать F3 и выбрать в появившемся окне «Вставка имени» созданное имя.
Еще один вариант — ссылаться на имя таблицы и столбца в ней через функцию ДВССЫЛ / INDIRECT. Эта функция принимает текст в качестве аргумента и превращает его в ссылку, то есть это такой непрямой способ ссылаться на диапазоны, и она в данном случае помогает обойти ограничение проверки данных.
В Excel 2007 и ранее в проверке данных в принципе нельзя было ссылаться на другой лист, это нужно было делать через ДВССЫЛ / INDIRECT, даже если список не предполагается обновлять.
Подробнее про выпадающие списки с обновлением смотрите в видео:
Выпадающие списки в Excel с автоматическим добавлением новых значений
https://mif.to/VRU7P
Проверка данных (Data Validation) с формулами
Для понимания этого раздела стоит разобраться с логическими значениями и формулами. Если вы еще не знакомы с этой темой, обратитесь к главе «Логические выражения и функция ЕСЛИ / IF».
Файл с примерами: 3 Проверка данных с формулами.xlsx
Формулы в проверке данных работают схожим образом с условным форматированием: вводите формулу для первой ячейки проверяемого диапазона и не забывайте закрепить ссылки (сделать их абсолютными или смешанными) при необходимости.
Для создания правила проверки с формулой нужно проследовать по следующему пути:
Данные → Проверка данных → Тип данных: Другой → Формула (Data → Data Validation → Allow: Custom → Formula).
Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение — зависит от настроек в разделе «Сообщение об ошибке» (Error Alert).
Рассмотрим несколько примеров проверки с формулами.
Длину текста можно определить с помощью функции ДЛСТР / LEN. Она возвращает число — количество символов в ячейке. Соответственно, если мы хотим запретить ввод текста длиннее, допустим, десяти символов, то формула будет выглядеть следующим образом:
=ДЛСТР(ссылка на первую ячейку диапазона) <= 10
Функция ЕФОРМУЛА / ISFORMULA возвращает ИСТИНА, если ее аргумент — ячейка с формулой. Соответственно, следующая формула в проверке данных будет разрешать ввод только формул.
=ЕФОРМУЛА(ссылка на первую ячейку диапазона)
Уникальные значения — это значения, которые в диапазоне встречаются всего лишь один раз. Подсчитать количество можно с помощью СЧЁТЕСЛИ / COUNTIF:
=СЧЁТЕСЛИ(диапазон; критерий)
В случае с проверкой данных мы будем проверять, сколько раз каждое конкретное значение (начиная с первой ячейки диапазона) будет встречаться во всем диапазоне:
=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)
И сравнивать это с единицей: если значение будет встречаться более одного раза, его вводить уже нельзя.
=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)<=1
Если нам нужно запретить ввод повторяющихся значений в диапазоне A2:A30, то формула будет выглядеть так:
=СЧЁТЕСЛИ(A$2:A$30; A2)<=1
Сортировка данных
Сортировка данных — упорядочивание по значениям в одном или нескольких столбцах. Без этой операции никуда: а как иначе посмотреть топ продаж по какому-либо параметру (городам, товарам и т. д.)? Это помогает упорядочить таблицу, анализировать данные становится проще.
Быстрее всего отсортировать данные по возрастанию или убыванию можно на вкладке «Данные».
Выделяем ячейку в том столбце, по которому нужно отсортировать, и нажимаем соответствующую кнопку. Так будет выглядеть начало нашего списка сделок после сортировки по возрастанию (от старых к новым) по столбцу D.
Если вы часто прибегаете к такой быстрой сортировке (по одному столбцу), можно добавить кнопки для сортировки на панель быстрого доступа.
Кроме того, сортировать можно с помощью фильтра (о нем ниже).
А если простой сортировки по одному столбцу недостаточно, открывайте окно «Сортировка» (одноименная кнопка на ленте).
Здесь есть возможность сортировать по нескольким столбцам. Допустим, мы хотим упорядочить таблицу по клиентам, а в рамках каждого клиента — по сумме, чтобы самые крупные сделки были сверху. Тогда мы добавляем два соответствующих уровня сортировки в этом окне — сначала компанию, а затем, нажав кнопку «Добавить уровень» (Add Level), — сумму.
Результат:
В «Параметрах» в сортировке есть две опции: можно сортировать не строки, а столбцы и учитывать регистр текста при сортировке.
Не всегда нужно сортировать данные по алфавиту.
Возможно, вы уже обращали внимание, что месяцы или дни недели сортируются не по алфавиту, а в правильном (хронологическом) порядке. Это происходит потому, что в Excel встроены соответствующие списки для сортировки — с короткими и длинными названиями месяцев и дней недели. Мы не можем их удалить или изменить, но можем добавлять новые!
Допустим, в вашей компании есть правила сортировки списка филиалов — не по алфавиту. В таком случае стоит создать пользовательский список.
Списки добавляются и удаляются в параметрах Excel → Дополнительно → Общие (раздел в самом конце вкладки «Дополнительно») → Изменить списки (Options → Advanced → General → Edit Custom Lists).
В окне со списками есть списки дней недели и месяцев — их редактировать и удалять нельзя. Но можно добавлять свои.
После добавления сортировка в диапазонах, таблицах и сводных таблицах может осуществляться по такому порядку. Кроме того, список можно создать непосредственно в процессе сортировки. В любом случае нужно выбрать «Настраиваемый список…» (Custom List…) в окне сортировки.
И далее в открывшемся окне выбрать список (или добавить новый).
Еще один плюс пользовательских списков: они работают при протягивании.
В Google Таблицах пользовательских списков нет.
Фильтрация данных
Фильтрация данных — еще одна базовая операция, без которой никуда в Excel или Google Таблицах. Фильтрация — отбор и отображение строк по заданным критериям (текстовым, числовым или даже по цвету ячейки/шрифта).
Фильтр можно установить и снять сочетанием клавиш Ctrl + Shift + L или на ленте инструментов на вкладке «Данные» (Data → Filter).
Кроме того, если вы превращаете диапазон в таблицу — сочетания клавиш Ctrl + L или Ctrl + T, «Вставка → Таблицы» (Insert → Table) или «Главная → Форматировать как таблицу» (Home