Excel :déterminer les coordonnées d'1 cellule Fermé

Question

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.

LatelyGeek · Answer

Qu'appelles tu "obtenir le résultat "tarifs" dans la cellule F1 ? "

echec et math · Answer

...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.

wilfried_42 · Answer

bonjour à tous

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

wilfried_42 · Answer

re:

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))

echec et math · Answer

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.

wilfried_42 · Answer

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
=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...

echec et math · Answer

...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,
???

wilfried_42 · Answer

bien entendu, mais pourrais tu envoyer un fichier exemple parce que la je suis dans le brouillard (j'ai besoin d'une sieste)

echec et math · Answer

j'essaie d'envoyer ça lundi...
rappelle moi juste comment on envoie un fichier !!!
bon week end

eriiic · Answer

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

echec et math · Answer

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

eriiic · Answer

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

echec et math · Answer

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

eriiic · Answer

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...

echec et math · Answer

Entièrement d'accord avec toi, problème résolu !!!
Merci pour ton aide et merci à Wilfried.
Bonne continuation...
martial