#Excel – Come usare CERCA.VERT con le funzioni SOMMA o SOMMA.SE

State cercando di creare un file di riepilogo in Excel che identificherà tutte le istanze di un particolare valore, e poi sommare altri valori che sono collegati a quelle istanze? Oppure, avete bisogno di trovare tutti i valori di una matrice che soddisfano la condizione specificata e quindi sommare i valori correlati situati in un altro foglio di lavoro? Oppure vi trovate di fronte a una sfida più concreta, come analizzare una tabella con le fatture dell’azienda, identificando tutte le fatture di un determinato fornitore, e quindi sommare tutti i valori relativi?

Gli incarichi possono variare, ma l’essenza è la stessa – volete cercare e sommare valori che soddisfano uno o più criteri. Che tipo di valori? Tutti i valori numerici. Che tipo di criteri? Qualsiasi 🙂 A partire da un numero o un riferimento ad una cella che contiene il giusto valore, per terminare con gli operatori logici e i risultati restituiti dalle formule di Excel.

Quindi, Microsoft Excel dispone di qualche funzionalità che possa aiutarvi con i compiti di cui sopra? Naturalmente, sì! È possibile trovare una soluzione, combinando CERCA.VERT o CERCA di Excel con le funzioni SOMMA o SOMMA.SE. Le formule di esempio che seguono vi aiuteranno a capire come funzionano queste funzioni di Excel e come applicarle ai dati reali.

Vi facciamo notare che questi sono esempi avanzati, che implicano una certa familiarità con i principi generali e la sintassi della funzione CERCA.VERT. In caso contrario, vi consiglio il seguente articolo che affronta tale funzione partendo dai principi di base: Come usare la funzione CERCA.VERT.

CERCA.VERT e SOMMA – Trovare la somma dei valori corrispondenti

Se lavorate con valori numerici in Excel, molto spesso non solo dovrete estrarre valori associati da un’altra tabella, ma dovrete anche sommare i numeri in più colonne o righe. Per fare questo, è possibile utilizzare una combinazione delle funzioni SOMMA e CERCA.VERT come illustrato di seguito.

Supponiamo che abbiate una lista dei prodotti con i dati di vendita per diversi mesi, una colonna per ogni mese.

Ora, volete fare una tabella riassuntiva con le vendite totali per ogni prodotto.

La soluzione è quella di utilizzare una matrice nel 3° argomento (indice) della funzione CERCA.VERT. Ecco una formula di esempio:

=SOMMA(CERCA.VERT(valore; matrice_tabella; {2.3.4}; FALSO))

Come vedete, usiamo una matrice {2.3.4} nel terzo argomento per eseguire diverse ricerche all’interno della stessa formula CERCA.VERT, al fine di ottenere la somma dei valori delle colonne 2,3 e 4.

Ed ora, cerchiamo di sistemare questa combinazione delle funzioni CERCA.VERT e SOMMA con i nostri dati , in modo da trovare il totale delle vendite presenti nelle colonne B – M nella tabella sopra riportata:

=SOMMA(CERCA.VERT(B2; 'Vendite mensili'!$A$2:$M$9; {2.3.4.5.6.7.8.9.10.11.12.13}; FALSO))

Come avete visto, utilizzare le funzioni CERCA.VERT e SOMMA insieme è facile. Tuttavia, questa non è la soluzione ideale, soprattutto se si lavora con grandi tabelle. Il punto è che l’utilizzo di formule matriciali può influire negativamente sulle prestazioni della cartella di lavoro in quanto ogni valore nella matrice effettua una chiamata separata della funzione CERCA.VERT. Così, più valori avete nella matrice e più formule matriciali avete nella cartella di lavoro, più Excel andrà lento.

È possibile evitare questo problema utilizzando una combinazione delle funzioni INDICE e CONFRONTA,  invece di SOMMA e CERCA.VERT, e vi mostrerò alcuni esempi formula nel prossimo articolo.

Come eseguire altri calcoli con la funzione CERCA.VERT

Un attimo fa abbiamo discusso un esempio di come sia possibile estrarre i valori da diverse colonne nella tabella di ricerca e calcolare la somma di questi valori. Allo stesso modo, è possibile eseguire altri calcoli matematici con i risultati restituiti dalla funzione CERCA.VERT. Ecco alcuni esempi di formule:

