Relatieve en absolute celverwijzing en opmaak

In deze les bespreken we celverwijzingen, hoe je een formule kopieert of verplaatst en hoe cellen worden opgemaakt. Om te beginnen, laten we verduidelijken wat we bedoelen met celverwijzingen, die ten grondslag liggen aan veel van de kracht en veelzijdigheid van formules en functies. Een concreet begrip van hoe celverwijzingen werken, stelt u in staat om het meeste uit uw Excel-spreadsheets te halen!

SCHOOLNAVIGATIE

1. Waarom heeft u formules en functies nodig??
2. Een formule definiëren en maken
3. Relatieve en absolute celverwijzing en opmaak
4. Handige functies die u moet leren kennen
5. Opzoeklijsten, grafieken, statistieken en draaitabellen

Notitie: we gaan er gewoon vanuit dat je al weet dat een cel een van de vierkanten in de spreadsheet is, gerangschikt in kolommen en rijen waarnaar wordt verwezen door letters en cijfers die horizontaal en verticaal lopen.

Wat is een celverwijzing?

Een "celverwijzing" betekent de cel waarnaar een andere cel verwijst. Als u bijvoorbeeld in cel A1 = A2 heeft. Vervolgens verwijst A1 naar A2.

Laten we eens kijken naar wat we in les 2 hebben gezegd over rijen en kolommen, zodat we celverwijzingen verder kunnen verkennen.

Cellen in de spreadsheet worden aangeduid met rijen en kolommen. Kolommen zijn verticaal en voorzien van letters. Rijen zijn horizontaal en gemarkeerd met cijfers.

De eerste cel in de spreadsheet is A1, wat kolom A betekent, rij 1, B3 verwijst naar de cel in de tweede kolom, derde rij, enzovoort.

Voor leerdoeleinden over celverwijzingen schrijven we ze soms als rij, kolom, dit is geen geldige notatie in de spreadsheet en is gewoon bedoeld om dingen duidelijker te maken.

Typen celverwijzingen

Er zijn drie soorten celverwijzingen.

Absoluut - Dit betekent dat de celverwijzing hetzelfde blijft als u de cel naar een andere cel kopieert of verplaatst. Dit gebeurt door de rij en kolom te verankeren, zodat deze niet veranderen wanneer ze worden gekopieerd of verplaatst.

Relatief - Relatieve verwijzingen betekent dat het celadres verandert terwijl u het kopieert of verplaatst; d.w.z. de celverwijzing is relatief ten opzichte van de locatie.

Gemengd - dit betekent dat u ervoor kunt kiezen om de rij of de kolom te verankeren wanneer u de cel kopieert of verplaatst, zodat de ene verandert en de andere niet. U kunt bijvoorbeeld de rijverwijzing verankeren en vervolgens een cel twee rijen omlaag verplaatsen over vier kolommen en de rijverwijzing hetzelfde blijft. We zullen dit hieronder verder toelichten.

Relatieve verwijzingen

Laten we naar dat eerdere voorbeeld verwijzen - stel dat we in cel A1 een formule hebben die simpelweg = A2 zegt. Dat betekent Excel-uitvoer in cel A1, wat dan ook wordt ingevoerd in cel A2. In cel A2 hebben we "A2" getypt, dus Excel geeft de waarde "A2" weer in cel A1.

Stel nu dat we ruimte moeten maken in onze spreadsheet voor meer gegevens. We moeten kolommen hierboven en rijen links toevoegen, dus we moeten de cel naar beneden en naar rechts verplaatsen om ruimte te maken.

Terwijl u de cel naar rechts verplaatst, neemt het kolomnummer toe. Naarmate u het naar beneden verplaatst, wordt het rijnummer groter. De cel waarnaar het verwijst, de celverwijzing, verandert ook. Dit wordt hieronder geïllustreerd:

Doorgaan met ons voorbeeld, en kijkend naar de onderstaande afbeelding, als je de inhoud van cel A1 twee naar rechts en vier naar beneden kopieert, verplaats je deze naar cel C5.

We hebben de cel twee kolommen naar rechts en vier naar beneden gekopieerd. Dit betekent dat we de cel twee en vier naar beneden hebben veranderd. A1 = A2 is nu C5 = C6. In plaats van te verwijzen naar A2, verwijst cel C5 nu naar cel C6.

De getoonde waarde is 0 omdat cel C6 leeg is. In cel C6 typen we "Ik ben C6" en nu toont C5 "Ik ben C6".

Voorbeeld: tekstformule

Laten we een ander voorbeeld proberen. Weet je nog van Les 2 waar we een volledige naam moesten splitsen in voor- en achternaam? Wat gebeurt er als we deze formule kopiëren?

Schrijf de formule = RECHTS (A3, LEN (A3) - FIND (",", A3) - 1) of kopieer de tekst naar cel C3. Kopieer niet de eigenlijke cel, alleen de tekst, kopieer de tekst, anders wordt de referentie bijgewerkt.

U kunt de inhoud van een cel boven in een spreadsheet bewerken in het vakje naast 'fx'. Dit vak is langer dan een cel die breed is, dus het is gemakkelijker om te bewerken.

