Formule matricielle : forme et critères

[goum]

bonjour,
j'ai quelques soucis pour venir à bout de mon objectif!!!!
voila j'ai un fichier excel avec une base de données.
une formule me permet d'extraire dans un autre onglet toute la ligne dont les données de la colonne A sont supérieur à 6.

1ère question: quand je mets en forme et que je saute des lignes avant les formules, les résultats disparaissent?!! je ne peux pas sauter de lignes avant les formules et pareil pour les données, je dois commencer pour les 2, à la 1ère ligne...
comment puis-je réparer cela??

2ème question: je souhaiterais extraire avec un intervalle, par exemple, toute la ligne dont les données de la colonne A qui sont entre 5 et 6...comment faire??

merci de votre aide en tout cas.

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonjour

Pour ce qui est de ta seconde question, essayes avec :
PETITE.VALEUR(SI((ENT(BDD!$A$1:$A$30/100000)>=$J$1)*(ENT(BDD!$A$1:$A$30/100000)<=$K$1); dans ta formule en A1, en plaçant en J1 ta borne inférieure, et en K1, ta borne supérieure.

@ plus

 

[goum]

Re : Formule matricielle : forme et critères

merci pour l'aide
si quelqu'un peut a une solution...
merci

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonsoir

Est-ce que le fichier ci-joint résoud ton pb lié aux lignes insérées ?

Si besoin est, je peux aussi modifier la formule pour qu'elle s'adapte automatiquement à la longueur de ton fichier réel (au lieu de s'arrêter à la ligne 30 dans tous les cas). A toi de voir...

@ plus

 

[goum]

Re : Formule matricielle : forme et critères

ouiiiiiiiii merci
ce serait parfait, s'il s'adaptait à tout taille de fichier réel :-D
si tu pouvais me faire ça, ce serait incroyable pour moi!!!

après je verrai par rapport à l'insertion de ligne si ça marche : le but est de pouvoir mettre en forme et créer des tableaux sans avoir à toucher à la formule, elle est trop compliquée pour mon niveau mdr
merci beaucoup

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonsoir

Ci-joint la modification proposée. Je met deux fichiers, car je ne suis pas sûr que le premier passe correctement sous excel 2003.

@ plus

 

[goum]

Re : Formule matricielle : forme et critères

c'est magnifique!!!!!
tout marche bien (insertion ligne dans fichier réel et rajout données à la xxxéme ligne)
par contre, c'est étrange car je ne peux pas rajouter de ligne au niveau de l'onglet avec les formules (pour les colonnes c'est possible) et j'ai également essayer de déplacer toutes les cellules d'un coup mais ça ne passe pas non plus (peut être le paramétrage du verrouillage de certaines cellules..??!?)
tu penses pouvoir corriger cela?
en tout cas, merci (les formules ont l'air folles lol)

 

[david84]

Re : Formule matricielle : forme et critères

Bonsoir Goum, Cisco
Cisco, pourrais-tu expliquer les différentes étapes de ta formule car j'avoue avoir du mal à en saisir toute la subtilité, notamment les parties
PETITE.VALEUR(SI(ENT(DECALER(BDD!A$6;;;EQUIV("zz";BDD!B$1:B$1000;1))/100000)
Merci

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonsoir

Et comme ça, est-ce que c'est mieux ?

Si il y a encore un problème, renvoies le fichier avec l'erreur bien en évidence.

@ plus

 

[goum]

Re : Formule matricielle : forme et critères

j'ai oublié l'histoire de l'intervalle!
la formule que tu m'as donné au début n'est plus possible a priori??
sinon c'est magnifiquement bien fait...

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonjour à tous

..., notamment les parties
PETITE.VALEUR(SI(ENT(DECALER(BDD!A$6;;;EQUIV("zz";BDD!B$1:B$1000;1))/100000)

Bonjour David84.

Petite explication de la formule ci-dessus : Goum veut transférer toutes les lignes contenant en colonne A un nombre compris entre 600 000 et 699 999 car la cellule K1 contient le nombre 6.

Pour faire cela :
EQUIV(valeur recherchée;tableau de recherche; type)
EQUIV("zz";BDD!B$1:B$1000;1)) cherche "zz" dans la plage B$1:B$1000.
Si on avait mis 0 comme type, cette formule aurait renvoyé #NA car zz n'est pas dans la plage B$1:B$1000.
Comme on a pris le type 1, cette formule renvoie le numéro de la dernière ligne utile de la colonne B, soit 22 dans le fichier mis en exemple en pièce jointe ci-dessus.

Ainsi, DECALER(BDD!A$6;;;EQUIV("zz";BDD!B$1:B$1000;1)) renvoie la plage B$1:B$22. Grace à cela, les calculs ne seront faits que sur la plage utile B$1:B$22, et pas sur la plage B$1:B$1000 qu'on aurait pu utiliser par défaut. Si goum rajoute des lignes à sa première feuille, cette plage s'allongera automatiquement grace au EQUIV("zz"..., et il ni y aura pas de problème tant qu'il ne dépasse pas la ligne 1000.

En divisant par 100 000 et en prenant la valeur entière avec ENT, la formule ne garde que le chiffre de gauche des nombres de la plage utile. Si il est égal à 6, la test SI renvoie VRAI, sinon c'est FAUX.

PETITE.VALEUR garde en mémoire la liste des contenus des cellules sur les lignes contenant en colonne A un nombre compris entre 600 000 et 699 999, et n'en affiche qu'un, en fonction de la valeur donnée par LIGNES(A$1;...) (attention, ne pas confondre les fonctions LIGNE et LIGNES).

Il a bien sûr d'autres possibilités (cf un exemple en pièce jointe avec 9^9, à rechercher dans une colonne contenant des nombres).

Tout cela donne une formule plutot lourde à cause de cette recherche de la plage utile. Pour l'alléger, on peut toujours passer par un nom défini pour l'occasion.

La formule est plus simple dans mon fichier test(1) bis, mais fonctionne avec la même méthode, pour ce qui est de PETITE.VALEUR..., sans recherche de la plage utile.

En espérant avoir été assez clair

@ plus

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonjour

j'ai oublié l'histoire de l'intervalle!

Moi aussi

Problème résolu, peut être , en pièce jointe, toujours avec la mêm méthode.

@ plus

P.S : Il te faudra peut être modifier les <= dans les formules en fonction de ton besoin exact.

 

[david84]

Re : Formule matricielle : forme et critères

"PETITE.VALEUR garde en mémoire la liste des contenus des cellules sur les lignes contenant en colonne A un nombre compris entre 600 000 et 699 999"

Merci Cisco pour cette brillante démonstration et ces explications.

Si j'ai bien compris le principe, tes explications doivent être modulées par les modifications ultérieures dues à l'ajout de l'intervalle : recherche des nombres compris entre la valeur égale ou sup à la valeur de l'intervalle inf et la valeur inf ou égale à la valeur de l'intervalle sup comprenant tous les nombres commençant par cette valeur (?).

J'étudie tranquillement ta démonstration.
A bientôt
Cordialement

 

[CISCO]

Re : Formule matricielle : forme et critères

Bonjour

Oui David84

@ plus

 

[goum]

Re : Formule matricielle : forme et critères

c'est exactement ça pour l'intervalle!! merci beaucoup
j'ai encore une question (qui est apparue entre-temps)...
dans l'exemple, je voulais tous les nombres qui commencent par 6 (ou avec intervalle entre 5 et 6) mais si je souhaitais avoir une précision à la dizaine (tous les nombres commençant par 61, ou 64, .../ou avec intervalle entre 51 et 61)
au début j'ai pensé que je pouvais mettre 6,1 puisque ma mesure est l'unité mais ça n'a pas marché.
alors j'ai décidé de changer les formules en mettant 10 000 au lieu de 100 000, et ça a marché!!
ma question est de savoir si tu peux faire quelque chose pour ne plus être obligé de modifier l'unité de mesure selon les besoins??

après ça, je promets de ne plus t'embêter
merci de ton aide précieuse...