Jakość projektów w MS Excel

Tekst otwarty nr 25/2020

Zastanawialiście się kiedyś, jak rozumieć jakość w oprogramowaniu? Kilkanaście lat temu, wchodząc do pokoju, zobaczyłem moich pracowników (wcześniej moich studentów) płaczących ze śmiechu. Pokazali mi kilka komentarzy pod rozwiązaniem w Internecie, które zamieniało wartość liczby na zapis słowny. Śmiali się z kontrastu między tym, co napisali komentujący, a jakością rozwiązania. Kilkulinijkowe, niezrozumiałe wzory, trudna w zrozumieniu struktura rozwiązania. Na zajęciach nie uczyłem ich tylko MS Excel, ale również zasad budowy rozwiązań, w tym zasad czytelności rozwiązań po to, aby mogli rozumieć, co napisali, oraz zasad elastyczności, by mogli w prosty sposób modyfikować rozwiązania.

Jakość projektów w MS Excel

Jakością oprogramowania, jako jeden z pierwszych, zajmował się J.A. McCall. Na podstawie jego prac powstały międzynarodowe normy ISO dotyczące jakości oprogramowania (wśród nich: 9126 i 25010). Ja zajmuję się zastosowaniem arkuszy kalkulacyjnych już ponad 30 lat, a jakością rozwiązań w MS Excel kilkanaście lat. Przybliżę ten temat tutaj.

Oprogramowanie

Oprogramowanie to zapis algorytmu w języku programowania. Wzory w kolejnych komórkach, korzystające z wyników wyliczeń w poprzednich komórkach, to wynik działania algorytmu. To znaczy, że zapis wzorów w komórkach arkusza jest oprogramowaniem i również powinien podlegać ocenie jakościowej. Oczywiście ocena dotyczy nie tylko zapisu wzorów w komórkach, ale również zastosowanych struktur danych, czy użytych narzędzi: języka Visual Basic czy języka SQL.

Wszyscy używający MS Excel wiedzą, że każde rozwiązanie w arkuszu można zrobić lepiej albo gorzej. Większość osób, która się tym zajmuje, próbuje (w swoim mniemaniu) stosować lepsze, a nie gorsze rozwiązania. Jeśli tak, to proponuję postawić pytanie: jak rozpoznać, które rozwiązanie jest lepsze, a które gorsze?

Jedną z podstawowych rzeczy, którą należy ocenić, jest realizacja założonej funkcjonalności. W tym celu należy porównać funkcjonalność, jaką założył użytkownik z funkcjami realizowanymi przez rzeczywiste rozwiązanie. Jednak takie porównanie to tylko część oceny jakości rozwiązania.

Analizując temat szerzej, możemy zauważyć, że nie tylko rozwiązanie ma działać, ale również ważne jest:

  • W jaki sposób rozwiązanie działa?
  • Czy wynik uzyskuje się w prosty sposób?
  • Czy czas działania jest akceptowalny?
  • Czy działanie rozwiązania jest zrozumiałe?
  • Czy rozwiązanie łatwo jest użytkować?

A jeśli dużo wiemy o działaniu MS Excel, to pewnie chcielibyśmy wiedzieć:

  • Czy można łatwo analizować poszczególne kroki działania/budowy rozwiązania?
  • Czy można w prosty sposób do rozwiązania wprowadzać modyfikacje?

Normy ISO dotyczące jakości oprogramowania

Takich i podobnych pytań można postawić wiele. Podobne pytania zadawali ci, którzy zajmowali się jakością oprogramowania, a ich prace były źródłem powstania norm ISO. W normach zostały wyszczególnione cechy, jakimi powinien charakteryzować się program. W normie ISO 9126 wymieniono cechy:

  • funkcjonalność (oprogramowanie powinno robić to, co było określone w wymaganiach użytkownika),
  • niezawodność (brak błędów),
  • użyteczność (łatwość korzystania z oprogramowania),
  • efektywność (akceptowalna szybkość działania),
  • pielęgnowalność (możliwość łatwej modyfikacji),
  • przenaszalność (możliwość wykorzystania oprogramowania w wielu środowiskach).

W normie 25010 dodano jeszcze cechy:

  • kompatybilność (możliwość współpracy/wymiany danych z innymi programami)

oraz

  • bezpieczeństwo (chronienie danych i algorytmu).

Jeśli oprogramowanie ma wymienione wyżej cechy, oznacza to, że utrzymana jest właściwa jakość oprogramowania.

Cechy jakościowe rozwiązań w arkuszu kalkulacyjnym

Badając jakość oprogramowania J.A. McCall przeprowadził badania na oprogramowaniu tworzonym w językach oprogramowania, a nie na rozwiązaniach w arkuszu kalkulacyjnym. Rozwój różnych narzędzi informatycznych spowodował, że w tych narzędziach algorytm można zapisywać na wiele różnych sposobów, np. w arkuszu kalkulacyjnym.

