Indlela yokusebenzisa amafomula e-dynamic array ku-Microsoft Excel

Uma usebenza namaspredishithi nsuku zonke, uzobe uqaphele ukuthi ngokushesha nje lapho eqala ukukhula emigqeni nasemakholomu, Amafomula e-Excel akudala awaphelele. ukwenza izibalo eziyinkimbinkimbi ngokunethezeka. Yilapho-ke amafomula e-dynamic matrix aqala khona ukusebenza, enye yezinto ezintsha, uma usuzifundile, ozothola ukuthi uzisebenzisa cishe kuzo zonke izincwadi.

Ezinguqulweni zanamuhla ze-Excel (ikakhulukazi ku- I-Microsoft 365Sinenjini entsha yokubala evumela ifomula eyodwa ukuthi ikhiqize imiphumela eminingi ngesikhathi esisodwa, ihlanganisa amaseli amaningana aseduze ngaphandle kokudinga ukukopishwa ngesandla. Ngenxa yalokhu kuziphatha, "ukuchichima" kwemiphumela ngobuningiManje sekulula kakhulu ukuhlunga, ukuhlunga, ukukhiqiza uhlu, noma ukwenza izibalo ezithuthukisiwe ngaphandle kokusebenzisa tricks okungajwayelekile noma ukuhlanganiswa kwezinkinobho ezifana ne-Ctrl+Shift+Enter.

I-athikili ehlobene:

Yenza amashadi abe ngokuzenzakalelayo anamabanga aqanjwe ngamagama kanye nochungechunge olushintshashintshayo ku-Excel

Kuyini ukugcwala kwamafomula e-dynamic matrix?

Kumodeli entsha yokubala ye-Excel, ifomula ingabuyisa hhayi inani elilodwa nje kuphela, kodwa nesethi ephelele yemiphumela ehlelekile; leli sethi livame ukubizwa ngokuthi uhlu lokukhipha oluchichimayo noma ububanziUma lokhu kwenzeka, i-Excel ibeka lawo manani ngokuzenzakalelayo kumaseli aseduze, yandisa ifomula phansi, ngakwesokudla, noma kuzo zombili izinkomba, kuye ngobukhulu bomphumela.

Isibonelo, lapho ubhala ifomula =HLELA(D2:D11,1,-1) Kuseli elilodwa (isibonelo, i-F2), i-Excel ikhiqiza uhlu oluhlelwe ngokwehla ngokusekelwe kububanzi be-D2:D11. Umphumela uthatha imigqa eyi-10, kodwa ufaka ifomula kuseli elilodwa kuphela; ezinye izikhundla zigcwaliswa yi-Excel ngokwayo isebenzisa le ndlela yokugcwala.

Amafomula angashintsha usayizi womphumela wawo ngokusekelwe kudatha yomthombo aziwa ngokuthi amafomula e-matrix ashukumisayoUma la mafomula ebuyisa imiphumela ehlukahlukene edlula iseli lapho ifomula yabhalwa khona, kuthiwa ifomula “ichichime kakhulu” futhi indawo ehlala kuyo ibizwa ngokuthi uhla lokuchichima.

Empeleni, lokhu kusho ukuthi imisebenzi eminingi, njenge-SORT, FILTER, RANDOMAR, SEQUENCE, noma i-UNIQUE, manje seyenzelwe ukubuya. ama-matrices edatha aphelele alungele ukusetshenziswa, ngendlela eqondile kakhulu futhi efundekayo kunamafomula amadala e-matrix.

Indlela ububanzi bokugcwala buziphatha ngayo ku-Excel

Uma uqinisekisa ifomula ye-dynamic array ngokucindezela ukhiye othi Enter kuphela, i-Excel ihlaziya umphumela bese ilungisa ngokuzenzakalelayo usayizi webanga lokukhipha ukuze kuhlangatshezwane nawo wonke amanani abuyiselwe yifomula. Bese, beka isici ngasinye se-array kuseli yaso ehambisanayo ngaphakathi kwalolo hlu olugcwele.

Uma ubhala enye yalezi zindlela ohlwini noma etafuleni ledatha, kungaba usizo kakhulu ukuguqula idatha yomthombo ibe yi- Ithebula le-Excel elinezinkomba ezihlelekileAmathebula avumelana ngokuzenzakalelayo uma ungeza noma ususa imigqa, ngakho-ke amafomula e-dynamic array ayisebenzisayo ayabuyekezwa ngaphandle kokuthi uthinte noma yini.

Kubalulekile ukwazi ukuthi amafomula okugcwala ngokweqile awasebenzi ngaphakathi kwamathebula ngokwawo: Ukugcwala akuvunyelwe ngaphakathi kwethebula le-Excel.Kunalokho, kufanele ubeke lawo mafomula kugridi evamile (ngaphandle kwethebula) bese usebenzisa ithebula njengomthombo wedatha kuphela. Amathebula enzelwe ukugcina amarekhodi, hhayi ukwandisa ububanzi bokukhipha bonke ngaphakathi kwawo.

Noma nini lapho uchofoza kunoma yiliphi iseli elingaphakathi kwebanga lokuchichima, i-Excel idweba ibhokisi eligqanyisiwe lizungeze lonke isethi yamaseli athintekile. Lokhu kwenza kube lula kakhulu ukukubona ngokushesha. ukuthi ifomula ifinyelela kude kangakanani nokuthi yimaphi amaseli ancike kuyoUma nje ukhetha iseli elingaphandle kwalelo banga, umngcele uyanyamalala.

Enye imininingwane ebalulekile ukuthi, kububanzi bokugcwala, iseli lokuqala kuphela (elisekhoneni eliphezulu kwesobunxele) eliqukethe ifomula. Amanye amaseli abonisa imiphumela, kodwa uma ukhetha noma yiliphi lawo, uzobona ifomula ifiphalisiwe kubha yefomula. Ngeke ukwazi ukuyishintsha ngqo.Kumelwe uhlele njalo iseli lomthombo; ngemva kokulishintsha nokucindezela u-Enter, i-Excel izobala kabusha lonke uhla lokugcwala ngesikhathi esisodwa.

Amaphutha ahambisanayo kanye nomyalezo othi #OVERFLOW!

Ukuze ifomula ye-dynamic array ikhule kahle, i-Excel idinga ukuthi indawo yokuphuma icace. Uma idatha, amafomula, noma ezinye izakhi zihlala kunoma yiliphi lamaseli lapho imiphumela kufanele ivele khona, kuzovela iphutha. Ukugqagqana kobubanzi bokuchichima futhi i-Excel ayikwazi ukuqeda umsebenzi.

Uma kunjalo, esikhundleni somphumela olindelekile, uzobona umlayezo wephutha. #UKUGCWELEKA! kuseli lapho ufake khona ifomula. Lena yindlela i-Excel ekwazisa ngayo ukuthi kukhona ukukhiya okuvimbela i-array ukuthi ibekwe kuwo wonke amaseli ewadingayo ukuze ibambe imiphumela yayo.

Uma ukhetha ifomula okukhulunywa ngayo, i-Excel izokukhombisa, ngomngcele onamachashazi, ububanzi lapho ihlose ukugcwala khona, kufaka phakathi noma yimaphi amaseli avimba inqubo. Lokhu kubuka kukuvumela ukuthi thola kalula amaseli anenkinga ukuze zikhishwe noma okuqukethwe kwazo kuthuthelwe kwenye indawo.

Uma ususususile idatha evimbela ukwanda (isibonelo, amanani alahlekile noma amafomula angaphambilini), uma ubala kabusha ishidi, i-Excel izokwenza ukuthi ifomula igcwale njengoba kuhlosiwe. Ezimweni eziningi, ukususa amaseli ambalwa kwanele ukwenza iphutha le-#OVERFLOW! linyamalale ngokushesha.

Kungenzeka futhi ukuthi ifomula ngokwayo ayiklanywanga kahle futhi ibuyisela uhlu olukhulu kakhulu kunesikhala esitholakalayo. Kulezi zimo, kuyalulekwa ukuthi ubuyekeze ifomula. kokubili ukuqonda kwefomula kanye nendawo ekhululekile ezungezile ukuqinisekisa ukuthi ububanzi bomkhiqizo bungakhula njengoba kudingeka.

Umehluko phakathi kwamafomula e-dynamic matrix namafomula e-CSE akudala

Ngaphambi kokufika kwama-matrices ashukumisayo, amafomula e-matrix aqalwa ngenhlanganisela edumile I-Ctrl+Shift+Enter (CSE)Lawa mafomula akudala asasekelwa ku-Excel ngezizathu zokuhambisana emuva, kodwa indlela enconywayo namuhla ukusebenza ngemodeli entsha enamandla, elula futhi engaba namaphutha amaningi.

Enye yezinzuzo ezinkulu zamafomula e-dynamic matrix ukuthi Udinga ukufaka ifomula kanye kuphela kuseli eliphezulu kwesobunxele.Imiphumela esele ivela ngokuzenzakalelayo ngenxa yokuchichima. Kumafomula amadala e-CSE, kwadingeka ukhethe lonke ibanga lapho ufuna imiphumela ivele khona bese uqinisekisa ifomula ngo-Ctrl+Shift+Enter.

Amafomula aguquguqukayo angalungisa nosayizi wawo lapho idatha yomthombo ishintsha. Uma ungeza imigqa eminingi emthonjeni wedatha, i-array inganda; uma ususa idatha, ingancipha—konke ngaphandle kokuthi uhlele ububanzi ngesandla. Ngama-array e-CSE akudala, uma indawo yokubuyela yayincane kakhuluImiphumela ifinyeziwe; futhi uma yayingaphezu kwamandla, ungahlangabezana namaphutha afana ne-#N/A.

Omunye umehluko othakazelisayo ukuthi imisebenzi eminingi yakudala, njenge-RAND, ROW, noma i-COLUMN, manje ihlolwa kumongo weseli elilodwa (1x1). Uma ufuna ukukhiqiza imiphumela eminingi engahleliwe noma ukulandelana kwezinombolo ngokusekelwe emigqeni nasemakholomu, kunconywa ukusebenzisa imisebenzi efana I-RANDOM MATRIX noma i-SEQUENCEeziklanyelwe ukubuyisa ama-matrices aphelele futhi zisebenze kahle nenjini entsha enamandla.

Ngaphezu kwalokho, esikhathini esidlule kwakukhona into eyaziwa ngokuthi “i-CSE break”, lapho amafomula athile e-legacy matrix ayencike komunye nomunye ayengabalwa ngokuzimela futhi abuye. imiphumela engaguquki noma okunzima ukuyilungisaNgama-array aguquguqukayo, lokhu kuziphatha kuyanyamalala: uma kunezinkomba eziyindilinga, i-Excel izozimaka kanjalo esikhundleni sokuphula i-logic yefomula.

Ukushintsha ifomula ye-dynamic array nakho kulula kakhulu. Udinga ukuhlela iseli yomthombo kuphela, bese okunye kubuyekezwa ngokuzenzakalelayo; ngamafomula e-CSE, lokhu bekudingeka. hlela lonke ububanzi obuthintekile ngesikhathi esisodwaLokhu kwaba nzima kakhulu ngamabanga amakhulu. Ngaphezu kwalokho, lapho ishidi liqukethe ibanga elisebenzayo elinefomula ye-CSE, kwakungenakwenzeka ukufaka noma ukususa imigqa noma amakholomu aphazamisa lolo bubanzi kuze kube yilapho ifomula ye-array ezuzwe njengefa isuswa noma iguqulwa kuqala.

Ukusebenzisa imisebenzi esemqoka ngama-array aguquguqukayo

Phakathi kwemisebenzi enamandla kakhulu esebenzisa ama-dynamic arrays kukhona Hlunga, Hlunga, Hlunga nge, Okuhlukile, I-RAY Engahleliwe kanye nokulandelanaZonke zibuyisa ububanzi obugcwele, ngakho-ke zihambisana kahle nokuziphatha kokugcwala, futhi kukhona amathuluzi awusizo afana nalawa I-Excel Formula Bot lokho kwenza kube lula ukuyisebenzisa.

Ngomsebenzi we-FILTER, isibonelo, ungakhipha kuphela imigqa ehlangabezana nezindinganiso ezithile etafuleni ledatha, ubuyisele isethi yemiphumela ebuyekeza ngokuzenzakalelayo lapho idatha yomthombo ishintsha. Lo msebenzi uhlangana kahle kakhulu ne-UNIQUE, evumela thola uhlu lwamanani ngaphandle kokuphindaphindwa kusuka kumakholomu anedatha ephindaphindwayo eminingi.

Umsebenzi we-MATRIZALEAT ukhiqiza uhla lwezinombolo ezingahleliwe kubhulokhi, olufanele ukulingisa noma ukuhlolwa; I-SECUENCIA, ngakolunye uhlangothi, ibuyisela ama-matrices ngochungechunge lwezinombolo ezilandelanayo, okukuvumela ukuthi wenze ngokwezifiso ukwanda kanye nosayizi we-matrix ngokwezidingo zakho. Zombili zisekelwe kumodeli entsha ye-matrix futhi zenzelwe gcwalisa izindawo ezinkulu zeshidi ngesikhathi esisodwa.

Okokugcina, i-SORT kanye ne-SORTBY kwenza kube lula kakhulu ukudala uhlu oluhleliwe kusuka kumakholomu noma amathebula akhona. Esikhundleni sokusebenzisa ukuhlunga ngesandla noma inhlanganisela eyinkimbinkimbi yemisebenzi, manje usungabhala ifomula eyodwa ezokwenza ibuyisela idatha ehleliwe futhi ivumelana ngokuzenzakalelayo nezinguquko kumanani okuqala.

Yonke le misebenzi iyahlangana futhi, ngenxa yokuchichima, ikuvumela ukuthi wakhe izixazululo ezithuthuke kakhulu ngaphandle kwesidingo sama-macro noma ama-legacy, amafomula e-array okunzima ukuwagcina, kanye nokwenza izincwadi zakho zokusebenza zibe ngokuzenzakalela, Izikripthi zeHhovisi ku-Excel Web Kungaba usizo olukhulu.

Umehluko wokubala phakathi kwama-matrices ashintshashintshayo nama-matrices azuzwe njengefa

Uma usasebenza nezincwadi ezindala ezisebenzisa amafomula e-CSE array, kubalulekile ukukhumbula ukuthi uma uziguqula zibe yizincwadi zazo. i-dynamic efanayo ingashintsha kancane ukuziphatha Kwezinye izimo. Nakuba ezimweni eziningi ukuguqulwa kuqondile, kuyalulekwa ukuthi kubuyekezwe umphumela.

Indlela evamile yokuguqula kusuka ku-array yefa ibe yi-dynamic ukuthola iseli lokuqala lobubanzi be-array, ukukopisha umbhalo wefomula, ukususa lonke ububanzi obudala, bese bhala kabusha ifomula kuphela kuseli eliphezulu kwesobunxele Isebenzisa indlela entsha, i-Excel izobhekana ngokuzenzakalelayo nomphumela ogcwele.

Ngesikhathi sokuguquka, naka kakhulu imisebenzi eyayithembele ekuziphatheni okungacacile kwe-intersection noma ekuhlolweni okukhethekile ngaphakathi kwamafomula e-CSE. I-Excel manje isinayo umqhubi we-inclusive intersection (@)okusebenza ukuphinda ukuziphatha okudala ezimweni ezithile, kodwa isincomo esijwayelekile sisalokhu siwukubhala kabusha ngokusobala amafomula sisebenzisa imisebenzi emisha.

I-Excel iphinde inikeze ukwesekwa okulinganiselwe lapho i-dynamic array ibhekisela kudatha etholakala kwenye incwadi yokusebenzela. Ukusebenza kahle kuqinisekiswa kuphela uma Womabili amafayela avuliwe ngesikhathi esifanayoUma uvala ibhuku lokusebenzela lomthombo, amafomula e-dynamic array axhunyiwe angabuyisa iphutha le-#REF! lapho ezama ukubuyekeza, ngakho-ke kubalulekile ukukhumbula lokhu kumamodeli ayinkimbinkimbi anezinkomba eziningi zangaphandle; ezimweni ezinjalo, bheka ukuthi kanjani londoloza futhi wabelane ngezincwadi zomsebenzi ukugwema izinkinga.

Nakuba amafomula e-CSE azoqhubeka ekhona ukuze ahambisane nokubuyela emuva, i-Microsoft ngokwayo incoma ukuyeka ukuwadala kumaphrojekthi amasha bese ukhetha ama-dynamic arrays. Ukufundeka kalula, ukulungiswa okulula, kanye nokuqina Lezi zindlela ezintsha zizenza zibe yindlela ekhethwayo yesikhathi esizayo.

Ububanzi bokuchichima kanye nokuhlela okusebenzayo eshidini

Uma ifomula igcwala ngokweqile, indawo ehlala kuyo yaziwa ngokuthi uhla lokugcwala ngokweqile. Kulolu hlu, njengoba sesishilo kakade, iseli lokuqala kuphela eliqukethe ifomula yangempela. Amaseli asele abonisa imiphumela, kodwa "alawulwa" kusukela kuseli lomthombo, okusho ukuthi yonke i-matrix iziphatha njengeyunithi.

Isibonelo, uma ubhala umsebenzi =UPPER(E7:E19) Kuseli elilodwa, uzobona ukuthi i-Excel ibuyisa kanjani, emigqeni eminingana, umbhalo ovela kulolo hlu uguqulwe waba ngosonhlamvukazi. Uma ukhetha noma yiliphi lamaseli kuhlu lokukhipha, uzobona umphumela, kodwa uma ubheka ibha yefomula, uzobona ukuthi okuqukethwe kubonakala kufiphele, okubonisa ukuthi Akuhleleki ngokuqondileNoma yiziphi izinguquko kumele zenziwe kuseli lapho ubhale khona ifomula okokuqala.

Lokhu kuziphatha kunenzuzo ecacile: kukuvimbela ekushintsheni ngephutha ingxenye yobubanzi kuphela bese ushiya okunye kungashintshiwe, okuvame ukuholela emaphutheni okunzima ukuwathola. Uma udinga ukushintsha ifomula, uyihlela kanye kuphela kuseli lomthombo, cindezela u-Enter, bese i-Excel izoyibuyekeza. ibala kabusha lonke ibhulokhi ngokuqhubekayo.

Ekusetshenzisweni kwansuku zonke, lokhu kuthinta nendlela osusa noma ohambisa ngayo idatha. Uma uzama ukususa iseli elilodwa ngaphakathi kobubanzi bokugcwala, i-Excel izokuxwayisa ukuthi uthinta i-array eguquguqukayo. Ukuze ugweme izinkinga, ngokuvamile kungcono ukususa noma ukusika lonke iseli. ngqo iseli lomthombo, elihudula lonke uhla., noma lungisa ifomula ukuze ubuyisele i-matrix yosayizi ohlukile.

Uma uhlanganisa amazinga okugcwala nezinye izindlela, khumbula ukuthi lawa maseli abalwa kabusha ndawonye. Noma yikuphi ukubhekisela ku-array eguquguqukayo kufanele kwenziwe ngokucophelela, kusetshenziswa injini entsha ngaphandle kokudala izinkomba eziyindilinga ezingadingekile noma ukungqubuzana nezinye izingxenye zeshidi.

Izibonelo zamafomula e-matrix athuthukisiwe anedatha yangempela

Amafomula amaningi e-array akudala asasebenza, ikakhulukazi uma usebenza ngama-range amakhulu futhi udinga imisebenzi eyinkimbinkimbi enemibandelaAke sibheke izibonelo eziningana ezijwayelekile ongase uzithole ziwusizo kakhulu emibikweni nasemamodelini.

Cabanga ukuthi unobubanzi obuqanjwe ngegama obubizwa ngokuthi i-Data obuhlanganisa amanani amaphutha athile afana ne-#N/A. Uma usebenzisa umsebenzi we-SUM ngqo kulolo bubanzi, uzothola iphutha. Ukuze ugweme lokhu, ungasebenzisa i-array enganaki amaphutha ngefomula efana nale: =ISAMBA(UMA(ISERROR(Idatha);"";Idatha)), okudala ngaphakathi uhlu lapho amaphutha ethathelwa indawo yizintambo ezingenalutho ngaphambi kokufingqa.

Ukulandela lowo mbono ofanayo, ungabala nokuthi mangaki amaphutha akhona kububanzi. Ifomula efana =ISAMBA(UMA(ISERROR(Idatha);1;0)) Ikhiqiza uhlu oluno-1 uma kukhona iphutha kanye no-0 uma lingekho. Isamba esiphelele sikutshela inani lamaseli anephutha. Ungakwazi ngisho nokulwenza lube lula =ISAMBA(UMA(ISERROR(Idatha);1)) bese uqhubeka isinyathelo esisodwa =USUM(IF(ISERROR(Data)*1)), ukusebenzisa leli thuba lokuthi IQINISO*1 ngu-1 futhi FALSE*1 ngu-0.

Esinye isimo esivamile ukufingqa amanani ngokusekelwe ezimweni. Isibonelo, ungase ube nobubanzi obubizwa ngokuthi i-Sales futhi ufuna ukufingqa amanani amahle kuphela usebenzisa ifomula efana =IMALI(IF(Ukuthengisa>0,Ukuthengisa))Lapha, umsebenzi we-IF udala uhlu olunamanani ahlangabezana nesimo kanye namanani angamanga kwabanye, okuyinto i-SUM engayinaki ekusebenzeni.

Uma udinga ukusebenzisa izimo ezingaphezu kwesisodwa ngesikhathi, ungaphinda-phinda izinkulumo ezinengqondo ukuze ulingise ukusebenza kwe-"AND" bese uzihlanganisa ndawonye. Isibonelo esijwayelekile kungaba =I-SUM((Ukuthengisa>0)*(Ukuthengisa<=5)*(Ukuthengisa))Lokhu kubala isamba sokuthengisa esingaphezu kuka-0 futhi esingaphansi kuka-5 noma esilingana no-5. Kodwa-ke, le ndlela idinga ukuthi ububanzi bungabi namaseli ombhalo, ngaphandle kwalokho ungase uhlangane namaphutha.

Ukuze ulingise u-“OR”, ungasebenzisa izibalo zezinkulumo ezinengqondo: isibonelo, =IMALI(IF((Ukuthengisa<5)+(Ukuthengisa>15);Ukuthengisa))lapho amanani angaphansi kuka-5 noma ngaphezulu kuka-15 ehlanganiswa ndawonye. Ngale ndlela, ungenza imisebenzi ethuthukisiwe ngaphandle kokusebenzisa ngqo imisebenzi ye-AND kanye ne-OR, ebuyisela inani elilodwa elinengqondo hhayi uhlu oluphelele lwemiphumela.

Izibalo kanye nokuqhathaniswa kwezibalo namafomula e-matrix

Amafomula e-matrix nawo awusizo kakhulu bala izilinganiso noma uqhathanise phakathi kwamabanga apheleleIsibonelo esivamile sihilela ukubala isilinganiso sesethi yedatha ngaphandle kwama-zero. Uma ububanzi bakho bubizwa ngokuthi i-Sales, ifomula =ISILINGANISO(IF(Ukuthengisa<>0;Ukuthengisa)) Idala uhlu olunenani elingelona u-zero kuphela bese lidlulisela ku-AVERAGE, ngaleyo ndlela lishiya amarekhodi anganikezi ulwazi.

Esinye isibonelo esithakazelisayo ukuqhathanisa amabanga amabili anobukhulu obufanayo, isibonelo, i-MyData kanye ne-YourData. Uma ufuna ukwazi ukuthi mangaki amaseli ahlukene phakathi kwawo, ungasebenzisa =ISUM(UMA(Idatha Yami=Idatha Yakho;0;1))Le fomula ikhiqiza uhlu lwama-0 uma amanani afana kanye nama-1 uma ehlukene, bese ihlanganisa imiphumela. Uma amabanga amabili efana, ifomula izobuya no-0.

Lokhu kuqhathanisa kungenziwa lula ngokwengeziwe nge =IMALI(1*(MyData<>YourData)), okwenza ukuqhathanisa okunengqondo (MyData<>YourData) kukhiqize i-TRUE noma i-FALSE, bese kuguqulwa kube yi-1 kanye ne-0 ngokuphindaphinda ngo-1. Futhi, isihluthulelo ukusizakala ngokuziphatha kwezinkulumo ze-boolean ngaphakathi kwama-array.

Ungasebenzisa futhi amafomula e-array ukuthola inani eliphakeme kakhulu kububanzi bese uthola indawo yalo. Uma unobubanzi obubizwa ngokuthi i-Data, inketho eyodwa iwukuthi =MIN(UMA(Idatha=MAX(Idatha);ROW(Idatha);»)))Le fomula idala uhlu lapho amaseli kuphela anenani elilingana neliphezulu aqukethe inombolo yomugqa wawo; okusele kuguqulwa kube yintambo engenalutho. I-MIN bese ibuyisela inombolo yomugqa encane kunazo zonke phakathi kwalabo bantu abazongenela ukhetho, okungukuthi, ukuvela kokuqala kwenani eliphezulu.

