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 Renvoie d'une valeur par rapport à une autre ligne

Renvoie d'une valeur par rapport à une autre ligne

16 réponses
Avatar
géjoun
Bonjour,

Dans le ficher ci-joint http://cjoint.com/?mfrfXgsGZH , j'essaie d'obtenir
en C6 le nombre qui se trouve sur la ligne 2 et qui correspond à la dernière
semaine du mois le plus présent entre E1 et AS1 (suivant comment est complété
E2; par exemple, avec 4 en E2, que ça me renvoie 8; et avec 37 en E2, que ça
me renvoie 43).

J'ai essayé de modifier des formules que j'ai trouvé en recherchant dans les
archives, mais j'arrive qu'à obtenir des #VALEUR

Si quelqu'un pouvait m'aider...

Merci!

--
géjoun
dam-mail2005@ifrance.com
/Enlever l'année pour m'écrire.../
Signaler un problème avec ce contenu

6 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
ChrisV
Merci du retour...
(parce que, les explications de texte, c'est pas vraiment mon truc...)
%-7


ChrisV


"JLuc" a écrit dans le message de news:

*Bonjour ChrisV*,
Si ca c'est pas de l'explication !! ;-)
Au moins, avec ca, on saisi mieux comment excel calcul
Un grand bravo


--
JLuc

Pensez a regarder ces sites très bien fait, ce sont des mines de trucs et
astuces !
http://www.excelabo.net
http://jacxl.free.fr/
http://dj.joss.free.fr/
C:Program FilesMicrosoft OfficeOffice1036VBALIST.XLS




Avatar
Ange Ounis
j'essaie d'obtenir
en C6 le nombre qui se trouve sur la ligne 2 et qui correspond à la dernière
semaine du mois le plus présent entre E1 et AS1


D'après ton fichier exemple, lorsque tu entres 2 en E2, la plage E4:AS4 comporte
24 jours de janvier et 16 jours de février. J'en déduis que c'est janvier le
"mois le plus présent entre E1 et AS1"... et la dernière semaine entière de
janvier est bien la semaine 4...

Et désolé, mais si je saisis 7, 8, 9 ou 10 en E2, la formule renvoie bien 13, et
non 12......

----------
Ange Ounis
----------

Bonjour,

je viens de voir ta réponse, c'est bien ce principe que je cherche à faire,
mais ça me convient qu'aux 3/4 car elle ne fonctionne pas toujours.

Par exemple, lorsqu'on renseigne 2 en E2, au lieu de renvoyer le numéro de
semaine 8, elle renvoie le numéro 4, et pareil lorsqu'on complète avec 7, 8,
9 ou 10 elle renvoie 12 au lieu de 13.

Je me penche dessus pour essayer de comprendre et voir d'oû ça vient.

Merci!



Avatar
géjoun
Bonjour,

Je viens de voir ton message, et là, ya un soucis.

Je viens de retéléchargé mon fichier car je pensais que j'avais pû modifier
quelque chose dessus, et j'ai re-essayé en complétant E2 avec 2, et dans ce
cas, je me retrouve avec janvier qui va du lundi 10 au lundi 31 = 16 jours;
et février du mardi 1 au vendredi 25 = 19 jours; donc (chez moi du moins)
c'est bien février qui est le plus présent entre E4 et AS4, donc, ta formule
devrait me renvoyer 8 à la place de 4 (qui correspond à la dernière semaine
de Janvier).

Et idem pour 7, 8, 9, 10 qui me renvoie 12 au lieu de 13.
Si une autre personne peut vérifier...
J'avoue que j'ai du mal à saisir pourquoi elle marcherait chez toi et pas
chez moi en partant du même fichier... :-(

--
géjoun

/Enlever l'année pour m'écrire.../





j'essaie d'obtenir
en C6 le nombre qui se trouve sur la ligne 2 et qui correspond à la dernière
semaine du mois le plus présent entre E1 et AS1


D'après ton fichier exemple, lorsque tu entres 2 en E2, la plage E4:AS4 comporte
24 jours de janvier et 16 jours de février. J'en déduis que c'est janvier le
"mois le plus présent entre E1 et AS1"... et la dernière semaine entière de
janvier est bien la semaine 4...

Et désolé, mais si je saisis 7, 8, 9 ou 10 en E2, la formule renvoie bien 13, et
non 12......

----------
Ange Ounis
----------



Avatar
géjoun
Ouuuuufffff!!!! 8-O

Et moi qui m'attendais à une p'tite explication à la portée de tout le
monde! ;-)