Do zapisu programu w MS Excel wykorzystywane są:

  • wzory (formuły),
  • język Visual Basic (integralna część MS Excel),
  • język SQL (język zapytań do baz danych),

i nowe narzędzia typu Power.

Możliwość zapisu algorytmu za pomocą wzorów w komórkach arkusza, a nie tylko za pomocą języka oprogramowania daje większe możliwości. Dzięki temu, w wielu przypadkach, można wybrać taki sposób zapisu algorytmu, który jest:

  • prostszy w budowie (tu ważne jest używanie jak najłatwiejszych elementów MS Excel, ale takich, które realizują wszelkie możliwe funkcjonalności),
  • łatwe w interpretacji (czytelne),
  • łatwe i proste w analizie oraz modyfikacji (elastyczne),
  • bezpieczne (mające możliwy w MS Excel zakres cech związany z bezpieczeństwem).

Przecież każda osoba używająca rozwiązań w arkuszu kalkulacyjnym chciałaby mieć takie rozwiązania, które oprócz tego, że automatycznie działają, mają również wymienione powyżej cechy.

Jeśli popatrzymy na cechy zawarte w normach ISO i porównamy je z cechami wymienionymi powyżej, to okaże się, że ich zakres merytoryczny niewiele się od siebie różni. Różnica wynika z:

  • różnych narzędzi (problemy, które są rozwiązywane za pomocą arkusza kalkulacyjnego, są mniejsze pod względem zakresu/wielkości niż te, które rozpatrywał J.A. McCall, analizując jakość oprogramowania),
  • innego przygotowania osób, które używają arkusza kalkulacyjnego (większość osób, które wykorzystują MS Excel do budowy różnych rozwiązań jest mniej do tego przygotowana niż informatycy, którzy uczyli się pracy w zespołach, powołanych do projektowania dużych systemów),
  • różnego zakresu rozwiązywanych problemów (klasy problemów rozwiązywane za pomocą arkusza kalkulacyjnego są mniejsze; zwykle do projektowania i budowy rozwiązań w arkuszu wystarczy jedna osoba, która jest jednocześnie projektantem oraz wykonawcą takiego systemu; do projektowania dużych systemów informatycznych powoływane są całe zespoły projektantów i programistów).

Narzędzia informatyczne a jakość rozwiązań

Należy zdać sobie sprawę, że stosowane narzędzia informatyczne do zapisu algorytmu mają wpływ na poziom trudności w utrzymaniu określonego poziomu jakości oprogramowania. Oznacza to, że narzędzia informatyczne, które wykorzystuje się do projektu wpływają na szybkość projektowania i budowy, łatwość analizy i modyfikacji oprogramowania. A to znaczy, że chcąc uzyskać określony poziom jakości, konieczny jest dobór właściwych narzędzi i sposobu ich wykorzystania. Oczywiste jest, że określoną funkcjonalność można zrealizować bardzo różnymi sposobami przy wykorzystaniu różnorodnych narzędzi w MS Excel, a to oznacza odmienną jakość tych rozwiązań.

Przykład:

Wiele osób stosuje funkcję WYSZUKAJ.PIONOWO. Funkcja ta realizuje funkcjonalność relacji między strukturami danych. Wyszukuje i wybiera dane z jednej struktury i umieszcza w drugiej. Problem jest w tym, że w trzecim parametrze tej funkcji należy podać numer kolumny. Jeśli poda się go za pomocą wartości (liczby), to funkcjonalność tworzenia relacji między strukturami danych nie będzie elastyczna. Po dostawieniu/usunięciu kolumny wewnątrz zakresu funkcja wybierze wartość z niewłaściwej kolumny. Ci, którzy w ten sposób tę funkcję zastosowali, pewnie wiedzą, jak poprawić rozwiązanie po zmianach w modelu. Ale zajmuje to trochę czasu. Czas poprawienia takiego błędu organizacyjnego rośnie wraz ze wzrostem złożoności rozwiązania. Wyobraźmy sobie rozwiązanie, w którym jest kilkadziesiąt arkuszy i po zmianie struktury danych w jednym arkuszu trzeba przeszukać całe rozwiązanie i poprawiać funkcję WYSZUKAJ.PIONOWO. Nie ma z tym problemu, jeśli do namierzania numeru kolumny wykorzystamy funkcję PODAJ.POZYCJĘ, a do wybrania danych z właściwej kolumny funkcję INDEKS. Tu uwaga: można namierzyć jeden raz numer kolumny za pomocą funkcji PODAJ.POZYCJĘ, a wynik jej działania wykorzystać wielokrotnie w funkcjach INDEKS. Takie rozwiązanie nie tylko jest elastyczne, ale również czytelne, a oprócz tego szybciej działa niż wielokrotne zastosowanie funkcji WYSZUKAJ.PIONOWO.

