Microsoft Excel ima nekaj kul trikov in z uporabo SUM in INDIRECT formul za dinamični razpon sta le dva načina za preprosto manipulacijo s podatki, ki jih imate.
SUM - INDIRECT Pregled formule
Uporaba funkcije INDIRECT v formulah Excel omogoča enostavno spreminjanje obsega referenčnih celic, uporabljenih v formuli, ne da bi morali urediti samo formulo.
INDIRECT se lahko uporablja s številnimi funkcijami, ki sprejemajo referenco celice kot argument, kot so funkcije OFFSET in SUM.
V slednjem primeru lahko s funkcijo INDIRECT kot argument za funkcijo SUM ustvarite dinamično območje referenc celic, ki se nato doda funkcija SUM.
INDIRECT to naredi tako, da podatke v celicah posredno posreduje prek vmesne lokacije.
Primer: SUM - INDIRECT Formula, uporabljena za skupno dinamično območje vrednosti
Ta primer temelji na podatkih, prikazanih na zgornji sliki.
Formula SUM - INDIRECT, ustvarjena s koraki spodaj, je:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
V tej formuli ugnezdeni argument funkcije INDIRECT vsebuje reference na celice E1 in E2. Številke v teh celicah, 1 in 4, v kombinaciji s preostalim argumentom INDIRECT-a, tvorijo reference celic D1 in D4.
Kot rezultat, obseg številk, ki jih ustvari funkcija SUM , so podatki v obsegu celic D1 do D4 - kar je 50.
S spreminjanjem številk v celicah E1 in E2; vendar je mogoče obseg, ki ga je treba ujemati, enostavno spremeniti.
Ta primer bo najprej uporabil zgornjo formulo za združevanje podatkov v celicah D1: D4 in nato spremeniti povzeto razpon na D3: D6 brez urejanja formule v celici F1.
01 od 03
Vstop v formulo - Možnosti
Možnosti za vnos formule vključujejo:
- natipkamo zgornjo formulo neposredno v celico F1 in pritisnemo tipko Enter na tipkovnici
- vstop v funkcijo INDIRECT kot argument s pogovorno okno funkcije SUM
Večina funkcij v Excelu ima pogovorno okno, ki vam omogoča vnos vseh argumentov funkcije v ločeni vrstici, ne da bi morali skrbeti za sintakso .
V tem primeru se pogovorno okno funkcije SUM lahko uporablja za poenostavitev formule v določeni meri. Ker je funkcija INDIRECT vgrajena v SUM, mora biti funkcija INDIRECT in njeni argumenti še vedno vneseni ročno.
Spodnji koraki uporabijo pogovorno okno SUM za vnos formule.
Vnos podatkov o vadnicah
Podatki celice D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- V celice D1 do E2 vnesite naslednje podatke
Zagon SUM - INDIRECT Formule - Odpiranje pogovornega okna funkcije SUM
- Kliknite na celico F1 - tukaj bodo prikazani rezultati tega primera
- Kliknite jeziček Formule v meniju traku
- Od traku izberite Math & Trig, da odprete spustni seznam funkcij
- Na seznamu kliknite SUM, da odprete pogovorno okno funkcije
02 od 03
Vstop v funkcijo INDIRECT - Kliknite, če si želite ogledati večjo sliko
Formulo INDIRECT je treba vnesti kot argument za funkcijo SUM.
V primeru ugnezdenih funkcij Excel ne dovoljuje odpiranja pogovornega okna druge funkcije za vnos svojih argumentov.
Funkcijo INDIRECT je zato treba ročno vpisati v vrstici Number1 v pogovornem oknu SUM Function.
- V pogovornem oknu kliknite na številko 1
- Vnesite to funkcijo INDIRECT: INDIRECT ("D" in E1 & ": D" in E2)
- Za dokončanje funkcije kliknite gumb V redu in zaprite pogovorno okno
- Številka 50 se mora pojaviti v celici F1, ker je to skupna vrednost za podatke, ki se nahajajo v celicah D1 do D4
- Ko kliknete celico F1, se v vrstici s formulo nad delovnim listom prikaže popolna formula = SUM (INDIRECT ("D" in E1 & ": D" in E2))
Prekinitev funkcije INDIRECT
Da bi ustvarili dinamično območje v stolpcu D z uporabo INDIRECT, moramo v argumentu funkcije INDIRECT združiti črko D s številkami v celicah E1 in E2.
To dosežemo z naslednjim:
- ampersand ( & ) se uporablja za združevanje ali združevanje besedilnih podatkov (v tem primeru črke D) s sklicem celice (E1 in E2)
- Poleg tega morajo biti podatki o besedilu, ki so povezani s sklicevanjem na celice, obkroženi z dvojnimi narekovaji ( "" )
- Nazadnje, končne točke območja so ločene z dvopičjem (:)
Zato je začetna točka območja določena z znaki: "D" in E1 .
Drugi niz znakov: ": D" & E2 združuje debelo črevo s končno točko. To se naredi, ker je debelo črevo besedilni znak, zato ga je treba vključiti v narekovaje.
Tretji ampersand in sredi se uporabljajo za združevanje obeh delov v en argument :
"D" & E1 & ": D" & E203 od 03
Dinamično spreminjanje obsega funkcije SUM
Celotna točka te formule je, da je enostavno spremeniti obseg, ki ga ustvari funkcija SUM, ne da bi morali urediti argument funkcije.
Z vključitvijo funkcije INDIRECT v formulo lahko spreminjanje števila v celicah E1 in E2 spremeni obseg celic, ki jih prebere funkcija SUM.
Kot je razvidno iz zgornje slike, to tudi povzroči, da se odgovor v formuli, ki se nahaja v celici F1, spremeni, saj pomeni nov obseg podatkov.
- Kliknite na celico E1
- Vnesite številko 3
- Pritisnite tipko Enter na tipkovnici
- Kliknite na celico E2
- Vnesite številko 6
- Pritisnite tipko Enter na tipkovnici
- Odgovor v celici F1 se mora spremeniti na 90 - kar je skupno število v celicah D3 do D6
- Nadalje preizkusite formulo s spreminjanjem vsebine celic B1 in B2 na katerokoli številko med 1 in 6
INDIRECT in #REF! Vrednost napake
#REF! se bo v celici F1 pojavila vrednost napake, če je argument funkcije INDIRECT:
- ni veljavna referenca celic
- vsebuje zunanji sklic na drug delovni zvezek in ta delovni zvezek ni odprt
- se nanaša na obseg celic zunaj omejitev delovnega lista (vrstica 1.048.576 ali stolpec XFD)