Amalungu afanayo anamandla ku-Excel

Ayini amalungu afanayo aguqukayo

NgoSepthemba 2018, iMicrosoft yakhipha isibuyekezo esengeza ithuluzi elisha ngokuphelele ku-Microsoft Excel: I-Dynamic Arrays nemisebenzi emisha engu-7 yokusebenza nayo. Lezi zinto, ngaphandle kokweqisa, zishintsha kakhulu yonke indlela evamile yokusebenza ngamafomula nemisebenzi nokukhathazeka, ngokwezwi nezwi, wonke umsebenzisi.

Cabangela isibonelo esilula ukuchaza ingqikithi.

Ake sithi sinethebula elilula elinedatha yezinyanga zedolobha. Kuzokwenzekani uma sikhetha noma yiliphi iseli elingenalutho kwesokudla seshidi futhi sifaka kulo ifomula engahlanganisi neseli eyodwa, kodwa ngokushesha ebangeni?

Kuzo zonke izinguqulo zangaphambilini ze-Excel, ngemuva kokuchofoza Faka sizothola okuqukethwe kweseli eyodwa kuphela yokuqala B2. Kanjani futhi?

Hhayi-ke, noma kungenzeka ukugoqa lobu bubanzi ngohlobo oluthile lomsebenzi wokuhlanganisa njengokuthi =SUM(B2:C4) futhi uthole isamba esikhulu sawo.

Uma besidinga imisebenzi eyinkimbinkimbi kunesamba sakudala, njengokukhipha amanani ahlukile noma i-Top 3, kuzodingeka ukuthi sifake ifomula yethu njengefomula yamalungu afanayo sisebenzisa isinqamuleli sekhibhodi. Ctrl+Shift+Faka.

Manje konke kuhlukile.

Manje ngemva kokufaka ifomula enjalo, singamane sichofoze Faka - futhi uthole ngokushesha wonke amanani uXNUMXbuXNUMXb esiwabhekise kuwo:

Lokhu akuwona umlingo, kodwa izinhlelo ezintsha eziguquguqukayo iMicrosoft Excel ezinazo manje. Siyakwamukela emhlabeni omusha 🙂

Izici zokusebenza ngamalungu afanayo aguqukayo

Ngobuchwepheshe, lonke uhlelo lwethu oluguquguqukayo lugcinwe kuseli lokuqala G4, lugcwalisa inani elidingekayo lamaseli kwesokudla naphansi ngedatha yalo. Uma ukhetha noma yiliphi elinye iseli ohlwini, isixhumanisi esikubha yefomula sizobe singasebenzi, okubonisa ukuthi sikwelinye lamaseli “engane”:

Umzamo wokususa iseli “lengane” elilodwa noma amaningi ngeke uholele kunoma yini - i-Excel izobala kabusha ngokushesha futhi iwagcwalise.

Ngesikhathi esifanayo, singabhekisela ngokuphephile kulawa maseli "engane" kwamanye amafomula:

Uma ukopisha iseli lokuqala lamalungu afanayo (ngokwesibonelo, ukusuka ku-G4 kuye ku-F8), khona-ke wonke amalungu afanayo (amareferensi alo) azohamba aye ohlangothini olufanayo njengakumafomula avamile:

Uma sidinga ukuhambisa uhlu, khona-ke kuzokwanela ukuhambisa (ngegundane noma inhlanganisela ye Ctrl+X, Ctrl+V), futhi, kuphela iseli lokuqala eliyinhloko G4 - ngemva kwalo, lizodluliselwa endaweni entsha futhi lonke uhlu lwethu luzonwetshwa futhi.

Uma udinga ukubhekisa kwenye indawo eshidini ohlwini oluguquguqukayo oludaliwe, ungasebenzisa uhlamvu olukhethekile # (“phawundi”) ngemva kwekheli leseli eliholayo:

Isibonelo, manje ungenza kalula uhlu lokudonsela phansi kuseli elibhekisela kumalungu afanayo aguquguqukayo adaliwe:

Amaphutha ohlelo olunamandla

Kodwa kwenzekani uma singekho isikhala esanele sokunweba amalungu afanayo, noma uma kukhona amaseli asevele ethathwe enye idatha endleleni yawo? Hlangana nohlobo olusha lwamaphutha ku-Excel - #DLULISELA! (#CHITHEKA!):

Njengokuvamile, uma sichofoza isithonjana esinedayimane eliphuzi kanye nophawu lwesibabazo, sizothola incazelo enemininingwane yomthombo wenkinga futhi singathola ngokushesha amaseli aphazamisayo:

