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.

 

[JHA]

Bonjour à tous,

Un essai avec sommeprod

JHA

 

[Hasco]

Bonjour,

Un début par power query. Pas le temps ce soir de finaliser.

Dans votre grand tableau, un colonne par mois mais on ne sait pas s'il faut mettre le nombre de dates ou le temps.

Cordialement

 

[chris]

Bonjour à tous

J'avais commencé PowerQuery avant diner et repris après alors je poste

Requête + TCD

Mais j'aime beaucoup la solution d'Hasco dont je vais décortiquer le
Colonne en tables
que je découvre

 

[fanou06]

Merci, mais pas de powerquery ni de macro SVP.
Car ce tableau sera partagé sur divers serveurs et certains plantent avec Power Query

 

[chris]

RE

Attention il y a 5 dates en janvier 2021 et 1 en octobre 2021 non prises en compte dans le stats

 

[fanou06]

Merci Je vais regarder de prês les formules.

 

[chris]

Merci Je vais regarder de prês les formules.

A noter aussi que j'ai rajouté les accents manquants sur certains mois en titre du tableau de stats

 

[merinos]

Salut le fofo...

juste pour le fun, car préparé avant de savoir que PQ ne peut-être une solution (j'ai été manger) :

des mesures de temps avec un affichage reconstruit:

a+


Merinos

 

[merinos]

Salut @Hasco,


j'adore ton code

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Colonne en tables" =
        List.Transform(List.Split(List.RemoveRange(Table.ColumnNames(Source),0,2),4), each
            Table.Skip(
                Table.DemoteHeaders(
                    Table.SelectColumns(Source,{"Franchises"} & _)
                ),
            1)           
            ),

Je vais devoir étudier cela. Cela sort d'où?
Il y a une explication?

Trop beau.


Merinos

 

[Hasco]

Bonsoir,
@chris, @merinos
Ce code vient de ma caboche.
Merci pour vos gentils commentaires

Table.ColumNames(Source) nous donne 82 noms de colonnes dont veut exclure les 2 premiers par List.RemoveRange.

Cette nouvelle liste est éclatée en sous listes de 4 noms par List.Split(...,4)
Ces sous-listes de 4 noms servent à sélectionner les colonnes par groupe de 4 et en faire une liste de sous-tables auxquelles on adjoint la colonne 'franchises'

Les en-têtes sont dépromues puis leur ligne sautée par Table.Skip(...,1)

Hi...hi..Hi... et j'ai zappé le renomage des colonnes pour ne pas noyer la chose en pensant que parfois le mieux est l'ennemi du bien.

Je m'en suis fait une fonction.

Dites moi si ces explications vous parlent et quels points je devrais développer.

Cordialement

 

[merinos]

merci.

 

[chris]

RE

Dites moi si ces explications vous parlent et quels points je devrais développer

J'ai à peu près décodé sauf le fait qu'on retrouve à la fin le numéro de franchise

 

[AtTheOne]

Bonsoir à tous, Bonsoir @fanou06
Sans VBA ni PowerQuery
En remplaçant tes titres des colonnes de Stat par des dates avec un format qui affiche le mois en littéral cette formule fait le travail (ici pour la cellule B3) :

=SOMMEPROD((MOIS(INDEX(visites!$C$2:$CD$33;$A3; ))=MOIS(B$2))*
(ANNEE(INDEX(visites!$C$2:$CD$33;$A3; ))=ANNEE(B$2)))

Voir le fichier en PJ

Amicalement
Alain

 

[AtTheOne]

Re-bonsoir,

Si ce sont les temps que tu veux, la formule devient :
=SOMMEPROD(INDEX(visites!$F$2:$CD$33;$A3; )*
(visites!$F$1:$CD$1="Temps")*
(MOIS(INDEX(visites!$C$2:$CA$33;$A3; ))=MOIS(B$2))*
(ANNEE(INDEX(visites!$C$2:$CA$33;$A3; ))=ANNEE(B$2)))

Pour le nombre total de visites en 2021 :
=SOMMEPROD(--(ANNEE(visites!$C$2:$CD$33)=2021))

Pour le nombre total de visites en 2022 :
=SOMMEPROD(--(ANNEE(visites!$C$2:$CD$33)=2022))

Amicalement
Alain