Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come utilizzare la funzione SUMIFS per sommare i dati corrispondenti a celle vuote o vuote in Excel e Fogli Google.
Somma se vuoto
Innanzitutto, dimostreremo come sommare le righe con celle vuote.
La funzione SUMIFS somma i dati che soddisfano determinati criteri.
Possiamo usare la funzione SUMIFS per sommare tutto punteggi per Giocatori senza nomi nell'esempio seguente.
| 1 | =SOMMA.SE(C3:C8;B3:B8,"") |
Usiamo le virgolette ("") per rappresentare una cella vuota in Excel. Il nostro esempio ignora Giocatori A, B, C e D e somma i punteggi per sconosciuto Giocatori.
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:
Per trattare una cella contenente solo spazi come se fosse una cella vuota, possiamo aggiungere una colonna di supporto con la funzione TRIM per rimuovere gli spazi extra dal valore di ogni cella:
| 1 | =TRIM(B3) |
Applichiamo la funzione SUMIFS alla colonna helper e ora calcola la somma in modo accurato.
| 1 | =SOMMA.SE(E3:E9,D3:D9,"") |
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 una colonna di supporto non è appropriata per le tue esigenze, puoi utilizzare la funzione SUMPRODUCT in combinazione con le funzioni LEN e TRIM per sommare righe vuote.
| 1 | =SOMMA.PRODOTTO(--(LEN(TRIM(B3:B9))=0),D3:D9) |
In questo esempio, utilizziamo la funzione SUMPRODUCT per eseguire un complicato calcolo "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 0 caratteri vengono modificati in TRUE:
| 1 | =SOMMA.PRODOTTO(--({FALSO; FALSO; VERO; FALSO; VERO; FALSO; VERO}),{25; 10; 15; 5; 8; 17; 50) |
Successivamente i doppi trattini (-) convertono i valori VERO e FALSO in 1 e 0:
| 1 | =SOMMA.PRODOTTO({0; 0; 1; 0; 1; 0; 1},{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 vuoti o composti solo da spazi:
| 1 | =SOMMA.PRODOTTO({0; 0; 15; 0; 8; 0; 50) |
Infine, i numeri nell'array vengono sommati insieme:
| 1 | =73 |
Maggiori dettagli sull'utilizzo delle istruzioni booleane e del comando "-" in una funzione SUMPRODUCT sono disponibili qui.
Somma se vuoto in Fogli Google
Queste formule funzionano esattamente allo stesso modo in Fogli Google come in Excel.