Spotkałem również rozwiązania, które wykorzystują w trzecim parametrze funkcję namierzającą numer kolumny. Taki wzór jest mniej czytelny od zastosowania dwóch funkcji PODAJ.POZYCJĘ – do namierzania numeru kolumny i funkcji INDEKS do wybierania potrzebnej wartości ze struktury źródłowej.

Ostatnio pojawiła się w MS Excel funkcja XWYSZUKAJ, która utrzymuje elastyczność rozwiązań. Jednak chcąc, aby rozwiązania działały w różnych środowiskach (muszą działać również na wcześniejszych wersjach MS Excel), do realizacji funkcjonalności relacji między strukturami danych w dalszym ciągu stosuję PODAJ.POZYCJĘ i INDEKS.

Aby osiągnąć określoną jakość oprogramowania, należy przeanalizować możliwości stosowania różnych narzędzi występujących w arkuszu. Jest ich coraz więcej, nowe narzędzia typu Power miały zmienić sposób pracy w MS Excel. Według mojej oceny niektóre cechy jakościowe tych narzędzi (na przykład czytelność) nie są na takim poziomie, aby większość osób je używających i mogła w łatwy sposób zrozumieć ich zapis. Ważne jest również, że nie zastępują pracy w takim zakresie, w jakim można to zrobić prostymi narzędziami: wzorami, językiem Visual Basic oraz SQL.

Czytelność

Wielekroć spotkałem się z różnymi rozwiązaniami, które były tak zawile zbudowane, że ich analiza zajmowała mnóstwo czasu. Oczywiście osoby, które tworzyły takie rozwiązania, dopóki się nim zajmowały, rozumiały ich budowę. A w firmie zazwyczaj były uznawane za guru od MS Excel.

W jednej z firm osoba, która zostawiła rozwiązanie (przed odejściem do innej firmy), napisała listę czynności, jakie musi zrobić operator, aby otrzymać comiesięczne raporty, ale nie zostawiła opisu budowy rozwiązania (rozwiązanie było nieelastyczne – sztywne, w wielu miejscach niezrozumiałe, z długimi wzorami, bez komentarzy). Trudno było analizować strumień przepływu informacji, a co dopiero go modyfikować. Zmiany organizacyjne spowodowały, że konieczna była zmiana działania rozwiązania. Zaproponowałem zaprojektowanie i zbudowanie nowego rozwiązania, które pracownicy łatwo mogliby zrozumieć i samodzielnie modyfikować.

Całkiem niedawno z innej firmy dostałem rozwiązanie. Osoba, która je przysłała prosiła o automatyzację transmisji danych. Transmitowane pliki miały strukturę danych, która nie przystawała do struktury rejestru w modelu głównym. Układ danych, które należało przepisać do modelu głównego, był różny od struktury danych w plikach. Do jednego wiersza rejestru modelu głównego trzeba było pobrać informacje, które znajdowały się w pliku – w kilku miejscach w układzie pionowym, a w innych miejscach w układzie poziomym. Oprócz tego niektóre komórki w transmitowanym pliku były scalone. Tu uwaga – scalanie komórek zdecydowanie przeszkadza przy modyfikacji rozwiązania (z tego powodu nie stosuję tego narzędzia).

Zaproponowałem zwrócenie uwagi na to, co się będzie działo potem, po dostawieniu funkcjonalności transmisji danych (oczywiście złożonej, ponieważ struktury danych do siebie nie pasują). Złożoność całego rozwiązania wzrośnie, a w efekcie będzie coraz trudniej nad nim panować. Dlatego zasugerowałem zmiany nie tylko w strukturze danych przesyłanego pliku, ale również w samym rozwiązaniu, które zbierało różne dane. Podsunąłem propozycję takiego rozwiązania, aby zmniejszyć złożoność struktury danych, a tym samym zmniejszyć złożoność algorytmu. Uczynić rozwiązanie bardziej prostym, po to, aby można było je potem łatwo analizować i modyfikować. Popatrzmy na efekt finansowy takich działań (Rysunek 1).

W pierwszej części linia prosta ciągła (wspólna dla wszystkich) oznacza poziom dotychczasowych kosztów ponoszonych na utrzymanie rozwiązania. Podjęcie decyzji o budowie nowego rozwiązania wiąże się przyjęciem poziomu kosztów wyznaczonego przez linię zieloną. Przyjęcie decyzji o modyfikacji rozwiązania, to akceptacja poziomu kosztów na poziomie linii szarej (Rysunek 1). Linia przerywana wskazuje poziom kosztów utrzymania rozwiązania, jeśli nie zostałaby podjęta decyzja o działaniach na rzecz ulepszenia rozwiązania. Należy również zwrócić uwagę, że modyfikacja rozwiązania wiąże się z mniejszymi miesięcznymi kosztami utrzymania w stosunku do miesięcznych kosztów utrzymania bez jakiejkolwiek modyfikacji rozwiązania (zob. Rysunek 1 i porównaj linię przerywaną z linią szarą; przerywana linia rośnie szybciej niż linia szara).

