Excel :déterminer les coordonnées d'1 cellule
echec et math
Messages postés
102
Statut
Membre
-
echec et math Messages postés 102 Statut Membre -
echec et math Messages postés 102 Statut Membre -
Bonjour,
Quelque part situé dans les colonnes A:E dans excel 2007, j'ai le tableau suivant:
D3 = tarifs
D4 = 12
D5 = 70
Ce tableau pourrait être situé n'importe où ailleurs dans les colonnes A:E.
Afin de déterminer les coordonnées du mot "tarifs" j'utilise les fonctions matricielles suivantes:
{SOMMEPROD(SI($A:$E="tarifs";LIGNE($A:E)))} et {SOMMEPROD(SI($A:$E="tarifs";COLONNE($A:E)))}
qui me donnent le résultat respectif 3 et 4.
Donc le mot "tarifs" se situe à l'intersection de la ligne 3 et de la colonne 4 soit dans la cellule D3.
A partir de ces coordonnées, quelle formule peut on utiliser afin d'obtenir le résultat "tarifs" dans la cellule F1 ?
Merci.
Quelque part situé dans les colonnes A:E dans excel 2007, j'ai le tableau suivant:
D3 = tarifs
D4 = 12
D5 = 70
Ce tableau pourrait être situé n'importe où ailleurs dans les colonnes A:E.
Afin de déterminer les coordonnées du mot "tarifs" j'utilise les fonctions matricielles suivantes:
{SOMMEPROD(SI($A:$E="tarifs";LIGNE($A:E)))} et {SOMMEPROD(SI($A:$E="tarifs";COLONNE($A:E)))}
qui me donnent le résultat respectif 3 et 4.
Donc le mot "tarifs" se situe à l'intersection de la ligne 3 et de la colonne 4 soit dans la cellule D3.
A partir de ces coordonnées, quelle formule peut on utiliser afin d'obtenir le résultat "tarifs" dans la cellule F1 ?
Merci.
A voir également:
- Excel :déterminer les coordonnées d'1 cellule
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Si ou excel - Guide
- Déplacer colonne excel - Guide
- Excel trier par ordre croissant chiffre - Guide
15 réponses
...en fait, dans la cellule F1 j'aimerais trouver une formule/fonction du style :
= ligne(3)/colonne(4) ou le résultats serait le mot "tarifs" de la cellule D3.
= ligne(3)/colonne(4) ou le résultats serait le mot "tarifs" de la cellule D3.
bonjour à tous
Sommeprod ne travaille pas sur des colonnes completes, donc voici une formule avec decaler
Formule NON matricielle
attention, ile ne doit y avoir qu'une seule foir le mot tarifs dans la plage sinon sommeprod va additionner les lignes et colonnes
maintenant si tu veux la premiere occurence de ce mot, c'est une autre formule un peu plus complexe et matricielle
Sommeprod ne travaille pas sur des colonnes completes, donc voici une formule avec decaler
Formule NON matricielle
=DECALER($A$1;SOMMEPROD(($A$1:$E$65000="tarifs")*LIGNE($A$1:$E$65000))-1;SOMMEPROD(($A$1:$E$65000="tarifs")*COLONNE($A$1:$E$65000))-1)
attention, ile ne doit y avoir qu'une seule foir le mot tarifs dans la plage sinon sommeprod va additionner les lignes et colonnes
maintenant si tu veux la premiere occurence de ce mot, c'est une autre formule un peu plus complexe et matricielle
re:
j'ai posté avent ta reponse
3/4 pour D3 : formule toujours NON matricielle
j'ai posté avent ta reponse
3/4 pour D3 : formule toujours NON matricielle
=SOMMEPROD(($A$1:$E$65000="tarifs")*LIGNE($A$1:$E$65000)) & " / " & SOMMEPROD(($A$1:$E$65000="tarifs")*COLONNE($A$1:$E$65000))
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Merci Wilfried,
Ta première réponse est celle que j'attendais, et la deuxième va m'aider également.
J'ai compris le principe de la fonction "décaler" qui va m'être très utile.
En revanche tu soulèves un pb que j'ai déjà rencontrer :
Comment déterminer les coordonnées de plusieurs mots identiques dans la même colonne ?
Par exemple si "tarifs" se situe en A1, A5 et A12.
Si c'est trop compliqué ou si tu n'as pas le temps laisse tomber, j'ai déjà pas mal à m'occuper avec tes 2 précédentes réponses.
Merci encore.
Ta première réponse est celle que j'attendais, et la deuxième va m'aider également.
J'ai compris le principe de la fonction "décaler" qui va m'être très utile.
En revanche tu soulèves un pb que j'ai déjà rencontrer :
Comment déterminer les coordonnées de plusieurs mots identiques dans la même colonne ?
Par exemple si "tarifs" se situe en A1, A5 et A12.
Si c'est trop compliqué ou si tu n'as pas le temps laisse tomber, j'ai déjà pas mal à m'occuper avec tes 2 précédentes réponses.
Merci encore.
re:
dans une seule cellule ???, c'est plus compliqué, il te faut une fonction personnalisée pour concatener les valeurs provenant de matrices (Formule matricielle)
exemple : en formule matricielle : à valider avec ctrl + Maj + Entrée
cette formule va te renvoyer une matrice avec les differnetes coordonnées, mais concatener ne fonctionne pas sur une formule matricielle.
voici donc une fonction personnalisée qui le fait (à placer dans un module
ce qui revient à ecrire comme formule :
je tiens à préciser que cette fonction existe deja : MCCONCAT je crois, mais pour cela il faut charger une macro complémentaire avec pleins d'autres formules que nous n'avons pas forcement besoin...
dans une seule cellule ???, c'est plus compliqué, il te faut une fonction personnalisée pour concatener les valeurs provenant de matrices (Formule matricielle)
exemple : en formule matricielle : à valider avec ctrl + Maj + Entrée
=Si($A$1:$E$100="tarifs"; ligne($A$1:$E$100) & "/" & colonne($A$1:$E:$100) & " - ";"")
cette formule va te renvoyer une matrice avec les differnetes coordonnées, mais concatener ne fonctionne pas sur une formule matricielle.
voici donc une fonction personnalisée qui le fait (à placer dans un module
Public Function Matrice_concatener(valeur As Variant) As String
Application.Volatile
For Each c In valeur
Matrice_concatener = Matrice_concatener & c
Next
End Function
ce qui revient à ecrire comme formule :
=Matrice_concatener(Si($A$1:$E$100="tarifs"; ligne($A$1:$E$100) & "/" & colonne($A$1:$E:$100) & " - ";""))
je tiens à préciser que cette fonction existe deja : MCCONCAT je crois, mais pour cela il faut charger une macro complémentaire avec pleins d'autres formules que nous n'avons pas forcement besoin...
...effectivement c'est plus compliqué.
la première formule me donne "1/1 -" seulement si "tarifs" est écrit en A1 et "0" si il n'y a rien d'inscrit en A1 même si "tarifs" est écrit dans d'autres cellule !!!
est ce qu'il ne serait pas plus simple d'obtenir le résultat dans des cellules différentes.
Par exemple si "tarifs" se situe en A1, A5 et A12.
Déterminer les coordonnées du mot "tarifs" de la cellule A1 an F1,
Déterminer les coordonnées du mot "tarifs" de la cellule A5 an F2,
Déterminer les coordonnées du mot "tarifs" de la cellule A12 an F3,
???
la première formule me donne "1/1 -" seulement si "tarifs" est écrit en A1 et "0" si il n'y a rien d'inscrit en A1 même si "tarifs" est écrit dans d'autres cellule !!!
est ce qu'il ne serait pas plus simple d'obtenir le résultat dans des cellules différentes.
Par exemple si "tarifs" se situe en A1, A5 et A12.
Déterminer les coordonnées du mot "tarifs" de la cellule A1 an F1,
Déterminer les coordonnées du mot "tarifs" de la cellule A5 an F2,
Déterminer les coordonnées du mot "tarifs" de la cellule A12 an F3,
???
bien entendu, mais pourrais tu envoyer un fichier exemple parce que la je suis dans le brouillard (j'ai besoin d'une sieste)
Bonsoir tout le monde,
En plus de decaler() pour récupérer une valeur d'une matrice tu as également index(matrice;ligne;colonne).
Pour la 2nde question tu peux faire avec :
=EQUIV("tarifs",INDIRECT("A"&F1+1&":A11"),0)+F1
à recopier vers le bas mais en F2 et en laissant F1 vide (pas de texte non plus) ou bien le (numéro-1) de la 1ère ligne où tu veux démarrer ta recherche colonne A.
En ajoutant des tests tu peux limiter l'affichage au nombre de 'tarifs' de la colonne et éviter les #N/A
eric
En plus de decaler() pour récupérer une valeur d'une matrice tu as également index(matrice;ligne;colonne).
Pour la 2nde question tu peux faire avec :
=EQUIV("tarifs",INDIRECT("A"&F1+1&":A11"),0)+F1
à recopier vers le bas mais en F2 et en laissant F1 vide (pas de texte non plus) ou bien le (numéro-1) de la 1ère ligne où tu veux démarrer ta recherche colonne A.
En ajoutant des tests tu peux limiter l'affichage au nombre de 'tarifs' de la colonne et éviter les #N/A
eric
Merci Eriiic et Wilfried pour votre aide,
J'ai fini la sieste et j'ai bossé un peu ;-)
voilà mon tableau en pièce jointe (voir lien ci-dessous, ce n'est qu'un exemple), en fait normalement, je "récupère" des pages web que j'exploite en les collant dans des tableaux excel, mais les tableaux ne coïncident pas toujours avec les miens, voilà pourquoi je suis obligé d'utiliser toutes ces formules pour repérer les "mots" qui m'intéressent.
Quelques explications :
Dans mon tableau les données se trouvent dans les colonnes A:D (il n'y a jamais 2 fois les mm mot sur une même ligne, une chance !!!)
Dans un premier temps je détermine les coordonnées verticales (N°de ligne) des mots "tarifs" (cellules J2:J7),
ensuite, je détermine les coordonnées horizontales (N°de colonne) des mots "tarifs" (cellules k2:k7),
et à partir de là, je détermine mes tableaux de tarifs dans l'ordre d'apparition par ligne (cellules M4:R9).
Ca fonctionne très bien, mais je n'utilise pas de matrice et donc je suis obligé de faire mes calculs pour chaque colonne B,C, et D.
Il y a peut être plus simple et plus souple ?
Mais là cela va déjà beaucoup m'aider
Lien tableau excel :
https://www.cjoint.com/?kgpPHvficS
J'ai fini la sieste et j'ai bossé un peu ;-)
voilà mon tableau en pièce jointe (voir lien ci-dessous, ce n'est qu'un exemple), en fait normalement, je "récupère" des pages web que j'exploite en les collant dans des tableaux excel, mais les tableaux ne coïncident pas toujours avec les miens, voilà pourquoi je suis obligé d'utiliser toutes ces formules pour repérer les "mots" qui m'intéressent.
Quelques explications :
Dans mon tableau les données se trouvent dans les colonnes A:D (il n'y a jamais 2 fois les mm mot sur une même ligne, une chance !!!)
Dans un premier temps je détermine les coordonnées verticales (N°de ligne) des mots "tarifs" (cellules J2:J7),
ensuite, je détermine les coordonnées horizontales (N°de colonne) des mots "tarifs" (cellules k2:k7),
et à partir de là, je détermine mes tableaux de tarifs dans l'ordre d'apparition par ligne (cellules M4:R9).
Ca fonctionne très bien, mais je n'utilise pas de matrice et donc je suis obligé de faire mes calculs pour chaque colonne B,C, et D.
Il y a peut être plus simple et plus souple ?
Mais là cela va déjà beaucoup m'aider
Lien tableau excel :
https://www.cjoint.com/?kgpPHvficS
Bonjour,
J'ai regardé ton tableau et c'est difficile de contrôler, bcp de formules complexes.
Il y a en a au moins 1 qui est bizarre c'est =INDEX($A:$D,M$1-1+LIGNE(A2),M$2) en M5
LIGNE(A2) te retournera toujours 2 puisque tu lui demandes son n° de ligne...
Je me demande s'il ne faudrait pas plutôt s'orienter vers une macro.
Quoiqu'il en soit il faut du temps dispo pour contrôler
eric
J'ai regardé ton tableau et c'est difficile de contrôler, bcp de formules complexes.
Il y a en a au moins 1 qui est bizarre c'est =INDEX($A:$D,M$1-1+LIGNE(A2),M$2) en M5
LIGNE(A2) te retournera toujours 2 puisque tu lui demandes son n° de ligne...
Je me demande s'il ne faudrait pas plutôt s'orienter vers une macro.
Quoiqu'il en soit il faut du temps dispo pour contrôler
eric
re:
Je ne comprends pas ce que tu veux dire, "LIGNE(A2)" me sert uniquement à recopier ma formule vers le bas en ajoutant "1" à chaque fois.
J'ai refait mon tableau en utilisant la fonction "INDIRECT", c'est bcp mieux (voir lien ci-dessous).
Mais ça ne change rein à mon pb de base.
En fait le plus difficile ici, c'est de déterminer les coordonnées de chaque mot "tarifs" (cellules J2:K7).
Certainement avec une macro, mais bon pour le moment, de cette façon ça me convient parfaitement.
En tous cas, merci pour tes conseils.
martial
lien tableau excel:
https://www.cjoint.com/?kij7TBgbmD
Je ne comprends pas ce que tu veux dire, "LIGNE(A2)" me sert uniquement à recopier ma formule vers le bas en ajoutant "1" à chaque fois.
J'ai refait mon tableau en utilisant la fonction "INDIRECT", c'est bcp mieux (voir lien ci-dessous).
Mais ça ne change rein à mon pb de base.
En fait le plus difficile ici, c'est de déterminer les coordonnées de chaque mot "tarifs" (cellules J2:K7).
Certainement avec une macro, mais bon pour le moment, de cette façon ça me convient parfaitement.
En tous cas, merci pour tes conseils.
martial
lien tableau excel:
https://www.cjoint.com/?kij7TBgbmD
Bonsoir,
Ca fonctionne très bien, mais je n'utilise pas de matrice et donc je suis obligé de faire mes calculs pour chaque colonne B,C, et D
Si tu parles de formules matricielles on les utilise quand on n'a pas le choix ou quand c'est 'évident' à formuler pour économiser des formules.
Ici je ne vois pas ou tu pourrais avoir un gain, surtout si tu as le résultat qui t'interesse...
Ca fonctionne très bien, mais je n'utilise pas de matrice et donc je suis obligé de faire mes calculs pour chaque colonne B,C, et D
Si tu parles de formules matricielles on les utilise quand on n'a pas le choix ou quand c'est 'évident' à formuler pour économiser des formules.
Ici je ne vois pas ou tu pourrais avoir un gain, surtout si tu as le résultat qui t'interesse...
