Aller au contenu
Bureautique avancée et outils collaboratifs · L2

Leçon 1 sur 5

Fonctions avancées, références et calculs conditionnels

Je sais construire dans un tableur des formules fiables qui utilisent les bonnes références, prennent des décisions avec SI et calculent des totaux selon des critères.

  • 1 h 30
  • 6 exercices corrigés
  • QCM de 6 questions

À la fin de la leçon, vous saurez :

  • Distinguer références relatives, absolues et mixtes et choisir la bonne avant de recopier une formule
  • Écrire des tests logiques avec SI, ET, OU et des SI imbriqués
  • Calculer des sommes, des comptages et des moyennes conditionnels avec SOMME.SI.ENS, NB.SI.ENS et MOYENNE.SI
  • Rechercher une information dans une table avec RECHERCHEV, RECHERCHEX ou INDEX et EQUIV
  • Repérer et traiter les messages d'erreur d'un tableur

Avant de commencer : Maîtrise de base d'un tableur (saisie, formules simples, SOMME, MOYENNE, mise en forme) ; cours de techniques de secrétariat.

1Je découvre

Mariama Sow est assistante de direction chez Kolon Agri SARL, un distributeur d'engrais, de semences et de petit matériel agricole dont le siège est à Matoto, à Conakry. L'entreprise a trois agences : Kindia, Labé et Kankan.

Chaque lundi, le directeur général, M. Condé, lui demande la même chose : le chiffre d'affaires de chaque agence, le nombre de grosses factures, les remises accordées et la commission de chaque commercial. Jusqu'ici, Mariama recopiait les prix à la main depuis le catalogue, additionnait avec la calculatrice et tapait les résultats dans le tableur. Il lui fallait toute une matinée, et la semaine dernière une erreur de saisie a fait payer à une commerciale une commission trop élevée.

Mariama sait pourtant que le tableur peut faire tout cela seul, à condition d'écrire les bonnes formules, une seule fois, puis de les recopier.

Comment écrire des formules qui cherchent les prix, testent des conditions et calculent des totaux par agence, sans erreur et sans tout refaire chaque semaine ?

2Je comprends

1. Le classeur d'exemple

Toute la leçon utilise le classeur de Kolon Agri. Les prix sont des exemples de travail, pas des prix réels du marché.

Feuille Produits (lignes 2 à 6) :

A : CodeB : DésignationC : Prix unitaire (GNF)
2P01Engrais NPK, sac de 50 kg350 000
3P02Urée, sac de 50 kg320 000
4P03Semences de riz, sac de 25 kg275 000
5P04Pulvérisateur 16 L450 000
6P05Arrosoir 10 L60 000

Feuille Ventes (lignes 2 à 9) : A N° de facture, B Date, C Agence, D Commercial, E Code produit, F Quantité, G Prix unitaire, H Montant, I Remise.

N°DateAgenceCommercialCodeQtéMontant (GNF)
112/01/2026KindiaAïssatouP01103 500 000
215/01/2026LabéIbrahimaP0382 200 000
320/01/2026KankanFantaP02123 840 000
428/01/2026KindiaAïssatouP0431 350 000
503/02/2026KankanFantaP01207 000 000
610/02/2026LabéIbrahimaP05251 500 000
717/02/2026KindiaMoussaP0361 650 000
824/02/2026KankanFantaP0515900 000

Les formules sont écrites avec les noms de fonctions français de Microsoft Excel et le point-virgule comme séparateur d'arguments. LibreOffice Calc en français utilise les mêmes noms pour la plupart de ces fonctions ; Google Sheets accepte aussi les noms français selon la langue du compte.

2. Références relatives, absolues et mixtes

Quand on recopie une formule vers le bas ou vers la droite, le tableur décale les références. C'est utile, mais dangereux si une cellule doit rester fixe.

TypeÉcritureCe qui se passe à la recopieUsage typique
RelativeA2la ligne et la colonne se décalentcalcul ligne par ligne
Absolue$A$2rien ne bougetaux, paramètre, table de référence
Mixte (colonne fixe)$A2seule la ligne se décalegrille à double entrée
Mixte (ligne fixe)A$2seule la colonne se décalegrille à double entrée

Dans Excel, la touche F4 fait défiler les quatre écritures quand le curseur est sur une référence. Dans LibreOffice Calc, c'est Maj + F4.

Exemple : le taux de commission de base, 2 %, est saisi une seule fois dans la cellule K1. Le calcul en J2, recopié vers le bas, doit toujours viser K1 :

