Суммирование по формату ячеек excel
Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.
Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.
Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20
Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются.
Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).
Рис. 2. Суммирование ячеек, отвечающих одному условию
Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).
Рис. 3. Суммирование ячеек, отвечающих нескольким условиям
Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.
В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).
Рис. 4. Использование функций баз данных
Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.
Дополнение от 29 марта 2017 г.
Функция суммирует ячейки по их формату(правая кнопка мыши на ячейке -Формат ячеек (Format cells) -вкладка Число (Number) ). К примеру: имеются данные по каким-либо финансовым операциям(продажи, суммы заключенных договоров, прибыль и т.д. и т.п.). И данные эти записаны по разному: где-то это формат долларов, где-то формат рублей, где-то гривны. А надо просуммировать отдельно гривны, отдельно доллары и отдельно рубли:
В Excel нет функции, которая бы суммировала данные, различая формат ячейки. Вот тогда-то и может пригодится эта функция.
Вызов команды через стандартный диалог:
Мастер функций-Категория "MulTEx"- СуммаЯчеек_Формат
Вызов с панели MulTEx:
Сумма/Поиск/Функции - Математические - СуммаЯчеек_Формат
Синтаксис:
=СуммаЯчеек_Формат( $E$2:$E$20 ; J8 ; I8 ; $A$2:$A$20 )
=СуммаЯчеек_Формат( $E$2:$E$20 ; J8 )
=СуммаЯчеек_Формат( $E$2:$E$20 ; J8 ; I8 )
ДиапазонСуммирования( $E$2:$E$20 ) - диапазон значений для суммирования. Можно указать несколько столбцов. Столбец с критерием(если планируется суммировать еще и по критерию) не обязательно должен входит в диапазон. Именно в этом диапазоне ищутся совпадения формата ячейки с указанным.
ЯчейкаОбразец( J8 ) - ячейка-образец формата. Ссылка на ячейку с форматом (ячейки именно с таким же форматом будут просуммированы).
Критерий( I8 ) - необязательный аргумент. Если указан, то суммируются ячейки с указанным критерием и форматом ячейки. Допускается применение в критерии символов подстановки - "*" и "?" . Если не указан, то суммируются все ячейки, к которым применен указанный формат. Если в диапазоне суммирования находятся ячейки с текстом, то они будут игнорироваться.
Так же данный аргумент может принимать в качестве критерия символы сравнения ( , =, <>, ):
- ">0" - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых больше нуля;
- ">=2" - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых больше или равно двум;
- " - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых меньше нуля;
- " - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых меньше или равно 60;
- "<>0" - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых не равно нулю;
- "<>" - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых не пустые;
- "*отчет*" - будут просуммированы все ячейки в ДиапазонеСуммирования, значения ячеек критериев для которых содержит слово "отчет";
Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: "<>"&D$1
ДиапазонКритерия( $A$2:$A$20 ) - Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан). ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСуммирования. Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСуммирования.
Важно: Функция не вычисляется при изменении формата. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2 - Enter . Либо нажать сочетания клавиш Shift + F9 (пересчет функций активного листа) или клавишу F9 (пересчет функций всей книги).
Примечание: данная функция не учитывает ячейки, к котjрым применено Условное Форматирование. Будет определен тот формат, который установлен через Формат ячеек стандартными средствами.
Видеоинструкции по использованию надстройки MulTEx
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Несколько нестандартная задача: получить сумму ячеек отдельно для долларов, отдельно для евро, отдельно для рублей, но в таблице они при этом записаны вперемешку:
И все эти доллары и рубли заданы для ячеек через формат ячеек(правая кнопка мыши на ячейке -Формат ячеек (Format Cells) -вкладка Число (Number) ).
Ни одна стандартная функция в Excel этого делать не умеет, т.к. не различает подобные форматы ячеек. Поэтому я решил написать функцию пользователя(UDF), которая исправляет эту несправедливость.
Если не знаете что такое функция пользователя советую сначала прочитать статью: Что такое функция пользователя(UDF)?.
При этом функция может работать только с видимыми ячейками. Т.е. если отфильтровать диапазон, то функция подсчитает данные только отфильтрованных ячеек.
Синтаксис функции:
без учета скрытых строк и столбцов:
=SumByNumberFormat( $A$1:$A$10 ; B1 )
все ячейки:
=SumByNumberFormat( $A$1:$A$10 ; B1 ;1)
rRange( $A$1:$A$10 ) - ссылка на диапазон с ячейками для суммирования.
rColorCell( B1 ) - ссылка на ячейка-образец с форматом ячейки.
bSumHide - Если указано ИСТИНА или 1 учитывает скрытые ячейки. ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются.
Как можно применить. Если посмотреть на картинку выше, то в ячейках D2:D4 уже просуммированы данные на основании формата приведенной выше функцией пользователя:
-
в D2: =SumByNumberFormat( $A$2:$A$8 ; C2 )
в D3: =SumByNumberFormat( $A$2:$A$8 ; C3 )
в D4: =SumByNumberFormat( $A$2:$A$8 ; C4 )
SumByFormat.xls (56,5 KiB, 983 скачиваний)
Чтобы подсчитывалось количество ячеек, а не их сумма, то функцию надо изменить самую малость:
Синтаксис и аргументы полностью идентичны с функцией SumByNumberFormat.
Что следует учитывать: функции подсчитывают и суммируют ячейки на основании формата, установленного вручную с панели. Если форматы ячеек созданы при помощи условного форматирования, то функции не определят формат такой ячейки. Это связано с особенностями создания визуального изменения свойств ячейки при помощи условного форматирования.
Так же функции не будут автоматически обновлять значения сразу после смены формата ячеек - это особенность Excel. Поэтому при изменении формата ячеек в вычисляемом диапазоне необходимо вручную пересчитать функцию( F2 - Enter ).
Как я уже писал ранее, профессиональная работа в Excel рано или поздно приведет вас к необходимости написания собственных функций. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, в Excel нет такой стандартной функции. Тем не менее, сумма по цвету ячеек в Excel может быть найдена!
Небольшой код VBA решит ваши проблемы. Для облегчения восприятия последующего материала откройте Excel-файл (он запакован в Zip, так как на сайте размещение файлов, содержащих макросы запрещено).
1. Убедитесь, что среди вкладок на ленте 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
Появится окно нового модуля, в которое следует перенести код:
При работе с функцией СУММ() в Excel следует учитывать особенности форматов данных, которые могут влиять на итоговые суммы при вычислениях.
Стоит обратить внимание на поведение самих форматов при работе с разными типами значений. При определенных условиях они могут автоматически изменяться и по-разному.
Ошибки суммирования при точке вместо запятой
Заполните ячейки значениями как показано на рисунке (в ячейке B2 точка вместо запятой):
Выделите диапазон A1:B3. Потом нажмите на закладке «Главная» инструмент «Сумма» (или нажмите ALT+=), чтобы автоматически просуммировать столбцы по отдельности.
Точка вместо запятой для Excel является текстом по умолчанию. Функция СУММ() пропускает текстовые значения при суммировании ячеек. В результате мы видим разные суммы.
Данная ошибка является опасной, особенно когда в таблице много данных. Во-первых, ее легко допустить, а во-вторых трудно заметить. Поэтому Excel в формате по умолчанию «Общий» выравнивает текст по левой стороне, а числа по правой. Если суммы не сходятся, стоит всем ячейкам задать формат «Общий», чтобы найти, где вместо запятой стоит точка. Но есть еще лучший способ.
Как найти текст в числовых значениях?
Чтобы быстро найти все ячейки, где точка вместо запятой нужно сделать следующее:
- Выберите инструмент «Главная»-«Найти и выделить»-«Перейти» (или нажмите комбинацию горячих клавиш CTRL+G).
- В появившимся окне нажмите на кнопку «Выделить».
- В окне «Выделение группы ячеек» нужно выбрать опцию «Константы» и отметить галочкой только «Текст». После чего нажать ОК.
Если точек слишком много, можно использовать инструмент «Главная»-«Найти и выделить»-«Заменить» (или комбинация клавиш CTRL+H). Он позволит в Excel заменить точку, на запятую – автоматически.
Внимание. Если перед поиском текстовых значений выше описанным методом был выделен диапазон, то поиск будет ограничен этим же диапазоном. Поэтому перед поиском точек лучше снять все выделения.
Читайте также: