Formule matricielle que je n'arrive pas à faire fonctionner

[Manusurf]

Bonjour à tous,

Je suis Emmanuel de Montpellier...et voici la formule que je n'arrive pas à faire fonctionner..
1ere onglet (Base de données) = Des colonnes avec des données.
2ème onglet (Fiche terrain) = Je rentre dans la cellule B1 un n° de local (Correpondant à la colonne C du 1er onglet)

J'aimerai que toutes les cellules grisées se remplissent. Notamment celles en face de type de matériaux, il faudrait que tous les matériaux (de la colonne I du 1er onglet) viennent se mettent les uns sous les autres en fonction du local rentré dans la cellule B1.

Je vous ai mis la formule qui doit fonctionner normalement sur laquelle je me suis cassé les dents pour être poli...
Si vous avez une autre piste je suis skieur...

La bises à tous...je vous souhaite d'excellente fêtes de fin d'année.
Emmanuel de Montpellier.

 

[Softmama]

Re : Formule matricielle que je n'arrive pas à faire fonctionner

Bonjour Manusurf,

Je suis Softmama de Neuilly, et j'ai pas plus pu faire fonctionner ta formule matricielle.

Par contre, avec de simples Index/Equiv, on s'en sort. Pour la commodité du truc, j'ai nommé dynamiquement la plage Libellé et Batiment (Insertion/Nom/Définir). Regarde comment je les ai construites pour pouvoir les reproduire sur les autres champs.

Ton fichier joint en retour avec juste le boulot fait pour les champs libellé et Bâtiment donc.

 

[Modeste]

Re : Formule matricielle que je n'arrive pas à faire fonctionner

Bonjour Emmanuel de Montpellier, Softmama de Neuilly,

La formule matricielle pourrait être utilisée pour obtenir les résultats en colonne D ... bien que l'exemple de formule fournie me laisse ... sans voix

A tester en D1 (si j'ai bien compris!!):

=INDEX('BASE DE DONNEES'!$I$4:$I$1000;PETITE.VALEUR(SI('BASE DE DONNEES'!$C$4:$C$1000=$B$1;LIGNE('BASE DE DONNEES'!$P$4:$P$1000)-3);LIGNES($1:1)))

Valider avec Ctrl+Shift+Enter

 

[Gorfael]

Re : Formule matricielle que je n'arrive pas à faire fonctionner

Salut Manusurf et le forum
Ouais, ouais pas sûr de comprendre.
Les bases d'excel :
- Une formule (matricielle ou non) donne un unique résultat affiché dans la cellule qui la contient ! Sinon, il faut passer par les macros Sub.

FICHE TERRAIN :
B2 :

=INDEX('BASE DE DONNEES'!$A$4:$G$15;EQUIV($B$1;'BASE DE DONNEES'!$C$4:$C$15;0);2)

Formule simple, à adapter pour chaque résultat voulu en modifiant la colonne de retour (ici, 2 correspondant au bâtiment).
Petite remarque anodine : il est préférable de faire correspondre l'intitulé des colonnes à celui de la recherche, ça permet d'automatiser).

Pour les matériaux, il faut une infinité de résultats en fonction d'une seule donnée (B1).
Dans ta formule, tu introduit Petite.Valeur : C'est bien, mais il faut pouvoir éliminer 0 => J'utilise Grande.Valeur
D1 :

{=SI(GRANDE.VALEUR(($B$1='BASE DE DONNEES'!$C$4:$C$15)*LIGNE('BASE DE DONNEES'!$C$4:$C$15);LIGNE())=0;"";INDIRECT("'BASE DE DONNEES'!I"&GRANDE.VALEUR(($B$1='BASE DE DONNEES'!$C$4:$C$15)*LIGNE('BASE DE DONNEES'!$C$4:$C$15);LIGNE())))}

