Microsoft 365 recherche multicritère de la valeur suivante

[Auré_keolis]

Bonjour à tous,

Merci pour votre accueil
Je me présente, Aurélie, 42 ans, région Bretagne.
Je travaille dans un centre d'appel pour les transports de personnes à mobilité réduite.

Si un client appel et que nous sommes tous en ligne, l'appel n'abouti pas. Or, dans le cadre de la démarche NF, nous devons retrouver pour chaque appel non abouti à quelle date et heure le client a rappeler. Et là, impossible de trouver de créer une matrice fonctionnelle.

Comme un client peut appeler plusieurs fois dans le mois voir plusieurs fois par jour, une simple recherchev n'est pas possible.
Je cherche donc a créer une matrice qui me permettrai, pour chaque appel non abouti, de retrouver la date et heure de l'appel suivant du client.

Ce sont de très gros fichiers (+ 8000 appels entrants / mois) donc je vous joint juste un extrait :

Le premier fichier comprend les numéro de tel, date et heure des appels non aboutis et le deuxième les numéro de tel, date et heure des appels entrants décrochés

Fichier 1 :
Colonne A : date / B heure / C numero

Fichier 2
Colonne A :
Colonne A : date / B heure / C numero

Je suis preneuse de toute vos idées. (actuellement la recherche manuelle me prend 4h par mois).
S'il faut modifier les formats, ajouter des colonnes, concaténer des données aucun problème

Un GRAND Merci d'avance à ceux qui prendront le temps de me répondre
Auré

 

[Phil69970]

Bonjour @Auré_keolis

Je cherche donc a créer une matrice qui me permettrai, pour chaque appel non abouti, de retrouver la date et heure de l'appel suivant du client.

Quelque soit la date de l'appel suivant ???
Exemple :
Pour le N° 06 12 34 56 78 ==> 01/01/2024 à 9h00 non décroché
Pour le N° 06 12 34 56 78 ==> 20/03/2024 à 10h00 décroché
soit plus de 2 mois et demi entre les 2 dates
Ou tu veux ne pas dépasser un délai entre 2 dates ? Et quel délai ??

 

[sylvanu]

Bonsoir Auré_keolis,
Ci joint un essai. serait ce suffisant ?
Dans "Appels non aboutis " on créé une colonne en mettant un "X" si le N° n'a pas été rappelé, et ce pour la dernière tentative d'appel du client. Avec :

=SI(ET(NB.SI('Appels entrants décrochés'!C:C;'Appels non aboutis '!C2)=0;NB.SI($C$1:C1;C2)>0);"X";"")

Le filtre en colonne D permet de voir tous les N° à rappeler.
Sinon précisez votre besoin.

 

[job75]

Bonsoir à tous,

J'avais mis cette solution sur l'autre fil en doublon...

Il vaut mieux avoir toutes les données dans un seul fichier, ce sera plus rapide.

Voyez le fichier .xlsm joint avec des tableaux structurés et cette fonction VBA :

