Discussion :

[Garex]

Bonjour,

Dans une feuille j'ai un tableau dont une colonne B qui comprend des dates au format JJ/MM/AAAA HH:MM.
J'ai crée une liste de validation dynamique avec la fonction décaler :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=(DECALER('Tableau Source'!$B$2;;;LIGNES(Source);1))

Toutefois je souhaiterais que la liste de validation n'affiche que les années présentes dans cette colonne. J'imagine qu'il faut faire appel à une formule matricielle, mais comment?

Un début de réponse est ici, sur le forum avec la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(LIGNES($8:9)<=NB(1/FREQUENCE(EQUIV(ENT(Col_Arret);ENT(Col_Arret);0);LIGNE(INDIRECT("1:"&LIGNES(Col_Arret)))));MIN(SI(NB.SI(H$7:H8;ENT(Col_Arret))=0;ENT(Col_Arret)));"")

Dont je ne comprends pas le fonctionnement.

Merci pour vos suggestions.

[njhub]

Bonjour Garex,
Essayez:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TEXTE(DECALER('Tableau Source'.$B$2;;;LIGNES(source);1);"AAAA")

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TEXTE(DECALER('Tableau Source'!$B$2;;;LIGNES(source);1);"AAAA")

[Garex]

Bonjoru njhub,

Merci pour l'aide. J'avais toutefois déjà essayé de passer par la mise en forme du texte mais :
Le premier code renvoi l'erreur "Nous avons rencontré une erreur dans cette formule."
Le deuxième code renvoi l'erreur "La source est reconnu comme erroné. Voulez-vous continuer ?" si je clic sur oui, la liste de validation est figé et ne sort aucun résultat.

Merci d'avoir proposé.

[Promethee25]

Bonjour,

Si tes dates+heures se trouvent en A1:A11 par exemple:

Pour les années triées sans doublon:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=PETITE.VALEUR(SI(FREQUENCE(ANNEE($A$2:$A$12);ANNEE($A$2:$A$12))> 0; ANNEE($A$2:$A$12)); LIGNE(INDIRECT("1:" & SOMME(1 * (FREQUENCE(ANNEE($A$2:$A$12);ANNEE($A$2:$A$12)) > 0)))))

Pour les dates triées sans doublon:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=PETITE.VALEUR(SI(FREQUENCE(ENT($A$2:$A$12);ENT($A$2:$A$12))> 0; ENT($A$2:$A$12)); LIGNE(INDIRECT("1:" & SOMME(1 * (FREQUENCE(ENT($A$2:$A$12);ENT($A$2:$A$12)) > 0)))))

Attention, c'est une formule matricielle avec une matrice en sortie donc elle est à appliquer simultanément sur un nombre suffisant de cases en sortie; il y aura des #N/A si tu en sélectionnes trop mais ce n'est pas grave dès lors que tu utilises la formule suivante dans le nom prévu pour la validation de données (si cette matrice de sortie est en F2:F9):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(Feuil1!$F$2:$F$9; 0; 0; NB(Feuil1!$F$2:$F$9))

Edit: si tu préfères une formule matricielle sortant sur une seule case (à étirer sur le nombre de cases nécessaires):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=PETITE.VALEUR(SI(FREQUENCE(ANNEE($A$2:$A$12);ANNEE($A$2:$A$12))> 0; ANNEE($A$2:$A$12)); LIGNE() - LIGNE($F$1))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=PETITE.VALEUR(SI(FREQUENCE(ENT($A$2:$A$12);ENT($A$2:$A$12))> 0; ENT($A$2:$A$12)); LIGNE() - LIGNE($F$1))

[Garex]

Bonjour Promethee25,

Toutes ces formules ne fonctionnent pas chez moi. On est d'accord que ma demande porte sur le faite d'afficher uniquement l'année dans une liste de validation dont la source est au format jj/mm/aaaa hh:mm?

Merci.

[Promethee25]

Rebonjour,

Ces formules donnent ce que tu souhaites (enfin la version pour les années).
Ton message manque de précision: qu'as-tu fait? quel résultat obtiens-tu? valides-tu bien les formules matricielles par Ctrl+Maj+Entrée? Tes cases en entrée sont-elles bien des dates et non du texte?

[Garex]

Bonjour,

Effectivement nos deux messages manquent de précisions. Je comprends maintenant ta manière de procéder par formules matricielles. Toutefois ce n'est pas ce que je souhaite.

Mon objectif est de partir d'une colonne d'un tableau source (au format JJ/MM/AAAA HH:MM) pour faire une liste (le tableau sert de source à la liste) dans laquelle seule l'année serait affichée et par conséquent renvoyée. Il ne faut pas qu'il y est une étape intermédiaire. En effet soit il faut des formules sur un grand nombre de lignes ce qui alourdi le fichier, soit il faut sélectionner une plage plus grande que la matrice pour afficher le plus grand renvoi possible de celle-ci. Se pose alors deux problèmes : 1 - l'apparition des #N/A et 2 - le fait que si une trop grande plage apparait par rapport à la matrice, des données n'apparaitront pas.

En posant la question je me doutais que se serait peut-être pas possible mais jusque là les possibilités d'excel m'ont surpris, j'ai donc demandé "au cas où".

Merci pour ton aide.

[Promethee25]

Bonsoir,

S'agissant d'années, tu ne devrais pas en avoir des milliers donc l'une ou l'autre des solutions devrait convenir au niveau perfs même si effectivement la 1ère est plus efficace car il n'y a qu'une seule matrice à calculer.

Il te suffit de prévoir un onglet à masquer dans lequel tu placerais une matrice suffisamment grande (cent cases?).

Malheureusement cette formule directement dans un nom (dans le gestionnaire de noms) ne fonctionne pas donc passer par des cases dans un onglet semble nécessaire.

[Garex]

Effectivement je vais me baser sur tes formules, il sera toujours temps plus tard d'optimiser.

Merci.