MOJE POSTRZEGANIE UŻYCIA MS EXCEL

Przykład 1.

Chcąc pokazać różnicę między tymi sposobami używania MS Excel przedstawię narzędzie do wypełniania komórek (Ctrl E).
Wiele osób może sądzić, że to narzędzie do automatyzacji, a to tylko narzędzie, które pomaga w pracy ręcznej w MS Excel.
Do komórek kol. B (B16:B12) zostały wprowadzone symbole tekstowe (zobacz Rysunek nr 1).


Rysunek nr 1. Przykład (narzędzie Ctrl E).

Możesz również, aby sprawdzić działanie, ściągnąć plik Ctrl_E.xlsm
Wszystkie dane (symbole) zostały poprzedzone apostrofem.
Te dane zostały skopiowane do komórek w kol. E. W komórce F6 wprowadzono datę (wzorcową do zamiany). Po ustawieniu się w komórce F6 i użyciu Ctrl E… narzędzie generuje błędne daty. Proszę spróbować.
Zaproponję usunięcie błędnych danych z komórek F7:F12 i wpisaniu do komórki F7 (28.09.2023). Teraz w komórkach F6 i F7 są dwie poprawne daty (wzorzec zasad zamiany tego co jest w kol. E na to co ma być w kol. F). Należy uaktywnić komórkę F6 lub F7 (zaznaczenie równocześnie obydwu komórek nie spowoduje poprawnego działania) i użyć ponownie Ctrl E i… też w niektórych komórkach są błędy.
Na przykład, w komórce F9 pojawiła się błędna data 23.10.2023.
Po edycji zawartości komórki F9 (klawisz F2) i poprawieniu dnia w dacie na: 03.10.2023, wszystkie pozostałe błędy w kolejnych komórkach kol. F automatycznie zostaną poprawione (MS Excel pamięta, że zostało użyte Ctrl E).
Proponuję samodzielnie sprawdzić.

Łatwo jest wyciągnąć wniosek, że użytkownik musi uczestniczyć w procesie weryfikacji poprawności zamiany realizowanej za pomocą Ctrl E. Do zamiany potrzebne jest użycie klawiatury – trzeba wprowadzić wzorzec, ustawić się we właściwej komórce i użyć Ctrl E, a potem analizować poprawność i wprowadzać poprawki.

Można wyobrazić sobie sposób, w którym do komórki C2 zostanie wprowadzona formuła (zobacz Rysunek nr 1) i będzie ona kopiowana automatycznie do komórek zakresu C6:C12 (lub zakresu z inną liczbą wierszy, jeśli będzie więcej lub mniej symboli dat w kolumnie B).
Wtedy, przy założeniu, że symbole dat we wszystkich komórkach kolumny B są poprawnie wpisane, zdecydowanie lepszym rozwiązaniem jest wypełnianie za pomocą makra napisanego w języku Visual Basic (zobacz Rysunek nr 2).


Rysunek nr 2. Makro do automatyzacji.

Makro kopiuje kolejno wszystkie wypełnione komórki z drugiego wiersza do poszczególnych wierszy rejestru i zamienia je na wartości.
Można sobie też wyobrazić, że używane jest makro, które czyści kolumny B i C, a potem automatycznie transmituje dane do kolumny B. A dopiero kolejną funkcjonalnością takiego makra będzie to, które zostało przedstawione powyżej. Robot, który realizuje te funkcjonalności działa zdecydowanie szybciej niż ręczne czyszczenie zakresu komórek, kopiowanie danych za pomocą Ctrl C + Ctrl V (+ kopiowanie z zamianą na wartości) oraz wykorzystanie Ctrl E (+ poprawianie).
To jest różnica między automatyzacją pracy (dzięki zastosowaniu robota), a wspomaganiem pracy „ręcznej” w Excel.

Wniosek: nie ma potrzeby używania Ctrl E. Trzeba używać alternatywne inne, lepsze narzędzia.

