Hvordan oprettes strukturerede referencer i Excel?
Strukturerede referencer starter med excel-tabeller. Så snart de tabeller, der er oprettet i excel, oprettes det automatisk strukturerede referencer til dig.
Se nu på nedenstående billede.
- Trin 1: Jeg havde givet et link til cellen B3 i stedet for at vise linket som B2 viser det som tabel1 [@Sales]. Her Tabel1 er navnet på bordet og @Sales er kolonnen, vi taler om. Alle celler i denne kolonne henvises til et tabelnavn og efterfulgt af kolonnens overskriftnavn.
- Trin 2: Nu vil jeg ændre tabelnavnet til Data_Table og ændre kolonneoverskriften til Beløb .
- Trin 3: For at ændre tabelnavnet skal du placere en markør inde i tabellen> gå til Design> Tabelnavn.
- Trin 4: Nævn tabelnavnet som Data_Table.
- Trin 5: Nu skift give en henvisning til B3-cellen.
Så vi har forstået, at struktureret reference har to dele Tabelnavn og søjlenavn.
Eksempler
Du kan downloade denne Excel-skabelon med strukturerede referencer her - Excel-skabelon med strukturerede referencerEksempel nr. 1
Ved hjælp af strukturerede referencer kan du gøre din formel dynamisk. I modsætning til normale cellereferencer tillader det formlen at være levende i tilfælde af tilføjelse og sletning i dataområdet.
Lad mig anvende SUM-formlen for både normalområdet og Excel-tabellen.
SUM Formula for Normal Range.
SUM Formula for Excel-tabel.
Lad mig tilføje et par linjer til dataene i både normal og excel-tabel. Jeg har tilføjet 2 linjeposter til dataene, se nu forskellen.
Struktureret reference i exceltabellen viser den opdaterede værdi, men det normale dataområde viser ikke de opdaterede værdier, medmindre du foretager nogle ændringer af formlen manuelt.
Eksempel 2
Se nu på endnu et eksempel. Jeg har oplysninger om produktnavn, mængde og pris. Ved hjælp af disse oplysninger skal jeg nå frem til salgsværdien.
For at få salgsværdien er formlen Antal * Pris . Lad os anvende denne formel i tabellen.
Formel siger [@QTY] * [@PRICE]. Dette er mere forståeligt end den normale reference for B2 * C2. Vi får ikke tabelnavnet, hvis vi lægger formlen inde i tabellen.
Problemer med Excel-strukturerede referencer
Mens vi bruger strukturerede referencer, står vi over for nogle problemer, som er anført nedenfor.
Problem nr. 1
Strukturerede referencer har også deres egne problemer. Vi er alle fortrolige med at anvende excelformlen og kopiere eller trække den til de andre resterende celler. Dette er ikke den samme proces i strukturerede referencer, det fungerer lidt anderledes.
Se nu på nedenstående eksempel. Jeg har anvendt SUM-formlen i Excel for det normale interval.
Hvis jeg vil opsummere pris og salgsværdi, vil jeg bare kopiere og indsætte eller trække den aktuelle formel til de to andre celler, og det giver mig SUM-værdien af pris og salgsværdi.
Anvend nu den samme formel for excel-tabellen for kolonnen Antal.
Nu har vi summen af antal kolonne. Som det normale interval kopierer formlen den aktuelle formel og indsætter den i kolonnen Pris for at få det samlede pris.
Åh gud!!! Det viser ikke det samlede antal kolonnen Pris, men det viser stadig kun det samlede antal kolonne. Så vi kan ikke kopiere og indsætte denne formel til den tilstødende celle eller en anden celle for at henvise til den relative kolonne eller række.
Træk formlen for at ændre referencen
Nu kender vi dens begrænsning, vi kan ikke længere kopiere og indsætte jobbet med strukturerede referencer. Hvordan overvinder vi så denne begrænsning?
Løsningen er meget enkel, vi skal bare trække formlen i stedet for at kopiere. Vælg formelcellen, og brug fyldhåndtaget, og træk den til de resterende to celler for at ændre kolonnereferencen til Pris og salgsværdi.
Nu har vi opdaterede formler for at få de respektive totaler.
Problem nr.2
Vi har set et problem med strukturreferencer, og vi har også fundet løsningen, men vi har endnu et problem her, vi kan ikke foretage opkaldet som en absolut reference, hvis vi trækker formlen til andre celler.
Lad os se på nedenstående eksempel nu. Jeg har en salgstabel med flere poster, og jeg vil konsolidere dataene ved hjælp af SUMIF-funktionen i excel.
Nu vil jeg anvende SUMIF-funktionen for at få de konsoliderede salgsværdier for hvert produkt.
Jeg har anvendt formlen i januar måned, da det er en struktureret reference, kan vi ikke kopiere og indsætte formlen i de resterende to kolonner, det ændrer ikke referencen til Feb & Mar, så jeg trækker formlen.
Åh !! Jeg fik ingen værdier i kolonnen Feb & Mar. Hvad ville være problemet ??? Se nøje på formlen.
Vi har trukket formlen fra januar måned. I SUMIF-funktionen er det første argument Criteria Range Sales_Table [Product], da vi trak formlen, det har ændringer til Sales _Table [Jan].
Så hvordan skal vi håndtere det ?? Vi er nødt til at gøre det første argument, dvs. produktkolonnen som absolut og andre kolonner som en relativ reference. I modsætning til normal reference har vi ikke den luksus at bruge F4-tasten til at ændre referencetypen.
Løsningen er, at vi har brug for at duplikere referencekolonnen som vist i nedenstående billede.
Nu kan vi trække formlen til andre reaming to kolonner. Kriterieområdet vil være konstant, og andre kolonnereferencer ændres i overensstemmelse hermed.
Pro-tip: For at gøre ROW som en absolut reference skal vi lave dobbelt ROW-indtastning, men vi skal sætte @ -symbolet foran ROW-navnet.
= Salgstabel [@ [Produkt]: [Produkt]]
Sådan slukkes struktureret reference i Excel?
Hvis du ikke er fan af strukturerede referencer, kan du slå den fra ved at følge nedenstående trin.
- Trin 1: Gå til FIL> Indstillinger.
- Trin 2: Formler> Fjern markeringen af Brug tabelnavne i formler.
Ting at huske
- For at gøre den absolutte reference i struktureret reference er vi nødt til at fordoble kolonnenavnet.
- Vi kan ikke kopiere formlen til den strukturerede reference i stedet for at trække formlen.
- Vi kan ikke se nøjagtigt, hvilken celle vi henviser til i strukturerede referencer.
- Hvis du ikke er interesseret i strukturerede referencer, kan du slå dem fra.