Формула Excel для суммирования столбца конкретных валют

У меня в Excel есть следующий столбец.

Формула Excel для суммирования столбца конкретных валют

Я хотел бы иметь формулу Excel, которая может суммировать ячейки определенных валют. Ячейки в формате валюты. Пользовательская функция VBA тоже подойдет, но предпочтительнее формула Excel.

Я использую Excel 2016.

Обновлено: ячейки представлены в денежном формате. Префикс символа валюты впереди не является строкой в ​​ячейке.

Я не голосовал против, но я думаю, что это связано с отсутствием каких-либо усилий с вашей стороны, например, любой попытки записать формулу или функцию UDF. И если вы не предоставите это, вы, вероятно, получите больше голосов против

DisplayName 10.09.2018 07:05

Спасибо за отзыв. Проголосовал за ваш комментарий

user781486 10.09.2018 07:10

Я не знаю способа определить префикс валюты, используя только формулы. Единственное решение, которое я могу придумать без VBA, - это разделить данные на два отдельных столбца, в одном из которых указан тип валюты, а в другом - стоимость валюты. Тогда было бы легко суммировать в зависимости от типа валюты (например, SUMIF), но я не уверен, подойдет ли это решение для вас.

ImaginaryHuman072889 10.09.2018 13:52
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
2
3
2 165
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Итак, я пошел по маршруту UDF - дайте мне знать, работает ли это для вас. Если вам нужна помощь в том, как это запустить, дайте мне знать.

Синтаксис UDF - CurrencyVal (диапазон, который вы используете как «sumif», ячейку с форматированием, которое вы хотите суммировать).

Так например:

Если у меня есть диапазон (A2: A5), где A2 = евро, а все остальное - доллары США, то для получения суммы в долларах вы должны ввести следующее в любую ячейку = CurrencyVal (A2: A5, A3).

Option Explicit
Function CurrencyVal(SumCellRange As Range, CurrencySumCell As Range) As Integer

Dim Cell As Variant
Dim SumRange As Integer

For Each Cell In SumCellRange
    If Cell.NumberFormat = CurrencySumCell.NumberFormat Then
        SumRange = SumRange + Cell
    End If
Next Cell


CurrencyVal = SumRange


End Function

Спасибо за Ваш ответ. Проголосовали. Что такое A3 в вашей формуле? Может быть, лучше, чтобы второй аргумент был строкой типа «JPY» или «USD» для указания валюты?

user781486 10.09.2018 05:18

Да, я определенно согласен с тем, что было бы более полезно иметь описательный аргумент. Однако я выбрал текущий формат, так как Cell.NumberFormat не является простым «$» или «JPY» - он больше похож на ($ #, ###, - $ #, ###) - - следовательно, чтобы избежать обработки ошибок, я использовал то, что, как я знал, будет точным совпадением.

Dylan L 10.09.2018 05:19

UDF на основе регулярных выражений. Это основано на том, что валюта представлена ​​в виде текста, т.е. имеет в ячейке USD / EUR и т. д.

Option Explicit

Public Function GetCurrencySum(ByVal rng As Range, ByVal aCurrency As String) As Variant
    Dim inputString As String, arr()
    If rng.Columns.Count > 1 Then
        GetCurrencySum = CVErr(xlErrNA)
        Exit Function
    End If

    Select Case rng.Count
    Case 1
        ReDim arr(0): arr(0) = rng.Value
    Case Else
        arr = rng.Value
    End Select

    inputString = Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Index(arr, 0, 1)), "~") & "~"

    Dim matches As Object, match As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = "[+-]?" & aCurrency & ".*?(?=~)"
        On Error GoTo errhand:
        If .TEST(inputString) Then
            Set matches = .Execute(inputString)
            For Each match In matches
                 GetCurrencySum = GetCurrencySum + CDbl(Replace$(match, aCurrency, vbNullString))
            Next
            Exit Function
        End If
        GetCurrencySum = 0
        Exit Function
    End With
errhand:
    GetCurrencySum = CVErr(xlErrNA)
End Function

В листе:


Регулярное выражение:

Попробуйте здесь.

[+-]?JPY.*?(?=~)
/
gm

Соответствует одиночному символу, присутствующему в списке ниже [+-]?

Квантификатор ? - соответствует от нуля до единицы, столько раз, сколько возможно, отдача по мере необходимости (жадность) +- соответствует одному символу в списке +- (с учетом регистра)

JPY буквально соответствует символам JPY (с учетом регистра) '

.*? соответствует любому символу (кроме терминаторов строк) Квантификатор *? - соответствует от нуля до неограниченного количества раз, как можно меньше раз, расширяется по мере необходимости (ленивый)

Положительный прогноз (?=~)

Утверждают, что приведенное ниже регулярное выражение соответствует ~ буквально соответствует символу ~ (с учетом регистра)


Если в ячейке есть другой текст, попробуйте:

Public Function GetCurrencySum(ByVal rng As Range, ByVal aCurrency As String) As Variant
    Dim inputString As String, arr()
    If rng.Columns.Count > 1 Then
        GetCurrencySum = CVErr(xlErrNA)
        Exit Function
    End If

    Select Case rng.Count
    Case 1
        ReDim arr(0): arr(0) = rng.Value
    Case Else
        arr = rng.Value
    End Select

    inputString = Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Index(arr, 0, 1)), "~") & "~"

    Dim matches As Object, match As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = "[\-\+]?" & aCurrency & "\s[\d,.]+"
        On Error GoTo errhand:
        If .test(inputString) Then
            Set matches = .Execute(inputString)
            For Each match In matches
                  GetCurrencySum = GetCurrencySum + CDbl(Replace$(Replace$(match, aCurrency, vbNullString), "~", vbNullString))
            Next
            Exit Function
        End If
        GetCurrencySum = 0
        Exit Function
    End With
errhand:
    GetCurrencySum = CVErr(xlErrNA)
End Function

Попробуйте здесь.

Ваше решение будет работать, если префикс валюты, такой как «JPY» или «USD», действительно является строкой. Однако ячейка представлена ​​в денежном формате. Приставка впереди не является строкой в ​​ячейке.

user781486 10.09.2018 14:58

Это не было ясно из вашего вопроса tbh. В этом случае вам нужно использовать формат ячейки. Я оставлю ответ на случай, если кому-то в будущем понадобится решение на основе присутствующего текста.

QHarr 10.09.2018 14:58

Извини, это моя вина. Я добавлю эти подробности в вопрос.

user781486 10.09.2018 14:59

Я внес некоторые изменения в ответ Дилана, чтобы внести некоторые изменения в соответствии со своими предпочтениями. Я отправляю этот ответ на свой вопрос для дальнейшего использования.

Предположим, существует диапазон (A2: A5), где A2 = евро, а все остальное - доллары США, тогда, чтобы получить сумму в долларах США, вы должны ввести следующее в любую ячейку =GetCurrencySum(A2:A5, "[$USD] #,##0.00").

Function GetCurrencySum(SumCellRange As Range, CurrencyFormat As String) As Single
    On Error GoTo errorhd
    Dim Cell As Variant
    Dim SumRange As Single

    SumRange = 0
    For Each Cell In SumCellRange
        If Cell.NumberFormat = CurrencyFormat Then
            SumRange = SumRange + Cell
        End If
    Next Cell    

    GetCurrencySum = SumRange
    Exit Function
errorhd:
    MsgBox Err.Source & "-->" & Err.Description, , "CurrencyVal"
End Function

Другие вопросы по теме