Inhoud
probleem
Stel dat we zo'n simpele tabel hebben, waarin de bedragen voor elke maand in twee steden worden berekend, en dan wordt het totaal omgerekend naar euro's tegen de koers uit de gele cel J2.
Het probleem is dat als u het bereik D2:D8 met formules ergens anders op het blad kopieert, Microsoft Excel automatisch de koppelingen in deze formules corrigeert, ze naar een nieuwe plaats verplaatst en stopt met tellen:
Taak: kopieer het bereik met formules zodat de formules niet veranderen en hetzelfde blijven, met behoud van de rekenresultaten.
Methode 1. Absolute links
Methode 2: Formules tijdelijk uitschakelen
Om te voorkomen dat formules tijdens het kopiëren veranderen, moet u er (tijdelijk) voor zorgen dat Excel ze niet meer als formules behandelt. Dit kan worden gedaan door het gelijkteken (=) te vervangen door een ander teken dat normaal niet in formules voorkomt, zoals een hekje (#) of een paar ampersands (&&) voor kopieertijd. Voor deze:
- Selecteer het bereik met formules (in ons voorbeeld D2:D8)
- Klik Ctrl + H op het toetsenbord of op een tabblad Home – Zoeken en selecteren – Vervangen (Home — Zoek&Selecteer — Vervang)
- Voer in het dialoogvenster dat verschijnt in wat we zoeken en wat we vervangen door, en in parameters (Opties) vergeet niet te verduidelijken Zoekbereik – Formules. wij drukken op Vervang alles (Vervang alles).
- Kopieer het resulterende bereik met gedeactiveerde formules naar de juiste plaats:
- vervangen # on = terug met hetzelfde venster, waarbij de functionaliteit terugkeert naar formules.
Methode 3: Kopiëren via Kladblok
Deze methode is veel sneller en gemakkelijker.
Druk op de sneltoets Ctrl+Ё of knop Formules weergeven tab formule (Formules — Formules weergeven), om de formulecontrolemodus in te schakelen - in plaats van de resultaten geven de cellen de formules weer waarmee ze zijn berekend:
Kopieer ons assortiment D2:D8 en plak het in de standaard Notitieboek:
Selecteer nu alles wat geplakt is (Ctrl + A), kopieer het opnieuw naar het klembord (Ctrl + C) en plak het op het blad op de plaats die je nodig hebt:
Het blijft alleen om op de knop te drukken Formules weergeven (Toon formules)om Excel terug te zetten naar de normale modus.
Opmerking: deze methode mislukt soms bij complexe tabellen met samengevoegde cellen, maar in de overgrote meerderheid van de gevallen werkt het prima.
Methode 4. Macro
Als je vaak formules moet kopiëren zonder verwijzingen te verschuiven, dan is het zinvol om hiervoor een macro te gebruiken. Druk op sneltoets Alt + F11 of knop Visual Basic tab ontwikkelaar (Ontwikkelaar), voeg een nieuwe module in via het menu Invoegen – Module en kopieer de tekst van deze macro daar:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Selecteer cellen met formules om te kopiëren.", _ "Kopieer formules exact", Default:=Selection.Address, Type := 8) Als copyRange niets is, verlaat dan Sub Set pasteRange = Application.InputBox("Selecteer nu het plakbereik." & vbCrLf & vbCrLf & _ "Het bereik moet even groot zijn als het oorspronkelijke " & vbCrLf & _ " celbereik kopiëren." , "Kopieer formules exact", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopieer en plak bereiken variëren in grootte!", vbExclamatie, "Kopieerfout " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End SubU kunt de knop gebruiken om de macro uit te voeren. Macro's tab ontwikkelaar (Ontwikkelaar — Macro's) of sneltoets Alt + F8. Nadat de macro is uitgevoerd, wordt u gevraagd om het bereik met de originele formules en het invoegbereik te selecteren en worden de formules automatisch gekopieerd:
- Handige weergave van formules en resultaten tegelijkertijd
- Waarom R1C1-referentiestijl nodig is in Excel-formules
- Snel alle cellen vinden met formules
- Tool om exacte formules uit de PLEX-add-on te kopiëren