Как правильно использовать стили в Excel

Что такое стиль

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

Весь список стилей доступен на вкладке Главная в группе Стили (Home - Styles):

Список стилей Excel

Важно отметить, что стиль - это не только внешнее оформление (цвет, шрифт, заливка), как многие думают. В стиле ячейки Excel можно сохранить:

  • Числовой формат (денежный, процентый, даты/времени...), в том числе и пользовательский (кг, шт, °С...)
  • Параметры выравнивания (по горизонтали и вертикали), направление текста, перенос по словам.
  • Параметры шрифта (цвет, начертание, размер...)
  • Цвет и тип заливки (сплошная, градиент)
  • Обрамление ячейки (тип, толщина и цвет линий с каждой стороны и внутри)
  • Параметры защиты (нужно ли защищать ячейку и прятать в ней формулы при включении защиты листа)

Как создать стиль

Выделите ячейку, оформление которой хотите сохранить в стиле (или пустую ячейку, если хотите создать стиль "с нуля") и на вкладке Главная в группе Стили выберите команду Создать стиль ячейки (New cell style).

Создание нового стиля

В открывшемся окне можно отметить флажками, какие именно параметры ячейки должны сохраняться в новом стиле и, нажав кнопку Формат (Format), настроить их более детально.

Чтобы создать стиль на основе уже имеющегося стиля, можно просто щёлкнуть правой кнопкой мыши по нужному стилю и выбрать команду Дублировать (Duplicate):

Дублирование стиля

Там же в контекстном меню любой стиль можно удалить или изменить его параметры.

Всё весьма несложно и даже слегка скучновато, не правда ли? Так что давайте перейдем к практическим примерам.

Стиль для целых чисел с разделителями

Начнем простого для затравки.

На Главной вкладке в группе Число есть кнопки для быстрого применения основных числовых форматов, которые вам, я уверен, хорошо знакомы:

Основные форматы

Лично меня всегда безмерно удивляло, почему в этом наборе нет самого нужного - формата, где отображается только целая часть числа с пробелом через три разряда? Денежный - есть, процентный - есть, а вот удобного формата для обычных чисел - почему-то нет.

Кстати, третья иконка со значком "000", про которую обычно думают в этом случае, делает совершенно не то, что нужно - она не убирает дробную часть, да ещё и зачем-то выводит вместо нуля прочерк, как в финансовом формате. Совсем не оно.

Давайте устраним этот недостаток, создав свой стиль. Выбираем, как было описано выше, последовательно Главная - Стили - Создать стиль ячейки (Home - Styles - New cell style). В окне создания стиля введём его имя и снимем все галочки, кроме первой, чтобы создаваемый нами стиль не влиял ни на какие другие параметры ячейки, кроме числового формата. Затем жмём кнопку Формат (Format) и в знакомом окне выбираем Числовой (Number) без десятичных знаков и с разделителем групп разрядов:

Формат для целых чисел

Жмём на все ОК и теперь у нас есть стиль для наглядного форматирования чисел - красота!

Применяем числовой стиль

Стили для своих единиц измерения (шт, чел, упак, кг...)

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

Выполняем те же действия, что и в предыдущем пункте, но в окне Формат ячеек выбираем на вкладке Число опцию (все форматы). В поле Тип (Type) вводим маску (шаблон) для нашего пользовательского формата:

Стиль ШТ


# ##0" шт"

Такой шаблон говорит Excel, что мы хотим выводить только целую часть числа и добавить к ней пробел и ШТ. Обратите внимание, что первый пробел здесь нужен для разделения тысяч-миллионов-миллиардов, а второй - чтобы число и текст не слиплись. И вот этот второй должен обязательно находиться внутри кавычек вместе с ШТ.

После применения такого стиля получим красивое представление для штук:

Применяем стиль ШТ

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

# ##0,000" шт"

Стили для тысяч (К) и миллионов (М)

Ещё один пример весьма распространённой задачи - компактное представление для очень больших чисел. Вы наверняка видели варианты для тысяч с буквой "К" (1289 = 1,3К), для миллионов с буквой "М" (1 595 000 = 1,6М) или что-то похожее. Чтобы создать такие стили нам опять потребуется пользовательский формат, но с немного другим шаблоном:

Стили ТЫС и МЛН

Обратите внимание на пробел (без кавычек) после нуля - именно он, являясь разделителем разрядов, округляет наше исходное значение до тысяч. Если поставите два пробела - получите округление до миллионов, три - до миллиардов и т.д.

Если нужно показать после запятой один или несколько знаков (например 3,5K или 5,23М), то добавляем нули, как в прошлом примере:

