Nous pouvons utiliser Sumif dans des cellules fusionnées dans Google Sheets. Découvrez-en plus sur ce type de somme conditionnelle ci-dessous.
Contrairement aux applications de base de données, nous pouvons fusionner des cellules dans des feuilles de calcul. L’option correspondante se trouve dans le menu Format de Google Docs Sheets.
Lorsque nous fusionnons des cellules dans notre feuille de calcul, nous devons comprendre que cela se fait au détriment de la « structure » de base de données de nos données saisies.
La fusion des cellules peut entraîner une différence importante (un impact négatif) dans notre manipulation des données.
Dans une plage fusionnée, il peut être difficile ou impossible d’utiliser des fonctions de base de données et d’autres fonctions telles que Sumif, Filter, Query, etc.
Pour que vous compreniez le problème, permettez-moi d’utiliser les données ci-dessous sur les fruits et une formule Sumif de base dans la cellule F3.
=SUMIF(B2:B10,E3,C2:C10)
La formule Sumif dans les cellules fusionnées renvoie 10 au lieu de 100 (10+20+30+40) pour le critère « GRENADE » (formule dans la cellule F3 et critère dans la cellule E3).
Si vous démergez les cellules et que vous rendez les données similaires à une table de base de données, la formule renverrait correctement 100. Voyez aussi cet exemple.
Mais si vous souhaitez conserver les cellules fusionnées et utiliser la fonction Sumif, vous pouvez trouver la formule correcte ci-dessous.
Sumif dans des cellules fusionnées dans Google Sheets
Lorsque vous fusionnez un ensemble de cellules, par exemple A1:A10, et que vous saisissez une valeur dans cette plage fusionnée, la valeur que vous avez saisie sera enregistrée dans la cellule A1, et non dans toutes les cellules de la plage A1:A10.
Par exemple, sélectionnez la plage A1:A10 et cliquez sur Format > Fusionner les cellules > Tout fusionner. Ensuite, saisissez « UK » dans cette plage fusionnée.
Saisissez =A1 dans n’importe quelle cellule vide. La formule renverra la valeur « UK ».
Remplacez A1 par A2 dans la formule et vous verrez que la formule renvoie maintenant une valeur vide. Cela sera également le cas pour les autres cellules de cette plage fusionnée.
Cela signifie que, dans ce cas particulier, Sheets enregistre la valeur « UK » uniquement dans la cellule A1, et non dans la plage A1:A10.
Ainsi, pour utiliser Sumif dans des cellules fusionnées dans Google Sheets, nous devons remplacer la « plage » par une plage virtuelle.
Le rôle de la plage virtuelle est de remplir toutes les cellules fusionnées avec des valeurs.
Cela signifie que, selon nos données sur les fruits, remplissez B2:B3 avec « POMME », B4:B7 avec « GRENADE » et B8:B10 avec « BANANE » (faites défiler vers le haut et consultez la toute première capture d’écran).
Commençons par coder cette formule.
« Plage » virtuelle
En fait, pour cela, nous pouvons utiliser différentes variations d’une formule. Ici, j’utilise la plus simple possible.
Tout d’abord, je vais utiliser deux plages de colonnes auxiliaires que nous pourrons supprimer ultérieurement.
Plage auxiliaire 1
Dans la cellule H2, insérez la formule suivante (appelons-la helper_formula_1) pour renvoyer les numéros de ligne des cellules contenant des valeurs dans la plage B2:B10.
=ArrayFormula(if(len(B2:B10),row(B2:B10)))
La formule renverra le résultat suivant.
[Insérer l’image ici]
De cela, que pouvons-nous comprendre ?
Nous pouvons comprendre que B2 contient « POMME », B4 contient « GRENADE » et B8 contient « BANANE ». Toutes les autres cellules de la plage B2:B10 sont vides.
Maintenant, passons à l’étape clé sous « Plage auxiliaire 2 » pour effectuer la formule Sumif dans des cellules fusionnées dans Google Sheets.
Plage auxiliaire 2
Dans la cellule I2, insérez la formule de recherche suivante (appelons-la helper_formula_2).
=ArrayFormula(lookup(row(B2:B10),H2:H10,B2:B10))
La fonction Recherche renseignera le résultat suivant dans la plage I2:I10.
[Insérer l’image ici]
Il est important de comprendre la formule ci-dessus pour comprendre comment utiliser Sumif dans des cellules fusionnées dans Google Sheets.
Pour comprendre la formule ci-dessus, référerez d’abord à la syntaxe de recherche que nous avons utilisée dans la formule Recherche ci-dessus (en tant que note annexe, la fonction Recherche a deux types de syntaxes).
Syntaxe : RECHERCHE(clé_recherche; plage_recherche; plage_resultat)
La clé_recherche sont les numéros de ligne de 2 à 10, la plage_recherche est le résultat de helper_formula_1 dans H2:H10 et la plage_resultat est B2:B10.
[Insérer l’image ici]
Règle de recherche : Si la clé_recherche n’est pas trouvée dans la plage_recherche, l’élément utilisé dans la recherche sera la valeur immédiatement inférieure dans la plage fournie. Vous ne comprenez pas ?
Par exemple, la clé_recherche 3 (numéro de ligne 3) n’est pas présente dans H2:H10. La recherche prendra donc 2 (immédiatement inférieur dans la plage_recherche) comme clé_recherche.
Dans le cas des clés_recherche 5, 6 et 7, la formule les considérera comme 4. Dans le cas des clés_recherche 9 et 10, la formule les considérera comme 8.
Ainsi, même si la clé_recherche est les numéros de ligne 2 à 10, la formule les lirait comme suit.
[Insérer l’image ici]
Les valeurs correspondantes de B2:B10 seront renvoyées.
Exemple de formule pour Sumif dans des cellules fusionnées
Nous sommes presque prêts à utiliser Sumif dans des cellules fusionnées dans Google Sheets.
Au début de cet article, j’ai donné une formule Sumif (dans la cellule F3) qui renvoie un résultat incorrect en raison des cellules fusionnées. Voici à nouveau cette formule.
=SUMIF(B2:B10,E3,C2:C10)
Dans cette formule, nous devons remplacer B2:B10 par les deux formules auxiliaires que nous avons écrites précédemment.
Avant de les remplacer, nous devons d’abord combiner les deux formules auxiliaires, ce qui est assez simple.
Remplacez simplement H2:H10 dans helper_formula_2 par helper_formula_1 comme suit. C’est tout ce que vous devez faire.
=ArrayFormula(lookup(row(B2:B10),if(len(B2:B10),row(B2:B10)),B2:B10))
Nous avons donc combiné les deux formules auxiliaires en une seule. Maintenant, nous pouvons utiliser la formule combinée pour remplacer B2:B10 dans la formule F3 comme suit.
=SUMIF(ArrayFormula(lookup(row(B2:B10),if(len(B2:B10),row(B2:B10)),B2:B10)),E3,C2:C10)
[Insérer l’image ici]
Ce qui précède est un exemple de Sumif dans des cellules fusionnées dans Google Sheets.
Ressources :
- Copier-coller des cellules fusionnées sans lignes vides dans Google Sheets.
- Comment trouver les adresses des cellules fusionnées dans Google Sheets.
