Utiliser l’IA pour écrire des formules Excel vérifiables
À partir d’un tableau de six commandes, demandez à l’IA une formule SUMIFS, vérifiez le total attendu et testez les changements avant de l’adapter à vos données Excel.
Vous avez besoin de connaître la valeur des commandes en ligne payées. Un assistant d’IA peut proposer une formule Excel, mais « calculer le total des ventes » laisse deux décisions dans l’ombre : quelles commandes compter et ce que représente la colonne des montants.
Commencez par un petit tableau dont vous pouvez calculer le résultat vous-même. Demandez la formule, son explication et les lignes prises en compte. Modifiez ensuite une donnée pour voir si le résultat réagit correctement. Vous pourrez ainsi vérifier la formule avant de l’utiliser dans un classeur plus volumineux.
Formulez explicitement la règle d’inclusion
Dans ce jeu de données fictif, chaque ligne correspond à une commande. Amount représente le montant total de la commande en dollars américains, et non un prix unitaire. Une ligne ne compte que si Channel vaut Online et si Status vaut Paid. Un zéro est un montant enregistré ; une valeur manquante demanderait une vérification.
Collez le bloc séparé par des tabulations dans la cellule A1 d’une feuille vierge. Vérifiez que les en-têtes occupent A1:D1 et les six commandes les lignes 2 à 7. Si tout se retrouve dans la colonne A, séparez le texte collé avec le délimiteur de tabulation avant de continuer.
Order Channel Status Amount
O101 Online Paid 120
O102 Store Paid 80
O103 Online Pending 60
O104 Online Paid 90
O105 Online Refunded 40
O106 Online Paid 0
Conservez ces libellés d’exemple sans les modifier pendant l’exercice, même si votre interface Excel est dans une autre langue. Les critères textuels de la formule se rapportent au contenu des cellules, et non à la langue de l’interface.
Sans l’aide de l’IA, repérez les lignes à compter : O101, O104 et O106. Leur somme est 120 + 90 + 0 = 210. La commande payée en magasin ne compte pas, pas plus que les commandes en ligne en attente ou remboursées.
Demandez une formule, pas seulement un total
Un assistant textuel suffit pour cette étape ; il n’a pas besoin d’accéder à votre véritable classeur professionnel. Fournissez-lui le petit tableau fictif et l’emplacement de ses cellules :
J’utilise Excel. Les en-têtes sont dans A1:D1 et les données dans A2:D7.
A correspond à Order, B à Channel, C à Status et D à Amount en USD.
Chaque ligne représente une commande et D contient le montant total de la commande.
Écris une formule pour F2 qui additionne Amount uniquement lorsque Channel vaut
Online et Status vaut Paid. Inclus les deux conditions. Utilise des fonctions Excel
ordinaires et leurs noms anglais. Explique chaque plage et indique les identifiants
des commandes à inclure. Ne modifie pas les données sources.
[Collez le tableau d’exemple.]
Voici une formule de référence pour cette tâche :
=SUMIFS(D2:D7,B2:B7,"Online",C2:C7,"Paid")
Il s’agit d’une réponse de référence rédigée pour cet article, et non de l’affirmation que tous les assistants produiront la même réponse. La documentation de Microsoft sur SUMIFS explique comment additionner les valeurs qui répondent à plusieurs critères.
Lisez la formule dans l’ordre : additionner D2:D7, mais uniquement pour les lignes où B2:B7 correspond à Online et C2:C7 à Paid. Les trois plages couvrent les mêmes six lignes. Une formule qui ne tiendrait compte que du canal inclurait des commandes qui n’ont pas été payées.
Ajoutez dans F1 un libellé descriptif, par exemple Paid online orders (USD), puis placez la formule dans F2. Le libellé doit rappeler la règle afin que la personne qui lit le résultat sache ce que signifie 210.
Vérifiez le résultat et les lignes sélectionnées
Si F2 affiche 210, comparez également les identifiants inclus. Dans un autre jeu de données, le bon total pourrait être obtenu par coïncidence. Ici, les lignes qui doivent contribuer au résultat sont les lignes 2, 5 et 7.
Un résultat erroné constitue un indice utile. Un total de 310, par exemple, correspond à l’ensemble des montants en ligne de cet exemple : 120 + 60 + 90 + 40 + 0. Cela invite à vérifier si la condition liée au paiement a été omise ; c’est un indice de diagnostic, pas la preuve de la cause dans tous les classeurs.
Si Excel refuse la formule, vérifiez les noms de fonctions et les séparateurs d’arguments attendus par votre installation. L’exemple utilise des noms de fonctions anglais et des virgules. Une installation qui emploie des points-virgules peut exiger ; entre les arguments. Ne remplacez pas les guillemets droits autour de Online et Paid par des guillemets typographiques. Ces ajustements concernent la syntaxe ; ils ne changent pas les commandes qui doivent être comptées.
Le guide de Microsoft sur la détection des erreurs dans les formules propose des contrôles pour les erreurs et les résultats inattendus. Ajouter immédiatement IFERROR(...,0) masquerait un symptôme avant que vous en compreniez la cause.
Testez des changements dont vous pouvez prévoir l’effet
Effectuez ces modifications une par une, puis rétablissez les données d’origine après chaque test :
| Modification | Valeur attendue dans F2 | Ce que le test vérifie |
|---|---|---|
Remplacer Pending par Paid dans C4 | 270 | O103 remplit désormais les critères et ajoute 60 |
| Remplacer 80 par 800 dans D3 | 210 | Une commande en magasin reste exclue |
| Remplacer 0 par 5 dans D7 | 215 | La dernière ligne de données est incluse |
| Rétablir le tableau d’origine | 210 | Les modifications de test ont été annulées |
Ces cas ne servent pas seulement à vérifier que la formule initiale affiche un nombre. Ils testent l’entrée d’une commande dans le groupe sélectionné, une modification importante en dehors de ce groupe et la dernière ligne de la plage.
La formule et ces modifications ont été vérifiées pour cet article dans un moteur indépendant de calcul de feuilles de calcul. Elles ne constituaient pas un test de la fonction de modification de classeurs d’une application d’IA. Exécutez les contrôles dans votre propre installation d’Excel avant d’adapter la formule.
N’étendez les données qu’après avoir validé le petit exemple
Cette formule de référence s’arrête volontairement à la ligne 7. Si vous ajoutez une commande à la ligne 8, elle ne sera pas incluse. Étendez les trois plages ensemble ou utilisez un tableau Excel dont les références suivent les lignes. Vérifiez une nouvelle commande qui remplit les critères au lieu de supposer que la plage s’est agrandie.
Pour vos propres données, remplacez les libellés d’exemple par les valeurs réellement présentes dans les cellules. Décidez comment traiter les montants incomplets ou les statuts incohérents avant de considérer le résultat comme un rapport. Formater du texte comme une devise ne suffit pas à établir que chaque valeur source est numérique.
Lorsque vous demandez une correction à l’IA, décrivez le test qui a échoué : « Remplacer C4 par Paid devrait ajouter 60, mais F2 n’a pas changé. Vérifie les plages et les deux critères. » Gardez le tableau d’origine à disposition. Acceptez la formule lorsque la règle, les lignes sélectionnées et les changements prévus concordent, et non simplement parce que l’explication paraît plausible.
Références
- Microsoft : fonction SUMIFS : ordre des arguments, critères multiples et exigences relatives aux plages.
- Microsoft : détecter les erreurs dans les formules : contrôles des erreurs de formule et des résultats involontaires.