# ##0,0 "K"

# ##0,00  "M"

Вместо международных "К" и "М" можно, само-собой, использовать и наши привычные " тыс.руб" и " млн.$" - кому как привычнее.

Стиль с цветом и процентами для отклонения от плана

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

Таблица План-Факт

Давайте создадим для последнего столбца особый стиль, который будет:

  • Положительные значения выводить зелёным цветом шрифта, со знаком плюс перед значенем процентов.
  • Отрицательные значения - красным цветом и с минусом, соответственно.

Создаём стиль как и ранее, а вот маска формата будет хитрая:

Шаблон для процентов

[Цвет10]+0%;[Красный]-0%;0%

Давайте разберём её подробнее:

  • Маска формата здесь состоит из 3 фрагментов, разделенных точкой с запятой. Первая применяется, если число в ячейке больше нуля, вторая - если меньше нуля, третья - если число равно нулю.
  • Отклонения от плана мы хотим указывать явно - с плюсом (перевыполнение) и минусом (невыполнение), поэтому знаки плюса и минуса явно прописываем в шаблоне.
  • Перед маской в каждом блоке можно указать в квадратных скобках цвет шрифта - словом (красный, синий, зеленый, желтый, черный, белый) или с помощью кода (Цвет1, Цвет2 и т.д.) Всего поддерживается 56 цветов - выбирайте любой на ваш вкус:

    Справочник кодов цветов Excel

После применения созданного стиля картинка станет существенно приятнее:

Примененный стиль отклонения в процентах

Стили со значками и спецсимволами

Чтобы сделать ещё красивее, можно вместо плюса и минуса выводить символы стрелки вверх и вниз, соответственно. Для этого выделим любую пустую ячейку на листе и вставим туда наши значки, через команду Вставка - Символ (Insert - Symbol):

Вставляем стрелки

Затем выделим вставленные символы в ячейке или в строке формул и скопируем их с помощью сочетания Ctrl+C.

Теперь, при создании стиля для отличий из предыдущего пункта, останется вставить с помощью Ctrl+V эти символы в нашу маску формата вместо плюса и минуса:

Создаем стиль со стрелками

[Цвет10]↑0%;[Красный]↓0%;0%

После применения такого стиля наше отклонение от плана станет ещё нагляднее:

Отклонение от плана со стрелками

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

Нестандартные ед.измерения

Стили со сложными условиями и эмодзи

Предположим, что у нас есть таблица с результатами экзаменов студентов или аттестации сотрудников:

Оценки

Задача - показать рядом с каждым результатом его графическое представление в виде иконки (эмодзи) по следующей логике:

  • больше или равно 80 баллов - отлично
  • от 40 до 80 - хорошо
  • меньше 40 - плохо

Создаем новый стиль, где в шаблоне числового формата задаём:

Маска формата для стиля с эмодзи

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

Эмодзи легко вставляются сочетанием клавиш Win+точка, если у вас Windows 10 со всеми обновлениями. Если же у вас более древняя операционная система, то придется их заранее вставить через Вставка - Символ из шрифтов типа Webdings, Windings и т.п. и скопировать, как мы делали в предыдущем примере.

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

Стиль с эмодзи

Песня!

Стиль для полей формы с защитой

Представьте, что вам нужно создать защищенную паролем форму для ввода данных пользователем. Для ячеек ввода лучше создать стиль с определенным форматированием (чтобы сразу было понятно, куда можно вводить данные) и отключенной блокировкой (чтобы не мешала защита листа):

Форма ввода

Для этого при создании стиля убираем все флажки, кроме первого и, нажав на кнопку Формат (Format) зададим характерный дизайн (например, жёлтую заливку, курсив, выравнивание по центру) и на вкладке Защита (Protection) снимем галочку Защищаемая ячейка (Locked).

Как лучше хранить стили

Все созданные пользовательские стили хранятся в текущей книге. Если вы хотите использовать их в других своих файлах, то есть три варианта:

  • Копировать формат ячеек с помощью инструмента Формат по образцу ("кисточка" или Format Painter).
  • Подгрузить в рабочую книгу все стили из другой книги с помощью команды Главная - Стили - Объединить стили (Home - Styles - Merge styles)

    Объединение стилей
  • Использовать инструмент автоматической загрузки стилей из файла-библиотеки в надстройке PLEX последней версии.

Идеи для продолжения

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

  • Стили с корпоративными цветами и шрифтами вашей компании или бренда.
  • Стили с валютами, которых нет на Главной и которые долго ставить через окно Формат ячеек (например, тенге, сомони, сом и др. валюты союзных государств).
  • Стили с маркерами для имитации маркированных списков в Excel как в Word.
  • Стили для скрытия данных "белым на белом" или с помощью маски из трех подряд точек с запятой.
  • Стили для отображения отрицательных чисел без минуса, но в скобках (как это принято в финансах для расходов).
  • Стили для отображения исходных данных, вычислений и ячеек со ссылками в финансовых моделях.
  • ... и т.д.

Дальше - сами.

Делитесь полезными стилями в комментариях - прогрессивное человечество и карма скажут вам "спасибо" :)

Ссылки по теме




Николай, добрый день!
Подскажите, какую маску необходимо использовать в случае вывода 1го знака после запятой в СЛУЧАЕ не целого значения И число без запятой в СЛУЧАЕ целого числа? Например: 10 и 10,2 или 63,5 и 63
11.10.2020 11:09:46
Боюсь, что не знаю такого способа. Если он есть :)
12.10.2020 12:41:59
а разве "Основной" не подойдет?
просто будет без разделителей разрядов.
07.10.2020 16:44:20
Добрый день! Как сохранить стиль? Открываю книгу, а там нет моего стиля
07.10.2020 22:01:03
Татьяна, после создания своего стиля, необходимо сохранить книгу в которой оно был создан.
Стиль сохраняется только в той книге, в которой он изначально был создан.
Если требуется добавить стили в новую книгу, то их необходимо скопировать из книги в которой они были созданы.
Как это выполнить, Николай рассказывает в своём видео.
07.10.2020 23:52:02
Предпоследний пункт в этой статье :)
07.10.2020 17:10:02
Николай, спасибо! Отличная тема! Спасибо. Очень нужная.
Есть вопрос по теме:
Какой формат ячейки со временем применить чтобы время меньше 04:00 окрашивалась красным?
Пробовал разные варианты форматов например:
[<4][Красный][ч]:мм;[ч]:мм,
[<0,166666666666667][Красный][ч]:мм;[ч]:мм
но у мени ни чего не получилось.

Сделал [Красный][<=0,165972222222222][ч]:мм;[ч]:мм и получилось как надо.
07.10.2020 21:55:36
Супер!
В общем как обычно)
11.10.2020 11:07:49
Спасибо! :)
08.10.2020 08:17:10
Доброе утро! Спасибо за ответы. Все понятно. Хотелось бы сохранить созданные мною стили на все книги.
Самые лучшие видео это у Николая! Вы очень харизматичный и артистичный, а главное все четко.
Спасибо! Всем удачного дня!
11.10.2020 11:07:39
Спасибо, Татьяна!
Чтобы стили были доступны всегда их нужно добавить в шаблон book.xlt.
Вот тема на нашем форуме, где это обсуждалось.
10.10.2020 08:13:45
Скачал ваш пример и все стили сохранились. Теперь когда я открываю любые экзель файлы, ваши стили мне мешают. Как удалить их так чтобы они удалились совсем?

Я удаляю по 1 - открываю новую книгу экзель но они не удаляются.
Попробовал макросом удалить все стили - открываю новую книгу экзель но они не удаляются.


Что делать?

Вохможно-ли удалить пользовательские стили из корневой папки экзель чтобы они удалились насовсем?
11.10.2020 11:03:42
Очень странно.
А перезагрузка ПК не помогает, часом?
12.10.2020 07:18:08
Здравствуйте!
К сожелению не помогает.
С макросом удаляется стили в конкретной книге. Когда открываете другую книгу экзель, стили надо заного удалить((
12.10.2020 09:56:58
Очень-очень странно. Стили хранятся внутри конкретного файла. Если у вас при открытии любого файла Excel внутри уже есть какие-то нестандартные стили - это очень странно. Не понимаю, как такое может быть возможно в принципе.

Попробуйте переустановить Office для начала.
13.10.2020 09:54:02
Спасибо Николай. Все удалились)
12.10.2020 17:09:34
Добрый день, Николай!
Благодарю Вас за ценные, простые, наглядные уроки!!

Подскажите, пожалуйста, почему не получается:
1) Цветными смайлики не все становятся (сделал в точности, как у Вас в образце) - скриншот  прикрепляю
https://ibb.co/Sm2hcnY 
2) Такую формулировку Excel не принимает: [Красный][>100]"😎";[Цвет10][>=80]"🙂";[Цвет45][>=40]"😐";[Красный]"☹"
20.10.2020 12:04:10
Николай, добрый день!

А можно ли как-то сделать, чтобы эмоджи/значки окрашивались, а цифры нет/оставались черными?

Пока только вижу способ создания рядом колонки, где через "=" повторять значения и делать формат без цифр для этой колонки.
Наверх