Zamiana liczb na słowa
Zamiana liczb na słowa za pomocą formatowania
Oto przykład pewnego zadania – wystawiając fakturę zawsze należy podać zapis słowny sumy do zapłacenia. Jeśli faktur tych jest niewiele, można wpisać słowną kwotę do zapłaty ręcznie. Można też skorzystać z formatowania niestandardowego. Ma ono tą zaletę nad zwykłym zapisem, że sformatowane liczby dalej pozostają liczbami, a nie słowami jak się wydaje na pierwszy rzut oka, i dzięki temu można wykonywać na nich operacje matematyczne.
Załóżmy, że w komórce mamy wpisaną liczbę 1500, ale chcemy żeby był to zapis słowny. Zaznaczmy tą komórkę, a następnie na karcie Narzędzia główne w grupie Liczba naciśnijmy na strzałkę umiejscowioną w prawym dolnym rogu grupy.
Rysunek 1. Jak otworzyć opcje formatowania liczb.
Można też rozwinąć listę obecną w grupie i wybrać polecenie Więcej formatów liczb.
Rysunek 2. Inny sposób otwarcia opcji formatowania liczb.
Pojawia się okno dialogowe Formatowanie komórek z domyślnie aktywną zakładką Liczby. Od razu przejdźmy do kategorii Niestandardowe. Formatowanie niestandardowe wykorzystuje się w przypadku, gdy dostępne w Excelu formaty są niewystarczające. Można go używać, aby np. liczby ujemne zaznaczyć na czerwono a dodatnie na niebiesko.
Rysunek 3. Zakładka Niestandardowe.
W obszarze Typ dostępne jest pole, w którym definiuje się niestandardowy format. Poniżej znajduje się lista już stworzonych szablonów z formatami, ale równie dobrze można je tworzyć samemu, jeśli posiada się odpowiednią wiedzę.
Wracając do problemu zapisu liczb, w polu typu wpiszmy naszą liczbę „tysiąc pięćset zł”. Zapisy słowne, które mają pojawić się w formatowaniu niestandardowym muszą znajdować się w cudzysłowach. W obszarze Przykład widoczny jest podgląd uzyskanych efektów. Następnie kliknijmy OK.
W arkuszu widoczny jest teraz zapis słowny liczby wpisanej na początku za pomocą cyfr. Zauważmy jednak, że kiedy komórka z liczbą jest zaznaczona, w pasku formuły widnieje ona pod postacią cyfr.
Rysunek 4. Wynik zmiany formatowania liczb.
Zamiana daty na zapis słowny dnia i miesiąca
Bardzo często mając datę (data jest liczbą dla Excela) chcielibyśmy uzyskać z niej zapis słowny miesiąca lub zobaczyć jaki dzień tygodnia dana data pokazuje.
Aby rozwiązać ten problem można użyć funkcji TEKST. Umożliwia ona zmianę metody wyświetlania liczby, dzięki zastosowaniu odpowiedniego formatowania z użyciem kodów formatów. Na pewno funkcja ta jest użyteczna, kiedy chcemy pokazać jakąś wartość w sposób bardziej czytelny lub połączyć ją z innym tekstem. TEKST posiada dwa argumenty. Pierwszym z nich jest wartość, która ma zostać przekonwertowana na tekst. Drugi argument to format definiujący nowy sposób wyświetlania wartości i jest on podawany w formie kodów w cudzysłowie.
Załóżmy, że mamy listę dat i musimy z nich uzyskać zapis słowny miesiąca w kolumnie obok. W wybranej komórce wpiszmy funkcję TEKST zaczynając od znaku =. Następnie trzeba podać jej argumenty, czyli w tym przypadku odwołanie do komórki z datą oraz kod formatu, jaki ma być zastosowany.
Rysunek 5. Stosowanie funkcji TEKST do zamiany zapisu daty.
Jeśli chcemy, aby wyświetlana była cała nazwa słowna miesiąca wpiszmy jako drugi argument „mmmm”. Kiedy interesują nas trzyliterowe skróty nazw miesiąca (sty, lut, mar, itp.) wpiszmy „mmm”. Natomiast, gdyby użyć formatu „mm” lub „m” miesiąc wyświetliłby się pod postacią liczby, czyli np. styczeń byłby reprezentowany jako zapis 01 dla „mm” i 1 dla „m”.
O ile miesiąc łatwo odczytać z daty, o tyle dzień tygodnia jest już sprawą trudniejszą. Tutaj także można użyć funkcji TEKST. Dzięki niej bardzo prosto datę zamienimy na słowny zapis dnia tygodnia.
Rysunek 6. Wyświetlenie nazwy dnia tygodnia.
Tak samo jak poprzednio, w wybranej komórce wpiszmy funkcję TEKST i podajmy jej argumenty, czyli odwołanie do komórki z datą oraz kod formatu, jaki ma być zastosowany. Aby zobaczyć pełną nazwę dni tygodnia wpiszmy kod formatu „dddd”. Jeśli interesują nas dwuliterowe skróty nazw dni tygodnia wpiszmy „ddd”. Natomiast, gdyby użyć formatu „dd” lub „d” uzyskalibyśmy zapis numeru dnia miesiąca, a nie tygodnia, czyli odpowiednio dla daty np. 2018-05-08 dostalibyśmy zapis 08 dla „dd” i 8 dla „d”.
Tysiące filmów szkoleniowych z Excela
Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej przykładowych zadań!
Czytanie liczb
Nie każdy wie, że Excel potrafi czytać i właśnie tę funkcjonalność można wykorzystać do zamiany liczb na słowa. Co prawda zamiana nastąpi tylko werbalnie, ale jest to dość ciekawa opcja.
Przycisk czytania dostępny jest z paska narzędzi szybkiego dostępu. Zapewne jednak, gdy spojrzysz na ten pasek nie odnajdziesz na nim wspomnianego przycisku. Musimy sami go tam ulokować.
Kliknij na strzałkę znajdującą się na pasku narzędzi szybkiego dostępu, a następnie wybierz Więcej poleceń…
Rysunek 7. Więcej poleceń na pasku szybkiego dostępu.
Wyświetli się okno dialogowe Opcje programu Excel z menu Pasek narzędzi Szybki dostęp. Wśród Wybierz polecenia z odszukaj i wybierz opcję Polecenia, których nie ma na wstążce.
Rysunek 8. Polecenia, których nie ma na wstążce.
Następnie trzeba odnaleźć jedną z opcji:
- Czytaj komórki – odczytuje zawartość zaznaczonych komórek,
- Czytaj komórki–zatrzymaj czytanie komórek – zatrzymuje czytanie zawartości komórek w wybranym momencie,
- Czytaj komórki kolumnami – powoduje, że zawartość komórek czytana jest kolumnami,
- Czytaj komórki przy wprowadzaniu – odczytuje zawartość komórki po jej zatwierdzeniu klawiszem ENTER,
- Czytaj komórki wierszami – powoduje, że zawartość komórek czytana jest wierszami.
Aby dodać wybraną opcję do paska szybkiego dostępu wystarczy ją zaznaczyć i kliknąć Dodaj. Wybrane opcje natychmiast pojawią się na podglądzie poleceń paska.
Rysunek 9. Dodanie poleceń do paska narzędzi – przycisk Dodaj.
Kolejno należy kliknąć OK, a wybrane polecenia staną się dostępne pod postacią odpowiednich ikon na pasku szybkiego dostępu.
Rysunek 10. Ikony widoczne na pasku.
Aby rozpocząć zamianę liczb na słowa czytane, zaznacz wybrane liczby i kliknij w ikonę Czytaj komórki. Kiedy chcesz, żeby liczby były czytane kolumnami lub wierszami najpierw wybierz odpowiedni przycisk, a dopiero potem Czytaj komórki. Chcąc zatrzymać czytanie wybierz Czytaj komórki – zatrzymaj czytanie komórek, natomiast dla czytania każdej komórki wybierz przycisk Czytaj komórki przy wprowadzaniu. Szczególnie ostatnia opcja może stanowić ułatwienie dla osób słabowidzących przy kontroli wprowadzanych danych.
Dodatki do Excela zamieniające liczby na słowa
Przejdźmy teraz do bardziej zaawansowanych środków radzenia sobie z zamianą liczb na słowa. Na pewno duża część zainteresowanych taką konwersją ma na myśli po prostu szybki zapis słowny liczby lub nawet konwersję całej kolumny z liczbami na słowa.
W sieci dostępne są dodatki do Excela, które pozwalają na taką zamianę. Wiele z tych dodatków jest darmowych i łatwych do wyszukania. Jako przykład podamy jeden z darmowych dodatków autorstwa Expose Lab. Jest on dostępny w sieci pod nazwą dodatek_expose_lab.xlam.
Znajdźmy w wyszukiwarce interesujący nas dodatek i ściągnijmy jego plik. Plik dodatku do programu Excel najczęściej posiada rozszerzenie xlam, xla lub xll. Do działania takiego dodatku nie wystarczy samo jego ściągnięcie. Należy go uruchomić wybierając Plik, a następnie Opcje i kategorię Dodatki. W polu Zarządzaj kliknij Dodatki programu Excel i kolejno Przejdź.
Zostanie wyświetlone okno dialogowe Dodatki, które zawiera listę dostępnych dodatków. Prawdopodobnie świeżo ściągnięty dodatek do zamiany liczb na słowa nie pojawi się od razu na tej liście. W takim przypadku za pomocą opcji Przeglądaj zlokalizuj plik dodatku i dodaj go klikając OK. Aby dodatek stał się aktywny zaznacz pole wyboru dodatku i zaakceptuj klikając OK.
W przypadku dodatku Expose Lab po uaktywnieniu pojawia się nowa zakładka, która zawiera polecenie Liczba słownie.
Po wybraniu tego polecenia zostajemy poproszeni o zaznaczenie komórki zawierającej liczbę, a następnie zaznaczenie komórki, w której ma się pojawić wynik. Wadą dodatku jest brak możliwości zaznaczenia zakresów i automatyczne dodanie waluty polskiej bez możliwości jej pominięcia. Dodatek udostępnia także gotową funkcję słownie, dla której argumentem jest liczba. Funkcja ta umożliwia już konwersję pewnego zakresu komórek poprzez przekopiowanie jej.
Funkcje pochodzące z dodatków lub stworzone za pomocą VBA znajdziemy klikając na symbol fx (Wstaw funkcję) obok paska formuły, a następnie wybierając kategorię Zdefiniowane przez użytkownika.
Zamiana liczb na słowa za pomocą formuł
Jeśli wpadniesz na pomysł zamiany liczb na słowa za pomocą tylko i wyłącznie funkcji programu Excel, wiedz, że czeka Cię trudne zadanie. Niestety Excel nie posiada bezpośrednio funkcji, która przekonwertuje liczby na słowa, tak jak wspomniano wcześniej. Aby zamienić liczby na słowa za pomocą formuł trzeba zagnieździć w sobie bardzo wiele funkcji. Można wspomóc się też dodatkowymi zakresami, z których będziemy wyszukiwać odpowiednie dane. Dla przykładu formuła, która będzie zamieniać tylko i wyłącznie czterocyfrowe liczby na słowa i nie będzie posiłkować się dodatkowymi zakresami może mieć postać:
=JEŻELI(DŁ(A1)=4;JEŻELI(LEWY(A1;1)=”1″;”jeden tysiąc”;JEŻELI(LEWY(A1;1)=”2″;”dwa tysiące”;JEŻELI(LEWY(A1;1)=”3″;”trzy tysiące”;JEŻELI(LEWY(A1;1)=”4″;”cztery tysiące”;JEŻELI(LEWY(A1;1)=”5″;”pięć tysięcy”;JEŻELI(LEWY(A1;1)=”6″;”sześć tysięcy”;JEŻELI(LEWY(A1;1)=”7″;”siedem tysięcy”;JEŻELI(LEWY(A1;1)=”8″;”osiem tysięcy”;JEŻELI(LEWY(A1;1)=”9″;”dziewięć tysięcy”))))))))))&” „&JEŻELI(DŁ(A1)=4;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”1″;”sto”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”2″;”dwieście”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”3″;”trzysta”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”4″;”czterysta”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”5″;”piećset”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”6″;”sześćset”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”7″;”siedemset”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”8″;”osiemset”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”9″;”dziewięćset”;JEŻELI(FRAGMENT.TEKSTU(A1;2;1)=”0″;””;””))))))))));””)&” „&JEŻELI(DŁ(A1)=4;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”10″;”dziesięć”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”11″;”jedenaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”12″;”dwanaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”13″;”trzynaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”14″;”czternaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”15″;”piętnaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”16″;”szesnaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”17″;”siedemnaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”18″;”osiemnaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”19″;”dziewiętnaście”;JEŻELI(FRAGMENT.TEKSTU(A1;3;2)=”00″;””;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”2″;”dwadzieścia”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”3″;”trzydzieści”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”4″;”czterdzieści”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”5″;”piećdziesiąt”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”6″;”sześćdziesiąt”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”7″;”siedemdziesiąt”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”8″;”osiemdziesiąt”;JEŻELI(FRAGMENT.TEKSTU(A1;3;1)=”9″;”dziewięćdziesiąt”;””)))))))))))))))))));””)&” „&JEŻELI(ORAZ(DŁ(A1)=4;NIE(LUB(FRAGMENT.TEKSTU(A1;3;2)=”11″;FRAGMENT.TEKSTU(A1;3;2)=”12″;FRAGMENT.TEKSTU(A1;3;2)=”13″;FRAGMENT.TEKSTU(A1;3;2)=”14″;FRAGMENT.TEKSTU(A1;3;2)=”15″;FRAGMENT.TEKSTU(A1;3;2)=”16″;FRAGMENT.TEKSTU(A1;3;2)=”17″;FRAGMENT.TEKSTU(A1;3;2)=”18″;FRAGMENT.TEKSTU(A1;3;2)=”19″)));JEŻELI(PRAWY(A1;1)=”1″;”jeden”;JEŻELI(PRAWY(A1;1)=”2″;”dwa”;JEŻELI(PRAWY(A1;1)=”3″;”trzy”;JEŻELI(PRAWY(A1;1)=”4″;”cztery”;JEŻELI(PRAWY(A1;1)=”5″;”pieć”;JEŻELI(PRAWY(A1;1)=”6″;”sześć”;JEŻELI(PRAWY(A1;1)=”7″;”siedem”;JEŻELI(PRAWY(A1;1)=”8″;”osiem”;JEŻELI(PRAWY(A1;1)=”9″;”dziewięć”;JEŻELI(PRAWY(A1;1)=”0″;””;””))))))))));””)
Ta wielokrotnie zagnieżdżona formuła może przerażać i w dodatku nie obsługuje przypadków więcej i mniej cyfrowych liczb oraz liczb ujemnych lub niecałkowitych. Dla pełniej obsługi większych i mniejszych liczb formuła ta musiałaby zostać dodatkowo rozbudowana. W formie zabawy zawsze można spróbować tworzyć takie formuły, jednak dla większej wydajności i zrozumiałości pracy polecamy rozwiązania bardziej zautomatyzowane. Tworzenia formuły odczytującej liczby nie ułatwia także fakt, że język polski posiada wiele zawiłości i trzeba uwzględniać deklinację liczebników.
Jeżeli nie ma możliwości zastosowania VBA lub użycia dodatków do zamiany liczb na słowa, można skorzystać z bezpłatnego rozwiązania autorstwa Marcina Egerta udostępnionego na licencji Creative Commons. Plik z rozwiązaniem dostępny jest w sieci. Po ściągnięciu pliku najlepiej jest przenieść zakładkę Excelblog.pl – Kwoty słownie do swojego skoroszytu. Aby to zrobić kliknij prawym przyciskiem myszy na zakładce i wybierz polecenie Przenieś lub kopiuj.
Pojawi się okno dialogowe Przenoszenie lub kopiowanie, gdzie należy zaznaczyć opcję Utwórz kopię, wybrać docelowy skoroszyt i zatwierdzić OK.
Arkusz zawierający formuły konwertujące liczby na słowa zostanie przekopiowany do naszego arkusza. Posiada on trzy miejsca, gdzie można wpisać kwotę, która zostaje zamieniona na słowa aż na trzy sposoby. Arkusz obsługuje liczby dodatnie aż do 999 999 999 999,99 i dodaje do zapisu słownego polską walutę. Wszystko zostało wykonane za pomocą funkcji dostępnych w Excelu bez użycia VBA.
Zamiana liczb na słowa za pomocą VBA
Jedną z lepszych opcji zamiany liczb na słowa jest użycie Visual Basic for Applications, czyli VBA. Jest to język programowania Microsoft. Znajduje głównie zastosowanie przy automatyzacji powtarzalnych operacji, obsłudze dużych ilości danych, automatyzacji skomplikowanych, złożonych czynności czy tworzeniu funkcji niezdefiniowanych w Excelu i nowych skrótów klawiszowych. Zamiana liczb na słowa jest bardzo skomplikowana przy użyciu funkcji Excela, dlatego akurat w tym przypadku polecane jest użycie VBA.
Spójrz najpierw czy wśród swoich kart w skoroszycie widoczna jest karta Deweloper. Jeżeli nie, to kliknij prawym przyciskiem myszy na jednej z widocznych na razie kart i wybierz polecenie Dostosuj wstążkę. W prawym oknie Karty główne zaznacz pole wyboru Deweloper i zatwierdź OK. Wśród kart na wstążce pojawiła się teraz karta Deweloper.
Z poziomu nowo utworzonej karty otwórz edytor VBA poprzez kliknięcie polecenia Visual Basic lub skrótu klawiszowego ALT+F11.
W oknie Project-VBA Project znajdziesz projekt o nazwie swojego skoroszytu. Kliknij na nazwie tego projektu prawym przyciskiem myszy i wybierz polecenie Insert, a następnie Module.
W tym momencie powinien pojawić się nowy moduł w widoku projektu. Poprzez podwójne kliknięcie otwórz ten moduł. Zaawansowany użytkownik Excela może spróbować sam stworzyć kod w tym miejscu. Użytkownicy mniej biegli mogą posilić się gotowymi kodami, które można odnaleźć w sieci. Kod należy skopiować i wkleić do modułu. Oczywiście można go też modyfikować. Przykładowy kod makra tworzącego funkcję Excela, która zamienia liczby na słowa znajduje się poniżej.
Kod ten tworzy niestandardową funkcję o nazwie LiczbaSłownie. Opiera się on na podobnej zasadzie co stworzona funkcja z punktu Zamiana liczb na słowa za pomocą formuł. Określana jest ilość znaków i ich rodzaj i na podstawie tego używane są odpowiednie liczebniki i ich deklinacje. Funkcja obsługuje liczby ujemne i pozwala na zastosowanie polskiej waluty.
Funkcje stworzone w edytorze VBA znajdziemy klikając na symbol fx (Wstaw funkcję) obok paska formuły, a następnie wybierając kategorię Zdefiniowane przez użytkownika. Oczywiście pojawiają się też one w podpowiedzi, jeśli zaczniemy je wpisywać do komórki. Funkcja LiczbaSłownie posiada dwa argumenty. Pierwszy z nich to liczba wymagająca konwersji, drugi natomiast jest opcjonalny i określa czy stosowana jest waluta polska. Jeśli chcemy, aby w zapisie pojawiały się „złote i grosze” wstawmy jako drugi argument 1 lub nie podajemy go w ogóle, a jeżeli nie chcemy waluty to wstawiamy 0.
Aby móc korzystać z utworzonego makra po ponownym otwarciu pliku, trzeba go koniecznie zapisać w formacie xlsm jako skoroszyt programu Excel z obsługą makr.
Ciekawostka
Przy temacie zamiany liczb na słowa można wspomnieć, że Excel jednak posiada wbudowaną funkcję, która przekonwertuje liczby, ale jest to funkcja zamieniająca liczbę na język tajski, także na 99% nigdy nam się ona nie przyda. Funkcja ta to BAT.TEKST, a jej argumentem jest dana liczba. Przykładowo konwertując liczbę 150 w wyniku otrzymamy zapis หนึ่งร้อยห้าสิบบาทถ้วน.