Oceniłem - porównałem rozwiązanie wypełniania komórek: Ctrl E z makrem.
Kryterium oceny to jakość użytkowania rozwiązania. Tu jako krytrium wykorzystałem tylko jeden z elementów jakości: istnienie automatyzacji (czyli szybkość rozwiązania problemu otrzymywania dat).
Jakość rozwiązania (robota) to nie tylko fakt automatycznego działania, które jest lepszą alternatywą pracy ręcznej lub półautomatycznej. Jakość to również inne cechy robota.
Według norm ISO (na przykład pierwszej: ISO 9126) jest kilka cech (równie ważnych) jakości oprogramowania.
Między innymi: czytelność, elastyczność, bezpieczeństwo, szybkość działania…

Dlaczego?
Bo pewnie każdy chciałby mieć zrozumiałe, łatwe w użyciu, bezpieczne w działaniu narzędzie informatyczne, którego budowa jest prosta i można byłoby takie narzędzie łatwo modyfikować (dopasowywać do zmieniających się warunków rzeczywistości). To powinien być standard, norma... dlatego wiele osób, poświęciło dużo energii po to, aby powstały normy jakości oprogramowania (chylę czoła przed jednym z nich: McCall).

A co ja robię?
Staram się stosować normy jakości oprogramowania w praktyce (w budowie robotów w MS Excel) - stąd neguję wielu sposobów rozwiązań, które nie spełniają norm.




Przykład 2.

Elastyczność rozwiązania

Do tworzenia funkcjonalności: relacji (za pomocą funkcji) można użyć:
- MATCH i INDEX (PODAJ.POZYCJĘ i INDEKS) (zobacz Rysunek nr 3, w. 11-13),
- VLOOKUP (WYSZUKAJ.PIONOWO) (zobacz w. 15-16),
- XLOOKUP (X.WYSZUKAJ) (zobacz w. 18-19).
Na Rysunku nr 3 jest porównanie relacji zrealizowanej za pomocą w/w funkcji.


Rysunek nr 3. Porównanie działania funkcji WYSZUKAJ.PIONOWO z funkcjami PODAJ.POZYCJĘ I INDEKS (przed i po dostawieniu dodatkowej kolumny).

Część 1. Wiek i płaca osoby Jan są wyliczone różnymi sposobami i są takie same (zobacz komórki F12, F15 i F18 oraz komórki F13, F16 i F19).

Część 2. Pomiędzy kol. B i C została dostawiona kolumna.
Efekt:
- wszystkie komórki na prawo od kolumny C zostatały przesunięte (są w innych kolumnach),
- w komórkach na prawo od kol. C zmieniły się adresy w formułach.
Zmienił się wynik działania formuły w komórkach: G15 i G16 (poprzednio komórki F15 i F16). Wynik funkcji WYSZUKAJ.PIONOWO w komórce G15 jest równy 0, poprzednio był równy 36 (w komórce F15). W komórce G16 wynik jest 36, a poprzednio 6200 (w komórce F16). Wyniki funkcji PODAJ.POZYCJĘ, INDEKS oraz X.WYSZUKAJ nie zmieniły się (zob. Rysunek nr 3, Część 2).

Wniosek: Funkcja WYSZUKAJ.PIONOWO nie jest elastyczna, nie działa poprawnie po zmianie struktury danych (dodaniu nowej kolumny).
To oznacza, że używając tej funkcji traci się czas przy modyfikowaniu rozwiązania (ponieważ błąd należy zidentyfikować i poprawić).
W niewielkim przykładzie można łatwo zauważyć błędy i szybko je poprawić. W rozwiązaniach wieloarkuszowych jest zdecydowanie gorzej.

Zastosowanie WYSZUKAJ.PIONOWO powoduje, że po zmianie struktury danych trzeba przejrzeć wszystkie arkusze i sprawdzić wystąpienie błędu.
Z praktyki wiemy, że zmiany w rozwiązaniach robi się często, a to oznacza, że używając funkcji WYSZUKAJ.PIONOWO, poświęca się (traci) czas na szukanie i korygowanie błędów. Zatem rezygnujemy z używania z tej funkcji.


