Подсчет ячеек в excel по цвету заливки

Посчитать количество выделенных цветом ячеек. (Формулы/Formulas)

​ формул, которые бы​​ End Sub, в​​: Да этот вариант,​ строку формул и​Юрий М​ кнопок внизу страницы.​Посчитать количество строк​и​ & cntRes &​​ ячейки. По крайней​​ которые определяют коды​ вручную, просто поставьте​ запустить код VBA​ xlRange.Rows.Count For indColumn​​ от их значений:​baneska​ одна волна (т.е.​ различали ячейки по​ чем ошибка?​ я тоже рассматривал.​ нажал зеленую галочку.​​: Это ведь НАДСТРОЙКА.​​ Для удобства также​​ можно с помощью​SumCellsByColor​ vbCrLf & «Sum=​​ мере, я не​ цветов.​ курсор на любую​ в Excel.​ = 1 To​​Due in X Days​​:​​ имеется в виду​ цвету, только по​и попутно вопрос​​ Пример все-таки не​Hugo​ Подключите её и​​ приводим ссылку на​​функции «Автосумма» в Excel​, и испытать их​ » & sumRes​ слышал о таких​Замечание:​

​ xlRange.Columns.Count arResults(indRow, indColumn)​​– оранжевые,​​ последовательно выделенные, без​ адресу​ — как узнать​ совсем удачный, наверное.​​: У меня тоже​​ всё заработает.​ оригинал (на английском​​. Выделяем ячейку, в​ на своих данных.​​ & vbCrLf &​​ функциях, а жаль​​Пожалуйста, помните, что​F2​​ будут выполнены, выберите​ = xlRange(indRow, indColumn).Font.Color​

​Delivered​​, Боже, вы гений.​​Эля коробицына​​ код цвета, насколько​

​ Просто делается универсальная​​ не работает, если​​Guest​​ языке) .​ которой будет стоять​​Урок подготовлен для Вас​ vbCrLf & _​Конечно, Вы можете найти​ все эти формулы​

​, а затем​​ ячейки, в которые​​– зелёные,​ Спасибо вам большое​;​​: с помощью условного​ я понял именно​ таблица, в которой​ как критерий указывать​: Это ведь НАДСТРОЙКА.​Если к одной или​

​ цифра количества строк.​​ командой сайта office-guru.ru​​ «Color=» & Left(«000000»,​​ тонны кода VBA​

​ имеено заточено будет​​ ячейку с УФ.​

excelworld.ru>

​ Вы уже добавили​

• Excel вставка картинки в ячейку
• Как в excel сделать перенос в ячейке
• Excel добавить в ячейку символ
• Как в excel сделать ячейку с выбором
• Как перемещать ячейки в excel
• Excel заливка ячейки по условию
• Excel значение ячейки
• Как в excel выровнять ячейки по содержимому
• Как в excel посчитать отклонение в процентах
• Excel новый абзац в ячейке
• Как в excel выделить только видимые ячейки
• Excel подсчитать количество символов в ячейке excel

Динамическая сортировка таблицы в MS Excel

При выполнении неких задач в Excel, требуется установить автоматическую сортировку, которая подразумевает наличие формул. Зависимо от типа данных в применяемом спектре динамическая сортировка быть может задана 3-мя методами:

1. Если информация в ячейках столбца представлена числами, употребляются функции НАИМЕНЬШИЙ и СТРОКА. 1-ая находит меньший элемент из массива, а 2-ая описывает порядковый номер строчки. Таковым образом формируется последовательность. Формула записывается последующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)).
2. Когда в ячейках содержаться текст, 1-ая формула не сработает. Для сортировки в этом случае целенаправлено использовать формулу: =СЧЁТЕСЛИ(A:A;»<«&A1)+ СЧЁТЕСЛИ ($A$1:A1;»=»&A1).
3. Для автоматической сортировки текстовой инфы текстовой инфы также употребляется формула массива: =ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; «<«&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; «<«&List); 0)). Тут List – это данный спектр.

Нажатие кнопки Enter

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

