Excel как посчитать цветные ячейки в

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

Скажем, я отслеживаю заказы на оборудование для занятий. Желтым цветом фона я обозначаю заказанные вещи, синим — вещи в пути, зеленым — доставленное оборудование:

Моя задача — посмотреть, сколько столов, компьютеров и других расходных материалов находится в пути в данный момент. Цвет — единственное отличие этих чисел в моей таблице. Поэтому я открываю инструмент для суммирования по цвету и использую следующие настройки:

1. Я выбираю весь диапазон со своими данными, чтобы проверить его.
2. Я выбираю C2 в качестве ячейки шаблона, чтобы указать формат элементов, которые я хочу вычислить.
3. Выберите функцию СУММ, чтобы сложить числа из зеленых ячеек.
4. Выберите ячейки, чтобы разместить полученные вычисления для каждого элемента.
5. Чтобы увидеть количество отправленных товаров для каждого продукта, примените функцию к каждой строке.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

С помощью VBA

В файле примера на листе VBA
приведено решение с помощью VBA. Решений может быть множество:

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

Работая с таблицами Excel, часто прибегают к заливке фона или цветному шрифту

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

Тем не менее, такая очевидная задача, как посчитать и суммировать значения с одинаковым цветом, нередко превращается в часы программирования макросов или формул.

Надстройка «Счёт по цвету» мгновенно и без VBA агрегирует значения любого типа цветовой расцветки:

• Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
• Агрегация по цвету заливки и/или цвету условного форматирования
• Cчёт по цвету фона ячеек или цвету шрифта
• Предпросмотр и вставка таблицы результата на рабочий лист

How to Make the Formula Automatically Update On Change

While the formula works perfectly and counts the number of cells with a specific background color, there is one minor irritant.

It does not automatically refresh when you make a change. For example, if I remove the color from one of the cells or I manually add color to one of the cells, then the formula would not automatically update to give me the right result.

Even if I go to the cell that has the formula, go into the edit mode, and hit enter, it would still not recalculate.

The reason for this is because it does not consider this as a change that entails recalculation. I even tried refreshing the workbook and it also did not work (maybe because of some cache issue)

Unfortunately, there is no automatic way to do this, but there is a really simple manual method to do this.

So here is the fix – go to any of the cells in the range, get into the edit mode (press F2 or double-click on the cell), add a space character at the end of the cell content, and hit enter. As soon as you do this, you’ll see that the formula recalculates and gives you the right result.

Now you can go back to the cell where you have made this change by adding a space character and remove it.

While this is inconvenient, as of now this is the only way I could figure out to make sure that we get the correct count of the number of colored cells using this custom formula.

If you’re working with a large data set, this formula may take a few seconds or even a few minutes to calculate the total number of cells with a specific background color. You may see a ‘Loading’ text in the cell during that time (as shown below).

Как закрасить ячейку в Excel двумя цветами?

На вкладке Главная нажмите кнопку вызова диалогового окна Формат ячеек или просто нажмите клавиши CTRL+SHIFT+F. На вкладке Заливка выберите в разделе Цвет фона нужный цвет. Чтобы использовать двухцветный узор, выберите цвет в поле Цвет узора, а затем выберите сам узор в поле Узор.

Как закрасить ячейку в Excel через формулу?

Условия закрашивания ячеек Excel в Windows

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

Как закрасить часть ячейки в Excel?

1. Выделить ячейки левой кнопкой мыши;
2. Кликнуть по выделению правой кнопкой мыши;
3. В появившемся контекстном меню кликнуть по строчке «Формат ячеек… .
4. В появившемся окне формы настроек следует выбрать вкладку «Заливка»;

Как закрасить ячейку в Excel в зависимости от значения?

1. Выделите таблицу или диапазон, в котором Вы хотите изменить цвет заливки ячеек. .
2. Откройте вкладку Home (Главная), в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило).

Как менять цвет ячейки по значению другой ячейки?

Нажимаем кнопку Формат (Format) и переходим на вкладку Заливка (Fill), чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку Другие цвета (More Colors), выберите подходящий и дважды нажмите ОК.

Как разбить ячейку в Excel?

1. В таблице щелкните ячейку, которую нужно разделить.
2. Перейдите на вкладку макет.
3. В группе Объединение нажмите кнопку Разделить ячейки.
4. В диалоговом окке Разделенные ячейки выберите нужное количество столбцов и строк и нажмите кнопку ОК.

Как быстро закрасить ячейку в Excel?

