La formule SUMIF résume les données selon des critères. La formule OFFSET nous permet de changer la référence. La combinaison des deux formules et l’ajout d’une zone de liste déroulante vous permettent de résumer facilement les données selon les critères du tableau de données.

Exemple

Tâche: Résumez les profits et pertes par éléments de P&L (voir la capture d’écran P&L, colonne B) pour le mois de janvier 2001 ou tout autre mois de votre choix.

Étape 1: spécifiez un nom pour la colonne B dans la feuille de calcul.

Sélectionnez la colonne B, appuyez sur Ctrl + F3, entrez ColB dans la zone Noms * dans la feuille de calcul, puis cliquez sur OK.

Étape 2: La formule SUMIF

La formule SUMIF * résume les données selon des critères.

  • La formule a 3 arguments:

Premier argument – Range – ColB * (colonne B dans la feuille de tableau de données).

Deuxième argument – Critères * – b3 (le texte: Chiffre d’affaires).

Troisième argument – Sum_range * – D: D (colonne D, janvier 2001). Il s’agit de la plage de données à partir de laquelle les données par critère seront résumées.

Problème

La formule SUMIF est excellente pour résumer les données selon des critères. Cependant, il existe une limitation: vous ne pouvez apporter aucune modification à la plage de résumé lorsque vous utilisez la formule SUMIF *. Dans l’exemple ci-dessus, vous avez résumé les données de la colonne de janvier 2001. Comment, alors, pouvez-vous facilement modifier la fourchette de la somme pour faire la somme à partir de la colonne de mars ou d’avril (au lieu de janvier)?

Il existe un troisième argument dans la formule VLookup *: la modification du numéro de colonne change également le numéro de la colonne intersectée.

La formule Index * est beaucoup plus flexible; vous pouvez modifier à la fois le numéro de la ligne et le numéro de la colonne.

La formule SUMIF a besoin d’aide et la solution consiste à ajouter la formule OFFSET *.

Étape 3: La formule OFFSET

OFFSET

Étape 4: Combinaison des formules OFFSET et SUMIF

OFFSET

Combo Box

Étape 5: Zone de liste déroulante (en savoir plus sur l’ajout d’une zone de liste déroulante) Ajoutez une zone de liste déroulante.

Le nom de la cellule liée à la zone de liste déroulante est MonthNumber. Dans la capture d’écran, voyez le troisième argument de la formule SUMIF dans la barre de formule. Le nom de la cellule liée à la zone de liste déroulante apparaît ici.

La combinaison de SUMIF, OFFSET et d’une zone de liste déroulante fournit un outil incroyablement puissant pour interroger et additionner les données d’un rapport. Capture d’écran // Récapitulation des données selon les critères de la table de données à l’aide de la formule Sumif, de la formule Offset et de la zone de liste déroulante dans Microsoft Excel //images/screenshots/299-1.JPG [image: //images/screenshots/299-1_s.JPG [ Résumez les données selon les critères de la table de données à l’aide de la formule Sumif, de la formule Décalage et de la zone de liste déroulante dans Microsoft Excel]] image: https: //www.office-skill.site/images/f [image, width = 20, height = 1 ] //images/screenshots/299-2.JPG [image: //images/screenshots/299-2_s.JPG [Résumer les données en fonction des critères de la table de données en utilisant la formule Sumif, la formule Offset et la zone de liste déroulante dans Microsoft Excel]] image: https: //www.office-skill.site/images/f [image, width = 20, height = 1] //images/screenshots/299-3.JPG [image: // images / screenshots / 299- 3_s.JPG [Résumer les données en fonction des critères de la table de données à l’aide de la formule Sumif, de la formule Décalage et de la zone de liste déroulante dans Microsoft Excel]] image: https: //www.office-skill.site/images/f [image, width = 20 , hauteur = 1]