Somme pour le mois d'une année d'un tableau

Bonjour,

Je viens vers de nouveau car je cherche à récupérer la somme journalière pour chaque colonne de mon tableau data, ceci avec deux critère le mois et l'année pour alimenter ma Feuil1.

ex: nous somme au mois de mars 2019 il me faudra récupérer mars 2018 et mars 2019 et ceci pour chaque mois.

Le choix ce fait par la cellule B3 de la Feuil1.

Je suis tombé sur le fonction SOMMEPROD() dans la doc mais je ne vois pas comment l'applique sur mon tableau.

Pour un feuille simple j'aurais eu :

=SOMMEPROD((MOIS(A1:A217)=1)*B1:B217)

La somme de la colonne B pour le mois de janvier présent dans la colonne A.

Merci par avance.

Bonjour,

Pourquoi ne pas faire un TCD ? Sans calcul tu obtiens les valeurs.

bonjour

salut Steelson

tout à fait d'accord avec toi

je suis toujours étonné qu'on cherche des formules alors qu'on n'a pas appris le menu Accueil, ni le menu Données, ni les fabuleux TCD (dont on se demande pourquoi ils ne sont pas dans le menu Données )

amitiés à tous

Merci pour vos réponses.

Ce n'est pas la première fois que l'on me suggère les TCD donc j'ai chercher comme y récupérer des données dedans.

J'ai trouvé comment les récupérer pour une année donnée :

=LIREDONNEESTABCROISDYNAMIQUE($A$3;"2019 'CA COUVERT'")

mais je ne vois pas comment ajouter mon autre paramètre qui n'est autre que le mois qui est en cellule B3 de la Feuil1.

Bonjour,

Si ton TCD ne comporte pas de champ Mois, tu ne pourras pas récupérer une valeur mensuelle !?

Cdlt.

Bonsoir,

J'ai un champ avec les date de type : 01/03/2019

Il y a juste un filtre pour obtenir le cumul mensuel.

Re,

Tu n'as donc pas de champ Mois.

Dans ton TCD, tu peux afficher ton champ Date, en année, trimestre, mois, etc...

Joins un fichier (représentatif et anonymisé si nécessaire).

Cdlt.

Re,

Effectivement pour l'affichage du champ DATE.

Voici le fichier en question.

=LIREDONNEESTABCROISDYNAMIQUE("Somme de CH POTENTIELLES";$A$3;"DATE";3;"Années (DATE)";2019)

... on récupère bien la valeur du mois 3 (Mars) !

Cela se trouve dans DATE car il y a eu une action "grouper"

C'était déjà le cas dans le fichier que j'avais posté plus tôt dans la journée.

Merci c'est si simple quand la solution est sous les yeux.

J'aurais juste une dernière question, puis ajouter comme paramètre un jour dans la formule :

=LIREDONNEESTABCROISDYNAMIQUE("Somme de CH POTENTIELLES";$A$3;"DATE";3;"Années (DATE)";2019;"Jours (DATE)";10)

Je suppose que je vais devoir ajouter dans Lignes : Jours (DATE) avec DATE et Années (DATE)

Oui si ton TCD affiche les jours.

Pour ne pas se tromper (l'ordre est important), fais d'abord le TCD, ensuite faire =une_cellule_du_TCD_la_plus_fine et remplace les valeurs par les paramètres voulus

Re

D'accord j'ai réussi a ajouté chaques jours dans le TCD.

Mais au moment de modifier la formule en "J7" pour avoir le cumul jusqu'au jour renseigné en "B4" par celle ci :

=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("CH POTENTIELLES";TCD!$A$3;"Mois (DATE)";MOIS($B$4);"Années (DATE)";$J$6);"DATE";JOUR($B$4);"")

J'obtiens ceci :

Je pensais avoir compris le principe mais ça n'a pas l'air d'être le cas.

D'après la Doc je me retrouve avec :

LIREDONNEESTABCROISDYNAMIQUE(champ_données, tableau_croisé_dyn, [champ1, élément1, champ2, élément2], ...)

Et "Champ1, Élément1, Champ2, élément2" je dois pouvoir avoir 126 paires de noms de champs.

De plus je viens de m'apercevoir qu'il manquait des valeurs pour certaines dates et colonne ex: 1,2,3 mars....

alors que ces valeurs sont présentes dans le tableau de la feuille Données.

edit:

En mentant ceci j'ai plus l'erreur mais rien en retour du moins : #REF!

=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("CH POTENTIELLES";TCD!$A$3;"DATE";JOUR($B$4);"Mois (DATE)";MOIS($B$4);"Années (DATE)";$J$6);"")

Je suis aussi scotché !!

