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

^{Пользовательские числовые форматы в Google Таблицах (Custom number formats in Google Sheets): https://shagabutdinov.ru/custom_format/}

Дополнительные примеры в статье и видео будут полезны и пользователям Excel.

Как изменить шаблон книги 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): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.

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

Числа