Kuidas Excelis struktureeritud viiteid luua?
Struktureeritud viited algavad Exceli tabelitega. Niipea kui tabelid on Excelis loodud, loob see teile automaatselt struktureeritud viited.
Nüüd vaadake allolevat pilti.
- 1. samm: olin andnud lingi rakule B3. Selle asemel, et näidata linki kui B2, näidatakse seda kui tabelit1 (@Sales). Siin Tabel 1 on nimi tabeli ja @Sales on veeru oleme viidates. Kõigile selle veeru lahtritele viidatakse tabeli nimega ja neile järgneb veeru pealkirja nimi.
- 2. samm: nüüd muudan tabeli nime Data_Table-ks ja muudan veeru pealkirjaks Summa .
- 3. samm: tabeli nime muutmiseks asetage kursor tabeli sisse ja valige jaotis Kujundus> Tabeli nimi.
- 4. samm: mainige tabeli nime kui Data_Table.
- 5. samm: muutke nüüd viide lahtrile B3.
Nii oleme aru saanud, et struktureeritud viites on kaks osa Tabeli nimi ja veeru nimi.
Näited
Näide 1
Struktureeritud viidete abil saate oma valemi muuta dünaamiliseks. Erinevalt tavalistest lahtriviidetest võimaldab see valemit andmevahemikus lisamise ja kustutamise korral kasutada.
Lubage mul rakendada SUM valemit nii normaalse vahemiku kui ka Exceli tabeli jaoks.
SUM valem normaalse vahemiku jaoks.
Exceli tabeli valem SUM.
Lubage mul lisada paar rida nii tavaliste kui ka Exceli tabelite andmetele. Lisasin andmetele 2 rida, nüüd vaadake erinevust.
Exceli tabeli struktureeritud viide näitab värskendatud väärtust, kuid tavaline andmevahemik ei näita värskendatud väärtusi, kui te ei tee valemis käsitsi muudatusi.
Näide 2
Vaadake nüüd veel ühte näidet. Mul on teavet toote nime, koguse ja hinna kohta. Selle teabe abil pean jõudma müügiväärtuseni.
Müügiväärtuse saamiseks on valem Kogus * Hind . Rakendame seda valemit tabelile.
Vormel ütleb (@QTY) * (@PRICE). See on arusaadavam kui B2 * C2 normaalne viide . Me ei saa tabeli nime, kui paneme valemi tabeli sisse.
Probleemid Exceli struktureeritud viidetega
Struktureeritud viidete kasutamisel seisame silmitsi mõningate probleemidega, mis on loetletud allpool.
Probleem nr 1
Struktureeritud viidetel on ka omad probleemid. Oleme kõik tuttavad Exceli valemi rakendamise ja selle kopeerimise või lohistamise teistele ülejäänud lahtritele. See pole struktureeritud viidetes sama protsess. See töötab natuke teisiti.
Nüüd vaadake allpool toodud näidet. Olen tavalises vahemikus rakendanud valemit SUM excelis.
Kui ma tahan kokku võtta hinna ja müügiväärtuse, siis lihtsalt kopeerin ja kleepin või lohistan praeguse valemi kahte teise lahtrisse ja see annab mulle hinna ja müügi väärtuse SUM väärtuse.
Rakendage nüüd veeru Kogus Exceli tabeli jaoks sama valemit.
Nüüd saime veeru Kogusumma. Sarnaselt tavalisele vahemikule kopeerige valem praegune valem ja kleepige see veergu Hind, et saada kogu hind.
Oh mu jumal!!! See ei näita veeru Hind koguarvu; pigem näitab see ikkagi ainult veeru Kogus koguarvu. Niisiis, me ei saa seda valemit kopeerida ja kleepida kõrvalolevasse lahtrisse või mõnda teise lahtrisse, et viidata suhtelisele veerule või reale.
Viite muutmiseks lohistage valemit
Nüüd teame selle piiratust. Struktureeritud viidetega ei saa me enam copy-paste tööd teha. Kuidas siis sellest piirangust üle saada?
Lahendus on väga lihtne. Peame kopeerimise asemel lihtsalt valemit lohistama. Valige valemi lahter ja kasutage täitekäepidet ning lohistage see ülejäänud kahele lahtrile, et muuta veeru viide hinnale ja müügiväärtusele.
Nüüd on vastavate kogusummade saamiseks värskendatud valemeid.
Probleem nr 2
Struktuuriviidetega oleme näinud ühte probleemi ja leidsime ka lahenduse, kuid meil on siin veel üks probleem, kui me lohistame valemit teistele lahtritele, ei saa me kõnet absoluutse viitena teha.
Vaatame nüüd allpool toodud näidet. Mul on mitme kirjega müügitabel ja soovin andmed koondada Excelis funktsiooni SUMIF abil.
Nüüd rakendan iga toote konsolideeritud müügiväärtuste saamiseks funktsiooni SUMIF.
Olen valinud jaanuari kuu valemi. Kuna see on struktureeritud viide, ei saa me valemit kahte ülejäänud veergu kopeerida ja kleepida. See ei muuda viidet veebruarile ja märtsile, seega lohistan valemi.
Oh !! Veebruari ja märtsi veergu ei leitud ühtegi väärtust. Milles oleks probleem ??? Vaadake tihedalt valemit.
Oleme valemit lohistanud jaanuarikuust. Funktsiooni SUMIF esimeseks argumendiks on kriteeriumivahemiku müügitabel (toode), kuna me lohistasime valemit. See on muutunud müügi _tabeliks (jaanuar).
Kuidas me siis sellega hakkama saame ?? Peame tegema esimese argumendi, st veeru Product absoluutse ja muud veerud suhtelise viitena. Erinevalt tavalisest viitest pole meil luksust kasutada viitetüübi muutmiseks klahvi F4.
Lahendus on see, et peame dubleerima viiteveeru, nagu on näidatud alloleval pildil.
Nüüd saame valemi lohistada teistesse kahe veeru reameerimiseks. Kriteeriumivahemik on konstantne ja muud veeruviited muutuvad vastavalt.
Pro-näpunäide: ROW-i absoluutseks võrdlusviisiks tegemiseks peame tegema topelt-ROW-sisestuse, kuid ROW-nime ette tuleb panna sümbol @.
= Müügitabel (@ (toode) :( toode))
Kuidas Excelis struktureeritud viide välja lülitada?
Kui te ei ole struktureeritud viidete fänn, saate selle välja lülitada, järgides alltoodud samme.
- 1. samm: minge FILE> Options.
- 2. samm: valemid> Tühjendage märkeruut Kasutage valemites tabelite nimesid.
Asjad, mida meeles pidada
- Struktureeritud viites absoluutse viite tegemiseks peame veeru nime kahekordistama.
- Me ei saa struktureeritud viite valemit kopeerida; selle asemel peame valemit lohistama.
- Me ei näe struktureeritud viidetes täpselt, millisele lahtrile viidame.
- Kui teid struktureeritud viited ei huvita, saate need välja lülitada.