Jednak miesięczne koszty utrzymania w przypadku budowy nowego, czytelnego i elastycznego rozwiązania są mniejsze (wolniej rosną) niż przy modyfikacji rozwiązania oraz pozostawieniu złożonych struktur i trudnego w analizie algorytmu (por. linię szarą z linią zieloną na Rysunku 1). Budowa takiego rozwiązania kosztuje więcej niż modyfikacja tego, które funkcjonuje. Jednakże po pewnym okresie koszt całkowity (budowy i utrzymania) nowego rozwiązania jest mniejszy niż modyfikacji istniejącego.

Ważne jest, aby nie brnąć w złożoność rozwiązań, tylko szukać prostych algorytmów i ich jak najłatwiejszej implementacji. Dzielić problem do rozwiązania na mniejsze problemy i dalej na szczegółowe funkcjonalności – zapisywać je w czytelny sposób, tak aby każdą część rozwiązania można było łatwo zinterpretować (zrozumieć). Robić komentarze w rozwiązaniu. Stosować kolory nie tylko dla potrzeb merytorycznych (problemów), ale również, a właściwie przede wszystkim, w celu łatwiejszej analizy strumienia przepływu informacji w rozwiązaniu.

Osobiście stosuję:

  • komórki z tłem jasnozielonym (komórki do wypisywania),
  • komórki z tłem jasnożółtym (komórki z ważniejszymi wzorami – po to, aby w razie konieczności analizy rozwiązania analizować wzory w tych komórkach w pierwszej kolejności),
  • komórki z tłem w kolorze łososiowym (komórki, do których makro wpisuje wartość),
  • komórki z tłem jasnoniebieskim (komórki, do których przy modyfikacji nie należy nic wpisywać bez wcześniejszej pogłębionej analizy rozwiązania),
  • czcionka niebieska (w komórkach, które wykorzystują wartości z innych arkuszy; najczęściej są to wartości parametrów).

Stosowanie przynajmniej tych zasad może zmienić czytelność – rozumienie rozwiązań nie tylko przez osoby, które je budowały, ale również przez osoby, które znają te zasady, ale nie budowały analizowanych rozwiązań.

Przykład

Dostałem do uzupełnienia rozwiązanie, które miało kalkulować koszty instalacji fotowoltaicznej.

W scalonych komórkach na początku każdego z bloków jest podany typ falownika, a w innej komórce moduł (rodzaj) panelu fotowoltaicznego. Z boku każdego bloku, w kilku wierszach, powtarza się struktura danych o powierzchni, na której miałyby być montowane panele. W kolumnach wiersza nagłówkowego mamy informację o liczbie paneli do zamontowania. Wyliczona moc za pomocą wzoru D5: = 0,350*D4. W każdej komórce D6:AJ12 wzory wyliczające koszt. Wzory korzystające w wielu przypadkach z wartości, a nie z adresów komórek; podobnie jak wzór w komórce D5 (Rysunek 2). Takich bloków w strukturze bazy danych jest wiele.

W innym arkuszu wybierane są (ze standardowej listy w MS Excel): typ falownika, rodzaj modułu, liczba modułów, powierzchnia dla montażu, a rozwiązanie ma wybierać wyliczone koszty z bazy danych. Problem, który miałem rozwiązać, to namierzenie i wybranie, z tak zaprojektowanej struktury, właściwych danych. W pierwszej chwili napisałem wzory, które łączą dane w bazie danych (typ falownika oraz rodzaj modułu), a w arkuszu z kalkulacją – narzędzie do namierzania właściwego bloku (za pomocą funkcji PODAJ.POZYCJĘ). Tę funkcję zastosowałem również do wyznaczenia numeru wiersza w namierzonym wcześniej bloku danych (na podstawie wybranej pozycji o powierzchni, na której będą montowane panele). PODAJ.POZYCJĘ została również wykorzystana do wyznaczenia numeru kolumny, w której jest określona liczba paneli. Z kolei za pomocą funkcji INDEKS udało się pobrać właściwe dane – wybraną skalkulowaną wartość instalacji (na przecięciu numeru wiersza i numeru kolumny wyznaczonych wcześniej przez dwie funkcje PODAJ.POZYCJĘ). Zrobiłem to rozwiązanie grzecznościowo, zajęło mi kilkanaście minut.

Po pewnym czasie rozwiązanie się zmieniło, w bazie danych (i tak już bardzo złożonej) zostały powielone bloki struktury danych. Struktura ta, z pewnymi modyfikacjami, skopiowana była trzy razy (Rysunek 3). W pierwszym bloku złożonej struktury danych wyliczony jest koszt (w arkuszu jest wpisana cena), w drugim bloku marża, a trzecim koszt + marża. Wszystkie trzy bloki powtarzały się dla każdego rodzaju panelu.

