Changer de Plage de recherche dans la Fonction sans le faire manuellement

Bonjour Axel,

ce que tu demandes n'est pas possible.

c'est en glissant la formule vers la droite, non pas vers le bas,
que B:B peut devenir C:C, D:D, E:E, et ainsi de suite...

sans voir ton fichier, sans savoir quelles sont tes données,
et sans savoir ce que tu veux faire au juste : difficile de
te répondre autre chose.

peut-être que ce que tu veux est possible par formule ?
si oui, la fonction INDIRECT() te servira peut-être ?

peut-être qu'il faudrait utiliser VBA ?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

si tu veux joindre ton fichier : mets-le sur le site de partage de
fichiers cjoint.com, puis mets le lien de téléchargement dans
ton prochain post du forum.

il faudra aussi que tu expliques ce que tu veux faire au juste.

rhodo

Bonjour Rhodo,
Merci de ta réponse :) 
Ci joint voici un petit exemple du fichier à traiter, j'ai essayé de la rendre très visuel pour qu'il soit plus simple à comprendre : https://www.cjoint.com/c/NEhhSqbT5Hc

Objectif voulu : Associer une marque au montant réalisé.
Càd, si en Semaine1 j'ai réaliser 3000 avec produit x, dans la catégorie 1.
J'aimerai en ressortir la Marque associé à ce montant.
Les colonnes à remplir sont dans la colonne N, P et R. 

Au plaisir, 
Axel 

Bonjour Axel,

merci pour avoir transmis ton fichier.  :)

bonne nouvelle : j'ai très bien compris ce que tu veux,
j'ai réussi à faire ton exo, et le fichier est prêt.  :)

rhodo

@Axel

j'ai trouvé une meilleure solution ; le nouveau fichier Excel
est dans mon post du 10 mai à 03:32.

regarde aussi la solution de Chris, post du 9 mai à 11:39.

rhodo

erreur ; ce post peut être supprimé.

Bonjour Axel et Rhodo

Voici une façon de faire :

https://www.cjoint.com/c/NEjjJo20eD0

Il y a quelques explications en fin de tableaux.  Si plus d'explications nécessaires n'hésites pas à me revenir.

Bonne continuation

Chris

Bonjour Axel et Chris,

j'ai amélioré ma solution ; c'est pourquoi j'ai supprimé le lien
de mon fichier précédent ; voici un nouveau lien :

https://www.cjoint.com/c/NEkbFHJPxt0

------------------------------------------------------------------------------------

le petit tableau technique est maintenant en S2:V7 ; plus tard,
tu pourras masquer les colonnes R:V.

* Ctg est l'abréviation de "Catégorie"
* L1 = première ligne de la catégorie
* L2 = dernière ligne de la catégorie
* Adr est l'abréviation de "Adresse"

* formule en T3 : =EQUIV(S3;B$4:B$23;0)+3
tirée vers le bas jusqu'en ligne 7.

* formule en U3 : =T4-1
tirée vers le bas jusqu'en ligne 6.

* en U7 : nombre saisi manuellement : 23
il n'y a donc pas de formule.

* formule en V3 : ="C" & T3 & ":C" & U3
tirée vers le bas jusqu'en ligne 7.

------------------------------------------------------------------------------------

A) exemples de formules pour GRANDE.VALEUR(...)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

a) formules pour Cat 1

* en M3 : =GRANDE.VALEUR(DECALER(INDIRECT(V$3);0;DROITE(K3));1)

* en O3 : =GRANDE.VALEUR(DECALER(INDIRECT(V$3);0;DROITE(K3));2)

* en Q3 : =GRANDE.VALEUR(DECALER(INDIRECT(V$3);0;DROITE(K3));3)

ces 3 formules sont tirées vers le bas jusqu'en ligne 8.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

b) formules pour Cat 2

* en M13 : =GRANDE.VALEUR(DECALER(INDIRECT(V$4);0;DROITE(K3));1)

* en O13 : =GRANDE.VALEUR(DECALER(INDIRECT(V$4);0;DROITE(K3));2)

ces 2 formules sont tirées vers le bas jusqu'en ligne 18.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

c) formules pour Cat 3

* en M23 : =GRANDE.VALEUR(DECALER(INDIRECT(V$5);0;DROITE(K3));1)

* en O23 : =GRANDE.VALEUR(DECALER(INDIRECT(V$5);0;DROITE(K3));2)

* en Q23 : =GRANDE.VALEUR(DECALER(INDIRECT(V$5);0;DROITE(K3));3)

ces 3 formules sont tirées vers le bas jusqu'en ligne 28.

------------------------------------------------------------------------------------

B) exemples de formules pour INDEX(...;EQUIV(...))

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

a) formules pour Cat 1

* en L3 :
=INDEX(INDIRECT(V$3);EQUIV(M3;DECALER(INDIRECT(V$3);0;DROITE(K3));0);1)

* en N3 :
=INDEX(INDIRECT(V$3);EQUIV(O3;DECALER(INDIRECT(V$3);0;DROITE(K3));0);1)

* en P3 :
=INDEX(INDIRECT(V$3);EQUIV(Q3;DECALER(INDIRECT(V$3);0;DROITE(K3));0);1)

ces 3 formules sont tirées vers le bas jusqu'en ligne 8.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

b) formules pour Cat 2

* en L13 :

=INDEX(INDIRECT(V$4);EQUIV(M13;DECALER(INDIRECT(V$4);0;DROITE(K3));0);1)

* en N13 :
=INDEX(INDIRECT(V$4);EQUIV(O13;DECALER(INDIRECT(V$4);0;DROITE(K3));0);1)

ces 2 formules sont tirées vers le bas jusqu'en ligne 18.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

c) formules pour Cat 3

* en L23 :
=INDEX(INDIRECT(V$5);EQUIV(M23;DECALER(INDIRECT(V$5);0;DROITE(K3));0);1)

* en N23 :
=INDEX(INDIRECT(V$5);EQUIV(O23;DECALER(INDIRECT(V$5);0;DROITE(K3));0);1)

* en P23 :
=INDEX(INDIRECT(V$5);EQUIV(Q23;DECALER(INDIRECT(V$5);0;DROITE(K3));0);1)

ces 3 formules sont tirées vers le bas jusqu'en ligne 28.

------------------------------------------------------------------------------------

C) remarques

a) dans toutes les formules, c'est volontairement que j'ai laissé K3 sans adapter
le numéro de ligne, car en colonne K : c'est les mêmes données "S1" à "S6"
pour : K3:K8 ; K13:K18 ; K23:K28 ; K33:K38 ; K43:K48.

avantage : après avoir copié / collé une formule, ça t'évite de devoir adapter K3.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

b) les formules GRANDE.VALEUR(...) n'ont plus besoin d'être conditionnelles
puisqu'elles utilisent la plage adéquate d'une catégorie (selon colonne V "Adr").

avantage : les formules GRANDE.VALEUR(...) peuvent être plus courtes.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

c) une formule avec INDEX(...;EQUIV(...)) qui utilise INDIRECT(...) cherche
uniquement dans les lignes d'une catégorie donnée ; exemple : pour Cat3,
ça cherche uniquement dans les lignes 10 à 15, car en V5, y'a C10:C15.

d) la même formule INDEX(...;EQUIV(...)) qui utilise aussi DECALER(...)
cherche uniquement dans une seule colonne : celle de la semaine qui
est concernée ; donc par exemple pour S5 la colonne H.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

e) en cas d'ex-aequo, il peut y avoir un résultat erroné ; selon les 2 remarques
précédentes c) et d), ça arrivera seulement pour une catégorie donnée et pour
la semaine concernée ; c'est-à-dire que pour les lignes de cette catégorie, si y'a
un ex-aequo dans une autre semaine que la semaine concernée, y'aura pas
d'incidence.

exemple pour Cat2, semaine S5 :

* en M17 : 96 ; ça cherche 96 dans H8:H9 ; en L17 : M2000 ; c'est exact :
c'est bien la marque qui correspond au 1er 96.

* en O17 : 96 ; ça cherche 96 dans H8:H9 ; en N17 : M2000 ; c'est faux ;
ça devrait être M88 : la marque du 2ème 96.

je ne connais pas de moyen d'y remédier ; à moins de faire par VBA
tout le travail de GRANDE.VALEUR(...) ; on pourra savoir le numéro
de ligne du résultat trouvé, puis l'utiliser pour retourner la marque
correcte ; cependant, je crois que "le jeu n'en vaut pas la chandelle".

donc je propose de laisser comme c'est ; il faut juste être conscient
de ce problème, qui survient uniquement en cas d'ex-aequo dans
la semaine concernée d'une catégorie donnée.

j'ai regardé la solution de Chris ; y'a le même problème avec la
très longue formule qui utilise RECHERCHEX(...).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

f) si tu veux ne pas voir les traits horizontaux et verticaux du
quadrillage, c'est inutile de mettre une couleur blanc pour la
couleur d'arrière plan (couleur du fond) : il te suffit de faire
ceci : onglet Affichage, groupe Afficher, ☐ Quadrillage
(la case doit être décochée).

------------------------------------------------------------------------------------

je te laisse vérifier tous les résultats ; si y'en a des erronés,
ça devrait être uniquement pour ce qui a déjà été indiqué
en remarque e).

à te lire pour avoir ton avis (si tu reviens sur ce sujet).  :)

rhodo

Bonjour Rhodo et Chris,
Je suis agréablement ravi, merci beaucoup :)

cela m'a fortement aidé à tout automatiser mon fichier et grace à votre aide je gagne un temps monstre.

Encore merci et bonne continuation :) 
Axel 

Bonjour, 
Un Nouveau PB, je pense une manip simple mais je n'arrive pas à trouver...

J'aimerai que le fichier se divise en deux parties, dans un onglet La base de donnée et de l'autre et de l'autre les formules... 

Avez une idée de la manip à réliser SVP ?
Axel

Bonjour Axel,

je trouve, comme toi, que tu as bien raison de préférer mettre
en 2 feuilles séparées ; c'est fait dans ce fichier joint :

https://www.cjoint.com/c/NEpwWPJIFU0

* la feuille qui contient la base de données s'appelle "Data Base".
* la feuille qui contient les formules s'appelle "Résultats".

toute la suite de ce post est pour "Résultats".

------------------------------------------------------------------------------------------

les données techniques sont maintenant en J2:M10 ;
plus tard, tu pourras masquer les colonnes i:M.

* en J3 : nom de la feuille de la Base de Données : Data Base

* formule en K6 : =EQUIV(J6;INDIRECT("'"&J$3&"'!B4:B23");0)+3
tirée vers le bas jusqu'en ligne 10.

* formule en L6 : =K7-1
tirée vers le bas jusqu'en ligne 9.

* en L10 : nombre saisi manuellement : 23
il n'y a donc pas de formule.

* formule en M6 : ="'"&J$3&"'!C" & K6 & ":C" & L6
tirée vers le bas jusqu'en ligne 10.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SI plus tard tu voudras changer le nom de la 1ère feuille :
fais-le, puis modifie uniquement le texte de J3.

tu n'auras aucun autre changement à faire.  :)

------------------------------------------------------------------------------------------

A) exemples de formules pour GRANDE.VALEUR(...)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

a) formules pour Cat 1

* en D4 : =GRANDE.VALEUR(DECALER(INDIRECT(M$6);0;DROITE(B4));1)

* en F4 : =GRANDE.VALEUR(DECALER(INDIRECT(M$6);0;DROITE(B4));2)

* en H4 : =GRANDE.VALEUR(DECALER(INDIRECT(M$6);0;DROITE(B4));3)

ces 3 formules sont tirées vers le bas jusqu'en ligne 9.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

b) formules pour Cat 2

* en D14 : =GRANDE.VALEUR(DECALER(INDIRECT(M$7);0;DROITE(B4));1)

* en F14 : =GRANDE.VALEUR(DECALER(INDIRECT(M$7);0;DROITE(B4));2)

ces 2 formules sont tirées vers le bas jusqu'en ligne 19.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

c) formules pour Cat 3

* en D24 : =GRANDE.VALEUR(DECALER(INDIRECT(M$8);0;DROITE(B4));1)

* en F24 : =GRANDE.VALEUR(DECALER(INDIRECT(M$8);0;DROITE(B4));2)

* en H24 : =GRANDE.VALEUR(DECALER(INDIRECT(M$8);0;DROITE(B4));3)

ces 3 formules sont tirées vers le bas jusqu'en ligne 29.

------------------------------------------------------------------------------------------

B) exemples de formules pour INDEX(...;EQUIV(...))

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

a) formules pour Cat 1

* en C4 :
=INDEX(INDIRECT(M$6);EQUIV(D4;DECALER(INDIRECT(M$6);0;DROITE(B4));0);1)

* en E4 :
=INDEX(INDIRECT(M$6);EQUIV(F4;DECALER(INDIRECT(M$6);0;DROITE(B4));0);1)

* en G4 :
=INDEX(INDIRECT(M$6);EQUIV(H4;DECALER(INDIRECT(M$6);0;DROITE(B4));0);1)

ces 3 formules sont tirées vers le bas jusqu'en ligne 9.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

b) formules pour Cat 2

* en C14 :
=INDEX(INDIRECT(M$7);EQUIV(D14;DECALER(INDIRECT(M$7);0;DROITE(B4));0);1)

