Index, Equiv délimité par une date

[imadaddy]

Bonjour à tous,

J'aurai d'une précieuse aide. Nous avons récemment fait des modifs dans nos tableaux ce qui m'amène à modifier des formules sur d'autres tableaux. Je préfère une formule à une macro.

Le premier tableau est celui où je dois renseigner la formule. Le 2ème est ma base de données (bien plus grande en vrai 30000 ligne et une 20aine de colonne).

Je souhaiterais faire que le résultat dans le tableau 1 indique la somme des ventes par produit, destination et année tout en étant inférieur à la date du 31.05.2019 pour 2019 et 31.05.2020 pour 2020.

Je vous joins le dit tableau afin de vous faire une idée.

Merci par avance.

 

[sylvanu]

Bonjour Imadaddy,
Un essai en PJ.
J'ai supposé que vous vouliez les quantité du 31/05/2018 au 31/05/2019 et du 31/05/2019 au 31/05/2020. Donc par année de mai à mai.

 

[job75]

Bonjour imadaddy, sylvanu,

Je comprends qu'il faut calculer les sommes entre le 1er janvier et la fin du mois indiqué (mai).

Voyez le fichier joint avec la liste de validation en B3 et cette formule en B8 :

=SOMME.SI.ENS($D$19:$D$36;$A$19:$A$36;">="&DATE(B$7;1;1);$A$19:$A$36;"<="&DATE(B$7;EQUIV($B$3;$Z$1:$Z$12;0)+1;0);$B$19:$B$36;$A8;$C$19:$C$36;SI(ESTTEXTE(B$6);B$6;A$6))

à propager à droite et vers le bas.

Avec décembre en B3 on vérifiera que les totaux en R15 et D37 sont égaux.

Edit : notez que cette formule n'utilise pas les colonnes Mois et Année du tableau du bas, elles sont donc inutiles et on peut les supprimer.

A+

 

[job75]

Autre solution avec SOMMEPROD dans ce fichier (2) :

=SOMMEPROD($D$19:$D$36;(ANNEE($A$19:$A$36)=B$7)*(MOIS($A$19:$A$36)<=EQUIV($B$3;$Z$1:$Z$12;0))*($B$19:$B$36=$A8)*($C$19:$C$36=SI(ESTTEXTE(B$6);B$6;A$6)))

C'est finalement plus simple et a l'avantage de fonctionner aussi sur les versions antérieures à 2007.

 

[imadaddy]

Bonjour Imadaddy,
Un essai en PJ.
J'ai supposé que vous vouliez les quantité du 31/05/2018 au 31/05/2019 et du 31/05/2019 au 31/05/2020. Donc par année de mai à mai.

Ça fonctionne. Merci bcp je connaissais pas cette fonction, elle est utile.

merci encore.

 

[imadaddy]

Autre solution avec SOMMEPROD dans ce fichier (2) :

=SOMMEPROD($D$19:$D$36;(ANNEE($A$19:$A$36)=B$7)*(MOIS($A$19:$A$36)<=EQUIV($B$3;$Z$1:$Z$12;0))*($B$19:$B$36=$A8)*($C$19:$C$36=SI(ESTTEXTE(B$6);B$6;A$6)))

C'est finalement plus simple et a l'avantage de fonctionner aussi sur les versions antérieures à 2007.

Ta formule est vraiment top même si j'ai énormément de mal à la comprendre. Dans un premier temps je vais faire avec celle de sylvanu, mais dans un futur proche je choisirai la tienne car en terme de rapidité c'est excellent.

La liste déroulante comment fais-tu pour la mettre à fin mai ou un autre mois stp ?

Merci énormément.

 

[imadaddy]

En faite la meilleure des solutions est un croisement de vos deux méthodes. Celle de Job75 pour fixer une délimitation de date grâce à la liste déroulante. Et celle de sylvanu pour délimiter en fixe au 31/12/2019.

Gros big up. Merci encore.

 

[job75]

Et celle de sylvanu pour délimiter en fixe au 31/12/2019.

