Discussion :

[eriiic]

Bonjour à tous,

Je suis confronté à un phénomène bizarre sur excel 2010.
Ou bien je ne vois pas ce qui devrait se voir comme le nez au milieu de la figure...

Jusqu'à maintenant je ne me rappelle pas avoir eu des problèmes avec des formules du type :

Code :

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

=DECALER(A2:A6;;COLONNE()-7)

Hors maintenant je récupère une matrice d'une seule cellule au lieu de x lignes (?!?) :

Par contre c'est bon avec d'autres fonctions ou bien l'offset en dur. Même pb pour Ligne()
Dois-je arrêter l'alcool ?
Merci
eric

[78chris]

Bonjour

Je comprends que ce n'est pas logique mais G -7 colonnes c'est en dehors de l'écran...

Edit non, c'est OK, sorry...

Edit 2 : bizarrement COLONNES(A:G)-7 fonctionne

[Menhir]

bizarrement COLONNES(A:G)-7 fonctionne

Perso, j'aurais plutôt essayé (sans certitude) :

Code :

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

COLONNE(G2:G6)

Je pense qu'il nous manques des morceaux du puzzle mais j'ai l'impression que cette formule est une partie d'une fonction matricielle, non ?
Si c'est le cas, peut-être que le fait que COLONNE soit sans paramètre est interprété comme COLONNE(G2).
Du coup, on se retrouve dans la fonction matricielle avec des zones relatives de taille différente et Excel doit un peu s'y emmêler les pinceaux.

[eriiic]

Je pense qu'il nous manques des morceaux du puzzle mais j'ai l'impression que cette formule est une partie d'une fonction matricielle, non ?

La formule ou du moins le résultat à obtenir en lui-même n'a pas d'importance, c'est plus au type de la formule qu'il faut s'attacher.
J'ai simplifié au minimum pour faire apparaitre le pb.

Je pars d'une matrice de x lignes que je décale de x colonnes, je dois obtenir une matrice de toujours x lignes.
Si pour x j'utilise une expression formée à partir de ligne() ou colonne() (sensées me fournir la ligne ou la colonne en cours) je me retrouve avec une matrice d'une ligne au lieu de x lignes.
Toute autre façon d'exprimer x fonctionne correctement.
Le problème est que
=DECALER(A2:A6;;COLONNE()-7)
une fois colonne() évalué devient :
=DECALER(A2:A6;;{7}-7)
et non :
=DECALER(A2:A6;;7-7) qui lui fonctionne
Si cela fonctionnait différemment avant je pense sincèrement que je l'aurai remarqué.
Si tu veux, pour retrouver un fonctionnement normal il faudrait écrire :
=DECALER(A2:A6;;INDEX(COLONNE();1)-7), pas terrible...

bizarrement COLONNES(A:G)-7 fonctionne

oui, colonnes() retourne bien une valeur 'normale', pas une matrice d'une cellule. Comme equiv() et bien d'autres fonctions. Seulement colonnes() et colonne() n'ont pas le même usage.

edit : avez-vous le même soucis, et sur quelle version ? Et surtout est-ce moi qui ait rêvé le fonctionnement antérieur ? Grave mais possible aussi.... :-s

[Menhir]

La formule ou du moins le résultat à obtenir en lui-même n'a pas d'importance, c'est plus au type de la formule qu'il faut s'attacher.

C'est pour ça que j'ai demandé s'il s'agit ou non d'une fonction matricielle.
Vu que utilisé classiquement (voir la formule ci-dessous), la formule initiale fonctionne sans problème (j'ai testé).

Code :

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

1
2
=SOMME(DECALER(A2:A6;;COLONNE()-7))
=SOMME(DECALER(A2:A6;;COLONNE()-6))

[eriiic]

Ok. Non, en validation normale.

Alors de plus en plus étrange
Si je rempli un peu A, ta formule =SOMME(DECALER(A2:A6;;COLONNE()-7)) inscrite en G me retourne bien la somme, en validation normale.
Mais si j'y sélectionne DECALER(A2:A6;;COLONNE()-7) pour l'évaluer avec F9 j'obtiens #VALEUR! au lieu de la matrice attendue.
Je n'y comprend plus rien.

Quelle version as-tu ? Moi c'est 2010
Pour info, par acquis de conscience j'ai rebooté au cas où mais aucun miracle ne s'est pas produit

[Ben_L]

Bonjour à vous,

Si j'ai bien compris, le problème provient de la fonction DECALER et non de LIGNE ou de COLONNE (qui me semblent avoir un comportement normal).

Il est possible de corriger cela en utilisant N(DECALER(<...>)).
Mais sur la raison du problème, pour l'instant je sèche complètement.
En utilisant VBA (fonction Evaluate appliquée à DECALER ou à d'autres fonctions qui renvoient des vecteurs/matrices comme INDIRECT) et en rentrant le résultat dans un Variant, je ne vois aucune particularité pour la fonction DECALER...

Problème intéressant.

[Menhir]

Si j'ai bien compris, le problème provient de la fonction DECALER et non de LIGNE ou de COLONNE (qui me semblent avoir un comportement normal).

Question de point de vue.

Si Eric pouvait confirmer que cette formule est destinée à une formule matricielle, je miserais volontiers un sesterce sur COLONNE() comme fauteur de trouble étant donné que cette fonction ne supporte pas d'être incluse dans une telle formule.
Pour s'en convaincre, il suffit de faire :

Code :

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

=A2:A6*COLONNE(F2:F7)

A valider avec Ctrl+Shift+Entrée et en mettant quelques valeurs dans A2:A6 bien sûr.

Le résultat constaté est le même que A2*COLONNE(F2), ce qui n'est pas celui attendu.

Mon conseil : mettre la fonction COLONNE() dans une autre cellule et récupérer sa valeur avec une référence absolue.
Par exemple, ceci (en matriciel) ne fonctionne pas, quelles que soient les références qu'on peut éventuellement mettre comme paramètres de COLONNE() :

Code :

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

=SOMME(B2:B6*DECALER(A2:A6;0;COLONNE()-7))

Par contre, si on met en G1 la formule =COLONNE(), la formule suivante (matricielle) fonctionne :

Code :

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

=SOMME(B2:B6*DECALER(A2:A6;0;$G$1-7))

CQFD

[eriiic]

C'est surtout l'utilisation de ligne() ou colonne() dans decaler()

Avec F9 sur la barre d'édition de la formule en G2: =DECALER($A$2:$A$6;;COLONNE()-7) je m'attendais à y lire ma matrice décalée de 0 : ={2;3;4;5;6} et je n'y vois qu'un triste ={x}
Ce que j'obtiens (la matrice évaluée) avec un offset calculé avec equiv() par exemple, qui lui retourne un nombre et non pas une matrice d'un élément.

A-priori c'est dû au fait que ligne() et colonne() retournent une matrice, même s'il y a un seul élément.
Cependant un test me montre que la matrice existe bien et est correcte, c'est l'essentiel.
Gênant pour la mise au point des formules mais c'est tout.
J'ai dû le rêver qu'on avait toujours une évaluation complète...

[Ben_L]

J'étais complètement à côté de la plaque :

Ok. Non, en validation normale.

Je suis parti sur des formules non matricielles...

N'ayant pas eu le temps de faire des tests en matriciel, tout ce que je vais dire dans ce post se rapporte à une validation classique des formules.
Ceci étant dit, je trouve intriguant le comportement de la fonction DECALER. Je m'explique :

En général lorsqu'on entre dans une seule cellule une formule qui renvoie une matrice (du type "=A2:A6" ou "={0;1;2;3}"), la valeur affichée est la première de la matrice.
Mais avec DECALER, on obtient le #VALEUR dont Eric parle dans son post 6.

Bon, j'arrête de polluer cette discussion avec ce hors-sujet et je regarderai ça dans mon coin.
Bonne soirée

[eriiic]

Bah, pas tant hors sujet que ça. Il s'agit bien d'une validation normale (qui pour moi veut dire non matricielle)
Au-delà de ça, même sans validation dans l'absolu puisqu'il s'agit de l'évaluation d'une expression par F9 dans la barre de formule.

Comme je l'ai dit, je constate que c'est dû au fait que colonne() retourne une matrice.
Et dans ce cas l'évaluation retourne la valeur de la matrice dont l'index dépend du n° de ligne de la formule.
J'aurais préféré y voir la matrice mais bon, on va faire avec :-)
Decaler() n'y est pour rien et on voit bien la matrice si on utilise d'autres fonctions pour l'offset, comme equiv() par exemple.
Je laisse encore un peu ouvert si d'autres idées se dégagent.

[mjpmjp]

bonjour,




@+JP

[eriiic]

mjpmjp, je n'ai pas dû être assez clair, c'est plutôt ça par F9 qui m'intéresse :

dans la phase de mise au point de formules.
Dans l'évaluation de formule on n'a pas de vision de la matrice elle-même pour decaler()

Pour l'instant un palliatif est de passer par une cellule intermédiaire comme proposé par Menhir,
ou bien de remplacer ...;COLONNE()-7; par ...;INDEX(COLONNE();1)-7; pour 'dématricer'
pour obtenir :

Code :

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

=DECALER($A$2:$A$6;;INDEX(COLONNE();1)-7)

et pouvoir contrôler si la matrice est conforme à ce qui est attendu suite à l'offset.
Je pense qu'on a fait le tour, merci à tous pour les cogitations et les apports :-)