Yestok.pl

Jedną z niedogodności arkuszy kalkulacyjnych, w tym także Excela, jest brak funkcji pozwalającej przekształcić wartość liczbową na wartość podaną słownie. Choć nie można powiedzieć, jest funkcja BAHTEXT() zamieniająca wartość liczbową na słowną, tyle że w języku tajskim. Przekształcenie na postać słowną przydaje się najczęściej przy finalizowaniu faktury wystawianej z pomocą programu arkusza kalkulacyjnego. Może jednak przydać się także w innych okolicznościach.

Rozwiązania są dwa. Można wprowadzić do swojego skoroszytu taki algorytm, który dokona odpowiednich przekształceń, czyli zadziała na zasadzie: jeśli w komórce A1 arkusza znajdzie się wartość liczbowa, to w komórce A2 pojawi się tekst tę wartość reprezentujący. Drugim rozwiązaniem jest dołączenie do skoroszytu własnej funkcji, która zwróci wartość liczby, będącej argumentem tej funkcji, w postaci słownej. Funkcja powinna zadziałać jak wspomniana na wstępie funkcja BAHTEXT() tylko po polsku.

Oba przypadki nie są jednak tak proste. W obu, fleksyjność naszego języka powoduje konieczność stworzenia dość złożonego algorytmu i nie wszyscy użytkownicy Excela mogą sobie z tym poradzić. W drugim – dodatkowo – trzeba znać język programowania VBA (Visual Basic for Application), w którym ten algorytm zostanie zakodowany.

Zastosowanie własnej funkcji, a więc drugi z przypadków, jest traktowane jak dołączenie do skoroszytu makra. Excel w wersjach 2007 i wyższej, dla skoroszytów zawierających makra przewiduje dedykowane rozszerzenie – XLSM. A więc skoroszyt taki będzie miał nazwę w rodzaju NAZWA.XLSM, podczas gdy skoroszyt bez makr nazywałby się NAZWA.XLSX. Ponieważ makro zdefiniowane w skoroszycie obowiązuje tylko w nim, pojawia się problem wykorzystania go w innych skoroszytach. Takie możliwości oczywiście, w różnym zakresie, istnieją. Można wykorzystać tak zwany skoroszyt personalny aby mieć dostęp do makra we wszystkich skoroszytach tworzonych na własnym komputerze. Można skorzystać z właściwości dodatków aby dystrybuować makro wewnątrz sieci firmowej i udostępniać je wszystkim użytkownikom. Jednak tylko skoroszyt z wbudowanym makrem może być przetwarzany na dowolnym innym komputerze, gdyż tylko taki skoroszyt nie musi korzystać z powiązania z zewnętrznym źródłem makr.

Rozwiązanie pierwsze, czyli wbudowanie do skoroszytu odpowiedniego algorytmu, nie wykorzystuje makr i pozwala przenosić skoroszyt na dowolny komputer. Algorytm jest tutaj częścią składową skoroszytu i zbudowany w oparciu o standardowe formuły obliczeniowe. Kłopotliwe może być jedynie powielanie tego algorytmu do każdego skoroszytu, w którym chcielibyśmy wykorzystać taką konwersję wartości.

Niniejszy artykuł opisuje jak wykorzystać napisany przeze mnie algorytm, zawarty w oddzielnym arkuszu Excela zapisanym jako szablon. Dedykowanym rozszerzeniem nazwy pliku szablonu są litery XLTX – dla Excela w wersji 2007 i wyższej oraz XLT – dla Excela w wersjach 97-2003. Odpowiednie pliki nazywać się więc będą SŁOWNIE.XLTX oraz SŁOWNIE.XLT. Pliki przygotowane do pobrania z serwisu są normalnymi skoroszytami Excela i to użytkownik powinien zapisać je w postaci szablonu. Zainteresowani mogą pobrać te pliki poprzez odpowiednie łącza znajdujące się w dalszej części tego opracowania.

[Dodane 29.06.2024] Opracowałem nową wersję algorytmu pozwalającego na prezentowanie liczby w postaci słownej. Zasada funkcjonowania tej wersji jest taka sama jak omówiona poniżej w opracowaniu z 2012 roku. Czyli nadal jest to arkusz, który może zostać zapisany jako szablon lub dołączony do istniejącego skoroszytu. Różnica polega na tym, że można równocześnie uzyskać wynik dla większej ilości liczb, bo aż do ośmiu. Ponadto można określić miana dla liczb, co pozwoli uzyskać efekt w postaci np. dwa złote i piętnaście groszy. Szczegółowy opis znajduje się pod omówieniem pierwotnej wersji tego algorytmu, nadal dostępnego do pobrania. Do opisu nowej wersji można przejść od razu z tego miejsca.