Nu hebben we:

Niets ingewikkelds, we hebben zojuist een nieuwe formule in cel C3 geschreven. Kopieer nu C3 naar cellen C2 en C4. Bekijk de resultaten hieronder:

Nu hebben we de voornamen van Alexander Hamilton en Thomas Jefferson.

Gebruik de cursor om cellen C2, C3 en C4 te markeren. Plaats de cursor op cel B2 en plak de inhoud. Kijk naar wat er gebeurde - we krijgen een foutmelding: "#REF." Waarom is dit?

Toen we de cellen kopieerden van kolom C naar kolom B, werd de referentie één kolom naar links bijgewerkt = RECHTS (A2, LEN (A2) - FIND (",", A2) - 1).

Het heeft elke verwijzing naar A2 in de kolom links van A veranderd, maar er is geen kolom links van kolom A. Dus de computer weet niet wat je bedoelt.

De nieuwe formule in B2 is bijvoorbeeld = RECHTS (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) en het resultaat is # VERF:

Een formule kopiëren naar een reeks cellen

Het kopiëren van cellen is erg handig omdat je een formule kunt schrijven en deze naar een groot gebied kunt kopiëren en de referentie wordt bijgewerkt. Hiermee wordt voorkomen dat elke cel moet worden bewerkt om te zorgen dat deze naar de juiste plaats wijst.

Met 'bereik' bedoelen we meer dan één cel. Bijvoorbeeld (C1: C10) betekent alle cellen van cel C1 tot cel C10. Dus het is een kolom met cellen. Een ander voorbeeld (A1: AZ1) is de bovenste rij van kolom A tot kolom AZ.

Als een bereik vijf kolommen en tien rijen overschrijdt, geeft u het bereik aan door de cel linksboven en rechtsonder, bijvoorbeeld A1: E10, te schrijven. Dit is een vierkant gebied dat rijen en kolommen kruist en niet alleen maar een deel van een kolom of een deel van een rij.

Hier is een voorbeeld dat illustreert hoe een cel naar meerdere locaties moet worden gekopieerd. Stel dat we onze verwachte uitgaven voor de maand in een spreadsheet willen weergeven, zodat we een budget kunnen maken. We maken een spreadsheet als deze:

Kopieer nu de formule in cel C3 (= B3 + C2) naar de rest van de kolom om een ​​lopende balans voor ons budget te krijgen. Excel werkt de celverwijzing bij terwijl u deze kopieert. Het resultaat wordt hieronder getoond:

Zoals je ziet, wordt elke nieuwe cel bijgewerkt familielid naar de nieuwe locatie, dus cel C4 werkt de formule bij naar = B4 + C3:

Cel C5 werkt bij tot = B5 + C4, enzovoort:

Absolute verwijzingen

Een absolute verwijzing verandert niet wanneer u een cel verplaatst of kopieert. We gebruiken het $ -teken om een ​​absolute referentie te maken - om dat te onthouden, denk aan een dollarteken als een anker.

Voer bijvoorbeeld de formule = $ A $ 1 in een willekeurige cel in. De $ vóór de kolom A betekent dat de kolom niet wordt gewijzigd, de $ vóór de rij 1 betekent dat de kolom niet wordt gewijzigd wanneer u de cel kopieert of verplaatst naar een andere cel.

Zoals je in het onderstaande voorbeeld kunt zien, hebben we in cel B1 een relatieve verwijzing = A1. Wanneer we B1 kopiëren naar de vier cellen eronder, verandert de relatieve verwijzing = A1 naar de cel links, dus B2 wordt A2, B3 A3 worden, enz. Die cellen hebben duidelijk geen ingevoerde waarde, dus de uitvoer is nul.

Als we echter = $ A1 $ 1 gebruiken, zoals in C1 en we kopiëren deze naar de vier cellen eronder, is de verwijzing absoluut, dus verandert deze nooit en is de uitvoer altijd gelijk aan de waarde in cel A1.

Stel dat u uw interesses bijhoudt, zoals in het onderstaande voorbeeld. De formule in C4 = B4 * B1 is de "rentevoet" * "balans" = "rente per jaar."

U hebt nu uw budget gewijzigd en een extra $ 2000 bespaard om een ​​beleggingsfonds te kopen. Stel dat het een fonds met een vaste rente is en dat het dezelfde rente betaalt. Voer de nieuwe account en balans in de spreadsheet in en kopieer vervolgens de formule = B4 * B1 van cel C4 naar cel C5.

Het nieuwe budget ziet er als volgt uit:

Het nieuwe beleggingsfonds verdient $ 0 rente per jaar, wat niet klopt omdat de rente duidelijk 5 procent is.

Excel markeert de cellen waarnaar een formule verwijst. U kunt hierboven zien dat de verwijzing naar de rentevoet (B1) wordt verplaatst naar de lege cel B2. We hadden de verwijzing naar B1 absoluut moeten maken door $ B $ 1 te schrijven met behulp van het dollarteken om de rij- en kolomverwijzing te verankeren.

