Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Applications Microsoft Office Excel Formule ?

Formule ?

16 réponses
Avatar
BreizhBird
Bonjour

Tableau

Alain 12
Berthe 15
Anatole 12
Louis 32
Toto 12

Existe-t-il une formule qui donne dans la même cellule tous les noms des
"=12" ?

Merci d'avance

BreizhBird
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
isabelle
bonjour BreizhBird,

à mon humble avis il n'en existe pas, mais tu peux créer une fonction
personnalisé,

Function ListeNom(plage As Range, valeur As Integer)
For Each c In Range(plage.Columns(2).Address)
If c = valeur Then ListeNom = ListeNom & Cells(c.Row,
plage.Columns(1).Column) & ", "
Next
End Function

isabelle


Bonjour

Tableau

Alain 12
Berthe 15
Anatole 12
Louis 32
Toto 12

Existe-t-il une formule qui donne dans la même cellule tous les noms des
"" ?

Merci d'avance

BreizhBird


Avatar
Vincent.
Bonsoir BreizhBird, et re bonsoir Isabelle ;-)
tout ça pour dire, que je suis d'accord sur le fait qu'une
telle formule ne doit pas exister, CONCATENER ne pouvant
être utiliser que sur des cellules uniques et non sur une
matrice ce qui aurait pu être ici fort utile...
La fonction vba elle marche impec ! à première vue ;-))
A+

V.

-----Message d'origine-----
bonjour BreizhBird,

à mon humble avis il n'en existe pas, mais tu peux créer
une fonction

personnalisé,

Function ListeNom(plage As Range, valeur As Integer)
For Each c In Range(plage.Columns(2).Address)
If c = valeur Then ListeNom = ListeNom & Cells(c.Row,
plage.Columns(1).Column) & ", "
Next
End Function

isabelle


Bonjour

Tableau

Alain 12
Berthe 15
Anatole 12
Louis 32
Toto 12

Existe-t-il une formule qui donne dans la même cellule
tous les noms des


"" ?

Merci d'avance

BreizhBird
.





Avatar
AV
Et les réponses données à ta même question du 24/11/03 20:25 ?

AV
Avatar
MAZAS Bernard
Salut Alain

"AV" a écrit dans le message de
news:
Et les réponses données à ta même question du 24/11/03 20:25 ?


Je ne les vois pas. PB de serveur ?

Bernard

Avatar
AV
Ave Bernard,

Je ne les vois pas. PB de serveur ?


Ben je sais pas mais moi je vois.
De toute façon ne cherche pas UNE formule --> il n'y en a pas ;-)

AV

Avatar
Michel Gaboly
Bonjour,

Mais si Vincent, on peut utiliser CONCATENER() ici ;-))

Tout ce qui suit est à considérer comme un exercice
de style qui montre, si besoin est, la puissance des
noms.

Il est évident qu'une fonction comme celle proposée
par isabelle est bien plus simple à mettre en oeuvre.
Cela peut servir sur un poste où le niveau de sécurité
maximum est souhaité (pas de macro).

1 - Nommer (Insertion, Noms, Définir...) "Valeurs"
(sans les guillemets, ceci est valable pour tous les
noms à créer) la plage correspondant à la seconde
colonne du tableau, par exemple "B1:B5" pour un
tableau de 5 lignes commençant en A1.

2 - Entrer la valeur dont on recherche les différen-
tes occurrences, 12 dans l'exemple de BreizhBird
(cf. son message, repris ci-dessous).

3 - Nommer cette cellule "Réf".

4 - Il est facile de trouver la première occurrence
de 12 dans la plage Valeurs :

=EQUIV(Réf;Valeurs;0)

5 - Au lieu d'entrer cette formule, on l'associe à
un nom, "Tr1" (Tr comme Trouvé).

6 - Pour trouver l'occurrence suivante, il ne faut
plus chercher dans "Valeurs", mais dans une plage
commençant juste en-dessous de la première occur-
rence trouvée. Cette plage peut être déterminée en
utilisant DECALER() :

