cuntenutu
Tutte e funzioni classiche di ricerca è di sustituzione di tipu VPR (CERCA V), GPR (Cerca), PIÙ ESPOSTA (MATCH) è quelli chì cum'è elli anu una funzione impurtante - cercanu da u principiu à a fine, vale à dì da manca à destra o da cima à fondu in i dati fonte. Appena u primu currispondente hè truvatu, a ricerca si ferma è si trova solu a prima occorrenza di l'elementu chì avemu bisognu.
Cosa da fà s'ellu ci vole à truvà micca u primu, ma l'ultimu casu? Per esempiu, l'ultima transazzione per u cliente, l'ultimu pagamentu, l'ordine più recente, etc.?
Metudu 1: Truvà l'Ultima Fila cù una Formula Array
Se a tavula originale ùn hà micca una colonna cù una data o un numeru di serie di una fila (ordine, pagamentu ...), allora u nostru compitu hè, in fattu, di truvà l'ultima fila chì satisface a cundizione datu. Questu pò esse fattu cù a seguente formula array:
Eccu:
- funzioni IF (SI) verifica tutte e cellule in una colonna una per una Customer è mostra u numeru di linea s'ellu cuntene u nome chì avemu bisognu. U numeru di linea nantu à u fogliu ci hè datu da a funzione LINE (fila), ma siccomu avemu bisognu di u numeru di fila in a tavula, avemu da sottrae 1, perchè avemu un capu in a tavola.
- Allora a funzione MAX (MAX) sceglie u valore massimu da u settore furmatu di numeri di fila, vale à dì u numeru di a linea più recente di u cliente.
- funzioni INDEX (INDICE) Ritorna u cuntenutu di a cellula cù l'ultimu numeru truvatu da qualsiasi altra colonna di tabella necessaria (Codice d'ordine).
Tuttu chistu deve esse inseritu cum'è formula di matrice, vale à dì:
- In Office 365 cù l'ultime aghjurnamenti installati è supportu per arrays dinamichi, pudete semplicemente appughjà Rinsignate.
- In tutti l'altri versioni, dopu avè intrutu in a formula, vi tuccherà à appughjà u scurciatoia di tastiera Ctrl+Shift+Rinsignate, chì aghjunghje automaticamente parentesi ricci in a barra di formula.
Metudu 2: Ricerca inversa cù a nova funzione LOOKUP
Aghju digià scrittu un articulu longu cù un video nantu à una nova funzione Visu (XLOOKUP), chì apparsu in l'ultime versioni di Office per rimpiazzà u vechju VLOOKUP (CERCA V). Cù l'aiutu di BROWSE, u nostru compitu hè risoltu abbastanza elementari, perchè. per sta funzione (cuntrariu di VLOOKUP), pudete stabilisce esplicitamente a direzzione di ricerca: top-down o bottom-up - u so ultimu argumentu (-1) hè rispunsevule per questu:
Metudu 3. Cerca una stringa cù l'ultima data
Se in a fonte di dati avemu una colonna cù un numeru di serie o una data chì ghjoca un rolu simili, allura u compitu hè mudificatu - avemu bisognu di truvà micca l'ultima linea (più bassu) cù una partita, ma a linea cù l'ultime ( massima) data.
Aghju digià discututu in dettagliu cumu fà questu utilizendu funzioni classiche, è avà pruvemu d'utilizà u putere di e novi funzioni di array dinamica. Per più bellezza è cunvenzione, cunvertemu ancu a tavola originale in una tavola "intelligente" cù una scurciatoia di tastiera Ctrl+T o cumandamenti Home - Formate cum'è una tavola (Casa - Formate cum'è Table).
Cù u so aiutu, sta "coppia assassina" risolve u nostru prublema assai grazia:
Eccu:
- Funzione prima FILTER (FILTRE) sceglie solu quelli fila da a nostra tavula induve in a colonna Customer - u nome chì avemu bisognu.
- Allora a funzione Pendentif (SORT) ordina e file selezziunate per data in ordine decrescente, cù l'affare più recente in cima.
- funzioni INDEX (INDICE) estratti a prima fila, vale à dì torna l'ultimu cummerciu chì avemu bisognu.
- È, infine, a funzione FILTER esterna elimina a 1a è a 3a colonna extra da i risultati (Codice d'ordine и Customer) è lascia solu a data è a quantità. Per questu, un array di custanti hè utilizatu. {0;1;0;1}, definendu quali colonne vulemu (1) o ùn vulemu micca (0) per vede.
Metudu 4: Truvà l'ultima partita in Power Query
Ebbè, per esse cumpletu, fighjemu una suluzione à u nostru prublema di ricerca inversa utilizendu l'add-in Power Query. Cù u so aiutu, tuttu hè risoltu assai rapidamente è bellu.
1. Cunvertemu a nostra tavola originale in una "intelligente" utilizendu una scorciata di tastiera Ctrl+T o cumandamenti Home - Formate cum'è una tavola (Casa - Formate cum'è Table).
2. Caricà in Power Query cù u buttone Da Table/Range Insignia Dati (Dati - Da Table/Range).
3. Sortemu (per mezu di a lista di u filtru in l'intestazione) a nostra tavola in ordine decrescente di data, in modu chì e transazzioni più recenti sò in cima.
4... In a tabulazione Mutazioni sceglie una squadra Gruppi da (Trasforma - Group By) è stabilisce u raggruppamentu per i clienti, è cum'è una funzione aggregating, selezziunate l'opzione Tutte e linee (tutte i file). Pudete nome a nova colonna qualcosa chì ti piace - per esempiu Details.
Dopu à u raggruppamentu, averemu una lista di nomi unichi di i nostri clienti è in a colonna Details - tavule cù tutte e transazzione di ognunu di elli, induve a prima linea serà l'ultima transazzione, chì hè ciò chì avemu bisognu:
5. Aghjunghjite una nova colonna calculata cù u buttone Colonna persunalizata Insignia Aghjunghjite a colonna (Aggiungi una colonna - Aggiungi una colonna persunalizata)è inserite a seguente formula:
quì Details – questu hè a colonna da quale avemu pigliatu tavule da i clienti, è 0 {} hè u numeru di a fila chì vulemu estrarre (numerazione di fila in Power Query principia da zero). Avemu una colonna cù i registri (Santuzza), induve ogni entrata hè a prima fila da ogni tavula:
Resta per espansione u cuntenutu di tutti i registri cù u buttone cù freccia doppia in l'intestazione di a colonna L'ultimu affare selezziunà e colonne desiderate:
... è dopu sguassate a colonna chì ùn hè più necessariu Details clicchendu u dirittu nantu à u so titulu - Eliminate e colonne (Eliminate e colonne).
Dopu à carica i risultati à u fogliu attraversu Casa — Chjode è carica — Chjama è carica (Casa - Chiudi è Carica - Chiudi è Carica à...) averemu un tavulu cusì bellu cù una lista di transazzioni recenti, cum'è vulemu:
Quandu cambiate i dati di a fonte, ùn devi micca scurdate di aghjurnà i risultati clicchendu u dirittu nantu à elli - u cumandamentu Aghjurnà & Salvà (rinfriscà) o scorciatoia da tastiera Ctrl+Alt+F5.
- A funzione LOOKUP hè un discendente di VLOOKUP
- Cumu aduprà e novi funzioni di array dinamica SORT, FILTER è UNIC
- Truvà l'ultima cellula non bianca in una fila o colonna cù a funzione LOOKUP