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

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.

Okno programu Calc. W kolumnie A znajduje się lista numerów, z którymi nawiązywano połączenia. W kolumnie B znajduje się lista numerów, na które nawiązywano połączenia.

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).

Przeczytaj moje wprowadzenie do stylów i zacznij korzystać z tej niezwykle użytecznej funkcji pakietu.

Następnie zaznaczamy kolumnę, która może zawierać dodatkowe wartości i wybieramy z menu FormatFormatowanie warunkoweWarunek....

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.

Okno tworzenia reguł formatowania warunkowego. Z listy wybrano „Formuła jest”, zaś w polu wpisano „NIE(LICZ.JEŻELI($A$2:$A$31;B2))”.

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.

Okno programu Calc. Niektóre komórki w kolumnie B mają odmienne tło. Komórki te zawierają wartości, które nie pojawiają się w kolumnie A.

Jeżeli chcesz dowiedzieć się więcej o formatowaniu warunkowym, koniecznie przeczytaj dedykowany mu artykuł.

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”).

Funkcja WYSZUKAJ.PIONOWO ma o wiele większe możliwości, które opisywałem w artykułach „Minidziennik ucznia w LibreOffice Calc” oraz „Jak powstał arkusz »zarobki.ods«?”.

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.

Okno programu Calc. W kolumnie C znajdują się komórki z listy B, których nie ma na liście A.

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 EdycjaZnajdź i zamień....

Pojawi się nowe okno. W polu Szukaj wpisujemy . (kropkę). Ponadto w części Inne opcje zaznaczamy pola Tylko bieżące zaznaczenieWyraż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ę.

Okno znajdowania i zamieniania. W polu „Szukaj” wstawiono kropkę. W części „Inne opcje” zaznaczono „Tylko bieżące zaznaczenie”, „Wyrażenie regularne” oraz z listy „Szukaj w” wybrano „Wartości”.

Dowiedz się wszystkiego o wyrażeniach regularnych czytając wprowadzenie w to zagadnienie oraz opis przykładowych wyrażeń.

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 DaneFiltrAutofiltr. 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 (EdycjaWklej 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.

Okno konfiguracji wklejania specjalnego. W części „Opcje” zaznaczono „Pomiń puste komórki”. W części „Wybór” odznaczono „Wklej wszystko” oraz „Formuły”, a zaznaczono wszystkie pozostałe pozycje.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

komentarzy 9

  • Marek napisał(a):

    W tekście omawiając wyszukiwanie rekordów przy pomocy funkcji WYSZUKAJ.PIONOWO posługujesz się funkcją CZY.BRAK a w przykładach na zdjęciach funkcja CZY.BŁĄD. Coś chyba trzeba by ujednolicić. Poza tym artykuł (jak inne) super. Akurat dzisiaj się przydał.

    • Mirosław Zalewski napisał(a):

      Racja. Zaktualizowałem grafiki, aby pokazywały to, co jest opisane w artykule.

      W omawianym przypadku obie te funkcje zapewniają takie same efekty, więc można je stosować zamiennie. CZY.BRAK zwraca prawdę, jeżeli argument zwróci błąd „N/A”; CZY.BŁĄD zwraca prawdę, jeżeli argument zwróci jakikolwiek błąd (pomyłka w składni, zbyt dużo elementów formuły, nieistniejąca funkcja itd., ale także „N/A”).

      Dziękuję za komentarz.

  • Piotr Gabriel napisał(a):

    Dobry artykuł. Zajmuję się pozycjonowaniem stron internetowych i czegoś takiego szukałem. W nagłówku jest napisane, że to narzędzie przydatne dla ludzi związanych z finansami, rachunkowością, logistyką czy analizą danych, można więc spokojnie dopisać, że w SEO również się bardzo przydaje 🙂

  • kris napisał(a):

    czy wymienione funkcje dzialaja tylko na liczbach ?? bo probowalem na numerach seryjnych gdzie sa litery i mi nie smiga

    • Marek napisał(a):

      Z tego co pamiętam to działają. Porównywałem numery projektów (cyfry, litery, kropki itp.) i przynajmniej WYSZUKAJ.PIONOWO działa. Spróbuj przećwiczyć funkcje na odrębnym uproszczonym zestawie danych (odrębny plik). Może popełniasz jakiś błąd, który znajdziesz na prostszym zestawie danych (u mnie taka metoda dochodzenia do wyniku z reguły działa – „nic na skróty” :))

  • Bogumił napisał(a):

    Witam. Mam taki problem, próbuję rozszerzyć tego typu funkcje na taką:
    mamy 4 kolumny: A, B, C, D.
    A – nazwa produktu
    B – cena produktu A
    C – nazwa produktu
    D – cena produktu C

    Kolumny A i C nie są jednakowe, ale występuje w nich bardzo dużo tych samych produktów.
    I co chcę osiągnąć:
    Nazwę produktu z pola A1 szuka w kolumnie C – jeżeli znajdzie to w polu E1 wyświetla cenę znalezionego produktu C (z kolumny D)

    • Bogdan napisał(a):

      W podobnych sytuacjach stosuję 2 rzeczy.
      1. kolumny C i D umieszczam kolejnym arkuszu aby mieć bardziej klarowny obraz.
      2. w komórce C2 w tym wypadku umieszczam funkcję Wyszukaj pionowo z parametrami:
      – kryterium wyszukiwania – zaznaczasz obszar nazw,
      – macierz zaznaczasz obszar z nazwami i cenami w sąsiedniej kolumnie,
      – Index przyjmujesz 2 bo chcesz uwidocznić dane z 2-giej kolumny,
      – porządek sortowania 0.
      Otrzymujemy taki zapis funkcji:
      =WYSZUKAJ.PIONOWO(A2:A11;Arkusz2.A2:B9;2;0)
      I tu ważna uwaga tworzymy funkcje dla jednej komórki ale jeśli chcemy aby w sposób logiczny przekopiowała się na pozostałe pola kolumny i użyjemy funkcji „przeciągnij” lub „kopiuj i wklej” to funkcja zadziała wadliwie. Dlatego trzeba między znakami wiersza i kolumny umieścić znak $:
      =WYSZUKAJ.PIONOWO(A$2:A$11;Arkusz2.A$2:B$9;2;0)
      i wtedy wszystko działa OK.

  • Jakub napisał(a):

    Trochę inny problem.
    Mam dwa arkusze. W obu są kolumny imię , nazwisko. Chodzi o tych samych klientów.
    Pierwszy arkusz ma kolumny: imię, nazwisko, adres,
    Drugi arkusz ma kolumny: imię, nazwisko, e-mail, telefon.
    Potrzebuję te dane scalić na podstawie imienia i nazwiska.
    A żeby było trudniej to nie które imiona i nazwiska są takie same u różnych klientów.
    Proszę o pomoc.
    Pozdrawiam

Dodaj komentarz