XL 2021 Extraire les valeurs affichées dans un filtre automatique

C

Constantin

XLDnaute Occasionnel
Supporter XLD
Bonjour à tous,
En PJ un tableau comportant près de 500 lignes (dont 55 vides) et une dizaine de colonnes. Les colonnes Top1_medic à Top10_medic contiennent des valeurs (classes thérapeutiques) que l'on visualise au moyen des tris. Je voudrai, au moyen d'une macro, sélectionner toutes les valeurs affichées dans les tris pour ces 10 colonnes et les afficher sous forme d'une seule liste dans une nouvelle feuille du fichier.
J'ai, dans la feuille3, réussi à extraire ces données au moyen d'un tableau croisé dynamique (ou plutôt 10 TCD).
Ce qui m’intéresse c'est d'avoir une liste unique et sans doublons de toutes les valeurs de ces 9 colonnes en sachant que les données de ces colonnes sont susceptibles de changer.
Merci de votre aide.
 

Pièces jointes

  • EHPAD_Indicateurs_2021_TCD_Essai.xlsx
    42 KB · Affichages: 7
Trier par date Trier par votes
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @Constantin :),

Un essai dans le fichier joint. Le résultat souhaité n'est pas très clair o_O.
La mise à jour se fait par deux méthodes :
  1. A chaque fois qu'on sélectionne la feuille "Feuil2"
  2. quand on clique sur le bouton Hop!

nota 1 : si on sait qu'on désire toujours un calcul avec ou sans prise on compte du filtre, il suffit de remplacer l'instruction rep = msgbox(...) par rep = vbyes ou rep =vbno suivant ce qu'on désire. On ne sera plus embêté par la question.

Le code principal est dans Module1. Il y a un peu de code dans le module attaché à Feuil2.
 

Pièces jointes

  • Constantin- EHPAD_Indicateurs_2021- v1.xlsm
    54.4 KB · Affichages: 6
Dernière édition:
Vote positif 0 Vote négatif
AtTheOne

AtTheOne

XLDnaute Impliqué
Supporter XLD
Bonjour à toutes & à tous, bonjour @Constantin
Comme tu as EXCEL 2021, tu peux utiliser les formules matricielles dynamiques et te passer d'une macro.
En transformant ton tableau en tableau structuré nommé "tb_Médoc"
et en deux formules dont le résultat évolue dynamiquement :
Pour Lister de façon unique et trier tes médicaments (j'en conviens un peu complexe):
Enrichi (BBcode): 
=TRIER(UNIQUE(LET(Chaîne;CAR(10)&JOINDRE.TEXTE(CAR(10);VRAI;UNIQUE(Tb_Médoc[top1_medic]);UNIQUE(Tb_Médoc[top2_medic]);UNIQUE(Tb_Médoc[top3_medic]);UNIQUE(Tb_Médoc[top4_medic]);UNIQUE(Tb_Médoc[top5_medic]);UNIQUE(Tb_Médoc[top6_medic]);UNIQUE(Tb_Médoc[top7_medic]);UNIQUE(Tb_Médoc[top8_medic]);UNIQUE(Tb_Médoc[top9_medic]);UNIQUE(Tb_Médoc[top10_medic]));Nb;NBCAR(Chaîne)-NBCAR(SUBSTITUE(Chaîne;CAR(10);""));Marquage;SUBSTITUE(SUBSTITUE(Chaîne;CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));Liste;GAUCHE(STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne));TROUVE("¯";STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne))&"¯")-1);Liste)))
Pour le comptage des occurences :
Enrichi (BBcode): 
=NB.SI(Tb_Médoc[[top1_medic]:[top10_medic]];Synthèse!B2#)

le principe est de concaténer les valeurs uniques de chaque colonne en une seule chaîne avec comme séparateur le car(10) (retour chariot), puis d'en faire une liste en s'appuyant sur les cars() remplacés 2 à 2 par des "¯". D'extraire de nouveaux les valeurs uniques et enfin de trier.

Le comptage lui est plus simple en se référent à la plage dynamique Synthèse!B2# (noter le # qui permet de se référer à la plage résultat de la formule en B2)

Voir la pièce jointe
A bientôt
 

Pièces jointes

  • EHPAD_Indicateurs_2021_Formules dynamiques.xlsx
    43.4 KB · Affichages: 4
Dernière édition:
Vote positif 0 Vote négatif
AtTheOne

AtTheOne

XLDnaute Impliqué
Supporter XLD
Re-bonjour,
Des explications plus détaillées sur la 1ère formule :
Enrichi (BBcode): 
=TRIER(UNIQUE(
    LET(Chaîne;
        CAR(10)&JOINDRE.TEXTE(CAR(10);VRAI;UNIQUE(Tb_Médoc[top1_medic]);
                                           UNIQUE(Tb_Médoc[top2_medic]);
                                           UNIQUE(Tb_Médoc[top3_medic]);
                                           UNIQUE(Tb_Médoc[top4_medic]);
                                           UNIQUE(Tb_Médoc[top5_medic]);
                                           UNIQUE(Tb_Médoc[top6_medic]);
                                           UNIQUE(Tb_Médoc[top7_medic]);
                                           UNIQUE(Tb_Médoc[top8_medic]);
                                           UNIQUE(Tb_Médoc[top9_medic]);
                                           UNIQUE(Tb_Médoc[top10_medic]));
        Nb;
        NBCAR(Chaîne)-NBCAR(SUBSTITUE(Chaîne;CAR(10);""));
        Marquage;
        SUBSTITUE(SUBSTITUE(Chaîne;CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));
        Liste;
        GAUCHE(STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne));TROUVE("¯";STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne))&"¯")-1);
    Liste)
))