Function TrouveRappel(numero#, dat&, heure#, tableau As Range)
Dim delai#, tablo, ub&, i&, j&
TrouveRappel = ""
delai = TimeValue("0:0:2") '2 secondes, modifiable
tablo = tableau 'matrice, plus rapide
ub = UBound(tablo)
For i = 1 To ub
    If tablo(i, 1) = numero Then
        If Abs(tablo(i, 2) + tablo(i, 3) - dat - heure) < delai Then
            For j = i + 1 To ub
                If tablo(j, 1) = numero Then TrouveRappel = tablo(j, 2) + tablo(j, 3): Exit Function
            Next j
        End If
    End If
Next i
End Function

Le code est placé impérativement dans un module standard.

Formule en D2 de la feuille "En attente rappel" :

=TrouveRappel(A2;B2;C2;Tableau2)

A+

 

[job75]

J'ai oublié de préciser que le tableau de la feuille "Appels" doit être trié sur les dates puis sur les heures en ordres croissants, mais c'est assez évident.

 

[job75]

Bonjour le forum,

Bien entendu il faut limiter la taille des tableaux.

Avec 8000 appels par mois on peut convenir par exemple de ne conserver que les 10 000 derniers.

Par ailleurs dans la feuille "En attente rappel" une fois que le rappel est trouvé il est inutile de conserver la formule, il faut la remplacer par sa valeur pour éviter son recalcul.

On placera donc cette macro dans le code de la feuille "Appels" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxlignes&, i&
maxlignes = 10000
Application.EnableEvents = False 'désactive les évènements
With [Tableau1]
    For i = 1 To .Rows.Count
        If IsDate(.Cells(i, 4)) Then .Cells(i, 4) = .Cells(i, 4).Value 'supprime la formule
    Next i
    If .Rows.Count > maxlignes Then .Rows(1).Resize(.Rows.Count - maxlignes).Delete xlUp
End With
With [Tableau2]
    If .Rows.Count > maxlignes Then .Rows(1).Resize(.Rows.Count - maxlignes).Delete xlUp
End With
Application.EnableEvents = True ' 'réactive les évènements
End Sub

Avec 10 000 appels et 100 formules en attente la macro s'exécute chez moi en 0,70 seconde.

Il faudra sans doute songer à limiter aussi le nombre de formules en attente en supprimant les plus anciennes, pour cela merci de répondre à ces 2 questions :

- quel est le nombre d'appels entrés en attente chaque mois

- là dessus combien restent sans rappel chaque mois en moyenne.

-A+

 

[Auré_keolis]

Bonjour,
Merci à vous pour ces premiers retours.
Je m'empresse d'aller tester et je reviens vers vous au plus vite

 

[Auré_keolis]

Bonjour @Auré_keolis



Quelque soit la date de l'appel suivant ???
Exemple :
Pour le N° 06 12 34 56 78 ==> 01/01/2024 à 9h00 non décroché
Pour le N° 06 12 34 56 78 ==> 20/03/2024 à 10h00 décroché
soit plus de 2 mois et demi entre les 2 dates
Ou tu veux ne pas dépasser un délai entre 2 dates ? Et quel délai ??

Bonjour Phil69970

Merci pour ce premier retour.
Désolé j'aurai du être un peu plus explicite.
L'objectif principal est de savoir si le client a rappelé le jour même (ou le lendemain pour les we et jours fériés - nous sommes ouvert 365 jours par an).
Si c'et le cas, l'appel sera considéré CONFORME par NF.
Une fois les données traitées, je pourrais remplacer les formules par leurs valeurs pour alléger le fichier et créer un tableau recap mois par mois indiquant le nombre d'appel non abouti CONFORME et le nombre NON CONFORME. (objectif NF 85 % de CONFORMITE)

J'espère avoir été plus clair
Encore merci
Auré

 

[Auré_keolis]

Bonjour le forum,

Bien entendu il faut limiter la taille des tableaux.

Avec 8000 appels par mois on peut convenir par exemple de ne conserver que les 10 000 derniers.

Par ailleurs dans la feuille "En attente rappel" une fois que le rappel est trouvé il est inutile de conserver la formule, il faut la remplacer par sa valeur pour éviter son recalcul.

On placera donc cette macro dans le code de la feuille "Appels" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxlignes&, i&
maxlignes = 10000
Application.EnableEvents = False 'désactive les évènements
With [Tableau1]
    For i = 1 To .Rows.Count
        If IsDate(.Cells(i, 4)) Then .Cells(i, 4) = .Cells(i, 4).Value 'supprime la formule
    Next i
    If .Rows.Count > maxlignes Then .Rows(1).Resize(.Rows.Count - maxlignes).Delete xlUp
End With
With [Tableau2]
    If .Rows.Count > maxlignes Then .Rows(1).Resize(.Rows.Count - maxlignes).Delete xlUp
End With
Application.EnableEvents = True ' 'réactive les évènements
End Sub

Avec 10 000 appels et 100 formules en attente la macro s'exécute chez moi en 0,70 seconde.

Il faudra sans doute songer à limiter aussi le nombre de formules en attente en supprimant les plus anciennes, pour cela merci de répondre à ces 2 questions :

- quel est le nombre d'appels entrés en attente chaque mois

- là dessus combien restent sans rappel chaque mois en moyenne.

-A+

Bonjour Job 75 et merci pour ces premiers retours.
Quand je travaille sur des fichiers volumineux, j'ai pris l'habitude d'ajouter les données chaque mois et une fois traité, de remplacer par les valeurs pour alléger le fichier.

Du coup j'ai ouvert le fichier proposé et c'est tout à fait çà.
Une fois qu'Excel me donnera la date et heure de rappel, je n'aurais plus qu'à ajouter une formule.

En moyenne, j'ai 500 appels non abouti chaque mois (min 300 max 800) et seulement une 20taine qui ne sont pas rappeler. Mais pour obtenir ce résultat, actuellement je passe 4h par mois à rechercher chaque numéro de téléphone (c'est non seulement long mais aussi totalement inintéressant en plus comme tache !)

Si rappel le jour même (ou le lendemain pour les we et jours férié) = CONFORME pour NF
Je n'aurais plus qu'à faire un tableau recap mois par mois pour indiquer le nombre d'appel CONFORME et NON CONFORME (objectif NF 85 % de conformité).

Après pour être honnête, si je me débrouille pas trop mal avec les formules, je suis novice en ce qui concerne les macro. Je viens d'ajouter le ruban développeur sur Excel et je commence à regarder comment çà fonctionne.

 

[job75]

Merci pour ces éclaircissements.

Pour tenir compte des week-ends et jours fériés on peut donc noter "Non conforme" un appel en attente et sans rappel pendant 4 jours, donc utilisez cette macro :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxlignes&, i&
maxlignes = 10000
Application.EnableEvents = False 'désactive les évènements
With [Tableau1]
    For i = 1 To .Rows.Count
        If IsDate(.Cells(i, 4)) Then
            .Cells(i, 4) = .Cells(i, 4).Value 'supprime la formule
        ElseIf .Cells(i, 4) = "" Then
            If Date > .Cells(i, 2) + 4 Then .Cells(i, 4) = "Non conforme"
        End If
    Next i
    If .Rows.Count > maxlignes Then .Rows(1).Resize(.Rows.Count - maxlignes).Delete xlUp
End With
With [Tableau2]
    If .Rows.Count > maxlignes Then .Rows(1).Resize(.Rows.Count - maxlignes).Delete xlUp
End With
Application.EnableEvents = True ' 'réactive les évènements
End Sub

Les formules ayant une durée de vie maximum de 4 jours il devrait y en avoir nettement moins de 100 dans la 1ère feuille et la durée d'exécution de la macro ne dépassera pas 0,70 seconde.

 

[ALS35]

Bonjour à tous,
Une alternative formule 365, pour obtenir l'appel suivant, j'ai transformé les deux tableaux en tableaux structurés :

=LET(t;ASSEMB.H(tDécrochés[Date]+tDécrochés[Heure];--tDécrochés[Appels entrants décrochés]);SIERREUR(PRENDRE(FILTRE(t;(PRENDRE(t;;-1)=--[@[Appels non aboutis]])*(PRENDRE(t;;1)>[@Date]+[@Heure]));1;1);""))

Cordialement

 

[job75]

En fait on gagnera beaucoup de temps en faisant commencer l'analyse de la feuille "Appels" par le bas et en remontant :

Function TrouveRappel(numero#, dat&, heure#, tableau As Range)
Dim delai#, tablo, ub&, i&, j&
TrouveRappel = ""
delai = TimeValue("0:0:2") '2 secondes, modifiable
tablo = tableau 'matrice, plus rapide
ub = UBound(tablo)
For i = ub To 1 Step -1
    If tablo(i, 1) = numero Then
        If Abs(tablo(i, 2) + tablo(i, 3) - dat - heure) < delai Then
            For j = i + 1 To ub
                If tablo(j, 1) = numero Then TrouveRappel = tablo(j, 2) + tablo(j, 3): Exit Function
            Next j
        End If
    End If
Next i
End Function

Et dans la 1ère feuille on limitera Tableau1 à 1000 lignes :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxlignes1&, maxlignes2&, i&
maxlignes1 = 1000
maxlignes2 = 10000
Application.EnableEvents = False 'désactive les évènements
With [Tableau1]
    For i = 1 To .Rows.Count
        If IsDate(.Cells(i, 4)) Then
            .Cells(i, 4) = .Cells(i, 4).Value 'supprime la formule
        ElseIf .Cells(i, 4) = "" Then
            If Date > .Cells(i, 2) + 4 Then .Cells(i, 4) = "Non conforme"
        End If
    Next i
    If .Rows.Count > maxlignes1 Then .Rows(1).Resize(.Rows.Count - maxlignes1).Delete xlUp
End With
With [Tableau2]
    If .Rows.Count > maxlignes2 Then .Rows(1).Resize(.Rows.Count - maxlignes2).Delete xlUp
End With
Application.EnableEvents = True ' 'réactive les évènements
End Sub

 

[job75]

Encore une chose, vous avez dit :

Une fois qu'Excel me donnera la date et heure de rappel, je n'aurais plus qu'à ajouter une formule.

Avec ma solution la formule est créée automatiquement dès qu'on entre une nouvelle donnée sous Tableau1 : c'est un des avantages des tableaux structurés.

 

[ALS35]

Bonjour à tous de nouveau,
Si tu peux avoir la date et l'heure dans une même cellule, on peut arriver à quelque chose d'assez simple par formule pour le rappel.
Cordialement

 

[alexga78]

Bonsoir à tous,

Un essai avec Power Query, optimisable avec plus de données et sous reserve d'avoir bien apprehendé la demande.

Bonne soirée à tous.