আসুন শিখি Advanced Microsoft Excel [পর্ব-১১]:: Goal Seek

টিউন   বার দেখা হয়েছে

আসসালামু আলাইকুম। আজ আমরা আলোচনা করবো Goal Seek নিয়ে। Goal শব্দের অর্থ হল লক্ষ্য, Seek শব্দের অর্থ হল খোঁজা বা অনুসন্ধান করা। তাহলে Goal Seek এর মানে হল লক্ষ্যে পোঁছানোর জন্য অনুসন্ধান করা। এর মাধ্যমে আমরা কোন একটি সেলের মান কত হলে আমরা আমাদের কাঙ্ক্ষিত লক্ষ্য অর্জন করতে পারবো বা পারতাম জানা যায়।

উদাহরণ ১

নিচের চিত্রের মত কিছু ডাটা টাইপ করি। এক ছাত্রের ৫ টি পরীক্ষার প্রাপ্ত নাম্বারগুলো লিখি। এখন ছাত্রটির সবগুলো পরিক্ষার গড় নির্ণয়ের জন্য E1 সেলে কার্সর রেখে ফর্মুলা বার এ  =AVERAGE(B1:B6) লিখে ইন্টার দেই। দেখলাম বর্তমানে গড় মার্ক ৭৮। আমাদের লক্ষ্য হল ছাত্রটি Geography পরীক্ষায় কত নাম্বার পেলে তার গড় মার্ক ৮০ হবে। এখন চিত্রের মত E1 সেল সিলেক্ট করে Data ট্যাব এর What-If-Analysis থেকে Goal Seek সিলেক্ট করি।

তাহলে নিচের মত ডায়ালগ বক্স আসবে। এখান থেকে To value এর বক্সে আমরা আমাদের লক্ষ্য ৮০ দিবো। এরপর By changing cell এর বক্সে কার্সর রেখে মাউস দিয়ে যেই সেলটির মান নির্ধারণ করে/ পরিবর্তন করে লক্ষ্য পুরন করতে হবে সেই সেলটি সিলেক্ট করুন। আমরা B6 সেলটি সিলেক্ট করলাম। এবার OK ক্লিক করুন।

এখন আমরা নিচের চিত্রে  মান পেয়ে গেছি। অর্থাৎ Geography তে ৯০ পেলে গড় মার্ক ৮০ হবে। আবার OK ক্লিক করুন।

Geography পরীক্ষায় যদি ছাত্রটির প্রাপ্ত নম্বর লিখা থাকতো তাহলে ও কোন সমস্যা হত না। আমরা একইভাবে বের করতাম Geography তে কত নম্বর পেলে গড় ৮০ হত।

উদাহরণ ২

নিচের চিত্রের মত ডাটা টাইপ করি এবং প্রয়োজনীয় ফর্মুলা দেই। আশা করি যারা বিগত পর্বগুলো দেখেছেন তাঁরা পারবেন। তারপর ও নতুনদের জন্য একটু বলে দেই। আমি D2 সেলে =B2*C2  , D6 সেলে =SUM(D2:D5) ফর্মুলা দিয়েছি। D2 থেকে D5 পর্যন্ত Fill Handle ব্যবহার করেছি। আমরা দেখতে পাচ্ছি মোট লাভ হয়েছে ৯৭৪০০ টাকা। আমরা চাচ্ছি ল্যাপটপ বিক্রি করে এই লাভ কে ১৫০০০০ টাকায় উন্নিত করতে। ল্যাপটপে লাভ ২০%। এখন আমাদের বের করতে হবে কত টাকার ল্যাপটপ বিক্রি করতে হবে।

D6 সেল সিলেক্ট করে  Data ট্যাব এর What-If-Analysis থেকে Goal Seek সিলেক্ট করি। এরপর উপরের চিত্রের মত To value এর বক্সে আমরা আমদের লক্ষ্য ১৫০০০০ দিবো। এরপর By changing cell এর বক্সে কার্সর রেখে মাউস দিয়ে  B5 সেলটি সিলেক্ট করে এবার OK ক্লিক করুন।
এখন আমরা নিচের চিত্রে ফলাফল পেয়ে গেছি। অর্থাৎ লক্ষ্য অর্জন করতে হলে ২,৬৩,০০০ টাকার ল্যাপটপ বিক্রি কতে হবে। ডায়ালগ বক্স এর OK ক্লিক করুন।