Allez, je m'imprime ça, je prends une grosse boite d'efferalgan, et je me
plonge dedans!

Merci pour cette explication si...."détaillée"! ;-) (je risque encore
d'avoir quelques questions, mais sur l'explication maintenant! :o)) ).

--
géjoun

/Enlever l'année pour m'écrire.../




Oups...
Désolé pour cette réponse tardive, j'avais oublié ce fil...

Donc...
Tu précisais lors d'un précédent message:
"N'importe comment que soit complété E2, on se retrouve toujours avec un
mois "dominant" entre E1 et AS1, et je cherche le moyen de faire renvoyer en
C6 le numéro de semaine qui correspond à la dernière semaine de ce mois
"dominant".

L'élaboration de la formule ne fait que suivre scrupuleusement cet énoncé,
la solution (pas la seule...) était donc dans la question en elle-même...

Afin de mieux se représenter l'explication donnée ci-après, prenons dans
notre exemple la valeur 13 en E2

1) "...on se retrouve toujours avec un mois dominant..."

Traduit en langage formule, par rapport à la plage de données (E1:AS1 nommée
ici Zn) où se situe les informations, cela donne: "renvoyer l'entrée la plus
fréquente contenue sur la plage Zn". Pour des valeurs numériques, on utilise
généralement la fonction MODE(), malheureusement inutilisable ici puisque la
plage se compose de valeurs numériques (vide interprété comme 0), mais aussi
de valeurs alpha (les mois), il nous faut donc "composer" autrement...
C'est ce que va nous permettre
INDEX(Zn;EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);))
Examinons maintenant les arguments de cette fonction.

a) Matrice: il s'agit bien de la plage où se situe l'information à renvoyer,
donc ici Zn composée ainsi:
{0.0."mars".0.0.0.0.0."avril".0.0.0.0.0."avril".0.0.0.0.0."avril".0.0.0.0.0."avril".0.0.0.0.0."mai".0.0.0.0.0."mai".0.0}

b) Il s'agit maintenant d'indiquer, par rapport à cette matrice, quel est
l'indice de la référence à renvoyer (nb: on pourrait d'ailleurs tout aussi
bien utiliser ici indifféremment l'argument No_lig, ou No_col), dans notre
exemple 9, puisque c'est bien, examiné de visu, le 9e élément de la matrice
qui est l'entrée la plus fréquente...
C'est ce que va nous permettre EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);)

En effet, NB.SI(Zn;Zn) va renvoyer la matrice suivante:
{0.0.1.0.0.0.0.0.4.0.0.0.0.0.4.0.0.0.0.0.4.0.0.0.0.0.4.0.0.0.0.0.2.0.0.0.0.0.2.0.0}
le 3e élément de la matrice (mars) apparaît 1 fois; le 9e élément (avril) 4
fois; le 33e élément (mai) 2 fois, etc...

MAX(NB.SI(Zn;Zn)) permettant bien évidement d'extraire ici la valeur 4, et
permettra de "poser" la question suivante: "quelle est la valeur de la
matrice Zn qui apparaît 4 fois. On utilisera pour cela la fonction EQUIV().

EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);) examine donc la matrice suivante
EQUIV(4;{0.0.1.0.0.0.0.0.4.0.0.0.0.0.4.0.0.0.0.0.4.0.0.0.0.0.4.0.0.0.0.0.2.0.0.0.0.0.2.0.0};)
et permet d'obtenir l'indice de référence que nous recherchions
précédemment, afin de compléter l'argument 2 (ou 3) de notre fonction
INDEX(), c'est à dire la valeur 9.

Dans son expression globale, la formule
INDEX(Zn;EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);)) est analysée en mémoire par
Excel sous la forme:
INDEX({0.0."mars".0.0.0.0.0."avril".0.0.0.0.0."avril".0.0.0.0.0."avril".0.0.0.0.0."avril".0.0.0.0.0."mai".0.0.0.0.0."mai".0.0};9)
et permet donc d'extraire la valeur "avril"

La formule finale est donc interprétée en interne de la façon suivante (déjà
plus "lisible"...):
=INDIRECT(ADRESSE(2;GRANDE.VALEUR(SI(Zn="avril";COLONNE(Zn)-2);1)))

