Microsoft 365 Aide sur formule matricielle complexe

jip31 · 27 Octobre 2022

Bonjour
L'onglet "Recap" du fichier joint récupére automatiquement des données saisies dans Forms
Les colonnes J et N récupèrent des informations liées au type de panne et les colonnes I et M des informations liées à la population impactée
Je peux donc avoir un jour la population "Guich" en colonne M et la poulation "COB" en colonne I et inversement le lendemain
C'est la même chose pour le type de panne
Je voudrais donc récupérer dans l'onglet "Suivi" les données si les 2 conditions suivantes sont respectées
1) Je ne veux récupérer que les lignes correspondant à la population "Guich"
Donc si dans la colonne I ou dans la colonne M de l'onglet "Recap", je veux récupérer cette valeur dans la colonne E de l'onglet "Suivi"
2) Je ne veux récupérer que les lignes correspondant à la population "Guich" que si en colonne J ou N le type de
panne est "Applicatifs" A copier dans la colonne F de l'onglet "Suivi". S'il est réseau, je ne veux pas récupérer la ligne
Dernière chose, s'il y a bien un évènement correspondant à ces 2 conditions, je veux que le contenu des cellules E, F, G, et H de l'onglet "Recap" soit copiées dans les colonnes
A, B, C et D de l'onglet "Suivi"
NB: Quand je parle de ne pas récupérer les lignes, cela veut dire qu'elle ne doit pas s'afficher du tout (Je ne veux pas de filtre)
Merci

job75 · 1 Novembre 2022

Pour moi la solution la plus simple, à comprendre et à mettre en œuvre, est le VBA.

Voyez le fichier joint et cette macro dans le code de la feuille "Suivi" :

VB:

Private Sub Worksheet_Activate()
Dim tablo, resu(), i&, test1 As Boolean, test2 As Boolean, n&, j%
tablo = [Table1] 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 6)
For i = 1 To UBound(tablo)
    test1 = tablo(i, 9) = "Guich" And tablo(i, 10) Like "*Applicatifs*"
    test2 = tablo(i, 13) = "Guich" And tablo(i, 14) Like "*Applicatifs*"
    If test1 Or test2 Then
        n = n + 1
        For j = 1 To 4: resu(n, j) = tablo(i, j + 4): Next
        resu(n, 5) = "Guich"
        resu(n, 6) = IIf(test1, tablo(i, 10), "") & IIf(test2, tablo(i, 14), "") 'concaténation
    End If
Next
'---testitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then .Resize(n, 6) = resu
    .Offset(n).Resize(Rows.Count - n - .Row, 6).ClearContents 'RAZ en dessous
End With
Columns("A:F").AutoFit 'ajuste les largeurs
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Elle se déclenche quand on active la feuille.

Elle est aussi très rapide : 1,4 seconde chez moi avec un tableau source de 60 000 lignes.

@jip31 si vous n'arrivez pas à débloquer les macros, clic droit sur l'icône du fichier => Propriétés puis cochez la case "Débloquer".

@mapomme ta macro posera problème sur 60 000 lignes, revois l'effacement...

mapomme · 1 Novembre 2022

Bonjour @job75

jip31 à dit:
Comme on peut le voir dans l'onglet "Recap", il s'agit d'un cas de figure ou la colonne I est = "Guich" et la colonne M est aussi = "Guich"
Il est donc normal que 2 lignes soient créées dans l'onglet "Suivi"

Je n'avais pas compris la même chose que toi. Pour moi, chaque "Guich" faisait l'objet d'une ligne.

job75 à dit:
@mapomme ta macro posera problème sur 60 000 lignes, revois l'effacement...

Je m'oblige à travailler le plus possible avec la syntaxe des objet ListObjects et ListObject. On s'amuse comme on peut .

