Подсчитать сумму ячеек по формату

Несколько нестандартная задача: получить сумму ячеек отдельно для долларов, отдельно для евро, отдельно для рублей, но в таблице они при этом записаны вперемешку:
Суммирование ячеек по формату
И все эти доллары и рубли заданы для ячеек через формат ячеек(правая кнопка мыши на ячейке -Формат ячеек(Format Cells) -вкладка Число(Number)).
Ни одна стандартная функция в Excel этого делать не умеет, т.к. не различает подобные форматы ячеек. Поэтому я решил написать функцию пользователя(UDF), которая исправляет эту несправедливость.
Если не знаете что такое функция пользователя советую сначала прочитать статью: Что такое функция пользователя(UDF)?.
При этом функция может работать только с видимыми ячейками. Т.е. если отфильтровать диапазон, то функция подсчитает данные только отфильтрованных ячеек.

Option Explicit
 
'---------------------------------------------------------------------------------------
' Procedure : SumByNumberFormat
' Author    : The_Prist(Щербаков Дмитрий)
'             http://www.excel-vba.ru
' Purpose   : Функция суммирования ячеек на основе формата ячейки.
' Аргументы:
'             rRange     - диапазон с ячейками для суммирования.
'             rTemplCell - ячейка-образец формата ячейки
'             bSumHide   - ИСТИНА или 1 учитывает скрытые ячейки.
'                          ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются.
'---------------------------------------------------------------------------------------
Function SumByNumberFormat(rRange As Range, rTemplCell As Range, Optional bSumHide As Boolean = False)
    Application.Volatile  'закомментировать, чтобы функция не обновляла свои значения по нажатию Shift+F9(пересчет листа)
    Dim sFormat As String, rCell As Range, dblSum As Double, vVal
    sFormat = rTemplCell.NumberFormatLocal
    For Each rCell In rRange
        If rCell.NumberFormatLocal = sFormat Then
            vVal = rCell.Value
            If IsNumeric(vVal) Then
                If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then
                    If bSumHide Then dblSum = dblSum + vVal
                Else
                    dblSum = dblSum + vVal
                End If
            End If
        End If
    Next rCell
    SumByNumberFormat = dblSum
End Function

Синтаксис функции:
без учета скрытых строк и столбцов:
=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 уже просуммированы данные на основании формата приведенной выше функцией пользователя:

=SumByNumberFormat($A$2:$A$8;C2)

=SumByNumberFormat($A$2:$A$8;C3)

=SumByNumberFormat($A$2:$A$8;C4)

в столбце С при этом заданы нужные форматы для суммирования. Чтобы теперь определить сумму в единой валюте(например, в рублях), можно записать в ячейки Е3 и Е4 курс доллара и евро(для этого можно использовать функцию из этой статьи - Получить курс валют от ЦБР). После чего в отдельной ячейке записать формулу: =СУММ(D2;D3*E3;D4*E4)
Ниже приложен файл, в котором уже прописана функция и пример суммирования разных валют:
Скачать пример

SumByFormat.xls (56,5 КиБ, 1 283 скачиваний)

Чтобы подсчитывалось количество ячеек, а не их сумма, то функцию надо изменить самую малость:

'---------------------------------------------------------------------------------------
' Procedure : CountByNumberFormat
' Author    : The_Prist(Щербаков Дмитрий)
'             http://www.excel-vba.ru
' Purpose   : Функция подсчета ячеек на основе формата ячейки.
' Аргументы:
'             rRange     - диапазон с ячейками для суммирования.
'             rTemplCell - ячейка-образец формата ячейки
'             bSumHide   - ИСТИНА или 1 учитывает скрытые ячейки.
'                          ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не подсчитываются
'---------------------------------------------------------------------------------------
Function CountByNumberFormat(rRange As Range, rTemplCell As Range, Optional bSumHide As Boolean = False)
    Application.Volatile  'закомментировать, чтобы функция не обновляла свои значения по нажатию Shift+F9(пересчет листа)
    Dim sFormat As String, rCell As Range, lCnt As Long, vVal
    sFormat = rTemplCell.NumberFormatLocal
    For Each rCell In rRange
        If rCell.NumberFormatLocal = sFormat Then
            vVal = rCell.Value
            If IsNumeric(vVal) Then
                If vVal <> 0 Then
                    If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then
                        If bSumHide Then lCnt = lCnt + 1
                    Else
                        lCnt = lCnt + 1
                    End If
                End If
            End If
        End If
    Next rCell
    CountByNumberFormat = lCnt
End Function

Синтаксис и аргументы полностью идентичны с функцией SumByNumberFormat.

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

Так же функции не будут автоматически обновлять значения сразу после смены формата ячеек - это особенность Excel. Поэтому при изменении формата ячеек в вычисляемом диапазоне необходимо вручную пересчитать функцию(F2 -Enter).

Добавить комментарий Отменить ответ