Hvordan lage strukturerte referanser i Excel?
Strukturerte referanser starter med excel-tabeller. Så snart tabellene er opprettet i Excel, oppretter det automatisk strukturerte referanser for deg.
Ta en titt på bildet nedenfor.
- Trinn 1: Jeg hadde gitt en lenke til cellen B3. I stedet for å vise lenken som B2, vises den som Table1 (@Sales). Her Table1 er navnet på bordet, og @Sales er kolonnen vi henviser til. Alle cellene i denne kolonnen er referert til med et tabellnavn og etterfulgt av kolonneoverskriften.
- Trinn 2: Nå vil jeg endre tabellnavnet til Data_Table og endre kolonneoverskriften til Beløp .
- Trinn 3: For å endre tabellnavnet, plasser en markør inne i tabellen> gå til Design> Tabellnavn.
- Trinn 4: Nevn tabellnavnet som Data_Table.
- Trinn 5: Endre nå en referanse til B3-cellen.
Så vi har forstått at strukturert referanse har to deler Tabellnavn og kolonnenavn.
Eksempler
Eksempel 1
Ved å bruke strukturerte referanser kan du gjøre formelen dynamisk. I motsetning til normale cellehenvisninger, tillater det at formelen er live i tilfelle tillegg og sletting i dataområdet.
La meg bruke SUM-formelen for både normalområde og Excel-tabell.
SUM Formula for Normal Range.
SUM Formula for Excel-tabell.
La meg legge til noen få linjer i dataene i både normale og excel-tabeller. Jeg har lagt til 2 ordrelinjer i dataene, se nå forskjellen.
Strukturert referanse i excel-tabellen viser den oppdaterte verdien, men det normale dataområdet viser ikke de oppdaterte verdiene med mindre du gjør noen endringer i formelen manuelt.
Eksempel 2
Se på et eksempel til. Jeg har informasjon om produktnavn, mengde og pris. Ved å bruke denne informasjonen må jeg komme til salgsverdien.
For å få salgsverdien er formelen Antall * Pris . La oss bruke denne formelen på tabellen.
Formula sier (@QTY) * (@PRICE). Dette er mer forståelig enn den normale referansen til B2 * C2. Vi får ikke tabellnavnet hvis vi legger formelen inne i tabellen.
Problemer med Excel-strukturerte referanser
Mens vi bruker strukturerte referanser, står vi overfor noen problemer som er oppført nedenfor.
Problem nr. 1
Strukturerte referanser har også sine egne problemer. Vi er alle kjent med å bruke Excel-formelen og kopiere eller dra den til de andre gjenværende cellene. Dette er ikke den samme prosessen i strukturerte referanser. Det fungerer litt annerledes.
Se nå på eksemplet nedenfor. Jeg har brukt SUM-formelen i excel for normalområdet.
Hvis jeg vil oppsummere pris og salgsverdi, vil jeg bare kopiere og lime inn eller dra den gjeldende formelen til de to andre cellene, og det vil gi meg SUM-verdien av pris og salgsverdi.
Bruk nå den samme formelen for excel-tabellen for Antall-kolonnen.
Nå har vi summen av Antall-kolonnen. Som vanlig område, kopier formelen den gjeldende formelen og lim den inn i Pris-kolonnen for å få den totale prisen.
Herregud!!! Det viser ikke summen av Pris-kolonnen; snarere viser den fremdeles bare summen av Antall-kolonnen. Så vi kan ikke kopiere og lime inn denne formelen til den tilstøtende cellen eller en hvilken som helst annen celle for å referere til den relative kolonnen eller raden.
Dra formelen for å endre referansen
Nå vet vi dens begrensning. Vi kan ikke gjøre jobben for kopiering og liming lenger med strukturerte referanser. Hvordan overvinner vi så denne begrensningen?
Løsningen er veldig enkel. Vi trenger bare å dra formelen i stedet for å kopiere. Velg formelcellen og bruk fyllhåndtaket og dra den til de resterende to cellene for å endre kolonnehenvisningen til Pris og salgsverdi.
Nå har vi oppdatert formler for å få de respektive totalene.
Oppgave 2
Vi har sett ett problem med strukturreferansene, og vi fant løsningen også, men vi har et problem til her, vi kan ikke ringe som en absolutt referanse hvis vi drar formelen til andre celler.
La oss ta en titt på eksemplet nedenfor nå. Jeg har en salgstabell med flere oppføringer, og jeg vil konsolidere dataene ved å bruke SUMIF-funksjonen i excel.
Nå vil jeg bruke SUMIF-funksjonen for å få de konsoliderte salgsverdiene for hvert produkt.
Jeg har brukt formelen i januar måned. Siden det er en strukturert referanse, kan vi ikke kopiere og lime inn formelen til de resterende to kolonnene. Det vil ikke endre referansen til Feb & Mar, så jeg vil dra formelen.
Åh!! Jeg fikk ingen verdier i kolonnen Feb & Mar. Hva ville være problemet ??? Se nøye på formelen.
Vi har dratt formelen fra januar måned. I SUMIF-funksjonen er det første argumentet Criteria Range Sales_Table (Product) siden vi dro formelen. Det har endret seg til Salg _Tabell (jan).
Så hvordan takler vi det ?? Vi må lage det første argumentet, dvs. produktkolonnen som absolutt og andre kolonner som en relativ referanse. I motsetning til vanlig referanse har vi ikke den luksusen å bruke F4-tasten til å endre referansetypen.
Løsningen er at vi trenger å duplisere referansekolonnen, som vist i bildet nedenfor.
Nå kan vi dra formelen til andre reaming to kolonner. Kriterieområdet vil være konstant, og andre kolonnereferanser vil endres tilsvarende.
Profftips: For å gjøre ROW som en absolutt referanse, må vi lage en dobbel ROW-oppføring, men vi må sette @ symbol foran ROW-navnet.
= Salgstabell (@ (produkt) :( produkt))
Hvordan slå av strukturert referanse i Excel?
Hvis du ikke er fan av strukturerte referanser, kan du slå av ved å følge trinnene nedenfor.
- Trinn 1: Gå til FIL> Alternativer.
- Trinn 2: Formler> Fjern merket for Bruk tabellnavn i formler.
Ting å huske
- For å gjøre den absolutte referansen i strukturert referanse, må vi doble kolonnenavnet.
- Vi kan ikke kopiere formelen for strukturert referanse; i stedet må vi dra formelen.
- Vi kan ikke se nøyaktig hvilken celle vi refererer til i strukturerte referanser.
- Hvis du ikke er interessert i strukturerte referanser, kan du slå dem av.