Porównując użycie pozostałych funkcji: X.WYSZUKAJ z PODAJ.POZYCJĘ i INDEKS należy zwrócić uwagę, na ich możliwości:
- INDEKS(zakres; PODAJ.POZYCJĘ(…);PODAJ.POZYCJĘ(…))
pozwala na wyszukanie wartości z dwuwymiarowego zakresu, co nie jest możliwe przy użyciu X.WYSZUKAJ.
- Wybranie wartości nie ze znalezionego wiersza, tylko z wiersza poprzedniego/następnego to:
-- INDEKS(zakres; PODAJ.POZYCJĘ(…)-1)
-- INDEKS(zakres; PODAJ.POZYCJĘ(…)+1), nie da się do tego celu użyć X.WYSZUKAJ.
- Użycie funkcji PODAJ.POZYCJĘ raz, a potem wykorzystanie jej wyniku w wielu funkcjach INDEKS działa szybciej, niż X.WYSZUKAJ.

I na koniec o sposobie zastosowania funkcji PODAJ.POZYCJĘ. Jeśli w drugim parametrze będzie podana cała kolumna, to wynik działania funkcji: numer pozycji jest jednocześnie numerem wiersza, z którego funkcja/funkcje INDEKS będą pobierały informacje. Taki sposób pozwala na szybszą analizę przepływu informacji (gdyby w drugi parametr funkcji PODAJ.POZYCJĘ nie zaczynał się w pierwszym wierszu, to czas analizy strumienia informacji wydłużyłby się (bo numer pozycji nie byłby równy numerowi wiersza i aby sprawdzić poprawność wyszukania należałoby przeliczać ręcznie). Stosując funkcję X.WYSZUKAJ nie ma możliwości takiego sprawdzenia. To są powody, dlaczego nie używam funkcji X.WYSZUKAJ.


+ Przykład nadmiernej złożoności

Bardzo często oglądam różne rozwiązania i widzę niepotrzebne komplikowanie formuł.
Na przykład, często powtarza się schemat przedstawiony (poniżej):
Załóżmy, że prowadzimy rejestr, w którym część danych jest wpisywanych, a część danych liczonych za pomocą formuł. Bywa tak, że kolejne wyliczone wartości zależne są od spełnienia lub niespełnienia warunku (naturalnym jest, że użytkownicy stosują funkcję JEŻELI). I tak, często widzę formułę w postaci (tu przykładowo T4 i późniejsze wykorzystanie U4):
T4: =JEŻELI(warunek; ”TAK”; ”NIE”)
a potem w kolejnych kolumnach
U4: =JEŻEL I(T4=”TAK”; wartość_1; wartość_2).
…
Tu jest zwykle wytłumaczenie, że jest potrzeba widoczności wskaźnika w kolumnie T, to znaczy, że użytkownik musi widzieć, czy sposób rozliczenia to sposób 1.

Ja proponuję mniej złożony sposób, który wykorzystuje wartości logiczne: PRAWDA/FAŁSZ; ten sposób jest łatwiejszy i działa szybciej.
T4: =warunek (formuła, która daje w wyniku wartość logiczną: PRAWDA lub FAŁSZ)
U4: JEŻELI(T4; wartość_1; wartość_2)
W nagłówku kolumny T powinien być tekst kończący się znakiem zapytania, aby jednoznacznie określał co oznacza odpowiedź w kolejnych wierszach kolumny T.

Jeśli tylko raz wykorzystany jest wynik warunku i użytkownik stwierdzi, że nie ma potrzeby mieć dodatkowej kolumny ze wskaźnikiem, to można nie tworzyć kolumny z wartościami logicznymi, tylko bezpośrednio, na podstawie warunku policzyć wartość:
U4: =JEŻEL I(warunek; wartość_1; wartość_2)
W pliku … znajduje się przykład, w kolejnych arkuszach znajdują się trzy rozwiązania. Zapraszam do analizy.

Zwracam uwagę, że:
* komplikowanie formuł wpływa na zwiększenie czasu analizy, modyfikacji rozwiązania, a także przy dużej liczbie formuł wydłuża czas przeliczania.
* złożone i długie formuły dłużej się analizuje, nie ma wyników pośrednich, które są ważne do analizy sprawdzania poprawności działania; zobacz przykład wykorzystania PODAJ.POZYCJĘ i INDEKS; jeśli te funkcje będą użyte w osobnych komórkach, to (dzięki widocznemu wynikowi funkcji PODAJ.POZYCJĘ) łatwiej sprawdzić, z której komórki funkcja INDEKS pobiera wartość.
* nazywanie zakresów nie sprzyja szybkiej analizie formuł; przy większej liczbie nazw nie jest możliwe łatwe zarządzanie nimi; dlatego to kolejny element arkusza, którego nie powinno się stosować (przeszkadza, a nie pomaga).


Wszyscy wiemy, że jeśli problem do zrealizowania będzie przemyślany i przygotuje się plan działania – projekt, to zadanie szybciej zostanie wykonane, a prawdopodobieństwo jego należytej jakości jest większe. Zasada przygotowanie zapisania) w punktach, kolejnych kroków (kolejnych działań) dotyczy również projektów w Excel.

