Przepis na

Porównywanie zawartości dwóch list w LibreOffice Calc

• Autor: Mirosław Zalewski
• 29 października 2013
• komentarzy 9

Porównanie dwóch list jest elementem wielu sekwencyjnych działań, regularnie wykonywanych przez ludzi związanych z finansami, rachunkowością, logistyką czy analizą danych. W artykule przyjrzymy się dwóm sposobom na zidentyfikowanie tych pozycji z jednego wykazu, których nie ma na drugim. Dużo uwagi poświęcimy również przedstawieniu wyniku w formie umożliwiającej dalsze jego przetwarzanie.

Wstęp

Omawianemu zagadnieniu przyjrzymy się na przykładzie listy połączeń telefonicznych. W kolumnie A zamieszczone zostały numery, z którymi nawiązywano połączenia wychodzące. W kolumnie B znajduje się wykaz numerów, z których nawiązywano połączenia przychodzące.

Naszym celem jest zidentyfikowanie numerów telemarketerów — takich, z których połączenia były nawiązywano, ale na które nie dzwoniono. Innymi słowy, chcemy dowiedzieć się, które pozycje z kolumny B nie występują w kolumnie A.

Ponieważ listę tę chcemy przekazać do specjalnego programu w telefonie, który automatycznie odrzuca połączenia z niechcianych numerów, będziemy dążyć do umieszczenia znalezionych numerów w dodatkowej kolumnie, jeden pod drugim.

Formatowanie warunkowe

Najprostszym sposobem realizacji postawionego celu jest wykorzystanie formatowania warunkowego.

W pierwszej kolejności musimy utworzyć nowy styl komórek, za pomocą którego wyróżnimy interesujące nas wartości. W tym celu otwieramy okno Style i formatowanie (klawisz F11), klikamy prawym przyciskiem myszy na dowolną pozycję i z menu wybieramy Nowy.... W nowym oknie wybieramy nazwę nowego stylu (np. „Nieobecne”) oraz jego właściwości (np. zmiana koloru tła).

Następnie zaznaczamy kolumnę, która może zawierać dodatkowe wartości i wybieramy z menu Format → Formatowanie warunkowe → Warunek....

Pojawi się nowe okno. Z listy po lewej wybieramy Formuła jest, zaś w polu po prawej wpisujemy:

NIE(LICZ.JEŻELI($A$2:$A$31;B2))

Następnie wybieramy z listy utworzony wcześniej styl i klikamy OK.

Zanim przejdziemy do wyników, przyjrzyjmy się bliżej temu, co zrobiliśmy.

Formatowanie warunkowe aplikuje komórce odpowiedni styl wtedy, gdy spełniony zostanie określony warunek.

W naszym przypadku warunkiem tym jest formuła. Mówiąc dokładniej — wynik formuły, przełożony na dwuwartościowy typ logiczny. Wynik wynoszący zero jest traktowany jako FAŁSZ (niespełnienie warunku). Każdy inny wynik oznacza PRAWDA, a co za tym idzie — nadanie wybranego stylu.

Spójrzmy teraz na naszą formułę. Jej podstawą jest funkcja LICZ.JEŻELI, która zwraca liczbę komórek spełniających określony warunek (drugi argument) w podanym zakresie (pierwszy argument). Wpisanie adresu komórki jako warunku jest skróconą formą wyrażenia „równa się zawartości komórki o adresie…”. Innymi słowy, funkcja ta mówi nam, ile razy dana wartość z kolumny B występuje w kolumnie A.

Jednak w najbardziej interesującym nas przypadku, gdy pozycja w ogóle nie występuje w kolumnie A, funkcja ta zwróci zero. Jak pamiętamy, zero oznacza FAŁSZ, w związku z czym formatowanie nie zostanie zaaplikowane. Problem ten możemy obejść przy pomocy funkcji NIE, logicznej negacji (zamienia wartość logiczną argumentu na wartość przeciwną — PRAWDA na FAŁSZ, FAŁSZ na PRAWDA). Dzięki temu wynik całej naszej formuły jest zgodny z tym, czego oczekuje LibreOffice.

Formuły

Formatowanie warunkowe jest rozwiązaniem prostym i skutecznym, ale nie ułatwia nam wykonania drugiej części zadania (skopiowania wszystkich komórek spełniających kryteria). Ponadto słabo sprawdza się w przypadku dużych zbiorów danych — odnalezienie kilku wyróżnionych komórek wśród kilku tysięcy wierszy jest bardzo pracochłonne i podatne na pomyłki. Zobaczmy więc, jak ten sam problem rozwiązać przy pomocy formuł.

