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

Как изменить шаблон книги Excel по умолчанию

И стили, и пользовательские форматы – отличные инструменты для оформления таблиц. Они сохраняются, их можно применять к разным ячейкам. Но сохраняются они в рамках одной рабочей книги Excel. А что, если вы хотите видеть некоторые свои форматы и стили в каждой книге Excel, которую будете создавать?

Для этого лучше изменить шаблон создаваемой по умолчанию (пустой) книги Excel.

Алгоритм следующий.

1. Создайте новую книгу Excel.

2. Добавьте в нее нужные вам пользовательские форматы.

3. Добавьте/создайте/измените стили, которые вам будут нужны в разных книгах. Обратите внимание, что если изменить стиль «Обычный» (Normal), то вы зададите формат абсолютно всех ячеек в новых книгах Excel (так как по умолчанию именно этот стиль применяется ко всем ячейкам).

4. Внесите другие изменения, которые хотите видеть в каждой вновь созданной книге.

5. Эту книгу нужно будет сохранить в системную папку XLSTART. Как ее найти?

Файл → Параметры → Центр управления безопасностью → Параметры центра управления безопасностью → Надежные расположения

(File → Options → Trust Center → Trust Center Settings → Trusted Locations).

6. Теперь эту книгу нужно сохранить как шаблон. «Сохранить как» (Save As), формат – шаблон Excel (Excel Template), расширение. xltx, название «Книга» (Book) – именно так, без цифр.

7. Готово! После сохранения книги под названием «Книга. xltx» в папку XLSTART она будет выступать шаблоном для всех новых книг. Если вам захочется вернуться к состоянию «по умолчанию», просто удалите ее из папки XLSTART.

Если вы хотите сделать шаблон в Google Таблицах, проще поступить следующим образом: создайте таблицу-шаблон, сделайте ссылку для создания копии (добавьте /copy в конце), добавьте на панель закладок в браузере – и вот вам кнопка для быстрого создания таблицы по образу шаблона. К тому же можно настроить выборочные доступы – только для себя, для коллег из своей команды или для разных подразделений.

Условное форматирование (Conditional formatting)

Файл с примерами: Условное форматирование. xlsx

Условное форматирование (Conditional formatting) – это автоматическое форматирование ячеек при соблюдении заданных вами условий, например:

• все ячейки с числами более 10 000 выделяются полужирным шрифтом, и к ним применяется определенный числовой формат;

• все дубликаты заливаются зеленым цветом;

• к любой текстовой ячейке, содержащей слово «Москва», применяется красный цвет шрифта.

Условное форматирование (Conditional formatting) находится на ленте инструментов на вкладке «Главная» (Home) в Excel.

В Google Таблицах – в меню «Формат» (Format) (Alt + O + F).

В меню условного форматирования Excel – следующие опции.

Правила выделения ячеек (Highlight Cells Rules): здесь можно настроить выделение ячеек, содержащих значения «больше/меньше определенного уровня» (для чисел), определенный текст, определенные даты или дубликаты.

Правила отбора первых и последних значений (Top/Bottom Rules): здесь правила с относительными условиями – например, для выделения значений выше или ниже среднего, лучших или худших значений (допустим, 10 % наименьших или 20 % наибольших значений).

Далее следуют три варианта условного форматирования с графическими элементами (гистограммы, цветовые шкалы и наборы значков) – подробнее о них ниже.

Создать правило (New Rule): создание собственного правила с указанием всех параметров, в том числе есть возможность создать условное форматирование с условием, заданным формулами.

Удалить правила (Clear Rules): удаление правил из выделенных ячеек или всего листа.

Управление правилами (Manage Rules): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.

«ОБЫЧНЫЕ» ПРАВИЛА

Числа

Рассмотрим работу условного форматирования на примере. Наша задача – выделить зеленым все ячейки с продажами более 400.

Выделяем все ячейки от B2 и до конца столбца (это проще всего сделать сочетанием клавиш Ctrl + Shift + ↓) и вызываем правило условного форматирования «Больше» (Greater Than).

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