DECALER(Valeurs;Tr1;0)

Le décalage en lignes est égal à "Tr1", il est de 0 en
colonnes. Pour limiter la recherche à l'intérieur de
"Valeurs", car un éventuel 12 situé à l'extérieur de
cette plage ne nous intéresse pas, il faut se limiter
à l'intersection entre la plage obtenue par DECALER()
et "Valeurs". L'opérateur d'intersection est l'espace.
La plage à utiliser est donc :

Valeurs DECALER(Valeurs;Tr1;0)

7 - La première occurrence dans cette plage, obtenue
avec EQUIV() doit être corrigée en ajoutant Tr1, pour
avoir sa position dans "Valeurs", et non dans la plage
utilisée pour la recherche.On définit donc ainsi le nom
"Tr2" :

=EQUIV(Réf;Valeurs DECALER(Valeurs;Tr1;0);0) + Tr1

8 - On peut définir selon le même principe "Tr3",
"Tr4", et autant de noms que nécessaires. Cependant,
au-delà de 3 ou 4, une procédure VBA pour définir
les noms est + rapide. (NB - La création de multiples
noms peut être faite à partir d'une procédure contenue
dans un au- tre classeur, si on ne veut pas de macro
dans le document.

9 - La première série de Noms a permis de localiser
les occurrences, au moinsles premières (cela dépend
du nombre, suffisant ou non de noms ayant été définis).
Pour trouver les noms correspondants, situés dans la
colonne précédente, définissons le nom "Noms" :

ÞCALER(Valeurs;0;-1)

10 - Le premier nom, "Nom1" s'obtient ainsi :

=SI(ESTNUM(Tr1);INDEX(Noms;Tr1);"")

Le ESTNUM() permet de tester si une occurrence a
été trouvée. Sinon, on associe à "Nom1" une chaîne
vide.

11 - Pour définir le second nom, il faut d'abord se
demander comment on va présenter le résultat final
(la liste des noms correspondant aux valeurs 12.
Il faut pouvoir les séparer, pour que ce soit lisible.
Le nom "Sep" permet de définir le séparateur,ici,
l'espace :

=" "

12 - Les noms "Nom2" et suivants sont définis selon
le même principe :

=Sep & SI(ESTNUM(Tr2);INDEX(Noms;Tr2);"")

13 - Il suffit d'entre dans une cellule

=Nom1 & Nom2 & Nom3 & Nom4

pour avoir la liste des 4 premiers noms.

14 - Cette formule peut être associée à un nom,
"Résultat" par exemple, ce qui permet de se conten-
ter d'inscrire dans une cellule

=Résultat

15 - On pourrait définir en plus les noms "Rés1",
"Rés2", ... permettant de récupérer ainsi les N
premiers noms en entrant

=RésN

Noms définis dans mon classeru de test.
Nom1 =SI(ESTNUM(Tr1);INDEX(Noms;Tr1);"")
Nom2 =Sep & SI(ESTNUM(Tr2);INDEX(Noms;Tr2);"")
Nom3 =Sep & SI(ESTNUM(Tr3);INDEX(Noms;Tr3);"")
Nom4 =Sep & SI(ESTNUM(Tr4);INDEX(Noms;Tr4);"")
Noms ÞCALER(Valeurs;0;-1)
Réf þuil1!$C$1
Résultat =Nom1 & Nom2 & Nom3 & Nom4
Sep =" "
Tr1 =EQUIV(Réf;Valeurs;0)
Tr2 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr1;0);0) + Tr1
Tr3 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr2;0);0) + Tr2
Tr4 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr3;0);0) + Tr3
Valeurs þuil1!$B$1:$B$7

Pour définir suffisamment de noms de façon aisée,
il faut une procédure VBA avec une boucle.

Voilà un aperçu ;-)) de ce que permettent les noms.


PS - Les noms "Tr1", "Tr2" et suivants poseront un
problème dans quelques années, le jour où Excel aura
franchi le cap des 512 colonnes (256 actuellement),
car TR1 correspondra alors à la cellule située en
première ligne d la colonne 538 ;-)))