Attention : C'est une formule matricielle validée par <Ctrl>+<Shift>+<Enter>.
Comme je suis toujours aussi feignant, il suffit de la recopier de d1 à D12 (eh ouais, on n'a que douze lignes de données)
Si tu ne comprends pas, demandes des explications !
A+

 

[Manusurf]

Re : Formule matricielle que je n'arrive pas à faire fonctionner

Bonjour à vous 2,

C'est super ca marche...

Par contre GORFAEL décidemment "petite.valeur" et "grande.valeur" j'ai toujours pas compris à quoi ca sert exactement et la différence entre les 2...

Si tu peux me mettre sur la piste je t'en remercie par avance...

Bonnes fêtes et encore merci

 

[Modeste]

Re : Formule matricielle que je n'arrive pas à faire fonctionner

Bonsoir,

Rien que pour faire "bisquer le dragon", une proposition avec PETITE.VALEUR, mais sans les messages d'erreurs ... même au-delà de la douzième ligne:

=SI(NB.SI('BASE DE DONNEES'!$C$4:$C$30;$B$1)>=LIGNES($1:1);INDEX('BASE DE DONNEES'!$I$4:$I$1000;PETITE.VALEUR(SI('BASE DE DONNEES'!$C$4:$C$1000=$B$1;LIGNE('BASE DE DONNEES'!$P$4:$P$1000)-3);LIGNES($1:1)));"")

Joyeuses fêtes

 

[Gorfael]

Re : Formule matricielle que je n'arrive pas à faire fonctionner

Salut Manusurf et le forum
Petite.valeur et Grande.Valeur on une fonction similaire : retourner plusieurs valeurs numériques en gardant le même "corps", et en changeant seulement le rang. Comme ça ne fonctionne que sur des données numériques (à ma connaissance), il faut chercher un nombre dans une plage, soit en croissant. de la plus petite (petite.valeur(Plage;1) à la plus grande (petite.valeur(Plage;X) avec X étant le rang le plus élevé des valeurs soit l'inverse avec Grande.Valeur()
exemple :
Avec les valeurs 5, 12, 8, 10, sur A1:A4 on aura
Petite.Valeur($A$1:$A$4;1)=5 / Petite.Valeur($A$1:$A$4;2)=8 / Petite.Valeur($A$1:$A$4;3)=10 / Petite.Valeur($A$1:$A$4;4)=12
Grande.Valeur($A$1:$A$4;1)=12 / Grande.Valeur($A$1:$A$4;2)=10 / Grande.Valeur($A$1:$A$4;3)=8 / Grande.Valeur($A$1:$A$4;4)=5

Comme j'ai du mal à chiffrer "Marteau" ou "Tournevis" (et Excel aussi), je recherche la ligne la plus élevée qui dans C4:C15 de base de donnée contient B1 de REC.
(Valeur=plage)*(ligne(plage)) Comme c'est du matricielle, ça revient à écrire une nouvelle plage de Valeur :
[(B1=C1)*ligne(C1)] ; [(B1=C2)*(ligne(C2)] ; ... ; [(B1=C14)*Ligne(C14)] ; [(B1=C15)*Ligne(C15)]
(B1=C10)*Ligne(C10)]
=> Ligne (C10) = 10
(B1=C10)
Si C10 =B1 la condition est vraie et comme elle est dans une multiplication, Excel retourne 1
Si C10<>B1 Excel retourne 0
Comme 0×A=0 et 1×A=A, on va avoir les numéros de ligne contenant B1 et 0 pour toutes les autres. en admettant que seules C5, C7 et C12 sont égales à B1, on se retrouve avec la plage de valeurs :
(0×1)0;(0×2)0;(0×3)0;0;(1×5=)5;0;7;0;0;0;0;12;0;0;0
En utilisant les Petite.valeur(), je me retrouve confronté du rang 1 au rang 12 à 0, puis rang 13=>5, puis 7, puis 12 Et ligne 0, Excel est un peu frileux ^^. Donc, j'utilise une formule, en prenant Grande.Valeur :
Si(Formule=0;""; indirect("I" & formule)) ce qui va me donner
I12, I7, I5,"","","","","","","","","","","","",

La formule de modeste permet sans doute d'éliminer les 0 dans les petite.valeur, mais comme ce qui me semble important c'est d'avoir tous les matériaux, j'ai pas cherché plus avant.
Bon réveillon
A+