CERCA.VERT su due o più colonne di criteri – Excel University

Se hai mai provato a utilizzare una funzione CERCA.VERT con due o più colonne di criteri, lo hai rapidamente scoperto semplicemente non è stato costruito per quello scopo. Fortunatamente, esiste unaltra funzione che può funzionare in alternativa a CERCA.VERT a seconda di ciò che si desidera restituire.

Obiettivo di ricerca su più colonne

Innanzitutto, confermiamo il nostro obiettivo guardando una cartella di lavoro di esempio. Abbiamo esportato alcune informazioni dal nostro sistema di contabilità, che sostanzialmente riassume i totali delle transazioni per il mese per classe e per conto. Di seguito è mostrato un esempio dellesportazione:

Da questi dati esportati, vorremmo recuperare gli importi selezionati in base al colonne di classi e account. Vogliamo recuperare gli importi e inserirli nel nostro piccolo report, illustrato di seguito:

Se hai familiarità con CERCA.VERT è naturale provare a creare il report con questa funzione perché, dopo tutto, si tratta di unattività di ricerca. Inoltre, le attività di ricerca vengono risolte al meglio con le funzioni di ricerca tradizionali … giusto? Beh, dipende. Dipende da cosa stai cercando di recuperare.

Somma condizionale per ricerche

Se stai cercando di recuperare un valore numerico, come un importo, una funzione di ricerca tradizionale potrebbe non sii la tua scommessa migliore. Ecco perché. A partire da Excel 2007, Microsoft ha incluso la funzione di somma condizionale SUMIFS. Questa funzione di somma di condizioni multiple è progettata per aggiungere una colonna di numeri e includere solo le righe che soddisfano una o più condizioni. I punti stanno già iniziando a connettersi?

Se applichiamo questa idea al nostro compito a portata di mano, ci renderemmo presto conto che potremmo usare questa funzione di somma condizionale per recuperare i nostri valori di report.

Il primo argomento della funzione SOMMA.SE è lintervallo della somma, ovvero la colonna di numeri da aggiungere. Nel nostro caso, la colonna che ha il valore che desideriamo restituire. Gli argomenti rimanenti sono a coppie: lintervallo dei criteri e il valore dei criteri.

È utile pensare alla funzione in questi termini: somma questa colonna (argomento 1), includi solo quelle righe in cui questa colonna (argomento 2) è uguale a questo valore (argomento 3), e dove questa colonna (argomento 4) è uguale a questo valore (argomento 5) e dove … e così via, fino a 127 coppie.

Pertanto, per completare il nostro rapporto, recupereremo i valori dellimporto dallesportazione e faremo corrispondere le colonne della classe e dellaccount, come mostrato di seguito.

Se ci sono più righe con la stessa classe e account, la funzione SUMIFS restituirà la somma di tutti gli elementi corrispondenti.

Come puoi vedere, se il valore stanno cercando di restituire è un numero, quindi la funzione SUMIFS semplifica lesecuzione di ricerche su più colonne. Ma cosa succede se il valore che stai cercando di restituire non è un numero? Bene, allora dovrai usare una funzione di ricerca tradizionale come discusso di seguito.

Uso di CERCA.VERT con il metodo SUMIFS

Un metodo è usare CERCA.VERT e SOMMA.SE in ununica formula. In sostanza, usi SUMIFS come primo argomento di CERCA.VERT. Questo metodo è esplorato completamente in questo post di Excel University:

Utilizzo di CERCA.VERT con il metodo CONCATENATE

Se stai tentando di restituire una stringa di testo anziché un numero o stai utilizzando una versione di Excel che non ha SUMIFS, quindi probabilmente sei bloccato con lutilizzo di una funzione di ricerca tradizionale come CERCA.VERT insieme alla funzione CONCATENA per generare una singola colonna di ricerca univoca. Questo approccio è abbastanza ben documentato, ma lidea di base è questa: creare prima una singola colonna di ricerca, quindi utilizzare CERCA.VERT.

Il nostro esempio sarà un elenco di dipendenti, come illustrato di seguito:

Dobbiamo recuperare lo stato dallelenco dei dipendenti per il nostro piccolo rapporto mostrato di seguito:

Poiché il valore che stiamo cercando di restituire, lo stato, è una stringa di testo e non un numero, ci è precluso lutilizzo della funzione SUMIFS. Pertanto, dovremo passare alla vecchia scuola con CERCA.VERT e CONCATENA.

Iniziamo creando una colonna helper che fondamentalmente crea i valori di ricerca combinati. Ciò può essere facilmente ottenuto con la funzione CONCATENATE o loperatore di concatenazione (&). Questa nuova colonna di ricerca è illustrata nella colonna B di seguito:

Ora abbiamo una singola colonna di ricerca che può essere utilizzata con un funzione di ricerca tradizionale come CERCA.VERT. Il rapporto può essere compilato cercando i nomi combinati allinterno del nuovo intervallo di ricerca, come mostrato di seguito:

Questo stesso approccio può essere utilizzato quando è necessario considerare due, tre o più colonne di ricerca.

Conclusione

Oltre a essere in grado di eseguire ricerche su più colonne quando il valore restituito è numerico, la funzione SUMIFS ha ulteriori vantaggi rispetto alle funzioni di ricerca tradizionali. Ad esempio, restituisce zero quando non viene trovato alcun valore corrispondente, restituisce la somma di tutte le corrispondenze, supporta operatori di confronto e non si interrompe quando viene inserita una nuova colonna tra le colonne di ricerca e di ritorno.

Quindi, quando stai per estrarre la funzione CERCA.VERT per eseguire unattività di ricerca, considera invece lutilizzo di SUMIFS. Che tu ci creda o no, la funzione SUMIFS è unottima funzione di ricerca.

Se hai altri approcci preferiti alle ricerche su più colonne, ci piacerebbe saperne di più … per favore pubblica un commento qui sotto.

File di esempio

Se vuoi giocare con la cartella di lavoro usata per generare gli screenshot qui sopra, non esitare a scaricare il file di esempio:

MultColumnLookup