Phatha izibalo ezininzi nge-Excel Iifomula eziHlangeneyo

Kwiiprogram ze- spreadsheet ezifana ne-Excel kunye ne-Google Spreadsheets, uluhlu luluhlu okanye uluhlu lweempawu zedatha ezihambelanayo ezigcinwa rhoqo kwiiseli eziseduze kwiphepha lokusebenzela.

Ifomula yoluhlu luyi- formula ebonisa ukubala-njengokungeza, okanye ukuphinda-phinda-kwiimpawu kwisinye okanye kwizinto ezingaphezulu kwexabiso elithile.

Iifomula zohlobo:

• sebenzisa i-syntax efanayo njengeifomula eziqhelekileyo-bonke baqala uphawu olulinganayo (=) umzekelo;
• sebenzisa abaqhubi beemathematika abafanayo;
• kwaye ulandele umyalelo ofanayo wokusebenza.

Iifomula zoHlelo kunye neMisebenzi ye-Excel

Uninzi lwe-Excel eyakhelwe imisebenzi-njenge- SUM , AVERAGE , okanye COUNT-nayo ingasetyenziselwa kwifomula yomhlathi.

Kukho nemisebenzi embalwa-efana ne-TRANSPOSE function-ekufuneka ihlale ifakwe njengoluhlu ukuze isebenze ngokufanelekileyo.

Ubuncedo bemisebenzi eminingi efana ne- INDEX kunye ne-MATCH okanye i- MAX kunye ne-IF ingahanjiswa ngokuyisebenzisa ndawonye kwifomula yomhlathi.

CSE Ifomu

Kwi-Excel, iifomula ezidibeneyo zizungezwe ngamabhonki "" } ". Ezi bhonkco azikwazi ukuba zifakwe, kodwa kufuneka zongezwe kwifomu ngokucinezela i- Ctrl, Shift, kunye ne- Enter Enter emva kokufakela ifomu kwifom okanye kwiiseli.

Ngesi sizathu, iifom zoluhlu ngamanye amaxesha zibizwa ngokuba ngumhlathi we- CSE kwi-Excel.

Imbandela kule migaqo yilapho i-brace brace isetyenziselwa ukufaka uluhlu njengengxabano yomsebenzi oqhelekileyo uqulethe ixabiso elilodwa okanye i-cell reference.

Umzekelo, kwikhowudi engezantsi esebenzisa i-VLOOKUP kunye nomsebenzi wokukhetha ukudala ifom ye-lookup yokushiya, i-array idalwe kwi-CHOOSE function Index_num argument ngokuthayipha iibhondi ezijikelezayo.

Amanyathelo okudala i-form Formula

1. Faka ifom;
2. Gcina iCtrl kunye neShift ukhiye kwikhibhodi;
3. Cinezela kwaye ukhulule ukhiye we- Enter ukudala ifom yobumba;
4. Ukukhupha ii- Ctrl kunye neShift .

Ukuba kwenziwe ngokuchanekileyo, ifomula iya kujikelezwa ngamabhonki kunye kunye neseli nganye ephethe ifomu iya kuba nomphumo ohlukileyo.

Ukuhlela i-Formula yohlobo

Naliphi na ixesha i-formula yolungiselelo ihlelwe ngayo i-curly braces yanyamalala ukusuka kwinqanaba lomgaqo.

Ukuzibuyisela kwakhona, ifomula yefayile kufuneka ifakwe ngokucinezela i- Ctrl, Shift, kunye neefayile zokungenisa kwakhona njengokuba i-formula yangaphambili yenziwe.

Iintlobo zeeFom form

Kukho ezimbini iintlobo eziphambili zefomula

• ifomula yamanqanaba emininzi - ifomula efanayo ifakwe kwisitya esingaphezu kwesinye kwiphepha lokusebenzela
• isisombululo esisodwa sefom yefomula esisodwa esenza izibalo ezininzi kwiseli yomsebenzi omnye

Multi Multi Array Formulas

Njengoko igama labo libonisa, ezi fomula ziyahlula kwiiseli ezininzi zamaphepha kwaye zibuyisela uluhlu njengempendulo.

Ngamanye amagama, ifomula efanayo ifumaneka kwiiseli ezimbini okanye ngaphezulu kwaye ubuyisela iimpendulo ezahlukileyo kwiselinye ngasinye.

Indlela okwenza ngayo le nto kukuba ikopi nganye okanye umzekelo wefomula yefomathi yenza ulinganiso olufanayo kwisitya ngasinye esilukho, kodwa umzekelo ngamnye wefomula isebenzisa idatha eyahlukileyo kwizibalo zayo, ngoko ke, umzekelo ngamnye uvelisa iziphumo ezahlukeneyo.

Umzekelo wefomula yamanxeba amaninzi uza kuba:

{= A1: A2 * B1: B2}

Ukuba umzekelo ongentla ungeziseli C1 no-C2 kwiphepha lokusebenzela ke ezi zilandelayo ziphumo:

• idatha kwi-A1 yanda nge data kwi-B1 kunye neziphumo ezigcinwe kwiseli C1
• idatha kwi-A2 yanda nge data kwi-B2 kunye neziphumo ezigcinwe kwiseli C2

Iifomula zeeNombolo eziLodwa

Uhlobo lwesibini lwefomula zoluhlu lusebenzisa umsebenzi, njenge-SUM, AVERAGE, okanye COUNT, ukudibanisa umkhiqizo wefomula yamanqanaba amaninzi kwixabiso elilodwa kwiseli enye.

