Microsoft Excel hefur nokkrar kaldar bragðarefur og með því að nota SUM og INDIRECT dynamic svið formúlurnar eru aðeins tvær leiðir til að auðveldlega vinna úr þeim gögnum sem þú hefur.
SUM - INDIRECT Formula Yfirlit
Notkun INNIRECT virka í Excel formúlur gerir það auðvelt að breyta fjölda reitum sem notaðir eru í formúlunni án þess að þurfa að breyta formúlunni sjálfu.
INDIRECT er hægt að nota með fjölda aðgerða sem samþykkja klefi tilvísun sem rök eins og OFFSET og SUM aðgerðir.
Í síðara tilvikinu, með því að nota INDIRECT sem rifrildi fyrir SUM-virknina, er hægt að búa til öflugt svið klefivísana sem SUM-aðgerðin bætir síðan við.
INDIRECT gerir þetta með því að vísa til gagna í frumum óbeint með millistaðsetningu.
Dæmi: SUM - INDIRECT Formúla notuð til að meta mörg gildi
Þetta dæmi byggist á gögnum sem sýndar eru í myndinni hér fyrir ofan.
SUM-INDIRECT formúlan sem búin er til með því að nota leiðbeiningarin hér að neðan er:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
Í þessari formúlu inniheldur rökin sem innihalda innbyggða INDIRECT-virkni tilvísanir í frumur E1 og E2. Tölurnar í þessum frumum, 1 og 4, þegar þau eru sameinuð með restinni af rökum INDIRECT, mynda klefatilvísanir D1 og D4.
Þar af leiðandi er fjöldi tölur sem eru í SUM-falli gögnin sem eru á bilinu frumna D1 til D4 - sem er 50.
Með því að breyta tölunum sem eru staðsettir í frumum E1 og E2; Hins vegar er hægt að breyta því bili sem hægt er að gera.
Þetta dæmi mun fyrst nota framangreind formúlu til að safna saman gögnum í frumum D1: D4 og breyta síðan samanlagðri bilinu D3: D6 án þess að breyta formúlunni í frumu F1.
01 af 03
Sláðu inn Formúlu - Valkostir
Valkostirnir til að slá inn formúluna eru:
- sláðu upp formúluna hér fyrir ofan beint í reit F1 og ýttu á Enter takkann á lyklaborðinu
- slá inn INDIRECT virknina sem rifrildi með valmyndinni SUM-aðgerðina
Flestar aðgerðir í Excel hafa valmynd, sem gerir þér kleift að slá inn hvert röksemdir aðgerðarinnar á sérstakri línu án þess að hafa áhyggjur af setningafræði .
Í þessu tilfelli er hægt að nota SUM-virknin til að einfalda formúluna að vissu marki. Vegna þess að INDIRECT aðgerðin er hreiður innan SUM, þá þarf að færa inn INDIRECT virknina og rökin hennar handvirkt.
Skrefin hér að neðan nota SUM valmyndina til að slá inn formúluna.
Sláðu inn kennsluupplýsingar
Cell gögn D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Sláðu inn eftirfarandi gögn í frumur D1 til E2
Byrjun SUM-INDIRECT Formúla - Opnun SUM Aðgerðir Dialog Box
- Smelltu á reit F1 - þetta er þar sem niðurstöðurnar af þessu dæmi verða birtar
- Smelltu á Formulas flipann á borði valmyndinni
- Veldu Stærðfræði og Trig úr borði til að opna fallgluggann
- Smelltu á SUM á listanum til að opna valmyndina
02 af 03
Slá inn INDIRECT virknina - Smelltu til að sjá stærri mynd
INDIRECT formúlan þarf að vera færð sem rök fyrir SUM aðgerðina.
Ef um er að ræða hreiður virka leyfir Excel ekki að opna valmyndina í annarri aðgerðinni til að slá inn rökin.
Því verður að slá inn INDIRECT virknuna í númer1 línu í valmyndinni SUM Function.
- Í glugganum, smelltu á Number1 línu
- Sláðu inn eftirfarandi INDIRECT virka: INDIRECT ("D" & E1 & ": D" & E2)
- Smelltu á Í lagi til að ljúka aðgerðinni og lokaðu valmyndinni
- Númerið 50 ætti að birtast í reit F1 þar sem þetta er heildar fyrir gögnin sem eru staðsett í frumum D1 til D4
- Þegar þú smellir á reit F1 er heildarsamsetningin = SUM (INDIRECT ("D" & E1 & ": D" & E2)) í formúlunni fyrir ofan vinnublað
Brjótast niður á óbeinan hátt
Til þess að búa til dynamic svið í dálki D með INDIRECT, verðum við að sameina stafinn D í röksemdum INDIRECT virksins með tölunum sem eru í frumum E1 og E2.
Þetta er gert með eftirfarandi:
- Ampersand ( & ) er notað til að sameina eða sameina textaupplýsingar (í þessu tilfelli stafurinn D) með klefi tilvísun (E1 og E2)
- Ennfremur skulu textatölur sem eru í samhengi við tilvísanir í reitinn vera umkringdur tvíteknum tilvitnunarmerkjum ( "" )
- Að lokum er lokapunktur sviðsins aðskilin með ristli (:)
Þess vegna er upphafspunktur sviðsins skilgreint með stafunum: "D" & E1 .
Annað sett af stöfum: ": D" & E2 sameinar ristillinn með endapunkta. Þetta er gert vegna þess að ristillinn er textareikningur og verður því að vera innifalinn í tilvitnunarmerkjum.
Þriðja amkandinn í miðjunni er notaður til að sameina tvær hlutar í eitt rök :
"D" & E1 & ": D" & E203 af 03
Dynamically Breyting svið SUM virka
Allt lið þessa formúlu er að gera það auðvelt að breyta sviðinu sem SUM-aðgerðin samanstendur af án þess að breyta röksemdafærslunni.
Með því að nota INDIRECT virka í formúlunni breytir tölurnar í frumum E1 og E2 breytingarsvið frumna sem lesin eru með SUM-aðgerðinni.
Eins og sést á myndinni hér að framan, leiðir þetta einnig til þess að svarið í formúlunni sem er staðsett í reit F1 breytist þar sem það samanstendur af nýjum gögnum.
- Smelltu á klefi E1
- Sláðu inn númerið 3
- Ýttu á Enter takkann á lyklaborðinu
- Smelltu á klefi E2
- Sláðu inn númer 6
- Ýttu á Enter takkann á lyklaborðinu
- Svarið í frumu F1 ætti að breytast í 90 - sem er heildar tölurnar í frumum D3 til D6
- Frekari prófið formúluna með því að breyta innihaldi frumna B1 og B2 við öll tölur á bilinu 1 og 6
INDIRECT og #REF! Villa gildi
The #REF! villa gildi birtist í reit F1 ef röksemdir INDIRECT virka:
- er ekki gild klefi tilvísun
- inniheldur ytri tilvísun í annan vinnubók og vinnubókin er ekki opnuð
- vísar til klefnisviðs utan ramma vinnublaðsins (röð 1.048.576 eða dálkur XFD)