На вкладке «Главная» в группе «Шрифт» нажмите кнопку запуска диалогового окна «Формат ячеек». Можно также нажать клавиши CTRL+SHIFT+F. В диалоговом окне «Формат ячеек» на вкладке «Заливка» в области «Цвет фона» выберите нужный цвет фона.

Как закрасить ячейку в таблице?

1. Вы выберите ячейки, в которых вы хотите добавить или изменить цвет заливки.
2. На вкладке «Таблицы» в области «Стилитаблиц» щелкните стрелку рядом с кнопкой «Заливка».
3. В меню «Заливка» выберите нужный цвет.

Как сделать автоматическую заливку в Excel?

1. Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». .
2. В поле ввода введите формулу:
3. Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца.

Как закрасить ячейки в Excel Горячие клавиши?

Заливка ячеек узором

Сочетание клавиш можно также нажать сочетание клавиш CTRL + SHIFT + F. В диалоговом окне Формат ячеек на вкладке Заливка в группе Цвет фона щелкните цвет фона, который требуется использовать.

Как выделить ячейки участвующие в формуле?

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

Что такое Excel?

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

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

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

Режим “Разработчик” в Excel

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

Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:

Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11.

Появиться окно, в котором выполняется написание кода на языке VBA, то есть фактически создаются пользовательские функции. Я писать их не буду, так как языка VBA не знаю и знать особого желания нет (все знать невозможно).

Какие варианты критериев поиска есть.

В данном пт, на примерах разглядим, какие варианты критериев для поиска можно применять в функции СЧЁТЕСЛИ. Представим, что у нас есть обычная таблица, с текстовыми данными и числами.

Больше (>), меньше(<), больше либо равно (>=), меньше либо равно (<=), равно (=), не равно (<>).

Больше.

Найдем, сколько ячеек в столбце Числа, содержат в для себя значение больше числа 50. Вызываем функцию СЧЁТЕСЛИ в ячейку Е2. В диалоговом окне Аргументы функции, в поле Спектр, указываем спектр С3:С17. Это ячейки столбца Числа, в которых мы будем производить поиск. В поле Аспект, пишем символ больше «>», и число 50.

Формула функции будет смотреться вот так:

Кавычки покажутся автоматом, опосля того, как надавить ОК.

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

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

Меньше.

Используем символ меньше «<».

В поле Аспект, диалогового окна Аргументы функции, пишем: <50.

Формула функции будет смотреться вот так:

В итоге получим количество ячеек, числа в которых меньше 50.

Больше либо равно.

В поле Аспект, диалогового окна Аргументы функции, пишем: >=50.

Формула функции будет смотреться вот так:

В итоге получим количество ячеек, числа в которых больше либо равны 50.

Меньше либо равно.

В поле Аспект, диалогового окна Аргументы функции, пишем: <=50.

Формула функции будет смотреться вот так:

В итоге получим количество ячеек, числа в которых меньше либо равны 50.

Равно.

В поле Аспект, диалогового окна Аргументы функции, пишем: =50.

Формула функции будет смотреться вот так:

В итоге получим количество ячеек, числа в которых равны 50.

Не равно.

В поле Аспект, диалогового окна Аргументы функции, пишем: <>50.

Формула функции будет смотреться вот так:

В итоге получим количество ячеек, числа в которых не равны 50.

Ссылка на ячейку в качестве аспекта поиска функции СЧЁТЕСЛИ в MS Excel.

Может быть применять ссылку на ячейку в качестве аспекта поиска. В нашем примере найдем сколько ячеек в столбце Числа содержат в для себя число 50. Значения в поле Спектр, диалогового окна Аргументы функции, остаётся без конфигураций. В поле Аспект указываем всякую ячейку из обозначенного спектра, которая отвечает нашему аспекту. В нашем пример выберем ячейку С11.

Формула функции будет смотреться вот так:

Кавычки в таком варианте не необходимы.

Жмем ОК. Получаем итог. Количество ячеек, которые содержат то же значение, что и ячейка С11. Три ячейки. Они залиты желтоватым цветом для наглядности.

При внедрение в качестве аспекта поиска ссылку на ячейку, применять знаки: <, >, =, <=, >=, <>, нужно с знаком амперсанда (&), меж этими знаками с самой ссылкой на ячейку.

Принципиальный момент, в данном случае, кавычки около знака больше (>) ставить необходимо вручную.

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

Текстовые значения в качестве аспекта поиска функции СЧЁТЕСЛИ в MS Excel.

В качестве аспекта поиска в поле Аспект, в диалоговом окне Аргументы функции, можно применять текстовое значения. К примеру, можно отыскать в столбце Значения, количество ячеек, которые не содержат в для себя Значение 1. Меняем Спектр поиска.

