Advanced Functions in Excel 2007 Featured Image

Advanced Functions (এডভান্সড ফাংশন) | Excel 2007 Tutorial in Bangla – Part 15

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

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

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

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

Why use concatenate function in Excel 2007

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

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

How to use concatenate function in Excel 2007

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

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

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

Function arguments for concatenate function in Excel 2007

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

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

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

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

Data for using SUMIF command in Excel 2007

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

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

Select SUMIF function from math and trig in Excel 2007

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

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

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

Setup Function arguments for sumif function in Excel 2007

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

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

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

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

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

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

Data for using VLOOKUP function in Excel 2007

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

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

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

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

Select VLOOKUP function in Excel 2007

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

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

Setup VLOOKUP value in Function Arguments dialog box in Excel 2007

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

Show VLOOKUP value in Excel 2007

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

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

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

Demo Data for using PMT in Excel 2007

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

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

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

Select Goal Seek from What-If Analysis in Excel 2007

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

Set value in Goal Seek dialog box in Excel 2007

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

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

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

Advanced Functions নিয়ে আলোচনা আজকের মত এখানেই শেষ করছি। এতক্ষণ আমাদের সাথে ধৈর্য ধরে থাকার জন্য অনেক ধন্যবাদ। পরবর্তীতে ভিন্ন টিউটোরিয়াল নিয়ে উপস্থিত হবো, ইনশাআল্লাহ্। টিউনটি ইনফরমেটিক হলে বন্ধু ও পরিচিত মহলে শেয়ার করুন। ভুল পেলে অনুগ্রহ করে কমেন্টস করে জানাবেন।