Chyba każdy program księgowy ma taką funkcjonalności, że potrafi zamienić liczbową reprezentację kwoty na jej reprezentację słowną, czyli przykładowo:
123, 45zł -> sto dwadzieścia trzy złote i czterdzieści pięć groszy
Jak to zrobić w Excelu? Potrzeba odpowiedni kod. Niestety nie jest znany mi jego autor, bo na żadnej stronie, gdzie go widziałem nikt nie wiedział kto go napisał. Mi on się bardzo podoba, bo jak się go odpowiednio podzieli jest naprawdę prosty do zrozumienia.
Ja żeby lepiej go zrozumieć dołożyłem do niego jeszcze część odpowiedzialną za przekształcenie miliardów.
Kod składa się z dwóch funkcji, które z komentarzami zamieszczam poniżej:
Function Słownie(x As Variant) As String 'dla liczb od -999 999 999 999.99 do 999 999 999 999.99
'Sprawdzamy czy podana kwota jest mniejsza od zera jeśli jest to dopisujemy do kwoty słownie słowo minus
If x < 0 Then w = w & "minus "
'Konwertujemy liczbę na zapis, który będzie pokazywał zawsze miliardy miliony, tysiące i jednostki
'nawet jeśli będą to same zera
x = Format(Abs(x), "000 000 000 000.00")
'części odpowiedzialne za miliardy, miliony itd. przypisujemy do oddzielnych zmiennych 3 cyfrowych
'wiodące zera również są w nich brane pod uwagę, a do groszy musimy dodać jedna zero na początku
'żeby później działało odpowiednio z funckją trzy
mld = Left(x, 3): m = Mid(x, 5, 3): t = Mid(x, 9, 3): j = Mid(x, 13, 3): g = "0" & Right(x, 2)
'Sprawdzamy miliardy i w zależności od ich ilości nadajemy inne końcówki
'analogicznie robimy z milionami, tysiącami, jednościami i groszami
Select Case mld
Case 0
Case 1
w = "jeden miliard "
Case Else
'za pomocą funkcji trzy sprawdzamy jaka jest liczba miliardów, a późniejszych częściach milionów itd.
w = w & trzy(mld)
'dodajemy odpowiedni "końcówek" na podstawie połączeniu odpowiednich warunków
If Mid(mld, 2, 1) <> 1 And (Right(mld, 1) = 2 Or Right(mld, 1) = 3 Or Right(mld, 1) = 4) Then w = w & "miliardy " Else w = w & "miliardów "
End Select
'Sprawdzanie milionów analogicznie jak miliardów
Select Case m
Case 0
Case 1
w = "jeden milion "
Case Else
w = w & trzy(m)
If Mid(m, 2, 1) <> 1 And (Right(m, 1) = 2 Or Right(m, 1) = 3 Or Right(m, 1) = 4) Then w = w & "miliony " Else w = w & "milionów "
End Select
'Sprawdzanie tysięcy analogicznie jak miliardów
Select Case t
Case 0
Case 1
w = w & "jeden tysiąc "
Case Else
w = w & trzy(t)
If Mid(t, 2, 1) <> 1 And (Right(t, 1) = 2 Or Right(t, 1) = 3 Or Right(t, 1) = 4) Then w = w & "tysiące " Else w = w & "tysięcy "
End Select
'Sprawdzanie jedności analogicznie jak miliardów
Select Case j
Case 0
If mld = 0 And m = 0 And t = 0 Then w = w & "zero złotych " Else w = w & "złotych "
Case 1
If mld = 0 And m = 0 And t = 0 Then w = w & "jeden złoty " Else w = w & "jeden złotych "
Case Else
w = w & trzy(j)
If Mid(j, 2, 1) <> 1 And (Right(j, 1) = 2 Or Right(j, 1) = 3 Or Right(j, 1) = 4) Then w = w & "złote " Else w = w & "złotych "
End Select
'sprawdzanie groszy analogicznie jak miliardów
Select Case g
Case 0
w = w '& "zero groszy"
Case 1
w = w & "jeden grosz"
Case Else
w = w & trzy(g)
If Mid(g, 2, 1) <> 1 And (Right(g, 1) = 2 Or Right(g, 1) = 3 Or Right(g, 1) = 4) Then w = w & "grosze" Else w = w & "groszy"
End Select
Słownie = w
End Function
Function trzy(x As Variant) As String
'dzielimy trzy cyfry na część odpowiedzialną za setki, dziesiątki i jedności
x3 = Val(Left(x, 1)): x2 = Val(Mid(x, 2, 1)): x1 = Val(Right(x, 1))
'Spradzamy jaka to setka
If x3 = 9 Then w = w & "dziewięćset "
If x3 = 8 Then w = w & "osiemset "
If x3 = 7 Then w = w & "siedemset "
If x3 = 6 Then w = w & "sześćset "
If x3 = 5 Then w = w & "pięćset "
If x3 = 4 Then w = w & "czterysta "
If x3 = 3 Then w = w & "trzysta "
If x3 = 2 Then w = w & "dwieście "
If x3 = 1 Then w = w & "sto "
'Sprawdzamy jaka to dziesiątka łącząc z ewentualną setką
If x2 = 9 Then w = w & "dziewięćdziesiąt "
If x2 = 8 Then w = w & "osiemdziesiąt "
If x2 = 7 Then w = w & "siedemdziesiąt "
If x2 = 6 Then w = w & "sześćdziesiąt "
If x2 = 5 Then w = w & "pięćdziesiąt "
If x2 = 4 Then w = w & "czterdzieści "
If x2 = 3 Then w = w & "trzydzieści "
If x2 = 2 Then w = w & "dwadzieścia "
'Sprawdzamy czy to nie jest nastka łącząc z ewentualną setką
If x2 = 1 Then
If x1 = 9 Then w = w & "dziewiętnaście "
If x1 = 8 Then w = w & "osiemnaście "
If x1 = 7 Then w = w & "siedemnaście "
If x1 = 6 Then w = w & "szesnaście "
If x1 = 5 Then w = w & "piętnaście "
If x1 = 4 Then w = w & "czternaście "
If x1 = 3 Then w = w & "trzynaście "
If x1 = 2 Then w = w & "dwanaście "
If x1 = 1 Then w = w & "jedenaście "
If x1 = 0 Then w = w & "dziesięć "
End If
'Sprawdzamy jaka to cyfra jedności i dołączamy do ewentualnej wcześniejszej części
If x2 <> 1 Then
If x1 = 9 Then w = w & "dziewięć "
If x1 = 8 Then w = w & "osiem "
If x1 = 7 Then w = w & "siedem "
If x1 = 6 Then w = w & "sześć "
If x1 = 5 Then w = w & "pięć "
If x1 = 4 Then w = w & "cztery "
If x1 = 3 Then w = w & "trzy "
If x1 = 2 Then w = w & "dwa "
If x1 = 1 Then w = w & "jeden "
End If
trzy = w
End Function
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Bill Szysz pod poradą 294 napisał jeszcze jedną ciekawą formułę na wyciąganie liczb z początku lub końca tekstu. Ważne jest, że formuła, którą zobaczymy wyciąga liczby, czyli nie zwróci wiodących zer. Za to jest prostą formułą w porównaniu do tych, które stosowaliśmy.
Będziemy musieli wyciągać coraz dłuższe ciągi z naszego tekstu odpowiednio z prawej strony (od końca – funkcja PRAWY) lub z lewej strony (od początku – funkcja LEWY). Zwiększającą się długość zapewnimy sobie za pomocą funkcji WIERSZ, która będzie wskazywała na zablokowany zakres – załóżmy, że od komórki A1 do A17, ale śmiało może przedłużyć ten zakres jeśli masz dłuższe teksty. Ważne, że zakres musi zaczynać się od komórki A1 (pierwszego wiersza) i być zablokowanym, żeby się nie przesuwał.
=PRAWY(A2;WIERSZ($A$1:$A$17))
Śmiało możesz zaznaczyć więcej wierszy nisz masz w tekście, bo w takiej sytuacji funkcja PRAWY (i analogicznie LEWY) wyciągają cały tekst.
Wartości zwracane przez funkcje tekstowe są traktowane jako tekst, więc musimy je przekonwertować na liczby. Z wcześniejszych porad znamy już tą sztuczkę – to podwójna negacja – dwa znaki minus. Jeśli dany tekst da się przekonwertować na liczbę to Excel to zrobi. Jeśli jest to niemożliwe, to będzie zwracał błąd:
W takiej tablicy wystarczy znaleźć największą liczbę. Można to zrobić za pomocą funkcji WYSZUKAJ i szukania dużej liczby np.: 9 do 99 potęgi:
=WYSZUKAJ(9^99;–PRAWY(A2;WIERSZ($A$1:$A$17)))
To już cała formuła ? Analogicznie przy szukaniu liczb od początku tekstu musimy zamienić tylko funkcję PRAWY na LEWY i gotowe.
=WYSZUKAJ(9^99;–LEWY(D2;WIERSZ($A$1:$A$17)))
Na koniec trzeba powiedzieć o jeszcze jednym ograniczeniu tego sposobu – jeśli Excel jest wstanie zamienić jakiś zapis na liczbę (datę) za pomocą podwójnej negacji to to zrobi. Np. tekst „23 Czekolada” powinien zwracać poprawnie 23, ale wynik formuły jest całkiem inny:
Wynika to, że w kolejnych wyciąganych tekstach pojawia się zapis „23 Cze”, który Excel tłumaczy sobie jako 23 czerwca bieżącego roku i zamienia na odpowiednią reprezentację liczbową. Stąd taki niespodziewany wynik formuły.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Potrzebujemy wyciągnąć wszystkie cyfry z tekstu jako liczbę. Nasze ograniczenia są takie, że nie możemy skorzystać z pomocy funkcji POŁĄCZ.TEKSTY czy ZŁĄCZ. TEKST, a dodatkowo nasza formuła nie może być zatwierdzana za pomocą kombinacji klawiszy Ctrl + Shift + Enter. To ciężkie zadanie, ale już ktoś taką formułę wymyślił:
Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.
Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:
=ADR.POŚR("1:"&DŁ(A2))
Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:
Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów
Wynikiem tej formuły będzie tablica z pozycjami cyfr i zerami tam gdzie cyfr nie było:
{1;0;0;0;0;0;0;0;0;0;0;12;13;14}
Do naszych potrzeb potrzebujemy ustawić tą tablicę od największych wartości do najmniejszych, czyli skorzystamy z funkcji MAX.K, ale zanim to zrobimy wykonamy pewną sztukę, która sprawi, że na zakończenie nie będziemy musieli zatwierdzać naszej formuły jako formuły tablicowej. Sztuczka ta polega na wstawieniu naszej formuły do funkcji INDEKS i wyciągnięcia całego wiersza danych wpisując w drugim argumencie funkcji INDEKS wartości zero.
Nie wiem dokładnie dlaczego ta sztuczka działa, bo podglądając wynik wygląda on tak samo z i bez funkcji INDEKS, ale najważniejsze, że działa. Teraz jak wspomnieliśmy wkładamy naszą formułę do funkcji MAX.K, a jako argument k posłuży nam znów liczenie po kolei wierszy.
Podglądając wynik zobaczymy najprawdopodobniej dużo zer.
{14;13;12;1;0;0;0;0;0;0;0;0;0;0}
Niestety będą nam te zera przeszkadzać później w funkcji FRAGMENT.TEKSTU, bo nie możemy wyciągać znaków od zerowego znaku (taki nie istnieje). Żeby się ich pozbyć po prostu dodajemy jedynkę do naszej formuły (+1). Jednak jeśli wykonamy taką operacje to przesuną się znaki, które będziemy wyciągać, dlatego dodatkowo dołożymy na początek naszego tekstu zero (0&A2)
Musimy pamiętać, że to znowu są liczby, które Excel traktuje jako tekst. Musimy te wartości przemnożyć przez liczby w zależności od ich pozycji. Dokładnie pierwszą wartość potrzebujemy przemnożyć przez 1, drugą przez 10, trzecią przez 100 itd. Są to kolejne potęgi 10, więc wystarczy, żebyśmy 10 podnieśli do potęgi pozycji cyfry na liście. Czyli znów korzystamy z listy wierszy:
10^WIERSZ(ADR.POŚR("1:"&DŁ(A2)))/10
Tylko potęgowanie musimy podzielić przez 10, żebyśmy zaczęli od 1, a nie od 10.
Przed nami ciężkie zadanie wyciągnięcia wszystkich cyfr. Okazuje się ono dużo prostsze jeśli w Twojej wersji Excel 2016 (lub 365) masz zainstalowany już update z funkcją POŁĄCZ.TEKSTY i ZŁĄCZ.TEKST (ważne! bez Y na końcu, bo ZŁĄCZ.TEKSTY działa inaczej).
Jeśli masz te funkcję to czytaj dalej. Jeśli nie to zajrzyj do następnej porady (296).
Żeby wyciągnąć wszystkie cyfry musimy wyciągnąć każdy znak i sprawdzić, czy jest on cyfrą/liczbą. Możemy to zrobić za pomocą funkcji FRAGMENT.TEKSTU, która wyciąga z testu fragment od wskazanego numeru znaków o wskazanej długości. W tym przykładzie będziemy chcieli wyciągać pojedyncze znaki, czyli długość tekstu = 1. Większym problemem jest to, że potrzebujemy wyciągnąć każdy pojedynczy znak, czyli najpierw wyciągamy pierwszy znak, potem drugi itd, czyli potrzebujemy tablicy znaków, która liczy od 1 do ilości znaków w tekście, który analizujemy.
Żeby to zrobić skorzystamy ze sztuczki, którą znajdziesz w internecie – wykorzystując funkcję ADR.POŚR odwołamy się od wiersza 1 do długości ciągu tekstowego:
=ADR.POŚR("1:"&DŁ(A2))
Tylko to dużo więcej niż potrzebujemy. Nam wystarczą tylko numery wierszy, czyli wstawiamy powyższą formułę do funkcji WIERSZ.
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Właśnie zbudowaliśmy tablicę numerującą znaki do funkcji FRAGMENT.TEKSTU.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Wystarczy ją tylko w niej umieścić pamiętając, że zawsze wyciągamy pojedyncze znaki:
Tylko poważnym problemem jest to, że funkcja FRAGMENT.TEKSTU wszystkie znaki zwraca jako tekst (są w podwójnych cudzysłowach). Na szczęście dowolna operacja matematyczna na liczbie zapisanej jako tekst zamienia ją ponownie na „prawdziwą” liczbę ? Skorzystamy z tutaj podwójnej negacji, czyli dwóch znaków minus przed funkcją. Wynikiem tego będzie tablica liczb i błędów
Na szczęście jest nam to na rękę ponieważ nam zależy tylko na liczbach, a tam gdzie pojawił się błąd nie chcemy nic wyświetlać, więc wystarczy, że wstawimy naszą formułę do funkcji JEŻELI.BŁĄD, która zamiast błędów będzie zwracała pusty ciąg tekstowy (dwa podwójne cudzysłowy)
Teraz formułą zwraca tablicę cyfr i pustych ciągów znaków,
{5;"";"";"";"";"";"";"";"";"";"";1;0;0}
którą możemy łatwo połączyć za pomocą funkcji ZŁĄCZ.TEKST (przypominam bez Y na końcu) lub POŁĄCZ.TEKSTY jeśli zależy nam, żeby rozdzielać cyfry konkretnym ogranicznikiem, a nie bezpośrednio łączyć je ze sobą, ale w większości sytuacji chcemy je bezpośrednio łączyć, więc formuła będzie wyglądać tak:
Chcemy wyciągnąć liczbę z początku lub końca tekstu. Najpierw zajmiemy się pisaniem formuły do wyciągnięcia liczby z końca tekstu.
Jednym ze sposobów jest zaczęcie od znalezienie pozycji kolejnych cyfr. Możemy wykorzystać funkcję SZUKAJ.TEKST lub ZNAJDŹ. Obie znajdują pozycję szukanego tekstu (w naszym przykładzie liczby) w przeszukiwanym tekście, ale funkcja SZUKAJ.TEKST nie zwraca uwagi na wielkość liter, a funkcja ZNAJDŹ zwraca uwagę na wielkość liter. Ponieważ szukamy cyfr skorzystamy z funkcji, która ma krótszą nazwę.
Dalej ważne jest, że chcemy znaleźć pierwszą pozycję każdej cyfry. Żeby to zrobić musimy wpisać wszystkie cyfry w nawiasach klamrowych oddzielone slashami
{0\1\2\3\4\5\6\7\8\9}
Można sobie ułatwić życie i stworzyć serię cyfr w wierszu, odwołać się do niej, a następnie zaznaczyć całe odwołanie do tej serii i nacisnąć klawisz F9 – wtedy Excel zamiast odwołania wstawi potrzebną tablicę.
To, że zamiast odwołania do komórek skorzystamy z tablicy wpisanej na stałe (hardkodowej) pozwoli nam zatwierdzać formułę zwyczajnie, a nie jako formułę tablicową (za pomocą kombinacji klawiszy Ctrl + Shift + Enter).
=ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2)
Jednak przy wyszukiwaniu poszczególnych cyfr pojawią się błędy, gdy danej cyfry nie ma w przeszukiwanym tekście.
Obsługa błędów może być kłopotliwa, dlatego skorzystamy ze sztuczki, która ułatwi nam zadanie – do oryginalnego tekstu dołożymy na koniec ciąg wszystkich cyfr.
=ZNAJDŹ({0\1\2\3\4\5\6\7\8\9}; A2&"0123456789")
Dzięki temu zawsze znajdziemy każdą cyfrę, co najwyżej poza długością przeszukiwanego tekstu.
={10\13\14\15\16\11\18\19\20\21}
Ponieważ nas interesuje pierwsze wystąpienie jakiejkolwiek cyfry dlatego naszą dotychczasową formułę wstawimy do funkcji MIN.
Teraz żeby odnaleźć ile znaków chcemy wyciągnąć z prawej strony tekstu (funkcja PRAWY) wystarczy znalezioną pozycję pierwszej cyfry odjąć od długości całego tekstu i dodać korygującą jedynkę.
Jak wyciągnęliśmy liczbę z końca tekstu to możemy teraz w łatwy sposób wyciągnąć sam tekst odejmując od długości całego tekstu długość liczby.
=LEWY(A2;DŁ(A2)-DŁ(C2))
Teraz zajmiemy się przypadkiem, gdy nasza liczba jest na początku tekstu. Musimy podejść do tego inaczej. Zaczniemy od tego, że usuniemy z tekstu po kolei każdą cyfrę za pomocą funkcji PODSTAW i pustego ciągu znaków:
=PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};"")
Uzyskamy wtedy dziesięć tekstów, w których nie będą się znajdować poszczególne cyfry, czyli w większości będzie to cały tekst, bo danej cyfry już na początku nie będzie w tekście.
Tylko, że nam nie są potrzebne same teksty, tylko ich długość, czyli naszą formułę wstawiamy do funkcji DŁ. Znowu ponieważ wpisaliśmy na stałe tablicę cyfr (hardkodowanie) nie musimy zatwierdzać naszej formuły jako formuły tablicowej.
=DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))
Dzięki temu uzyskamy długość tekstu po usunięciu każdej kolejnej cyfry.
={12\13\13\13\13\12\13\13\13\13}
Czasami widać różnicę, do oryginalnej długości tekstu i właśnie tej różnicy szukamy. Wystarczy, że od długości oryginalnego tekstu odejmiemy te poszczególne długości.
=DŁ(I2)-DŁ(PODSTAW(I2;{0\1\2\3\4\5\6\7\8\9};""))
Wynikiem będzie tablica różnic
={1\0\0\0\0\1\0\0\0\0}
Zostaje nam ją tylko zsumować i mamy długość całej liczby, która znajduje się na początku tekstu.
