Microsoft Excel het 'n paar cool truuks en die gebruik van die SUM- en INDIRECT-dinamiese reeks formules is net twee maniere om die data wat jy het maklik te manipuleer.
SUM - INDIREKTE Formule Oorsig
Met behulp van die INDIRECT-funksie in Excel maak formules dit maklik om die reeks selverwysings wat in die formule gebruik word, te verander sonder om die formule self te verander.
INDIRECT kan gebruik word met 'n aantal funksies wat 'n selverwysing aanvaar as 'n argument soos die OFFSET en SUM funksies.
In laasgenoemde geval kan die gebruik van INDIRECT as die argument vir die SUM funksie 'n dinamiese reeks selverwysings skep wat die SUM-funksie dan optel.
INDIRECT doen dit deur indirek na die data in selle te verwys deur middel van 'n intermediêre ligging.
Voorbeeld: SUM - INDIRECT Formule gebruik om 'n dinamiese reeks waardes te bereik
Hierdie voorbeeld is gebaseer op die data wat in die prent hierbo getoon word.
Die SUM - INDIREKTE formule wat geskep is deur die tutoriaal stappe hieronder te gebruik, is:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
In hierdie formule bevat die geneste INDIRECT-funksie se verwysings na selle E1 en E2. Die getalle in die selle 1 en 4, wanneer dit gekombineer word met die res van INDIRECT se argument, vorm die selverwysings D1 en D4.
Gevolglik is die aantal getalle wat deur die SUM-funksie behaal word, die data in die reeks selle D1 tot D4, wat 50 is.
Deur die getalle in selle E1 en E2 te verander; Die omvang wat getel kan word, kan egter maklik verander word.
Hierdie voorbeeld sal eers die bostaande formule gebruik om die data in selle D1: D4 te totaal en verander dan die opgesomde reeks na D3: D6 sonder om die formule in sel F1 te verander.
01 van 03
Die Formule - Opsies ingevoer
Die opsies vir die invoer van die formule sluit in:
- Tik die bostaande formule direk in sel F1 en druk die Enter-sleutel op die sleutelbord
- voer die INDIRECT funksie as 'n argument in deur die SUM funksie se dialoog te gebruik
Die meeste funksies in Excel het 'n dialoogkassie, wat u toelaat om elkeen van die funksies se argumente op 'n aparte reël te betree sonder om oor sintaksis te bekommer.
In hierdie geval kan die SUM-funksie se dialoog gebruik word om die formule tot 'n sekere mate te vereenvoudig. Omdat die INDIRECT-funksie in SUM genesteer word, moet die INDIRECT-funksie en sy argumente nog steeds handmatig ingevoer word.
Die stappe hieronder gebruik die SUM dialoog om die formule in te voer.
Voer die tutoriaal data in
Sel data D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Gee die volgende data in selle D1 tot E2
Begin van die SUM - INDIRECT Formule - Open die SUM Funksie Dialoogkassie
- Klik op sel F1 - dit is waar die resultate van hierdie voorbeeld vertoon sal word
- Klik op die Formules- oortjie van die lintmenu
- Kies Math & Trig uit die lint om die aftreklys vir funksies oop te maak
- Klik op SUM in die lys om die dialoog van die funksie oop te maak
02 van 03
Die INDIRECT-funksie invoer - Klik om groter prent te sien
Die INDIRECT formule moet ingevoer word as die argument vir die SUM funksie.
In die geval van geneste funksies kan Excel nie die dialoog van die tweede funksie oopmaak om sy argumente te betree nie.
Die INDIRECT-funksie moet dus handmatig ingevoer word in die Number1- reël van die SUM-funksie se dialoog.
- Klik in die dialoogkassie op die Number1 reël
- Voer die volgende INDIRECT-funksie in: INDIRECT ("D" & E1 & ": D" & E2)
- Klik OK om die funksie te voltooi en sluit die dialoogkassie
- Die getal 50 moet in sel F1 verskyn, aangesien dit die totaal is vir die data wat in selle D1 tot D4 geleë is
- As jy op sel F1 klik, verskyn die volledige formule = SUM (INDIRECT ("D" & E1 & ": D" & E2)) in die formulebalk bokant die werkblad
Breek die INDIREKTE funksie af
Om 'n dinamiese omvang in kolom D te skep deur gebruik te maak van INDIRECT, moet ons die letter D in die INDIRECT-funksie se argument kombineer met die getalle wat in selle E1 en E2 voorkom.
Dit word bereik deur die volgende:
- Die ampersand ( & ) word gebruik om teksdata (in hierdie geval die letter D) met 'n selverwysing (E1 en E2) saam te voeg of saam te voeg.
- Verder moet teksdata wat met selverwysings gekonsentreer word, omring word deur dubbele aanhalingstekens ( "" )
- Laastens word die eindpunte van die reeks geskei deur die kolon (:)
Daarom word die beginpunt van die reeks gedefinieer deur die karakters: "D" en E1 .
Die tweede stel karakters: ": D" & E2 kombineer die kolon met die eindpunt. Dit word gedoen omdat die dubbelpunt 'n tekskarakter is en dus binne die aanhalingstekens ingesluit moet word.
Die derde ampersand in die middel word gebruik om die twee dele in een argument saam te vat:
"D" & E1 & ": D" & E203 van 03
Dinamiese verandering van die SUM-funksie se omvang
Die hele punt van hierdie formule is om dit maklik te maak om die omvang wat deur die SUM-funksie behaal word, te verander sonder om die funksie se argument te verander.
Deur die INDIRECT funksie in die formule in te sluit, verander die getalle in selle E1 en E2 die reeks selle wat deur die SUM-funksie gelees word, verander.
Soos in die bostaande beeld gesien kan word, lei dit ook tot die formule se antwoord wat in sel F1 verander word, aangesien dit die nuwe reeks data bevat.
- Klik op sel E1
- Tik die nommer 3
- Druk die Enter-sleutel op die sleutelbord
- Klik op sel E2
- Tik die nommer 6
- Druk die Enter-sleutel op die sleutelbord
- Die antwoord in sel F1 moet verander na 90 - wat is die totaal van die getalle wat in selle D3 tot D6 voorkom
- Verdere toets die formule deur die inhoud van selle B1 en B2 te verander na enige getalle tussen 1 en 6
INDIRECT en die #REF! Foutwaarde
Die #REF! fout waarde sal in sel F1 verskyn as die INDIRECT funksie se argument:
- is nie 'n geldige selverwysing nie
- bevat 'n eksterne verwysing na 'n ander werkboek en die werkboek is nie oop nie
- verwys na 'n selreeks buite die werkblad se perke (ry 1,048,576 of kolom XFD)