Fonction SI / Tableau de suivi de CA avec une mise à jour par semaine

Question

Je travail sur un fichier pour mon travail mais je bloque sur une formule. J'ai une base de données de CA budgétaire et je souhaite faire un suivi par rapport au CA réel dans mon troisième onglet. Via des formules, j'ai préconisé le CA à réaliser par semaine en fonction des jours dans la semaine. Mon souhaite si cela est possible est de compléter de manière hebdomadaire mon chiffre d'affaires réel et modifier mes objectifs en fonction de ce CA réel pour les semaines suivantes sauf que le montant cumulé des CA réels ne correspond pas au CA budgetaire... Ex : Onglet 3 / Ligne 3 Colonne J (Première semaine) : CA mensuel / par nb de jours travaillés dans le mois * 6 jours (dans la semaine 1) Colonne K : CA réel qui sera indiqué en fin de la semaine Colonne P (Deuxième semaine) : Formule indiqué : =((D3-K3)/I$2)*U$2 / soit CA Budget - CA réel de la semaine précédente / nb de jours travaillés dans le mois * nombre de jours en semaine 2 Et c'est à ce moment la que mes compétences s'arrêtent ... Je n'arrive pas à trouver la formule pour prendre en compte la saisie ou non du CA de la semaine précédente et les jours déjà passés de la semaine 1. Si une âme charitable peut m'aider... En espérant que mes explications soient claires.... D'avance merci à vous Gwénael...

Cylfo · Answer

Pour " pour prendre en compte la saisie ou non du CA de la semaine précédente ", vous ne pouvez pas conditionner votre formule à l'aide la formule ESTVIDE qui ne retourne vrai que si la cellule est réellement, si vous indiquez un CA égal à 0, la cellule ne sera plus vide ? Ce qui donnerait en P3 : =SI(ESTVIDE(K3);0;((D3-K3)/I$2)*U$2) Nota : pour que cette formule soit copiable sans avoir à la modifier pour ajouter la soustraction des colonnes précédentes, vous pouvez essayer (j'ai un doute avec la version 2010) : =SI(ESTVIDE(K3);0;(($D3- SOMME.SI($J$1:K$1;"Semaine*CA Réel HT";$J3:K3) )/I$2)*AA$2) Par contre, je n'ai pas compris " et les jours déjà passés de la semaine 1" : je ne vois qu'une valeur fixe (6) pas de valeur qui prenne en compte une progression Cdlt, Cylfo...

78chris · Answer

Si tu peux charger l'add on PowerQuery (intégré à Excel à partir de 2016 mais en add sur 2010 et 2013) tu peux simplifier les choses Un tableau du réel et un tableau du Budget et la synthèse est automatique si les règles sont définies clairement Là je situe mal le réel dans ton classeur.....

Cylfo · Answer

Re, Dans la phrase " Et qu'a la saisie des colonnes K;Q;W;AC;AI, les colonnes J ; P;V;AB;AH (CA prévisionnel / semaines) se mettent à jour : ", J est bien en trop ? Si oui, ok, sinon je n'ai pas ou rien compris. Pour le calcul en colonne P si un CA réalisé est saisi en colonne K, la formule =($D3-SOMME.SI($K$1:K$1;"Semaine*CA Réel HT";$K3:K3))/(25-SOMME.SI($O$1:O$1;"Jours";$O$2:O$2))*O$2 donne le résultat attendu, par contre si K3 n'est pas saisi, quel est le résultat attendu ? Cela expliquera peut-être comment, dans votre exemple, le montant de 8287,56 en colonne V est calculé ou reconduit. Cdlt, Cylfo...

Cylfo · Answer

J'ai modifié les formules de calcul du CA budget HT à partir de la semaine 2 et sur les lignes 3 à 14. Je vous laisse faire des test et me dire si cela vous convient.

Pour que cela fonctionne correctement, il ne doit pas y avoir sur une même ligne une colonne "CA réel HT" vide entre des semaines ayant un "CA réel HT" renseigné. Si pour une semaine il n'y avait pas de "CA réel Ht" réalisé, il faudra saisir 0.

15suivi-ratios-test-3.xlsx (51.92 Ko)

Cdlt,

Cylfo

78chris · Answer

Là tu reportes à la main semble-t-il des montants hebdomadaires En partant du début : on aurait un budget pour les 12 mois un tableau saisie des ventes avec les colonnes Date, Parent Site, Clusters , Sales où on saisit les lignes de ventes au fil des jours du 1er janvier au 31/12 Ces 2 tableaux chargés dans PowerQuery permettraient de calculer le nombre de jours de semaine (lundi à samedi si j'ai bien compris. Il manque sans doute la gestion des fériés) par semaine et par mois le CA réel hebdomadaire et cumulé des semaines écoulées le budget recalculé de la ou les semaines à venir (pas sûr que calculer les 52 semaines en début d'année ait un intérêt... 3 ou 4 semaines futures suffirait sans doute...) le COGS Prévisionel recalculé de la ou les semaines à venir.....