XL 2019 Statistiques "avancées" . . . Réalisables ?

F

fanou06

XLDnaute Occasionnel

• 8 Février 2022

• #16

Merci, ça fonctionne.

Et concernant ce tableau (En PJ), quelles formules puis je mettre pour comptabiliser le nombre de visites et d'heures ?
Est-ce la même formule ?
Car dans ce tableau, tout est affiché à la suite donc on peut utiliser un NB.SI et/ou SOMMEPROD ?
Mais je teste aussi des formules pour que EXCEL prenne par exemple le mois en formule selon une date.
Ex : 10/01/22 = JANVIER. J'ai testé INDEX sans résultat.

Merci pour votre aide.

 

Pièces jointes

• 2021_2022_VISITES_DRHBILAN.xls

  43 KB · Affichages: 4

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 8 Février 2022

• #17

Bonjour à tous,

En "C2" de l'onglet "Bilan"

VB:

=SOMMEPROD((MOIS(Visites_2022!$C$2:$C$29)=MOIS(1&Bilan!C$1))*(Visites_2022!$A$2:$A$29=Bilan!$A2)*(Visites_2022!$B$2:$B$29=Bilan!$B2))

copier vers la droite et le bas

JHA

 

Pièces jointes

• 2021_2022_VISITES_DRHBILAN.xls

  57.5 KB · Affichages: 6

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 8 Février 2022

• #18

Merci, vais tester.

 

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 8 Février 2022

• #19

JHA à dit:

Bonjour à tous,

En "C2" de l'onglet "Bilan"

VB:

=SOMMEPROD((MOIS(Visites_2022!$C$2:$C$29)=MOIS(1&Bilan!C$1))*(Visites_2022!$A$2:$A$29=Bilan!$A2)*(Visites_2022!$B$2:$B$29=Bilan!$B2))

copier vers la droite et le bas

JHA

Cliquez pour agrandir...

C'est ok, mais pour les heures ? Est-ce le même type de formule ?
Par exemple savoir combien d'heure par moi par site ?
Merci.

 

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 8 Février 2022

• #20

Bonjour à tous,

Je ne comprends pas ta question, apparemment, le numéro de site est affilié à un "DPT".

La formule proposée te donne le nombre par mois, par site et "DPT".

Si tu veux, tu peux ôter les "DPT" mais tu auras le même résultat dans ton exemple.

VB:

=SOMMEPROD((MOIS(Visites_2022!$C$2:$C$29)=MOIS(1&Bilan!C$1))*(Visites_2022!$B$2:$B$29=Bilan!$B2))

Edit il n'y a pas d'année 2021 dans ton fichier mais au cas où pour l'année 2022

Code:

=SOMMEPROD((MOIS(Visites_2022!$C$2:$C$29)=MOIS(1&Bilan!C$1))*(ANNEE(Visites_2022!$C$2:$C$29)=2022)*(Visites_2022!$B$2:$B$29=Bilan!$B2))

JHA

 

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 8 Février 2022

• #21

Je vais tester.
Je cherche en fait à faire un tableau pouvant donner les heures selon les visites faites sur différents sites par mois.
Merci

 

Vote positif 0 Vote négatif

AtTheOne

XLDnaute Accro

Supporter XLD

• 9 Février 2022

• #22

Bonne nuit à tous, Bonne nuit @fanou06

D'abord je suis passé au format xlsx, pour gérer efficacement les tableaux structurés.
Pour tes visites 2022, j'ai transformé ta plage en tableau structuré (nommé _Tb_Visites)

Pour tes calculs sur l'année (Nombre, temps, temps DRH) et en supposant qu'il n'y ait qu'une année,

• Le nombre de visites par DPT, Site (en C13) :
  VB:

  =NB.SI.ENS(_Tb_Visites[DPT];Bilan!A23;_Tb_Visites[N° de SITE];$B23)

• Le temps des visites (en D13) :
  Code:

  =SOMME.SI.ENS(_Tb_Visites[TEMPS];_Tb_Visites[DPT];$A23;_Tb_Visites[N° de SITE];$B23)