Dans la fonction LET
  • Le nom local "Chaîne" renvoie une concaténation des valeurs de chaque colonne séparées par un retour chariot
  • Le nom local "Nb"renvoie le nombre de médicaments compris dans cette chaîne
  • Le nom local "Marquage" renvoie autant de fois la Chaîne qu'elle contient de médicaments mais en isolant à chaque fois un médicament par deux caractères "¯"
  • Enfin Le nom "Liste" ne reprend dans chaque chaîne de Marquage que le médicament marqué par les caractères "¯"
La fonction LET renvoie "Liste" (on pourrait se passer du nom local Liste et renvoyer directement sa formule)
UNIQUE extrait les valeurs uniques du résultat de LET
TRIER trie dans l'ordre alphanumérique le résultat de UNIQUE
A bientôt
 
Dernière édition:
Vote positif 0 Vote négatif
C

Constantin

XLDnaute Occasionnel
Supporter XLD
AtTheOne à dit:
Bonjour à toutes & à tous, bonjour @Constantin
Comme tu as EXCEL 2021, tu peux utiliser les formules matricielles dynamiques et te passer d'une macro.
En transformant ton tableau en tableau structuré nommé "tb_Médoc"
et en deux formules dont le résultat évolue dynamiquement :
Pour Lister de façon unique et trier tes médicaments (j'en conviens un peu complexe):
Enrichi (BBcode): 
=TRIER(UNIQUE(LET(Chaîne;CAR(10)&JOINDRE.TEXTE(CAR(10);VRAI;UNIQUE(Tb_Médoc[top1_medic]);UNIQUE(Tb_Médoc[top2_medic]);UNIQUE(Tb_Médoc[top3_medic]);UNIQUE(Tb_Médoc[top4_medic]);UNIQUE(Tb_Médoc[top5_medic]);UNIQUE(Tb_Médoc[top6_medic]);UNIQUE(Tb_Médoc[top7_medic]);UNIQUE(Tb_Médoc[top8_medic]);UNIQUE(Tb_Médoc[top9_medic]);UNIQUE(Tb_Médoc[top10_medic]));Nb;NBCAR(Chaîne)-NBCAR(SUBSTITUE(Chaîne;CAR(10);""));Marquage;SUBSTITUE(SUBSTITUE(Chaîne;CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));Liste;GAUCHE(STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne));TROUVE("¯";STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne))&"¯")-1);Liste)))
Pour le comptage des occurences :
Enrichi (BBcode): 
=NB.SI(Tb_Médoc[[top1_medic]:[top10_medic]];Synthèse!B2#)

le principe est de concaténer les valeurs uniques de chaque colonne en une seule chaîne avec comme séparateur le car(10) (retour chariot), puis d'en faire une liste en s'appuyant sur les cars() remplacés 2 à 2 par des "¯". D'extraire de nouveaux les valeurs uniques et enfin de trier.

Le comptage lui est plus simple en se référent à la plage dynamique Synthèse!B2# (noter le # qui permet de se référer à la plage résultat de la formule en B2)

Voir la pièce jointe
A bientôt
Cliquez pour agrandir...
Merci infiniment pour cette réponse ultra rapide !
Sachant que je dispose d'Excel21 depuis seulement 2 jours, j'ai beaucoup à apprendre... Il ne me reste plus qu'à assimiler et comprendre le détail de cette longue formule et l'adapter au tableau d'origine dont j'ai supprimé environ 50 colonnes.
A bientôt !
Constantin
 
Vote positif 0 Vote négatif
AtTheOne

AtTheOne

XLDnaute Impliqué
Supporter XLD
Re-Bonjour @Constantin
Merci pour ta réponse
Constantin à dit:
Il ne me reste plus qu'à assimiler et comprendre le détail de cette longue formule et l'adapter au tableau d'origine dont j'ai supprimé environ 50 colonnes.
Cliquez pour agrandir...
A priori si les colonnes concernées par l'extraction ne changent pas de nom, tu n'as qu'à transformer ton tableau réel en tableau structuré (Accueil Mise sous forme de tableau, mon tableau comporte des entêtes) et le nommer "Tb_Médoc"
Sinon tu devras modifier cette partie de la 1ère formule :
Enrichi (BBcode): 
    LET(Chaîne;
        CAR(10)&JOINDRE.TEXTE(CAR(10);VRAI;UNIQUE(Tb_Médoc[top1_medic]);
                                           UNIQUE(Tb_Médoc[top2_medic]);
                                           UNIQUE(Tb_Médoc[top3_medic]);
                                           UNIQUE(Tb_Médoc[top4_medic]);
                                           UNIQUE(Tb_Médoc[top5_medic]);
                                           UNIQUE(Tb_Médoc[top6_medic]);
                                           UNIQUE(Tb_Médoc[top7_medic]);
                                           UNIQUE(Tb_Médoc[top8_medic]);
                                           UNIQUE(Tb_Médoc[top9_medic]);
                                           UNIQUE(Tb_Médoc[top10_medic]));

le reste pour cette formule devrait fonctionner
Fait moi un retour
A bientôt
 
Vote positif 0 Vote négatif
C

Constantin

XLDnaute Occasionnel
Supporter XLD
AtTheOne à dit:
Re-bonjour,
Des explications plus détaillées sur la 1ère formule :
Enrichi (BBcode): 
=TRIER(UNIQUE(
    LET(Chaîne;
        CAR(10)&JOINDRE.TEXTE(CAR(10);VRAI;UNIQUE(Tb_Médoc[top1_medic]);
                                           UNIQUE(Tb_Médoc[top2_medic]);
                                           UNIQUE(Tb_Médoc[top3_medic]);
                                           UNIQUE(Tb_Médoc[top4_medic]);
                                           UNIQUE(Tb_Médoc[top5_medic]);
                                           UNIQUE(Tb_Médoc[top6_medic]);
                                           UNIQUE(Tb_Médoc[top7_medic]);
                                           UNIQUE(Tb_Médoc[top8_medic]);
                                           UNIQUE(Tb_Médoc[top9_medic]);
                                           UNIQUE(Tb_Médoc[top10_medic]));
       Nb;
        NBCAR(Chaîne)-NBCAR(SUBSTITUE(Chaîne;CAR(10);""));
        Marquage;
        SUBSTITUE(SUBSTITUE(Chaîne;CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));CAR(10);"¯";LIGNE(DECALER(Synthèse!$A$1;0;0;Nb;1)));
        Liste;
        GAUCHE(STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne));TROUVE("¯";STXT(Marquage;TROUVE("¯";Marquage)+1;NBCAR(Chaîne))&"¯")-1);
    Liste)
))

