Trier, filtrer et sous-totaliser un grand livre

Un grand livre n’a d’intérêt que si on peut l’interroger : « montrez-moi les mouvements du compte banque », « classez les écritures par compte puis par date », « quel est le total de ce que je vois à l’écran ? ». Trois outils répondent à ces questions : le tri, le filtre et le sous-total. Ils sont simples, mais chacun a un piège qui a déjà coûté des heures à bien des comptables.

Ce que vous saurez faire à la fin de l’épisode

• trier un journal sur plusieurs niveaux sans désaligner les colonnes ;
• appliquer des filtres par texte, par nombre, par date et par couleur ;
• extraire des lignes avec le filtre avancé ;
• totaliser uniquement les lignes visibles avec SOUS.TOTAL ;
• savoir quand préférer un tableau croisé dynamique à la commande Sous-total.

Le cas : douze écritures de janvier

Le journal d’Atlas Négoce pour janvier compte douze lignes : achat, vente, règlements, loyer, sur sept comptes (3421 clients, 3455 TVA récupérable, 4411 fournisseurs, 4455 TVA facturée, 5141 banque, 6111 achats, 6131 loyers, 7111 ventes). Les écritures sont dans l’ordre chronologique de saisie. On veut deux vues : par compte, puis les mouvements de banque seulement.

Trier

Placez-vous dans une cellule de la plage (ou du tableau) et ouvrez Données › Trier. Dans la boîte :

1. cochez Mes données ont des en-têtes ;
2. choisissez le premier niveau : Compte, de A à Z ;
3. Ajouter un niveau : Date, du plus ancien au plus récent ;
4. validez.

Figure 1 : après le tri, tous les mouvements d’un compte sont regroupés et, à l’intérieur d’un compte, classés chronologiquement.

Le compte 3421 apparaît d’abord avec ses deux mouvements (vente de 6 000 le 8 janvier, règlement de 6 000 le 15), puis 3455, 4411, etc. Remarquez que le tri est textuel : les comptes sont des textes (épisode 2), donc classés caractère par caractère, ce qui convient aux numéros de compte de même longueur. Si certains comptes avaient plus de chiffres que d’autres (61 et 6111), l’ordre serait aussi lexicographique.

D’autres critères de tri existent : par couleur de cellule ou de police, par liste personnalisée (janvier, février… ou « Haut, Moyen, Bas »).

Les trois pièges du tri

• Trier une seule colonne. Si vous sélectionnez la colonne Montant et triez, Excel propose d’« étendre la sélection » : c’est presque toujours le bon choix. Si vous refusez, seule la colonne bouge, et les montants ne correspondent plus aux libellés. Résultat : un journal faux sans aucune erreur visible. Pour éviter ce risque, partez toujours d’une seule cellule du tableau.
• Les formules à références relatives vers d’autres lignes. Une colonne Solde cumulé qui référence la ligne du dessus (=F2+D3-E3) est détruite par un tri : elle doit être recalculée après coup. Un tri se fait sur des données, pas sur des cumuls.
• Les lignes de total dans la plage. Elles seraient triées avec les données. Laissez une ligne vide entre les données et les totaux, ou placez les totaux au-dessus (ou dans une autre feuille).

Réflexe : avant un tri important, ajoutez une colonne N° d’ordre (1, 2, 3…) avec la valeur d’origine : un tri sur cette colonne remet l’ordre de saisie à tout moment. Et gardez toujours une copie de la feuille avant d’expérimenter.

Filtrer

Ctrl + Maj + L active les boutons de filtre sur la ligne d’en-têtes (un tableau Excel les possède déjà). Un clic sur le bouton d’une colonne ouvre un menu :

• la liste des valeurs distinctes de la colonne, avec cases à cocher et zone de recherche : décochez Sélectionner tout, cochez 5141 ;
• les filtres textuels (contient, commence par, ne contient pas) : Règlement dans la colonne Libellé ;
• les filtres numériques (supérieur à, entre, 10 premiers) : débits supérieurs à 5 000 ;
• les filtres de dates : regroupés par année puis mois, avec des périodes prêtes à l’emploi (ce mois-ci, le trimestre dernier) ;
• le filtre par couleur.

On combine les colonnes : filtrer Compte = 5141 et Date = janvier donne les mouvements de banque du mois. Un numéro de ligne affiché en bleu signale qu’un filtre est actif : c’est un indice utile quand on rouvre un fichier et qu’on s’étonne de ne voir que trois lignes.

Figure 2 : filtre sur le compte 5141. Les lignes visibles sont les lignes 8, 11 et 13 du journal d’origine (numéros en bleu).

SOMME ou SOUS.TOTAL ?

