XL 2016 Consommation journalière avec graphique

[amgue]

Bonjour à tous,

J’aimerais calculer la somme de dépenses par date.

Feuille du mois en cours : "décembre"

Les jours en chiffres sont inscrits dans la ligne 50 en partant de la cellule K50 jusqu’à AO50.

Le mois en cours en $F$2 (=décembre)

La sommes des débits dans la colonne : Tableau2[Débit]

Test du jour du mois : TEXTE(Tableau2[Date de l''opération];"j")=Décembre!K50

Test du nom du mois : TEXTE(Tableau2[Date de l''opération];"mmmm")=Décembre!$F$2

Formule en K51 :

=SOMMEPROD(Tableau2[Débit]*(TEXTE(Tableau2[Date de l''opération];"j")=Décembre!K50)*(TEXTE(Tableau2[Date de l''opération];"mmmm")=Décembre!$F$2))

Mais j’obtiens toujours la même valeur 0 pour toutes les journées du mois !

Une solution ?

Merci d’avance.

 

[sylvanu]

Bonsoir Amgue,

Une solution ?

Un fichier test ?

 

[amgue]

Bonsoir Sylvain,

En PJ

 

[sylvanu]

Re,

Les jours en chiffres sont inscrits dans la ligne 50 en partant de la cellule K50 jusqu’à AO50.

Pa&s bien compris car je ne trouve rien dans cette plage.

Donc un en essai en PJ un peu au pif avec :

