Microsoft 365 Prendre les 10 1ère valeur d'une plage en fonction de 2 critères

Merlin258413 · 12 Octobre 2023

Bonjour le Forum,

Je viens solliciter votre aide car j'ai une problématique que je n'arrive pas à régler.

Dans le fichier que je vous ai mis en PJ, je dois tous les mois après ma période de clôture indiquer les 10 premiers clients du mois pour chaque secteur.
Dans notre exemple, dans 'l'onglet FA, j'ai le secteur en cellule A10 = 1361 et le mois cellule A1.
Donc j'ai déjà 2 critères le mois et le secteur. Ici nous avons déjà une problématique sur le mois ca je mois que je dois utiliser dans l'onglet FA cellule A1 est sous la forme JJ/mm/aaaa et dans mon état (issu de notre ERP) la date est sous le format aaaamm.

Jusqu'à maintenant je réalisais un TCD (cf. l'onglet) issu de base qui se trouve dans l'onglet SUIVI EXPLOIT je sélectionnais mon mois et mon secteur. je trier ma colonne du mois du plus grand au plus petit; et je copiais en valeur dans l'onglet FA pour chaque secteur. cf. onglet FA ligne 20 à 30.

Ma question :

Est-il possible svp de rendre cette tâche automatique sans passer par un TCD et de travailler directement par l'onglet SUIVI EXPLOIT ?
C'est à dire qu'en fonction du mois indiqué en cellule A1 et du code secteur se trouvant en A10, A38, A62...
je trouve les 10 1er clients du mois.

Vous avez dans la plage E21 à F30 le résultat attendu pour le mois d'aout et le secteur 1361.
Le résultat doit apparaitre dans la plage A21:C30.

De plus il arrive parfois que dans les 10 1er client j'obtienne le nom (vide). ce qui signifie dans notre entreprise que c'est un client interne.
Donc est-il possible que si dans les 10 1er résultats j'ai le client (vide) alors je le remplace par clients internes.

J'espère avoir été assez clair dans mes explications et je vous remercie par avance pour votre aide.

job75 · 13 Octobre 2023

Vous vous êtes trompé, vous avez écrit :

VB:

For Each secteur In [A258,A286,A324,A353,A381]

alors qu'il faut :

VB:

For Each secteur In [A258,A286,A324,A352,A380]

Je rappelle que le code élimine les valeurs zéro.

Merlin258413 · 13 Octobre 2023

ALS35 à dit:
VB:

=LET(u;SUBSTITUE(UNIQUE(Base[[ Nom client]]);0;""); s;BYROW(u;LAMBDA(p;SOMME.SI.ENS(Base[Production];Base[[ Code secteur]];A10;Base[ [ Mois AAAAMM] ];CNUM(C$3&TEXTE(A$1;"mm"));Base[[ Nom client]];p))); SIERREUR(PRENDRE(TRIER(ASSEMB.H(SI(u="";"Clients internes";u);s);2;-1);MIN(10;NB(FILTRE(s;s>0))));""))

La fonction LET permet de nommer des plages ou des formules, ici u (pour unique) est la liste unique des clients, s (pour somme) est la somme pour chaque client correspondant aux critères demandés (Somme.Si.Ens)
p est le paramètre de la fonction Lambda qui s'applique à la fonction BYROW (par ligne) et représente le nom client concerné (pour chaque ligne de u).
Cordialement

Bonjour et merci pour cette découverte excellente journée et très bon week end

Merlin258413 · 13 Octobre 2023

sylvanu à dit:
Bonjour Merlin, Job, Als,
"Just for the fun" et avant que le VBA ne s'arrête ( ) un essai en PJ avec du VBA comme demandé.
La macro s'exécute automatiquement lorsqu'on change la date en FA A1.

Bonjour @sylvanu
Encore merci pour cette macro qui marche très bien sur le fichier joint quand je transpose cette macro sur mon fichier d'origine rien ne se passe. Pourtant j'ai le même nom de tableau la même structure. Le fichier est très volumineux 8.52 Mo
Je met ici le lien pour le partage de fichier; https://www.transfernow.net/dl/2023101333bmlEuE
Pouvez vous m'aider svp ?
Excellent week end et bonne journée

job75 · 13 Octobre 2023

Bonjour Merlin258413, le forum,

Une solution VBA très classique et rapide avec le Dictionary :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colmois, colsecteur, colclient, colproduction, mois&, secteur As Range, s, n, d As Object, i&, a, b
With Sheets("SUIVI EXPLOIT").ListObjects(1).Range 'tableau structuré
    colmois = .Columns(16) 'matrice, plus rapide
    colsecteur = .Columns(5)
    colclient = .Columns(13)
    colproduction = .Columns(54)