Wersja z roku 2012.

Ideą szablonów jest możliwość utworzenia nowego dokumentu na bazie wybranego wzorca. Otwarcie zatem dokumentu opartego o szablon, oznacza przejęcie wszystkich rozwiązań zastosowanych w szablonie z jednoczesnym traktowaniem dokumentu jako całkowicie nowego. Takiego, jakby użytkownik sam go opracował do tego etapu prac, a teraz kontynuując pracę, może rozbudowywać, zmieniać i dopasowywać projekt do własnych potrzeb.

Przygotowany przeze mnie skoroszyt zawiera tylko jeden arkusz nazwany: „ArkuszSłownie”. W tym arkuszu w komórce A1 należy umieścić wartość liczbową. Wynik, czyli wartość z komórki A1 zapisana słowami, pojawi się w komórce A10 tego arkusza. Ze względów bezpieczeństwa, w obawie przed przypadkowym naruszeniem integralności algorytmu, arkusz został zabezpieczony hasłem i użytkownik ma dostęp tylko do komórki A1.

Pierwszy sposób wykorzystania szablonu polega na otwarciu nowego dokumentu w oparciu o ten szablon a następnie rozbudowywanie projektu, drugi polega na dołączeniu do własnego funkcjonującego skoroszytu, zawartości szablonu.

Aby ten mechanizm wykorzystać trzeba najpierw przekształcić pobrany plik w szablon. Po pierwsze należy pobrać właściwy plik skoroszytu do dostępnej lokalizacji, np. „Moje Dokumenty”. Dwukrotne kliknięcie w pobrany plik spowoduje otwarcie go w Excelu. Teraz – bez żadnych innych działań – zapisać go, wybierając polecenie „Zapisz jako” i ustalając typ pliku na „Szablon programu Excel”. Obrazuje to rysunek poniżej.

Można oczywiście w tej operacji nadać zapisywanemu plikowi własną, inną niż zaproponowana, nazwę. Tak zapisywany szablon umieszczany jest automatycznie we właściwej lokalizacji na dysku. To właśnie lokalizacja szablonów jest powodem, że do pobrania przygotowałem skoroszyty a nie szablony. Pliki szablonów znajdują się bowiem w zależności od systemu operacyjnego w następujących lokalizacjach.

• Windows XP: c:\Document and Settings\nazwa_użytkownika\Dane aplikacji\Microsoft\Szablony
• Windows Vista: c:\Users\nazwa_użytkownika\AppData\Roaming\Microsoft\Szablony
• Windows 7: c:\Użytkownicy\nazwa_użytkownika\AppData\Roaming\Microsoft\Szablony

Foldery te mają zazwyczaj ustawiony status na „ukryte” i mogą nie być ujawniane w programie zarządzania plikami i folderami, co w konsekwencji, mniej obeznanym z systemem operacyjnym użytkownikom, uniemożliwia skopiowanie tam plików.

Aby rozpocząć pracę z nowym skoroszytem opartym o szablon, wywołujemy Excela i wybieramy polecenie Nowy. Proszę nie korzystać z ikonki „Nowy”, gdyż otworzy ona rzeczywiście nowy skoroszyt ale bez możliwości wskazania szablonu. W Excelu 2007 wybór nowego skoroszytu pokazuje rys. 2. w wersjach wcześniejszych należy wywołać polecenie „Plik” i z rozwiniętej listy wybrać „Nowy...”.

Fragment nowego okna, jakie zostanie teraz otwarte pokazuje rys. 3., wcześniejsze wersje Excela otworzą okno podobne do tego z rys. 10., w którym zapisane szablony są dostępne w zakładce „Ogólne”.

Lewa część okna to wykaz kategorii szablonów jakie można wykorzystać. Po prawej stronie, w części „Niedawno używane szablony” będą widoczne nazwy szablonów już użytych. Jeśli potrzebny szablon jest na tej liście, należy go zaznaczyć i wybrać (niewidoczny na tym fragmencie ilustracji) przycisk „Utwórz”. Szablon zapisany wcześniej wspomnianą metodą a więc znajdujący się w standardowej lokalizacji szablonów, dostępny będzie po wybraniu opcji „Moje szablony...”. Nowe okno dialogowe, pokazane na ilustracji poniżej, pozwoli wskazać konkretny szablon.