Условное форматирование (и это касается не только правил «Больше», а любых правил вообще) применяется автоматически при изменении данных, то есть как только в какой-то ячейке мы поменяем значение на число менее 400, она перестанет форматироваться, и наоборот, если ячейка начнет удовлетворять условиям, к ней будет применена зеленая заливка.

Условное форматирование выше по приоритету, чем обычное. То есть даже если вы применили к ячейке, например, желтую заливку, но к ней также применяется условное форматирование и она соответствует заданному условию, то она будет зеленой (или другого цвета/форматирования, заданного именно в условном форматировании).

ОТБОР ПЕРВЫХ И ПОСЛЕДНИХ ЗНАЧЕНИЙ

Здесь есть три варианта выделения ячеек: первые и последние значения (по величине), значения, входящие в заданный процент самых больших и самых маленьких, и значения выше/ниже среднего.

Допустим, мы хотим выделить 5 самых больших чисел в диапазоне. Тогда мы выделяем диапазон с числами и выбираем вариант «Первые 10 элементов…» (Top 10 Items). Обратите внимание, что в названиях тут фигурируют 10 элементов или 10 %, хотя на самом деле эти значения мы можем задавать сами.

В поле с количеством по умолчанию будет 10, но мы вправе менять это число. В примере мы выделяем зеленым только 5 наибольших элементов.

Если нам нужно выделять какую-то часть (в процентах) самых больших или самых маленьких значений, то нужны правила «Первые 10 %» (Top 10 %) или «Последние 10 %» (Bottom 10 %). Допустим, мы хотим выделить красным худшую четверть – 25 % самых маленьких значений. Это правило «Последние 10 %».

Наконец, если мы хотим выделить все дни выше или ниже среднего значения, используем соответствующие правила «Выше среднего» (Above Average) и «Ниже среднего» (Below Average). У них нет числового параметра – только возможность выбора форматирования.

Текст

Правила для текстовых ячеек есть двух типов: точное соответствие («Равно») и «Текст содержит», то есть вхождение определенного слова / символа / сочетания символов в состав текстовой строки в ячейке.

Так, если в следующей таблице мы хотим выделить все ячейки с конкретной моделью ноутбука в столбце «Товар», то подойдет и правило «Равно» (Equal To), и правило «Текст содержит» (Text that Contains).

А вот если мы хотим выделить цветом все товары бренда Lenovo (то есть ячейки, в которых это слово входит в состав текста), то подойдет только правило «Текст содержит» (Text that Contains).

ССЫЛКИ НА ЯЧЕЙКИ В ПРАВИЛАХ УСЛОВНОГО ФОРМАТИРОВАНИЯ

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

Обычно условие (например, число, больше которого должны быть значения ячеек, чтобы они форматировались) указывается в самом правиле, например:

В таком случае все ячейки с числами более 400 будут автоматически форматироваться. Но чтобы изменить само условие (число 400), нужно будет изменять правило условного форматирования:

Главная → Условное форматирование → Управление правилами (Home → Conditional Formatting → Manage Rules).

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

Обратите внимание на доллары в ссылке на ячейку E1 в правиле – это «закрепленная» (абсолютная, со знаками доллара) ссылка на ячейку E1. Мы форматируем целый столбец B с продажами, а сравниваем каждое число в нем с одной и той же ячейкой E1. Если бы мы не закрепили ссылку на нее долларами, то с ней сравнивалась бы только первая ячейка диапазона – B2. А уже B3 бы сравнивалась с пустой ячейкой E2, B4 – с E3 и так далее (по аналогии с протягиванием формул, при котором все относительные ссылки смещаются).

Теперь при изменении числа в E1 форматирование будет изменяться автоматически, так как именно эта ячейка выступает условием для нашего правила.

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

