Formules kopiëren met Automatisch aanvullen in Excel 2016 - dummies

Als u slechts één formule in Excel 2016 hoeft te kopiëren, gebruikt u de functie Automatisch aanvullen of de opdrachten Kopiëren en Plakken. Dit type formulekopie, hoewel vrij gebruikelijk, kan niet worden gedaan met slepen en neerzetten.

Vergeet de optie Totalen in de tool Snelle analyse niet. U kunt het gebruiken om in een flits een rij of een kolom met totalen aan de onder- of rechterkant van een gegevenstabel te maken. Selecteer de tabel als een cellenbereik en klik op de knop Snelle analyse gevolgd door Totalen in het palet. Klik vervolgens op de knop Som aan het begin van het palet om formules te maken die de kolommen in een nieuwe rij onder aan de tabel en / of de knop Som aan het einde van het palet bevatten om formules te maken die de rijen in een nieuw rijtje bevatten. kolom aan het rechter uiteinde.

Zo kunt u Automatisch aanvullen gebruiken om een ​​formule naar een celbereik te kopiëren. In deze afbeelding ziet u het werkblad Mother Goose Enterprises - 2016 Sales bij alle bedrijven, maar dit keer met slechts één maandelijks totaal in rij 12, die momenteel wordt gekopieerd via cel E12.

Een formule kopiëren naar een celbereik met Automatisch aanvullen.

De volgende afbeelding toont het werkblad na het slepen van de vulgreep in cel B12 om het celbereik C12: E12 te selecteren (waar deze formule moet worden gekopieerd).

Het werkblad na het kopiëren van de formule voor de maandelijkse (en driemaandelijkse) verkoop.

Relatief gesproken

De afbeelding toont het werkblad nadat de formule in een cel is gekopieerd naar het celbereik C12: E12 en cel B12 is actief. Merk op hoe Excel omgaat met het kopiëren van formules. De oorspronkelijke formule in cel B12 is als volgt:

= SOM (B3: B11)

Wanneer de originele formule naar cel C12 wordt gekopieerd, wijzigt Excel de formule enigszins zodat het er zo uitziet:

= SOM (C3: C11)

Excel past de kolomverwijzing aan en wijzigt deze van B naar C, omdat u van links naar rechts over de rijen hebt gekopieerd.

Wanneer u een formule kopieert naar een celbereik dat zich uitstrekt over de rijen, past Excel de rijnummers in de gekopieerde formules aan in plaats van de kolomletters die overeenkomen met de positie van elke kopie. Cel E3 in het werkblad Mother Goose Enterprises - 2016 Sales bevat bijvoorbeeld de volgende formule:

= SUM (B3: D3)

Wanneer u deze formule naar cel E4 kopieert, wijzigt Excel de kopie van de formule in volgende:

= SUM (B4: D4)

Excel past de rijverwijzing aan zodat deze actueel blijft met de nieuwe rij 4 positie. Omdat Excel de celverwijzingen in kopieën van een formule ten opzichte van de richting van het kopiëren aanpast, zijn de celverwijzingen relatieve celverwijzingen .

Sommige dingen zijn absoluut!

Alle nieuwe formules die u maakt, bevatten van nature relatieve celverwijzingen, tenzij u iets anders zegt. Omdat de meeste kopieën die je maakt van formules aanpassingen van hun celverwijzingen vereisen, hoef je dit arrangement zelden een tweede gedachte te geven. Dan kom je af en toe een uitzondering tegen die vraagt ​​om beperking wanneer en hoe celverwijzingen worden aangepast in kopieën.

Een van de meest voorkomende uitzonderingen is wanneer u een bereik van verschillende waarden wilt vergelijken met één waarde. Dit gebeurt het vaakst wanneer u wilt berekenen welk percentage elk onderdeel is voor het totaal. Op het werkblad Mother Goose Enterprises - 2016 Sales ziet u bijvoorbeeld deze situatie bij het maken en kopiëren van een formule die berekent welk percentage van elk maandelijks totaal (in celbereik B14: D14) van het kwartaal totaal is in cel E12.

