Как в Excel посчитать количество ячеек по цвету ячейки или цвету текста
Мы с вами уже рассматривали вопрос о том как посчитать в Excel количество ячеек/значений в подробном видео уроке. Сегодня мы бы хотели немного расширить данную статью для решения более узкой задачи. Допустим, вам необходимо посчитать количество ячеек в зависимости от цвета ячеек или цвета текста.
Начиная с Excel 2007 в программе встроили возможность сортировки ячеек по цвету. Таким образом, можно отфильтровать нужный нам цвет, выделить оставшиеся на виду ячейки и визуально посмотреть общее количество ячеек. Но что делать, если нам требуется делать это часто и при этом нам необходимо, чтобы все считалось и пересчитывалось с помощью формул.
Для этих целей необходимо использовать очень простенький макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам вытягивать числовой код цвета заливки и далее по этому коду мы и будет считать общее количество ячеек, используя приемы, описываемые в статье как посчитать в Excel количество ячеек/значений
Итак, приступим. Зайдите в редактор Visual Basic, для этого:
в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.
в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик, далее выберите Visual Basic
После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module
После этого уже не составит труда посчитать количество ячеек или значений в зависимости от цвета ячейки. Используйте нашу статью как посчитать в Excel количество ячеек/значений
Если вам необходимо посчитать количество значений или сумму в зависимости от цвета текста, то необходимо немного изменить код пользовательской функции.
Пример подсчета количества значений по цвету цвету заливки ячеек в Excel
Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.
Далее для удобства мы создадим вспомогательную таблицу из всех существующих цветов заливки. В нашем случае это красный, зеленый и желтый. Рядом с помощью все той же формулы определим код цвета.
В третьем столбце мы уже будет считать количество ячеек определенного цвета по условию, использую код цвета.
Диапазон мы указали со знаком доллара, чтобы он был закреплен и можно было протянуть формулу. Критерия у нас встречается всего три и они указаны в нашей вспомогательной таблице. Протянем формулу и получим количество ячеек по цветам.
[expert_bq id=»1570″]StrConv строка, метод, LCID данная функция vba является универсальной, так как позволяет преобразовать заданную строку в различные вариации верхний и нижний регистр, кодировка, перевод байт. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Начиная с Excel 2007 в программе встроили возможность сортировки ячеек по цвету. Таким образом, можно отфильтровать нужный нам цвет, выделить оставшиеся на виду ячейки и визуально посмотреть общее количество ячеек. Но что делать, если нам требуется делать это часто и при этом нам необходимо, чтобы все считалось и пересчитывалось с помощью формул.
Урок 14 по VBA — функции работы со строками |
Способ исполнения может отличатся, в зависимости от тех целей которые вы ставить и только вам решать подойдет он или нет. Я не скажу, что простой способ, станет выходом из вашей ситуации, это может быть и самый сложный, вам самим нужно будет это определить и выбрать.
Как подсчитывать повторения в Excel?
Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:
Используя функцию СЧЁТЕСЛИ
В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать функцию СЧЁТЕСЛИ и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:
=СЧЁТЕСЛИ($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11, указываем тот диапазон ячеек, в котором и будет производиться подсчёт повторяющихся данных. Важно! Указывать случайный диапазон данных недопустимо. Его особенностью является то, что он может быть только диапазоном ячеек или ссылкой на определённую ячейку.
Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.
Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*», применять их возможно только при работе с текстовыми символами. Знак «*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.
Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «».
Используем функцию СЧЁТЕСЛИМН
Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.
В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:
=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14) Замечу, что орфография функции абсолютно аналогична предыдущей функции СЧЁТЕСЛИ, разница состоит только в их количестве. В нашем примере их два, но функция умеет работать и со 127 диапазонами.
Работаем с функцией ДЛСТР
С помощью функций VBA
Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.
Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module». В созданное окно модуля вы вставляете код функции:
[expert_bq id=»1570″]Стоит отметить, что у большинства пользователей запуск макросов по умолчанию отключен, именно поэтому на главном листе Main необходимо сделать что-то вроде памятки со справочной информацией о том, как активировать работу макросов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] ASC (строковое значение) – применяется для получения числового ASCII кода заданного символа. В качестве параметра можно передать как целую строку (проверка произойдет для первого символа в строке), так и один символ или пробел.Как убрать значение формулы в Excel — Как скрыть в Excel значение ячеек? Как в офисе.
В макросе CommandButton1_Click используется функция GetHash (строка 15), которая преобразует входящие данные в хеш. Данная функция взята с сайта ExcelVba.ru, за что выражаю им огромную благодарность. Код необходимо вставить в главный модуль книги:
