Strukturerade referenser i Excel

Hur skapar man strukturerade referenser i Excel?

Strukturerade referenser börjar med excel-tabeller. Så snart tabellerna som skapats 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 Tabell 1 namnet på tabellen och @Sales är den kolumn vi hänvisar till. Alla celler i denna kolumn hänvisas till ett tabellnamn och följt av kolumnrubrikens namn.
  • Steg 2: Nu ska 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

Du kan ladda ner denna Excel-mall för strukturerade referenser här - Excel-mall för strukturerade referenser

Exempel nr 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 normal och excel-tabell. 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 använda 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. Det här är inte samma process i strukturerade referenser, det fungerar lite annorlunda.

Titta nu på exemplet nedan. Jag har använt 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.

Tillämpa nu samma formel för exceltabellen för kolumnen Antal.

Nu har vi summan av antal kolumner. Som normalt intervall, kopiera formeln den aktuella formeln och klistra in den i Pris-kolumnen för att få summan av Pris.

Herregud!!! Den visar inte summan av kolumnen Pris snarare, men den visar fortfarande endast summan av antalet kolumner. 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 referensen

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 mycket 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 också lösningen, 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 tillämpat formeln för 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 drar formeln har ändringar till Sales _Table [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 behöver kopiera referenskolumnen som visas i bilden nedan.

Nu kan vi dra formeln till andra reaming två kolumner. Kriterieintervallet kommer att vara konstant och andra kolumnreferenser kommer att ändras därefter.

Proffstips: För att göra ROW som en absolut referens måste vi göra 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 dubbla 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.