Amaphutha afanayo azokwenzeka uma amalungu afanayo ephuma eshidini noma eshaya iseli elihlanganisiwe. Uma ususa isithiyo, khona-ke konke kuzolungiswa ngokushesha ku-fly.

Amalungu afanayo anamandla namathebula ahlakaniphile

Uma amalungu afanayo aguqukayo akhomba kuthebula “elihlakaniphile” elidalwe yisinqamuleli sekhibhodi Ctrl+T noma ngo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula), khona-ke izophinde izuze ikhwalithi yayo eyinhloko - ukulinganisa okuzenzakalelayo.

Lapho ungeza idatha entsha phansi noma kwesokudla, ithebula elihlakaniphile nobubanzi obuguqukayo nakho kuzokwelula ngokuzenzakalelayo:

Nokho, kunomkhawulo owodwa: asikwazi ukusebenzisa ireferensi yebanga eliguqukayo kumaforamu ngaphakathi kwethebula elihlakaniphile:

Amalungu afanayo anamandla nezinye izici ze-Excel

Kulungile, usho. Konke lokhu kuyathakazelisa futhi kuyahlekisa. Asikho isidingo, njengangaphambili, sokunweba ifomula ngesandla ngereferensi yeseli lokuqala lobubanzi bangempela phansi nakwesokudla kanye nakho konke lokho. Futhi yilokho kuphela?

Hhayi ngempela.

Amalungu afanayo anamandla akulona nje elinye ithuluzi ku-Excel. Manje zishumekwe kuyo kanye inhliziyo (noma ubuchopho) be-Microsoft Excel - injini yayo yokubala. Lokhu kusho ukuthi amanye amafomula nemisebenzi ye-Excel esijwayelene nathi manje futhi asekela ukusebenza ngama-array ashintshayo. Ake sibheke izibonelo ezimbalwa zokukunikeza umbono wokujula kwezinguquko ezenzekile.

Shintsha

Ukuguqula ububanzi (shintshanisa imigqa namakholomu) I-Microsoft Excel ibilokhu inomsebenzi owakhelwe ngaphakathi I-TRANSP (TRANSPOSE). Nokho, ukuze uyisebenzise, ​​kufanele uqale ukhethe ngendlela efanele uhla lwemiphumela (isibonelo, uma okokufaka bekuyibanga elingu-5×3, kufanele ngabe ukhethe u-3×5), bese ufaka umsebenzi bese ucindezela inkinobho. inhlanganisela Ctrl+Shift+Faka, ngoba ingasebenza kuphela kumodi yefomula yamalungu afanayo.

Manje ungavele ukhethe iseli eyodwa, ufake ifomula efanayo kuyo bese uchofoza kwejwayelekile Faka - I-dynamic array izokwenza yonke into ngokwayo:

Ithebula lokuphindaphinda

Lesi isibonelo engangivame ukusinikeza lapho ngicelwa ukuba ngibone ngeso lengqondo izinzuzo zamafomula afanayo ku-Excel. Manje, ukubala lonke itafula lePythagorean, kwanele ukuma esitokisini sokuqala se-B2, faka lapho ifomula ephindaphinda ama-array amabili (isethi yezinombolo eziqondile nezivundlile 1..10) bese uchofoza nje Faka:

I-Gluing nokuguqulwa kwekesi

Ama-arrays awakwazi ukuphindaphindwa kuphela, kodwa futhi ahlanganiswe no-opharetha ojwayelekile & (ampersand). Ake sithi sidinga ukukhipha igama nesibongo kumakholomu amabili futhi silungise icala eligxumayo kudatha yokuqala. Lokhu sikwenza ngefomula eyodwa emfushane eyakha wonke amalungu afanayo, bese sifaka umsebenzi kuwo I-PROPNACH (KUFANELE)ukulungisa irejista:

Isiphetho Top 3

Ake sithi sinenqwaba yezinombolo esifuna ukuthola kuzo imiphumela emithathu ephezulu, sizihlele ngokulandelana kokwehla. Manje lokhu kwenziwa ngefomula eyodwa futhi, futhi, ngaphandle kwanoma iyiphi Ctrl+Shift+Faka njengangaphambili:

Uma ufuna ukuthi imiphumela ibekwe hhayi kukholomu, kodwa ngokulandelana, khona-ke kwanele ukufaka esikhundleni samakholoni (isihlukanisi somugqa) kule fomula nge-semicolon (isihlukanisi sesici ngaphakathi komugqa owodwa). Enguqulweni yesiNgisi ye-Excel, lezi zihlukanisi zingama-semicolon kanye namakhoma, ngokulandelana.

I-VLOOKUP ikhipha amakholomu amaningi ngesikhathi esisodwa