1. Выделяем ячейку, в которую нужно поставить прочерк. Жмем на кнопку «Выровнять по центру», которая находится на ленте во вкладке «Главная» в группе инструментов «Выравнивание». А также кликаем по кнопке «Выровнять по середине», находящейся в том же блоке. Это нужно для того, чтобы прочерк располагался именно по центру ячейки, как и должно быть, а не слева.
2. Набираем в ячейке с клавиатуры символ «-». После этого не делаем никаких движений мышкой, а сразу жмем на кнопку Enter, чтобы перейти на следующую строку. Если вместо этого пользователь кликнет мышкой, то в ячейке, где должен стоять прочерк, опять появится формула.

Данный метод хорош своей простотой и тем, что работает при любом виде форматирования

Но, в то же время, используя его, нужно с осторожностью относиться к редактированию содержимого ячейки, так как из-за одного неправильного действия вместо прочерка может опять отобразиться формула

Стандартный фильтр и сортировка по цвету в Excel

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.

С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

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

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Вставляем отделяющие линии между группами строк.

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

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

Для этого сначала выделим весь наш диапазон с данными.

Внимание! Первую шапку таблицы не выделяем, начинаем с данных!

В нашем случае, выделяем A3:G33.

Затем далее действуем по уже отработанной схеме. В меню условного форматирования выбираем использование формулы (1). Далее записываем само правило:

Иначе говоря, мы проверяем, равна ли наша текущая дата предыдущей. Если не равна, значит, мы перешли к новому дню. Соответственно наше текущее положение нужно выделить. Выбираем формат (3). Тип границы – линия (4). Она будет использоваться по верхней границе (5).

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

Выделение через строку.

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

Итак, возьмем для примера небольшую таблицу.

Выделим диапазон A1:D18.

Затем создаем новое правило при помощи формулы

В чем ее смысл? Если номер, полученный при помощи функции СТРОКА(), делится без остатка на 2, то значит у нее чётный номер, и к ней следует применить правило форматирования. Если же номер не делится на 2 без остатка, то это нечетная. Ее мы оставляем без изменений.

В результате получилась «полосатая» таблица по принципу «четный-нечетный».

Выделяем ячейки, содержащие более 1 слова.

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

Правило условного форматирования здесь будет такое:

Разберем, как это работает. Логика здесь не сложная:

1. Между словами всегда есть пробелы.
2. Но, поскольку пробелы в начале и в конце текста нам не нужны, избавляемся от них при помощи функции СЖПРОБЕЛЫ(А1).
3. Подсчитаем количество символов в получившемся в п.2 тексте при помощи ДЛСТР. Запишем ДЛСТР(СЖПРОБЕЛЫ(A1)).
4. Удалим все пробелы из текста при помощи функции ПОДСТАВИТЬ, которая заменит пробел на пустой символ “”, то есть фактически удалит его: ПОДСТАВИТЬ(A1;» «;»»)
5. Считаем количество символов в тексте, получившемся в п.4, используя функцию ДЛСТР: ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)).
6. Сравниваем результаты, получившиеся в п.3 и п.5 путем их вычитания. Если результатом будет ноль, значит текст состоял из одного слова. Если получится число больше нуля, значит, были пробелы между словами. И, следовательно, текст состоял более чем из одного слова.

Что нам и было необходимо. Их и закрасим.

Изменяем цвет заливки для особых ячеек (пустые, с ошибкой в формуле)

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

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

Цвет ячейки будет изменяться автоматически в зависимости от значения ячейки.

Этот способ решения задачи Вы, вероятнее всего, будете использовать в 99% случаев, то есть заливка ячеек будет изменяться в соответствии с заданным Вами условием.

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

1. На вкладке Home (Главная) в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило). Точно также, как на 2-м шаге примера .
2. В диалоговом окне New Formatting Rule (Создание правила форматирования) выберите вариант Use a formula to determine which cells to format (Использовать формулу для определения форматируемых ячеек). Далее в поле Format values where this formula is true (Форматировать значения, для которых следующая формула является истинной) введите одну из формул:

   • чтобы изменить заливку пустых ячеек

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

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

3. Нажмите кнопку Format (Формат), выберите нужный цвет заливки на вкладке Fill (Заливка), а затем нажмите ОК. Подробные инструкции даны на примера «Как динамически изменять цвет ячейки, основываясь на её значении».Образец настроенного Вами условного форматирования будет выглядеть приблизительно так:
4. Если Вы довольны цветом, жмите ОК. Вы увидите, как созданное правило немедленно будет применено к таблице.