J2 : =H2*\(K\)1     recopiée en J3 : =H3*\(K\)1   (H se décale, K1 reste fixe)
J2 : =H2*K1       recopiée en J3 : =H3*K2     (erreur : K2 est vide, résultat 0)

Pour une grille de tarifs (prix en colonne A, quantités en ligne 1), une seule formule en B2 suffit pour toute la grille : =$A2*B$1. La colonne A (prix) reste fixe, la ligne 1 (quantités) reste fixe ; recopiée en D4, elle devient =$A4*D$1.

Une autre solution consiste à nommer la cellule (onglet Formules, Définir un nom) : la formule =H2*TauxCommission est plus lisible qu'une référence absolue, et le nom désigne toujours la même cellule.

Méthode

Choisir le type de référence avant de recopier

  1. Écrire la formule pour la première cellule seulement.
  2. Pour chaque référence, se demander : quand je descends, la ligne doit-elle changer ?
  3. Puis : quand je vais à droite, la colonne doit-elle changer ?
  4. Placer le symbole dollar devant ce qui ne doit pas changer (F4 dans Excel).
  5. Recopier, puis contrôler deux cellules éloignées en affichant leur formule.

3. Les tests logiques : SI, ET, OU

La fonction SI choisit entre deux résultats selon une condition : =SI(test_logique;valeur_si_vrai;valeur_si_faux). Kolon Agri accorde une remise de 5 % sur toute facture d'au moins 3 000 000 GNF ; en I2, recopiée jusqu'à I9 : =SI(H2>=3000000;H2*5%;0).

Les tests utilisent les opérateurs =, <> (différent), <, >, <=, >=. Un texte s'écrit entre guillemets : =SI(C2="Kankan";"Zone Est";"Autre").

Pour combiner plusieurs conditions, ET(cond1;cond2;…) est VRAI si toutes les conditions sont vraies, OU(cond1;cond2;…) si au moins une l'est.

Exemple : livraison gratuite si la facture dépasse 3 000 000 GNF et si l'agence est Kindia : `=SI(ET(H2>3000000;C2="Kindia");"Gratuite";"Payante")`.

Quand il y a plus de deux cas, on imbrique des SI. La grille de commission annuelle de Kolon Agri est la suivante : moins de 2 000 000 GNF de ventes, pas de commission ; de 2 000 000 inclus à 5 000 000 exclu, 1 % ; 5 000 000 GNF et plus, 2 %. Si le total du commercial est en M2 : =SI(M2<2000000;0;SI(M2<5000000;M2*1%;M2*2%)).

On teste les seuils dans l'ordre : dès qu'un test est vrai, le tableur s'arrête. Dans les versions récentes d'Excel (2019 et Microsoft 365), SI.CONDITIONS évite l'imbrication : =SI.CONDITIONS(M2<2000000;0;M2<5000000;M2*1%;VRAI;M2*2%).

Exemple