উদাহরণ ৩

গত পর্বে আমরা  PV,PMT,FV বের করা শিখেছি। Goal Seek ব্যবহার করে জায়।কিভাবে আরও সহজভাবে করা যায় তা আমরা এখন দেখবো। গত পর্ব যারা দেখেননি:
গত পর্বে আমরা দেখেছি মকবুল সাহেব ১৭% হার সুদে ৬০০০০০ টাকা ঋণ নিল যা তিনি ৪ বছরে শোধ করবেন। তার মাসিক কিস্তি হবে ১৭০৭১.৮০ টাকা। মকবুল সাহেব ভাবলেন তিনি প্রতি মাসে ২০০০০ টাকা করে পরিশোধ করতে পারবেন। তার ঋণ এর পরিমান কিছুটা বেশি হলে তার সুবিধা হয়। এখন তিনি কত টাকা ঋণ নিতে পারবেন। এটা আমরা Goal Seek এর মাধ্যমে খুব সহজে করতে পারি।

 B5 সেল সিলেক্ট করে Data ট্যাব এর What-If-Analysis থেকে Goal Seek সিলেক্ট করি। এরপর উপরের চিত্রের মত To value এর বক্সে আমরা আমদের লক্ষ্য ২০০০০ দিবো। এরপর By changing cell এর বক্সে কার্সর রেখে মাউস দিয়ে  B1 সেলটি সিলেক্ট করে এবার OK ক্লিক করুন। এরপর আরেকটি ডায়ালগ বক্স আসলে সেখানে আবার OK ক্লিক করুন।

আমরা ফলাফল পেয়ে গেছি , মকবুল সাহেব সর্বমোট ৭,০২,৯৩৮.৯৬ টাকা ঋণ পাবেন।

উদাহরণ ৪

এইবার একটু কঠিন উদাহরণ দেয়া যাক। ধরুন  5x³+6x²-8x+15=9423  এই সমীকরণটির x এর মান বের করতে বলা হল। দেখি আমরা Goal seek এর মাধ্যমে সমীকরণটির x এর মান বের করতে পারি কিনা।
সমিকরনটিকে Ax³+Bx²-Cx+D=800 এভাবে লিখলাম। অর্থাৎ A=5, B=6, C=8 এবং D=15
এখন নিচের মত ডাটা টাইপ করে F2 সেলে কার্সর রেখে ফর্মুলা বার এ =B2*A2^3+C2*A2^2-D2*A2+E2 লিখে ইন্টার দেই। F2 সেলে  আমরা ফলাফল পেলাম ১৫। অর্থাৎ x=0 ধরে এক্সেল হিসাব করেছে। এখন F2 সেলে x এর মান বিভিন্ন মান বসালে আমরা সমীকরণটির বিভিন্ন মান পাবো।

 যদি বলা হতো x=20 হলে 5x³+6x²-8x+15 এই সমীকরণটির মান বের কর তাহলে আমরা A2 সেলে ২০ লিখে ইন্টার দিলেই নিচের চিত্রের মত এক নিমিষেই মান পাবো।

 যেহেতু 5x³+6x²-8x+15=9423  এই সমীকরণটির x এর মান বের করতে হবে তাই নিচের প্রথম চিত্রের মত F2 সেল সিলেক্ট করে Data ট্যাব এর What-If-Analysis থেকে Goal Seek সিলেক্ট করি। এরপর  To value এর বক্সে আমরা আমদের লক্ষ্য ৯৪২৩ দিবো এবং By changing cell এর বক্সে কার্সর রেখে মাউস দিয়ে  A2 সেলটি সিলেক্ট করে এবার OK ক্লিক করুন। এরপর আরেকটি ডায়ালগ বক্স আসলে সেখানে আবার OK ক্লিক করুন।

আমরা 5x³+6x²-8x+15=9423  এই সমীকরণটির x এর মান 12 পেয়ে গেছি।
আজ এই পর্যন্তই। আশা করি আপনারা Goal Seek  কিভাবে কাজ করে ও কেন ব্যবহার করা হয় তা বুঝতে পেরেছেন। সবাই ভালো থাকবেন।