Изменяем цвет заливки особых ячеек статически

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

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

1. Выделите таблицу или диапазон и нажмите F5, чтобы открыть диалоговое окно Go To (Переход), затем нажмите кнопку Special (Выделить).
2. В диалоговом окне Go to Special (Выделить группу ячеек) отметьте вариант Blanks (Пустые ячейки), чтобы выделить все пустые ячейки.Если Вы хотите выделить ячейки, содержащие формулы с ошибками, отметьте вариант Formulas (Формулы) > Errors (Ошибки). Как видно на рисунке выше, Вам доступно множество других настроек.
3. И наконец, измените заливку выделенных ячеек или настройте любые другие параметры форматирования при помощи диалогового окна Format Cells (Формат ячеек), как это описано в разделе .

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

Как убрать сортировку в Excel

Для отмены единичной сортировки спектра данных довольно надавить клавишу «Отменить ввод» в левом углу экрана. Случается так, что конфигурации в файле были сохранены и отменить действие нереально. Как убрать сортировку в этом случае?

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

Подсветка дат и сроков.

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

Начнем с простого способа.

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

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

Итак, обратите внимание на дату отгрузки.  Если она в прошлом и товар уже поставлен – можно не волноваться

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

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

Таким образом, мы имеем 3 группы условий, которые сразу же запишем в виде правил условного форматирования:

1. Сегодняшняя или завтрашняя дата (1):

Обратите внимание на порядок следования формул. Формула, находящаяся выше, имеет более высокий приоритет, поскольку выполняется раньше

Поэтому мы в начале выделяем только сегодняшнюю и завтрашнюю даты, затем – все остальные в будущем. Если ваш файл выглядит не так, как на рисунке, при помощи клавиш «вверх-вниз» (3) просто измените порядок расположения условий.

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

И еще один пример. Выделим выходные дни.

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

Давайте разбираться, как это работает.

Если клетка пустая, то возвращаем ЛОЖЬ и не меняем ее вид. В этом нам поможет функция ЕПУСТО, которая станет условием в функции ЕСЛИ.

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

ДЕНЬНЕД(A1;2)=6   и   ДЕНЬНЕД(A1;2)=7

Функция ДЕННЕД с аргументом 2 помогает нам определить номер дня недели по дате. 6 и 7 день – выходные в привычном для нас исчислении (суббота и воскресенье).

Объединяем эти два выражения при помощи ИЛИ, что означает, что нам достаточно выполнения хотя бы одного из них. Получается

ИЛИ(ДЕНЬНЕД(A1;2)=6;ДЕНЬНЕД(A1;2)=7)

В итоге, выходные дни будут выделены так, как мы определим при помощи кнопки Формат.

Чтобы перепроверить себя, давайте используем столбец В. В B1 запишите формулу =А1 и скопируйте ее вниз для всех дат. Первоначально получится копия столбца А.

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

Выделите их и нажмите комбинацию клавиш CTRL и 1. В появившемся окне выберите «Все форматы» и в поле «Тип» впишите 4 латинских буквы d (как на рисунке). И вот у вас дата преобразована в день недели без всяких формул!

И, как можете сами убедиться, что нами выделены именно субботы и воскресенья.

Сортировка по цвету ячейки и по шрифту

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

1. Курсором мыши необходимо избрать одну из ячеек с данными в подходящем столбце.
2. На вкладке «Основная» в многофункциональной группе «Редактирование» следует отыскать клавишу «Сортировка и фильтрация» и избрать в доп меню команду «Настраиваемая сортировка».
3. В открывшемся окне поначалу указать столбец, в каком необходимо упорядочить данные, а позже тип сортировки. Это быть может цвет ячейки, цвет шрифта либо значок условного форматирования.
4. Зависимо от избранного вида сортировки в группе порядок нужно отметить подходящий значок или колер заливки либо шрифта.
5. Крайний параметр, который необходимо указать – это последовательность расположения (сверху либо снизу).

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

Быстрое выделение ячеек по цвету или форматированию в Excel

Kutools for Excel

