Pan fydd gennych fformiwla gymhleth sy'n dibynnu ar newidynnau lluosog ac eisiau deall sut mae newid y mewnbynnau hynny'n effeithio ar y canlyniadau'n effeithlon, mae tabl data dadansoddi Beth-Os yn Excel yn arf pwerus. Mae'n caniatáu ichi weld yr holl ganlyniadau posibl yn gyflym. Dyma ganllaw cam wrth gam ar sut i greu tabl data yn Excel. Yn ogystal, byddwn yn trafod rhai pwyntiau allweddol ar gyfer defnyddio tablau data yn effeithiol ac yn dangos gweithrediadau eraill fel dileu, golygu ac ailgyfrifo tablau data.

Beth yw tabl data yn Excel?

Creu tabl data un newidyn

• Tabl data sy'n canolbwyntio ar golofnau
• Tabl data rhes-gyfeiriedig
• Fformiwlâu lluosog ar gyfer tabl data un-newidyn

Creu tabl data dau newidyn

Pwyntiau allweddol gan ddefnyddio'r tablau data

Gweithrediadau eraill ar gyfer defnyddio tablau data

• Dileu tabl data
• Golygu canlyniad tabl data
• Ailgyfrifwch y tabl data â llaw

Beth yw tabl data yn Excel?

Yn Excel, mae tabl data yn un o'r offer Dadansoddi Beth-Os sy'n eich galluogi i arbrofi gyda gwahanol werthoedd mewnbwn ar gyfer fformiwlâu ac arsylwi'r newidiadau yn allbwn y fformiwla. Mae'r offeryn hwn yn hynod ddefnyddiol ar gyfer archwilio senarios amrywiol a chynnal dadansoddiadau sensitifrwydd, yn enwedig pan fo fformiwla'n dibynnu ar newidynnau lluosog.

Mae dau fath o dablau data yn Excel:

Tabl Data Un-Amrywiadwy: Mae hyn yn eich galluogi i ddadansoddi sut y bydd gwerthoedd gwahanol un newidyn yn effeithio ar fformiwla. Gallwch chi sefydlu tabl data un newidyn naill ai mewn fformat sy'n canolbwyntio ar y rhes neu ar y golofn, yn dibynnu a ydych chi am amrywio'ch mewnbwn ar draws rhes neu i lawr colofn.

Tabl Data Dau-Amrywiadwy: Mae'r math hwn yn eich galluogi i weld effaith newid dau newidyn gwahanol ar ganlyniad fformiwla. Mewn tabl data dau newidyn, rydych chi'n amrywio'r gwerthoedd ar hyd rhes a cholofn.

Creu tabl data un newidyn

Mae creu tabl data un-newidyn yn Excel yn sgil gwerthfawr ar gyfer dadansoddi sut y gall newidiadau mewn un mewnbwn effeithio ar ganlyniadau amrywiol. Bydd yr adran hon yn eich arwain trwy'r broses o greu tablau data amrywiol sy'n canolbwyntio ar golofnau, rhes, ac aml-fformiwla un.

Anhygoel! Defnyddio Tabiau Effeithlon yn Excel Fel Chrome, Edge, Firefox a Safari!
Arbedwch 50% o'ch amser, a lleihau miloedd o gliciau llygoden i chi bob dydd!

---

Tabl data sy'n canolbwyntio ar golofnau

Mae tabl data â gogwydd colofn yn ddefnyddiol pan fyddwch am brofi sut mae gwerthoedd gwahanol newidyn unigol yn effeithio ar allbwn fformiwla, gyda'r gwerthoedd newidiol wedi'u rhestru i lawr colofn. Gadewch i ni ystyried enghraifft ariannol syml:

Tybiwch eich bod yn ystyried benthyciad o $50,000, yr ydych yn bwriadu ei ad-dalu dros gyfnod o 3 blynedd (cyfwerth â 36 mis). Er mwyn gwerthuso pa daliad misol fyddai'n fforddiadwy yn seiliedig ar eich cyflog, mae gennych ddiddordeb mewn archwilio sut y byddai cyfraddau llog amrywiol yn effeithio ar y swm y mae angen i chi ei dalu bob mis.

Cam 1: Gosodwch y fformiwla sylfaenol

I gyfrifo'r taliad, yma, byddaf yn gosod y llog fel 5%. Mewn cell B4, nodwch y fformiwla PMT, mae'n cyfrifo'r taliad misol yn seiliedig ar y gyfradd llog, nifer y cyfnodau, a swm y benthyciad. Gweler y sgrinlun:

= -PMT($B$1/12, $B$2*12, $B$3)

Cam 2: Rhestrwch y cyfraddau llog mewn colofn

Mewn colofn, rhestrwch y gwahanol gyfraddau llog rydych chi am eu profi. Er enghraifft, rhestrwch werthoedd o 4% i 11% mewn cynyddrannau o 1% mewn colofn, a gadewch o leiaf un golofn wag ar y dde ar gyfer y canlyniadau. Gweler y sgrinlun:

Cam 3: Creu'r tabl data

1. Yn y gell E2, rhowch y fformiwla hon: = B4.
   
   Nodyn: B4 yw'r gell lle mae'ch prif fformiwla wedi'i lleoli, dyma'r fformiwla yr ydych am weld ei chanlyniadau'n newid wrth i chi amrywio'r gyfradd llog.
   
2. Dewiswch yr ystod sy'n cynnwys eich fformiwla, rhestr o gyfraddau llog a chelloedd cyfagos ar gyfer y canlyniadau (ee, dewiswch D2 i E10). Gweler y sgrinlun:
   
3. Ewch i'r rhuban, cliciwch ar y Dyddiad tab, yna cliciwch Dadansoddiad Beth-Os > Tabl Data, gweler y screenshot:
   
4. Yn y Tabl Data blwch deialog, cliciwch yn y Cell Mewnbwn Colofn blwch (gan ein bod yn defnyddio colofn ar gyfer gwerthoedd mewnbwn), a dewiswch y gell newidiol y cyfeirir ati yn eich fformiwla. Yn yr enghraifft hon, rydym yn dewis B1 sy'n cynnwys y gyfradd llog. O'r diwedd, cliciwch OK botwm, gweler y screenshot:
   
5. Bydd Excel yn llenwi'r celloedd gwag yn awtomatig wrth ymyl pob un o'ch gwerthoedd amrywiol (cyfraddau llog gwahanol) gyda'r canlyniadau cyfatebol. Gweler y sgrinlun:
   
6. Cymhwyswch y fformat rhif dymunol i'r canlyniadau (Arian cyfred) i'ch angen. Nawr, mae'r tabl data sy'n canolbwyntio ar golofnau'n cael ei greu'n llwyddiannus, a gallwch chi ddarllen y canlyniadau'n gyflym i werthuso pa symiau talu misol fyddai'n fforddiadwy i chi pan fydd y gyfradd llog yn newid. Gweler y sgrinlun:
   

Tabl data rhes-gyfeiriedig

Mae creu tabl data rhes yn Excel yn golygu trefnu eich data fel bod y gwerthoedd newidiol yn cael eu rhestru ar draws rhes yn hytrach nag i lawr colofn. Gan gymryd yr enghraifft uchod fel cyfeiriad, gadewch i ni symud ymlaen â'r camau i gwblhau creu tabl data rhes-oriented yn Excel.

Cam 1: Rhestrwch y cyfraddau llog yn olynol

Rhowch y gwerthoedd newidiol (cyfraddau llog) mewn rhes, gan sicrhau bod o leiaf un golofn wag i'r chwith ar gyfer y fformiwla ac un rhes wag isod ar gyfer y canlyniadau, gweler y llun:

Cam 2: Creu'r tabl data

1. Yn y gell A9, rhowch y fformiwla hon: = B4.
   
2. Dewiswch yr ystod sy'n cynnwys eich fformiwla, rhestr o gyfraddau llog a chelloedd cyfagos ar gyfer y canlyniadau (ee, dewiswch A8 i I9). Yna, cliciwch Dyddiad > Dadansoddiad Beth-Os > Tabl Data.
3. Yn y Tabl Data blwch deialog, cliciwch yn y Cell mewnbwn rhes blwch (gan ein bod yn defnyddio rhes ar gyfer gwerthoedd mewnbwn), a dewiswch y gell newidiol y cyfeirir ati yn eich fformiwla. Yn yr enghraifft hon, rydym yn dewis B1 sy'n cynnwys y gyfradd llog. O'r diwedd, cliciwch OK botwm, gweler y screenshot:
   
4. Cymhwyswch y fformat rhif dymunol i'r canlyniadau (Arian cyfred) i'ch angen. Nawr, mae'r tabl data sy'n canolbwyntio ar y rhes yn cael ei greu, gweler y sgrinlun:
   

Fformiwlâu lluosog ar gyfer tabl data un-newidyn

Mae creu tabl data un-newidyn gyda fformiwlâu lluosog yn Excel yn caniatáu ichi weld sut mae newid mewnbwn sengl yn effeithio ar sawl fformiwlâu gwahanol ar unwaith. Yn yr enghraifft uchod, beth os ydych am weld y newid yn y cyfraddau llog ar yr ad-daliadau a chyfanswm y llog? Dyma sut i'w sefydlu:

Cam 1: Ychwanegu fformiwla newydd i gyfrifo cyfanswm y llog

Yng nghell B5, rhowch y fformiwla ganlynol i gyfrifo cyfanswm y llog:

=B4*B2*12-B3

Cam 2: Trefnwch ddata ffynhonnell y tabl data

Mewn colofn, rhestrwch y gwahanol gyfraddau llog rydych am eu profi, a gadewch o leiaf ddwy golofn wag ar y dde ar gyfer y canlyniadau. Gweler y sgrinlun:

Cam 3: Creu tabl data:

1. Yng nghell E2, rhowch y fformiwla hon: = B4 i greu cyfeiriad at y cyfrifiad ad-daliad yn y data gwreiddiol.
2. Yng nghell F2, rhowch y fformiwla hon: = B5 i greu cyfeiriad at gyfanswm y diddordeb yn y data gwreiddiol.
   
3. Dewiswch yr ystod sy'n cynnwys eich fformiwlâu, rhestr o gyfraddau llog a chelloedd cyfagos ar gyfer y canlyniad (e.e., dewiswch D2 i F10). Yna, cliciwch Dyddiad > Dadansoddiad Beth-Os > Tabl Data.
4. Yn y Tabl Data blwch deialog, cliciwch yn y Cell mewnbwn colofn blwch (gan ein bod yn defnyddio colofn ar gyfer gwerthoedd mewnbwn), a dewiswch y gell newidiol y cyfeirir ati yn eich fformiwla. Yn yr enghraifft hon, rydym yn dewis B1 sy'n cynnwys y gyfradd llog. O'r diwedd, cliciwch OK botwm, gweler y screenshot:
   
5. Cymhwyswch y fformat rhif dymunol i'r canlyniadau (Arian cyfred) i'ch angen. A gallwch weld y canlyniadau ar gyfer pob fformiwla yn seiliedig ar y gwerthoedd amrywiol amrywiol.
   

Creu tabl data dau newidyn

Mae tabl data dau newidyn yn Excel yn dangos effaith gwahanol gyfuniadau o ddwy set o werthoedd newidiol ar ganlyniad fformiwla, gan ddangos sut mae newidiadau mewn dau fewnbwn o fformiwla yn effeithio ar ei ganlyniad ar yr un pryd.

Yma, rwyf wedi llunio braslun syml i'ch helpu i ddeall yn well ymddangosiad a strwythur tabl data dau newidyn.

Gan adeiladu ar yr enghraifft i greu tabl data un-newidyn, gadewch i ni nawr fynd ymlaen i ddysgu sut i greu tabl data dau-newidyn yn Excel.

Yn y set ddata isod, mae gennym y gyfradd llog, tymor y benthyciad a swm y benthyciad, ac rydym wedi cyfrifo’r taliad misol drwy ddefnyddio’r fformiwla hon: =-PMT($B$1/12, $B$2*12, $B$3) hefyd. Yma, byddwn yn canolbwyntio ar ddau newidyn allweddol o'n data: cyfradd llog a swm y benthyciad, i arsylwi sut mae newidiadau yn y ddau ffactor hyn yn effeithio ar y symiau ad-dalu ar yr un pryd.

Cam 1: Gosodwch y ddau newidyn newidiol

1. Mewn colofn, rhestrwch y gwahanol gyfraddau llog rydych chi am eu profi. gweler y sgrinlun:
   
2. Mewn rhes, nodwch wahanol werthoedd swm y benthyciad ychydig uwchlaw gwerthoedd y golofn (gan ddechrau o un gell i'r dde o'r gell fformiwla), gweler y sgrinlun:
   

Cam 2: Creu'r tabl data

1. Rhowch eich fformiwla ar groesffordd y rhes a'r golofn lle gwnaethoch restru'r gwerthoedd newidiol. Yn yr achos hwn, byddaf yn nodi'r fformiwla hon: = B4 i mewn i gell E2. Gweler y sgrinlun:
   
2. Dewiswch yr ystod sy'n cynnwys eich symiau benthyciad, cyfraddau llog, y gell fformiwla, a'r celloedd lle bydd y canlyniadau'n cael eu harddangos.
   
3. Yna, cliciwch Dyddiad > Dadansoddiad Beth-Os > Tabl Data. Yn y blwch deialog Tabl Data:
   • Yn y Cell mewnbwn rhes blwch, dewiswch y cyfeirnod cell i'r gell mewnbwn ar gyfer y gwerthoedd newidiol yn y rhes (yn yr enghraifft hon, mae B3 yn cynnwys swm y benthyciad).
   • Yn y Mewnbwn colofn cell blwch, dewiswch y cyfeirnod cell i'r gell mewnbwn ar gyfer y gwerthoedd newidiol yn y golofn (mae B1 yn cynnwys y gyfradd llog).
   • Ac yna, cliciwch OK Button.
     
4. Nawr, bydd Excel yn llenwi'r tabl data gyda'r canlyniadau ar gyfer pob cyfuniad o swm y benthyciad a chyfradd llog. Mae'n rhoi golwg uniongyrchol ar sut mae gwahanol gyfuniadau o symiau benthyciad a chyfraddau llog yn effeithio ar y taliad misol, gan ei wneud yn arf gwerthfawr ar gyfer cynllunio a dadansoddi ariannol.
   
5. Yn olaf, dylech gymhwyso'r fformat rhif a ddymunir i'r canlyniadau (Arian cyfred) i'ch angen.
   

Pwyntiau allweddol gan ddefnyddio'r tablau data

• Rhaid i'r tabl data sydd newydd ei greu fod yn yr un daflen waith â'r data gwreiddiol.
• Mae allbwn y tabl data yn dibynnu ar y gell fformiwla yn y set ddata ffynhonnell, a bydd unrhyw newidiadau i'r gell fformiwla hon yn diweddaru'r allbwn yn awtomatig.
• Unwaith y bydd gwerthoedd wedi'u cyfrifo gan ddefnyddio'r tabl data, ni ellir eu dadwneud Ctrl + Z. Fodd bynnag, gallwch ddewis yr holl werthoedd â llaw a'u dileu.
• Mae'r tabl data yn cynhyrchu fformiwlâu arae, felly ni ellir newid na dileu celloedd unigol o fewn y tabl.

Gweithrediadau eraill ar gyfer defnyddio tablau data

Unwaith y byddwch wedi creu tabl data, efallai y bydd angen gweithrediadau ychwanegol arnoch i'w reoli'n effeithiol, gan gynnwys dileu'r tabl data, addasu ei ganlyniadau, a chynnal ailgyfrifiadau â llaw.

Dileu tabl data

Oherwydd bod canlyniadau'r tabl data yn cael eu cyfrifo gyda fformiwla arae, ni allwch ddileu gwerth unigol o'r tabl data. Fodd bynnag, dim ond y tabl data cyfan y gallwch chi ei ddileu.

Dewiswch yr holl gelloedd tabl data neu dim ond y celloedd gyda'r canlyniadau, ac yna pwyswch Dileu allwedd ar y bysellfwrdd.

Golygu canlyniad tabl data

Yn wir, nid yw'n bosibl golygu celloedd unigol o fewn tabl data yn uniongyrchol, gan eu bod yn cynnwys fformiwlâu arae y mae Excel yn eu cynhyrchu'n awtomatig.

I wneud newidiadau, fel arfer byddai angen i chi ddileu'r tabl data presennol ac yna creu un newydd gyda'r addasiadau dymunol. Mae hyn yn golygu addasu eich fformiwla sylfaenol neu'r gwerthoedd mewnbwn ac yna gosod y tabl data eto i adlewyrchu'r newidiadau hyn.

Ailgyfrifwch y tabl data â llaw

Fel arfer, mae Excel yn ailgyfrifo'r holl fformiwlâu ym mhob llyfr gwaith agored bob tro y gwneir newid. Os yw tabl data mawr sy'n cynnwys nifer o werthoedd amrywiol a fformiwlâu cymhleth yn achosi i'ch llyfr gwaith Excel arafu.

Er mwyn osgoi gwneud cyfrifiadau yn awtomatig gan Excel ar gyfer yr holl dablau data bob tro y gwneir newid yn y llyfr gwaith, gallwch newid y modd cyfrifo o Awtomatig i Llawlyfr. Gwnewch fel a ganlyn os gwelwch yn dda:

Ewch i'r Fformiwlâu tab, ac yna cliciwch Opsiynau Cyfrifo > Awtomatig Ac eithrio Tablau Data, gweler y screenshot:

Ar ôl i chi addasu'r gosodiad hwn, pan fyddwch chi'n ailgyfrifo'ch llyfr gwaith cyfan, ni fydd Excel bellach yn diweddaru'r cyfrifiadau yn eich tablau data yn awtomatig.

Os oes angen i chi ddiweddaru'ch tabl data â llaw, dewiswch y celloedd lle mae'r canlyniadau (celloedd sy'n cynnwys y fformiwlâu TABLE()) yn cael eu harddangos, ac yna pwyswch F9 allweddol.

Trwy ddilyn y camau a amlinellir yn y canllaw hwn a chadw'r pwyntiau allweddol mewn cof, gallwch ddefnyddio tablau data yn effeithlon ar gyfer eich anghenion dadansoddi data. Os oes gennych ddiddordeb mewn archwilio mwy o awgrymiadau a thriciau Excel, mae ein gwefan yn cynnig miloedd o sesiynau tiwtorial, cliciwch yma i gael mynediad iddynt. Diolch am ddarllen, ac edrychwn ymlaen at ddarparu mwy o wybodaeth ddefnyddiol i chi yn y dyfodol!