Wybór szablonu, potwierdzony przyciskiem OK spowoduje otwarcie nowego skoroszytu gotowego do dalszej pracy. Nowy skoroszyt zawiera tylko jeden arkusz i wygląda jak na rysunku 5.

Skoroszyt otrzymuje nazwę użytego szablonu uzupełnioną o liczbę, tak jak nazwy typu Zeszyt1. Jest to nazwa przypisana tymczasowo więc w momencie zapisywania pliku zostaniemy poproszeni o nadanie ostatecznej własnej nazwy. Ponieważ jest to w tej chwili jedyny arkusz, to jest on także arkuszem aktywnym, zatem skorzystanie ze skrótu klawiaturowego SHIFT+F11 wstawi nowy arkusz o nazwie Arkusz1 bezpośrednio przed arkusz aktywny. W tym wstawionym arkuszu lub większej ich liczbie możemy zbudować nasz projekt.

Na ilustracjach 6. i 7. przedstawiam równocześnie dwa arkusze skoroszytu: „Arkusz2” i „ArkuszSłownie”. W komórce A1 arkusza Arkusz2 została wpisana liczba. W komórce A1 arkusza „ArkuszSłownie” wpisana jest formuła przekazująca tę liczbę do przekształcenia. Ma ona postać „=Arkusz2!A1”.

Wynik przekształcenia widoczny jest w komórce A10 arkusza „ArkuszSłownie”, aby umieścić go w komórce A4 arkusza Arkusz2, należy wstawić w nim formułę „=ArkuszSłownie!A10”.

Teraz każda zmiana wartości komórki A1 arkusza Arkusz2 spowoduje wyświetlenie tej wartości słownie w komórce A4.

Jeśli użytkownik ma już zbudowany arkusz, np. taki jak na rys. 8.

Może także wykorzystać przedstawiony szablon do przekształcenia liczby na wyrażenie słowne. Po pierwsze trzeba go dołączyć do własnego skoroszytu.

Kliknięcie prawym przyciskiem myszki w nazwę arkusza otwiera menu kontekstowe pokazane na rys. 9.

Wybór opcji „Wstaw...” otworzy okno dialogowe pokazane na rysunku poniżej. W zakładce „Ogólne” tego okna pojawią się także szablony umieszczone w standardowej lokalizacji. W oknie tym należy wskazać skoroszyt szablonu.

Skoroszyt wstawiony zostanie przed arkuszem, na którym rozpoczęto operację wstawiania. Na rys. 11 pokazuję dolny fragment okna programu Excel z widoczną teraz kolejnością arkuszy.

W menu kontekstowym pokazanym na rys. 9. widoczna jest opcja „Przenieś lub kopiuj”. Jej wybór na zaznaczonej nazwie arkusza wywoła okno dialogowe, pokazane na rys. 12., w którym można ustalić położenie tego arkusza. Dzięki temu arkusz możemy umieścić w dowolnym miejscu struktury skoroszytu.

Wykorzystując tę możliwość można ułożyć arkusze w pożądanej kolejności. Dalsze działania, polegające na przekazaniu wartości liczbowej i odebraniu tej wartości w postaci słownej zostały już omówione.

Jeśli użytkownik chce przekształcić w tym samym skoroszycie także drugą wartość liczbową, musi w tym celu dołączyć ponownie arkusz szablonu. Każda nowa kopia arkusza „ArkuszSłownie” zostanie opatrzona dodatkowym deskryptorem liczbowym. Więc druga kopia będzie miała nazwę „ArkuszSłownie (2)” i taką nazwą należy się wówczas posługiwać w tworzonych formułach.

Przygotowany przeze mnie arkusz przekształca na postać słowną tylko liczby nieujemne, i po zaokrągleniu nie większe niż 999 999 999 999,99 co pozwoli wystawić fakturę za wykonanie około 500 stadionów narodowych. Liczba jest zawsze najpierw zaokrąglana do dwóch miejsc po przecinku i dopiero potem przekształcana. Jeśli wartość liczbowa nie spełnia tego warunku lub przekazano zawartość komórki nie zawierającej liczby, wynikiem jest tekst „Liczba jest ujemna, albo za duża, albo nie jest to liczba!”.