Bonsoir BreizhBird, et re bonsoir Isabelle ;-)
tout ça pour dire, que je suis d'accord sur le fait qu'une
telle formule ne doit pas exister, CONCATENER ne pouvant
être utiliser que sur des cellules uniques et non sur une
matrice ce qui aurait pu être ici fort utile...
La fonction vba elle marche impec ! à première vue ;-))
A+

V.

-----Message d'origine-----
bonjour BreizhBird,

à mon humble avis il n'en existe pas, mais tu peux créer
une fonction

personnalisé,

Function ListeNom(plage As Range, valeur As Integer)
For Each c In Range(plage.Columns(2).Address)
If c = valeur Then ListeNom = ListeNom & Cells(c.Row,
plage.Columns(1).Column) & ", "
Next
End Function

isabelle


Bonjour

Tableau

Alain 12
Berthe 15
Anatole 12
Louis 32
Toto 12

Existe-t-il une formule qui donne dans la même cellule
tous les noms des


"" ?

Merci d'avance

BreizhBird
.





--
Cordialement,

Michel Gaboly
http://www.gaboly.com



Avatar
Vincent.
Merci pour cette xl-ente démonstration !! ;-))
Mais dans mon précédent message je pensais plutôt à
concaténer des éléments d'une matrice plutôt qu'à des
noms...

-----Message d'origine-----
Bonjour,

Mais si Vincent, on peut utiliser CONCATENER() ici ;-))

Tout ce qui suit est à considérer comme un exercice
de style qui montre, si besoin est, la puissance des
noms.

Il est évident qu'une fonction comme celle proposée
par isabelle est bien plus simple à mettre en oeuvre.
Cela peut servir sur un poste où le niveau de sécurité
maximum est souhaité (pas de macro).

1 - Nommer (Insertion, Noms, Définir...) "Valeurs"
(sans les guillemets, ceci est valable pour tous les
noms à créer) la plage correspondant à la seconde
colonne du tableau, par exemple "B1:B5" pour un
tableau de 5 lignes commençant en A1.

2 - Entrer la valeur dont on recherche les différen-
tes occurrences, 12 dans l'exemple de BreizhBird
(cf. son message, repris ci-dessous).

3 - Nommer cette cellule "Réf".

4 - Il est facile de trouver la première occurrence
de 12 dans la plage Valeurs :

=EQUIV(Réf;Valeurs;0)

5 - Au lieu d'entrer cette formule, on l'associe à
un nom, "Tr1" (Tr comme Trouvé).

6 - Pour trouver l'occurrence suivante, il ne faut
plus chercher dans "Valeurs", mais dans une plage
commençant juste en-dessous de la première occur-
rence trouvée. Cette plage peut être déterminée en
utilisant DECALER() :

DECALER(Valeurs;Tr1;0)

Le décalage en lignes est égal à "Tr1", il est de 0 en
colonnes. Pour limiter la recherche à l'intérieur de
"Valeurs", car un éventuel 12 situé à l'extérieur de
cette plage ne nous intéresse pas, il faut se limiter
à l'intersection entre la plage obtenue par DECALER()
et "Valeurs". L'opérateur d'intersection est l'espace.
La plage à utiliser est donc :

Valeurs DECALER(Valeurs;Tr1;0)

7 - La première occurrence dans cette plage, obtenue
avec EQUIV() doit être corrigée en ajoutant Tr1, pour
avoir sa position dans "Valeurs", et non dans la plage
utilisée pour la recherche.On définit donc ainsi le nom
"Tr2" :

=EQUIV(Réf;Valeurs DECALER(Valeurs;Tr1;0);0) + Tr1