Dans la fonction LET
  • Le nom local "Chaîne" renvoie une concaténation des valeurs de chaque colonne séparées par un retour chariot
  • Le nom local "Nb"renvoie le nombre de médicaments compris dans cette chaîne
  • Le nom local "Marquage" renvoie autan de fois la Chaîne qu'elle contient de médicaments mais en isolant à chaque fois un médicament par deux caractères "¯"
  • Enfin Le nom "Liste" ne reprend dans chaque chaîne de Marquage que le médicament marqué par les caractères "¯"
La fonction LET renvoie "Liste" (on pourrait se passer du nom local Liste et renvoyer directement sa formule)
UNIQUE extrait les valeurs uniques du résultat de LET
TRIER trie dans l'ordre alphanumérique le résultat de UNIQUE
A bientôt
Cliquez pour agrandir...
Merci AtTheOne,
Je suis passé d'Excel2007 à 2021 il y a seulement deux jours... Donc je découvre !
Excellente présentation et explications très utile pour le balbutiant que je suis. Reste à adapter cette formule à mon tableau originel (environ 80 colonnes).
Bon week-end et si j'osai abuser, je n'ai pas trouvé comment clore ma requête auprès des ExcelNautes.
 
Vote positif 0 Vote négatif
C

Constantin

XLDnaute Occasionnel
Supporter XLD
mapomme à dit:
Bonjour @Constantin :),

