Nietypowe zastosowanie LICZ.JEŻELI

Być może część z moich czytelników zna już troszkę funkcje z rodziny SUMA.JEŻELI. W tym poście chciałbym Wam pokazać ciekawe zastosowanie funkcji LICZ.JEŻELI w połączeniu z formatowaniem warunkowym.

LICZ.JEŻELI to fukcja, która zlicza elementy pasujące do podanego kryterium. Zacznijmy od składni tej funkcji:

LICZ.JEŻELI( gdzie_szukasz; czego_szukasz )

• gdzie_szukasz - zakres komórek, w których szukamy wg kryterium
• czego_szkasz - kryterium wyszukiwania, zapisane w postaci tekstowej lub jako odwołanie do komorki zawierającej kryterium

Kryteria wyszukiwania mogą zawierać operatory porównania, takie jak =, >, <, >=, <=. Mogą też zawierać wartości tekstowe, a w szczególności tekst z symbolami wieloznacznymi: *, ?. Przykładowe zastosowanie takich symboli to wyszukiwanie konkretnych słów w dłuższych tekstach. Spójrz na poniższy przykład zastosowania:

W dolnej tabeli przy użyciu LICZ.JEŻELI policzono zdania zawierające różne kryteria tekstowe. W pierwszych trzech kryteriach symbol * umieszczono na początku i końcu kryterium, co w efekcie szuka fragmentu "kot", "ala" i "pies" w całym zdaniu. Ostatni wiersz zawiera * tylko na początku, więc funkcja zliczy tylko zdania kończące się tekstem "ami".

W porządku, zliczanie to jedno. Co jednak jeśli chciałbyś wiedzieć które konkretnie wiersze zawierały kryterium? Jest na to rada. Zakres przeszukiwania nie musi przecież odwoływać się do całej tabeli, ale również do pojedynczej komórki. Można więc dodać dodatkową kolumnę w pierwszej tabeli, aby sprawdzić czy sąsiednia komórka zawiera kryterium:

Stąd już tylko krok do wiualnego podświetlenia wierszy spełniających kryterium. Wystarczy użyć tej samej formuły w formatowaniu warunkowym. Wynik 1 automatycznie zostanie potraktowany jako logiczne PRAWDA, a 0 jako FAŁSZ - a te wartości są potrzebne do formatowania warunkowego. Wystarczy zaznaczyć cały przeszukiwany zakres i dodać nową regułę formatowania warunkowego:

Efekt będzie taki jak poniżej:

W tym momencie nawet nie potrzebujemy w tabeli kolumny B, aby szybko zlokalizować rekordy pasujące do kryterium.

Szukajcie, a znajdziecie

"Jeśli się chce coś znaleźć, trzeba po prostu szukać (...) Rzeczywiście, kto szuka, ten najczęściej coś znajduje, niestety czasem zgoła nie to, czego mu potrzeba."

R. R. Tolkien, Hobbit

Ostatnio zakończyliśmy nasze eksperymenty na etapie sprawdzania poprawności danych. Zastosowaliśmy kolumnę tabeli pomocniczej, jako źródła elementów listy do wyboru. W tym i kolejnych wpisach postaram się przybliżyć niektóre sposoby przeszukiwania tabel i łączenia danych w nich się znajdujących.

Inny wymiar

W poprzednim poście przybliżyłem Wam podstawy tworzenia tabel. Poznaliśmy użyteczny skrót klawiszowy CTRL+L, który tworzy tabelę z zakresu komórek. Przekonaliście się, jak kolumny obliczeniowe pomagają zachować porządek w formułach, a odwołania do tabel podnoszą ich czytelność.
Pora zaprząc tabele do czegoś bardziej pożytecznego. Dodajmy im dodatkowy wymiar, używając sprawdzania poprawności danych.

Na początku była tabela...

Wszystko zaczęło się od tabelek. Część z moich czytelników być może pamięta jeszcze wielkie płachty papieru, misternie wyrzeźbione liniami, na których przechowywano zapiski wszystkich operacji w firmach. W Polsce czasami nazywano je "szubienicami". Skojarzenie najwyraźniej od głównych linii ksiąg, układających się w kształt litery T, które rozdzielały strony Winien i Ma. Pod liniami "wisiały" kwoty transakcji, pracowicie wypełniane przez księgowe.
Do dziś lekko przymglony ślad tych zapisków możesz czytelniku zauważyć w niektórych raportach, wypluwanych przez współczesne programy księgowe. Widać to szczególnie wyraźnie przy eksportowaniu takich danych do Excela. Swoją drogą, ta aplikacja nie bez kozery jest dziś jedną z najczęściej używanych w świecie finansów. Wciąż jednak niewielu z nas jest świadomych rewolucji jaka dokonała się w samym sercu arkuszy kalkulacyjnych. Dzisiejszym postem chciałbym przykuć Twoją uwagę do specjalnego rodzaju tabel, który może ułatwić Ci pracę na różne sposoby.