
WYSZUKAJ.PIONOWO: jak dopasować przelewy z wyciągu do listy uczestników
Przelewy z wyciągu dopasujesz do listy uczestników po numerze rezerwacji w tytule przelewu. Najpierw formuła wycina numer z tytułu (FRAGMENT.TEKSTU i SZUKAJ.TEKST), potem WYSZUKAJ.PIONOWO z argumentem FAŁSZ albo X.WYSZUKAJ znajduje płatnika na liście. Przelewy bez numeru albo z numerem spoza listy odkładasz do wyjaśnienia, zanim wpiszesz je jako wpłaty.
W tym tekście
- W skrócie
- Jak działa wyszukaj pionowo w Excelu?
- Jak wkleić wyciąg z banku do Excela?
- Jak wyciągnąć numer rezerwacji z tytułu przelewu?
- Jak znaleźć płatnika po numerze rezerwacji?
- X.WYSZUKAJ: prostsza wersja tej samej formuły
- Które przelewy wymagają wyjaśnienia?
- Jak przenieść dopasowane przelewy do arkusza Wpłaty?
- Dlaczego wyszukaj pionowo zwraca #N/D?
- Jaki numer rezerwacji najłatwiej znaleźć w tytule przelewu?
- Pytania
- Źródła
Plik przykładowy: wyciąg i lista uczestników
Arkusz wyjazdu z listą 13 fikcyjnych uczestników i arkuszem Wyciąg z 9 przelewami: numer z tytułu, płatnik, status i kontrola, czy wpłata jest już we Wpłatach. Wpisujesz tylko arkusz Wyjazd, kolumny A-H listy, A-E wpłat i A-D wyciągu, reszta liczy się sama. Bez zapisu i bez podawania e-maila.
W skrócie
- Klucz: numer rezerwacji w stałym zapisie, ten sam w liście i w tytule przelewu.
- Numer z tytułu: PODSTAW usuwa spacje i kreski, FRAGMENT.TEKSTU i SZUKAJ.TEKST wycinają numer.
- Płatnik: WYSZUKAJ.PIONOWO z FAŁSZ albo X.WYSZUKAJ, błąd zamieniony na brak na liście.
- Status: dopasowana, do wyjaśnienia albo obciążenie do pominięcia.
- Wpłaty: do arkusza wpłat przenosisz tylko dopasowane przelewy, sprawdzając, czy już tam nie są.
Lista, wpłaty i wyciąg leżą w jednym arkuszu wyjazdu, który opisujemy w tekście lista uczestników wyjazdu w Excelu.
Jak działa wyszukaj pionowo w Excelu?
Funkcja WYSZUKAJ.PIONOWO szuka wartości w pierwszej kolumnie zakresu i zwraca wartość z tego samego wiersza, z kolumny o podanym numerze. Ma składnię WYSZUKAJ.PIONOWO(szukana_wartość; tabela_tablica; nr_kolumny; [przeszukiwany_zakres]), a argument FAŁSZ oznacza dokładne dopasowanie (Microsoft). Przy przelewach szukaną wartością jest numer rezerwacji, a wynikiem płatnik z listy:
| Argument | Co oznacza | W pliku przykładowym |
|---|---|---|
| szukana_wartość | Czego szukasz | F2, numer rezerwacji wycięty z tytułu |
| tabela_tablica | Zakres, w którego pierwszej kolumnie Excel szuka | Lista!$A$2:$F$41, od numeru rezerwacji do płatnika |
| nr_kolumny | Z której kolumny zakresu wziąć wartość, licząc od 1 | 6, czyli Płatnik |
| przeszukiwany_zakres | PRAWDA albo pominięty to przybliżone dopasowanie, FAŁSZ to dokładne | FAŁSZ, bo numer musi się zgadzać co do znaku |
Gdy numeru nie ma w pierwszej kolumnie zakresu, funkcja zwraca błąd #N/D. W pliku przykładowym ten błąd zamienia na czytelny tekst JEŻELI.BŁĄD.
Jak wkleić wyciąg z banku do Excela?
Wyciąg wklejasz do arkusza Wyciąg w cztery kolumny: data, kwota, nadawca i tytuł przelewu. Jeśli bank pozwala pobrać historię rachunku jako CSV albo plik Excela, skopiuj z niej tylko te cztery kolumny i tylko wpływy z okresu, który sprawdzasz.
- Wklej dane od wiersza 2, pod nagłówkami.
- Sprawdź, czy daty są datami, a kwoty liczbami. Kwota z dopiskiem zł albo z apostrofem to tekst.
- Obciążenia rachunku, czyli kwoty ujemne, możesz zostawić. Formuła oznaczy je jako obciążenie do pominięcia.
- Kolumny E-I uzupełnią się same. Arkusz mieści 60 pozycji, a dłuższy wyciąg wymaga przeciągnięcia formuł niżej.
Jak wyciągnąć numer rezerwacji z tytułu przelewu?
Numer rezerwacji wyciągniesz z tytułu w dwóch krokach: najpierw ujednolicasz tytuł, potem wycinasz z niego numer o znanym początku i długości. Płatnicy piszą numer po swojemu: małymi literami, ze spacjami albo z kreskami. Kolumna E zamienia tytuł na wielkie litery i usuwa spacje, kreski i ukośniki funkcją PODSTAW:
=PODSTAW(PODSTAW(PODSTAW(LITERY.WIELKIE(D2);" ";"");"-";"");"/";"")
Kolumna F szuka w oczyszczonym tytule początku numeru ZK27 funkcją SZUKAJ.TEKST i wycina 7 znaków funkcją FRAGMENT.TEKSTU. Gdy numeru nie ma, zostawia komórkę pustą:
=JEŻELI.BŁĄD(FRAGMENT.TEKSTU(E2;SZUKAJ.TEKST("ZK27";E2);7);"")
W swoim pliku zmień ZK27 na stały początek Twoich numerów, a 7 na ich długość. Tak wyglądają wybrane tytuły z pliku przykładowego po każdym kroku. Wszystkie 9 pozycji jest w pliku:
| Tytuł przelewu | Tytuł bez spacji | Nr rezerwacji | Płatnik z listy | Status |
|---|---|---|---|---|
| zk27001 rata | ZK27001RATA | ZK27001 | Kowalska Ewa | dopasowana |
| ZK 27 012 Krawczyk Tymon | ZK27012KRAWCZYKTYMON | ZK27012 | Krawczyk Daria | dopasowana |
| Kamińska Lena obóz rata | KAMIŃSKALENAOBÓZRATA | puste | puste | do wyjaśnienia: brak numeru |
| ZK-27-004 Zielińska | ZK27004ZIELIŃSKA | ZK27004 | Zieliński Marek | dopasowana |
| ZK27099 obóz | ZK27099OBÓZ | ZK27099 | brak na liście | do wyjaśnienia: numer spoza listy |
Jak znaleźć płatnika po numerze rezerwacji?
Płatnika po numerze rezerwacji znajdzie WYSZUKAJ.PIONOWO w zakresie listy od kolumny A, czyli numeru rezerwacji, do kolumny F, czyli płatnika. Pusty numer daje pustą komórkę, a numer spoza listy tekst brak na liście:
=JEŻELI(F2="";"";JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(F2;Lista!$A$2:$F$41;6;FAŁSZ);"brak na liście"))
Zakres zaczyna się od kolumny A, bo WYSZUKAJ.PIONOWO szuka tylko w pierwszej kolumnie zakresu. Znaki dolara blokują zakres przy przeciąganiu formuły w dół, a FAŁSZ wymusza dokładne dopasowanie. Bez FAŁSZ funkcja przy braku numeru może zwrócić płatnika z innego wiersza, zamiast błędu.
X.WYSZUKAJ: prostsza wersja tej samej formuły
Funkcja X.WYSZUKAJ robi to samo krócej: osobno wskazujesz kolumnę z numerami i kolumnę z wynikiem, a czwarty argument podaje tekst na wypadek braku numeru. Domyślnie szuka dokładnie, więc FAŁSZ nie jest potrzebny:
=JEŻELI(F2="";"";X.WYSZUKAJ(F2;Lista!$A$2:$A$41;Lista!$F$2:$F$41;"brak na liście"))
W pliku przykładowym X.WYSZUKAJ daje dla wszystkich 9 przelewów te same wyniki co WYSZUKAJ.PIONOWO. Microsoft podaje ją dla Excela dla Microsoft 365, Excela 2021 i 2024, więc w Excelu 2016 i 2019 jej nie ma. Tam zostań przy WYSZUKAJ.PIONOWO.
Które przelewy wymagają wyjaśnienia?
Wyjaśnienia wymagają przelewy bez numeru w tytule i przelewy z numerem, którego nie ma na liście. Kolumna H nadaje każdej pozycji status:
=JEŻELI(A2="";"";JEŻELI(B2<=0;"obciążenie: pomiń";JEŻELI(F2="";"do wyjaśnienia: brak numeru";JEŻELI(G2="brak na liście";"do wyjaśnienia: numer spoza listy";"dopasowana"))))
Z 9 pozycji października w pliku przykładowym 6 jest dopasowanych, 2 wymagają wyjaśnienia, a 1 to obciążenie rachunku. Przelew Kamińskiej przyszedł bez numeru, a numer ZK27099 nie należy do tego wyjazdu. Dopóki ich nie wyjaśnisz, rezerwacja ZK27007 pokazuje zaległość, choć rodzic zapłacił. Formuła zlicza takie pozycje także w arkuszu Wyjazd, więc widać je bez przeglądania wyciągu.
Jak przenieść dopasowane przelewy do arkusza Wpłaty?
Dopasowany przelew przenosisz do arkusza Wpłaty tylko raz, a kolumna I pilnuje, czy już tam jest. Funkcja LICZ.WARUNKI szuka we Wpłatach wiersza z tym samym numerem, datą i kwotą:
=JEŻELI(H2<>"dopasowana";"";JEŻELI(LICZ.WARUNKI(Wpłaty!$B$2:$B$81;F2;Wpłaty!$A$2:$A$81;A2;Wpłaty!$C$2:$C$81;B2)>0;"tak";"przepisz do Wpłat"))
- Włącz filtr na arkuszu Wyciąg i wybierz w kolumnie I wartość przepisz do Wpłat.
- Skopiuj datę z kolumny A, numer z kolumny F i kwotę z kolumny B.
- Wklej je na końcu arkusza Wpłaty, w kolumny A, B i C, przez Wklej specjalnie, Wartości. W kolumnie D wpisz przelew.
- Sprawdź, że kolumna I pokazuje teraz tak przy każdym dopasowanym przelewie.
W pliku przykładowym do przepisania zostaje przelew ZK27005 z 14 października na 500 zł. Po jego dopisaniu zaległość tej rezerwacji spada z 700 zł do 200 zł. Jak działa liczenie zaległości, opisujemy w tekście kto nie zapłacił za wyjazd.
Dlaczego wyszukaj pionowo zwraca #N/D?
WYSZUKAJ.PIONOWO zwraca #N/D, gdy szukanej wartości nie ma w pierwszej kolumnie zakresu w dokładnie tym samym zapisie. Zły wynik bez błędu daje zwykle brak argumentu FAŁSZ. Najczęstsze przyczyny przy przelewach:
| Objaw | Przyczyna | Co zrobić |
|---|---|---|
| #N/D przy numerze, który jest na liście | Spacja albo kreska w numerze, inny zapis niż na liście | Szukać w kolumnie z tytułem oczyszczonym przez PODSTAW |
| Zły płatnik zamiast #N/D | Brak czwartego argumentu, czyli przybliżone dopasowanie | Dopisać FAŁSZ jako ostatni argument |
| #N/D mimo identycznego zapisu | Numer zapisany raz jako liczba, raz jako tekst | Numery z literami, np. ZK27001, zawsze są tekstem |
| Wynik z niewłaściwej kolumny | nr_kolumny liczony od kolumny A arkusza, a nie od początku zakresu | Liczyć od pierwszej kolumny zakresu |
| Numer jest na liście, ale nie w pierwszej kolumnie zakresu | WYSZUKAJ.PIONOWO szuka tylko w pierwszej kolumnie | Zacząć zakres od kolumny z numerem albo użyć X.WYSZUKAJ |
Jaki numer rezerwacji najłatwiej znaleźć w tytule przelewu?
Najłatwiej znaleźć numer ze stałym początkiem, stałą długością i bez spacji, na przykład ZK27001. Stały początek pozwala go wyszukać, stała długość wyciąć, a brak spacji zmniejsza liczbę wariantów zapisu. Podaj płatnikowi gotowy tytuł przelewu z numerem w umowie i w każdej wiadomości o racie. Numery opisujemy też w tekście lista uczestników wyjazdu w Excelu.
Pytania
WYSZUKAJ.PIONOWO czy X.WYSZUKAJ: której funkcji użyć?
X.WYSZUKAJ, jeśli masz Excela dla Microsoft 365, 2021 albo 2024. Domyślnie szuka dokładnie, ma osobny argument na brak wyniku i nie wymaga, żeby numer był w pierwszej kolumnie. WYSZUKAJ.PIONOWO działa także w Excelu 2016 i 2019, ale wymaga FAŁSZ i klucza w pierwszej kolumnie.
Czy wielkość liter w tytule przelewu ma znaczenie?
Nie. Formuła w pliku zamienia tytuł na wielkie litery, a SZUKAJ.TEKST i tak nie uwzględnia wielkości liter (Microsoft). Dlatego zk27001 rata daje numer ZK27001.
Co, gdy jeden przelew opłaca dwie rezerwacje?
Formuła wytnie pierwszy numer z tytułu i przypisze cały przelew do niego. Taki przelew podziel ręcznie w arkuszu Wpłaty na dwa wiersze z kwotami dla każdej rezerwacji. Jeśli rodzeństwo ma jedną rezerwację, problem nie powstaje.
Jak nie wpisać tej samej wpłaty dwa razy, gdy wklejam wyciąg ponownie?
Patrz na kolumnę Jest we Wpłatach. Formuła z LICZ.WARUNKI szuka w arkuszu Wpłaty wiersza z tym samym numerem, datą i kwotą. Wartość tak oznacza, że wpłata już jest, a przepisz do Wpłat, że trzeba ją dodać.
Co zrobić z przelewem do wyjaśnienia?
Przelew bez numeru przypisz po nadawcy i tytule, a numer spoza listy porównaj z listami innych wyjazdów. Dopiero po ustaleniu rezerwacji wpisz wpłatę w arkuszu Wpłaty. Do tego czasu rezerwacja pokazuje zaległość, więc nie wysyłaj przypomnienia bez sprawdzenia tej listy.
Wyciąg wczytany, wpłaty przypisane
W Mój Wyjazd wklejasz wyciąg albo wgrywasz CSV z kolumnami data, kwota, tytuł i nadawca. System szuka numeru rezerwacji w tytule, przypisuje wpłatę, a przelewy bez jednoznacznego numeru odkłada do wyjaśnienia. Ten sam wyciąg można wczytać drugi raz bez dublowania wpłat. Działa w demo. Zobacz, co działa dziś.