Complete Guide to Microsoft Excel: Beginner theke Advanced Formulas & Functions
Keno Microsoft Excel Aajker Dine Shobcheye Dorkari Skill?
Ajker corporate world, private company, banking sector ba local business — jekhanei kaj korun na keno, Microsoft Excel chara kono kaj kora asombhob. Purba Medinipur ebong West Bengal-er bivinno job circular-e "Proficiency in Advanced Excel" ekta mandatory requirement.
Ei guide-e amra Excel-er basic theke advanced formulas emon vabe alochona korbo jate apni ekjon professional-er moto spreadsheet handle korte paren.
💡 Pragati Skill Academy Pro Tip:
Excel-e jekono formula shuru korte hoy = (equals) sign diye. Reference lock korar jonno F4 press kore Absolute Reference ($A$1) toiri kora hoy.
1. Essential Basic Functions (যেগুলো প্রত্যেকে জানা উচিত)
Shurutei ashi sei shob function-e ja dainandin hishebe kaje lage:
- SUM: Number jog korar jonno.
=SUM(A1:A10) - AVERAGE: Gor hisheb korar jonno.
=AVERAGE(B2:B50) - COUNT & COUNTA: Number cell count korte
=COUNT(range)ebong blank chara shob text/number count korte=COUNTA(range). - MIN & MAX: Shobcheye boro ba chhoto songkha ber korte.
=MAX(C2:C100)
2. Conditional Calculations: SUMIF, COUNTIF & SUMIFS
Kono specific condition-er upor base kore hisheb korte ei formulas gulo obosthyoi lagbe:
COUNTIF & COUNTIFS:
=COUNTIF(D2:D100, "Ramnagar") // Ramnagar location kotogulo ache ta count korbe =COUNTIFS(D2:D100, "Ramnagar", E2:E100, "Passed") // Duti condition eki sathe
SUMIFS (Multi-condition Sum):
=SUMIFS(Sales_Amount, Branch_Range, "Ramnagar", Month_Range, "January")
3. Logical Functions: IF, AND, OR & IFS
Decision-making analysis-e logical function khub dorkari. Udaharon hishebe student result ba discount calculation:
=IF(Marks >= 80, "A+", IF(Marks >= 60, "A", "Pass"))
Excel 2019/365-e IFS function diye aro shohoje lekha jay:
=IFS(Marks>=80, "Distinction", Marks>=60, "First Class", Marks>=40, "Pass", TRUE, "Fail")
4. Lookup Functions: VLOOKUP vs XLOOKUP
| Feature | VLOOKUP | XLOOKUP (Modern) |
|---|---|---|
| Direction | Sudhu Left theke Right | Left, Right, Up, Down jekono dike |
| Exact Match Default | FALSE likhte hoy | Default-i Exact Match |
| Column Insertion Error | Column index bhul hoye jay | Kono somossa hoy na (Safe) |
VLOOKUP Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) // Example: Roll No diye Student Name ber kora =VLOOKUP(G2, A2:E100, 2, FALSE)
XLOOKUP Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) // Example: =XLOOKUP(G2, A2:A100, B2:B100, "Student Not Found")
5. INDEX & MATCH: The Advanced Master Combo
Purono Excel version-e jekhane XLOOKUP nei kintu left-side data lookup korte hoy, sekhane INDEX ebong MATCH use kora hoy:
=INDEX(Return_Column, MATCH(Lookup_Value, Lookup_Column, 0))
Kothay Sikhben Advanced Excel Training?
Pragati Skill Academy (Ramnagar, Purba Medinipur)-e amra practical project, real business datasets, GST billing sheet ebong corporate reporting niye hathe-kolome Advanced Excel sikhai. Amader trainer Satyajit Giri 7+ bochhorer oviyogggota niye student-der train korchen.
Official Course Study Notes & Practice Materials
Pragati Skill Academy provides complete chapter-by-chapter curriculum notes, 195+ solved MCQs, formula cheat sheets, and practical lab assignments with recognized government certifications.