В таком случае нам нужна относительная ссылка, ведь B2 будет сравниваться с C2, B3 c С3 и так далее – каждый раз это разные ячейки в рамках одной и той же строки.

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

ГИСТОГРАММЫ, ЦВЕТОВЫЕ ШКАЛЫ И ЗНАЧКИ

В условном форматировании есть три опции для визуализации данных с помощью графических элементов: гистограммы (Data Bars), цветовые шкалы (Color Scales) и значки (Icon Sets). Причем их можно применять как к обычным ячейкам с данными, так и к полям сводных.

В Google Таблицах графических элементов (гистограмм и значков) в условном форматировании нет – только цветовые шкалы. Значки можно вставлять с помощью текстовых функций или через функцию IMAGE (если у вас есть ссылка на значок или эмодзи). А гистограмму можно построить с помощью функции SPARKLINE (горизонтальная гистограмма в этой функции имеет тип bar).

Гистограммы (Data bars)

Гистограммы – это маленькие графики (горизонтальные линии, или, как они называются в Excel, столбики) в ячейках. Их ширина зависит от значения в ячейке, а максимальная ширина (во всю ячейку) определяется максимальным значением в диапазоне.

Есть двенадцать готовых вариантов гистограмм – со сплошным цветом и полупрозрачных.

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

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

– если сразу выбрать в списке наборов значков не готовый вариант, а «Другие правила» (More Rules), тогда вы попадете в диалоговое окно с настройками;

– через «Управление правилами» (Manage Rules) в «Условном форматировании».

Например, так будет выглядеть гистограмма, если активировать опцию «Показывать только столбец» (Show Bar Only) в настройках.

Цветовые шкалы (Color Scales)

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

Есть встроенные двух– и трехцветные шкалы, которые подойдут для многих случаев.

Есть возможность настраивать свои шкалы, для этого нужно выбрать вариант «Другие правила» (More Rules). В диалоговом окне выбираем нужные цвета и в образце видим, как будет выглядеть фон ячеек.

Наборы значков (Icon Sets)

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

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

Если их три, то для каждой трети (меньшая треть, средняя и наибольшая).

Если четыре, то для каждой из четырех равных частей.

Если пять, то, соответственно, для каждой из пяти.

Но настройки можно изменять – см. «Настройка графических объектов условного форматирования» ниже.

Превращаем цифры (оценки) в значки

Значки хорошо подходят для оценок, например от 1 до 5.

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

Если включить опцию «Показывать только значок» в настройках правила, то значения в ячейках отображаться не будут – только сами значки.

Настройка графических объектов условного форматирования

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

• В случае с цветовой шкалой все числа в диапазоне сортируются: для минимального применяется один «крайний» цвет шкалы, для максимального – другой, для среднего (если в шкале три цвета) – «средний» цвет, а для остальных – в зависимости от положения числа на спектре всех значений в диапазоне.

• В случае с иконками все числа в диапазоне будут разделены на три, четыре или пять (смотря сколько иконок в выбранном наборе) равных частей и для каждой из них будет применяться значок из набора.

• В случае с гистограммой к максимальному числу в диапазоне применяется гистограмма шириной во весь столбец, а к остальным – шириной относительно максимального значения.

Если вам нужно что-то другое, заходите в настройки правила или сразу выбирайте вариант «Другие правила» (More Rules).

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

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

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

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

Еще один вариант – ссылаться на имя таблицы и столбца в ней через функцию ДВССЫЛ / INDIRECT. Эта функция принимает текст в качестве аргумента и превращает его в ссылку, то есть это такой непрямой способ ссылаться на диапазоны, и она в данном случае помогает обойти ограничение проверки данных.

В Excel 2007 и ранее в проверке данных в принципе нельзя было ссылаться на другой лист, это нужно было делать через ДВССЫЛ / INDIRECT, даже если список не предполагается обновлять.

Подробнее про выпадающие списки с обновлением смотрите в видео:

Выпадающие списки в Excel с автоматическим добавлением новых значений

https://mif.to/VRU7P

؂