Excel : SOMME.SI.ENS pour analyser un grand livre par compte et par mois (tutoriel illustré)

Un grand livre de plusieurs milliers de lignes ne se lit pas : il se synthétise. La fonction SOMME.SI.ENS (SUMIFS en anglais) permet de construire en quelques minutes un tableau de charges par compte et par mois, qui se met à jour dès que l’on ajoute des écritures. Ce tutoriel détaille chaque étape.

Ce que nous allons construire

À partir d’un extrait de grand livre (date, compte, libellé, débit), nous voulons un tableau qui donne, pour chaque compte de charge, le total débité chaque mois.

Étape 1 : préparer les données

Placez les écritures dans une feuille « Grand livre », en colonnes : Date, Compte, Libellé, Débit, Crédit. Les règles qui évitent 90 % des problèmes :

• une ligne = une écriture, pas de lignes vides ni de totaux au milieu ;
• les dates sont de vraies dates (alignées à droite par défaut), non du texte ;
• les comptes ont tous le même format (tous du texte ou tous des nombres) ;
• les montants sont des nombres (pas de texte avec espaces ou lettres).

Figure 1 : l’extrait de grand livre (capture reconstituée). Les données occupent A1:E8.

Étape 2 : préparer le tableau de synthèse

Dans une zone libre (ici les colonnes G à I), créez :

1. En G1 le libellé « Compte », puis en G2:G4 la liste des comptes à analyser : 6111, 6125, 6136.
2. En H1 la date 01/01/2026 et en I1 la date 01/02/2026. Appliquez le format de cellule « mmm-aa » pour qu’elles s’affichent « janv-26 » et « févr-26 » : la cellule contient bien une date complète, indispensable au calcul.

Étape 3 : écrire la formule

Sélectionnez H2 et saisissez :

=SOMME.SI.ENS($D$2:$D$8;$B$2:$B$8;$G2;$A$2:$A$8;">="&H$1;$A$2:$A$8;"<="&FIN.MOIS(H$1;0))

Décryptage :

Le symbole & concatène l’opérateur (“>=”) et la date : indispensable pour que la date soit lue comme un nombre dans le critère.

Figure 2 : la formule dans la barre de formule (repère 3), les mois en en-tête (repère 2) et les comptes en colonne G (repère 1). Le cadre vert montre la zone remplie par la recopie.

Étape 4 : recopier la formule

Pourquoi les $ ? Ils figent les parties de la formule qui ne doivent pas bouger :

• $D$2:$D$8, $B$2:$B$8, $A$2:$A$8 : les plages de données restent les mêmes pour toutes les cellules ;
• $G2 : on fige la colonne G, mais pas la ligne (la formule suit les comptes vers le bas) ;
• H$1 : on fige la ligne 1, mais pas la colonne (la formule suit les mois vers la droite).

Sélectionnez H2, copiez (Ctrl+C), sélectionnez H2:I4 puis collez (Ctrl+V). Excel recalcule chaque cellule.

Résultats attendus :

Vérification : 50 000 + 32 000 = 82 000 pour 6111 en janvier ✔.

Étape 5 : ajouter des totaux et des contrôles

• Total par mois : =SOMME(H2:H4).
• Contrôle de complétude : le total du tableau doit égaler le total de la colonne Débit des comptes concernés (=SOMME.SI.ENS($D$2:$D$8;$B$2:$B$8;"61*") si les comptes sont en texte).
• Mise en forme conditionnelle pour repérer un mois en hausse de plus de 20 % : voir la mise en forme conditionnelle pour des alertes automatiques.

Variantes utiles

Erreurs fréquentes

1. Plages de tailles différentes : #VALEUR !. Toutes les plages doivent avoir exactement le même nombre de lignes.
2. Références relatives au lieu d’absolues : les plages glissent à la recopie et les résultats deviennent faux.
3. Dates en texte : un critère de date ne fonctionne pas. Convertissez avec =DATEVAL() ou Données > Convertir.
4. Comptes de formats mixtes : 6111 (nombre) n’est pas égal à “6111” (texte). Uniformisez avec =TEXTE(B2;"0").
5. Séparateur de formules : dans Excel en français, les arguments se séparent par ;, et non par , (réglage régional).

Cas pratique : une balance âgée avec SOMME.SI.ENS

Un fichier de factures clients contient, pour chaque facture, le client, la date d’échéance et le solde restant dû. On veut ventiler l’encours de chaque client par ancienneté de retard.

Date de l’analyse : 30/06. Les tranches se définissent par rapport à cette date (mise à la place d’AUJOURDHUI() pour figer le calcul, dans une cellule nommée Date_analyse).