* en E14 :
=INDEX(INDIRECT(M$7);EQUIV(F14;DECALER(INDIRECT(M$7);0;DROITE(B4));0);1)

ces 2 formules sont tirées vers le bas jusqu'en ligne 19.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

c) formules pour Cat 3

* en C24 :
=INDEX(INDIRECT(M$8);EQUIV(D24;DECALER(INDIRECT(M$8);0;DROITE(B4));0);1)

* en E24 :
=INDEX(INDIRECT(M$8);EQUIV(F24;DECALER(INDIRECT(M$8);0;DROITE(B4));0);1)

* en G24 :
=INDEX(INDIRECT(M$8);EQUIV(H24;DECALER(INDIRECT(M$8);0;DROITE(B4));0);1)

ces 3 formules sont tirées vers le bas jusqu'en ligne 29.

------------------------------------------------------------------------------------------

C) remarques

a) dans toutes les formules, c'est volontairement que j'ai laissé B4 sans adapter
le numéro de ligne, car en colonne B : c'est les mêmes données "S1" à "S6"
pour : B4:B9 ; B14:B19 ; B24:B29 ; B34:B39 ; B44:B49.

avantage : après avoir copié / collé une formule, ça t'évite de devoir adapter B4.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

b) les formules GRANDE.VALEUR(...) n'ont plus besoin d'être conditionnelles
puisqu'elles utilisent la plage adéquate d'une catégorie (selon colonne M "Adr").

avantage : les formules GRANDE.VALEUR(...) peuvent être plus courtes.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

c) une formule avec INDEX(...;EQUIV(...)) qui utilise INDIRECT(...) cherche
uniquement dans les lignes d'une catégorie donnée ; exemple : pour Cat3,
ça cherche uniquement dans les lignes 10 à 15 (de la feuille "Data Base")
car en M8, y'a une formule qui retourne : 'Data Base'!C10:C15.

d) la même formule INDEX(...;EQUIV(...)) qui utilise aussi DECALER(...)
cherche uniquement dans une seule colonne : celle de la semaine qui est
concernée ; exemple : pour S5 la colonne H (de la feuille "Data Base").

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

e) en cas d'ex-aequo, il peut y avoir un résultat erroné ; selon les 2 remarques
précédentes c) et d), ça arrivera seulement pour une catégorie donnée et pour
la semaine concernée ; c'est-à-dire que pour les lignes de cette catégorie, si y'a
un ex-aequo dans une autre semaine que la semaine concernée, y'aura pas
d'incidence.

exemple pour Cat2, semaine S5 :

* en D18 : 96 ; ça cherche 96 dans H8:H9 (de la feuille "Data Base") ;
en C18 : M2000 ; c'est exact : c'est bien la marque qui correspond
au 1er 96.

* en F18 : 96 ; ça cherche 96 dans H8:H9 (de la feuille "Data Base") ;
en E18 : M2000 ; c'est faux ; ça devrait être M88 : la marque du
2ème 96.

je ne connais pas de moyen d'y remédier ; à moins de faire par VBA
tout le travail de GRANDE.VALEUR(...) ; on pourra savoir le numéro
de ligne du résultat trouvé, puis l'utiliser pour retourner la marque
correcte ; cependant, je crois que "le jeu n'en vaut pas la chandelle".

donc je propose de laisser comme c'est ; il faut juste être conscient
de ce problème, qui survient uniquement en cas d'ex-aequo dans
la semaine concernée d'une catégorie donnée.

------------------------------------------------------------------------------------------

je te laisse vérifier tous les résultats ; si y'en a des erronés,
ça devrait être uniquement pour ce qui a déjà été indiqué
en remarque e).

rhodo

Bonjour, 
Un grand Merci pour toutes vos réponses.
J'ai de nouveau un PB, quand je veux étendre la formule pour les semaines suivantes (jusqu'à S52 = une année).
A chaque dizaine, ça repart de 0. Càd quand je suis à la S10, le résultat obtenu est "NOMBRE#!" et pour la S11, le résultat sera le même que la S1... :/
Je vous joint le fichier pour que ce soit plus parlant :

https://www.cjoint.com/c/NEur4mdnM0Y

Encore merci 
Bonne fin de soirée
Axel

Bonjour Axel,

je suis bien content que tu apprécies mes solutions.  :)

lien fichier : https://www.cjoint.com/c/NEvb0GI3ob0

rhodo

Un grand merci pour ton aide Rhodo, tu m'as apporté de très bonne solution :)