8 - On peut définir selon le même principe "Tr3",
"Tr4", et autant de noms que nécessaires. Cependant,
au-delà de 3 ou 4, une procédure VBA pour définir
les noms est + rapide. (NB - La création de multiples
noms peut être faite à partir d'une procédure contenue
dans un au- tre classeur, si on ne veut pas de macro
dans le document.

9 - La première série de Noms a permis de localiser
les occurrences, au moinsles premières (cela dépend
du nombre, suffisant ou non de noms ayant été définis).
Pour trouver les noms correspondants, situés dans la
colonne précédente, définissons le nom "Noms" :

ÞCALER(Valeurs;0;-1)

10 - Le premier nom, "Nom1" s'obtient ainsi :

=SI(ESTNUM(Tr1);INDEX(Noms;Tr1);"")

Le ESTNUM() permet de tester si une occurrence a
été trouvée. Sinon, on associe à "Nom1" une chaîne
vide.

11 - Pour définir le second nom, il faut d'abord se
demander comment on va présenter le résultat final
(la liste des noms correspondant aux valeurs 12.
Il faut pouvoir les séparer, pour que ce soit lisible.
Le nom "Sep" permet de définir le séparateur,ici,
l'espace :

=" "

12 - Les noms "Nom2" et suivants sont définis selon
le même principe :

=Sep & SI(ESTNUM(Tr2);INDEX(Noms;Tr2);"")

13 - Il suffit d'entre dans une cellule

=Nom1 & Nom2 & Nom3 & Nom4

pour avoir la liste des 4 premiers noms.

14 - Cette formule peut être associée à un nom,
"Résultat" par exemple, ce qui permet de se conten-
ter d'inscrire dans une cellule

=Résultat

15 - On pourrait définir en plus les noms "Rés1",
"Rés2", ... permettant de récupérer ainsi les N
premiers noms en entrant

=RésN

Noms définis dans mon classeru de test.
Nom1 =SI(ESTNUM(Tr1);INDEX(Noms;Tr1);"")
Nom2 =Sep & SI(ESTNUM(Tr2);INDEX(Noms;Tr2);"")
Nom3 =Sep & SI(ESTNUM(Tr3);INDEX(Noms;Tr3);"")
Nom4 =Sep & SI(ESTNUM(Tr4);INDEX(Noms;Tr4);"")
Noms ÞCALER(Valeurs;0;-1)
Réf þuil1!$C$1
Résultat =Nom1 & Nom2 & Nom3 & Nom4
Sep =" "
Tr1 =EQUIV(Réf;Valeurs;0)
Tr2 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr1;0);0) + Tr1
Tr3 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr2;0);0) + Tr2
Tr4 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr3;0);0) + Tr3
Valeurs þuil1!$B$1:$B$7

Pour définir suffisamment de noms de façon aisée,
il faut une procédure VBA avec une boucle.

Voilà un aperçu ;-)) de ce que permettent les noms.


PS - Les noms "Tr1", "Tr2" et suivants poseront un
problème dans quelques années, le jour où Excel aura
franchi le cap des 512 colonnes (256 actuellement),
car TR1 correspondra alors à la cellule située en
première ligne d la colonne 538 ;-)))



Bonsoir BreizhBird, et re bonsoir Isabelle ;-)
tout ça pour dire, que je suis d'accord sur le fait
qu'une


telle formule ne doit pas exister, CONCATENER ne pouvant
être utiliser que sur des cellules uniques et non sur
une


matrice ce qui aurait pu être ici fort utile...
La fonction vba elle marche impec ! à première vue ;-))
A+

V.

-----Message d'origine-----
bonjour BreizhBird,

à mon humble avis il n'en existe pas, mais tu peux
créer



une fonction
personnalisé,

Function ListeNom(plage As Range, valeur As Integer)
For Each c In Range(plage.Columns(2).Address)
If c = valeur Then ListeNom = ListeNom & Cells(c.Row,
plage.Columns(1).Column) & ", "
Next
End Function

isabelle


Bonjour

Tableau

Alain 12
Berthe 15
Anatole 12
Louis 32
Toto 12

Existe-t-il une formule qui donne dans la même
cellule




tous les noms des
"" ?

Merci d'avance

BreizhBird
.





--
Cordialement,

Michel Gaboly
http://www.gaboly.com


.






Avatar
Michel Gaboly
De rien ;-))

J'ai simplement utilisé ta remarque sur la conca-
ténation comme prétexte ;-))



Merci pour cette xl-ente démonstration !! ;-))
Mais dans mon précédent message je pensais plutôt à
concaténer des éléments d'une matrice plutôt qu'à des
noms...

-----Message d'origine-----
Bonjour,

Mais si Vincent, on peut utiliser CONCATENER() ici ;-))

Tout ce qui suit est à considérer comme un exercice
de style qui montre, si besoin est, la puissance des
noms.

Il est évident qu'une fonction comme celle proposée
par isabelle est bien plus simple à mettre en oeuvre.
Cela peut servir sur un poste où le niveau de sécurité
maximum est souhaité (pas de macro).

1 - Nommer (Insertion, Noms, Définir...) "Valeurs"
(sans les guillemets, ceci est valable pour tous les
noms à créer) la plage correspondant à la seconde
colonne du tableau, par exemple "B1:B5" pour un
tableau de 5 lignes commençant en A1.

2 - Entrer la valeur dont on recherche les différen-
tes occurrences, 12 dans l'exemple de BreizhBird
(cf. son message, repris ci-dessous).

3 - Nommer cette cellule "Réf".

4 - Il est facile de trouver la première occurrence
de 12 dans la plage Valeurs :

=EQUIV(Réf;Valeurs;0)

5 - Au lieu d'entrer cette formule, on l'associe à
un nom, "Tr1" (Tr comme Trouvé).

6 - Pour trouver l'occurrence suivante, il ne faut
plus chercher dans "Valeurs", mais dans une plage
commençant juste en-dessous de la première occur-
rence trouvée. Cette plage peut être déterminée en
utilisant DECALER() :

DECALER(Valeurs;Tr1;0)

Le décalage en lignes est égal à "Tr1", il est de 0 en
colonnes. Pour limiter la recherche à l'intérieur de
"Valeurs", car un éventuel 12 situé à l'extérieur de
cette plage ne nous intéresse pas, il faut se limiter
à l'intersection entre la plage obtenue par DECALER()
et "Valeurs". L'opérateur d'intersection est l'espace.
La plage à utiliser est donc :

Valeurs DECALER(Valeurs;Tr1;0)

7 - La première occurrence dans cette plage, obtenue
avec EQUIV() doit être corrigée en ajoutant Tr1, pour
avoir sa position dans "Valeurs", et non dans la plage
utilisée pour la recherche.On définit donc ainsi le nom
"Tr2" :

=EQUIV(Réf;Valeurs DECALER(Valeurs;Tr1;0);0) + Tr1

