Excel-celverwijzing (inhoudsopgave)

  • Celverwijzing in Excel
  • Typen celverwijzingen in Excel
    • # 1 - Relatieve celverwijzing in Excel
    • # 2- Absolute celverwijzing in Excel
    • # 3- Gemengde celverwijzing in Excel

Celverwijzing in Excel

Als je wordt gevraagd om te vermelden dat een functie van Excel die de ervaring echt magisch maakt tijdens het werken met formules, zou het misschien het feit zijn dat het hardcoderen van de waarden in je formules niet nodig is (het wordt zelfs niet eens aanbevolen).

In Excel zijn spreadsheets samengesteld uit kolommen en rijen, die 'cellen' vormen. Elke cel komt overeen met een precieze kolom en precieze rij. Om dit aan de hand van een voorbeeld uit te leggen, verwijst cel B2 naar de tweede kolom (B) en de tweede rij (2). Dienovereenkomstig wijst D16 naar de vierde kolom (D) en de zestiende rij (16). De werkelijke voorsprong van Excel zit in de bruikbaarheid van de celverwijzingen in andere cellen tijdens het maken van formules.

Typen celverwijzingen in Excel

We hebben drie verschillende soorten celverwijzingen in Excel -

  1. Relatieve celverwijzing in Excel
  2. Absolute celverwijzing in Excel
  3. Gemengde celverwijzing in Excel

Het gebruik van het juiste type celverwijzing in Excel in een bepaald scenario bespaart veel tijd en moeite en maakt het werk veel eenvoudiger.

# 1 - Relatieve celverwijzing in Excel

Relatieve celverwijzingen in excel verwijzen naar een cel of een reeks cellen in excel. Telkens wanneer een waarde in een formule wordt ingevoerd, zoals SOMMEN, is het mogelijk om in Excel een "celverwijzing" in te voeren als vervanging voor een hard gecodeerd nummer. Een celverwijzing kan de vorm B2 hebben, waarbij B overeenkomt met de kolomletter van de betreffende cel en 2 staat voor het rijnummer. Wanneer Excel een celverwijzing tegenkomt, bezoekt het de betreffende cel, haalt de waarde eruit en gebruikt die waarde in welke formule u ook schrijft. Wanneer deze celverwijzing in Excel naar een andere locatie wordt gedupliceerd, veranderen de relatieve celverwijzingen in Excel dienovereenkomstig ook automatisch.

Wanneer we cellen op deze manier doorverwijzen, kunnen we dit bereiken met elk van de twee celverwijzingstypes in excel: absoluut en relatief. Het onderscheid tussen deze twee verschillende referentietypen is het verschillende inherente gedrag wanneer u ze naar verschillende cellen sleept of kopieert en plakt. Relatieve celverwijzingen kunnen zichzelf wijzigen en aanpassen terwijl u ze kopieert en plakt; absolute referenties daarentegen niet. Om met succes resultaten in Excel te bereiken, is het van cruciaal belang om relatieve en absolute celverwijzingen op de juiste manier te kunnen gebruiken.

Hoe effectief relatieve celverwijzing in Excel te gebruiken?

Om de veelzijdigheid en bruikbaarheid van deze geweldige functie van Excel volledig te begrijpen, moeten we een paar praktische voorbeelden bekijken om de werkelijke waarde ervan te begrijpen.

U kunt deze celreferentie Excel-sjabloon hier downloaden - Celreferentie Excel-sjabloon

Voorbeeld 1

Laten we een eenvoudig voorbeeld bekijken om de mechanica van relatieve celverwijzing in Excel uit te leggen. Als we de som van twee getallen in twee verschillende cellen willen hebben - A1 en A2, en het resultaat willen hebben in een derde cel A3.

Dus passen we de formule = A1 + A2 toe

Dat zou het resultaat opleveren als 100 in A3.

Stel nu dat we een soortgelijk scenario hebben in de volgende kolom (B). Cel B1 en B2 hebben twee getallen en we willen de som in B3 hebben.

We kunnen dit op twee verschillende manieren bereiken:

Hier schrijven we fysiek de formule om de twee cellen B1 en B2 in B3 toe te voegen.

Het resultaat als 30.

Of we kunnen de formule eenvoudig uit cel A3 kopiëren en in cel B3 plakken (het zou werken als we de formule ook van A3 naar B3 slepen).

