Nous pouvons utiliser la fonction Décalage pour obtenir des plages dynamiques dans la formule SOMME.SI dans Google Sheets. Dans ce tutoriel, nous allons apprendre cette astuce.
Par plages dynamiques, j’entends la plage dans une formule qui s’adapte aux valeurs des lignes insérées ultérieurement, juste au-dessus ou en dessous de la plage de formule.
Cette fois, dans ce tutoriel avancé sur SOMME.SI, vous allez apprendre comment utiliser des plages dynamiques dans la formule SOMME.SI dans Google Sheets.
Comment appliquer des plages dynamiques dans la formule SOMME.SI dans Google Sheets
Suivez l’approche/les étapes ci-dessous pour apprendre cela.
- Données d’exemple.
- Formule SOMME.SI de base.
- Création d’une plage dynamique pour la formule SOMME.SI ci-dessus.
- Explication de la formule.
1. Données d’exemple
Voici mes données d’exemple pour apprendre les plages dynamiques dans une formule.
2. Formule SOMME.SI de base
= SOMME.SI(D2:D8, »Casque de sécurité »,H2:H8)
Dans cette formule, D2:D8 est la plage, « Casque de sécurité » est le critère et H2:H8 est la plage de somme.
Syntaxe : SOMME.SI(plage, critère, [plage_somme])
Résultat : 120.00
Maintenant, voyons ce qui se passe lorsque vous insérez de nouvelles lignes au-dessus et en dessous de nos plages sélectionnées dans SOMME.SI.
La formule change les plages de D2:D8 à D3:D9 et de H2:H8 à H3:H9. Elle exclut les lignes nouvellement ajoutées.
Essayez d’utiliser le symbole du dollar pour figer les lignes, ainsi que les colonnes. Mais cela n’affecte pas les lignes insérées.
3. Comment créer une plage dynamique pour la formule SOMME.SI ci-dessus ?
Nous pouvons utiliser des plages dynamiques basées sur Décalage dans SOMME.SI pour surmonter le problème mentionné ci-dessus dans Google Sheets.
Dans la formule SOMME.SI ci-dessus, au lieu de la plage D2:D8, nous pouvons utiliser la formule OFFSET suivante.
= décalage(D1,1,0):décalage(D9,-1,0)
Pour H2:H8, la plage de décalage alternative est :
= décalage(H1,1,0):décalage(H9,-1,0)
Maintenant, voyez comment j’applique des plages dynamiques dans la formule SOMME.SI dans Google Sheets. Voici cette formule.
= SOMME.SI(décalage(D1,1,0):décalage(D9,-1,0), »Casque de sécurité »,décalage(H1,1,0):décalage(H9,-1,0))
Si vous utilisez cette formule dans l’exemple précédent, vous n’avez pas besoin de modifier la plage de formule manuellement.
Lorsque vous insérez de nouvelles lignes juste au-dessus ou en dessous de la plage de formule, elles seront automatiquement incluses dans le calcul.
4. Explication de la formule de la plage dynamique de SOMME.SI
Avant de passer à la partie d’explication de la formule, voyez ce qui se passe lorsque j’insère de nouvelles lignes au-dessus et en dessous de notre plage de données.
Ici, notre formule dynamique SOMME.SI utilisant la fonction Décalage renvoie le résultat correct dans la cellule H14, alors que la formule SOMME.SI habituelle dans la cellule H13 ignore les lignes insérées dans le calcul.
Il n’y a rien de spécial dans cette formule dynamique. Vous remplacez simplement la plage par le décalage.
Voyez la syntaxe du décalage.
= DÉCALAGE(référence_cible, lignes_décalage, colonnes_décalage
Encore une fois, voyez notre formule SOMME.SI habituelle et nos plages.
= SOMME.SI(D2:D8, »Casque de sécurité »,H2:H8)
Dans SOMME.SI en utilisant Décalage comme plages, D2:D8 est remplacé par la formule de décalage suivante.
= décalage(D1,1,0):décalage(D9,-1,0)
Au lieu de D2, j’ai utilisé D1 comme référence de cellule et un décalage de 1 dans Décalage.
Donc, ici aussi, la référence de cellule est D2.
Mais l’avantage est que vous pouvez insérer n’importe quel nombre de lignes juste au-dessus de D2. Le décalage les inclurait dans les plages dynamiques dans SOMME.SI.
De même, dans SOMME.SI, une autre référence de cellule est D8. Mais dans SOMME.SI dynamique en utilisant Décalage, c’est D9 et décalage -1.
Donc, encore une fois, ici aussi, la référence de cellule réelle pointe vers D8.
Les points ci-dessus s’appliquent également à la plage H2:H8.
Ressources
- REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets
- Multiple Sum Columns in SUMIF in Google Sheets
- How to Sumif/Sumifs Excluding Duplicates in Google Sheets
- Sumif Importrange in Google Sheets – Examples
- Sum of Matrix Rows or Columns Using Sumif in Google Sheets
- How to Use Sumif in Merged Cells in Google Sheets
