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

class="p1">(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).

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