End With
mois = CLng(Format([A1], "yyyymm"))
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
For Each secteur In [A10,A38,A62]
    s = secteur
    Set d = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(colmois)
        If colmois(i, 1) = mois And colsecteur(i, 1) = s Then
            d(colclient(i, 1)) = d(colclient(i, 1)) + colproduction(i, 1)
            If d(colclient(i, 1)) = 0 Then d.Remove colclient(i, 1) 'élimine les valeurs zéro
        End If
    Next i
    secteur(12).Resize(10, 3) = "" 'RAZ
    If d.Count Then
        a = d.items
        b = d.keys
        tri a, b, 0, UBound(a) 'tri décroissant
        secteur(12, 3).Resize(IIf(UBound(a) < 10, UBound(a) + 1, 10)) = Application.Transpose(a)
        secteur(12, 2).Resize(IIf(UBound(b) < 10, UBound(b) + 1, 10)) = Application.Transpose(b)
        secteur(12, 2).Resize(IIf(UBound(b) < 10, UBound(b) + 1, 10)).Replace "", "Clients internes"
    End If
Next secteur
Application.EnableEvents = True 'réactive les évènements
End Sub

Sub tri(a, b, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) > ref: g = g + 1: Loop
    Do While ref > a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      temp = b(g): b(g) = b(d): b(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, b, g, droi)
If gauc < d Then Call tri(a, b, gauc, d)
End Sub

La macro se déclenche automatiquement quand on modifie ou valide une cellule quelconque.

A+

sylvanu · 13 Octobre 2023

Merlin258413 à dit:
Pourtant j'ai le même nom de tableau la même structure.

En êtes vous sûr ?
"1361" qui était en A10 est en A297, "1367" est passé de A38 à A325....
Il ne risque pas de trouvé les codes secteurs.
Je regarde aujourd'hui.
Testez la solution de Job, elle m'a l'air plus rapide.

Merlin258413 · 13 Octobre 2023

sylvanu à dit:
En êtes vous sûr ?
"1361" qui était en A10 est en A297, "1367" est passé de A38 à A325....
Il ne risque pas de trouvé les codes secteurs.
Je regarde aujourd'hui.
Testez la solution de Job, elle m'a l'air plus rapide.

D'accord merci bon week end

sylvanu · 13 Octobre 2023