Remise des huit factures (5 % si le montant est d'au moins 3 000 000 GNF)

Factures concernées : n° 1 (3 500 000), n° 3 (3 840 000), n° 5 (7 000 000).

Remises : 3 500 000 x 0,05 = 175 000 ; 3 840 000 x 0,05 = 192 000 ; 7 000 000 x 0,05 = 350 000.

Les cinq autres factures donnent 0.

Total des remises = 175 000 + 192 000 + 350 000 = 717 000 GNF.

4. Calculer selon des critères : SOMME.SI.ENS, NB.SI.ENS, MOYENNE.SI

Ces fonctions répondent directement aux questions du directeur, sans filtrer ni recopier.

FonctionSyntaxeRésultat
SOMME.SI=SOMME.SI(plage_critère;critère;plage_somme)somme avec un critère
SOMME.SI.ENS=SOMME.SI.ENS(plage_somme;plage1;critère1;plage2;critère2…)somme avec un ou plusieurs critères
NB.SI / NB.SI.ENS=NB.SI.ENS(plage1;critère1;plage2;critère2…)nombre de lignes qui vérifient les critères
MOYENNE.SI=MOYENNE.SI(plage_critère;critère;plage_moyenne)moyenne avec un critère

Attention à l'ordre des arguments : dans SOMME.SI, la plage à additionner vient en dernier ; dans SOMME.SI.ENS, elle vient en premier.

Un critère numérique avec opérateur s'écrit entre guillemets (">=3000000"). S'il dépend d'une cellule, on concatène : ">="&K2.

Chiffre d'affaires de Kindia :           =SOMME.SI(C2:C9;"Kindia";H2:H9)
Factures de Kankan d'au moins 3 M GNF :  =SOMME.SI.ENS(H2:H9;C2:C9;"Kankan";H2:H9;">=3000000")
Nombre de factures de Kankan :           =NB.SI(C2:C9;"Kankan")
Montant moyen d'une facture de Kindia :  =MOYENNE.SI(C2:C9;"Kindia";H2:H9)

Exemple

Tableau de bord du lundi, calculé sur les huit factures

Kindia : 3 500 000 + 1 350 000 + 1 650 000 = 6 500 000 GNF.

Labé : 2 200 000 + 1 500 000 = 3 700 000 GNF.

Kankan : 3 840 000 + 7 000 000 + 900 000 = 11 740 000 GNF.

Contrôle : 6 500 000 + 3 700 000 + 11 740 000 = 21 940 000 GNF = SOMME(H2:H9).

Factures de Kankan d'au moins 3 M GNF : 3 840 000 + 7 000 000 = 10 840 000 GNF.

Montant moyen à Kindia : 6 500 000 / 3 ≈ 2 166 667 GNF.

Le contrôle croisé (la somme des agences doit égaler le total général) est un réflexe professionnel : il détecte une agence mal orthographiée (« Kindia » et « Kindia  » avec une espace en trop ne sont pas le même texte).

5. Rechercher une information dans une table

Au lieu de recopier les prix du catalogue, on les recherche à partir du code produit. RECHERCHEV cherche une valeur dans la première colonne d'une table et renvoie la valeur d'une autre colonne de la même ligne. En G2 :

=RECHERCHEV(E2;Produits!\(A\)2:\(C\)6;3;FAUX)

E2 est la valeur cherchée, Produits!$A$2:$C$6 la table (en référence absolue pour la recopie), 3 le numéro de la colonne à renvoyer (le prix) et FAUX impose une correspondance exacte. Avec VRAI ou sans ce dernier argument, le tableur cherche une valeur approchée dans une colonne supposée triée, ce qui donne des prix faux sur un catalogue.

Si la table n'est pas figée, elle glisse à la recopie (Produits!A3:C7, puis A4:C8…) et les premiers codes en sortent : #N/A. Limites de RECHERCHEV : elle ne cherche qu'à droite de la première colonne, et le numéro de colonne devient faux si l'on insère une colonne dans la table.

RECHERCHEX (Excel 2021 et Microsoft 365) lève ces limites :

=RECHERCHEX(E2;Produits!\(A\)2:\(A\)6;Produits!\(C\)2:\(C\)6;"Code inconnu")

INDEX et EQUIV fonctionnent dans toutes les versions et dans LibreOffice : EQUIV trouve la position du code, INDEX renvoie la valeur à cette position.

=INDEX(Produits!\(C\)2:\(C\)6;EQUIV(E2;Produits!\(A\)2:\(A\)6;0))

Le 0 de EQUIV demande une correspondance exacte.

6. Comprendre et traiter les erreurs

MessageCause habituelleRemède
#N/Avaleur cherchée absente de la table (code mal saisi, espace en trop)corriger la saisie ; SIERREUR pour un message clair
#DIV/0!division par zéro ou par une cellule videtester le diviseur avec SI
#VALEUR!texte dans un calcul (« 10 sacs » au lieu de 10)saisir des nombres seuls
#REF!cellule référencée suppriméeannuler la suppression ou réécrire la formule
#NOM?fonction mal orthographiée, nom non défini, texte sans guillemetsvérifier l'orthographe
####colonne trop étroite (ce n'est pas une erreur de calcul)élargir la colonne

SIERREUR(valeur;valeur_si_erreur) remplace l'erreur par un message : `=SIERREUR(RECHERCHEV(E2;Produits!$A$2:$C$6;3;FAUX);"Code à vérifier")`. À utiliser avec discernement : masquer une erreur ne la corrige pas.

3Je retiens

Je retiens

Référence relative : se décale ; absolue (dollar devant la colonne et la ligne) : fixe ; mixte : fixe sur un seul axe.

Paramètres et tables de référence : toujours en référence absolue ou nommée.

SI(test;si vrai;si faux) ; ET : toutes les conditions ; OU : au moins une.

SI imbriqués : tester les seuils dans l'ordre ; SI.CONDITIONS dans les versions récentes.

SOMME.SI : plage à additionner en dernier ; SOMME.SI.ENS : plage à additionner en premier.

RECHERCHEV(valeur;table;n° colonne;FAUX) : FAUX pour une correspondance exacte.

INDEX/EQUIV et RECHERCHEX cherchent dans n'importe quelle colonne.

Toujours un contrôle croisé : somme des parties = total.

4Erreurs fréquentes

  • Recopier une formule qui vise un taux sans le figer : les lignes suivantes donnent 0 ou un résultat faux.
  • Oublier FAUX dans RECHERCHEV : le tableur renvoie un prix approché, sans message d'erreur.
  • Inverser l'ordre des arguments entre SOMME.SI et SOMME.SI.ENS.
  • Mal ordonner des SI imbriqués (tester « < 5 000 000 » avant « < 2 000 000 ») : le cas « 0 % » n'est jamais atteint.
  • Saisir « 10 sacs » ou « 350.000 » au lieu d'un nombre : #VALEUR! ou résultat faux.
  • Masquer toutes les erreurs avec SIERREUR sans chercher leur cause.

5Je m’exerce

1Exercice 1

(Questions de cours)

a) Quelle est la différence entre A2, $A$2, $A2 et A$2 ?