Je m'aperçois qu'entre l'enregistrement d'une macro et son écriture efficace, il y a quelquefois un écart. Quand on enregistre un effacement de ligne, la macro fait une boucle alors que DataBody.Delete est bien plus rapide que la boucle.

Bon j'ai revu l'effacement. Il est plus rapide et devrait fonctionner quelque soit le nombre de lignes.

Dans le fichier joint, on peut initialiser à 80 000 lignes à deux "Guich" pour chaque ligne.
Le résultat est donc un tableau structuré à 160 000 lignes de données. La durée d'exécution est d'environ 2,2s.

jip31 · 3 Novembre 2022

chris à dit:
Bonjour à tous

Ne t'embarque pas dans l'éditeur avancé : on n'en a que très rarement besoin et surtout pas pour débuter.
On se sert à 99 % du ruban et du clic droit.

Donne plutôt

un tableau de tous les cas à sélectionner (arbre de décision)

la réponse concernant les cas 3 à 5

On pourra ensuite t'expliquer comment faire avec PowerQuery afin que tu en comprennes la logique

Je m'absente ce matin mais les autres aficionados, que je salue, seront sans doute là avant mon retour

Bonjour Chris, le fil
Encore merci pour votre aide
Pour répondre à votre question, il va y avoir 4 types de population impactée (COB, Guich, Manager, LPAC)
J'ai donc adapté l'onglet "Recap" (colonne U à X) en conséquence mais en revanche je ne sais pas comment faire dans Power Query pour que Population3 et Panne3 et Poulation4 et Panne 4 soient prises en compte dans l'onglet "Suivi"
Concernant l'arbre de décision, je crois que j'ai compris comment faire
J'ai commencé à l'implémenter dans l'étape "Lignes filtrées" et çà correspond exactement à mes attentes
Par contre j'ai une question
Dans la colonne Type de panne, y'aurait t'il un moyen de n'afficher que la panne répondant à la condition?
Je m'explique
Si j'ai par exemple Poulation = Guich et Type de panne = Applicatifs bancaires;WIFI, est t'il possible de n'afficher que "Applicatifs bancaires" comme le stipule la condition?
Merci

chris · 3 Novembre 2022

Bonjour

J'ai fait un tableau des couples populations/panne à restituer et modifié la requête

Ce tableau peut évoluer pour prendre de nouveaux cas et la requête s'adaptera automatiquement

Elle prend en compte toutes les colonnes au delà de Code et donc Population à Populationn

Tu sembles compléter le tableau de résultats

Si c'est le cas, soit il faut

soit le déconnecter de la requête pour le stabiliser définitivement,
soit il faut passer par du self referencing qui permet de lier les données de Recap avec les ajouts mais il faut alors un ID unique.

Est bien le cas de ID ?

job75 · 3 Novembre 2022

Bonsoir tout le monde,

jip31 à dit:
Si j'ai par exemple Poulation = Guich et Type de panne = Applicatifs bancaires;WIFI, est t'il possible de n'afficher que "Applicatifs bancaires" comme le stipule la condition?

En VBA c'est facile en utilisant la fonction VBA Recup, voyez ce fichier (2) et le code :

VB:

Private Sub Worksheet_Activate()
Dim tablo, resu(), i&, test1 As Boolean, test2 As Boolean, n&, j%, x$, y$
tablo = [Table1] 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 6)
For i = 1 To UBound(tablo)
    test1 = tablo(i, 9) = "Guich" And tablo(i, 10) Like "*Applicatifs*"
    test2 = tablo(i, 13) = "Guich" And tablo(i, 14) Like "*Applicatifs*"
    If test1 Or test2 Then
        n = n + 1
        For j = 1 To 4: resu(n, j) = tablo(i, j + 4): Next
        resu(n, 5) = "Guich"
        If test1 Then x = Recup(tablo(i, 10)) Else x = ""
        If test2 Then y = Recup(tablo(i, 14)) Else y = ""
        resu(n, 6) = x & IIf(test1 And test2, ";", "") & y 'concaténation
    End If
