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

fanou06

XLDnaute Occasionnel
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.
 

Pièces jointes

  • Visites franchises.xlsx
    65.3 KB · Affichages: 16

fanou06

XLDnaute Occasionnel
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

JHA

XLDnaute Barbatruc
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

JHA

XLDnaute Barbatruc
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
 

AtTheOne

XLDnaute Impliqué
Supporter XLD
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

AtTheOne

XLDnaute Impliqué
Supporter XLD
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

Discussions similaires

Réponses
3
Affichages
212
Réponses
5
Affichages
476

Statistiques des forums

Discussions
311 735
Messages
2 082 024
Membres
101 873
dernier inscrit
excellllll