Takich trójblokowych struktur danych zobaczyłem więcej niż we wcześniejszym pliku. To znaczy, że pojawiały się nowe typy falowników i rodzaje paneli oraz kombinacje obydwu. Modyfikacja struktury danych spowodowała, że przestało działać rozwiązanie, które zaproponowałem wcześniej. Rozwiązanie zawsze wybierało dane z pierwszego bloku trójblokowej struktury, a powinno wybrać dane zarówno z pierwszego, jak i trzeciego bloku (dla zarządzającego i klienta).

Okazało się, że rozwiązanie prostego problemu kalkulacji kosztów jest złożone i nieelastyczne. Każdy dostawiony falownik, rodzaj paneli, nowa powierzchnia (na której montowane byłyby panele), powoduje duże zmiany w bazie danych. W takich przypadkach należy zmodyfikować wszystkie bloki danych lub utworzyć nowe struktury danych (bloki). A w arkuszu z kalkulacją kosztów pojawiają się trudniejsze wzory, które mają namierzyć nie tylko typ falownika czy rodzaj paneli, ale jeszcze koszt i koszt z marżą.

Należało zadać pytanie o celowość przechowywania wszystkich kalkulacji. Jeżeli interesują zarządzających jakiegoś typu porównania, to warto by było zaprojektować miejsce do wpisania różnych parametrów (na przykład powierzchni montażowej czy potrzebnej mocy) i na tej podstawie policzyć koszty, marżę czy cenę podawaną klientowi dla różnych zastosowanych falowników czy rodzajów paneli (policzyć, a nie wybrać). Przecież po zmianie wartości parametrów zmienią się wyniki.

Rozwiązanie problemu widzę zupełnie inaczej. Nie potrzeba arkusza z bazą bloków struktur danych, z wyliczoną kalkulacją kosztów dla każdej kombinacji wybranych elementów, lecz listy poszczególnych elementów (z kosztem jednostkowym). Potrzeba nam takiej funkcjonalności, która po wyborze elementu dawałaby automatycznie obok niego koszt jednostkowy. Na przykład lista, z której wybierzemy falownik, a obok (w komórce po prawej stronie) pojawi się jego koszt, podobnie przy wyborze z listy rodzaju paneli. Po wyborze określonego rodzaju paneli w komórce po prawej stronie pojawi się koszt jednostkowy itd. Po podaniu (wyborze) wszystkich elementów, od których zależna jest cena instalacji, wzór powinien policzyć koszty. Może to być wzór podzielony na kilka mniejszych wzorów, tak aby było widać grupy kosztów (do analizy) oraz/lub strumień przepływu informacji.

Kolejny problem w modelu ze strukturą blokową, to zmiana cen u dostawców (to nasze koszty). Po zmianie należy w wielu miejscach poprawić ceny we wzorach. Trzeba przeszukiwać arkusz i sprawdzić, w których miejscach należy zmienić ceny. A to oznacza, że jeśli nie znajdzie się wszystkich miejsc, w których konieczna jest zmiana ceny (jakaś poprawka nie zostanie zrobiona), to może wystąpić błąd w kalkulacji. Prawdopodobieństwo błędu jest o wiele niższe wtedy, kiedy zmiana ceny falowników czy paneli fotowoltaicznych byłaby robiona w jednym miejscu.

Oprócz błędów dotyczących struktury danych są jeszcze błędy organizacyjne w arkuszu:

  • scalenie komórek, które przeszkadza w nawigacji po arkuszu oraz przy budowie/modyfikacji wzorów,
  • wzory na wyliczenie poziomu mocy, na wyliczenie kosztów (w arkuszu z blokami danych), nie powinny zawierać wartości – powinny wartość pobierać z tych komórek, w których zostanie ona wpisana. W zamieszczonym przykładzie przedstawiłem wzór, który zawiera wartość (moc: 0,350 zamieszczona we wzorze D5: =0,350*D4), po skopiowaniu tego wzoru do innego bloku danych – z innym rodzajem paneli (najprawdopodobniej z inną mocą) trzeba poprawić (zmienić moc jednostkową we wzorze) i skopiować wzór w ramach wiersza. To sporo czynności do wykonania, a przecież można odwołać się do komórki, w której zostanie wpisana moc. Wtedy po skopiowaniu bloku ze wzorami można zmieniać zawartość jednej komórki (tej komórki, do której odwołują się skopiowane wzory). Gdyby moc była wpisywana do kolumny A (w tym przykładzie byłaby to komórka A5, ponieważ w 5 wierszu liczymy moc dla przykładowego bloku danych), to wzór wyglądałby następująco: D5: =$A5*D4, a po skopiowaniu całego bloku w inne miejsce, moc należałoby zmienić w innej komórce kolumny A. Takie rozwiązanie ma dodatkową zaletę, że wartości wpisane w osobnych komórkach (w tym przypadku wartość mocy jednostkowej) widać bezpośrednio w arkuszu i nie trzeba analizować wzorów w komórkach, aby sprawdzić, czy wartości są dobrze wpisane,
  • powtórzony tekst w każdym wierszu „Oferta na dostawę i montaż instalacji PV (netto)” – wydaje się, że wystarczyłoby tylko raz napisać ten tekst nad strukturą danych; powtórzony tekst zwraca na siebie uwagę i zakłóca czytelność całości.

Wszystkie wymienione tutaj przyczyny obniżenia jakości rozwiązania powodują, że zwiększa się:

  • trudność użytkowania (użytkowanie wymaga wielu czynności),
  • prawdopodobieństwo błędów,
  • czas obsługi,
  • czas analizy i modyfikacji przy konieczności zmian (uzupełnień),

a to wszystko wpływa na koszt tworzenia i późniejszego użytkowania rozwiązania.

Pamiętajmy, że:

Im łatwiejsze rozwiązanie, im mniej różnych elementów MS Excel zostało zastosowanych, im prostsze elementy MS Excel są wykorzystane do budowy rozwiązania, tym łatwiej je zrozumieć, analizować, modyfikować.

Funkcjonalności – rozwiązania standardowe

Rozwiązywane problemy można podzielić na mniejsze, te na jeszcze mniejsze i tak dalej. W ten sposób dochodzimy do problemów, które nazwałem funkcjonalnościami. Po każdym podziale problemu na mniejsze problemy konieczne jest zadanie pytania: czy są narzędzia informatyczne (w tym przypadku narzędzia w MS Excel), które rozwiązują problem? Jeśli nie, to problem dzielimy na mniejsze problemy. Jeśli tak, to wykorzystujemy narzędzia, aby zaimplementować rozwiązanie problemu. Dalsza analiza zajmuje się wyodrębnionym problemem i jego implementacją. Jeżeli wyodrębniony problem występuje również w innych większych problemach do rozwiązania (przykłady problemów: wykorzystanie rejestrowej struktury danych, relacje między rejestrami, zasilanie rejestru danymi, usuwanie danych z rejestru, tworzenie raportów z rejestrów), to możemy go nazwać funkcjonalnością standardową, a ogólne rozwiązanie problemu w MS Excel – rozwiązaniem standardowym. Rozwiązania standardowe służą do budowy automatycznie działających rozwiązań (systemów), a nie do pracy ad hoc. Jednak zdarza się, że oprócz rozwiązań standardowych konieczne jest rozwiązanie problemu nietypowego. Oznacza to, że automatycznie działające systemy zbudowane są z:

  • rozwiązań standardowych, które działają w wielu systemach;
  • rozwiązań nietypowych, specyficznych dla danego systemu.

Jeśli zadbamy o to, aby rozwiązania standardowe oraz rozwiązania nietypowe były zbudowane według zasad jakościowych (zob. ww. cechy jakościowe oprogramowania), to całość rozwiązania też będzie miała właściwą jakość.

Podczas rozwiązywania problemów związanych z szeroko pojętą ekonomią, okazało się, że można określić funkcjonalności standardowe, to znaczy takie, które są potrzebne do budowy automatycznie działających systemów.

Na przykład:

  • czytanie danych,
  • zapis danych,
  • filtrowanie danych,
  • sortowanie danych,
  • wybieranie unikatów,
  • tworzenie relacji między strukturami danych,
  • zarządzanie listami wyboru itd.

MS Excel jest nastawiony na pracę ręczną, co oznacza, że aby zrealizować funkcjonalności należy, w wielu przypadkach, wybrać określoną funkcjonalność z menu. Na przykład, aby wykonać sortowanie, konieczne jest zaznaczenie danych i wybranie z menu parametrów sortowania. Można też tę funkcjonalność zapisać w Visual Basic tak, aby wykonał ją automat, zadać, w arkuszu z parametrami, parametry sortowania (którymi można sterować wykonywaniem programu w Visual Basic), wartości parametrów wpisać do arkusza albo wpisać wzory, które wartości parametrów wyliczą. Uruchomienie jednej funkcjonalności bardzo często jest realizowane z innymi funkcjonalnościami, np. sortowanie za pomocą makro może wiązać się z realizacją funkcjonalności kopiowania danych.

No i ciekawostka: funkcjonalność sortowania według wartości liczbowych można zapisać za pomocą samych wzorów (stosuje się to zwykle w niewielkich, pod względem zakresu, strukturach danych) (Rysunek 4).

Jedną z zasad budowy automatycznie działających rozwiązań jest stosowanie jednakowych wzorów w jednej kolumnie (jeśli w kolumnie są wzory, to mają być powielarne – jednakowe w ramach jednej kolumny). Uwzględniając powyższe można w opisie przykładu wyjaśnić tylko wzory z wiersza 7.

