Kuzinhlelo zesipredishithi ezifana ne-Excel nama-Google Spreadsheets, i-array ibanga noma uchungechunge lwamanani ahambisanayo nedatha ngokuvamile agcinwe kumaseli aseduze kukhasi lokusebenzela.
Ifomula yamakhemikhali iyimfomula eyenza izibalo-njengokungeza, noma ukuphindaphinda-ngamagugu kokukodwa noma ngaphezulu kunezinto eziningi kunenani elilodwa lemininingwane.
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 -can nayo isetshenziselwa ifomula yamalungu.
Kukhona nemisebenzi embalwa-efana ne-TRANSPOSE function-okumele ihlale ifakwe njengendlela ehlelekile ukuze isebenze 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 zinsimbi azikwazi ukufakwa nje kuphela, 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;
- Gcina 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 eziyinhloko zamafomu ahlukene:
- amakhemikhali amaningi we-cell array - ifomula elifanayo lisendaweni engaphezu kwesisodwa kwiphepha lokusebenzela
- isisombululo esisodwa sefomula esisodwa sefomula eyodwa efaka 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, kodwa 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}
Uma isibonelo esingenhla sisezinqoleni C1 no-C2 kufomathi lokusebenzela bese imiphumela elandelayo ingaba:
- idatha ku-A1 yanda ngemininingwane ku-B1 nemiphumela egcinwe kuseli C1
- idatha ku-A2 yanda ngemininingwane ku-B2 nemiphumela egcinwe kuseli C2
Amafomu e-Single Cell Array Formulas
Loluhlobo lwesibili lwamakhemu afanayo lusetshenziswa umsebenzi, njenge-SUM, AVERAGE, noma COUNT, ukuhlanganisa ukukhishwa kwefomula yamaselingi amaningi e-cell esilinganisweni esisodwa kuseli elilodwa.
Isibonelo sefomula eyodwa yamaseli eyodwa kungaba:
{= SUM (A1: A2 * B1: B2)}
Le fomula ihlanganisa ndawonye umkhiqizo we-A1 * B1 no-A2 * B2 futhi ubuyisela umphumela owodwa esitokisini esisodwa ekhasini lokusebenzela.
Enye indlela yokubhala ifomula ngenhla ingaba:
= (A1 * B1) + (A2 * B2)
Uhlu lwe-Excel Yakha amafomula
Ngezansi kubalwa inombolo yezifundo eziqukethe amafomula ahlukahlukene e-Excel.
- Isifundo sokuqala sihlanganisa amafomula amaningi amathrekhi amaningi
- Izifundiswa eziyisishiyagalombili ezilandelayo ziphathelene namafomula ahlukene weselula asebenzisa imisebenzi ehlukahlukene ye-Excel
- Isifundo sokugcina sise-TRANSPOSE function
01 kwezingu-10
I-Excel Multi Cell Array Formula
Ifomula lamathuluzi amaningi noma amakhemikhali amaningi wamaseli yifomula ehambisanayo etholakala engaphezu kwesisodwa kwifomu lokusebenzela . Izibalo ezifanayo zenziwa ngamaseli amaningi usebenzisa idatha ehlukene yefomu ngayinye. Okuningi "
02 kwezingu-10
I-Excel Single Cell Array Formula Isinyathelo ngesinyathelo Isifundo
Amafomula angasese amancane ajwayelekile aqala ukwenza izibalo eziningi zamanani weselula (njengokuphindaphindiwe) bese usebenzisa umsebenzi onjenge-AVERAGE noma i-SUM ukuhlanganisa ukukhipha komphumela kumphumela owodwa. Okuningi "
03 ka-10
Yeka izimiso zokuphutha lapho uthola i-AVERAGE
Leli fomula elihlanganisiwe lingasetshenziswa ukuthola inani elilinganiselwe lwedatha ekhona ngenkathi ingayinaki amanani wephutha njenge- # DIV / 0 !, noma i- #NAME?
Isebenzisa umsebenzi we-AVERAGE kanye nemisebenzi ye-IF ne-ISNUMBER. Okuningi "
04 kwezingu-10
I-SUM IF Ex IF Formula
Sebenzisa umsebenzi we-SUM kanye nomsebenzi we-IF kumfomula we-array ukuze ubale kunokuba uqoke amaseli wedatha ahlangabezana nomunye wemibandela eminingi.
Lokhu kuhluke kumsebenzi we-Excel we- COUNTIFS okudinga ukuthi zonke izimo zibekwe ngaphambi kokuba iseli libalwe.
05 ka-10
I-Excel MAX IF Ifomula Yomhlahlandlela Yokuthola Inombolo Enhle Kunazo Zonke Noma Enhle
Le tutorial ihlanganisa umsebenzi we- MAX kanye nomsebenzi we- IF kumfomula we-array ozothola inani elikhulu noma eliphakeme kakhulu lohlu lwemininingwane uma umgomo othize uhlangene. Okuningi "
06 kwangu-10
I-Excel MIN IF Fomula Iformula - Thola Inombolo Encane Kunazo Zonke Noma Inombolo Enhle
Ngokufana nalokho okushiwo ngenhla, lokhu kuhlanganisa umsebenzi we- MIN nomsebenzi we- IF kumfomula wamakhemikhali ukuze uthole inani elincinci noma elilinganiselwe lohlu lwemininingwane uma kutholakala umgomo othize. Okuningi "
07 kwangu-10
I-Excel I-MEDIAN IF Array Formula - Thola inani eliphakathi noma eliphakathi
Umsebenzi we-MEDIAN ku-Excel uthola inani eliphakathi lohlu lwedatha. Ngokuzihlanganisa nomsebenzi we-IF kumfomula we-array, inani eliphakathi lamaqembu ahlukene wedatha ehlobene lingatholakala. Okuningi "
08 kwezingu-10
I-Formula Yokuthola Ngezimpawu Eziningi ku-Excel
Ngokusebenzisa ifomula yamagama ifomula yokufundwa ingadalwa esebenzisa izindlela eziningi ukuthola ulwazi ku-database. Leli fomula elihlanganisiwe lihilela ukwakha imisebenzi ye- MATCH ne- INDEX . Okuningi "
09 kwezingu-10
I-Excel Lookup Formula Left
Umsebenzi we-VLOOKUP uvame ukusesha kuphela idatha ekhonjiwe ngakwesokudla, kodwa ngokuyihlanganisa nomsebenzi we- CHOOSE u- eft lookup ifomula kungadalwa ukuthi izosesha amakholomu wedatha ngakwesokunxele kwempikiswano ye- Lookup_value . Okuningi "
10 kwangu-10
Thumela noma Flip Rows noma Columns of Data ku-Excel
Umsebenzi we-TRANSPOSE usetshenziselwa ukukopisha idatha elandelanayo ibe ikholomu noma ukopishe idatha ekhoneni elandeni. Lo msebenzi ungomunye wabambalwa ku-Excel okumelwe usetshenziswe ngaso sonke isikhathi njengefomula yamalungu. Okuningi "