L'une des questions courantes sur le babillard électronique est de savoir comment utiliser la fonction SumIf avec deux conditions différentes. Malheureusement, la réponse est que SumIf ne peut pas gérer deux conditions différentes.
Pour faire deux conditions, vous devez utiliser une formule matricielle assez compliquée. Le complément d'assistant de somme conditionnelle vous permet de saisir facilement ces formules compliquées.
Voici une feuille de calcul Excel avec des colonnes pour le produit, le commercial et les ventes. Les données se trouvent dans les cellules A2: C29.
Si vous souhaitez totaliser les ventes, une simple fonction SUM () fonctionnera. =SUM(C2:C29).
De nombreux Exceller découvrent la fonction SumIf. En utilisant cette fonction, il est assez facile de calculer le total des ventes du produit ABC.=SUMIF(A2:A29,E2,C2:C29)
Il est également facile de calculer le total des ventes réalisées par le représentant des ventes Joe avec =SUMIF(B2:B29,E2,C2:C29).
Vous supposeriez alors qu'il est possible de calculer les ventes totales du produit ABC fabriqué par Joe. Cependant, il n'existe aucun moyen de le faire avec la fonction SumIf. Il s'avère que vous devez utiliser un tableau ou une formule CSE assez complexe.
Regardons les choses en face - la formule Sum est Excel 101. La formule SumIf n'est pas loin derrière en complexité. Cependant, la formule CSE pour calculer le total des ventes ABC réalisées par Joe suffit à me faire tourner la tête.
La bonne nouvelle - Microsoft propose l'Assistant Somme conditionnelle qui permet même à un novice d'entrer des formules conditionnelles complexes basées sur 1, 2 ou plusieurs conditions. L'Assistant Somme conditionnelle est un complément. Pour ajouter cette fonctionnalité à Excel, accédez au menu Outils et sélectionnez Compléments. Dans la boîte de dialogue Compléments, cochez la case en regard de Assistant Somme conditionnelle et choisissez OK. Il est possible que vous ayez besoin de votre CD d'installation à ce stade, car Microsoft n'inclut pas l'assistant dans l'installation par défaut.
Une fois que le complément est activé avec succès, il y aura un choix Somme conditionnelle… près du bas du menu Outils.
Sélectionnez une seule cellule dans votre ensemble de données et choisissez Outils - Somme conditionnelle. En supposant que vos données sont bien formatées avec une seule ligne d'en-têtes, Excel devinera correctement la plage de vos données. Choisissez Suivant.
À l'étape 2, sélectionnez la colonne à additionner. Dans ce cas, l'assistant a déjà deviné que vous vouliez additionner la première (et seule) colonne numérique - Ventes. Au milieu de la boîte de dialogue se trouvent trois contrôles déroulants. Celles-ci sont correctes pour la première condition - Le produit est égal à ABC, choisissez donc le bouton Ajouter une condition.
Ensuite, vous pouvez ajouter votre deuxième condition. Dans ce cas, vous souhaitez spécifier que le représentant commercial est Joe. Choisissez la flèche pour la première liste déroulante. Excel propose une liste alphabétique des noms de colonnes disponibles. Choisissez Sales Rep.
La liste déroulante du centre est correcte, mais par souci d'exhaustivité ici, vous pouvez voir que vous auriez pu choisir égal, inférieur à, supérieur à, inférieur ou égal, supérieur ou égal ou non égal.
Dans la troisième liste déroulante, sélectionnez Joe.
Cliquez sur le bouton Ajouter une condition.
Vous êtes maintenant prêt à passer à l'étape 3. Appuyez sur le bouton Suivant.
À l'étape 3, vous avez deux choix. Dans le premier choix, l'assistant entrera une formule unique avec les valeurs «ABC» et «Joe» codées en dur dans la formule. Cela vous donnera la réponse, mais il n'y aura aucune possibilité de changer facilement la formule. Avec le deuxième choix, Excel configurera une nouvelle cellule avec la valeur «ABC» et une nouvelle cellule avec la valeur «Joe». Une troisième cellule contiendra la formule qui fait une somme conditionnelle basée sur ces deux valeurs. Avec cette option, vous pouvez taper de nouvelles valeurs dans les cellules afin de voir le total des XYZ vendus par Adam.
L'assistant vous demandera alors où vous voulez la valeur pour ABC. Sélectionnez une cellule et choisissez Suivant. Répétez l'opération pendant que l'Assistant vous demande de sélectionner une cellule pour Joe et la formule.
Lorsque vous choisissez Terminer à la dernière étape, Excel crée une version légèrement différente (mais valide) de la formule CSE.
Cette formule calcule que Joe a vendu 33 338 $ d'ABC.
Si vous modifiez la cellule d'entrée de produit de ABC à DEF, la formule recalculera pour montrer que Joe a vendu 24 478 $ de DEF.
L'assistant de somme conditionnelle met les formules complexes à la portée de tous les propriétaires d'Excel.
Information additionnelle:Si vous voulez créer un tableau qui montrera les ventes de chaque produit par chaque représentant des ventes, il y a quelques «soins et alimentation» spéciaux que vous devrez connaître sur ces formules. Tapez chaque représentant commercial dans le haut de la gamme. Tapez chaque produit dans la colonne de gauche de la gamme. Modifiez la formule fournie par l'assistant. Dans l'image ci-dessous, la formule pointe vers le produit de la cellule E6. Cette référence doit vraiment être $ E6. Si vous laissez la référence comme E6 et copiez la formule dans la colonne G, la formule regarderait F6 au lieu de E6 et ce serait faux. L'ajout d'un signe dollar avant le E dans E6 garantira que la formule regarde toujours le produit dans la colonne E. La formule pointe également vers un représentant commercial dans la cellule F5. Cette référence doit vraiment être de 5 F $. Si vous avez laissé la référence comme F5 et copiez jusqu'à la ligne 7,la référence F5 passera à F6 et ce n'est pas correct. L'ajout d'un signe dollar avant le numéro de ligne verrouille le numéro de ligne et la référence pointe toujours vers la ligne 5.
En mode d'édition (sélectionnez la cellule et appuyez sur F2 pour modifier), tapez un $ avant le E. Tapez un signe dollar avant le 5 dans F5. N'appuyez pas encore sur Entrée!
Cette formule est un type spécial de formule. Si vous appuyez sur Entrée, vous obtiendrez un 0, ce qui n'est pas correct.
Au lieu de taper Entrée, maintenez les touches Ctrl et Maj enfoncées tout en appuyant sur Entrée. Cette combinaison magique de C trl + S hift + E nter est la raison pour laquelle j'appelle ces formules CSE.
Il y a une dernière considération avant de copier la formule dans le reste du tableau. Votre inclination pourrait être de copier F6 et de coller dans F6: G8. Si vous essayez ceci, Excel vous donnera le message déroutant «Vous ne pouvez pas modifier une partie d'un tableau». Excel se plaint que vous ne pouvez pas coller une formule CSE dans une plage qui contient la formule CSE d'origine.
Il est facile de contourner ce problème. Copiez F6. Coller à F7: F8.
Copiez F6: F8. Coller à G6: G8. Vous aurez un tableau de formules CSE montrant les totaux basés sur deux conditions.
