EXCEL : Insérer cellule sans modifier formule

Résolu

Excel
Formule

julien -
gaelle - 14 mars 2017 à 12:29

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

Afficher la suite

Répondre (16) Posez votre question

Répondre (16)

A voir également:

Comment ajouter une ligne sur excel sans modifier formule
Modifier liste déroulante excel - Guide
Formule si et excel - Guide
Modifier dns - Guide
Formule moyenne excel plusieurs colonnes - Guide
Formule somme excel ligne - Guide

16 réponses

Réponse 1 / 16

Meilleure réponse

La meilleure réponse est la réponse qui a été validée par nos équipes.

julien

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 ?

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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

Réponse 2 / 16

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 279

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

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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.

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 279 > Vaucluse Messages postés 27336 Statut Contributeur

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

Skatino Messages postés 26 Statut Membre 4

Ta technique a marché chez moi avec les apostrophes ! merci!

gaelle

trop fort eriiic ! merci

Réponse 3 / 16

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 279

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

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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

francis

bonjour
=A12 + INDIRECT("f" & LIGNE() + 1)
eriiic n'était pas loin !!!
francis

Réponse 4 / 16

ouam90

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 ;-)

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 16

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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? :-)

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 279

Ah mais j'essayais, j'insistais.... ;-)

Réponse 6 / 16

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 317

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

Réponse 7 / 16

rasoa10 Messages postés 924 Statut Membre 272

la solution est des mettre le signe $ :

G12 = $A$12 + $F$12

ça devrait aller :)

Réponse 8 / 16

julien

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 ?

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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

Réponse 9 / 16

Mabelle60 Messages postés 492 Statut Membre 131

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

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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

Réponse 10 / 16

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 317

Réponse 11 / 16

Mabelle60 Messages postés 492 Statut Membre 131

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.

Réponse 12 / 16

francis

bonjour

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

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

francis

Réponse 13 / 16

roroo888

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 :)

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 279

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

Julien

c'est clair ! Déterrage de topic inutile et à côté de la plaque....

Réponse 14 / 16

guy175

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

Réponse 15 / 16

Misterjg70

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)

Réponse 16 / 16

Mabelle60 Messages postés 492 Statut Membre 131

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+

-1

Vaucluse Messages postés 27336 Statut Contributeur 6 441

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

Répondre Posez votre question

Votre réponse

Discussions similaires

Excel écart entre 2 valeurs en valeur absolue

Pascal -
guyguy -

5 réponses

separe entre nom et prenom dans excel

abel_dz -
yasmine -

14 réponses

Excel - Inverse de Concatenate

Ophelie75 -
SPB -

16 réponses

faire afficher "0" à la place de #N/A

sunray2000 -
Nono -

7 réponses

Excel ne veut plus calculer des cellules

Pow59 -
babette -

42 réponses

Excel : cellule bloquée

jessica64 -
DB -

10 réponses

Devenez membre en quelques clics

Connectez-vous simplement avec ceux qui partagent vos intérêts
Suivez vos discussions facilement et obtenez plus de réponses
Mettez en avant votre expertise et aidez les autres membres
Profitez de nombreuses fonctionnalités supplémentaires en vous inscrivant

S'inscrire