Uma esikhundleni somugqa ufuna ireferensi ephelele yeseli, ungahlanganisa i-ADDRESS ne-logic engenhla usebenzisa into efana nale =IKHELI(I-MIN(UMA(Idatha=MAX(Idatha);UMQOLO(Idatha);»));IKHOLOMU(Idatha))ukuze uthole ireferensi efana nethi “$B$7” ekhomba ngqo ukuthi inani eliphezulu likuphi ebangeni.

Isibonelo esisebenzayo: ukuthengiswa ngomkhiqizo kusetshenziswa amafomula e-matrix

Ukuze uqonde kangcono amandla amafomula e-matrix, cabanga ngetafula lokuthengisa izimoto ezincane elinamakholomu e- umthengisi, uhlobo lwemoto, amayunithi athengisiwe, intengo yeyunithi, kanye nokuthengiswa okupheleleAke sithi amakholomu C no-D aqukethe, ngokulandelana, inombolo ethengisiwe kanye nentengo yeyunithi.

Uma ukopisha leli thebula ku-Excel, ungakhetha ububanzi E2:E11 bese ufaka ifomula efana =C2:C11*D2:D11Ezinguqulweni ezindala, bekuzodingeka uqinisekise ifomula ngo-Ctrl+Shift+Enter ukuze uyiguqule ibe yi-array yakudala ezobuyisa ukuphindaphinda komugqa ngomugqa ngobuningi. I-Excel ibizobe isibala inani lokuthengisa lomugqa ngamunye ngokuphindaphinda amayunithi ngentengo yeyunithi.

