Cynnwys
A oes gennych dablau gyda data yn Excel y gellir eu newid maint, hy gall nifer y rhesi (colofnau) gynyddu neu leihau yn ystod y gwaith? Os yw maint y tabl yn “arnofio”, yna bydd yn rhaid i chi fonitro'r foment hon yn gyson a'i chywiro:
- dolenni mewn fformiwlâu adroddiadau sy'n cyfeirio at ein tabl
- ystodau cychwynnol o dablau colyn sy'n cael eu hadeiladu yn ôl ein bwrdd
- ystodau cychwynnol o siartiau wedi'u hadeiladu yn ôl ein tabl
- yn amrywio ar gyfer cwymplenni sy'n defnyddio ein tabl fel ffynhonnell ddata
Ni fydd hyn i gyd yn gadael i chi ddiflasu 😉
Bydd yn llawer mwy cyfleus a chywir creu ystod “rwber” ddeinamig, a fydd yn addasu maint yn awtomatig i nifer gwirioneddol y rhesi a cholofnau data. Er mwyn gweithredu hyn, mae yna sawl ffordd.
Dull 1. Tabl smart
Amlygwch eich ystod o gelloedd a dewiswch o'r tab Cartref – Fformat fel Tabl (Cartref – Fformat fel Tabl):
Os nad oes angen y dyluniad streipiog arnoch sy'n cael ei ychwanegu at y bwrdd fel sgîl-effaith, yna gallwch ei ddiffodd ar y tab sy'n ymddangos Adeiladwr (dylunio). Mae pob tabl a grëir yn y modd hwn yn derbyn enw y gellir ei ddisodli ag un mwy cyfleus yn yr un lle ar y tab Adeiladwr (dylunio) yn y maes Enw'r tabl (Enw Tabl).
Nawr gallwn ddefnyddio dolenni deinamig i'n “tabl smart”:
- Tabl 1 - dolen i'r tabl cyfan heblaw am y rhes pennawd (A2: D5)
- Tabl 1[# Pawb] – dolen i’r tabl cyfan (A1:D5)
- Tabl 1[Pedr] – cyfeiriad at golofn amrediad heb bennawd cyntaf y gell (C2:C5)
- Tabl 1[# Penawdau] – dolen i'r “pennawd” gydag enwau'r colofnau (A1: D1)
Mae cyfeiriadau o'r fath yn gweithio'n wych mewn fformiwlâu, er enghraifft:
= SUM (Tabl 1[Moscow]) – cyfrifo’r swm ar gyfer y golofn “Moscow”
or
=VPR(F5;Tabl 1;3;0) – chwiliwch yn y tabl am y mis o gell F5 a rhowch swm St. Petersburg ar ei gyfer (beth yw VLOOKUP?)
Gellir defnyddio dolenni o'r fath yn llwyddiannus wrth greu tablau colyn trwy ddewis ar y tab Mewnosod – Tabl Colyn (Mewnosod – Tabl Colyn) a nodi enw'r tabl clyfar fel y ffynhonnell ddata:
Os dewiswch ddarn o dabl o'r fath (er enghraifft, y ddwy golofn gyntaf) a chreu diagram o unrhyw fath, yna wrth ychwanegu llinellau newydd, byddant yn cael eu hychwanegu at y diagram yn awtomatig.
Wrth greu cwymplenni, ni ellir defnyddio dolenni uniongyrchol i elfennau tabl clyfar, ond gallwch chi fynd o gwmpas y cyfyngiad hwn yn hawdd gan ddefnyddio tric tactegol - defnyddiwch y swyddogaeth INDIRECT (ANUNIONGYRCHOL), sy'n troi'r testun yn ddolen:
Y rhai. dolen i dabl clyfar ar ffurf llinyn testun (mewn dyfynodau!) yn troi'n ddolen gyflawn, ac mae'r gwymplen fel arfer yn ei ganfod.
Dull 2: Ystod enwir deinamig
Os yw troi eich data yn dabl smart yn annymunol am ryw reswm, yna gallwch ddefnyddio dull ychydig yn fwy cymhleth, ond yn llawer mwy cynnil ac amlbwrpas - creu ystod a enwir deinamig yn Excel sy'n cyfeirio at ein tabl. Yna, fel yn achos tabl smart, gallwch chi ddefnyddio enw'r ystod a grëwyd yn rhydd mewn unrhyw fformiwlâu, adroddiadau, siartiau, ac ati. Gadewch i ni ddechrau gydag enghraifft syml:
Gorchwyl: gwneud amrediad deinamig a enwir a fyddai'n cyfeirio at restr o ddinasoedd ac yn ymestyn a chrebachu o ran maint yn awtomatig wrth ychwanegu dinasoedd newydd neu eu dileu.
Bydd angen dwy swyddogaeth Excel adeiledig arnom sydd ar gael mewn unrhyw fersiwn − POICPOZ (MATCH) i benderfynu cell olaf yr amrediad, a MYNEGAI (MYNEGAI) i greu cyswllt deinamig.
Dod o hyd i'r gell olaf gan ddefnyddio MATCH
MATCH(gwerth_lookup, amrediad, math_match) – swyddogaeth sy'n chwilio am werth penodol mewn ystod (rhes neu golofn) ac sy'n dychwelyd rhif trefnol y gell lle cafodd ei ganfod. Er enghraifft, bydd y fformiwla MATCH ("Mawrth"; A1:A5; 0) yn dychwelyd y rhif 4 o ganlyniad, oherwydd mae'r gair "March" wedi'i leoli yn y bedwaredd gell yng ngholofn A1: A5. Mae'r arg ffwythiant olaf Match_Type = 0 yn golygu ein bod yn chwilio am union gyfatebiaeth. Os na nodir y ddadl hon, yna bydd y ffwythiant yn newid i'r modd chwilio am y gwerth lleiaf agosaf - dyma'n union beth y gellir ei ddefnyddio'n llwyddiannus i ddod o hyd i'r gell feddiannu olaf yn ein harae.
Mae hanfod y tric yn syml. Mae MATCH yn chwilio am gelloedd yn yr ystod o'r top i'r gwaelod ac, mewn theori, dylai ddod i ben pan fydd yn dod o hyd i'r gwerth lleiaf agosaf i'r un a roddir. Os ydych chi'n nodi gwerth sy'n amlwg yn fwy na'r un sydd ar gael yn y tabl fel y gwerth dymunol, yna bydd MATCH yn cyrraedd pen eithaf y tabl, yn dod o hyd i ddim ac yn rhoi rhif dilyniant y gell llenwi ddiwethaf. Ac mae ei angen arnom!
Os mai dim ond rhifau sydd yn ein harae, yna gallwn nodi rhif fel y gwerth dymunol, sy'n amlwg yn fwy nag unrhyw un o'r rhai yn y tabl:
Ar gyfer gwarant, gallwch ddefnyddio'r rhif 9E + 307 (9 gwaith 10 i bŵer 307, hy 9 gyda 307 sero) - yr uchafswm y gall Excel weithio gydag ef mewn egwyddor.
Os oes gwerthoedd testun yn ein colofn, yna fel yr hyn sy'n cyfateb i'r rhif mwyaf posibl, gallwch fewnosod y lluniad REPEAT ("i", 255) - llinyn testun sy'n cynnwys 255 o lythrennau “i” - y llythyren olaf o yr wyddor. Gan fod Excel mewn gwirionedd yn cymharu codau nodau wrth chwilio, yn dechnegol bydd unrhyw destun yn ein tabl yn “lai” na llinell “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy:
Cynhyrchu dolen gan ddefnyddio MYNEGAI
Nawr ein bod ni'n gwybod sefyllfa'r elfen olaf nad yw'n wag yn y tabl, mae'n dal i ffurfio cyswllt â'n holl ystod. Ar gyfer hyn rydym yn defnyddio'r swyddogaeth:
INDEX(ystod; rhes_num; colofn_num)
Mae'n rhoi cynnwys y gell o'r ystod fesul rhes a rhif colofn, hy er enghraifft, bydd y ffwythiant =INDEX(A1:D5;3;4) yn ein tabl gyda dinasoedd a misoedd o'r dull blaenorol yn rhoi 1240 – y cynnwys o'r 3ydd rhes a'r 4edd golofn, hy celloedd D3. Os mai dim ond un golofn sydd, yna gellir hepgor ei rhif, hy bydd fformiwla INDEX(A2:A6;3) yn rhoi “Samara” yn y sgrin lun olaf.
Ac mae un naws nad yw'n gwbl amlwg: os nad yw'r MYNEGAI yn cael ei roi yn y gell yn unig ar ôl yr arwydd =, yn ôl yr arfer, ond yn cael ei ddefnyddio fel rhan olaf y cyfeiriad at yr amrediad ar ôl y colon, yna nid yw'n rhoi allan mwyach. cynnwys y gell, ond ei chyfeiriad! Felly, bydd fformiwla fel $A$2:INDEX($A$2:$A$100;3) yn rhoi cyfeiriad at yr ystod A2:A4 yn yr allbwn.
A dyma lle mae'r swyddogaeth MATCH yn dod i mewn, yr ydym yn ei fewnosod y tu mewn i'r MYNEGAI i bennu diwedd y rhestr yn ddeinamig:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("I"; 255);A2:A100))
Creu ystod a enwir
Erys i bacio'r cyfan yn un cyfanwaith. Agor tab fformiwla (fformiwlâu) A chliciwch ar y Rheolwr Enw (Rheolwr Enw). Yn y ffenestr sy'n agor, cliciwch ar y botwm Creu (newydd), rhowch ein henw amrediad a'n fformiwla yn y maes Ystod (Cyfeirnod):
Mae'n parhau i fod i glicio ar OK a gellir defnyddio'r ystod barod mewn unrhyw fformiwlâu, cwymplenni neu siartiau.
- Defnyddio'r swyddogaeth VLOOKUP i gysylltu tablau a gwerthoedd chwilio
- Sut i greu cwymplen sy'n llenwi'n awtomatig
- Sut i greu tabl colyn i ddadansoddi swm mawr o ddata