Monday 28 August 2017

MIZORAM SCHOOL RESULT PREPARATION USING EXCEL

Mizorama school tam zawk chuan Result siam nan Excel hi kan la hmang nasa hle a, Mizoram Board of School Education (MBSE) ngei pawhin an la hmang a. Mizoram University ngei pawhin an hmang nia sawi thawm a awm bawk a ni. Excel hi thil han chawhkual zung zung nan a awlsam phian a. Word nena combine thiam phei chuan result siam nan tangkai taka hman theih a ni. Tun hma lam khan Word hi marksheet siam nan hmangin Excel-ah result calculation zawng zawng chawkin Word-a marksheet nena combine-in result siamna kan lo siam ve tawh a. Chutianga siam nana kan hman ber chu Word Feature pakhat Mail Merge hi a ni. Mail merge-a data source atan khan excel-a kan result chawh kha kan va hmang a ni. Hetianga siam hi Zirlai tam lovah chuan a tha phian bawk a ni.Tun tumah hian Result siam mawi dan lam ni lovin a chawh dan kan sawi dawn a ni.

Excel hi database ni lovin spreadsheet a ni a, mahse Mizoram chu sawi loh hmun dang/ramdanga company lian ve ang reng tak tak te pawh hian database ang deuh thaw-in an hmang tlat a. Chutihrualin Database ang chuan Excel hi a hmantlak loh hle tih hi mi changkang te chuan an hmu thiam chho zel bawk a ni. Tun tumah hian Spreadsheet a nih anga a tangkai dan tlem sawi kan tum a. Example atan, kan school software la hmang pha ve lo te lo copy atan naupang result siam dan, internet leh hmun dang pawha in la hmuh loh tur 2010 chho vela kan siam tawh kan rawn hmang dawn a ni.
Result Sheet
A hmasa ber chuan thlalak-a kan hmuh ang result hi siam kan tum dawn a ni. Naupang result siam tur chuan an Roll No, Hming leh Subject tina an mark hmuh kan chhut luh a ngai phawt a. A hnuaia point hrang 7 te hi result kan siam dawn chuan ngaihtuah tel ngei ngei a ngai a ni.
  1. A1-ah Roll No, B1-ah Name chutiang zelin a column a tlem ang hian Excel-ah kan chhu lut ang.
  2. SS column atanga veilam zawng hi chu chhutluh ngai vek a ni a, Roll No, Hming, English, Mizo, Maths, Science, SS-a an mark hmuh theuh kha kan chhutluh vek a ngai a ni.
  3. A total, percentage, Result, Ranking, Letter leh Division te hi Excel kan chawh tir dawn a ni. Total leh Percentage tih loh a bak zawng hi Manual-in an chhulut tlangpui a, Excel-ah hian a chawhtheih loh ang te pawhin thenkhat chuan an sawi a, mahse keini chuan kan chawk vek dawn a ni.
  4. Result hi Pass leh Fail sawina ber a ni a. Mizoramah chuan Subject khata an fail chuan Failed kan ti mai a. Pass tur chuan Subject zawng zawngah Pass vek a ngai a. Pass Mark chu 33 a ni.
  5. Ranking hi in ang a awm theih a ngai a. Chutihrualin Fail an nih chuan Ranking chawh loh tur a ni a, Mahse Excel =rank() function danah chuan totalscore tam tam kha rank sang an ni mai a. Pass leh Fail pawh a thliar chuang lo va, mark total san dan indawtin rank a siam tawp mai a ni. Chu chu phuahchawp formula hmanga chinfel a ngai a ni ber.
  6. Letter mark hmu chin hi Subject tinah mark 80 emaw a aia tam hmu te hi an ni a, zirlai pakhat-in subject zawng zawngah letter a hmu thei bawk a ni.
  7. Division chawh hi en ang ngawta awl a ni lo va, dan tlangpui chuan student mark percentage atangin Division hi kan chawk thin a. Mahse percentage sang deuh pawh kha subject pakhat lekah a fel chuan Division-ah hian Fail tih a ngai tlat a. Chuvangin A fail leh fail loh atanga a division hi chhut phawt a ngai tihna a ni bawk.
  8. Sample Sheet thlalak kan tarlan ang hian hetah pawh hian kan chawk dawn a ni.
