Как делать колор кодинг в экселе

Обновлено: 04.07.2024

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

В Excel есть несколько встроенных числовых форматов. Их можно использовать без изменения или в качестве основы для создания собственных числовых форматов. При создании пользовательских числовых форматов можно указать до четырех разделов кода формата. Они определяют формат положительных чисел, отрицательных чисел, нулевых значений и текста (в указанном порядке). Разделы кода должны быть разделены точкой с запятой (;).

В следующем примере показаны четыре типа разделов кода формата:

Формат положительных чисел

Формат отрицательных чисел

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

Создание кода пользовательского формата

На вкладке Главная щелкните Числовой формат и выберите Другие числовые форматы.

В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

В списке Тип выберите числовой формат, который нужно настроить.

Выбранный формат отображается вверху списка в поле Тип.

В поле Тип внесите необходимые изменения в выбранный числовой формат.

Советы по созданию кодов форматирования

Чтобы в ячейке отображался текст и числа, заключите текстовые знаки в двойные кавычки (" ") или поставьте перед отдельным символом обратную косую черту (\). Добавьте знаки в нужный раздел кода формата. Например, введите формат "Избыток "0,00 ₽;"Дефицит "-0,00 ₽, чтобы положительные значения отображались в формате "Избыток 125,74 ₽", а отрицательные — в формате "Недостаток -125,74 ₽".

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

Левая круглая скобка

Правая круглая скобка

Левая фигурная скобка

Правая фигурная скобка

Чтобы создать числовой формат, содержащий текст, который вводится в ячейке, вставьте в текстовом разделе кода числового формата символ "@" в том месте, где в ячейке должен будет отображаться введенный текст. Если в текстовом разделе числового формата отсутствует символ "@", в ячейке не будет отображаться введенный текст (будут видны только числа). Вы также можете создать числовой формат, который объединяет определенные текстовые символы с текстом, вводимым в ячейке. Для этого необходимые текстовые символы следует ввести перед символом "@", после символа "@" или и перед ним, и после него. Затем введенные текстовые символы нужно заключить в двойные кавычки (" "). Например, чтобы вставить текст перед текстом, вводимым в ячейке, введите "валовой доход для "@ в текстовом разделе кода числового формата.

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

Чтобы число заполнило ячейку по всей ширине за счет повторения определенного символа, поставьте в коде числового формата перед этим символом звездочку (*). Например, можно ввести 0*–, чтобы заполнить пунктиром всю ячейку справа от числа, или *0 перед любым форматом, чтобы отобразить начальные нули.

Добавление заливки ячеек делает ваш документ Excel более понятным, добавляя наглядности для анализа данных. Часто возникает потребность посчитать количество ячеек с заданным цветом заливки.

К сожалению, Excel не имеет встроенной функции, которая использует цвета заливки в качестве условий в формулах. Альтернативой является использование собственных функций (написанный на VBA). Надстройка VBA-Excel содержит функцию СЧЁТЗАЛИВКА, с помощью которой можно это реализовать.

Описание функции

Функция =СЧЁТЗАЛИВКА(ДИАПАЗОН, ЯЧЕЙКА) имеет два обязательных аргумента:

ДИАПАЗОН ячеек, где необходимо произвести подсчет ячеек с определенной заливкой.
ЯЧЕЙКА ячейка с цветом заливки которые необходимо посчитать.

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

Замечание об автоматическом вычислении. Так как пересчет формул в Excel автоматически не происходит при изменении заливки ячеек, то данная функция не пересчитывается автоматически. Для того, чтобы принудительно пересчитать формулы активного листа используйте сочетание клавиш Ctrl+Alt+F9.

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

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

Для этих целей необходимо использовать очень простенький макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам вытягивать числовой код цвета заливки и далее по этому коду мы и будет считать общее количество ячеек, используя приемы, описываемые в статье как посчитать в Excel количество ячеек/значений

Итак, приступим. Зайдите в редактор Visual Basic, для этого:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик, далее выберите Visual Basic

Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик

После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module

и скопируйте туда текст простой функции:

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

После этого закройте редактор Visual Basic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =ColorNom ( A1 ), где A1 — это наша ячейка, в которой нам необходимо определить индекс цвета.

После этого уже не составит труда посчитать количество ячеек или значений в зависимости от цвета ячейки. Используйте нашу статью как посчитать в Excel количество ячеек/значений

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

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

=ColorNom (A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.

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

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

Считать количество мы будем с помощью функции СЧЁТЕСЛИ

Вот так выглядят аргументы данной функции

=СЧЁТЕСЛИ( диапазон ; критерий )

=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )

Диапазон мы указали со знаком доллара, чтобы он был закреплен и можно было протянуть формулу. Критерия у нас встречается всего три и они указаны в нашей вспомогательной таблице. Протянем формулу и получим количество ячеек по цветам.