2) "...je cherche le moyen de faire renvoyer en C6 le numéro de semaine qui
correspond à la dernière semaine de ce mois dominant."
Traduit en langage formule: "renvoyer la plus grande valeur correspondant à
la donnée la plus fréquente de la plage (E1:AS1 nommée ici Zn)"
Deux options sont ici possibles, utiliser la fonction GRAND.VALEUR() ou, et
nous le verrons un peu plus tard, utiliser la fonction MAX(). L'intérêt de
la première résidant dans la possibilité d'extraire, si besoin, le nième (le
kième devrais-je plutôt dire…) élément recherché de la matrice examinée.
GRANDE.VALEUR(SI(Zn=INDEX(Zn;EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);));COLONNE(Zn)-2);1)
par rapport aux calculs précédents, on a donc:
GRANDE.VALEUR(SI(Zn="avril";COLONNE(Zn)-2);1)
Examinons maintenant les arguments de cette fonction.

a) Matrice: correspond à la plage sur laquelle s'effectue la recherche.
Cette recherche devant toutefois se limiter au seul mois "dominant", il est
impératif de ne renvoyer que les éléments correspondant à ce critère...
C'est ce que va nous permettre le test:
SI(Zn=INDEX(Zn;EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);));COLONNE(Zn)-2)
ou encore, selon les calculs déjà effectués...
SI(Zn="avril";COLONNE(Zn)-2)

Si le test est vérifié, alors Excel devra renvoyer le numéro de colonne (-2
afin d'ajuster les données à l'organisation de ton tableau) de chaque
élément correspondant. L'analyse s'opère de la façon suivante:
SI({FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.VRAI.FAUX.
FAUX.FAUX.FAUX.FAUX.VRAI.FAUX.FAUX.FAUX.FAUX.FAUX.VRAI.
FAUX.FAUX.FAUX.FAUX.FAUX.VRAI.FAUX.FAUX.FAUX.FAUX.FAUX.
FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX};{3.4.5.6.7.8.
9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.
33.34.35.36.37.38.39.40.41.42.43})

Il transmet donc à notre fonction GRAND.VALEUR() la matrice suivante:
GRANDE.VALEUR({FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.
11.FAUX.FAUX.FAUX.FAUX.FAUX.17.FAUX.FAUX.FAUX.FAUX.FAUX.
23.FAUX.FAUX.FAUX.FAUX.FAUX.29.FAUX.FAUX.FAUX.FAUX.FAUX.
FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX};1)

b) K: la valeur recherchée étant la plus grande, le rang de la donnée à
renvoyer correspond au rang 1.
GRANDE.VALEUR(SI(Zn="avril";COLONNE(Zn)-2);1) renvoie donc la valeur 29;
valeur qui correspond au numéro de la colonne (ici AC) contenant la plus
grande valeur sur la matrice composée uniquement de la valeur la plus
fréquente contenue sur la plage Zn...
(tu m'suis toujours...?:-)

3) Le numéro de colonne étant maintenant calculé et connu, le numéro de
ligne ne variant pas (ligne2) il ne nous reste plus qu'à composer une
adresse valide pour Excel, et de renvoyer le contenu de cette cellule...
C'est ce que va nous permettre INDIRECT(ADRESSE())
On a donc, avec les éléments calculés précédents:
INDIRECT(ADRESSE(2;29)) qui sera finalement interprété de la façon suivante:
INDIRECT("$AC$2"), soit:


La validation matricielle étant nécessaire dans la mesure ou l'on demandera
à Excel de créer et de travailler en mémoire sur des tableaux successifs,
permettant d'analyser les matrices "virtuelles" utilisées en argument de
fonction.

Voili, voilou...
J'espère que ces quelques lignes n'auront pas obscurci d'avantage les points
que tu souhaitais éclaircir, et/ou qu'elles ne t'auront pas fait vider le
tube d'aspirines...

Ah... j'oubliais...
nb: le point 2) faisait mention de la fonction MAX que nous aurions pu
utiliser à la place de GRANDE.VALEUR(), la formule finale aurait été:
{=INDIRECT(ADRESSE(2;MAX(SI(Zn=INDEX(Zn;EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);));COLONNE(Zn)-2))))}


ChrisV


"ChrisV" a écrit dans le message de news:
ewo0lxy%
et je me retrouve avec 50 fonctions combinées...


Pfff... 9 fonctions, pas 50 ?! ;-)
Pour l'explication, ok pour ce soir, pas le temps maintenant...
(à moins que JP, beaucoup plus habile que moi avec sa plume, ne prenne le
relais...)


ChrisV


"géjoun" a écrit dans le message de news:

Bonjour,

C'est bon cette fois-ci, ta formule fonctionne parfaitement!
Une fois de plus, je m'attendais à une formule assez simple pour faire
ça,
et je me retrouve avec 50 fonctions combinées...