Jeśli nie będzie projektu (czyli co się przypomni to będzie robione), to działania sprowadzają się do cyklu: tworzenia, poprawiania – powtarzanego wiele razy. Wtedy wydłuża się proces tworzenia rozwiązania, a prawdopodobieństwo uzyskania jego właściwej jakości jest niskie. Wiem to z doświadczenia, sam to przerabiałem na początku pracy z arkuszem i innymi narzędziami informatycznymi (w tym językami programowania), w których można tworzyć automatyzację. Dlatego jestem zdania, że już na początku procesu dydaktycznego trzeba wprowadzać elementy projektowania + zasady oceny rozwiązań. Przyswajanie wiedzy na temat technicznych możliwości narzędzi powoduje ukształtowanie się nawyków, niekoniecznie najszczęśliwszych, a takie należy eliminować. Usuwanie złych nawyków jest trudniejsze niż kształtowanie od razu (na początku procesu dydaktycznego) nawyków logicznie uzasadnionych. Tu polecam wiedzę o neuroplastyczności (działaniu) mózgu, czyli jak kształtują się nawyki, jak utrwalają i jak mózg postępuje z nawykami (również nałogami).

Ciut potrzebnej wiedzy o tworzeniu projektu - planu działania...
...aby przygotować taki plan (oczywiście uwzględniający należytą jakość rozwiązania), to potrzebna jest wiedza jak powinny być zrealizowane poszczególne funkcjonalności, które w takim planie należy umieścić, a potem wykorzystać przy budowie.
Całość projektu sprowadza się do:
- ustalenia wyobrażenia/wizji działania robota w MS Excel,
- zapisania wizji działania w postaci poszczególnych funkcjonalności – projekt,
- przełożenia funkcjonalności na ich implementację w MS Excel (+ dopracowanie szczegółów).
Rysunek nr 5. Schemat standardowego podzialu modelu (rozwiązania, robota) na poszczególne arkusze. Idea podziału. Na Rysunku został przedstawiony ogólny schemat, który stosuję do projektów, taki sposób pomaga ustalić wyobrażenia działania projektu.


Na ogół na różnych szkoleniach z Excel pokazywane są tylko aspekty techniczne MS Excel.
Wiem, że jeśli ktoś posiadłby większość wiedzy technicznej o działaniu arkusza, a nie będzie miał wiedzy merytorycznej na temat projektowania i budowy robotów (tworzenia automatyzacji) to i tak będzie miał kłopoty ze zbudowaniem dobrego rozwiązania.

