Нужно выделить повторяющиеся значения в столбце? Надо выделить первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро. В Excel за выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем ниже:

Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование,

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите возможностей здесь действительно много.

Теперь подробнее о самых полезных:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:

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

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

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

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой = , т.е. у в отдельной ячейке у вас будет написано ИвановИванИваныч, тогда по этому столбцу вы сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться Excel сочтет такие строки неповторяющимися.

Выделение цветом первых/последних значений. Опять же условное форматирование

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

Построение термальной диаграммы и гистограммы

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

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

Выделение цветом ячеек содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = , но проще и быстрее применить в условное форматирование, пройдите — Правила отбора ячеек — Текст содержит (см. картинку 2).

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

Excel выделение цветом. Фильтр по цвету

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

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

Инструкция для Excel 2010


ВКЛЮЧИТЕ СУБТИТРЫ!

Как это сделать в Excel 2007


ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».

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


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


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


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


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


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

Как это сделать в Excel 2003


ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.


Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!

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

Один щелчок для подсчета, суммирования и усреднения цветных ячеек в Excel

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

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

1 , Выберите пустые ячейки, чтобы войти в функцию SUBTOTAL.

  1. Чтобы подсчитать все ячейки с одинаковым цветом фона, введите формулу = ВСЕГО (102, E2: E20) ;
  2. Чтобы сложить все ячейки с одинаковым цветом фона, введите формулу = ВСЕГО (109, E2: E20) ;


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

2 , Выберите заголовок таблицы и нажмите дата > Фильтр , Смотрите скриншот:

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

После фильтрации обе СУБТОТАЛЬНЫЕ формулы автоматически подсчитывают и суммируют все отфильтрованные цветовые ячейки в столбце Количество. Смотрите скриншот:

Внимание : Этот метод требует, чтобы цветные ячейки, которые вы будете считать или суммировали, находились в одном столбце.

Подсчет или суммирование цветных ячеек с помощью функции GET.CELL

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

1 . Щелчок Формулы > Определить имя , Смотрите скриншот:

2 , В диалоговом окне «Новое имя» сделайте, как показано ниже:
(1) Введите имя в поле «Имя»;
(2) Введите формулу = GET.CELL (38, Sheet4! $ E2) в поле Относится к (внимание : в формуле, 38 означает вернуть код ячейки, и Sheet4! $ E2 является первой ячейкой в ​​столбце Сумма, кроме заголовка столбца, который необходимо изменить на основе данных таблицы.)
(3) Нажмите OK Кнопка.

3 , Теперь добавьте новый столбец Color прямо к исходной таблице. Далее введите формулу = NumColor и перетащите маркер автозаполнения, чтобы применить формулу к другим ячейкам в столбце «Цвет». Смотрите скриншот:
Внимание : В формуле, NumColor это именованный диапазон, который мы указали в первых шагах 2. Вам нужно изменить его на указанное вами имя.

Теперь код цвета каждой ячейки в столбце Количество возвращается в столбце Цвет. Смотрите скриншот:

4 , Скопируйте и укажите цвет заливки в пустом диапазоне на активном листе и введите формулы рядом с ним, как показано на снимке экрана ниже:
А. Для подсчета клеток по цвету, пожалуйста, введите формулу = COUNTIF ($ F $ 2: $ F $ 20, NumColor) ;
B. Для суммирования ячеек по цвету, пожалуйста, введите формулу = СУММЕСЛИ ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20) .

Внимание : В обеих формулах $ F $ 2: $ F $ 20 столбец Цвет, NumColor указанный именованный диапазон, $ E $ 2: $ E $ 20 это столбец суммы, и вы можете изменить их, как вам нужно.

Теперь вы увидите, что ячейки в столбце Количество подсчитываются и суммируются по цветам заливки.

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

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

1 , Удерживайте ALT + F11 и открывает Microsoft Visual Basic для приложений окна.

