মূল কনটেন্টে যান
Prepo
অনুশীলন (১৫)
Office ApplicationsSpreadsheet (MS Excel)

MS Excel: সেল রেফারেন্স, ফর্মুলা, ফাংশন ও Error

পড়তে প্রায় ১৩ মিনিটসংস্করণ ১সর্বশেষ আপডেট: ২ নভেম্বর, ২০২৬১৫টি অনুশীলন প্রশ্ন

সংক্ষেপে মনে রাখুন

দ্রুত রিভিশনের জন্য মূল কথা

মূল কথা
  • Relative, Absolute ও Mixed রেফারেন্স চিনে F4 দিয়ে বদলাতে পারা
  • SUM, AVERAGE, COUNT, COUNTA, COUNTIF, SUMIF, IF, VLOOKUP-এর আর্গুমেন্ট ও ফল সঠিকভাবে বলতে পারা
  • Excel-এর Error বার্তা দেখে কারণ ধরতে পারা
সূত্র ও নিয়ম
Excel 2007+: 2^20 = 1,048,576 rows, 2^14 = 16,384 columns, last column XFD | Excel 2003: 2^16 = 65,536 rows, 2^8 = 256 columns, last column IV

ঘাত মনে রাখুন: নতুন Excel-এ সারি 2²⁰ ও কলাম 2¹⁴, পুরনোতে সারি 2¹⁶ ও কলাম 2⁸। শেষ কলাম নতুনে XFD, পুরনোতে IV।

যেখানে ভুল হয়

প্রশ্নে সংস্করণ উল্লেখ না থাকলে আধুনিক Excel ধরা হয়। 65,536 পুরনো Excel 2003-এর সারি, আর 16,384 নতুন Excel-এর কলাম, সারি নয়।

Excel-এর গঠন

  • ফাইলকে বলে Workbook (.xlsx), এর ভেতরে এক বা একাধিক Worksheet।
  • কলাম অক্ষরে (A, B, ... Z, AA ...), সারি সংখ্যায় (1, 2, 3 ...)। কলাম ও সারির মিলনস্থল Cell, যেমন B3।
  • Excel 2007 থেকে প্রতি শিটে 1,048,576 সারি ও 16,384 কলাম (শেষ কলাম XFD)। Excel 2003-এ ছিল 65,536 সারি ও 256 কলাম (শেষ কলাম IV)।
  • পাশাপাশি কয়েকটি সেলের সমষ্টি Range, যেমন A1:A10।
  • প্রতিটি ফর্মুলা শুরু হয় = চিহ্ন দিয়ে।

সেল রেফারেন্স ও F4

ধরনউদাহরণকপি করলে কী হয়
RelativeA1সারি ও কলাম দুটোই বদলায়
Absolute$A$1কিছুই বদলায় না
Mixed$A1কলাম স্থির, সারি বদলায়
MixedA$1সারি স্থির, কলাম বদলায়

ফর্মুলায় রেফারেন্সের ওপর কার্সর রেখে F4 চাপলে ক্রমে A1 → $A$1 → A$1 → $A1 → A1 হয়।

উদাহরণ: C2-তে আছে =B2*$F$1 (F1-এ ভ্যাটের হার)। C2 কপি করে C3-তে নিলে ফর্মুলা হবে =B3*$F$1। B2 বদলে B3 হয়েছে, কিন্তু $F$1 একই থেকেছে।

অপারেটর ও অগ্রাধিকার

ক্রম: বন্ধনী ( ) → ঋণাত্মক চিহ্ন - → শতাংশ % → ঘাত ^ → গুণ/ভাগ * / → যোগ/বিয়োগ + - → জোড়া লাগানো & → তুলনা = < > <= >= <>।

  • =2+3*4 → 14
  • =(2+3)*4 → 20
  • =-2^2 → 4, কারণ Excel আগে ঋণাত্মক চিহ্ন বসায়, তারপর ঘাত করে।
  • ="Dhaka"&" "&"City" → Dhaka City
  • "সমান নয়" লেখা হয় <> দিয়ে।

জরুরি ফাংশন

ফাংশনকাজউদাহরণ
SUMযোগফল=SUM(B2:B11)
AVERAGEগড়=AVERAGE(B2:B11)
MAX / MINসর্বোচ্চ / সর্বনিম্ন=MAX(B2:B11)
COUNTশুধু সংখ্যা থাকা সেল গোনে=COUNT(B2:B11)
COUNTAফাঁকা নয় এমন সব সেল গোনে (লেখা, সংখ্যা সব)=COUNTA(A2:A11)
COUNTBLANKফাঁকা সেল গোনে=COUNTBLANK(B2:B11)
COUNTIFশর্ত মানা সেল গোনে=COUNTIF(B2:B11,">=40")
SUMIFশর্ত মানা সারির যোগফল=SUMIF(A2:A11,"Dhaka",C2:C11)
IFশর্ত সত্য হলে এক মান, মিথ্যা হলে আরেক মান=IF(B2>=40,"Pass","Fail")
AND / ORএকাধিক শর্ত মেলানো=IF(AND(B2>=40,C2>=40),"Pass","Fail")
ROUNDনির্দিষ্ট দশমিক ঘরে রাউন্ড=ROUND(3.456,2) → 3.46
LENঅক্ষর সংখ্যা (স্পেসসহ)=LEN("Excel") → 5
TODAY / NOWআজকের তারিখ / তারিখ ও সময়=TODAY()
CONCATENATE / CONCATলেখা জোড়া লাগানো=CONCAT(A2," ",B2)

