মাইক্রোসফট এক্সেলের এডভান্সড ফাংশনসমূহ - Graphic School

Blog

মাইক্রোসফট এক্সেলের এডভান্সড ফাংশনসমূহ

আমরা এর আগের ব্লগে মাইক্রোসফট এক্সেলের বেসিক ফাংশনসমূহ সম্পর্কে জেনেছি। এই ব্লগ থেকে মাইক্রোসফট এক্সেলের এডভান্সড ফাংশনসমূহ সম্পর্কে জেনে নিবো।

Advanced Functions ব্যবহার করে মাইক্রোসফট এক্সেলে অনেক জটিল কাজসমূহ সহজে সম্পাদন করা যায়।

এ অধ্যায়ে Advanced Functions (এডভান্সড ফাংশনসমূহ) এর কিছু কমান্ড নিয়ে বিস্তারিত আলোচনা করা হয়েছে। যেমন- Concatenate, CountIf, Lookup, What if Analysis ইত্যাদি।

Concatenate কমান্ডের ব্যবহার

এ কমান্ড দ্বারা একাধিক সেলের ভেল্যু একটি সেলে একত্রিত করা যায়। নিচের চিত্রটি লক্ষ্য করুন।

ধরুন, B7, B8 ও B9 সেলের ভেল্যুসমূহ একত্রিত করে F7 সেলে বসাতে চাই।

  • F7 সেলে সেলে পয়েন্টার স্থাপন করুন।
  • ট্যাববার হতে Formulas ক্লিক করুন।
  • Function Library গ্রুপ হতে Text এর ড্রপ-ডাউন ক্লিক করে CONCATENATE ক্লিক করুন।

এবারে নিচের মত Function Arguments এর ঘরগুলো পূরণ করুন।

  • Text1 = B7
  • Text2 = একবার স্পেস চাপুন
  • Text3 = C7
  • Text4 = একবার স্পেস চাপুন
  • Text5 = D7

নোট: শব্দগুলো মাঝে দূরত্ব দেয়ার জন্য একবার স্পেস ব্যবহার করুন।

  • Ok ক্লিক করুন।

লক্ষ্য করে দেখুন, B7, B8 ও B9 সেলের ভেল্যুসমূহ একত্রিত করে F7 সেলে প্রদর্শিত হচ্ছে।

SUMIF কমান্ডের ব্যবহার

এ কমান্ডের দ্বারা শর্তসাপেক্ষে যোগফল বের করা যায়। নিচের চিত্রটি লক্ষ্য করুন।

ওপরের চিত্রের তথ্য থেকে Designation যাদের Manager শুধুমাত্র তাদের কিংবা Designation যাদের Supervisor শুধুমাত্র তাদের Salary এর যোগফল বের করে G16 সেলে বের করতে চাই। এক্ষেত্রে আমরা SUMIF কমান্ড ব্যবহার করে এ সমস্যার সমাধান করতে পারি।

  • G16 সেলে সেল পয়েন্টার রাখুন।
  • ট্যাববার হতে Formulas ক্লিক করুন।
  • Function Library গ্রুপ হতে Math & Trig এর ড্রপ-ডাউন ক্লিক করে SUMIF ক্লিক করুন।

এবারে নিচের মত Range, Criteria এবং Sum_range নির্ধারণ করুন।

  • Range= এখানে রেঞ্জ হিসেবে C16:C30 ব্যবহার করা হয়েছে। কারণ আমরা Designation কলামের তথ্যের উপর ভিত্তি করে ফলাফল বের করতে চাই।
  • Criteria= এখানে Manager দেয়া হয়েছে কারণ। আমরা এক্ষেত্রে Manager পদবীদের যোগফল বের করতে চাই।
  • Sum_range= এখানে রেঞ্জ হিসেবে D16:D30 ব্যবহার করা হয়েছে। কারণ আমরা Salary কলামের ভেল্যুর যোগফল বের করতে চাই।

নিচের চিত্রটি লক্ষ্য করুন।

লক্ষ্য করুন, G16 সেলে শুধুমাত্র যাদের পদবী Manager তাদের বেতনসমূহের যোগফল প্রদর্শিত হচ্ছে।

একইভাবে Supervisor এর শর্তসাপেক্ষে যোগফল বের করার চেষ্টা করুন।

নোট: ট্যাববার ব্যবহার না করেও ফাংশনটি বুঝে উঠতে পারলে সরাসরি টাইপ করেও ফলাফল পাওয়া যাবে।

VLOOKUP কমান্ডের ব্যবহার

Formula ট্যাবের Function Library গ্রুপের Lookup & Reference এর ফাংশনগুলোর মধ্যে VLOOKUP একটি ফাংশন। যখন কোন টেবিল কিংবা রেঞ্জের ভেতর থেকে রো’র উপর ভিত্তি করে তথ্য খুঁজে বের করতে চান তখন এই কমান্ডের ব্যবহৃত হয়ে থাকে।

নিচের চিত্রটি লক্ষ্য করুন।

ধরুন, Name ফিল্ডের উপর ভিত্তি করে Designation ও Net Salary ফিল্ডের রো ভিত্তিক ফলাফল বের করতে চাই। অর্থাৎ কোন নির্দিষ্ট সেলে কোন নাম টাইপ বা নির্বাচন করলে স্বয়ংক্রিয়ভাবে টাইপকৃত নামের Designation ও Net Salary বের হবে।