Формула функции будет смотреться вот так:

Кавычки около знака не равно (<>) ставить вручную.

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

Формула функции будет смотреться вот так:

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

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

В поле Аспект, в диалоговом окне Аргументы функции, указываем: «Ст*».

Формула функции будет смотреться вот так:

Сейчас укажем в аспекты поиска конец слова. К примеру, буковку ф, из слова шкаф.

В поле Аспект, в диалоговом окне Аргументы функции, указываем: «*ф».

Формула функции будет смотреться вот так:

Кавычки около Ст* и *ф, ставятся автоматом.

Варианты использования функции СЧЁТЕСЛИ с 2-мя (несколькими) аспектами поиска описаны в статье: Функция СЧЁТЕСЛИ с внедрением 2-ух (нескольких) критериев поиска. Описание и примеры.

Сумма ячеек по цвету

​ цвету. В строке​ Мы адрес ячейки​ ячейку столбца. Нажимаем​ цифру «Количество». Это​ строках, но по​Ещё примеры счета​ на букву «Р»​ четвертая и восьмая​P.S.​ идейка, надо со​ я совсем понял!​ не установите надстройку​ английской версий.​ нажатию ​ выражения.​ ColorSample.Interior.Color Then Sum​ старше для этого​

​ состояния написано, что​ А4 исправляем на​ левую мышку.​ число заполненных выделенных​ другим условиям. Смотрите​ с условием смотрите​ и заканчивающиеся на​

​ строки.​

• ​уточните вопрос: Вам​ стандартными средствами и​ Прикрепляю файл образец​ как положено, Вам​​Inter_E​F9​Если вам нужно подсчитывать​ = Sum +​ нужно выбрать в​
• ​ выделено 3 строки​ адрес ячейки А1.​Или нажимаем сочетание​​ ячеек. Пустые ячейки​​ в статье «Как​ в статье «Функция​ букву «в», нужно​В ячейке F1​ надо посчитать кол-во​ плюс функциями Прист-а​​ там я написал​ придется запускать её​: у меня в​).​

​ не сумму покрашенных​ cell.Value End If​ меню​​ (ячейки).​​ Получится так.​ клавиш «Ctrl» +​ так Excel не​

​Пятый способ.​ «А». Активная ячейка,​ считает. Но, если​​ каждую вторую ячейку,​​Теперь нам нужно​ — «Р*в» (или​​ закладке «Главная» в​​ вам нужна сумма​ через стандартный COUNT​ Заранее спасибо!​

​ у вас как​

• ​ что наша функция​​ а всего лишь​ = Sum End​
• ​ Редактор Visual Basic​​ данных из других​Посчитать, если выделено цветом​ при этом, находится​

Цвет шрифта

​ нужно посчитать строку,​ строку».​посчитать проценты в Excel​ «Р*В»). Найдет фамилии​ разделе «формулы» -​ во всех желтых​ и пристовским, че​Юрий М​​ help-a нет на​​ «Определенные пользователи» появились​​ перебирает все (и​​ их количество, то​ Function​

Количество вместо суммы

​ (Tools — Macro​ программ в формат​ – формула Excel​ в таблице.​ в которой в​Говорят, что нет​.​ — Рублев, Рылеев,​ «Математические» выбираем функцию​

​ ячейках ?​ то не получается​

Нюансы пересчета

​: Inter_E, Вы читаете,​ этих функциях» -​ эти функций. Знаете​ пустые тоже) ячейки​ наша функция будет​Если теперь вернуться в​ — Visual Basic​ числа Excel, чтобы​.​О других сочетаниях​ столбце А нет​ специальной функции без​В ячейку F3​

​ т.д.​ «СУММЕСЛИ». В появившемся​Ми​ у меня…​ что Вам пишут?​ какую функцию Вам​​ как я сделал,​​ в диапазоне​ еще проще. Замените​ Excel, то в​ Editor)​ формулы считали такие​Можно написать такую​​ клавиш, чтобы выделить​​ значений, а есть​

​ макросов​ напишем формулу, которая​? (знак вопроса)​ диалоговом окне в​: В EXEL нет​​Abram pupkin​​»Если в диапазоне​ пояснить поподробней? Их​ просто 2-раза кликнул​DataRange​ в ней 7-ю​

planetaexcel.ru>

Суммирование ячеек по цвету заливки

Как часто Вы при работе с таблицами окрашиваете ячейки в тот или иной цвет? Желтый


— расходы Транспортного отдела, Красный

— Экономического, Зеленый