Un essai dans le fichier joint. Le résultat souhaité n'est pas très clair o_O.
La mise à jour se fait par deux méthodes :
  1. A chaque fois qu'on sélectionne la feuille "Feuil2"
  2. quand on clique sur le bouton Hop!

nota 1 : si on sait qu'on désire toujours un calcul avec ou sans prise on compte du filtre, il suffit de remplacer l'instruction rep = msgbox(...) par rep = vbyes ou rep =vbno suivant ce qu'on désire. On ne sera plus embêté par la question.

Le code principal est dans Module1. Il y a un peu de code dans le module attaché à Feuil2.
Cliquez pour agrandir...
Merci de cet envoi. Reste à "digérer" et surtout comprendre les possibilités d'Excel2021 que je découvre depuis deux jours (j'ai du boulot sur la planche)
Bon WE
 
Vote positif 0 Vote négatif
AtTheOne

AtTheOne

XLDnaute Impliqué
Supporter XLD
Coucou tout le monde
Constantin à dit:
et les afficher sous forme d'une seule liste dans une nouvelle feuille du fichier.
Cliquez pour agrandir...
J'ai compris qu'il fallait ne plus faire qu'une liste et non 10 comme dans ton fichier exemple, d'où la relative complexité de ma première formule, sachant qu'avec EXCEL2021 on ne dispose pas de la fonction FRACTIONNER.TEXTE().
me serais-je trompé ?
A bientôt
 
Vote positif 0 Vote négatif
C

Constantin

XLDnaute Occasionnel
Supporter XLD
djidji59430 à dit:
Bonjour à tous,

Une proposition :
Cliquez pour agrandir...
Rebonjour
Comme indiqué dans mon premier d'appel au secours, je souhaite pouvoir lister dans une même colonne ou ligne la totalité des éléments des colonnes D à M dans une même colonne.
J'ai essayé d'ajouter une colonne Top_Totaux (colonne C) avec en formule dans C2 :
=UNIQUE(FILTRE('ehpad indicateurs 2021'!D$2:M$494;'ehpad indicateurs 2021'!D$2:M$494<>0))
Ça me renvoie "#VALEUR!".
Sans doute une erreur de syntaxe ou une impossibilité liée aux fonctions UNIQUE ou FILTRE. Il me reste la possibilité de déplacer les résultats obtenus pour chaque colonne dans une seule et d'appliquer la formule magique que tu m'as envoyée.
Si tu as une idée pour parfaire mon inculture, je suis preneur !
Bon WE
Pierre Constantin
 
Vote positif 0 Vote négatif
C

Constantin

XLDnaute Occasionnel
Supporter XLD
AtTheOne à dit:
Coucou tout le monde