Algorytm do sortowania struktury danych (struktura danych do posortowania znajduje się w kolumnach A:C) za pomocą wzorów składa się z:

  • ustalenia rankingu,
  • stworzenia relacji między liczbą porządkową (w komórce A7 jest wzór: = A6+1) a rankingiem w celu ustalenia numeru wiersza, z którego trzeba wybrać uporządkowane (posortowane) dane,
  • wybranie danych według numeru wiersza – wyniku relacji (zob. poprzedni punkt).

Ustalenie rankingu (kolumny E:F)

Tu uwaga: narzędzie do budowy rankingu (funkcja POZYCJA i korespondująca z nią funkcja PODAJ.POZYCJĘ) wymaga unikatowych wartości liczbowych, według których struktury będą sortowane. To jest powód modyfikacji wartości z kolumny C. Wartość 34 powtarza się w dwóch komórkach kolumny C. Gdyby wartości nie powtarzały się, to i tak trzeba by było zastosować rozwiązanie, ponieważ po zmianie danych może się okazać, że występują jednakowe dane – jeśli tworzy się model działający automatycznie, trzeba dopuścić możliwość różnych danych. Wartości z kolumny C zostały zmodyfikowane w komórkach kolumny E. W komórce E7 jest wzór: =ZAOKR(C7;0)+WIERSZ()/1000. Część pierwsza wzoru: ZAORK(C7;0) zapewnia, że w przypadku wpisania ułamka do komórki C7 wartość zostanie zaokrąglona do wartości całkowitej. Druga część wzoru: WIERSZ()/1000 to wartość ułamkowa – inna w każdym wierszu. Zastosowanie całego wzoru (sumy obydwu części) gwarantuje otrzymanie unikatowych wartości w całej kolumnie E.

Ranking jest wyliczony w kolumnie F, wzór w komórce F7: =POZYCJA(E7;E:E) wyznacza numer pozycji (według unikatowych wartości z kolumny E).

Utworzenia relacji między liczbą porządkową, a pozycją w rankingu (kolumna G)

Celem jest wyznaczenie numeru wiersza, z którego należy pobrać posortowane dane. Tu należy zwrócić uwagę, że w komórkach kolumny A i w komórkach kolumny F są takie same wartości liczbowe, tylko w innej kolejności. Gdyby nie zastosowano narzędzia, które daje unikatowe wartości (zob. kolumna E), to w rankingu niektóre pozycje mogłyby wystąpić wielokrotnie, a tym samym nie byłoby niektórych (tych, które wystąpiły w kolumnie A). Proponuję samodzielnie sprawdzić. Relacja między liczbą porządkową a wartością rankingu jest zapisana w kolumnie G. I tak, w komórce G7 wzór: =PODAJ.POZYCĘ(A7;F:F;0) wyznacza numer wiersza (dla pierwszej pozycji rankingu – komórka A7).

Wybranie pozycji w strukturze danych według ustalonej kolejności (kolumny I:J)

Wybranie danych (w komórkach kolumn I i J) jest realizowane za pomocą wzoru w komórce I7: =INDEKS(B:B;$G7). Wzór wybiera wartość z kolumny B, w tym przypadku drugi parametr G7 (tu 10) wskazuje, że dana zostanie pobrana z komórki B10. Po skopiowaniu wzoru do komórek I7:J11 otrzymamy posortowane dane początkowej struktury danych.

W Power Query pojawiła się możliwość ustalenia realizacji sekwencji różnych funkcjonalności (w tym sortowania) – tak jak w języku Visual Basic. Oznacza to, że realizacja określonej funkcjonalności w automatycznie działającym rozwiązaniu, może być różnie wykonana, ale powinna mieć wszelkie cechy jakościowe rozwiązania (patrz powyżej). Chodzi również o to, aby przejście z realizacji jednej funkcjonalności do następnej było płynne (dlatego narzędzia standardowe muszą do siebie pasować). Jeśli nie można w łatwy sposób, na podstawie wyników danych poprzedniej funkcjonalności, zrealizować kolejnej, to oznacza, że zachodzi niespójność między doborem rozwiązań (wprowadzając Power Query producent MS Excel w dużej części rozwiązał problem niespójności). Ja, tworząc narzędzia – rozwiązania standardowe oraz zasady projektowania i budowy automatycznie działających rozwiązań – pokazuję inny, prosty sposób. Jest nim Metodyka 4TG.

Na ogół rzeczywistość jest taka, że użytkownicy, a często osoby, które budują rozwiązania w MS Excel, aby uzyskać wynik próbują połączyć różne, często przypadkowe, narzędzia MS Excel. Inaczej mówiąc, próbują zrealizować różne funkcjonalności połączone ze sobą, ale jeśli wykorzystują do tego nieprzystające do siebie narzędzia, to jakość takich rozwiązań jest niska.

Przykład

