Mae gan Microsoft Excel driciau oer a defnyddio fformiwlâu ystod dynamig SUM a INDIRECT yn ddwy ffordd i drin y data sydd gennych yn hawdd.
SUM - INDIRECT Trosolwg Fformiwla
Mae defnyddio'r swyddogaeth INDIRECT mewn fformiwlâu Excel yn ei gwneud hi'n hawdd newid yr ystod o gyfeiriadau cell a ddefnyddir yn y fformiwla heb orfod olygu'r fformiwla ei hun.
Gellir defnyddio INDIRECT gyda nifer o swyddogaethau sy'n derbyn cyfeirnod cell fel dadl fel y OFFSET a swyddogaethau SUM.
Yn yr achos olaf, gall defnyddio INDIRECT fel y ddadl ar gyfer y swyddogaeth SUM greu ystod ddeinamig o gyfeiriadau cell y mae'r swyddog SUM wedyn yn ychwanegu ato.
Mae INDIRECT yn gwneud hyn trwy gyfeirio at y data mewn celloedd yn anuniongyrchol trwy leoliad canolradd.
Enghraifft: SUM - Fformiwla INDIRECT a ddefnyddir i Gyfanswm Amrediad Gwerthoedd Dynamig
Mae'r enghraifft hon yn seiliedig ar y data a ddangosir yn y ddelwedd uchod.
Y fformiwla SUM - INDIRECT a grëwyd trwy ddefnyddio'r camau tiwtorial isod yw:
= SUM (INDIRECT ("D" & E1 & ": D" ac E2))
Yn y fformiwla hon, mae dadl swyddogaeth INDIRECT nythedig yn cynnwys cyfeiriadau at gelloedd E1 ac E2. Mae'r niferoedd yn y celloedd hynny, 1 a 4, pan eu cyfuno â gweddill dadl INDIRECT, yn ffurfio cyfeiriadau cell D1 a D4.
O ganlyniad, yr ystod o rifau a gyfanswmwyd gan y swyddogaeth SUM yw'r data a gynhwysir yn yr ystod o gelloedd D1 i D4 - sef 50.
Trwy newid y niferoedd sydd wedi'u lleoli yng nghelloedd E1 ac E2; fodd bynnag, gellir newid yr ystod i gael ei gyfansymio'n hawdd.
Yn gyntaf, bydd yr enghraifft hon yn defnyddio'r fformiwla uchod i gyfanswm y data yn y celloedd D1: D4 ac yna newid yr ystod gryno i D3: D6 heb olygu'r fformiwla yn y gell F1.
01 o 03
Ymuno â'r Fformiwla - Opsiynau
Mae'r opsiynau ar gyfer mynd i mewn i'r fformiwla yn cynnwys:
- Teipio'r fformiwla uchod yn uniongyrchol i mewn i gell F1 a phwyso'r Allwedd Enter ar y bysellfwrdd
- gan fynd i mewn i'r swyddogaeth INDIRECT fel dadl gan ddefnyddio blwch deialog swyddogaeth SUM
Mae gan y rhan fwyaf o swyddogaethau yn Excel blwch deialog, sy'n eich galluogi i nodi pob un o ddadleuon y swyddogaeth ar linell ar wahân heb orfod poeni am gystrawen .
Yn yr achos hwn, gellir defnyddio blwch deialog swyddogaeth SUM i symleiddio'r fformiwla i ryw raddau. Oherwydd bod y swyddogaeth INDIRECT yn cael ei nythu y tu mewn i SUM, rhaid i'r swyddogaeth INDIRECT a'i dadleuon gael eu cofnodi o hyd.
Mae'r camau isod yn defnyddio'r blwch deialog SUM i fynd i mewn i'r fformiwla.
Mynd i'r Data Tiwtorial
Data Cell D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Rhowch y data canlynol i gelloedd D1 i E2
Dechrau'r SUM - Fformiwla INDIRECT - Agor Blwch Deialog Swyddog SUM
- Cliciwch ar gell F1 - dyma lle bydd canlyniadau'r enghraifft hon yn cael eu harddangos
- Cliciwch ar daflen Fformiwlâu'r ddewislen rhuban
- Dewiswch Mathemateg a Trig o'r rhuban i agor y rhestr ostwng swyddogaeth
- Cliciwch ar SUM yn y rhestr i agor blwch deialog y swyddogaeth
02 o 03
Mynd i'r Swyddogaeth INDIRECT - Cliciwch i Gweld Delwedd Mwy
Mae angen cofnodi'r fformiwla INDIRECT fel y ddadl dros y swyddogaeth SUM.
Yn achos swyddogaethau nythu, nid yw Excel yn caniatáu agor blwch deialog yr ail swyddogaeth i nodi ei ddadleuon.
Rhaid i'r swyddogaeth INDIRECT, felly, gael ei gofnodi â llaw yn llinell Number1 y blwch deialog SUM Function.
- Yn y blwch deialog, cliciwch ar linell Number1
- Nodwch y swyddogaeth INDIRECT canlynol: INDIRECT ("D" & E1 & ": D" ac E2)
- Cliciwch OK i gwblhau'r swyddogaeth a chau'r blwch deialog
- Dylai'r rhif 50 ymddangos yn y gell F1 gan mai dyma gyfanswm y data a leolir yng nghellion D1 i D4
- Pan fyddwch yn clicio ar gell F1, mae'r fformiwla gyflawn = SUM (INDIRECT ("D" & E1 & ": D" ac E2) yn ymddangos yn y bar fformiwla uwchben y daflen waith
Torri i lawr y Swyddog INDIRECT
Er mwyn creu ystod ddeinamig yng ngholofn D gan ddefnyddio INDIRECT, rhaid inni gyfuno'r llythyr D yn ddadl y swyddogaeth INDIRECT gyda'r niferoedd a gynhwysir yng nghelloedd E1 ac E2.
Gwneir hyn gan y canlynol:
- defnyddir yr ampersand ( & ) i gysatenu neu ymuno â data testun (yn yr achos hwn mae'r llythyr D) gyda chyfeirnod cell (E1 ac E2)
- ymhellach, rhaid i ddata testun sy'n cael ei gysoni â chyfeiriadau cell gael ei amgylchynu gan ddyfynodau dwbl ( "" )
- Yn olaf, mae pwyntiau terfyn yr amrediad yn cael eu gwahanu gan y colon (:)
Felly, mae pwynt cychwyn yr amrediad yn cael ei ddiffinio gan y cymeriadau: "D" ac E1 .
Mae'r ail set o gymeriadau: ": D" ac E2 yn cyfuno'r colon gyda'r pen draw. Gwneir hyn oherwydd bod y colon yn gymeriad testun ac felly mae'n rhaid ei gynnwys y tu mewn i ddyfynodau.
Defnyddir y trydydd ampersand yn y canol i concatenate'r ddwy ran yn un ddadl :
"D" ac E1 a ": D" ac E203 o 03
Newid yn Ddynamig Ystod y Swyddog SUM
Pwynt cyfan y fformiwla hon yw ei gwneud hi'n hawdd newid yr ystod a gyfansawddir gan y swyddogaeth SUM heb orfod olygu dadl y swyddogaeth.
Drwy gynnwys y swyddogaeth INDIRECT yn y fformiwla, bydd newid y niferoedd yn y celloedd E1 ac E2 yn newid ystod y celloedd a ddarllenir gan y swyddogaeth SUM.
Fel y gwelir yn y ddelwedd uchod, mae hyn hefyd yn arwain at ateb y fformiwla wedi'i leoli yng nghellell F1 yn newid gan ei fod yn cyfansymio'r ystod newydd o ddata.
- Cliciwch ar gell E1
- Teipiwch rif 3
- Gwasgwch yr allwedd Enter ar y bysellfwrdd
- Cliciwch ar gell E2
- Teipiwch rif 6
- Gwasgwch yr allwedd Enter ar y bysellfwrdd
- Dylai'r ateb yn y gell F1 newid i 90 - sef cyfanswm y niferoedd a gynhwysir yng nghellion D3 i D6
- Prawf ymhellach y fformiwla trwy newid cynnwys celloedd B1 a B2 i unrhyw rifau rhwng 1 a 6
INDIRECT a'r #REF! Gwall Gwerth
Mae'r #REF! bydd gwerth gwall yn ymddangos yn y gell F1 os yw dadl y swyddogaeth INDIRECT:
- Nid yw cyfeirnod cell dilys
- yn cynnwys cyfeiriad allanol at lyfr gwaith gwahanol ac nid yw'r llyfr gwaith hwnnw ar agor
- yn cyfeirio at ystod gell y tu allan i derfynau'r daflen waith (rhes 1,048,576 neu golofn XFD)