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.

 

[AtTheOne]

Re-Bonsoir,
Je décortique :
VRAI ou FAUX est converti en 1 ou 0 dans ce qui suit

=SOMMEPROD(
... On va faire le produit de toutes les matrices qui suivent puis en faire la somme
(_Tb_Visites[DPT]=$A3)*
... une matrice, chaque élément vaut VRAI ou FAUX en fonction du fait que le DPT = $A3 ou non
(_Tb_Visites[N° de SITE]=$B3)*
... Une matrice, chaque élément vaut VRAI ou FAUX en fonction du fait que le N° de SITE = $B3 ou non
(MOIS(_Tb_Visites[DATE])=MOIS(1&C$2))*
... Une matrice, chaque élément vaut VRAI ou FAUX en fonction du fait que le mois de la date = le mois de "1"&C$2 (par ex 1Janvier)
SI($C$1="Nb Visites";(_Tb_Visites[DPT]<>"");
... Si $C$1 vaut "Nb Visite", une matrice ou chaque élément vaut VRAI (si le DPT est renseigné)
SI($C$1="Durée";_Tb_Visites[TEMPS];
... Sinon Si $C$1 vaut "Durée" une matrice qui contient les durée de visite (TEMPS)
SI($C$1="Temps DRH";_Tb_Visites[TEMPS DRH];
... Sinon Si $C$1 vaut "Temps DRH" une matrice qui contient les temps DRH de chaque visite
0))))
... Sinon 0

On multiplie ces matrices membre à membre, si pour une ligne les 3 premières conditions sont remplis on a 1 sinon on a 0.
Ensuite, suivant le choix en $C$1, on multiplie membre à membre ce résultat par une matrice de 1 (pour Nb Visites) ou par une matrice de temps pour "Durée" ou "Temps DRH".
Enfin on fait la somme des membres de la matrice finale ...

Un petit tableau explicatif :

Voilà
Bonne nuit
Alain

 

[fanou06]

Un énorme merci

 

[fanou06]

Re,

Malheureusement cela ne fonctionne pas
Ca me marque ZERO . . .

Par exemple pour n'avoir que PARIS (sans les sites détaillés) ça me met une erreur.

 

[AtTheOne]

Bonjour à tous, bonjour @fanou06

Malheureusement cela ne fonctionne pas
Ca me marque ZERO . . .

Avec le fichier joint dans le post #29 :

Ça n'affiche 0 que si $C$1 est vide !

Par exemple pour n'avoir que PARIS (sans les sites détaillés) ça me met une erreur.

Là il faut supprimer la condition sur le Site : *(_Tb_Visites[N° de SITE]=$B3) de la formule =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))))

Envoie-moi tes formules que je comprenne ce qui se passe ...

Amicalement
Alain

 

[fanou06]

Dans ma formule je ne comprends pas le nom en regardant la tienne 'BILAN! puisque je suis déja sur la page Bilan. Aussi le "nb visites" . . .

Merci.

 

[fanou06]

Bonjour,

J'ai créé un tout autre fichier plus détaillé avec divers sites.
J'ai suivi ton conseil concernant les tableaux structurés.
Et dans l'onglet HEURES 2022 en E2 je ne sais pas quoi mettre afin d'avoir le temps passé. Un seul temps en exemple, pour le second (EOH) je pense la formule sera quasi identique . . .

Merci beaucoup.

 

[AtTheOne]

Bonsoir à Tous, Bonsoir @fanou06

Dans ma formule je ne comprends pas le nom en regardant la tienne 'BILAN! puisque je suis déja sur la page Bilan. Aussi le "nb visites" . . .

Le préfixe 'Nom de feuille'! est ajouté par EXCEL lorsqu'en écrivant une partie de formule, on va sur une autre feuille puis qu'on revient sur la première, ce n'est pas gênant (sauf pour la lecture) et on peut le supprimer.

Dans mon exemple je laissait le choix "nb visites", "Durée", ou "Temps DRH" en $C$1 pour faire varier le résultat de la formule fait un essai sur mon fichier exemple en changeant la valeur de C1 de la feuille Bilan, tu verras les résultats évoluer. (C'est l'objet des 3 fonctions SI(test;si vrai,si faux) dans la formule)

J'ai créé un tout autre fichier plus détaillé avec divers sites.

J'ai repris tes formules pour utiliser les tableaux structurés.
Remarque : avec les tableaux structurés inutile de prévoir des lignes vierges à la fin comme tu l'as fait, le tableau s'étendra automatiquement lorsque tu saisiras une valeur sous sa dernière ligne.

Formule pour "Bilan par unités 2021" (en C2)

=SOMMEPROD((MOIS(visites2021[DATE])=MOIS(1&C$1))*(visites2021[SERVICE]=$A2)*(visites2021[UNITE]=$B2))

Formule pour "Bilan par services 2021" (en B2)

=SOMMEPROD((MOIS(visites2021[DATE])=MOIS(1&B$1))*(visites2021[SERVICE]=$A2))

Formule pour "Bilan par unités 2022" (en C2)

=SOMMEPROD((MOIS(visites2022[DATE])=MOIS(1&C$1))*(visites2022[SERVICE]=$A2)*(visites2022[UNITE]=$B2))

Formule pour "Bilan par services 2022" (en B2)

=SOMMEPROD((MOIS(visites2022[DATE])=MOIS(1&B$1))*(visites2022[SERVICE]=$A2))

Formule pour "Bilan par services 2022" (en Q2)

=NB.SI.ENS(visites2022[SERVICE];$A2;visites2022[EVALUATION];Q$1)

Formule pour "Heures 2022" (en D2)

=SOMMEPROD((visites2022[SERVICE]=$A2)*(MOIS(visites2022[DATE])=MOIS(1&D$1))*visites2022[TEMPS])

Nota : Comment sélectionner une colonne de tableau dans une formule :

Voir le fichier joint
Voilà ...
Bonne Nuit
Amicalement
Alain

PS les GIF c'est avec ScreenToGif un "gratuiciel"

 

[fanou06]

Merci, je vais tester, imprimer et apprendre les formules grâce à ton aide précieuse

 

[fanou06]

Par contre petite question, quand je glisse vers la droite la formule, les critères se décalent aussi. Y a t'il moyen de les verrouiller ? Merci.

 

[AtTheOne]

Bonne nuit
Post 22 :

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

J'avais déjà attiré ton attention sur ce point
Copier-Coller ou Coller les formules mais ne pas tirer vers la droite ...
A+
Alain

 

[fanou06]

Merci

 

[fanou06]

En fait j'ai trouvé le GROS soucis.
J'ai un tableau qui se trouve sur un autre serveur et à chaque ouverture il me demande d'ouvrir les liaisons, de mettre à jour et là ça plante.
Sinon en local ou sur mon serveur tout est ok