b) Que signifie le dernier argument FAUX dans RECHERCHEV ?

c) Citez deux limites de RECHERCHEV et une fonction qui les lève.

Voir le corrigéCacher le corrigé

a) A2 est relative : la ligne et la colonne se décalent à la recopie. $A$2 est absolue : rien ne bouge. $A2 fige la colonne A, la ligne se décale. A$2 fige la ligne 2, la colonne se décale.

b) FAUX demande une correspondance exacte : si la valeur n'existe pas, le résultat est #N/A au lieu d'une valeur approchée.

c) Elle ne cherche que dans la première colonne de la table et ne renvoie qu'une colonne située à sa droite ; le numéro de colonne est fixe et devient faux si l'on insère une colonne. RECHERCHEX ou la combinaison INDEX/EQUIV lèvent ces limites.

2Exercice 2

La cellule F5 contient =D5*$B$1+E$3. On la recopie en G8. Quelle formule obtient-on ? Justifiez.

Voir le corrigéCacher le corrigé

De F5 à G8, on se déplace d'une colonne vers la droite et de trois lignes vers le bas. D5 (relative) devient E8 ; $B$1 (absolue) reste $B$1 ; E$3 (ligne fixe) devient F$3. Formule obtenue : =E8*$B$1+F$3.

3Exercice 3

Écrivez la formule de la colonne Statut (en N2) : « À relancer » si la facture n'est pas payée (L2 = "Non") et si son montant H2 dépasse 2 000 000 GNF ; sinon « RAS ».

Voir le corrigéCacher le corrigé
=SI(ET(L2="Non";H2>2000000);"À relancer";"RAS")

Les deux conditions doivent être vraies en même temps, d'où ET. Une facture non payée de 1 500 000 GNF donne « RAS » ; une facture payée de 7 000 000 GNF aussi.

4Exercice 4

À partir du tableau des ventes de la leçon, donnez la formule et le résultat :

a) du nombre de factures d'au moins 3 000 000 GNF ;

b) du chiffre d'affaires réalisé par Fanta ;

c) du chiffre d'affaires de l'agence de Labé en février 2026 (les dates sont en colonne B).

Voir le corrigéCacher le corrigé

a) =NB.SI(H2:H9;">=3000000") donne 3 (factures 1, 3 et 5).

b) =SOMME.SI(D2:D9;"Fanta";H2:H9) donne 3 840 000 + 7 000 000 + 900 000 = 11 740 000 GNF.

c) On combine l'agence et deux bornes de date :

=SOMME.SI.ENS(H2:H9;C2:C9;"Labé";B2:B9;">="&DATE(2026;2;1);B2:B9;"<"&DATE(2026;3;1))

Une seule facture de Labé en février : la n° 6, soit 1 500 000 GNF. La borne « < 1er mars » évite d'oublier le 28 février.

5Exercice 5

Calculez avec la grille de commission (0 sous 2 000 000 GNF, 1 % de 2 000 000 inclus à 5 000 000 exclu, 2 % au-delà) la commission de chacun des quatre commerciaux, en donnant d'abord leur total de ventes.

Voir le corrigéCacher le corrigé
CommercialFacturesTotal des ventes (GNF)TrancheCommission (GNF)
Aïssatou1 et 43 500 000 + 1 350 000 = 4 850 0001 %48 500
Ibrahima2 et 62 200 000 + 1 500 000 = 3 700 0001 %37 000
Fanta3, 5 et 811 740 0002 %234 800
Moussa71 650 0000 %0

