Tutoriels > Tutoriels & Astuces Excel > Fonctions & Formules > L'Assistant Somme Conditionnelle

L'Assistant Somme Conditionnelle

Publié par Mth le 01-11-2011 (16534 lectures)

 

L'Assistant Somme conditionnelle est un un programme complémentaire fourni avec Excel.


Il crée une formule qui permet d'additionner ou de compter les données dans une liste si les données répondent aux critères spécifiés, c'est un outil puissant qui permet de faire des sommes selon une ou plusieurs conditions.


Si vous ne trouvez pas l'assistant dans vos compléments Excel, cliquez sur ce lien pour savoir comment installer une macro complémentaire:   Installer/désinstaller un complément ou macro complémentaire


Panneau alerte A savoir :

 

Les formules créées par l'Assistant Somme Conditionnelle sont des formules matricielles.

Après les avoir modifiées, vous devez appuyer sur les 3 touches CTRL+MAJ+ENTER pour les verrouiller.



 

Utilisation de l'Assistant :
Trait bleu



Commençons par un exemple simple pour illustrer le fonctionnement de l'assistant; le tableau à gauche ci-dessous fournit des informations détaillées de chiffre d'affaires, voyons à l'aide de l'assistant comment synthétiser les ventes par représentant afin de remplir le tableau de synthèse de droite:


Assistant Somme Conditionnelle - 1


Numéro 1  Sélectionner la zone où se trouvent les données (dans notre exemple le tableau ci dessus commence en C34 et se termine en E44), puis cliquer sur l'onglet Formules / Groupe Solutions / Somme Conditionnelle

(Pour les versions antérieures à 2007: menu Outils, choisir Assistant puis Somme conditionnelle ):

Assistant Somme Conditionnelle



Numéro 2  L'assistant somme conditionnelle apparaît, avec la zone que nous venons de sélectionner; cette zone peut être saisie, ou sélectionnée en maintenant le bouton gauche de la souris enfoncé tout en parcourant la zone du tableau. La sélection doit contenir les en-têtes des colonnes.

 

Cliquer sur "Suivant"

Assistant Somme Conditionnelle

 

Numéro 3 Sélectionner la colonne à totaliser dans la liste déroulante (la liste des intitulés de colonnes s'ouvre en cliquant sur la petite flèche); dans notre exemple nous voulons obtenir la somme du chiffre d'affaires. Sélectionner ensuite un critère; ici nous souhaitons une somme par représentant:

 

Assistant Somme Conditionnelle



Numéro 4  Choisir l'opérateur (signe = dans notre cas), ainsi que la valeur du critère (ici monsieur Durand) puis cliquer sur "Ajouter la condition":


Assistant Somme Conditionnelle

La condition est venue s'inscrire dans la fenêtre:

Assistant Somme Conditionnelle

 

Plusieurs conditions peuvent être ajoutées, mais arrêtons nous à une seule pour l'instant et cliquons sur le bouton "Suivant".

 

Numéro 5  Nous pouvons alors choisir d'afficher la formule obtenue ou la formule et la condition, choisissons ici de n'afficher que le résultat et cliquons sur suivant; puis sélectionnons la cellule où nous voulons l'afficher (La cellule peut être saisie ou directement sélectionnée avec la souris)

Terminer en cliquant sur le bouton "Fin".


Assistant Somme Conditionnelle

Le résultat apparaît dans notre tableau à la ligne indiquée

Assistant Somme Conditionnelle

 

L'assistant a généré tout seul cette formule matricielle:

 

{=SOMME(SI($D$35:$D$43="Durand";$E$35:$E$43;0))}

 

Si dans l'avant dernière fenêtre de l'assistant nous avions choisi de copier la formule ET les conditions en cochant la deuxième option, Excel nous aurait tout d'abord demandé de choisir la cellule où placer les conditions (par exemple la cellule K37 où l'assistant aurait inscrit "Durand") et ensuite la cellule où placer le résultat (par exemple L37):


Assistant Somme Conditionnelle
 
Si nous avions choisi cette option, l'assistant aurait généré cette formule :

{=SOMME(SI($D$35:$D$43=K35;$E$35:$E$43;0))}


Icone info A noter

Nous remarquons ici que lorsque nous demandons à l'assistant d'indiquer le résultat et les conditions, Excel insère automatiquement le signe $ dans la formule afin de figer les plages de critères.

Si nous ne demandons que le résultat, Excel écrit directement le critère dans la formule.

 

 

Trait bleu

 

Pour compter le nombre de transactions, le cheminement est exactement le même, simplement nous allons demander à l'assistant de totaliser la colonne représentant:

 

Assistant Somme Conditionnelle
Le résultat obtenu est 4, il y a bien 4 lignes concernant Monsieur Durand dans notre tableau, la formule générée par l'assistant est:

 

{=SOMME(SI($D$35:$D$43="Durand";1;0))}




Pour effectuer des calculs selon plusieurs conditions:
Trait bleu

 

