Hoe gestructureerde verwijzingen maken in Excel?
Gestructureerde verwijzingen begint met Excel-tabellen. Zodra de tabellen in Excel zijn aangemaakt, worden automatisch gestructureerde verwijzingen voor u aangemaakt.
Bekijk nu de onderstaande afbeelding.
- Stap 1: Ik had een link naar cel B3 gegeven, in plaats van de link als B2 te laten zien, wordt deze weergegeven als Tabel1 [@Sales]. Hier is Table1 de naam van de tabel en @Sales is de kolom waarnaar we verwijzen. Alle cellen in deze kolom worden aangeduid met een tabelnaam en gevolgd door de naam van de kolomkop.
- Stap 2: Nu zal ik de tabelnaam wijzigen in Data_Table en de kolomtitel wijzigen in Amount .
- Stap 3: Om de tabelnaam te wijzigen, plaatst u een cursor in de tabel> ga naar Design> Tabelnaam.
- Stap 4: noem de tabelnaam als Data_Table.
- Stap 5: Verander nu en geef een verwijzing naar de B3-cel.
We hebben dus begrepen dat gestructureerde verwijzing uit twee delen bestaat: tabelnaam en kolomnaam.
Voorbeelden
U kunt dit Excel-sjabloon voor gestructureerde verwijzingen hier downloaden - Excel-sjabloon voor gestructureerde verwijzingenVoorbeeld 1
Met behulp van gestructureerde verwijzingen kunt u uw formule dynamisch maken. In tegenstelling tot normale celverwijzingen, staat het de formule toe om actief te zijn in geval van toevoeging en verwijdering in het gegevensbereik.
Laat me de SUM-formule toepassen voor zowel de normale reeks als de Excel-tabel.
SOM-formule voor normaal bereik.
SOM-formule voor Excel-tabel.
Laat me een paar regels toevoegen aan de gegevens van zowel de normale als de Excel-tabel. Ik heb 2 regelitems aan de gegevens toegevoegd, zie nu het verschil.
Gestructureerde verwijzing in de Excel-tabel toont de bijgewerkte waarde, maar het normale gegevensbereik toont de bijgewerkte waarden niet, tenzij u handmatig enkele wijzigingen in de formule aanbrengt.
Voorbeeld # 2
Bekijk nu nog een voorbeeld. Ik heb een productnaam, hoeveelheid en prijsinformatie. Met behulp van deze informatie moet ik komen tot de verkoopwaarde.
Om de verkoopwaarde te krijgen, is de formule Aantal * Prijs . Laten we deze formule in de tabel toepassen.
Formule zegt [@QTY] * [@PRICE]. Dit is begrijpelijker dan de normale referentie van B2 * C2. We krijgen de tabelnaam niet als we de formule in de tabel plaatsen.
Problemen met gestructureerde verwijzingen in Excel
Bij het gebruik van gestructureerde verwijzingen worden we geconfronteerd met enkele problemen die hieronder worden opgesomd.
Probleem # 1
Gestructureerde referenties hebben ook hun eigen problemen. We zijn allemaal bekend met het toepassen van de Excel-formule en het kopiëren of slepen naar de andere resterende cellen. Dit is niet hetzelfde proces in gestructureerde verwijzingen, het werkt een beetje anders.
Bekijk nu het onderstaande voorbeeld. Ik heb de SUM-formule in Excel toegepast voor het normale bereik.
Als ik prijs en verkoopwaarde wil optellen, kopieer en plak of sleep ik de huidige formule gewoon naar de andere twee cellen en het geeft me de SOM-waarde van prijs en verkoopwaarde.
Pas nu dezelfde formule toe voor de Excel-tabel voor de kolom Qty.
Nu hebben we de som van de kolom Qty. Net als bij een normaal bereik, kopieert de formule de huidige formule en plakt u deze in de kolom Prijs om het totaal van Prijs te krijgen.
O mijn God!!! Het toont niet het totaal van de kolom Prijs, maar toont nog steeds alleen het totaal van de kolom Aantal. We kunnen deze formule dus niet kopiëren en in de aangrenzende cel of een andere cel plakken om naar de relatieve kolom of rij te verwijzen.
Sleep de formule om de referentie te wijzigen
Nu we de beperking kennen, kunnen we niet meer kopiëren en plakken met gestructureerde verwijzingen. Hoe kunnen we deze beperking dan overwinnen?
De oplossing is heel eenvoudig, we hoeven alleen maar de formule te slepen in plaats van te kopiëren. Selecteer de formulecel en gebruik de vulgreep en sleep deze naar de resterende twee cellen om de kolomverwijzing naar Prijs en Verkoopwaarde te wijzigen.
Nu hebben we formules bijgewerkt om de respectieve totalen te krijgen.
Probleem # 2
We hebben een probleem gezien met de structuurreferenties en we hebben ook de oplossing gevonden, maar we hebben hier nog een probleem: we kunnen de aanroep niet als een absolute referentie maken als we de formule naar andere cellen slepen.
Laten we nu eens kijken naar het onderstaande voorbeeld. Ik heb een verkooptabel met meerdere items en ik wil de gegevens consolideren met behulp van de SUMIF-functie in Excel.
Nu zal ik de SUMIF-functie toepassen om de geconsolideerde verkoopwaarden voor elk product te krijgen.
Ik heb de formule voor de maand januari toegepast, aangezien het een gestructureerde referentie is, kunnen we de formule niet kopiëren en plakken in de resterende twee kolommen, het zal de verwijzing naar februari en maart niet wijzigen, dus ik zal de formule slepen.
Oh!! Ik heb geen waarden gekregen in de kolom februari en maart. Wat zou het probleem zijn ??? Kijk goed naar de formule.
We hebben de formule van jan maand gesleept. In de SUMIF-functie is het eerste argument Criteria Bereik Sales_Table [Product], aangezien we de formule hebben gesleept, heeft deze wijzigingen in Sales _Table [Jan].
Dus hoe gaan we ermee om ?? We moeten het eerste argument maken, dat wil zeggen Productkolom als absolute en andere kolommen als relatieve verwijzing. In tegenstelling tot normale referentie, hebben we niet de luxe om de F4-toets te gebruiken om het referentietype te wijzigen.
De oplossing is dat we de referentiekolom moeten dupliceren zoals weergegeven in de onderstaande afbeelding.
Nu kunnen we de formule naar twee andere kolommen slepen. Het criteriabereik zal constant zijn en andere kolomverwijzingen zullen dienovereenkomstig veranderen.
Pro-tip: om de RIJ als een absolute referentie te maken, moeten we een dubbele RIJ invoeren, maar we moeten het @ -symbool voor de RIJ-naam plaatsen.
= Verkooptabel [@ [Product]: [Product]]
Hoe gestructureerde referentie in Excel uitschakelen?
Als u geen fan bent van gestructureerde verwijzingen, kunt u deze uitschakelen door de onderstaande stappen te volgen.
- Stap 1: Ga naar FILE> Options.
- Stap 2: Formules> Schakel het selectievakje Gebruik tabelnamen in formules uit.
Dingen om te onthouden
- Om de absolute verwijzing in gestructureerde verwijzing te maken, moeten we de kolomnaam verdubbelen.
- We kunnen de formule van gestructureerde referentie niet kopiëren, maar moeten de formule slepen.
- We kunnen niet precies zien naar welke cel we verwijzen in gestructureerde verwijzingen.
- Als u niet geïnteresseerd bent in gestructureerde verwijzingen, kunt u deze uitschakelen.