2 . Щелчок Вставить > модуль , и вставьте следующий код в окно модуля.

VBA: подсчет и суммирование ячеек в зависимости от цвета фона:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function

3 , Затем сохраните код и примените следующую формулу:
А. Подсчитайте цветные клетки: = Colorfunction (А, В, С, значение FALSE)
Б. Суммируйте цветные ячейки: = Colorfunction (A, B, C, TRUE),

Примечание: в приведенных выше формулах A является ячейка с конкретным цветом фона, который вы хотите рассчитать, подсчет и сумма, и ДО НАШЕЙ ЭРЫ диапазон ячеек, в котором вы хотите рассчитать количество и сумму.

4 , Например, сделайте следующий скриншот, введите формулу= Colorfunction (A1, A1: D11, FALSE) для подсчета желтых клеток. И используйте формулу = Colorfunction (A1, A1: D11, TRUE) суммировать желтые клетки. Смотрите скриншот:

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

Подсчет и суммирование ячеек на основе определенного цвета заливки с помощью функций Kutools

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

1 , Выберите пустую ячейку, в которую вы помещаете результаты подсчета, и нажмите Kutools > Функции Kutools > > COUNTBYCELLCOLOR , Смотрите скриншот:

2 , В диалоговом окне «Аргументы функции» укажите диапазон, в котором вы будете считать цветные ячейки в Справка выберите ячейку, которая заполнена указанным цветом фона в Color_index_nr и нажмите OK кнопка. Смотрите скриншот:

Заметки:
(1) Вы также можете ввести указанную функцию Kutools = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) непосредственно в пустой ячейке или строке формул, чтобы получить результаты подсчета;
(2) Нажмите Kutools > Функции Kutools > Статистический и математический > SUMBYCELLCOLOR или типа = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) непосредственно в пустую ячейку для суммирования ячеек на основе заданного цвета фона.
Применить COUNTBYCELLCOLOR и SUMBYCELLCOLOR функции для каждого цвета фона отдельно, и вы получите результаты, как показано на скриншоте ниже:

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

Ячейки Count и Sum, основанные на конкретном цвете заполнения с помощью Kutools for Excel

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

1 , Выберите диапазон, который вы хотите использовать, и нажмите Kutools Plus > По цвету , см. снимок экрана:

2 . И в По цвету диалоговое окно, пожалуйста, сделайте, как показано на скриншоте ниже:
(1) Выберите Стандартное форматирование из Метод цвета выпадающий список;
(2) Выберите Задний план из Тип счета выпадающий список.
(3) Нажмите кнопку «Создать отчет».


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

