Générer et corriger des formules avec l’IA
Apprenez à demander des formules fiables à une intelligence artificielle, à comprendre leur logique, à diagnostiquer les erreurs et à tester les corrections dans Excel ou Google Sheets. Une méthode concrète pour automatiser des calculs sans perdre le contrôle des résultats.
À la fin de ce cours, vous saurez :
- Formuler un besoin de calcul précis.
- Décrire correctement la structure d’un tableau à une IA.
- Générer des formules compatibles avec votre tableur.
- Utiliser les références relatives, absolues et mixtes.
- Employer des fonctions courantes et conditionnelles.
- Créer des recherches entre tableaux.
- Comprendre les erreurs de formule les plus fréquentes.
- Demander une correction argumentée à l’IA.
- Tester les cas normaux et les cas limites.
- Contrôler la confidentialité et la fiabilité des calculs.
Savoir saisir des données dans un tableur, identifier les cellules par leurs coordonnées (A1, B2, C3) et comprendre les opérations mathématiques de base.
Les exemples utilisent principalement la syntaxe française d’Excel et de Google Sheets : noms de fonctions en français, arguments séparés par des points-virgules et nombres décimaux avec virgule. Certains noms de fonctions ou séparateurs varient selon le logiciel, sa version et les paramètres régionaux.
1. Pourquoi utiliser l’IA pour les formules ?
Un tableur peut réaliser des calculs simples ou complexes à l’aide de formules. Mais trouver la fonction adaptée, construire une formule comportant plusieurs conditions ou comprendre un message d’erreur peut demander du temps.
Une intelligence artificielle générative peut assister ce travail en traduisant une demande formulée en langage naturel en une proposition de formule.
Générer
Proposer une formule à partir d’un objectif de calcul.
Expliquer
Décomposer une formule existante pour en comprendre la logique.
Corriger
Identifier des erreurs possibles et suggérer des modifications.
Optimiser
Proposer une formule plus lisible ou mieux adaptée à la situation.
L’IA propose du texte, notamment des formules. C’est le moteur de calcul du tableur qui exécute ces formules. Une proposition d’IA n’est donc pas un résultat validé.
2. Excel, Google Sheets et IA : quelles différences ?
Excel et Google Sheets utilisent des concepts proches, mais leur compatibilité n’est pas totale.
| Élément | Excel | Google Sheets |
|---|---|---|
| Formules classiques | Oui | Oui |
| Fonctions conditionnelles | Oui | Oui |
| Fonctions de recherche | Oui, selon la version | Oui |
| Tableaux structurés | Références structurées propres à Excel | Fonctionnement différent |
| Fonctions matricielles | Disponibles selon les fonctions et versions | Disponibles avec ses propres règles |
| Assistance par IA | Selon les fonctionnalités Microsoft accessibles | Selon les fonctionnalités Google accessibles |
Une IA externe peut également aider à écrire des formules pour les deux logiciels.
Il faut toujours préciser le logiciel, sa langue et, lorsque c’est pertinent, sa version. Une formule valide dans Google Sheets peut ne pas fonctionner dans une ancienne version d’Excel, et inversement.
3. Comprendre la structure d’une formule
Une formule commence généralement par le signe égal =.
Exemple simple
=B2*C2Cette formule multiplie la valeur de B2 par celle de C2.
Si B2 contient 8 et C2 contient 12,50 :
8 × 12,50 = 100,00Les éléments d’une formule
| Élément | Exemple | Rôle |
|---|---|---|
| Signe égal | = | Introduit la formule. |
| Référence | B2 | Désigne une cellule. |
| Opérateur | * | Effectue une multiplication. |
| Fonction | SOMME | Réalise une opération prédéfinie. |
| Plage | B2:B20 | Désigne un ensemble de cellules. |
| Argument | B2:B20 | Information transmise à une fonction. |
4. Décrire précisément le besoin à l’IA
La qualité de la formule proposée dépend des informations fournies.
Une demande insuffisante
Cette demande ne précise ni le calcul attendu ni l’organisation des cellules.
Une demande exploitable
Réponse attendue
=B2*C2Cette formule est recopiable vers le bas, car les références sont relatives.
5. La méthode de prompt en sept éléments
Excel ou Google Sheets, avec la version si nécessaire.
Indiquer la langue des fonctions et les paramètres régionaux.
Décrire les colonnes et les lignes.
Préciser le résultat attendu.
Définir les règles métier et les cas particuliers.
Demander la logique de chaque partie.
Demander des exemples vérifiables.
6. Maîtriser les références relatives et absolues
Les références déterminent la manière dont une formule évolue lorsqu’elle est copiée.
| Référence | Type | Comportement |
|---|---|---|
| A1 | Relative | Ligne et colonne peuvent évoluer. |
| $A$1 | Absolue | Ligne et colonne restent fixes. |
| $A1 | Mixte | Colonne fixe, ligne variable. |
| A$1 | Mixte | Ligne fixe, colonne variable. |
Exemple : appliquer un taux de TVA
Le montant HT est en D2 et le taux de TVA est stocké en H1.
=D2*$H$1Lors de la recopie, D2 devient D3, D4, etc., tandis que $H$1 reste inchangé.
Si le taux se trouve dans H1 et que la formule utilise simplement H1, une recopie vers le bas peut transformer la référence en H2, H3, etc. Le calcul risque alors d’utiliser une mauvaise cellule.
7. Générer les fonctions de calcul courantes
| Besoin | Formule | Résultat recherché |
|---|---|---|
| Total | =SOMME(D2:D20) | Addition des valeurs. |
| Moyenne | =MOYENNE(D2:D20) | Moyenne arithmétique. |
| Maximum | =MAX(D2:D20) | Plus grande valeur. |
| Minimum | =MIN(D2:D20) | Plus petite valeur. |
| Nombre de valeurs numériques | =NB(D2:D20) | Comptage des nombres. |
| Nombre de cellules non vides | =NBVAL(D2:D20) | Comptage des cellules renseignées. |
| Arrondi à deux décimales | =ARRONDI(D2;2) | Valeur arrondie. |
Ces fonctions peuvent être proposées par une IA, mais leur calcul est exécuté par le tableur.
8. Générer des formules conditionnelles
Une formule conditionnelle effectue un traitement différent selon le résultat d’un test.
Exemple : identifier les commandes importantes
Une commande est qualifiée d’importante si son montant HT est supérieur ou égal à 500 €.
=SI(D2>=500;"Importante";"Standard")| Montant | Résultat |
|---|---|
| 499,99 € | Standard |
| 500,00 € | Importante |
| 750,00 € | Importante |
Le signe >= signifie « supérieur ou égal ».
Combiner plusieurs conditions
On souhaite identifier les commandes d’au moins 500 € dont le statut est « Validée ».
Le montant se trouve en D2 et le statut en E2.
=SI(ET(D2>=500;E2="Validée");"Prioritaire";"Normale")La fonction ET exige que les deux conditions soient vraies.
9. Compter et additionner selon des critères
Compter les commandes validées
Les statuts figurent en E2:E100.
=NB.SI(E2:E100;"Validée")Calculer le montant des commandes validées
Les montants figurent en D2:D100.
=SOMME.SI(E2:E100;"Validée";D2:D100)Appliquer plusieurs critères
On souhaite totaliser les montants des commandes validées du service Commercial.
Les services figurent en F2:F100.
=SOMME.SI.ENS(D2:D100;E2:E100;"Validée";F2:F100;"Commercial")Dans SOMME.SI.ENS, la plage à additionner est indiquée en premier, suivie des plages de critères et de leurs conditions.
10. Générer une formule de recherche
Les fonctions de recherche permettent de retrouver une information dans un tableau à partir d’une clé.
Exemple
Un tableau de référence contient :
| Colonne H | Colonne I |
|---|---|
| Code produit | Prix HT |
| P001 | 12 € |
| P002 | 18 € |
| P003 | 25 € |
Le code recherché est en A2. Le tableau de référence occupe H2:I4.
Avec RECHERCHEV
=RECHERCHEV(A2;$H$2:$I$4;2;FAUX)Cette formule recherche une correspondance exacte dans la première colonne de la plage, puis renvoie le prix situé dans la deuxième colonne.
Avec RECHERCHEX
=RECHERCHEX(A2;$H$2:$H$4;$I$2:$I$4;"Code introuvable")RECHERCHEX est disponible dans Google Sheets et dans les versions d’Excel qui la prennent en charge. Il faut vérifier sa disponibilité dans l’environnement utilisé.
Une recherche peut échouer à cause d’espaces invisibles, de différences entre nombres et textes ou de codes absents. Il faut contrôler les clés avant de modifier la formule.
11. Utiliser les dates dans les formules
Les dates sont stockées sous une forme permettant les calculs dans les tableurs, à condition qu’elles soient reconnues comme de véritables dates.
Calculer une durée en jours
La date de début est en B2 et la date de fin en C2.
=C2-B2Si B2 contient le 1er novembre 2026 et C2 le 11 novembre 2026, la durée écoulée est de 10 jours.
Afficher la date du jour
=AUJOURDHUI()Cette fonction est dynamique : sa valeur peut évoluer lors du recalcul du fichier.
Éviter une durée négative
=SI(C2<B2;"Dates incohérentes";C2-B2)Cette formule suppose que B2 et C2 contiennent des dates valides.
12. Comprendre les erreurs de formule
Un message d’erreur peut provenir d’une formule incorrecte, d’une donnée incompatible ou d’une référence invalide.
| Erreur | Signification fréquente | Contrôle |
|---|---|---|
| #DIV/0! | Division par zéro ou cellule vide assimilée à zéro. | Vérifier le dénominateur. |
| #N/A | Valeur recherchée indisponible. | Contrôler la clé de recherche. |
| #REF! | Référence invalide. | Vérifier les cellules et plages. |
| #VALEUR! | Type de donnée incompatible. | Vérifier les textes et nombres. |
| #NOM? | Nom de fonction ou référence nommée non reconnu. | Vérifier la syntaxe et la langue. |
| #NOMBRE! | Argument numérique ou calcul invalide. | Examiner les valeurs utilisées. |
| Erreur d’analyse | Syntaxe non reconnue, notamment dans Google Sheets. | Vérifier les séparateurs et parenthèses. |
Les messages peuvent varier selon Excel, Google Sheets, la langue et le type d’erreur. Une cellule peut aussi afficher un résultat incorrect sans aucun message d’erreur.
13. Corriger une division par zéro
Situation
On souhaite calculer un prix moyen en divisant un montant par une quantité.
=D2/C2Si C2 vaut zéro, le tableur affiche une erreur de division.
Correction explicite
=SI(C2=0;"Quantité nulle";D2/C2)Cette formule indique clairement pourquoi le calcul ne peut pas être réalisé.
Autre possibilité
=SIERREUR(D2/C2;"Calcul impossible")SIERREUR intercepte plusieurs types d’erreurs, pas uniquement la division par zéro.
Remplacer systématiquement une erreur par zéro peut fausser les totaux et les moyennes. Il faut choisir un message ou une valeur de remplacement en fonction de la règle métier.
14. Corriger une formule de recherche
Formule initiale
=RECHERCHEV(A2;H2:I4;2;VRAI)Cette formule utilise une recherche approximative. Elle n’est pas adaptée si l’objectif est de retrouver un code produit exact.
Correction
=RECHERCHEV(A2;$H$2:$I$4;2;FAUX)Deux corrections ont été effectuées :
- Le quatrième argument devient FAUX pour imposer une correspondance exacte.
- La plage de recherche est fixée pour permettre la recopie.
15. Diagnostiquer une erreur avec l’IA
Pour obtenir une correction utile, il faut fournir le message d’erreur, la formule et la structure des données.
Ce qu’une bonne réponse doit fournir
- Un diagnostic explicite.
- Une formule corrigée.
- Une explication de la correction.
- Des tests sur des codes présents et absents.
- Les limites de la solution.
16. Vérifier les formules sans message d’erreur
Les erreurs les plus dangereuses ne sont pas toujours visibles.
Exemple
Une formule calcule le total de D2 à D9 alors que les données vont jusqu’à D10.
=SOMME(D2:D9)Le tableur affiche un nombre, mais la dernière ligne est exclue.
Correction
=SOMME(D2:D10)L’absence d’erreur affichée ne prouve pas qu’une formule est correcte. Il faut aussi vérifier que le calcul correspond à la règle attendue et couvre toutes les données pertinentes.
17. Construire un protocole de test
Une formule fiable doit fonctionner dans plusieurs situations, pas uniquement sur un exemple favorable.
| Type de test | Exemple | Objectif |
|---|---|---|
| Cas normal | Quantité 10, prix 5 € | Vérifier le calcul habituel. |
| Valeur nulle | Quantité 0 | Contrôler les cas particuliers. |
| Valeur négative | Quantité -2 | Vérifier la règle métier. |
| Valeur limite | Montant exactement 500 € | Contrôler les seuils. |
| Donnée manquante | Cellule vide | Vérifier le comportement attendu. |
| Référence absente | Code produit inconnu | Contrôler la recherche. |
Les résultats attendus doivent être définis avant de considérer le test comme réussi.
18. Optimiser une formule sans la rendre opaque
L’IA peut proposer des formules plus courtes, mais une formule compacte n’est pas nécessairement plus facile à maintenir.
Exemple
Une formule comportant plusieurs SI imbriqués peut devenir difficile à relire.
Dans certains cas, une table de correspondance accompagnée d’une fonction de recherche est plus facile à vérifier.
- Elle calcule le résultat attendu.
- Elle reste compatible avec le logiciel.
- Elle traite les cas particuliers.
- Elle peut être comprise et maintenue.
- Elle a été testée sur des données représentatives.
19. Protéger les données transmises à l’IA
Les formules professionnelles peuvent concerner des données financières, commerciales ou personnelles.
Il est généralement possible de demander une formule en décrivant les colonnes sans transmettre les données réelles.
Exemple de demande prudente
Ne copiez pas des données sensibles dans un outil externe sans autorisation. Vérifiez les conditions de traitement, les règles internes et les obligations applicables.
20. Cas pratique complet : automatiser un suivi de commandes
Situation professionnelle
Une petite entreprise souhaite automatiser le calcul de ses commandes dans Excel ou Google Sheets.
Elle dispose des colonnes suivantes :
| Colonne | Information |
|---|---|
| A | Référence commande |
| B | Quantité |
| C | Prix unitaire HT |
| D | Montant HT |
| E | Remise |
| F | Montant net HT |
| G | Statut |
Règle commerciale
Une remise de 5 % est accordée lorsque le montant HT atteint ou dépasse 500 €.
Sinon, aucune remise n’est appliquée.
Données
| Commande | Quantité | Prix unitaire HT |
|---|---|---|
| CMD001 | 10 | 30 € |
| CMD002 | 20 | 25 € |
| CMD003 | 15 | 40 € |
| CMD004 | 0 | 50 € |
Étape 1 — Calculer le montant HT
=B2*C2Cette formule est placée en D2 puis recopiée jusqu’en D5.
Étape 2 — Calculer la remise
La colonne E contient le montant de la remise en euros, et non son taux.
=SI(D2>=500;D2*5%;0)Étape 3 — Calculer le montant net HT
=D2-E2Étape 4 — Identifier le statut
Une commande de quantité nulle doit être signalée comme « À vérifier ». Les autres sont classées « Calculée ».
=SI(B2=0;"À vérifier";"Calculée")Étape 5 — Résultats corrigés
| Commande | Montant HT | Remise | Net HT | Statut |
|---|---|---|---|---|
| CMD001 | 300 € | 0 € | 300 € | Calculée |
| CMD002 | 500 € | 25 € | 475 € | Calculée |
| CMD003 | 600 € | 30 € | 570 € | Calculée |
| CMD004 | 0 € | 0 € | 0 € | À vérifier |
Étape 6 — Calculer les totaux
=SOMME(D2:D5)Total brut HT : 1 400 €.
=SOMME(E2:E5)Total des remises : 55 €.
=SOMME(F2:F5)Total net HT : 1 345 €.
Étape 7 — Tester les cas limites
| Cas | Résultat attendu |
|---|---|
| Montant 499,99 € | Aucune remise. |
| Montant 500 € | Remise de 25 €. |
| Montant 600 € | Remise de 30 €. |
| Quantité 0 | Statut « À vérifier ». |
Étape 8 — Prompt de correction
Les montants calculés sont cohérents avec les règles fournies : 1 400 € HT avant remises, 55 € de remises et 1 345 € HT après remises.
La commande CMD004 est signalée comme « À vérifier ».
Attention : cette méthode ne traite pas explicitement les quantités négatives, les cellules vides ou les prix invalides. Des règles complémentaires seraient nécessaires avant un déploiement professionnel complet.
21. Les erreurs fréquentes à éviter
| Erreur | Conséquence | Correction |
|---|---|---|
| Ne pas préciser le logiciel | Fonction incompatible. | Indiquer Excel ou Google Sheets. |
| Oublier la langue | Nom de fonction non reconnu. | Préciser les paramètres régionaux. |
| Utiliser une mauvaise plage | Calcul incomplet. | Vérifier les limites. |
| Oublier une référence absolue | Résultat incorrect après recopie. | Fixer la cellule nécessaire. |
| Employer une recherche approximative | Mauvaise correspondance. | Utiliser une recherche exacte si nécessaire. |
| Masquer toutes les erreurs | Problèmes invisibles. | Afficher des messages explicites. |
| Tester un seul exemple | Cas limites ignorés. | Prévoir plusieurs tests. |
| Copier des données sensibles | Risque de divulgation. | Utiliser des données fictives ou anonymisées. |
22. Vérifiez votre compréhension
La quantité est en B2 et le prix unitaire HT en C2. Quelle formule permet de calculer le montant HT ?
Afficher la correction
=B2*C2. Elle multiplie la quantité par le prix unitaire HT.Un taux est stocké en H1. Quelle référence permet de conserver cette cellule lors de la recopie vers le bas et vers la droite ?
Afficher la correction
$H$1, car la colonne et la ligne sont fixées.Une remise s’applique à partir de 500 € inclus. Faut-il utiliser > ou >= ?
Afficher la correction
>=, qui signifie « supérieur ou égal ».Une formule affiche #DIV/0!. Quelle vérification faut-il effectuer en priorité ?
Afficher la correction
Une formule affiche un résultat numérique sans message d’erreur. Peut-on considérer qu’elle est correcte ?
Afficher la correction
23. Questions fréquentes
1. Une IA peut-elle créer une formule Excel ?
Oui. Elle peut proposer une formule à partir d’une description précise du besoin.
2. Peut-elle créer une formule Google Sheets ?
Oui, en tenant compte de la syntaxe et des fonctions disponibles.
3. Faut-il connaître les fonctions avant d’utiliser l’IA ?
Il n’est pas nécessaire de toutes les mémoriser, mais il faut comprendre la logique des calculs pour vérifier les résultats.
4. Pourquoi préciser la langue du tableur ?
Les noms de fonctions et les séparateurs peuvent varier selon la langue et les paramètres régionaux.
5. Pourquoi une formule ne fonctionne-t-elle pas ?
Elle peut contenir une erreur de syntaxe, une référence invalide ou utiliser une fonction indisponible.
6. L’IA peut-elle corriger une formule ?
Oui, en proposant un diagnostic et une correction à tester.
7. Qu’est-ce qu’une référence absolue ?
Une référence dont la ligne et la colonne sont fixées, par exemple $A$1.
8. Pourquoi utiliser SI ?
Pour obtenir un résultat différent selon qu’une condition est vraie ou fausse.
9. Quelle différence entre SOMME.SI et SOMME.SI.ENS ?
SOMME.SI traite un critère ; SOMME.SI.ENS permet d’en combiner plusieurs.
10. À quoi sert RECHERCHEV ?
À rechercher une valeur dans la première colonne d’une plage et à retourner une information associée.
11. RECHERCHEX fonctionne-t-elle partout ?
Non. Il faut vérifier la version d’Excel ou l’environnement utilisé.
12. Que signifie #DIV/0! ?
Une division par zéro ou par une cellule vide assimilée à zéro.
13. Que signifie #N/A ?
Une valeur attendue n’a pas été trouvée ou n’est pas disponible.
14. Que signifie #REF! ?
Une référence de cellule ou de plage est invalide.
15. Peut-on masquer les erreurs ?
Oui, avec certaines fonctions, mais il faut éviter de masquer des problèmes importants.
16. Pourquoi tester une valeur limite ?
Pour vérifier notamment l’application correcte des seuils.
17. Une formule peut-elle être fausse sans afficher d’erreur ?
Oui, par exemple lorsqu’une plage oublie une ligne.
18. L’IA peut-elle optimiser une formule ?
Oui, mais il faut vérifier que la nouvelle formule conserve exactement le comportement attendu.
19. Peut-on transmettre des données professionnelles à une IA ?
Uniquement si leur traitement est autorisé et conforme aux règles applicables.
20. Quelle méthode adopter pour progresser ?
Commencer par des calculs simples, comprendre chaque formule, puis augmenter progressivement la complexité.
24. Glossaire — 25 termes essentiels
| Terme | Définition |
|---|---|
| 1. Formule | Expression calculée par un tableur. |
| 2. Fonction | Opération prédéfinie utilisable dans une formule. |
| 3. Cellule | Intersection d’une ligne et d’une colonne. |
| 4. Plage | Ensemble de cellules. |
| 5. Argument | Valeur ou référence transmise à une fonction. |
| 6. Opérateur | Symbole représentant une opération. |
| 7. Référence relative | Référence pouvant évoluer lors d’une recopie. |
| 8. Référence absolue | Référence fixant ligne et colonne. |
| 9. Référence mixte | Référence fixant une seule dimension. |
| 10. SOMME | Fonction additionnant des valeurs. |
| 11. MOYENNE | Fonction calculant une moyenne arithmétique. |
| 12. SI | Fonction permettant un test conditionnel. |
| 13. ET | Fonction exigeant plusieurs conditions vraies. |
| 14. NB.SI | Fonction comptant selon un critère. |
| 15. SOMME.SI | Fonction additionnant selon un critère. |
| 16. SOMME.SI.ENS | Fonction additionnant selon plusieurs critères. |
| 17. RECHERCHEV | Fonction recherchant dans la première colonne d’une plage. |
| 18. RECHERCHEX | Fonction de recherche avec plages de recherche et de retour distinctes. |
| 19. SIERREUR | Fonction permettant de traiter certaines erreurs. |
| 20. AUJOURDHUI | Fonction renvoyant la date courante. |
| 21. Syntaxe | Règles d’écriture d’une formule. |
| 22. Cas limite | Situation particulière permettant de tester une règle. |
| 23. Diagnostic | Recherche de la cause d’une erreur. |
| 24. Prompt | Consigne adressée à une intelligence artificielle. |
| 25. Validation | Contrôle permettant de confirmer qu’un résultat est conforme. |
25. Ressources pour approfondir
- Support Microsoft Excel — Fonctions, formules et résolution des erreurs.
- Aide Google Sheets — Utilisation des formules et fonctions.
- CNIL — Protection des données personnelles.
À retenir
- L’IA peut proposer, expliquer et corriger des formules.
- Le logiciel, la langue et la structure du tableau doivent être précisés.
- Une formule commence généralement par le signe égal.
- Les références relatives et absolues jouent un rôle essentiel lors de la recopie.
- Les fonctions conditionnelles permettent d’appliquer des règles.
- Les fonctions de recherche doivent être configurées selon le type de correspondance attendu.
- Un message d’erreur aide au diagnostic, mais son absence ne prouve pas la justesse du calcul.
- Les cas limites doivent être testés.
- Les erreurs ne doivent pas être masquées sans justification.
- Les données confidentielles doivent être protégées.
- La validation humaine reste indispensable.
Apprenez à concevoir des supports structurés, lisibles et convaincants en utilisant l’IA pour préparer les messages, les visuels et la narration.
Entraînez-vous sur ce cours
Appliquez les méthodes de génération, de diagnostic et de correction des formules dans des situations professionnelles progressives.
Passer à la pratique →Exercices du cours
Entraînez-vous avec 15 exercices progressifs. Ouvrez un exercice pour répondre, puis afficher sa correction.