Pourquoi le 31/12/2019 ???

sylvanu calcule les sommes du 31/05 de l'année précédente au 31/05 de l'année inscrite en ligne 7, bornes exclues.

Avec les fichiers (1) et (2) j'ai calculé les sommes du 01/01 au dernier jour du mois inscrit en B3, bornes incluses.

Avec ce fichier (3) je calcule comme sylvanu les sommes des 12 mois précédant le dernier jour du mois inscrit en B3, mais ce dernier jour est inclus.

La formule en B8 dérive de celle du fichier (1) :

=SOMME.SI.ENS($D$19:$D$36;$A$19:$A$36;">"&DATE(B$7-1;EQUIV($B$3;$Z$1:$Z$12;0)+1;0);$A$19:$A$36;"<="&DATE(B$7;EQUIV($B$3;$Z$1:$Z$12;0)+1;0);$B$19:$B$36;$A8;$C$19:$C$36;SI(ESTTEXTE(B$6);B$6;A$6))

PS : attention, sylvanu a entré "123" en D19 pour le récupérer en B8.

 

[sylvanu]

Bonjour tout le monde,
Job, 123 était pour le test. Je ne teste jamais avec 0, ça peut être piégeant.

 

[imadaddy]

Pourquoi le 31/12/2019 ???

sylvanu calcule les sommes du 31/05 de l'année précédente au 31/05 de l'année inscrite en ligne 7, bornes exclues.

Avec les fichiers (1) et (2) j'ai calculé les sommes du 01/01 au dernier jour du mois inscrit en B3, bornes incluses.

Avec ce fichier (3) je calcule comme sylvanu les sommes des 12 mois précédant le dernier jour du mois inscrit en B3, mais ce dernier jour est inclus.

La formule en B8 dérive de celle du fichier (1) :

=SOMME.SI.ENS($D$19:$D$36;$A$19:$A$36;">"&DATE(B$7-1;EQUIV($B$3;$Z$1:$Z$12;0)+1;0);$A$19:$A$36;"<="&DATE(B$7;EQUIV($B$3;$Z$1:$Z$12;0)+1;0);$B$19:$B$36;$A8;$C$19:$C$36;SI(ESTTEXTE(B$6);B$6;A$6))

PS : attention, sylvanu a entré "123" en D19 pour le récupérer en B8.

Bonjour à tous,

désolé de ne pas être rentré dans le détail. Mais à la base j'ai deux tableaux qui doivent être renseignés l'un qui est fixe au 31/12/2019 et 31/12/2020 même si l'année n'est pas terminé. Et l'autre qui vient prendre le cumul du 01/01/2019 au 31/05/2019 puis un autre fois en Septembre au 31/08/2019 (idem pour 2020). Nous réajustons le budget en fonction des ventes en comparant du 01012019 au 31052019 et celle entre le 01012020 au 31052020.

Job75 l'apport de ta formule me permet un énorme gain de temps avec la formule et la liste déroulante. Plus tôt que de changer de formule à chaque date butoir (31/05 et 31/08 de N et N-1 ) j'ai juste à dérouler et choisir les mois voulus.

Pour terminer mon explication lié à mes soucis de budget, j'ai besoin de savoir ce qu'il reste à faire en terme de vente en faisant la différence entre les ventes totales 2019 et les ventes réalisées entre le 01/01/2019 et 31/05/2019. Donc j'ai toujours besoin d'avoir les ventes totales réalisées au 31/12/2019.

C'est pour ça que j'ai opté pour vos deux solutions.

Job75 peux-tu me dire comment faire une liste déroulante avec fin de mois stp ?

Merci encore.

 

[job75]

@imadaddy je n'y comprends plus rien, joignez un fichier avec les résultats escomptés.

Job75 peux-tu me dire comment faire une liste déroulante avec fin de mois stp ?

Fin de mois ? On pourrait y mettre 31/01 mais pas 28/02 puisqu'en 2020 il faudrait 29/02.