Mukati
Chii chinonzi dynamic arrays
MunaGunyana 2018, Microsoft yakaburitsa chigadziriso chinowedzera chishandiso chitsva kuMicrosoft Excel: Dynamic Arrays uye 7 mabasa matsva ekushanda nawo. Izvi zvinhu, pasina kuwedzeredza, zvinoshandura zvakanyanya maitiro ese akajairwa ekushanda nemafomati uye mabasa uye kunetseka, chaizvo, wese mushandisi.
Chimbofunga muenzaniso wakapfava unotsanangura musimboti.
Ngatitii tine tafura iri nyore ine data pamusoro peguta-mwedzi. Chii chichaitika kana tikasarudza chero sero isina chinhu kurudyi rwebepa uye topinda mairi fomula isingabatanidzi kune imwe sero, asi nekukasira kune renji?
Mune ese apfuura mavhezheni eExcel, mushure mekudzvanya pa pinda taizowana zviri mukati mekutanga sero B2 chete. Zvimwe sei?
Zvakanaka, kana kuti zvinogoneka kuputira iyi renji mune imwe mhando yeaggregating basa se = SUM(B2:C4) uye uwane yakakura yakazara yayo.
Kana isu taida mamwe maoperation akaoma kupfuura mari yekare, senge kutora hunhu hwakasiyana kana Top 3, saka taizofanira kuisa fomula yedu sefomula yehurongwa tichishandisa nzira yekudimbudzira keyboard. Ctrl+kusuduruka+pinda.
Iye zvino zvinhu zvose zvasiyana.
Zvino mushure mekuisa fomula yakadaro, tinogona kungodzvanya pairi pinda - uye tora semhedzisiro nekukasira ese kukosha uXNUMXbuXNUMXb kwatakareva:
Aya haasi mashiripiti, asi matsva ane simba arrays ayo Microsoft Excel ikozvino inawo. Kugamuchirwa kunyika itsva 🙂
Zvimiro zvekushanda neine dynamic arrays
Nehunyanzvi, hurongwa hwedu hwese hune simba hunochengetwa musero rekutanga G4, ichizadza nhamba inodiwa yemasero kurudyi uye pasi nedata rayo. Kana ukasarudza chero imwe sero muhurongwa, ipapo chinongedzo chiri muformula bar chinenge chisisashande, zvichiratidza kuti tiri mune rimwe re“mwana” maseru:
Kuedza kudzima sero rimwe chete kana anopfuura "emwana" hakuzotungamira kune chero chinhu - Excel inokurumidza kuverengera nekuzadza.
Panguva imwecheteyo, tinogona kutaura zvakachengeteka kune aya "mwana" masero mune mamwe mafomula:
Kana iwe ukakopa sero rekutanga rehurongwa (semuenzaniso, kubva kuG4 kuenda kuF8), ipapo rondedzero yese (mareferensi ayo) ichafamba nenzira imwechete seyakajairika mafomula:
Kana isu tichida kufambisa array, zvino zvichave zvakakwana kufamba (nemouse kana musanganiswa we Ctrl+X, Ctrl+V), zvakare, chete yekutanga huru sero G4 - mushure mayo, ichaendeswa kune imwe nzvimbo uye hurongwa hwedu hwose huchawedzerwa zvakare.
Kana iwe uchida kunongedzera kumwe kunhu pane pepa kune yakasikwa ine simba array, saka unogona kushandisa yakakosha hunhu # ("pondo") mushure mekero yeinotungamira sero:
Semuenzaniso, ikozvino iwe unogona nyore kugadzira rondedzero yekudonhedza musero inoreva iyo yakagadzirwa ine simba array:
Dynamic array kukanganisa
Asi chii chinoitika kana pasina nzvimbo yakakwana yekuwedzera dhizaini, kana kana paine maseru akatogarwa neimwe data munzira yayo? Sangana nemhando nyowani yezvikanganiso muExcel - #KUTAURA! (#SPIRI!):
Senguva dzose, kana tikadzvanya pachiratidzo chine dhaimondi reyero uye chiratidzo chekudaidzira, tinowana tsananguro yakadzama yekwakabva dambudziko uye tinogona kukurumidza kuwana maseru anopindira:
Zvikanganiso zvakafanana zvichaitika kana rondedzero ikaenda pashizha kana kurova sero rakasanganiswa. Kana iwe ukabvisa chipingamupinyi, ipapo zvese zvichagadziriswa pakarepo pakubhururuka.
Dynamic arrays uye smart tables
Kana iyo dhizaini inonongedza kune "smart" tafura yakagadzirwa nekhibhodi yekudimbudzira Ctrl+T kana na Kumba – Fomati setafura (Kumba - Fomati seTafura), ipapo ichaitawo nhaka yayo huru yemhando - auto-size.
Kana uchiwedzera data nyowani kuzasi kana kurudyi, iyo yakangwara tafura uye ine simba renji inozotambanudzawo otomatiki:
Nekudaro, pane chimwe chinogumira: isu hatigone kushandisa dhizaini rengedzo mumaforamu mukati metafura yakangwara:
Dynamic arrays uye mamwe maExcel maficha
Zvakanaka, unoti. Zvese izvi zvinonakidza uye zvinosetsa. Hapana chikonzero, sepakutanga, kutambanudza nemaoko fomula nereferensi kune yekutanga sero rekutanga renji pasi uye kurudyi uye zvese izvo. Uye ndizvo chete?
Kwete.
Dynamic arrays haisi imwe chishandiso muExcel. Iye zvino vakaiswa mukati memoyo chaiwo (kana uropi) weMicrosoft Excel - injini yayo yekuverenga. Izvi zvinoreva kuti mamwe mafomula eExcel uye mabasa atinoziva ikozvino anotsigirawo kushanda neane simba arrays. Ngatitarisei mienzaniso mishoma kuti ikupe pfungwa yehudzamu hweshanduko dzakaitika.
Shandura
Kushandura mutsara (chinjanisa mitsetse nemakoramu) Microsoft Excel yagara iine basa rakavakwa TRANSP (TRANSPOSE). Nekudaro, kuti uishandise, iwe unofanirwa kutanga wasarudza nemazvo huwandu hwezvawanikwa (semuenzaniso, kana iyo yekuisa yaive ye5 × 3, saka unofanirwa kunge wasarudza 3 × 5), wobva waisa basa uye wodzvanya musanganiswa Ctrl+kusuduruka+pinda, nekuti yaigona kungoshanda mune array formula mode.
Iye zvino unogona kungosarudza sero rimwe chete, isa iyo yakafanana formula mukati maro uye tinya pane yakajairwa pinda -Dynamic array ichaita zvese zvega:
Tafura yekuwandisa
Uyu ndiwo muenzaniso wandaiwanzopa pandakakumbirwa kuti ndione mabhenefiti emhando dzemafomula muExcel. Zvino, kuverenga tafura yose yePythagorean, zvakakwana kumira musero rekutanga B2, pinda imomo fomula inowanza mitsara miviri (yakatwasuka uye yakatwasuka seti yenhamba 1..10) wobva wadzvanya pa pinda:
Gluing uye kesi kutendeuka
Arrays haingogone kuwanzwa chete, asi zvakare yakanamirwa pamwe chete neyakajairwa opareta & (ampersand). Ngatitii tinoda kubvisa zita rekutanga nerekupedzisira kubva pamakoramu maviri uye kugadzirisa nyaya yekusvetuka mune data rekutanga. Isu tinoita izvi nekapfupi formula inoumba iyo yese rondedzero, uye isu tozoisa basa kwairi PROPNACH (ZVAKANAKA)kugadzirisa rejista:
Mhedziso Pamusoro 3
Ngatitii isu tine nhamba dzenhamba dzatinoda kutora mhinduro nhatu dzepamusoro, tichidzironga mukudzika. Zvino izvi zvinoitwa neimwe formula uye, zvakare, pasina chero Ctrl+kusuduruka+pinda sekare:
Kana iwe uchida kuti mhedzisiro isaiswe kwete mumutsara, asi mumutsara, saka zvakakwana kutsiva colon (mutsara separator) mune iyi fomula ine semicolon (element separator mukati memutsara mumwe). Mushanduro yeChirungu yeExcel, vapatsanuri ava masemicolon nemakoma, zvichiteerana.
VLOOKUP inoburitsa makoramu akawanda kamwechete
mabasa VPR (VLOOKUP) ikozvino iwe unokwanisa kudhonza kukosha kwete kubva kune imwe, asi kubva kune akati wandei makoramu kamwechete - ingo tsanangura nhamba dzadzo (mune chero yaunoda kurongeka) seyakarongwa munharo yechitatu yebasa:
OFFSET basa rinodzosa dhizaini array
Imwe yeanonyanya kunakidza uye anobatsira (mushure meVLOOKUP) mabasa ekuongorora data ndiro basa KURASA (OFFSET), yandakapa pane imwe nguva chitsauko chose mubhuku rangu uye imwe nyaya iri pano. Kuomerwa nekunzwisisa uye kugona basa iri kwagara kuri kwekuti yakadzosa dhata (renji) yedata semhedzisiro, asi isu hatina kukwanisa kuiona, nekuti Excel haisati yaziva mashandisiro ekushanda nehurongwa kubva mubhokisi.
Zvino dambudziko iri rave munguva yakapfuura. Ona kuti sei ikozvino, uchishandisa fomula imwechete uye dhizaini ine simba yakadzoserwa neOFFSET, unogona kubvisa mitsara yese yechigadzirwa chakapihwa kubva kune chero tafura yakarongedzwa:
Ngatitarisei nharo dzake:
- A1 -Kutanga sero (reference point)
- ПОИСКПОЗ(F2;A2:A30;0) - kuverenga kwekuchinja kubva pakatanga sero pasi - kune yekutanga yakawanikwa kabichi.
- 0 - kuchinja kwe "hwindo" kune kodzero yehama kune yekutanga sero
- СЧЁТЕСЛИ(A2:A30;F2) - kuverenga kwekukwirira kwe "hwindo" rakadzoka - nhamba yemitsara apo pane kabichi.
- 4 - saizi ye "hwindo" yakatwasuka, kureva kuburitsa makoramu mana
Mabasa Matsva eDynamic Arrays
Pamusoro pekutsigira iyo dhizaini dhizaini mumabasa ekare, akati wandei mabasa matsva akawedzerwa kuMicrosoft Excel, akarodzwa zvakanangana nekushanda neane simba arrays. Kunyanya, izvi ndezvi:
- giredha (SORT) - inoronga huwandu hwekuisa uye inogadzira dhizaini dhizaini pane inobuda
- SORTPO (SORTBY) - inogona kuronga rimwe renji nemitengo kubva kune imwe
- FILTER (SEFA) -inotora mitsara kubva kune sosi renji inosangana nemamiriro akatsanangurwa
- UNIK (ZAKASIYANA) - inobvisa yakasarudzika makoshero kubva mumhando kana kubvisa zvakapetwa
- SLMASSIVE (RANARRAY) -Inogadzira huwandu hwehuwandu hwehuwandu hwakapihwa saizi
- KUBEREKWA (ZVINOTEVERANA) - inoumba mutsara kubva munhevedzano yenhamba ine nhanho yakapihwa
Zvimwe pamusoro pavo - zvishoma gare gare. Ivo vakakodzera chinyorwa chakasiyana (uye kwete chimwe) chekudzidza zvine musoro 🙂
mhedziso
Kana iwe wakaverenga zvese zvakanyorwa pamusoro, saka ndinofunga iwe unotoona chiyero chekuchinja kwakaitika. Saka zvinhu zvakawanda muExcel zvino zvinogona kuitwa zviri nyore, zviri nyore uye zvine musoro. Ndinofanira kubvuma kuti ndinoshamiswa zvishoma kuti zvingani zvinyorwa zvino zvichafanirwa kugadziriswa pano, panzvimbo ino uye mumabhuku angu, asi ndakagadzirira kuita izvi nemoyo wakareruka.
Kupfupisa zvabuda, pluses dynamic arrays, unogona kunyora zvinotevera:
- Iwe unogona kukanganwa pamusoro pekusanganiswa Ctrl+kusuduruka+pinda. Excel ikozvino haioni mutsauko pakati pe "nguva dzose mafomula" uye "akarongwa mafomula" uye anoabata nenzira imwechete.
- Pamusoro pebasa SUMPRODUCT (SUMPRODUCT), iyo yaimboshandiswa kuisa array formula pasina Ctrl+kusuduruka+pinda unogonawo kukanganwa – ikozvino zviri nyore zvakakwana SUM и pinda.
- Matafura eSmart uye mabasa akajairwa (SUM, IF, VLOOKUP, SUMIFS, nezvimwewo) ikozvino zvakare zvizere kana zvishoma kutsigira ane simba arrays.
- Kune kuenderana kumashure: kana iwe ukavhura bhuku rebasa rine dhizaini arrays mune yekare vhezheni yeExcel, inoshanduka kuita array mafomula (muakamonana braces) uye arambe achishanda mu "chinyakare chimiro".
Wakawana imwe nhamba minuses:
- Iwe haugone kudzima mitsara yega, makoramu kana maseru kubva kune ane simba array, kureva kuti inogara sechinhu chimwe chete.
- Iwe haugone kuronga dhizaini array nenzira yakajairika kuburikidza Data – Kuronga (Data - Ronga). Ikozvino pane basa rinokosha reizvi. giredha (SORT).
- Iyo ine simba renji haigone kushandurwa kuita yakangwara tafura (asi iwe unogona kugadzira ine simba renji zvichibva pane yakangwara tafura).
Ehe, uku hakusi kupera, uye ndine chokwadi chekuti Microsoft icharamba ichivandudza iyi michina mune ramangwana.
Ndingadhaunirodha kupi?
Uye pakupedzisira, mubvunzo mukuru 🙂
Microsoft yakatanga kuzivisa uye yakaratidza tarisiro yeane simba arrays muExcel kumashure munaGunyana 2018 pamusangano Ignite. Mumwedzi mishoma yakatevera, pakave nekuyedzwa kwakakwana uye kushanda-mukati kwezvinhu zvitsva, kutanga katsi vashandi veMicrosoft pachayo, uyezve pane vanozvipira testers kubva kudenderedzwa reHofisi Insiders. Gore rino, iyo yekuvandudza iyo inowedzera simba arrays yakatanga kuendeswa zvishoma nezvishoma kune venguva dzose Office 365 vanyoreri. Semuenzaniso, ini ndakangoigamuchira muna Nyamavhuvhu nekunyoreswa kwangu kweHofisi 365 Pro Plus (Mwedzi weTarged).
Kana yako Excel isati yave neane simba arrays, asi iwe uchida chaizvo kushanda nawo, saka pane zvinotevera sarudzo:
- Kana iwe uine Hofisi 365 kunyoreswa, unogona kungomirira kusvika iyi update yasvika kwauri. Kuti izvi zvinokurumidza sei kuitika zvinoenderana nekuti kangani zvigadziriso zvinounzwa kuHofisi yako (kamwe pagore, kamwe chete mwedzi mitanhatu yega yega, kamwe chete pamwedzi). Kana uine PC yekambani, unogona kukumbira maneja wako kuti agadzirise zvigadziriso kuti zvidhawunirwe kakawanda.
- Iwe unogona kujoina mazinga eavo Office Insider test volunteers - ipapo iwe uchave wekutanga kugamuchira ese matsva maficha uye mabasa (asi pane mukana wekuwedzera buggy muExcel, hongu).
- Kana iwe usina kunyoreswa, asi bhokisi rakamira rakamira vhezheni yeExcel, saka uchafanirwa kumirira kusvika kuburitswa kweiyo inotevera vhezheni yeHofisi uye Excel muna 2022, zvirinani. Vashandisi veshanduro dzakadaro vanongogamuchira zvigadziriso zvekuchengetedza uye gadziriso dzebug, uye zvese zvitsva "zvakanaka" zvino zvinongoenda kune vanyoreri veHofisi 365. Zvinosuruvarisa asi chokwadi 🙂
Chero zvazvingaitika, kana arrays ane simba achionekwa muExcel yako - mushure mechinyorwa ichi, iwe unenge wakazvigadzirira 🙂
- Ndeapi array mafomula uye maitiro ekuashandisa muExcel
- Hwindo (range) kupfupisa uchishandisa iyo OFFSET basa
- 3 Nzira dzekuisa Tafura muExcel