=SOMME.SI.ENS(TbTransactions[[#Tout];[Débit]];TbTransactions[[#Tout];[Date de l''opération]];CNUM(B19&" "&$F$2&" 2023"))

La colonne L de Transaction est inutile puisqu'on reconstitue la date.

 

[amgue]

Bonjour sylavnu, le forum,

Merci pour la solution.

Pa&s bien compris car je ne trouve rien dans cette plage.

La ligne 50 était dans mon classeur. J'ai changé vers la ligne 19 dans l'exemple ci-joint.

TbTransactions[[#Tout];[Débit]]

Si je supprime l'en-tête de colonne [#All] dans TbTransactions, le résultat sera 0. Pourquoi cette différence ?

 

[sylvanu]

Bonsoir,
Non ça donne la même chose avec :

=SOMME.SI.ENS(TbTransactions[Débit];TbTransactions[Date de l''opération];CNUM(B19&" "&$F$2&" 2023"))

Je suppose que vous avez changé la formule en B22 puis tirez vers la droite.
Ca ne marche pas. Si vous faites cela, XL incrémente les colonnes, par ex en C22 vous allez avoir :

B22:
=SOMME.SI.ENS(TbTransactions[Débit];TbTransactions[Date de l''opération];CNUM(B19&" "&$F$2&" 2023"))
C22:
=SOMME.SI.ENS(TbTransactions[Crédit];TbTransactions[Description];CNUM(C19&" "&$F$2&" 2023"))

Il faut copier B22 puis sélectionner B22:AF22 et faire coller spécial formules.

 

[amgue]

Bonsoir sylvanu,

et merci pour la solution

 

[amgue]

Bonjour,

J'ai un petit soucis la formule ici :

CNUM(C19&" "&$F$2&" 2023")

La formule fonctionne bien pour les mois qui commencent par le 1er jour et se terminent par un jour entre 28 et 31.

Mais je ne sais pas comment l'adapter pour les mois qui commencent le 26 du mois précédent et se terminent le 26 du mois en cours.

Par exemple, le mois actuel est nommé "Janvier", mais il commence le 26 décembre 2023 et se termine le 26 janvier 2024.

Merci d'avance pour votre aide.

 

[sylvanu]

Bonjour Amgue,
Rien compris. Surement les dernières effluves du Week end.
Alors au pif :

Pour le jour :
=CNUM(AE19&" "&$F$2&" "&ANNEE(AUJOURDHUI()))
Pour le mois suivant :
=MOIS.DECALER(AE19&" "&$F$2&" "&ANNEE(AUJOURDHUI());1)
Pour le mois précédent :
=MOIS.DECALER(AE19&" "&$F$2&" "&ANNEE(AUJOURDHUI());-1)

Ensuite dans vos formules en B20, je ne comprends pas ce que signifie : Décembre!K$50 puisqu'il n'y a rien en K50.
Si vous voulez tracer Débit et crédit, peut être serait il plus simple de mettre directement la date en ligne 19 :

B19 :
=CNUM("1 " &$F$2&" " & ANNEE(AUJOURDHUI()))
C19 :
=B19+1
Débit :
=SOMME.SI.ENS(TbTransactions[Débit];TbTransactions[Date de l''opération];Décembre!B19)
Crédit :
=SOMME.SI.ENS(TbTransactions[Crédit];TbTransactions[Date de l''opération];Décembre!B19)

 

[amgue]

Bonsoir Sylvain, le forum,

Pour la ligne 19, je dois expliquer que les jours du 26 du mois en cours au 26 du mois suivant doivent être inclus, car le mois budgétaire commence le 26 de chaque mois.

Prenons l'exemple de janvier 2024, où le mois s'étend du 26/12/2023 au 26/01/2024.

1. Le mois change.
2. L'année change.
3. Si le mois en cours se termine par le 28, les jours 29, 30 et 31 n'auront pas de données.
4. Si le mois en cours se termine par le 29, les jours 30 et 31 n'auront pas de données, et ainsi de suite.

J'ai utilisé une formule pour les jours du 26 au 31, ou $F$2 équivaut à "janvier" et An est à "2024" :

=SOMME.SI.ENS(TbTransactions[Débit];TbTransactions[Date de l''opération];CNUM(B19&"/"&MOIS(DATEVAL($F$2&"1")-1)&"/"&An-1))

Et une autre formule pour les jours du 1 au 25, toujours en prenant en considération que $F$2 est égal à "janvier" et An est à "2024" :

=SOMME.SI.ENS(TbTransactions[Débit];TbTransactions[Date de l''opération];CNUM(H19&"/"&MOIS(DATEVAL($F$2&"1"))&"/"&An))

Mais, ces changements ont affecté le graphique et ont créé des perturbations.

 

[sylvanu]

Bonjour,

Pour la ligne 19, je dois expliquer que les jours du 26 du mois en cours au 26 du mois suivant doivent être inclus, car le mois budgétaire commence le 26 de chaque mois.

Ca, c'était difficile à imaginer.
D'autre part, un mois budgétaire va du 26 au 25, et non du 26 au 26 car sinon le 26 serait compté deux fois, sur le mois précédent et sur le mois suivant. ( sinon modifier les formules en conséquence )
En PJ pas mal de modifs :
Début du calendrier en B19 :

=MOIS.DECALER(CNUM("26 "&F2&" "&An);-1)

Puis de C19 à AA19 : = B19+1
A partir de AB19 on peut avoir plusieurs configurations suivant les mois pour ne pas dépasser le 25 :

=SIERREUR(SI(JOUR(AA19+1)>25;"";AA19+1);"")

Puis pour débit et crédit :

=SI(B19<>"";SOMME.SI.ENS(TbTransactions[Débit];TbTransactions[Date de l''opération];B19);"")
=SI(B19<>"";SOMME.SI.ENS(TbTransactions[Crédit];TbTransactions[Date de l''opération];B19);"")

Ainsi toute transaction après le 25 est ignorée.

 

[amgue]

Bonsoir sylvanu,

C'est excellent, merci.

Je vais continu les tests avec les réglages du graphique.

 

[amgue]

Bonsoir sylvanu, le forum,

J'ai un petit soucis :

Dans la colonne G de la feuille 'Transactions', j'utilise une formule pour une liste déroulante de validation de données autofiltrante :

=SI(G11<>"";DECALER(SousCategories;EQUIV(G11&"*";SousCategories;0)-1;;NB.SI(SousCategories;G11&"*");1);SousCategories)

Cependant, lorsque la plage 'SousCategorie', dans laquelle la recherche est effectuée, contient plus d'une occurrence du mot saisi dans la liste, la formule n'extrait que la première occurrence et ignore les autres.

Par exemple, si je tape "rep", la formule devrait extraire les occurrences suivantes :

• Repas à l’école (Situé en A2 de la feuille "Data validation")
• Repas à l’extérieur (Situé en A27 de la feuille "Data validation")

Mais actuellement, la formule n'extrait que l’occurrence en A2 et ignore celle en A27.

Comment puis-je corriger cette formule ?
Est-il possible d'élargir la recherche en utilisant la syntaxe "*"&G11&"*" au lieu de simplement G11&"*" ?

Merci pour ton aide.

 

[sylvanu]

Bonjour,
Essayez avec :

=DECALER(SousCategories;EQUIV(G6&"*";SousCategories;0)-1;;NB.SI(SousCategories;G6&"*"))

Cela donne ça pour Rep :

Est-il possible d'élargir la recherche en utilisant la syntaxe "*"&G11&"*" au lieu de simplement G11&"*" ?

J'ai essayé sans succès. Mais peut être existe il une solution, attendez d'autres réponses peut être positives.

 

[amgue]

Bonsoir sylvanu,

=DECALER(SousCategories;EQUIV(G6&"*";SousCategories;0)-1;;NB.SI(SousCategories;G6&"*"))

Cela n'a pas marché pour moi et je ne sais pas pour quelle raison !