Mienzaniso yeRANK Mabasa muExcel, 2021 Gaidhi

Iwe unoda kuziva sei kushandisa iyo RANK inoshanda muExcel? Pachiitiko ichi tichakusiyirai mimwe mienzaniso yeiyi basa rakanakisa. Iwe unogona kushandisa iyo RANK basa muExcel kuenzanisa nhamba nedzimwe nhamba mune imwecheteyo runyorwa. Unoda kuziva zvakawanda here? Tinokukumbira kuti urambe uchiverenga.

Maitiro ekushandisa iyo RANK basa

Zvino, ngationei kuti RANK Mabasa anoshanda sei muExcel. Semuyenzaniso, kana ukapa RANK basa nhamba uye rondedzero yenhamba, inokuudza nhanho yenhamba iyo muhurongwa, kungave mukukwira kana kudzika.

Pano iwe unogona kudzidza nezve: Maitiro ekukopa Excel Sheet kune Rimwe Bhuku Rebasa - Tutorial

Muchidzitiro chiri pazasi, pane rondedzero yebvunzo mamakisi evadzidzi gumi, mumasero B2:B11.

• 1 danho: Kuti uwane huwandu hwezvibodzwa zvemudzidzi wekutanga muchitokisi B2, isa iyi fomula muchitokisi C2:

=RANK(B2,$B$2:$B$11)

• 2 danho: Wobva wakopa fomula kubva pasero C2 kuenda kusero C11, uye zvibodzwa zvicharongwa mukudzika.

Nharo dzeRANK mabasa muExcel

Pane 3 nharo kumabasa eRANK muExcel, ayo ari:

1. Nhamba: Mumuenzaniso uri pamusoro, nhamba yekusarudza iri muchitokisi B2
2. Ref: Kana iwe uchida kuenzanisa nhamba nerondedzero yenhamba mumaseru $B$2: $B$11. Inoshandisa chinongedzo chakakwana ($B $2:$B11), pachinzvimbo chereferensi (B2:B11) kuitira kuti referensi yereferensi irambe yakafanana kana uchikopa fomula mumaseru ari pazasi.
3. kurongeka: (optional) Iyi nharo inoudza Excel kuti igadzirise rondedzero mukukwira kana kudzika.
   1. Shandisa zero, kana kusiya nharo iyi isina chinhu, kuti uwane huwandu huri muhurongwa mukudzika. Mumuenzaniso uri pamusoro, nharo yehurongwa yakasiiwa isina chinhu kuti uwane huwandu mukudzika kwekudzika. =RANK(B2,$B$2:$B$11)
   2. Nekuda kwekukwira, nyora 1 kana imwe nhamba kunze kwe zero. Kana wanga uchienzanisa zvibodzwa zvegorofu, waigona kupinda 1 kuti umire mukukwira. =RANK(B2,$B$2:$B$11)

RANK Kurongeka kwebasa

Mune RANK mabasa muExcel, nharo yechitatu (kurongeka) isarudzo. Iyo nharo yekuraira inoudza Excel kuti igadzirise rondedzero mukukwira kana kudzika kurongeka.

Kudzika kurongeka muRANK mabasa muExcel

Kana iwe ukashandisa zero segadziriro yekurongeka, kana iwe usingashandisi nharo yechitatu, huwandu hunoiswa pakudzika kurongeka.

• Nhamba yakakura anowana chinzvimbo che1
• Nhamba yechishanu pakukura inowana chinzvimbo che5.

Kukwira kurongeka muRANK mabasa muExcel

Kana iwe ukashandisa 1 segadziriro yekurongeka, kana iwe ukapinda chero nhamba kunze kwazero senharo yechitatu, kuronga kunoiswa muhurongwa hwekukwira.

• Nhamba diki anowana chinzvimbo che1
• Nhamba diki yechishanu inowana chinzvimbo che5.

Flexible formula muRANK inoshanda muExcel

Panzvimbo pekunyora iyo yekuronga nharo nhamba mumabasa eRANK muExcel, shandisa sero referenzi kugadzira fomula inochinjika.

Somuenzaniso: inonyora 1 kusero E1 uye zvinongedzo kusero E1 kuitira nharo yekuraira.

NOTA: iwe unofanirwa kuve nechokwadi chekushandisa absolute reference ( $E$1 ), ingave fomula yacho ichakopwa kune mamwe mitsara. Kana iwe ukashandisa chirevo chehukama (E1), referensi ichachinja mumutsara wega wega.

=RANK(B2,$B$2:$B$6,$E$1)

Nekubatanidza kune sero, unogona kukurumidza kuona mhinduro dzakasiyana, pasina kushandura fomula. Nyora zero muchitokisi E1 kana kudzima nhamba uye huwandu huchachinja kuenda kuKudzika hurongwa.

Shandisa bhokisi rekutarisa muRANK mabasa muExcel

Pakurongeka kwesarudzo, kune sarudzo mbiri chete: kukwira kana kudzika. Kuti zvive nyore kuti vanhu vachinje odha, shandisa bhokisi rekutarisa kubatidza kana kudzima odha inokwira.

• Kana ON, iyo RANK odha ichange ichikwira.
• Kana AKADZIMA, iyo RANK odha ichave ichidzika.

Loops yeRANK inoshanda muExcel

Chii chinoitika kune chinzvimbo kana zvimwe zvibodzwa zvakasungwa? Excel inosvetuka nhamba dzinotevera, kana zvichidikanwa, kuratidza iro rakakodzera renji.

• Mumuenzaniso uyu uri pamusoro, zvibodzwa zviviri zvekupedzisira pachirongwa zvakafanana: 38. Vadzidzi vaviri, Ivy naJoe, yakanyorwa se4th.
• Chibodzwa chepamusoro chinotevera, chibodzwa chaEd chemakumi matatu nematanhatu, chiri ari pachinhanho chechitanhatu, kwete muchishanu, nokuti kune vadzidzi 5 mberi kwake.

Dhonza zvisungo neRANK mabasa muExcel

Mune zvimwe zviitiko, zvisungo hazvibvumirwi, saka unofanira kutsvaga nzira yekuputsa tie.

Mumuenzaniso uyu, unogona kuronda nhamba yemaminetsi akashanda mudzidzi wega wega pabvunzo uye woshandisa nguva iyoyo kutyora hukama. Kana zvibodzwa zvakasungwa, mudzidzi anotora nguva shoma kuti apedze bvunzo anoiswa pamberi pemumwe mudzidzi ane zvibodzwa zvakafanana.

Verenga Decimal Mari yeZvakabatana Zvikoro neRANK Mabasa muExcel

• 1 danho: Wedzera nguva dzekuyedza mukoromo C uye TieBreak fomula mukoramu E.
  • =KANA(COUNTIF($B$2:$B$11,B2)>1,
    CHIKAMU(C2, $C$2: $C$11,1)/100,0)

Iyo tiebreaker formula inoshanda sei muRANK mabasa muExcel

Iyo Tie Break formula inoshandisa mabasa COUNTIF uye RANGE, yakaputirwa ne IF basa, kuona kana tie-breaking decimal ichifanira kuwedzerwa kune yekutanga Range.

• 1 danho: Chekutanga, iyo TieBreak fomula inotarisa KANA paine inopfuura imwe muenzaniso yenhamba mune yese runyorwa: IF(COUNTIF($B$2:$B$11,B2) > 1
• 2 danho: Kana paine kanopfuura kamwechete, ronga nguva dzakarongeka kukwira, nokuti nguva yakaderera iri nani:CHIKAMU (C2, $C$2: $C$11, 1)
• 3 danho: Tevere, patsanura huwandu uhu nezana kuti uwane desimali. Gare gare, iwe unozowedzera iyi nhamba yedesimali kune yekutanga renji.

chitsamba: Iyo divisor, zana, inogona kuchinjirwa kune imwe nhamba, kana uri kushanda nerunyorwa rurefu.   / 100

• 4 danho: Pakupedzisira, kupedzisa IF basa, kana paine imwe chete AA Rank muenzaniso, mhedzisiro ye TieBreak iri zero. (0)

Verenga iyo yekupedzisira chinzvimbo neiyo RANK mabasa muExcel

Mushure mekuverenga iyo decimal tie-kutyora huwandu, unogona kuwedzera mhedzisiro yeRANK basa kune TieBreak mhedzisiro kuti uwane yekupedzisira chinzvimbo.

Mumuenzaniso uyu, vadzidzi vaviri vakasungwa pachinzvimbo chechina.

• Zvakatora Joe maminetsi makumi maviri nemanomwe kuti apedze bvunzo uye nguva yake ari pachishanu.
• Zvakatora Ivy maminetsi makumi maviri nemapfumbamwe kuti apedze bvunzo uye nguva yake ari pachipfumbamwe.

Iyo Tie Break formula inowedzera decimal ye 0.09 kusvika kuIvy's mamakisi uye 0.05 kune zvibodzwa zvaJoe. Muchikamu chekupedzisira, Joe, ane 4.05, ari pamusoro peIvy, aine 4.09.

Kupatsanura mihoro yenzvimbo yakasungirirwa

Mumakwikwi, panzvimbo yekutyora zvisungo, ungangoda kupatsanura mawinnings pakati pevatambi vakasungwa, kana iwe uchipa mubairo wemari kana mapoinzi. Kana vatambi 2 kana kupfuura vaine chinzvimbo chakafanana, vanozopatsanura huwandu hwemubairo huripo padanho iroro, kusvika kune inotevera chinzvimbo.

Pazasi pane tafura yemubairo wemuenzaniso, inoratidza mari yakapihwa padanho rega rega. Mumuenzaniso uyu, kana vatambi vatatu vari pachinhanho 3, vangapatsanura huwandu hwese (10 + 9 + 8 = 27) pachikamu 1, 2 ne3.

Mumwe nemumwe wevatambi vatatu vari pachinhanho 3 anohwina 9 (27/3 = 9) uye mutambi ane chibodzwa chepamusoro anotevera angave pachinhanho chechina ohwina 4.

Verenga huwandu hwakakamurwa neRANK mabasa muExcel

Kugovanisa huwandu hwemubairo pakati pevatambi vakasungwa, fomula yemubairo inoshandisa iyo AVERAGE basa, ne OFFSET basa kuwana huwandu hwemasero kusvika pakati. Iyi fomula inopinzwa muchitokisi D2 uye inokopwa muchitokisi D11.

= AVERAGE(OFFSET($K$1,C2,0,COUNTIF($C$2:$C$11,C2)))

Maitiro emubairo fomula anoshanda neRANK mabasa muExcel

Iyo mubairo formula inoshandisa iyo AVERAGE basa, ne OFFSET basa kuwana huwandu hwemasero kusvika pakati.

• 1 danho: Iyo AVERAGE basa richaverenga huwandu hwemutambi wega wega, zvichibva pane yakatarwa masero: AVERAGE (
• 2 Step: Iyo OFFSET basa rinodzosa huwandu nehuwandu hwekushandisa paavhareji: OFFSET (
• 3 Step: Mune iyo OFFSET formula, nharo yekutanga ireferensi sero. Mumuenzaniso uyu, ndiyo sero K1, musoro weiyo Prize Amounts column.$K$2,
• 4 Step: In the OFFSET formula, nharo yechipiri inhamba yemitsara yakadzika kubva pareferensi yesero, iyo masero anotanga kuita avhareji. Marenji akanyorwa muhurongwa hwekukwira, saka paChinzvimbo che1, maseru kusvika pakati aizotanga mutsara umwe zvichidzika kubva paReferensi cell ye$K$1 Chinzvimbo chemutambi wekutanga chiri musero C1, saka zvitarise muforamu C2,
• 5 Step: Mune iyo OFFSET formula, nharo yechitatu inhamba yemakoramu kurudyi kwereferensi cell, kubva panotanga maseru ekuyereswa. Iwe unoda kutsvaga huwandu muchikamu chimwe chete, saka nhamba yacho i zero 0,
• 6 Step: Mune iyo OFFSET formula, nharo yechina ndiyo nhamba yemitsara yekuisa muhuwandu. Iyi inofanira kunge iri nhamba yevatambi vakasungirirwa pachinzvimbo ichocho. Iyo COUNTIF basa ichaverenga zviitiko zvehuwandu mukororamu C, iyo yakaenzana nerenji muC2 KANA($C$2:$C$11,C2)

RANK inoshanda muExcel neIF

Panzvimbo pekushandisa iyo RANK basa kuenzanisa nhamba kune yakazara runyorwa rwenhamba, ungangoda kuisa kukosha mukati meimwe subset yenhamba.

Somuenzaniso, inoronga kutengesa kwezuva rega rega zvichienzaniswa nemamwe mazuva muvhiki imwe chete. Mumufananidzo uri pasi apa, kune zvinyorwa zvekutengesa kwemavhiki maviri.

1. Ndira 2nd naNdira 4th vane yakanyanya kutengesa muvhiki 1, saka ivo vanofanirwa kuve nechikamu che1.
2. Muvhiki 2, Ndira 10th ine yakanyanya kutengesa, saka inofanirwa kuve nenzvimbo ye1 yevhiki iyoyo.

Pasina RANK IF basa

Iko hakuna RANKIF basa, asi iwe unogona kushandisa iyo COUNTIF basa kuverenga uwandu zvichienderana nezvinhu zvine nhamba yevhiki imwe chete.

Pinda iyi fomula muchitokisi D2 woikopa kumutsara wekupedzisira nedata: = COUNT.SE ([Wk], [@Wk], [Kutengesa], «>» uye [@Kutengesa]) + 1

Iyo inoshanda sei

Chiyero chekutanga cheformula chinotarisa kune kumwe kutengesa nenhamba yevhiki imwechete:

= COUNTSE ([Wk], [@Wk]

Chepiri chechipiri chinotarisa zvinhu zvine huwandu hwepamusoro muKutengesa column.

[Kutengesa], «>» uye [@Kutengesa])

Zvadaro, 1 inowedzerwa kune iyo nhamba, kuti uwane iyo chinzvimbo.

+1

Semuenzaniso, muvhiki yekutanga, tarisa kutengesa kubva muna Ndira 1 kusvika 3.

• Kune 2 misi ine yakanyanya kutengesa muvhiki 1: Ndira 2 uye Ndira 4
• Wedzera 1 kune iyo nhamba, uye Ndira 3 ine huwandu hwe3

Iwe unogona zvakare kufarira kuverenga nezve: Maitiro ekugadzira PivotTable neMwedzi muExcel

Sezvauchaona, iyi ndiyo mienzaniso yemabasa eRANK muExcel, iyi inzira inoshanda kana iwe uchida kuenzanisa nekukurumidza. Isu tinongokurudzira kuti udzidzire kuitira kuti zviuye zvakasununguka kwauri. Tinovimba takakubatsira.