У меня в Excel есть следующий столбец.
Я хотел бы иметь формулу Excel, которая может суммировать ячейки определенных валют. Ячейки в формате валюты. Пользовательская функция VBA тоже подойдет, но предпочтительнее формула Excel.
Я использую Excel 2016.
Обновлено: ячейки представлены в денежном формате. Префикс символа валюты впереди не является строкой в ячейке.
Спасибо за отзыв. Проголосовал за ваш комментарий
Я не знаю способа определить префикс валюты, используя только формулы. Единственное решение, которое я могу придумать без VBA, - это разделить данные на два отдельных столбца, в одном из которых указан тип валюты, а в другом - стоимость валюты. Тогда было бы легко суммировать в зависимости от типа валюты (например, SUMIF
), но я не уверен, подойдет ли это решение для вас.
Итак, я пошел по маршруту 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» для указания валюты?
Да, я определенно согласен с тем, что было бы более полезно иметь описательный аргумент. Однако я выбрал текущий формат, так как Cell.NumberFormat не является простым «$» или «JPY» - он больше похож на ($ #, ###, - $ #, ###) - - следовательно, чтобы избежать обработки ошибок, я использовал то, что, как я знал, будет точным совпадением.
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», действительно является строкой. Однако ячейка представлена в денежном формате. Приставка впереди не является строкой в ячейке.
Это не было ясно из вашего вопроса tbh. В этом случае вам нужно использовать формат ячейки. Я оставлю ответ на случай, если кому-то в будущем понадобится решение на основе присутствующего текста.
Извини, это моя вина. Я добавлю эти подробности в вопрос.
Я внес некоторые изменения в ответ Дилана, чтобы внести некоторые изменения в соответствии со своими предпочтениями. Я отправляю этот ответ на свой вопрос для дальнейшего использования.
Предположим, существует диапазон (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
Я не голосовал против, но я думаю, что это связано с отсутствием каких-либо усилий с вашей стороны, например, любой попытки записать формулу или функцию UDF. И если вы не предоставите это, вы, вероятно, получите больше голосов против