Strukturoidut viitteet Excelissä Vaiheittainen opas esimerkkien kanssa

Kuinka luoda jäsenneltyjä viitteitä Excelissä?

Strukturoidut viitteet alkavat Excel-taulukoilla. Heti kun Excelissä luodut taulukot luovat sinulle automaattisesti jäsenneltyjä viitteitä.

Katsokaa nyt alla olevaa kuvaa.

  • Vaihe 1: Olin antanut linkin soluun B3 sen sijaan, että näytän linkin B2: ksi, se näkyy taulukossa 1 [@Sales]. Tässä Taulukko1 on taulukon nimi ja @Sales on sarake, johon viittaamme. Kaikkiin tämän sarakkeen soluihin viitataan taulukon nimellä ja sen jälkeen sarakkeen otsikon nimellä.
  • Vaihe 2: Nyt vaihdan taulukon nimeksi Data_Table ja vaihdan sarakkeen otsikkoon Määrä .
  • Vaihe 3: Muuta taulukon nimeä viemällä kohdistin taulukon sisälle> siirry kohtaan Suunnittelu> Taulukon nimi.

  • Vaihe 4: Mainitse taulukon nimi nimellä Data_Table.

  • Vaihe 5: Nyt muutos antaa viitteen B3-soluun.

Joten olemme ymmärtäneet, että jäsennetyssä viitteessä on kaksi osaa Taulukon nimi ja Sarakkeen nimi.

Esimerkkejä

Voit ladata tämän jäsenneltyjen viitteiden Excel-mallin täältä - Structured References Excel Template

Esimerkki 1

Käyttämällä jäsenneltyjä viitteitä voit tehdä kaavastasi dynaamisen. Toisin kuin tavalliset soluviitteet, se sallii kaavan elävän, jos dataa lisätään ja poistetaan.

Haluan soveltaa SUM-kaavaa sekä normaalialueelle että Excel-taulukkoon.

SUM-kaava normaalille alueelle.

Excel-taulukon SUM-kaava.

Haluan lisätä muutaman rivin sekä normaalin että Excel-taulukon tietoihin. Olen lisännyt tietoihin 2 rivikohtaa, katso nyt ero.

Excel-taulukon jäsennelty viite näyttää päivitetyn arvon, mutta normaali tietoalue ei näytä päivitettyjä arvoja, ellet tee joitain muutoksia kaavaan manuaalisesti.

Esimerkki 2

Katso nyt vielä yksi esimerkki. Minulla on tuotenimi, määrä ja hinta. Näiden tietojen avulla minun on päästävä myynti-arvoon.

Myyntiarvon saamiseksi kaava on Määrä * Hinta . Sovelletaan tätä kaavaa taulukossa.

Kaavan mukaan [@QTY] * [@PRICE]. Tämä on ymmärrettävämpää kuin normaali viite B2 * C2. Emme saa taulukon nimeä, jos laitamme kaavan taulukon sisälle.

Ongelmia Excelin jäsenneltyjen viitteiden kanssa

Kun käytämme jäsenneltyjä viitteitä, kohtaamme joitain alla lueteltuja ongelmia.

Ongelma # 1

Strukturoiduilla viitteillä on myös omat ongelmansa. Me kaikki tunnemme Excel-kaavan soveltamisen ja kopioimisen tai vetämisen muihin jäljellä oleviin soluihin. Tämä ei ole sama prosessi strukturoiduissa viitteissä, se toimii hieman eri tavalla.

Katso nyt alla olevaa esimerkkiä. Olen soveltanut SUM-kaavaa erinomaisesti normaalialueelle.

Jos haluan laskea yhteen hinta- ja myyntiarvon, kopioin ja liitän vain vetämällä nykyisen kaavan kahteen muuhun soluun ja se antaa minulle Hinta- ja myynti-arvon SUM-arvon.

Käytä nyt samaa kaavaa Excel-taulukkoon Määrä-sarakkeelle.

Nyt saimme Määrä-sarakkeen summan. Kuten normaalilla alueella, kopioi kaava nykyinen kaava ja liitä se Hinta-sarakkeeseen saadaksesi kokonaishinnan.

