Читать книгу: «Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)», страница 3
Стили (Cell Styles)
Стиль (Cell Styles) – это готовый набор параметров форматирования ячейки, стилевого и/или числового. У стилей есть имена, их можно менять, удалять и создавать с нуля.
Чем полезны стили?
1. Можно настроить совокупность параметров форматирования (числовой формат, выравнивание, заливка, шрифт, границы) и использовать в будущем для разных ячеек «в один клик».
2. Сам стиль можно поменять в любой момент (нажмите для этого в списке стилей правой кнопкой мыши на тот, что хотите настроить), и изменения будут применяться ко всем ячейкам с этим стилем (например, можно не переживать, что заголовки в документе будут разные: если применять к ним один стиль, то сможете регулировать внешний вид всех заголовков через настройку этого стиля).
Стили существуют в рамках одной рабочей книги Excel. Если вы хотите, чтобы созданные вами стили были доступны по умолчанию в любой новой книге, создайте пустую книгу, настройте в ней необходимые стили и сохраните как шаблон (инструкция ниже).
Если нужно забрать стили из другой книги Excel (она должна быть открыта), используйте команду «Объединить стили» (Merge Styles). Из выбранной книги в текущую попадут все стили – и созданные пользователем, и измененные стандартные (они заменят такие же стандартные в текущей книге – имейте это в виду, если много ячеек уже оформлены с помощью стандартных стилей).
Увы, в Google Таблицах стилей нет.
Пользовательские форматы (Custom format)
Файл с примерами: Пользовательские форматы. xlsx
Пользовательский формат – числовой формат, создаваемый «с нуля» на специальном языке. Используя коды, применяемые в пользовательских форматах, мы можем создавать собственные варианты отображения чисел, дат, текста в ячейках и сделать наши таблицы наглядными (и нарядными) и настроенными именно под наши задачи.
Формат настраивается в окне формата ячеек – Ctrl + 1 (⌘ + 1) – выбирайте «Все форматы» (Custom) и вводите код формата в поле сверху.
В Google Таблицах: Формат → Числа → Другие форматы чисел (Format → Number → Custom number format).
СИМВОЛЫ, ИСПОЛЬЗУЕМЫЕ В КОДАХ ПОЛЬЗОВАТЕЛЬСКИХ ФОРМАТОВ
0 – незначащие цифры (отображаются всегда)
Если в формате указан один ноль, числа любой разрядности будут отображаться (то есть никакое число не будет «обрезаться»). Но если в формате указано несколько нулей, а числа в ячейках меньшей разрядности – нули все равно будут отображаться (допустим, если в формате пять нулей, а в числе четыре цифры, в ячейке будут отображаться пять цифр – в начале будет ноль).
, – десятичная запятая (знак, отделяющий целую часть от дробной)
В американских региональных настройках и в Google Таблицах при любых настройках – точка, а не запятая.
Используйте ее в формате, если нужно отображать знаки после запятой.
Например:
0,000 – формат, в котором всегда будут отображаться три знака после запятой (даже если число целое).
# – значащие цифры (отображаются, если на этой позиции есть значение)
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, предлагаю вам статью и видео для ознакомления:
Пользовательские числовые форматы в 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): вызов окна, в котором будут видны все существующие правила условного форматирования, можно удалять правила, настраивать их и менять их приоритетность.
«ОБЫЧНЫЕ» ПРАВИЛА
Числа
Рассмотрим работу условного форматирования на примере. Наша задача – выделить зеленым все ячейки с продажами более 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 – крестик.
Если мы хотим, чтобы гистограмма была шириной на весь столбец для какого-то фиксированного значения, а не уже имеющегося в диапазоне максимального значения, то можно поменять это значение.
