Remplacement par groupe de lignes

Le 25/03/2020 à 10:44

Bonjour à tous,

Je voudrais une aide concernant la fonction Rechercher-Remplacer par groupe de lignes (réseaux d'individus IND cités par l'interviewé INT). Je joins un petit échantillon pour une meilleure compréhension (voir fichier: test AD).

Pour chaque INT (1 à 4), on doit remplacer les valeurs n par les identifiants correspondants (CIT). Cela donne par exemple pour INT=1 => n(IND)=5. Et on doit remplacer les valeurs n (1 à 5) dans les 4 colonnes HAB, SOUT, INF, CONF par les identifiants.

Le fichier en attaché est assez explicite. Le problème est que je dois le faire sur des centaines de IND.

Si je dois appliquer une formule, merci de me préciser où je dois l'écrire exactement car je ne suis pas à l'évidence un utilisateur avancé.

Merci d'avance de votre réponse.

11test-ad.xlsx (10.52 Ko)
Le 25/03/2020 à 18:07

Bonjour et bienvenue sur le forum

Je suis désolé mais je ne vois pas comment tu fais pour passer d'un tableau à l'autre.

Tu devrais détailler davantage la manip....

Bye !

Le 25/03/2020 à 20:01

Bonjour à tous

Une solution PowerQuery probablement améliorable, mais qui fonctionne

3test-ad2-pq.xlsx (27.55 Ko)
Le 26/03/2020 à 08:31
gmb a écrit :

Bonjour et bienvenue sur le forum

Je suis désolé mais je ne vois pas comment tu fais pour passer d'un tableau à l'autre.

Tu devrais détailler davantage la manip....

Bye !

Bonjour GMB,

Merci pour le mot de bienvenue.

Ces données décrivent les réseaux sociaux pour des personnes interviewées (egos) qui sont invitées à:

1) citer un nombre n de leurs réseaux (1.a. dénombré ou listé dans la colonne IND; 1.b. identifie le lien social avec ego dans la colonne CIT);

2) décrire les relations entre tous les membres cités, égo compris (les 4 dernières colonnes décrivent les relations entre chaque membre cité et les autres cités par égo).

Dans ces colonnes en question (HAB, SOUT, INF, CONF), les membres sont identifiés avec les valeurs n dans IND. La manipulation consiste donc à remplacer chaque valeur n par le lien social codé dans la colonne CIT. Avec la condition que les valeurs n dans IND correspondent aux liens dans CIT que pour chaque interviewée INT. Cette condition renvoie à des loops...du genre:

SI INT=1, alors 1 devient ego, 2 devient F, 3 devient C, etc…

SI INT=2, alors 1 devient ego, 2 devient C,

SI INT=3, alors 1 devient ego, 2 devient C, 3 devient S, etc…

Pour être plus clair, j'ai mis dans le fichier CE QUE J'AI comparativement à CE QUE JE VOUDRAIS AVOIR.

Merci encore.

Le 26/03/2020 à 10:38
78chris a écrit :

Bonjour à tous

Une solution PowerQuery probablement améliorable, mais qui fonctionne

Merci 78chris,

Cela fonctionne visiblement puisque c'est bien le résultat recherché.

Par contre, je ne vois pas la démarche avec PowerQuery. Comment avez-vous procédé exactement?

AD

Le 26/03/2020 à 11:43

Bonjour

Il y a trois requêtes que tu peux voir : Données, Obtenir des données, Lancer l'éditeur PowerQuery

J'ai commencé par mettre la source sous forme de tableau nommé Data puis