• Le temps DRH (en E13) :
  Code:

  =SOMME.SI.ENS(_Tb_Visites[TEMPS DRH];_Tb_Visites[DPT];$A23;_Tb_Visites[N° de SITE];$B23)

Les formules du Tableau par mois deviennent (en C2) :

Code:

=SOMMEPROD((_Tb_Visites[DPT]=Bilan!$A2)*(_Tb_Visites[N° de SITE]=Bilan!$B2)*(MOIS(_Tb_Visites[DATE])=MOIS(1&Bilan!C$1)))

(A recopier par Copier-Coller (ne pas tirer vers la droite sinon le nom des colonnes s'incrémente))

L’intérêt du tableau structuré c'est que tu n'as plus besoin de te soucier du nombre de lignes qu'il contient, il s'etend vers le bas dès qu'on saisie une valeur sous sa dernière ligne et on fait simplement référence au nom des colonnes (Ex : _Tb_Visites[DPT] pour la colonne DPT, _Tb_Visites[TEMPS DRH] pour les temps DRH etc.)


Voir le fichier joint
Amicalement
Alain

 

Pièces jointes

• 2021_2022_VISITES_DRHBILAN.xlsx

  20.3 KB · Affichages: 5

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 16 Février 2022

• #23

Petites précisions je ne comprends pas le tableau structuré.
Je pense c'est hyper efficace et pratique mais comment en créer ?

Merci.

 

Vote positif 0 Vote négatif

chris

XLDnaute Barbatruc

• 16 Février 2022

• #24

Bonjour

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

 

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 16 Février 2022

• #25

Merci beaucoup, c'est vrai que c'est bien mieux.
J'ai eu du mal à trouver l'option

Merci.

 

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 16 Février 2022

• #26

Par contre je n'arrive pas à avoir le nombre d'heures selon les mois . . .
Merci.

 

Vote positif 0 Vote négatif

AtTheOne

XLDnaute Accro

Supporter XLD

• 16 Février 2022

• #27

Bonsoir à tous, bonsoir @fanou06

fanou06 à dit:

Par contre je n'arrive pas à avoir le nombre d'heures selon les mois . . .

Cliquez pour agrandir...

Peux-tu préciser où dans ton fichier car il y a déjà un récapitulatif par mois dans le fichier exemple du post #22 ?
Amicalement
Alain

 

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 16 Février 2022

• #28

Non, c'est le récapitulatif du nombre de visites mensuelles, mais pas des heures.
J'aimerais en fait que chaque mois je connaisse les heures passées selon les sites.

 

Vote positif 0 Vote négatif

AtTheOne

XLDnaute Accro

Supporter XLD

• 16 Février 2022

• #29

Re bonsoir à tous,
Ok,

En insérant une ligne et en rajoutant un choix en $C$1 pour le type de bilan (Nb Visite, durée de la visite, Heures DRH) la formule dans le tableau de bilan devient :

Code:

=SOMMEPROD((_Tb_Visites[DPT]=Bilan!$A3)*
           (_Tb_Visites[N° de SITE]=Bilan!$B3)*
           (MOIS(_Tb_Visites[DATE])=MOIS(1&Bilan!C$2))*
            SI($C$1="Nb Visites";(_Tb_Visites[DPT]<>"");
            SI($C$1="Durée";_Tb_Visites[TEMPS];
            SI($C$1="Temps DRH";_Tb_Visites[TEMPS DRH];
             0))))

En ajoutant un format conditionnel, on affiche selon le cas des heures ou des nombres standards.
Les formules du bas du tableau ont été adaptées pour afficher toujours la bonne valeur (quelque soit le choix en $C$1).

A bientôt
Amicalement
Alain

 

Pièces jointes

• 2021_2022_VISITES_DRHBILAN.xlsx

  21.6 KB · Affichages: 3

Vote positif 0 Vote négatif

F

fanou06

XLDnaute Occasionnel

• 16 Février 2022

• #30

Merci, je vais tester et surtout apprendre la formule

Merci ++++

 

Vote positif 0 Vote négatif