In questo tutorial di Excel imparerai come gestire più corrispondenze (risultati) da una funzione CERCA.VERT. Tratteremo due diverse tecniche. Il primo utilizza effettivamente la funzione CERCA.VERT (insieme a CONTA.SE). Il secondo utilizza INDEX/MATCH per simulare un VLOOKUP.
CERCA.VERT con più risultati
Per cercare più corrispondenze con la funzione CERCA.VERT è necessario creare una colonna di supporto all'interno della tabella di dati. La colonna helper usa la funzione CONTA.SE per creare un ID univoco per ogni istanza. La colonna di supporto deve essere la colonna più a sinistra all'interno del set di dati. Se non è pratico regolare il set di dati, dovresti esaminare l'altro metodo nella sezione successiva.
Diamo un'occhiata a questo metodo in pratica:
Passo 1:
Notare la formula nella cella F6:=B5&CONTA.SE(B5:B$9,B5)
Nello specifico, l'intervallo di riferimento: B6:B$11 . Notare il segno $. Il segno $ "blocca" il riferimento della cella: B$11. Quindi, mentre copi la formula, B $ 11 rimane bloccato. B6, tuttavia, non è bloccato, quindi quando si copia la formula B6 si trasforma in B7, ecc. Questa tecnica crea un numero univoco per ogni istanza trovata. Il motivo per cui lasciamo B6 sbloccato è che quando viene contabilizzata un'istanza, viene rimossa dal conteggio totale, creando il numero univoco.
Notare anche il &. & unisce il nome del prodotto con il suo numero di istanza per creare un campo che utilizzeremo nella nostra ricerca.
Passo 2:
Abbiamo spostato la nuova colonna helper (project_adj) a sinistra del set di dati in B14:C19. Ora possiamo eseguire il VLOOKUP su più risultati. Invece di cercare calzini, cerca calzini1 e calzini2. Ora puoi avere più righe di risultati CERCA.VERT, che rappresentano le più corrispondenze trovate.
Lo svantaggio di questo metodo è che devi modificare il set di dati originale (o copiare/incollare il set di dati altrove) per eseguire più risultati CERCA.VERT. In alternativa, puoi utilizzare il metodo INDEX/MATCH:
INDICE / CORRISPONDENZA per ricerche di più corrispondenze
La maggior parte degli utenti di Excel è consapevole della potenza della funzione CERCA.VERT, ma molti non sono a conoscenza della potenza della funzione INDICE e della funzione di corrispondenza utilizzate in combinazione. La combinazione INDEX/MATCH può essere utilizzata per emulare un VLOOKUP, con il vantaggio di una maggiore flessibilità.
Nota: l'immagine direttamente sotto contiene le formule. L'immagine in basso contiene i risultati della formula.
Cosa sta succedendo nelle formule sopra?
CONFRONTA - Cerca la posizione di un valore con un intervallo. In questo esempio, MATCH sta cercando "Calze" all'interno dell'elenco dei prodotti.
INDIRETTO - Crea un riferimento da una stringa di testo. Lo usiamo per regolare l'array di ricerca dell'elenco di prodotti. Una volta trovata una corrispondenza, l'intervallo viene regolato per escludere tale corrispondenza dalla ricerca, consentendo di trovare la corrispondenza successiva. Nella cella G5, impostiamo l'intervallo iniziale su B5:B10 (impostando start_num iniziale nella cella F5 su 5). In G5 abbiamo trovato una corrispondenza nella riga 1 dell'intervallo, quindi start_num di F6 è 5+1=6.
INDEX - Restituisce un valore da un array in base alla posizione del numero di colonna/riga in quell'array.
!! Immagino di includere la formula dell'array in modo che abbiano 1 formula da collegare…
