Tupeane maujanja ya Excel

[Njuka II]

Hata Vlookup, ningeitumia ningekopi hizo headings nikaziweka vertically nikainsert formula na kupata majibu baada ya kuwasort alphabetically

Mimi nilikuwa nataka general formula ya kutumia hata kama hawajawa sorted (multiple names)

In case kama lookup values zipo nyingi (zaidi ya moja),VLOOKUP huwa inachukua data ya kwanza tu, data zingine zinazofuata zinakuwa omitted

 

[Promenthous]

Hata Vlookup, ningeitumia ningekopi hizo headings nikaziweka vertically nikainsert formula na kupata majibu baada ya kuwasort alphabetically

Mi nilikuwa nataka general formula ya kutumia hata kama hawajawa sorted (multiple names)

Vlookup haiwezi kwa case yako,labda uifanyie nested na formula nyingine,hata index match haiwezi pia peke yake kukupa jibu unalotaka,hizo ni just lookup functions,weka majina ya kila casual unaetaka kupata amount zao,hauna haja ya kusort wala kufilter chochote,thn tumia formula aliyokupa Njuka au sumif kupata total ya kila mmoja thn mwisho tafuta grand total, alternative unaweza kutumia Pivot Table, kama hautaki kuandika table ya summary,hakikisha kwenye value unabadilisha count iwe sum.

 

[APEFACE]

In case kama lookup values zipo nyingi (zaidi ya moja),VLOOKUP huwa inachukua data ya kwanza tu, data zingine zinazofuata zinakuwa omitted

Si naikopi kwenye values zingine alafu nakuwa nabadilisha column index number kwa kuitafuta matching value ninayoitaka au inakaaje hapa

 

[APEFACE]

Vlookup haiwezi kwa case yako,labda uifanyie nested na formula nyingine,hata index match haiwezi pia peke yake kukupa jibu unalotaka,hizo ni just lookup functions,weka majina ya kila casual unaetaka kupata amount zao,hauna haja ya kusort wala kufilter chochote,thn tumia formula aliyokupa Njuka au sumif kupata total ya kila mmoja thn mwisho tafuta grand total, alternative unaweza kutumia Pivot Table, kama hautaki kuandika table ya summary,hakikisha kwenye value unabadilisha count iwe sum.

Kwenye pivot huko ndo sijajaribu, hebu ngoja nione

 

[Mwalimu wa tuisheni]

Asante Kwa Swali Lako na samahani Kwa kuchelewa kukujibu..

Hapo tutatumia function ya RANK na Sio SORTING kama mdau mmoja alivoelekeza..

Unaandika =RANK(C3,C3:C12,1)

Formula hiyo iweke Kwenye Column mwanzo ili uweze ku Drag mpaka mwisho...

Lakini kumbuka C3 ni namba unayotaka ipewe nafasi yake

C3:C12 ndio range ambao Unataka itumike na hiyo moja ya mwisho ni kwamba ianze kuhesabu namba kuanzia 1 na Sio 0,1,2....

Pia hakikisha una ongezea Alama ya $ Kwenye kila herufi ya range yako ili unapodrag isibadilishe maana..

Kwahiyo itakuwa =RANK(C4,$C$3:$C$12,1)

Nikitaka ku drag kwenye simu nafanyaje

 

[wakutafuta]

Nikitaka ku drag kwenye simu nafanyaje

Hold kibox cha kudrag mpaka ikulete options kama picha ya kwanza hapo then chagua fill kibox kitabadilika na kuwa kama picha ya pili then uta drag tu.

 

[Mwalimu wa tuisheni]

Hold kibox cha kudrag mpaka ikulete options kama picha ya kwanza hapo then chagua fill kibox kitabadilika na kuwa kama picha ya pili then uta drag tu.View attachment 2472838View attachment 2472844

Ahsante sana mkuu, nineweza

 

[wakutafuta]

Ahsante sana mkuu, nineweza

Vizuri[emoji1531][emoji1531]

 

[Bloodstone]

Interesting

 

[Njuka II]

Si naikopi kwenye values zingine alafu nakuwa nabadilisha column index number kwa kuitafuta matching value ninayoitaka au inakaaje hapa

Kwasababu pc unayo, kwanini usingekuwa unajaribu kufanya kwanza ili ukaona output inatokeaje mana ukisema usubiri mpaka mtu akuambie ukifanya hivi kinatokea hiki utachelewa sana kujifunza

 

[APEFACE]

Kwasababu pc unayo, kwanini usingekuwa unajaribu kufanya kwanza ili ukaona output inatokeaje mana ukisema usubiri mpaka mtu akuambie ukifanya hivi kinatokea hiki utachelewa sana kujifunza

Hapa nilikuwa najaribu kuwa na alternatives ngapi katika kusolve hii issue, already solved

 

[k2206]

Unganisha text na formula kwa kutumia alama &
Mfano
="CIV- "&IF(B2>=81,"A",IF(B2>=61,"B",.........IF(B2>=0,"F")))))&" KISW- "&IF(C2>=81,"A",IF(C2>=61,"B",.........IF(C2>=0,"F")))))&" HIST- "&IF(D2>=81,"A",IF(D2>=61,"B",.........IF(D2>=0,"F"))))).......

Mkuu nashukuru sana. Nimeifanyia kazi hii na nimepata matokeo ninayoyataka.
Naomba pia kufahamu iwapo mwanafunzi hajafanya mtihani(absent), nifanyeje Ili ionekane hayupo?
Kwa formula hizo hapo wanafunzi hata kama hayupo itamwandikia masomo bila maksi wakati Mimi nataka aonekane hajafanya.
Naomba msaada hapo tafadhali.

 

[Njuka II]

Mkuu nashukuru sana. Nimeifanyia kazi hii na nimepata matokeo ninayoyataka.
Naomba pia kufahamu iwapo mwanafunzi hajafanya mtihani(absent), nifanyeje Ili ionekane hayupo?
Kwa formula hizo hapo wanafunzi hata kama hayupo itamwandikia masomo bila maksi wakati Mimi nataka aonekane hajafanya.
Naomba msaada hapo tafadhali.

Where column A ni column ya majina ya wanafunzi

="CIV- "&IF(AND(A2<>"",B2=""),"ABS",IF(B2>=81,"A",IF(B2>=61,"B",.........IF(B2>=0,"F"))))))&" KISW- "&IF(AND(A2<>"",C2=""),"ABS",IF(C2>=81,"A",IF(C2>=61,"B",.........IF(C2>=0,"F"))))))&" HIST- "&IF(AND(A2<>"",D2=""),"ABS",IF(D2>=81,"A",IF(D2>=61,"B",.........IF(D2>=0,"F")))))).......

 

[k2206]

Where column A ni column ya majina ya wanafunzi

="CIV- "&IF(AND(A2<>"",B2=""),"ABS",IF(B2>=81,"A",IF(B2>=61,"B",.........IF(B2>=0,"F"))))))&" KISW- "&IF(AND(A2<>"",C2=""),"ABS",IF(C2>=81,"A",IF(C2>=61,"B",.........IF(C2>=0,"F"))))))&" HIST- "&IF(AND(A2<>"",D2=""),"ABS",IF(D2>=81,"A",IF(D2>=61,"B",.........IF(D2>=0,"F")))))).......

Pamoja sana mkuu. Nitaifanyia kazi pia hii.
Nitatoa mrejesho.
Ahsante sana.

 

[k2206]

Where column A ni column ya majina ya wanafunzi

="CIV- "&IF(AND(A2<>"",B2=""),"ABS",IF(B2>=81,"A",IF(B2>=61,"B",.........IF(B2>=0,"F"))))))&" KISW- "&IF(AND(A2<>"",C2=""),"ABS",IF(C2>=81,"A",IF(C2>=61,"B",.........IF(C2>=0,"F"))))))&" HIST- "&IF(AND(A2<>"",D2=""),"ABS",IF(D2>=81,"A",IF(D2>=61,"B",.........IF(D2>=0,"F")))))).......

Imefanya kazi mkuu.
Shukrani Sana.

 

[Njuka II]

Imefanya kazi mkuu.
Shukrani Sana.

Pamoja ndg

 

[isakwisa og]

Asante sana

 

[APEFACE]

Imefanya kazi mkuu.
Shukrani Sana.

Sema mwamba yuko nondo...

 

[Rusumo one]

Uzi bora 2023

 

[k2206]

Sema mwamba yuko nondo...

Sana aisee.