Voyons maintenant comment aller plus loin avec l'assistant, afin d'effectuer des calculs sur plusieurs conditions.

 

Le tableau de gauche ci-dessous indique des ventes mensuelles d'une équipe de délégués commerciaux, l'assistant va nous permettre de remplir le tableau de synthèse ci-dessous où nos calculs devront intégrer deux conditions: le mois et le délégué.


Assistant Somme Conditionnelle

Le cheminement est le même que celui exposé plus haut, simplement nous allons ajouter plusieurs conditions:

 

La première : 

Assistant Somme Conditionnelle
Après avoir ajouté la 1ère condition, ne pas cliquer sur le bouton suivant, rester sur cette fenêtre et choisir une 2ème condition, ici le mois, qui sera prise en compte après avoir cliqué sur le bouton "ajouter la condition".


Assistant Somme Conditionnelle

Assistant Somme Conditionnelle

 

Une fois toutes les conditions mises en place (deux dans notre cas), cliquer sur "suivant" et poursuivre comme présenté plus haut.

La formule a été écrite à notre demande en I242:

Assistant Somme Conditionnelle
 
{=SOMME(SI($C$237:$C$248="Dupont";SI($B$237:$B$248="Janvier";$D$237:$D$248;0);0))}
Afin de pouvoir copier/coller cette formules dans les autres cellules de notre tableau récapitulatif, transformons les valeurs fixes "Dupont" et "Janvier" en références aux en-têtes de lignes et de colonnes. Le nom Dupont se trouve en cellule H242, afin de pouvoir recopier cette référence nous figeons la colonne en mettant le signe $ devant le H.

 

De même pour le mois qui se trouve sur la ligne 241, nous faisons précéder ce numéro de ligne du signe $ afin de pouvoir le figer quand nous recopierons la formule.

(pour plus d'informations sur le rôle de ce signe $ et les références relatives ou absolues, consultez l'article Références Relatives & Absolues )

 

Assistant Somme Conditionnelle

 

La formule corrigée sera validée avec les trois touches CTRL+MAJ+ENTER (elle sera alors encadrée par des accolades), et pourra être copiée / collée dans les autres cellules du tableau:

 

Assistant Somme Conditionnelle




Trait bleu


Pour vous entraîner à manipuler l'assistant somme conditionnelle, vous trouverez un cas pratique et ses corrigés dans le fichier qui accompagne cet article.

 

Extrait du fichier d'exercices: 

Assistant Somme Conditionnelle - Téléchargez les exercices corrigés

 

Assistant Somme Conditionnelle - Téléchargez les exercices corrigés
 

 

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

Tags Tutoriel   Formule   Exemple   Assistant   Somme   Condition   Matricielle  

 

Autres articles dans cette catégorie Publié le Vues
Rechercher une Fonction 28-05-2013 10083
T() - Tester et renvoyer du texte 02-07-2012 30728
EPURAGE() - Supprimer certains caractères indésirables 02-06-2012 85586
REMPLACER() - Echanger une chaîne de caractères par une autre 02-06-2012 29865
CTXT() - Convertir un nombre en texte 23-05-2012 83227
REPT() - Répéter des caractères un certain nombre de fois 26-04-2012 40750
L'Assistant Somme Conditionnelle 01-11-2011 16535
CELLULE() - Nom de l'onglet, du fichier ou du répertoire dans une cellule 03-10-2010 157015
EXACT() - Comparer deux chaînes de texte 26-04-2010 164350
Purgez les espaces inutiles ! 05-04-2010 209436
SUPPRESPACE() - Supprimer les espaces inutiles dans une cellule 05-04-2010 108600
STXT() - Extraire une chaîne de caractères au sein d'une autre 13-03-2010 395473
CHERCHE() TROUVE() - Retourne la position d'une chaîne de caractères 04-01-2010 185373
NBCAR() - Compter les caractères d'une chaîne de texte ou d'une plage de cellules 21-11-2009 45635
SUBSTITUE() - Remplacer une chaîne de texte par une autre 08-11-2009 209524
CAR() - CODE() : Utilisation des codes caractères 25-10-2009 76423
CONCATENER() - Assembler plusieurs chaînes de texte en une seule. 10-10-2009 43950
TEXTE() - Convertir une valeur numérique en texte formaté 29-09-2009 138982
DROITE() - GAUCHE() : Extraire les premiers ou derniers caractères d'une chaîne. 12-09-2009 478283
MAJUSCULE() - MINUSCULE() - NOMPROPRE(): Modifier la casse d'un texte 23-08-2009 13478
CNUM() - Convertir une chaîne en valeur numérique 19-08-2009 102784
DATEDIF() : Calculer la différence entre deux dates 26-07-2009 358696
Nommer une Plage Dynamique 06-12-2008 99886
Nommer une Plage de Cellules 05-12-2008 104163
Références Relatives & Absolues 05-12-2008 24140
Formule pour Nombres Premiers 29-08-2007 26629