Imisebenzi VPR (VLOOKUP) manje ungakwazi ukudonsa amanani hhayi kweyodwa, kodwa kusukela kumakholomu amaningana ngesikhathi esisodwa - vele ucacise izinombolo zabo (kunoma yikuphi ukuhleleka okufunayo) njengohlu ku-agumenti yesithathu yomsebenzi:

Umsebenzi we-OFFSET ubuyisela amalungu afanayo aguqukayo

Omunye wemisebenzi ethakazelisa kakhulu newusizo (ngemuva kwe-VLOOKUP) yokuhlaziywa kwedatha umsebenzi UKULAHLWA (OFFSET), engasebenzisa kuso ngesinye isikhathi isahluko sonke sencwadi yami nesihloko lapha. Ubunzima bokuqonda kanye nokwenza kahle lo msebenzi bekulokhu kuwukuthi ibuyise uhlu (uhlu) lwedatha njengomphumela, kodwa asikwazanga ukukubona, ngoba i-Excel ibingazi ukuthi isebenza kanjani ngezinhlu ngaphandle kwebhokisi.

Manje le nkinga idlule. Bona ukuthi manje, usebenzisa ifomula eyodwa kanye nohlelo oluguqukayo olubuyiswe yi-OFFSET, ungakhipha yonke imigqa yomkhiqizo onikeziwe kunoma yiliphi ithebula elihlungiwe:

Ake sibheke izimpikiswano zakhe:

  • A1 - iseli lokuqala (iphoyinti lereferensi)
  • ПОИСКПОЗ(F2;A2:A30;0) - ukubalwa kokuguquguquka ukusuka kungqamuzana lokuqala ukuya phansi - ukuya kwiklabishi lokuqala elitholiwe.
  • 0 – ukushintsha “iwindi” liye kwesokudla isihlobo neseli eqalayo
  • СЧЁТЕСЛИ(A2:A30;F2) - ukubala ukuphakama "kwefasitela" elibuyiselwe - inombolo yemigqa lapho kukhona iklabishi.
  • 4 — usayizi “wewindi” ngokuvundlile, okungukuthi amakholomu ama-4 aphumayo

Imisebenzi Emisha ye-Dynamic Arrays

Ngokungeziwe ekusekeleni umshini wamalungu afanayo aguquguqukayo emisebenzini emidala, imisebenzi eminingana emisha ngokuphelele yengezwe ku-Microsoft Excel, ecijelwe ngokukhethekile ukuze isebenze ngamaqoqo aguquguqukayo. Ikakhulukazi, lezi yizi:

  • LIBANGA (Hlunga) – ihlunga ububanzi bokufaka futhi ikhiqize amalungu afanayo aguqukayo kokuphumayo
  • I-SORTPO (HLUNGA NGE) - angakwazi ukuhlunga uhla olulodwa ngamavelu asuka kolunye
  • FILTER (ISIHLUNGI) – ithola imigqa ebangeni lomthombo elihlangabezana nemibandela eshiwo
  • I-UNIK (OKUHLUKILE) - ikhipha amanani ahlukile ebangeni noma isusa izimpinda
  • SLMASSIVE (RANDARRAY) - ikhiqiza izinombolo eziningi ezingahleliwe zosayizi othile
  • NGEMVA KOKUZALWA (UKULANDELANA) — wakha uhlu olusuka kuchungechunge lwezinombolo ngesinyathelo esinikeziwe

Okuningi ngabo - kamuva nje. Zifanele i-athikili ehlukile (hhayi neyodwa) yokutadisha okucatshangelwayo 🙂

iziphetho

Uma ufunde konke okubhalwe ngenhla, ngicabanga ukuthi usuvele uyasibona isilinganiso sezinguquko ezenzekile. Izinto eziningi ku-Excel manje zingenziwa kalula, zibe lula futhi zibe nengqondo. Kumelwe ngivume ukuthi ngishaqekile kancane ukuthi zingaki izihloko manje okufanele zilungiswe lapha, kule ngosi nasezincwadini zami, kodwa ngikulungele ukwenza lokhu ngenhliziyo elula.