Podstawowe wyrażenie wygląda następująco; wprowadzamy je do jednej z wolnych komórek w arkuszu:

=JEŻELI(CZY.BRAK(WYSZUKAJ.PIONOWO(B2;$A$2:$A$31;1;0));B2;"")

Tym razem głównym elementem jest funkcja WYSZUKAJ.PIONOWO. Sprawdza ona, czy wartość podana jako pierwszy argument jest obecna w zakresie podanym jako drugi argument. Jeżeli tak, zwraca tę wartość (albo wartość z tego samego wiersza, z kolumny po prawej stronie). Jeżeli nie, zwraca błąd „N/A” (ang. not available, „niedostępny”).

Pojawienie się tego błędu sprawdzamy przy pomocy funkcji CZY.BRAK. Wykonuje ona działanie określone jako swój argument i zwraca PRAWDA (gdy błąd wystąpi) lub FAŁSZ (w pozostałych sytuacjach).

Wynik tej funkcji przekazujemy jako pierwszy argument funkcji JEŻELI. Na jego podstawie zwróci ona swój drugi (gdy wynikiem będzie PRAWDA) lub trzeci argument (w pozostałych przypadkach).

Podsumowując, powyższa formuła sprawdza, czy wartość z komórki B2 występuje w kolumnie A. Jeżeli nie, to wyświetla tę wartość. W przeciwnym wypadku wyświetla pusty ciąg znaków.

Formułę tę kopiujemy do pozostałych wierszy w kolumnie roboczej.

Analiza wyników

Szybko zauważymy, że wykorzystanie formuł wcale nie usunęło ograniczeń, na które natknęliśmy się podczas używania formatowania warunkowego. Nadal nie dysponujemy prostym i skutecznym sposobem na odnalezienie tych wartości z kolumny B, które nie występują w kolumnie A. Chociaż nasza nowa kolumna wyświetla jedynie dane spełniające to kryterium, nie możemy się pomiędzy nimi poruszać przy pomocy Ctrl + ↓.

Jednak formuły, w przeciwieństwie do formatowania warunkowego, zapewniają nam zaplecze niezbędne do rozwiązania tego problemu. Musimy jedynie wykonać jeszcze jeden krok.

Poniżej przedstawiam trzy spośród kilku możliwości. Każda z nich ma swoje wady i zalety, jednak wszystkie prowadzą do osiągnięcia naszego podstawowego celu. Wystarczy więc wykorzystać jedną z nich.

Wyszukiwanie

Jeżeli chcemy jedynie zobaczyć wszystkie różnice pomiędzy listami, możemy do tego wykorzystać wyszukiwarkę.

W pierwszej kolejności zaznaczamy całą kolumnę roboczą, po czym wybieramy z menu Edycja → Znajdź i zamień....

Pojawi się nowe okno. W polu Szukaj wpisujemy . (kropkę). Ponadto w części Inne opcje zaznaczamy pola Tylko bieżące zaznaczenie i Wyrażenie regularne, a z listy Szukaj w wybieramy Wartości.

Po kliknięciu przycisku Znajdź, wskaźnik aktywnej komórki zostanie przeniesiony na następną pozycję.

Filtrowanie

Innym sposobem jest wykorzystanie filtrowania w celu ukrycia wszystkich pustych komórek w kolumnie roboczej. W ten sposób otrzymamy kompletny wykaz tych wartości z kolumny B, które nie występują w kolumnie A.

Po umieszczeniu kursora w dowolnej komórce w kolumnie roboczej wybieramy z menu Dane → Filtr → Autofiltr. Następnie klikamy na strzałkę w pierwszym wierszu naszej kolumny. Pojawi się okienko zawierające w centralnej części listę wszystkich wartości z danej kolumny. Przechodzimy na jej koniec, gdzie odznaczamy pole obok pustej wartości. Zatwierdzamy zmiany klikając OK.

Kopiowanie

Wreszcie możemy skopiować zawartość kolumny roboczej w inne miejsce arkusza. Jeżeli zrobimy to w odpowiedni sposób, otrzymamy wykaz wszystkich interesujących nas wartości z kolumny B w formie nadającej się do dalszego przetwarzania.

Aby to zrobić, zaznaczamy całą kolumnę roboczą po czym kopiujemy jej zawartość (Ctrl + C).

Następnie umieszczamy kursor gdzieś w pustej części arkusza i wklejamy specjalnie zawartość schowka (Edycja → Wklej specjalnie lub Ctrl + Shift + V).

Pojawi się nowe okno. W części Opcje zaznaczamy Pomiń puste komórki. W części Wybór odznaczamy Wklej wszystko oraz Formuły, a zaznaczamy wszystkie pozostałe pozycje. Klikamy OK.