Définir ce que l’on veut additionner

« Quel est le montant du mois ? » paraît une question simple jusqu’à ce que deux services donnent des résultats différents. L’un additionne toutes les commandes, l’autre uniquement les factures réglées, un troisième retire les avoirs. Avant d’utiliser SOMME.SI.ENS, écrivez la mesure en une phrase : montant des encaissements enregistrés à Kénitra pendant octobre, selon la date d’encaissement, en MAD et hors lignes en attente. Cette définition détermine les colonnes nécessaires.

Nous utiliserons un registre entièrement fictif, sans lien avec les tarifs de GeniusCenter.ma. Il contient un identifiant, une date d’opération, une ville, un statut et un montant signé. Chaque ligne représente un mouvement, pas un client ni un mois. La colonne Montant conserve des nombres ; la devise apparaît dans l’en-tête de présentation ou le format. En fixant cette granularité, on évite d’additionner simultanément des lignes détaillées et leurs sous-totaux, erreur qui double parfois les résultats sans produire de message Excel.

Préparer un petit registre de contrôle

Créez un tableau tblMouvements avec Date, Ville, Statut et Montant. Saisissez six opérations : le 2 octobre, Kénitra, Réglé, 4 800 ; le 5 octobre, Rabat, Réglé, 3 200 ; le 12 octobre, Kénitra, En attente, 2 100 ; le 16 octobre, Kénitra, Réglé, 3 900 ; le 31 octobre à 18 h 30, Kénitra, Réglé, 1 200 ; le 1er novembre, Kénitra, Réglé, 800.

Tous les événements sont datés de 2026 pour cet exercice. Le total de l’ensemble vaut 16 000 MAD. Les opérations d’octobre valent 15 200 MAD, mais celles d’octobre réglées valent 13 100 MAD. Le sous-ensemble réglé de Kénitra en octobre représente 9 900 MAD. Ces quatre valeurs servent de points de repère. Elles démontrent qu’un résultat différent n’est pas forcément faux : il peut reposer sur une autre population. Le rapport doit afficher la population retenue et la période de calcul.

Lire la syntaxe dans le bon ordre

SOMME.SI.ENS commence par la plage à additionner, puis reçoit des couples plage de critères et critère. Pour notre registre, =SOMME.SI.ENS(tblMouvements[Montant];tblMouvements[Ville];"Kénitra";tblMouvements[Statut];"Réglé") renvoie 10 700 MAD, car aucune restriction de date n’exclut encore novembre. Chaque ligne doit respecter simultanément les deux conditions. La ville et le statut fonctionnent donc comme un ET logique.

Les plages doivent avoir des dimensions cohérentes et représenter les mêmes enregistrements. Un décalage d’une ligne entre la colonne Ville et la colonne Montant peut associer le montant d’une opération au statut de la suivante. L’utilisation d’un tableau structuré réduit ce risque et rend la formule lisible. Attention à la confusion avec SOMME.SI : l’ordre des arguments n’est pas identique. Les formules présentées suivent une configuration française avec point-virgule ; adaptez les noms et séparateurs si votre installation les affiche différemment, sans changer le rôle des arguments.

Filtrer une période avec deux bornes sûres

Placez en H2 la date =DATE(2026;10;1) et en H3 la date =DATE(2026;11;1). La formule complète devient =SOMME.SI.ENS(tblMouvements[Montant];tblMouvements[Ville];"Kénitra";tblMouvements[Statut];"Réglé";tblMouvements[Date];">="&H2;tblMouvements[Date];"<"&H3). Elle retient les dates à partir du 1er octobre inclus et avant le 1er novembre, donc toute la journée du 31 octobre même si une heure est présente.

Le résultat attendu est 9 900 MAD. Utiliser une borne « inférieur ou égal au 31 octobre » peut exclure les opérations de l’après-midi si cette borne correspond au début du jour. Les bornes de début inclus et de mois suivant exclus rendent la convention plus robuste. Vérifiez cependant que Date contient de vraies valeurs de date et d’heure, pas des textes qui ressemblent à une date. Modifier simplement le format d’affichage d’un texte ne garantit pas sa conversion en valeur exploitable pour une comparaison chronologique.

Utiliser des paramètres au lieu de réécrire les critères

Un rapport utile permet de changer la ville ou le statut sans modifier la formule. Placez la ville en H4, le statut en H5 et remplacez les textes fixes par ces références. Ajoutez des listes de validation pour limiter les variantes de saisie. Le modèle devient un petit moteur de lecture : H4 égale Rabat produit 3 200 MAD pour la même période, tandis que H4 égale Kénitra produit 9 900.

Affichez les paramètres près du résultat, et pas seulement dans une feuille cachée. Une capture de 9 900 MAD ne permet pas de comprendre la mesure si la ville et la période n’y figurent plus. Prévoyez également le cas « toutes les villes » avec une logique explicite, par exemple une formule différente sans critère Ville. Ne considérez pas automatiquement une cellule vide comme synonyme de totalité : elle pourrait signifier paramètre oublié, ville inconnue ou recherche des enregistrements dont la ville manque réellement.

Représenter les avoirs et les mouvements négatifs

Ajoutez une opération d’avoir de -600 MAD, datée du 20 octobre à Kénitra, avec le statut Réglé selon notre convention de démonstration. Le montant net réglé d’octobre pour Kénitra devient 9 300 MAD. Le signe négatif porte une signification ; le remplacer par une valeur absolue ferait passer le résultat à 10 500 MAD et masquerait l’effet de l’avoir.

