Kuinka luoda jäsenneltyjä viitteitä Excelissä?
Strukturoidut viitteet alkavat Excel-taulukoilla. Heti kun taulukot luodaan Excelissä, se luo sinulle automaattisesti jäsenneltyjä viitteitä.
Katsokaa nyt alla olevaa kuvaa.
- Vaihe 1: Olin antanut linkin soluun B3. Sen sijaan, että linkki näytetään 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: Muutan nyt taulukon nimeksi Data_Table ja vaihdan sarakkeen otsikkoon määrän .
- Vaihe 3: Muuta taulukon nimeä viemällä kohdistin taulukon sisään> 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ä
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 taulukkoon.
Kaava sanoo (@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 ongelmia, jotka on lueteltu alla.
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 vähän eri tavalla.
Katso nyt alla olevaa esimerkkiä. Olen soveltanut SUM-kaavaa erinomaisesti normaalialueelle.
Jos haluan laskea yhteen hinta- ja myynti-arvon, 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ää; pikemminkin se 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ä kopioi-liitä-työtä strukturoiduilla viitteillä. Kuinka sitten voimme ylittää tämän rajoituksen?
Ratkaisu on hyvin yksinkertainen. Meidän on vain vedettävä kaavaa 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 otan SUMIF-funktion saadaksesi konsolidoidut myyntiarvot kullekin tuotteelle.
Olen soveltanut kaavaa tammikuulle. 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 Criteria Range Sales_Table (Product), koska vedimme kaavaa. Se on muuttunut Myynnin _taulukoksi (tammikuu).
Joten miten voimme käsitellä sitä? Meidän on tehtävä ensimmäinen argumentti, ts. 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, kuten alla olevassa kuvassa on esitetty.
Nyt voimme vetää kaavan muihin sarakkeisiin. Kriteerialue on vakio, ja muut sarakeviitteet muuttuvat vastaavasti.
Ammattilaisvinkki: 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 sammuttaa sen noudattamalla seuraavia vaiheita.
- 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; sen sijaan 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ä.
