Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come utilizzare la funzione SUMIFS per sommare i dati relativi a celle non vuote o non vuote in Excel e Fogli Google.
Somma se non vuoto
Innanzitutto, dimostreremo come sommare i dati relativi a celle non vuote.
Possiamo usare la funzione SUMIFS per sommare tutto punteggi per Giocatori con nomi non vuoti.
| 1 | =SOMMA.SE(C3:C8;B3:B8,"") |
Per sommare righe con celle non vuote, escludiamo punteggi con mancante Giocatore nomi. Usiamo i criteri "non uguale a vuoto" ("") all'interno della funzione SUMIFS.
Trattare gli spazi come celle vuote - Con colonna helper
Devi stare attento quando interagisci con celle vuote in Excel. Le celle possono apparire vuote per te, ma Excel non le tratterà come vuote. Ciò può verificarsi se la cella contiene spazi, interruzioni di riga o altri caratteri invisibili. Questo è un problema comune quando si importano dati in Excel da altre fonti.
Se abbiamo bisogno di trattare le celle che contengono solo spazi allo stesso modo come se fossero vuote, la formula nell'esempio precedente non funzionerà. Nota come la formula SUMIFS non considera la cella B9 di seguito (" ") vuota:
| 1 | =SOMMA.SE(D3:D9,B3:B9,"") |
Per trattare una cella contenente solo spazi come se fosse una cella vuota, possiamo aggiungere una colonna di supporto utilizzando le funzioni LEN e TRIM per identificare Giocatori con nomi.
La funzione TRIM rimuove gli spazi extra dall'inizio e dalla fine del valore di ogni cella e la funzione LEN conta quindi il numero di caratteri rimanenti. Se il risultato della funzione LEN è 0, allora il Giocatore il nome doveva essere vuoto o composto solo da spazi:
| 1 | =LUNGHEZZA(TRIM(B3)) |
Applichiamo la funzione SUMIFS alla colonna helper (Summing se maggiore di 0) e ora calcola la somma in modo accurato.
| 1 | =SOMMA.SE(E3:E9,D3:D9,">0") |
La colonna helper è facile da creare e da leggere, ma potresti voler avere un'unica formula per svolgere l'attività. Questo è trattato nella sezione successiva.
Trattare gli spazi come celle vuote - Senza colonna di supporto
Se è necessario trattare qualsiasi cella contenente solo spazi allo stesso modo come se fosse vuota, ma l'utilizzo di una colonna di supporto non è appropriato, è possibile utilizzare la funzione SUMPRODUCT in combinazione con le funzioni LEN e TRIM per sommare i dati relativi alle celle contenente non vuoto Giocatore nomi:
| 1 | =SOMMA.PRODOTTO(--(LEN(TRIM(B3:B9))>0),D3:D9) |
In questo esempio, usiamo la funzione SUMPRODUCT per eseguire complicati calcoli "somma se". Esaminiamo la formula.
Questa è la nostra formula finale:
| 1 | =SOMMA.PRODOTTO(--(LEN(TRIM(B3:B9))>0),D3:D9) |
Innanzitutto, la funzione SUMPRODUCT elenca l'array di valori dei due intervalli di celle:
| 1 | =SOMMA.PRODOTTO(--(LEN(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))>0),{25; 10; 15; 5 ; 8; 17; 50) |
Quindi, la funzione TRIM rimuove gli spazi iniziali e finali da Giocatore nomi:
| 1 | =SOMMA.PRODOTTO(--(LEN({"A"; "B"; ""; "C"; ""; "XX"; ""})>0),{25; 10; 15; 5; 8; 17; 50) |
La funzione LEN calcola le lunghezze del rifilato Giocatore nomi:
| 1 | =SOMMA.PRODOTTO(--({1; 1; 0; 1; 0; 2; 0}>0),{25; 10; 15; 5; 8; 17; 50) |
Con il test logico (>0), qualsiasi tagliato Giocatore i nomi con più di 0 caratteri vengono modificati in TRUE:
| 1 | =SOMMA.PRODOTTO(--({VERO; VERO; FALSO; VERO; FALSO; VERO; FALSO}),{25; 10; 15; 5; 8; 17; 50) |
Successivamente i doppi trattini (-) convertono i valori VERO e FALSO in 1 e 0:
| 1 | =SOMMA.PRODOTTO({1; 1; 0; 1; 0; 1; 0},{25; 10; 15; 5; 8; 17; 50) |
La funzione SUMPRODUCT quindi moltiplica ogni coppia di voci negli array per produrre un array di punteggi solo per Giocatore nomi non vuoti o non composti solo da spazi:
| 1 | =SOMMA.PRODOTTO({25; 10; 0; 5; 0; 17; 0) |
Infine, i numeri nell'array vengono sommati insieme
| 1 | =57 |
Maggiori dettagli sull'utilizzo delle istruzioni booleane e del comando "-" in una funzione SUMPRODUCT possono essere trovati qui
Somma se non è vuoto in Fogli Google
Queste formule funzionano esattamente allo stesso modo in Fogli Google come in Excel.