Na zakończenie jeszcze uwaga związana z budową formuły powrotnej. Zabezpieczenie szablonu przed zmianami powoduje, że nie można podczas tworzenia formuły metodą wskazywania, kliknąć myszką komórki A10 arkusza „ArkuszSłownie” i w ten sposób umieścić ją w formule. Adres ten trzeba wpisać samodzielnie. Nazwę arkusza, czyli „ArkuszSłownie” można wpisywać dowolnymi literami. Jeśli jednak chcesz użyć myszki, to wskaż komórkę A1 w arkuszu „ArkuszSłownie”, bo ta jest dostępna, a następnie dopisz po jedynce cyfrę zero w utworzonym w formule odwołaniu do adresu.

Możliwość pobrania opisanych powyżej wersji arkusza znajduje się pod opisem nowej wersji.

Nowa wersja arkusza konwersji.

Nowy skoroszyt zapisany jest pod nazwą „LiczbaNaTekst.xls dla starszej wersji Excela (97 - 2003) i LiczbaNaTekst.xlsx dla wersji co najmniej 2007. Przy okazji tych prac okazało się, że wielu nowszych funkcji, które znacząco uprościłyby algorytm, nie można użyć w wersjach starszych, np. 2016, które są przecież ciągle w użyciu. Chyba jedynym pakietem MS Office, który zawsze ma najnowsze funkcjonalności, jest MS Office 365, za który trzeba płacić coroczną subskrypcję. To właśnie z powodu tych różnic nowa wersja skoroszytu wykorzystuje podstawowe funkcje arkusza dzięki czemu może zostać wykorzystana w starszych wydaniach Excela.

Nowy skoroszyt ma tylko jeden arkusz o nazwie takiej samej jak zawierający go plik, czyli „LiczbaNaTekst”. Tak jak w pierwowzorze arkusz jest chroniony hasłem. Kolumny przeznaczone do pomocniczych obliczeń są ukryte.

Głównym obszarem dostępnym dla użytkownika jest obszar przekazywania liczb i odbierania wyniku słownego. Wygląda on tak:

Do komórek w obszarze A3:A10 należy przekazać liczby. Liczby te mogą mieć co najwyżej 12 cyfr w części całkowitej i nie mogą mieć więcej niż dwóch cyfr po przecinku. To oznacza, że największą liczbą, jaką można przedstawić słownie, jest 999 999 999 999,99 a najmniejszą -999 999 999 999,99 . Liczbę wpisać można w dowolny sposób, z lub bez miejsc po przecinku. W kolumnie A każda wprowadzona liczba jest ukazana jako sformatowana z dwoma miejscami po przecinku. Algorytm sprawdza, czy te warunki są spełnione oraz to, czy przekazana zawartość jest w ogóle liczbą, na ciągu wprowadzonym, a nie na prezentowanym w kolumnie A. Jeśli warunki nie są spelnione, komórka przyjmująca wartość przedstawia ją na czerwonym tle, natomiast komórka wynikowa dla takiego wpisu zawiera tekst „Liczba za duża, wpisano więcej niż dwie cyfry po przecinku, albo to nie jest liczba.”.

W obszarze komórek C3:C10 można wybrać z rozwijanej listy wymagane miano dla liczby w odpowiadającej komórce kolumny A. Komórki można pozostawić puste, wówczas liczby są przedstawione słowami bez miana, ale część ułamkowa liczby, jeśli występuje, zostanie podana z mianem setnych tak jak to widać na ilustracji w drugim i siódmym wierszu liczb.

Tak jak w poprzedniej wersji, odbiór wyniku następuje poprzez utworzenie we własnym arkuszu formuły przejmującej wynik z odpowiedniej komórki w kolumnie B.

Drugi obszar przeznaczony dla użytkownika pozwala ustalić własne opcje dotyczące prezentacji liczby. Wygląda on tak:

Pierwszym ustawieniem jest „Łącznik”. To jest napis lub znak, jakie będą wstawione pomiędzy część całkowitą a ułamkową. Największą część zajmuje jednak obszar przeznaczony na zdefiniowanie mian.