Imininingwane eyinhloko ukuthi kufanele uhlale ukhetha wonke amaseli azoqukatha imiphumela ngaphambi kokubhala ifomula ye-array. Uma ungakwenzi lokho, kungabalwa amanye amanani kuphela, noma kungadingeka ukuthi uphinde inqubo izikhathi eziningana, okuyinto engasebenzi kahle.

Kulesi simo, kuvamile futhi ukusebenzisa ifomula ye-array yeseli elilodwa ukuthola isamba esiphelele sazo zonke izintengiso. Isibonelo, ungaya kuseli B13 bese ufaka =ISUM(C2:C11*D2:D11)Uma uqinisekisa ifomula (kumodeli yakudala ene-Ctrl+Shift+Enter), i-Excel iphindaphinda inani ngalinye ku-C no-D bese ihlanganisa yonke imikhiqizo. buyisela inani elilodwa elihlanganisiwe.

Nakuba lezi zibonelo zincike ekuziphatheni okuzuzwe njengefa kwama-CSE arrays, imibono eyisisekelo iyafana naleyo esetshenziswa ngama-current dynamic arrays: ukwenza imisebenzi kububanzi obuphelele ngesikhathi esisodwa nokubuyisela imiphumela eminingi noma ehlanganisiwe ngaphandle kwesidingo samafomula asizayo aphakathi emgqeni ngamunye.

Namuhla, imisebenzi eminingi yale ingaxazululwa ngokuhlanganisa imisebenzi eguquguqukayo kanye nokugcwala, okwenza amaspredishithi kuhlanzekile, kulula ukukufunda, futhi kulula ukukunakekelaikakhulukazi lapho inani ledatha likhula noma lapho abasebenzisi abaningana besebenza encwadini efanayo.

Ukwazi kahle ukusebenzisa amafomula e-dynamic array ku-Excel kushintsha ngokuphelele indlela owakha ngayo amaspredishithi: ngokuqonda ukuthi ukusebenza kwe-overflow kusebenza kanjani, ukuthi amazinga okukhipha aziphatha kanjani, ukuthi ahluke kanjani kumafomula amadala e-CSE, nokuthi ungasebenzisa kanjani izibonelo ezisebenzayo ukuze ungawanaki amaphutha, wenze izibalo ezinemibandela, noma uqhathanise amazinga, ugcina usebenza namamodeli aguquguqukayo kakhulu, azenzakalelayo, futhi athembekile, ugcina isikhathi ngesibuyekezo ngasinye futhi unciphise amaphutha enziwe ngesandla.

Isaka

Umbhali oshisekayo ngomhlaba wamabhayithi nobuchwepheshe ngokujwayelekile. Ngiyathanda ukwabelana ngolwazi lwami ngokubhala, futhi yilokho engizokwenza kule bhulogi, ngikubonise zonke izinto ezithakazelisayo kakhulu ngamagajethi, isofthiwe, ihadiwe, izitayela zobuchwepheshe, nokuningi. Inhloso yami ukukusiza ukuthi uzulazule emhlabeni wedijithali ngendlela elula nejabulisayo.