J'ai compris qu'il fallait ne plus faire qu'une liste et non 10 comme dans ton fichier exemple, d'où la relative complexité de ma première formule, sachant qu'avec EXCEL2021 on ne dispose pas de la fonction FRACTIONNER.TEXTE().
me serais-je trompé ?
A bientôt
Cliquez pour agrandir...
Bonjour Alain,
Je suis allé voir dans "excel-pratique" à quoi correspondait la fonction FRACTIONNER.TEXTE. Le nul que je suis n'a pas compris grand chose aux explications données et du peu que j'ai retenu, cette fonction semble destinée à isoler et séparer des informations présentes dans une cellule et séparées par un délimiteur (";" par exemple). A première vue, cela ne répond pas à mon souci mais je peux me tromper. J'ai aussi pu constater que cette fonction n'était pas présente dans mon Excel2021 (peut-être réservée à office365 ?)
Merci de ton message et bon dimanche
Pierre Constantin
 
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

[Troll du dimanche]
Donc Excel 2021 ne possède pas la fonction FRACTIONNER.TEXTE(). mais possède JOINDRE.TEXTE() (qui existe depuis assez longtemps).
Cela illustre sans doute le fait que Krosoft fait tout ce qu'il peut pour pousser à s'abonner à O365 pour générer un revenu mensuel assuré, non ?
Son rêve ultime : supprimer VBA du paysage d'Office. Nul doute qu'il y a arrive un jour, mais quand ?
Les pronostics sont ouverts...
[/Troll du dimanche]
 
Vote positif 0 Vote négatif
C

Constantin

XLDnaute Occasionnel
Supporter XLD
mapomme à dit:
Re,

[Troll du dimanche]
Donc Excel 2021 ne possède pas la fonction FRACTIONNER.TEXTE(). mais possède JOINDRE.TEXTE() (qui existe depuis assez longtemps).
Cela illustre sans doute le fait que Krosoft fait tout ce qu'il peut pour pousser à s'abonner à O365 pour générer un revenu mensuel assuré, non ?
Son rêve ultime : supprimer VBA du paysage d'Office. Nul doute qu'il y a arrive un jour, mais quand ?
Les pronostics sont ouverts...
[/Troll du dimanche]
Cliquez pour agrandir...
Tout à fait d'accord avec toi. C'est pour ça que j'ai acheté la version 2021. Je vais travailler la fonction JOINDRE.TEXTE et voir ce que je peux en tirer.
A bientôt
Pierre
 
Vote positif 0 Vote négatif

Discussions similaires

cabsen
  • Question
XL 2019 Extraire toutes les données d'une liste en fonction d'un critère
Réponses
2
Affichages
368
cabsen
cabsen
L
  • Question
tri et export
Réponses
4
Affichages
185
lescoches
L
M
  • Question
XL 2010 Récupération de données et les transformer avec Power Query ?
Réponses
3
Affichages
252
DJunqueira
D
chaelie2015
  • Résolu(e)
XL 2013 (RESOLU) Affichage des résultats dans un tableau selon des conditions
Réponses
13
Affichages
298
chaelie2015
chaelie2015
A
  • Question
Microsoft 365 Alimenter automatiquement un tableau à partir des données d'un autre tableau en fonction d'un critère
Réponses
6
Affichages
241
Alexandrax971
A
Z
  • Question
Microsoft 365 Validation de données et saisie partielle
Réponses
3
Affichages
139
Dranreb
Dranreb
JBOBO
  • Question
XL 2019 sommer les valeurs d'un tableau dans un autre selon critere
Réponses
2
Affichages
162
JBOBO
JBOBO
V
  • Question
XL 2021 Implémenter tableau avec des fichiers mis dans un dossier
Réponses
2
Affichages
177
merinos
merinos
PatFr38
  • Résolu(e)
Microsoft 365 Masquer des colonnes aux cellules avec valeurs données selon sélection d'une liste déroulante
Réponses
5
Affichages
591
PatFr38
PatFr38
Messan
  • Résolu(e)
XL 2019 Organiser les données dans listbox
Réponses
2
Affichages
386
Messan
Messan

Membres actuellement en ligne

7 membres et 118 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 203
Messages
2 086 193
Membres
103 153
dernier inscrit
SamirN