Selon l’organisation, un avoir, un remboursement et une annulation peuvent être enregistrés dans des colonnes ou statuts différents. Ce tutoriel ne fixe pas leur traitement comptable. Il invite à définir une convention de reporting approuvée, à l’appliquer uniformément et à expliquer les exclusions. Pour un indicateur commercial brut, vous pouvez présenter séparément 9 900 MAD de mouvements positifs et -600 MAD de corrections, puis le net de 9 300 MAD. Cette décomposition permet au lecteur de comprendre pourquoi une hausse du nombre d’opérations peut coïncider avec une baisse du montant net.

Exprimer un OU sans compter deux fois

Supposons que le rapport retienne les statuts Réglé ou Rapproché. Une méthode lisible consiste à additionner deux SOMME.SI.ENS ayant les mêmes autres critères, une pour chaque statut. C’est correct lorsque chaque ligne possède un seul statut parmi ces valeurs distinctes. Documentez la liste afin qu’un nouveau statut ne soit pas exclu pendant plusieurs mois par oubli.

En revanche, additionner « Ville = Rabat » et « Produit = Clavier » ne calcule pas proprement l’union des deux ensembles : une vente de clavier à Rabat répond aux deux conditions et serait comptée deux fois. Il faut prévoir une colonne EstRetenu qui représente la règle métier, ou une méthode de calcul adaptée à l’union. Le raisonnement sur les ensembles précède le choix de la fonction. Dessiner trois petites populations sur papier aide souvent à repérer le chevauchement avant qu’il se transforme en écart de plusieurs milliers de dirhams dans le rapport.

Contrôler les résultats plutôt que le seul total

Une somme égale à zéro peut signifier qu’aucune ligne ne correspond, que les montants sont tous nuls ou que des positifs et négatifs se compensent. Associez donc le résultat à un nombre de lignes calculé avec NB.SI.ENS sur les mêmes critères. Dans le registre initial, Kénitra réglé en octobre comprend trois mouvements ; après l’avoir, il en comprend quatre pour un montant net de 9 300 MAD.

Cette paire montant et volume facilite le diagnostic. Ajoutez un contrôle des villes non reconnues et des montants stockés en texte. Évitez les critères trop permissifs avec astérisque sur les noms de villes : ils peuvent fusionner involontairement un lieu et une mention annexe. Un référentiel de villes et une étape de normalisation sont préférables à une formule qui essaie de deviner la saisie. Contrôlez également les clés d’opération : SOMME.SI.ENS additionne chaque ligne admissible, y compris une transaction importée deux fois. Elle ne détecte pas les doublons à votre place.

Un autre contrôle utile consiste à comparer la synthèse détaillée au total source. Si les villes du référentiel sont exhaustives et exclusives, la somme des résultats de toutes les villes doit retrouver le total calculé sans critère Ville pour la même période et les mêmes statuts. Un écart révèle souvent une ville vide, un libellé non normalisé ou une catégorie absente de la synthèse. Ne créez pas une ligne « ajustement » pour faire disparaître cet écart. Identifiez les mouvements concernés et décidez de leur traitement avec le responsable des données. Ce rapprochement transforme la synthèse en système contrôlable plutôt qu’en simple assemblage de formules qui semblent donner des montants raisonnables.

Exercice corrigé : une synthèse par agence

Créez un second tableau de quatre ventes : Rabat, Réglé, 1 000 MAD ; Rabat, En attente, 400 MAD ; Kénitra, Réglé, 600 MAD ; Rabat, Réglé, -100 MAD. Toutes portent une date d’octobre 2026. Calculez le net réglé de Rabat, le net réglé total et le nombre de mouvements réglés de Rabat.

Les réponses sont respectivement 900 MAD, 1 500 MAD et deux mouvements. Le total de toutes les lignes vaut 1 900 MAD, ce qui permet de rapprocher les 1 500 réglés et les 400 en attente. Modifiez ensuite le statut de la ligne de 400 en Réglé. Les résultats deviennent 1 300 MAD pour Rabat, 1 900 MAD réglés au total et trois mouvements réglés de Rabat. Si votre formule ne change pas, vérifiez les critères, le périmètre du tableau et le mode de recalcul avant de corriger arbitrairement les résultats.

Transformer la formule en règle de gestion explicable

Une synthèse maintenable décrit sa définition, ses paramètres, ses exclusions et son calendrier d’actualisation. Notez la date de l’export, le propriétaire de la source et la signification de chaque statut. Pour un rapport mensuel, conservez le résultat validé et les données qui l’ont produit afin de pouvoir expliquer une correction ultérieure. Une cellule portant seulement « CA » ne suffit pas à définir une mesure.

Enfin, ne moyennez pas des taux mensuels sans réfléchir aux dénominateurs. Deux agences ayant respectivement 10 et 100 opérations n’ont pas le même poids. Si l’objectif est un taux global, calculez la somme des numérateurs divisée par la somme des dénominateurs. SOMME.SI.ENS sert alors à produire des composantes contrôlées, puis une formule distincte calcule le ratio. Cette séparation entre sélection, agrégation et interprétation rend le reporting plus facile à vérifier et réduit les discussions où chacun défend un résultat obtenu sur une population différente.

Références

Retour au blog →