Funda ukuthi ama-arrays angenza kanjani umsebenzi ku-Excel
Uhlu lububanzi noma iqembu lamanani ahambisanayo nedatha. Kuzinhlelo zesipredishithi ezifana ne-Excel nama-Google Spreadsheet, amanani ku-array avame ukugcinwa kumaseli aseduze.
Ukusetshenziswa kwe-Arrays
I-Arrays ingasetshenziselwa kokubili amafomula (amanani amafomu) kanye neziphakamiso zemisebenzi efana namafomu afanayo we-LOOKUP ne- INDEX imisebenzi.
Izinhlobo ze-Arrays
Kunezinhlobo ezimbili zemigqa e-Excel:
- I-one-dimensional array, eyaziwa nangokuthi i-vector noma i-vector array. Idatha iyatholakala:
- emgqeni owodwa (uhlangothi olulodwa oluyingqayizivele lendawo).
- kukholomu eyodwa (i-single-dimensional vertical array).
- I-two-dimensional array, eyaziwa nangokuthi i-matrix
- idatha itholakala kumakholomu amaningi noma imigqa.
- I-Table Array - Ithebula lethebula lenye yezingxabano ezisetshenziselwa imisebenzi yokuhlola ye-Excel, njenge- VLOOKUP ne-HLOOKUP.
- Imisebenzi yokubuka ihlola uhlu lwetafula ukuze uthole ulwazi oluqondile.
- Ku-VLOOKUP (ukubonakala okuqondile), i-table_array kufanele ibe namakholomu amabili wedatha okungenani.
- Ku-HLOOKUP (ukubuka okuzungezile), i-table_array kufanele ibe nemigqa emibili yedatha.
Bhala ukubuyekeza
Ifomula yamafomu iyimfomula eyenza izibalo - njengokungeza, noma ukubuyabuyelela - ngamagugu kokukodwa noma ngaphezulu kwesigcawu kunokubaluleka kwedatha eyodwa.
Amafomu amahle:
- sebenzisa i-syntax efanayo njengamafomula avamile (bonke baqala ngesibalo esilinganayo (=) isibonelo).
- sebenzisa opharetha abafanayo bezibalo .
- futhi ulandele umyalelo ofanayo wokusebenza.
Amafomu amafomu kanye nemisebenzi ye-Excel
Imisebenzi eminingi e-Excel eyakhelwe ngaphakathi - njenge-SUM, AVERAGE, noma COUNT - ingasetshenziswa futhi kwifomula yamalungu.
Kukhona nemisebenzi embalwa - njengomsebenzi we-TRANSPOSE - okumele uhlale ungeniswa ukuze usebenze kahle.
Ukusebenza kwemisebenzi eminingi efana ne-INDEX ne-MATCH noma i-MAX ne-IF inganwetshwa ngokuwasebenzisa ndawonye kwifomula yamalungu.
I-CSE Amafomula
Ku-Excel, amafomu amahle ahlanganiswe nama-curly braces " {} ". Lezi zibopho azikwazi nje ukuthayipha kodwa kufanele zengezwe kwifomula ngokucindezela izinkinobho ze-Ctrl, Shift, ne-Enter ngemuva kokuthayipha ifomula ibe iseli noma amaseli.
Ngenxa yalesi sizathu, ifomula yamagama ngezinye izikhathi ibizwa ngokuthi ifomu le-CSE ku-Excel.
Okungafani nalomthetho wukuthi lapho amabhontshisi asebenzayo asetshenziselwa ukufaka i-array njengengxabano yomsebenzi ngokuvamile oqukethe inani elilodwa noma ireferensi yeseli .
Isibonelo, kusifundo esingezansi esisebenzisa i-VLOOKUP kanye nomsebenzi wokukhetha ukudala ifomula yokufundwa kwesobunxele, i-array idalwe yenkomba ye- CHOOSE function_num argument ngokuthayipha ama-brace azungeze uhlu olufakelwe.
Izinyathelo Zokudala I-Formula Yokwakha
- Faka ifomula.
- Bamba izinkinobho ze- Ctrl ne-Shift ekhibhodi.
- Cindezela bese ukhulula ukhiye we- Enter ukudala ifomula yamakheli.
- Khipha okhiye be- Ctrl ne-Shift .
Uma kwenziwe kahle, ifomula izozungezwa ngamabhonti aphikisiwe futhi iseli ngalinye eliphethe ifomula lizoqukatha umphumela ohlukile.
Ukuhlela i-Formula ye-Array
Noma yisiphi isikhathi ifomula ehleliwe ihlelwe ama-curly braces ayanyamalala ukusuka kumakhemikhali afanayo.
Ukuze uwabuyisele emuva, ifomula yamakheli kufanele ifakwe ngokucindezela izinkinobho ze-Ctrl, Shift, ne-Enter futhi njengalapho kuqala ifomula yamakheli.
Izinhlobo Zamafomu Okuhle
Kunezinhlobo ezimbili zamafomu okulandelana:
- Ifomula yamakheli amaningi, lapho ifomu elifanayo likhona ezingaphezu kwesisodwa kwifomu lokusebenzela;
- Ifomula elilodwa lamaseli elilodwa, lapho ifomula elilodwa lenza izibalo eziningi kuseli elilodwa lomsebenzi weshidi.
Ama-Multi-Cell Array Formulas
Njengoba igama labo liphakamisa, la mafomula ahlukene atholakala kumaseli amaningi okusebenza omsebenzi futhi abuyisela uhlu njengempendulo.
Ngamanye amazwi, ifomula efanayo ifakwe amangqamuzana amabili noma ngaphezulu futhi ibuyisela izimpendulo ezihlukene esitokisini ngasinye.
Indlela okwenza ngayo lokhu ukuthi ikhophi ngayinye noma isibonelo sefomula yamakheli yenza ukulingana okufanayo esitokisini ngasinye esitholakala kuyo, kepha isinye isinye sefomula sisebenzisa idatha ehlukene ekubalweni kwayo, ngakho-ke, isinye isenzakalo sibonisa imiphumela ehlukene.
Isibonelo sefomula yamakheli amaningi amaningi kungaba:
{= A1: A2 * B1: B2}
Amafomu e-Single Cell Array Formulas
Loluhlobo lwesibili lwamakhemu afanayo lusebenta umsebenzi - njenge-SUM, AVERAGE, noma COUNT - ukuhlanganisa ukukhishwa kwefomula yamakheli amaningi we-cell ibe yi-value eyodwa esitokisini esisodwa.
Isibonelo sefomula eyodwa yamaseli eyodwa kungaba:
{= SUM (A1: A2 * B1: B2)}