Moyenne par heure sur les données d'un mois

Bonjour à tous !

Je possède une liste d'appel reçu durant le mois de Juin. Les appels ont tous été reçu entre 9h et 18h et j'aimerais avoir une moyenne du nombre d'appel par heure et par numéro de téléphone de la colonne "phoneline" (il y a 5 numéros différents).

Par exemple, sur trois jours, si je trouve que tel numéro a été appelé 12 fois le lundi entre 9h et 10h, 13 fois le mardi entre 9h et 10h et 8 fois le mercredi entre 9h et 10h, j'aimerais avoir la moyenne du nombre d'appel sur ce numéro entre 9h et 10h, soit 11 appels en moyenne sur cette plage horaire pour ce numéro.

J'ai tenté de faire une macro pour d'abord avoir le nombre d'appel mais celle-ci ne semble pas fonctionner...

Option Explicit
Sub test()
Dim dico As Object, a, i As Long, h
    Set dico = CreateObject("Scripting.Dictionary")
    dico.CompareMode = 1
    a = Sheets(2).Range("a1").CurrentRegion.Value
        dico("heure") = VBA.Array("9h-10h", "10h-11h", "11h-12h", "12h-13h", "13h-14h", "14h-15h", "15h-16h", "16h-17h", "17h-18h")
        If Not dico.exists(a(i, 1)) Then
            dico(a(i, 1)) = VBA.Array(0, 0, 0, 0, 0, 0, 0, 0, 0)
        End If
        For i = 2 To UBound(a, 1)
            h = a(i, 7) \ 10000
            dico(a(i, 1))(h - 8) = dico(a(i, 1))(h - 8) + 1
        Next
    With Sheets(2).Range("k1")
        .CurrentRegion.Clear
        .Resize(dico.Count, 9).FormulaLocal = _
        Application.Transpose(Application.Transpose(dico.items))
    End With
End Sub

Je vous laisse les données sur lesquelles je travaille :

PS : l'heure est donnée par la colonne "time" sous la forme suivante : 17h45m33s donne 174533.

Merci d'avance !

Hello,

Jsuis pas un expert en Excel, mais pourquoi ne pas utiliser un TCD ?

J'ai essayé, il ne me permet pas d'obtenir ce que je veux. Ou alors je ne sais pas m'y prendre correctement.

Je débute également sur Excel.

C'est trèèèèèèès loin d'être estétique mais ça fera peut être l'affaire en attendant que certains trouvent mieux ..

Moyenne en ligne 52 de la feuille 2

En effet c'est une piste. Mais pour sélectionner la bonne plage horaire, tu as coché les heures à la main ?

Non !

Il y a le filtre élaboré, j'ai juste spécifié que pour les étiquettes je veux afficher les étiquettes uniquement entre 90000 (soit 9:00:00) et 100000 (10:00:00).

Il y a surement plus élégant cependant c'est tout ce que j'ai trouvé de plus simple à première vue.

En restant à ta dispo.

D'accord, je vois.

Merci Ergotamine, c'est une solution possible, même si ce n'est pas tout à fait ce à quoi je m'attendais.

Je m'attendais plus à une solution sous forme de macro qui aboutirait à un tableau regroupant toutes les informations que je cherchais.

Je suis certain que c'est possible, mais ça dépasse mes compétences, je laisse les pro prendre le relais

Aucune autre solution ou commentaire sur mon code défectueux ?

Bonjour

Par TCD avec 2 colonnes supplémentaires dans la source pour décoder le jour de la semaine et l'heure.

Il serait bien de mettre cette source sous forme de tableau et de baser le TCD sur le tableau : recopie automatique des formules et prise en compte auto des nouvelles lignes par le TCD...

Bonjour 78chris,

Merci de ton aide. Il y a juste un problème avec la formule que tu as mis dans Heure. elle ne donne pas la bonne heure. Peux-tu apporter une correction s'il-te-plait ?

Il a juste inversé les heures et les minutes ! Merci au passage Chris pour l'astuce du DROITE(GAUCHE()) et du TEMPS(), un enchaînement et une fonction que je ne connaissaient pas

=TEMPS(GAUCHE(G2;NBCAR(G2)-4);DROITE(GAUCHE(G2;NBCAR(G2)-2);2);DROITE(G2;2))

Bonne journée !

Merci 78chris et à toi aussi Ergotamine.

Est-il possible de mettre les moyennes voulues directement dans le TCD, un peu à la manière du total général mais avec un calcul personnalisé ? je pense que c'est quelque part dans le menu paramètre des champs de valeurs mais je ne vois pas le moyen de mettre un calcul personnel.

Edit : J'ai trouvé comment rajouter des valeurs qui suivent une formule personnalisée (Outils de tableau croisé dynamique -> Analyse -> Champs, éléments et jeux). Mais je n'arrive pas à récupérer les valeurs dans les colonnes du TCD.

