Jak uzgodnić transakcje w arkuszu kalkulacyjnym (bez ręcznego dopasowywania wierszy)
Uzgadnianie dwóch list transakcji sprowadza się do znalezienia czterech rodzajów różnic. Brakujące wiersze po jednej stronie, zduplikowane wiersze po drugiej, niezgodne kwoty oraz ta sama płatność zaksięgowana w różnych okresach. Cała reszta to tylko księgowość wokół tych czterech kwestii.
Pierwszym odruchem jest zestawienie obu plików obok siebie i ręczne dopasowywanie wierszy. To sprawdza się przy około dwustu wierszach, ale powyżej tej liczby zamienia się w całe popołudnie pracy, której efektu końcowego nikt nie jest w stanie zweryfikować.
Ten poradnik wyjaśnia, dlaczego arkusze kalkulacyjne słabo radzą sobie z tym konkretnym zadaniem, opisuje trzy najpopularniejsze rozwiązania doraźne oraz wskazuje, gdzie każde z nich napotyka swoje ograniczenia. Jest to opis przepływu danych, a nie porada księgowa.
Dlaczego uzgadnianie danych w arkuszu kalkulacyjnym jest trudniejsze, niż się wydaje
Arkusz kalkulacyjny porównuje komórki. Uzgadnianie porównuje zdarzenia, a to samo zdarzenie rzadko wygląda identycznie po obu stronach.
Płatność kartą może pojawić się raz w księdze głównej i dwukrotnie w raporcie od operatora płatności, podzielona na kwotę obciążenia i prowizję. Faktura od dostawcy może mieć numer referencyjny INV-0042 w jednym systemie i INV42 w drugim. Przelew zlecony 31. dnia miesiąca może zostać rozliczony 1. dnia kolejnego, co zaburza rozliczenie całego miesiąca.
Żadna z tych sytuacji nie wynika z błędu w danych. To naturalny efekt rejestrowania tej samej rzeczywistości przez dwa różne systemy i żadna formuła wyszukiwania nie rozwiąże tego problemu samodzielnie.
Istnieje również pułapka zaokrągleń, w którą co miesiąc wpada wiele osób. Wartości walutowe zapisane z pełną precyzją zmiennoprzecinkową mogą różnić się na czwartym miejscu po przecinku, przez co dwie kwoty wyświetlane jako 1,204.50 nie przejdą testu dokładnej równości.
Skala danych również zmienia charakter problemu. Przy pięćdziesięciu wierszach człowiek jest w stanie ogarnąć wzrokiem obie listy. Przy pięciu tysiącach zadanie zamienia się w poszukiwanie igły w stogu siana – kilku wyjątków ukrytych w masie zgodnych danych. Ludzka uwaga nie jest do tego przystosowana.
Ile Cię to kosztuje
Przeciągające się zamknięcie miesiąca. Pierwsze dziewięćdziesiąt procent wierszy dopasowuje się w kilka minut. Pozostała garstka zajmuje godziny, ponieważ każdy przypadek wymaga interwencji człowieka, który musi ustalić, z którym z czterech rodzajów różnic ma do czynienia.
Brak możliwości audytu. Gdy dopasowywanie odbywa się „na oko”, ślad po wykonanej pracy znika w momencie zamknięcia pliku. Sześć tygodni później nikt nie jest w stanie odtworzyć, dlaczego dwa wiersze zostały uznane za tę samą płatność.
Niewykryte błędy. Duplikat dopasowany do niewłaściwego odpowiednika znosi się w sumie końcowej, dając złudzenie poprawnego uzgodnienia. Zgodność sum końcowych nie jest dowodem na to, że poszczególne wiersze są poprawne.
Te trzy koszty kumulują się. Przeciągająca się praca powoduje zmęczenie, zmęczenie prowadzi do chodzenia na skróty, a stąd już tylko krok do zatwierdzenia błędnego dopasowania jako prawidłowego.
Rozwiązania doraźne, po które sięgają użytkownicy
Opcja 1: Porównaj sumy przed dopasowaniem czegokolwiek
Zacznij od porównania sum grupowych zamiast pojedynczych wierszy. Użyj funkcji SUMIFS, aby zsumować dane po obu stronach według miesięcy, kont lub kontrahentów, a następnie zestaw te dwie kolumny obok siebie.
Pozwala to zlokalizować różnicę, zanim zaczniesz marnować na nią czas. Jeśli jedenaście z dwunastu miesięcy zgadza się co do grosza, do uzgodnienia pozostaje tylko jeden miesiąc, a no nie cały rok.
To naprawdę przydatna metoda, ale jej możliwości szybko się kończą. Sumy grupowe wskazują jedynie, gdzie leży rozbieżność, ale nigdy nie wskażą, które wiersze ją spowodowały. Ponadto dwa wzajemnie znoszące się błędy w tej samej grupie pozostaną niewidoczne.
Opcja 2: Stwórz klucz dopasowania i wyszukaj go
Połącz pola identyfikujące zdarzenie w jeden klucz – zazwyczaj jest to data, kwota oraz oczyszczony numer referencyjny. Następnie użyj funkcji XLOOKUP w obu kierunkach, aby znaleźć wiersze obecne po jednej stronie, a brakujące po drugiej.
Dodaj funkcję COUNTIFS dla tego samego klucza, aby wyłapać duplikaty, ponieważ funkcja wyszukiwania zwraca tylko pierwsze dopasowanie, ignorując kolejne. Przed utworzeniem klucza zaokrąglij kwoty za pomocą funkcji ROUND do dwóch miejsc po przecinku, co wyeliminuje wspomniany wcześniej problem z precyzją zmiennoprzecinkową.
To najpopularniejsza metoda, która sprawdza się w większości przypadków. Jej ograniczenie ma jednak charakter strukturalny: wymaga klucza, który oznacza dokładnie to samo po obu stronach. Różne formaty numerów referencyjnych lub prowizja rozbita na dwa wiersze natychmiast ją uniemożliwiają.
Opcja 3: Świadome podejście do czterech rodzajów różnic
Zamiast jednego ogólnego dopasowania, przeprowadź cztery bardziej szczegółowe testy. Brakujące wiersze wykryjesz za pomocą dwukierunkowego wyszukiwania. Duplikaty znajdziesz, licząc wystąpienia klucza. Niezgodności kwot wykryjesz, dopasowując dane wyłącznie po numerze referencyjnym, a następnie porównując wartości. Różnice w czasie zidentyfikujesz, dopasowując dane w określonym przedziale czasowym, a nie po dokładnej dacie.
Prawidłowo przeprowadzona, jest to najbardziej rzetelna metoda, ponieważ każdy niedopasowany wiersz trafia do konkretnej kategorii, zamiast lądować w jednym worku z napisem „inne”.
Wymaga ona jednak najwięcej pracy. Cztery etapy oznaczają cztery kolumny pomocnicze po każdej stronie, a całą strukturę trzeba budować od nowa, gdy tylko zmieni się kolejność kolumn w pliku eksportu. Nasz poradnik dotyczący czyszczenia i usuwania duplikatów z danych opisuje etap przygotowawczy, od którego zależy ta metoda.
Wspólne ograniczenie. Wszystkie trzy podejścia zakładają relację jeden do jednego między wierszami po obu stronach. Tymczasem jedno rozliczenie może obejmować czterdzieści transakcji. Jedna płatność może składać się z obciążenia, prowizji i zwrotu. W takich przypadkach dopasowywanie kluczy nie ma punktu zaczepienia. I właśnie na tym marnuje się całe popołudnie.
Jak uzgadniać transakcje za pomocą Powerdrill Bloom
Krok 1: Prześlij oba pliki
Prześlij jednocześnie wyciąg z księgi głównej oraz wyciąg od kontrahenta. Powerdrill Bloom przeanalizuje oba pliki, dzięki czemu niezgodne nazwy kolumn, różne formaty dat i niespójne style numerów referencyjnych będą widoczne jeszcze przed rozpoczęciem dopasowywania.
Krok 2: Opisz proces uzgadniania w języku naturalnym
Poproś o wskazanie czterech kategorii z nazwy. Zażądaj wierszy, które są obecne w jednym pliku, a brakuje ich w drugim, oraz zduplikowanych numerów referencyjnych. Następnie poproś o wskazanie niezgodności kwot przekraczających określony próg tolerancji oraz wpisów, których daty różnią się o kilka dni.
Na koniec zadaj pytanie, które pozwoli rozwiązać najtrudniejsze przypadki. Zapytaj, które grupy wierszy po jednej stronie sumują się do pojedynczego wiersza po drugiej stronie. To właśnie przypadki typu „wiele do jednego”, których tradycyjne dopasowywanie kluczy nie jest w stanie obsłużyć.
Krok 3: Wyeksportuj wykres, raport lub prezentację
Pobierz listę wyjątków, podsumowanie niedopasowanych wartości według kategorii lub krótką notatkę tekstową do dokumentacji zamknięcia okresu.
Dlaczego to rozwiązanie jest lepsze od budowania dopasowań na nowo co miesiąc
| Metoda ręczna | Powerdrill Bloom | |
|---|---|---|
| Różne formaty numerów referencyjnych | Najpierw ręczne oczyszczenie obu stron | Opisz różnicę i zadaj pytanie |
| Wiele wierszy do jednego wiersza | Ręczne grupowanie | Zapytaj, które wiersze sumują się do odpowiednika |
| Duplikaty | Dodatkowa kolumna zliczająca po każdej stronie | Uwzględnione na liście wyjątków |
| Kolejny miesiąc | Odtwarzanie wszystkich kolumn pomocniczych | Wystarczy przesłać nowe pliki eksportu |
Pierwszy wiersz tabeli to etap, na który schodzi najwięcej czasu. Oczyszczanie numerów referencyjnych, aby dopasować dane z dwóch systemów, to praca przygotowawcza, która sama w sobie nie przynosi żadnego rezultatu. Ponadto trzeba ją powtarzać za każdym razem, gdy zmieni się format eksportu.
Najczęstsze błędy
Uznawanie zgodności sumy końcowej za zakończone uzgodnienie. Dwa błędy o tej samej wartości, ale o przeciwnych znakach, dadzą idealną sumę końcową. Zawsze sprawdzaj liczbę wierszy i niedopasowane wartości, a nie tylko samą sumę.
Dopasowywanie wyłącznie po kwocie. W każdej rzeczywistej księdze wiele transakcji ma tę samą wartość. Wyszukiwanie po samej kwocie połączy błędne wiersze i przedstawi to jako pewny wynik.
Ignorowanie różnic w zaokrągleniach. Wartości, które wyglądają identycznie na ekranie, mogą nie przejść testu równości. Przed porównaniem zaokrąglij dane po obu stronach do tej samej precyzji.
Zapominanie o kierunku weryfikacji. Wyszukiwanie jednokierunkowe znajdzie wiersze, których brakuje w drugim pliku, ale nigdy nie wykaże wierszy brakujących w pierwszym. Zawsze przeprowadzaj wyszukiwanie w obu kierunkach.
Usuwanie dopasowanych wierszy na bieżąco. Może się to wydawać wygodne, ale niszczy ścieżkę audytu. Zamiast tego oznaczaj wiersze w kolumnie statusu i zachowaj oryginalne dane w nienaruszonym stanie.
Uzgadnianie przed zamknięciem okresu. Późno zaksięgowane wpisy powodują różnice w czasie, które z czasem same się rozwiązują. Próby ich wyjaśniania w trakcie trwania okresu to strata czasu.
Podsumowanie
Uzgadnianie to problem klasyfikacji, a nie dopasowywania. Przypisz każdy niedopasowany wiersz do jednej z kategorii: brakujący, zduplikowany, błędna kwota lub błędny okres, a pozostała praca będzie minimalna i łatwa do wyjaśnienia.
Kosztownym elementem tego procesu jest budowanie całego systemu od nowa co miesiąc, zwłaszcza gdy numery referencyjne się nie zgadzają lub jedna płatność odpowiada kilku wierszom. Jeśli na to właśnie marnujesz czas przy zamknięciu miesiąca, wypróbuj Powerdrill Bloom dla obu plików eksportu. Zobacz również nasze poradniki dotyczące łączenia dwóch plików Excel bez użycia VLOOKUP oraz tworzenia raportu budżetu i jego wykonania. Strony poświęcone raportom z wydatków oraz analizie przepływów pieniężnych opisują powiązane procesy.
Najczęściej zadawane pytania
Co oznacza uzgadnianie transakcji?
Oznacza to potwierdzenie, że dwa zapisy tej samej operacji są zgodne, oraz wyjaśnienie każdej pozostałej różnicy. Wyjaśnienia te dzielą się na cztery grupy: brakujące wiersze, duplikaty, niezgodności kwot oraz różnice w czasie.
Czy Excel może automatycznie uzgodnić dwie listy?
Nie robi tego sam z siebie. Excel dostarcza narzędzi – głównie funkcji wyszukiwania, zliczania i sumowania warunkowego – ale logikę dopasowywania musisz stworzyć samodzielnie i budować ją od nowa przy każdej zmianie w plikach eksportu.
Dlaczego dwie kwoty, które wyglądają tak samo, nie pasują do siebie?
Zazwyczaj wynika to z precyzji zapisu danych. Wartość wyświetlana z dwoma miejscami po przecinku może mieć ich więcej w pamięci systemu, przez co dokładne porównanie kończy się niepowodzeniem. Rozwiązaniem jest zaokrąglenie obu stron do tej samej precyzji.
Jak poradzić sobie z jedną płatnością, która pojawia się w kilku wierszach?
Zgrupuj mniejsze wiersze i porównaj sumę grupy z pojedynczym odpowiednikiem. Dopasowywanie kluczy na poziomie pojedynczych wierszy nie obsługuje takich przypadków, dlatego jest to najczęstsze źródło pracy ręcznej.
Czy niedopasowane wiersze powinny być usuwane?
Nie. Zachowaj je i dodaj kolumnę statusu, w której zapiszesz kategorię i przyczynę rozbieżności. Usuwanie danych niszczy ścieżkę audytu, która pozwala później obronić poprawność uzgodnienia.