Na moich warsztatach, pokazuję tylko wybrane elementy MS Excel, które najczęściej uczestnicy znają i używają. Do działania technicznego tych elementów dokładam: organizację ,zasady, zasady projektowania, umiejętności oceny. Z tego powodu na warsztatach są głównie przykłady, które to pokazują.
Dlatego jeśli posiadasz sprawność techniczną posługiwania się Excel (swoboda posługiwania się kalwiaturą/myszą umiesz stosować w odpowiednich miejscach właściwe formuły (w tym funkcje), to zapraszam od razu na warsztaty PE1 (projektowanie i budowa robotów wg Metodyki 4TG). Jeśli brakuje Ci takiej swobody, to zapraszam na wPE (wstęp do Metodyki 4TG) na których jest też część techniczna narzędzi MS Excel (używanych w Metodyce 4TG).
Zobacz:


Sam używam poniżej 1% elementów, tych które są dostępne w pakiecie MS Excel. Za pomocą tak małej ilości elementów tworzę roboty, które mają wszystkie potrzebne funkcjonalności i nie tylko łatwo się je używa, ale również (głównie dzięki temu że korzystam z samych prostych elementów Excel) łatwo się je rozumie (szybko analizuje). To jest inna filozofia pracy niż tak, którą preferuje producent Excel (MS tworzone są trudne narzędzia, dla specjalistów… bo dla MS liczy się kasa, a nie jakość rozwiązań).

IM MNIEJ ELEMENTÓW EXCEL STOSUJESZ (zachowując potrzebne funkcjonalności), TYM ŁATWIEJ ZROZUMIESZ ROZWIĄZANIE

IM ŁATWIEJSZE ELEMENTY STOSUJESZ, TYM SPRAWNIEJ MOŻESZ POPRAWIĆ (ZMODYFIKOWAĆ) ROZWIĄZANIE

Zrobiłem wiele narzędzi (prostymi elementami arkusza, Visual Basic, czy SQL), które realizują potrzebne funkcjonalności (takich, które w MS Excel są kiepsko zrobione, albo ich nie ma wcale). Przy tworzeniu narzędzi skupiam uwagę na zasadach projektowania struktur danych oraz wykorzystaniu zasad, które powodują utrzymanie jakości robotów.

Pamiętajmy, że jeśli ustalimy proste zasady organizacji i działań na strukturach danych, to w Visual Basic takie zasady obsługują łatwe i prosto zbudowane narzędzia do automatyzacji tych działań.


I na koniec to, co zmieniam w ustawieniach standardowych na komputerze jak zaczynam pracować w MS Excel, po to, aby było łatwiej używać Exel/VB.

1. W niektórych komputera klawisze funkcyjne są tak ustawione, że w MS Excel chcąc ich użyć należy użyć jeszcze inny klawisz (Fn, Ctrl, Alt lub Shift) – to koniecznie trzeba zmienić. Po to, aby w prosty sposób używać: F2, F4, F9 (to są bardzo często używane przeze mnie, skróty klawiszowe wykorzystujące klawisze funkcyjne). Dlatego nie może być tak, aby gimnastykować ręce i tracić czas na ich użycie.

2. W opcjach zaawansowanych MS Excel: Plik | Opcje | Zaawansoane | Przenieś zaznaczenie po naciśnięcuu klawisza Enter – usuwam zaznaczenie. Wtedy, po wpisaniu dowolnej wartości do komórki, i zastosowaniu ENTER komórka aktywna nie zmienia się, a to znaczy, że po użyciu klawisza F2 można szybko wrócić do edycji (nie trzeba zmieniać komórki). A to pozwala na szybszą pracę. Można również, po wpisaniu danej do komórki użyć strzałkę w dowolnym kierunku (w trybie Wprowadź), a to oznacza, że wpisana wartość zostanie zaakceptowana + aktywna komórka zmieni się (uwaga: w trybie Edycja strzałki inaczej działają).
Proszę spróbować.

3. W edytorze VB zmieniam (włączam)
Tools | Option | Editor | Require Variable Declaration...
… wtedy, przy tworzeniu nowego modułu pojawi się: Option Explicit (na początku modułu). To oznacza większe bezpieczeństwo, ponieważ zastosowanie tego zapisu na początku modułu powoduje, że wszystkie zadeklarowane zmienne są automatycznie sprawdzane przed każdym uruchomieniem (narzędzie wychwytuje literówki w nazwach zmiennych przed uruchomieniem makra). Dlatego użycie tej opcji jest koniecznością (zwiększa się bezpieczeństwo i oszczędza się czas).

PAMIĘTAJ… tworząc cokolwiek, musisz usunąć ze swojego otoczenia wszystko to, co przeszkadza, czy może przeszkadzać, a zdecydowanie szybciej rozwiążesz problem.


Pomagamy naszym klientom w różny sposób:

- uczymy ich postępowania z MS Excel tak, aby nie tracili dużo czasu (w tym uczymy ich projektowania i budowy robotów)… i nie chodzi tu o sprawność techniczną posługiwania się Excel,

- projektujemy i budujemy roboty dla użytkowników wg zasad Metodyki 4TG,

- przeprowadzamy audyty rozwiązań w MS Excel… ostatnie analizy rozwiązań doprowadziły do tego, aby autorzy tych rozwiązań zmienili je wg przedstawionych wytycznych,

- przetwarzamy zdalnie dane dla klientów (wykorzystujemy do tego zrobione przez nas roboty).