Dus wanneer we de inhoud van cel A3 kopiëren en in B3 plakken of de inhoud van cel A3 slepen en in B3 plakken, wordt de formule gekopieerd, niet het resultaat. We kunnen hetzelfde resultaat bereiken door met de rechtermuisknop op cel A3 te klikken en de optie Kopiëren te gebruiken.

En daarna gaan we naar de volgende cel B3 en klikken met de rechtermuisknop en selecteren "Formules (f)".

Wat dit betekent is dat cel A3 = A1 + A2. Wanneer we A3 kopiëren en één cel naar rechts verplaatsen en in cel B3 plakken, past de formule zich automatisch aan en verandert in B3 = B1 + B2. In plaats daarvan past het de sommatieformule toe voor B1- en B2-cellen.

Voorbeeld 2

Laten we nu eens kijken naar nog een ander praktisch scenario dat het concept vrij duidelijk zou maken. Laten we aannemen dat we een gegevensset hebben die bestaat uit de eenheidsprijs van een product en de verkochte hoeveelheid voor elk product. Ons doel is nu om de verkoopprijs te berekenen, die kan worden beschreven met de volgende formule:

Verkoopprijs = Eenheidsprijs x Verkochte eenheden

Om de verkoopprijs te kunnen vinden, moeten we nu eenheidsprijzen vermenigvuldigen met verkochte eenheden voor elk product. We zullen nu deze formule toepassen voor de eerste cel in verkoopprijs, dwz voor product 1.

Wanneer we de formule toepassen, krijgen we het volgende resultaat voor product 1:

Het vermenigvuldigde met succes de eenheidskosten met de verkochte eenheden voor product 1, dwz cel G2 * cel H2, dwz 1826.00 * 20, wat ons het resultaat 36520.00 geeft.

Dus nu zien we dat we nog 9 producten te gaan hebben. In echte scenario's kan dit oplopen tot honderden of duizenden of rijen. Het wordt moeilijk en bijna onmogelijk om gewoon de formule voor elke rij te schrijven.

Daarom zullen we de Relatieve Referentie-functie van Excel gebruiken en eenvoudig de inhoud van cel I2 kopiëren en in alle resterende cellen in de tabel voor de kolom Verkoopprijs plakken of de formule van cel I2 naar de rest van de rijen slepen in die kolom en ontvang de resultaten voor de hele tabel in minder dan 5 seconden.

Hier drukken we op Ctrl + D. De uitvoer ziet er dus als volgt uit:

# 2- Absolute celverwijzing in Excel

Het grootste deel van ons dagelijkse werk in Excel omvat het verwerken van formules. Daarom is een praktische kennis van relatieve, absolute of gemengde celverwijzingen in Excel heel belangrijk.

Laten we het volgende zien:

= A1 is een relatieve verwijzing, waarbij zowel de rij als de kolom verandert wanneer we de formulecel kopiëren.

= $ A $ 1 is een absolute celverwijzing, zowel de kolom als de rij zijn vergrendeld en veranderen niet wanneer we de formulecel kopiëren. De celwaarde blijft dus constant.

In = $ A1 is de kolom vergrendeld en kan de rij voor die specifieke kolom blijven veranderen.

In = A $ 1 is de rij vergrendeld en kan de kolom voor die specifieke rij blijven veranderen.

In tegenstelling tot Relatieve referentie die kan veranderen als deze naar verschillende cellen wordt verplaatst, verandert de absolute referentie niet. Het enige dat hier vereist is, is om de specifieke cel volledig te vergrendelen.

Met behulp van een dollarteken in de formule wordt een celverwijzing een absolute celverwijzing omdat het dollarteken de cel vergrendelt. We kunnen de rij of de kolom vergrendelen met het dollarteken. Als de "$" vóór een alfabet staat, wordt een kolom vergrendeld en als de "$" vóór een cijfer staat, is een rij vergrendeld.

# 3- Gemengde celverwijzing in Excel

Hoe gebruik je Absolute celverwijzing in Excel ook hoe gebruik je Gemengde celverwijzing in Excel?

Laten we het volgende voorbeeld bekijken om een ​​uitgebreid begrip van Absolute en Gemengde celverwijzing in Excel te krijgen.

We hebben de verkoopgegevens voor 4 verkoopmanagers over verschillende maanden, waarbij de verkoop meerdere keren in een maand heeft plaatsgevonden.

Ons doel is om het geconsolideerde verkoopoverzicht van alle 4 verkoopmanagers te berekenen. We zullen de SUMIFS-formule toepassen om het gewenste resultaat te krijgen.

