W tej notce przedstawię sposób na sumowanie według waluty. Excel nie posiada w swoim potężnym arsenale takiej funkcji, więc musimy ją sobie stworzyć sami. Jak to zrobić? Najlepiej odwołując się do pewnej właściwości obiektu Range.
Koncepcja rozwiązania
Aby rozwiązać problem należy wykonać dwie czynności.
- Napisać własną funkcję VBA, która w wyniku zwróci symbol danej waluty.
- Zsumować kwoty warunkowo, przyjmując za kryterium symbol waluty zwrócony wcześniej przez funkcję VBA.
Symbol waluty
Funkcja do pobierania symbolu waluty może wyglądać w ten sposób.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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 oryginalny tekst (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 też 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 sprawdzać każdy znak 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 z rozwiązaniem
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.
Cała operacja pt: Sumowanie według waluty została więc zakończona sukcesem.
Opis funkcji
Opcjonalnie, możemy dodać również opis dla naszej funkcji VBA, tak aby pojawiał się w okienku podczas jej wywoływania.
Kod, który to realizuje wygląda tak:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
