Guarda il video – Come utilizzare la funzione CERCA.VERT con più criteri

La funzione CERCA.VERT di Excel, nella sua forma base, può cercare un valore di ricerca e restituire il valore corrispondente dalla riga specificata.

Ma spesso è necessario utilizzare CERCA.VERT di Excel con più criteri.

Come utilizzare CERCA.VERT con più criteri

Supponi di disporre di dati con il nome degli studenti, il tipo di esame e il punteggio matematico (come mostrato di seguito):

Usare la funzione CERCA.VERT per ottenere il punteggio matematico di ogni studente per i rispettivi livelli di esame potrebbe essere una sfida.

Si può discutere che un’opzione migliore sarebbe ristrutturare il set di dati o utilizzare una tabella pivot. Se per te funziona, niente del genere. Ma in molti casi, sei bloccato con i dati che hai e la tabella pivot potrebbe non essere un’opzione.

In questi casi, questo tutorial è per te.

Ora ci sono due modi per ottenere il valore di ricerca utilizzando CERCA.VERT con più criteri.

• Utilizzo di una colonna helper.
• Utilizzo della funzione SCEGLI.

CERCA.VERT con più criteri: utilizzo di una colonna helper

Sono un fan delle colonne helper in Excel.

Trovo due vantaggi significativi dell’utilizzo di colonne helper rispetto alle formule di matrice:

• Rende facile capire cosa sta succedendo nel foglio di lavoro.
• Lo rende più veloce rispetto alle funzioni di matrice (evidente in grandi set di dati).

Ora, non fraintendermi. Non sono contrario alle formule di matrice. Adoro le cose incredibili che si possono fare con le formule di matrice. È solo che li salvo per occasioni speciali quando tutte le altre opzioni non sono di aiuto.

Tornando alla domanda in questione, la colonna helper è necessaria per creare un qualificatore univoco. Questo qualificatore univoco può quindi essere utilizzato per cercare il valore corretto. Ad esempio, ci sono tre Matt nei dati, ma c’è solo una combinazione di Matt e Unit Test o Matt e Mid-Term.

Ecco i passaggi:

Come funziona?

Creiamo qualificatori univoci per ogni istanza di un nome e per l’esame. Nella funzione CERCA.VERT qui utilizzata, il valore di ricerca è stato modificato in $ F3 & ”|” & G $ 2 in modo che entrambi i criteri di ricerca vengono combinati e utilizzati come un singolo valore di ricerca. Ad esempio, il valore di ricerca per la funzione CERCA.VERT in G2 è Matt | Test unità. Ora questo valore di ricerca viene utilizzato per ottenere il punteggio da C2: D19.

Chiarimenti:

Ci sono un paio di domande che probabilmente ti verranno in mente, quindi ho pensato di proverò a rispondere qui:

• Perché ho usato | simbolo pur unendo i due criteri? – In alcune condizioni eccezionalmente rare (ma possibili), potresti avere due criteri che sono diversi ma finiscono per dare lo stesso risultato quando combinati. Ecco un esempio molto semplice (perdonami per la mia mancanza di creatività qui):

Nota che mentre A2 e A3 sono diversi e B2 e B3 sono diversi, le combinazioni finiscono per essere le stesse. Ma se usi un separatore, anche la combinazione sarebbe diversa (D2 e D3).

• Perché ho inserito la colonna helper tra le colonne B e C e non all’estrema sinistra? – Non c’è nulla di male nell’inserire la colonna helper all’estrema sinistra. In effetti, se non vuoi temperare con i dati originali, questa dovrebbe essere la strada da percorrere. L’ho fatto in quanto mi fa utilizzare meno numero di celle nella funzione CERCA.VERT. Invece di avere 4 colonne nella matrice della tabella, potrei gestire solo 2 colonne. Ma sono solo io.

Ora non esiste una taglia che vada bene per tutti. Alcune persone potrebbero preferire non utilizzare alcuna colonna helper durante l’utilizzo di CERCA.VERT con più criteri.

Quindi ecco il metodo della colonna non helper per te.

Scarica il file di esempio

CERCA.VERT con più criteri – Utilizzo della funzione SCEGLI

Utilizzo di formule di matrice invece di helper le colonne ti fanno risparmiare spazio sul foglio di lavoro e le prestazioni possono essere ugualmente buone se utilizzate meno volte in una cartella di lavoro.

Considerando lo stesso set di dati usato sopra, ecco la formula che ti darà il risultato :

= CERCA.VERT ($ E3 & “|” & F $ 2, SCEGLI ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Poiché si tratta di una formula di matrice, utilizzala con Control + Maiusc + Invio, anziché solo Invio.

Come funziona?

La formula utilizza anche il concetto di colonna helper. La differenza è che invece di inserire la colonna helper nel foglio di lavoro, considerala come dati helper virtuali che fanno parte della formula.

Lascia che ti mostri cosa intendo per dati helper virtuali.

Nell’illustrazione sopra, quando seleziono la parte SCEGLI della formula e premo F9, viene visualizzato il risultato che il SCEGLI la formula darebbe.

Il risultato è {“Matt | Unit Test”, 91; “Bob | Unit Test”, 52; ……}

È un array dove una virgola rappresenta la cella successiva nella stessa riga e il punto e virgola rappresenta che i seguenti dati si trovano nella colonna successiva. Pertanto, questa formula crea 2 colonne di dati: una colonna ha l’identificatore univoco e una ha il punteggio.

Ora , quando usi la funzione CERCA.VERT, cerca semplicemente il valore nella prima colonna (di questi dati di 2 colonne virtuali) e restituisce il punteggio corrispondente.

Scarica il File di esempio

Puoi anche utilizzare altre formule per eseguire una ricerca con più criteri (come INDEX / MATCH o SUMPRODUCT).