Od znajomej dostałem plik w MS Excel, który za pomocą MS Query ściągał informacje z pliku typu *.csv. Zostałem poproszony o poprawienie rozwiązania w Power Query. Z jednego wiersza w pliku *.csv (jednego rekordu, w którym dwie pozycje powtarzały się kilka razy) miałem zrobić kilka rekordów w MS Excel (po jednym rekordzie dla każdej pary powtarzających się danych). Powstały dwa rozwiązania: jedno zrealizowane za pomocą Power Query, a drugie w Visual Basic.

Trudno mi ocenić obiektywnie, bo pracuję w Visual Basic od lat i mam wypracowane proste narzędzia, które realizują wszelkie potrzebne funkcjonalności przy transmisji danych. Power Query jest od niedawna. Jednak według mojej oceny rozwiązanie za pomocą MS Query jest bardziej złożone (i trudniej je zrozumieć) niż rozwiązanie uzyskane za pomocą Visual Basic. Rozwiązanie w Visual Basic zostało zrobione według projektu:

  1. Otworzenie pliku *.csv
  2. Przepisanie do arkusza roboczego
  3. Zamknięcie pliku *.csv
  4. Dla każdego wiersza użytkowego arkusza roboczego: dla każdej powtarzającej się struktury w arkuszu roboczym – przepisanie części stałej i właściwej części zmiennej do arkusza wynikowego (zgodnie z parametrami zapisanymi w wyznaczonym do tego arkuszu
  5. Wyczyszczenie arkusza roboczego.

Komunikacja

Dla przekazu informacji między ludźmi czy komunikacji z komputerem ważny jest język i jego funkcje. Funkcji języka do komunikacji między ludźmi jest dużo¹. W komunikacji człowieka z komputerem ważna jest tylko niewielka część tych funkcji, która sprowadza się do przekazu informacji. Jeśli nie potrzeba wielu funkcji języka, to nie potrzeba również wielu jego elementów. Ważne również jest to, aby język był prosty i w czytelny sposób określał przekaz informacji. Nie powinno być możliwości używania wyrazów bliskoznacznych. Zastosowanie wyrazów bliskoznacznych w komunikacji między ludźmi jest potrzebne, ponieważ powoduje niewielkie zróżnicowanie w rozumieniu informacji plus niuanse, które wyrażają większe lub mniejsze emocje. W komunikacji między człowiekiem a komputerem – niepotrzebne.

W rzeczywistości określoną funkcjonalność można zrealizować w MS Excel wieloma różnymi sposobami. Jest to, w rozumieniu komunikacji z komputerem, bliskoznaczność. Powoduje to, że zapis w MS Excel może być różny, a przez to, dla odbiorcy, nie zawsze czytelny. Dlatego należy pamiętać, że: TO, CO ZAPISZESZ W FORMIE PROGRAMU, POTEM BĘDZIESZ CZYTAŁ.

Przecież chodzi nam również o to, aby rozumieć to, co zapisaliśmy wcześniej, po to, aby łatwo można było analizować, modyfikować (porównaj to z cechami norm ISO).

Mając na uwadze praktyki stosowania narzędzi w MS Excel, nie pozostaje mi nic innego jak, przy okazji realizacji projektów:

  • przeprowadzać badania nad algorytmami i ich implementacją,
  • polepszać czytelności,
  • poprawiać użyteczność i łatwość komunikacji,
  • ulepszać elastyczność różnych funkcjonalności potrzebnych przy budowie automatycznie działających rozwiązań.
Przypisy / Źródła / Podstawa prawna
  1. https://pl.wikipedia.org/wiki/Funkcje_j%C4%99zyk

  • Głuszkowski Tomasz, Metodyka 4TG na tle metodologii projektowania systemów informatycznych, Łódź 2013.

Zobacz również

Tylko on-line nr 26/2021

Przegląd systemów WMS

Przegląd systemów WMS

Magazyn staje się sercem łańcucha dostaw i dlatego jest centralnym punktem inwestycji sprzedawców detalicznych, producentów i dostawców usług logistycznych. System technologii magazynowej staje się coraz bardziej złożony, operatorzy łańcucha dostaw potrzebują bardziej wyrafinowanych systemów zarządzania, które mogą koordynować dużą liczbę indeksów, różnorodność inteligentnych urządzeń i systemów połączonych w ich obiektach. Z pomocą przychodzą systemy WMS.

Czytaj więcej
Tylko on-line nr 26/2021

Ataki hakerskie na dane firmowe: dlaczego nadal padamy ich ofiarą?

Ataki hakerskie na dane firmowe: dlaczego nadal padamy ich ofiarą?

Z roku na rok rośnie liczba i zakres ataków hakerskich. Niestety, kultura bezpieczeństwa w polskich firmach nie rośnie wraz z nimi. Jak przebiegają najczęstsze ataki hakerskie? Jak się przed nimi zabezpieczyć i dlaczego inwestycja w nawet najnowocześniejsze technologie to wciąż za mało?

Czytaj więcej

Przejdź do

Partnerzy

Reklama