Herranjumala!!! Se ei näytä Hinta-sarakkeen kokonaismäärää, vaan näyttää edelleen vain Määrä-sarakkeen kokonaismäärän. Joten emme voi kopioida ja liittää tätä kaavaa viereiseen soluun tai mihinkään muuhun soluun viittaamaan suhteelliseen sarakkeeseen tai riviin.

Muuta viittausta vetämällä kaavaa

Nyt tiedämme sen rajoituksen, emme voi enää tehdä copy-paste-työtä strukturoiduilla viitteillä. Kuinka sitten voimme ylittää tämän rajoituksen?

Ratkaisu on hyvin yksinkertainen, meidän on vain vedettävä kaava kopioinnin sijaan. Valitse kaavasolu ja käytä täyttökahvaa ja vedä se kahteen muuhun soluun vaihtaaksesi sarakkeen viitteeksi hinta ja myynti -arvo.

Nyt meillä on päivitetyt kaavat vastaavien summien saamiseksi.

Ongelma # 2

Olemme havainneet yhden ongelman rakenneviitteissä ja löysimme myös ratkaisun, mutta meillä on vielä yksi ongelma, emme voi tehdä kutsua absoluuttisena viitteenä, jos vedämme kaavaa muihin soluihin.

Katsotaanpa nyt alla olevaa esimerkkiä. Minulla on myyntitaulukko, jossa on useita merkintöjä, ja haluan yhdistää tiedot käyttämällä SUMIF-funktiota excelissä.

Nyt käytän SUMIF-funktiota saadaksesi konsolidoidut myyntiarvot kullekin tuotteelle.

Olen soveltanut kaavaa tammikuussa, koska se on jäsennelty viite, emme voi kopioida ja liittää kaavaa kahteen jäljellä olevaan sarakkeeseen, se ei muuta viittausta helmi- ja maaliskuuhun, joten vedän kaavaa.

Vai niin!! En saanut mitään arvoja helmikuu ja maaliskuu -sarakkeessa. Mikä olisi ongelma ??? Tarkastele kaavaa tarkasti.

Olemme vetäneet kaavaa tammikuusta. SUMIF- funktion ensimmäinen argumentti on Ehtoja- alue Myyntotaulukko [Tuote],  koska vedimme kaavan, jolla se on muutoksia, kohtaan Myynti _Taulukko [Jan].

Joten miten voimme käsitellä sitä? Meidän on tehtävä ensimmäinen argumentti eli Tuotesarake absoluuttisena ja muut sarakkeet suhteellisena viitteenä. Toisin kuin normaalissa viitteessä, meillä ei ole ylellisyyttä käyttää F4-näppäintä viittaustyypin muuttamiseen.

Ratkaisu on, että meidän on kopioitava viitesarake alla olevan kuvan mukaisesti.

Nyt voimme vetää kaavan muihin sarakkeisiin. Kriteerialue on vakio ja muut sarakeviitteet muuttuvat vastaavasti.

Pro-vinkki: Jotta ROW olisi absoluuttinen viite, meidän on tehtävä kaksinkertainen ROW-merkintä, mutta meidän on lisättävä @ -merkki ROW-nimen eteen.

= Myyntitaulukko [@ [Tuote]: [Tuote]]

Kuinka poistaa jäsennelty viite käytöstä Excelissä?

Jos et ole jäsenneltyjen viitteiden fani, voit poistaa sen käytöstä noudattamalla seuraavia ohjeita.

  • Vaihe 1: Siirry TIEDOSTO> Asetukset.
  • Vaihe 2: Kaavat> Poista valinta Käytä taulukoiden nimiä kaavoissa.

Muistettavaa

  • Jotta absoluuttinen viite saadaan jäsennetyssä viitteessä, meidän on kaksinkertaistettava sarakkeen nimi.
  • Emme voi kopioida jäsennetyn viitteen kaavaa, vaan meidän on vedettävä kaavaa.
  • Emme näe tarkalleen mihin soluun viittaamme jäsennellyissä viitteissä.
  • Jos et ole kiinnostunut strukturoiduista viitteistä, voit poistaa ne käytöstä.