Из этой статьи Вы узнаете, как в Excel посчитать количество и сумму ячеек определенного цвета. Этот способ работает как для ячеек, раскрашенных вручную, так и для ячеек с правилами условного форматирования. Кроме того, Вы научитесь настраивать фильтр по нескольким цветам в Excel 2010 и 2013.

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

Как известно, Microsoft Excel предоставляет набор функций для различных целей, и логично предположить, что существуют формулы для подсчёта ячеек по цвету. Но, к сожалению, не существует формулы, которая позволила бы на обычном листе Excel суммировать или считать по цвету.

Если не использовать сторонние надстройки, существует только одно решение – создать пользовательскую функцию (UDF). Если Вы мало знаете об этой технологии или вообще никогда не слышали этого термина, не пугайтесь, Вам не придётся писать код самостоятельно. Здесь Вы найдёте отличный готовый код (написанный нашим гуру Excel), и всё, что Вам потребуется сделать – это скопировать его и вставить в свою рабочую книгу.

Как считать и суммировать по цвету на листе Excel

Предположим, у Вас есть таблица заказов компании, в которой ячейки в столбце Delivery раскрашены в зависимости от их значений: Due in X Days – оранжевые, Delivered – зелёные, Past Due – красные.

Теперь мы хотим автоматически сосчитать количество ячеек по их цвету, то есть сосчитать количество красных, зелёных и оранжевых ячеек на листе. Как я уже сказал выше, прямого решения этой задачи не существует. Но, к счастью, в нашей команде есть очень умелые и знающие Excel гуру, и один из них написал безупречный код для Excel 2010 и 2013. Итак, выполните 5 простых шагов, описанных далее, и через несколько минут Вы узнаете количество и сумму ячеек нужного цвета.

Откройте книгу Excel и нажмите Alt+F11, чтобы запустить редактор Visual Basic for Applications (VBA).
Правой кнопкой мыши кликните по имени Вашей рабочей книги в области Project – VBAProject, которая находится в левой части экрана, далее в появившемся контекстном меню нажмите Insert >Module.
Вставьте на свой лист вот такой код:

Сохраните рабочую книгу Excel в формате .xlsm (Книга Excel с поддержкой макросов).Если Вы не слишком уверенно чувствуете себя с VBA, то посмотрите подробную пошаговую инструкцию и массу полезных советов в учебнике Как вставить и запустить код VBA в Excel.
Когда все закулисные действия будут выполнены, выберите ячейки, в которые нужно вставить результат, и введите в них функцию CountCellsByColor:

CountCellsByColor( диапазон , код_цвета )

В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это диапазон, содержащий раскрашенные ячейки, которые Вы хотите посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красный.

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

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

SumCellsByColor( диапазон , код_цвета )

Как показано на снимке экрана ниже, мы использовали формулу:

где D2:D14 – диапазон, A17 – ячейка с образцом цвета.

Таким же образом Вы можете посчитать и просуммировать ячейки по цвету шрифта при помощи функций CountCellsByFontColor и SumCellsByFontColor соответственно.

Замечание: Если после применения выше описанного кода VBA Вам вдруг потребуется раскрасить ещё несколько ячеек вручную, сумма и количество ячеек не будут пересчитаны автоматически после этих изменений. Не ругайте нас, это не погрешности кода

На самом деле, это нормальное поведение макросов в Excel, скриптов VBA и пользовательских функций (UDF). Дело в том, что все подобные функции вызываются только изменением данных на листе, но Excel не расценивает изменение цвета шрифта или заливки ячейки как изменение данных. Поэтому, после изменения цвета ячеек вручную, просто поставьте курсор на любую ячейку и кликните F2, а затем Enter, сумма и количество после этого обновятся. Так нужно сделать, работая с любым макросом, который Вы найдёте далее в этой статье.

Считаем сумму и количество ячеек по цвету во всей книге

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

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

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

Пользовательские функции для определения кодов цвета заливки ячеек и цвета шрифта

Здесь Вы найдёте самые важные моменты по всем функциям, использованным нами в этом примере, а также пару новых функций, которые определяют коды цветов.

Замечание: Пожалуйста, помните, что все эти формулы будут работать, если Вы уже добавили в свою рабочую книгу Excel пользовательскую функцию, как было показано ранее в этой статье.

Функции, которые считают количество по цвету:

CountCellsByColor( диапазон , код_цвета ) – считает ячейки с заданным цветом заливки.В примере, рассмотренном выше, мы использовали вот такую формулу для подсчёта количества ячеек по их цвету:

где F2:F14 – это выбранный диапазон, A17 – это ячейка с нужным цветом заливки.

Все перечисленные далее формулы работают по такому же принципу.

Функции, которые суммируют значения по цвету ячейки:

SumCellsByColor( диапазон , код_цвета ) – вычисляет сумму ячеек с заданным цветом заливки.
SumCellsByFontColor( диапазон , код_цвета ) – вычисляет сумму ячеек с заданным цветом шрифта.