Pourrais-tu me donner quelques explications sur son fonctionnement? (et
qu'apporte le fait que ce soit une matricielle?)

Merci!

--
géjoun

/Enlever l'année pour m'écrire.../



Bonjour Géjoun,

Avec la plage de données E1:AS1 nommée ici Zn
(à saisir sans les { } et valider par Ctrl+Shift+Entrée)

{=INDIRECT(ADRESSE(2;GRANDE.VALEUR(SI(Zn=INDEX(Zn;EQUIV(MAX(NB.SI(Zn;Zn));NB.SI(Zn;Zn);));COLONNE(Zn)-2);1)))}


ChrisV


"géjoun" a écrit dans le message de news:

Bonjour,

je viens de voir ta réponse, c'est bien ce principe que je cherche à
faire,
mais ça me convient qu'aux 3/4 car elle ne fonctionne pas toujours.

Par exemple, lorsqu'on renseigne 2 en E2, au lieu de renvoyer le
numéro de
semaine 8, elle renvoie le numéro 4, et pareil lorsqu'on complète avec
7,
8,
9 ou 10 elle renvoie 12 au lieu de 13.

Je me penche dessus pour essayer de comprendre et voir d'oû ça vient.

Merci!

--
géjoun

/Enlever l'année pour m'écrire.../




Je te propose d'essayer cette formule :

=ENT((DATE(AN;MODE(MOIS(E4:AS4))+1;0)-6-JOURSEM(DATE(AN;MODE(MOIS(E4:AS4))+1;0)-6;2)+11-("1/"&ANNEE(DATE(AN;MODE(MOIS(E4:AS4))+1;0)-6+4-JOURSEM(DATE(AN;MODE(MOIS(E4:AS4))+1;0)-6;2))))/7)

Il y a sans doute plus concis..

----------
Ange Ounis
----------

Bonjour,

Dans le ficher ci-joint http://cjoint.com/?mfrfXgsGZH , j'essaie
d'obtenir
en C6 le nombre qui se trouve sur la ligne 2 et qui correspond à la
dernière
semaine du mois le plus présent entre E1 et AS1 (suivant comment
est
complété
E2; par exemple, avec 4 en E2, que ça me renvoie 8; et avec 37 en
E2,
que ça
me renvoie 43).

J'ai essayé de modifier des formules que j'ai trouvé en recherchant
dans les
archives, mais j'arrive qu'à obtenir des #VALEUR

Si quelqu'un pouvait m'aider...

Merci!
























Avatar
Ange Ounis
Oui oui oui, ya un souci :)

D'abord je testais 2008 (va savoir pourquoi !) ce qui fait qu'on n'avait pas les
mêmes nombres de jour en janvier et février (par ex) et ensuite la fonction MODE
n'accepte que 30 arguments (ce qui ne suffit pas puisque tu as 35 cellules
"utiles" à tester).
Donc tout faux et honte sur moi. Désolé de t'avoir fait perdre ton temps :(

----------
Ange Ounis
----------

Bonjour,

Je viens de voir ton message, et là, ya un soucis.

Je viens de retéléchargé mon fichier car je pensais que j'avais pû modifier
quelque chose dessus, et j'ai re-essayé en complétant E2 avec 2, et dans ce
cas, je me retrouve avec janvier qui va du lundi 10 au lundi 31 = 16 jours;
et février du mardi 1 au vendredi 25 = 19 jours; donc (chez moi du moins)
c'est bien février qui est le plus présent entre E4 et AS4, donc, ta formule
devrait me renvoyer 8 à la place de 4 (qui correspond à la dernière semaine
de Janvier).

Et idem pour 7, 8, 9, 10 qui me renvoie 12 au lieu de 13.
Si une autre personne peut vérifier...
J'avoue que j'ai du mal à saisir pourquoi elle marcherait chez toi et pas
chez moi en partant du même fichier... :-(



Avatar
géjoun
C'est pas grave, ça m'a permis de voir le principe de la formule!

--
géjoun

/Enlever l'année pour m'écrire.../



Oui oui oui, ya un souci :)

D'abord je testais 2008 (va savoir pourquoi !) ce qui fait qu'on n'avait pas les
mêmes nombres de jour en janvier et février (par ex) et ensuite la fonction MODE
n'accepte que 30 arguments (ce qui ne suffit pas puisque tu as 35 cellules
"utiles" à tester).
Donc tout faux et honte sur moi. Désolé de t'avoir fait perdre ton temps :(

----------
Ange Ounis
----------


1 2