Quand un filtre est actif, la barre d’état donne la somme des cellules visibles, mais pas une formule de la feuille. Une formule =SOMME(D2:D13) additionne toutes les lignes, y compris celles masquées. Pour un total qui suit le filtre, utilisez :

=SOUS.TOTAL(109;D2:D13)

Le premier argument est le code de la fonction (109 pour la somme, 101 pour la moyenne, 102 pour NB, 103 pour NBVAL, 104 pour MAX, 105 pour MIN). Les codes 1 à 11 incluent les lignes masquées à la main, les codes 101 à 111 les excluent ; dans les deux cas, les lignes masquées par un filtre sont toujours ignorées.

Sur notre journal, avec le filtre sur le compte 5141 :

Le solde du compte banque sur le mois est donc de 6 000 − 20 500 = −14 500,00 : la banque est à découvert de 14 500 MAD sur la base de ces seules écritures (en pratique, le solde d’ouverture compte aussi). Remarquez aussi que 44 500 au débit et au crédit : le journal est équilibré.

Le total du bas, dans la ligne de totaux d’un tableau Excel, utilise exactement cette fonction : c’est pourquoi, au dernier épisode, la ligne de totaux suivait le filtre.

Le filtre avancé

Quand les critères sont plus riches qu’un menu (« les débits supérieurs à 5 000 sur les comptes 5141 ou 3421 »), ou qu’on veut extraire le résultat sans toucher à la liste, on utilise le filtre avancé (Données › Avancé). On prépare une zone de critères en dehors des données : une ligne d’en-têtes identiques à ceux du journal, puis une ligne par condition ; les critères sur une même ligne sont liés par un ET, ceux de lignes différentes par un OU.

Cette zone signifie : (Compte = 5141) OU (Compte = 3421 ET Débit > 5000). Dans la boîte du filtre avancé, on désigne la plage de la liste, la plage de critères, et l’on choisit Copier vers un autre emplacement pour obtenir un extrait dans une zone libre. La case Extraction sans doublon supprime les lignes identiques : c’est un moyen rapide de dresser la liste des comptes utilisés ou des clients sans doublon.

À partir d’Excel 365, la fonction FILTRE fait la même chose avec une formule, qui se met à jour toute seule (épisode 48).

La commande Sous-total

Données › Sous-total insère des lignes de total à chaque changement de valeur d’une colonne. Mode d’emploi :

1. Triez d’abord la liste par la colonne de regroupement (ici Compte) ;
2. ouvrez la commande : À chaque changement de : Compte ; Utiliser la fonction : Somme ; Ajouter un sous-total à : Débit et Crédit ;
3. validez : Excel insère une ligne « Total 3421 », « Total 3455 »… et un total général ; des boutons 1 2 3 à gauche permettent de n’afficher que les totaux.

C’est pratique pour un contrôle rapide, mais la commande modifie la structure de la feuille (lignes insérées, plan activé) : elle ne convient pas à un classeur qu’on réutilise. Pour des totaux par compte propres et dynamiques, un tableau croisé dynamique (épisode 25) ou la fonction SOMME.SI.ENS (épisode 23) sont préférables.

À vous de jouer

Dans le classeur d’exercice, onglet Journal :

1. Triez le journal par Compte puis par Date. Comparez avec l’onglet Tri par compte.
2. Revenez à l’ordre chronologique (annulez ou triez par Date).
3. Activez les filtres et filtrez Compte = 5141. Notez les numéros de lignes visibles.
4. Observez les totaux : lequel change, lequel ne change pas ?
5. Ajoutez un filtre textuel Libellé contient « Règlement ». Combien de lignes restent ? Quel est le total visible ?

Correction. Sur le compte 5141, trois lignes restent visibles (lignes 8, 11 et 13). Le total SOUS.TOTAL passe à 6 000,00 au débit et 20 500,00 au crédit, tandis que le total SOMME reste à 44 500,00. Avec le filtre supplémentaire contient Règlement, il ne reste que deux lignes (le règlement client et le règlement fournisseur) ; le débit visible est toujours 6 000,00 et le crédit visible tombe à 12 000,00, puisque le loyer est exclu.

À retenir

• Trier : partez d’une cellule du tableau, cochez « mes données ont des en-têtes », ajoutez des niveaux ; ne triez jamais une colonne isolée.
• Filtrer masque sans supprimer ; les numéros de ligne bleus signalent un filtre actif.
• SOMME ignore les filtres, SOUS.TOTAL(109;plage) les respecte.
• Le filtre avancé extrait avec des critères ET/OU et peut éliminer les doublons.
• Pour des totaux par groupe réutilisables, passez par un tableau croisé dynamique.

Prochain épisode : les noms définis et la feuille de paramètres, pour que les taux et barèmes de vos formules se lisent et se modifient en un seul endroit.