Regrouper tous les noms d'un tableau sur une seule colonne

[Clochard]

Bonjour le Forum,

J'essaie de regrouper avec une formule les noms présents dans un tableau de plusieurs colonnes.... sur une seule colonne....
J'y arrive pas

D'après-vous..... Est-ce possible?

Merci d'y regarder

Clochard

 

[Claudy]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour le forum...
Pas avec formules mais avec macro!
Bonne journée,
Claudy

 

[JeanMarie]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour Clochard

Ci-joint ton fichier modifié, la aussi les modifications sont trop importantes pour les expliquer. Cette fois j'espère n'avoir pas commis d'impair.

@+Jean-Marie

 

[hoerwind]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour Clochard, Claudy et JeanMarie,

Autre approche, mais encore partielle
La seule modification apportée au tableau est le déplacement de la classe F (entre A et BB)

La formule en colonne W fonctionne, je ne me suis pas encore intéressé aux colonnes V et X, mais cela ne doit pas poser problème.
Une partie répétitive (matricielle) de cette formule a été nommée "valeur".
Le fait de la nommer ne nécessite plus de valider la formule complète en matricielle.

Par contre en colonne W le classement ne se fait pas (encore) par ordre décroissant des points.
Si c'est souhaité, une légère modification des formules en colonnes A, E, I, ... y portera remède.

Vois quelle approche a ta préférence, la macro ou l'une des deux formules.

 

[hoerwind]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Re,

Une version complète en pièce jointe, liste rangée par classe, elle-même triée par ordre croissant des points.

Vois si cela répond à ta demande.

 

[Clochard]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour Claudy, JeanMarie, Hoerwind ainsi que le Forum

Merci Claudy pour ta suggestion.... mais sans vouloir te vexer je préfère une formule... merci pour ton aide.

Merci JeanMarie... ça fonctionne super bien ...

Grand merci à toi Hoerwind.... j'aime et préfère ta suggestion....

Merci pour tout ce temps que vous consacrez à ma demande.... j'apprécie énormément votre aide

Merci...Merci...
Clochard

 

[CyberNeo99]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour à tous,

J'aimerais avoir les explications relier au dernier fichier qui a été joint dans ce thread si possible, ce qui serais très apprécié.

Référence fichier Regroupementv2.zip.

L'élément que j'ai du du mal à saisir est le gauche(valeur) et dans la recherchev encore le critère de recherche qui se trouve à être valeur comment peut-il faire une recherche sur une variable.... Les explications pour le reste serait aussi grandement apprécier pour mon apprentissage de ce genre de problème.

Merci infiniment à l'avance

CyberNeo 😉

 

[hoerwind]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour,

Active la cellule Y5
Menu Insertion - Nom - Coller
Clique sur valeur, puis sur Coller une liste

Active la cellule Z5
Clique dans la barre de formule et valide la formule en matricielle (Ctrl+Maj+Entrée)
Tu obtiens 1034.01 comme résultat.
Étire cette formule vers le bas jusqu'en Z20
Tu obtiens une liste triée (PETITE.VALEUR) des valeurs contenues dans les colonnes A, E, I, ... (MOD(COLONNE(Feuil1!$A:$T)-1;4)=0), à commencer par la plus petite (LIGNE()-5)
Remarque aussi que le 1er chiffre (à gauche) de chaque nombre est le même pour toutes les nombres d'une même colonne (1 pour A, 2 pour E, 3 pour I, ...) et que le décalage de ces colonnes entre elles est de 4 colonnes.

Active la cellule W19
On recherche la valeur de Z19 (3069.012) dans une plage qui est décalée par rapport à A6, pour les lignes de 0 (rien entre les deux ; ), pour les colonnes de la valeur du chiffre de gauche -1,*4 → (3-1)*4=8, soit la 9ème colonne (I), sur une hauteur de 165 lignes et une largeur de 3 colonnes.
Je ne me souviens plus d'où vient le 165, probablement d'une version précédente et oublié de l'adapter.
Il serait plus logique, pour la compréhension, de remplacer ce 165 par NBVAL(B:B)-5
La fonction RECHERCHEV renvoie la valeur de la 3ème colonne de cette plage.

