Cuprins
Toate funcțiile clasice de căutare și înlocuire de tip VPR (CĂUTARE V), GPR (CĂUTARE), MAI EXPUSĂ (MECI) iar cei ca ei au o caracteristică importantă – caută de la început până la sfârșit, adică de la stânga la dreapta sau de sus în jos în datele sursă. De îndată ce este găsită prima potrivire, căutarea se oprește și este găsită doar prima apariție a elementului de care avem nevoie.
Ce să facem dacă trebuie să găsim nu prima, ci ultima apariție? De exemplu, ultima tranzacție pentru client, ultima plată, cea mai recentă comandă etc.?
Metoda 1: Găsirea ultimului rând cu o formulă matrice
Dacă tabelul inițial nu are o coloană cu o dată sau un număr de serie al unui rând (comandă, plată...), atunci sarcina noastră este, de fapt, să găsim ultimul rând care satisface condiția dată. Acest lucru se poate face cu următoarea formulă matrice:
Aici:
- Funcţie IF (DACĂ) verifică toate celulele dintr-o coloană una câte una Client și afișează numărul liniei dacă conține numele de care avem nevoie. Numărul rândului de pe foaie ne este dat de funcție LINE (RÂND), dar din moment ce avem nevoie de numărul rândului din tabel, în plus trebuie să scădem 1, deoarece avem un antet în tabel.
- Apoi funcția MAX (MAX) selectează valoarea maximă din setul format de numere de rând, adică numărul celei mai recente linii a clientului.
- Funcţie INDEX (INDEX) returnează conținutul celulei cu ultimul număr găsit din orice altă coloană obligatorie a tabelului (Codul de comanda).
Toate acestea trebuie introduse ca formula matrice, adică:
- În Office 365, cu cele mai recente actualizări instalate și suport pentru matrice dinamice, puteți apăsa pur și simplu Intrați.
- În toate celelalte versiuni, după introducerea formulei, va trebui să apăsați comanda rapidă de la tastatură Ctrl+Schimba+Intrați, care îi va adăuga automat acolade în bara de formule.
Metoda 2: Căutare inversă cu noua funcție LOOKUP
Am scris deja un articol lung cu un videoclip despre o funcție nouă VIEW (CĂUTARE XL), care a apărut în cele mai recente versiuni de Office pentru a înlocui vechiul VLOOKUP (CĂUTARE V). Cu ajutorul BROWSE, sarcina noastră este rezolvată destul de elementar, deoarece. pentru această funcție (spre deosebire de VLOOKUP), puteți seta în mod explicit direcția de căutare: de sus în jos sau de jos în sus – ultimul argument (-1) este responsabil pentru aceasta:
Metoda 3. Căutați un șir cu cea mai recentă dată
Dacă în datele sursă avem o coloană cu un număr de serie sau o dată care joacă un rol similar, atunci sarcina este modificată - trebuie să găsim nu ultima linie (cea mai joasă) cu o potrivire, ci linia cu cea mai recentă ( maxim) data.
Am discutat deja în detaliu cum să facem acest lucru folosind funcții clasice, iar acum să încercăm să folosim puterea noilor funcții de matrice dinamică. Pentru mai multă frumusețe și comoditate, transformăm, de asemenea, tabelul original într-un tabel „inteligent”, folosind o comandă rapidă de la tastatură Ctrl+T sau comenzi Acasă – Formatați ca tabel (Acasă — Format ca tabel).
Cu ajutorul lor, acest „cuplu ucigaș” ne rezolvă problema cu multă grație:
Aici:
- Funcționează mai întâi FILTRU (FILTRU) selectează numai acele rânduri din tabelul nostru unde se află în coloană Client – numele de care avem nevoie.
- Apoi funcția GRADE (FEL) sortează rândurile selectate după dată în ordine descrescătoare, cu cea mai recentă ofertă în partea de sus.
- Funcţie INDEX (INDEX) extrage primul rând, adică returnează ultima tranzacție de care avem nevoie.
- Și, în sfârșit, funcția externă FILTER elimină prima și a treia coloană suplimentară din rezultate (Codul de comanda и Client) și lasă doar data și suma. Pentru aceasta, se folosește o matrice de constante. {0;1;0;1}, definind ce coloane dorim (1) sau nu vrem să fie afișate (0).
Metoda 4: Găsirea ultimei potriviri în Power Query
Ei bine, de dragul caracterului complet, să ne uităm la o soluție la problema noastră de căutare inversă folosind programul de completare Power Query. Cu ajutorul ei, totul se rezolvă foarte repede și frumos.
1. Să transformăm tabelul nostru original într-unul „inteligent” folosind o comandă rapidă de la tastatură Ctrl+T sau comenzi Acasă – Formatați ca tabel (Acasă — Format ca tabel).
2. Încărcați-l în Power Query cu butonul Din masă/gamă fila Date (Date - Din tabel/gamă).
3. Sortăm (prin lista derulantă a filtrului din antet) tabelul nostru în ordinea descrescătoare a datei, astfel încât cele mai recente tranzacții să fie în partea de sus.
4… În filă Transformare alege o echipa A se grupa cu (Transformare — Grupați după) și setați gruparea după clienți și, ca funcție de agregare, selectați opțiunea Toate liniile (Toate rândurile). Puteți denumi noua coloană orice doriți, de exemplu Detalii.
După grupare, vom obține o listă cu numele unice ale clienților noștri și în coloană Detalii – tabele cu toate tranzacțiile fiecăruia dintre ele, unde prima linie va fi cea mai recentă tranzacție, care este ceea ce avem nevoie:
5. Adăugați o nouă coloană calculată cu butonul Coloană personalizată fila Adăugați coloana (Adăugați coloană — Adăugați coloană personalizată)și introduceți următoarea formulă:
Aici Detalii – aceasta este coloana din care luăm tabele după clienți, și 0 {} este numărul rândului pe care vrem să-l extragem (numerotarea rândurilor în Power Query începe de la zero). Primim o coloană cu înregistrări (Record), unde fiecare intrare este primul rând din fiecare tabel:
Rămâne să extindem conținutul tuturor înregistrărilor cu butonul cu săgeți duble din antetul coloanei Ultima afacere selectarea coloanelor dorite:
… și apoi ștergeți coloana care nu mai este necesară Detalii făcând clic dreapta pe titlul său - Eliminați coloanele (Eliminați coloanele).
După încărcarea rezultatelor pe foaie prin Acasă — Închideți și încărcați — Închideți și încărcați (Acasă — Închideți și încărcați — Închideți și încărcați în...) vom obține un tabel atât de frumos cu o listă de tranzacții recente, așa cum ne-am dorit:
Când modificați datele sursă, nu trebuie să uitați să actualizați rezultatele făcând clic dreapta pe ele - comanda Actualizați și salvați (Reîmprospăta) sau scurtătură de la tastatură Ctrl+Alt+F5.
- Funcția LOOKUP este un descendent al VLOOKUP
- Cum să utilizați noile funcții ale matricei dinamice SORT, FILTER și UNIC
- Găsirea ultimei celule non-vide dintr-un rând sau coloană cu funcția LOOKUP