Ponieważ arkusz pozwala jednocześnie otrzymać wynik dla ośmiu liczb, obszar ustawień umożliwia zdefiniowanie do ośmiu mian, na wypadek gdyby każda liczba miała mieć swoje własne miano. Każde miano należy zdefiniować oddzielnie dla części całkowitej oraz ułamkowej. Miana znajdujące się w komórkach obszaru Y4:Y11 są jednocześnie wykazem, jaki pojawia się po wybraniu komórki w kolumnie C. Oczywiste jest, że można mian w ogóle nie wpisywać lub wpisać tylko te potrzebne. Fleksyjność języka polskiego wymusiła na mnie żądanie wpisania miana w trzech postaciach. Pierwszej, do wyrażenia liczebności jednej jednostki miana (jeden złoty), drugiej dla wyrażenia miana dla jednostek o liczebnościach 2 – 4 (dwa złote, trzy złote, cztery złote), ta forma miana jest właściwą także dla liczebności większej niż 20, wtedy gdy ostatnią cyfrą jest 2, 3 lub 4 i trzeciej, dla pozostałych liczebności (pięć złotych). Ponadto dla każdego miana należy podać jego rodzaj gramatyczny, gdyż od tego zależy słowna postać liczby (jeden złoty, jedna korona, jedno euro, dwa złote, dwie korony). W taki sam sposób należy podać miana dla części ułamkowej, jeśli są potrzebne. Ostatnia kolumna tego obszaru, zatytułowana „Zerowy ułamek” decyduje o tym, czy wtedy, gdy część ułamkowa jest równa zero, ma się w postaci słownej pojawić zapis „zero «miano ułamka»”. Wpisanie litery „T” oznacza, że taki zapis ma się pojawić. W przypadku, gdy nie określono miana dla liczby, słowna postać podana jest gramatycznie w rodzaju męskim, a część ułamkowa pojawi się w postaci ilości setnych tylko wtedy, gdy zawiera ją sama liczba.

Pliki do pobrania.

Zanim pobierzecie Państwo potrzebne pliki, pamiętacie, że za pobranie pliku odpowiada kliknięcie odnośnika prawym przyciskiem myszki i wybranie z wyświetlonego menu pozycji w rodzaju „Zapisz element docelowy jako...” lub „Zapisz link jako...”. Kliknięcie lewym przyciskiem może spowodować inne działania niż pobieranie, jeśli plik danego typu jest zarejestrowany w przeglądarce do specjalnego traktowania.

Starsze wersje plików.

• Aby pobrać skoroszyt dla Excela 97 - 2003, kliknij odnośnik z nazwą pliku: slownie.xls.
• Aby pobrać skoroszyt dla Excela 2007/2010, kliknij odnośnik z nazwą pliku: slownie.xlsx.

Nowe wersje plików.

• Aby pobrać skoroszyt dla Excela 97 - 2003, kliknij odnośnik z nazwą pliku: LiczbaNaTekst.xls.
• Aby pobrać skoroszyt dla Excela 2007/2010 lub nowszego, kliknij odnośnik z nazwą pliku: LiczbaNaTekst.xlsx.

Wrócę na moment do problematyki makr. Czytelnik może bowiem skonstatować, że można wbudować makro do szablonu, i takiego szablonu używać do tworzenia własnych aplikacji. Będzie miał rację. Przeszkodą, niedogodnością a dla niektórych wadą, jest przyjęta przez firmę Microsoft polityka korzystania z makr, związana z zagrożeniami, jakie mogą być w nich zawarte. Dlatego każdy otwierany skoroszyt zawierający makro, wymaga potwierdzenia, że makro może być uruchomione. Brak takiego potwierdzenia wyłącza możliwość korzystania z makr. Rozwiązaniem jest certyfikacja makr. Certyfikat może być lokalny, wystawiony we własnym systemie operacyjnym i wówczas skoroszyt z takim certyfikowanym makrem uruchamia się bez dodatkowych pytań na własnym komputerze. Przeniesienie pliku na inny komputer nie przenosi jednak certyfikatu. Można tez przypisać certyfikat globalny. Ten przydzielany jest przez centra certyfikacji i jest niestety płatny, często z płatnością odnawianą co rok. To jest główny powód zniechęcający do wbudowywania makr.

Użytkowniku serwisu
Jeśli opracowanie, które właśnie przeczytałeś, pomogło rozwiązać Twój problem, wyjaśniło wątpliwości, pomogło zrozumieć to zagadnienie czy choćby Ci się spodobało i uważasz, że jest tego warte – możesz wesprzeć finansowo serwis.

Wsparcie zrealizujesz za pośrednictwem serwisu płatności internetowych PayPal. Nie musisz mieć konta w tym serwisie. Wystarczy kliknąć rysunek obok. Gdy masz swoje konto w PayPal, możesz skorzystać z linku PayPal.me.

Dziękuję!

Jeśli dotarłeś do tej strony znajdując ją bezpośrednio w Internecie, możesz przywołać w jej miejsce listę tematów, o których piszę w tym serwisie. Wystarczy kliknąć w poniższy tekst.
Chcę zobaczyć tematykę innych problemów.

Utworzono 16.02.2012
Zmodyfikowano 29.06.2024