Téléchargez l'exemple de classeur
Ce didacticiel montrera comment utiliser la fonction SUMIFS pour additionner les données correspondant à des cellules vides ou vides dans Excel et Google Sheets.
Somme si vide
Tout d'abord, nous montrerons comment additionner des lignes avec des cellules vides.
La fonction SUMIFS additionne les données qui répondent à certains critères.
Nous pouvons utiliser la fonction SUMIFS pour additionner tous Notes pour Joueurs sans noms dans l'exemple ci-dessous.
| 1 | =SUMIFS(C3:C8,B3:B8,"") |
Nous utilisons des guillemets ("") pour représenter une cellule vide dans Excel. Notre exemple ignore Joueurs A, B, C et D et additionne les scores pour inconnu Joueurs.
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 :
Pour traiter une cellule contenant uniquement des espaces comme s'il s'agissait d'une cellule vide, nous pouvons ajouter une colonne d'aide avec la fonction TRIM pour supprimer les espaces supplémentaires de la valeur de chaque cellule :
| 1 | = COUPER(B3) |
Nous appliquons la fonction SUMIFS à la colonne d'aide, et elle calcule maintenant la somme avec précision.
| 1 | =SUMIFS(E3:E9,D3:D9,"") |
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
Si une colonne d'aide ne convient pas à vos besoins, vous pouvez utiliser la fonction SUMPRODUCT en combinaison avec les fonctions LEN et TRIM pour additionner les lignes vides.
| 1 | =SUMPRODUCT(--(LEN(TRIM(B3:B9))=0),D3:D9) |
Dans cet exemple, nous utilisons la fonction SOMMEPROD pour effectuer un calcul compliqué de « 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 avec 0 caractères sont remplacés par TRUE :
| 1 | =SOMMEPROD(--({FAUX; FAUX; VRAI; FAUX; VRAI; FAUX; VRAI}),{25; 10; 15; 5; 8; 17; 50) |
Ensuite, les doubles tirets (-) convertissent les valeurs VRAI et FAUX en 1 et 0 :
| 1 | =SOMMEPROD({0; 0; 1; 0; 1; 0; 1},{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 sont vides ou composés uniquement d'espaces :
| 1 | =SOMMEPROD({0; 0; 15; 0; 8; 0; 50) |
Enfin, les nombres du tableau sont additionnés :
| 1 | =73 |
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 vide dans Google Sheets
Ces formules fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