বিষয়টি ভালভাবে বোঝার জন্য অনুশীলনের মাধ্যমে দেখা যাক।

ধরুন, S8 সেলে টেবিলে অবস্থিত কোন নাম টাইপ করলে ঐ নামের Designation ও Net Salary স্বয়ংক্রিয়ভাবে T8 ও U8 সেলে প্রদর্শিত হবে।

  • T8 সেলে সেলে পয়েন্টার রাখুন।
  • ট্যাববার হতে Formulas ট্যাব ক্লিক করুন।
  • Function Library গ্রুপ হতে Lookup & Reference এর ড্রপ-ডাউন ক্লিক করে VLOOKUP ক্লিক করুন।

এবারে প্রদর্শিত Function Arguments এর ডায়ালগ বক্সে নিচের মত তথ্য ইনপুট করুন।

  • Lookup_value= যে সেলের ওপর ভিত্তি করে ফলাফল প্রদর্শন করাতে চান সেই সেল এড্রেস লিখুন। এক্ষেত্রে S8 নির্ধারণ করা হয়েছে।
  • Table_array= যে টেবিল থেকে তথ্য বের করতে চাই সেই টেবিলের এড্রেস। এক্ষেত্রে I8:Q18 নির্ধারণ করা হয়েছে।
  • Col_index_num= যেহেতু আমরা Designation বের করতে চাই আর Designation কলামটি টেবিলের ২নং কলাম তাই এক্ষেত্রে 2 নির্ধারণ করা হয়েছে।
  • Range_lookup= এর ভেতর একটি লজিক্যাল ভেল্যু ইনপুট করতে হয়। হুবুহু মিলে যাওয়া তথ্য প্রদর্শন করার জন্য false ব্যবহার করা হয়েছে।

  • অবশেষে Ok বাটন ক্লিক করুন।
  • এবারে U8 সেলে ওপরের নিয়মে Net Salary প্রদর্শনের জন্য ফাংশন ব্যবহার করুন।অথবা, সরাসরি =VLOOKUP(S8,I8:Q18,9,FALSE) টাইপ করে এন্টার চাপুন।
  • লক্ষ্য করুন, T8 ও U8 সেলে #N/A প্রদর্শিত হচ্ছে।
  • এবারে S8 সেলে Name কলামের যে কোন নাম ইনপুট করুন এবং দেখুন ঐ নামের Designation ও Net Salary প্রদর্শিত হচ্ছে।

নোট: বিষয়টি বোঝার জন্য বারবার অনুশীলন করুন। একবার বুঝে উঠলেই বুঝবেন এ ফাংশনের কার্যকারীতার ব্যপকতা।

PMT ফাংশনের ব্যবহার

নির্দিষ্ট ইন্টারেস্ট ও পেমেন্ট ওপর ভিত্তি করে লোন এমাউন্ট বের করার জন্য Advanced Functions এর PMT ফাংশন ব্যবহার করা হয়ে থাকে। নিচের চিত্রটি লক্ষ্য করুন।

ধরুন, কোন ব্যাংক থেকে আপনি লোন নিতে আগ্রহ প্রকাশ করলেন। ব্যাংক কর্তৃপক্ষ তাদের লোন দেয়ার শর্তসমূহ বর্ণনা করতে গিয়ে বললেন, বাৎসরিক ১২% ইন্টারেস্ট এবং ২৪ কিস্তিতে তা শোধ করতে হবে।

এবারে আমরা দেখবো নির্দিষ্ট পরিমাণ কিস্তিতে কত টাকা লোন পাওয়া যেতে পারে। ধরুন, আপনি প্রতি কিস্তিতে ৫,০০০ টাকা পরিশোধ করতে পারবেন। তবে ব্যাংক কর্তৃপক্ষের শর্তানুসারে আপনি কত টাকা ঋণ পেতে পারেন।

  • ওপরের চিত্রের মত Interest এর ঘরে (D5) 12% এবং Term এর ঘরে (D6) 24 লিখুন।
  • অতপর D7 সেলে =PMT(D5/12,D6,D4) টাইপ করে এন্টার চাপুন।
  • এবারে D7 সেলে সেল পয়েন্টার রাখুন।
  • ট্যাববার হতে Data ট্যাব ক্লিক করুন।
  • Data Tools গ্রুপ হতে What-If Analysis এর ড্রপ-ডাউন ক্লিক করে Goal Seek অপশন নির্বাচন করুন।

  • প্রদর্শিত ডায়ালগ বক্সের স্বয়ংক্রিয়ভাবে Set cell এর ভেল্যু D7 থাকবে, কারণ D7 সেলে সিলেক্ট করে Goal Seek কমান্ড প্রয়োগ করা হয়েছে।
  • To value এর ঘরে 5000 টাইপ করুন, কারণ প্রতি কিস্তি 5000 টাকা।
  • অবশেষে By changing cell এর ঘরে মাউস পয়েন্টার রেখে D4 সেলে ক্লিক করুন অথবা, $D$4 টাইপ করুন।

  • Ok বাটন ক্লিক করুন। পুনরায় Ok বাটন ক্লিক করুন।

লক্ষ্য করুন, D4 সেলে 1,06,216.94 প্রদর্শিত হচ্ছে।

অর্থাৎ আপনি প্রতি মাসে 5000 টাকা কিস্তি প্রদান করার সম্মতি জ্ঞাপন করলে 1,06,216.94 টাকা লোন পেতে পারেন।

Facebook Comment