Total des commissions : 48 500 + 37 000 + 234 800 = 320 300 GNF.

6Exercice 6

(Type examen) M. Condé veut un onglet « Synthèse » qui se met à jour seul.

a) Proposez la disposition de l'onglet (agences en lignes, indicateurs en colonnes).

b) Écrivez, pour la ligne de Kindia (nom de l'agence en A2), les formules du chiffre d'affaires, du nombre de factures et du montant moyen, de façon qu'elles puissent être recopiées pour les autres agences.

c) Donnez les résultats pour les trois agences et la formule de contrôle.

d) Expliquez pourquoi ce classeur est plus fiable que la méthode utilisée jusque-là par Mariama.

Voir le corrigéCacher le corrigé

a) Colonne A : Agence (Kindia, Labé, Kankan) ; B : Chiffre d'affaires ; C : Nombre de factures ; D : Montant moyen ; une ligne Total en bas.

b) Pour la ligne 2, avec des plages absolues pour pouvoir recopier vers le bas :

B2 : =SOMME.SI(Ventes!\(C\)2:\(C\)9;A2;Ventes!\(H\)2:\(H\)9)
C2 : =NB.SI(Ventes!\(C\)2:\(C\)9;A2)
D2 : =SIERREUR(B2/C2;0)

Le critère est la cellule A2 (relative) : recopiée en ligne 3, la formule vise A3 (Labé).

c) Kindia : 6 500 000 GNF, 3 factures, moyenne ≈ 2 166 667 GNF. Labé : 3 700 000 GNF, 2 factures, moyenne 1 850 000 GNF. Kankan : 11 740 000 GNF, 3 factures, moyenne ≈ 3 913 333 GNF. Contrôle : =SOMME(B2:B4)=SOMME(Ventes!H2:H9) doit afficher VRAI (21 940 000 GNF des deux côtés).

d) Les prix viennent du catalogue par recherche (plus de recopie manuelle), les totaux se recalculent dès qu'une vente est ajoutée ou corrigée, le contrôle croisé signale les incohérences et le taux de commission n'est saisi qu'une fois. Le travail d'une matinée devient une simple vérification.

Cherchez d’abord seul, sur votre cahier, puis ouvrez le corrigé pour comparer.

6Je vérifie

Choisissez une réponse pour chaque question : la correction s’affiche aussitôt.

1On recopie =B2*$C$1 de D2 vers D5. On obtient :
Voir la réponse

Réponse B : =B5*$C$1. B2 est relative et descend de trois lignes ; $C$1 est absolue et ne bouge pas.

2Dans la formule =$A2*B$1 recopiée dans toute une grille :
Voir la réponse

Réponse B : la colonne A et la ligne 1 restent fixes. Le symbole dollar fige ce qu'il précède : la colonne dans $A2, la ligne dans B$1.

3=ET(H2>3000000;C2="Kindia") renvoie VRAI pour :
Voir la réponse

Réponse B : une facture de 3 500 000 GNF à Kindia. ET exige les deux conditions, et 3 000 000 n'est pas strictement supérieur à 3 000 000.

4Dans SOMME.SI.ENS, la plage à additionner se place :
Voir la réponse

Réponse A : en premier argument. C'est l'inverse de SOMME.SI, où elle vient en dernier.

5Une RECHERCHEV sans le dernier argument FAUX :
Voir la réponse

Réponse B : peut renvoyer une valeur approchée fausse sans message d'erreur. Par défaut, la recherche est approchée et suppose une première colonne triée.

6Le message #N/A après une RECHERCHEV signifie le plus souvent :
Voir la réponse

Réponse C : que la valeur cherchée n'est pas dans la table. Code mal saisi, espace en trop ou table mal figée à la recopie.

Karamö, votre enseignant

Il a lu cette leçon. Posez-lui votre question : il vous répond ici.

Connectez-vous pour poser vos questions à Karamö.

Vous avez fini la leçon ?

Avec un compte Kharandi gratuit, vous cochez les leçons terminées et vous suivez votre progression, cours par cours.

Karamö
Un point pas clair ? Dites à Karamö ce qui vous bloque dans cette leçon : il vous l’explique autrement, pas à pas, sans faire le travail à votre place. Demander à Karamö
Signaler une erreur dans cette leçon

Toutes les leçons de Bureautique avancée et outils collaboratifs

Pose ta question à Karamö