Посчитать жирные строчки эксель
Иногда появляется необходимость понять, сколько ячеек содержат какую-либо информацию. В арсенале инструментов Excel есть набор функций, которые позволяют выполнить эту задачу. Давайте наглядно покажем, со скриншотами, что нужно делать для этого. Мы разберем самые типичные ситуации, в которых следует осуществлять определение количества ячеек с информацией и методы, которые лучше всего подходят в них.
Как посчитать количество ячеек в Excel
Какие инструменты доступны пользователю, если он хочет определить, сколько ячеек есть?
- Специальный счетчик, который показывает количество на строке состояния.
- Арсенал функций, которые определяют количество ячеек, в которых содержится информация определенного типа.
Пользователь может выбирать, какой метод использовать, исходя из ситуации, имеющейся в наличии. Более того, можно использовать сразу несколько инструментов, чтобы решать особенно сложные задачи.
Метод 1. Счетчик ячеек по строке состояния
Это наиболее легкий метод получения количества ячеек, включающих в себя какие-либо сведения. На правой стороне статусбара находится счетчик. Его можно найти немного левее кнопок изменения способов отображения в Excel. Этот индикатор не показывается, если ни одного элемента не выделено или нет ячеек, содержащих значения. Также он не отображается, если такая ячейка только одна. А вот если выделить две непустые ячейки, то счетчик сразу появится, и можно определить количество ячеек, в которых есть информации.
Несмотря на то, что этот счетчик активирован при «заводских» настройках, в ряде ситуаций его может не быть. Такое случается, если до этого какой-то пользователь его отключил. Чтобы это сделать, следует вызвать контекстное меню статусбара и активировать пункт «Количество». Индикатор снова отобразится после этих действий.
Метод 2. Подсчет ячеек при помощи функции СЧЕТЗ
Оператор СЧЕТЗ – очень простой метод подсчета количества ячеек, где есть какие-то данные, если надо записать итоговый результат в другой ячейке или использовать при вычислениях другим оператором. Преимущество применения функции: нет необходимости каждый раз пересматривать число ячеек, в которых есть какая-то информация, если диапазон изменится. Содержимое (возвращаемое формулой значение) будет автоматически изменяться. Как же это сделать?
- Сначала от нас требуется выделить ту ячейку, куда запишется итоговое число заполненных ячеек. Находим кнопку «Вставить функцию» и делаем клик.
- После того, как мы выполнили действия, указанные выше, появится диалоговое окно , в котором нам нужно выбрать нашу функцию. После выделения нажимаем на кнопку «ОК».
- Далее появится диалог ввода аргументов. Им выступает диапазон на ячейки или непосредственно адреса тех ячеек, которые следует проанализировать на заполненность и определить количество. Есть два способа ввода диапазона: ручной и автоматический. Чтобы не ошибиться в указании адресов ячеек, лучше выделить соответствующий диапазон после того, как нажмете на поле ввода данных. Если же ячейки, число которых следует определять, находятся в удаленности, необходимо заносить их отдельно, заполняя поля «Значение2», «Значение3» и так далее.
- Нажимаем «ОК».
Также возможен ручной ввод этой функции. Строение функции: =СЧЁТЗ(значение1;значение2;…).
После ввода этой формулы нажимаем клавишу ввода, и программа автоматически сделает все необходимые подсчеты. Результат она выведет в ту же ячейку, куда записывалась формула.
Метод 3. Функция СЧЕТ для подсчета ячеек
Есть еще один оператор, предназначенный для получения числа ячеек. Но ее отличие от прошлого оператора тем, что способна осуществлять расчет исключительно тех ячеек, в которых есть числа. Как же использовать эту функцию?
- Аналогично ситуации с предыдущей формулой выделяем ту ячейку, куда будет записываться формула и включаем Мастер функций. Затем выбираем «СЧЕТ» и подтверждаем свои действия (нажимаем левой кнопкой мыши по клавише ОК).
- Далее появляется окно ввода аргументов. Они такие же самые, как и в прошлом способе. Нужно указать или диапазон (можно несколько), или ссылки на ячейки. Нажимаем «ОК».
Синтаксис аналогичен предыдущей. Поэтому если нужно ввести ее вручную, нужно написать следующую строку кода: =СЧЁТ(значение1;значение2;…).
Затем в области, где записывается формула, покажется количество ячеек, в которых есть числа.
Метод 4. Функция СЧЕТЕСЛИ
С этой функцией пользователь может определить не только лишь число ячеек, где есть числовые данные, но также и те, подпадающие под конкретный критерий. Например, если критерий будет >50, то считаться будут исключительно те ячейки, где записано число, больше пятидесяти. Можно указывать любые другие условия, в том числе, и логические. Последовательность действий в целом, аналогичная предыдущим двум методам, особенно на первых этапах. Нужно вызвать мастер функций, ввести аргументы:
- Диапазон. Это тот набор ячеек, где будет осуществляться проверка и расчет.
- Критерий. Это условие, на которое будут проверяться ячейки диапазона.
Синтаксис при ручном вводе: =СЧЁТЕСЛИ(диапазон;критерий).
Программа выполнит вычисления и выведет их в ту ячейку, где формула будет записана.
Метод 5. Функция СЧЕТЕСЛИМН для подсчета ячеек
Функция, похожая на предыдущую, только предусматривает проверку по нескольким критериям. Аргументы видны на этом скриншоте.
Соответственно, при ручном вводе синтаксис такой: =СЧЁТЕСЛИМН(диапазон_условия1;условие1; диапазон_условия2;условие2;…).
Как посчитать количество ячеек с текстом внутри диапазона
Чтобы посчитать общее количество ячеек с текстом внутри, следует в качестве диапазона вставить функцию –-ЕТЕКСТ(диапазон подсчета). Функция, куда вставляется диапазон, может быть любой из вышеперечисленных. Например, можно использовать функцию СЧЕТЗ, где в качестве аргумента вводим вместо диапазона функцию, которая ссылается на этот диапазон. Таким образом, нет ничего сложного в том, чтобы определить количество ячеек, в которых есть текст. Еще проще посчитать, сколько ячеек содержат какое-либо значение.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Как суммировать / подсчитывать жирные числа в диапазоне ячеек в Excel?
Если у вас есть диапазон данных, содержащий несколько жирных чисел на листе, и теперь вы хотите суммировать или подсчитывать только выделенные жирным шрифтом ячейки, вы, конечно, можете сложить их по одной вручную, но это займет много времени. Как можно легко и быстро суммировать или подсчитывать только выделенные жирным шрифтом ячейки в Excel?
Quickly select all bold cells in a range in Excel:
With the Kutools for Excel's Select Cells with Format utility, you can select all cells with same formatting or color in a range at once.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Подсчет жирных чисел в диапазоне с помощью функции, определяемой пользователем
Следующие Функция, определяемая пользователем может помочь вам быстро получить количество выделенных жирным шрифтом ячеек. Пожалуйста, сделайте так:
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: подсчет жирных чисел в диапазоне ячеек
3. Затем сохраните этот код и введите эту формулу = Жирный счетчик (A1: C9) в пустую ячейку, см. снимок экрана:
4, Затем нажмите Enter key, и вы будете считать выделенные жирным шрифтом ячейки в диапазоне A1: C9.
Примечание:A1: C9 в формуле указывает диапазон, в котором вы хотите использовать функцию для подсчета выделенных жирным шрифтом ячеек, вы можете изменить его по своему усмотрению.
Суммирование жирных чисел в диапазоне с помощью функции, определяемой пользователем
И если вы хотите суммировать только жирные числа в диапазоне данных, я также могу создать определяемую пользователем функцию для решения этой проблемы.
1. Удерживая нажатой ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: суммирование полужирных чисел в диапазоне ячеек
3. Затем сохраните этот код и введите эту формулу = sumbold (A1: C9) в пустую ячейку, см. снимок экрана:
4. Затем нажмите Enter key, все жирные числа в диапазоне A1: C9 добавлены. Смотрите скриншот:
Примечание:A1: C9 в формуле указывает диапазон, в котором вы хотите использовать функцию для суммирования ячеек, выделенных жирным шрифтом, вы можете изменить его по своему усмотрению.
Несколько щелчков мышью, чтобы подсчитать и суммировать жирные ячейки в диапазоне с помощью Kutools for Excel
Y Вы можете легко подсчитать или суммировать все выделенные жирным шрифтом ячейки в диапазоне несколькими щелчками мыши, не обрабатывая сложный код VBA и не запоминая формулы.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Для подсчета ячеек, выделенных жирным шрифтом, выберите пустую ячейку для поиска результата, а затем щелкните Кутулс > Kutools Функции > Статистические и математические > COUNTBYFONTBOLD.
А для суммирования выделенных жирным шрифтом ячеек нажмите Кутулс > Kutools Функции > Статистические и математические > SUMBYFONTBOLD. Смотрите скриншот:
2. В FАргументы соборования диалоговом окне, укажите диапазон, выделенный жирным шрифтом, который необходимо подсчитать или суммировать, в поле Ссылка, а затем щелкните значок OK кнопку.
Теперь подсчет или суммирование всех выделенных жирным шрифтом ячеек в указанном диапазоне немедленно заполняется в выбранной ячейке. Смотрите скриншот:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Иногда вы можете выделить некоторые специальные ячейки жирным шрифтом, а позже захотите подсчитать общее количество этих жирных ячеек. С COUNTBYFONTBOLD Функция Kutools for Excel, вы можете легко подсчитать количество выделенных жирным шрифтом ячеек в диапазоне в Excel.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Нажмите Кутулс > Kutools Функции > Статистические и математические > COUNTBYFONTBOLD. Смотрите скриншот:
Подсчитайте количество выделенных жирным шрифтом ячеек в диапазоне в Excel
Предположим, вам нужно подсчитать количество выделенных жирным шрифтом ячеек в диапазоне A1: B12 в Excel, сделайте следующее:
1. Выберите пустую ячейку, чтобы использовать функцию COUNTBYFONTBOLD, и нажмите Кутулс > Kutools Функции > Статистические и математические > COUNTBYFONTBOLD.
2. В открывшемся диалоговом окне «Аргументы функции» щелкните значок Приложения кнопка для выбора определенного диапазона, в котором вы будете считать только количество выделенных жирным шрифтом ячеек.
3. Нажмите OK кнопка. И тогда вы получите количество выделенных жирным шрифтом ячеек. Смотрите скриншот:
Внимание: Вы можете ввести формулу = COUNTBYFONTBOLD (A1: B12) (A1: B12 - это диапазон, в котором вы хотите подсчитать количество выделенных жирным шрифтом ячеек), чтобы получить результат напрямую.
Подсчитать количество выделенных жирным шрифтом ячеек Демо
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.
Как подсчитать ячейки с помощью определенного форматирования, такого как полужирный, курсив в Excel?
Допустим, вы ранее выделяли некоторые ячейки на листе жирным шрифтом, а теперь вам нужно посчитать эти ячейки, выделенные жирным шрифтом. Есть идеи быстро решить эту проблему? В этой статье представлены некоторые приемы для подсчета ячеек по определенному формату, например полужирному, курсиву и т. Д.
Kutools for Excel выпущен COUNTBYFONTBOLD функции и SUMBYFONTBOLD Функция для пользователей Excel позволяет быстро подсчитывать или суммировать более жирные ячейки в указанном диапазоне. Смотрите скриншот:
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Подсчет ячеек по определенному форматированию с помощью функции поиска и замены
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
На самом деле мы можем найти все ячейки с определенным форматированием в диапазоне / листе с помощью функции «Найти и заменить», а затем легко подсчитать эти ячейки форматирования в Excel.
1. Выберите диапазон, в котором вы будете подсчитывать ячейки с помощью определенного форматирования, и откройте диалоговое окно «Найти и заменить», нажав кнопку Ctrl + F ключи одновременно.
Внимание: Вы также можете открыть диалоговое окно «Найти и заменить», нажав Главная > Найти и выбрать > Найдите.
2. В диалоговом окне «Найти и заменить» щелкните значок Доступные опции кнопку, чтобы развернуть параметры поиска, а затем нажмите кнопку Формат кнопку.
3. В открывшемся диалоговом окне «Найти формат» укажите форматирование, по которому будет производиться подсчет ячеек, и щелкните значок OK кнопку.
Например, в нашем случае мы будем считать ячейки полужирным шрифтом, поэтому перейдем к шрифт и выберите Жирный в Стиль шрифта коробка. Смотрите скриншот:
4. Теперь вы вернетесь в диалоговое окно «Найти и заменить» и нажмите Найти все кнопку.
Теперь вы увидите количество ячеек с определенным форматированием внизу этого диалогового окна. Смотрите скриншот:
Подсчет ячеек по определенному форматированию с помощью Kutools for Excel
Если у вас установлен Kutools for Excel, его Выбрать ячейки с форматом Утилита может помочь вам быстро подсчитать все ячейки в диапазоне с помощью определенного форматирования. Пожалуйста, сделайте следующее:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите диапазон, который вы будете считать с помощью определенного форматирования, и нажмите Кутулс > Выберите > Выбрать ячейки с форматом.
2. В открывшемся диалоговом окне «Выбор ячеек с форматом» сделайте следующее (см. Снимок экрана выше):
(1) Щелкните значок Выбрать формат из ячейки и выберите ячейку с определенным форматированием, например полужирным.
(2) Снимите флажок со всех параметров форматирования, кроме конкретного форматирования в Тип .
В нашем случае мы проверяем только Жирный вариант, как показано на скриншоте слева:
(3) Щелкните значок Ok кнопку.
3. Теперь открывается диалоговое окно, в котором отображается количество ячеек, соответствующих указанному форматированию. Щелкните значок Да кнопку, чтобы выбрать эти ячейки в выбранном диапазоне.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Из этой статьи Вы узнаете, как в 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, и испытать их на своих данных.
Читайте также: