SIG DCG (UE8) : Tableur, audit de feuille et programmation

Tout le programme DCG, déjà prêt à réviser

Fiches, quiz, flashcards et infographies déjà créés, avec un tuteur IA. Paiement unique, dès 29 €.

Fonctions de recherche et gestion des erreurs du tableur

Q: Que signifie le dernier argument de `RECHERCHEV` et quand faut-il le mettre à `FAUX` ?

Il indique le type de recherche. `FAUX` exige une correspondance exacte : on l'emploie pour un code client ou une référence. `VRAI` (ou omis) retient la plus grande valeur inférieure ou égale dans une table triée : on l'emploie pour un barème par tranches.

Q: Que fait `SIERREUR` et quel est son danger ?

`SIERREUR(valeur;valeur_si_erreur)` remplace toute erreur par la valeur choisie. Son danger est de masquer aussi les erreurs de construction (faute de frappe, plage mal choisie) : on préfère tester la cause précise quand on le peut.

Q: Quelle erreur apparaît quand `RECHERCHEV` ne trouve pas un code client ?

`#N/A`, valeur non disponible. On peut la traiter avec `SIERREUR`, ou avec `SI(ESTNA(...);...)` pour ne traiter que ce cas.

Q: Comment trouver le prix d'un colis de 42 kg en Zone 2 dans une grille poids par zone ?

`=INDEX(prix;EQUIV(42;poids;1);EQUIV("Zone 2";en-têtes;0))`. Le premier `EQUIV` retient la tranche de poids (en approché sur des seuils triés), le second la colonne de la zone (en exact).

À retenir

Cadre programme : DCG, programme 2025 (arrêté du 4 août 2025, première session 2027), UE 8 « Systèmes d'information de gestion », partie 2 « Gérer des données du système d'information », sous-partie 2.2.1 « Automatiser la résolution des problèmes de gestion » (formules utilisant des fonctions de recherche d'informations ; éléments d'ergonomie dont la gestion des erreurs).

Pourquoi c'est central à l'examen : retrouver le taux de remise d'un client, le tarif d'une zone ou la tranche d'un barème à partir d'une table est le geste le plus fréquent de la gestion de données. L'écrit de 4 h attend que le candidat choisisse entre recherche exacte et approchée, sache lire une erreur et empêche qu'une valeur absente ne se propage dans les totaux.

01Rechercher une information dans une table

1.1 Le principe

Une table de référence (clients, tarifs, barèmes) est maintenue une fois dans le classeur ; les autres feuilles y cherchent l'information dont elles ont besoin à partir d'une clé (un code client, un montant). On évite ainsi de recopier à la main les données et on garantit leur cohérence.

1.2 `RECHERCHEV`

RECHERCHEV(valeur_cherchée;table;no_colonne;valeur_proche)

Argument	Rôle
`valeur_cherchée`	La clé à retrouver (un code, un montant)
`table`	La plage qui contient la clé dans sa première colonne, puis les informations à renvoyer
`no_colonne`	Le rang de la colonne à renvoyer, la première colonne de la table portant le rang 1
`valeur_proche`	`FAUX` (ou 0) pour une recherche exacte ; `VRAI` (ou 1) pour une recherche approchée

Si le dernier argument est omis, le tableur applique la recherche approchée. C'est l'erreur de syntaxe la plus coûteuse : en écrivant RECHERCHEV(A5;Table_clients;3), on lance une recherche approchée sans le savoir.

1.3 Recherche exacte (`FAUX`)

On cherche une correspondance parfaite : un code client, une référence article. Si la clé est absente, la fonction renvoie l'erreur #N/A (valeur non disponible). Les données n'ont pas besoin d'être triées.

1.4 Recherche approchée (`VRAI`) : le barème par tranches

On cherche la plus grande valeur inférieure ou égale à la valeur cherchée. C'est le fonctionnement d'un barème : la première colonne contient le seuil bas de chaque tranche.

Conditions impératives :

la première colonne de la table est triée par ordre croissant ; sinon le résultat est imprévisible ;
la première tranche commence en dessous de toute valeur possible (souvent 0) ; si la valeur cherchée est plus petite que le premier seuil, on obtient #N/A.

CA cherché	Seuil retrouvé	Raisonnement
4 999,99	0	Inférieur à 5 000
5 000	5 000	Égal au seuil : il appartient à la tranche qui commence à ce seuil
12 750	10 000	Plus grand seuil inférieur ou égal

1.5 Quand utiliser quoi

Situation	Mode
Code client, référence article, numéro de compte	Exact (`FAUX`)
Taux selon une tranche de montant, de poids, d'ancienneté	Approché (`VRAI`) sur table triée

1.6 Les limites de `RECHERCHEV`

La clé doit être dans la première colonne de la table : on ne peut pas chercher « vers la gauche ».
Le no_colonne est un rang saisi à la main : si on insère une colonne dans la table, le rang ne suit pas toujours et l'on peut renvoyer la mauvaise colonne sans erreur apparente.

1.7 `INDEX` et `EQUIV` : une alternative plus robuste

Fonction	Syntaxe	Rôle
`EQUIV`	`EQUIV(valeur_cherchée;plage;type)`	Renvoie la position de la valeur dans la plage
`INDEX`	`INDEX(plage;no_ligne;no_colonne)`	Renvoie le contenu de la cellule située à la position indiquée (`no_colonne` est facultatif pour une plage à une seule colonne)

Le troisième argument d'EQUIV sélectionne le mode : 0 pour une correspondance exacte ; 1 (valeur par défaut) pour la plus grande valeur inférieure ou égale dans une plage triée par ordre croissant ; -1 pour la plus petite valeur supérieure ou égale dans une plage triée par ordre décroissant.

On les combine :

=INDEX(Clients!$B$2:$B$6;EQUIV(A5;Clients!$A$2:$A$6;0))

Lecture : EQUIV trouve la ligne du code A5 dans la colonne des codes ; INDEX renvoie le nom situé à la même ligne dans la colonne des noms.

Intérêts : la colonne renvoyée est désignée par sa plage (pas par un rang), donc l'insertion d'une colonne ne casse rien ; la clé n'est pas obligée d'être dans la première colonne ; on peut combiner deux EQUIV pour une recherche à double entrée (ligne et colonne).

02Gérer les erreurs

2.1 Les types d'erreurs

Erreur	Cause habituelle	Exemple
`#N/A`	Valeur cherchée absente de la table	`RECHERCHEV("C006";...;FAUX)` sur une table sans C006
`#VALEUR!`	Opération sur un type inadapté (texte à la place d'un nombre)	`=A2+B2` quand `A2` contient `n.c.`
`#REF!`	Référence supprimée ou hors plage	Colonne supprimée à laquelle une formule se référait
`#DIV/0!`	Division par zéro (ou par une cellule vide)	`=B2/C2` avec `C2` égal à 0
`#NOM?`	Nom de fonction ou de plage inconnu (faute de frappe)	`=SOMMME(B2:B5)`
`#NOMBRE!`	Nombre invalide pour la fonction	Racine carrée d'un nombre négatif
`#NUL!`	Intersection vide de deux plages	Rare ; une espace saisie à la place d'un séparateur

L'écriture exacte de ces codes peut varier légèrement d'un tableur à l'autre ; on les reconnaît à leur forme générale. La fonction TYPE.ERREUR(cellule) renvoie le numéro de l'erreur d'une cellule : 1 pour #NUL!, 2 pour #DIV/0!, 3 pour #VALEUR!, 4 pour #REF!, 5 pour #NOM?, 6 pour #NOMBRE!, 7 pour #N/A.

Une rangée de croisillons (#####) n'est pas une erreur de calcul : la colonne est trop étroite pour afficher le résultat.

Une erreur se propage : une cellule qui contient #N/A fait renvoyer #N/A à toutes les formules qui l'utilisent, y compris SOMME. Il faut donc la traiter à la source.

2.2 Tester une cellule

Fonction	Renvoie `VRAI` quand...
`ESTVIDE(cellule)`	la cellule est vide
`ESTNUM(cellule)`	la cellule contient un nombre
`ESTTEXTE(cellule)`	la cellule contient du texte
`ESTERREUR(cellule)`	la cellule contient n'importe quelle erreur
`ESTNA(cellule)`	la cellule contient l'erreur `#N/A` seulement

Ces fonctions s'emploient dans le test d'un SI : =SI(ESTNUM(B3);B3*Taux_TVA;"Montant non numérique").

2.3 `SIERREUR`

SIERREUR(valeur;valeur_si_erreur) renvoie valeur si elle ne produit pas d'erreur, sinon valeur_si_erreur.

=SIERREUR(RECHERCHEV(A5;Table_clients;2;FAUX);"Client inconnu")

C'est pratique, mais SIERREUR masque toutes les erreurs : une faute de frappe dans le nom d'une fonction (#NOM?) ou une plage mal sélectionnée donne aussi « Client inconnu ». Deux bonnes pratiques :

choisir une valeur de remplacement qui se voit (un message, pas un 0 silencieux) ;
tester la cause précise quand c'est possible : =SI(ESTNA(EQUIV(A5;codes;0));"Client inconnu";...) ne traite que l'absence de la clé.

Pour la division, on préfère tester la cause : =SI(C2=0;"n.d.";B2/C2) plutôt que d'envelopper la division dans SIERREUR.

Tu es à mi-parcours. Dans le pack DCG, ce chapitre est déjà prêt en fiche, quiz, flashcards et infographie : tu te testes tout de suite.

Voir les packs DCG

Exemples corrigés

Cas 1 : remise client par recherche exacte, erreurs traitées

La feuille Clients contient la table (plage nommée Table_clients pour A2:D6) :

A (Code)	B (Nom)	C (Remise)	D (Délai en jours)
C001	Dupont SA	5 %	30
C002	Martin SARL	8 %	45
C003	Leroy et Fils	0 %	30
C004	Garnier SAS	10 %	60
C005	Petit SA	3 %	30

La feuille Commandes contient cinq lignes (de 5 à 9) avec le code en A et le montant brut HT en B. Formules de la ligne 5 :

Colonne	Formule
C (nom)	`=SIERREUR(RECHERCHEV(A5;Table_clients;2;FAUX);"Client inconnu")`
D (remise)	`=SIERREUR(RECHERCHEV(A5;Table_clients;3;FAUX);0)`
E (net HT)	`=B5*(1-D5)`

Résultats attendus :

Code	Brut HT	Nom	Remise	Net HT
C002	4 500,00	Martin SARL	8 %	4 140,00
C004	12 000,00	Garnier SAS	10 %	10 800,00
C006	800,00	Client inconnu	0 %	800,00
C001	2 300,00	Dupont SA	5 %	2 185,00
C003	650,00	Leroy et Fils	0 %	650,00
Total	20 250,00
	18 575,00

Contrôle de la ligne 5 : RECHERCHEV("C002";Table_clients;3;FAUX) renvoie la troisième colonne de la ligne dont le code vaut C002, soit 0,08 ; net : 4 500 × (1 - 0,08) = 4 140.

La ligne C006 : le code n'existe pas dans la table, RECHERCHEV renvoie #N/A, que SIERREUR remplace par « Client inconnu » (nom) et par 0 (remise). Un compteur d'alerte, =NB.SI(C5:C9;"Client inconnu"), renvoie 1. La remise remplacée par 0 n'est admissible que parce que ce compteur et le message « Client inconnu » signalent l'anomalie : seul, ce 0 passerait inaperçu (piège 7).

Version avec INDEX et EQUIV (même résultat, plus robuste) :

=SIERREUR(INDEX(Clients!$B$2:$B$6;EQUIV(A5;Clients!$A$2:$A$6;0));"Client inconnu")

L'oubli de FAUX. Avec =RECHERCHEV("C006";Table_clients;3) (dernier argument omis), la table étant triée par codes croissants, le tableur retient le plus grand code inférieur ou égal à C006, soit C005 : il renvoie la remise de Petit SA (3 %) pour un client qui n'existe pas, sans aucune erreur affichée.

À retenir

Conclusion à destination de la directrice commerciale : sur 20 250 € de commandes brutes, 1 675 € de remises sont accordées, soit un net de 18 575 €. Le code C006, absent de la table, est signalé « Client inconnu » et a été facturé sans remise : il faut vérifier s'il s'agit d'un nouveau client à créer ou d'une faute de saisie avant d'émettre la facture de 800 €. Les recherches sont exactes (FAUX) : une approximation sur un code client aurait appliqué la remise d'un autre client.

Cas 2 : un barème par tranches, par paliers ou progressif

La feuille Bareme (plage nommée Bareme, première colonne triée par ordre croissant) :

A (Seuil de CA)	B (Taux)	C (Cumul des tranches précédentes)
0	2 %	0
5 000	3 %	100
10 000	4 %	250
20 000	5 %	650

La colonne C se calcule : 5 000 × 2 % = 100 ; 100 + 5 000 × 3 % = 250 ; 250 + 10 000 × 4 % = 650.

Deux lectures du barème, que l'énoncé distingue (le chiffre d'affaires à évaluer est en B2) :

Taux de palier : le taux de la tranche atteinte s'applique à tout le chiffre d'affaires. Formule : =ARRONDI(B2*RECHERCHEV(B2;Bareme;2;VRAI);2).
Barème progressif : chaque taux ne s'applique qu'à la part du chiffre d'affaires qui se trouve dans sa tranche. Formule : =ARRONDI(RECHERCHEV(B2;Bareme;3;VRAI)+(B2-RECHERCHEV(B2;Bareme;1;VRAI))*RECHERCHEV(B2;Bareme;2;VRAI);2). Lecture : cumul des tranches précédentes + (part qui dépasse le seuil de la tranche) × taux de la tranche.

Résultats attendus (recalculés hors tableur) :

CA	Taux de palier	Commission par palier	Commission progressive
4 999,99	2 %	100,00	100,00
5 000,00	3 %	150,00	100,00
12 750,00	4 %	510,00	360,00
20 000,00	5 %	1 000,00	650,00
35 000,00	5 %	1 750,00	1 400,00

Détail pour un CA de 12 750 : la recherche approchée retient la ligne du seuil 10 000 (taux 4 %, cumul 250). Palier : 12 750 × 4 % = 510. Progressif : 250 + (12 750 - 10 000) × 4 % = 250 + 110 = 360.

Effet de seuil. Avec le taux de palier, passer de 4 999,99 à 5 000,00 de chiffre d'affaires fait sauter la commission de 100,00 à 150,00 (50 € pour un centime) ; avec le barème progressif, la commission est continue (100,00 dans les deux cas).

Table mal conçue. Si le barème commençait à 5 000, un chiffre d'affaires de 3 200 renverrait #N/A : la première ligne doit commencer à 0.

À retenir

Conclusion à destination de la direction : sur un chiffre d'affaires de 35 000 €, le choix entre les deux lectures représente 350 € de commission (1 750 € contre 1 400 €). Le mode par paliers incite à « pousser » une vente juste avant un seuil ; le mode progressif est plus coûteux à expliquer mais évite cet effet. Quel que soit le choix, les seuils et les taux sont dans la table Bareme, que l'on modifie sans toucher aux formules.

Cas 3 : tarif de transport à double entrée

La feuille Tarifs donne le prix d'un envoi selon la tranche de poids (colonne A, en kg, triée) et la zone (ligne 1) :

	A (Poids à partir de)	B (Zone 1)	C (Zone 2)	D (Zone 3)
2	0	8	11	15
3	10	12	16	22
4	30	20	27	38
5	100	45	60	85

Le colis pèse 42 kg et part en « Zone 2 » (poids en G1, zone en G2).

=INDEX(Tarifs!$B$2:$D$5;EQUIV(G1;Tarifs!$A$2:$A$5;1);EQUIV(G2;Tarifs!$B$1:$D$1;0))

Évaluation :

EQUIV(42;Tarifs!$A$2:$A$5;1) : plus grand seuil inférieur ou égal à 42 dans 0, 10, 30, 100 : c'est 30, en position 3.
EQUIV("Zone 2";Tarifs!$B$1:$D$1;0) : correspondance exacte dans les en-têtes : position 2.
INDEX(Tarifs!$B$2:$D$5;3;2) : ligne 3, colonne 2 de la plage de prix, soit 27.

Autres résultats : 10 kg en Zone 3 donne 22 (la valeur exacte 10 appartient à la tranche qui commence à 10) ; 9,9 kg en Zone 1 donne 8 ; 150 kg en Zone 3 donne 85.

À retenir

Conclusion à destination du service expéditions : le colis de 42 kg vers la Zone 2 coûte 27 €. La formule ne fait référence à aucun rang de colonne saisi à la main : si l'on ajoute une zone, on étend la plage et les en-têtes, et la formule s'adapte. Le contrôle de cohérence consiste à tester les valeurs exactement égales à un seuil (10, 30 et 100 kg) : elles doivent tomber dans la tranche qui commence à ce seuil.

Cas 4 : diagnostiquer cinq erreurs

Formule saisie	Résultat	Cause	Correction
`=RECHERCHEV("C006";Table_clients;3;FAUX)`	`#N/A`	Code absent de la table	Compléter la table, ou traiter par `SI(ESTNA(...);...)`
`=A2+B2` avec `A2` contenant `n.c.`	`#VALEUR!`	Texte dans une addition	Corriger la saisie, ou `=SI(ESTNUM(A2);A2+B2;"à saisir")`
`=B2/C2` avec `C2` égal à 0	`#DIV/0!`	Division par zéro	`=SI(C2=0;"n.d.";B2/C2)`
`=SOMMME(B2:B5)`	`#NOM?`	Faute de frappe dans le nom de la fonction	`=SOMME(B2:B5)`
Formule renvoyant vers une colonne supprimée	`#REF!`	Référence supprimée	Restituer la colonne, ou reconstruire la formule

À retenir

Conclusion à destination du responsable du classeur : ces cinq erreurs ont cinq causes différentes ; l'enveloppe SIERREUR systématique les rendrait toutes invisibles. Un modèle fiable corrige la cause chaque fois que c'est possible et ne remplace l'erreur par un message que pour les cas attendus (code client inconnu, division par zéro d'un indicateur).

Vocabulaire essentiel

Terme	Définition
Table de référence	Plage de données maintenue une fois, interrogée par les formules
Clé de recherche	Valeur utilisée pour retrouver une ligne (code client)
Recherche exacte	Correspondance parfaite exigée (`FAUX`)
Recherche approchée	Plus grande valeur inférieure ou égale, sur table triée (`VRAI`)
Barème	Table de seuils et de taux
Palier, tranche	Intervalle de valeurs auquel s'applique un taux
Propagation d'erreur	Transmission d'une erreur aux formules qui l'utilisent
`SIERREUR`	Fonction qui remplace toute erreur par une valeur choisie

Points clés à retenir

RECHERCHEV cherche dans la première colonne de la table ; le dernier argument vaut FAUX pour l'exact et VRAI (ou omis) pour l'approché.
Une recherche approchée exige une table triée par ordre croissant et une première tranche à 0 (ou en dessous de toute valeur).
Codes et références : exact. Tranches de montant ou de poids : approché.
INDEX et EQUIV ne dépendent pas d'un rang de colonne et permettent la double entrée.
EQUIV(...;0) cherche une valeur exacte, EQUIV(...;1) la plus grande valeur inférieure ou égale.
Une erreur se propage : on la traite à la source.
SIERREUR masque toutes les erreurs : à réserver aux cas attendus et à doubler d'un message visible.
Un barème s'applique par paliers (taux de la tranche sur tout le montant) ou de façon progressive (taux de chaque tranche sur sa part) : l'énoncé tranche.

Pièges fréquents

Omettre le dernier argument de RECHERCHEV : la recherche est approchée sans prévenir.
Recherche approchée sur une table non triée : résultat imprévisible.
Table de barème qui ne commence pas à 0 : #N/A pour les petites valeurs.
Rang de colonne figé : l'insertion d'une colonne dans la table décale le résultat sans erreur visible.
Chercher une clé qui n'est pas dans la première colonne de la table avec RECHERCHEV.
Enrober toutes les formules de SIERREUR : les fautes de frappe disparaissent.
Remplacer une erreur par 0 : un taux nul ou un montant nul passe inaperçu.
Confondre palier et progressif : l'écart de commission peut atteindre plusieurs centaines d'euros.

Q&R pour le tuteur IA

Q : Que signifie le dernier argument de RECHERCHEV et quand faut-il le mettre à FAUX ? R : Il indique le type de recherche. FAUX exige une correspondance exacte : on l'emploie pour un code client ou une référence. VRAI (ou omis) retient la plus grande valeur inférieure ou égale dans une table triée : on l'emploie pour un barème par tranches.

Q : Pourquoi INDEX et EQUIV sont-elles plus robustes que RECHERCHEV ? R : Parce qu'elles désignent la colonne renvoyée par sa plage et non par un rang saisi à la main : insérer une colonne ne casse pas la formule. De plus, la clé n'a pas à être dans la première colonne, et on peut combiner deux EQUIV pour une double entrée.

Q : Que fait SIERREUR et quel est son danger ? R : SIERREUR(valeur;valeur_si_erreur) remplace toute erreur par la valeur choisie. Son danger est de masquer aussi les erreurs de construction (faute de frappe, plage mal choisie) : on préfère tester la cause précise quand on le peut.

Q : Quelle erreur apparaît quand RECHERCHEV ne trouve pas un code client ? R : #N/A, valeur non disponible. On peut la traiter avec SIERREUR, ou avec SI(ESTNA(...);...) pour ne traiter que ce cas.

Q : Quelle est la différence entre une commission par palier et une commission progressive ? R : Par palier, le taux de la tranche atteinte s'applique à tout le chiffre d'affaires. En progressif, chaque taux ne s'applique qu'à la part du chiffre d'affaires qui se situe dans sa tranche, ce qui évite l'effet de seuil. Pour un CA de 12 750 avec le barème du cours : 510 € par palier, 360 € en progressif.

Q : Comment trouver le prix d'un colis de 42 kg en Zone 2 dans une grille poids par zone ? R : =INDEX(prix;EQUIV(42;poids;1);EQUIV("Zone 2";en-têtes;0)). Le premier EQUIV retient la tranche de poids (en approché sur des seuils triés), le second la colonne de la zone (en exact).

Tu as lu le cours. Passe maintenant à la pratique :