"Microsoft Excel" turi keletą puikių triukų, o "SUM" ir "INDIRECT" dinaminio diapazono formulių naudojimas yra tik du būdai lengvai manipuliuoti turimais duomenimis.
SUM - Netiesioginė formulės apžvalga
Naudojant NETIESIOS funkciją "Excel" formules lengva pakeisti formulėje naudojamų ląstelių nuorodų diapazoną, nereikia redaguoti pačios formulės.
INDIRECT gali būti naudojamas su daugybe funkcijų, kurios priima ląstelės nuorodą kaip argumentą, pvz., OFFSET ir SUM funkcijas.
Pastaruoju atveju naudojant INDIRECT kaip SUM funkcijos argumentą galima sukurti dinaminį ląstelių nuorodų diapazoną, kurį vėliau papildo SUM funkcija.
Netiesioginis tai daro netiesiogiai nurodydamas ląstelių duomenis per tarpinę vietą.
Pavyzdys: SUM - NETIESIOGINĖ formulė, naudojama visuminiam dinaminiam vertės diapazonui
Šis pavyzdys pagrįstas duomenimis, pateiktais aukščiau esančiame paveikslėlyje.
SUM - NETIESIOGINĖ formulė, sukurta naudojant toliau paminėtus žingsnius, yra:
= SUM (NETIESIOGINIS ("D" & E1 & ": D" & E2))
Šioje formulėje nesudėtingos netiesioginės funkcijos argumentas yra nuorodos į ląsteles E1 ir E2. Šių ląstelių skaičius 1 ir 4, derinant su likusiu INDIRECT argumentu, sudaro langelių nuorodas D1 ir D4.
Todėl skaičių diapazonas, susidedantis iš SUM funkcijos, yra duomenys, esantys ląstelių D1-D4 diapazone , ty 50.
Pakeitus numerius, esančius ląstelėse E1 ir E2; Vis dėlto gali būti lengvai pakeistas diapazonas.
Šiame pavyzdyje pirmiausia naudosime aukščiau pateiktą formulę, kad gautume duomenis iš ląstelių D1: D4, o po to pakeiskite suminį diapazoną į D3: D6, redaguodami formulę lange F1.
01 iš 03
Formulės įvedimas - parinktys
Galimybės įvesti formulę apima:
- pirmiau pateiktą formulę įveskite tiesiai į ląstelę F1 ir paspauskite Enter klavišą
- į NETIRECT funkciją įvedate kaip argumentą naudodami SUM funkcijos dialogo langą
Daugelyje Excel funkcijų yra dialogo langas, kuris leidžia įvesti kiekvieną funkcijos argumentą atskiroje eilutėje, nesijaudindamas sintaksės .
Tokiu atveju, tam tikru mastu formulę galima supaprastinti SUM funkcijos dialogo langą. Kadangi netiesioginė funkcija yra įdėta į SUM viduje, NETIRTOS funkcija ir jos argumentai vis tiek turi būti įvesti rankiniu būdu.
Žemiau aprašyti veiksmai naudoja SUM dialogo langą, norėdami įvesti formulę.
Įvadas į mokymo duomenis
Ląstelių duomenys D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Įrašykite šiuos duomenis į ląsteles nuo D1 iki E2
SUM - NETIESIOGINĖS FORMOS paleidimas - SUM funkcijos dialogo lango atidarymas
- Spustelėkite langelį F1 - čia bus rodomi šio pavyzdžio rezultatai
- Spustelėkite skirtuką Formulės juostelės meniu
- Pasirinkite " Math & Trig" iš juostos, kad atidarytumėte išskleidžiamąjį sąrašą
- Spustelėkite SUM sąrašą, kad atidarytumėte funkcijos dialogo langą
02 iš 03
Netiesioginio funkcijos įvedimas - spustelėkite norėdami pamatyti didesnį vaizdą
Netiesioginė formulė turi būti įvesta kaip SUM funkcijos argumentas.
Lanksčių funkcijų atveju "Excel" neleidžia atidaryti antrosios funkcijos dialogo lango, norint įvesti jo argumentus.
Todėl netiesioginė funkcija turi būti įvesta rankiniu būdu SUM funkcijos dialogo lango eilutėje Number1 .
- Dialogo lange spustelėkite eilutės numerį1
- Įveskite šią NETIESIOGINĘ funkciją: NETIESIOGINIAI ("D" & E1 & ": D" & E2)
- Spustelėkite Gerai, kad užbaigtumėte funkciją ir uždarytumėte dialogo langą
- Numeris 50 turėtų pasirodyti lange F1, nes tai yra duomenų, esančių ląstelėse nuo D1 iki D4, bendra
- Kai paspausite langelį F1, formulės juostoje virš darbalapio pasirodys visa formulė = SUM (netiesiogiai ("D" & E1 & ": D" & E2)
Netiesioginės funkcijos nutraukimas
Siekiant sukurti dinaminį diapazoną D skiltyje, naudojant NETIESIOGINĮ, mes turime derinti D raidę NETIESIOS Funkcijos argumentu su skaičiais, esančiais ląstelėse E1 ir E2.
Tai pasiekiama šiais būdais:
- ampersandas ( & ) naudojamas sujungti tekstinius duomenis (šiuo atveju raidę D) su ląstelės nuoroda (E1 ir E2)
- Be to, tekstiniai duomenys, kurie jungiami su ląstelių nuorodomis, turi būti aptraukti dvigubomis kabutėmis ( "" )
- galiausiai diapazono pabaigos taškai yra atskirti dvitaškio (:)
Todėl diapazono pradžios tašką apibrėžia simboliai: "D" ir E1 .
Antrasis simbolių rinkinys: ": D" & E2 apjungia apatinę žarną su galiniu tašku. Tai daroma, nes dvitaškis yra teksto pobūdis ir todėl turi būti įtrauktas į kabutes.
Trečioji ampersanda viduryje yra naudojama susieti dvi dalis į vieną argumentą :
"D" & E1 & ": D" & E203 iš 03
Dinaminis SUM funkcijos diapazono keitimas
Visas šios formulės tikslas - lengvai pakeisti SUM funkcijos diapazoną, nereikia redaguoti funkcijos argumentų.
Į formulę įtraukiant NETIESIOS funkciją, pakeitus skaičių ląstelėse E1 ir E2 bus pakeista ląstelių, skaitomų pagal SUM funkciją, diapazonas.
Kaip matyti paveikslėlyje aukščiau, tai taip pat lemia formulės atsakymą, esantį ląstelėje F1, nes ji susumuoja naują duomenų diapazoną.
- Spustelėkite langelį E1
- Įveskite skaičių 3
- Paspauskite klaviatūros klavišą Enter
- Spustelėkite langelį E2
- Įveskite skaičių 6
- Paspauskite klaviatūros klavišą Enter
- F1 ląstelių atsakymas turėtų keistis iki 90, ty iš skaičių, esančių ląstelėse nuo D3 iki D6
- Toliau išbandykite formulę pakeiskite ląstelių B1 ir B2 turinį į bet kuriuos skaičius tarp 1 ir 6
NETIESIOGIAI ir #REF! Klaida
#REF! klaida vertė pasirodys langelyje F1, jei netiesioginio funkcijos argumentas:
- nėra galiojančios nuorodos langelyje
- yra išorinė nuoroda į kitą darbo knygą, o darbo knyga nėra atvira
- nurodo ląstelių diapazoną už darbalapio ribų (1,048,576 eilutė arba XFD stulpelis)