— Администрация и т.п.
А потом хочется все эти расходы просуммировать, и не просто просуммировать ВСЕ расходы, а только расходы в ячейках с определенным цветом заливки. Это еще одна нерешенная проблема Excel. Разработчики категорически не хотят встраивать в него хоть какую-то функцию для суммирования данных в ячейках с определенным цветом заливки. Именно это делает данная функция — СуммаЯчеек_Заливка.

Вызов команды через стандартный диалог:

Вызов с панели MulTEx:

Сумма/Поиск/Функции
— Математические
— СуммаЯчеек_Заливка

Синтаксис:

=СуммаЯчеек_Заливка($E$2:$E$20 ; $E$7 ; I3 ; $A$2:$A$20)

В принципе, данная функция аналогична по сути стандартной СУММЕСЛИ
, только в качестве основного критерия здесь ячейка с заливкой. Но можно указать и привычный критерий — значение ячейки, в таком случае суммироваться будут ячейки с указанным цветом и критерием.

ДиапазонСуммирования
($E$2:$E$20)- диапазон значений для суммирования. Можно указать несколько столбцов. Столбец с критерием(если планируется суммировать еще и по критерию)
не обязательно должен входит в диапазон.

ЯчейкаОбразец
($E$7) — ячейка-образец заливки. Ссылка на ячейку с цветом заливки.

Критерий
(I3) — необязательный аргумент. Если указан, то суммируются ячейки с указанным критерием и цветом заливки. Допускается применение в критерии символов подстановки — «*
» и «?
» . Если не указан, то суммируются все ячейки с указанным цветом заливки. Если в диапазоне суммирования находятся ячейки с текстом, то они будут игнорироваться.
Так же данный аргумент может принимать в качестве критерия символы сравнения (, =,):

• «>0» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше нуля;
• «>=2» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше или равно двум;
• «0» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не равно нулю;
• «» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не пустые;

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: «»&D$1

ДиапазонКритерия
($A$2:$A$20) — Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан)
. ДиапазонКритерия
должен быть равен по количеству ячеек ДиапазонуСуммирования
. Если ДиапазонКритерия
не указан, то критерий просматривается в ДиапазонеСуммирования
.

ИспУФ
() — Необязательный аргумент. Допускается указание логических значений ИСТИНА(TRUE) или ЛОЖЬ(FALSE). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет суммировать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет суммировать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца
— то её значение не будет суммироваться.

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

Функции для суммирования значений по цвету ячеек в EXCEL не существует (по крайней мере, в EXCEL 2016 и в более ранних версиях). Вероятно, подавляющему большинству пользователей это не требуется.

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

Необходимо сложить значения ячеек в зависимости от цвета фона. Основная задача: Как нам «объяснить» функции сложения, что нужно складывать значения, например, только зеленых ячеек?

Это можно сделать разными способами, приведем 3 из них: с помощью , Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA.

Обновить результаты

Вы помните, что в Google Таблицах нет функций для работы с цветом? Это означает, что изменения в форматировании ячеек не считаются причиной для повторного расчета результатов формулы. Хорошая новость заключается в том, что оба обходных пути, которые вы можете использовать, очень просты.

1. Вы можете изменить любое значение в расчетном диапазоне. Скажем, вы считаете зеленые ячейки в A1:C254. Вы можете просто добавить символ в любую из ячеек этого диапазона, а затем удалить его, чтобы получить обновленные результаты.
2. Если у вас много значения по цвету формул на вашем листе, щелкните параметр «Обновить», который находится прямо под функцией по цвету в Power Tools, чтобы обновить все формулы за один раз.

Свойство .Interior.ColorIndex объекта Range

До возникновения Excel 2007 была лишь ограниченная гамма для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в истинное время. Любому цвету в данной для нас гамме присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу либо вывести сообщение о нем можно при помощи характеристики .Interior.ColorIndex:

Пример кода 5:

Просмотреть ограниченную гамму для заливки ячеек фоном можно, запустив в VBA Excel простой макрос:

Пример кода 6:

Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита подходящим индексу фоном.

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

Зачем считать количество закрашенных ячеек?

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

1. Анализ данных:

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

2. Подготовка отчетов:

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

3. Проверка целостности данных:

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

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

Наборы значков

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

1. Откройте наборы значков и ознакомьтесь со всеми доступными. Присутствуют различные направляющие в виде стрелок, фигуры разных цветов, индикаторы и оценки.

2. Настроек для этого правила по умолчанию нет, поэтому при выборе значки сразу же появляются в своих клетках. Я выбрал шкалу с несколькими делениями, которые заполняются в зависимости от величины значения в текущем диапазоне. Вы же можете протестировать абсолютно все наборы значков.

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