Hur skapar jag strukturerade referenser i Excel?
Strukturerade referenser börjar med excel-tabeller. Så snart tabellerna skapas i Excel skapas automatiskt strukturerade referenser åt dig.
Titta nu på bilden nedan.
- Steg 1: Jag hade gett en länk till cellen B3. Istället för att visa länken som B2, visas den som Tabell1 (@Sales). Här är Tabell1 namnet på tabellen och @Sales är den kolumn vi hänvisar till. Alla celler i den här kolumnen hänvisas till med ett tabellnamn och följt av kolumnrubrikens namn.
- Steg 2: Nu kommer jag ändra tabellen namn till Data_Table och ändra kolumnrubriken till belopp .
- Steg 3: För att ändra tabellnamnet, placera en markör i tabellen> gå till Design> Tabellnamn.
- Steg 4: Nämn tabellnamnet som Data_Table.
- Steg 5: Ändra nu, ge en referens till B3-cellen.
Så vi har förstått att strukturerad referens har två delar Tabellnamn och kolumnnamn.
Exempel
Exempel 1
Med hjälp av strukturerade referenser kan du göra din formel dynamisk. Till skillnad från normala cellreferenser tillåter det att formeln är aktiv vid tillägg och radering i dataområdet.
Låt mig tillämpa SUM-formeln för både normalintervall och Excel-tabell.
SUM-formel för normalt intervall.
SUM Formula for Excel Table.
Låt mig lägga till några rader till data i både normala tabeller och excel-tabeller. Jag har lagt till 2 rader i data, se nu skillnaden.
Strukturerad referens i exceltabellen visar det uppdaterade värdet, men det normala dataområdet visar inte de uppdaterade värdena om du inte gör några ändringar i formeln manuellt.
Exempel 2
Titta nu på ytterligare ett exempel. Jag har information om produktnamn, kvantitet och pris. Med hjälp av denna information måste jag komma till försäljningsvärdet.
För att få försäljningsvärdet är formeln Antal * Pris . Låt oss tillämpa denna formel i tabellen.
Formel säger (@QTY) * (@PRICE). Detta är mer förståeligt än den normala referensen för B2 * C2. Vi får inte tabellnamnet om vi lägger formeln inuti tabellen.
Problem med Excel-strukturerade referenser
När vi använder strukturerade referenser står vi inför några problem som listas nedan.
Problem nr 1
Strukturerade referenser har också sina egna problem. Vi är alla bekanta med att använda excelformeln och kopiera eller dra den till de andra återstående cellerna. Detta är inte samma process i strukturerade referenser. Det fungerar lite annorlunda.
Titta nu på exemplet nedan. Jag har tillämpat SUM-formeln i excel för det normala intervallet.
Om jag vill summera pris och försäljningsvärde kommer jag helt enkelt bara att kopiera och klistra in eller dra den nuvarande formeln till de andra två cellerna, och det kommer att ge mig SUM-värdet på Pris & Försäljningsvärde.
Använd nu samma formel för exceltabellen för kolumnen Antal.
Nu har vi summan av antal kolumner. Som normalt intervall kopierar formeln den aktuella formeln och klistrar in den i kolumnen Pris för att få totalt pris.
Herregud!!! Det visar inte summan av kolumnen Pris; snarare visar den fortfarande endast summan av kolumnen Antal. Så vi kan inte kopiera och klistra in den här formeln till den intilliggande cellen eller någon annan cell för att referera till den relativa kolumnen eller raden.
Dra formeln för att ändra referens
Nu vet vi dess begränsning. Vi kan inte göra kopierings- och klistrajobbet längre med strukturerade referenser. Hur övervinner vi då denna begränsning?
Lösningen är väldigt enkel. Vi behöver bara dra formeln istället för att kopiera. Välj formelcellen och använd fyllningshandtaget och dra det till de återstående två cellerna för att ändra kolumnreferensen till Pris och försäljningsvärde.
Nu har vi uppdaterat formler för att få respektive totalsummor.
Problem nr 2
Vi har sett ett problem med strukturreferenser, och vi hittade lösningen också, men vi har ytterligare ett problem här, vi kan inte ringa samtalet som en absolut referens om vi drar formeln till andra celler.
Låt oss ta en titt på exemplet nedan nu. Jag har en försäljningstabell med flera poster och jag vill konsolidera data med SUMIF-funktionen i Excel.
Nu kommer jag att använda SUMIF-funktionen för att få de konsoliderade försäljningsvärdena för varje produkt.
Jag har använt formeln i januari månad. Eftersom det är en strukturerad referens kan vi inte kopiera och klistra in formeln till de återstående två kolumnerna. Det kommer inte att ändra referensen till Feb & Mar, så jag drar formeln.
Åh!! Jag fick inga värden i kolumnen Feb & Mar. Vad skulle vara problemet ??? Titta noga på formeln.
Vi har dragit formeln från januari månad. I SUMIF-funktionen är det första argumentet Criteria Range Sales_Table (Product) eftersom vi drog formeln. Det har ändrats till Försäljning _Tabell (jan).
Så hur hanterar vi det ?? Vi måste göra det första argumentet, dvs. produktkolumnen som absolut och andra kolumner som en relativ referens. Till skillnad från normal referens har vi inte lyxen att använda F4-tangenten för att ändra referenstyp.
Lösningen är att vi måste duplicera referenskolumnen, som visas i bilden nedan.
Nu kan vi dra formeln till andra reaming två kolumner. Kriterieintervallet kommer att vara konstant och andra kolumnreferenser ändras därefter.
Proffstips: För att göra ROW som en absolut referens måste vi göra en dubbel ROW-post, men vi måste sätta @ -symbolen före ROW-namnet.
= Försäljningstabell (@ (produkt) :( produkt))
Hur stänger jag av strukturerad referens i Excel?
Om du inte gillar strukturerade referenser kan du stänga av genom att följa stegen nedan.
- Steg 1: Gå till FIL> Alternativ.
- Steg 2: Formler> Avmarkera Använd tabellnamn i formler.
Saker att komma ihåg
- För att göra den absoluta referensen i strukturerad referens måste vi fördubbla kolumnnamnet.
- Vi kan inte kopiera formeln för strukturerad referens; istället måste vi dra formeln.
- Vi kan inte se exakt vilken cell vi hänvisar till i strukturerade referenser.
- Om du inte är intresserad av strukturerade referenser kan du stänga av dem.