I lo chawk dawn ang hmiang:
i) Total: Total markscore chawk tur chuan a hnuaia Formula te hi kan hmang thei:
=SUM(C2+D2+E2+F2+G2) 
=SUM(C2:G2)
Double click Autocomplete
A eng zawk zawk pawh hi H2-ah kan chhulut anga, Enter hmeh tur. Hemi kan tihzawh tawh chuan H2 cell dinglam hnuaikil mouse-in kawk la (dinglama a lemah hian en rawh), double click la, zirlai dang zawng totalscore pawh a rawn chawk nghal vek tawh ang. 
 
ii) Percentage: Percentage chawh hi a har lo hle a. Formula kan hmang tlanglawn deuh ber chu Total Markscore ÷ Total Fullmark × 100 a ni a. Duh chuan Total Markscore ÷ Number of Subject hmang pawhin a chawh theih tho bawk a ni. A hnuaia Formula te hi Percentage chawh nan lo hmang ve teh le:
=H2/COUNT(C2:G2)
=H2/500*100
iii) Result: Hei pawh hi a zia man chuan awlsam phian. A chawh dan chi thum vel kan sawi ang. hei lo pawh hi a awm nual thei ang.
  • =IF(C2>=33,IF(D2>=33,IF(E2>=33,IF(F2>=33,IF(G2>=33,"Pass","Failed"),"Failed"),"Failed"),"Failed"),"Failed")
    Sawifiahna
    : Subject tina an mark hmuh kha 33 or aia tam a nih loh chuan an fail tihna a ni. C2, D2, E2, F2, G2 mi te kha IF condition hmangin 33 or aia tam a nih leh nih loh kan check a, 33 or aia tam a nih chuan "Pass" tih kan dah a, a nih loh chuan "Failed" tih kan print chhuak thung.
  • =IF(MIN(C2:G2)>=33,"Pass","Failed")
    Sawifiahna
    : He formula-ah hi chuan Subject tina a mark hmuh a mal te te-a va en lovin a cell range ang zawngin kan en a, C2 leh G2 inkara a mark hmuh chu 33 or aia tam a nih chuan "Pass" kan print a, 33 aia tlem a nih chuan "Failed" Kan print thung.
  • =IF(COUNTIF(C2:G2,">=33")=5,"Pass","Failed")
    Sawifiahna: He formula hi a dang leh daih a, cell range hmang tho siin 33 or aia tam mark an hmuhna subject zat kha kan chhiar tir phawt a, 33 or aia tam an hmuhna subject chu panga (5) a nih chuan "Pass" tih kan print a, 5 a tlin loh chuan "Failed" tih kan print mai a ni.
iv) Ranking: Ranking chawh hi a buaithlak em em mai a. A formulae tam tak a awm theih rualin sawifiah thui a ngaih thin avang leh hun heh loh zawk nan tun tumah hi chuan pakhat chiah kan rawn tarlang ang:
=IF(AND(J2="Pass"), SUMPRODUCT(--(J$2:J$6="Pass"),--(H2<H$2:H$6))+1,"")
Sawifiahna: Kan hmuh ang hian (iii)-ah khan Pass leh Failed chi thum laiin kan chawk chhuak thei tawh a. Kha kha hmang tangkaiin Ranking chawh nan kan hmang a ni. Tichuan J2 chu Pass a nih chuan A rank kan zawng dawn a ni. A tir lama kan la hmuh loh formula zingah hian $ (dollar symbol) a lo lang a. A awmzia sawifiah hmasa ila: Excel-ah hian Cell pakhat atanga Cell danga formula kan copy kual hian a column leh row number a danglam tel zel thin a. 
 
Entirnan H2-ah =SUM(C2:G2) tih kan type-a a hnuai chiah (H3)ah kan copy thlak chuan =SUM(C3:G3) a lo ni tawh a. Tin H2 mi kha I2-ah kan copy chuan =SUM(D3:H3) a lo ni tawh ang. Hei hi Excel kalphung awmsa a ni a, A saptawng chuan Relative Reference an ti. Hemi Opposite chiah hi Absolute Reference an ti a, hetiangah hi chuan formula chu hmun hranah copy mah la, a inthlak ve lovang. Absolute Reference siam nan hian $ symbol hi Column hming hmaah leh Row number hmaah dah thin a ni. Entirnan =SUM($C2:$G2) kan tih chuan he formula hi hmun hranah copy mah la C leh G hi chu a danglam ve dawn lo tihna. Chutiang bawkin =SUM(C$2:G$2) kan tih chuan row value hi a danglam dawn loh tihna a ni bawk ang.
 
