Somme en fonction date+catégorie

[apt]

Bonjour à tous,

J'aimerais calculer une somme en fonction d'une date et une catégorie.

Mais je n'arrive toujours pas .... 🙄

Merci d'avance.

 

[apt]

Re : Somme en fonction date+catégorie

Re,

Une autre question :

Pourquoi mettre le "Fin" en dernier pour que cela fonctionne ?

Merci.

 

[Gael]

Re : Somme en fonction date+catégorie

Bonjour Apt, bonjour à tous,

Pour les difficultés que tu rencontres avec la définition de Plage_cat, lorsque tu utilises un nom (avec insertion - nom - définir) qui fait référence à des cellules variables (sans "$" en colonne ou en ligne) il faut que lors de la création de la formule, la cellule sélectionnée dans la feuille soit bien celle qui correspond à la formule.

Ce n'est pas très clair, mais si tu crée un nom avec une formule qui fait référence à $N2 par exemple, $N2 représente l'adresse relative par rapport à la cellule sélectionnée. Si la sélection est en cellule N5 par exemple, lorsque tu sélectionnera N6, la formule fera référence à N3 et ainsi de suite. mais dans ce cas si tu sélectionnes N3, la référence relative sera inférieure à 1 donc la plage fera référence à =Feuil1!$N65536.

La définition est corrigée dans le fichier joint de même que la formule somme où il suffit de faire la somme de la plage Categ décalée d'une colonne soit:

=SOMME(DECALER(Categ;;1))

La plage "Plage _Cat" étant définie entre la catégorie choisie et la suivante, il est nécessaire qu'il y ait toujours une catégorie suivante. Le mot "FIN" permet de donner une limite inférieure lorsque tu es sur la dernière catégorie de la liste.

Tu peux remplacer le mot FIN par ce que tu veux, à condition que ce soit le même terme à la fin de la colonne A, comme dernière catégorie et qui ne soit pas une catégorie déjà existante.

Par ailleurs, la plage Categ est définie à partir du nombre de valeurs dans la colonne N, il ne faut donc pas utiliser d'autres cellules de cette colonne.

@+

Gael

 

[apt]

Re : Somme en fonction date+catégorie

Bonjour Gael,

Je devrais relire tes explications, peut être que j'aurais d'autres questions à te poser si c'est possible 🙂

- Pour un premier test, il y'a toujours des sommes fixes dans la colonne O (25.00), pourtant j'ai testé avec différentes dates choisies... !?

- Peut-on nettoyer la liste de validation des dates et la faire triér ?

Merci.

 

[Gael]

Re : Somme en fonction date+catégorie

Re,

Petite erreur, je n'ai pas envoyé la dernière version (la plage est définie avec $N$2, donc le changement de ligne ne se faisait pas). Réparé avec le fichier joint.

Oui, on peut trier les plages de dates avec une macro soit pour une catégorie soit pour toutes.

Peux-tu préciser ce que tu entends par nettoyage? il est possible de supprimer aussi des dates mais sur quels critères?

@+

Gael

 

[apt]

Re : Somme en fonction date+catégorie

Re,

Oui, on peut trier les plages de dates avec une macro soit pour une catégorie soit pour toutes.

Peux-tu préciser ce que tu entends par nettoyage? il est possible de supprimer aussi des dates mais sur quels critères?

Enlever par exemple tout ce qui n'a pas de relation avec les dates, supprimer les doublons et enfin trier la liste résultante 😛

 

[Gael]

Re : Somme en fonction date+catégorie

Re,

Ben, c'est toujours pas clair. Peux-tu donner quelques exemples dans le tableau, quelles dates faudrait-il supprimer et où sont les doublons?

@+

Gael

 

[apt]

Re : Somme en fonction date+catégorie

Re,

La liste de validation pour les dates en P7 à la source =$A$1:$A$60

Donc on trouve des doublons de date plus les noms des catégories (BL, BT, EF et FIN) en plus du mot "Date".

Peut-on faire en sorte que cette liste de validation ne contiendra que des dates, sans doublons et trier ?

Comme ça :

12/10/2008​

13/10/2008
14/10/2008
15/10/2008
16/10/2008
17/10/2008
18/10/2008
20/10/2008
21/10/2008
25/10/2008
26/10/2008

Merci.

 

[Gael]

Re : Somme en fonction date+catégorie

Re,

Je n'avais pas compris qu'il s'agissait des listes de validation, pourtant tu l'avais bien précisé.

