Formule de calcul et récupération de données d'un autre classeur
Bonjour,
J'aurai besoin pour trouver LA formule de calcul qui pourrait résoudre mon problème.
Je vous mets les 2 classeurs Excel sur lesquels j'essaie de travailler en pièce jointe pour que vous puissiez mieux me comprendre.
J'ai créé le classeur "Compte analytique test" avec quelques formules qui me permettent d'effectuer des rapprochements bancaires, avec un onglet par mois. Dans ces tableaux j'ai une colonne "Répartition" (colonne D) que je voudrais bien exploiter dans un autre classeur "Dépenses Recettes test", avec un onglet "recette" et un onglet "dépenses".
J'ai rempli à titre d'exemple le classeur "compte analytique test" pour janvier, février et mars. Je voudrais que la répartition se fasse automatiquement dans le classeur "Dépenses Recettes test" en fonction de la date du relevé de banque (colonne H du classeur "compte analytique test"), sachant qu'il peut y avoir plusieurs dates différentes pour un même onglet mois.
Je vous mets dans le classeur "Dépenses Recettes test" les résultats qu'il faudrait obtenir grâce à des formules de calcul ... dont je n'arrive pas à me dépatouiller. Et le top du top serait que la colonne A se renseigne aussi en automatique en fonction s'il y a eu des ajouts, suppression ou modification de code de répartition dans le classeur "compte analytique test".
J'espère avoir été claire dans mon explication
Merci par avance de votre aide
Elodie
Bonjour,
Voici ma solution à ton problème:
/!\ Attention /!\
- fonctionne que si il y a un écart inférieur ou égal à un mois entre date paiement et date relevé bancaire.
(exemple: ne prend pas en compte un paiement en février et un relevé en avril)
- formule différente en janvier car pas de données décembre 2020.
Cordialement
Merci pour ta réponse mais j'avoue que je n'arrive pas à interprêter ta formule de calcul
Est-ce qu'il serait possible que tu me l'expliques avec des mots ? Au moins la 1ère partie car après c'est la même chose qui se répète.
D'après ce que je comprends avec ta solution, il n'est pas possible d'avoir plus d'un mois d'écart donc ca va me poser problème lorsqu'il va y avoir un décalage (par exemple si je fais un chèque en février et que la personne ne l'encaisse que 3 ou 4 mois plus tard, il apparaitra seulement sur mon relevé de banque à ce moment là), et il y a souvent des décalages de plus d'un mois...
Je m'arrache encore les cheveux à essayer de trouver la solution
Bonjour,
J'ai modifié la formule pour chercher les relevés bancaires dans toutes les feuilles du tableau "compte-analytique" (et pas juste un mois d'écart, comme tu le souhaites).
=SOMME(SOMMEPROD(('[compte-analytique-test.xlsx]Janvier'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Janvier'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Janvier'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Janvier'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Février'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Février'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Février'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Février'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Mars'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Mars'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Mars'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Mars'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Avril'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Avril'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Avril'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Avril'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Mai'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Mai'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Mai'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Mai'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Juin'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Juin'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Juin'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Juin'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Juillet'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Mars'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Juillet'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Juillet'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Août'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Août'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Août'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Août'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Septembre'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Septembre'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Septembre'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Septembre'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Octobre'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Octobre'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Octobre'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Octobre'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Novembre'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Novembre'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Novembre'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Novembre'!$G$6:$G$13));SOMMEPROD(('[compte-analytique-test.xlsx]Décembre'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Décembre'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Décembre'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Décembre'!$G$6:$G$13)))On retrouve dans la formule générale, 12 SOMMEPROD pour les 12 feuilles du tableau "compte-analytique".
Il s'agit d'une SOMME de SOMMEPROD composés dans cette forme (exemple de recherche dans la feuille 'Janvier').
SOMMEPROD(('[compte-analytique-test.xlsx]Janvier'!$D$6:$D$13=$A4)*('[compte-analytique-test.xlsx]Janvier'!$H$6:$H$13>=B$3)*('[compte-analytique-test.xlsx]Janvier'!$H$6:$H$13<C$3)*('[compte-analytique-test.xlsx]Janvier'!$G$6:$G$13))
Cherche les lignes avec la répartition
Cherche les lignes avec une date de relevé bancaire >= au 01/01/21
Cherche les lignes avec une date de relevé bancaire < au 01/02/21
(Ainsi, on a en B4 toutes les lignes de COT du mois de janvier, en B5 toutes les lignes de FB du mois de janvier, ...)
Colonne des valeurs a sommer
(en l'occurrence Dépenses, colonne G de la feuille 'Janvier')
Les paramètres à modifier dans une SOMMEPROD sont le mois pour chercher dans toutes les feuilles et la colonne de recherche dans la dernière parenthèse selon si tu es dans la feuille 'Dépenses' colonne G ou 'Recettes' colonne F.
Voila, j'espère avoir était suffisamment claire
N'hésite pas à me solliciter si besoin
Super merci
Je vais étudier en détail tes explications et si je ne comprends pas quelque chose je reviens vers toi ;-)
C'est vraiment génial ! la formule correspond exactement à ce que je cherchais.
Encore merci
Par contre j'ai une autre question est-ce qu'il y aurait moyen que cette formule se mette automatiquement à jour lorsqu'on veut reproduire ce tableau sous un autre nom ou un autre emplacement.
En fait je l'utilise pour plusieurs de mes clients et à chaque fois je suis obligée de retaper la formule pour qu'il aille chercher les informations au bon endroit, et j'avoue que la formule est un longue à retaper à chaque fois. Il faudrait une sorte de modèle qui va chercher automatiquement les informations dans le bon fichier, même si ce dernier vient à être déplacé par exemple.
Je ne sais pas si j'ai été assez claire
Merci de votre aide
Elodie