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.

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.

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.

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.

Podczas opracowywania tego artykułu zmodyfikowałem nieco pliki slownie.xls i slownie.xlsx, więc ich ostateczna pobrana postać wizualnie nieco się różni od prezentowanej na ilustracjach.

Chcę jeszcze na moment wrócić 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 16.02.2012