Fonction recherche sur excel 2007
Résolu/Fermé
Pitive
-
30 nov. 2011 à 17:00
Raymond PENTIER Messages postés 58397 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 26 avril 2024 - 2 déc. 2011 à 04:22
Raymond PENTIER Messages postés 58397 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 26 avril 2024 - 2 déc. 2011 à 04:22
A voir également:
- Fonction recherche sur excel 2007
- Liste déroulante excel - Guide
- Excel fonction si et - Guide
- Formule excel - Guide
- Recherche musique - Guide
- Save as pdf office 2007 - Télécharger - Bureautique
7 réponses
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 396
Modifié par Vaucluse le 30/11/2011 à 17:54
Modifié par Vaucluse le 30/11/2011 à 17:54
Bonsoir
il vaudrait mieux peut être donner une base de fichier pour que les explications soient bien adaptées!!!
Sinon vous allez transpirer, essayez avec ces infos à adapter:
1° sortir une température en fonction d'un coef
Feuille 1
en A liste des T°
en B liste des coef
la formule est, pour ligne 1 en feuille 2 avec le coef en A1, en B1:
=SI(ESTVIDE(A1);"";RECHERCHEV(A1;'Feuille 1'!A:B;2;0)
si vous avez maintenant des tableaux de référence en feuille 3 et 4 et que pour l'édition, vous avez un code OUI qui doit choisir feuille 3, sinon feuille 4:
sur la feuille d'édition:
=A1 coef
B& Code OUI ou rien (ou non, c'est sans importance)
en C1:
=SI(ESTVIDE(A1);"";SI(B1="OUI";RECHERCHEV(A1;'feuille 3'!A:B;2;0);RECHERCHEV(A1;'feuille 4'!A:B;02;0))) essayez de vous en sortir avec ça, sinon revenez, mais avec un modèle, pour que l'on ne s'use pas à vous faire des propositions inadaptées.
Nota: RECHERCHEV(valeur;champ;colonne;0) recherche la valeur exacte dans la 1° colonne d'un champ et ressort celle de la même ligne au N° de colonne indiqué,ici 2° du champ
si cette formule ne se termine pas par ;0), soit:
RECHERCHEV(valeur;champ;colonne)
elle ressort la valeur de la colonne indiquée sur la ligne où se trouve la valeur directement inférieure à celle cherchée dans la 1° colonne.A condition que cette colonne soit classée en ordre croissant.
crdlmnt
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
il vaudrait mieux peut être donner une base de fichier pour que les explications soient bien adaptées!!!
Sinon vous allez transpirer, essayez avec ces infos à adapter:
1° sortir une température en fonction d'un coef
Feuille 1
en A liste des T°
en B liste des coef
la formule est, pour ligne 1 en feuille 2 avec le coef en A1, en B1:
=SI(ESTVIDE(A1);"";RECHERCHEV(A1;'Feuille 1'!A:B;2;0)
si vous avez maintenant des tableaux de référence en feuille 3 et 4 et que pour l'édition, vous avez un code OUI qui doit choisir feuille 3, sinon feuille 4:
sur la feuille d'édition:
=A1 coef
B& Code OUI ou rien (ou non, c'est sans importance)
en C1:
=SI(ESTVIDE(A1);"";SI(B1="OUI";RECHERCHEV(A1;'feuille 3'!A:B;2;0);RECHERCHEV(A1;'feuille 4'!A:B;02;0))) essayez de vous en sortir avec ça, sinon revenez, mais avec un modèle, pour que l'on ne s'use pas à vous faire des propositions inadaptées.
Nota: RECHERCHEV(valeur;champ;colonne;0) recherche la valeur exacte dans la 1° colonne d'un champ et ressort celle de la même ligne au N° de colonne indiqué,ici 2° du champ
si cette formule ne se termine pas par ;0), soit:
RECHERCHEV(valeur;champ;colonne)
elle ressort la valeur de la colonne indiquée sur la ligne où se trouve la valeur directement inférieure à celle cherchée dans la 1° colonne.A condition que cette colonne soit classée en ordre croissant.
crdlmnt
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
Voici mon fichier : http://cjoint.com/?AKEvKJgHsbJ
les cases jaunes sont a remplir manuellement et H8 et H9 ce sont les coef en fonction de divers éléments :
H8 en fonction de E9( le DN) et E8 (nu ou isolé).
Si E8= isolé alors la recherche s'effectue sur la feuille "Coef KT-KTi" colonne E
Si E8= nu alors la recheche s'effectue sur la feuille "Coef KT-KTi" colonne D
La ligne dépend de E9.
Si E9=43/48 alors la recheche s'effectue sur la feuille "Coef KT-KTi" ligne 11
Exemple
E8=nu
E9 = 1"
alors H8 = 1.48
pour la cellule H9, elle dépend de B8, B9, B10 et E8.
Si E8 = nu alors la recherche s'effectue sur la feuille "Coef fCT"
Si E8 = isolé alors la recherche s'effectue sur la feuille "Coef fCTi"
Ensuite si B8= 75 et B9=60 alors c'est la ligne 9 pour le nu et 12 pour l'isolé
en fin ei ti =15 alors c'est la colonne H pour le nu comme pour l'isolé.
Exemple :
B8=50 B9=40 B10=20 et E8 = isolé alors H9=0.41 (feuille coef fCTi céllule F18).
J'espère avoir été clair.
merci
les cases jaunes sont a remplir manuellement et H8 et H9 ce sont les coef en fonction de divers éléments :
H8 en fonction de E9( le DN) et E8 (nu ou isolé).
Si E8= isolé alors la recherche s'effectue sur la feuille "Coef KT-KTi" colonne E
Si E8= nu alors la recheche s'effectue sur la feuille "Coef KT-KTi" colonne D
La ligne dépend de E9.
Si E9=43/48 alors la recheche s'effectue sur la feuille "Coef KT-KTi" ligne 11
Exemple
E8=nu
E9 = 1"
alors H8 = 1.48
pour la cellule H9, elle dépend de B8, B9, B10 et E8.
Si E8 = nu alors la recherche s'effectue sur la feuille "Coef fCT"
Si E8 = isolé alors la recherche s'effectue sur la feuille "Coef fCTi"
Ensuite si B8= 75 et B9=60 alors c'est la ligne 9 pour le nu et 12 pour l'isolé
en fin ei ti =15 alors c'est la colonne H pour le nu comme pour l'isolé.
Exemple :
B8=50 B9=40 B10=20 et E8 = isolé alors H9=0.41 (feuille coef fCTi céllule F18).
J'espère avoir été clair.
merci
Bonjour, merci pour ta réponse. Tu as raison ça me semble bien complique et je transpire déjà. Des que je rentre ce soir, je m'attaque a faire mes tableaux pour vous montrer exactement ce que je souhaite faire et dans quelle case.
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
1 déc. 2011 à 00:59
1 déc. 2011 à 00:59
Bonjour pitive.
Ton dossier étant par nature complexe, toute optimisation est bonne à effectuer.
Ainsi "Ensuite si B8= 75 et B9=60 alors c'est la ligne 9 pour le nu et 12 pour l'isolé" juste parce que tu n'as pas autant de lignes vides au début des 2 feuilles !
Tu en supprimes 3 dans "coef fCTi" et ton explication devient
"Ensuite si B8=75 et B9=60 alors c'est la ligne 9".
Pour les listes déroulantes de cellules, puisque tu as pris la peine de les préparer dans une feuille particulière, donne-leur un nom (commande "définir un nom").
Par exemple
- le nom "état" à la plage 'menu deroulant'!B1:B3
- le nom "diam" à la plage 'menu deroulant'!C1:C14
- le nom "désign" à la plage 'menu deroulant'!E1:E4
et ce sont ces noms que tu utilises comme sources pour les validations de données/liste
...
Ton dossier étant par nature complexe, toute optimisation est bonne à effectuer.
Ainsi "Ensuite si B8= 75 et B9=60 alors c'est la ligne 9 pour le nu et 12 pour l'isolé" juste parce que tu n'as pas autant de lignes vides au début des 2 feuilles !
Tu en supprimes 3 dans "coef fCTi" et ton explication devient
"Ensuite si B8=75 et B9=60 alors c'est la ligne 9".
Pour les listes déroulantes de cellules, puisque tu as pris la peine de les préparer dans une feuille particulière, donne-leur un nom (commande "définir un nom").
Par exemple
- le nom "état" à la plage 'menu deroulant'!B1:B3
- le nom "diam" à la plage 'menu deroulant'!C1:C14
- le nom "désign" à la plage 'menu deroulant'!E1:E4
et ce sont ces noms que tu utilises comme sources pour les validations de données/liste
...
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
1 déc. 2011 à 01:21
1 déc. 2011 à 01:21
...
Dans la feuille "coef kt-kti", on donne le nom "DN" à la plage B5:E18.
Dans la feuille "déper tuyau", on ajoute en F8 la formule =SI(E8="nu";3;4) qui sert à déterminer quelle colonne de "DN" va servir pour la recherche effectuée en cellule H8, avec la formule =RECHERCHEV(E9;DN;F8;0).
https://www.cjoint.com/?ALbbvn2pQuX
Dans la feuille "coef kt-kti", on donne le nom "DN" à la plage B5:E18.
Dans la feuille "déper tuyau", on ajoute en F8 la formule =SI(E8="nu";3;4) qui sert à déterminer quelle colonne de "DN" va servir pour la recherche effectuée en cellule H8, avec la formule =RECHERCHEV(E9;DN;F8;0).
https://www.cjoint.com/?ALbbvn2pQuX
Merci, ce que tu m'a envoyé ca fonctionne, mais j'ai encore 3 questiosns :
1/ Comment donner un nom à une plage ?
2/ Comment 3 ou 4 peuvent déterminer la colonne de DN. D'après la formules c'est une simple conditions, pourquoi on a mis 3 et 4 et pas 1 et 2 par exemple?
3/Peux-tu m'expliquer la formule : =RECHERCHEV(E9;DN;F8;0). pour que je la comprenne.
Par exemple explication de la formule =SI(E8="nu";3;4) :
Si dans la case E8 c'est ecrit nu alors dans la case F8 le 3 apparaitra, sinon c'est 4.
Et si tu peux faire de même avec lec formules :
='Coef fCT'!B6&'Coef fCT'!C6
=SI(F8=3 ; INDEX(fCT;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Ti;2;0)) ;
INDEX(fCTi;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Tii;2;0)))
C'est juste pour que je retienne bien à quoi sert tout les éléments de la formule, pour ne pas vous rembéter si je dois refaire un tableau de ce style, ce qui risque d'arrivé.
Merci encore
1/ Comment donner un nom à une plage ?
2/ Comment 3 ou 4 peuvent déterminer la colonne de DN. D'après la formules c'est une simple conditions, pourquoi on a mis 3 et 4 et pas 1 et 2 par exemple?
3/Peux-tu m'expliquer la formule : =RECHERCHEV(E9;DN;F8;0). pour que je la comprenne.
Par exemple explication de la formule =SI(E8="nu";3;4) :
Si dans la case E8 c'est ecrit nu alors dans la case F8 le 3 apparaitra, sinon c'est 4.
Et si tu peux faire de même avec lec formules :
='Coef fCT'!B6&'Coef fCT'!C6
=SI(F8=3 ; INDEX(fCT;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Ti;2;0)) ;
INDEX(fCTi;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Tii;2;0)))
C'est juste pour que je retienne bien à quoi sert tout les éléments de la formule, pour ne pas vous rembéter si je dois refaire un tableau de ce style, ce qui risque d'arrivé.
Merci encore
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
1 déc. 2011 à 18:42
1 déc. 2011 à 18:42
Salut, pitive ------------------------------- Il faut lire TOUT ce que je t'écris, sinon je perds mon temps, et toi tu ne peux pas suivre mes explications !
1) Je te l'ai indiqué au post #7 : (commande "définir un nom").
Dans la feuille 'menu deroulant' tu sélectionnes la plage B1:B3, tu fais clic droit, tu choisis l'avant-dernière commande "définir un nom" et dans la première petite fenêtre tu tapes le nom "état".
2) Coef Kt/Kti, que tu veux afficher en H8, est à rechercher dans la plage DN ('coef kt-kti'!B5:E18) ; mais si E8="nu", c'est la colonne N°3 de DN qui sera consultée ; si E8="isole", ce sera la colonne N°4. Je n'ai donc nul besoin de mettre 1 ou 2, ni aucune autre paire de valeurs, puisque je veux le N° de colonne pour ma recherche.
3) Fonction SI
=SI(test_logique;valeur_si_vrai;valeur_si_faux)
Excel effectue le test_logique (comparaison de 2 valeurs) ; si la comparaison est exacte, la donnée valeur_si_vrai sera affichée ; sinon, ce sera la donnée valeur_si_faux.
Dans notre exemple, le test logique consiste à regarder si le contenu de la cellule F8 est égal au nombre 3 ; si c'est le cas, la première recherche s'effectue avec la fonction INDEX dans la plage fCT ; sinon, une seconde recherche avec INDEX est lancée, mais dans la plage fCTi.
...
1) Je te l'ai indiqué au post #7 : (commande "définir un nom").
Dans la feuille 'menu deroulant' tu sélectionnes la plage B1:B3, tu fais clic droit, tu choisis l'avant-dernière commande "définir un nom" et dans la première petite fenêtre tu tapes le nom "état".
2) Coef Kt/Kti, que tu veux afficher en H8, est à rechercher dans la plage DN ('coef kt-kti'!B5:E18) ; mais si E8="nu", c'est la colonne N°3 de DN qui sera consultée ; si E8="isole", ce sera la colonne N°4. Je n'ai donc nul besoin de mettre 1 ou 2, ni aucune autre paire de valeurs, puisque je veux le N° de colonne pour ma recherche.
3) Fonction SI
=SI(test_logique;valeur_si_vrai;valeur_si_faux)
Excel effectue le test_logique (comparaison de 2 valeurs) ; si la comparaison est exacte, la donnée valeur_si_vrai sera affichée ; sinon, ce sera la donnée valeur_si_faux.
Dans notre exemple, le test logique consiste à regarder si le contenu de la cellule F8 est égal au nombre 3 ; si c'est le cas, la première recherche s'effectue avec la fonction INDEX dans la plage fCT ; sinon, une seconde recherche avec INDEX est lancée, mais dans la plage fCTi.
...
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
1 déc. 2011 à 20:12
1 déc. 2011 à 20:12
...
3) (suite)
=RECHERCHEV(valeur_cherchée, table_matrice, no_index_col, [valeur_proche])
=RECHERCHEV(E9;DN;F8;0) demande à Excel de se baser sur la valeur de E9 (valeur_cherchée) pour regarder dans la plage DN (table_matrice) ce qui se trouve à la colonne F8 de cette plage (no_index_col) ; et on met comme dernier argument (valeur_proche) le chiffre 0 (ou le mot FAUX) pour que la recherche s'opère même si la première colonne de la plage n'est pas triée.
='Coef fCT'!B6&'Coef fCT'!C6 permet juste d'accoller (concaténer) les 2 valeurs contenues dans B6 et C6. Dans la plage TaTr, cellule G2, cette formule récupère la donnée "90" contenue dans la cellule 'Coef fCT'!B6 et lui accolle la donnée "70" contenue dans la cellule 'Coef fCT'!C6 pour donner comme résultat "9070".
=INDEX(fCT;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Ti;2;0))
=INDEX(matrice;no_lig;no_col) affiche la valeur contenue à l'intersection de la ligne "no_lig" et de la colonne "no_col" de la plage "matrice".
Ici, la matrice est fCT, la ligne est calculée par la première fonction RECHERCHEV (valeur B11, matrice TaTr) , la colonne par la seconde fonction RECHERCHEV (valeur B10, matrice Ti).
Voilà : tu as toutes les infos voulues (que tu aurais, par ailleurs, pu lire dans l'Aide d'Excel).
3) (suite)
=RECHERCHEV(valeur_cherchée, table_matrice, no_index_col, [valeur_proche])
=RECHERCHEV(E9;DN;F8;0) demande à Excel de se baser sur la valeur de E9 (valeur_cherchée) pour regarder dans la plage DN (table_matrice) ce qui se trouve à la colonne F8 de cette plage (no_index_col) ; et on met comme dernier argument (valeur_proche) le chiffre 0 (ou le mot FAUX) pour que la recherche s'opère même si la première colonne de la plage n'est pas triée.
='Coef fCT'!B6&'Coef fCT'!C6 permet juste d'accoller (concaténer) les 2 valeurs contenues dans B6 et C6. Dans la plage TaTr, cellule G2, cette formule récupère la donnée "90" contenue dans la cellule 'Coef fCT'!B6 et lui accolle la donnée "70" contenue dans la cellule 'Coef fCT'!C6 pour donner comme résultat "9070".
=INDEX(fCT;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Ti;2;0))
=INDEX(matrice;no_lig;no_col) affiche la valeur contenue à l'intersection de la ligne "no_lig" et de la colonne "no_col" de la plage "matrice".
Ici, la matrice est fCT, la ligne est calculée par la première fonction RECHERCHEV (valeur B11, matrice TaTr) , la colonne par la seconde fonction RECHERCHEV (valeur B10, matrice Ti).
Voilà : tu as toutes les infos voulues (que tu aurais, par ailleurs, pu lire dans l'Aide d'Excel).
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
1 déc. 2011 à 02:13
1 déc. 2011 à 02:13
...
Pour la recherche des coefficients, on va donner
- le nom "fCT" à la plage 'Coef fCT'!D6:L15
- le nom "fCTi" à la plage 'Coef fCTi'!D6:L15
et pour déterminer la ligne et la colonne de recherche (fonction INDEX) dans l'une ou l'autre plage, on crée dans la feuille "menu deroulant" trois nouvelles plages :
- "TaTr" en G1:H11 avec la formule ='Coef fCT'!B6&'Coef fCT'!C6 en G2 (à recopier vers le bas) et les chiffres 1, 2, 3 ...en colonne H (pour fixer la ligne)
- "Ti" en J1:K10 avec les valeurs de Ti (pour fixer la colonne de "fCT")
- "Tii" en M1:N10 avec les valeurs de Ti (pour fixer la colonne de "fCTi").
Pour exploiter la plage "TaTr", on aura besoin de concaténer les valeurs de Ta et Tr, avec en B11 la formule =B8&B9.
En H9 la formule sera (attention aux points-virgules et aux parenthèses) :
=SI(F8=3 ; INDEX(fCT;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Ti;2;0)) ;
INDEX(fCTi;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Tii;2;0)))
Et ça marche ... https://www.cjoint.com/?ALbcmHRvOSH
Pour la recherche des coefficients, on va donner
- le nom "fCT" à la plage 'Coef fCT'!D6:L15
- le nom "fCTi" à la plage 'Coef fCTi'!D6:L15
et pour déterminer la ligne et la colonne de recherche (fonction INDEX) dans l'une ou l'autre plage, on crée dans la feuille "menu deroulant" trois nouvelles plages :
- "TaTr" en G1:H11 avec la formule ='Coef fCT'!B6&'Coef fCT'!C6 en G2 (à recopier vers le bas) et les chiffres 1, 2, 3 ...en colonne H (pour fixer la ligne)
- "Ti" en J1:K10 avec les valeurs de Ti (pour fixer la colonne de "fCT")
- "Tii" en M1:N10 avec les valeurs de Ti (pour fixer la colonne de "fCTi").
Pour exploiter la plage "TaTr", on aura besoin de concaténer les valeurs de Ta et Tr, avec en B11 la formule =B8&B9.
En H9 la formule sera (attention aux points-virgules et aux parenthèses) :
=SI(F8=3 ; INDEX(fCT;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Ti;2;0)) ;
INDEX(fCTi;RECHERCHEV(B11;TaTr;2;0);RECHERCHEV(B10;Tii;2;0)))
Et ça marche ... https://www.cjoint.com/?ALbcmHRvOSH
Raymond PENTIER
Messages postés
58397
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
26 avril 2024
17 094
1 déc. 2011 à 20:34
1 déc. 2011 à 20:34
Mise au point : ça ne m'a pas "embêté" de te renseigner, mais il faut aussi que tu t'habitues à pratiquer un minimum d'auto-formation, ce qui est particulièrement facile avec les accompagnements que te propose le logiciel :
- L'Aide d'Excel, en appuyant sur la touche F1 ou sur le point d'interrogation en haut à droite de l'écran.
- Les info-bulles, qui s'affichent dès que tu restes 2 secondes au-dessus d'une icône de la barre d'outils.
- Les commandes appropriées à chaque cellule dans laquelle tu fais un clic-droit.
- Les explications détaillées sur la syntaxe et le fonctionnement de toute fonction, sitôt que tu cliques sur cette fonction dans l'info-bulle (après sélection dans la barre de formule).
- L'Assistant de saisie d'une formule, avec Formules/Insérer une fonction.
Tu seras ainsi plus autonome.
Cordialement.
- L'Aide d'Excel, en appuyant sur la touche F1 ou sur le point d'interrogation en haut à droite de l'écran.
- Les info-bulles, qui s'affichent dès que tu restes 2 secondes au-dessus d'une icône de la barre d'outils.
- Les commandes appropriées à chaque cellule dans laquelle tu fais un clic-droit.
- Les explications détaillées sur la syntaxe et le fonctionnement de toute fonction, sitôt que tu cliques sur cette fonction dans l'info-bulle (après sélection dans la barre de formule).
- L'Assistant de saisie d'une formule, avec Formules/Insérer une fonction.
Tu seras ainsi plus autonome.
Cordialement.
30 nov. 2011 à 17:42
30 nov. 2011 à 21:57
Merci