Somma se le celle contengono testo specifico in un'altra colonna
Questo tutorial ti mostrerà come sommare i valori se le celle contengono testo specifico o parziale in un'altra colonna. Prendi come esempio l'intervallo di dati seguente, per ottenere gli importi totali dei prodotti contenenti il testo "T-Shirt", sia la funzione SOMMA.SE che la funzione SOMMA.PRODOTTO possono gestire questa attività in Excel.
- Somma i valori se la cella contiene testo specifico o parziale con la funzione SOMMA.SE
- Somma i valori se la cella contiene testo specifico o parziale con la funzione SUMPRODUCT
Somma i valori se la cella contiene testo specifico o parziale con la funzione SOMMA.SE
Per sommare i valori se una cella contiene un testo specifico in un'altra colonna, puoi utilizzare la funzione SOMMA.SE con un carattere jolly (*), le sintassi generiche sono:
Formula generica con testo codificato:
- range: l'intervallo di dati che si desidera valutare utilizzando i criteri;
- *text*: i criteri in base ai quali si desidera sommare i valori. Qui, il carattere jolly * viene utilizzato per trovare un numero qualsiasi di caratteri, per abbinare tutti gli elementi che contengono un testo specifico, inserire il testo tra i due caratteri *. (Nota che devi racchiudere il testo e il carattere jolly tra virgolette doppie.)
- sum_range: l'intervallo di celle con valori numerici corrispondenti che si desidera sommare.
Formula generica con riferimento di cella:
- range: l'intervallo di dati che si desidera valutare utilizzando i criteri;
- "*"&cell&"*": i criteri in base ai quali si desidera sommare i valori;
- *: Il carattere jolly che trova un numero qualsiasi di caratteri.
- cella: la cella contiene il testo specifico da cercare.
- &: questo operatore di concatenazione (&) viene utilizzato per unire un riferimento di cella con gli asterischi.
- sum_range: l'intervallo di celle con valori numerici corrispondenti che si desidera sommare.
Dopo aver conosciuto il funzionamento di base della funzione, utilizzare una delle seguenti formule necessarie, quindi premere Enter tasto per ottenere il risultato:
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
Note:: questa funzione SOMMA.SE non fa distinzione tra maiuscole e minuscole.
Somma i valori se la cella contiene testo specifico o parziale con la funzione SUMPRODUCT
La funzione SUMPRODUCT può anche aiutarti a completare questo lavoro senza utilizzare alcun carattere jolly. Qui, dovresti racchiudere le funzioni ISNUMBER e SEARCH all'interno di una funzione SUMPRODUCT, la sintassi generica è:
- sum_range: l'intervallo di celle con valori numerici corrispondenti che si desidera sommare;
- criteria: i criteri in base ai quali si desidera sommare i valori. Può essere un riferimento di cella o un testo specifico che hai definito;
- range: l'intervallo di dati che si desidera valutare utilizzando i criteri;
Si prega di utilizzare una delle formule seguenti in una cella vuota e premere Enter chiave per restituire il risultato:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
Spiegazione di questa formula:
=SOMMA.PRODOTTO($B$2:$B$12*(VAL.NUMERO(RICERCA("Maglietta",$A$2:$A$12)))))
- CERCA("Maglietta",$A$2:$A$12): La funzione SEARCH restituisce la posizione del testo specifico "T-Shirt" dall'intervallo di dati A2:A12, quindi otterrai un array come questo: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#VALORE!;#VALORE!;#VALORE!;#VALORE!;7}.
- ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Questa funzione VAL.NUMERO viene utilizzata per verificare i valori numerici e restituisce un nuovo array: {VERO;FALSO;FALSO;VERO;FALSO;VERO;FALSO;FALSO;FALSO;FALSO;VERO}.
- $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: per moltiplicare questi due array, l'operazione matematica forza i valori TRUE e FALSE su 1 e 0. Quindi, la moltiplicazione dei due array sarà così: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Infine, la funzione SUMPRODUCT somma tutti i valori nell'array per ottenere il risultato:1605.
Funzione relativa utilizzata:
- SUMIF:
- La funzione SOMMA.SE può aiutare a sommare le celle in base a un criterio.
- MATR.SOMMA.PRODOTTO:
- La funzione SUMPRODUCT può essere utilizzata per moltiplicare due o più colonne o matrici insieme e quindi ottenere la somma dei prodotti.
- NUMERO IS:
- La funzione VAL.NUMERO di Excel restituisce VERO quando una cella contiene un numero e FALSO in caso contrario.
- RICERCA:
- La funzione RICERCA può aiutarti a trovare la posizione di un carattere specifico o di una sottostringa dalla stringa di testo data
Altri articoli:
- Somma se inizia con o finisce con testo o caratteri specifici
- Per sommare i valori se le celle corrispondenti iniziano o terminano con un valore specifico, puoi applicare la funzione SOMMA.SE con un carattere jolly (*) per estrarlo. Questo articolo introdurrà come utilizzare la formula in dettaglio.
- Somma i valori N più piccoli o più bassi
- In Excel, è facile per noi sommare un intervallo di celle utilizzando la funzione SOMMA. A volte, potrebbe essere necessario sommare i numeri 3, 5 o n più piccoli o inferiori in un intervallo di dati come mostrato nell'immagine sottostante. In questo caso, SUMPRODUCT insieme alla funzione PICCOLO possono aiutarti a risolvere questo problema in Excel.
- Somma i valori N più piccoli o più bassi in base a criteri
- Nel tutorial precedente, abbiamo discusso come sommare i valori n più piccoli in un intervallo di dati. In questo articolo, eseguiremo un'ulteriore operazione avanzata: sommare i valori n più bassi in base a uno o più criteri in Excel.
I migliori strumenti per la produttività in ufficio
Kutools for Excel - Ti aiuta a distinguerti dalla massa
Kutools for Excel Vanta oltre 300 funzionalità, Garantirti che ciò di cui hai bisogno sia a portata di clic...
Office Tab - Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Un secondo per passare da una dozzina di documenti aperti all'altra!
- Riduci ogni giorno centinaia di clic del mouse, dì addio alla mano del mouse.
- Aumenta la produttività del 50% durante la visualizzazione e la modifica di più documenti.
- Porta schede efficienti in Office (incluso Excel), proprio come Chrome, Edge e Firefox.
