#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.

Base dati per la ricerca e la somma dei valori corrispondenti ad un criterio

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))
ATTENZIONE
Dal momento che stiamo creando una formula matriciale, assicuratevi di premere Ctrl + Maiusc + Invio invece di un semplice Invio. Quando si esegue questa operazione, Microsoft Excel racchiude la formula tra parentesi graffe come in questo caso:
=SOMMA(CERCA.VERT(B2; ‘Vendite mensili’!$A$2:$M$9; {2.3.4.5.6.7.8.9.10.11.12.13}; FALSO))
Se si preme il tasto Invio, come fate di solito, solo il primo valore della matrice sarà processato, il che produrrà risultati non corretti.

La formula restituisce la somma dei valori della colonne B - M del foglio Vendite mensili

SUGGERIMENTI
Potreste essere curiosi di sapere perché, nella schermata qua sopra, la formula mostri [@Prodotto] come valore di ricerca. Questo avviene perché ho convertito i miei dati in tabella (scheda INSERISCI > Tabella). Trovo molto comodo lavorare con le tabelle di Excel piuttosto che con i semplici intervalli. Ad esempio, quando si digita una formula in una cella, Excel la copia automaticamente lungo l’intera colonna e in questo modo si risparmia qualche secondo prezioso 🙂

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.

LEGGI:  #Excel - Come trovare il valore massimo che soddisfi criteri multipli

È 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:

Operazione Esempio di formula Descrizione
Calcolare la media {=MEDIA(CERCA.VERT(A2; ‘Tabella ricerca’!$A$2:$D$10; {2.3.4}; FALSO))} La formula cerca il valore della cella A2 in ‘Tabella ricerca’ e calcola la media dei valori delle colonne B, C e D nella stessa riga.
Trovare il valore massimo {=MAX(CERCA.VERT(A2; ‘Tabella ricerca’!$A$2:$D$10; {2.3.4}; FALSO))} La formula cerca il valore della cella A2 in ‘Tabella ricerca’ e trova il valore massimo delle colonne B, C e D nella stessa riga.
Trovare il valore minimo {=MIN(CERCA.VERT(A2; ‘Tabella ricerca’!$A$2:$D$10; {2.3.4}; FALSO))} La formula cerca il valore della cella A2 in ‘Tabella ricerca’ e trova il valore minimo delle colonne B, C e D nella stessa riga.
Calcolare la % di un importo {=0,3*SOMMA(CERCA.VERT(A2; ‘Tabella ricerca’!$A$2:$D$10; {2.3.4}; FALSO))} La formula cerca il valore della cella A2 in ‘Tabella ricerca’, somma i valori delle colonne B, C e D nella stessa riga, e quindi calcola il 30% dell’importo.
ATTENZIONE
Dal momento che tutte le formule di cui sopra sono formule matriciali, ricordatevi di premere Ctrl + Maiusc + Invio per inserirle correttamente in una cella.

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

Usare CERCA.VERT con altre funzioni di Excel

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.

LEGGI:  #Excel - Come calcolare una media escludendo il valore minimo e il valore massimo

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.

Trovare il valore totale di tutti i prodotti acquistati da un dato 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.

Una combinazione delle funzioni SOMMA e CERCA che trovano il valore totale dei prodotti acquistati da un certo cliente

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:

  • CERCA($C$2:$C$10; ‘Tabella ricerca’!$A$2:$A$10; ‘Tabella ricerca’!$B$2:$B$10)
    Questa funzione CERCA prende i prodotti di cui alla colonna C della tabella ordini, e restituisce il prezzo corrispondente dalla colonna B nella tabella di ricerca.
  • $D$2:$D$10
    Questo componente restituisce le quantità di ogni prodotto acquistato da ciascun cliente, elencate alla colonna D della tabella ordini. Moltiplicate per il prezzo, restituito dalla funzione CERCA di cui sopra, vi dà il costo di ogni prodotto acquistato.
  • $B$2:$B$10=$G$1
    Questa formula mette a confronto i nomi dei clienti nella colonna B con il nome presente in cella G1. Se viene trovata una corrispondenza, restituisce “1”, altrimenti “0”. Si utilizza semplicemente per “eliminare” i nomi dei clienti diversi dal nome in cella G1, dal momento che tutti noi sappiamo che ogni numero moltiplicato per zero è zero.

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?

INFO
Perché la formula di ricerca funzioni correttamente è necessario ordinare la colonna di ricerca nella tabella di ricerca in ordine crescente (da A a Z). Se l’ordinamento non è possibile per i vostri dati, occorre utilizzare una formula diversa che fa uso delle funzioni SOMMA e MATR.TRASPOSTA:
{=SOMMA($D$2:$D$10*SE($C$2:$C$10=MATR.TRASPOSTA(‘Tabella ricerca’!$A$2:$A$10); MATR.TRASPOSTA(‘Tabella ricerca’!$B$2:$B$10); 0)*($B$2:$B$10=$G$1))}
Il nocciolo sta nell’usare la funzione MATR.TRASPOSTA per trasformare la tabella di ricerca da verticale a orizzontale. Di conseguenza, possiamo moltiplicare la matrice verticale (tabella ordini) e la matrice orizzontale (tabella di ricerca) per creare una formula matriciale bi-dimensionale.

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.

LEGGI:  #Excel - Come calcolare le medie in Excel - Esempi di formule

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:

  • La tabella principale contiene più elementi per la stessa matricola in ordine casuale
  • Non è possibile aggiungere la colonna “Nome venditori” alla tabella principale.

Cerca e somma i valori che soddisfano i criteri

‎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])‎
  • intervallo: ‎questo argomento è auto-esplicativo, è semplicemente un intervallo di celle che si desidera valutare con i criteri specificati‎
  • criterio: ‎la condizione che specifica alla formula i valori da sommare. Può essere fornito sotto forma di un numero, di un riferimento di cella, di un’espressione o di un’altra funzione di Excel‎
  • int_somma: ‎questo parametro è opzionale, ma molto importante per noi. Definisce l’intervallo in cui sono presenti le celle da sommare. Se omesso, Excel somma i valori delle celle specificate nell’argomento intervallo (1‎‎°‎‎ argomento).‎

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).

  • intervallo: ‎ dal momento che siamo alla ricerca della matricola del venditore, il parametro intervallo per la nostra funzione SOMMA.SE è la colonna B della tabella principale. Quindi, è possibile immettere l’intervallo b:b, o se convertiamo i dati in una tabella, possiamo utilizzare il nome della colonna: Tabella_principale[Matr. Venditore]
  • criterio: poiché abbiamo i nomi degli addetti alle vendite in un’altra tabella (tabella di ricerca), dobbiamo utilizzare la funzione CERCA.VERT per trovare la matricola corrispondente a una data persona. Il nome della persona è scritto nella cella F2 della tabella principale, quindi lo cerchiamo usando la formula: CERCA.VERT($F$2; Tabella_ricerca; 2; FALSO)
    Naturalmente, è possibile immettere il nome direttamente nei criteri di ricerca della funzione CERCA.VERT, ma utilizzare un riferimento di cella assoluto è un approccio migliore, perché questo crea una formula universale che funziona con qualsiasi nome inseriremo in quella cella.‎
  • int_somma: questa è la parte più facile. Dal momento che gli importi delle vendite sono in colonna C denominata “Vendite”, mettiamo semplicemente Tabella_principale[Vendite]

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])

Formula SOMMA.SE + CEERCA.VERT che cerca e somma i valori che soddisfano il criterio che fornite

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti