EXCEL : Insérer cellule sans modifier formule [Résolu/Fermé]

Signaler
-
 gaelle -
Bonjour,

Je voudrais savoir s'il est possible de faire en sorte sur EXCEL (v2000) que l'insertion de cellules n'ait pas d'impact sur les formules.

Ex :

G12 = A12 + F12

Actuellement, si j'insère une cellule en F12, alors G12 devient :
G12 = A12 + F13

Moi, je souhaiterai que G12 reste :
G12 = A12 + F12

Je n'ai pas trouvé la solution, merci de me dire si elle existe.

Merci d'avance pour votre aide,

Julien

16 réponses

Non,

le signe $ permet seulement de ne pas décaler lorsque l'on copie la formule dans une autre cellule, mais dans mon cas, le $ ne change rien !

Même avec le $, si j'insère une cellule en F12, la formule deviendra :

G12 = $A$12 + $F$13

Fais l'essai et tu verras.

D'autres idées ?
7
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 60769 internautes nous ont dit merci ce mois-ci

Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
Bonjour

Je ne crois pas qu'il y ai de solution!
bloqué ou non, effectivement les codes changent.... sauf si on insére les cellules ou les lignes en dessous de celles concernées par la formule
Par exemple ici sur F13

Crdlmnt
Messages postés
23506
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
29 septembre 2020
6 374
Bonjour vaucluse et les autres,

Pour ma solution aucun lien avec l'insertion au-dessus, j'aurai tout aussi bien pu utiliser A1 ou Z65000 comme cellule intermédiaire et également ne pas en utiliser si j'inscris la ref de la cellulle dans indirect().
Ce qui donne en G12 :
=A12 + indirect("F12")
Quelles que soient les insertions/suppressions sur la feuille indirect("F12") pointera toujours sur F12

Pas besoin de macro, ni de suspendre les calculs qui se mettront à jour dès qu'on les réactivera (et on sera bien obligé de le faire un jour ou l'autre...)
eric
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
D'accord Eric

Sauf sur la fin...tu ne peux pas avoir un résultat de A2 +F12 en mettant la formule A2+INDIRECT(F12) puisque INDIRECT attend une référence que tu ne peux pas mettre en F12.(résultat:#REF)
Il faut nécessairement passer par une cellule intermèdiaire.

Non?

A part ce point ayant juste pour but de te taquiner un peu, la solution est excellente,bien qu'elle demande une organistaion spéciale des données. et ceci quelque soit effectivement la position de la cellule de transfert

Encore bravo, mais ce sera tout pour cette fois :-)

Amicalement

Crdlmnt

Ps: il n'empêche qu'il aurait été sympa de la part d'Excel de céer une option sur le sujet.
Messages postés
23506
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
29 septembre 2020
6 374 >
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020

re tout le monde,

je n'ai pas écris A12+INDIRECT(F12) mais....
=A12+INDIRECT("F12") avec les " "
ce qui est vraiment différent. Je passe une reférence texte à indirect()

eric
Messages postés
25
Date d'inscription
samedi 29 juin 2013
Statut
Membre
Dernière intervention
12 juillet 2016
5
Ta technique a marché chez moi avec les apostrophes ! merci!
trop fort eriiic ! merci
Messages postés
23506
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
29 septembre 2020
6 374
Bonjour,

C'est possible en utilisant l'indirection.
Saisir dans une cellule (par exemple en F1) la reference texte de la cellule voulue, c'est à dire F12.
en G12:
=A12+indirect(F1)

eric
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
Salut Eric
..solution maline qui utilise le fait que la formule, comme dit plus haut, n'incrémente pas les cellules au dessus de l'insertion. Une autre ruse
Bravo
A+ Crdlmnt
bonjour
=A12 + INDIRECT("f" & LIGNE() + 1)
eriiic n'était pas loin !!!
francis
Pour ceux que ça intéresse encore une solution simple est de d'abord remplacer tous les '=' du tableau et les remplacer par exemple par '..' (Ctrl F).

Du coup on peut aisement ensuite copier coller le tableau sans que les formules changent car non comprises par Excel comme formules.

Ensuite on refait la manip inverse à savoir remplacer les '..' par des ''=''.

Bye ;-)
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
Il a encore raison, le bougre.
Salut, je n'ai plus rien à dire je crois que c'est là LA solution.
PS: t'aurais pu le dire tout de suite, tu l'as fait exprès? :-)
Messages postés
23506
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
29 septembre 2020
6 374
Ah mais j'essayais, j'insistais.... ;-)
Messages postés
826
Date d'inscription
mardi 20 mai 2008
Statut
Membre
Dernière intervention
12 janvier 2018
258
la solution est des mettre le signe $ :
G12 = $A$12 + $F$12 

ça devrait aller :)
Tout à fait d'accord avec Vaucluse, j'avais déjà essayé cette option, ça décale malgré tout, la seule action, c'est que ça ne fait pas le calcul en direct mais quand on le demande : c'est donc encore pire.

Je suis dégouté, il doit bien y avoir une solution quand même....

Je cherche mais ne trouve pas...

Quelqu'un d'autre ?
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
Re
Au moins on est d'accord. Je viens d'essayer une petite combine (c'est le jour des ruses) qui pourra peut être vous arranger:
J'ai rentré votre formule en G12
J'ai coupé une cellule vide sur F11
Je suis venu sur F12: Clic droit / Insérer cellule coupée / Décaler vers le bas et là, au miracle =A12+F12 est bien resté sans changement.
En conclusion la formule se décale quand on insère une cellule, mais ne bouge pas lorsqu'on insère une cellule vide coupée (attention: coupée, pas copiée)
Crdlmnt
Messages postés
16218
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
30 septembre 2020
3 037
Bonjour tout le monde,

3 petites questions:

1/cette opération d'insertion se fait elle dans une zone bien déterminée de la feuille?
2/ cette insertion fait elle toujours décaler vers le bas ?
3/ sur une seule cellule?

l'idée étant une macro...

Michel
Messages postés
469
Date d'inscription
mercredi 4 avril 2007
Statut
Membre
Dernière intervention
21 juin 2017
122
Bonjour
Je en saurais vous dire pourquoi, mais ça fonctionne bien chez moi (attention formules simples (somme et recherchev) je n'ai pas essayé avec une formule complexe)

D5 je met 1
E5 je met 2
F5 je met =somme(D5:E5)

Ensuite je fait Outil\Option\Onglet 'Calcul'\Cocher 'Sur Ordre'

je me place en d3, je fait Clic droit/Insérer/Décaler les cellules vers le haut ou le bas

F9 pour ordonner le calcul et j'ai mon résultat conforme.

Désolé de ne pouvoir apporter plus
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
Re Mabelle:
Saur erreur ou incompréhension de ma part:
On ne peut pas vous donner tort, mais avec votre démonstration, si vous trouvez 3 en F5 après insertion de D3 en D5, cela prouve bien que la formule est devnue D6+E5 puisque votre D5 est devenu, lui, égal à 0.
Passer par l'option est inutile dans ce cas
Non?
Crdlmnt
Messages postés
16218
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
30 septembre 2020
3 037
Bonjour tout le monde,

3 petites questions:

1/cette opération d'insertion se fait elle dans une zone bien déterminée de la feuille?
2/ cette insertion fait elle toujours décaler vers le bas ?
3/ sur une seule cellule?

l'idée étant une macro...

Michel
Messages postés
469
Date d'inscription
mercredi 4 avril 2007
Statut
Membre
Dernière intervention
21 juin 2017
122
non non Vaucluse
ni erreur ni incompréhension. Juste manque d'explication de ma part
Ma somme trouve bien 3 en premier lieu puis 2 lorsque j'insère vers le bas.
bonjour

=A12 + INDIRECT("f" & LIGNE() + 1)

et là on peut insérer sans que ça modifie la formule !!!

francis
Bonjour Francis,

Je suis très intéressé par votre dernière formule, néanmoins je ne comprend pas tous les éléments de cette formule et j'aimerais un supplément d'informations !

Merci bien :)
Messages postés
23506
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
29 septembre 2020
6 374
Bonjour,

Tu sais, il a répondu un peu n'importe quoi à une question posée 2 ans auparavant qui était :
comment insérer une ligne sans que les références d'une formule change ?
Il a transformé la réponse en écrivant d'une façon plus compliquée =A12+F13 (car sa formule revient à ça), le ligne() inséré annule justement le indirect()....
Démarre plutôt une nouvelle discussion en exposant ton problème
eric
c'est clair ! Déterrage de topic inutile et à côté de la plaque....
Examinez la création des références nommées, surtouts si ces références sont des plages.
EX: Une plage nommée Test = F1:F15, G12 =A12+Test. l'insertion d'une cellule en F12 ne changera pas la formule en G12
bonjour, j'ai trouvé une solution qui fonctionne, et qui est toute simple :
ce que je voulais faire, c'était aussi recopier une ligne complète de formules, sans changer les références.
donc en copiant la ligne 1 dans laquelle mes formules étaient par exemple cellule A1 = Feuil2!A1 , cellule B1 = Feuil2!B1 etc ...
je voulais qu'en collant en ligne 2, ma formule reste la même :
Cellule A1 = Feuil2!A1 , cellule B1 = Feuil2!B1 etc ...
J'ai tout essayé avant, sans que ça marche !! j'ai perdu quelques cheveux, tant pis ;o)
Et j'ai trouvé une solution :
Je crée tout d'abord une nouvelle feuille que j'appelle "Formule",
je copie toutes les formules de ma ligne 1 (feuil1) en supprimant le "="
comme ça, j'ai simplement du texte.
Ensuite, quand je veux dupliquer ma ligne en conservant les références de cellules,
je copie la ligne de ma feuille "Formule",
je me place sur la feuille 1 : cellule A2, et je colle toute la ligne de texte.
mes formules sont donc les bonnes, mais comme il n'y a pas le signe "=", ça ne fonctionne pas !
Donc je surligne la ligne complète,
je fais "Ctrl + H" pour ouvrir "remplacer"
et là, j'indique :
remplacer : Feuil2!
par : =Feuil2!
remplacer tout, et le tour est joué.
j'utilise cette technique pour un tableau client, qui me renvoie sur chaque feuille que je créée pour chaque client.
donc je crée déjà la feuille du client, en faisant un copié collé de mon tableau modèle, puis je remplace Feuil2! par =Nomduclient!

et ça marche ;o)
il y a peut-être plus simple, mais je n'ai trop le temps de chercher ;o)
Messages postés
469
Date d'inscription
mercredi 4 avril 2007
Statut
Membre
Dernière intervention
21 juin 2017
122
Bonjour
En plaçant l'option de calcul 'sur odre', on a la possibilité de décaler des cellules sans toucher a la formule.

Outil\Option\Onglet 'Calcul'\Cocher 'Sur Ordre'

A+
Messages postés
25328
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
30 septembre 2020
5 504
Bonjour Mabelle
En êtes vous sur(e), ça ne marche pas comme ça chez moi! On garde le résultat du calcul, mais la formule à changé!A mon avis, c'est encore pire
Crdlmnt