depuis une cellule de ce tableau, Données, A partir d'un tableau : ce qui ouvre PowerQuery

  • PowerQuery type automatiquement les données : j'ai supprimé cette action de la liste des actions à droite
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • sélection des colonnes INT, IND, CIT, Index, clic droit, Dépivoter les autres colonnes
  • Ajouter une colonne, Colonne personnalisée : nom OldV, formule :
    =Text.From([INT])&"-"&Text.Replace([Valeur],",",","&Text.From([INT])&"-")
  • sélection de la colonne OldV, Accueil, Type de données, Text
  • Requête Ref : Accueil, Nouvelle source, Autre source, Requête vide
  • dans la barre de formule, taper
    = Excel.CurrentWorkbook(){[Name="Data"]}[Content]
  • sélection des colonnes INT, CIT, clic droit, Supprimer les autres colonnes
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • renommer l'étape Etat1 et la requête Ref
  • selection de la colonne INT, Transformer, Regrouper par : opération Compter les lignes
  • Ajouter une colonne, Colonne personnalisée : nom IDx, formule :
    =List.Numbers([Nombre],[Nombre],-1)
  • clic près de la double flèche près du titre de la colonne IDx, Développer sur de nouvelles lignes
  • trier par INT et IDx
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • Accueil, Fusionner des requêtes, choisir REF en seconde requête, sélectionner la colonne Index dans chaque requête, jointure Interne
  • dans la barre de formule, remplacer la seconde occurrence #"Index ajouté" par Etat1 et "Index ajouté" aussi par "Etat1"
  • clic près de la double flèche près du titre de la colonne Etat1, décocher tout sauf CIT
  • supprimer les colonnes Index et Nombre
  • Ajouter une colonne, Colonne personnalisée : nom Old, formule :
    =Text.From([INT])&"-"&Text.From([IDx])
  • Ajouter une colonne, Colonne personnalisée : nom New, formule :
    =Text.From([INT])&"-"&[CIT]
  • sélection des colonne Old et New, Accueil, Type de données, Text
  • Requête Final : Accueil, Nouvelle source, Autre source, Requête vide
  • dans la barre de formule, taper
    = Ref
    et renommer la requête en Final
  • Ajouter une colonne, Colonne personnalisée : nom Liste, formule :
    =({[Old], [New]})
  • renommer cette étape CreateListOfLists
  • cliquer sur le Fx à gauche de la barre de formule et taper = Data
  • Ajouter une colonne, Colonne personnalisée : nom Custom, formule :
    =Text.Split([OldV], ",")
  • Ajouter une colonne, Colonne personnalisée : nom NewV, formule :
    =Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),",")
  • Ajouter une colonne, Colonne personnalisée : nom Résultat, formule :
    =Text.Replace([NewV],Text.From([INT])&"-","")
  • supprimer les colonnes OldV, Custom, NewV, Valeur
  • sélectionner la colonne Attribut, Transformer, Pivoter : colonne de valeurs : Résultat, ne pas agréger
  • sortir par Fermer et charger dans, connexion seulement
  • Afficher le volet des requêtes : Données, requêtes et connexions, clic droit sur Final, Charger dans, Table : choisir l'emplacement

On pourrait faire en deux requêtes mais j'ai préféré garder Ref dans un état intermédiaire pour contrôler.

Le 26/03/2020 à 16:30
78chris a écrit :

Bonjour

Il y a trois requêtes que tu peux voir : Données, Obtenir des données, Lancer l'éditeur PowerQuery

J'ai commencé par mettre la source sous forme de tableau nommé Data puis

depuis une cellule de ce tableau, Données, A partir d'un tableau : ce qui ouvre PowerQuery

  • PowerQuery type automatiquement les données : j'ai supprimé cette action de la liste des actions à droite
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • sélection des colonnes INT, IND, CIT, Index, clic droit, Dépivoter les autres colonnes
  • Ajouter une colonne, Colonne personnalisée : nom OldV, formule :
    =Text.From([INT])&"-"&Text.Replace([Valeur],",",","&Text.From([INT])&"-")
  • sélection de la colonne OldV, Accueil, Type de données, Text
  • Requête Ref : Accueil, Nouvelle source, Autre source, Requête vide
  • dans la barre de formule, taper
    = Excel.CurrentWorkbook(){[Name="Data"]}[Content]
  • sélection des colonnes INT, CIT, clic droit, Supprimer les autres colonnes
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • renommer l'étape Etat1 et la requête Ref
  • selection de la colonne INT, Transformer, Regrouper par : opération Compter les lignes
  • Ajouter une colonne, Colonne personnalisée : nom IDx, formule :
    =List.Numbers([Nombre],[Nombre],-1)
  • clic près de la double flèche près du titre de la colonne IDx, Développer sur de nouvelles lignes
  • trier par INT et IDx
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • Accueil, Fusionner des requêtes, choir REF en seconde requête, sélectionner la colonne Index dans chaque requête, jointure Interne
    J'ai des soucis à partir d'ici. Avec ce message d'erreur:
    " Expression.Error : Désolé... Nous n'avons pas pu convertir une valeur de type Table en type Text.
    Détails :
    Value=
    Type=[Type] "
    [*]dans la barre de formule, remplacer la seconde occurrence #"Index ajouté" par Etat1 et "Index ajouté" aussi par Etat1
    [*]clic près de la double flèche près du titre de la colonne Etat1, décocher tout sauf CIT
    [*]supprimer les colonnes Index et Nombre
    [*]Ajouter une colonne, Colonne personnalisée : nom Old, formule :
    =Text.From([INT])&"-"&Text.From([IDx])
    [*]Ajouter une colonne, Colonne personnalisée : nom New, formule :
    =Text.From([INT])&"-"&[CIT]
    [*]sélection des colonne Old et New, Accueil, Type de données, Text
    [*]Requête Final : Accueil, Nouvelle source, Autre source, Requête vide
    [*]dans la barre de formule, taper
    = Ref
    et renommer la requête en Final
    [*]Ajouter une colonne, Colonne personnalisée : nom Liste, formule :
    =({[Old], [New]})
    [*]cliquer sur le Fx à gauche de la barre de formule et taper = Data
    [*]Ajouter une colonne, Colonne personnalisée : nom Custom, formule :
    =Text.Split([OldV], ",")
    [*]Ajouter une colonne, Colonne personnalisée : nom NewV, formule :
    =Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),",")
    [*]Ajouter une colonne, Colonne personnalisée : nom Résultat, formule :
    =Text.Replace([NewV],Text.From([INT])&"-","")
    [*]supprimer les colonnes OldV, Custom, NewV, Valeur
    [*]sélectionner la colonne Attribut, Transformer, Pivoter : colonne de valeurs : Résultat, ne pas agréger
    [*]sortir par Fermer et charger dans, connexion seulement
    [*]Afficher le volet des requêtes : Données, requêtes et connexions, clic droit sur Final, Charger dans, Table : choisir l'emplacement

