Filtres, tri, et formules
Bonjour tout le monde.
Je découvre aujourd'hui un problème sur Excel qui me surprend énormément.
Voici un petit tableau
colonne 1 colonne 2
569 =$A$2
962 =A3+$A$2
658 =A4+$A$2
53 =A5+$A$2
45 =A6+$A$2
987 =A7+$A$2
Si je place un filtre sur les 2 colonnes et que je trie donc l'ensemble du tableau en me basant sur les valeurs croissantes de la colonne 1, voici ce que j'obtiens:
colonne 1 colonne 2
45 =A2+$A$2
53 =A3+$A$2
569 =$A$2
658 =A5+$A$2
962 =A6+$A$2
987 =A7+$A$2
Personnellement, je me serais attendu à ce qu'Excel comprennent que la cellule A2 était désormais en A4 pour que les formules restent cohérentes.
Ainsi, si j'insère une ligne sous les entêtes de mes colonnes, voici ce que j'obtiens
colonne 1 colonne 2
569 =$A$3
962 =A4+$A$3
658 =A5+$A$3
53 =A6+$A$3
45 =A7+$A$3
987 =A8+$A$3
Ici, Excel a bien compris que pour que les formules restent cohérentes, il faut que A2 soit transformé en A3.
Quelqu'un saurait-il m'expliquer où se trouve mon erreur de raisonnement ou s'il y a quelque part dans Excel une solution à mon problème de logique?
Merci d'avance pour votre collaboration.
Bonjour,
La réponse est peut-être du coté de l'instruction Indirect()
@ bientôt
LouReeD
Salut LouReeD,
Merci pour ta réponse. J'ai testé ton fichier, mais je ne vois pas ce que cela change au résultat.
Je modifie A4 et passe sa valeur de 658 à 23. La cellule C4 passe à 1010.
Si je reclasse la colonne A par ordre croissant, 23 passe en A2 et C2 passe à 46 alors que l'objectif est d'avoir 1010 en C2...
mais peut-être y-a-t-il quelque chose que je n'ai pas compris dans ta proposition?
Humm,
en fait il suffirait peut-être simplement de supprimer le filtre sur la colonne des formules, non ?
LouReeD
Salut LouReed,
Sur le coup, j'ai bien cru que tu avais trouvé la solution. J'en était même vexé de ne pas l'avoir trouvé par moi-même. Mais en fait, il ne s'agit que d'un contournement du problème, et malheureusement, cette façon de gérer la difficulté ne règle en rien mon problème actuel. En effet, il se trouve que dans le tableau réel sur lequel je travaille, la formule dans la colonne B peut varier d'une ligne à l'autre. Je pense que tu comprendras en voyant mon nouvel exemple en pièce jointe.
Bonjour,
Alors là je reprend depuis le début d'après le dernier fichier joint de votre par.
Nous avons bien en ligne 7 d'Excel :
en colonne A la valeur 45
en colonne B la formule : =$A$2*A7
ce qui veut dire : on prend la valeur de la cellule fixe $A$2 qu'on multiplie par la valeur de la cellule A7
Si on fait un tri du plus petit au plus grand sur la colonne A, la ligne 7 d'Excel passe en ligne 2 (45 étant le plus petit)
et la formule en colonne B en face est bien celle qui avait en ligne 7 mais modifiée du au changement de ligne
formule en ligne 7 : =$A$2*A7
égale bien la même formule en ligne 2 : =$A$2*A2
En effet la référence de la cellule non "verrouillée" A7 "suit" le déplacement de la cellule de référence suite au tri donc A7 se transforme en A2
Alors ma question est : que voulez vous qui se passe (ou ne se passe pas) suite à un tri sur le dernier fichier que vous avez joint.
@ bientôt
LouReeD
En fait, il faudrait que la valeur de chacune des cellules reste inchangée, ligne par ligne, ce qui implique que dans tout le tableau, la valeur $A$2 devienne $A$7 dans toutes les cellulles comme dans la pièce ci-jointe, puisque $A$2 s'est déplacé en $A$7.
Cela me paraîtrait logique selon l'idée que je me faisais de la fonction tri des tableaux Excel.
Jusqu'ici, je pensais en effet que le fait de modifier l'ordre dans une des colonnes ne devait pas modifier la structure ni les valeurs de chacune des lignes. Donc pour moi, les cellules A2 et B2 sont liées, et le fait que la cellule A2 passe en A7 du fait d'un nouveau mode de tri devrait faire que la cellule B2 passe en B7 sans changer de valeur, ce qui implique un changement dans la formule. La valeur de $A$2 s'étant déplacée en $A$7, il faudrait donc que dans la cellule B7 (après nouveau tri), la cellule B7 devienne "=A7+$A$7*5", ce qui ferait que la valeur resterait bien 5922
J'espère être maintenant plus clair...
Alors là je sèche, j'ai du mal à saisir le truc...
Pourriez vous me donner un fichier avec des cellules en format texte avec les valeurs colonnes A, les valeurs colonne B et formules colonne C, puis la même chose en colonne E,F et G avec le résultat escompté après le tri... Merci
Attention, tout en format texte, donc les formules avec un apostrophe devant le "=" et sans mettre de tri sur les colonnes.
@ bientôt
LouReeD
J'ai aussi placé le fichier en pièce jointe. J'espère qu'il est compréhensible.
En gros, lorsque j'effectue le tri, je voudrais que le tableau Excel comprenne que $A$3 est passé en 8ème ligne, même s'il est ponctué du $$.
Bonsoir,
et Ok sans test en visualisant les résultats escompté, je m'aperçois que la référence des cellules sans $ ne changent pas, et que vous souhaitez que malgré le tri, que ce soit toujours $A$3 qui soit prise en référence dans les dîtes formules.
C'est bien un indirect qu'il faut utiliser, non pas sur la première partie des formules mais sur la deuxième.
En effet Indirect revoit le valeur de la cellule indirectement, ce qui fait qu'elle n'est pas sensible à son changement de place
donc :
569 =Indirect("A2")
962 =A3+Indirect("A2")
658 =A4+Indirect("A2")
53 =A5+Indirect("A2")
45 =A6+Indirect("A2")
987 =A7+Indirect("A2")
(fichier joint)
L'autre solution est de nommer la cellule et remplacer Indirect("A3") par le nom de la cellule nommée, en effet les cellules nommées sont insensibles au tris.
@ bientôt
LouReeD
Nous avons souffert un peu, mais le résultat est là! J'ai enfin LA solution à mon problème. je dirais même plus, voilà LES solutions à mon problème. Je me coucherai moins bête ce soir. Merci beaucoup LouReed.
Je marque le problème comme résolu.
Mais de rien ! Ca fait plaisir et me permet d'en apprendre beaucoup !
@ bientôt
LouReeD
Oupss, je crois qu'il y a une erreur dans les formules, non ?
Ca marche ?
@ bientôt
LouReeD
Exact.
J'ai crié victoire un peu rapidement.
Cela ne change rien au problème.
Je suis vraiment surpris que ce problème soit si compliqué à résoudre. Cela dépasse totalement mes capacités en terme de logique...
Bonjour,
en fait la solution est de faire comme dans mon fichier :
créer l'erreur en faisant appel à une colonne qui ne subit pas le tri...
les colonne E prenne en compte les valeurs de la colonne A qui ne sont pas touchées par le tri !!!!
D'une erreur on trouve la solution.... donc un fichier à trois colonne, la première pour les valeurs fixes, la deuxième pour les valeurs filtrable, et la troisième pour les formules dans lesquelles il y a un savoureux mélange des deux autres colonnes....
@ bientôt, je ne joint pas de fichier car en fait il est sur un post précédent
LouReeD