J'espère avoir été clair.

 

[CyberNeo99]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour,

Je dois avouer avoir besoin d'explication encore... 😛

Je dois avouer que ça commence à être de la haute voltige.... Dire que j'ai étudier en Math mais là je dois avouer que c'est fort ce que tu as fais...

Prenons la formule qui tri, en fait pour le nom valeur

=PETITE.VALEUR(SI((Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0);Feuil1!$A$6:$T$170);LIGNE()-5)

Si on prend la partie du test que voici

SI((Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0);Feuil1!$A$6:$T$170)

La partie du test logique est la suivante

(Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0)

Si cela est vrai tu envoie la plage

Feuil1!$A$6:$T$170

Mais si c'est faux la partie est absente....

Prenons maintenant la partie suivante

(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0)

Ceci est toujours égale à 0 car tu lui dis que la position à la colonne divisé par 4 égale, le modulo est toujours égale à 0. Ce résultat multiplie la partie suivante

(Feuil1!$A$6:$T$170>0)

Donc la plage se trouve à être égale à 0...

En gros j'ai du mal à saisir comment tu fais pour définir ta plage pour la fct Petite.valeur(plage;k-ième valeur)....

Au fait ton 65 vient de 170-6+1 qui correspond au nb de ligne que ta plage renferment en fait la hauteur.

Gros Merci pour ton aide, cela me sera très utile.

CyberNeo 😀

 

[hoerwind]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour,

Il me semble que tu n'a pas tout à fait saisi la portée d'une formule matricielle.
Fais une recherche sous l'aide MS, sous les anciens messages et sur le Net, et essaie d'assimiler cette notion.
Mais avant cela je te conseillerais de faire une recherche de George Boole, mathématicien anglais, et attardes-toi plus spécialement sur son algèbre et les notions de 1 et 0, en addition et en multiplication.

