Załóżmy, że mamy ponad 800 faktur do wprowadzenia do systemu. Oczywiście robimy to importem, a nie ręcznie. Na koniec tego importu system raportuje jakie faktury zaimportował i okazuje się, że 600. Czyli jakieś faktury nie zostały zaimportowane do systemu. 

Pytanie tylko: jakie? 

I jak to teraz sprawdzić? 

W tym artykule pokażę Ci trzy sposoby, aby to sprawdzić. 

Założenie jest takie, że dysponujemy listą wszystkich faktur – u mnie w danych znajdują się one w arkuszu Wszystkie, oraz listą faktur zaimportowanych do systemu – u mnie w danych są one w arkuszu W systemie. O tak: 

Formatka

Metoda 1: WYSZUKAJ.PIONOWO 

Zapewne pierwszą myślą, jaka przychodzi do głowy użytkownikom Excela w takiej sytuacji, jest funkcja WYSZUKAJ.PIONOWO. – i bardzo słusznie – porównywanie danych jest jednym z jej podstawowych zastosowań. 

Chodzi o to, aby w zestawieniu wszystkich faktur dorobić kolumnę, w której zanotujemy czy konkretna faktura występuje w zestawieniu faktur z systemu, czy nie. 

W tym celu do kolumny obok danych, u mnie będzie to komórka H4, wpiszmy następującą formułę: 

=WYSZUKAJ.PIONOWO([@[Nr Faktury]];tbSystem[Nr Faktury];1;0) 

U mnie dane znajdują się w obiektach tabela, stąd występujące w formułach odwołania strukturalne np. [@[Nr Faktury]]. 

Jeśli nie mielibyśmy obiektów tabela, formuła wyglądałaby tak: 

=WYSZUKAJ.PIONOWO(Wszyskie!$C4;'W systemie'!$C$4:$C$650;1;0) 

A wynik funkcji wygląda tak: 

Wynik WYSZUKAJ.PIONOWO

Zatem wszędzie tam, gdzie funkcja zwróciła błąd #N/D mamy do czynienia z brakującą fakturą. Możemy je wyfiltrować: 

Wybieranie brakujących faktur

Efekt osiągnięty. 

Metoda 2: X.WYSZUKAJ 

Drugą i bardzo podobną metodą jest użycie funkcji X.WYSZUKAJ (nie każda wersja Excela ją posiada!). Robimy to na tej samej zasadzie co poprzednio, czyli tworzymy nową kolumnę w tabeli, zawierającą następującą funkcję: 

=X.WYSZUKAJ([@[Nr Faktury]];tbSystem[Nr Faktury];tbSystem[Nr Faktury];"Brak") 

Lub w wersji bez obiektu tabeli: 

=X.WYSZUKAJ(Wszyskie!$C4;'W systemie'!$C$4:$C$650;'W systemie'!$C$4:$C$650;"Brak") 

Efekt obu jest identyczny, o taki: 

Wynik X.WYSZUKAJ

Funkcja X.WYSZUKAJ ma wbudowaną możliwość wyświetlenia jakiegoś tekstu, np. Brak wszędzie tam, gdzie zwróci błąd #N/D, czyli mówiąc po ludzku: nie znajdzie faktury. Skorzystałam z tej opcji 😊. 

Metoda 3: Power Query – scalanie  

Ostatnia metoda to zastosowanie Power Query. Aby je użyć, najpierw musimy zaimportować do Power Query dane, a zatem obie tabele: ze wszystkimi fakturami (u mnie tabela nazywa się tbWszystkie) i fakturami z systemu (tbSystem). 

Aby to zrobić: 

1. Ustaw się w tabeli, którą chcesz zaimportować do Power Query (tbWszystkie) 
2. Menu Dane → Z tabeli/zakresu 

Otworzy się Power Query z nowo utworzonym zapytaniem o nazwie takiej, jak importowana tabela. Nazwę tę możesz oczywiście zmienić w okienku Ustawienia zapytania po prawej stronie. Ja zmieniłam na Wszystkie. Wynik jest tak: 

Dane zaimportowane do Power Query

Power Query automatycznie zastosował krok zmiany typu danych, który śmiało możesz teraz usunąć. Wystarczy nam bowiem tylko kolumna z numerem faktury, a resztę możemy usunąć. 

W tym celu kliknij na kolumnę Nr Faktury prawym przyciskiem myszy i z menu kontekstowego wybierz Usuń inne kolumny: 

Usuwanie innych kolumn w Power Query

Teraz możemy zmienić typ danych pozostawionej kolumny na Tekstowy: kliknij ikonkę ABD123 w nagłówku tej kolumny i wybierz Tekst. 

Zapytanie gotowe: 

Wynik zapytania Wszystkie

Dokładnie to samo trzeba zrobić teraz z drugą tabelą w Excelu – tbSystem. Efekt będzie taki: 

Wynik zapytania System

Kiedy już mamy obie tabele zaimportowane do Power Query, możemy zabrać się za ich scalenie, czyli połączenie w taki sposób, aby w wyniku otrzymać tylko faktury, których nie ma w zestawieniu System. 