Umzekelo wesalathisi esisodwa sefom yefayile yayiya kuba:

{= SUM (A1: A2 * B1: B2)}

Le fomula idibanisa umveliso we-A1 * B1 kunye ne-A2 * B2 kwaye ubuyisela umphumo owodwa kwiseli enye kwiphepha lokusebenzela.

Enye indlela yokubhala le ndlela ingentla:

= (A1 * B1) + (A2 * B2)

Uludwe lwe-Excel Iifomula ezikhoyo

Ngezantsi zidweliswe inani lezifundo eziqulethe iifomula ze-Excel.

• I-tutorial yokuqala ibandakanya iifomula ezininzi zeefom
• Ezi zifundo ezisibhozo ezilandelayo zi malunga neefomula zamanani zeselula esisodwa zisebenzisa imisebenzi ye-Excel
• I-tutorial yokugqibela i-WORLD TRANSPOSE function

01 ngo-10

Excel Multi Cell Array Formula

Ifom yeqela okanye iqela leqela leselfowuni yindlela elandelelanayo ekhoyo kwiseli enye kwiphepha lokusebenzela . Izibalo ezifanayo zenziwa kwiiseli ezininzi usebenzisa idatha eyahlukileyo nganye kwifomula nganye. Kaninzi "

02 ngo 10

I-Excel eSebenzi yeeNgcaciso zeeFomula I-Formula Isinyathelo ngeSigaba soTitshala

Iifomula eziqhelekileyo zeseli ngokuqhelekileyo ziqala ukubala ukubalwa kweeseli (ezifana nokuphindaphinda) kwaye usebenzise umsebenzi ofana ne-AVERAGE okanye i-SUM ukudibanisa umkhiqizo woluhlu kwisiphumo esisodwa. Kaninzi "

03 ngo 10

Ukungahoyi Iimpazamo zeSiphumo xa ufumana i-AVERAGE

Le fomula yoluhlu ingasetyenziselwa ukufumana ixabiso eliqhelekileyo le data ekhoyo ngelixa libala ixabiso leephutha ezifana ne # DIV / 0 !, okanye #NAME?

Isebenzisa umsebenzi we-WVERAGE kunye nemisebenzi ye-IF kunye ne-ISNUMBER. Kaninzi "

04 we-10

I-Excel's SUM IF Ifom Formula

Sebenzisa umsebenzi we-SUM kunye nomsebenzi we-IF kwifom yefrayibhile yokubala ngaphezu kweeseli zedatha ezidibeneyo kweemeko ezininzi.

Oku kwahluka kumsebenzi we-Excel we- COUNTIFS ofuna ukuba zonke iimeko zibekwe phambi kokuba iseli libale.

05 we-10

I-Excel MAX IF Ifom ye-Formula yokufumana iNani elihle kakhulu okanye elincinci

Olu qeqesho ludibanisa umsebenzi we- MAX kwaye umsebenzi we- IF kwifom yefom elandelayo iya kufumana ixabiso elikhulu okanye elona likhulu leenkcukacha xa kulandelwa umlinganiselo othile. Kaninzi "

06 ngo 10

I-Excel MIN IF IF Array Formula - Fumana iNgcinci encinci okanye iNani elincinci

Efana nale nqaku apha ngasentla, lo udibanisa umsebenzi we- MIN kunye ne-IF umsebenzi kwifom yefom elandelelanayo ukuze athole inani elincinci okanye elincinci kwinqanaba leenkcukacha xa kulandelwa umlinganiselo othile. Kaninzi "

07 ngo 10

I-Excel IMANZI IF IF Array Formula - Fumana ixabiso eliphakathi okanye eliphakathi

Umsebenzi we-MEDIA kwi-Excel ufumana uluhlu oluphakathi phakathi kwoluhlu lweenkcukacha. Ngokudibanisa kunye nomsebenzi we-IF kwindlela yokwenza i-formula, ixabiso eliphambili lamaqela ahlukeneyo eenkcukacha ezihambelanayo zingatholakala. Kaninzi "

08 ngo 10

Ifom yeLowokuQala kunye neNkcazo ezininzi kwi-Excel

Ngokusebenzisa ifom yefom yefowuni yokungena ingadalwa esebenzisa iindlela zokufumana iinkcukacha kwi-database. Le fom yobungakanani ibandakanya ukuhlawula imisebenzi ye MATCH ne- INDEX . Kaninzi "

09 we-10

Excel Left Lookup Formula

Umsebenzi weVLOOKUP ngokuqhelekileyo uhlola kuphela idatha efumaneka kwiikholam ukuya ngakwesokudla, kodwa ngokudibanisa nomsebenzi we- CHOOSE u-fom lookup formula ungadalwa oza kuphanda iikholomu zedatha ngakwesobunxele kwingxabano yeLookup_value . Kaninzi "

10 kwi-10

Ukuhambisa okanye iiFlip Rows okanye iikholam zeDatha kwi-Excel

UMSEBENZI OQINISEKILEYO usetyenziselwa ukukopisha idatha elandelwe kumqolo kwiikholomu okanye ikopi yedatha ekhompyutheni. Lo msebenzi ungomnye wezinto ezimbalwa kwi-Excel ekufuneka isetyenziswe rhoqo njengendlela yefomula. Kaninzi "