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

# — значащие цифры (отображаются, если на этой позиции есть значение)

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

? — цифры после запятой, если нужно выравнивать числа по десятичной запятой

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

# ## — разделители разрядов

В американских региональных настройках и Google Таблицах #,## (с запятой между решетками, а не пробелом).

# ## — разделители групп разрядов (пробелы в российских региональных настройках) к числу. Например, # ##0,00 — это числовой формат с разделителями групп разрядов и двумя обязательными знаками после запятой.

0% — процентный формат

Здесь все просто: 0% — это процентный формат, полностью соответствующий стандартному процентному формату, который формируется в ячейках автоматически при вводе знака процента. Но в пользовательских форматах вы можете его модифицировать, например добавить разделители групп разрядов.

* — заполнение ячейки указанным символом до конца

Например, если после звездочки поставить дефис, то этот символ будет повторяться до конца ячейки (а до повторяющихся дефисов будет то, что указано в формате до звездочки, например число).

"Текст" — текст, указанный в кавычках, будет отображаться в ячейке

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

В следующем примере к числу с разделителями разрядов мы добавляем текст "шт.".

Пробел — после формата числа округляет его до тысяч (два пробела — до миллиона, три — до миллиарда и так далее).

В американских региональных настройках — запятая, а не пробел.

В следующем примере в качестве формата используется 0 (с одним пробелом) после него. Это округляет числа до тысяч.

@ — текст, введенный в ячейке

Этот символ обозначает текст из ячейки. Например, @@ — повторение текста дважды. А @*- — формат с заполнением ячейки дефисами после текста.

_ (нижнее подчеркивание) — отступ на ширину указанного после нижнего подчеркивания символа

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

Без отступа эти числа выглядели бы так.

[Цвет] — цвет значения

Задается по названию (в Excel — на языке интерфейса, в Google Таблицах — на английском) [Красный][Red] или номеру [ЦветN][ColorN].

Указывается перед форматом.

Например, [Синий]0,00 — синий цвет, число с двумя знаками после запятой.

Коды цветов есть в файле с примерами — «Пользовательские форматы.xlsx».

СТРУКТУРЫ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМАТОВ

Есть две структуры пользовательских форматов.

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

Положительные; Отрицательные; Ноль; Текст

Пример 1: положительные числа — зеленым, отрицательные — красным, ноль не отображаем, текст — синим.

[Зеленый]0;[Красный]-0;;[Синий]@

Пример 2: положительные числа со знаком «плюс» и в процентном формате, отрицательные — со знаком «минус», красным цветом, в процентном формате. Ноль — как прочерк (дефис, «-»).

+ 0%;[Красный]-0%;"-"

По условиям. Один формат для одного условия, опционально — другой для второго и третий для всех остальных случаев.

Условие 1; Условие 2; Остальные случаи

Условия указываются в квадратных скобках с использованием знаков «равно» (=), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=).

Пример 1: числа больше 2000 с разделителями разрядов, меньше — с одним знаком после запятой:

[>2000]# ##0;0,0

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

Пользовательские форматы в Google Таблицах

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

Видео: https://m.youtube.com/watch?v=O-YOrSL89f4#bottom-sheet,

Пользовательские числовые форматы в 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. Как ее найти?

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