Вредоносное ПО (malware) - это назойливые или опасные программы,...
![Лучшие утилиты для удаления вирусов и вредоносных программ](https://i2.wp.com/webhelper.info/images/danger.jpg)
В подробном видео уроке. Сегодня мы бы хотели немного расширить данную статью для решения более узкой задачи. Допустим, вам необходимо посчитать количество ячеек в зависимости от цвета ячеек или цвета текста.
Начиная с Excel 2007 в программе встроили возможность сортировки ячеек по цвету. Таким образом, можно отфильтровать нужный нам цвет, выделить оставшиеся на виду ячейки и визуально посмотреть общее количество ячеек. Но что делать, если нам требуется делать это часто и при этом нам необходимо, чтобы все считалось и пересчитывалось с помощью формул.
Для этих целей необходимо использовать очень простенький макрос, а точнее пользовательскую функцию, назовем ее ColorNom , онапозволит нам вытягивать числовой код цвета заливки и далее по этому коду мы и будет считать общее количество ячеек, используя приемы, описываемые в статье
Итак, приступим. Зайдите в редактор Visual Basic, для этого:
в Excel 2003 нажмите на Сервис , далее Макрос и затем Редактор Visual Basic .
в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик , далее выберите Visual Basic
Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик
После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module
Public Function ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function
Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.
=ColorNom (A1)+Сегодня()*0
Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.
Вот так выглядят аргументы данной функции
СЧЁТЕСЛИ(диапазон ;критерий )
Пропишем формулу:
СЧЁТЕСЛИ($B$1:$B$8 ;E2 )
Диапазон мы указали со знаком доллара, чтобы он был закреплен и можно было протянуть формулу. Критерия у нас встречается всего три и они указаны в нашей вспомогательной таблице. Протянем формулу и получим количество ячеек по цветам.
Скачать пример файла: (файл с поддержкой макросов)
Предположим, у вас есть диапазон ячеек с разными цветами фона, таких как красный, зеленый, синий и т. Д., Но теперь вам нужно посчитать, сколько ячеек в этом диапазоне имеют определенный цвет фона, и суммировать окрашенные ячейки с одинаковым определенным цветом. , В Excel нет прямой формулы для вычисления суммы и количества цветовых ячеек, здесь я представлю вам несколько способов решения этой проблемы.
Предположим, у нас есть таблица продаж фруктов, как показано на скриншоте ниже, и мы будем подсчитывать или суммировать цветные ячейки в столбце Количество. В этой ситуации мы можем отфильтровать столбец Amount по цвету, а затем легко подсчитать или суммировать отфильтрованные цветные ячейки с помощью функции SUBTOTAL в Excel.
1 , Выберите пустые ячейки, чтобы войти в функцию SUBTOTAL.
Внимание
: В обеих формулах E2: E20 - это столбец Amount, содержащий цветные ячейки, и вы можете изменять их по мере необходимости.
2
, Выберите заголовок таблицы и нажмите дата
> Фильтр
, Смотрите скриншот:
3
, Нажмите значок фильтра в ячейке заголовка столбца Сумма и нажмите Фильтр по цвету
и указанный цвет вы будете считать последовательно. Смотрите скриншот:
После фильтрации обе СУБТОТАЛЬНЫЕ формулы автоматически подсчитывают и суммируют все отфильтрованные цветовые ячейки в столбце Количество. Смотрите скриншот:
Внимание : Этот метод требует, чтобы цветные ячейки, которые вы будете считать или суммировали, находились в одном столбце.
В этом методе мы создадим именованный диапазон с помощью функции 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 для 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 легко рассчитать, в том числе Количество / Сумма / Среднее видимых ячеек , Подсчет / Сумма по цвету ячейки , Подсчет / сумма по цвету шрифта , Считать персонажей , Подсчет шрифтом жирным шрифтом , И т.д. Бесплатная пробная версия!
При использовании вышеуказанной функции, определяемой пользователем, вам нужно вводить формулу один за другим, если есть много разных цветов, этот метод будет утомительным и трудоемким. Но если у вас есть Kutools for Excel Автора По цвету утилита, вы можете быстро генерировать отчет о цветных ячейках. Вы можете не только подсчитывать и суммировать цветные ячейки, но также получать средние, максимальные и минимальные значения цветного диапазона.
1
, Выберите диапазон, который вы хотите использовать, и нажмите Kutools Plus
> По цвету
, см. снимок экрана:
2
. И в По цвету
диалоговое окно, пожалуйста, сделайте, как показано на скриншоте ниже:
(1) Выберите Стандартное форматирование
из Метод цвета
выпадающий список;
(2) Выберите Задний план
из Тип счета
выпадающий список.
(3) Нажмите кнопку «Создать отчет».
Внимание
: Для подсчета и суммирования цветных ячеек по определенному условному цвету форматирования выберите Условное форматирование
из Метод цвета
раскрывающийся список в диалоговом окне выше или выберите Стандартное и условное форматирование
из выпадающего списка для подсчета всех ячеек, заполненных указанным цветом.
Теперь вы получите новую рабочую тетрадь со статистикой. Смотрите скриншот:
При работе с таблицами первоочередное значение имеют выводимые в ней значения. Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.
Конечно, всегда приятно иметь хорошо оформленную таблицу, в которой ячейки в зависимости от содержимого, окрашиваются в разные цвета. Но особенно актуальна данная возможность для больших таблиц, содержащих значительный массив данных. В этом случае заливка цветом ячеек значительно облегчит пользователям ориентирование в этом огромном количестве информации, так как она, можно сказать, будет уже структурированной.
Элементы листа можно попытаться раскрасить вручную, но опять же, если таблица большая, то это займет значительное количество времени. К тому же, в таком массиве данных человеческий фактор может сыграть свою роль и будут допущены ошибки. Не говоря уже о том, что таблица может быть динамической и данные в ней периодически изменяются, причем массово. В этом случае вручную менять цвет вообще становится нереально.
Но выход существует. Для ячеек, которые содержат динамические (изменяющиеся) значения применяется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить» .
С помощью условного форматирования можно задать определенные границы значений, при которых ячейки будут окрашиваться в тот или иной цвет. Окрашивание будет проводиться автоматически. В случае, если значение ячейки, вследствие изменения выйдет за пределы границы, то автоматически произойдет перекрашивание данного элемента листа.
Посмотрим, как этот способ работает на конкретном примере. Имеем таблицу доходов предприятия, в которой данные разбиты помесячно. Нам нужно выделить разными цветами те элементы, в которых величина доходов менее 400000 рублей, от 400000 до 500000 рублей и превышает 500000 рублей.
Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.
Если в таблице находятся статические данные, которые не планируется со временем изменять, то можно воспользоваться инструментом для изменения цвета ячеек по их содержимому под названием «Найти и выделить» . Указанный инструмент позволит отыскать заданные значения и изменить цвет в этих ячейках на нужный пользователю. Но следует учесть, что при изменении содержимого в элементах листа, цвет автоматически изменяться не будет, а останется прежним. Для того, чтобы сменить цвет на актуальный, придется повторять процедуру заново. Поэтому данный способ не является оптимальным для таблиц с динамическим содержимым.
Посмотрим, как это работает на конкретном примере, для которого возьмем все ту же таблицу дохода предприятия.
Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон «3?????» . Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры «3» . То есть, в выдачу поиска попадут значения в диапазоне 300000 – 400000 , что нам и требуется. Если бы в таблице были числа меньше 300000 или меньше 200000 , то для каждого диапазона в сотню тысяч поиск пришлось бы производить отдельно.
Вводим выражение «3?????» в поле «Найти» и жмем на кнопку «Найти все ».
Как видим, существует два способа окрасить ячейки в зависимости от числовых значений, которые в них находятся: с помощью условного форматирования и с использованием инструмента «Найти и заменить» . Первый способ более прогрессивный, так как позволяет более четко задать условия, по которым будут выделяться элементы листа. К тому же, при условном форматировании цвет элемента автоматически меняется, в случае изменения содержимого в ней, чего второй способ делать не может. Впрочем, заливку ячеек в зависимости от значения путем применения инструмента «Найти и заменить» тоже вполне можно использовать, но только в статических таблицах.
Для выполнения данной операции необходим пакет утилит Excel под названием .
Вообще, подсчет по цветам удобен в том случае, если вы работаете с цветной таблицей.
Представляет собой программный пакет, который расширяет возможности Excel. Данный программный пакет действительно удобен, позволяя сэкономить драгоценное время пользователей. Несмотря на то, что это платное программное обеспечение, но для использования дома и в образовательных целях пакет бесплатный.
Надстройку довольно легко использовать, она добавляется как вкладка в окне Excel, где вы можете найти все функции и утилиты услуги, предлагаемые ею. Интерфейс выглядит следующим образом:
Шаг 1: Откройте лист, содержащий цветные ячейки. Нажмите на любую пустую ячейку, в которую нужно поместить результат.
Шаг 2: Теперь откройте вкладку ASAP Utilities. Из выпадающего списка Формулы выберите 12. Вставить функцию из библиотеки
Шаг 3: После этого появится диалоговое окно, содержащее множество формул. Из того же списка, выберите функцию ASAPCOUNTBYCELLCOLOR и нажмите кнопку OK.
Шаг 4: Теперь появится диалоговое окно с аргументами функции. Укажите их и нажмите кнопку ОК.
В поле Reference
укажите диапазон, в котором необходимо произвести вычисления, Color_index_nr
- адрес ячейки, цвет которой необходимо учитывать.
После нажатия на ОК вам будет показан результат:
Таким образом в Excel легко подсчитать количество ячеек по цвету фона.
Процесс подсчета в значительной степени похож на тот, что описан выше. Только в данном случае среди формул необходимо выбрать ASAPCOUNTBYFONTCOLOR .
Появится диалоговое окно с аргументами функции, где также нужно указать диапазон, в котором необходимо произвести вычисления, и адрес ячейки, цвет шрифта который нужно учитывать при подсчете в формуле. Нажмите кнопку OK. Перед вами - количество ячеек по цвету шрифта.
Для суммирования ячеек нужно выполнить следующее:
Шаг 1: Откройте лист Excel, имеющий цветные ячейки со значениями в них. Выберите конкретную ячейку, в которой нужно отобразить результат.
Шаг 2: Перейдите на вкладку в разделе формул опять-таки выберите раздел 12. Вставить функцию из библиотеки .
Шаг 3: Перечень функций прокрутите список вниз и выберите функцию ASAPSUMBYCELLCOLOR и нажмите кнопку OK.
Шаг 4: О ткроется диалоговое окно с аргументами функции. Здесь также нужно указать диапазон и адрес ячейки, фон которой учитывается при суммировании остальных ячеек. Нажмите кнопку OK и порадуйтесь результату.
Вот таким образом можно просуммировать ячейки по цвету фона. И весь процесс вряд ли занимает несколько секунд.
Процесс подсчета аналогичен вышеописанному, но в перечне функция нужно выбрать ASAPSUMBYFONTCOLOR .
Появится диалоговое окно, где вы должны указать диапазон ячеек и адрес ячейки с образцом цвета шрифта. Нажмите OK.
Использование пакета ASAP Utilities позволило быстро произвести подсчеты в ячейках с учетом цвета ячеек и цвета шрифта текста. ASAP Utilities гармонично вливается в интерфейс 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
Хочу обратить ваше внимание на две особенности функции СумЦвет:
На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций: