Faut-il un onglet par chantier ?

Non, c'est le piège le plus courant. Un seul onglet de dépenses pour tous les chantiers, avec une colonne « chantier », se filtre, se totalise et s'interroge bien mieux qu'une collection d'onglets.

Quelle formule pour totaliser les dépenses d'un chantier par poste ?

SOMME.SI.ENS, avec la colonne des montants en premier argument, puis la colonne chantier et son critère, puis la colonne poste et son critère.

Tableau de suivi de chantier Excel : les colonnes utiles

Un bon tableau de suivi de chantier sous Excel n'est pas celui qui a le plus de couleurs : c'est celui qui répond à une question en quelques secondes, que vous la posiez avec un filtre, une formule ou un assistant. Pour cela, trois choses comptent : une ligne par fait (une dépense, un pointage, une facture), une référence de chantier sur chaque ligne, et des colonnes qui ne changent jamais de sens.

La structure : quatre onglets, pas un par chantier

Onglet	Une ligne =	Colonnes indispensables
Chantiers	un chantier	N° de chantier, client, adresse, date de début, date de fin prévue, montant du devis HT, statut (en cours, terminé, réceptionné)
Budget	un poste d'un chantier	N° de chantier, poste, montant prévu HT, heures prévues
Dépenses	une facture, un bon ou une note	Date, n° de chantier, poste, fournisseur ou sous-traitant, libellé, montant HT, pièce (n° de facture)
Heures	un pointage	Date, n° de chantier, poste ou lot, personne ou équipe, nombre d'heures

L'article d'habitatpresto sur l'accostage financier propose un tableau par chantier avec un en-tête (numéro de chantier, client, adresse, dates de début et de fin) et, pour chaque poste, le budget prévu, l'engagé, le restant, l'écart et un commentaire. C'est une bonne vue de synthèse. Mais pour pouvoir interroger tous les chantiers à la fois, mieux vaut garder les données brutes dans les onglets ci-dessus et calculer la synthèse à partir d'elles.

Les règles de saisie qui font la différence

Le numéro de chantier est écrit toujours pareil. « 24-031 » et « 24031 » sont deux chantiers pour Excel comme pour une IA. Une liste déroulante, alimentée par l'onglet Chantiers, évite le problème.
Les postes sont une liste fermée. Par exemple : main-d'œuvre, matériaux, sous-traitance, location, déplacements, divers. Un poste « divers » qui grossit est un signal.
Tous les montants en hors taxes, et le nom de la colonne le dit : « Montant_HT ».
Les dates sont des dates, pas du texte. Un tri par date qui met « 03/02 » après « 28/01 » trahit des dates au format texte.
Une colonne « pièce » avec le numéro de facture : c'est elle qui permet de vérifier une ligne et de repérer un doublon.
Mettez chaque onglet sous forme de tableau, avec un nom court (Depenses, Heures) : les formules ci-dessous s'y réfèrent par le nom des colonnes. D'après l'aide de Microsoft, ces « références structurées » s'adaptent automatiquement quand des lignes sont ajoutées. La méthode est détaillée sur NL-Q Excel.

Les formules de la synthèse

Deux fonctions d'Excel font l'essentiel du travail. D'après l'aide de Microsoft :

SOMME.SI.ENS additionne une plage quand toutes les conditions sont remplies. La plage à additionner vient en premier : SOMME.SI.ENS(somme_plage; plage_critères1; critère1; …).
NB.SI.ENS compte les lignes qui remplissent plusieurs conditions : NB.SI.ENS(plage_critères1; critère1; …). Jusqu'à 127 paires plage/critère sont autorisées pour les deux fonctions.

Exemple illustratif, avec un onglet Dépenses mis sous forme de tableau nommé Depenses :

Engagé du chantier 24-031 en matériaux : =SOMME.SI.ENS(Depenses[Montant_HT];Depenses[Chantier];"24-031";Depenses[Poste];"Matériaux")
Nombre de factures du « Fournisseur A » sur le chantier 24-031 : =NB.SI.ENS(Depenses[Chantier];"24-031";Depenses[Fournisseur];"Fournisseur A")

L'écart se calcule ensuite poste par poste : engagé moins prévu, ou, mieux, coût à terminaison moins prévu (voir coût à terminaison).

Les questions que ce tableau permet de poser

Une fois les quatre onglets tenus, vous pouvez demander, avec un filtre ou en français à un assistant :

« Quels chantiers en cours ont déjà engagé plus de 80 % de leur budget matériaux ? »
« Quelles dépenses de plus de 1 000 € HT n'ont pas de numéro de pièce ? »
« Quel poste représente la plus grosse part des dépenses sur les chantiers terminés cette année ? »
« Quels chantiers n'ont reçu aucun pointage cette semaine ? »

D'autres exemples sont dans les questions à poser à ses données de chantier.

Les contrôles à faire chaque mois

Contrôle	Comment
Dépenses sans chantier	Filtrer la colonne chantier sur les cellules vides
Doublons de facture	Trier par fournisseur puis par n° de pièce
Chantiers terminés encore « en cours »	Filtrer le statut et la date de fin prévue dépassée
Total des dépenses = total de la comptabilité	Comparer le total du mois au total des achats comptabilisés

Le dernier contrôle est le plus utile : si votre tableau et votre comptabilité ne donnent pas le même total d'achats sur un mois, une partie des dépenses n'est pas rattachée. Pour l'analyse côté comptabilité, voyez NL-Q Comptabilité.

Les limites d'un tableur

Un tableur reste fragile quand plusieurs personnes saisissent en même temps, quand les factures arrivent par mail et doivent être recopiées, ou quand les heures sont pointées sur papier. Si votre logiciel de devis ou de gestion tient déjà ces données, partez plutôt d'un export : voir exporter les données de son logiciel de devis.