On pourrait faire en deux requêtes mais j'ai préféré garder Ref dans un état intermédiaire pour contrôler.

Bonsoir 78CHRIS,

merci d'avoir pris de votre temps pour détailler la procédure. Je suis allé jusqu'à mi-chemin et j'ai le même blocage après plusieurs essais, précisément après la fusion interne des 2 requêtes REF.

Avec ce message d'erreur:

" Expression.Error : Désolé... Nous n'avons pas pu convertir une valeur de type Table en type Text.

Détails :

Value=

Type=[Type] "

Merci encore.

Le 26/03/2020 à 19:45

RE

Vérifie si tu as bien corrigé au bon endroit à l'étape suivante

Dans le 1er cas on n'as pas de "" autour de Etat1 dans l'autre on en a (j'ai corrigé mon explication sur cette ligne pour que ce soit plus clair)

Essaie de lancer 2 sessions Excel et vérifie au fur et à mesure ton avancée par rapport à mon exemple

Le 28/03/2020 à 00:43
78chris a écrit :

RE

Vérifie si tu as bien corrigé au bon endroit à l'étape suivante

Dans le 1er cas on n'as pas de "" autour de Etat1 dans l'autre on en a (j'ai corrigé mon explication sur cette ligne pour que ce soit plus clair)

Essaie de lancer 2 sessions Excel et vérifie au fur et à mesure ton avancée par rapport à mon exemple

Feedback:

Le problème après la fusion des requêtes est résolu avec la formule suivante:

= Table.NestedJoin(#"Index ajouté", {"Index"}, #"Etat1", {"Index"}, "Etat1", JoinKind.Inner)

Ajouter une colonne, Colonne personnalisée : nom New, formule : =Text.From([INT])&"-"&[CIT] (ne fonctionnait pas, remplacement de "CIT" par "Etat1.CIT".
Ajouter une colonne, Colonne personnalisée : nom NewV, formule :

=Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),",")

==> nouveau message d'erreur:

Expression.Error : Le nom « CreateListOfLists » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.

La syntaxe et l'expression me semblent bien écrites. Je ne sais pas si je dois télécharger une sorte de librairie comportant ces expressions.

Merci d'avance

Le 28/03/2020 à 10:20

RE

ADjeremy a écrit :

Feedback:

Le problème après la fusion des requêtes est résolu avec la formule suivante:

= Table.NestedJoin(#"Index ajouté", {"Index"}, #"Etat1", {"Index"}, "Etat1", JoinKind.Inner)

Inutile mais pas gênant

ADjeremy a écrit :

Ajouter une colonne, Colonne personnalisée : nom New, formule : =Text.From([INT])&"-"&[CIT] (ne fonctionnait pas, remplacement de "CIT" par "Etat1.CIT"

Tu n'as pas respecté :

clic près de la double flèche près du titre de la colonne Etat1, décocher tout sauf CIT

Donc aussi la case du bas

ADjeremy a écrit :

Ajouter une colonne, Colonne personnalisée : nom NewV, formule :

=Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),",")

==> nouveau message d'erreur:

Expression.Error : Le nom « CreateListOfLists » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.

La syntaxe et l'expression me semblent bien écrites. Je ne sais pas si je dois télécharger une sorte de librairie comportant ces expressions.

As-tu bien fait un copier coller de ma formule ?

J'avais fait avec l'add on, je viens de revérifier sur 365, pas d'erreur

Il doit y avoir une faute de frappe (attention c'est case sensitive : une erreur de majuscule/minuscule suffit)

Pas de librairie à charger, ce sont les fonctions natives de PQ

Le 28/03/2020 à 15:12

RE

J'ai oublié une étape ce qui explique le souci

....renommer la requête en Final

  • Ajouter une colonne, Colonne personnalisée : nom Liste, formule :
    =({[Old], [New]})
  • renommer cette étape CreateListOfLists
  • cliquer sur le Fx à gauche de la barre de formule et taper = Data
Le 28/03/2020 à 23:19
78chris a écrit :

Bonjour

Il y a trois requêtes que tu peux voir : Données, Obtenir des données, Lancer l'éditeur PowerQuery

J'ai commencé par mettre la source sous forme de tableau nommé Data puis

depuis une cellule de ce tableau, Données, A partir d'un tableau : ce qui ouvre PowerQuery

  • PowerQuery type automatiquement les données : j'ai supprimé cette action de la liste des actions à droite
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • sélection des colonnes INT, IND, CIT, Index, clic droit, Dépivoter les autres colonnes
  • Ajouter une colonne, Colonne personnalisée : nom OldV, formule :
    =Text.From([INT])&"-"&Text.Replace([Valeur],",",","&Text.From([INT])&"-")
  • sélection de la colonne OldV, Accueil, Type de données, Text
  • Requête Ref : Accueil, Nouvelle source, Autre source, Requête vide
  • dans la barre de formule, taper
    = Excel.CurrentWorkbook(){[Name="Data"]}[Content]
  • sélection des colonnes INT, CIT, clic droit, Supprimer les autres colonnes
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • renommer l'étape Etat1 et la requête Ref
  • selection de la colonne INT, Transformer, Regrouper par : opération Compter les lignes
  • Ajouter une colonne, Colonne personnalisée : nom IDx, formule :
    =List.Numbers([Nombre],[Nombre],-1)
  • clic près de la double flèche près du titre de la colonne IDx, Développer sur de nouvelles lignes
  • trier par INT et IDx
  • Ajouter une colonne, Colonne d'index, A partir de 1
  • Accueil, Fusionner des requêtes, choisir REF en seconde requête, sélectionner la colonne Index dans chaque requête, jointure Interne
  • dans la barre de formule, remplacer la seconde occurrence #"Index ajouté" par Etat1 et "Index ajouté" aussi par "Etat1"
  • clic près de la double flèche près du titre de la colonne Etat1, décocher tout sauf CIT
  • supprimer les colonnes Index et Nombre
  • Ajouter une colonne, Colonne personnalisée : nom Old, formule :
    =Text.From([INT])&"-"&Text.From([IDx])
  • Ajouter une colonne, Colonne personnalisée : nom New, formule :
    =Text.From([INT])&"-"&[CIT]
  • sélection des colonne Old et New, Accueil, Type de données, Text
  • Requête Final : Accueil, Nouvelle source, Autre source, Requête vide
  • dans la barre de formule, taper
    = Ref
    et renommer la requête en Final
  • Ajouter une colonne, Colonne personnalisée : nom Liste, formule :
    =({[Old], [New]})
  • renommer cette étape CreateListOfLists
  • cliquer sur le Fx à gauche de la barre de formule et taper = Data
  • Ajouter une colonne, Colonne personnalisée : nom Custom, formule :
    =Text.Split([OldV], ",")
  • Ajouter une colonne, Colonne personnalisée : nom NewV, formule :
    =Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),",")
  • Ajouter une colonne, Colonne personnalisée : nom Résultat, formule :
    =Text.Replace([NewV],Text.From([INT])&"-","")
  • supprimer les colonnes OldV, Custom, NewV, Valeur
  • sélectionner la colonne Attribut, Transformer, Pivoter : colonne de valeurs : Résultat, ne pas agréger
  • sortir par Fermer et charger dans, connexion seulement
  • Afficher le volet des requêtes : Données, requêtes et connexions, clic droit sur Final, Charger dans, Table : choisir l'emplacement

On pourrait faire en deux requêtes mais j'ai préféré garder Ref dans un état intermédiaire pour contrôler.

Merci 78chris,

Avec les dernières modifications, cela a effectivement marché.

Merci beaucoup et très sincèrement.

Rechercher des sujets similaires à "remplacement groupe lignes"