ут отображаться.
Положительные; Отрицательные; Ноль; Текст
Пример 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. Как ее найти?
Файл → Параметры → Центр управления безопасностью → Параметры центра управления безопасностью → Надежные расположения
(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).
Если же нужна возможность изменить условие форматирования в любой момент в ячейке, не изменяя правило, нужно вместо числа ввести в окне правила ссылку на ячейку, в которой будет храниться это число. А еще проще — щелкнуть на нее мышкой, тогда ссылка сформируется автоматически.
Обратите внимание на доллары в ссылке на ячейку