Aby to zrobić, wejdź do zapytania ze wszystkimi fakturami (u mnie nazywa się ono Wszystkie). 

Następnie z menu Narzędzia główne wybierz Scal zapytania → Scal zapytania jako nowe: 

Scalanie zapytań jako nowe w Power Query

Utworzysz w ten sposób nowe zapytanie, będą wynikiem scalenia dwóch zapytań. Tylko jeszcze trzeba powiedzieć których dwóch. Robimy to w okienku scalania. 

1. Z drugiej listy rozwijanej wskaż zapytanie, które ma zostać scalone z zapytaniem, od którego rozpocząłeś scalanie. U nas jest to System 
2. Na podglądzie obu zapytań wskaż kolumny, po których ma nastąpić scalanie (w obu przypadkach jest to kolumna Nr Faktury) 
3. Następnie wybierz rodzaj sprzężenia: Lewe anty 
4. Zatwierdź OK: 

Okienko scalania w Power Query

Efektem będzie nowe zapytanie o nazwie Scalanie1 (polecam zmienić jego nazwę na np. Brakujące): 

Wynik scalania do dalszych zmian

Obecnie w kolumnie Nr Faktury znajdują się już tylko brakujące faktury, czyli dokładnie to, o co nam chodziło. Teraz wystarczy tylko usunąć nowopowstałą kolumnę System (nic nie wnosi – będzie ona zawsze pusta) i można ładować dane do Excela. Oto wynik Brakujące już po załadowaniu do Excela: 

Wynik zapytania scalającego

Metoda 4: Funkcja LICZ.JEŻELI 

Kolejną metodą na rozwiązanie tego problemu jest funkcja LICZ.JEŻELI. Przyda nam się tutaj, aby policzyć, ile razy dana faktura na liście wszystkich faktur wystąpiła na liście faktur zaimportowanych do systemu. Jeśli wynik wyjdzie równy zero, oznacza to, że mamy do czynienia z fakturą, która nie została zaimportowana. Zatem będziemy wypatrywać zer. 

A sama formuła wygląda następująco: 

=LICZ.JEŻELI(tbSystem[Nr Faktury];[@[Nr Faktury]]) 

(ang: COUNTIF) 

Lub na zakresach danych: 

=LICZ.JEŻELI('W systemie'!D4:D650;C4) 

Efekt będzie taki: 

Wynik dla LICZ.JEŻELI

Metoda 5: Funkcje STOS.PION i UNIKATOWE 

Najlepsze na koniec: funkcje tablicowe dostępne w nowszych wersjach Excela. Rozwiązanie, którego autorem jest Bill Szysz – tutaj możesz go znaleźć, który pozwolił mi się podzielić z Tobą tą absolutnie GENIALNĄ metodą. 

A zatem: rozwiązanie polega na wybraniu wartości, które występują dokładnie raz na połączonej liście obu zakresów. 

Czyli połączymy oba zakresy – zarówno ten zawierający wszystkie faktury, jak i ten z fakturami zaimportowanymi do systemu. 

Następnie z tego zakresu wybierzemy numery faktur, które występują tam dokładnie raz. Czyli, w praktyce – są na liście wszystkich faktur, ale nie ma ich na liście faktur w systemie. 

W końcu faktury, które udało się zaimportować do systemu, z pewnością są na liście wszystkich faktur. 

Poniższą formułą połączymy oba zakresy w jedną listę: 

= STOS.PION(tbWszystkie[Nr Faktury];tbSystem[Nr Faktury]) 

(STOSPION = VSTACK, UNIKATOWE = UNIQUE) 

W wyniku otrzymamy 1500+ wierszy: 

Wynik funkcji STOS.PION

To oczywiście zbyt dużo, bo teraz niektóre faktury występują na tej liście dwukrotnie. Ale to jeszcze nie koniec.  

Wrzucimy bowiem tę funkcję do funkcji UNIKATOWE, która wybierze z połączonej listy faktury, występujące na niej dokładnie raz. O tym mówi ostatni argument funkcji równy 1 (lub PRAWDA, obojętnie). O tak: 

=UNIKATOWE(STOS.PION(tbWszystkie[Nr Faktury];tbSystem[Nr Faktury]);;1) 

Efektem będzie dynamiczna tablica (lista) zawierająca szukane 200+ faktur, które nie zaimportowały się do systemu: 

Wynik dla funkcji tablicowych NEW

Dla mnie metoda tip-top! Dodam jeszcze, że dla dużych (np. kilkadziesiąt tysięcy rekordów) zestawów danych działa nieporównywalnie szybciej niż wszystkie wyżej wymienione funkcje arkuszowe. Polecam bardzo 👍. 

Co kto woli, co kto lubi, co dla kogo jest odpowiedniejsze 👍. 

A Ty którą metodę wolisz? 

Mam nadzieję, że pomogłam 😊 

Plik do pobrania:

https://malinowyexcel.pl/wp-content/uploads/2024/09/20240917-MalinowyExcel-Brakujace-faktury-DW.xlsx

I filmy na Youtubie, gdzie pokazuję powyższe rozwiązania: