30 Excel aðgerðir á 30 dögum: ÓBEIN

Efnisyfirlit

Virkni 30: ÓBEIN
Dæmi 1: Búðu til upphafstengil sem ekki breytist
Dæmi 2: Tengill á kyrrstætt nafngreint svið
Dæmi 3: Búðu til tengil með því að nota blað, línu og dálkupplýsingar
Dæmi 4: Búðu til óbreytt fylki af tölum

Til hamingju! Þú komst á lokadag maraþonsins 30 Excel aðgerðir á 30 dögum. Þetta hefur verið langt og áhugavert ferðalag þar sem þú hefur lært margt gagnlegt um Excel aðgerðir.

Á 30. degi maraþonsins munum við helga rannsókn á fallinu ÓBEIN (ÓBEIN), sem skilar hlekknum sem textastrengurinn tilgreinir. Með þessari aðgerð geturðu búið til óháða fellilista. Til dæmis, þegar land er valið af fellilista ákvarðar hvaða valkostir munu birtast í fellilistanum fyrir borgina.

Svo skulum við líta nánar á fræðilega hluta fallsins ÓBEIN (ÓBEIN) og skoðaðu hagnýt dæmi um beitingu þess. Ef þú hefur frekari upplýsingar eða dæmi, vinsamlegast deildu þeim í athugasemdunum.

Virkni 30: ÓBEIN

virka ÓBEIN (INDIRECT) skilar hlekknum sem textastrengurinn tilgreinir.

Hvernig geturðu notað ÓBEINAR aðgerðina?

Þar sem aðgerðin ÓBEIN (ÓBEIN) skilar tengli sem gefinn er með textastreng, þú getur notað hann til að:

Búðu til upphafstengil sem ekki breytist.
Búðu til tilvísun í fast heitt svið.
Búðu til tengil með því að nota upplýsingar um blað, línu og dálk.
Búðu til óbreytt fylki af tölum.

Setningafræði ÓBEIN (ÓBEIN)

virka ÓBEIN (ÓBEIN) hefur eftirfarandi setningafræði:

INDIRECT(ref_text,a1)

ДВССЫЛ(ссылка_на_ячейку;a1)

ref_texti (link_to_cell) er texti hlekksins.
a1 – ef það er jafnt og TRUE (TRUE) eða ekki tilgreint, þá verður stíll hlekksins notaður A1; og ef FALSE (FALSE), þá stíllinn R1C1.

Gildrur ÓBEINAR (ÓBEINAR)

virka ÓBEIN (ÓBEIN) er endurreiknað þegar gildin í Excel vinnublaðinu breytast. Þetta getur mjög hægt á vinnubókinni ef aðgerðin er notuð í mörgum formúlum.
Ef aðgerðin ÓBEIN (ÓBEIN) býr til tengil á aðra Excel vinnubók, sú vinnubók verður að vera opin annars mun formúlan tilkynna um villu #REF! (#LINK!).
Ef aðgerðin ÓBEIN (ÓBEIN) vísar til sviðs sem fer yfir línu- og dálkamörk, formúlan mun tilkynna um villu #REF! (#LINK!).
virka ÓBEIN (INDIRECT) getur ekki vísað til kviks heits sviðs.

Dæmi 1: Búðu til upphafstengil sem ekki breytist

Í fyrsta dæminu innihalda dálkar C og E sömu tölur, upphæðir þeirra reiknaðar með fallinu SUMMA (SUM) eru líka þau sömu. Hins vegar eru formúlurnar aðeins öðruvísi. Í reit C8 er formúlan:

=SUM(C2:C7)

=СУММ(C2:C7)

Í reit E8 er fallið ÓBEIN (ÓBEIN) býr til tengil á upphafsreit E2:

=SUM(INDIRECT("E2"):E7)

=СУММ(ДВССЫЛ("E2"):E7)

Ef þú setur inn línu efst á blaðinu og bætir við gildinu fyrir janúar (jan), þá breytist upphæðin í dálki C ekki. Formúlan mun breytast og bregst við því að línu er bætt við:

=SUM(C3:C8)

=СУММ(C3:C8)

Hins vegar virka ÓBEIN (ÓBEIN) lagar E2 sem upphafshólf, þannig að janúar er sjálfkrafa innifalinn í útreikningi á heildartölum dálks E. Lokahólfið hefur breyst en upphafshólfið hefur ekki orðið fyrir áhrifum.

=SUM(INDIRECT("E2"):E8)

=СУММ(ДВССЫЛ("E2"):E8)

Dæmi 2: Tengill á kyrrstætt nafngreint svið

virka ÓBEIN (ÓBEIN) getur búið til tilvísun í nefnt svið. Í þessu dæmi mynda bláu frumurnar bilið Númeralisti. Að auki er einnig búið til kraftmikið svið úr gildunum í dálki B NumListDyn, allt eftir fjölda númera í þessum dálki.

Summan fyrir bæði svið er hægt að reikna út með því einfaldlega að gefa nafn þess sem rök fyrir fallinu SUMMA (SUM), eins og þú sérð í hólfum E3 og E4.

=SUM(NumList) или =СУММ(NumList)

=SUM(NumListDyn) или =СУММ(NumListDyn)

Í stað þess að slá inn sviðsheiti í fall SUMMA (SUM), Þú getur vísað til nafnsins sem er skrifað í einum af hólfum vinnublaðsins. Til dæmis ef nafnið Númeralisti er skrifað í reit D7, þá verður formúlan í reit E7 svona:

=SUM(INDIRECT(D7))

=СУММ(ДВССЫЛ(D7))

Því miður aðgerðin ÓBEIN (ÓBEIN) getur ekki búið til tilvísun á kraftmikið svið, þannig að þegar þú afritar þessa formúlu niður í reit E8 færðu villu #REF! (#LINK!).

Dæmi 3: Búðu til tengil með því að nota blað, línu og dálkupplýsingar

Þú getur auðveldlega búið til tengil sem byggir á línu- og dálknúmerum, auk þess að nota gildið FALSE (FALSE) fyrir seinni föllin ÓBEIN (ÓBEIN). Svona er stíltengillinn búinn til R1C1. Í þessu dæmi bættum við blaðheitinu við hlekkinn – 'MyLinks'!R2C2

=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)

=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)

Dæmi 4: Búðu til óbreytt fylki af tölum

Stundum þarftu að nota fjölda af tölum í Excel formúlum. Í eftirfarandi dæmi viljum við taka meðaltal af 3 stærstu tölunum í dálki B. Tölurnar má slá inn í formúlu eins og gert er í reit D4:

=AVERAGE(LARGE(B1:B8,{1,2,3}))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))

Ef þú þarft stærra fylki, þá er ólíklegt að þú viljir slá inn allar tölurnar í formúlunni. Annar kosturinn er að nota aðgerðina UMFERÐ (ROW), eins og gert er í fylkisformúlunni sem færð var inn í reit D5:

=AVERAGE(LARGE(B1:B8,ROW(1:3)))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))

Þriðji valkosturinn er að nota aðgerðina UMFERÐ (STRING) ásamt ÓBEIN (ÓBEIN), eins og gert er með fylkisformúlunni í reit D6:

=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))

Niðurstaðan fyrir allar 3 formúlurnar verður sú sama:

Hins vegar, ef línur eru settar inn efst á blaði, mun önnur formúlan skila rangri niðurstöðu vegna þess að tilvísanir í formúlunni breytast samhliða röðinni. Nú, í stað meðaltals þriggja stærstu talnanna, skilar formúlan meðaltali 3., 4. og 5. stærstu tölunnar.

Að nota aðgerðir ÓBEIN (ÓBEIN), þriðja formúlan heldur réttum línutilvísunum og heldur áfram að sýna rétta niðurstöðu.