Excel 2010 : Les formules et les fonctions avancées

S'approprier les fonctions à conditions multiples

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Vous maîtrisez SOMME.SI, MOYENNE.SI et NB.SI ? Décuplez les possibilités avec les fonctions SOMME.SI.ENS, MOYENNE.SI.ENS et NB.SI.ENS.
06:56

Transcription

Durant cette session, nous allons apprendre ensemble à manipuler les fonctions à conditions multiples. Commençons directement avec un cas concret. J'ai sous les yeux un tableau de suivi professionnel avec des dates, des dossiers de différentes couleurs, de différents montants et de différents états. Terminé ou En cours. Pour info, mon tableau s'étale sur 2 000 lignes. Je sais que je peux obtenir facilement le montant global. J'écris =somme, j'ouvre la parenthèse, je sélectionne la colonne C, je ferme la parenthèse, Entrée. Je change le format, c'est bon. Je sais que je peux obtenir une somme qui respecte un critère avec somme.si =somme.si j'ouvre la parenthèse. Par exemple si je veux les montants des dossiers rouges, je sélectionne donc plage la colonne où il y a toutes les couleurs ; Je précise mon critère, ici rouge, je le précise bien entre guillemets sinon ça ne fonctionne pas. ; et la colonne qui contient les montants à additionner, je ferme la parenthèse, Entrée. Mais j'ai un problème : mettons que je veuille avoir le montant des dossiers rouge et qui sont terminé, ça me fait 2 critères. somme.si ne me permet de valider qu'un seul critère. C'est là que la fonction somme.si.ens entre en jeu. somme.si, un seul critère somme.si.ens, un ensemble de critères. J'ouvre la parenthèse, attention les arguments ne sont pas tout à fait dans le même ordre, pour somme.si la plage somme est à la fin. somme.si.ens la plage somme est au début. Pour la plage somme, je choisi donc la colonne C, qui contient les montants à additionner, ; pour la suite c'est un enchainement qui se répète. Plage critère, critère, plage critère, critère, etc. Plage critère, je lui précise une colonne, par exemple. Et critère, le critère que je recherche. Pour le numéro 1, nous allons choisir les couleurs. Pour le numéro 2, nous allons choisir l'état. Commençons par le 1, plage critère 1, je vais choisir la colonne B, là où il y a toutes mes couleurs. Critère 1 je précise du rouge, toujours entre guillemets ; Plage critère de critère 2, là je vais pouvoir me concentrer sur l'état Plage critère je choisi donc la colonne D, ; critère je vais choisir Terminé J'écris donc Terminé entre guillemets, je ferme les parenthèse, Entrée. J'ai donc un montant encore plus précis et nous pouvons aller encore plus loin. Je vais sélectionner mes couleurs, Ctrl/Cmd +C Ctrl/Cmd +V Puis je fais la même manipulation pour les états. Au préalable, je modifie mes formats, c'est bon. Le but, a priori, semble simple, écrire cette fonction-là, et puis l'étirer dans les deux sens. Ce n'est pas aussi simple que ça, vous allez comprendre pourquoi. =somme.si.ens j'ovre la parenthèse, plage somme, même logique, là où il y a tous les montants ; Pour les premiers, on va se concentrer sur la couleur. La colonne qui contient les couleurs c'est bien la colonne B ; Pour mon critère au lieu de l'écrire je vais le sélectionner à côté. Je choisis cette cellule pour le rouge. Maintenant on passe à l'état ; plage critère, tous mes états sont dans la colonne D ; comme critère je vais prendre celui qu'il y a dessus, Terminé. Plage somme, plage critère 1, plage critère 2, critère 1, critère 2, tout est bon. Je ferme la parenthèse, Entrée. Cette formule était truffée de pièges, regardez pourquoi. Je fais un clic gauche sur ma cellule, enfoncé sur la poignée de recopie je descend et je vois que ça ne marche pas. Pourquoi ? Je regarde la première, tout semble en place, je regarde la deuxième, regardez ce qui se passe ici. Le critère Bleu est bon, mais la cellule n'est pas située sur Terminé. Ça s'est décalé. C'est normal dans ma formule je n'ai que des références relatives. Ici Terminé a décalé d'un cran vers le bas. Je vois que Terminé est en H6, je ne veux pas qu'il ait en H7, puis en H8, etc. donc je dois bloquer le chiffre, c'est le chiffre qui bouge à chaque fois. Je me place devant, je pose un $, Entrée. Clic gauche enfoncé sur la poignée de recopie, je descends. Super, ça marche ! Clic gauche enfoncé, super ça ne marche plus ! Ce qui se passe ici, regardons. Même problème, En cours est bien sélectionné, mais ma couleur n'est plus au bon endroit, je dois bloquer ma couleur. Dans ma formule de base je vois que la couleur se situe en G7 et se décale vers la droite, donc se décale d'une lettre. C'est la lettre que je dois bloquer. Je place donc un $ avant le G, Entrée. Clic gauche enfoncé dans un sens, heureusement ça ne bouge pas. Clic gauche enfoncé dans l'autre sens, et ça ne marche toujours pas. Vérifions. Mes critères sont bien corrects cette fois-ci, regardez vos colonnes, c'est pour ça que c'est un vrai piège, elles se sont décalées. Vous devez vous assurer de placer un $ devant chaque lettre pour chaque colonne. Je clique sur la première cellule et je place un $ devant chaque lettre. Voilà qui est fait, je fais Entrée. Je déroule ma cellule, clic gauche enfoncé je descends, clic gauche enfoncé je vais à droite, c'est bon. Si vous voyez ceci c'est normal, il vous suffit juste d'agrandir la taille de votre colonne. somme.si.ens est structuré de cette manière : plage somme, plage critère, critère. moyenne.si.ens est organisé strictement de la même façon. Ce sont les mêmes arguments, juste plage moyenne qui a un nom différent mais sinon c'est le même fonctionnement. Enfin, nb.si.ens a, pareil, le même fonctionnement, sauf qu'il est plus simple : il n'y a pas de plage à choisir avec des nombres à additionner. Il y a juste plage critère, critère, plage critère, critère. Vous savez désormais comment utiliser les fonctions à conditions multiples dans Microsoft Excel.

Excel 2010 : Les formules et les fonctions avancées

Créez des formules efficaces et exploitez au maximum les tableaux et les calculs. Redécouvrez les fonctions essentielles, exploitez les dates et gérez vos erreurs.

2h55 (42 vidéos)
Aucun commentaire n´est disponible actuellement
Spécial abonnés
Date de parution :16 févr. 2018
Mis à jour le:4 avr. 2018

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !