Aller au contenu principal

SI, RECHERCHEV et les fonctions conditionnelles

Ce que ce chapitre apporte 6 points
  • Écrire une fonction SI, empiler plusieurs SI dans le bon ordre de seuils, et savoir quand SI.CONDITIONS se lit mieux.
  • Compter et additionner sous une condition avec NB.SI et SOMME.SI, puis sous plusieurs avec NB.SI.ENS, SOMME.SI.ENS et MOYENNE.SI.ENS.
  • Lire un RECHERCHEV et déjouer ses quatre pièges, puis écrire la même recherche avec RECHERCHEX, ou avec INDEX et EQUIV.
  • Intercepter une erreur avec SIERREUR, et reconnaître les cas où il ne faut pas le faire.
  • Combiner plusieurs conditions avec ET et OU, et distinguer une règle d'une moyenne.
  • Obtenir une liste sans doublons, filtrée ou triée, avec UNIQUE, FILTRE et TRI, et la réutiliser par D2#.
La fonction SI choisit entre deux résultats, et RECHERCHEV, ou RECHERCHEX qui la remplace, va chercher une valeur dans un autre tableau. Jusqu'ici, les formules calculaient toujours de la même façon. On passe maintenant à autre chose : faire prendre une décision au tableur, et lui faire retrouver une information dans un grand tableau. Ce sont les deux familles de fonctions qui font gagner des heures, et ce sont aussi celles où les erreurs sont les plus discrètes. Le chapitre se termine sur les formules qui débordent, capables de rendre toute une liste d'un seul coup.

La fonction SI

SI

=SI(test ; valeur_si_vrai ; valeur_si_faux)

Le test est une comparaison qui rend vrai ou faux : A1>10, B2="Oui", C3<>0. Les deux autres arguments sont ce que la cellule affichera dans chaque cas.

Le texte s'écrit entre guillemets ; un nombre ou une référence s'écrivent tels quels.

OpérateurSens
=égal
<>différent
> <strictement supérieur, inférieur
>= <=supérieur ou égal, inférieur ou égal
D2fx
ABCD
1ÉlèveNoteRésultatMention
2Amina14ReçuB
3Bastien8Ajourné 
4Chloé17ReçuTB
5Dorian11Reçu 
6Elias9,5Ajourné 
7    

D2 contient la formule =SI(B2>=16;"TB";SI(B2>=14;"B";SI(B2>=12;"AB";""))), et affiche B. Les cellules qu'elle utilise sont teintées.

Cinq élèves. La colonne C tranche sur un seul seuil, la colonne D empile trois SI pour attribuer une mention. Changer la note d'Elias en 10 pour le voir passer de « Ajourné » à « Reçu », et en 12 pour lui donner une mention.
Imbriquer des SI sans s'y perdre

Un SI imbriqué se place dans l'argument « sinon » du précédent, et l'on obtient une cascade de seuils.

La règle qui évite toutes les erreurs : ordonner les seuils du plus exigeant au moins exigeant. Une note de 17 est aussi supérieure à 14 et à 12 ; si l'on teste 12 en premier, tout le monde a la mention AB et les deux autres tests ne servent à rien.

Écrire "" pour l'absence de mention affiche une case vide, ce qui se lit mieux qu'un mot inutile.

Au-delà de trois ou quatre niveaux, l'imbrication devient illisible. Deux sorties existent : SI.CONDITIONS, qui aligne les tests au lieu de les emboîter, et surtout une table de correspondance lue par RECHERCHEX, que la suite du chapitre présente.

SI.CONDITIONS, la cascade mise à plat

SI.CONDITIONS

=SI.CONDITIONS(test1 ; valeur1 ; test2 ; valeur2 ; … )

Les couples « test ; valeur » se lisent dans l'ordre, et le premier test vrai l'emporte. Il n'y a plus de parenthèses à fermer en fin de formule, et chaque seuil se lit à côté de ce qu'il donne.

La fonction n'a pas de « sinon ». On le lui donne en dernier couple, avec un test toujours vrai : VRAI ; "Bon".

Un service maintenance classe ses machines d'après la vitesse de vibration mesurée sur les paliers, en millimètres par seconde, avec quatre classes fixées par l'atelier. La même règle est écrite deux fois.

D6fx
ABCD
1MachineVibration (mm/s)SI imbriquésSI.CONDITIONS
2Tour 11,2BonBon
3Tour 23,1AcceptableAcceptable
4Fraiseuse6,8À surveillerÀ surveiller
5Presse12,5ArrêtArrêt
6Compresseur0,9Bon#N/A
7    
D6 #N/A

D6 contient la formule =SI.CONDITIONS(B6>=11,2;"Arrêt";B6>=4,5;"À surveiller";B6>=1,8;"Acceptable"), et affiche #N/A. Les cellules qu'elle utilise sont teintées.

Les colonnes C et D rendent la même classe, sauf sur la dernière ligne : la formule de D6 a perdu son dernier couple, et une vibration de 0,9, qui ne passe aucun des trois tests, rend #N/A. Rajouter « ;VRAI;"Bon" » à la fin de la formule de D6 pour la réparer, puis porter la vibration du Tour 2 à 4,5 pour vérifier qu'un seuil atteint exactement fait changer de classe.
SI.CONDITIONS sans dernier couple rend #N/A
Une cascade de SI finit toujours par un « sinon » : la syntaxe l'impose. SI.CONDITIONS, elle, s'arrête quand les couples sont épuisés, et si aucun test n'a été vrai elle rend #N/A.
L'erreur n'apparaît que sur les lignes qui tombent hors de tous les tests, ce qui peut n'arriver qu'un mois sur dix. D'où le réflexe : terminer par VRAI ; …, même quand on croit que tous les cas sont couverts.
L'ordre reste celui de la cascade, du seuil le plus exigeant au moins exigeant, puisque le premier test vrai l'emporte.

SI.CONDITIONS rend une cascade lisible, mais les seuils restent écrits dans chaque formule : pour les changer, il faut retoucher toute la colonne. La vraie solution, quand la règle est un barème, est de poser les seuils dans une petite table et de les y rechercher, ce que la section « Retrouver une donnée » montre sur ces mêmes vibrations.

Une cousine sert quand on compare à des valeurs exactes plutôt qu'à des seuils : =SI.MULTIPLE(C2;"U";"Usinage";"M";"Montage";"autre atelier") rend « Usinage » si C2 vaut « U », « Montage » si elle vaut « M », et le dernier argument, laissé seul, joue le rôle du « sinon ».

Vérification rapideon peut se reprendre

1.Dans =SI(B2>=10;"Reçu";"Ajourné"), à quoi servent les guillemets ?

2.Dans une cascade de SI sur des seuils, il faut tester…

3.Une colonne de SI.CONDITIONS affiche #N/A sur quelques lignes seulement. La cause la plus probable est…

Compter et additionner sous condition

NB.SI et SOMME.SI

=NB.SI(plage ; critère) compte les cellules de la plage qui satisfont le critère.

=SOMME.SI(plage_de_test ; critère ; plage_à_additionner) additionne les valeurs de la troisième plage, pour les lignes où la première satisfait le critère.

Le critère s'écrit entre guillemets quand il contient un opérateur : ">10", "<>0", "informatique".

B8fx
ABCD
1ArticleCatégoriePrixStock
2Clavierinformatique25,912
3Sourisinformatique12,540
4Écraninformatique1495
5Chaisemobilier898
6Bureaumobilier2493
7Lampemobilier34,922
8Articles informatiques3Valeur du stock info187,4
9Articles à moins de 503Stock total du mobilier33
10    

B8 contient la formule =NB.SI(B2:B7;"informatique"), et affiche 3. Les cellules qu'elle utilise sont teintées.

Six articles et quatre calculs conditionnels. Changer la catégorie de la lampe en « informatique » pour voir les quatre résultats bouger d'un coup. Attention à l'orthographe : « Informatique » avec une majuscule compte quand même, mais « informatiques » au pluriel ne compte plus.
Un critère qui ne correspond à rien rend zéro, pas une erreur
=NB.SI(B2:B7;"informatiques"), avec un « s » de trop, rend 0. Pas #VALEUR!, pas d'avertissement : zéro, comme s'il n'y avait effectivement aucun article dans cette catégorie.
C'est la même défaillance silencieuse que le total faux du chapitre 2, et elle a la même parade : vérifier que la somme des comptages fait bien le total. Si NB.SI sur chaque catégorie ne redonne pas NBVAL de la colonne entière, une catégorie manque à l'appel.
Un espace en fin de saisie produit exactement le même effet, et il est invisible. La fonction SUPPRESPACE existe pour cela.

Plusieurs critères à la fois

NB.SI.ENS, SOMME.SI.ENS et MOYENNE.SI.ENS

=NB.SI.ENS(plage1 ; critère1 ; plage2 ; critère2 ; … ) compte les lignes qui satisfont tous les critères à la fois.

=SOMME.SI.ENS(plage_à_additionner ; plage1 ; critère1 ; plage2 ; critère2 ; … ) additionne, sur ces mêmes lignes, les valeurs de la plage donnée en premier.

=MOYENNE.SI.ENS(plage_à_moyenner ; plage1 ; critère1 ; … ) suit le même ordre que SOMME.SI.ENS.

Toutes les plages doivent avoir la même hauteur. Les critères se relient par un ET : une ligne n'est retenue que si elle passe chacun d'eux.

Le journal de maintenance d'un atelier recense ses interventions. Les deux critères sont posés dans des cellules plutôt qu'écrits dans les formules, ce qui permet de changer de question sans toucher à aucune formule.

B14fx
ABCD
1MachineTypeDurée (h)Coût
2Tour 1curative3420
3Tour 1préventive1,5180
4Fraiseusecurative5760
5Tour 1curative2310
6Pressepréventive2240
7Fraiseusepréventive1120
8Pressecurative4650
9Tour 1préventive1120
10    
11Machine choisieTour 1  
12Type choisicurative  
13Interventions2  
14Coût total730  
15Durée moyenne (h)2,5  
16Ce type, plus de 3 h, toutes machines2  
17    

B14 contient la formule =SOMME.SI.ENS(D2:D9;A2:A9;B11;B2:B9;B12), et affiche 730. Les cellules qu'elle utilise sont teintées.

Deux interventions curatives sur le Tour 1, pour 730 euros et 2,5 heures en moyenne. Taper « Fraiseuse » en B11, puis « préventive » en B12, pour interroger le journal autrement. Taper enfin « Perceuse » : le compte et le coût tombent à zéro, mais la moyenne rend #DIV/0!, parce qu'une moyenne de rien n'existe pas.
La plage à additionner change de place
SOMME.SI la met à la fin : =SOMME.SI(B2:B9;"curative";D2:D9). SOMME.SI.ENS la met au début : =SOMME.SI.ENS(D2:D9;B2:B9;"curative"). C'est ce qui permet d'ajouter autant de couples « plage ; critère » qu'on veut derrière, mais c'est un piège pour qui passe de l'une à l'autre en ajoutant simplement un critère au bout.
Se tromper d'ordre donne le plus souvent une formule refusée ou une erreur, et l'on s'en aperçoit. Le cas sournois est celui où la plage mise en tête est une autre colonne de nombres de la même hauteur : la formule additionne alors des heures au lieu d'euros, et le résultat a l'air juste.
Le plus simple est d'écrire toujours SOMME.SI.ENS, même pour un seul critère : un seul ordre à retenir, et la formule est prête à recevoir un deuxième critère.

NB.SI.ENS ne sait faire que le ET. Pour un OU entre deux valeurs d'une même colonne (les interventions sur le Tour 1 ou sur la Presse), on additionne deux comptes : =NB.SI(A2:A9;"Tour 1")+NB.SI(A2:A9;"Presse"). Une même ligne ne pouvant pas porter deux machines, rien n'est compté deux fois.

Vérification rapideon peut se reprendre

1.=NB.SI(B2:B50;"informatiques") avec un « s » de trop rend…

2.Où se place la plage à additionner dans SOMME.SI.ENS ?

3.=NB.SI.ENS(A2:A9;"Tour 1";B2:B9;"curative") compte les lignes…

Retrouver une donnée

RECHERCHEV

=RECHERCHEV(valeur_cherchée ; table ; numéro_de_colonne ; FAUX)

Le tableur cherche la valeur dans la première colonne de la table, puis rend ce qu'il trouve dans la colonne dont on donne le numéro, compté à partir de la première colonne de la table.

Le dernier argument, FAUX, demande une correspondance exacte. C'est presque toujours celui qu'on veut.

RECHERCHEV a une remplaçante, présentée juste après. Elle reste pourtant à connaître : elle figure dans presque tous les classeurs écrits avant elle, et il faut savoir la lire et la corriger.

Ce que cette fonction réalise porte un nom ailleurs : c'est une jointure, l'opération qui relie deux tables par une valeur commune. Le chapitre sur les requêtes SQL l'écrit en une clause, sans numéro de colonne à compter ni plage à figer, et c'est l'un des moments où le tableur montre sa limite : ici la table cherchée doit commencer par la colonne de recherche, et le numéro se décale dès qu'une colonne est insérée.

B8fx
ABCDE
1CodeDésignationPrix  
2CLAClavier25,9  
3SOUSouris12,5  
4ECRÉcran 24 pouces149  
5CASCasque79,9  
6     
7CommandeDésignationPrix unitaireQuantitéTotal
8SOUSouris12,5450
9ECRÉcran 24 pouces1492298
10XXX#N/A#N/A1#N/A
11     
B10 #N/A C10 #N/A E10 #N/A

B8 contient la formule =RECHERCHEV(A8;$A$2:$C$5;2;FAUX), et affiche Souris. Les cellules qu'elle utilise sont teintées.

Un catalogue en haut, une commande en bas. Taper un code dans la colonne A remplit tout le reste de la ligne. Le code XXX de la dernière ligne n'existe pas au catalogue, et RECHERCHEV rend #N/A, qui se propage jusqu'au total. Remplacer XXX par CLA pour voir la ligne se compléter.
Les quatre pièges de RECHERCHEV
La valeur cherchée doit être dans la première colonne de la table. La fonction ne regarde jamais à gauche. Si le code se trouve en colonne C et la désignation en colonne A, il faut réorganiser la table, ou passer à RECHERCHEX ou à INDEX et EQUIV, qui n'ont pas cette contrainte.
Le numéro de colonne se compte dans la table, pas dans la feuille. Sur une table qui commence en colonne A, la colonne 2 est B ; sur une table qui commence en colonne D, la colonne 2 est E.
La table doit être verrouillée avec des $. Sans eux, la recopie vers le bas fait glisser la table et l'on cherche dans une plage qui rétrécit ligne après ligne, ce qui produit des #N/A qui apparaissent à mi-parcours.
Le quatrième argument est FAUX. Omis, il vaut VRAI, ce qui déclenche une recherche approchée : la fonction rend alors la valeur immédiatement inférieure, ce qui donne des résultats faux mais plausibles sur une table non triée. C'est le plus dangereux des quatre.

RECHERCHEX, la recherche d'aujourd'hui

RECHERCHEX

=RECHERCHEX(valeur_cherchée ; colonne_de_recherche ; colonne_à_renvoyer ; [si_introuvable])

On désigne deux colonnes au lieu d'une table et d'un numéro : celle où chercher, et celle d'où tirer la réponse. Elles doivent avoir la même hauteur, et rien n'oblige la seconde à être à droite de la première.

La recherche est exacte par défaut. Le quatrième argument, facultatif, est ce que la formule affiche quand la valeur est introuvable.

RECHERCHEX existe dans Excel 2021, Microsoft 365 et Excel en ligne, pas dans Excel 2019 ni dans les versions antérieures, qui ne savent pas la calculer. Il en va de même des formules qui débordent, présentées à la fin du chapitre.

Le même catalogue, cette fois avec la désignation à gauche du code, comme on le trouve souvent dans un fichier fourni par quelqu'un d'autre. RECHERCHEV ne pourrait pas y retrouver la désignation sans réorganiser la table.

B8fx
ABCDE
1DésignationCodePrix  
2ClavierCLA25,9  
3SourisSOU12,5  
4Écran 24 poucesECR149  
5CasqueCAS79,9  
6     
7CommandeDésignationPrix unitaireQuantitéTotal
8SOUSouris12,5450
9ECRÉcran 24 pouces1492298
10XXXcode inconnu#N/A1#N/A
11     
C10 #N/A E10 #N/A

B8 contient la formule =RECHERCHEX(A8;$B$2:$B$5;$A$2:$A$5;"code inconnu"), et affiche Souris. Les cellules qu'elle utilise sont teintées.

La désignation est cherchée à gauche du code, ce que RECHERCHEV ne sait pas faire. Pour le code XXX, la colonne B affiche le message prévu, mais la colonne C, écrite sans quatrième argument, garde #N/A : le total ne doit pas pouvoir se calculer sur un prix inconnu. Remplacer XXX par CAS pour compléter la ligne.

Ce que l'on gagne tient en quatre points, qui répondent un à un aux pièges de RECHERCHEV.

  • La recherche à gauche : les deux colonnes sont désignées séparément, dans n'importe quel ordre.
  • Plus de numéro de colonne : insérer une colonne dans le catalogue décale les références, et la formule continue de viser la bonne colonne. Le numéro de RECHERCHEV, lui, reste 3 quand la colonne des prix devient la quatrième.
  • La recherche exacte par défaut : le quatrième argument de RECHERCHEV, oublié, rendait du plausible ; il n'y a plus rien à oublier.
  • Le cas introuvable prévu dans la formule, sans SIERREUR autour.

Reste le troisième piège, qui ne dépend pas de la fonction : les colonnes doivent être verrouillées avant la recopie, exactement comme la table de RECHERCHEV.

La figure le montre aussi : le message de remplacement se choisit colonne par colonne. Un texte lisible convient à la désignation, qu'un humain lit ; il ne convient pas au prix, qui entre dans un calcul, et que l'on laisse en erreur pour que l'erreur arrête le total.

Les tranches, sans cascade ni table triée

Le cinquième argument de RECHERCHEX règle le mode de correspondance. Avec -1, la fonction prend la valeur exacte, ou à défaut la plus grande valeur inférieure : c'est la recherche par tranches. On y retrouve les vibrations de tout à l'heure, dont les seuils quittent les formules pour une petite table.

F6fx
ABCDEF
1À partir de (mm/s)Classe MachineVibration (mm/s)Classe
20Bon Tour 11,2Bon
31,8Acceptable Tour 23,1Acceptable
44,5À surveiller Fraiseuse6,8À surveiller
511,2Arrêt Presse12,5Arrêt
6   Perceuse1,8Acceptable
7      

F6 contient la formule =RECHERCHEX(E6;$A$2:$A$5;$B$2:$B$5;;-1), et affiche Acceptable. Les cellules qu'elle utilise sont teintées.

La perceuse, à 1,8 exactement, tombe dans la classe qui commence à 1,8. Passer le seuil de « À surveiller » de 4,5 à 7 : la fraiseuse redevient « Acceptable », et aucune formule n'a été touchée. La règle vit dans la table.

Le quatrième argument est laissé vide (;;-1) : il n'y a rien à prévoir pour une valeur introuvable, puisque toute vibration positive tombe dans une tranche. Et contrairement à la recherche approchée de RECHERCHEV, la table n'a pas besoin d'être triée : la fonction la parcourt entièrement.

INDEX et EQUIV, pour les versions qui n'ont pas RECHERCHEX

INDEX et EQUIV

=EQUIV(valeur ; liste ; 0) rend la position de la valeur dans la liste : 3 si elle est à la troisième place. Le 0 demande une correspondance exacte, et joue le rôle du FAUX de RECHERCHEV.

=INDEX(plage ; ligne ; colonne) rend la valeur qui se trouve à cette position dans la plage.

Réunies, =INDEX(colonne_à_renvoyer ; EQUIV(valeur ; colonne_de_recherche ; 0)) font exactement ce que fait RECHERCHEX, et fonctionnent dans toutes les versions.

Le couple a un usage que RECHERCHEX seul n'a pas : la double entrée. Une grille de tarifs de découpe laser, en euros par mètre, dépend de la matière (en ligne) et de l'épaisseur (en colonne). Deux EQUIV trouvent la ligne et la colonne, INDEX lit la case à leur croisement.

B10fx
ABCDE
1Matière / épaisseur (mm)2468
2Acier1,21,92,83,9
3Inox1,82,94,36,1
4Aluminium1,52,43,65,2
5     
6MatièreInox   
7Épaisseur (mm)6   
8Ligne trouvée (EQUIV)2   
9Colonne trouvée (EQUIV)3   
10Prix au mètre (INDEX)4,3   
11En une seule formule4,3   
12     

B10 contient la formule =INDEX(B2:E4;B8;B9), et affiche 4,3. Les cellules qu'elle utilise sont teintées.

L'inox est à la ligne 2 de la grille, l'épaisseur de 6 mm à la colonne 3, et la case à leur croisement vaut 4,3 euros par mètre. Taper « Acier » et 8 pour lire 3,9. Taper ensuite une épaisseur de 5 : elle n'existe pas dans la grille, et EQUIV, en mode exact, rend #N/A plutôt qu'un prix voisin.

Les deux lignes intermédiaires ne servent qu'à comprendre : dans un vrai classeur, on écrit directement la formule de la dernière ligne. Mais quand une double recherche rend un résultat douteux, les décomposer ainsi montre tout de suite laquelle des deux positions est fausse.

Sans son 0, EQUIV redevient approché
Le troisième argument d'EQUIV vaut 1 quand on l'omet, ce qui demande une recherche approchée sur une liste supposée triée. C'est le même piège que le FAUX oublié de RECHERCHEV, et il produit le même genre de résultat : plausible et faux. On écrit donc toujours EQUIV(… ; … ; 0), sauf recherche par tranches voulue.
Vérification rapideon peut se reprendre

1.Un RECHERCHEV recopié donne des #N/A à partir du milieu du tableau. La cause est…

2.Omettre le quatrième argument de RECHERCHEV déclenche…

3.Quel avantage RECHERCHEX n'a-t-il pas sur RECHERCHEV ?

4.Dans =INDEX(A2:A5;EQUIV(F2;C2:C5;0)), que rend EQUIV ?

Intercepter les erreurs

SIERREUR

=SIERREUR(calcul ; valeur_de_remplacement) rend le résultat du calcul, sauf s'il produit une erreur, auquel cas il rend la valeur de remplacement.

C3fx
ABC
1CodeSans SIERREURAvec SIERREUR
2CLAClavierClavier
3XXX#N/Acode inconnu
4   
5Catalogue  
6CLAClavier 
7SOUSouris 
8ECRÉcran 
9   
B3 #N/A

C3 contient la formule =SIERREUR(RECHERCHEV(A3;$A$6:$B$8;2;FAUX);"code inconnu"), et affiche code inconnu. Les cellules qu'elle utilise sont teintées.

Le même code introuvable, traité de deux façons. À gauche, #N/A s'affiche et se propagerait dans tout calcul en aval. À droite, SIERREUR le remplace par un message lisible. Le résultat est plus présentable, et c'est précisément le danger.
SIERREUR masque le problème au lieu de le résoudre
Envelopper une formule dans SIERREUR ne la corrige pas : cela cache le message. Le code introuvable reste introuvable, et la ligne de commande reste fausse ; elle est simplement plus jolie.
Deux usages sont légitimes. Éviter un #DIV/0! quand un dénominateur vide est normal en début de mois. Afficher un message compréhensible dans un fichier destiné à quelqu'un d'autre, à condition que ce message dise quoi faire.
Un usage ne l'est jamais : mettre SIERREUR partout par principe, ce qui revient à débrancher le seul système d'alerte du tableur.
Le bon réflexe est inverse : quand une erreur apparaît, chercher pourquoi, et n'employer SIERREUR qu'une fois la cause comprise et jugée normale.

SIERREUR rattrape toutes les erreurs, y compris celles qu'on voudrait voir : une faute de frappe dans un nom de fonction, une division par une case vide qui ne devrait pas l'être. Deux parades plus étroites existent pour le seul cas d'une recherche infructueuse. Le quatrième argument de RECHERCHEX, vu plus haut. Et =SI.NON.DISP(calcul ; valeur), qui s'écrit comme SIERREUR mais ne remplace que #N/A, l'erreur des recherches, et laisse passer toutes les autres.

Vérification rapideon peut se reprendre

1.SIERREUR sert à…

2.Qu'est-ce qui distingue SI.NON.DISP de SIERREUR ?

Combiner des conditions

ET exige que toutes les conditions soient vraies, OU qu'au moins une le soit. Les deux s'emploient à l'intérieur d'un SI.

D3fx
ABCD
1FournisseurQualitéDélaiDécision
2Alpha1411Retenu
3Beta167Écarté
4Gamma915Écarté
5Delta1212Retenu
6Retenus2Qualité moyenne des retenus13
7    

D3 contient la formule =SI(ET(B3>=10;C3>=10);"Retenu";"Écarté"), et affiche Écarté. Les cellules qu'elle utilise sont teintées.

La règle d'achat exige au moins 10 sur chacun des deux critères. Regarder la ligne Beta : 16 en qualité, et pourtant « Écarté », parce que son délai est noté 7. Un critère excellent ne rachète pas un critère insuffisant, et c'est exactement ce que ET exprime. Remplacer ET par OU dans une des formules pour voir la décision basculer.

Le même ET se cache dans NB.SI.ENS et SOMME.SI.ENS : leurs critères doivent tous être vérifiés. Compter les fournisseurs retenus s'écrirait donc aussi, sans colonne de décision, =NB.SI.ENS(B2:B5;">=10";C2:C5;">=10"). La colonne D reste pourtant la meilleure solution quand la décision doit se lire ligne par ligne.

Les formules qui débordent

Toutes les formules vues jusqu'ici rendent une valeur, dans une cellule. Certaines fonctions rendent une liste entière : les valeurs distinctes d'une colonne, les lignes qui passent un filtre, un tableau trié. La formule s'écrit dans une seule cellule, et son résultat déborde sur les cellules voisines, vers le bas et vers la droite, autant qu'il le faut.

UNIQUE, FILTRE, TRI et la référence D2#

=UNIQUE(plage) rend chaque valeur de la plage une seule fois, dans l'ordre où elle apparaît.

=FILTRE(plage ; condition ; si_vide) rend les lignes de la plage pour lesquelles la condition est vraie. La condition porte sur une colonne entière, comme B2:B9<C2:C9, et le troisième argument est ce qu'on affiche si aucune ligne ne passe.

=TRI(plage ; numéro_de_colonne ; ordre) rend la plage triée sur l'une de ses colonnes, 1 pour l'ordre croissant et -1 pour l'ordre décroissant. Les lignes restent entières.

Si la formule est en D2, D2# désigne toute la zone qu'elle a remplie, quelle que soit sa taille du moment.

Ces fonctions existent dans les mêmes versions que RECHERCHEX. Leur force est de suivre les données : une machine de plus dans le journal, et la liste s'allonge d'une ligne, sans rien recopier.

D2fx
ABCDEF
1MachineArrêt (h) MachineArrêtsHeures
2Tour 12 Compresseur16
3Presse1,5 Fraiseuse25,5
4Fraiseuse3 Presse36,5
5Tour 10,5 Tour 145
6Presse4    
7Tour 11    
8Fraiseuse2,5    
9Presse1    
10Tour 11,5    
11Compresseur6    
12      

D2 contient la formule =TRI(UNIQUE(A2:A11)), qui rend plusieurs valeurs : elles débordent sur la zone entourée de pointillés.

Trois formules, écrites en D2, E2 et F2 seulement, dressent le bilan des arrêts. Le compresseur ne s'est arrêté qu'une fois, mais c'est lui qui a coûté le plus d'heures. Remplacer « Presse » en A6 par « Robot » : une cinquième ligne apparaît dans les trois colonnes. Taper ensuite n'importe quoi en D4 : la liste ne peut plus s'étaler, D2 affiche #PROPAGATION!, et les deux colonnes qui la lisent par D2# affichent #REF!. Effacer D4 pour tout rétablir.

Choisir une cellule de la zone l'entoure de pointillés. Seule la première cellule contient la formule ; les autres affichent son résultat, mais on ne peut pas les modifier une à une, et effacer la première efface toute la zone.

Le couple =UNIQUE(…) puis =NB.SI(… ; D2#) est le moyen le plus rapide d'obtenir un tableau de comptes : la liste des catégories, et en face le nombre de lignes de chacune. NB.SI reçoit toute la zone D2# comme critère, et rend un compte par valeur, qui déborde à son tour. Le tableau croisé dynamique du chapitre sur les graphiques fait la même chose avec plus de réglages ; celui-ci se recalcule seul à chaque saisie.

#PROPAGATION! : la place est prise
Une formule qui déborde a besoin de cellules vides. Si une seule case de la zone contient quelque chose, même une espace ou un ancien résultat oublié, la formule ne remplit rien et affiche #PROPAGATION!.
La parade est de réserver la place : ne rien écrire sous une formule qui déborde, et prévoir que la liste peut s'allonger. Un total placé juste sous un UNIQUE finira par bloquer la liste le jour où une nouvelle valeur apparaîtra.
Pour réutiliser le résultat, on écrit D2# plutôt que D2:D5 : la plage écrite en dur ne suivrait pas la liste quand elle s'allonge.

FILTRE et TRI travaillent sur des lignes entières. Un magasin de pièces détachées compare son stock au seuil de réapprovisionnement de chaque article.

F2fx
ABCDEFGHI
1ArticleStockSeuilÉcart À commanderStockSeuilÉcart
2Roulement 62041220-8 Roulement 62041220-8
3Courroie A42853 Filtre à huile310-7
4Filtre à huile310-7 Fusible 10 A1925-6
5Joint torique15010050     
6Fusible 10 A1925-6     
7Graisse (kg)642     
8Plaquette carbure403010     
9Huile de coupe (L)25205     
10         
11Du plus en retard au plus fourni        
12Roulement 62041220-8     
13Filtre à huile310-7     
14Fusible 10 A1925-6     
15Graisse (kg)642     
16Courroie A42853     
17Huile de coupe (L)25205     
18Plaquette carbure403010     
19Joint torique15010050     
20         

F2 contient la formule =FILTRE(A2:D9;B2:B9<C2:C9;"rien à commander"), qui rend plusieurs valeurs : elles débordent sur la zone entourée de pointillés.

FILTRE ne garde que les trois articles sous leur seuil, et TRI range tout le stock par écart croissant, le roulement en tête. Porter le stock de filtres à huile à 12 : il quitte la liste et descend dans le classement. Porter aussi le roulement à 20 et les fusibles à 25 : plus rien n'est sous le seuil, et FILTRE affiche le message prévu.

Sans son troisième argument, un FILTRE qui ne garde aucune ligne rend l'erreur #CALC! : prévoir ce message est le même geste que le quatrième argument de RECHERCHEX.

Vérification rapideon peut se reprendre

1.Un =UNIQUE(A2:A40) placé en D2 affiche #PROPAGATION!. Que faire ?

2.Pourquoi écrire =NB.SI(A2:A40;D2#) plutôt que =NB.SI(A2:A40;D2:D6) ?

Mettre en pratique

Cinq résultats sur la même colonne de notes : 14, 8, 17, 11 et 15. Les quatre premiers font travailler les fonctions conditionnelles, le dernier une recherche suivie d'un calcul.

Calculer sous condition

  • 1.

    Que rend =SOMME.SI sur cette colonne, avec le critère « supérieur ou égal à 10 » ?

  • 2.

    Que rend =MOYENNE.SI avec le même critère ?

  • 3.

    De combien cette moyenne conditionnelle dépasse-t-elle la moyenne de toute la colonne ?

  • 4.

    Que rend =NB.SI.ENS sur cette colonne avec deux critères, « supérieur ou égal à 10 » et « inférieur à 15 » ?

  • 5.

    Une recherche dans un barème rend un taux de commission de 8 % pour un chiffre d'affaires de 2500 euros. Combien vaut la commission ?

    euros
Écrire une formule qui affiche « Rupture » si le stock est inférieur à 5, et rien sinon.

=SI(D2<5;"Rupture";"")

Trois détails comptent.

Le texte va entre guillemets. Sans eux, Rupture est pris pour un nom de plage et la formule rend #NOM?.

"" désigne une chaîne vide, qui affiche une case vide. C'est plus lisible qu'un mot inutile, et cela permet de compter facilement les ruptures ensuite avec =NB.SI(E2:E20;"Rupture").

Le test emploie < et non <= : « inférieur à 5 » exclut 5. Si la règle est « 5 ou moins », il faut <=5.

Un RECHERCHEV recopié vers le bas donne des #N/A à partir de la moitié du tableau. Pourquoi ?

La table n'est pas verrouillée.

Écrite =RECHERCHEV(A8;A2:C5;2;FAUX) sans les $, la table se décale à chaque recopie : A3:C6, puis A4:C7. Elle glisse vers le bas et finit par ne plus contenir les premières lignes du catalogue.

C'est ce qui explique que les erreurs apparaissent progressivement plutôt que d'emblée, ce qui égare la recherche de la cause.

La correction : $A$2:$C$5. Sélectionner la référence dans la barre de formule et appuyer sur F4 pose les quatre $ d'un coup.

Pourquoi toujours écrire FAUX en quatrième argument de RECHERCHEV ?

Parce qu'omis, cet argument vaut VRAI, ce qui déclenche une recherche approchée.

En recherche approchée, la fonction suppose la première colonne triée par ordre croissant et rend la valeur immédiatement inférieure à celle qu'on cherche. Sur une table non triée, elle rend n'importe quoi.

Le danger est que le résultat est plausible : un prix, une désignation, quelque chose qui ressemble à une réponse. Aucune erreur n'est signalée.

La recherche approchée a un usage légitime, celui des tranches : trouver le taux correspondant à une tranche de revenus. Mais il faut alors le vouloir explicitement, et trier la table.

Avec RECHERCHEX, la question ne se pose plus : la recherche est exacte par défaut, et les tranches se demandent par le mode -1, sur une table qui n'a pas besoin d'être triée.

Réécrire =RECHERCHEV(A8;$A$2:$C$5;3;FAUX) avec RECHERCHEX, sachant que les codes sont en colonne A et les prix en colonne C. Qu'y gagne-t-on ?

=RECHERCHEX(A8;$A$2:$A$5;$C$2:$C$5)

La table et son numéro de colonne sont remplacés par deux colonnes : celle où chercher, $A$2:$A$5, et celle d'où tirer le prix, $C$2:$C$5.

Le FAUX disparaît, puisque la recherche est exacte par défaut. Il n'y a plus d'argument dont l'oubli change le sens de la formule.

Si l'on insère une colonne « Fournisseur » entre la désignation et le prix, les références se décalent d'elles-mêmes vers $D$2:$D$5. Le 3 de RECHERCHEV, lui, serait resté 3, et la formule aurait rendu le fournisseur à la place du prix, sans la moindre erreur.

Les $ restent : c'est la seule précaution que RECHERCHEX n'a pas supprimée.

Une réserve : un classeur qui doit être ouvert dans Excel 2019 ou une version antérieure garde RECHERCHEV, ou passe à INDEX et EQUIV.

=SOMME.SI(B2:B9;"curative";D2:D9) donne le coût de toutes les interventions curatives. Comment n'additionner que celles du Tour 1, dont le nom est en colonne A ?

=SOMME.SI.ENS(D2:D9;B2:B9;"curative";A2:A9;"Tour 1")

Il faut changer de fonction, et surtout d'ordre : dans SOMME.SI.ENS, la plage à additionner, D2:D9, passe en tête. Suivent les couples « plage ; critère », ici deux, reliés par un ET.

L'erreur tentante est de garder l'ordre de SOMME.SI et d'ajouter le second critère au bout. Le critère « curative » se retrouve alors là où la fonction attend une plage, et la formule est refusée ou rend une erreur : on s'en aperçoit. Plus sournois, écrire C2:C9 en tête au lieu de D2:D9 additionne les durées au lieu des coûts, sans aucun message.

Poser « Tour 1 » et « curative » dans deux cellules, et y faire référence, permet ensuite d'interroger le journal pour n'importe quelle machine sans réécrire la formule.

=NB.SI(B2:B50;"informatique") rend 0 alors que la colonne en contient. Que chercher ?

Une différence invisible entre le critère et les données.

Trois causes, par ordre de fréquence.

Un espace en fin de saisie : "informatique " n'est pas "informatique". La fonction SUPPRESPACE nettoie la colonne.

Une faute d'orthographe ou un pluriel : informatiques au lieu de informatique.

Un caractère invisible venu d'un copier-coller depuis une page web, souvent un espace insécable.

La casse, elle, ne pose pas de problème : NB.SI ne distingue pas les majuscules des minuscules.

Le contrôle général : la somme des NB.SI sur toutes les catégories doit redonner le NBVAL de la colonne. Si l'un manque, on sait combien de lignes échappent au compte.

Faut-il envelopper toutes ses formules dans SIERREUR ?

Non, presque jamais.

SIERREUR ne corrige rien : il masque le message. Le code introuvable reste introuvable, la ligne reste fausse, elle est seulement plus présentable.

Deux usages sont défendables. Éviter un #DIV/0! quand un dénominateur vide est normal, par exemple un taux de progression au premier mois. Et afficher un message compréhensible dans un fichier destiné à autrui, à condition que ce message dise quoi faire.

Le mettre partout par principe revient à débrancher le seul système d'alerte du tableur.

Le bon réflexe est inverse : quand une erreur apparaît, chercher pourquoi, et n'employer SIERREUR qu'une fois la cause comprise et jugée acceptable. Pour une recherche, le quatrième argument de RECHERCHEX ou SI.NON.DISP font le même travail sans cacher les autres erreurs.

Un fournisseur obtient 16 sur 20 en qualité et 7 sur 20 en délai. La règle d'achat exige au moins 10 sur chacun des deux critères. Quelle formule, et pourquoi pas la moyenne des deux ?

=SI(ET(B3>=10;C3>=10);"Retenu";"Écarté")

La moyenne des deux notes vaut 11,5, ce qui le ferait retenir. Mais la règle n'est pas « avoir 10 de moyenne », elle est « avoir 10 sur chaque critère ».

ET exige que toutes ses conditions soient vraies. OU se contenterait d'une seule, et donnerait ici « Retenu », ce qui serait faux.

C'est un cas où la formule doit traduire une règle, pas un calcul. Confondre les deux est la source d'erreurs les plus difficiles à repérer, parce que le résultat reste vraisemblable.

Dresser, à côté d'un journal dont les machines sont en A2:A200, la liste des machines et le nombre d'arrêts de chacune, de façon qu'une machine nouvelle y apparaisse seule.

En D2 : =UNIQUE(A2:A200). En E2 : =NB.SI(A2:A200;D2#).

UNIQUE rend chaque machine une fois et déborde vers le bas. D2# désigne toute cette liste, quelle que soit sa longueur, et NB.SI rend un compte par machine, qui déborde à son tour.

Une machine nouvelle saisie dans le journal allonge la liste d'une ligne, et son compte apparaît en face, sans rien recopier. C'est ce qu'une plage écrite en dur, D2:D6, ne ferait pas.

Deux conditions : laisser vides les cellules sous D2 et E2, faute de quoi les formules affichent #PROPAGATION! ; et disposer d'une version qui connaît ces fonctions. =TRI(UNIQUE(A2:A200)) donne la même liste dans l'ordre alphabétique.

Chapitre révisé en octobre 2026 · Une erreur dans ce chapitre ?

Dans la bibliothèque d'exercices

Conditions imbriquées, recherche exacte ou approximative, références figées.

Tous les exercices sur formules et recherche