Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

что выступает минимальным, средним и максимальным значением.

Например, можно задать точные числа (а не минимум/максимум, как по умолчанию) в качестве «пограничных».

Тогда для всех чисел меньше минимума и больше максимума будут применяться одни и те же цвета уже без градиента.

Обратите внимание, что есть тип «Процент» (Percent) и «Процентиль» (Percentile), — так, по умолчанию у трехцветных шкал «средний» цвет для процентиля равен 50. Это медиана — значение, которое находится в середине набора чисел (если его упорядочить).

То есть процент — про значения, а процентиль — про положение в упорядоченном наборе чисел.

Разница хорошо видна в случае с диапазонами, где одно число сильно выбивается из ряда: в варианте с процентом просто нет числа, которое бы заливалось желтым.

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

В следующем примере мы ориентируемся не на проценты, а на конкретные числа (для этого нужно поменять «Тип» на «Число»): числа больше 40 000 — галочка, числа от 25 000 до 40 000 — без значка (значок выбирается из списка, можно комбинировать значки из разных наборов или, как в этом случае, вовсе убрать значок для определенных условий), числа до 25 000 — крестик.

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

Проверка данных (Data validation)

Проверка данных (Data validation) — инструмент для предотвращения ошибок при вводе информации в ячейки. Проверка данных позволяет заранее определить, какие значения можно вводить, а какие нет. Находится на ленте в «Данных» (Data).

В Google Таблицах: в меню «Данные» — «Настроить проверку данных» (Data — Data Validation).

Для чего нужна проверка данных? Чтобы исключать типовые ошибки:

• текст вместо чисел;

• отрицательные числа там, где их быть не может;

• числа с дробной частью там, где должны быть целые;

• текст вместо даты;

• разные варианты написания одного и того же значения — например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами, — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

Выделяйте ячейки, к которым хотите применить проверку данных, вызывайте этот инструмент и далее настраивайте параметры проверки в диалоговом окне «Проверка данных».

Вот какие типы данных тут есть.

• Любое значение (Any value), то есть отсутствие проверки данных.

• Целое число (Whole number): можно задать ограничения снизу или сверху (например, числа больше 10 000). Удобно, если у вас натуральный показатель (в штуках) и дробной части быть не может.

• Действительное число (Decimal): тоже можно задавать ограничения. Подойдет, например, для величины сделки. У такого показателя может быть дробная часть (копейки), но может понадобиться ограничение (например, от 0).

• Список (List): ограниченный набор значений, перечень. Например, фамилии менеджеров, клички ваших котов, названия товаров или список городов, где ваша компания работает. Можно делать его с выпадающим списком или без. Главное, что вводить можно только значения из списка. Их можно перечислить прямо в проверке данных через точку с запятой (или запятую — смотря какие у вас региональные настройки и что является разделителем элементов списка) или сослаться на диапазон со значениями (об этом подробнее ниже).

• Дата (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 и выбрать в появившемся окне «Вставка имени» созданное имя.