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 de cel B3 gegeven. In plaats van de link als B2 weer te geven, 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 Bedrag .
- 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, geef een verwijzing naar de B3-cel.
We hebben dus begrepen dat gestructureerde verwijzing uit twee delen bestaat: tabelnaam en kolomnaam.
Voorbeelden
Voorbeeld 1
Met behulp van gestructureerde verwijzingen kunt u uw formule dynamisch maken. In tegenstelling tot normale celverwijzingen, staat het de formule toe om live 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 normale als Excel-tabellen. 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 op de tafel 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 krijg ik de som van de 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; in plaats daarvan wordt alleen het totaal van de kolom Qty weergegeven. 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 kennen we de beperking ervan. We kunnen de copy-paste job niet meer doen met gestructureerde referenties. Hoe kunnen we deze beperking dan overwinnen?
De oplossing is heel simpel. We hoeven alleen 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 één 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 absolute referentie maken als we de formule naar andere cellen slepen.
Laten we nu eens naar het onderstaande voorbeeld kijken. Ik heb een verkooptabel met meerdere items en ik wil de gegevens consolideren door de SUMIF-functie in Excel te gebruiken.
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. Omdat het een gestructureerde referentie is, kunnen we de formule niet kopiëren en in de overige twee kolommen plakken. De verwijzing naar februari en maart verandert niet, 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 functie SOM.ALS is het eerste argument Criteria Bereik Sales_Table (Product) sinds we de formule hebben gesleept. Het is gewijzigd in Sales _Table (jan).
Dus hoe gaan we ermee om ?? We moeten het eerste argument maken, dat wil zeggen, de 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-invoer maken, 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; in plaats daarvan moeten we 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.
