Магия таблиц. 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. Как ее найти?
Файл → Параметры → Центр управления безопасностью → Параметры центра управления безопасностью → Надежные расположения
