Leer hoe u formules kopieert naar meerdere cellen, een kolom omlaag, naar niet-aangrenzende cellen, formules kopieert met absolute of gemengde celverwijzingen, enz.
Het kopiëren van formules is een van de meest voorkomende en gemakkelijkste taken die u uitvoert in een typische spreadsheet die voornamelijk op formules is gebaseerd. In plaats van steeds dezelfde formule in Excel te typen, kunt u eenvoudig een formule van de ene cel naar meerdere cellen kopiëren en plakken.
Nadat u een formule in Excel hebt geschreven, kunt u de kopieer- en plakopdrachten gebruiken voor meerdere cellen, meerdere niet-aangrenzende cellen of hele kolommen. Als je het niet goed doet, krijg je die vreselijke # REF- en /DIV0-fouten. In dit artikel laten we u de verschillende methoden zien die u kunt gebruiken om formules in Excel te kopiëren.
Formules kopiëren en plakken in Excel
Microsoft Excel biedt verschillende manieren om formules met relatieve celverwijzingen, absolute celverwijzingen of gemengde verwijzingen te kopiëren.
- Formule kopiëren van de ene cel naar de andere
- Kopieer formule één cel naar meerdere cellen
- Formule kopiëren naar de hele kolom
- Formule kopiëren zonder opmaak
- Formules kopiëren naar niet-aangrenzende cellen
- Formules kopiëren zonder celverwijzingen te wijzigen
Een formule kopiëren van de ene cel naar de andere in Excel
Soms wil je misschien een formule van de ene cel naar de andere kopiëren in Excel om te voorkomen dat je de hele formule opnieuw typt en wat tijd bespaart terwijl je dat doet.
Laten we zeggen dat we deze tabel hebben:
Er zijn maar weinig methoden om formules van de ene cel naar de andere te kopiëren.
Selecteer eerst de cel met de formule en klik met de rechtermuisknop, en selecteer in het contextmenu 'Kopiëren' om de formule te kopiëren. Of u kunt de optie 'Kopiëren' gebruiken in het gedeelte 'Klembord' van het tabblad 'Home'.
Maar je kopieert ook formules door simpelweg op de sneltoets te drukken Ctrl + C. Dit is een efficiëntere en tijdbesparende methode.
Dan gaan we naar de cel die we willen plakken, druk op de snelkoppeling Ctrl + V om de formule te plakken. Of klik met de rechtermuisknop op de cel die u wilt plakken en klik op de opties onder 'Plakopties': ofwel de eenvoudige optie 'Plakken (P)' of plakken als 'Formule (F)'-optie.
Als alternatief kunt u ook op 'Plakken speciaal' klikken onder de zes plakpictogrammen om het dialoogvenster 'Plakken speciaal' te openen. Hier heb je verschillende opties, waaronder de zes plakopties uit het contextmenu. Selecteer 'Alle' of 'Formules' onder het gedeelte Plakken en klik op 'OK'.
Nu zou de cel met de geplakte formule dezelfde formules moeten hebben (als die in de gekopieerde cel) maar met de verschillende celverwijzingen. Het celadres wordt door Excel zelf aangepast om overeen te komen met het rijnummer van de geplakte cel.
Formule kopiëren van één cel naar meerdere cellen
Dezelfde plakbewerking werkt precies hetzelfde als we meerdere cellen of een reeks cellen selecteren.
Selecteer de cel met de formule en druk op Ctrl + C om de formule te kopiëren. Selecteer vervolgens alle cellen waarin u de formule wilt plakken en druk op Ctrl + V om de formule te plakken of gebruik een van de bovenstaande plakmethoden om de formule te plakken (zoals we deden voor de enkele cel).
Formule kopiëren naar een hele kolom of rij
In Excel kunt u snel een formule naar een hele kolom of rij kopiëren.
Om een formule naar een kolom of rij te kopiëren, voert u eerst een formule in een cel in. Selecteer vervolgens de formulecel (D1) en beweeg uw cursor over een klein groen vierkantje in de rechterbenedenhoek van de cel. Terwijl u zweeft, verandert de cursor in een zwart plusteken (+), dat de vulgreep wordt genoemd. Klik en houd die vulgreep vast en sleep deze in elke gewenste richting (kolom of rij) over de cellen om de formule te kopiëren.
Wanneer u een formule naar een celbereik kopieert, worden celverwijzingen van de formule automatisch aangepast op basis van de relatieve locatie van rijen en kolommen en voert de formule berekeningen uit op basis van de waarden in die celverwijzingen (zie hieronder).
In het bovenstaande voorbeeld, wanneer de formule in D1 (=A1*B1)/2) gekopieerd wordt naar cel D2, verandert de relatieve verwijzing op basis van de locatie (=A2*B2)/2) enzovoort.
Op dezelfde manier kunt u de formule naar aangrenzende cellen naar links, naar rechts of naar boven slepen.
Een andere manier om de formule naar een hele kolom te kopiëren, is door te dubbelklikken op de vulgreep in plaats van deze te slepen. Wanneer u dubbelklikt op de vulgreep, wordt de formule onmiddellijk toegepast voor zover er gegevens zijn in de aangrenzende cel.
Kopieer een formule naar een bereik zonder opmaak te kopiëren
Wanneer u een formule naar een celbereik kopieert met de vulgreep, wordt ook de opmaak van de broncel gekopieerd, zoals de kleur van het lettertype of de achtergrondkleur, valuta, percentage, tijd, enz. (zoals hieronder weergegeven).
Om te voorkomen dat de celopmaak wordt gekopieerd, sleept u de vulgreep en klikt u op 'Opties voor automatisch vullen' in de rechterbenedenhoek van de laatste cel. Selecteer vervolgens in het vervolgkeuzemenu 'Vullen zonder opmaak'.
Het resultaat:
Kopieer een Excel-formule met alleen getalopmaak
Als u de formule wilt kopiëren met alleen de formule en de opmaak zoals percentagenotatie, decimale punten, enz.
Kopieer de formule en selecteer alle cellen waarnaar u de formule wilt kopiëren. Klik op het tabblad 'Home' op de pijl onder de knop 'Plakken' op het lint. Klik vervolgens op het pictogram 'Formules en getalopmaak' (het pictogram met % fx) in de vervolgkeuzelijst om alleen de formule en de getalopmaak te plakken.
Deze optie kopieert alleen formule- en getalopmaak, maar negeert alle andere celopmaak zoals achtergrondkleur, letterkleur, enz.
Een formule kopiëren naar niet-aangrenzende/niet-aangrenzende cellen
Als u een formule naar niet-aangrenzende cellen of niet-aangrenzende bereiken wilt kopiëren, kunt u dat doen met behulp van Ctrl sleutel.
Selecteer de cel met de formule en druk op Ctrl + C om het te kopiëren. Selecteer vervolgens niet-aangrenzende cellen/bereiken terwijl u de ingedrukt houdt Ctrl sleutel. Druk vervolgens op Ctrl + V om de formule te plakken en op . te drukken Binnenkomen vervolledigen.
Formules kopiëren zonder celverwijzingen in Excel te wijzigen
Wanneer een formule naar een andere cel wordt gekopieerd, wijzigt Excel automatisch de celverwijzingen zodat ze overeenkomen met de nieuwe locatie. Deze celverwijzingen gebruiken de relatieve locatie van een celadres, daarom worden ze relatieve celverwijzingen genoemd (zonder $). Als u bijvoorbeeld de formule '=A1*B1' in cel C1 heeft en u kopieert deze formule naar cel C2, verandert de formule in '=A2*B2'. Alle methoden die we hierboven hebben besproken, gebruiken relatieve verwijzingen.
Wanneer u een formule kopieert met relatieve celverwijzingen, worden de verwijzingen automatisch gewijzigd zodat de formule verwijst naar de overeenkomstige rijen en kolommen. Als u absolute verwijzingen in een formule gebruikt, wordt dezelfde formule gekopieerd zonder de celverwijzingen te wijzigen.
Wanneer u een dollarteken ($) voor de kolomletter en het rijnummer van een cel plaatst (bijvoorbeeld $A$1), verandert de cel in een absolute cel. Het maakt niet uit waar u de formule kopieert die de absolute celverwijzing bevat, de formule zal dat nooit doen. Maar als je een relatieve of gemengde celverwijzing in een formule hebt, gebruik dan een van de volgende methoden om te kopiëren zonder de celverwijzingen te wijzigen.
Kopieer formule met absolute celverwijzing met behulp van de kopieer-plakmethode
Soms moet u de exacte formule in de kolom kopiëren/toepassen, zonder de celverwijzingen te wijzigen. Als u een exacte formule met absolute verwijzing wilt kopiëren of verplaatsen, doet u dit:
Selecteer eerst de cel met de formule die u wilt kopiëren. Klik vervolgens op de formulebalk, selecteer de formule met de muis en druk op Ctrl + C om het te kopiëren. Als u de formule wilt verplaatsen, drukt u op Ctrl + X om het te snijden. Druk vervolgens op de Esc toets om de formulebalk te verlaten.
Of selecteer de cel met de formule en druk op F2 toets of dubbelklik op de cel. Hierdoor wordt de geselecteerde cel in de bewerkingsmodus gezet. Selecteer vervolgens de formule in de cel en druk op Ctrl + C om de formule in de cel als tekst te kopiëren.
Selecteer vervolgens de bestemmingscel en druk op Ctrl + V om de formule te plakken.
Nu wordt de exacte formule gekopieerd naar de doelcel zonder enige wijziging in de celverwijzing.
Formules kopiëren met absolute of gemengde celverwijzingen
Als u exacte formules wilt verplaatsen of kopiëren zonder celverwijzingen te wijzigen, moet u de relatieve celverwijzingen wijzigen in absolute verwijzingen. Als u bijvoorbeeld het teken ($) toevoegt aan relatieve celverwijzing (B1), wordt het een absolute verwijzing ($ B$1), zodat het statisch blijft, ongeacht waar de formule wordt gekopieerd of verplaatst.
Maar soms moet u mogelijk gemengde celverwijzingen ($ B1 of B $ 1) gebruiken door een dollarteken ($) voor de kolomletter of het rijnummer toe te voegen om een rij of een kolom op zijn plaats te vergrendelen.
Laten we het uitleggen met een voorbeeld. Stel dat u deze tabel heeft die de maandelijkse besparingen berekent door elke maand huur (B9) af te trekken van de inkomsten (in kolom B).
In het onderstaande voorbeeld gebruikt de formule een absolute celverwijzing ($ B $ 9) om het huurbedrag te vergrendelen naar cel B9, en een relatieve celverwijzing naar cel B2, omdat deze voor elke rij moet worden aangepast om elke maand overeen te komen. B9 is een absolute celverwijzing ($ B $ 9) omdat u hetzelfde huurbedrag wilt aftrekken van de inkomsten van elke maand.
Stel dat u de saldi van kolom C naar kolom E wilt verplaatsen. Als u de formule kopieert (met de gebruikelijke kopieer-/plakmethode) uit cel C2 (=B2-$B$9), verandert deze in =D2-$B$9 wanneer deze wordt geplakt in cel E2, waardoor je berekeningen helemaal verkeerd zijn!
Wijzig in dat geval de relatieve celverwijzing (B2) in een gemengde celverwijzing ($B2) door het teken '$' toe te voegen voor de kolomletter van de formule die in cel C2 is ingevoerd.
En als u nu de formule kopieert of verplaatst van cel C2 naar E2, of een andere cel, en de formule in de kolom toepast, blijft de kolomverwijzing hetzelfde terwijl het rijnummer voor elke cel wordt aangepast.
Kopieer Excel-formules plakken zonder verwijzingen te wijzigen met Kladblok
U kunt elke formule in uw Excel-spreadsheet bekijken door de opties voor Formule weergeven te gebruiken. Ga hiervoor naar het tabblad ‘Formules’ en selecteer ‘Toon formules’.
U kunt ook naar de formuleweergavemodus gaan door op de Ctrl + ` snelkoppeling, die elke formule in uw werkblad weergeeft. U vindt de grave-accenttoets (`) in de linkerbovenhoek van uw toetsenbord op de rij met de cijfertoetsen (onder de ESC toets en vóór de cijfertoets 1).
Selecteer alle cellen met de formules die u wilt kopiëren en druk op Ctrl + C om ze te kopiëren, of Ctrl + X om ze te snijden. Open vervolgens Kladblok en druk op Ctrl + V om de formules in het kladblok te plakken.
Selecteer vervolgens de formule en kopieer (Ctrl + C) het uit het kladblok en plak het (Ctrl + V) in de cel waar u de exacte formule wilt kopiëren. U kunt ze één voor één of allemaal tegelijk kopiëren en plakken.
Nadat u de formules hebt geplakt, schakelt u de weergavemodus voor formules uit door op . te drukken Ctrl + ` of ga opnieuw naar ‘Formules’ -> ‘Toon formules’.
Kopieer de exacte formules met behulp van Zoeken en vervangen in Excel
Als u een reeks Exact-formules wilt kopiëren, kunt u hiervoor ook de tool Zoeken en vervangen van Excel gebruiken.
Selecteer alle cellen met de formules die u wilt kopiëren. Ga vervolgens naar het tabblad 'Home', klik op 'Zoeken en selecteren' in de groep Bewerken en selecteer de optie 'Vervangen', of druk gewoon op Ctrl + H om het dialoogvenster Zoeken en vervangen te openen.
Voer in het dialoogvenster Zoeken en vervangen het gelijkteken (=) in het veld 'Zoeken naar' in. Voer in het veld 'Vervangen door' een symbool of teken in dat nog geen deel uitmaakt van uw formules, zoals #, of , enz. Klik vervolgens op de knop 'Alles vervangen'.
U krijgt een prompt berichtvenster met de tekst 'We hebben 6 vervangingen gemaakt' (omdat we 6 cellen met formules hebben geselecteerd). Klik vervolgens op 'OK' en 'Sluiten' om beide dialoogvensters te sluiten. Als u dit doet, worden alle tekens die gelijk zijn aan (=) vervangen door hekjes (#) en worden formules omgezet in tekenreeksen. Nu worden de celverwijzingen van de formules niet gewijzigd wanneer ze worden gekopieerd.
Nu kunt u deze cellen selecteren, druk op Ctrl + C om ze te kopiëren en in de doelcellen te plakken met Ctrl + V.
Ten slotte moet u de (#) tekens weer veranderen in (=) tekens. Selecteer hiervoor beide bereiken (oorspronkelijk en gekopieerd bereik) en druk op Ctrl + H om het dialoogvenster Zoeken en vervangen te openen. Typ deze keer het hekje (#) in het veld 'Zoeken naar' en gelijk aan (=) in het veld 'Vervangen door' en klik op de knop 'Alles vervangen'. Klik op 'Sluiten' om het dialoogvenster te sluiten.
Nu worden de tekstreeksen terug geconverteerd naar de formules en krijgt u dit resultaat:
Gedaan!