Contenuti
Tutte le classiche funzioni di ricerca e sostituzione del tipo VPR (CERCA.VERT), GPR (RICERCA), PIÙ ESPOSTO (INCONTRO) e quelli come loro hanno una caratteristica importante: ricercano dall'inizio alla fine, cioè da sinistra a destra o dall'alto verso il basso nei dati di origine. Non appena viene trovata la prima corrispondenza corrispondente, la ricerca si interrompe e viene trovata solo la prima occorrenza dell'elemento di cui abbiamo bisogno.
Cosa fare se dobbiamo trovare non la prima, ma l'ultima occorrenza? Ad esempio, l'ultima transazione per il cliente, l'ultimo pagamento, l'ordine più recente, ecc.?
Metodo 1: trovare l'ultima riga con una formula di matrice
Se la tabella originale non ha una colonna con una data o un numero seriale di una riga (ordine, pagamento...), allora il nostro compito è, infatti, trovare l'ultima riga che soddisfi la condizione data. Questo può essere fatto con la seguente formula di matrice:
Qui:
- Funzione IF (SE) controlla tutte le celle in una colonna una per una e visualizza il numero di riga se contiene il nome di cui abbiamo bisogno. Il numero di riga sul foglio ci viene fornito dalla funzione LINE (RIGA), ma poiché abbiamo bisogno del numero di riga nella tabella, dobbiamo anche sottrarre 1, perché abbiamo un'intestazione nella tabella.
- Allora la funzione MAX (MASSIMO) seleziona dall'insieme formato di numeri di riga il valore massimo, ovvero il numero della riga più recente del client.
- Funzione INDICE (INDICE) restituisce il contenuto della cella con l'ultimo numero trovato da qualsiasi altra colonna della tabella richiesta (Codice d'ordine).
Tutto questo deve essere inserito come formula di matrice, cioè:
- In Office 365 con gli ultimi aggiornamenti installati e il supporto per gli array dinamici, puoi semplicemente premere entrare.
- In tutte le altre versioni, dopo aver inserito la formula, dovrai premere la scorciatoia da tastiera Ctrl+Shift+entrare, che aggiungerà automaticamente parentesi graffe nella barra della formula.
Metodo 2: ricerca inversa con la nuova funzione CERCA
Ho già scritto un lungo articolo con un video su una nuova funzionalità VISTA (CERCA X), che è apparso nelle ultime versioni di Office per sostituire il vecchio VLOOKUP (CERCA.VERT). Con l'aiuto di BROWSE, il nostro compito è risolto in modo abbastanza elementare, perché. per questa funzione (a differenza di CERCA.VERT), puoi impostare esplicitamente la direzione di ricerca: top-down o bottom-up – il suo ultimo argomento (-1) è responsabile di questo:
Metodo 3. Cerca una stringa con la data più recente
Se nei dati di origine abbiamo una colonna con un numero di serie o una data che svolge un ruolo simile, l'attività viene modificata: dobbiamo trovare non l'ultima riga (più bassa) con una corrispondenza, ma la riga con l'ultima ( massimo) data.
Ho già discusso in dettaglio come farlo usando le funzioni classiche e ora proviamo a usare la potenza delle nuove funzioni di array dinamico. Per maggiore bellezza e praticità, convertiamo anche la tabella originale in una tabella "intelligente" utilizzando una scorciatoia da tastiera Ctrl+T o comandi Home – Formatta come una tabella (Home — Formatta come tabella).
Con il loro aiuto, questa "coppia assassina" risolve il nostro problema con molta grazia:
Qui:
- Prima la funzione FILTRO (FILTRO) seleziona solo quelle righe dalla nostra tabella in cui nella colonna – il nome di cui abbiamo bisogno.
- Allora la funzione GRADE (ORDINARE) ordina le righe selezionate per data in ordine decrescente, con l'offerta più recente in alto.
- Funzione INDICE (INDICE) estrae la prima riga, ovvero restituisce l'ultimo trade di cui abbiamo bisogno.
- E, infine, la funzione FILTRO esterna rimuove la prima e la terza colonna extra dai risultati (Codice d'ordine и ) e lascia solo la data e l'importo. Per questo, viene utilizzata una matrice di costanti. {0;1;0;1}, definendo quali colonne si desidera visualizzare (1) o non si desidera (0).
Metodo 4: trovare l'ultima corrispondenza in Power Query
Bene, per motivi di completezza, esaminiamo una soluzione al nostro problema di ricerca inversa utilizzando il componente aggiuntivo Power Query. Con il suo aiuto, tutto è risolto molto rapidamente e magnificamente.
1. Convertiamo la nostra tabella originale in una "intelligente" usando una scorciatoia da tastiera Ctrl+T o comandi Home – Formatta come una tabella (Home — Formatta come tabella).
2. Caricalo in Power Query con il pulsante Dalla tabella/intervallo linguetta Dati (Dati — Da tabella/intervallo).
3. Ordiniamo (tramite l'elenco a discesa del filtro nell'intestazione) la nostra tabella in ordine decrescente di data, in modo che le transazioni più recenti siano in cima.
4... Nella scheda Trasformazione scegli una squadra Raggruppa per (Trasforma — Raggruppa per) e impostare il raggruppamento per clienti e, come funzione di aggregazione, selezionare l'opzione Tutte le linee (Tutte le righe). Puoi nominare la nuova colonna come preferisci, ad esempio Dettagli.
Dopo il raggruppamento, otterremo un elenco di nomi univoci dei nostri clienti e nella colonna Dettagli – tabelle con tutte le transazioni di ciascuna di esse, dove la prima riga sarà l'ultima transazione, che è ciò di cui abbiamo bisogno:
5. Aggiungi una nuova colonna calcolata con il pulsante Colonna personalizzata linguetta Aggiungi colonna (Aggiungi colonna: aggiungi una colonna personalizzata)e inserisci la seguente formula:
Qui Dettagli – questa è la colonna da cui prendiamo le tabelle per clienti, e 0 {} è il numero della riga che vogliamo estrarre (la numerazione delle righe in Power Query inizia da zero). Otteniamo una colonna con i record (Record), dove ogni voce è la prima riga di ogni tabella:
Resta da espandere il contenuto di tutti i record con il pulsante con le doppie frecce nell'intestazione della colonna Ultimo affare selezionando le colonne desiderate:
… e quindi eliminare la colonna che non è più necessaria Dettagli cliccando con il tasto destro del mouse sul suo titolo – Rimuovi colonne (Rimuovi colonne).
Dopo aver caricato i risultati sul foglio tramite Home — Chiudi e carica — Chiudi e carica (Home — Chiudi e carica — Chiudi e carica in...) otterremo una bella tabella con un elenco di transazioni recenti, come volevamo:
Quando modifichi i dati di origine, non devi dimenticare di aggiornare i risultati facendo clic con il pulsante destro del mouse su di essi: il comando Aggiorna e salva (Ricaricare) o scorciatoia da tastiera Ctrl+altro+F5.
- La funzione CERCA è un discendente di CERCA.VERT
- Come utilizzare le nuove funzioni di array dinamico SORT, FILTER e UNIC
- Trovare l'ultima cella non vuota in una riga o colonna con la funzione CERCA