Et est-il possible de réutiliser ce TCD avec un autre fichier Excel ? Par exemple en 1 clique, je voudrais faire réapparaître ce TCD avec les données d'un autre mois.

Bonjour

Je me suis souvenu avoir déjà fait cela pour une question similaire il y a quelques années.

J'avais résolu par PowerPivot qui est intégré à Excel 2016 mais selon ta version tu ne pourras par forcément accéder au modèle de données (versions pro plus seulement).

Si on y accède on peut ajouter les colonnes calculées dans PowerPivot, sinon il faut le faire dans la source comme déjà fait.

Ne sachant pas ta version précise, j'ai laissé dans la source en modifiant un poil les formules

"Par exemple, sur trois jours, si je trouve que tel numéro a été appelé 12 fois le lundi entre 9h et 10h, 13 fois le mardi entre 9h et 10h et 8 fois le mercredi entre 9h et 10h, j'aimerais avoir la moyenne du nombre d'appel sur ce numéro entre 9h et 10h, soit 11 appels en moyenne sur cette plage horaire pour ce numéro".

Il y a une ambiguïté dans ta demande : si tu prends 3 jours calendaires ton exemple est correct mais si tu prends 3 jours de la semaine sur toute la période, la moyenne telle que tu l'énonces serait une moyenne de moyennes donc fausse.

L'exemple ci-joint fait un vrai moyenne pondérée selon le nombre de lundi, mardi, etc de la période.

Fait avec 2010 mais 2016 devrait te demander si tu veux convertir et ensuite l'ouvrir.

Sinon je referai avec 2016.

N.B. j'ai laissé le TCD classique dans le fichier joint

Edit : Fichier joint mis à jour en version 2016

Bonjour 78chris,

C'est ce que je voulais, merci beaucoup !

Il semble effectivement avoir un problème de version. Lorsque j'essaie de manipuler le second tableau, un message m'indique :

Ce classeur comporte un modèle de données Power Pivot créé dans une version antérieur du complément Power Pivot. Vous devez mettre à niveau ce modèle de données avec Power Pivot pour Microsoft Excel 2016.

escsr a écrit :

J'ai essayé, il ne me permet pas d'obtenir ce que je veux. Ou alors je ne sais pas m'y prendre correctement.

Je débute également sur Excel.

Si tu ne connais pas les bases d'excel, pourquoi te lancer dans le VBA ?

P.

Bonjour patrick1957,

Simplement parce que je sais programmer, je pensais alors m'en sortir un peu mieux avec des macros en vba, mais ce n'est finalement pas super glorieux.

Bonjour

Il ne te propose pas la mise à jour ? Il faut accéder à l'onglet PowerPivot pour cela.

Tu as quelle version de 2016 exactement ?

J'ai fait la MAJ.

Je change le fichier joint au post précédent pour ne pas surcharger.

@78chris

Non, aucune mise à jour ne m'est proposée.

Je travaille sur Microsoft Office Professionnel Plus 2016.

Parfait, avec la dernière version, je peux manipuler les tableaux sans soucis.

Il ne me reste plus qu'à trouver le moyen d'exporter ce modèle sur d'autre fichier Excel pour pouvoir analyser les données des autres mois. Je pense que c'est possible en changeant la source des données, mais il faut alors que les nouvelles sources soient également dans le classeur. J'ai également vu sur internet qu'il était possible de sauvegarder le style du tableau (son apparence).

Je me permet donc de vous demander si vous avez une dernière idée pour exporter ou mémoriser ou sauvegarder ces formats de tableaux (je parle bien des formats des champs et des petites fenêtres permettant de choisir l'heure).

En vous remerciant sincèrement.

RE

Donc tu peux activer l'onglet PowerPivot https://support.office.com/fr-fr/article/D%C3%A9marrer-le-compl%C3%A9ment-Power-Pivot-dans-Microsoft-Excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8 pour avoir accès au modèle et à toutes les possibilités.

Tu as peut-être intérêt à faire un seule source avec requête liant tous les fichiers et mettre les calculs dans PowerPivot pour ne pas avoir à le faire dans tous les fichiers.

Soit requête par PowerPivot : exemple http://www.excel-formations.fr/Trucs_astuces/TCD02-PWP.php

Soit requête PowerQuery également intégré à Excel 2016. Dans 2016 les requêtes unions ont été facilitées par rapport à la version précédente.

Il suffira de filtrer la période dans le TCD.

Le TCD n'a pas de mise en forme particulière et les "petites fenêtres permettant de choisir l'heure" sont des segments... (onglet insertion en étant positionné dans le TCD

Tu pourras donc en avoir un pour le mois et un pour l'heure.