Hvordan lage strukturerte referanser i Excel?
Strukturerte referanser starter med excel-tabeller. Så snart tabellene som er opprettet i Excel, opprettes 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 Tabell1 [@Sales]. Her er Tabell 1 navnet på tabellen og @ Salg er kolonnen vi refererer til. Alle cellene i denne kolonnen er henvist med et tabellnavn og etterfulgt av kolonneoverskriftens navn.
- 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: Nå endrer gi en referanse til B3-cellen.
Så vi har forstått at strukturert referanse har to deler Tabellnavn og kolonnenavn.
Eksempler
Du kan laste ned denne Excel-malen for strukturerte referanser her - Strukturerte referanser Excel-malenEksempel 1
Ved å bruke strukturerte referanser kan du gjøre formelen din 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 normal og excel-tabell. 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 i tabellen.
Formel 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
Når du 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, den fungerer litt annerledes.
Se nå på eksemplet nedenfor. Jeg har brukt SUM-formelen i Excel for det normale området.
Hvis jeg vil oppsummere pris og salgsverdi, vil jeg bare kopiere og lime inn eller dra gjeldende formel 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, men den viser fortsatt bare antall 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 har også funnet løsningen, 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. Den vil ikke endre referansen til februar og mars, 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 den har endret til Sales _Table [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 normal 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 gjøre dobbel ROW-oppføring, men vi må sette @ -symbolet 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.