Note: The other languages of the website are Google-translated. Back to English
English English

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

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:

=SUMIF(range,"*text*",sum_range)
  • 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:

=SUMIF(range,"*"&cell&"*",sum_range)
  • 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 Entra tasto per ottenere il risultato:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=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 è:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • 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 Entra chiave per restituire il risultato:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=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 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.

I migliori strumenti per la produttività in ufficio

Kutools per Excel: ti aiuta a distinguerti dalla folla

Vorresti completare il tuo lavoro quotidiano in modo rapido e perfetto? Kutools per Excel offre 300 potenti funzionalità avanzate (Combina cartelle di lavoro, somma per colore, contenuto di celle divise, converti data e così via ...) e risparmia l'80% di tempo per te.

  • Progettato per 1500 scenari di lavoro, ti aiuta a risolvere l'80% dei problemi di Excel.
  • Riduci migliaia di clic della tastiera e del mouse ogni giorno, allevia i tuoi occhi e le tue mani stanche.
  • Diventa un esperto di Excel in 3 minuti. Non è più necessario ricordare formule dolorose e codici VBA.
  • Prova gratuita illimitata di 30 giorni. Garanzia di rimborso di 60 giorni. Aggiornamento gratuito e supporto per 2 anni.
Nastro di Excel (con Kutools per Excel installato)

Scheda Office: 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, Firefox e il nuovo Internet Explorer.
Screenshot di Excel (con la scheda Office installata)
Commenti (0)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Non ci sono ancora commenti pubblicati qui
Lasciate i vostri commenti
Pubblicazione come ospite
×
Valuta questo post:
0   Personaggi
Posizioni suggerite