Функции, которые возвращают код цвета:

Итак, посчитать количество ячеек по их цвету и вычислить сумму значений в раскрашенных ячейках оказалось совсем не сложно, не так ли? Но что если Вы не раскрашиваете ячейки вручную, а предпочитаете использовать условное форматирование, как мы делали это в статьях Как изменить цвет заливки ячеек и Как изменить цвет заливки строки, основываясь на значении ячейки?

Как посчитать количество и сумму ячеек по цвету, раскрашенных при помощи условного форматирования

Конечно, Вы можете найти тонны кода VBA в интернете, который пытается сделать это, но все эти коды (по крайней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:

Format all cells based on their values (Форматировать все ячейки на основании их значений);
Format only top or bottom ranked values (Форматировать только первые или последние значения);
Format only values that are above or below average (Форматировать только значения, которые находятся выше или ниже среднего);
Format only unique or duplicate values (Форматировать только уникальные или повторяющиеся значения).

Кроме того, практически все эти коды VBA имеют целый ряд особенностей и ограничений, из-за которых они могут не работать корректно с какой-то конкретной книгой или типами данных. Так или иначе, Вы можете попытать счастье и google в поисках идеального решения, и если Вам удастся найти его, пожалуйста, возвращайтесь и опубликуйте здесь свою находку!

Код VBA, приведённый ниже, преодолевает все указанные выше ограничения и работает в таблицах Microsoft Excel 2010 и 2013, с любыми типами условного форматирования (и снова спасибо нашему гуру!). В результате он выводит количество раскрашенных ячеек и сумму значений в этих ячейках, независимо от типа условного форматирования, применённого на листе.

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

Для этого примера мы выбрали столбец Qty. и получили следующие цифры:

Count – это число ячеек искомого цвета; в нашем случае это красноватый цвет, которым выделены ячейки со значением Past Due.
Sum – это сумма значений всех ячеек красного цвета в столбце Qty., то есть общее количество элементов с отметкой Past Due.
Color – это шестнадцатеричный код цвета выделенной ячейки, в нашем случае D2.

Рабочая книга с примерами для скачивания

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

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

Все функции являются функциями пользователя(UDF), поэтому прежде чем с ними работать необходимо разрешить макросы. Так же следует знать, что все такие функции содержатся в категории Определенные пользователем (User Defined) Мастера функций:

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

Summ_Color.zip (2,5 MiB, 2 894 скачиваний)

В архиве расположен файл надстройки и файлы справки. Прежде чем установить надстройку, необходимо её распаковать из архива на жесткий диск, после чего установить. Как установить надстройку: Установка надстроек

Надстройка распространяется бесплатно и с открытыми исходными кодами - смотрите, изучайте, меняйте под себя.

Статья помогла? Сделай твит, поделись ссылкой с друзьями!

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

В новых версиях Excel поддерживает более 4 млн. цветов. Настроить это богатство очень легко - идем на вкладку Разметка страницы - Цвета (Page Layout - Colors) и выбираем любую радующую вас палитру:

Цвета заботливо подобраны в гармоничные сочетания, эффект от применения видно уже при наведении мыши. Выбранная палитра будет применяться ко всем объектам (листам, ячейкам, диаграммам и т.д.) в пределах книги. Для маньяков мастеров дизайна в конце списка есть опция Создать новые цвета темы (New Theme Colors) , где можно попытаться выпилить свою палитру (например, с корпоративными цветами компании):

Excel 2003 и старше

Старые версии Excel имели очень ограниченную палитру - только 56 цветов. Это количество нельзя увеличить, но можно заменить стандартные цвета в этой палитре на свои, нестандартные. Для этого идем в меню Сервис - Параметры - вкладка Цвет (Tools - Options - Color) и меняем любой выбранный цвет стандартной палитры на свой собственный кнопкой Изменить (Edit) :

Обладая некоторым терпением, можно добиться, например, следующего результата:

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

Иногда цвета стандартной палитры сбиваются (например, после выгрузки из 1С в Excel). Кнопка Сброс (Reset) вам поможет.

У меня новый Excel 2007-2010, но я хочу цвета как в старом Excel 2000-2003

Тяжелый случай. Но бывает. Я с этим столкнулся, помнится, когда делал проект для клиента, работающего до сих пор на Excel 2003. Красивая палитра Excel 2010 превращалась в его файлах в непонятную цветовую кашу: зеленый мог стать желтым, а красный - оранжевым. Видимо, при сохранении в формате Excel 97-2003 преобразование из 4 млн. цветов в 65 бесследно не проходило.

Выход нашелся - бесплатная надстройка Classic Excel Colors от Andrew Engwirda, MVP Excel. Надстройка добавляет вам на ленту кнопку, открывающую окно с "теми-самыми старыми добрыми" цветами из палитры Excel 2003. Если использовать их, то цветовых искажений при работе с новыми файлами в старых версиях не будет:

Читайте также: