Sut i osod ystod o werthoedd yn Excel

Wrth ddefnyddio Microsoft Excel, yn aml iawn nid yw'r defnyddiwr yn gwybod ymlaen llaw faint o wybodaeth fydd yn y tabl o ganlyniad. Felly, nid ydym yn deall ym mhob sefyllfa pa ystod y dylid ei chwmpasu. Wedi'r cyfan, mae set o gelloedd yn gysyniad amrywiol. I gael gwared ar y broblem hon, mae angen gwneud yr ystod cynhyrchu yn awtomatig fel ei fod yn seiliedig yn unig ar faint o ddata a gofnodwyd gan y defnyddiwr.

Newid ystodau celloedd yn awtomatig yn Excel

Mantais ystodau awtomatig yn Excel yw eu bod yn ei gwneud hi'n llawer haws defnyddio fformiwlâu. Yn ogystal, maent yn ei gwneud yn bosibl i symleiddio'n sylweddol y dadansoddiad o ddata cymhleth sy'n cynnwys nifer fawr o fformiwlâu, sy'n cynnwys llawer o swyddogaethau. Gallwch chi roi enw i'r ystod hon, ac yna bydd yn cael ei diweddaru'n awtomatig yn dibynnu ar ba ddata sydd ynddo.

Sut i wneud newid amrediad awtomatig yn Excel

Tybiwch eich bod yn fuddsoddwr sydd angen buddsoddi mewn rhyw wrthrych. O ganlyniad, rydym am gael gwybodaeth am faint y gallwch ei ennill am yr holl amser y bydd yr arian yn gweithio ar gyfer y prosiect hwn. Serch hynny, er mwyn cael y wybodaeth hon, mae angen inni fonitro'n rheolaidd faint o elw y mae'r gwrthrych hwn yn ei roi i ni. Gwnewch yr un adroddiad ag yn y sgrinlun hwn.

Ar yr olwg gyntaf, mae'r ateb yn amlwg: does ond angen i chi grynhoi'r golofn gyfan. Os bydd cofnodion yn ymddangos ynddo, bydd y swm yn cael ei ddiweddaru'n annibynnol. Ond mae gan y dull hwn lawer o anfanteision:

1. Os caiff y broblem ei datrys yn y modd hwn, ni fydd yn bosibl defnyddio'r celloedd a gynhwysir yng ngholofn B at ddibenion eraill.
2. Bydd bwrdd o'r fath yn defnyddio llawer o RAM, a fydd yn ei gwneud hi'n amhosibl defnyddio'r ddogfen ar gyfrifiaduron gwan.

Felly, mae angen datrys y broblem hon trwy enwau deinamig. Er mwyn eu creu, rhaid i chi berfformio'r dilyniant canlynol o gamau gweithredu:

1. Ewch i'r tab "Fformiwlâu", sydd wedi'i leoli yn y brif ddewislen. Bydd adran “Enwau Diffiniedig”, lle mae botwm “Assign a name”, y mae angen i ni glicio arno.
2. Nesaf, bydd blwch deialog yn ymddangos lle mae angen i chi lenwi'r meysydd fel y dangosir yn y sgrin. Mae'n bwysig nodi bod angen i ni gymhwyso'r swyddogaeth = DADLEUON ynghyd â'r swyddogaeth GWIRIOi greu ystod diweddaru awtomatig. 
3. Ar ôl hynny mae angen i ni ddefnyddio'r swyddogaeth SUM, sy'n defnyddio ein hystod ddeinamig fel dadl. 

Ar ôl cwblhau'r camau hyn, gallwn weld sut mae cwmpas y celloedd sy'n perthyn i'r ystod “incwm” yn cael ei ddiweddaru wrth i ni ychwanegu elfennau newydd yno.

Swyddogaeth OFFSET yn Excel

Gadewch i ni edrych ar y swyddogaethau a gofnodwyd gennym yn y maes “ystod” yn gynharach. Gan ddefnyddio'r swyddogaeth GWAREDU gallwn bennu faint o amrediad o ystyried faint o gelloedd yng ngholofn B sydd wedi'u llenwi. Mae'r dadleuon swyddogaeth fel a ganlyn:

1. Cychwyn cell. Gyda'r ddadl hon, gall y defnyddiwr nodi pa gell yn yr ystod fydd yn cael ei hystyried ar y chwith uchaf. Bydd yn adrodd i lawr ac i'r dde.
2. Ystod wedi'i wrthbwyso gan resi. Gan ddefnyddio'r ystod hon, rydym yn gosod nifer y celloedd y dylai gwrthbwyso ddigwydd o'r gell chwith uchaf yn yr ystod. Gallwch ddefnyddio nid yn unig gwerthoedd cadarnhaol, ond sero a minws. Yn yr achos hwn, efallai na fydd y dadleoli yn digwydd o gwbl, neu fe'i cynhelir i'r cyfeiriad arall.
3. Ystod wedi'i wrthbwyso gan golofnau. Mae'r paramedr hwn yn debyg i'r un blaenorol, dim ond mae'n caniatáu ichi osod gradd symudiad llorweddol yr ystod. Yma gallwch hefyd ddefnyddio gwerthoedd sero a negyddol.
4. Swm yr ystod mewn uchder. Mewn gwirionedd, mae teitl y ddadl hon yn ei gwneud yn glir i ni beth mae'n ei olygu. Dyma nifer y celloedd y dylai'r amrediad gynyddu yn eu herbyn.
5. Gwerth yr ystod mewn lled. Mae'r ddadl yn debyg i'r un flaenorol, dim ond y colofnau y mae'n ymwneud â hi.

Nid oes angen i chi nodi'r ddwy ddadl olaf os nad oes angen i chi wneud hynny. Yn yr achos hwn, dim ond un gell fydd y gwerth amrediad. Er enghraifft, os byddwch yn nodi'r fformiwla =OFFSET(A1; 0; 0), bydd y fformiwla hon yn cyfeirio at yr un gell â'r un yn y ddadl gyntaf. Os yw'r gwrthbwyso fertigol wedi'i osod i 2 uned, yna yn yr achos hwn bydd y gell yn cyfeirio at gell A3. Nawr gadewch i ni ddisgrifio'n fanwl beth mae'r swyddogaeth yn ei olygu GWIRIO.

Swyddogaeth COUNT yn Excel

Gan ddefnyddio'r swyddogaeth GWIRIO rydym yn pennu faint o gelloedd yng ngholofn B rydym wedi'u llenwi i gyd. Hynny yw, gan ddefnyddio dwy swyddogaeth, rydym yn pennu faint o gelloedd yn yr ystod sy'n cael eu llenwi, ac yn seiliedig ar y wybodaeth a dderbyniwyd, yn pennu maint yr amrediad. Felly, y fformiwla derfynol fydd y canlynol: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Gadewch i ni edrych ar sut i ddeall egwyddor y fformiwla hon yn gywir. Mae'r ddadl gyntaf yn pwyntio at ble mae ein hystod ddeinamig yn dechrau. Yn ein hachos ni, cell B2 yw hon. Mae gan baramedrau pellach ddim cyfesurynnau. Mae hyn yn awgrymu nad oes angen gwrthbwyso arnom o'i gymharu â'r gell chwith uchaf. Y cyfan yr ydym yn ei lenwi yw maint fertigol yr amrediad, y gwnaethom ddefnyddio'r swyddogaeth fel GWIRIO, sy'n pennu nifer y celloedd sy'n cynnwys rhywfaint o ddata. Y pedwerydd paramedr y gwnaethom ei lenwi yw'r uned. Felly rydym yn dangos y dylai cyfanswm lled yr amrediad fod yn un golofn.

Felly, gan ddefnyddio'r swyddogaeth GWIRIO gall y defnyddiwr ddefnyddio'r cof mor effeithlon â phosibl trwy lwytho dim ond y celloedd hynny sy'n cynnwys rhai gwerthoedd. Yn unol â hynny, ni fydd unrhyw wallau ychwanegol yn y gwaith sy'n gysylltiedig â pherfformiad gwael y cyfrifiadur y bydd y daenlen yn gweithio arno.

Yn unol â hynny, er mwyn pennu maint yr ystod yn dibynnu ar nifer y colofnau, mae angen i chi berfformio dilyniant tebyg o gamau gweithredu, dim ond yn yr achos hwn mae angen i chi nodi'r uned yn y trydydd paramedr, a'r fformiwla yn y pedwerydd paramedr. GWIRIO.

Gwelwn, gyda chymorth fformiwlâu Excel, nid yn unig y gallwch chi awtomeiddio cyfrifiadau mathemategol. Dim ond gostyngiad yn y cefnfor yw hwn, ond mewn gwirionedd maen nhw'n caniatáu ichi awtomeiddio bron unrhyw weithrediad sy'n dod i'r meddwl.

Siartiau deinamig yn Excel

Felly, yn y cam olaf, roeddem yn gallu creu ystod ddeinamig, y mae ei maint yn dibynnu'n llwyr ar faint o gelloedd wedi'u llenwi sydd ynddo. Nawr, yn seiliedig ar y data hwn, gallwch greu siartiau deinamig a fydd yn newid yn awtomatig cyn gynted ag y bydd y defnyddiwr yn gwneud unrhyw newidiadau neu'n ychwanegu colofn neu res ychwanegol. Mae'r dilyniant o gamau gweithredu yn yr achos hwn fel a ganlyn:

1. Rydyn ni'n dewis ein hystod, ac ar ôl hynny rydyn ni'n mewnosod siart o'r math “Histogram gyda grwpio”. Gallwch ddod o hyd i'r eitem hon yn yr adran “Mewnosod” yn yr adran “Siartiau – Histogram”.
2. Rydyn ni'n gwneud clic chwith y llygoden ar golofn ar hap o'r histogram, ac ar ôl hynny bydd y ffwythiant = CYFRES() yn cael ei ddangos yn llinell y ffwythiant. Yn y screenshot gallwch weld y fformiwla fanwl. 
3. Ar ôl hynny, mae angen gwneud rhai newidiadau i'r fformiwla. Mae angen i chi ddisodli'r ystod ar ôl "Taflen 1!" i enw'r ystod. Bydd hyn yn arwain at y swyddogaeth ganlynol: =ROW(Taflen1!$B$1;;Taflen1!incwm;1)
4. Nawr mae angen ychwanegu cofnod newydd i'r adroddiad i wirio a yw'r siart yn cael ei diweddaru'n awtomatig ai peidio.

Gadewch i ni edrych ar ein diagram.

Gadewch i ni grynhoi sut y gwnaethom hynny. Yn y cam blaenorol, fe wnaethom greu ystod ddeinamig, y mae ei maint yn dibynnu ar faint o elfennau sydd ynddo. I wneud hyn, defnyddiwyd cyfuniad o swyddogaethau GWIRIO и GWAREDU. Gwnaethom enwi'r amrediad hwn, ac yna defnyddiwyd y cyfeiriad at yr enw hwn fel ystod ein histogram. Nid yw pa ystod benodol i'w dewis fel ffynhonnell ddata yn y cam cyntaf mor bwysig. Y prif beth yw rhoi enw'r amrediad yn ei le yn ddiweddarach. Fel hyn gallwch arbed llawer o RAM.

Ystodau a enwir a'u defnydd

Gadewch i ni nawr siarad yn fanylach am sut i greu ystodau a enwir yn gywir a'u defnyddio i gyflawni'r tasgau a osodir ar gyfer y defnyddiwr Excel.

Yn ddiofyn, rydym yn defnyddio cyfeiriadau cell rheolaidd er mwyn arbed amser. Mae hyn yn ddefnyddiol pan fydd angen i chi ysgrifennu ystod unwaith neu fwy. Os oes angen ei ddefnyddio drwy'r amser neu os oes angen iddo fod yn addasol, yna dylid defnyddio ystodau a enwir. Maent yn gwneud creu fformiwlâu yn llawer haws, ac ni fydd mor anodd i'r defnyddiwr ddadansoddi fformiwlâu cymhleth sy'n cynnwys nifer fawr o swyddogaethau. Gadewch i ni ddisgrifio rhai o'r camau sydd ynghlwm wrth greu ystodau deinamig.

Mae'r cyfan yn dechrau gydag enwi'r gell. I wneud hyn, dewiswch ef, ac yna ysgrifennwch yr enw sydd ei angen arnom ym maes ei enw. Mae'n bwysig ei fod yn hawdd i'w gofio. Mae rhai cyfyngiadau i’w hystyried wrth enwi:

1. Yr hyd mwyaf yw 255 nod. Mae hyn yn ddigon i neilltuo enw y mae eich calon yn ei ddymuno.
2. Ni ddylai'r enw gynnwys bylchau. Felly, os yw'n cynnwys sawl gair, yna mae'n bosibl eu gwahanu gan ddefnyddio'r nod tanlinellu.

Os bydd angen i ni arddangos y gwerth hwn neu ei gymhwyso i wneud cyfrifiadau pellach yn ddiweddarach ar ddalennau eraill y ffeil hon, yna nid oes angen newid i'r ddalen gyntaf un. Gallwch chi ysgrifennu enw'r gell ystod hon.

Y cam nesaf yw creu ystod a enwir. Mae'r weithdrefn yr un peth yn y bôn. Yn gyntaf mae angen i chi ddewis ystod, ac yna nodi ei enw. Ar ôl hynny, gellir defnyddio'r enw hwn ym mhob gweithrediad data arall yn Excel. Er enghraifft, defnyddir ystodau a enwir yn aml i ddiffinio swm y gwerthoedd.

Yn ogystal, mae'n bosibl creu ystod a enwir gan ddefnyddio'r tab Fformiwlâu gan ddefnyddio'r offeryn Enw Gosod. Ar ôl i ni ei ddewis, bydd ffenestr yn ymddangos lle mae angen i ni ddewis enw ar gyfer ein hystod, yn ogystal â nodi'r ardal y bydd yn ymestyn iddi â llaw. Gallwch hefyd nodi lle bydd yr ystod hon yn gweithredu: o fewn un ddalen neu drwy gydol y llyfr.

Os yw ystod enw eisoes wedi'i chreu, yna er mwyn ei ddefnyddio, mae gwasanaeth arbennig o'r enw rheolwr enw. Mae'n caniatáu nid yn unig olygu neu ychwanegu enwau newydd, ond hefyd eu dileu os nad oes eu hangen mwyach.

Dylid cofio, wrth ddefnyddio ystodau a enwir mewn fformiwlâu, yna ar ôl ei ddileu, ni fydd y fformiwlâu yn cael eu trosysgrifo'n awtomatig gyda'r gwerthoedd cywir. Felly, gall gwallau ddigwydd. Felly, cyn dileu ystod a enwir, mae angen i chi sicrhau nad yw'n cael ei ddefnyddio yn unrhyw un o'r fformiwlâu.

Ffordd arall o greu ystod a enwir yw ei gael o dabl. I wneud hyn, mae teclyn arbennig o'r enw "Creu o Ddewis". Fel y deallwn, er mwyn ei ddefnyddio, rhaid i chi yn gyntaf ddewis yr ystod y byddwn yn ei olygu, ac yna gosod y man y mae gennym y penawdau ynddo. O ganlyniad, yn seiliedig ar y data hwn, bydd Excel yn prosesu'r holl ddata yn awtomatig, a bydd teitlau'n cael eu neilltuo'n awtomatig.

Os yw'r teitl yn cynnwys sawl gair, bydd Excel yn eu gwahanu'n awtomatig gyda thanlinell.

Felly, gwnaethom ddarganfod sut i greu ystodau deinamig a enwir a sut maent yn caniatáu ichi awtomeiddio gwaith gyda llawer iawn o ddata. Fel y gallwch weld, mae'n ddigon defnyddio sawl swyddogaeth a'r offer rhaglen sydd wedi'u hymgorffori yn y swyddogaeth. Nid oes unrhyw beth cymhleth o gwbl, er y gall ymddangos felly i ddechreuwr ar yr olwg gyntaf.