$A$2 = Hmun danga copy pawhin a row value leh column value a danglam lo
A$2 = A row value a danglam ve lo. (Mixed Reference)
$A2 = A column value a danglam ve lo. (Mixed Reference)
 
Kan rank formula lamah lo lut leh tawh ila: Kan rank formula-ah hian a formula kan copy thlak pawha danglam ve lo absolute reference pali lai kan nei a. -- hi True or False value 1 or 0-a convert nana an hman lar deuh a ni a, a duh tan chuan * symbol hman pawh a theih tho. =SUMPRODUCT() function hi array multiply-a belhkhawmna formula changkang zet a ni. Rank chawh nan hian SUMPRODUCT()-ah hian array pahnih parameter atan kan hmang a. 
Chung te chu:
--(J$2:J$6="Pass") hian J2 leh J6 inkara value chu "Pass" a nih leh nih lo a check a, Pass a nih chuan 1 a rawn dahchhuak a,  
--(H2<H$2:H$6))+1 hi array pahnihna a ni a, H2 (total markscore) chu H$2 (2 hi a danglam ve lo) leh H$6 (6 hi a danglam ve lo) inkara mi aiin a te em tih a zawng a, a tet chuan 0 a len chuan 1 a ni a, +1 kan belh zui bawk a ni. Hemi ringawt hian engkim a la chawhfel theih loh avangin IF conditional hmangin kan tuam leh a ni. J2 chuan Pass a tluk loh chuan "" a tawpah kan dah leh a ni.
 
v) Letter in: Letter chawh hi en ang ai ngawtin a buaithlak ve thei bawk a. a hnuaia formula hi kan hman thin chu a ni:
=CONCATENATE(IF($C2>=80,C$1&",",),IF($D2>=80,D$1&",",),IF($E2>=80,E$1&",",),IF($F2>=80,F$1&",",),IF($G2>=80,G$1&",",))
Sawifiahna: Concatenate hi cell hrang hranga mi thuziak zawm khawmna a ni.
 
vi) Division: Division chawh nan hian Pass leh Fail criteria kha condition-a hmangin kan endik leh hmasa phawt a, tichuan a hnuaia formula hmang hian kan chawk a ni:
=IF(J2="Pass",IF(I2>=80,"Distinction",IF(I2>=60,"First",IF(I2>=45,"Second",IF(I2>=33,"Third","")))),"")
 
Ti zawng kha Excel hmanga Result siam dan kan sawifiah theih chin chu a ni ta. Zawhna leh hriatbelh duh nei chuan comment lam atangin thu leh hla nei ila. Excel-a Result siam hi a awlsam viau kan ti maithei a, mahse zirlai ngah tan a buaithlak thei a. Result data vawn that nan a rintlak loh bakah thil zawn vel a buaithlak thei hle bawk a ni. Tin excel hi kan sawi tawh thin angin a thiam bik khawih tur a ni a. A thiam bik tan lo chuan data pawimawh tihbo leh delete palh te a awlsam bik hliah hliah a ni. A that tawk lohna dang leh chu Excel khawihtu ber a lo dam loh hriltlan palh chuan thiam ve bawk dang tan pawh midang excel khawih hnu khawih hi chu inzawnkual vel hi a peihawm loh hle a ni.
 
HL Solution chuan School Database Management Software puitling leh tha Excel aia rintlak leh khawih nuam, computer thiam vak lo pawhin an hman theih tur MBSE kalphung mil thlapin kan siamchhuak a. Kan sawi tawh angin Excel hi Database a ni lo va, hun rei tak thlenga rin tur data vawn thatna tura siam ni lovin eng emaw hun bik atana data sawngbawlna leh data visualization atana siam a ni ber. Rang taka engkim tih theihna, Marksheet Calculation, Staff Management,Certificate, ID Card, SMS, Student Info, Biometric Attendance kengtel vek School management tha leh rintlak i mamawh chuan min rawn dawr ve ngei dawn nia.
(Hman remchan hunah Excel leh Word Combine-a Result Siam Dan te, MBSE Integrated Evaluation Scheme thar HSSLC leh HSLC hman tur Excel-a siam dan te kan la rawn ziak leh ang...)

No comments :

Post a Comment