Next
'---testitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then .Resize(n, 6) = resu
    .Offset(n).Resize(Rows.Count - n - .Row, 6).ClearContents 'RAZ en dessous
End With
Columns("A:F").AutoFit 'ajuste les largeurs
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Function Recup$(x)
Dim s, i%
s = Split(x, ";")
For i = 0 To UBound(s)
    If s(i) Like "*Applicatifs*" Then Recup = Recup & ";" & s(i) 'concaténation
Next
Recup = Mid(Recup, 2)
End Function

A+

jip31 · 4 Novembre 2022

job75 à dit:

Bonsoir tout le monde,

En VBA c'est facile en utilisant la fonction VBA Recup, voyez ce fichier (2) et le code :

VB:

Private Sub Worksheet_Activate()
Dim tablo, resu(), i&, test1 As Boolean, test2 As Boolean, n&, j%, x$, y$
tablo = [Table1] 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 6)
For i = 1 To UBound(tablo)
    test1 = tablo(i, 9) = "Guich" And tablo(i, 10) Like "*Applicatifs*"
    test2 = tablo(i, 13) = "Guich" And tablo(i, 14) Like "*Applicatifs*"
    If test1 Or test2 Then
        n = n + 1
        For j = 1 To 4: resu(n, j) = tablo(i, j + 4): Next
        resu(n, 5) = "Guich"
        If test1 Then x = Recup(tablo(i, 10)) Else x = ""
        If test2 Then y = Recup(tablo(i, 14)) Else y = ""
        resu(n, 6) = x & IIf(test1 And test2, ";", "") & y 'concaténation
    End If
Next
'---testitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then .Resize(n, 6) = resu
    .Offset(n).Resize(Rows.Count - n - .Row, 6).ClearContents 'RAZ en dessous
End With
Columns("A:F").AutoFit 'ajuste les largeurs
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Function Recup$(x)
Dim s, i%
s = Split(x, ";")
For i = 0 To UBound(s)
    If s(i) Like "*Applicatifs*" Then Recup = Recup & ";" & s(i) 'concaténation
Next
Recup = Mid(Recup, 2)
End Function

A+

merci job75
j ai envoyé ta solution à un collègue qui maitrise mieux le vba pour implementer la solution
apres je ne te cache pas que le travail de chris en power query correspond vraiment au besoin

jip31 · 4 Novembre 2022

chris à dit:
Bonjour

J'ai fait un tableau des couples populations/panne a restituer et modifié la requête

Le tableau peut évoluer pour prendre de nouveaux cas et la requête s'adaptera automatiquement

Elle prend en compte toutes les colonnes au delà de code et donc Population à Populationn

Tu sembles compléter le tableau de résultats

Si c'est le cas, soit il faut
soit le déconnecter de la requête pour le stabiliser définitivement,
soit il faut passer par du self referencing qui permet de lier les données de Recap avec les ajouts mais il faut alors un ID unique.

Est bien le cas de ID ?