Теперь вы получите новую рабочую тетрадь со статистикой. Смотрите скриншот:

  • Объединение и объединение нескольких листов и книг.
  • Сравнение диапазонов, копирование нескольких диапазонов, преобразование текста в дату, преобразование единиц и валют.
  • Подсчет количества цветов, Пейджинговые субтитры, Расширенный сортировка и Суперфильтр,
  • Подробнее Выбрать / Вставить / Удалить / Текст / Формат / Ссылка / Комментарий / Рабочие книги / Рабочие листы Инструменты...

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

    Как часто Вы при работе с таблицами окрашиваете ячейки в тот или иной цвет? Желтый - расходы Транспортного отдела, Красный - Экономического, Зеленый - Администрация и т.п. А потом хочется все эти расходы просуммировать, и не просто просуммировать ВСЕ расходы, а только расходы в ячейках с определенным цветом заливки. Это еще одна нерешенная проблема 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). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет суммировать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет суммировать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца - то её значение не будет суммироваться.

    Важно: Функция не вычисляется при изменении цвета заливки. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2 -Enter . Либо нажать сочетания клавиш Shift +F9 (пересчет функций активного листа) или клавишу F9 (пересчет функций всей книги)

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

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

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

    1. Убедитесь, что среди вкладок на ленте Excel, присутствует «Разработчик»:

    Скачать заметку в формате Word

    Скачать заметку в формате pdf

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

    2. Если такой закладки вы не видите, щелкните на кнопке Officeв левом верхнем углу и затем на кнопке «Параметры Excel»:

    3. В открывшемся окне «Параметры Excel» перейдите на вкладку «Основные» и поставьте галочку в строке «Показывать меню Разработчик на ленте». Нажмите Ok

    4. Создайте на листе Excel диапазон со значениями; несколько ячеек раскрасьте:

    5. Перейдите на вкладку Разработчик и щелкните на VisualBasic:

    6. У вас откроется окно VBA, содержащее окно VBAProject:

    7. Если окна VBAProjectнет на экране

    щелкните на меню View — Project Explorer:

    8. Если вы хотите использовать создаваемый код VBA в любом Excel-файле, вам следует его записать в VBAProject, относящийся к Personal.xlsb. Если вы хотите применять код только в одном файле, с которым вы сейчас работаете, сохраните код в VBAProject`е именно этого файла (в нашем случае Сумма по цвету.xlsm):

    9. Допустим, вы решили, что создаваемая функция будет использоваться в дальнейшем в различных файлах. Щелкните правой кнопкой мыши на VBAProject(Personal.xlsb) и выберите Insert — Module


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

    Function СумЦвет(диапазон As Range, критерий As Range) As Double " Определяет сумму значений в ячейках "диапазона", " цвет которых совпадает с цветом в ячейке "критерий" Application.Volatile True Dim i As Range For Each i In диапазон If i.Interior.Color = критерий.Interior.Color Then СумЦвет = СумЦвет + i End If Next End Function

    Function СумЦвет(диапазонAs Range , критерийAs Range ) As Double

    " Определяет сумму значений в ячейках "диапазона",

    " цветкоторыхсовпадаетсцветомвячейке"критерий"

    Application . Volatile True

    Dim i As Range

    For Each i In диапазон

    If i . Interior . Color = критерий. Interior . Color Then

    СумЦвет= СумЦвет+ i

    End If

    Next

    End Function

    Номер вашего модуля (у меня он 5) будет зависеть от числа ранее созданных модулей.

    Несколько слов о коде:

    Function СумЦвет (диапазон AsRange, критерий AsRange) AsDouble/ Задает пользовательскую функция под названием СумЦвет с двумя параметрами: диапазоном суммирования и критерием – ячейкой, по цвету которой определяется, значения в каких ячейках суммировать.

    Application.Volatile True/ Заставит нашу функцию пересчитываться при любом изменении значения в любой из ячеек на листе

    Dim i AsRange/ Определяет переменную i, как диапазон ячеек

    For Eachi In диапазон / Для всех ячеек из выбранного диапазона

    If i.Interior.Color = критерий.Interior.Color Then/ Если цвет ячейки совпадает с критерием

    СумЦвет = СумЦвет + i.Value/ то добавляем значение, хранящееся в ячейке в сумму

    10. Вы создали пользовательскую функцию СумЦвет, которую можно найти в категории «Определенные пользователем»


    11. Окно мастера функции выглядит также, как и для стандартной функции Excel

    Хочу обратить ваше внимание на две особенности функции СумЦвет:

    • При изменении значения в одной из ячеек происходит автоматический пересчет значения функции СумЦвет. Если вы поменяли только цвет ячейки , автоматический пересчет не произойдет. Нажмите F9.
    • К сожалению, функция не работает, если ячейки раскрашены с помощью условного форматирования. Это ограничение можно преодолеть, если применить правила, использованные в условном форматировании, в функциях СУММЕСЛИ и СУММЕСЛИМН. Подробнее см.

    На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций:

    • КолЦвет – определяет число ячеек выделенного цвета
    • СумНеЦвет – определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
    • КолНеЦвет – определяет число выделенных ячеек (не белых)