Jak zapisać kwotę słownie w arkuszu Google – bez użycia skryptów

Momi celem było poskładanie formuły, która zmieści się w jednej komórce i zamieni kwotę zapisaną liczbami w słowa. Dla osób które wystawiają faktury w arkuszach, albo inne dokumenty wymagające pisania kwot słownie.

Dla leniwych – w pliku roboczym jest gotowa formuła

Dla wnikliwych tworzenie całej formuły w podziale na etapy:

Liczba jednocyfrowa słownie

Tu jest stosunkowo prosto. Używamy funkcji index, która pozwala na wybranie fragmentu tabeli. W tym wypadku nie odwołujemy się do jakiegoś zakresu z arkusza, ale tabelę definiujemy wewnątrz samej funkcji: {element1;element2;element3;….}, drugi składnik formuły mówi, który z elementów tabeli należy wyświetlić.

W B2 występuje czwórka, więc funkcja index wyświetla czwarty element tabeli.

Liczba mniejsza od 20

Jak pewnie zauważyliście – liczby możemy podzielić na te mniejsze od 20, które są skonstruowane w jeden sposób (1-9), a potem jedenaście, dwanaście, etc. oraz liczby jak dwadzieścia i więcej, gdzie mamy słowo określające liczbę dziesiątek i liczbę jedności.

Wszystkie liczby mniejsze od dwudziestu możemy opracować w taki sam sposób jak liczby 1-10, używając formuły INDEX.

Liczby z zakresu 20-99

Tutaj mamy konstrukcję: określające liczbę dziesiątek, a po nim drugie słowo określające liczbę jedności. Przy czym, tego drugiego słowa może nie być wcale (np. 80).

Najpierw wydzielamy liczbę dziesiątek. Robimy to przy pomocy formuły INT (część całkowita). Wystarczy, że podzielimy liczbę przez 10 i wyciągniemy część całkowitą, to uzyskamy liczę dziesiątek. Przykład:

34/10 = 3,4

INT(3,4) = 3 – i to jest nasza poszukiwana liczba dziesiątek.

Do tego musimy dorzucić liczbę jedności. Tu posłużymy się funkcją MOD – czyli resztą z dzielenia

Składnia =MOD(dzielna;dzielnik)

MOD(34;10) = 4 – i to jest nasza liczba jedności.

Dla liczby dziesiątek budujemy tabelę słów: dwadzieścia, trzydzieści, etc. Z tym, że musimy dodać na początku puste pole. W ten sposób jak wskażemy liczbę 2 to funkcja wyświetli nam właściwe słowo.

W przypadku liczby jedności działamy podobnie jak z liczbą jednocyfrową, czyli tworzymy listę słów: jeden, dwa, trzy, etc… Jednak musimy wziąć pod uwagę też liczby bez jedności. Jak czterdzieści, pięćdziesiąt. Nie możemy w funkcji INDEX wyświetlić zerowego elementu (wtedy wypluwa całą tabelę). Więc dodajemy z przodu jeden element pusty. A do reszty z dzielenia zawsze będziemy dodawać 1.

Czyli jeśli padnie 24 to reszta z dzielenia wynosi 4, po dodaniu 1 – mamy wyświetlić piąty element tabeli. Czyli “cztery”. Jeśli reszta z dzielenia to będzie zero – wyświetlony zostanie pierwszy – pusty element tabeli.

Żeby wyświetlić oba słowa w jednej komórce, należy skleić formuły wyświetlające dziesiątki i jedności przy pomocy znaku & oraz dodać jeszcze spację między nimi ” “. Czyli =Formuła&" "&Formuła

I gotowe!

Liczba z zakresu 100-999

Tutaj działamy podobnie. Musimy wyciągnąć liczbę setek, liczbę dziesiątek i liczbę jedności.

Liczba setek to część całkowita z dzielenia przez 100:

345 / 100 = 3,45

INT(3,45)=3

Ponownie tworzymy listę słów i korzystamy z funkcji indeks, która wyświetla nam element tabeli o odpowiednim numerze.

Kolejny nowy krok to wydzielenie liczby dziesiątek z liczby trzycyfrowej. Robimy to biorąc resztę z dzielenia przez 100: MOD(345;100)=45 , następnie dzielimy przez 10 (uzyskujemy 4,5) i wyciągamy część całkowitą (4).

Ogarnięcie liczb w stylu 312, 306

Musimy wziąć pod uwagę, że możemy trafić na liczbę 312, czy 306, czyli że nie możemy w celu wyświetlania dziesiątek, swobodnie skorzystać z funkcji podającej słownie liczbę z zakresu 20-99. Musimy zrobić rozgałęzienie:

“Jeśli liczba dziesiątek jest mniejsza od 2, to użyj funkcji dla liczb 0-19, a jeśli większa lub równa to funkcji dla liczb 20-99. “

