Créer une liste, fonction décaler

Hello le forum

Je pêche sur une copie qui en soit à l'air facile à mettre en place mais pour laquelle je bute.

J'ai une matrice de données et je souhaite récupérer tous les prénoms dans le même ordre par rapport à 2 critères: récupérer les prénoms uniquement du lundi et ne pas copier les vides.

Au début j'ai pensé tout bêtement utiliser ceci :

=SI(B2="lundi";F2;"")

et étendre la formule.

Le problème est que ca me copie les vides.

J'ai aussi pensé à faire un TCD et copier les données mais ca va me supprimer les prénoms en doublons

Je pense qu'il faut donc que j'utilise la fonction decaler avec index equiv avec pour valeur de ref

=INDEX(B:F;EQUIV("lundi";B:B;0);5)

, mais je ne m'en sors pas.

89liste.xlsx (112.67 Ko)

Bonjour,

Ceci semble fonctionner selon ta demande : =SI(ET(B2="lundi";F2<>"");F2;"")

ric

J'y avais aussi pensé, mais quand j'étends ma formule, ca m'affiche une cellule vide tout de même. Ca ne me met pas la prochaine cellule non vide

Bonjour

Un essai à tester. Te convient-il ?

Bye !

104liste-v1.xlsm (119.73 Ko)

Hello je préfère les formules aux macro, mais j'accepte volontiers.

Ca marche bien pour les cellules vides mais ta macro va me faire comme un TCD. Les prénoms en doublons seront considérées comme une seule valeur. Par conséquent, il me manque 11 prénoms.

Hello je préfère les formules aux macro

Malheureusement, ta demande n'est pas possible ici avec uniquement une formule car requiert une boucle, avec un TDC peut-être en revanche.

Ne connaissant pas bien les dicos, je laisse gmb modifier son code pour que cela te convienne

A + !

Baboutz

Bonjour,

pour simuler des boucles sur des feuilles Excel il y a les formules matricielles...

C'est en toute simplification le résumé de l'utilisation de celles-ci.

Formule à mettre en H2 de votre fichier :

=INDEX($F$2:$F$1700;PETITE.VALEUR(SI(($B$2:$B$1700="lundi")*($F$2:$F$1700<>"");LIGNE(INDIRECT("1:"&LIGNES($F$2:$F$1700))));LIGNES($1:1)))

à valider avec la combinaison des 3 touches suivantes : [Ctrl]+ [Alt] [Shift] +[Entrée] (corrigé après information de non fonctionnement, et pour cause ! )

Normalement des accolades vont apparaître de chaque coté de la formules, il suffira alors de l'incrémenter vers le bas avec la poignée de recopie automatique.

@ bientôt

LouReeD

Bonjour LouReeD,

Je ne savais pas du tout, merci ! Je vais me renseigner sur tout ça tient.

J'ai voulu essayer ta formule, copié/collé en H2 mais lorsque j'effectue la combinaison de touche [Ctrl]+[Alt]+[Entrée], aucune accolade apparaît et rien ne se passe...

Ai-je loupé une étape ou mal procédé ?

oups !

[Shift]+[Ctrl]+[Enter] !

Désolé !

@ bientôt

LouReeD

Les prénoms en doublons seront considérées comme une seule valeur. Par conséquent, il me manque 11 prénoms.

Si tu veux les doublons, qu'à cela ne tienne.

Nouvelle version;

Bye !

13liste-v2.xlsm (118.22 Ko)

Merci Loureed et gmd

Vos 2 méthodes fonctionnent.

Je pense que je vais privilégier la macro car si moi je suis à l'aise avec l'utilisation des matricielles ce n'est pas le cas de mes collaborateurs.

La macro est plus simple à appliquer au final

Merci de vos remerciements !

Moi qui était content d'avoir trouver une formule... Mais cela m'a permis de mieux appréhender ce type de formules.

Mais ce n'est pas encore ça !

Le plus de la formule c'est sa mise à jour lors du chandement d'une donnée. Son inconvénient c'est qu'il faut ajuster le nombre de recopies...

@ bientôt

LouReeD

Oui et la macro je vais devoir l'adapter à mon fichier réel et non ce bout de fichier.

Si ca se trouve je vais privilégier la formule que je comprends mieux que le code VBA

En tout cas j'étais persuadé que je ne pouvais pas faire autrement qu'avec DECALER.

Mais je n'aurais jamais été capable de créer ta formule

Génial Loureed.

J'ai pris la macro pour mon premier fichier, mais j'ai du créer un fichier bilan avec une recréation d'une autre liste à partir d'une plage et fonction d'un critère différent.

J'ai réussit sans problème à réadapter ta formule pour que ca fonctionne.

Je dois avouer que je ne comprends pas trop comment elle fonctionne mais ca marche nickel.

voila tout ça pour dire que ta recherche n'aura pas été vaine

Bonsoir,

une tentative d'explication :

=INDEX($F$2:$F$1700;PETITE.VALEUR(SI(($B$2:$B$1700="lundi")*($F$2:$F$1700<>"");LIGNE(INDIRECT("1:"&LIGNES($F$2:$F$1700))));LIGNES($1:1)))

Petite.valeur renvoie la kième petite valeur NUMERIQUE d'une matrice.

Le premier argument est la liste de la série, le deuxième la position kième de la petite valeur de la série voulue.

Vous ai-je perdu ?

le premier argument se trouve être une condition SI, avec comme test logique de condition une multiplication de deux égalités.

Sous Excel, une égalité "vrai" équivaut à 1 et une fausse équivaut à 0, donc si l'on fait le produit de ces égalités, le résultat vaut 1 si tout est à 1 donc tout est Vrai, et vaut 0 si au moins une égalité est fausse.

Dans la fonction SI un test logique = à 1 vaut VRAI et = à 0 vaut FAUX

Donc dans votre cas si les deux conditions sont vrai le SI = VRAI alors c'est la "double" fonction LIGNE qui fonctionne

sinon c'est la simple.

Pour comprendre la suite il faut faire la différence entre ligne(référence) et ligneS(Matrice).

La première renvoie le numéro de ligne de la feuille Excel de la référence. Exemple ligne(b32) renvoie 32 même si la fonction ligne est écrite dans une formule de la ligne 10. En l'absence de référence elle renvoie le numéro de ligne où elle est inscrite.

La deuxième renvoie le NOMBRE de lignes de la matrice, c'est à dire que si la plage fournie en paramètre comporte 13 ligne alors la fonction renvoie 13 : Lignes(A3:A15) renvoie 13 !

Une petite touche de INDIRECT afin de créer des références qui ne seront pas liées au numéro de ligne Excel lors de la recopie de la formule, et surtout afin d'avoir des référence matricielle en fonction de la condition SI.

La formule matricielle c'est comme une boucle sous VBA si je puis dire et cette boucle est interne à la cellule :

on a une matrice qui va de $F$2:$F$1700, le fait de valider en matricielle, le résultat sur une ligne correspondrait au premier tour de boucle, la ligne suivante au deuxième etc...

donc si on prend la première ligne la fonction SI donnerait cela si on devait l'écrire :

SI((B2="lundi")*(F2<>"");LIGNE(INDIRECT("1:"&LIGNES(F2))))

si((1)*(1);ligne(indirect("1:2")))

si(1;ligne(A1:A2))

si(vrai;ligne(1))

= 1

donc PETITE.VALEUR a en premier paramètre 1 et en paramètre k il a

Lignes($1:1) qui renvoie 1

(Le deuxième 1 lui n'ayant pas de "$" va s'incrémenter avec la recopie de la formule, nous le verrons plus loin)

Donc dans la liste de valeurs (1) PETITE.VALEUR doit renvoyer celle qui est la "1ième" donc 1.

La fonction INDEX nous renvoie donc la valeur en ligne 1 de sa matrice colonne F, qui elle commence en ligne 2 de la feuille Excel,

ce qui fait qu'INDEX renvoie bien la valeur de la cellule F2 soit Rodrigue !

Pour le test suivant, supprimons Selim de la liste, que va nous renvoyer la formule ?

Comme je vous l'ai dit le matriciel c'est comme une boucle.

Donc on passe à la deuxième ligne, la fonction SI donnerait cela si on devait l'écrire :

SI((B3="lundi")*(F3<>"");LIGNE(INDIRECT("1:"&LIGNES(F3))))

si((1)*(0);ligne(indirect("1:3")))

si(0;ligne(A1:A3))

si(faux;ligne(1))

= FAUX

donc PETITE.VALEUR a en premier paramètre et deuxième place de la matrice la valeur FAUX et en paramètre k il a

Lignes($1:2) qui renvoie 2 (l'incrémentation due à la recopie)

Donc petite valeur devrait renvoyer la deuxième petite valeur de sa matrice, hors dans l'explication nous venons de voir que la deuxième valeur vaut FAUX, elle n'est donc pas prise en compte !!!

C'est là qu'intervient le coté "matricielle" de la formule :

il faut s'imaginer que le tableau va vers le bas sur 1700 lignes, mais qu'à chaque cellule où se trouve la formule, c'est lignes vont également en profondeur de ces cellules, nous entrons dans une troisième dimension !

Du coup en étant "terre à terre" il devrait y avoir une erreur, mais comme dans la cellule on a bien une matrice avec 1700 données, lorsque l'on demande la deuxième donnée numérique de la matrice petite.valeur, alors Excel nous renvoi l'équivalent de la troisième ligne "en 2D" du tableau !!!

C'est comme si qu'à chaque ligne où la formule est inscrite, cette même formule faisait une boucle en elle même sur le tableau !

J'ai beau me relire, c'est difficile à faire passer comme message. Pour plus de clarté, faites un tour sur "Formules" puis "Evaluer la formule" et vous verrez ceci écrit pour la ligne 3 d'Excel, qui prend en référence la ligne 2 de la matrice sur laquelle on a supprimé Selim pour l'exemple :

PETITE.VALEUR({1;FAUX;3....];LIGNES($1:2))

k = 2 car ligneS renvoie le nombre de ligne de la matrice

et entre les accolades on a une image de la troisième dimension du tableau qui se trouve dans une seule cellule, et en fonction des différents tests du SI il y aura inscrit le numéro de ligne où le test est bon, ou bien FAUX quand le test n'est pas bon, comme le paramètre k ne prend que les numériques, la deuxième petite.valeur est bien 3, donc index renvoie la valeur de sa troisième ligne en colonne F qui est Loic ! CQFD !

Allez bon courage pour la compréhension, comme je disais, j'ai encore du mal avec cette gymnastique, rien ne vaut l'entrainement et grâce à vous j'en ai eu!

@ bientôt

LouReeD

Franchement merci et bravo pour la temps passé à la pédagogie de la formule.

J'ai tout lu, mais j'avoue pas toujours tout compris

Une chose est sure, je n'arrive pas à comprendre comment on peut imaginer tout ça!

Ca montre bien l'écart entre une pratique intermédiaire et une pratique à haut niveau d'excel

Rechercher des sujets similaires à "creer liste fonction decaler"