• Обзор
• Бесплатная загрузка
• Учебники по функциям
• Что нового?

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

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

Нажмите на Kutools >> Выберите >> Выбрать ячейки с форматом. Смотрите скриншоты:

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

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

1. Примените эту утилиту, нажав Kutools > Выберите > Выбрать ячейки с форматомИ в Выбрать ячейки с форматом диалоговое окно сделайте следующие параметры:

A : Выберите диапазон, для которого нужно выбрать форматирование, аналогичное указанной ячейке, нажав  кнопку. B: Нажмите Выбрать формат из ячейки кнопку, чтобы выбрать ячейку, для которой вы хотите использовать ее форматирование. C : Все атрибуты базовой ячейки отображаются в списке ниже. Если вы хотите выбрать все ячейки с таким же форматированием, что и базовая ячейка, проверьте все параметры атрибутов. D Если диапазон содержит пустые ячейки и вы не хотите их выделять, проверьте это  Игнорировать пустые ячейки опцию.

2. Затем нажмите OK кнопка, и появится окно подсказки, чтобы напомнить вам, сколько ячеек будет выбрано.

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

Выбрать все ячейки по цвету ячейки

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

1. Примените эту утилиту, нажав Kutools > Выберите > Выбрать ячейки с форматомИ в Выбрать ячейки с форматом диалоговое окно сделайте следующие параметры:

A : Выберите диапазон, в котором вы хотите выделить ячейки того же цвета, что и указанная ячейка, нажав  кнопку. B : Нажмите Выбрать формат из ячейки чтобы выбрать ячейку с желаемым цветом фона. C : Все атрибуты базовой ячейки отображаются в списке ниже. Нам нужно только проверить цвет ячейки, чтобы выбрать все ячейки в этой демонстрации. D : Если диапазон содержит пустые ячейки и вы не хотите их выделять, проверьте это Игнорировать пустые ячейки опцию.

2. Затем нажмите OK кнопка, и появится окно подсказки, чтобы напомнить вам, сколько ячеек будет выбрано.

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

См. Демонстрацию функции выбора ячеек с форматированием (видео 1)

Видео 1.Выбрать ячейки с форматом демонстрация функции.

Рекомендуемые инструменты для повышения производительностиСледующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?Office Tab: Использование удобных вкладок в вашем офисе, как и в случае Chrome, Firefox и New Internet Explorer.Kutools for Excel: Более 300 дополнительных функций для Excel 2019, 2016, 2013, 2010, 2007 и Office 365.Classic Menu for Office: Верните знакомые меню в Office 2007, 2010, 2013, 2016, 2019 и 365, как если бы это были Office 2000 и 2003.

Kutools for Excel

Описанная выше функциональность — лишь одна из 300 мощных функций Kutools for Excel.

Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.

Как в Excel посчитать количество ячеек по цвету ячейки или цвету текста

Мы с вами уже рассматривали вопрос о том как посчитать в 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 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик

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

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

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

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

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

1. В третьем столбце мы уже будет считать количество ячеек определенного цвета по условию, использую код цвета.
2. Считать количество мы будем с помощью функции СЧЁТЕСЛИ
3. Вот так выглядят аргументы данной функции
4. =СЧЁТЕСЛИ(диапазон;критерий)
5. Пропишем формулу:
6. =СЧЁТЕСЛИ($B$1:$B$8;E2)

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

Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)

Закрасить ячейку по условию или формуле

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

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

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

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

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

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

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

Комментарии:

1. Svetlana — 27.06.2015 21:28

наконец-то узнала, как это можно сделать!

Виктор — 14.04.2016 17:23

Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

salam — 19.05.2016 16:24

Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?

Федя — 16.11.2016 14:39

Как задать цвет определенному значению в одной ячейки, например — вожу 5 — она будет красным цветом, вожу 4 — она станет зелёным цветом

Оля — 03.05.2017 12:12

подскажите, как заливать в гамме одного цвета с разными оттенками в столбике, если напр., если 100% — зеленый, 95- зеленый но светлее, 75 — еще светлее и т.д. заранее спасибо

Как в экселе суммировать ячейки одного цвета?

Главная » Прочее »

Загрузка…