Pour ce qui est de la partie de formule MOD(...
Elle renvoie VRAI (donc 1) si le résultat est 0, et FAUX (donc 0) si le résultat est différent de 0.
Ce sera plus compréhensible si tu remplaces cette partie de formule par (et je me demande bien pourquoi je ne l'ai pas écrit ainsi !) :
(MOD(COLONNE(Feuil1!$A:$T);4)=1)

Mets en A2 la formule :
=SI(MOD(COLONNE();4)=1:"VRAI";"FAUX")
La formule globale, parce qu'elle est matricielle, ne prendra en considération que les colonnes affichant "VRAI", c'est à dire A, E, I, ...

Un peu de courage et beaucoup de concentration !

 

[ROGER2327]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonsoir à tous

Peut-être un peu à côté du problème, mais sait-on jamais ?
Un proposition (brute de fonderie) pour cinq colonnes (pas nécessairement contigües) sans colonne supplémentaire...

On s'amuse, le dimanche...​

ROGER2327
#3011

 

[CISCO]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonsoir à tous

Cf aussi ce fil

https://www.excel-downloads.com/threads/liste-dans-lordre-alphabetique-extraite-dun-tableau.117989/

@ plus

 

[CyberNeo99]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Voici ta formule originale :
=PETITE.VALEUR(SI((Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0);Feuil1!$A$6:$T$170);LIGNE()-5)

En faisant CTRL+SHIFT+ENTRÉE elle devient une matrice
Ce qui fait que ta formule Petite.Valeur tri les données où se trouve la formule du genre

=SI(D6="";"";EQUIV(B6;{"AA";"A";"F";"BB";"B"};0)*1000+D6+LIGNE()/1000)

qui sera sur une colonne en fait car il ne fait que trier, mettre en ordre croissant ces données

Cette partie qui suit de la Fct Petite.Valeur est la matrice qu’il va retourner dans lequel il va prendre la n-ième valeur

SI((Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0);Feuil1!$A$6:$T$170)

La n-ième valeur est

LIGNE()-5

Prenons le test

SI((Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0);Feuil1!$A$6:$T$170)

Si tu mets ceci dans une cellule il renvoie toujours la plage Feuil1!$A$6:$T$170 car VRAI * VRAI = VRAI et alors la condition est respecter.

Cette partie en matrice

(Feuil1!$A$6:$T$170>0)

est toujours VRAI

Dans ta nouvelle formule

=SI(MOD(COLONNE();4)=1:"VRAI";"FAUX")

Effectivement il retourne VRAI dans les colonnes où il y a la formule, ceux en fait où tu veux que le tri croissant soit fait,

=SI(D6="";"";EQUIV(B6;{"AA";"A";"F";"BB";"B"};0)*1000+D6+LIGNE()/1000)

Et FAUX dans les autres.
Dans ta formule orginale suivante

SI((Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0);Feuil1!$A$6:$T$170)

Test logique

(Feuil1!$A$6:$T$170>0)*(MOD(COLONNE(Feuil1!$A:$T)-1;4)=0)
Si VRAI il prend cette plage(matrice)
Feuil1!$A$6:$T$170

Où se trouve la partie fausse….. ???

J’imagine que si c’est faux il ne fait rien…. Mais la partie se doit d’y être quand même sinon la syntaxe n’est pas respecter….

Désolé si je reviens là dessus... je ne veux pas dire que tu as tords loin de là car ça semble fonctionner car le résultat est là, peut être qu'il y a un cas d'exception mais rien ne dit qu'il en existe un. Ta formule semble fonctionner à merveille.

J'essais de comprendre le pourquoi du quoi et de prendre parti par parti.

Gros Merci encore.

CyberNeo😱

 

[hoerwind]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Bonjour,

Je vois que tu n'as pas encore assimilé le fonctionnement d'une formule matricielle.

En pièce jointe un petit exemple avec un mot d'explication.
J'espère que cela sera plus clair.

 

[CyberNeo99]

Re : Regrouper tous les noms d'un tableau sur une seule colonne

Salut Hoerwind,

Merci pour ton temps qui est très apprécier. En fait la notion matrice je n,ai aucun problème car j'ai étudier en Math à l'Université mais je dois avouer dans Excel c'est une autre pair de manche. Je ne savais pas que tu pouvais ignorer la partie fausse si ton test logique est faux. Tu m'apprends quelque chose je pensais que cela devais toujours y être. Là je crois que ce sera plus clair. Je vais regarder ça aujourd'hui car je viens tout juste de me lever après une bonne nuit de réflexion. Je suis au Québec, j'imagine que tu es de la France ou un autre pays Européens francophone puisqu'en me levant je vois ta réponse....

En fait je dois faire exactement ce que le fichier Regroupementv2 fait mais les 2 différences sont que je n'ai pas besoin de mettre en ordre "valeur" dans ton fichier pour que les note soit en ordre croissant. L'autre différence est que j'ai 21 matrice si je puis dire, 21 séries de bloc de donnée que je dois mettre les un à côté des autres, dans ton fichier tu en as 5 moi 21. Le problème que je vais avoir est le gauche(valeur) rendu à 10, la 10e série et bien j'aurai encore un 1 comme résultat. il me faut adaptés tes formules à ma situation. Pour contourner le problème il me faudra utiliser la notion de suite et séries. J'ai trouvé une formule général pour identifier les 5 colonnes, voici la formule général 4*n-3 où n est la position de la colonne. Si la première colonne commence à la position 0 alors la formule sera 4*n+1. Je ne me souviens pas si la première colonne sa position est 0.

Au fait un expert comme toi quelle est le prix, le taux horaire du marché par curiosité....Je ne te demande pas ton salaire et/ou ton tarif bien sûr 🙂

Merci encore pour ton aide

CyberNeo😉