Se aggiungiamo le formule di cui sopra alla tabella di “riepilogo delle vendite” dall’esempio precedente, il risultato sarà simile a questo:

CERCA e SOMMA – Cercare valori corrispondenti in una matrice e sommarli

Nel caso in cui il parametro di ricerca fosse una matrice piuttosto che un singolo valore, la funzione CERCA.VERT non è di alcuna utilità perché non può ricercare matrici di dati. In questo caso, è possibile utilizzare la funzione CERCA di Excel che è analoga a CERCA.VERT ma funziona sia con le matrici che con i valori singoli.

Consideriamo il seguente esempio, in modo da poter capire meglio di cosa stiamo parlando. Supponiamo di avere una tabella che elenca i nomi dei clienti, i prodotti acquistati e le quantità (tabella principale). Avete anche una seconda tabella che contiene i prezzi dei prodotti (tabella di ricerca). Il vostro compito è quello di fare una formula che trovi il totale di tutti gli ordini effettuati da un determinato cliente.

Come ricorderete, non è possibile utilizzare la funzione CERCA.VERT, dal momento che presenta più istanze del valore di ricerca (matrice di dati). utilizziamo invece una combinazione delle funzioni SOMMA e CERCA come questa:

=SOMMA(CERCA($C$2:$C$10; 'Tabella ricerca'!$A$2:$A$10; 'Tabella ricerca'!$B$2:$B$10)*$D$2:$D$10*($B$2:$B$10=$G$1))

Poiché si tratta di una formula matriciale, ricordatevi di premere Ctrl + Maiusc + Invio per inserirla.

E ora, analizziamo gli ingredienti della formula in modo che possiate capire come lavori ciascuna delle funzioni e possiate modificarle per i vostri dati.

Metteremo da parte la funzione SOMMA per un po’, perché il suo scopo è ovvio, e ci concentreremo sui 3 componenti che si moltiplicano:

Poiché la nostra formula è una formula matriciale, essa itera il processo sopra descritto per ogni valore nella matrice di ricerca. E, infine, la funzione SOMMA somma i prodotti di tutte le moltiplicazioni. Niente affatto difficile, eh?

CERCA.VERT e SOMMA.SE – Trovare e sommare i valori che soddisfano determinati criteri

La funzione SOMMA.SE di Excel è simile a SOMMA e anch’essa viene utilizzata per sommare valori. La differenza è che la funzione SOMMA.SE somma solo quei valori che soddisfano i criteri specificati. Per esempio, una semplice formula =SOMMA.SE(A2:A10; “>10”) somma i valori delle celle da A2 a A10 che sono più grandi di 10.

Fin qui è tutto semplice, giusto? E ora prendiamo in considerazione uno scenario un po’ più complesso. Supponete di avere una tabella che elenchi i nomi dei venditori della vostra azienda assieme ai loro numeri di matricola (tabella di ricerca). Avete poi un’altra tabella che contiene le stesse matricole con associati dei dati di vendita (tabella principale). Il vostro compito è quello di trovare il totale delle vendite effettuate da un dato venditore in base alla sua matricola. Oltre a questo, ci sono 2 fattori che complicano le cose:

‎E ora, creiamo una formula che, in primo luogo, trovi tutte le vendite effettuate da un certo venditore e, in secondo luogo, sommi i valori trovati.

‎Prima di iniziare con la formula, permettetemi di ricordarvi la sintassi della funzione SOMMA.SE:

SOMMA.SE(intervallo; criterio; [int_somma])‎

Tenendo le informazioni di cui sopra in mente, definiamo i 3 parametri per la nostra funzione SOMMA.SE. Come ricorderete, vogliamo sommare tutte le vendite effettuate da una data persona il cui nome viene immesso nella cella F2 nella tabella principale (vedi immagine sopra).

Ora, non vi resta che assemblare le parti della formula e la formula SOMMA.SE + CERCA.VERT è pronta:

=SOMMA.SE(Tabella_principale[Matr. Venditore]; CERCA.VERT($F$2;Tabella_ricerca; 2; FALSO); Tabella_principale[Vendite])