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

[fanou06]

Bonjour,

Je mets en PJ mon fichier.
Je ne sais pas si la formule sommeprod peut fonctionner ou si une NI.SI.ENS convient mieux ou une autre.
En fait je suis perdu sur mon tableau.

Ce que je cherche à faire :

Dans l'onglet visites je mets les dates et le temps de début et de fin de chaque visite de franchise.

Dans l'onglet stats je souhaiterais :
- Dans B36:Q36 : Que cela me donner le nombre total des visites effectuées par mois (selon les données de l'onglet 'Visites').
- En B41 : Nombre de visites 2021
- En B42 : Nombre de visites 2022
- En B43 : Nombre total des heures de visites 2021
- En B44 : Nombre total des heures de visites 2022

Au final, je recherche une formule permettant de mettre des critères de dates (Mois/Année) et calculer le nombre de jours de visites et les heures.
Je ne sais pas si cela est réalisable.

merci.

 

[fanou06]

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.

 

[JHA]

Bonjour à tous,

En "C2" de l'onglet "Bilan"

=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

 

[fanou06]

Merci, vais tester.

 

[fanou06]

Bonjour à tous,

En "C2" de l'onglet "Bilan"

=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

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.

 

[JHA]

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.

=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

=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

 

[fanou06]

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

 

[AtTheOne]

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) :

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

• Le temps des visites (en D13) :

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

• Le temps DRH (en E13) :

  =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) :

=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

 

[fanou06]

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

Merci.

 

[chris]

Bonjour

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

 

[fanou06]

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

Merci.

 

[fanou06]

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

 

[AtTheOne]

Bonsoir à tous, bonsoir @fanou06

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

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

 

[fanou06]

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.

 

[AtTheOne]

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 :

=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

 

[fanou06]

Merci, je vais tester et surtout apprendre la formule

Merci ++++