Sifingqa imiphumela, pluses Amalungu afanayo aguqukayo, ungabhala okulandelayo:

  • Ungakhohlwa mayelana nenhlanganisela Ctrl+Shift+Faka. I-Excel manje ayiboni mehluko phakathi “kwamafomula avamile” kanye “namalungu afanayo amafomula” futhi iwaphatha ngendlela efanayo.
  • Mayelana nomsebenzi SUMPRODUCT (SUMPRODUCT), ebisetshenziswa ngaphambilini ukufaka amafomula afanayo ngaphandle Ctrl+Shift+Faka ungakwazi futhi ukukhohlwa – manje sekulula ngokwanele ISUM и Faka.
  • Amathebula ahlakaniphile nemisebenzi ejwayelekile (i-SUM, IF, i-VLOOKUP, i-SUMIFS, njll.) manje nayo isekela ngokugcwele noma ngokwengxenye amalungu afanayo aguqukayo.
  • Kukhona ukuhambisana okusemuva: uma uvula ibhuku lokusebenza elinamalungu afanayo aguquguqukayo enguqulweni endala ye-Excel, azoshintsha abe amafomula ahlukahlukene (ngamabrace asontekile) futhi aqhubeke nokusebenza “ngesitayela esidala”.

Kutholwe inombolo ethile imizuzu:

  • Awukwazi ukususa imigqa ngayinye, amakholomu noma amaseli ohlwini oluguquguqukayo, okungukuthi, luphila njengebhizinisi elilodwa.
  • Awukwazi ukuhlunga amalungu afanayo aguqukayo ngendlela evamile Idatha - Ukuhlunga (Idatha - Hlunga). Manje sekunomsebenzi okhethekile walokhu. LIBANGA (Hlunga).
  • Ibanga eliguqukayo alinakushintshwa libe ithebula elihlakaniphile (kodwa ungenza ububanzi obuguquguqukayo obususelwe kuthebula elihlakaniphile).

Kunjalo, lokhu akusona isiphetho, futhi ngiyaqiniseka ukuthi iMicrosoft izoqhubeka nokuthuthukisa le mishini ngokuzayo.

Ngingalanda kuphi?

Futhi ekugcineni, umbuzo oyinhloko 🙂

I-Microsoft yaqala ukumemezela futhi yabonisa ukubuka kuqala kwamalungu afanayo ashukumisayo ku-Excel emuva ngoSepthemba 2018 engqungqutheleni Ignite. Ezinyangeni ezimbalwa ezalandela, kube nokuhlolwa okuphelele nokusebenza kwezici ezintsha, kuqale Amakati abasebenzi beMicrosoft uqobo, bese kuba ngabahloli abangamavolontiya abavela kumbuthano wama-Office Insider. Kulo nyaka, isibuyekezo esingeza ama-array ashukumisayo siqale ukukhishwa kancane kancane kwababhalisile abajwayelekile be-Office 365. Isibonelo, ngiyithole ngo-Agasti kuphela ngokubhaliselwe kwami ​​kwe-Office 365 Pro Plus (Okukhonjwe Nganyanga Zonke).

Uma i-Excel yakho ingakabi nawo ama-arrays ashukumisayo, kodwa ufuna ngempela ukusebenza nawo, khona-ke kukhona izinketho ezilandelayo:

  • Uma unokubhaliselwe kwe-Office 365, ungavele ulinde kuze kube yilapho lesi sibuyekezo sifika kuwe. Ukuthi lokhu kwenzeka ngokushesha kangakanani kuncike ekutheni izibuyekezo zilethwa kaningi kangakanani eHhovisi lakho (kanye ngonyaka, kanye ezinyangeni eziyisithupha, kanye ngenyanga). Uma une-PC yebhizinisi, ungacela umlawuli wakho ukuthi asethe izibuyekezo ezizolandwa kaningi.
  • Ungakwazi ukujoyina amarenki alawo mavolontiya okuhlola ama-Office Insider - khona-ke uzoba ngowokuqala ukuthola zonke izici ezintsha nemisebenzi (kodwa kunethuba lokukhuphuka kwe-buggy ku-Excel, kunjalo).
  • Uma ungenakho ukubhalisa, kodwa inguqulo enebhokisi ezimele ye-Excel, kuzomele ulinde kuze kukhishwe inguqulo elandelayo ye-Office ne-Excel ngo-2022, okungenani. Abasebenzisi bezinguqulo ezinjalo bathola kuphela izibuyekezo zokuphepha nokulungiswa kweziphazamisi, futhi zonke “izinto ezinhle” ezintsha manje seziya kwababhalisile be-Office 365 kuphela. Kuyadabukisa kodwa kuyiqiniso 🙂

Kunoma yikuphi, lapho kuvela ama-array ashukumisayo ku-Excel yakho - ngemuva kwalesi sihloko, uzobe usulungele 🙂

  • Ayini amafomula afanayo nokuthi asetshenziswa kanjani ku-Excel
  • Iwindi (ububanzi) lihlanganisa kusetshenziswa umsebenzi we-OFFSET
  • Izindlela ezi-3 Zokuguqula Ithebula ku-Excel

shiya impendulo