Od dość dawna (lata bardziej niż miesiące, ale jeszcze nie dekady) próbuję sobie zrobić jakieś w miarę sensowne narzędzie do prowadzenia domowego budżetu. Jednak jak się nie obrócę tak dupa z tyłu, wszystkie podejścia jak na razie skończyły się raczej średnio. Próbowałem już klamotów typu Firefly (bardzo solidne, a do tego darmowe, oprogramowanie typu klient-serwer, ale niestety w wersji III usunęli forecasting, a ja potrzebuję), GnuCash (też świetny, ale za bardzo zorientowany pod księgowych) i paru innych cudaków. Po drodze mnóstwo podejść w Excelu i jedno nawet w Pythonie, własnej roboty.
Najświeższa inkarnacja to domowej roboty arkusz, zawiła mieszanka excelowych formuł na powierzchni doprawiona solidną porcją zapytań PowerQuery pod maską. Działa na tyle przyzwoicie, że używamy tego od mniej więcej roku i jako tako się sprawdza. Ale ma pewne ograniczenia, a także raczej mizernie zrobioną obsługę błędów danych wejściowych. Nie mam aspiracji żeby to narzędzie kiedykolwiek upublicznić, a znając jego ograniczenia i ułomności nauczyliśmy się już używać go tak, żeby spełniało swoje zadanie - ale czasem załącza mi się w głowie mały, upierdliwy krasnalek i cieniutkim głosikiem się ze mnie nabija, że odwalam lipę, i że mógłbym w końcu zrobić ten projekt porządnie.
Z własnym perfekcjonizmem walkę wygrałem już dawno. Ale że wciąż lubię dłubać, postawiłem sobie wyzwanie: zrobić arkusz do domowego budżetowania, na samych li tylko formułach Excela. Żadnego PowerQuery czy VBA. "Goły" Excel. Powalczyłem ze dwa tygodnie (w słabo dostępnych slotach czasowych, jako że wokół przydarza się Życie) - i udało mi się w końcu osiągnąć coś w rodzaju Zen.
Tyle tytułem wstępu. Celem tego wpisu nie jest bowiem prezentacja owego gotowego produktu, ale pokazanie kilku nowych excelowych sztuczek, których nauczyłem się po drodze. To znaczy tak: nowych jak nowych, sporo tych formuł znanych jest już od 2018 roku. Ale ponieważ ja się zafiksowałem na jakiejś starszej wersji Excela, dla mnie to są absolutne nowości. Arkuszowi wyjadacze będą się pewnie uśmiechać pod wąsem - no i fajnie. Śmiech to zdrowie.
Zaczynamy!
1 SEQUENCE
Funkcja SEQUENCE umożliwia wygenerowanie listy wartości numerycznych:
Przyjmuje ona cztery parametry wejściowe: liczba wierszy, liczba kolumn, wartość początkowa, oraz wielkość, o którą zwiększamy przy każdym kroku. Powyższy przykład pokazuje listę dziesięciu wierszy, pojedyncza kolumna, z wartościami zaczynającymi się od jedynki i zwiększającymi się o jeden.
A tutaj przykład jak wygenerować 20 dat, począwszy od dzisiejszej, co tydzień:
W tym drugim przykładzie funkcja TODAY() zwraca tak naprawdę liczbę całkowitą odpowiadającą dzisiejszej dacie, trzeba ją "upiększyć" do postaci faktycznej daty za pomocą formatowania.
Jeżeli w którymkolwiek z powyższych przykładów klikniemy w komórkę A2, zobaczymy:
Wszystko identycznie, ale formuła w pasku na górze jest wyświetlona na szaro. Jest tak, ponieważ SEQUENCE jest formułą zwracającą wiele wartości, które zostają "rozlane" (z ang. spilling) poniżej (lub w prawo, jeżeli zażyczymy sobie wielu kolumn). Oznacza to, że w komórce A2 nie ma tak naprawdę żadnej formuły, a widoczna tam liczba (lub data) są efektem spilling z komórki powyżej.
Dodatkowo jeżeli spróbujemy w taką "zalaną" komórkę coś wpisać, Excel natychmiast powie nam, że jest problem:
Dlatego planując arkusz z tego typu formułami (tj. używającymi spilling) musimy zawsze solidnie planować co i gdzie wpisujemy.
Aha, oczywiście nie musimy wpisywać wartości parametrów "z ręki", możemy odwoływać się do innych komórek:
Zmieniając wartość w komórce A1 kontrolujemy liczbę wierszy w kolumnie B.
2 (V/H)STACK
VSTACK i HSTACK umożliwiają "sklejanie" ze sobą tabelek z danymi. Jedna w pionie, druga w poziomie.
Tutaj mamy dwie listy wartości (w kolumnie A wygenerowana formułą =SEQUENCE(5, 1, 1, 1), w kolumnie B formułą =SEQUENCE(5, 1, 100, 1)). Formuła VSTACK w kolumnie C "skleja" te dwie sekwencje w pionie.
Przy okazji zauważamy, że zamiast A1, B1, używamy zapisu A1#, B1#. Dodanie kratki za adresem komórki oznacza: "wszystkie komórki wygenerowane przez formułę w tej komórce". Innymi słowy, zamiast pisać np. A1:A5 i zmieniać zakres za każdym razem kiedy formuła w kolumnie A "rozleje" się na więcej (lub mniej) komórek, po prostu wpisujemy A1#. Poniższy przykład pokazuje, że to faktycznie działa:
HSTACK działa identycznie, tylko w drugi poprzek:
Ważne jest, żeby liczba kolumn (VSTACK) lub wierszy (HSTACK) się zgadzała, inaczej dostaniemy błąd:
Zauważamy jednak, że błąd ten jest dość "liberalny". Nie powoduje on, że nie zostaną zwrócone *żadne* dane - tylko dane z brakujących komórek.
Oczywiście - nic nowego - formuły można zagnieżdżać:
3 FILTER
Funkcja FILTER pozwala odfiltrować komórki spełniające zadane kryteria.
Tak filtrujemy z zadanej listy tylko liczby parzyste
A tak możemy odfiltrować tylko komórki zawierające tekst na literę "A":
4 SORT
Zgodnie z przypuszczeniami, funkcja SORT sortuje listę wartości.
Można też malejąco:
Według wybranej kolumny:
Według wielu kolumn:
W ostatnim przykładzie {2,1} oznacza sortuj według drugiej kolumny, a jeżeli wartości się powtarzają, to w tej powtórzonej grupie sortuj według pierwszej kolumny. Natomiast {-1,1} oznacza że sortowanie według pierwszej kolumny jest malejąco, a drugiej - rosnąco.
5 LAMBDA
LAMBDA w Excelu działa - z logicznego punktu widzenia - identycznie jak w Pythonie. A więc mamy możliwość stworzenia nienazwanej funkcji, z parametrami oraz przez nas zdefiniowaną logiką.
Jeżeli bardziej techniczny Czytelnik zastanawia się teraz, po kiego grzmota w Excelu taka Lambda, to spieszę wyjaśnić, że sama w sobie to właściwie zbędna.
Sama LAMBDA zwróci zawsze błąd - trzeba ją jeszcze nakarmić jakimś parametrem. W przypadku powyżej funkcja przelicza z Fahrenheitów na Celsjusze. Żeby zadziałało, trzeba zrobić tak:
Przeliczyliśmy sobie właśnie 83F na 28.33C. Trochę bez sensu, bo zamiast tego moglibyśmy po prostu wpisać tę formułę bezpośrednio w Excela, bez żadnej głupiej lambdy.
Z tym, że jest tu ukryty kruczko-haczyko-piespogrzebany. Mianowicie możemy sobie LAMBDĘ wetknąć w Menedżera Nazw, ubrać ją w nazwę i od tej pory używać w całym arkuszu identycznie jak każdej innej funkcji:
Jak widać tutaj już zaczyna się robić ciekawie - możemy mieć arkusz z własnymi, niestandardowymi funkcjami. Całkiem jak z VBA, ale bez konieczności podpisywania cyfrowego czy dodatkowych głupich okienek przy otwieraniu dokumentu (innymi słowy wszystko w ramach pliku xlsx, bez konieczności zapisywania jako xlsm). Oczywiście możliwości są nieporównywalnie mniejsze niż w przypadku "porządnych" języków skryptowych typu VBA (nie odczytamy sobie pliku, nie otworzymy zewnętrznej strony, nie poczekamy na naciśnięcie klawisza itd), ale i tak jest ciekawie.
6 LET
LET to prawdziwy kombajn, ze składnią trochę kojarzącą się z PowerQuery:
W ogólnym przypadku, LET przyjmuje na wejściu pary (zmienna,wyrażenie), z wyjątkiem ostatniego parametru, który jest zwracany jako wynik.
Wyrażeniem może być cokolwiek zwracającego wartość, dzięki czemu można budować całkiem zaawansowane "programy":
LET można zagnieżdżać:
Podane tu przykłady są dość bzdurne, ale dobrze pokazują elastyczność i możliwości operatora LET.
Na koniec jeszcze jeden przykład, nieco mniej bzdurny: dynamiczna tabliczka mnożenia:
Ciekawostką jest tutaj ostatnia linijka: x*y. Ponieważ x oraz y to są listy liczb (jedna w pionie, druga w poziomie), Excel uprzejmie wykonuje tutaj mnożenie pierwszego elementu x przez pierwszy element y i umieszcza wynik w komórce (1,1), po czym powtarza to dla pozostałych kombinacji elementów. A więc nie robi mnożenia macierzy (to możemy uzyskać za pomocą MMULT), tylko zwykłe mnożenie pojedynczych liczb.
Tych sześć (a w sumie siedem bo HSTACK i VSTACK można liczyć x2) funkcji to tylko wierzchołek lodowej góry funkcji Excela operujących na całych zakresach danych i "rozlewających" wyniki na wiele komórek. Inne warte wspomnienia funkcje, których nie omówiłem: UNIQUE, SORTBY, CHOOSECOLS / CHOOSEROWS, DROP / TAKE, WRAPCOLS / WRAPROWS, TEXTSPLIT / TEXTJOIN, SCAN, REDUCE, BYROW / BYCOL, MAKEARRAY, TOCOL / TOROW.
Od strony wydajności wspólną cechą tego typu funkcji jest to, że wewnętrznie wektoryzują one obliczenia gdzie się da, dzięki czemu wypluwają wyniki dużo szybciej (i niejako w pojedynczej operacji) niż ich "tradycyjne" odpowiedniki. Może przy małej porcji danych nie widać wielkiej różnicy, ale czasem arkusze rosną ponad miarę i wtedy wyraźnie opłaca się sięgnąć po nowsze formuły.
Albo rzucić wszystko w cholerę i wyjechać w Bieszczady przejść na SQL. Ale to już całkiem inna bajka.
Nie powiem, ciekawe… po pracy sprawdzę w Google Spreadsheets. A
nóżnuż się przyda?