Rectifié, voir en PJ.
En fait je recherche en col B "clients du mois" pour être sur de les repérer.
Cependant il faut respecter la même structure par bloc.
Pour le bloc 1381 il manque une ligne vide ( que j'ai mis en vert dans la PJ )
J'ai réduit la DB pour être directement livrable.
J'ai rajouté un message dans le statusbar pour suivre la mise à jour.

Merlin258413 · 13 Octobre 2023

sylvanu à dit:
Rectifié, voir en PJ.
En fait je recherche en col B "clients du mois" pour être sur de les repérer.
Cependant il faut respecter la même structure par bloc.
Pour le bloc 1381 il manque une ligne vide ( que j'ai mis en vert dans la PJ )
J'ai réduit la DB pour être directement livrable.
J'ai rajouté un message dans le statusbar pour suivre la mise à jour.

Merci

Merlin258413 · 13 Octobre 2023

@job75 ,@sylvanu ,@merinos ,@ALS35 , @Lolote83 et @danielco
je vous remercie pour votre aide très précieuse. Je vous souhaite une excellente journée et un bon Week end

job75 · 13 Octobre 2023

J'ai téléchargé votre fichier de 8 Mo et fait ces 2 modifications :

- inséré une ligne au-dessus de la cellule A535 (secteur 1381)

- le code pour la boucle des secteurs :

VB:

For Each secteur In [A297,A325,A349,A374,A400,A426,A450,A477,A503,A528]

La macro de mon post #33 s'exécute chez moi en 0,35 seconde.

Merlin258413 · 13 Octobre 2023

job75 à dit:
J'ai téléchargé votre fichier de 8 Mo et fait ces 2 modifications :

- inséré une ligne au-dessus de la cellule A535 (secteur 1381)

- le code pour la boucle des secteurs :

VB:

For Each secteur In [A297,A325,A349,A374,A400,A426,A450,A477,A503,A528]

La macro de mon post #33 s'exécute chez moi en 0,35 seconde.

Alors sur 6 agences testées sur 9 la macro fonctionne parfaitement bien
Par contre sur cette agence pour les secteurs

6940 : ligne 352
6960 : ligne 380

Les valeurs sont incomplètes comme vous pouvez le voir dans l'onglet TCD
Vu le volume de mon fichier voici le lien

0923 - DMIE - FA 2069 OE LYON.xlsm est disponible au téléchargement

Cliquez pour accéder au téléchargement de 0923 - DMIE - FA 2069 OE LYON.xlsm (815 Ko) avec TransferNow

www.transfernow.net

Encore merci

job75 · 13 Octobre 2023

Vous vous êtes trompé, vous avez écrit :

VB:

For Each secteur In [A258,A286,A324,A353,A381]

alors qu'il faut :

VB:

For Each secteur In [A258,A286,A324,A352,A380]

Je rappelle que le code élimine les valeurs zéro.

merinos-BernardEtang · 14 Octobre 2023

ALS35 à dit:
Bonjour à tous,
Un essai en B21, formule unique et dynamique pour Excel 365 :

VB:

=LET(u;SUBSTITUE(UNIQUE(Base[[ Nom client]]);0;""); s;BYROW(u;LAMBDA(p;SOMME.SI.ENS(Base[Production];Base[[ Code secteur]];A10;Base[ [ Mois AAAAMM] ];CNUM(C3&TEXTE(A1;"mm"));Base[[ Nom client]];p))); PRENDRE(TRIER(ASSEMB.H(SI(u="";"Clients internes";u);s);2;-1);10))

Et j'ai été obligé de supprimer les parenthèses du titre de la colonne Mois :
Mois (AAAAMM) devient Mois AAAAMM
Cordialement

@ALS35 ,

Je me suis bien amusé...

Sur une autre liste, je fait un TOP(3) des personnes.
MERCI, j'ai bien appris.

J'arrive a employer des colonnes du genre "qty( 4545)" ...

Je ne suis pas arrivé a ajoute une colonne qui compte le nombre de lignes pour une personne pour un mois.
J'obtiens un horrible "#N/A".

Merinos

ALS35 · 14 Octobre 2023

merinos à dit:
@ALS35 ,

Je me suis bien amusé...

Sur une autre liste, je fait un TOP(3) des personnes.
MERCI, j'ai bien appris.

J'arrive a employer des colonnes du genre "qty( 4545)" ...

Je ne suis pas arrivé a ajoute une colonne qui compte le nombre de lignes pour une personne pour un mois.
J'obtiens un horrible "#N/A".

Merinos

Bonjour @merinos ,
Ces fonctions matricielles dynamiques sont effectivement intéressantes et très puissantes.

Si j'ai bien compris ce que tu voulais faire, une proposition avec NB.SI.ENS

Code:

=LET(unqui; L_Qui();PRENDRE(TRIER(ASSEMB.H(unqui;SOMME.SI.ENS(TBL [ qty (4545)] ;TBL[qui];unqui;TBL[quoi];Select_Quoi;TBL[Month];L_Date(Select_Quand));NB.SI.ENS(TBL[qui];unqui;TBL[quoi];Select_Quoi;TBL[Month];L_Date(Select_Quand)));3;-1);3))

et une autre avec FILTRE et en passant par BYROW (sinon ça ne marche pas)

Code:

=LET(unqui; L_Qui();PRENDRE(TRIER(ASSEMB.H(unqui;SOMME.SI.ENS(TBL [ qty (4545)] ;TBL[qui];unqui;TBL[quoi];Select_Quoi;TBL[Month];L_Date(Select_Quand));BYROW(unqui;LAMBDA(p;NBVAL(FILTRE(TBL[qui];(TBL[quoi]=Select_Quoi)*(TBL[Month]=L_Date(Select_Quand))*(TBL[qui]=p))))));3;-1);3))

Moi j'ai toujours des pb avec la colonne qty (4545), je suis obligé de redéfinir la plage à chaque fois que je valide la formule, si je ne mets ni espace ni parenthèse, là c'est bon ???

Cordialement

merinos-BernardEtang · 14 Octobre 2023

ALS35 à dit:
Moi j'ai toujours des pb avec la colonne qty (4545), je suis obligé de redéfinir la plage à chaque fois que je valide la formule, si je ne mets ni espace ni parenthèse, là c'est bon ???

Cordialement

@ALS35 ,

Merci pour la formule... cela fonctionne... quand j'ai passé Excel en Français...

Et en Français, impossible d'employer "qty (4545)"

Bref une fois de plus a force de traduire plus cela ne marche plus.
Effectivement le BYROW est indispensable dans ce cas.

et j'en ai fait une LAMBDA...

A+

Microsoft 365 Prendre les 10 1ère valeur d'une plage en fonction de 2 critères

Merlin258413

XLDnaute Occasionnel

Pièces jointes

job75

Merlin258413

XLDnaute Occasionnel

Merlin258413

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Pièces jointes

sylvanu

XLDnaute Barbatruc

Merlin258413

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Pièces jointes

Merlin258413

XLDnaute Occasionnel

Merlin258413

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Merlin258413

XLDnaute Occasionnel

0923 - DMIE - FA 2069 OE LYON.xlsm est disponible au téléchargement

job75

XLDnaute Barbatruc

merinos-BernardEtang

XLDnaute Accro

Pièces jointes

ALS35

XLDnaute Impliqué

merinos-BernardEtang

XLDnaute Accro

Pièces jointes

Discussions similaires