XL 2013 Comparer un tableau de résultats avec des objectifs avec un TDC

[saintphilibert]

Bonjour à tous,

J'ai un tableau avec des prestations horaire d'un employé.
Je veux faire un tableau croisé dynamique pour afficher directement la somme d'heure de chaque "Remarque" (EDD, Ferié...) (accessoirement à l'intérieur d'un bloc.
Attention c'est des calculs d'heures format [hh]:mm
Ca ca va.
Dans mon TDC j'ai donc "Bloc" et "Remarque" dans mes lignes et Somme de "Total" les heures dans valeurs (ou "Toto" si les heures foirent...).

Dans ce TDC, je souhaiterais afficher les heures que l'employé est censé faire dans chaque "Remarque" de chaque "Bloc".
Dans la colonne à côté donc...
Si une "Remarque" listée dans la feuille "encodage" n'existe pas dans la feuille "Référentiel" on n'affiche rien.... ou alors on recrée toutes les "remarques" existante et on leur met 0...

Je joins le fichier excel .xlsx

 

[Dugenou]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Bonjour,
Il faut une colonne d'objectif dans ton tableau de données : mais où le trouve t on cet objectif ??
A te lire

 

[saintphilibert]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Il est dans la feuille "référentiel".
En clair j'aimerais avoir dans mon TCD une

Colonne
Bloc1

EDD 9 (la somme des EDD du bloc1 et dans la colonne suivante 22 qui est indiqué dans le référentiel​

Maladie 3 et col suivante 3​

 

[chris]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Bonjour
Coucou dugenou

Soit, si tu as la version pro plus de 2013 ou 365 pro, tu te sers de Powerpivot en établissant une relation entre tes tables sous réserve d'avoir tous les cas dans le référentiel écrits correctement pas ferie d'un côté et férié de l'autre.

Par ailleurs donne des noms parlants à tes tableaux...

Ton fichier utilise powerpivot mais si tu as une version autre que celles citées tu ne peux accéder au modèle de données.

 

[Dugenou]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Bonjour Jean-Philippe, Salut Chris,

Un essai en pj : remplacé la colonne toto par objectif dans la feuille encodage
corrigé les données du référentiel et ajouté une colonne pour la recherche (on peut faire sans avec une formule matricielle si besoin)
remplacé le powerpivot par un tcd classique

voir si j'ai compris le besoin

 

[saintphilibert]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Merci Dugenou pour déjà toute ton aide.

En fait si on ne prend que le bloc1 dans le TCD, il faudrait 22 et pas 44...
L'objectif des EDD reste 22 pour tous les EDD du bloc1...

J'étais peut-être pas clair dans mon exemple...
On a la fiche de Jean-Paul. Jean-Paul doit prester x heures par trimestre (Bloc1, 2...). Il fait plus d'heures certaines semaines suivant les saisons. On doit compter ses fériés, ses maladies...
Pour le premier trimestre (Bloc 1), Jean-Paul doit faire 200h par ex.
26h max d'EDD (objectif), 26h max d'accompagnement A, 30h max de Truc, 50h max de Trac...
Tous ces max, je les encode dans le Référentiel...
Comme ca si Jean-Paul dépasse ses EDD sur son trimestre, j'en remet sur Sophie, Martine, Brigitte
C'est pour une gestion d'équipe.

Dans dans le TCD de Jean-Paul, j'aurais voulu dans une colonne qu'il somme les EDD du bloc1, les Accompagnement A, les Truc les trac, et que dans la colonne à côté il m'écrive le max (l'objectif) qu'il pouvait faire...

J'espère que tu comprendras ;-)
JPhi.

 

[Dugenou]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Re,
J'ai un peu de mal avec

En fait si on ne prend que le bloc1 dans le TCD, il faudrait 22 et pas 44...

et

26h max d'EDD (objectif)

alors que dans le référentiel il est noté 22.....
En pj un essai avec une formule qui ne donne le référentiel dans la colonne objectif qu'une fois par bloc (content déjà de comprendre qu'un bloc est une unité de temps...

Cordialement

 

[saintphilibert]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Slt Dugenou,

Tu es dans le bon, et donc j'ai décidé de rééclaircir mes intitulés...
J'ai essayé de réappliquer tes formules en vain...
Peux-tu y regarder?

 

[Dugenou]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Bonjour,
Il fallait corriger aussi les intitulés des blocs dans le tableau de données et adapter le recherchev suite à l'addition d'une colonne.
Voir pj

 

[saintphilibert]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Un tout grand merci.
C'est bien ça.
Peux-tu juste encore, pour ma curiosité personnelle, m'expliquer ta formule, pq un nb.si.ens avant la recherchev? Ca correspond à quoi déjà le 4,faux,0 en fin de formule recherchev?
Encore merci.

 

[Dugenou]

Re : Comparer un tableau de résultats avec des objectifs avec un TDC

Bien volontiers :
le NB.SI.ENS($A$6:A6;A6;$C$6:C6;C6) compte le nb d’occurrences de la valeur de la colonne remarque pour un même bloc. Une utilisation avisée des $ permet de recopier en bas en allongeant la zone. Donc de compter le nb de fois qu'on a la même remarque pour le même bloc depuis le haut du tableau jusqu'à la ligne considérée. Si(NB.SI.ENS(...)>1;0;---) permet d'afficher 0 quand on arrive à la deuxieme (et plus) occurrence de la même remarque pour le même bloc => l'idée étant de ne l'afficher qu'une fois par bloc puisque

L'objectif des EDD reste 22 pour tous les EDD du bloc1

Si maintenant on s'occupe du --- ci dessus :
RECHERCHEV([@[Concat_Encod]];Tableau2[[concat]:[Objectif]];4;FAUX)
4 car on veut afficher la 4eme colonne du Tableau2[[concat]:[Objectif]] et FAUX pour n'avoir un résultat affiché qu'en cas de correspondance exacte
enfin, le recherchev(...) est enfermé dans un sierreur(....;0) pour éviter d'afficher des #N/A quand il n'y a pas de correspondance exacte

Cordialement