W dzisiejszej notce przedstawię sposób na sumowanie kwot wg waluty. Temat przewijał się w internecie dawno temu, ale jego rozwiązanie jest na tyle ciekawe, że zdecydowałem się do niego powrócić.
Problem
Excel nie posiada funkcji, która pozwalałaby sumować liczby według ich formatu.
Jeżeli mamy podane różne kwoty (w złotówkach, euro, dolarach czy funtach), to sumowanie wg waluty staje się wyzwaniem. Na szczęście z pomocą przychodzi VBA.
Rozwiązanie
Aby rozwiązać problem należy wykonać kilka czynności. Po pierwsze, utworzyć własną funkcję, która w wyniku będzie zwracać symbol danej waluty. Po drugie, zsumować kwoty, przyjmując za kryterium symbol waluty zwrócony wcześniej przez funkcję VBA.
Symbol Waluty
Funkcja do pobrania symbolu waluty może wyglądać w ten sposób.
Public Function SymbolWaluty(Komorka As Range) As String
'// Funkcja wyciąga z tekstu pojedyncze znaki, które nie są cyframi.
'// Funkcja pomija także znak spacji i separator dziesiętny.
Dim sTekst As String ' Tekst źródłowy po wykluczeniu zbędnych spacji
Dim sZnak As String ' Pojedynczy znak (cyfra, litera, symbol, przecinek)
Dim sSymbol As String ' Symbol waluty, wynik działania funkcji
Dim iLicznik As Integer ' Licznik w pętli
Const sSEPARATOR As String = "," ' Separator liczb dziesiętnych, przecinek
'Pobierz do zmiennej oryginalny tekst (włącznie z formatowaniem) i skasuj zbędne odstępy
1 sTekst = Trim(Komorka.Text)
'Przejedź w pętli po wszystkich znakach tekstu
2 For iLicznik = 1 To Len(sTekst)
'Pobierz do zmiennej String pojedynczy znak
3 sZnak = Mid(sTekst, iLicznik, 1)
'Sprawdź czy podany znak nie jest liczbą
4 If Not IsNumeric(sZnak) Then
'Jeśli nie jest liczbą, sprawdź czy nie jest również separatorem lub spacją
5 If (sZnak <> sSEPARATOR) And (sZnak <> Space(1)) Then
'Dopisz ten znak do ciągu tekstowego
6 sSymbol = sSymbol & sZnak
7 End If '// If sZnak <> sSEPARATOR And sZnak <> Space(1) Then
8 End If '// If Not IsNumeric(sZnak) Then
9 Next iLicznik '// For iLicznik = 1 To Len(sTekst)
'Przypisz do wyniku wartość zmiennej sSymbol
10 SymbolWaluty = sSymbol
End Function
Analiza Kodu
Funkcja posiada jeden argument – komórkę z kwotą i cztery zmienne.
Zmienne
Pierwsza zmienna sTekst odwołuje się do właściwości Text obiektu Range. Właściwość ta w wyniku zwraca ciąg tekstowy – wartość komórki z jej formatem liczbowym – czyli np. 454,8 zł. Funkcja TRIM dodatkowo usuwa zbędne spacje z tekstu.
Stała sSEPARATOR przechowuje separator dziesiętny ustawiony w Excelu. Dla polskich ustawień domyślnie jest to przecinek, dla innych krajów przeważnie kropka.
Zmienna sZnak przechowuje każdy pojedynczy znak zmiennej sTekst.
Zmienna iLicznik to prosty licznik pętli, w którym będziemy „przechodzić” po wszystkich znakach zmiennej sTekst.
W zmiennej sSymbol będziemy przechowywać wynik działania funkcji SymbolWaluty.
Pętla
Funkcja LEN określa długość tekstu. Jak wspomniałem, w pętli będziemy „przechodzić” po wszystkich pojedynczych znakach zmiennej sTekst.
Funkcja MID zwraca fragment tekstu. Pierwszy argument funkcji to nasz tekst, z którego chcemy pobrać dane (sTekst). Drugi argument określa miejsce w tekście, od którego chcemy zacząć pobieranie (iLicznik – czyli najpierw zaczynamy od pierwszego znaku, potem drugiego itd.). Trzeci, opcjonalny argument określa liczbę znaków, jaką chcemy pobrać (1 – bo za każdym razem chcemy pobrać tylko jeden znak). Dla tekstu 454,8 zł, zmienna sZnak przyjmie najpierw wartość „4” (wynik typu tekstowego).
Naszą intencją jest to, aby odrzucić wszystkie cyfry, a wyciągnąć tylko te znaki, które są tekstem. Za pomocą funkcji ISNUMERIC sprawdzamy czy podany znak jest liczbą, czy też nie. Pomimo tego, że zmienna sZnak jest zmienną typu String (w wyniku otrzymujemy „4” a nie 4) to VBA dokonuje automatycznej konwersji i traktuje „4” jako liczbę, zatem warunek postawiony w tej linii kodu nie zostaje spełniony i pętla przechodzi do następnego znaku.
Jeżeli dany znak nie jest liczbą wówczas pętla przechodzi do następnej linii, aby sprawdzić dwa następne warunki. Ponieważ chcemy z wyniku wyeliminować wszystkie spacje, a także separator dziesiętny (przecinek) to musimy sprawdzić obydwa warunki.
Jeżeli wszystkie trzy warunki są spełnione (znak nie jest liczbą, spacją, ani separatorem dziesiętnym) wtedy ten pojedynczy znak zostaje dodany do wyniku funkcji. W efekcie dla 454,8 zł otrzymujemy wynik zł – pierwsze trzy cyfry, a także cyfra po przecinku są odrzucane przy pierwszym warunku. Przecinek i spacja w następnej linijce kodu. Znaki z i ł spełniają wszystkie warunki, zatem to właśnie ich połączenie otrzymujemy w wyniku funkcji.
Screen
W kolumnie C znajduje się wynik działania funkcji SymbolWaluty.
Jak łatwo zauważyć, argumentem dla funkcji jest zawsze komórka znajdująca się w tym samym wierszu, w kolumnie B. Aby zsumować kwoty dla poszczególnych walut wystarczy skorzystać z wbudowanej w Excelu funkcji SUMA.JEŻELI.
Opis Funkcji
Opcjonalnie, możemy dodać również opis dla funkcji, tak aby pojawiał się w okienku podczas jej wywoływania.
Kod, który to realizuje wygląda tak:
Public Sub DodajOpisFunkcjiSymbolWaluty()
'// Makro dodaje opis do funkcji SymbolWaluty
Dim sOpisFunkcji As String ' Opis funkcji "SymbolWaluty"
Dim sOpisArgumentu As String ' Opis argumentu "Komorka"
Dim iKategoria As Integer ' Kategoria, do której dodamy funkcję
'Zdefiniuj zmienne
1 sOpisFunkcji = "Funkcja zwraca w wyniku tekst. " & _
"Kasuje cyfry, spację i separator dziesiętny z oryginalnego wpisu"
2 sOpisArgumentu = "Pojedyncza komórka zawierająca liczbę sformatowaną jako kwota."
3 iKategoria = 7 ' Funkcje tekstowe
'Przypisz opis
4 Application.MacroOptions _
Macro:="SymbolWaluty", _
Description:=sOpisFunkcji, _
Category:=iKategoria, _
ArgumentDescriptions:=Array(sOpisArgumentu)
End Sub
