DAY-1
DAY-1
DAY-2
DAY-2
DAY-3
DAY-3
Excel-এ Data-এর উপর Calculation
Topic: Performing Calculations on Data
আজ আমরা শিখব কীভাবে Excel-এ বিভিন্ন Formula ব্যবহার করে Data-এর উপর Calculation করা যায়।
Demo Data
নিচের Data-টি Excel-এ A1:F8 পর্যন্ত লিখুন।
| Student | Bengali | English | Math | Section | Attendance |
|---|---|---|---|---|---|
| Rahul | 80 | 75 | 90 | A | Present |
| Ananya | 65 | 70 | 60 | A | Present |
| Sourav | 90 | 88 | 95 | B | Absent |
| Priya | 78 | 82 | 85 | B | Present |
| Amit | 55 | 60 | 58 | A | Present |
| Neha | 92 | 95 | 98 | B | Present |
| Arjun | 70 | 68 | 72 | A | Absent |
Naming Groups of Data (Named Range)
ধরুন আপনি Bengali Marks (B2:B8)-এর জন্য একটি নাম দিতে চান।
ধাপ:
- B2:B8 সিলেক্ট করুন।
- উপরের Name Box (Formula Bar-এর বাম পাশে) ক্লিক করুন।
- লিখুন:
BengaliMarks
- Enter চাপুন।
এখন Formula লিখতে পারবেন:
=SUM(BengaliMarks)
এটি =SUM(B2:B8)-এর সমান কাজ করবে।
সুবিধা:
- Formula পড়তে সহজ হয়।
- বড় Data-তে কাজ করা সহজ হয়।
COUNTBLANK()
কতগুলো Cell খালি আছে তা গণনা করবে।
উদাহরণ:
=COUNTBLANK(F2:F8)
যদি সব Attendance লেখা থাকে,
Result হবে – 0
যদি একটি Cell খালি থাকে,
Result হবে –1
SUMIF()
একটি নির্দিষ্ট শর্ত অনুযায়ী যোগফল বের করবে।
Section A-এর Bengali Marks-এর যোগফল বের করুন।
=SUMIF(E2:E8,"A",B2:B8)
Calculation
Rahul = 80
Ananya = 65
Amit = 55
Arjun = 70
Total
= 270
AVERAGEIF()
শর্ত অনুযায়ী Average বের করবে।
Section B-এর Math-এর Average
=AVERAGEIF(E2:E8,"B",D2:D8)
Calculation
95
85
98
Average
= 92.67
COUNTIFS()
একাধিক শর্ত অনুযায়ী Count করবে।
Section A এবং Present Student কতজন?
=COUNTIFS(E2:E8,"A",F2:F8,"Present")
Result
Rahul
Ananya
Amit
= 3
IFERROR()
Formula Error হলে নিজের Message দেখাবে।
Example
=A2/B2
যদি B2 = 0 হয়,
Result
#DIV/0!
Error দূর করতে
=IFERROR(A2/B2,"Division Error")
Result
Division Error
সাধারণ Error এবং সমাধান
| Error | কারণ | সমাধান |
|---|---|---|
| #DIV/0! | ০ দিয়ে ভাগ | IFERROR ব্যবহার করুন বা ০ এড়িয়ে চলুন |
| #NAME? | Formula বা Function-এর নাম ভুল | বানান ঠিক করুন |
| #VALUE! | ভুল ধরনের Data | Number ব্যবহার করুন |
| #REF! | ভুল Cell Reference | সঠিক Cell নির্বাচন করুন |
| ###### | Column ছোট | Column Width বাড়ান |
Practice Exercise
নিচের Formula-গুলো নিজে লিখে ফলাফল বের করুন:
- সব English Marks-এর যোগফল বের করুন।
=SUM(C2:C8)
- Math-এর Average বের করুন।
=AVERAGE(D2:D8)
- মোট কতজন Student আছে?
=COUNTA(A2:A8)
- কতজন Absent?
=COUNTIF(F2:F8,"Absent")
- Section B-এর Bengali Marks-এর Total বের করুন।
=SUMIF(E2:E8,"B",B2:B8)
- Section B-তে কতজন Present Student আছে?
=COUNTIFS(E2:E8,"B",F2:F8,"Present")
DAY-4
Excel Day-4
DAY-5
Excel Tutorial-5
DAY-6
Excel Tutorial-6
##নিচে আপনার অনুরোধ অনুযায়ী ডেটা ক্লিনিং (Data cleaning), টেক্সট ফাংশন (Text function), কন্ডিশনাল ফরম্যাটিং (Conditional formatting) এবং ডেটা ভ্যালিডেশন (Data validation) এর ওপর প্রাকটিক্যাল ডেটাসেট সহ ৫টি প্রশ্ন এবং সবশেষে তার সমাধান দেওয়া হলো।##
প্রশ্নাবলী (Questions in Bengali)
প্রাকটিক্যাল ডেটাসেট (Sample Dataset)
নিচের টেবিলটি লক্ষ্য করুন। এই ডেটার ওপর ভিত্তি করেই প্রশ্নগুলো তৈরি করা হয়েছে:
| Row # | A (Employee ID) | B (Full Name) | C (Department) | D (Monthly Salary) | E (Join Date) |
|---|---|---|---|---|---|
| 1 | EMP001 | rahul sharma | HR | 45000 | 12-05-2023 |
| 2 | emp002 | PRIYA ROY | Finance | 55000 | 25/08/2022 |
| 3 | EMP003 | amit das | IT | 32000 | 14-02-2024 |
| 4 | EMP004 | Joya Sen | Operations | 60000 | 01-11-2021 |
1. প্রশ্ন (Data Cleaning & Text Function):
Row ১ এবং ৩-এর Full Name কলামে নামের আগে, পরে এবং মাঝে অনেক অতিরিক্ত স্পেস (extra spaces) আছে। এছাড়া নামগুলো সঠিকভাবে ছোট হাতের বা বড় হাতের অক্ষরে লেখা নেই (যেমন: rahul sharma)। এমন একটি ফর্মুলা তৈরি করুন যা নামের সব অতিরিক্ত স্পেস মুছে দেবে এবং প্রতিটি শব্দের প্রথম অক্ষর বড় হাতের (Proper Case) করবে।
2. প্রশ্ন (Text Function):
কোম্পানির নিয়ম অনুযায়ী প্রতিটি কর্মচারীর জন্য একটি কর্পোরেট ইমেইল আইডি তৈরি করতে হবে। ইমেইল আইডিটি হবে: [Employee ID-এর ছোট হাতের রূপ]@company.com। Row ২-এর জন্য (যেখানে Employee ID হলো ’emp002′ বা ‘EMP002’) এই ইমেইলটি তৈরি করার ফর্মুলাটি কী হবে?
৩. প্রশ্ন (Conditional Formatting – New Rule):
আপনি Monthly Salary (D কলাম) এর ওপর একটি কন্ডিশনাল ফরম্যাটিং রুল সেট করতে চান। যদি কোনো কর্মচারীর বেতন ৫০,০০০ টাকার বেশি হয়, তবে সেই সম্পূর্ণ সেলটি স্বয়ংক্রিয়ভাবে হালকা সবুজ (Light Green) রঙের হয়ে যাবে। “New Rule” অপশন ব্যবহার করে এটি করার জন্য আপনাকে কোন ফর্মুলা বা কন্ডিশন লিখতে হবে?
4. প্রশ্ন (Data Validation):
Department (C কলাম) এ ডেটা এন্ট্রি করার সময় যেন কোনো বানান ভুল না হয়, সেজন্য আপনি একটি ড্রপডাউন লিস্ট (Dropdown List) তৈরি করতে চান। তালিকায় শুধুমাত্র তিনটি ডিপার্টমেন্ট থাকবে: HR, Finance, IT। এটি ডেটা ভ্যালিডেশনের মাধ্যমে কীভাবে সেট করবেন?
5. প্রশ্ন (Data Validation with Custom Formula):
আপনি চান Monthly Salary (D কলাম) এ কেউ যেন ভুল করেও ২০,০০০ টাকার কম কোনো সংখ্যা টাইপ করতে না পারে। যদি কেউ ২০,০০০ এর কম টাকা লেখে, তবে এক্সেল একটি এরর মেসেজ দেখাবে। ডেটা ভ্যালিডেশনের “Custom” অপশন ব্যবহার করে এর ফর্মুলাটি কীভাবে লিখবেন?
SOLUTION
উত্তরমালা (Answers)
1. উত্তর (Data Cleaning & Text Function):
অতিরিক্ত স্পেস মোছার জন্য TRIM এবং নাম সঠিক ফর্ম্যাটে আনার জন্য PROPER ফাংশন একসাথে ব্যবহার করতে হবে। [1, 2]
- ফর্মুলা:
=PROPER(TRIM(B2)) - ব্যাখ্যা: এই ফর্মুলাটি B2 সেলের নামের চারপাশের সব অতিরিক্ত স্পেস কেটে দেবে এবং নামটিকে “Rahul Sharma” আকারে সাজিয়ে দেবে।
2. উত্তর (Text Function):
Employee ID-কে ছোট হাতের অক্ষরে রূপান্তর করতে LOWER ফাংশন এবং টেক্সট জোড়া লাগাতে & অপারেটর ব্যবহার করতে হবে।
- ফর্মুলা:
=LOWER(A3)&"@company.com" - ব্যাখ্যা: এটি ‘EMP002′-কে ছোট হাতের ’emp002′ করবে এবং শেষে ‘@company.com’ যুক্ত করে
emp002@company.comতৈরি করবে।
3. উত্তর (Conditional Formatting):
- ধাপ ১: প্রথমে D কলামের বেতনের সেলগুলো (D2:D5) সিলেক্ট করুন।
- ধাপ ২: Home ট্যাব থেকে Conditional Formatting > New Rule-এ যান।
- ধাপ ৩: “Use a formula to determine which cells to format” অপশনটি বেছে নিন।
- ধাপ ৪: ফর্মুলা বক্সে লিখুন:
=D2>50000 - ধাপ ৫: Format বোতামে ক্লিক করে Fill ট্যাব থেকে হালকা সবুজ রঙ বেছে নিয়ে OK করুন। [3, 4, 5]
৪. উত্তর (Data Validation – Dropdown):
- ধাপ ১: C কলামের সেলগুলো সিলেক্ট করুন যেখানে ড্রপডাউন চান।
- ধাপ ২: Data ট্যাবে গিয়ে Data Validation-এ ক্লিক করুন।
- ধাপ ৩: Allow ড্রপডাউন থেকে List সিলেক্ট করুন।
- ধাপ ৪: Source বক্সে কমা দিয়ে লিখুন:
HR, Finance, IT - ধাপ ৫: OK করুন। এখন ওই সেলগুলোতে ক্লিক করলে এই ৩টি অপশন দেখা যাবে। [6, 7]
৫. উত্তর (Data Validation – Custom):
- ধাপ ১: D কলামের বেতনের সেলগুলো সিলেক্ট করুন।
- ধাপ ২: Data > Data Validation-এ যান।
- ধাপ ৩: Allow ড্রপডাউন থেকে Decimal বা Whole number সিলেক্ট করতে পারেন। অথবা Custom সিলেক্ট করতে পারেন।
- যদি Whole Number বেছে নেন: Data অপশনে “greater than or equal to” সিলেক্ট করে Minimum বক্সে
20000লিখুন। - যদি Custom বেছে নেন ফর্মুলা হবে:
=D2>=20000 - ধাপ ৪: OK করুন। এখন ২০,০০০ এর কম লিখলেই এক্সেল বাধা দেবে। [8]