Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'ENREGISTRER
x

or

Comment remplir automatiquement une formule lors de l'insertion de lignes dans Excel?

Lorsque vous insérez des lignes vides entre des lignes de données existantes, la formule ne copiera pas et ne collera pas dans la nouvelle ligne, vous devez la faire glisser ou la remplir manuellement. Mais, parfois, vous souhaitez remplir la formule lorsque vous insérez des lignes vides entre la plage de données automatiquement comme capture d'écran suivante, cet article, je vais parler de quelques astuces pour résoudre cette tâche.

doc autofill formula insertion de ligne 1

Formule de remplissage automatique lors de l'insertion de lignes vides avec la création d'une table

Formule de remplissage automatique lors de l'insertion de lignes vides avec le code VBA


Formule de remplissage automatique lors de l'insertion de lignes vides avec la création d'une table


Dans Excel, le format Table peut vous aider à remplir automatiquement la formule ci-dessus dans les nouvelles lignes vides insérées, procédez comme suit:

1. Sélectionnez la plage de données à remplir automatiquement, puis cliquez sur insérer > table, voir capture d'écran:

doc autofill formula insertion de ligne 2

2. Dans le Créer un tableau dialogue, vérifiez le Ma table contient des en-têtes s'il y a des en-têtes dans vos données, voir capture d'écran:

doc autofill formula insertion de ligne 3

3. Puis clique OK bouton, et la table a été créée, et maintenant, lorsque vous insérez une ligne vide, la formule ci-dessus sera automatiquement insérée dans la nouvelle ligne, voir capture d'écran:

doc autofill formula insertion de ligne 4


Formule de remplissage automatique lors de l'insertion de lignes vides avec le code VBA

Si vous n'aimez pas modifier les données à table, le code VBA ci-dessous peut également vous aider à résoudre ce problème.

1. Sélectionnez l'onglet de la feuille de calcul contenant les formules que vous souhaitez remplir automatiquement, puis faites un clic droit pour choisir Voir le code à partir du menu contextuel pour aller à la Microsoft Visual Basic pour applications fenêtre, puis copiez et collez le code suivant dans le module:

Code VBA: Formule de remplissage automatique lors de l'insertion de lignes vides

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

doc autofill formula insertion de ligne 5

2. Puis enregistrez et fermez ce code, revenez à la feuille de calcul, maintenant, lorsque vous double-cliquez sur une cellule entre les données, une nouvelle ligne sera insérée sous la cellule active et la formule est également remplie automatiquement.


Démonstration: Formule de remplissage automatique lors de l'insertion de lignes vides


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Marcelo Moreira · 4 months ago
    Excellent simple piece of code!!!!
  • To post as a guest, your comment is unpublished.
    Niket Shet · 11 months ago
    Hi,

    This code is very helpful, however, I have a slightly different requirement. The formulas should be copied when the row is inserted manually, i.e. by "insert row".
    Can you please resend the code after incorporating this change i.e. instead of "Worksheet_BeforeDoubleClick", some other trigger point to be used "insert row/etc"

    Note - I do have any exposure to coding being from the Accounts background.


    Regards
    Niket Shet
  • To post as a guest, your comment is unpublished.
    David Banks · 1 years ago
    I am using the code to insert a line on double-click. However, there is something wrong, Excel does not increment the first reference in my formula in the next but one row. However, it is corrected for all remaining rows.
    I inserted a line at row 144 and this is what I got: (note S144 in 3rd line should be S145)
    row 144 =(S143+D144+E144)-(K144+L144+M144+N144+O144+Q144+R144)
    row 145 =(S144+D145+E145)-(K145+L145+M145+N145+O145+Q145+R145)
    row 146 =(S144+D146+E146)-(K146+L146+M146+N146+O146+Q146+R146)
    row 147 =(S146+D147+E147)-(K147+L147+M147+N147+O147+Q147+R147)
    Any clues as to why it does this, I don't think it is the VBA???
    David
  • To post as a guest, your comment is unpublished.
    gt · 2 years ago
    Does the table method work in excel for xp (2002) and if yes, how?
  • To post as a guest, your comment is unpublished.
    RR · 2 years ago
    The code works very well, but does not update cell reference in first row after the inserted row.See below"
    e.g
    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    RR · 2 years ago
    This code is very good, but it does not update the cell reference in the first following row after the inserted row. Can you please help?
    e.g:

    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 2 years ago
    Above formula is very helpful for me please tell me how to use this code permanently
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 2 years ago
    dear above formula which helps to update the above formula on inserting row by double clicking. I want to know how to use this code permanently because when i close the sheet and reopen code finishes. Please help to solve the issue. This formula is very helpful for me
  • To post as a guest, your comment is unpublished.
    Bhavin · 3 years ago
    I followed the VBA code mentioned above for my xls. I have one .xls with multiple sheets in it , i want if i insert a row in my one sheet at the start or any of the sheet for that matter it should auto insert row in all my sheet in the same worksheet. Also each of my sheet as formulas hence i want the moment auto insert takes place the formula of above row should get autofill in the new row which is not happening totally if i follow the VBA code mentioned , the rows are getting inserted in all my sheets the formula also shows up but it misses one row in it.
    Eg. I have sheet A with 100 rows , sheet B with 100 ros and so on with other sheets. If I insert row in sheet A it shows auto insert in Sheet B , but sheet B values of some column are linked with sheet A and the moment i insert a row eg 10 , row 11 is a new row and the same row 11 in sheet B also shows up but the value it should pull from row 11 of sheet A from column C rather it shows row 12 of sheet A , this happens on any row i insert the value in sheet B is always of the next row rather than same row that i inserted. Another problem that comes here is with my first column Sr. no the moment i insert a row in 5 , sr no 6 with new row shows up but prior to that i already had row 6 as i have a formula for Sr. no A6+1 as my column starts from A6 and so on so how to sort this issue with 2 rows with same sr. no. This is very crucial sheet am working on and i need to get this sorted, i have refereed many VBA codes but none is effective. Hoping for a proper solution to my issue.