To wymaga również stworzenia rozgałęzienia w przypadku jedności. Jeśli wcześniej podamy np. dwanaście, to nie chcemy już wyświetlać dodatkowego słowa “dwa”.

“Jeśli liczba dziesiątek jest mniejsza od 2 to zostaw puste pole, a jak jest inaczej to użyj funkcji podającej jedności”.

=IF (int(mod(B8;100)/10<2;"";funkcja od jedności)

Radzimy sobie z pełną setką

Jest jeszcze jeden mały kruczek o który musimy uzupełnić funkcję podającą liczby od 1 do 19 w przypadku dużych liczb powyżej 100 – musimy obsłużyć pełną setkę (np. liczbę 300). W takim wypadku liczba dziesiątek i jedności ma pozostać pusta. Zatem przesuniemy wszystkie elementy tabeli o 1, tak jak to robiliśmy wcześniej. I pierwszy element będzie pusty. Zatem jak liczba dziesiątek i jedn. wyniesie 0, wyświetlimy pierwszy element.

Liczby z zakresu 0-999

Łatwo możemy sprawić by funkcja działała również dla liczb mniejszych od 100. Wystarczy, żeby wyświetlała się pusta wartość setek, jeśli liczba jest mniejsza od 100 (czyli jeśli część całkowita z dzielenia przez 100 równa się 0)

Dodajemy tylko jeden pusty element tablicy na początku, i dodajemy 1 do każdego wyniku dzielenia.

Teraz pokleimy to co uzyskaliśmy.

Żeby uzyskać pełną liczbę musimy skleić funkcje:

funkcja od setek &” “& funkcja od dziesiątek &” “& funkcja od jedności

Razem stworzy nam to sporego tasiemca. Potrzebujemy jeszcze zlikwidować zbędne spacje, które pojawią się jeśli któraś z wartości będzie pusta. Dlatego wszystko opakujemy funkcją TRIM() wycinającą nadmiarowe spacje.

Tak wygląda działająca całość:

A to treść tej funkcji (b14 to komórka w której znajduje się liczba).

=trim( index({"";"sto";"dwieście";"trzysta";"czterysta";"pięćset";"sześćset";"siedemset";"osiemset";"dziewięćset"};int(B14/100)+1)& " "& if(int(mod(B14;100)/10)<2; index({"";"jeden";"dwa";"trzy";"cztery";"pięć";"sześć";"siedem";"osiem";"dziewięć";"dziesięć";"jedenaście";"dwanaście";"trzynaście";"czternaście";"piętnaście";"szesnaście";"siedemnaście";"osiemnaście";"dziewiętnaście"};mod(B14;100)+1); index({"";"dwadzieścia";"trzydzieści";"czterdzieści";"pięćdziesiąt";"sześćdziesiąt";"siedemdziesiąt";"osiemdziesiąt";"dziewięćdziesiąt"};int(mod(B14;100)/10)))& " "& if(int(mod(B14;100)/10)<2; ""; index({"";"jeden";"dwa";"trzy";"cztery";"pięć";"sześć";"siedem";"osiem";"dziewięć"};mod(B14;10)+1)) )

Obsługujemy tysiące (1-999 999)

Na pierwszy rzut oka widać, że będziemy kleić dwie liczby.

Liczba tysięcy, słowo “tysiące” lub “tysięcy” lub “” i dalej liczba setek, dziesiątek, jedności, tak jak to opracowaliśmy już wcześniej.

W pliku roboczym możecie zobaczyć etapy przygotowania. Nie będę ich ponownie rozpisywał. Pojawiają się niewielkie komplikacje formuł wyławiających setki, dziesiątki tysięcy i pojedyncze tysiące (INT i MOD)

Ustalamy słowa tysiąc, tysiące, tysięcy

Tutaj język polski jest bogaty i musimy obsłużyć go na bogatości. Są 4 przypadki: Jeśli liczba jest mniejsza od 1000 nie wpisujemy nic, jeśli mniejsza od 2000, a większa lub równa tysiąc, wpisujemy “tysiąc”. Później istotna jest końcówka. Jeśli liczba tysięcy końcówka liczby tysięcy to dwa, trzy lub cztery, to mówimy “tysiące” (np. dwieście sześćdziesiąt trzy tysiące), natomiast jeśli tysięcy jest kilkanaście lub końcówka jest 0 5,6 itp. to mówimy “tysięcy”. Tak jak “kapelusz za sto tysięcy”.

Te wszystkie warunki obsługuję funkcją IFS – żeby było przejrzyściej, używam teraz roboczych wartości z kolumny C (jedności i dziesiątki tysięcy).

Ponieważ chcę, żeby finalnie cała formuła mieściła się w jednej komórce, zamieniam odniesienia do C20 i C21 na formuły znajdujące się tam.

Na koniec kleję wszystko w jednej komórce i uzyskuję pełną liczbę słownie:

Formuła zrobiła się już dość długa…

Wstawiamy słowo “złoty”,”złote”,”złotych” oraz grosze

Tu jest znów fikołek polskiego języka. Jeśli kupujemy piwo za 1,99 to będzie to jeden “złoty”. Ciut droższe piwo, będzie już po dwa “złote”. Podobnie jak z Dwadzieścia dwa “złote”. Regułę opisałem już wcześniej przy okazji słówka “tysiąc” więc tylko pokażę tu funkcję w wersji uproszczonej:

Następnie przerabiam wszystkie odwołania do komórek innych niż B28, tak by odnosiły się do bezpośrednio do B28.

Wygląda to tak:

Wpisujemy grosze

Ostatnia sprawa to załatwienie kwestii groszy. Zadowolimy się zapisem / XX gr. W tym celu najpierw musimy wydobyć ułamek z liczby. W praktyce oznacza to, że bierzemy liczbę i odejmujemy od niej jej część całkowitą. Zostaje to co po przecinku.

Np. = 34,56 – INT(34,56) daje wynik 0,56

Pozostaje to pomnożyć przez 100 i zaokrąglić do pełnego grosza. I na koniec dopisać jeszcze / i gr.

Doklejamy ostatni składnik do naszej formuły i mamy pełen wynik:

Cała formuła do skopiowania

Tu wszystko jest w jednej formule. Zakładamy, że liczba znajduje się w B2.

=trim( index({"";"sto";"dwieście";"trzysta";"czterysta";"pięćset";"sześćset";"siedemset";"osiemset";"dziewięćset"};int(B2/100000)+1)&" "& if(int(MOD(int(B2/1000);100)/10)<2; index({"";"jeden";"dwa";"trzy";"cztery";"pięć";"sześć";"siedem";"osiem";"dziewięć";"dziesięć";"jedenaście";"dwanaście";"trzynaście";"czternaście";"piętnaście";"szesnaście";"siedemnaście";"osiemnaście";"dziewiętnaście"};mod(int(B2/1000);100)+1); index({"";"dwadzieścia";"trzydzieści";"czterdzieści";"pięćdziesiąt";"sześćdziesiąt";"siedemdziesiąt";"osiemdziesiąt";"dziewięćdziesiąt"};int(mod(int(B2/1000);100)/10))) &" "& if(int(MOD(int(B2/1000);100)/10)<2; ""; index({"";"jeden";"dwa";"trzy";"cztery";"pięć";"sześć";"siedem";"osiem";"dziewięć"};mod(int(B2/1000);10)+1))&" "& ifs(B2<1000;""; B2<2000;"tysiąc"; and(int(mod(int(B2/1000);100)/10)<>1;mod(int(B2/1000);10)>1;mod(int(B2/1000);10)<=4);"tysiące"; len(B2);"tysięcy")&" "&index({"";"sto";"dwieście";"trzysta";"czterysta";"pięćset";"sześćset";"siedemset";"osiemset";"dziewięćset"};int(mod(B2;1000)/100)+1)&" "& if(int(mod(B2;100)/10)<2; index({"";"jeden";"dwa";"trzy";"cztery";"pięć";"sześć";"siedem";"osiem";"dziewięć";"dziesięć";"jedenaście";"dwanaście";"trzynaście";"czternaście";"piętnaście";"szesnaście";"siedemnaście";"osiemnaście";"dziewiętnaście"};mod(B2;100)+1); index({"";"dwadzieścia";"trzydzieści";"czterdzieści";"pięćdziesiąt";"sześćdziesiąt";"siedemdziesiąt";"osiemdziesiąt";"dziewięćdziesiąt"};int(mod(B2;100)/10)))&" "& if(int(mod(B2;100)/10)<2; ""; index({"";"jeden";"dwa";"trzy";"cztery";"pięć";"sześć";"siedem";"osiem";"dziewięć"};mod(B2;10)+1))&" ")&" "&ifs( int(B2)<1;"zero złotych"; int(B2)=1;"złoty"; and(int(mod(B2;100)/10)<>1;int(mod(B2;10))>1;int(mod(B2;10))<5);"złote"; Len(B2);"złotych")&" / "&round((B2-int(B2))*100;0)&" gr"

Jak wygodnie dostosować formułę do swojego arkusza?

Jeśli nie chcesz ręcznie przerabiać B2 na potrzebną Ci wartość, najlepiej użyj opcji Menu –> Edytuj –> Znajdź / Zamień. Wyszukaj określony zakres (komórka z formułą), Szukaj również w formułach.