Ci-joint un essai avec en colonne T une liste des dates triées sans doublons avec des formules. Cette liste s'adapte automatiquement au contenu de la colonne A. Les formules sont copiées jusqu'à la ligne 27 mais si c'est insuffisant, il suffit de continuer en recopiant la dernière formule vers le bas.

La liste de dates est définie avec le nom "Dates" sous forme de plage variable.

Les validations font référence à la liste "Dates".

@+

Gael

 

[Gael]

Re : Somme en fonction date+catégorie

Re,

Et un dernier essai avec seulement les dates correspondant à la catégorie choisie.

J'ai enlevé dans ce cas le premier tableau qui n'avait plus son utilité.

@+

Gael

 

[apt]

Re : Somme en fonction date+catégorie

Bonjour Gael,

Ce n'est pas facile de jouer avec les définitions des plages 🙄

Alors en voulant adapter ton exemple dans mon classeur, je me suis trouvé dans une impasse 😡

Pourtant, j'ai changé les définitions des plages selon ma feuille 😕

Alors je viens de demander de l'aide pour éclaircir mon échec d'adaptation et de corriger les erreurs dans les formules 😀

En PJ ma feuille de travail.

Merci d'avance.

 

[Gael]

Re : Somme en fonction date+catégorie

Bonjour Apt, bonjour à tous,

Ci-joint ton fichier corrigé.

Pour le point 1, affichage de la date en S2, tu utilises une formule matricielle donc à saisir avec Ctrl-Maj-Entrée sinon le résultat sera faux. Il faut simplement vérifier la présence des accolades dans la formule.

Points 2 et 3 affichage des sommes. Ici 2 erreurs, le nom "Plage_cat" mal défini et comme tu as étendu les formules sur des cellules sans catégorie correspondante et devant la catégorie fictive "FIN", tu avais un #NA qui était intégré dans le calcul de la somme.

J'ai redéfini le nom "plage_cat": lorsque dans un nom tu fais référence à des cellules relatives (sans $ en ligne ou colonne) il faut vraiment faire très attention à la cellule active lors de la saisie. Pour "Plage_cat" par exemple, avant de saisir la formule, tu te positionnes en "M2" et tu peux rentrer la formule ...EQUIV(Dttes!$L2;Tableau;0)+1;;EQUIV(Dttes!$L3;Tableau;0)... le n° de ligne (ici 2 et 3) sera toujours pris en compte par Excel en fonction de la cellule active.

les formules de somme font référence à "Choix_cat" qui n'intègre pas la catégorie "FIN" plutôt que Categ (l'utilisation de Categ ne génère pas d'erreur sauf s'il y a une formule associée à "FIN" qui donne un #NA.

Pour mettre la formule avec un code VBA, tu fais un enregistrement automatique (Outils - macro - nouvelle macro, puis sélectionne une cellule et tu valides la formule) et ta formule sera traduite en VBA. C'est fait dans Macro1 ce qui donne:

Range("M7").Select
    ActiveCell.FormulaR1C1 = "=SUMPRODUCT((Plage_cat=R6C15)*Prix_cat)"

Pour les listes de dates, comme elles ne sont pas utiles dans la feuille principale, tu peux soit les déplacer dans la feuille ou sur une autre feuille (masquée ou non) soit mettre une police de couleur blanche pour les rendre invisibles.

Voilà, ça devrait commencer à être nickel maintenant.

@+

Gael

 

[apt]

Re : Somme en fonction date+catégorie

Bonjour Gael,

C'est vraiment sympa ce que tu fais ! 🙂

Je vais essayé le nouveau fichier et voir ce qui donne.

Merci bien

Salutations.

 

[apt]

Re : Somme en fonction date+catégorie

Bonsoir Gael,

Le grand tableau des dates, j'en aurais besoin pour voir la somme d'una date sélectionner sans passer par la selection de chaque catégorie.

J'aimerais l'integrer au deuxieme tableaude calcul.

Merci.

 

[apt]

Re : Somme en fonction date+catégorie

Bonjour à tous,

🙂

 

[apt]

Re : Somme en fonction date+catégorie

Bonjour,

J'ai essayé de trouver une somme pour toutes les catégories, avec une date séléctionner, mais sans succés :

1)

=SOMMEPROD((Tableau=$O$18)*$E:$E)

2)

=SOMMEPROD(($A:$A=$O$18)*$E:$E)

Merci.