Het resultaat is als:

Laten we de formule bekijken om te zien wat er is gebeurd.

  • In het "sombereik" hebben we $ C $ 2: $ C $ 17. Er staat een dollarteken voor zowel het alfabet als de cijfers. Dus zowel de rijen als de kolommen voor het celbereik zijn vergrendeld. Dit is een absolute celverwijzing.
  • Vervolgens hebben we “criteria_bereik1”. Ook hier hebben we absolute celverwijzing.
  • Hierna hebben we “criteria1” - $ F2. Hier zien we dat alleen de kolom wordt vergrendeld tijdens het kopiëren van de formulecel, wat betekent dat alleen de rij verandert wanneer we de formule naar een andere cel kopiëren (naar beneden verplaatsen). Dit is een gemengde celverwijzing.
  • Vervolgens hebben we “criteria_bereik2”, wat ook een absolute celverwijzing is.
  • Het laatste segment van de formule is "criteria2" - G $ 1. Hier zien we dat het dollarteken voor het cijfer staat en niet het alfabet. Dus wordt alleen de rij vergrendeld wanneer we de formulecel kopiëren. De kolom kan veranderen wanneer we de formulecel naar een andere cel kopiëren (naar rechts). Dit is een gemengde celverwijzing.

De formule naar de overzichtstabel slepen door eerst op Ctrl + D te drukken en later op Ctrl + R. We krijgen het volgende resultaat:

Gemengde celverwijzing verwijst alleen naar een bepaalde rij of kolom, zoals = $ A2 of = A $ 2 . Als we een gemengde celverwijzing willen maken, kunnen we twee tot drie keer op de F4-toets op de formule drukken, volgens uw vereiste, dat wil zeggen om te verwijzen naar een rij of een kolom. Als u nogmaals op F4 drukt, verandert de celverwijzing in relatieve verwijzing.

Dingen om te onthouden

  • Bij het kopiëren van de Excel-formule is in het algemeen relatieve verwijzing gewenst. Dit is de reden waarom dit het standaardgedrag van Excel is. Maar soms kan het doel zijn om absolute referentie toe te passen in plaats van relatieve celverwijzing in Excel. Absolute referentie is het maken van een celverwijzing die is vastgezet op een absoluut celadres, waardoor de formule, wanneer deze wordt gekopieerd, ongewijzigd blijft.
  • Absoluut geen dollartekens zijn vereist met relatieve verwijzing. Wanneer we de formule van de ene plaats naar de andere kopiëren, past de formule zich dienovereenkomstig aan. Dus als we = B1 + B2 in cel B3 typen en vervolgens dezelfde formule in cel C3 slepen of kopiëren en plakken, past de relatieve celverwijzing de formule automatisch aan = C1 + C2 .
  • Met Relatieve verwijzingen passen de cellen waarnaar wordt verwezen zich automatisch aan in de formule volgens uw beweging, naar rechts, links, omhoog of omlaag.
  • Met Relatieve verwijzingen, als we een verwijzing naar cel D10 zouden geven en vervolgens één cel naar beneden schuiven, zou dit veranderen in D11, als we in plaats daarvan één cel naar boven schuiven, zou het veranderen naar D9 . Als we echter één cel naar rechts schuiven, verandert de referentie in E10 en in plaats daarvan als we één cel naar links schuiven, past de referentie zich automatisch aan C10 aan.
  • Als u eenmaal op F4 drukt, verandert de relatieve celverwijzing in absolute celverwijzing in Excel.
  • Als u tweemaal op F4 drukt, wordt de celverwijzing gewijzigd in gemengde verwijzing waarbij de rij is vergrendeld.
  • Als u driemaal op F4 drukt, wordt de celverwijzing gewijzigd in gemengde verwijzing waarbij de kolom is vergrendeld.
  • Als u voor de vierde keer op F4 drukt, verandert de celreferentie terug naar de relatieve referentie in excel.

Aanbevolen artikelen

Dit is een gids geweest voor Celverwijzing in Excel. Hier bespreken we drie soorten celverwijzingen in Excel, dwz absolute, relatieve en gemengde celverwijzing en hoe ze allemaal moeten worden gebruikt, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -

  1. Relatieve referentie in Excel begrijpen
  2. Excel Absolute Reference | Eenvoudige Excel-zelfstudie
  3. De SUMIF-functie gebruiken in Excel
  4. SOM-functie gebruiken met voorbeelden

Categorie:

Excel-tips