Bonjour Chris
Merci pour ce magnifique travail qui correspond vraiment au besoin!
D'après ce que je comprend, même si demain il doit y avoir une population 6 et une panne 6, il n'y aura rien à modifier?
Pour ma culture perso, à quelle étape se fait ce changement car j'ai regardé la requête et je ne trouve pas?
Concernant la table T_Cas que tu as créé à partir de l'onglet REF, merci de me confirmer qu'il faudra juste que j'insère de nouvelles lignes pour prendre en compte de nouveaux cas vu que l'arbre de décision n'est pas finalisé?
Effectivement, j'ajoute des lignes dans l'onglet "Recap" afin de pouvoir tester un maximum de choses et je vous confirme aussi que l'ID de l'onglet "Recap" sera toujours unique (il s'incrémente automatiquement lorsque un nouveau questionnaire Forms nous est envoyé)
Encore mille merci et bonne journée!

chris · 4 Novembre 2022

Bonjour

La 4ème étape de la requête dépivote les colonnes au delà de Code et la 6ème enlève la numérotation afin de ne garder que Population impactée et Type de panne
Ces infos seront renumérotées par les 3 étapes suivantes
On pourrait simplifier si on avait Population impactée1 et Type de panne1 dans la source

On peut aussi gagner 3 étapes en les renommant dans la requête (ci-joint).

Concernant l'ajout de données aux résultat de la requête et au fait que la source évolue, tu n'a pas répondu clairement.

Si c'est le cas : si la numérotation croit linéairement il n'y aura pas de problème sinon il faut ajouter une requête pour lier ID et modifs en cas de numéro intermédiaire qui décalerai les anciennes lignes complétées

jip31 · 4 Novembre 2022

chris à dit:
Bonjour

La 4ème étape de la requête dépivote les colonnes au delà de Code et la 6ème enlève la numérotation afin de ne garder que Population impactée et Type de panne
Ces infos seront renumérotées par les 3 étapes suivantes
On pourrait simplifier si on avait Population impactée1 et Type de panne1 dans la source

On peut aussi gagner 3 étapes en les renommant dans la requête (ci-joint).

Concernant l'ajout de données aux résultat de la requête et au fait que la source évolue, tu n'a pas répondu clairement.

Si c'est le cas : si la numérotation croit linéairement il n'y aura pas de problème sinon il faut ajouter une requête pour lier ID et modifs en cas de numéro intermédiaire qui décalerai les anciennes lignes complétées

Bonjour et merci pour cette nouvelle version
oui l'ID coit linéairement avec un ncrément de +1 à chaque fois ;-)
Bonne soirée

jip31 · 23 Novembre 2022

Bonsoir Chris
Je me permets de vous contacter car j'aurais besoin d'une petite évolution sur ma requête Power Query et je n'arrive pas à l'implémenter
Jusqu'à maintenant, lorsqu'une nouvelle population était créée dans l'onglet "Recap Forms", les données étaient copiées dans "Population impactée", "Type de panne" puis "Population impactée2", "Type de panne2" et ainsi de suite
Désormais j'aurais besoin que le type de panne soit mis à jour dans l'onglet Suivi en fonction des conditions suivantes dans l'onglet "Recap Forms"
Par exemple, si dans l'onglet "Recap Forms", population impactée (population 1 a 10) = Guichet, alors le type de panne à afficher dans Suivi est = à "Type de panne"
Par exemple, si dans l'onglet "Recap Forms", population impactée (population 1 a 10) = Smarteo BP, alors le type de panne à afficher dans Suivi est = à "Type de panne2"
Par exemple, si dans l'onglet "Recap Forms", population impactée (population 1 a 10) = COBA PC, alors le type de panne à afficher dans Suivi est = à "Type de panne3"
J'ai mentionnées ces conditions dans l'onglet REF
Du coup, vu qu'aujourd'hui cela n'est pas fonctionnel, lorsque j'actualise les données dans l'onglet Suivi, j'ai le message d'erreur suivant

car comme vous pouvez le voir sur la ligne 4 de l'onglet "Recap Forms", j'ai une population renseignée dans "Population impactée" mais rien dans Type de panne ce qui est normal par rapport à mes nouveaux besoins
Du coup, le type de panne étant renseigné dans Type de panne2 au lieu de Type de panne, les données ne s'actualisent plus
Y'aurait t'il une solution pour résoudre mon problème?
Merci d'avance

Microsoft 365 Aide sur formule matricielle complexe

jip31

Guest

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

jip31

Guest

Pièces jointes

chris

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

jip31

Guest

jip31

Guest

chris

XLDnaute Barbatruc

Pièces jointes

jip31

Guest

jip31

Guest

Pièces jointes

Discussions similaires