Вопрос знатокам: Если быть точнее надо, чтобы из диапазона A1:A10 выбирались (а затем суммировались) значения только определенного цвета

Лучшие ответы

А Программное обеспечение тут причем. Спросил бы где скачать Excel kheujq разговор, как работать в программе есть уроки и книги в гугле и на торентах

на прямую даже Excel 2013 так считать не может тут модуль в Visual Basic писать надо полный текст см здесь .planetaexcel /techniques/9/162/ там и пример есть

Excel суммировать по цвету до сих пор не умеет. Чтобы исправить этот существенный недостаток можно использовать несложную пользовательскую функцию на Visual Basic ——Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double Dim Sum As Double

Application.Volatile True

Value End If Next cell SumByColor = Sum End Function ——-Если теперь вернуться в Excel, то в Мастере функций (Вставка — Функция) в появившейся там категории Определенные пользователем можно найти нашу функцию и вставить ее на лист: У нее два аргумента: DataRange — диапазон раскрашенных ячеек с числами (Al:A6) ColorSample — ячейка, цвет которой принимается как образец для суммирования (C2) пример

=SumByColor(Al:A6;C2)

Ответы знатоков

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

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

• Если это так, то наверно только писать макрос, функции использовать, есть такая тема как Сумм_ЦеллЦолор (транслитерируйте) — Суммирование ячеек по цвету заливки
• Задайте вопрос подробнее
• Отправил дополнительные ссылки Вам на почту

а конкретнее можно? почему функцию не назначить тогда?

Как раз на работе недавно подобное делал, написал для вас функцию, работает по аналогии с суммесли: диапазон, критерий из которого берется цвет для выборки из диапазона, диапазон суммирования files.mail /P1IFSZ

=ЕСЛИ (ЯчейкаЖелтая … НЕТ такой фукции !Если цвет задавался условным форматированием — тогда можно привязаться к формулам УФ Если ячейки окрашивались вручную — тогда ТОЛЬКО МАКРОСВ ссылке от BOBAH-а об этом упоминается

Может не цвет а код использовать. просто на разных годах офиса цвет различен

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

1. Пожалуйста, готовый код:
2. Sub Zakaz()‘åñëè ñ÷èòàòü ñòðîêè 1000 øòóê
3. For b = 6 To 1000

If Range(«A» & b).Interior.Color = 65535 Then Cells(b, 14) = 1 Else Cells(b, 14) = 0

Next‘åñëè ñ÷èòàòü êîëîííû N, O ñâîáîäíû

‘ýòè ìîìåíòû ìîæíî ðåäàêòèðîâàòü

Range(«O6») = «=SUMIFS(C,C,1)» Range(«O6»).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns(«N:N»).Select Range(«N6»).Activate Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft

Range(«N6»).Select

End Sub

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

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Заливка ячейки цветом в VBA Excel

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

1 2 3 4 5 6

SubColorTest1() Range(«A1»).Interior.Color=31569 Range(«A4:D8»).Interior.Color=4569325 Range(«C12:D17»).Cells(4).Interior.Color=568569 Cells(3,6).Interior.Color=12659 EndSub

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

Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

1 2 3 4 5

SubColorTest11() Cells(1,1).Interior.Color=-12207890 Cells(2,1).Interior.Color=16777215+(-12207890) Cells(3,1).Interior.Color=4569325 EndSub

Проверено в Excel 2016.

Вывод сообщений о числовых значениях цветов

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

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

1 2 3 4 5 6

SubColorTest2() MsgBox Range(«A1»).Interior.Color MsgBox Range(«A4:D8»).Interior.Color MsgBox Range(«C12:D17»).Cells(4).Interior.Color MsgBox Cells(3,6).Interior.Color EndSub

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

Использование предопределенных констант

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Предопределенная константа	Наименование цвета
vbBlack	Черный
vbBlue	Голубой
vbCyan	Бирюзовый
vbGreen	Зеленый
vbMagenta	Пурпурный
vbRed	Красный
vbWhite	Белый
vbYellow	Желтый
xlNone	Нет заливки

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

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

1	Range(«A1»).Interior.Color=vbGreen

Цветовая модель RGB

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

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

Палитра Excel

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

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

1	Range(«A1»).Interior.Color=RGB(100,150,200)