8 - On peut définir selon le même principe "Tr3",
"Tr4", et autant de noms que nécessaires. Cependant,
au-delà de 3 ou 4, une procédure VBA pour définir
les noms est + rapide. (NB - La création de multiples
noms peut être faite à partir d'une procédure contenue
dans un au- tre classeur, si on ne veut pas de macro
dans le document.

9 - La première série de Noms a permis de localiser
les occurrences, au moinsles premières (cela dépend
du nombre, suffisant ou non de noms ayant été définis).
Pour trouver les noms correspondants, situés dans la
colonne précédente, définissons le nom "Noms" :

ÞCALER(Valeurs;0;-1)

10 - Le premier nom, "Nom1" s'obtient ainsi :

=SI(ESTNUM(Tr1);INDEX(Noms;Tr1);"")

Le ESTNUM() permet de tester si une occurrence a
été trouvée. Sinon, on associe à "Nom1" une chaîne
vide.

11 - Pour définir le second nom, il faut d'abord se
demander comment on va présenter le résultat final
(la liste des noms correspondant aux valeurs 12.
Il faut pouvoir les séparer, pour que ce soit lisible.
Le nom "Sep" permet de définir le séparateur,ici,
l'espace :

=" "

12 - Les noms "Nom2" et suivants sont définis selon
le même principe :

=Sep & SI(ESTNUM(Tr2);INDEX(Noms;Tr2);"")

13 - Il suffit d'entre dans une cellule

=Nom1 & Nom2 & Nom3 & Nom4

pour avoir la liste des 4 premiers noms.

14 - Cette formule peut être associée à un nom,
"Résultat" par exemple, ce qui permet de se conten-
ter d'inscrire dans une cellule

=Résultat

15 - On pourrait définir en plus les noms "Rés1",
"Rés2", ... permettant de récupérer ainsi les N
premiers noms en entrant

=RésN

Noms définis dans mon classeru de test.
Nom1 =SI(ESTNUM(Tr1);INDEX(Noms;Tr1);"")
Nom2 =Sep & SI(ESTNUM(Tr2);INDEX(Noms;Tr2);"")
Nom3 =Sep & SI(ESTNUM(Tr3);INDEX(Noms;Tr3);"")
Nom4 =Sep & SI(ESTNUM(Tr4);INDEX(Noms;Tr4);"")
Noms ÞCALER(Valeurs;0;-1)
Réf þuil1!$C$1
Résultat =Nom1 & Nom2 & Nom3 & Nom4
Sep =" "
Tr1 =EQUIV(Réf;Valeurs;0)
Tr2 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr1;0);0) + Tr1
Tr3 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr2;0);0) + Tr2
Tr4 =EQUIV(Réf;Valeurs DECALER(Valeurs;Tr3;0);0) + Tr3
Valeurs þuil1!$B$1:$B$7

Pour définir suffisamment de noms de façon aisée,
il faut une procédure VBA avec une boucle.

Voilà un aperçu ;-)) de ce que permettent les noms.


PS - Les noms "Tr1", "Tr2" et suivants poseront un
problème dans quelques années, le jour où Excel aura
franchi le cap des 512 colonnes (256 actuellement),
car TR1 correspondra alors à la cellule située en
première ligne d la colonne 538 ;-)))



Bonsoir BreizhBird, et re bonsoir Isabelle ;-)
tout ça pour dire, que je suis d'accord sur le fait
qu'une


telle formule ne doit pas exister, CONCATENER ne pouvant
être utiliser que sur des cellules uniques et non sur
une


matrice ce qui aurait pu être ici fort utile...
La fonction vba elle marche impec ! à première vue ;-))
A+

V.

-----Message d'origine-----
bonjour BreizhBird,

à mon humble avis il n'en existe pas, mais tu peux
créer



une fonction
personnalisé,

Function ListeNom(plage As Range, valeur As Integer)
For Each c In Range(plage.Columns(2).Address)
If c = valeur Then ListeNom = ListeNom & Cells(c.Row,
plage.Columns(1).Column) & ", "
Next
End Function

isabelle


Bonjour

Tableau

Alain 12
Berthe 15
Anatole 12
Louis 32
Toto 12

Existe-t-il une formule qui donne dans la même
cellule




tous les noms des
"" ?

Merci d'avance

BreizhBird
.





--
Cordialement,

Michel Gaboly
http://www.gaboly.com


.




--
Cordialement,

Michel Gaboly
http://www.gaboly.com





Avatar
Vincent.
J'ai simplement utilisé ta remarque sur la conca-
ténation comme prétexte ;-))


Et tu as bien fait !!! :o)
D'autant qu'une bonne utilisation des noms est toujours un
peu délicate pour le non-initié, et là, c'était très
clair, voire didactique...

A+

Avatar
Michel Gaboly
Re,

En principe, c'était le but ;-)))



J'ai simplement utilisé ta remarque sur la conca-
ténation comme prétexte ;-))


Et tu as bien fait !!! :o)
D'autant qu'une bonne utilisation des noms est toujours un
peu délicate pour le non-initié, et là, c'était très
clair, voire didactique...

A+


--
Cordialement,

Michel Gaboly
http://www.gaboly.com


1 2