ফাংশনের আর্গুমেন্ট মনে রাখুন

  • IF(logical_test, value_if_true, value_if_false)
  • COUNTIF(range, criteria): আর্গুমেন্ট দুটি।
  • SUMIF(range, criteria, [sum_range]): sum_range না দিলে range-এর মানই যোগ হয়।
  • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value খোঁজা হয় table_array-এর প্রথম (বাম) কলামে।
  • col_index_num হলো table_array-এর ভেতরের কলাম নম্বর (1 মানে প্রথম কলাম), শিটের কলাম অক্ষর নয়।
  • range_lookup FALSE (বা 0) দিলে হুবহু মিল খোঁজে; TRUE দিলে বা খালি রাখলে কাছাকাছি মিল খোঁজে, তখন প্রথম কলাম ছোট থেকে বড় ক্রমে সাজানো থাকতে হয়।
  • HLOOKUP একই কাজ করে, তবে প্রথম সারিতে খোঁজে।

উদাহরণ ১: গ্রেড বের করা (nested IF)

B2-তে নম্বর। 80 বা বেশি হলে A+, 60 বা বেশি হলে A, 40 বা বেশি হলে Pass, নইলে Fail:

=IF(B2>=80,"A+",IF(B2>=60,"A",IF(B2>=40,"Pass","Fail")))

B2 = 65 হলে প্রথম শর্ত মিথ্যা, দ্বিতীয়টি সত্য, ফল A। বড় সীমা আগে না দিলে ভুল হয়: প্রথমে B2>=40 দিলে 95 পাওয়া প্রার্থীও "Pass" পাবে।

উদাহরণ ২: VLOOKUP দিয়ে বেতন খোঁজা

A2:C6 টেবিলে A কলামে কর্মচারী ID, B কলামে নাম, C কলামে বেতন। F1-এ ID লিখলে বেতন:

=VLOOKUP(F1,$A$2:$C$6,3,FALSE)

এখানে 3 মানে টেবিলের তৃতীয় কলাম (C)। ID না পেলে ফল আসবে #N/A।

Error বার্তার মানে

Errorকারণ
#DIV/0!শূন্য বা ফাঁকা সেল দিয়ে ভাগ
#N/Aখোঁজা মান পাওয়া যায়নি (VLOOKUP-এ বেশি দেখা যায়)
#NAME?ফাংশনের নাম ভুল বানান, বা লেখা উদ্ধৃতিচিহ্ন ছাড়া
#REF!রেফার করা সেল ডিলিট হয়ে গেছে
#VALUE!ভুল ধরনের ডেটা, যেমন লেখার সাথে সংখ্যা যোগ
#NUM!অসম্ভব সংখ্যা, যেমন =SQRT(-4)
#NULL!দুটি range-এর কোনো মিল নেই এমন intersection
#####Error নয়; কলাম সরু, মান ধরছে না (বা ঋণাত্মক তারিখ)

কাজের শর্টকাট

  • F2: সেল এডিট
  • Alt+=: AutoSum
  • Ctrl+;: আজকের তারিখ বসানো, Ctrl+Shift+;: বর্তমান সময়
  • Alt+Enter: একই সেলে নতুন লাইন
  • F11: আলাদা শিটে চার্ট, Alt+F1: একই শিটে চার্ট
  • Shift+F11: নতুন worksheet
  • Ctrl+Shift+L: Filter চালু/বন্ধ

পরীক্ষায় যা আসে

  • COUNT শুধু সংখ্যা গোনে, COUNTA ফাঁকা নয় এমন সব সেল গোনে।
  • Absolute রেফারেন্সের চিহ্ন $, বদলানোর কী F4।
  • VLOOKUP-এ exact match চাইলে শেষ আর্গুমেন্ট FALSE।
  • ফর্মুলা শুরু হয় = দিয়ে।

পরীক্ষার ফাঁদ

  • =COUNTIF(B2:B11,>=40) লিখলে ভুল; শর্ত উদ্ধৃতিচিহ্নে দিতে হয়: ">=40"।
  • VLOOKUP lookup কলামের বাম দিকে থাকা মান ফেরত দিতে পারে না। সেজন্য INDEX-MATCH বা নতুন ভার্সনে XLOOKUP লাগে।
  • ##### দেখলে ফর্মুলা ভুল ভাববেন না; কলাম চওড়া করলেই মান দেখা যায়।
  • =-2^2 এর ফল 4, -4 নয়।

পরের ধাপ

এই টপিকে ৫টি প্রশ্ন অনুশীলন করুন

পড়া মনে আছে কি না, ছোট একটি সেটে দেখে নিন। প্রতিটি উত্তরের সাথে ব্যাখ্যা থাকবে, আর ভুল প্রশ্নগুলো রিভিশনের তালিকায় যাবে