Formule Excel

Question

Bonjour à Tous,

Je fais appel à votre sagacité pour résoudre le problème suivant car je n'arrive pas à trouver la formule adéquate.
Dans un tableau de 40 lignes sur 15 colonnes, j'ai une colonne qui ne doit comporter qu'une seule cellule (texte ou numérique) à une position pas toujours identique. Le but est de pouvoir récupérer le contenu de cette unique cellule pour l'inscire dans une feuille récapitulative.
En bref, comment, à l'aide d'une formule, récupérer la seule cellule non vide dans une colonne. Avec une fonction perso dans une macro, pas de problèmes, mais ce n'est pas souhaitable dans cette application.

Merci d'avance pour l'aide que vous voudrez bien m'apporter.

Amicalement de Cheyenne.

jmfmarques · Answer

Bonsoir (et de mémoire auditive uniquement car je n'ai pas Excel et n'en connais donc que ce que j'ai entendu dire lors de conversations)...

Voyons donc, alors :

So c'est vraiment la seule cellule non vide de ta colonne, elle en est également la dernière (puisque la seule) à contenir quelque chose ...
Il me semble alors bien que l'utilisation conjuguée des fonctions INDEX, EQUIV et REPT devrait te permettre d'extraire précisément le dernier texte présent d'une colonne déterminée...

Je ne peux malheureusement être plus précis.
Tu devrais faure une recherche sur ces fonctions (mais pas ici, car ton problème n'est pas VBA mais purement EXCEL)

cs_cheyenne · Answer

Merci jmfmarques pour ces éléments de réponse.

J'ai posté sur ce forum car j'y vois souvent des réponses très pertinentes concernant Excel...

Quant à tes aides en VB6 on ne peut que t'en féliciter eu égard à la manière didactique d'y répondre ! Tu analyses toujours très bien le problème même (et surtout) quand les questions sont confuses ou peu précices et tu ne ménages pas ton temps pour apporter très souvent de l'aide. Il fallait que cela soit dit !

Merci encore et bonne continuation,

Bien cordialement de Cheyenne

cs_MPi · Answer

Il faudrait que tu expliques un peu plus clairement ...

Rechercher une valeur dans une colonne ...
Connais-tu la colonne ?
Connais-tu la valeur ?

MPi

jmfmarques · Answer

Bonjour,

J'ai "fouillé un peu partout et suis tombé sur ceci (avec EQUIV) qui pourrait t'être utile (je crois quand même qu'il te faudra également utiliser REPT et INDEX pour parvenir à du "plus mieux")

Molenn · Answer

Tu as moyen de le faire qu'avec des formules EXCEL, en trichant un tout petit peu.
Ton tableau fait 40 lignes sur 15 colonnes, mettons les colonnes B à P.
Supposons que ta colonne qui doit contenir une cellule unique non vide est la colonne B.

Là où tu vas tricher, c'est que tu vas rajouter une colonne à ton tableau (Tu pourras toujours la masquer, verrouiller, etc), considérons que cette colonne supplémentaire soit la A.
En A1, tu colles la formule suivante : =SI(ESTVIDE(B1)=VRAI;"";"toto")  et tu la recopies sur les 40 lignes. Tu obtiendras la même chose que dans ta colonne B, à savoir une unique cellule non vide dont l'adresse peut être modifiée mais, la différence, c'est que cette fois, tu sais quel mot sera affiché, c'est toi qui l'a choisi (j'ai mis toto, mais je te laisse choisir le mot qui te convient ^^).

Maitnenant, dans ta fiche récapitulative, tu peux récupérer le contenu sans problème.
Avec la formule =EQUIV("toto";A:A;0), tu récupères la position relative dans la matrice de ta cellule non vide (Si ton tableau commence ligne 1 comme dans mon exemple, ça correspond au n° de ligne, sinon, faut que tu ajoutes au chiffre obtenu la valeur de la première ligne de ton tableau).
Ensuite, avec la formule Adresse, tu peux reconstituer les coordonnées de la celulle qui t'intéresse, tu as le numéro de ligne, tu rentres le numéro de ta colonne, la colonne B cette fois.
Enfin, avec la formule INDIRECT, tu récupères le contenu de la cellule entrée en référence.

Mon exemple donc : 
Tableau B1:P40
Colonne A remplie avec ma formule ci-dessus.
La cellule récapitulative (que je mets en Z1 par exemple) : 
=INDIRECT(ADRESSE(EQUIV("toto";A:A;0);2))

Et voilà ^^

Molenn

cs_cheyenne · Answer

Bonjour mpi et jmfmarques (et les autres)

mpi : excuses-moi si tu as eu du mal à me comprendre, je croyais pourtant avoir été clair.
1) Le contenu de la cellule peut-être soit du texte soit du numérique et je n'en connais pas la 'valeur'.
2) Oui je connais la colonne. 

jmfmarques : merci, pour les diverses formules.  EQUIV me retourne bien le n° de ligne, mais le problème, comme dit à mpi, c'est que le contenu de la cellule peut changer de type. Comme tu le disait justement 'elle en est également la dernière (puisque la seule) à contenir quelque chose ...' 
Donc, il me serait utile de connaitre la formule qui me renvoie la dernière ligne remplie d'une colonne.
Pour ton info REPT est sensiblement l'équivalent en VB6 de String(10, "A")

Bonne journée,

Cheyenne

Molenn · Answer

Rafraîchis ton navigateur Cheyenne, tu auras la réponse 

Molenn

cs_cheyenne · Answer

Bonjour Molenn,


Peux-tu m'en dire plus ?  Je n'ai pas saisi pas ton trait d'humour... 


Cordialement

Molenn · Answer

Ben, juste avant ton dernier post, je t'ai donné une réponse pour obtenir ce que tu veux avec des formules EXCEL. A priori, tu ne l'as pas vu puisque tu ne me dis pas si ça te convient ou ce qui te pose problème.

Chose qui personnellement m'arrive souvent, j'ouvre plein de fenêtre, je fais une réponse sur une, et si je ne pense pas à rafraichir mon navigateur, je ne m'aperçois pas que quelqu'un a répondu pendant ce temps.

Donc, dans ma solution, la seule chose qui peut poser pb, c'est qu'il faille rajouter une colonne. A toi de me dire s'il faut que je continue à chercher autre chose (même si je doute de pouvoir trouver mieux), sinon, et bien ... Tout roule et ton pb est résolu 

Molenn

cs_cheyenne · Answer

Hé, hé

GRAND MERCI Molenn, cela me convient parfaitement au prix effectivementd'une petite tricherie. J'en ai bien compris le principe que tu as parfaitement expliqué.

Effectivement je n'avais pas rafraîchi le navigateur, j'attendais simplement en fait d'être prévenu par mail, car je suis en train de faire autre chose.

Encore merci à toi pour ton aide, je savais bien qu'en postant sur ce forum j'aurais une réponse rapide et parfaitement adaptée.

Bonne journée à toi.

Bien cordialement de Cheyenne.

cs_MPi · Answer

Salut Cheyenne,

Comme tu connais la colonne, une RechercheV pourrait peut-être suffire (?), mais avec VRAI comme dernier paramètre plutôt que FAUX qu'on a l'habitude d'utiliser. En mettant ce VRAI, la recherche trouvera la valeur inférieure se rapprochant le plus.

Le seul problème que je verrais, c'est au niveau des nombres. Il s'agirait de mettre un nombre de base (mon 999999999) qui sera toujours plus élevé que le plus grand nombre que tu pourrais retrouver dans ta plage... C'est du moins ce que j'en conclus après quelques tests. Et c'est peut-être impossible de savoir dans ton cas...(?)

Au niveau du texte, si ce sont des mots "normaux", il ne devrait pas y avoir de problème, je pense...

Si ta valeur unique est en colonne C
=SI(  ESTERREUR(  RECHERCHEV("zz";C1:C40;1;VRAI)); RECHERCHEV(999999999;C1:C40;1;VRAI);  
RECHERCHEV("zz";C1:C40;1;VRAI))

Mais l'idée de Molenn est excellente, encore une fois...

MPi

cs_cheyenne · Answer

Bonjour MPi,

C'est nickel-chrome ta combinaison de fonctions et, en bonus, les valeurs 999999999 et "zz" conviennent parfaitement.
De plus cette formule m'évite d'ajouter une colonne 'virtuelle' comme me l'avait suggéré l'Ami Molenn.

Tu écris 'Mais l'idée de Molenn est excellente, encore une fois...' oui, certes, c'est dans son habitude. Néanmoins ta solution est parfaite et c'est celle-ci que je retiens.

Encore une fois merci à vous deux pour votre aide efficace.

TRES cordialement de Cheyenne.

Molenn · Answer

N'en jetez plus, n'en jetez plus, c'est trop merci Sans compter qu'avec ce que je vous ai trouvé, vous allez devoir en jeter beaucoup plus encore Parce que j'ai trouvé une formule qui évite de devoir créer une colonne supplémentaire, et qui en plus n'oblige pas à mettre un chiffre maximum (genre 999999999) ou un mot (style "zzzzz") ! Postulat de base : Ta colonne B ne peut contenir qu'une seule et unique cellule. Cette cellule peut contenir du texte ou du numérique. Le numéro de colonne est connu et sera toujours le même : 2 L'inconnu est donc le numéro de ligne de la seule cellule non vide. Alors, si la cellule contient du texte, on peut trouver son numéro de ligne avec la formule EQUIV, en effet, je viens de découvrir celà, il existe une syntaxe particulière au format texte, si on cherche le caractère "*" et le type à 0, donc la formule : =EQUIV("*";B:B;0) donne la position relative dans la matrice du premier "texte" rencontré, et comme il y en a un seul dans toute la matrice (comme précédemment, faut rajouter la valeur de la première ligne de ton tableau s'il ne commence pas à la ligne 1) Pour le numérique, il faut savoir qu'Excel interprète une cellule vide comme une cellule numérique = à 0. Donc, quelque soit le chiffre marqué dans cette cellule, il sera forcément supérieur à 0 (une seule exception donc, le chiffre 0). On peut donc récupérer ce chiffre avec la formule MAX(Plage tableau). On a donc la valeur que l'on cherche, et avec une formule EQUIV, on récupère à nouveau sa position dans la matrice (à laquelle on ajoute la valeur de la 1ere ligne blabla) =EQUIV(MAX(B:B);B:B) Voilà, après, comme donné dans mon post précédent, avec la formule ADRESSE, on recrée les coordonnées de la cellule, et avec INDIRECT, on récupère sa valeur. il reste à mettre une condition pour différencier si la cellule contient du texte ou du numérique, ce que je fais avec un SI(ESTERREUR(recherche texte)=VRAI; ...) Et on obtient donc la formule magique suivante : INDIRECT(ADRESSE(SI(ESTERREUR(EQUIV("*";B:B;0))VRAI;EQUIV(MAX(B:B);B:B);EQUIV("*";B:B;0));2)) Un seul pb à cette formule : si la cellule prend pour valeur numérique 0. Mais bon, on ne peut pas tout avoir Molenn

cs_cheyenne · Answer

Hé bé, Comme tu n'as pas mis off '', je vais donc en rajouter : MERCI ET BRAVO BRAVO BRAVO Merci également pour les explications extrêmement bien formulées. J'arrête là l'usage des superlatifs. Il ne reste plus qu'à MPi de tenter de faire mieux ou plus court car ta formule est plus compacte que la sienne. Donc c'est adopté ! Comme quoi une simple question peut conduire à des recherches fructueuses puisque cela ta permis de découvrir les différentes syntaxes de EQUIV. De toutes façons il est souvent intéressant de chercher des solutions à des problèmes que l'on ne se pose pas forcément... C'est bien joué, merci encore Cheyenne

jmfmarques · Answer

EQUIV ? C'est quoi EQUIV ?


Quelqu'un pour me renseigner ?

cs_cheyenne · Answer

Bonjour, Oui, voilà ce que fait la fonction EQUIV suivant l'aide d'Excel :

Renvoie la position relative d'un élément d'une matrice qui équivaut à une valeur spécifiée dans un ordre donné. Utilisez la fonction EQUIV plutôt qu'une des fonctions RECHERCHE lorsque vous avez besoin de la position d'un élément dans une plage et non de l'élément en tant que tel. Syntaxe EQUIV(valeur_cherchée;tableau_recherche;type) valeur_cherchée est la valeur utilisée pour trouver la valeur souhaitée dans une matrice.

L'argument valeur_cherchée est la valeur dont vous voulez l'équivalent dans l'argument tableau_recherche. Par exemple, lorsque vous cherchez le numéro de téléphone d'une personne dans un annuaire, vous utilisez le nom de la personne comme valeur de recherche alors que la valeur que vous voulez obtenir est son numéro de téléphone.

L'argument valeur_cherchée peut être une valeur (nombre, texte ou valeur logique) ou une référence de cellule à un nombre, à du texte ou à une valeur logique.tableau_recherche est une plage de cellules adjacentes contenant les valeurs d'équivalence possibles. L'argument tableau_recherche peut être une matrice ou une référence matricielle.

type est le nombre -1, 0 ou 1 qui indique comment Microsoft Excel doit procéder pour comparer l'argument valeur_cherchée aux valeurs de l'argument tableau_recherche.

Si la valeur de l'argument type est 1, la fonction EQUIV trouve la valeur la plus élevée qui est inférieure ou égale à celle de l'argument valeur_cherchée. Les valeurs de l'argument tableau_recherche doivent être placées en ordre croissant : ...-2, -1, 0, 1, 2, ...A-Z, FAUX, VRAI.

Si la valeur de l'argument type est 0, la fonction EQUIV trouve la première valeur exactement équivalente à celle de l'argument valeur_cherchée. Les valeurs de l'argument tableau_recherche peuvent être placées dans un ordre quelconque.

Si la valeur de l'argument type est -1, la fonction EQUIV trouve la plus petite valeur qui est supérieure ou égale à celle de l'argument valeur_cherchée. Les valeurs de l'argument tableau_recherche doivent être placées en ordre décroissant : VRAI, FAUX, Z-A,...2, 1, 0, -1, -2,..., et ainsi de suite.

Si l'argument type est omis, la valeur par défaut est 1.

Notes

La fonction EQUIV renvoie la position de la valeur équivalente dans l'argument tableau_recherche et non la valeur en elle-même. Par exemple, EQUIV("b".{"a"."b"."c"};0) renvoie 2, c'est-à-dire la position relative de « b » dans la matrice {"a"."b"."c"}.

La fonction EQUIV ne distingue pas les majuscules des minuscules lorsqu'elle donne l'équivalence de valeurs de texte.

Si la fonction EQUIV ne peut trouver de valeur équivalente, elle renvoie la valeur d'erreur #N/A.

Si la valeur de l'argument type est 0 et que celle de l'argument valeur_cherchée est du texte, l'argument valeur_cherchée peut comprendre les caractères génériques, l'astérisque (*) et le point d'interrogation (?). L'astérisque est équivalent à une séquence de caractères, le point d'interrogation à un caractère unique.

Amicalement de Cheyenne

cs_MPi · Answer

Salut JMF, EQUIV équivaut à MATCH en anglais
Peut-être est-ce plus "lisible" pour toi ?

Molenn, tu me surprends encore avec tes formules tordues,... Bravo !

Cheyenne, si je trouve mieux, je n'hésiterai pas te donner l'éventuelle solution et en profiter en même temps pour relancer Molenn ... 

MPi

Formule Excel

17 réponses

Votre réponse

Discussions similaires