Relevé de quantités dans Excel : quel poste mesuré dépasse le prévu
Un relevé de quantités, c'est une ligne par poste. Vous saisissez le poste, le lot, l'unité, la quantité prévue, la quantité mesurée et le prix unitaire. L'écart est le mesuré moins le prévu tant que la ligne est ouverte et que les deux quantités sont remplies. La fenêtre dit dessus quand cet écart est au-dessus de zéro, dessous quand il est en dessous de zéro, et egal quand les deux quantités se valent. Le montant est le mesuré fois le prix, et il reste vide quand le prix est vide. Le premier écran est une feuille nommée Metre. La ligne 1 est l'en-tête. La ligne 2 est un poste que vous pouvez vérifier à la main : prévu 100, mesuré 120, prix 45, statut ouvert. L'écart est 20. Le montant est 5400. La fenêtre est dessus. Il n'y a pas de pourcentage de chantier ni de minutes standard sur cet écran. Cette feuille n'est pas un journal d'avancement. Ce journal utilise une date et un pourcentage. Cette liste utilise les deux quantités saisies. Ce n'est pas une gamme en minutes, et ce n'est pas un budget d'événement qui compare un montant prévu à un montant dépensé. Le lot est un mot que vous saisissez pour filtrer. Vous saisissez les deux quantités. La question est quelle ligne ouverte a été mesurée au-dessus du prévu. Les lignes closes sortent de la fenêtre. Elles restent sur la feuille. Un écart négatif est dessous, pas un nombre à cacher. Un prix manquant vide le montant et laisse l'écart. Elyx peut écrire l'écart, le montant et la fenêtre. Vous saisissez toujours le prévu, le mesuré et le prix. L'essai dure 7 jours, sans carte.
Le probleme
Le relevé échoue quand la mesure vit sur un plan annoté. Une phrase dit que la dalle dépasse le prévu. Personne ne peut additionner les écarts, parce que le prévu et le mesuré ne sont pas sur la même ligne. Une phrase sans ces deux nombres n'est pas un relevé. Un deuxième échec est de mélanger cette feuille avec un pourcentage de chantier, un temps de gamme ou un budget d'événement. Une ligne de chantier répond à quel pourcentage vous avez saisi aujourd'hui. Une ligne de gamme répond à quelle opération dépasse les minutes standard. Une ligne de budget répond à quelle ligne de coût dépasse le montant prévu, sans quantité. Si vous collez ici des pourcentages, des minutes ou des lignes d'événement, la somme du matin ne veut plus dire mesuré moins prévu. Gardez cette feuille pour les postes, les quantités et le prix saisi. Un troisième échec est de supprimer un poste quand la mesure est acceptée. Passez le statut à clos et laissez la ligne. L'écart se vide. Supprimer la ligne retire la seule trace du 100 et du 120 déjà saisis. La feuille ne décide toujours pas de ce que le prévu aurait dû être.
Les benefices
Chaque poste est une ligne avec une quantité prévue, une quantité mesurée et un prix unitaire saisis.
L'écart est le mesuré moins le prévu tant que la ligne est ouverte.
Dessus veut dire que le mesuré est au-dessus du prévu. Dessous veut dire qu'il est en dessous. Egal veut dire que les deux quantités se valent.
Le montant est le mesuré fois le prix, et il reste vide quand le prix est vide.
Les lignes closes restent sur la feuille et sortent du compte des dessus.
Tutoriel pas a pas
Créer la liste et l'en-tête
Nommez la feuille Metre. En ligne 1, saisissez : Poste, Lot, Unite, Prevu, Mesure, Prix, Statut, Ecart, Montant, Fenetre. Poste est la ligne de travaux. Lot et Unite sont des mots saisis. Prevu et Mesure sont des quantités. Prix est le prix unitaire saisi, pas une formule. Ecart, Montant et Fenetre sont des formules. Statut est un mot d'une liste. Figez la ligne 1. Ne fusionnez pas les cellules.
Une ligne par poste. Une seconde mesure du même poste est une seconde ligne. Ne mettez pas deux mesures dans la même cellule.
Verrouiller les mots de statut
Sélectionnez Statut à partir de la ligne 2. Données, Validation des données, Liste. La source, sur une feuille nommée Listes, est ouvert et clos. Ouvert veut dire que l'écart est encore à lire. Clos veut dire que vous en avez fini avec ce poste. Seul ouvert reste dans la fenêtre. Il faut choisir un mot. Un synonyme comme ok ou fait ne correspond pas aux formules.
Prevu, Mesure et Prix sont des nombres. Ne saisissez pas le mot dessus dans ces colonnes.
Saisir cinq postes que vous pouvez vérifier
En ligne 2, saisissez le poste Dalle, l'unité m2, prévu 100, mesuré 120, prix 45, statut ouvert. Vous prévoyez un écart de 20, un montant de 5400 et une fenêtre dessus. En ligne 3, Mur, prévu 80, mesuré 80, prix 30, statut ouvert. L'écart doit être 0, le montant 2400, la fenêtre egal. En ligne 4, Tuyau, prévu 50, mesuré 40, prix 12, statut ouvert. L'écart doit être -10, le montant 480, la fenêtre dessous. En ligne 5, Peinture, prévu 20, mesuré 25, prix vide, statut ouvert. L'écart doit être 5, le montant vide, la fenêtre dessus. En ligne 6, Porte, prévu 10, mesuré 12, prix 200, statut clos. L'écart, le montant et la fenêtre doivent se vider. Utilisez ces nombres avant de faire confiance aux formules.
Si vous ne connaissez pas encore le prix, laissez Prix vide. Le montant reste vide. L'écart se calcule quand même à partir des deux quantités.
Écrire l'écart
L'écart est vide quand le statut est clos, ou quand Prevu ou Mesure est vide. Sinon c'est Mesure moins Prevu. Avec Prevu en D2, Mesure en E2 et Statut en G2, utilisez =SI(OU(G2="clos";D2="";E2="");"";E2-D2). Mettez le format Nombre. Un nombre positif a été mesuré au-dessus du prévu. Zéro veut dire que les quantités se valent. Un nombre négatif a été mesuré en dessous du prévu. Le prix n'est pas dans cette formule.
=SI(OU(G2="clos";D2="";E2="");"";E2-D2)120 moins 100 font 20. 40 moins 50 font -10. Si vous voyez un autre nombre, les cellules ne sont pas les quantités saisies.
Écrire le montant et la fenêtre
Le montant est vide quand le statut est clos, ou quand Mesure ou Prix est vide. Sinon c'est Mesure fois Prix. Utilisez =SI(OU(G2="clos";E2="";F2="");"";E2*F2). La fenêtre est vide quand le statut est clos ou qu'une quantité est vide. Sinon elle dit dessus quand Mesure moins Prevu est au-dessus de zéro, dessous quand cette différence est en dessous de zéro, et egal quand elle est nulle. Utilisez =SI(OU(G2="clos";D2="";E2="");"";SI(E2-D2>0;"dessus";SI(E2-D2<0;"dessous";"egal"))). 120 fois 45 font 5400. Un prix vide sur Peinture laisse le montant vide et marque quand même l'écart de 5 comme dessus.
=SI(OU(G2="clos";D2="";E2="");"";SI(E2-D2>0;"dessus";SI(E2-D2<0;"dessous";"egal")))N'enveloppez pas l'écart dans ABS. Cacher le signe moins cache le tuyau mesuré 40 contre un prévu de 50.
Compter les dessus et additionner les écarts
Sur une feuille nommée Matin, les lignes dessus sont =NB.SI.ENS(Metre!J:J;"dessus"). Avec les cinq lignes ci-dessus, ce compte est 2 : l'écart de 20 et l'écart de 5. La somme des écarts dessus est =SOMME.SI.ENS(Metre!H:H;Metre!J:J;"dessus"), soit 25. Le montant dessus est =SOMME.SI.ENS(Metre!I:I;Metre!J:J;"dessus"), soit 5400, parce que Peinture n'a pas de prix. Le compte des dessous est =NB.SI.ENS(Metre!J:J;"dessous"), soit 1, l'écart de -10. La porte close est dehors parce que sa fenêtre est vide. Une fenêtre vide n'est pas un dessus. Écrivez la règle à côté du compte.
=SOMME.SI.ENS(Metre!H:H;Metre!J:J;"dessus")N'ajoutez pas le -10 dans la somme des dessus. Dessous est un autre mot, donc SOMME.SI.ENS le laisse de côté.
Lire un prix manquant comme un montant vide
Si l'écart est 5 et que le prix est vide, la fenêtre peut quand même dire dessus. Le montant reste vide. C'est une ligne à chiffrer, pas une ligne à supprimer. Ne saisissez pas un prix que vous n'avez pas. Un écart sur une ligne close ne devrait pas arriver, parce que clos vide la cellule. Si vous voyez un nombre sur une ligne close, la formule a été écrasée. La porte aurait fait un écart de 2 et un montant de 2400, mais clos garde les deux hors de la liste du matin.
Filtrez Fenetre sur dessus pour la liste du matin. Filtrez Lot quand vous ne voulez qu'un mot. Filtrez Statut sur clos quand vous avez besoin de l'historique.
Vérifier les cinq lignes avant de partager la somme
Prévu 100 et mesuré 120, prix 45, statut ouvert : écart 20, montant 5400, fenêtre dessus. Prévu 80 et mesuré 80, prix 30 : écart 0, montant 2400, fenêtre egal. Prévu 50 et mesuré 40, prix 12 : écart -10, montant 480, fenêtre dessous. Prévu 20 et mesuré 25, prix vide : écart 5, montant vide, fenêtre dessus. Prévu 10 et mesuré 12, prix 200, statut clos : écart vide, montant vide, fenêtre vide. Le compte des dessus est 2, la somme des écarts dessus est 25, et le montant dessus est 5400. Si un contrôle échoue, corrigez les quantités, le prix ou le mot de statut avant d'envoyer la somme. La somme n'est que cette feuille. Ce n'est pas un pourcentage de chantier, et ce n'est pas un standard en minutes.
Écrivez les cinq contrôles sur Matin si plusieurs personnes mettent le relevé à jour.
Fonctionnalites du template
Écart tant que le poste est ouvert
Mesuré moins prévu. Vide quand le statut est clos ou qu'une quantité est vide.
=SI(OU(G2="clos";D2="";E2="");"";E2-D2)Montant à partir du prix saisi
Mesuré fois le prix. Vide quand le prix est vide.
=SI(OU(G2="clos";E2="";F2="");"";E2*F2)Fenêtre à partir de l'écart
Dessus quand l'écart est au-dessus de zéro, dessous quand il est en dessous, egal quand il est nul.
=SI(OU(G2="clos";D2="";E2="");"";SI(E2-D2>0;"dessus";SI(E2-D2<0;"dessous";"egal")))Somme des écarts dessus
SOMME.SI.ENS additionne seulement les écarts dont la fenêtre est dessus. La somme d'exemple est 25.
=SOMME.SI.ENS(Metre!H:H;Metre!J:J;"dessus")L'historique reste sur la feuille
Les lignes closes restent. Elles sortent du compte parce que la fenêtre est vide.
Exemples concrets
Un écart de 20
La dalle était prévue à 100 mètres carrés et mesurée à 120. Le prix saisi est 45.
Prévu : 100. Mesuré : 120. Prix : 45. Statut : ouvert.
Resultat : L'écart est 20. Le montant est 5400. La fenêtre est dessus. Le compte des dessus inclut la ligne.
Une mesure sous le prévu
Le tuyau était prévu à 50 et mesuré à 40. Vous ne voulez pas cette ligne dans la somme des dessus.
Prévu : 50. Mesuré : 40. Prix : 12. Statut : ouvert.
Resultat : L'écart est -10. Le montant est 480. La fenêtre est dessous. La somme des écarts dessus n'inclut pas -10.
Un écart sans prix
La peinture était prévue à 20 et mesurée à 25. La cellule de prix est vide.
Prévu : 20. Mesuré : 25. Prix : vide. Statut : ouvert.
Resultat : L'écart est 5. La fenêtre est dessus. Le montant est vide. Le compte des dessus est 2 avec la dalle, et le montant dessus reste 5400.
Astuces de pro
Un prix vide n'est pas zéro
Laissez Prix vide quand vous ne l'avez pas. Saisir 0 fait un montant de 0, qui est un nombre, pas un prix manquant. 120 fois 0 font 0.
L'unité est une étiquette
m2 permet de filtrer. La formule de l'écart ne lit jamais l'unité. N'ajoutez pas un écart en mètres carrés à un écart en mètres linéaires pour en faire une seule quantité. Filtrez Unite d'abord.
Une quantité en texte ne se soustrait pas
Si Mesure a été collée comme texte, E2-D2 ne fera pas 20. Ressaisissez 120 et 100 comme nombres. Puis relisez la fenêtre.
Distinct d'un pourcentage de chantier et d'une ligne de budget
L'avancement quotidien utilise une date et un pourcentage. Le temps de gamme utilise des minutes. Un budget d'événement utilise un montant prévu et un montant dépensé. Ne collez pas ces lignes ici en attendant que cette somme veuille dire la même chose.