Résultat pour le client Atlas au 30/06 :

La somme des quatre tranches doit égaler l’encours total (170 000) : c’est le contrôle de la construction. Les 40 000 MAD de plus de 60 jours demandent une action (relance ferme, provision à examiner : voir l’article sur les créances douteuses).

Les points d’attention des formules à dates

1. Concaténer l’opérateur et la date avec & : ">="&Date_analyse, jamais ">=Date_analyse".
2. Les dates sont des nombres : Date_analyse-30 donne la date trente jours plus tôt.
3. Des bornes qui ne se chevauchent pas : « < » d’un côté, « >= » de l’autre, sinon une facture est comptée deux fois.
4. Des plages de même taille : toutes les plages d’un SOMME.SI.ENS doivent avoir le même nombre de lignes.
5. Des noms de plages (Solde, Client, Echeance) rendent les formules lisibles et évitent les décalages quand le tableau grandit.

Totaux par période avec des critères de dates

SOMME.SI.ENS accepte des opérateurs de comparaison dans ses critères, ce qui permet de ventiler un grand livre par mois, trimestre ou exercice. Avec une colonne de dates en A, les comptes en B, les débits en D et les crédits en E, et des dates de début et de fin de période en cellules G1 et H1 :

Les critères de texte acceptent les caractères génériques (* pour une suite de caractères, ? pour un seul). Pour des comptes enregistrés comme nombres, la formule "6*" ne fonctionne pas : il faut soit convertir la colonne en texte, soit utiliser des bornes numériques (">=6000" et "<7000").

Un tableau de bord mensuel des charges par classe

On construit un tableau de douze colonnes (mois) avec en ligne les familles de charges (achats, charges externes, personnel…) :

Le tableau se met à jour à chaque nouvel export du grand livre. Une mise en forme conditionnelle colore les mois dont la charge dépasse de plus de 20 % la moyenne des mois précédents.

Comparer deux exercices

Avec un grand livre sur deux ans, une colonne « Exercice » (=ANNEE([@Date])) permet de comparer :

Les formules : N−1 =SOMME.SI.ENS(Débit;Compte;$A2;Exercice;2025), N identique avec 2026, variation =C2-B2, pourcentage =D2/B2. Les variations supérieures à un seuil (par exemple 20 % et 20 000 MAD) sont mises en évidence et font l’objet d’une explication dans le dossier. Dans l’exemple, la prime d’assurance (+46,8 %) appelle une vérification : nouveau contrat, hausse tarifaire, régularisation ?

Repérer les plus gros montants

Pour lister les cinq plus grosses charges d’un compte, associer GRANDE.VALEUR et RECHERCHEX :

=GRANDE.VALEUR(SI(Compte="6136";Débit);1) (formule matricielle dans les anciennes versions) pour le premier montant, puis =RECHERCHEX(valeur;Débit;Libellé) pour retrouver le libellé. Cette liste des cinq premières lignes donne une idée immédiate de la concentration de la dépense et sert de base à la revue des fournisseurs.

Les cinq erreurs qui produisent des totaux faux sans message d’erreur

1. Critère ">=01/01/2026" entre guillemets sans concaténation : la date doit être concaténée avec & (">="&G1).
2. Plage de somme décalée d’une ligne par rapport aux plages de critères.
3. Montants importés en texte (alignés à gauche) ignorés par la somme.
4. Comptes avec espaces ou zéros en tête : "6111 " n’est pas égal à "6111".
5. Plage de références relative après recopie : sans $, les plages glissent.

Un contrôle de total par un moyen indépendant (tableau croisé dynamique, SOMME.SI) à chaque modification de formule détecte ces erreurs avant qu’elles n’aient de conséquences.

Pour aller plus loin

Quand les données dépassent quelques milliers de lignes, transformez la plage en tableau Excel (Ctrl+T) : les plages deviennent des références structurées (Tableau1[Débit]) qui s’étendent automatiquement. La version 365 ajoute les fonctions dynamiques (FILTRE, UNIQUE, SOMME.SI.ENS avec tableaux), mais SOMME.SI.ENS reste compatible avec toutes les versions.

Cette synthèse alimente naturellement un tableau de bord mensuel et le suivi budgétaire. Un modèle de plan de trésorerie montre comment brancher ce type de calcul sur des prévisions.

Pour aller plus loin

Ces techniques s’enchaînent avec d’autres tutoriels : le tableau croisé dynamique pour explorer les données sans formule, la recherche de valeurs pour habiller les comptes, le rapprochement bancaire et le nettoyage des données avant tout calcul. Un classeur bâti sur ces briques se met à jour d’un mois à l’autre en remplaçant simplement l’export du grand livre.