Stel dat u deze formules wilt invoeren in rij 14 van het werkblad Mother Goose Enterprises - 2016 Sales, te beginnen in cel B14. De formule in cel B14 voor het berekenen van het percentage van het totaal voor de januari-verkoop tot het eerste kwartaal is zeer eenvoudig:

= B12 / E12

Deze formule verdeelt het verkooptotaal van januari in cel B12 op kwartaalbasis in totaal in E12 (wat is er eenvoudiger?). Kijk echter naar wat er zou gebeuren als u de vulgreep één cel naar rechts versleept om deze formule naar cel C14 te kopiëren:

= C12 / F12

De aanpassing van de eerste celverwijzing van B12 naar C12 is slechts wat de dokter heeft besteld. De aanpassing van de tweede celverwijzing van E12 naar F12 is echter een ramp. Niet alleen bereken je niet welk percentage de verkopen in februari in cel C12 zijn van de verkopen in het eerste kwartaal in E12, maar je eindigt ook met een van die vreselijke # DIV / 0! fout dingen in cel C14.

Als u wilt voorkomen dat Excel een celverwijzing in een formule in alle kopieën die u maakt aanpast, converteert u de celverwijzing van relatief naar absoluut. Dit doet u door op de functietoets F4 te drukken, nadat u Excel in de bewerkmodus (F2) hebt gezet. Excel geeft aan dat u de celverwijzing absoluut maakt door dollartekens te plaatsen voor de kolomletter en het rijnummer. In deze afbeelding bevat cel B14 bijvoorbeeld de juiste formule om naar het celbereik te kopiëren C14: D14:

De formule kopiëren voor het berekenen van de verhouding tussen maandelijkse en driemaandelijkse verkoop met een absolute celverwijzing.

= B12 / $ E $ 12

Kijk naar het werkblad nadat deze formule is gekopieerd naar het bereik C14: D14 met de vulgreep en cel C14 is geselecteerd (zie de volgende afbeelding). U ziet dat de formulebalk aangeeft dat deze cel de volgende formule bevat:

Het werkblad na het kopiëren van de formule met de absolute celverwijzing.

= C12 / $ E $ 12

Omdat E12 in de oorspronkelijke formule is gewijzigd in $ E $ 12, hebben alle exemplaren dezelfde absolute (niet-veranderende) referentie.

Als u een formule opstart en kopieert waarbij een of meer van de celverwijzingen absoluut moeten zijn maar u ze allemaal relatief hebt achtergelaten, bewerkt u de oorspronkelijke formule als volgt:

1. Dubbelklik op de cel met de formule of druk op F2 om het te bewerken.

2. Plaats de invoegpositie ergens op de referentie die u naar absoluut wilt converteren.

3. Druk op F4.

4. Als u klaar bent met bewerken, klikt u op de knop Enter op de formulebalk en kopieert u de formule naar het verprutste celbereik met de vulgreep.

Zorg ervoor dat u slechts één keer op F4 drukt om een ​​celverwijzing naar volledig absoluut te wijzigen, zoals ik eerder beschreef. Als u een tweede keer op de F4-functietoets drukt, krijgt u een zogenaamde gemengde referentie, , waarbij alleen het rijgedeelte absoluut is en het kolomgedeelte relatief is (zoals in E $ 12). Als u vervolgens opnieuw op F4 drukt, komt Excel met een ander type gemengde referentie, waarbij het kolomgedeelte absoluut is en het rijgedeelte relatief is (zoals in $ E12). Als je doorgaat en nogmaals op F4 drukt, wijzigt Excel de celverwijzing terug naar volledig relatief (zoals in E12).

Nadat u terug bent waar u bent begonnen, kunt u doorgaan met het gebruik van F4 om dezelfde reeks celverwijzingsveranderingen helemaal opnieuw te doorlopen.

Als u Excel 2016 op een apparaat gebruikt zonder toegang tot een fysiek toetsenbord met functietoetsen (zoals een tablet met touchscreen), is de enige manier om celadressen in uw formules om te zetten van relatief naar absoluut of naar een of andere vorm van gemengd adres is om het aanraaktoetsenbord te openen en het te gebruiken om de dollartekens toe te voegen vóór de kolomletter en / of het rijnummer in het juiste celadres op de formulebalk.