I-Excel I-MEDIAN IF Array Formula

Hlanganisa imisebenzi ye-MEDIAN ne-IF ku-Formula ye-Array

Lesi sibonelo sokufundisa sisebenzisa umthamo we-MEDIAN IF ohlangene ukuze uthole ithenda eliphakathi kwamaphrojekthi amabili ahlukene.

Imvelo yefomula isivumela ukuba sifune imiphumela eminingi ngokumane sishintshe umgomo wokusesha - kulokhu, igama lephrojekthi.

Umsebenzi wengxenye ngayinye yefomula ngu:

• umsebenzi we- MEDIAN uthola ukubaluleka okuphakathi kwephrojekthi
• Umsebenzi we-IF usenza sikhethe ukuthi iyiphi iphrojekthi esiyifuna ithenda ngokubeka isimo ngokusebenzisa amagama ephrojekthi
• Ifomula ye-array ivumela umsebenzi we-IF ukuhlolwa ngezimo eziningi esitokisini esisodwa, futhi, uma isimo sesifinyelelwe, ifomula yamakhethi inquma ukuthi iyiphi idatha (ithenda yephrojekthi) umsebenzi we-MEDIAN uzohlola ukuthola ithenda eliphakathi

I-CSE Amafomula

Amafomu amaqoqo adalwe ngokucindezela okhiye be- Ctrl , Shift , no- Enter ekhibhodi ngesikhathi esifanayo uma ifomula ifakiwe.

Ngenxa yezihluthulelo ezicindezelwe ukudala ifomula yamakheli, ngezinye izikhathi zibizwa ngokuthi ama- CSE amafomula.

I-MEDIAN IF Nested Formula Syntax ne-Arguments

I- syntax ye-MEDIAN IF formula iwukuthi:

& # 61; MEDIAN (IF (okuqondakalayo_kusezingeni, inani_if_ngempela, value_if_false))

• Njengoba umsebenzi we-IF unamathele ngaphakathi komsebenzi we-MEDIAN, wonke umsebenzi we-IF uba yinto ewukuphela komsebenzi we-MEDIAN

Iziphakamiso zomsebenzi we-IF ziyi:

• kunengqondo_kugqamile - (okudingekayo) inani noma inkulumo evivinywa ukuze ibone ukuthi iqinisile noma ingamanga
• value_if_true - (edingekayo) inani eliboniswa uma kungenangqondo_kukuqiniso
• inani_if_false - (ngokuzikhethela) inani eliboniswa uma logical_test ingamanga

I-Excel & # 39; s i-MEDIAN IF Array Formula Isibonelo

Njengoba kushiwo, isibonelo sibheka amathenda amaphrojekthi amabili ahlukene ukuthola ithenda eliphakathi noma eliphakathi. Iziphakamiso zomsebenzi we-IF zifeza lokhu ngokubeka izimo nemiphumela elandelayo:

• ukuhlolwa okunengqondo kuzama ukuthola umdlalo wegama lephrojekthi elifakwe esitokisini D10 kwiphepha lokusebenzela
• I-argument value_if_true izoba, ngosizo lomsebenzi we-MEDIAN, ithenda eliphakathi lephrojekthi ekhethiwe
• I-value_if_false ingxabano izoshiywa ngoba ayidingekile futhi ukungabi khona kwayo kuzofinyeza ifomula. Uma igama lephrojekthi elingekho etafuleni lwedatha - njengeprojekthi C - lifakwe efolini D10 ifomula izobuyisela i-zero (0)

Ukufaka idatha yokufundisa

1. Faka idatha elandelayo kumaseli D1 kuya ku-E9 njengoba kuboniswe esithombeni ngenhla: Iphrojekthi ye-Project Tender Project Tender Project A Project 15,785 Project A $ 15,365 Project A $ 16,472 Project B $ 24,365 Project B $ 24,612 Project B $ 23,999 Iphrojekthi Ephakathi Project
2. Ngeselula ye-D10 "Iphrojekthi A" (ayikho izingcaphuno). Ifomula izobukeka kule seli ukuze uthole ukuthi iyiphi iphrojekthi okufanele ifane.

Ukufaka i-MEDIAN IF Nested Formula

Njengoba sakha kokubili ifomula esakhiweyo kanye nefomula yamakhemikhali, kuzodingeka ukuthi siyifake yonke ifomula ibe yinye ishidi lomsebenzi weselula .

Uma usufake ifomula Awufaki ukhiye we- Enter kwikhibhodi noma uchofoze esitokisini esithile negundane njengoba sidinga ukuvula ifomula ibe ifomula yamalungu.

1. Chofoza kuseli E10 - indawo lapho imiphumela yefomula izoboniswa khona
2. Thayipha okulandelayo:

   = I-MEDIAN (IF (D3: D8 = D10, E3: E8))

Ukudala ifomu ye-Array

1. Cindezela bese ubamba izinkinobho ze- Ctrl ne- Shift ekhibhodi
2. Cindezela ukhiye wokungena kwikhibhodi ukuze udale ifomula yamalungu
3. Impendulo engu- 15875 ($ 15,875 ngokufometha) kufanele ivele esitokisini esingu-E10 ngoba lokhu kuyithenda eliphakathi kweProjekthi A
4. Ifomula eqondile ye-array

   {= MEDIAN (IF (D3: D8 = D10, E3: E8))}

   kungabonakala kubha yefomula ngaphezulu kwephepha lokusebenzela

Hlola i-Formula

Hlola ifomu ngokuthola ithenda eliphakathi kwephrojekthi B

Thayipha i- Project B kwiselula D10 bese ucindezela ukhiye we- Enter ekhibhodi.

Ifomula kufanele ibuyele inani le-24365 ($ 24,365) kuseli E10.