Téléchargez l'exemple de classeur
Ce didacticiel montrera comment utiliser la fonction SUMIFS pour additionner les données liées aux cellules non vides ou non vides dans Excel et Google Sheets.
Somme si non vide
Tout d'abord, nous montrerons comment additionner des données relatives à des cellules non vides.
Nous pouvons utiliser la fonction SUMIFS pour additionner tous Notes pour Joueurs avec des noms non vides.
| 1 | =SUMIFS(C3:C8,B3:B8,"") |
Pour additionner les lignes avec des cellules non vides, nous excluons Notes avec manquant Joueur noms. Nous utilisons le critère "pas égal à blanc" ("") à l'intérieur de la fonction SUMIFS.
Traiter les espaces comme des cellules vides - Avec colonne d'assistance
Vous devez être prudent lorsque vous interagissez avec des cellules vides dans Excel. Les cellules peuvent vous sembler vides, mais Excel ne les traitera pas comme vides. Cela peut se produire si la cellule contient des espaces, des sauts de ligne ou d'autres caractères invisibles. Il s'agit d'un problème courant lors de l'importation de données dans Excel à partir d'autres sources.
Si nous devons traiter des cellules qui ne contiennent que des espaces de la même manière que si elles étaient vides, la formule de l'exemple précédent ne fonctionnera pas. Remarquez comment la formule SUMIFS ne considère pas la cellule B9 ci-dessous ("") comme vide :
| 1 | =SUMIFS(D3:D9,B3:B9,"") |
Pour traiter une cellule contenant uniquement des espaces comme s'il s'agissait d'une cellule vide, nous pouvons ajouter une colonne d'aide à l'aide des fonctions LEN et TRIM pour identifier Joueurs avec des noms.
La fonction TRIM supprime les espaces supplémentaires au début et à la fin de la valeur de chaque cellule et la fonction LEN compte ensuite le nombre de caractères restants. Si le résultat de la fonction LEN est 0, alors le Joueur le nom doit être vide ou composé uniquement d'espaces :
| 1 | =LEN(COUPER(B3)) |
Nous appliquons la fonction SUMIFS à la colonne d'aide (Sommation si supérieure à 0), et elle calcule maintenant la somme avec précision.
| 1 | =SUMIFS(E3:E9,D3:D9,">0") |
La colonne d'aide est facile à créer et à lire, mais vous souhaiterez peut-être avoir une seule formule pour accomplir la tâche. Ceci est couvert dans la section suivante.
Traiter les espaces comme des cellules vides - sans colonne d'assistance
S'il est nécessaire de traiter des cellules contenant uniquement des espaces de la même manière que si elles étaient vides, mais que l'utilisation d'une colonne d'aide n'est pas appropriée, nous pouvons utiliser la fonction SUMPRODUCT en combinaison avec les fonctions LEN et TRIM pour additionner les données relatives aux cellules contenant non vide Joueur noms:
| 1 | =SUMPRODUCT(--(LEN(TRIM(B3:B9))>0),D3:D9) |
Dans cet exemple, nous utilisons la fonction SOMMEPROD pour effectuer des calculs compliqués « somme si ». Parcourons la formule.
Voici notre formule finale :
| 1 | =SUMPRODUCT(--(LEN(TRIM(B3:B9))>0),D3:D9) |
Tout d'abord, la fonction SOMMEPROD répertorie le tableau de valeurs des deux plages de cellules :
| 1 | =SUMPRODUCT(--(LEN(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))>0),{25; 10; 15; 5 ; 8 ; 17 ; 50) |
Ensuite, la fonction TRIM supprime les espaces de début et de fin de Joueur noms:
| 1 | =SOMMEPROD(--(LEN({"A"; "B"; ""; "C"; ""; "XX"; ""})>0),{25; 10; 15; 5; 8; 17 ; 50) |
La fonction LEN calcule les longueurs des Joueur noms:
| 1 | =SOMMEPROD(--({1; 1; 0; 1; 0; 2; 0}>0),{25; 10; 15; 5; 8; 17; 50) |
Avec le test logique (>0), tout rogné Joueur les noms de plus de 0 caractères sont remplacés par TRUE :
| 1 | =SOMMEPROD(--({VRAI; VRAI ; FAUX; VRAI; FAUX; VRAI; FAUX}),{25; 10; 15; 5; 8; 17; 50) |
Ensuite, les doubles tirets (-) convertissent les valeurs VRAI et FAUX en 1 et 0 :
| 1 | =SOMMEPROD({1; 1; 0; 1; 0; 1; 0},{25; 10; 15; 5; 8; 17; 50) |
La fonction SOMMEPROD multiplie ensuite chaque paire d'entrées dans les tableaux pour produire un tableau de Notes seulement pour Joueur les noms qui ne sont pas vides ou qui ne sont pas composés uniquement d'espaces :
| 1 | =SOMMEPROD({25; 10; 0; 5; 0; 17; 0) |
Enfin, les nombres du tableau sont additionnés
| 1 | =57 |
Plus de détails sur l'utilisation des instructions booléennes et de la commande « - » dans une fonction SOMMEPROD peuvent être trouvés ici
Somme si non vide dans Google Sheets
Ces formules fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