Car la formule prise en direct pour le 2 mars 2019 donne :

=LIREDONNEESTABCROISDYNAMIQUE("Somme de CH POTENTIELLES";TCD!$A$3;"DATE";62;"Mois (DATE)";3;"Années (DATE)";2019)

Ok pour le mois, Ok pour l'année ... mais la date du 2 mars est remplacée par 62 donc même pas par 2 !!!!!!!!! sachant en plus que le 2 mars est le 61ème jour de l'année !

Une solution serait pour les valeurs quotidienne de faire une RECHERCHEV (ou un SOMMEPROD si tu avais plusieurs lignes pour une même date) ou bien de doubler la colonne DATE et ne pas regrouper sur la seconde colonne !

J'ai compris pourquoi mais c'est tordu ! à suivre ...

Microsoft, dans sa grande stupidité (là on peut le dire !), prend le n° du jour dans l'année. Mais il compte systématiquement 29 jours pour le mois de février ! pftt .... comme si toutes les années devaient être bissextiles.

Ainsi, pour le jour, il faudrait mettre :

DATEDIF(DATE(ANNEE(D6)-1;12;31);D6;"d")+SI(ET(MOIS(D6)>=3;MOD(ANNEE(D6);4)<>0);1;0)

c'est-à-dire le nombre de jour depuis le 31 déc de l'année précédebnte, plus 1 si le mois est supérieur ou égal à 3 (mars) et si l'année n'est pas bissextile.

Cela me laisse rêveur !

re à tous

notez que, c'est perso, je ne fais plus aucun calcul de ce genre : extraire d'un TCD, comparer des années ou des écarts à un budget etc. ni aucun graphique dans Excel

je n'ai jamais aimé les LIRETABLEAUCROIStruc. Pour moi, un TCD est une fonctionnalité qui exploite les données, il n'en crée pas et ne peut servir de base à d'autres formules. Mais ce n'est que mon avis.

dans Excel, je ne fais plus que de simples TCD ou GCD

voir le truc magique qui fait tout ça bien mieux qu'Excel : Power BI Desktop gratuit.

sans s'opposer https://www.youtube.com/watch?v=jmTedSuKers

attention : vous allez devenir addicts

amitiés excelliennes à tous

@jmd merci pour l'info cela fait beaucoup d'info pour moi tout cela.

@Steelson

Oui effectivement étonnant que Microsoft ne gère ce genre de cas particulier.

Je me retrouve donc avec :

=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("CH POTENTIELLE";TCD!$A$3;"DATE";DATEDIF(DATE(ANNEE(D6)-1;12;31);D6;"d")+SI(ET(MOIS(D6)>=3;MOD(ANNEE(D6);4)<>0);1;0);"Mois (DATE)";MOIS(D6);"Années (DATE)";ANNEE(D6));"")

et qui fonctionne parfaitement.

Un grand merci.

Une idée du pourquoi dans mon TCD je me retrouve avec des valeurs vide pour le 1,2,3,24 et 26 mars 2018 et 2019 alors que dans le tableau data ces valeurs sont bien renseignées pour les colonnes "CH POTENTIELLE", "TOTALT CH INCL DAYUSE" et "NB COUVERTS JOUR" ?

De ce fait les totaux sont faussés.

Re

Par acquis de conscience j'ai retapé les valeurs a la main, actualisé et TCD c'est retrouvé avec les valeurs.

Pas tout compris.

Merci a tous pour votre aide mon problème est résolu.

laloute a écrit :

Par acquis de conscience j'ai retapé les valeurs a la main, actualisé et TCD c'est retrouvé avec les valeurs.

laloute a écrit :

Je me retrouve donc avec :

=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("CH POTENTIELLE";TCD!$A$3;"DATE";DATEDIF(DATE(ANNEE(D6)-1;12;31);D6;"d")+SI(ET(MOIS(D6)>=3;MOD(ANNEE(D6);4)<>0);1;0);"Mois (DATE)";MOIS(D6);"Années (DATE)";ANNEE(D6));"")

et qui fonctionne parfaitement.

Tu peux aussi modifier ceci LIREDONNEESTABCROISDYNAMIQUE("CH POTENTIELLE" ................

et remplacer "CH POTENTIELLE" par la cellule qui contient cette en-tête de colonne ... c'est alors plus rapide à dupliquer !

Re,

Ah oui ce serait plus simple.

J'ai une dernière question pour rafraîchir mon TCD à chaque modifications.

ActiveWorkbook.RefreshAll

Je l'ai mis au changement de feuille

c'est pas un peu lourd ?

Ah !

Moi j'utilise ceci :

    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh

Tu peux le mettre à l'activation de la feuille en effet.