Herschrijf de eerste berekening in C4 om = B4 * $ B $ 1 te lezen, zoals hieronder wordt getoond:

Kopieer die formule vervolgens van C4 naar C5. De spreadsheet ziet er nu als volgt uit:

Omdat we de formule één cel naar beneden hebben gekopieerd, d.w.z. de rij met één vergroot, is de nieuwe formule = B5 * $ B $ 1. De rentevoet voor beleggingsfondsen wordt nu correct berekend, omdat de rente is verankerd in cel B1.

Dit is een goed voorbeeld van wanneer u een "naam" zou kunnen gebruiken om naar een cel te verwijzen. Een naam is een absolute referentie. Als u bijvoorbeeld de naam "rentevoet" wilt toewijzen aan cel B1, klikt u met de rechtermuisknop op de cel en selecteert u vervolgens "naam definiëren".

Namen kunnen verwijzen naar één cel of een bereik en u kunt een naam in een formule gebruiken, bijvoorbeeld = interesse_rente * 8 is hetzelfde als schrijven = $ B $ 1 * 8.

Gemengde verwijzingen

Gemengde verwijzingen zijn wanneer een van beide de rij of kolom is verankerd.

Stel dat u een boer bent die een budget maakt. Je hebt ook een feed store en verkoopt zaden. Je gaat maïs, sojabonen en alfalfa planten. Het onderstaande spreadsheet toont de kosten per hectare. De "kosten per acre" = "prijs per pond" * "ponden zaden per acre" - dat is wat het u zal kosten om een ​​acre te planten.

Voer de kosten per acre in als = $ B2 * C2 in cel D2. U zegt dat u de prijs per pond kolom wilt verankeren. Kopieer die formule vervolgens naar de andere rijen in dezelfde kolom:

Nu wil je de waarde van je inventaris van zaden weten. U hebt de prijs per pond nodig en het aantal pond in de inventaris om de waarde van de voorraad te kennen.

We voegen twee kolommen toe: 'pond zaad in voorraad' en vervolgens 'waarde van voorraad'. Kopieer nu de cel D2 naar F4 en merk op dat de rijverwijzing in het eerste deel van de oorspronkelijke formule ($ B2) is bijgewerkt naar rij 4 maar de kolom blijft gerepareerd omdat $ deze verankert in "B".

Dit is een gemengde verwijzing omdat de kolom absoluut is en de rij relatief is.

Circulaire verwijzingen

Een kringverwijzing is wanneer een formule naar zichzelf verwijst.

U kunt bijvoorbeeld niet schrijven c3 = c3 + 1. Dit soort berekening wordt "iteratie" genoemd, wat betekent dat het zichzelf herhaalt. Excel biedt geen ondersteuning voor iteratie omdat het alles slechts één keer berekent.

Als u dit probeert, typt u SUM (B1: B5) in cel B5:

Er verschijnt een waarschuwingsscherm:

Excel vertelt u alleen dat u een kringverwijzing onder aan het scherm hebt, zodat u het misschien niet opmerkt. Als u een kringverwijzing hebt en een spreadsheet sluit en deze opnieuw opent, geeft Excel in een pop-upvenster aan dat u een kringverwijzing hebt.

Als u een kringverwijzing hebt, wordt Excel elke keer dat u de spreadsheet opent, met dat pop-upvenster aangegeven dat u een kringverwijzing hebt.

Verwijzingen naar andere werkbladen

Een "werkboek" is een verzameling "werkbladen". Simpel gezegd betekent dit dat u meerdere spreadsheets (werkbladen) in hetzelfde Excel-bestand (werkmap) kunt hebben. Zoals u in het onderstaande voorbeeld kunt zien, heeft ons voorbeeldwerkboek veel werkbladen (in rood).

Werkbladen standaard heten Sheet1, Sheet2, enzovoort. U maakt een nieuwe door op de "+" onder aan het Excel-scherm te klikken.

U kunt de naam van het werkblad wijzigen in iets nuttigs, zoals 'uitlenen' of 'budget' door met de rechtermuisknop op het werkbladtabblad onder aan het Excel-programmascherm te klikken, naam wijzigen te selecteren en een nieuwe naam in te typen.

Of u kunt eenvoudig op het tabblad dubbelklikken en de naam ervan wijzigen.

De syntaxis voor een werkbladverwijzing is = werkblad! Cel. U kunt dit soort referentie gebruiken wanneer dezelfde waarde wordt gebruikt in twee werkbladen, voorbeelden hiervan zijn:

• De datum van vandaag
• Valutaconversie van Dollars naar Euro's
• Alles dat relevant is voor alle werkbladen in de werkmap

Hieronder staat een voorbeeld van een werkblad "interesse" met verwijzing naar werkblad "lening", cel B1.

Als we het werkblad 'lenen' bekijken, zien we de verwijzing naar het geleende bedrag:

Volgende volgende ...

We hopen dat je nu een stevige greep hebt op celverwijzingen inclusief relatief, absoluut en gemengd. Er is zeker veel.

Dat is het voor de les van vandaag, in les 4 bespreken we enkele nuttige functies die u misschien wilt weten voor dagelijks gebruik van Excel.