আসুন শিখি Adv Microsoft Excel

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০১] :: Microsoft Excel এর উইন্ডো পরিচিতি ও বেসিক ফর্মুলাসমূহ

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

Microsoft Excel কি?

Excel শব্দের আভিধানিক অর্থ শ্রেষ্ঠতর হওয়া। গুণ, কৃতিত্ব, ব্যবহারের সুবিধা প্রভৃতি বিবেচনায় Microsoft Corporation কর্তৃক তৈরী ও বাজারজাতকৃত এই প্রোগ্রামটি এক সাথে অনেক সমস্যা সামাধানে অন্যান্য অনেক প্রোগ্রামের থেকে শ্রেষ্ঠতর বলেই হয়তো একে Microsoft Excel বা সংক্ষেপে Ms-Excel বলা হয়। এই প্রোগ্রামটির মাধ্যমে গাণিতিক হিসাব নিকাশ, তথ্য ব্যবস্থাপনা , পরিসংখ্যান ভিত্তিক ডাটাবেইজ তৈরি, তথ্যকে আকর্ষণীয় করে উপস্থাপনায় নিখুঁত ভাবে চার্ট বা গ্রাফ তৈরী করা  ছাড়াও আরও অনেক জটিল কাজকে সহজে সম্পন্ন করা যায়।

উইন্ডো পরিচিতি

আসুন আমরা মাইক্রোসফট এক্সেল ২০০৭ ওপেন করলে যে উইন্ডো আসে তার সাথে পরিচিত হই।

মাইক্রোসফট এক্সেলের বেসিক ফর্মূলাসমূহ

যোগ করন:

ফর্মুলাঃ  =SUM(প্রথম সংখ্যার সেল এর এড্রেস : শেষ সংখ্যার সেলের এড্রেস)

ধরুন আপনি  35, 40, 50 তিনটি সংখ্যা যোগ করবেন যা যথাক্রমে A1, B1 ও C1 সেলে রয়েছে।  তাহলে যেই সেলে আমার যোগফলটি দরকার (ধরি D1) সেই সেলে কার্সর রেখে ফর্মুলা বার এ =SUM(A1:C1) লিখে ইন্টার চাপতে হবে।

যদি 35, 40, 50 সংখ্যা তিনটি যথাক্রমে B2, B3, B4  সেলে থাকে এবং  যোগফল যদি আমার F3 সেলে  দরকার তাহলে F3 সেলে কার্সর রেখে ফর্মুলা বার এ =SUM(B2:B4) লিখে ইন্টার চাপতে হবে।

আরেকটি পদ্ধতি হল যেই সংখ্যাগুলো যোগ করতে হবে তার পরের সেলে কার্সর রেখে Auto Sum এ ক্লিক করে ইন্টার চাপতে হবে। ছবিতে আমরা B1থেকে B5 পর্যন্ত যোগ করার জন্য B6 সেলে কার্সর রেখে  Auto Sum এ ক্লিক করেছি। তারপর ইন্টার চাপতে হবে।

বিয়োগ করন:

ফর্মুলাঃ   =প্রথম সংখ্যার সেল এর এড্রেস - দ্বিতীয় সংখ্যার সেলের এড্রেস

যদি আমরা D3 সেলে থাকা 870 হতে E3 সেলে থাকা 820 বিয়োগ করতে চাই, তাহলে যেই সেলে আমার ফলাফল দরকার (ধরিF3 সেল) সেই সেলে কার্সর রেখে ফর্মুলা বার এ =D3-E3 লিখে ইন্টার চাপতে হবে।

গুন করন:

ফর্মুলাঃ  =প্রথম সংখ্যার সেল এর এড্রেস * দ্বিতীয় সংখ্যার সেলের এড্রেস

যদি আমরা D3 সেলে থাকা 80 এবং E3 সেলে থাকা 6০ গুন করতে চাই, তাহলে তাহলে যেই সেলে (ধরি F3 সেল) আমার ফলাফল দরকার সেই সেলে কার্সর রেখে ফর্মুলা বার এ =D3*E3লিখে ইন্টার চাপতে হবে। একটা জিনিশ খেয়াল রাখবেন সবসময় যে পরের সেলটি হবে তা কিন্তু নয় যে কোন সেল এই আমাদের ফলাফল প্রয়োজন হতে পারে।

ভাগ করন:

ফর্মুলাঃ  =প্রথম সংখ্যার সেল এর এড্রেস / দ্বিতীয় সংখ্যার সেলের এড্রেস

যদি আমরা A1 সেলে থাকা 840 এবং B1 সেলে থাকা 70 দ্বারা ভাগ করতে চাই, তাহলে যেই সেলে আমার ফলাফল দরকার সেই  সেলে কার্সর রেখে ফর্মুলা বার এ =A1/B1 লিখে ইন্টার চাপতে হবে।

গড় করন:

ফর্মুলাঃ  =AVERAGE(প্রথম সংখ্যার সেল এর এড্রেস : দ্বিতীয় সংখ্যার সেলের এড্রেস)

যদি B3, C3, D3, E3, F3 সেলে থাকা যথাক্রমে 30, 40, 55, 120, 65 সংখ্যাসমূহের গড় মান নির্ণয় করতে চাই তাহলে যেই সেলে আমার ফলাফল দরকার (ধরিG3 সেল) সেই সেলে কার্সর রেখে ফর্মুলা বার এ =AVERAGE(B3:F3)  লিখে ইন্টার চাপতে হবে।

আবার নিচের চিত্রের মত যেইসব সেলের সংখ্যাসমূহের গড় মান নির্ণয় করতে হবে তার পাশের সেলে কার্সর রেখে Average এ ক্লিক করে ইন্টার চাপার মাধ্যমে ও গড় মান নির্ণয় করা যায়।

পারসেন্টেজ(%) নির্ণয় করন:

ফর্মুলাঃ  =(যে সংখ্যার পার্সেন্টিজ বের করবেন ঐ সংখ্যা * যত পার্সেন্ট বের করবেন% )

যদি B2 সেলে থাকা 2500 এর 40%  বের করতে চাই তাহলে যেই সেলে আমার ফলাফল দরকার (ধরি C2 সেল) সেই সেলে কার্সর রেখে ফর্মুলা বার এ =B2*40%  লিখে ইন্টার চাপতে হবে।

আজ এই পর্যন্তই। আশা করি খুব দ্রুতই পরবর্তী পর্ব নিয়ে হাজির হব। খোদা হাফেজ।

----------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০২] :: বেসিক ফর্মুলাসমূহ

আশা করি সবাই ভালো আছেন এবং প্রথম পর্বটি ভালোভাবেই বুঝতে পেরেছেন। আসলে না বুঝার কিছু নেই কিছু বেসিক ফর্মুলা নিয়ে আলাপ করেছি। আজ অবশিষ্ট কিছু বেসিক ফর্মুলা নিয়ে আলাপ করবো।

সংখ্যা গণনা করন :

ফর্মুলাঃ  =COUNT(প্রথম সংখ্যার সেল এর এড্রেস : দ্বিতীয় সংখ্যার সেলের এড্রেস)
ছবিতে আমরা দেখতে পাচ্ছি আমাদের E কলামে E4 থেকে E16 পর্যন্ত কিছু সংখ্যা আছে। এখানে মোট কতটি সংখ্যা আছে তা যদি আমরা G9 সেলে দেখতে চাই  তাহলে যেই সেলে আমার ফলাফল দরকার (ধরলাম G9 সেল) সেই সেলে কার্সর রেখে ফর্মুলা বার এ =COUNT(E3:E16)  লিখে ইন্টার চাপতে হবে।

অথবা অন্যভাবে ও বের করা যায়। নিচের চিত্রের মত সেল সমূহ সিলেক্ট করে Count Number এ ক্লিক করুন।

সর্বোচ্চ সংখ্যা নির্ণয় করন:

ফর্মুলাঃ  =MAX(প্রথম সংখ্যার সেল এর এড্রেস : দ্বিতীয় সংখ্যার সেলের এড্রেস)

ছবিতে আমরা দেখতে পাচ্ছি আমাদের C রো তে  C2 থেকে M2 পর্যন্ত কিছু সংখ্যা আছে। এদের মধ্যে সর্বোচ্চ সংখ্যা কোনটা তা জানতে হলে   তাহলে যেই সেলে আমার ফলাফল দরকার (ধরলাম H4 সেল) সেই সেলে কার্সর রেখে ফর্মুলা বার এ =MAX(C2:M2)  লিখে ইন্টার চাপতে হবে।
অথবা অন্যভাবে ও বের করা যায়। নিচের চিত্রের মত সেল সমূহ সিলেক্ট করে Max এ ক্লিক করুন।

সর্বনিম্ন সংখ্যা নির্ণয় করন:

ফর্মুলাঃ  =MIN(প্রথম সংখ্যার সেল এর এড্রেস : দ্বিতীয় সংখ্যার সেলের এড্রেস)
মনে করি C রো তে  C2 থেকে M2 পর্যন্ত কিছু সংখ্যা আছে। এদের মধ্যে সর্বনিম্ন সংখ্যা কোনটা তা জানতে হলে   যেই সেলে আমার ফলাফল দরকার সেই সেলে কার্সর রেখে ফর্মুলা বার এ =MIN(C2:M2)  লিখে ইন্টার চাপতে হবে।
অথবা সেল সমূহ সিলেক্ট করে Min এ ক্লিক করুন।

বড় থেকে ছোট নির্ণয় করন:

নিচের ছবিতে আমরা দেখতে পাচ্ছি C কলামে কয়েকজন স্টুডেন্ট এর নাম এবং D কলামে তাদের প্রাপ্ত নম্বর দেওয়া আছে। B কলামে তাদের বর্তমান রোল নম্বর ও দেওয়া আছে। রেজাল্ট অনুযায়ী তাদের নতুন রোলনম্বর নির্ধারণ করতে হলে আমরা প্রথমে D কলামের সেলগুলো সিলেক্ট করবো, তারপর ছবির মত Sort & Filter থেকে Sort Largest to Smallest এ ক্লিক করতে হবে।

এখন নিচের ছবির মত একটা ডায়ালগ বক্স আসবে (এটা দিয়ে বুজাচ্ছে সংখ্যাগুলো পরিবর্তন হওয়ার পাশাপাশি পাশের কলামে থাকা নামসমুহ ও সাথেসাথে পরিবর্তন হবে কিনা) । এখান থেকে Continue with the current selection সিলেক্ট করে Sort ক্লিক করতে হবে। এখন আমরা সবচেয়ে বেশি নম্বর থেকে ক্রমান্বয়ে সবচেয়ে কম নম্বর কে পেয়েছে তা দেখবো। পাশাপাশি নতুন কার কত রোল নম্বর হল তা ও জানতে পারব।

ছোট থেকে বড় নির্ণয় করন:

যেই সংখ্যাগুলোর বড় থেকে ছোট নির্ণয় করতে হবে তা সিলেক্ট করে Sort & Filter থেকে Sort Smallest to Largest এ ক্লিক করতে হবে। তারপর ডায়ালগ বক্স আসলে সেখান থেকে Continue with the current selection সিলেক্ট করে Sort ক্লিক করতে হবে।

বর্গমূল করন:

ফর্মুলাঃ  =SQRT(সেল এর এড্রেস)
ধরি B2 সেলে 144 সংখ্যাটি লিখা আছে। E2 সেলে আমদের এর বর্গমূল বের করতে হবে। তাহলে E2 সেলে কার্সর রেখে ফর্মুলা বার এ =SQRT(B2) লিখে ইন্টার চাপতে হবে। তাহলেই আমরা E2সেলে 12 সংখ্যাটি দেখতে পাবো।

আজ এই পর্যন্তই। সবাই ভালো থাকবেন।

----------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৩] :: Fill অপশন এর জটিল কিছু ব্যবহার

আশা করি সবাই ভালো আছেন ও আমার প্রথম দুইটি পর্ব ভালোভাবে বুঝতে পেরেছেন। গত দুই পর্বে মাইক্রোসফট এক্সেল এর বেসিক ফর্মুলাসমুহ নিয়ে আলোকপাত করেছিলাম। আজ আমরা Fill অপশন নিয়ে আলোচনা করবো।

Fill অপশন

Fill অপশন এর মাধ্যমে আমরা একটা সেল এ একটি সংখ্যা বা টেক্সট লিখে তা  যতগুলো সেলে প্রয়োজন কপি করতে পারি এক নিমিষেই। এর মাধ্যমে যে কোন সিরিজ, সাতদিনের নাম, মাসের নাম, তারিখ দ্বারা এক নিমিষেই কষ্ট না করে সেলগুলো পূরণ করা যায়।

আমরা নিচের ছবির মত দুইটি সেলে যথাক্রমে 1 ও 2 লিখি। আমি একই রো তে লিখলাম। আপনি ইচ্ছা করলে একই কলামে ও লিখতে পারেন। এখন লিখা দুইটি সেল সহ একই রো বা একই কলামে আরও কয়েকটি সেল সিলেক্ট করুন। তারপর চিত্রের মত Fill অপশন এ ক্লিক করে Series সিলেক্ট করুন।

Series এ ক্লিক করলে নিচের মত ডায়ালগ বক্স আসবে। এখন OK তে ক্লিক করুন।

তাহলে আমরা নিচের ছবির মত সিরিজ টি সেলগুলো তে Fill হতে দেখব। আপনি ইচ্ছা করলে পুরো রো বা কলাম সিলেক্ট করে। stop value তে একটা মান দিতে পারেন। ধরুন আপনি চান 1 থেকে 30 পর্যন্ত। তাহলে stop value এর ঘরে 30 লিখে OK তে ক্লিক করুন।

অন্য পদ্ধতিঃ  এখন আমরা আসলে সবাই যেভাবে Fill অপশন এর কাজ করে অর্থাৎ সহজ পদ্ধতি টা দেখবো। যেকোনো কলামে বা রো তে 1 ও 2 লিখি। এখন 1 ও 2 লিখা সেল দুইটি সিলেক্ট করে ছবির মত নিচের ডানপাশের কোনায় + চিহ্ন আসলে মাউসের রাইট বাটন ক্লিক করা অবস্থায় নিচের কলাম বরাবর ড্রাগ করে নিচে নামাই আমাদের যতটা দরকার।

তাহলেই নিচের ছবির মত জাদু দেখা যাবে। যদি আমরা একই রো তে লিখতাম তাহলে নিচের ডানপাশের কোনায় + চিহ্ন আসলে মাউসের রাইট বাটন ক্লিক করা অবস্থায় রো বরাবর অর্থাৎ ডানদিকে ড্রাগ করা লাগতো।

এভাবে 5, 10 লিখলে এই ক্রমিক এ পরের নাম্বার গুলো আসবে। January, February লিখলে পরের মাসগুলোর নাম আসবে।

Saturday, Sunday লিখলে পরের দিনগুলোর নাম আসবে। 01/09/2014, 02/09/2014 লিখলে পরের তারিখগুলো আসবে (যদি তারিখ সিরিজ হিসাবে নিচের চিত্রের মত না আসে তাহলে আপনার পিসি এর ডেট ফরম্যাট কন্ট্রোল প্যানেল থেকে  অথবা Format Cells-Customs থেকে ঠিক করে নিতে হবে)।

আপনি ইচ্ছা করলে নিজের পছন্দমত ও অনেক Series আগে থেকে তৈরি করে রাখতে পারেন, তাহলে প্রয়োজনের সময় এভাবে Fill অপশন ব্যবহার করে একটানেই সবকিছু করতে পারবেন। আসুন দেখি কিভাবে Fill  অপশন ব্যাবহারএ ইচ্ছামতো series যুক্ত করবো। আমি বাংলা সাতদিনের নাম দেখালাম। বাংলা সাতদিনের নাম বা বারমাসের নাম এইগুলো এক্সেল এ আগে থেকে সিরিজ করা নাই। আমাদের প্রয়োজন হলে আমরা করে নিতে পারি বা এখানে অন্য যে কোন কিছু করতে পারি যা আমাদের কাজের ক্ষেত্রে বেশি বেশি লিখতে হয় এবং বড় আকারের। এভাবে একবার সিরিজ করে রাখলে আপনি ওই পিসি বা ল্যাপটপ থেকে পরে যে কোন সময় এই সুবিধা নিতে পারবেন।

প্রথমে উপরের ছবির মত বাংলা সাতদিনের নাম লিখি এবং তা সিলেক্ট করি। অফিস বাটন এ ক্লিক করে যে বক্স আসে সেখান থেকে চিত্রের মত Excel Option এ ক্লিক করুন। তারপর নিচের চিত্রের মত Edit Custom lists এ ক্লিক করি।

এখন প্রথমে Import ও পরে OK তে ক্লিক করুন। এরপর যদি আরেকটি ডায়ালগ বক্স আসে তাতে ও OK ক্লিক করুন।

এখন এই সিরিজ টি আপনার পিসি তে কপি হয়ে গেছে।  যে কোন সময় আপনি এই সিরিজ টি ব্যবহার  করতে পারবেন। উপরের নিয়মে প্রথম সেল এ শনিবার এবং দ্বিতীয় সেল এ রবিবার লিখে সেল দুইটি সিলেক্ট করে নিচের ডানপাশের কোনায় + চিহ্ন আসলে মাউসের রাইট বাটন ক্লিক করা অবস্থায় ড্রাগ করতে হবে। একটা জিনিশ মনে রাখবেন সিরিজ লিখতে হলে কমপক্ষে অবশ্যই দুইটি সেল এ লিখতে হবে। একটি সেল এ লিখলে ওইটাই বার বার কপি হবে, সিরিজ আসবে না।

নিচে আমরা Fill অপশন এর আরেকটি উদাহরণ দেখবো। A,B,C কলামে আমাদের কিছু ডাটা আছে। D1 কলামে আমরা =(A1-B1)*C1 এই ফর্মুলা দিলাম এবং তার ফলাফল পেলাম।

এখন আমরা যদি চাই একই ফর্মুলা নিচের কলামগুলোর জন্য ও প্রযোজ্য হবে তাহলে D1 সেলে কার্সর রেখে ডানপাশের কোনায় + চিহ্ন আসলে মাউসের রাইট বাটন ক্লিক করে নিচের দিকে  ড্রাগ করলে ওই একই ফর্মুলা D2,D3, D4 সেলগুলোর জন্য প্রযোজ্য হবে এবং ফলাফল দেখাবে।

অর্থাৎ কষ্ট করে আমাদের প্রতি সেল এ ফর্মুলা লিখতে হল না। 🙄

আজ এই পর্যন্তই। সবাই ভালো থাকবেন। আমার জন্য দোয়া করবেন।

যারা আমার আগের দুইটি পর্ব দেখেন নি নিচের লিঙ্ক থেকে দেখে নিতে পারেন

-------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৪] :: Freeze অপশন ও Chart তৈরি

আশা করি সবাই ভালো আছেন। গত পর্বটি বেশ ভালোভাবে বুঝতে পেরেছেন ও চর্চা করেছেন। গত পর্বটি প্রকাশিত হওয়ার পর আপনাদের ব্যাপক সাড়া পেয়েছি।  আপনাদের উপকারে এসেছে, নতুন কিছু জানতে পেরেছেন জেনে ভালো লেগেছে। আজ আমরা Freeze  ও Chart অপশন  নিয়ে আলোচনা করবো।

Freeze অপশন

যখন বেশ কয়েকটা কলাম ও অনেকগুলো রোতে ডাটা থাকে তখন  Scroll করলে প্রথম রো টি দেখা না যাওয়ায় কোন ডাটাটি কিসের তা আমাদের বুঝতে অসুবিধা হয়। এই ক্ষেত্রে যদি Scroll করলে ও প্রথম রো টি দেখা যায় তাহলে বেশ ভালোভাবে বুঝা যেত।

নিচের চিত্রে কিছু ডাটা আছে লক্ষ্য করুন।

এখন আমরা Scroll করলে বা নিচের ডাটাগুলো দেখতে চাইলে এইরকম দেখাবে। প্রথম রো টি দেখা না যাওয়ায় ভালোভাবে বুঝা যাচ্ছে না।

এই অসুবিধা দূর করার জন্য নিচের চিত্রের মত View ট্যাব এ গিয়ে Freeze Panesথেকে Freeze Top Row সিলেক্ট করি।

এখন Scroll করলেও প্রথম রো টি দেখা যাবে

এভাবে আপনি চাইলে Freeze First Column সিলেক্ট করে প্রথম কলামটি কে Freeze করতে পারেন।আপনার যদি প্রথম কয়েকটি রো Freeze করার প্রয়োজন হয় তাহলে যেই কয়টা রো আপনার Freeze করা প্রয়োজন তার পরের রো এর প্রথম সেলে  কার্সর রেখে নিচের চিত্রের মত Freeze Panes সিলেক্ট করুন। যদি দ্বিতীয় সেলে কার্সর রাখেন তাহলে রোগুলোর পাশাপাশি প্রথম কলামটি ও Freeze হয়ে যাবে।

পূর্বের অবস্থায় ফিরে যাবার জন্য অর্থাৎ Unfreeze করার জন্য নিচের চিত্রের মত Unfreeze Panes এ ক্লিক করুন।

Chart তৈরি

Excel এ খুব অল্প সময়েই অনেক দৃষ্টিনন্দন চার্ট তৈরি করা সম্ভব। এর মাধ্যমে যে কোন রিপোর্ট সম্পর্কে সংক্ষেপে ধারনা পাওয়া যায় ও তুলনামুলক বিশ্লেষন করা যায়। চার্ট তৈরি করার জন্য আমাদের শুদু ডাটা দিতে হবে বাকি দায়িত্ব এক্সেল এর উপর ছেড়ে দিন। আসুন তাহলে কাজ শুরু করি। আসুন আমরা নিচের চিত্রের মত SSC ২০১৪ পরিক্ষায় কতজন কোন গ্রেড পেয়েছে তার একটা ডাটা লিখি।

এখন সেলগুলো সিলেক্ট করে Insert ট্যাব হতে Chart গ্রুপ এর Pie টাইপ এ ক্লিক করে যে কোন একটি ফরম্যাট সিলেক্ট করি। আমি এখানে 2-D এর প্রথম ফরম্যাট টাই সিলেক্ট করলাম।

সাথে সাথে নিচের ছবির মতো চার্ট তৈরি হয়ে গেল।

আপনি Chart Style ও  Chart layout পরিবর্তন করে দেখতে পারেন। ইচ্ছে করলে  Chart Type ও পরিবর্তন করতে পারেন। যেমন আমি নিচের চিত্রে Chart Layout পরিবর্তন করে দেখালাম। এর মাধ্যমে কত পারসেন্ট কোন গ্রেড পেল তা জানা গেল।

আরেকটি উদাহরণ:

আমরা গত ৫ বছরে SSC ও HSC পরীক্ষায় কতজন করে A+ পেয়েছে তার একটা ডাটা লিখি। এখন পূর্বের নেয় সেলগুলো সিলেক্ট করে Insert ট্যাব এর Chart গ্রুপ হতে Column থেকে একটা 2-D টাইপ সিলেক্ট করলাম। সাথে সাথেই আমাদের কাঙ্ক্ষিত চার্ট পেয়ে গেলাম।

আপনারা বিভিন্ন ধরনের চার্ট টাইপ, চার্ট লে-আউট, চার্ট স্টাইল পরিবর্তন করে দেখবেন। যত চর্চা করবেন তত শিখবেন তাহলেই সুন্দর সুন্দর বাহারি চার্ট তৈরি করতে পারবেন। আজ এই পর্যন্তই। সবাই ভালো থাকবেন।

---------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৫] :: IF Function এর ব্যবহার

আসসালামু আলাইকুম। আশা করি সবাই ভালো আছেন। আজ আমরা IF Function নিয়ে আলোচনা করবো। IF Function ব্যবহার করে কিভাবে বিভিন্ন Condition অনুযায়ী ফর্মুলা তৈরি করতে হয় তা শিখব।

IF Function

IF অর্থ হল যদি। IF একটি Logical ফাংশন। এর মাধ্যমে কোন একটি বা একাধিক শর্ত সাপেক্ষে আমার বর্তমান ডাটা এর অবস্থান কি হবে তা জানা যায়। এখন কঠিন মনে হলে ও আস্তে আস্তে আমাদের কাছে পরিস্কার হয়ে যাবে।
IF Function এর Structure হল
=IF(logical_test, value_if_true, value_if_false)
বাংলায় করলে এমন দাড়ায়     =যদি (শর্ত, শর্ত সত্য হলে কি হবে, শর্ত মিথ্যা হলে কি হবে)
আসুন আমরা নিচের কয়েকটি উদাহরণ এর মাধ্যমে IF Function ব্যবহার করে বিভিন্ন শর্ত অনুযায়ী ফর্মুলা বানানোর চেষ্টা করি।

উদাহরণ ১

নিচে কয়েকজন ছাত্রের একটি বিষয়ের মার্ক দেয়া আছে। শর্ত হল যারা ৩৩ এর কম পেয়েছে তাঁরা ফেল বাকিরা পাশ করেছে।

আমরা দেখতে পাচ্ছি B কলামে মার্ক দেওয়া আছে। আমরা C কলামে রেজাল্ট বের করবো। আমরা প্রথমে B2 সেল এর রেজাল্ট দেখতে চাই। শর্ত হল ৩৩ এর কম হলে ফেল অন্যথায় পাশ। এখন C2 কলামে কার্সর রেখে ফর্মুলা বার এ লিখি =IF(B2>33, "Fail", "Pass") ।  এরপর ইন্টার দেই। এতে আমরা প্রথম ছাত্রের রেজাল্ট দেখতে পেলাম।

ফর্মুলাটি আমরা এভাবে ও লিখতে পারি =IF(B2<=33, "Pass", "Fail") । অর্থাৎ ৩৩ এর সমান বা বেশি হলে পাশ অন্যথায় ফেল। এখন Fill অপশন ব্যবহার করে একনিমিষেই বাকি ছাত্রদের রেজাল্ট দেখতে পারি।

যারা Fill অপশন এর ব্যবহার জানেন না তাঁরা আমার এই পোস্ট টি দেখতে পারেন আসুন শিখি Advanced Microsoft Excel [পর্ব-০৩]

উদাহরণ ২

শর্তঃ যাদের মাসিক আয় ২০০০০ টাকা বা এর বেশি তাদের আয়কর ৩%, অন্যথায় ২% (২০০০০ টাকা এর কম হলে ২%)।
নিচের ছবিতে কয়েকজনের মাসিক আয় দেয়া আছে। প্রথম জনের আয়কর বের করতে হলে C2 সেলে কার্সর রেখে ফর্মুলা বার এ   =IF(B2<=20000,B2*3%,B2*2%) লিখি।

এখন ইন্টার দিলে প্রথম জনের আয়কর কত হল তা দেখতে পাবো। এরপর Fill অপশন ব্যবহার করে আমরা বাকিদের আয়কর বের করতে পারবো।

উদাহরণ ৩

১ম শর্ত : মার্ক ৭৫০ বা এর বেশি হলে STAR
২য় শর্ত: মার্ক ৬০০-৭৪৯ এর মধ্যে হলে First Division
৩য় শর্ত: মার্ক ৪৫০-৫৯৯ এর মধে হলে Second Division
৪র্থ শর্ত: মার্ক ৩৩০-৪৪৯ এর মধ্যে হলে Third Division
৫ম শর্ত: মার্ক ৩৩০ এর কম হলে Fail
সংক্ষেপে: মার্ক ৭৫০ এর সমান বা বেশি হলে STAR, ৬০০ এর সমান বা বেশি হলে First Division, ৪৫০ এর সমান বা বেশি হলে Second Division, ৩৩০ এর সমান বা বেশি হলে Third Division অন্যথায় Fail
নিচের চিত্রে B কলামে কয়েকজন ছাত্রের পরীক্ষার মার্ক দেওয়া আছে। আমরা C কলামে তাদের রেজাল্ট বের করতে চাই। এখন প্রথম ছাত্রের রেজাল্ট বের করার জন্য C2 সেলে কার্সর রেখে ফর্মুলা বার এ =IF(B2>=750,"STAR",IF(B2>=600,"First Division",IF(B2>=450,"Second Division",IF(B2>=330,"Third Division","Fail")))) লিখি। এখানে একটা জিনিশ লক্ষণীয় যে কয়টা First Bracket হবে ঠিক ততোগুলোই শেষে ক্লোজ করতে হবে, অন্যথায় error দেখাবে।

এখন ইন্টার চাপলে আমরা প্রথম ছাত্রের রেজাল্ট দেখতে পাবো। এরপর Fill অপশন ব্যবহার করে আমরা বাকি ছাত্রদের রেজাল্ট পাবো।

আজ এই পর্যন্তই। সবাই ভালো থাকবেন। আমার জন্য দোয়া করবেন।


-------------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৬] :: Salary Sheet তৈরি

আসসালামু আলাইকুম। আশা করি আপনারা সবাই ভালো আছেন। গত পর্বে আমরা IF Function নিয়ে আলোচনা করেছিলাম। আজ আমরা IF Function এর মাধ্যমে আরও কিছু জটিল শর্তপুরন করে  বিগত ৫ টি পর্বে  যা শিখেছি তা দিয়ে একটি Salary Sheet তৈরি করবো। তাহলে চলুন শুরু করি।

Salary Sheet তৈরি করার আগে আমাদের জানতে হবে শর্তগুলো কি কি ? আসুন জেনে নেই উক্ত প্রতিষ্ঠানের শর্তগুলো (IF Function ভালোভাবে আয়ত্ত করার জন্য কিছুটা শর্ত জটিল করা হয়েছে)।

শর্তসমূহ

১। House Rent:  বেসিক ১১০০০ টাকা এর সমান বা বেশি হলে House Rent হবে বেসিক এর ৫০% তবে কোনমতেই তা ৬২০০ টাকা এর কম হবে না। বেসিক ১১০০০ টাকা এর কম হলে House Rent হবে বেসিক এর ৬০% তবে কোনমতেই ৩৪০০ এর কম হবে না।

২। Medical Allowance:  সকল কর্মকর্তা/ কর্মচারী মাসিক ১০০০ টাকা করে চিকিৎসা ভাতা পাবেন।

৩। Conveyance Allowance: সকল কর্মকর্তা/ কর্মচারী মাসিক বেসিক এর ১০% হারে Conveyance Allowance পাবেন শুধুমাত্র যাদের বেসিক ২৫০০০ টাকা  বা তার উপরে তাঁরা ব্যাতিত। তাঁরা কোম্পানির নিজস্ব গাড়ি ব্যবহার করবে বিধায় যাতায়াত ভাতা পাবে না।

৪। Provident Fund: সকল কর্মকর্তা/ কর্মচারীর নিকট হতে ভবিষ্যৎ তহবিলের জন্য বেসিক এর ১০% হারে Provident Fund কাঁটা হবে।

৫। Welfare Fund: বেসিক এর ১% হারে Welfare Fund এর জন্য টাকা কাঁটা হবে।

৬। Income Tax: যাদের বেসিক ১১০০০ টাকার নিচে তাদের জন্য আয়কর প্রযোজ্য নয়। ১১০০০ টাকা বা এর উপরের বেসিক এর জন্য আয়কর বেসিক এর ৩% তবে ২৫০০০ টাকা বা এর উপরের বেসিক হলে আয়কর ৫% হারে কর্তন হবে।

যাদের কাছে এখনই কঠিন মনে হচ্ছে তাঁরা আমার গত পর্বটি দেখে আসুন

আসুন শিখি Advanced Microsoft Excel [পর্ব-০৫]

Salary Sheet তৈরি

আসুন প্রথমে নিচের মতো একটি Salary Sheet এর ডাটাবেজ তৈরি করি।

আসুন এখন শর্ত অনুযায়ী ফর্মুলা/ ডাটা ইনপুট করি (প্রথমে বুঝার সুবিধার জন্য সহজ থেকে শুরু করলাম)

Medical Allowance:  চিকিৎসা ভাতার জন্য E2 সেলে ১০০০লিখি।

Provident Fund: Provident Fund নির্ণয়ের জন্য G2 সেলে =C2*10% লিখে ইন্টার দেই।

Welfare Fund: Welfare Fund নির্ণয়ের জন্য H2 সেলে =C2*1% লিখে ইন্টার দেই।

Conveyance Allowance: যাতায়াত ভাতা নির্ণয়ের জন্য F2 সেলে =IF(C2<25000,C2*10%,0) লিখে ইন্টার দেই।

Income Tax: আয়কর নির্ণয়ের জন্য I2 সেলে =IF(C2>=25000, C2*5%, IF(C2>=11000,C2*3%,0)) লিখে ইন্টার দেই।

House Rent: House Rent নির্ণয়ের জন্য D2 সেলে কার্সর রেখে ফর্মুলা বার এ =IF(C2>=11000,IF(C2*50%<=6200,6200,C2*50%),IF(C2*60%<=3400,3400,C2*60%)) লিখে ইন্টার দেই।

তাহলে আমরা নিচের চিত্রের মত দেখতে পাবো। এখানে একটা জিনিশ লক্ষণীয়  GM মহোদয় কোম্পানির গাড়ি ব্যবহার করেন বিদায় শর্ত মোতাবেক তার কোন Conveyance Allowance নাই।

Total: এখন প্রথম জনের মোট বেতন বের করার জন্য J2 সেলে কার্সর রেখে ফর্মুলা বার এ =(C2+D2+E2+F2)-(G2+H2+I2) লিখে ইন্টার দেই। কেননা Besicএর সাথে House Rent, Medical Allowance,  Conveyance Allowance যোগ হচ্ছে আর  Provident Fund, Welfare Fund, Income Tax বাদ যাচ্ছে। এখন আমরা নিচের চিত্রের মত প্রথম জনের Total Salary পেয়ে গেছি।

বাকিদের Total Salary বের করার জন্য আমরা Fill Option সাহায্য নেই। এখন বাকি গুলা বের করতে মাউস দিয়ে D2 সেলে ক্লিক করে মাউস পয়েন্টার D2 সেলের ডানের নিচের কর্নারে নিলে দেখবেনে একটি প্লাস চিহৃ দেখা যাচ্ছে তখন মাউস পয়েন্টারকে ড্রাগকরে (চেপে নিচের দিকে টান দিন)  নিচের দিকে টেনে ছেড়ে দিন । এভাবে পর্যায়ক্রমে E2,F2,G2,H2,I2,J2 সেলে ও করুন। এখন আমদের কাঙ্খিত Salary Sheet টি তৈরি হয়ে গেছে নিচের ছবির মত

Fill Option সম্বন্ধে বিস্তারিত জানতে আমার এই পোস্টটি দেখুন: আসুন শিখি Advanced Microsoft Excel [পর্ব-০৩]

আজকে এ পর্যন্তই । সবাই ভাল থাকবেন।

---------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৭] :: Data Validation

আসসালামু আলাইকুম। কেমন আছেন সবাই। আমি আজ একটু ফুরফুরে মেজাজেই আছি, ছুটির দিন তো তাই। আজ আমরা শিখবো Data Validation, যা  কিনা Data ট্যাব এর Data Tools গ্রুপে রয়েছে। Validation শব্দের বাংলা অর্থ হল নিয়ন্ত্রণ করা বা বৈধতা প্রদান করা। তাহলে আমরা দেখি Data Validation এর মাধ্যমে কিভাবে ডাটা কে নিয়ন্ত্রণ করা যায়।

উদাহরণ ১

প্রথমে নিচের চিত্রের মতো কিছু ডাটা টাইপ করি। যেসব সেলের ডাটা কে নিয়ন্ত্রণ করতে হবে সেগুলো সিলেক্ট করি। তারপর Data ট্যাব থেকে Data Validation এ ক্লিক করি।

তাহলে নিচের চিত্রের মত একটা ডায়ালগ বক্স আসবে। এখান থেকে Allow এর ড্রপ ডাউন লিস্ট থেকে Whole Number সিলেক্ট করি। সর্বনিম্ন ০ ও  সরবোচ্চ মান ১০০- সিলেক্ট করি। এরপর Ok তে ক্লিক করি।

এখন আপনি চাইলে ও এই সেল সমুহে ০-১০০ এর বাহিরের ডাটা লিখতে পারবেন না। লিখলেই নিচের মত ডায়ালগ বক্স আসবে।

আপনি ইচ্ছে করলে Input message ও Error message ও দিতে পারেন। এতে আপনি যেই সেল/সেলসমুহে Data Validation করেছেন সেইগুলো সিলেক্ট করা অবস্থায় Data validation ডায়ালগ বক্স থেকে Input Message মেনুটি সিলেক্ট করুন।

তারপর বক্স এ Input Message হিসাবে লিখলাম Please Write here between 0 to 100. এরপর Error Alert মেনুটি সিলেক্ট করি Error message  হিসাবে যা মন চায় লিখুন। আমি লিখলাম "ওই মিয়া পাগল হইছো ?কাজে মনোযোগ দাও" 🙄 ।  এবার Ok ক্লিক করুন। ওই সেল/সেলসমুহে কার্সর নিলে একটি ইনপুট মেসেজ দেখাবে এবং ডাটা লিখতে ভুল করলে আপনার নিজের দেওয়া ইরর ম্যাসেজটি দেখাবে।

আপনি যদি সব সেলের/কয়েকটি সেলের Data Validation বাদ দিতে চান তাহলে ওই সেল/সেলসমুহ সিলেক্ট করে Data Validation ডায়ালগ বক্স থেকে প্রথমে Clear All তারপর OK তে ক্লিক করুন।

উদাহরণ ২

অনেক সময় আমরা দেখি ৩০ বছর বয়সের বেশি লোক চাকুরিতে আবেদন করতে পারেনা। এক্ষেত্রে চাকুরিদাতা প্রতিষ্ঠান তাদের অনলাইন আবেদন ফর্মে Date of Birth এর ডাটা কে নিয়ন্ত্রণ করতে পারেন। এক্ষেত্রে নিচের ছবির মত যেই সেলে আবেদনকারী তার জন্মতারিখ লিখবে তা সিলেক্ট করে Data Validation ডায়ালগ বক্সে Allow এর ড্রপ ডাউন লিস্ট থেকে Date এবং Data এর ড্রপ ডাউন লিস্ট থেকে greater then or equal to সিলেক্ট করি। যেই তারিখের পূর্বে জন্ম নেয়া কোন লোক এপ্লাই করতে পারবে না সেই তারিখ  লিখি। এরপর OK ক্লিক করি। আমাদের কাজ শেষ।

উপরের চিত্রে ০১/০১/১৯৮৪ তারিখ দিলাম। এখন যেসব আবেদনকারীর জন্ম উক্ত তারিখের পূর্বে তাঁরা উক্ত চাকুরিতে আবেদন করতে পারবেন না। কেননা ওই ডাটা লিখলেই ইরর মেসেজ দেখাবে। আজ এই পর্যন্তই, আগামি পর্বে কিভাবে Data Validation এর মাধ্যমে Drop Down List তৈরি করতে হয় তা নিয়ে আলোচনা করবো। সবাই আমার জন্য দোয়া করবেন।

-------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৮] :: Drop Down List তৈরি

আশা করি সবাই ভালো আছেন ও গত পর্বটি বুঝতে পেরেছেন। আজ আমরা শিখবো Data Validation এর মাধ্যমে কিভাবে Drop Down List তৈরি করা যায়। এটা মুলত যেইসব ডাটা প্রায় ব্যবহার করতে হয় সেইখানে বার বার না লিখে একটা লিস্ট একবার তৈরি করে নিলে বারবার ব্যবহার করা যায়। ফলে কাজ করা অনেক সহজ হয়। আজ আমরা বিভিন্ন অনলাইন Application Form, Email ID খোলার সময় যে ধরনের ড্রপ ডাউন লিস্ট দেখি তা ও বানানোর চেষ্টা করবো। তো আসুন কাজ শুরু করা যাক

উদাহরণ ১

ধরুন ১ টি কোম্পানির ৭টি শোরুম  আছে। কোম্পানির হিসাব কর্মকর্তাকে প্রতিদিন প্রতিটা শোরুম এর মোট বিক্রয়ের হিসাব রাখতে হয়। প্রতিদিনই উনাকে হিসাব করতে গিয়ে শোরুমগুলোর নাম লিখতে হয়। উনি কিভাবে সহজে একটা Drop Down List তৈরি করে কাজটি করতে পারে তা আমরা দেখবো। প্রথমে নিচের চিত্রের মত কিছু ডাটা টাইপ করি (সহজে বুঝার জন্য অল্প ডাটাই লিখলাম কেননা একজন হিসাব কর্মকর্তাকে অনেক ডাটাই সংগ্রহ করতে হয়) এবং একপাশে শোরুমগুলোর নাম লিখি। এখন showroom কলামের সেল এ ছবির মতো কার্সর থাকা অবস্থায় Data ট্যাব থেকে Data Validation এ ক্লিক করি।

এখন নিচের ছবির মত Data Validation ডায়ালগ বক্স থেকে Allow এর ড্রপ ডাউন লিস্ট থেকে List সিলেক্ট করি। এরপর Source এর ঘরে কার্সর রেখে সোর্স দেখিয়ে দেই অর্থাৎ চিত্রের মতো showroom গুলো সিলেক্ট করি। তবে সিলেক্ট করার সময় কয়েকটি সেল বেশি সিলেক্ট করা বুদ্ধিমানের কাজ হবে।  কিছুদিন পর যদি নতুন আরও ২/১ টি শোরুম চালু হয় তাহলে ওই লিস্টে শোরুমের নাম লিখে দিলেই অটো নিয়ে নিবে কেননা কয়েকটি সেল তো আমরা আগে থেকেই সিলেক্ট করে রেখেছি।

এবার OK তে ক্লিক করুন। এখন B2 সেলে কার্সর নিলেই একটি Drop Down List থাকার চিহ্ন দেখাবে। যাতে ক্লিক করলেই Drop Down List দেখাবে।

আমরা Fill অপশন এর মাধ্যমে ওই কলামের আরও কিছু সেল Fill করি। যতগুলো সেল আমরা Fill করলাম ততগুলো সেলেই ক্লিক করলেই Drop Down List আসবে। এখন হিসাব কর্মকর্তাকে আর কষ্ট করে শোরুমের নাম লিখতে হবে না, সিলেক্ট করলেই হবে। এখানে একটা জিনিশ লক্ষণীয় সোর্স হিসাবে আমরা যতটুকু অংশ সিলেক্ট করেছি তা Edit করলে তা অটো Drop Down List এ দেখাবে। ধরুন নতুন একটা ব্রাঞ্চ খুলনা তে চালু হইছে, এখন Rajshahi এর নিচে khulna লিখলে ৬তা অটো Drop down list এ দেখাবে।

উদাহরণ ২

নিচের চিত্রের মত আমরা কিছু ডাটা টাইপ করি।

এখন Fill অপশন এর মাধ্যমে তারিখ ১ হতে ৩১ , বারো মাসের নাম, আর সন আমরা যতটা চাই Fill করি। যারা করতে পারবেন না আমার এই পোস্ট টি দেখে আসুন আসুন শিখি Advanced Microsoft Excel [পর্ব-০৩]

চিত্রের মত C2 সেলে কার্সর রেখে Data Validation ডায়ালগ বক্স থেকে Allow এর ড্রপ ডাউন লিস্ট থেকে List এবং Source বক্সে কার্সর রেখে ১ থেকে ৩১ পর্যন্ত তারিখসমুহ সিলেক্ট করে OK তে ক্লিক করি।

এবার E2 সেলে কার্সর রেখে Data Validation ডায়ালগ বক্স থেকে Allow এর ড্রপ ডাউন লিস্ট থেকে List এবং Source বক্সে কার্সর রেখে January থেকে December পর্যন্ত মাস সমুহ সিলেক্ট করে OK তে ক্লিক করুন। এরপর G2 সেলে কার্সর রেখে Data Validation ডায়ালগ বক্স থেকে Allow এর ড্রপ ডাউন লিস্ট থেকে List এবং Source বক্সে কার্সর রেখে সনসমুহ সিলেক্ট করে OK তে ক্লিক করুন। আমাদের কাজ শেষ। এই ফরম্যাটটি আমরা বিভিন্ন অনলাইন Application Form, Email ID খোলার সময় দেখতে পাই।

আমি একটা জন্ম তারিখ লিখেছি। এবার আপনারাও যার যার জন্মতারিখ তৈরিকৃত Drop Down List থেকে সিলেক্ট করুন। 😆

আজ এই পর্যন্তই। সবাই ভালো থাকবেন।

---------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-০৯] :: Define Name, Hyperlink

আসসালামু আলাইকুম। কেমন আছেন সবাই? আজ আমরা আলোচনা করবো Define Name ও Hyperlink নিয়ে। MS Excel 2007 এ একটি ওয়ার্কশীটে মোট ১০৪৮৫৭৬ টি রো এবং ১০২৪ টি কলাম আছে। আবার একটি বড় ডাটাবেজ এ অনেকগুলো ওয়ার্কশীট ও থাকে। তাই একটি ওয়ার্কবুক থেকে আপনি যদি কিছু ডাটা খুঁজে পেতে চান বা এডিট করতে চান তা কতটা ঝামেলাপূর্ণ একবার চিন্তা করে দেখুন। এই ঝামেলা থেকে রেহাই পেতে Define Name ও Hyperlink বেশ বড় ভুমিকা পালন করে থাকে।

Define Name

এর মাধ্যমে ওয়ার্কশীটের প্রয়োজনীয় ডাটাগুলোকে বিভিন্ন ছোট ছোট অংশে ভাগ করে বিভিন্ন নাম দেওয়া হয়। তারপর আপনি ওই ওয়ার্কবুকের যেই ওয়ার্কশীটেই থাকুক, যে চিপাতেই থাকুক নাম ধরে ডাক দিলে বান্দা হাজির। তো আসুন কাজ শুরু করি

Microsoft Excel চালু করি। এরপর কিবোর্ড থেকে Ctrl+Down Arrow চেপে ওয়ার্কশীটের একদম তলানিতে নামি। এবার Ctrl+Right Arrow চেপে একেবারে ডান কোনায় চলে আসুন। এখন এখানে কিছু ডাটা টাইপ করুন। আমি January থেকে December পর্যন্ত বারমাসের নাম লিখেছি। আমি একটি কলামে লিখেছি আপনি চাইলে বেশ কিছু কলামের বেশ কিছু সেলে ও যা খুশি ডাটা লিখতে পারেন। এখন ডাটাগুলো সিলেক্ট করে নিচের চিত্রের মত Formulas ট্যাব এর Defined Names গ্রুপের Define Name এ ক্লিক করুন। এখন যে ডায়ালগ বক্স আসবে তার Name বক্স এ ডাটাগুলোর একটা নাম দিন আমি নাম দিলাম Month। এবার OK ক্লিক করুন। আমাদের কাজ শেষ (একদম পানির মত সোজা)।

নাম তো দিলাম। এখন দেখি ডাক দিলে আসে কিনা? কিবোর্ড থেকে Ctrl+Home চেপে ওই ওয়ার্কশীটের প্রথমে চলে আসুন। আপনি ইচ্ছা করলে অন্য কোন ওয়ার্কশীটে ও যেতে পারেন। এখন কিবোর্ড থেকে F5 বাটন চাপুন। চিত্রের মত একটি Go To ডায়ালগ বক্স আসবে। এখন আপনি যেই নাম দিয়েছেন সেটি সিলেক্ট করে OK ক্লিক করুন। এখন আপনি যেই ওয়ার্কশীটেই অবস্থান করেন না কেনও আপনি যেই নাম সিলেক্ট করেছেন সেই নামের আওতায় থাকা ডাটাগুলো যেখানে আছে সেখানে চলে যাবে ।

এখন আমরা শিখব Define Name ব্যবহার করে Drop Down List কিভাবে তৈরি করা যায়। যদিও গত পর্বে আমরা Drop Down List তৈরি করা শিখেছি। সে ক্ষেত্রে সোর্স ডাটা একই ওয়ার্কশীটে থাকা লাগতো। Define Name ব্যবহার করে আমরা যে কোন ওয়ার্কশীটের ডাটা দিয়ে Drop Down List তৈরি করতে পারি। তবে অবশ্যই একটা জিনিশ লক্ষ্য রাখতে হবে Define Name করা ডাটাগুলো একই রোতে অথবা একই কলামে হতে হবে অন্যথায় Drop Down List হবে না। গত পর্বটি যারা দেখেননি : আসুন শিখি Advanced Microsoft Excel [পর্ব-০৮]:: Drop Down List তৈরি

যেই সেলে আমরা Drop Down List বানাতে চাই সিলেক্ট করি।নিচের চিত্রের মত Data ট্যাব থেকে Data Validation সিলেক্ট করি। এখন যে ডায়ালগ বক্স এসেছে Allow এর Drop Down List থেকে List সিলেক্ট করি। এখন মুল যে কাজটি তা হল = চিহ্ন দিয়ে Define Name এর নামটি লিখতে হবে। আমরা আগে যে Month নামে একটি Name Define করে ছিলাম তা দিলাম। এবার Ok ক্লিক করুন।

এখন দেখুন আপনার কাংখিত সেলে Drop Down List তৈরি হয়ে গেছে।

কোন কারনে যদি Define Name বাদ দিতে চান তাহলে নিচের চিত্রের মত Name Manager এ ক্লিক করুন। যে ডায়ালগ বক্স আসবে এখান থেকে যেই নামটি আপনি বাদ দিতে চান তা সিলেক্ট করুন। এরপর Delete এ ক্লিক করুন। এখন সতর্কতামুলক আরেকটি বক্স আসবে সেখান থেকে Ok তে ক্লিক করুন। আপনি যদি নাম পরিবর্তন করতে চান তাহলে ডায়ালগ বক্স থেকে Edit এ ক্লিক করে নতুন নাম দিতে পারেন।

Hyperlink

Hyperlink মুলতঃ কোন লেখার সাথে অন্য কোন লেখার, এক ওয়ার্কশীটের সাথে অন্য ওয়ার্কশীটের, ইমেইল, ওয়েব এ্যাড্রেস ইত্যাদির link করার জন্য ব্যবৃহত হয়। এটা হল Click-able লিঙ্ক অর্থাৎ ক্লিক করলেই লিঙ্ক এর স্থানে চলে যাওয়া যায়।  যেমনঃ ডকুমেন্টের কোথাও যদি Prothom Alo শব্দটি লিখলেন। এখন আপনি চাচ্ছেন এর সাথে প্রথম আলো পত্রিকার একটা  লিংক দিবেন। যাতে কেউ Prothom Alo শব্দটির উপর ক্লিক করলেই এর ওয়েব পেজ সাথে সাথে ব্রাউজারে ওপেন হবে। এজন্য প্রথমে Prothom Alo শব্দটিকে সিলেক্ট করুন। তারপর চিত্রের মত Insert ট্যাব থেকে Hyperlink ক্লিক করুন (অথবা মাউসের Right button ক্লিক করে Hyperlink সিলেক্ট করুন) । তারপর Address বক্সে লিখুন http://www.prothom-alo.com লিখে  Ok ক্লিক করুন। এখন Prothom Alo লিখাতে ক্লিক করলেই পত্রিকাটির ওয়েবসাইট ওপেন হবে ( যদি ইন্টারনেট কানেকশন থাকে 😆 )।

এখন আমরা দেখবো Name Define এর সহযোগিতা নিয়ে কিভাবে Hyperlink করা যায়। সেই Name define টি যদি ১০ নাম্বার ওয়ার্কশীটের একেবারে কোনায় ও হয়ে থাকে কোন অসুবিধা নাই। কিছুক্ষন পূর্বে আমরা প্রথম শিটের একেবারে ডানের কোনায় বারমাসের নাম Name define করেছিলাম। এখন সেটাকে লিঙ্কিং করি আসুন।

Insert ট্যাব থেকে Hyperlink এ ক্লিক করুন। নিচের চিত্রের মত ডায়ালগ বক্স আসবে। এখান থেকে Place in This Document সিলেক্ট করে Define Name হিসাবে যে নামটি দেওয়া আছে তা সিলেক্ট করুন। এরপর OK ক্লিক করুন।

এখন উক্ত সেলে একটি Hyperlink হয়ে গেছে। যাতে ক্লিক করলেই Define Name এর ডাটা চলে আসবে।

অনেক সময় আমরা বিভিন্ন ওয়েবসাইট ভিজিট করার সময় ক্রল করে নিচের দিকে নামলে দেখতে পাই Go to Top/উপরে যান লিখা। যাতে ক্লিক করলে ওই পেজের উপরে চলে যায়। এখন আমরা আমাদের এক্সেল শিটে এমনটি করবো। কিবোর্ড থেকে Ctrl+Down Arrow চেপে ওয়ার্কশীটের একদম তলানিতে নামি। এখন একটি সেল সিলেক্ট করে Hyperlink এ ক্লিক করলে যে ডায়ালগবক্স আসবে তাতে Place in This Document সিলেক্ট করে Text to Display এর বক্সে Go to Top লিখে চিত্রের মত Sheet1 সিলেক্ট করে OK ক্লিক করুন। এখন ওই সেলে Go to Top নামে একটি Hyperlink তৈরি হবে যাতে ক্লিক করলে ওই শিটের একদম উপরে উঠে যাবে।

যদি প্রথম শিটে অবস্থান করে ৩ নং শিটে যেতে চান তাহলে যেই সেলে Hyperlink করতে চান তা সিলেক্ট করে Place in This Document সিলেক্ট থাকা অবস্থায় Sheet 3 সিলেক্ট করুন। Text to Display এর বক্সে Go to Sheet 3 বা আপনার বুঝার সুবিদারথে অন্য কিছু লিখুন। বড় ডাটাবেইজ এর ক্ষেত্রে ৪০/৫০ বা তারও অধিক ওয়ার্কশিট থাকে। তখন স্ক্রল করে না গিয়ে যেইসব শিটে প্রায় কাজ করতে হয় তা এভাবে লিঙ্কিং করা হয় প্রথম পেজের সাথে। এমনকি এক শিটের ডাটা এর সাথে বুঝার সুবিদারথে অন্য শিটের ডাটা ও লিঙ্কিং করা হয়।

যদি Hyperlink বাদ দিতে চান তাহলে ওই সেলটি সিলেক্ট করে মাউসের Right button ক্লিক করে Remove Hyperlink সিলেক্ট করুন।

আজ এই পর্যন্তই। অনেক বড় টিউন হয়ে গেল। চেষ্টা করেছি আজকের Define Name ও Hyperlink এই দুটি বিষয় নিয়ে ভালোভাবে আলোকপাত করতে। ভুল ক্রুটি ক্ষমা সুন্দর দৃষ্টিতে দেখবেন। যদি কারো কোথাও বুঝতে সমস্যা হয় অবশ্যই কমেন্টস করবেন। সবাই ভালো থাকবেন।

------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১০] :: PV, PMT, FV নির্ণয়

আসসালামু আলাইকুম। কেমন আছেন সবাই? আমি বেশ ভালো কেননা ঈদের ছুটি শুরু হয়ে গেছে। ঢাকাতেই ঈদ করছি তাই বাড়ি যাওয়ার ঝামেলা নাই। এই ছুটিতে বেশ কিছু টিউন আপনাদের উপহার দিতে পারবো বলে আশা রাখি। আজকে আমরা এক্সেল এর মাধ্যমে কিভাবে PV, PMT, FV নির্ণয় করা যায় তা শিখবো। যারা Finance, Accounting নিয়ে পড়াশুনা করেছেন তাঁরা এই টার্মগুলোর সাথে আগে থেকেই পরিচিত। আর আমরা যারা অন্য ফ্যাকাল্টির তাদেরও টেনশন এর কোন কারন নেই। আজ আমরা সব শিখে নিব।

PV (Present Value)

উদাহরণ ১:

ব্যাংক ১১% চক্রবৃদ্ধি হারে সুদ দেয়। এখন আপনি যদি ১০ বছর পরে ৫ লক্ষ টাকা জমাতে চান তাহলে এখন আপনার কত টাকা ব্যাংকে জমা রাখতে হবে?

এখন PV বা Present Value বের করার জন্য B5 সেলে ফর্মুলা লিখার জন্য =PV( লিখলেই একটা সাজেশন দেখাবে PV(rate, nper, pmt, [fv], [type]) । এবার আমরা এগুলোর সাথে আমাদের বাস্তবের অর্থগুলো একটু মিলিয়ে নিবো। যেমন:

rate = Interest Rate এটা সহজেই বোঝা যায়।

nper = এটার অর্থ Number of Periods (মানে আপনি যে কয়টা Payment বা Installment দিবেন)

fv = Future Value বা কোন কিছুর ভবিষ্যৎ মূল্য

PV =  Present Value বা কোন কিছুর বর্তমান মূল্য

pmt = Payment বা প্রতি Installment এ টাকার পরিমান

type = এটা সাধারণ ০ বা ১ হয়, মাসের প্রথমে payment করলে ১ আর মাসের শেষে payment করলে ০ type হিসেবে দিতে পারেন, আর কোন কিছু না দিলেও হবে। সাধারণত কোন কিছু না দিলে ০ হিসেবে ধরে নেয়।

এখন আমরা B5 সেলে কার্সর রেখে ফর্মুলাবার এ  =PV(B1,B2,0,-B3,1) লিখে  ইন্টার চাপি। যেহেতু আমরা মাসিক কোন Installment দিবো না তাই ০, B3 এর পূর্বে - দেওয়ার কারন হল যেহেতু এটা কিছুটা সপ্নের মত, কল্পনাপ্রসূত তাই FV,PV, PMT বের করার পূর্বে টাকার পরিমান এর পূর্বে - চিহ্ন দিতে হয়  ( এই ব্যপার এ আমি আর বিস্তারিত জানিনা, কেও জানলে অবশ্যই জানাবেন কৃতজ্ঞ থাকব)।  – চিহ্ন না দিলেও হবে তবে রেজাল্ট লাল কালারে দেখাবে।আর এখানে ১ বা ০ দেওয়ার মধ্যে কোন পার্থক্য নাই যেহেতু মাসিক কোন Installment নাই। ১০ বছর মেয়াদ শেষ হলেই আমরা পাঁচ লক্ষ টাকা পাবো।

আমরা ফলাফল পেয়ে গেছি অর্থাৎ এখন যদি ১৭৬০৯২.২৪ টাকা ব্যাংক এ রাখি তাহলে ১০ বছর পর আমরা পাঁচ লক্ষ টাকা পাবো

 উদাহরণ ২:

মনে করুন আপনি ব্যাংক থেকে ১৫% সুদে ঋণ নিতে চাইছেন। আপনি আগামী ৮ বছর প্রতি মাসে ৫০০০ টাকা করে পরিশোধ করতে পারবেন। আপনি এখন কত টাকা ঋণ পাবেন ?

তাহলে আমরা চিত্রের মত ডাটা টাইপ করে B5 সেলে কার্সর রেখে  =PV(B1/12,B2*12,-B3,0,1) লিখে ইন্টার দেই। তাহলেই আমরা আমাদের কাংখিত ফলাফল পাবো। যেহেতু আমরা মাসিক কিস্তি দেব তাই হিসাবটা ও মাসিক ভিত্তিতে করতে হবে। আর এইজন্যই  মাসিক সুদ বের করার জন্য B1 কে ১২ দ্বারা ভাগ , মোট কতটি কিস্তি টা বের করার জন্য ৮ বছরকে অর্থাৎ B2 কে ১২ দ্বারা গুন করা হয়েছে।  B3 এর পূর্বে ঐতিহাসিক সেই  - চিহ্ন , যেহুতু ঋণ পরিশোধ হতে হতে অবশেষে ০ হয়ে যাবে তাই FV= 0, আর ধরে নিলাম আমরা কিস্তি প্রতি মাসের প্রথমে দিবো তাই 1 দিলাম। কিস্তি যদি প্রতি মাসের শেষে দেই তাহলে আমরা কম টাকা পাবো। অর্থাৎ PV কমবে। আপনারা করে দেখতে পারেন।

PMT ( Payment/Par Month Installment)

মকবুল সাহেব ১৭% হার সুদে ৬০০০০০ টাকা ঋণ নিল যা তিনি ৪ বছরে শোধ করবেন। তার মাসিক কিস্তি কত হবে? আসুন আমরা অঙ্কটি এক্সেলে করি

উপরের চিত্রের মত B5 সেলে কার্সর রেখে ফর্মুলা বার এ =PMT(B3/12, B2*12,-B1,0,1)  লিখে ইন্টার দেই। তাহলে আমরা পেয়ে গেলাম মাসিক কিস্তি ১৭০৭১.১৮ টাকা।  যদি আমরা এই ফর্মুলা  =PMT(B3/12, B2*12,-B1,0,০) দিতাম অর্থাৎ কিস্তি প্রতি মাসের শেষে দিবে তাহলে মাসিক কিস্তির টাকার পরিমান বাড়ত।

FV (Future Value)

উদাহরণ ১:

Interest Rate= 11%

Payment period= 3 years

Per Month Installment: 15000 tk

Future Value: ?

অর্থাৎ কেও যদি ১১% হার সুদে প্রতি মাসে ১৫০০০ টাকা করে ব্যাংক এ রাখে। ৩ বছর পরে সে কত টাকা পাবে? উপরের চিত্রের মত B5 সেলে কার্সর রেখে ফর্মুলা বার এ =FV(B1/12, B2*12,-B3,0,1)  লিখে ইন্টার দেই। তাহলে আমরা পেয়ে গেলাম ৬,৪২,১৮০.০৩ টাকা।

উদাহরণ ২:

আপনি যদি ১২% হার সুদে ১০ লাখ টাকা FDR  করে রাখেন ৫ বছর পর কত টাকা পাবেন ?

উপরের চিত্রের মত B5 সেলে কার্সর রেখে ফর্মুলা বার এ =FV(B1, B2,-B3,0)  লিখে ইন্টার দেই। তাহলে আমরা পেয়ে গেলাম ১৯,৭৩,৮২২.৬৯ টাকা। যেহেতু এখানে মাসিক কোন ইন্সটলমেন্ট নাই তাই ১২ দিয়ে গুন ভাগ করার কাহিনী করা লাগবে না।

লক্ষণীয় বিষয়সমূহ :

১. যদি কারো টাকার পরিমান এ $ (ডলার) চিহ্ন আসে তাহলে ওই সেল/কলাম/ পুরো শিটটি সিলেক্ট করে মাউসের রাইট বাটন ক্লিক করে Format Cells এ ক্লিক করুন । এখন Category থেকে Currency সিলেক্ট করে Symbol থেকে None সিলেক্ট করে OK তে ক্লিক করুন।

২. PV, PMT, FV যেটাই বের করেন না  কেনও একবার ফর্মুলা লিখার পর এরপর যেকোনো মান ই চেঞ্জ  করে ফলাফল কি হয় দেখতে চাইলে নতুন করে লিখার দরকার নেই। শুধু মানটি বা মান সমূহ পরিবর্তন করে ইন্টার চাপলেই ফলাফল পেয়ে যাবেন।

আজ এই পর্যন্তই। সবাই ভালো থাকবেন। অগ্রিম ঈদ মোবারক।

----------------------------------------------------------------------------------------------------------------------

• আসুন শিখি 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 ব্যবহার করে জায়।কিভাবে আরও সহজভাবে করা যায় তা আমরা এখন দেখবো। গত পর্ব যারা দেখেননি: আসুন শিখি Advanced Microsoft Excel [পর্ব-১০]:: PV, PMT, FV নির্ণয়

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

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

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

উদাহরণ ৪

এইবার একটু কঠিন উদাহরণ দেয়া যাক। ধরুন  5x3+6x2-8x+15=9423  এই সমীকরণটির x এর মান বের করতে বলা হল। দেখি আমরা Goal seek এর মাধ্যমে সমীকরণটির x এর মান বের করতে পারি কিনা।

সমিকরনটিকে Ax3+Bx2-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 হলে 5x3+6x2-8x+15 এই সমীকরণটির মান বের কর তাহলে আমরা A2 সেলে ২০ লিখে ইন্টার দিলেই নিচের চিত্রের মত এক নিমিষেই মান পাবো।

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

আমরা 5x3+6x2-8x+15=9423  এই সমীকরণটির x এর মান 12 পেয়ে গেছি।

আজ এই পর্যন্তই। আশা করি আপনারা Goal Seek  কিভাবে কাজ করে ও কেন ব্যবহার করা হয় তা বুঝতে পেরেছেন। সবাই ভালো থাকবেন।

-------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১২] :: Filter, Advanced Filter

আসসালামু আলাইকুম। কেমন আছেন সবাই? বেশ কয়েকদিন বিরতির পর আজ নতুন আরেকটি পর্ব নিয়ে হাজির হলাম। আজ আমরা Filter ও Advanced Filter নিয়ে আলাপ করবো। আমরা সবাই জানি Filter শব্দের অর্থ ছাকনি। মাইক্রোসফট এক্সেল এ ফিল্টারিং করার মাধ্যমে বড় ডাটাবেজ থেকে আমরা আমাদের চাহিদা মোতাবেক ডাটাসমূহ খুঁজে বের করতে পারি।  যেমন Filter Option এর মাধ্যমে ১০ লক্ষ লোকের তথ্য সম্বলিত একটি বড় ডাটাবেজ থেকে কোন নিদিষ্ট জেলার, নিদিষ্ট উপজেলার, নিদিষ্ট গ্রামের কোন একজন লোকের তথ্য সহজেই জানা যায়।

Filter

আসুন নিচের মত প্রথমে একটি ছোট ডাটাবেজ তৈরি করি। আর যারা আমার থেকেও বেশি অলস তারা  মাত্র ১৫ কেবি এর ফাইলটি এখান থেকে ডাউনলোড করে নিন।

এখন হেডারটি সিলেক্ট করে নিচের চিত্রের মত Data ট্যাব থেকে Filter এ ক্লিক করুন। লক্ষ্য করলে দেখবেন প্রতিটা কলামেই ড্রপ ডাউন লিস্ট এর চিহ্ন দেখাচ্ছে ( যা আমি নিচের ছবিতে তীর চিহ্ন দিয়ে দেখিয়েছি) ।

এখন যদি আমরা দেখতে চাই উক্ত প্রতিষ্ঠানে কতজন Operator কাজ করে বা অপারেটরদের ডাটা সমূহ দেখতে চাই, তাহলে নিচের চিত্রের মত Designation এর ডান পাশের  ড্রপ ডাউন লিস্ট এর চিহ্ন ক্লিক করে Select All এর টিক চিহ্ন উঠিয়ে দিয়ে Operator এর বক্সে ক্লিক করে OK তে ক্লিক করুন। 

এখন নিচের চিত্রের মত অপারেটরদের তথ্য পেয়ে গেলাম। তীর চিহ্ন বরাবর যে সিম্বলটি দেখাচ্ছে তা দিয়ে বুঝা যায় এই কলামটিতে ফিল্টারিং করা হয়েছে। আপনি যদি পূর্বের অবস্থায় ফিরে যেতে চান তাহলে ওই চিহ্ন বরাবর ক্লিক করে Select All এর বক্সে ক্লিক করে OK ক্লিক করুন অথবা সরাসরি Data ট্যাব এর Filter এ ক্লিক করুন।

আমরা যদি আরও ফিল্টার করতে চাই যেমন অপারেটরদের মধ্যে গাজীপুর জেলার HSC পাশ কে কে আছে তাহলে Education এর ডান পাশের  ড্রপ ডাউন লিস্ট এর চিহ্ন ক্লিক করে Select All এর টিক চিহ্ন উঠিয়ে দিয়ে HSC এর বক্সে ক্লিক করে OK তে ক্লিক করুন। এরপর District এর ডান পাশের  ড্রপ ডাউন লিস্ট এর চিহ্ন ক্লিক করে Select All এর টিক চিহ্ন উঠিয়ে দিয়ে Gazipur সিলেক্ট করে OK তে ক্লিক করুন।  এখন আমরা আমাদের কাংখিত ডাটাটি পেয়ে গেছি। 

এভাবে আমরা হাজার হাজার ডাটা থেকে আমাদের চাহিদা মোতাবেক ডাটা আমরা অতি অল্প সময়েই খুঁজে বের করতে পারবো। একেবারে প্রথম অবস্থায় ফিরে যেতে Data ট্যাব এর Filter এ ক্লিক করুন।

উদাহরণ ২:

আমরা উক্ত কোম্পানিতে ৩০০০০ এর বেশি এবং ১০০০০ এর কম কে কে বেতন পায় তাদের তালিকা দেখতে চাই। প্রথমে হেডারটি সিলেক্ট করে Data ট্যাব থেকে Filter এ ক্লিক করুন। এখন নিচের চিত্রের মত Salary এর ড্রপ ডাউন লিস্ট থেকে Number Filters থেকে Greater Than সিলেক্ট করুন।

এখন নিচের মত একটা ডায়ালগ বক্স আসবে। এখান থেকে নিচের চিত্রের মত প্রথম বক্সে is greater than সিলেক্ট করে তার পরের বক্সে ৩০০০০ লিখুন। এরপর OR সিলেক্ট করুন।  পরের বক্সে is less than  সিলেক্ট করে ১০০০০ লিখে OK ক্লিক করুন।

এখন আমরা নিচের চিত্রের মত উক্ত কোম্পানিতে ৩০০০০ এর বেশি এবং ১০০০০ এর কম কে কে বেতন পায় তাদের তালিকা পেয়ে গেছি।

এভাবে আরও বেশ কিছু কন্ডিশনে ডাটা ফিল্টারিং করা যায়। আশা করি এখন আপনারা একটু চর্চা করলেই পারবেন।

Advanced Filter

Advanced Filter এর মাধ্যমে আরও অনেক সহজে বেশ কয়েকটি শর্ত একসাথে দিয়ে ফিল্টারিং করা যায়। ডাউনলোড করা এক্সেল ফাইলটির Sheet 2 ওপেন করুন। দেখবেন একটি ডাটাবেজ রয়েছে। এখান থেকে নিম্নোক্ত শর্ত সাপেক্ষে  আমরা ডাটা সর্টিং করতে চাই।

শর্ত ১-  ইংলিশ ডিপার্টমেন্টের  Senior Product Engineer যারা C1 গ্রেডে বেতন পায় তাদের তালিকা।

শর্ত ২-  ফাইনান্স ডিপার্টমেন্টের  যারা A1 গ্রেডে বেতন পায় তাদের তালিকা।

শর্ত ৩-  যেসব গ্রাফিক্স ডিজাইনার ৫০০০০ এর বেশি বেতন পায় তাদের তালিকা।

আসুন কাজ শুরু করি। প্রথমে হেডারটি কপি করে পাশে কোথাও পেস্ট করি। এরপর নিচের চিত্রের মত শর্ত সমূহ লিখি। শর্ত সমূহ লিখার সময় Spelling এর প্রতি বিশেষ নজর দিবেন।

এখন Data ট্যাব থেকে Advanced Filter সিলেক্ট করুন। এখন নিচের মত ডায়ালগ বক্স আসবে । এখান থেকে আপনি কিআপনার ফিল্টারিং করা ডাটা সমূহ Filter অপশন এর মত ওই স্থানেই দেখতে চান নাকি ওই শিটের অন্য কোথাও দেখতে চান টা সিলেক্ট করুন। আমি Copy to Another Location সিলেক্ট করলাম। এতে সুবিধা হল আমার ডাটাবেজ ঠিক থাকল আমি আলাদা জায়গায় ফিল্টারিং করা ডাটাগুলো দেখতে পারবো পাশাপাশি ডাটাগুলো সঠিক কিনা তা ও যাচাই করতে পারবো।

এখন List range এর বক্সে কার্সর রেখে আমাদের পুরো ডাটাবেজটি সিলেক্ট করে দিন ( আমাদের শিটে A থেকে F পর্যন্ত কলামে ১০৩ রো পর্যন্ত ডাটা আছে) অথবা ডাটা থাকা কলামসমুহ পুরো সিলেক্ট করে দিন ( এখানে A থেকে F পর্যন্ত)। এরপর Criteria range এর বক্সে কার্সর রেখে হেডার সহ শর্তসমুহ লিখা স্থানটুকু সিলেক্ট করে দিন (এখানে H2 সেল থেকে M5 সেল)। একটা জিনিশ এখানে লক্ষণীয়, প্রয়োজন এর অতিরিক্ত কোন রো বা কলাম সিলেক্ট করা যাবে না। এরপর Copy to এর বক্সে  যেখান থেকে কপি শুরু করতে চান সেই সেল টি সিলেক্ট করুন ( আমি M9 সেলটি সিলেক্ট করলাম)। এবার OK ক্লিক করুন।

এখন নিচের চিত্রের মত আমরা ফলাফল পাবো। এখন আমাদের ডাটাবেজ এর সাথে শর্ত অনুযায়ী প্রাপ্ত ফলাফল ঠিক আছে কিনা আপনারা মিলিয়ে নিন।

আজ এই পর্যন্তই। কারো বুজতে সমস্যা হলে কমেন্ট এ অবশ্যই জানাবেন। সবাই ভালো থাকবেন।

----------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১৩] :: Conditional Formatting

আশা করি সবাই ভালো আছেন ও গত পর্বটি বুঝতে পেরেছেন। আজ আমরা শিখবো Conditional Formatting । এর মাধ্যমে ডাটাবেজকে আরো বেশি আকর্ষণীয় ও সহজভাবে উপস্থাপন করা যায় । নির্ভুলভাবে ডাটা লিখার ক্ষেত্রেও Conditional Formatting সহযোগিতা করে থাকে। তো আসুন দেখি

উদাহরণ ১

নিচের চিত্রের মত কিছু ডাটা টাইপ করি। ডাটাগুলো সিলেক্ট করে Home ট্যাব এর Styles গ্রুপ থেকে Conditional Formatting এ ক্লিক করি। এরপর যে ড্রপ ডাউন লিস্ট আসবে সেখান থেকে চিত্রের মত Highlight Cells Rules থেকে Duplicates Values  এ ক্লিক করুন।

এখন নিচের চিত্রের মত একটি ডায়ালগ বক্স আসবে সেখানে OK তে ক্লিক করলে আমরা দেখতে পাবো একই ডাটা আমরা দুইবার লিখেছি। অর্থাৎ এর মাধ্যমে ডুপ্লিকেট ডাটা বের করা যায় যা আমাদের সঠিকভাবে ডাটা এন্ট্রিতে সহায়ক হবে। আপনি চাইলে values with এর ড্রপডাউন লিস্ট থেকে সেল বা ফ্রন্ট এর বিভিন্ন কালার দিতে পারেন।

উদাহরণ ২

নিচের চিত্রের মত কিছু ডাটা টাইপ করি। Conditional Formatting এ ক্লিক করে যে ড্রপ ডাউন লিস্ট আসবে সেখান থেকে New rule এ ক্লিক করলে নিচের ছবির মত যে ডায়ালগ বক্স আসবে সেখান থেকে Select a Rule Type থেকে Format only cells that contain সিলেক্ট করুন। এরপর চিত্রের মত Format only cells with এর ২য় বক্স এ ড্রপ ডাউন লিস্ট থেকে not between সিলেক্ট করুন। ৩য় ও ৪ নং বক্সে যথাক্রমে ০ ও ১০০ লিখি (অর্থাৎ আমরা একটি সাবজেক্ট এর মার্ক লিখছি। এখানে ০ থেকে ১০০ এর কম বেশি পাওয়ার কোন সুযোগ নেই। যদি কোন কারনে এর বাইরের ডাটা লিখা থাকে তাহলে সেটা অবশ্যই  ভুলবশত হয়েছে) । এবার Format এ ক্লিক করুন।

এখন নিচের চিত্রের মত একটি ডায়ালগ বক্স আসবে। এখান থেকে color এর বক্স এর ড্রপডাউন লিস্ট থেকে যে কোন একটি কালার সিলেক্ট করুন। আমি লাল রঙ সিলেক্ট করলাম। এবার OK তে ক্লিক করুন।

প্রথম ডায়ালগ বক্স থেকে ও OK ক্লিক করুন। তাহলে নিচের চিত্রের মত দুইটি ভুল ধরা পড়েছে।  যা লাল কালারে দেখাচ্ছে।

উদাহরণ ৩

নিচের চিত্রের মত কিছু ডাটা টাইপ করি ও ফলাফল নির্ধারণ করি। আমি IF Function ব্যবহার করেছি। যারা বুঝতে পারেননি এই টিউনটি দেখে নিন অথবা টাইপ করে নিন। এখন আমরা চাই যারা ফেল করেছে তাদের Fail লিখাটি লাল কালারে প্রদর্শন করবে।  প্রথমে Result কলামের ডাটা সমূহ সিলেক্ট করে উদাহরণ ২ এর মত Conditional Formatting এ ক্লিক করে যে ড্রপ ডাউন লিস্ট আসবে সেখান থেকে New rule এ ক্লিক করলে নিচের ছবির মত যে ডায়ালগ বক্স আসবে সেখান থেকে Select a Rule Type থেকে Format only cells that contain সিলেক্ট করুন। এরপর নিচের চিত্রের মত প্রথম বক্সে Text সিলেক্ট করে ৩য় বক্সে Fail লিখে Format এ ক্লিক করুন।

যে ডায়ালগ বক্স আসবে তার color এর বক্স এর ড্রপডাউন লিস্ট থেকে লাল রঙ সিলেক্ট করে OK তে ক্লিক করুন। এরপর প্রথম ডায়ালগ বক্স এর ও OK ক্লিক করলে আমরা দেখবো নিচের চিত্রের মত  Fail লিখা গুলো লাল কালারে দেখাচ্ছে।

পূর্বের অবস্থায় ফিরে যেতে বা Conditional Formatting বাদ দিতে চাইলে সেল সমূহ সিলেক্ট করে Conditional Formatting>Clear Rules> Clear Rules from selected cells নির্বাচন করুন অথবা Ctrl+Z চাপুন।

উদাহরণ ৪

ধরুন আপনি একটি প্রতিষ্ঠানের  হাজিরা শীট তৈরী করবেন। অফিস টাইম হচ্ছে ৯.০০ টা থেকে ৫.০০ টা ।এখন আপনি চাচ্ছেন যারা দেরী করে অফিসে আসবে বা আগে চলে যাবে তাদের সময়ের টেক্সটা লাল কালারের হবে । হাজিরা শীটটি প্রতিদিন ইমেইল এর মাধ্যমে প্রতিষ্ঠানের চেয়ারম্যানের কাছে পাঠাতে হয় । এ কাজটি আমরা অতি সহজে করতে পারবো Conditional Formatting এর মাধ্যমে করতে পারবো।
প্রথমে নিচের মত করে একটা ডাটাবেজ তৈরী করুন

আপনি যেকোন সেলে 9.00 টাইপ করলে দেখবেন এটা 9 হয়ে গেছে কিন্তু আপনার প্রয়োজন 9.00। দশমিক এর পর দুইটা শুন্য দেয়ার জন্য আপনাকে নিচের চিত্রের মত একটি সেলে 9 লিখে In এবং Out এর সকল সেল সিলেক্ট করে Home ট্যাব এর Number গ্রুপের  নিম্নের চিত্রের লাল চিহ্নস্থানে দুটি ক্লিক করুন, তাহলে দেখবেন 9.00 হয়ে গেছে এবং বাকি সেল গুলোতে আর আপনাকে বার বার সিলেক্ট করে পরিবর্তন করতে হবে না।

০১/১০/১৪ তারিখের In এর সেলসমুহ সিলেক্ট করে (এখানে C9 থেকে C17 পর্যন্ত) Conditional Formatting এ ক্লিক করে Highlight Cells Rules থেকে Greater Than এ ক্লিক করুন। এখন যে ডায়ালগ বক্স আসবে সেখানে প্রথম বক্সে নিচের চিত্রের মত 9.00 লিখে এবং পরের বক্সে ড্রপ ডাউন লিস্ট থেকে Red Text সিলেক্ট করে OK ক্লিক করুন।

এরপর একইরকমভাবে ০১/১০/১৪ তারিখের Out এর সেলসমুহ সিলেক্ট করে (এখানে D9 থেকে D17 পর্যন্ত) Conditional Formatting এ ক্লিক করে Highlight Cells Rules থেকে Less Than এ ক্লিক করুন। এখন যে ডায়ালগ বক্স আসবে সেখানে প্রথম বক্সে  5.00 লিখে এবং পরের বক্সে ড্রপ ডাউন লিস্ট থেকে Red Text সিলেক্ট করে OK ক্লিক করুন।
এবার ০১/১০/১৪ তারিখের In এর সেলসমুহ সিলেক্ট করে (এখানে C9 থেকে C17 পর্যন্ত) Home ট্যাব এর Format Painter এ ডাবল ক্লিক করে অন্যান্য তারিখের In এর সেলসমুহ সিলেক্ট করুন। তাহলে আমাদের In কলামে দেওয়া ফরমুলাটি প্রতিটা সেলে কার্যকর হবে। এরপর একইভাবে ০১/১০/১৪ তারিখের Out এর সেলসমুহ সিলেক্ট করে (এখানে D9 থেকে D17 পর্যন্ত) Format Painter এ ডাবল ক্লিক করে অন্যান্য তারিখের Out এর সেলসমুহ সিলেক্ট করুন।
এখন যেসব কর্মকর্তা/ কর্মচারী অফিসে দেরি করে আসবে এবং যারা অফিস টাইম শেষ হবার আগেই চলে যাবে লাল কালিতে তাদের সময় লিখা থাকবে।

আজ এই পর্যন্তই। সবাই ভালো থাকবেন। আমার জন্য দোয়া করবেন।



----------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১৪] :: IF এর সাথে AND ও OR ফাংশন

আসসালামু আলাইকুম। এই পর্বে আমরা AND ও OR ফাংশন এর ব্যবহার দেখবো। AND, IF, OR এইগুলো হল লজিকাল ফাংশন অর্থাৎ কোন শর্তের ভিত্তিতে ফলাফল প্রকাশ করা হয়।  মূলত IF  ফাংশনের সাথেই AND ও OR ফাংশন ব্যবহৃত হয়। যারা IF ফাংশন নিয়ে পূর্বে কাজ করেননি তারা  আসুন শিখি Advanced Microsoft Excel [পর্ব-০৫]:: IF Function এর ব্যবহার এই টিউনটি দেখে আসুন।
সহজে বুঝার জন্য একটা উদাহরণ দেই-
আবুল, বাবুল এবং মোকলেস গেলে টাকাটা মহাজন দিবে। অর্থাৎ টাকাটা আনার জন্য তিনজনকেই যেতে হবে।
আবুল অথবা বাবুল অথবা মোকলেস গেলে মহাজন টাকাটা দিবে।  অর্থাৎ টাকাটা যে কোন একজন গেলেই দিবে।
তার মানে AND এর ক্ষেত্রে সবগুলো শর্তপুরন হলেই ফলাফল পাওয়া যাবে। আর OR এর ক্ষেত্রে যে কোন একটি শর্ত পুরন হলেই ফলাফল হবে। আরেকটু সহজভাবে বললে কেও যদি ৬ টি সাবজেক্ট পরিক্ষা দেয়  AND এর ক্ষেত্রে ৬ টি সাবজেক্ট এই পাশ মার্ক পেলেই সে পাশ আর অন্যদিকে OR এর ক্ষেত্রে যে কোন একটি সাবজেক্ট এ পাশ মার্ক পেলে ছাত্রটি পাশ।

উদাহরণ ১

নিচের চিত্রের মত A, B ও C কলামে কিছু ডাটা লিখি। D1 সেলে কার্সর রেখে ফর্মুলা বার এ  =IF(AND(A1>10,B1>12,C1>20),"True", "False") লিখে ইন্টার দেই। এরপর D2,D3,D4 সেলগুলো ও Fill Handle এর সাহায্যে একই ফর্মুলায় ফিল করুন(কেও না বুঝলে আমার এই টিউনটি দেখুন)। আমরা দেখতে পাচ্ছি তিনটা শর্তই যখন পুরন হচ্ছে তখন True, অন্যথায় False.

এখন আমরা E1 সেলে কার্সর রেখে ফর্মুলা বার এ =IF(OR(A1>10,B1>12,C1>20),"True","False") লিখে ইন্টার দেই। এরপর E2,E3,E4 সেলগুলো ও Fill Handle এর সাহায্যে ফিল করুন। আমরা নিচের চিত্রের মত দেখতে পাবো যে কোন একটি শর্ত পুরন হলেই True, কেবমাত্র যখন একটি শর্তও পুরন হয় না তখন False.

উদাহরণ ২

A1 থেকে A5 সেলে যথাক্রমে 50,80,120,220,5 লিখি। এখন B1 সেলে কার্সর রেখে ফর্মুলা বার এ =IF(AND(A1>10,A1>100),"True","False") লিখে ইন্টার দেই। এখন B2 থেকে B5 পর্যন্ত  Fill Handle এর সাহায্যে ফিল করুন। আবার C1 সেলেকার্সর রেখে ফর্মুলা বার এ =IF(OR(A1>10,A1>100),"True","False") লিখে ইন্টার দেই।  C2 থেকে C5 পর্যন্ত  Fill Handle এর সাহায্যে ফিল করুন। তাহলে আমরা নিচের চিত্রের মত ফলাফল পাবো।

অর্থাৎ AND এর ক্ষেত্রে দুইটি শর্তই পুরন হলে True অন্যদিকে OR এর ক্ষেত্রে যেকোন একটি শর্ত পুরন হলে True.

উদাহরণ ৩

নিচের চিত্রের মত কিছু ডাটা টাইপ করি। এটা একটা রেজাল্ট শিট। আমরা সবাই জানি পরীক্ষায় যেকোন সাবজেক্ট এ ৩৩ এর কম পেলে ফেল অন্যথায় পাশ। আমাদের শর্ত ও এটাই।
H2 সেলে কার্সর রেখে ফর্মুলা বার নিচের যেকোন একটি ফর্মুলা লিখে ইন্টার দেই। প্রথম ফর্মুলাটি IF , ২য় টি AND দ্বারা এবং শেষেরটি OR দ্বারা গঠন করা হয়েছে (স্বাভাবিক ভাবেই AND, OR দ্বারা ফর্মুলা বানালেও IF থাকবেই)।  এরপর H3,H4 সেলদ্বয়কে Fill Handle এর সাহায্যে ফিল করুন। যেই ফর্মুলাই বসান না কেন একই রেজাল্ট আসবে।
=IF(MIN(B2:G2)<33,"Fail","Pass")  [Minimum ৩৩ পেতে হবে। ৩৩ এর কম পেলে ফেল, অন্যথায় পাশ।]
=IF(AND(B2>=33,C2>=33,D2>=33,E2>=33,F2>=33,G2>=33),"Pass","Fail") [সবগুলো সাবজেক্ট এ ৩৩ এর সমান বা বেশি পেলে পাশ অন্যথায় ফেল ]
=IF(OR(B2<33,C2<33,D2<33,E2<33,F2<33,G2<33),"Fail","Pass") [যে কোন একটা সাবজেক্ট এ ৩৩ এর কম পেলে ফেল অন্যথায় পাশ]

আশা করি সবাই পেরেছেন। আজ এই পর্যন্তই। সবাই ভালো থাকবেন।



-------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১৫] :: Result Sheet তৈরি

আসসালামু আলাইকুম। আশা করি সবাই ভালো আছেন ও গত পর্বটি ভালোভাবে আয়ত্ত করতে পেরেছেন। গত পর্বে আপনারা IF, AND, OR ফাংশন ব্যবহার করে কিভাবে নিজের প্রয়োজনমত ফর্মুলা তৈরি করতে হয় তা শিখেছেন। আজ আমরা Result Sheet তৈরি করা শিখবো।
আজকের পর্বে কিভাবে ডিভিশন পদ্ধতিতে রেজাল্ট শিট তৈরি করতে হয় তা দেখবো। এটা তুলনামুলকভাবে সহজ বিধায় প্রথমে পুরাতন পদ্ধতিটাই দেখাচ্ছি। পরবর্তী কোন এক পর্বে কিভাবে গ্রেডিং পদ্ধতিতে রেজাল্ট শিট তৈরি করতে হয় তা দেখাবো। তো আসুন কাজ শুরু করি
প্রথমে নিচের মত একটা ডাটা বেজ তৈরি করুন। যারা বেশি অলস এখান থেকে ডাউনলোড করুন।

যারা সাবজেক্ট গুলোকে চিত্রের মত Rotate করতে পারছেন না তারা সাবজেক্ট লিখা সেলসমুহ সিলেক্ট করে  Home ট্যাব এর Alignment গ্রুপ এর নিচের চিত্রের মত ab Rotate সিম্বল এর ড্রপ ডাউন লিস্ট থেকে Rotate Text Down এ ক্লিক করুন।

যদি কোন কারনে এই রোটেশন বাদ দিতে চান সেল/ সেলগুলো সিলেক্ট করে আবার Rotate Text Down এ ক্লিক করুন।

শর্তসমূহ:

পরীক্ষার রেজাল্ট শিট তৈরি করার শর্ত সমূহ যদি ও আমরা সবাই জানি তথাপি বলে নিচ্ছি
- প্রতিটা বিষয়ে পাশ মার্ক ৩৩। যে কোন একটি বিষয়ে ও যদি ৩৩ এর কম মার্ক পায় তাহলে সে ফেল হিসাবে গণ্য হবে। কেবলমাত্র অপশনাল সাবজেক্ট Agriculture  এ ৩৩ এর কম পেলেও কোন অসুবিধা নাই।
- অপশনাল সাবজেক্ট Agriculture এ কেও যদি ৪০ এর বেশি মার্ক পায় সেই অবশিষ্ট মার্ক মোট মার্ক এর সাথে যোগ হবে।
- মার্ক ৭৫০ বা এর বেশি হলে STAR
-মার্ক ৬০০-৭৪৯ এর মধ্যে হলে First Division
-মার্ক ৪৫০-৫৯৯ এর মধে হলে Second Division
-মার্ক ৩৩০-৪৪৯ এর মধ্যে হলে Third Division
এখন প্রথম ছাত্রের মোট মার্ক বের করার জন্য N2 সেলে কার্সর রেখে ফর্মুলা বার এ =SUM(C2+D2+E2+F2+G2+H2+I2+J2+K2+L2+IF(M2>40,M2-40,0)) লিখে ইন্টার দেই। বাকিদের মোট মার্ক বের করার জন্য Fill অপশন এর সহায়তা নিন। 

প্রথম ছাত্রের রেজাল্ট বের করার জন্য O2 সেলে কার্সর রেখে ফর্মুলা বার এ                                                                                             =IF(MIN(C2:L2)<33,"Fail",IF(N2>=750,"STAR",IF(N2>=600,"First Division",IF(N2>=450,"Second Division","Third Division")))) লিখে ইন্টার দেই। অন্যান্য স্টুডেন্টদের রেজাল্ট বের করার জন্য Fill অপশন এর সহায়তা নেই। তাহলেই নিচের চিত্রের মত আমরা একটা রেজাল্ট শিট পেয়ে যাবো।

সবার কাছ থেকে বিদায় নিচ্ছি। খোদা হাফেজ।



---------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১৬] :: Formula Hide, Data/Worksheet/Workbook পাসওয়ার্ড দিয়ে Protect করুন (মেগা টিউন)

আসসালামু আলাইকুম। আশা করি সবাই ভালো আছেন ও গত পর্বটি ভালোভাবে বুঝতে পেরেছেন। গত পর্বে আমরা কিভাবে রেজাল্ট শিট তৈরি করতে হয় শিখেছিলাম। আজকের পর্বে আমরা ডাটা প্রোটেকশন ও ফর্মুলা হাইড কিভাবে করতে হয় তা শিখবো।

ফর্মুলা হাইড ( Formula Hide)

প্রথমে একটি এক্সেল শিট ওপেন করে কিছু ডাটা লিখুন ও ফর্মুলা দিন। আমি এখানে গত পর্বে করা রেজাল্ট শিটটি ওপেন করলাম। আমরা Total ও Result এই দুইটি কলামে ফর্মুলা দিয়েছিলাম। আমরা চাই এই ফর্মুলা যেন কেও দেখতে না পারে।
যেইসব সেল এর ফর্মুলা হাইড করতে চান, সেইসব সেল সিলেক্ট করে মাউসের রাইট বাটন ক্লিক করে Format Cells সিলেক্ট করুন ( অথবা Ctrl+Shift+F চাপুন)।

নিচের চিত্রের মত একটি Window আসবে। উপরের মেনুবার থেকে Protection এ সিলেক্ট করুন। তারপর দেখতে পাবেন Locked এর বামপাশের বক্সে টিক চিহ্ন দেওয়া আছে। চিত্রের মত Locked এর বাম পাশের বক্সে টিক চিহ্ন উঠিয়ে দিয়ে Hidden এর বাম পাশের বক্সে টিক চিহ্ন দিন ( ক্লিক করলেই হবে)। এবার OK ক্লিক করুন।

এখন নিচের চিত্রের মত  Review ট্যাব এর Protect Sheet এ ক্লিক করুন। দেখবেন Protect Sheet নামে একটি ডায়ালগ বক্স আসবে সেখানে আপনার ইচ্ছামত পাসওয়ার্ড দিন এবং OK ক্লিক করুন। এখন Confirmed Password নামে আরেকটি ডায়ালগ বক্স আসবে। একই পাসওয়ার্ড দিয়ে আবার Ok ক্লিক করুন।

এখন সেল সিলেক্ট করলেও ফর্মুলা বার এ ফর্মুলা দেখাবে না। অর্থাৎ কেও জানতে পারবে না আপনি এই সেল সমুহে কি ফর্মুলা দিয়েছেন। যে কেও ইনপুট চেঞ্জ করলে অটো আউটপুট পাবে। কিন্তু কোন ফর্মুলায় কাজটি হল কেও জানতে পারবে না। এটা সাধারণত বড় বড় কোম্পানির উদ্ধতন কর্মকর্তাবৃন্দ ব্যবহার করে থাকেন যাতে নতুন জয়েন করা/ জুনিয়র কর্মকর্তা, কর্মচারী ফর্মুলা না জানতে পারে।
আপনি যদি পূর্বের অবস্থায় ফিরে যেতে চান Review ট্যাব থেকে Unprotect Sheet এ ক্লিক করুন। যে ডায়ালগ বক্স আসবে তাতে পূর্বের দেওয়া পাসওয়ার্ড টি দিয়ে OK ক্লিক করুন। এখন আবার পূর্বের মত ফর্মুলাগুলো দেখা যাবে।

Worksheet Protection

একটি সম্পূর্ণ ওয়ার্কশিট প্রটেক্ট করতে চাইলে Ctrl+A চেপে অথবা নিচের চিত্রের লাল কালিতে চিহ্নিত স্থানে ক্লিক করে পুরো ওয়ার্কশিটটি সিলেক্ট করুন।এরপর  Review ট্যাব এর Protect Sheet এ ক্লিক করুন। যে ডায়ালগ বক্স আসবে তাতে নিজের ইচ্ছামত Password দিয়ে Ok ক্লিক করুন। এখন Confirm Password নামে আরেকটি ডায়ালগ বক্স আসবে একই Password আরেকবার দিয়ে OK ক্লিক করুন।

এখন অন্য কেও এই শিট এ কোন কিছু লিখতে বা এডিট করতে পারবে না। লিখতে গেলেই নিচের ছবির মত একটি ডায়ালগ বক্স এসে জানান দিবে এই শিটটি প্রটেক্ট করা আছে। আপনি শুধু মাত্র দেখতে পারবেন এর বেশি কিছু নয়।

আপনি যদি পূর্বের অবস্থায় ফিরে যেতে চান অর্থাৎ শিটটি Unprotect করতে চান তাহলে  Review ট্যাব থেকে Unprotect Sheet এ ক্লিক করুন। যে ডায়ালগ বক্স আসবে তাতে পূর্বের দেওয়া পাসওয়ার্ড টি দিয়ে OK ক্লিক করুন।

Data Protection

এখন আমরা চাচ্ছি একটা ওয়ার্কশিটের কিছু অংশ বা কিছু সেলকে প্রটেক্ট করতে। যেমন নিচের চিত্রের মত যেসব সেল আমরা প্রটেক্ট করতে চাই কিছু সেল সিলেক্ট করি (কিবোর্ড থেকে  Ctrl চেপে থাকা অবস্থায় মাউস দিয়ে সেলসমুহ সিলেক্ট করুন যদি একই রো বা কলামে না হয়)। সেল সমূহ প্রটেক্ট করতে হল এই জন্য যে এই সেল সমূহ যেন কেও এডিট করতে না পারে  অর্থাৎ সেলসমুহের লিখা সবসময় একই থাকবে। সেল ভলিউম, প্রফিট এইসব চেঞ্জ হবে।  এখন পূর্বের মত Review ট্যাব এর Protect Sheet এ ক্লিক করুন। যে ডায়ালগ বক্স আসবে তাতে নিজের ইচ্ছামত Password দিয়ে Ok ক্লিক করুন। এখন Confirm Password নামে আরেকটি ডায়ালগ বক্স আসবে একই Password আরেকবার দিয়ে OK ক্লিক করুন।
আপনি যদি এমন চান যে ওয়ার্কশিটের মাত্র কয়েকটি সেলে User Access দিবেন।  অর্থাৎ  যে কেও পুরো ওয়ার্কশিটের মাত্র কয়েকটি সেলে লিখার সুযোগ পাবে। অন্য কোথাও কিছু লিখতে বা এডিট করতে পারবে না।  তাহলে নিচের চিত্রের মত যেই সেল গুলতে User Access দিতে চান তা সিলেক্ট করে মাউসের রাইট বাটন ক্লিক করে Format Cells সিলেক্ট করি ( অথবা Ctrl+Shift+F চাপুন)। যে Window আসবে তার মেনুবার থেকে Protection সিলেক্ট করুন। চিত্রের মত Locked এর বাম পাশের বক্সে টিক চিহ্ন উঠিয়ে দিয়ে OK ক্লিক করুন।

এবার Review ট্যাব এর Protect Sheet এ ক্লিক করুন। যে ডায়ালগ বক্স আসবে তাতে নিজের ইচ্ছামত Password দিয়ে Ok ক্লিক করুন। এখন Confirm Password নামে আরেকটি ডায়ালগ বক্স আসবে একই Password আরেকবার দিয়ে OK ক্লিক করুন। এখন User শুধুমাত্র ওই চারটি সেলেই ডাটা ইনপুট দিতে পারবে। অন্য কোথাও কিছু লিখতে পারবে না।

Workbook Protection

১ম পদ্ধতি: আপনার যে কোন একটি সেভ করা excel ফাইল ওপেন করুন। নিচের চিত্রের মত Office Button এ ক্লিক করুন। Prepare থেকে Encrypt Document এ ক্লিক করুন।

এখন যে ডায়ালগ বক্স আসবে তাতে আপনার ইচ্ছামত Password দিয়ে Ok ক্লিক করুন। এরপর Confirm Password নামে আরেকটি ডায়ালগ বক্স আসবে সেই একই পাসওয়ার্ড এখানে আবার লিখে Ok ক্লিক করুন। এবার Ctrl+S চেপে সেইভ করে নিন।
এখন যে কেও এমনকি আপনি ও যদি এই ফাইলটি খুলতে চান Password চাইবে।  এই Workbook টি পাসওয়ার্ড মুক্ত করতে হলে Workbook ওপেন করে( অবশ্যই পাসওয়ার্ড দিয়ে)  Office Button>Prepare> Encrypt Document এ গিয়ে ডায়ালগ বক্স থেকে পূর্বের পাসওয়ার্ড মুছে দিয়ে Ok ক্লিক করুন। এরপর Ctrl+S চেপে সেভ করে নিন।
২য় পদ্ধতি:  আপনার যে কোন একটি সেভ করা excel ফাইল ওপেন করুন।  Office Button থেকে Save As এ ক্লিক করুন। Tools এর ড্রপডাউন লিস্ট থেকে General Option সিলেক্ট করুন। চিত্রের মত একটি ডায়ালগ বক্স আসবে।

দেখবেন দুইটি পাসওয়ার্ড দেওয়ার অপশন রয়েছে।  প্রথমটি দিয়ে ওয়ার্কবুক ওপেন করতে পারবে কিন্তু কোন এডিট করে সেভ করতে পারবে না  (তবে অন্য নামে Save As করতে পারবে)। ২য় পাসওয়ার্ড টি দিলে User প্রয়োজনীয় এডিট ও করতে পারবে।  আপনি চাইলে যে কোন একটি পাসওয়ার্ড ও দিতে পারেন।
এখন General Option ডায়ালগ বক্সে  Password to Open এর বক্সে যে পাসওয়ার্ড দিয়ে ওয়ার্কবুক টি ওপেন করতে হবে সেইটা দিন।  Passwoard to Modify এর বক্সে  মডিফাই করার জন্য আরেকটি পাসওয়ার্ড দিন। এবার Ok ক্লিক করুন। এখন পাসওয়ার্ড কনফার্ম এর জন্নে যে ডায়ালগ বক্স আসবে তাতে প্রথম পাসওয়ার্ড দিয়ে Ok ক্লিক করুন। পরের ডায়ালগ বক্সে ২য় পাসওয়ার্ড দিয়ে Ok ক্লিক করুন।

এখন উপরের চিত্রের মত Save As ডায়ালগ বক্সের Save এ ক্লিক করুন। এরপর যে ডায়ালগ বক্স আসবে তাতে Yes এ ক্লিক করুন। আমাদের কাজ শেষ 😆
আশা করি সবাই বুঝতে পেরেছেন এবং করতে সক্ষম হয়েছেন। না বুঝলে অবশ্যই কমেন্টস করে জানাবেন।
আজ এই পর্যন্তই। সবাই ভালো থাকবেন।



-------------------------------------------------------------------------------------------------------------------

• আসুন শিখি Advanced Microsoft Excel [পর্ব-১৭] :: কিছু গুরুত্বপূর্ণ টিপস

আসসালামু আলাইকুম। আশা করি  সবাই ভালো আছেন।  আজকে আমরা কিছু টিপস সম্বন্ধে জানবো যার মাধ্যমে আমরা আরও সহজভাবে দ্রুত সময়ে সঠিকভাবে এক্সেল শিটে কাজ করতে পারবো।

টিপস-১ঃ  আমরা অনেকেই বিভিন্নভাবে মাইক্রোসফট এক্সেল পোগ্রামটি ওপেন করি।  শর্টকাটে ওপেন করার জন্য রান কমান্ড চালু করুন অর্থাৎ কিবোর্ড এর উইন্ডোজ ও R  বাটন দুটি একসাথে চাপুন। এরপর excel  টাইপ করে ইন্টার দিন।

মাইক্রোসফট এক্সেল পোগ্রামটি বন্ধ করার জন্য Alt+F4 চাপুন (অর্থাৎ কিবোর্ড থেকে  Alt ও F4 কি দুটি একসাথে চাপুন)।

টিপস-২ঃ  কোন একটি সেলে কিছু লিখতে বা এডিট করতে চাইলে সেলটি সিলেক্ট করে আমরা সাধারণত ডাবল ক্লিক করি। এর পরিবর্তে সেলটি সিলেক্ট করে F2 চেপে ও কাজটি করা যায়।

টিপস-৩ঃ  যদি আপনি পিসিতে কাজ করে থাকেন কোন কারনে আপনার কাছে মাউস না থাকে বা নষ্ট হয়ে গিয়ে থাকে তাহলে কি করবেন। আপনার তো আর সব শর্টকার্ট মুখস্ত নেই। কিভাবে কাজ করবেন। চিন্তার কিছু নেই।  টিপস- ১ অনুযায়ী এক্সেল শিট ওপেন করুন। এরপর Alt চাপুন। দেখবেন  শর্টকার্ট নির্দেশ করবে। এখন নির্দেশনা  অনুযায়ী আপনার কাজ সম্পাদন করুন। যেমন Alt+N চাপলে Insert ট্যাব ওপেন হবে। এরপর Alt+T চাপলে table এ কাজ করা যাবে।

টিপস- ৪ঃ  Ctrl+; (সেমিকোলন) চাপলে আজকের তারিখ দেখাবে।  Ctrl+Shift+;  চাপলে এখনকার সময় দেখাবে।  আপনি যদি সময় বা তারিখের ফরম্যাট পরিবর্তন করতে চান তাহলে ওই সেল সিলেক্ট করে মাউসের রাইট বাটন ক্লিক করে Format Cells এ ক্লিক করুন। ২ নং চিত্রে দেখুন বেশকিছু টাইপ আছে তার মধ্যে থেকে একটি সিলেক্ট করে Ok ক্লিক করুন।

যদি আপনি নিজের মত করে ফরম্যাট তৈরি করতে চান তাহলে নিচের চিত্রের মত Custom এ ক্লিক করুন।  নিচের চিত্রের মত করে Type এর বক্সে আপনার নিজের ফরম্যাট টাইপ করুন। যেমন আমি dd/mm/yy লিখলাম। আউটপুট কি হবে তা Sample এ দেখাবে। এবার Ok ক্লিক করুন। এখন আজকের তারিখটি 23/01/15 এভাবে দেখাবে।  যদি আপনি ddd-dd-mmm-mm-yyy টাইপ করেন তাহলে Fri-23-jan-01-2015 এই ফরম্যাটে দেখাবে।

আশা করছি আপনি এখন নিজের মত করে প্রয়োজনীয় ফরম্যাটটি তৈরি করে নিতে পারবেন।

টিপস- ৫ঃ  আপনি যদি কোন মোবাইল নাম্বার লিখেন যেমন ০১৭১১৪২০৪২০। দেখবেন অন্য সেলে কার্সর নেওয়ার সাথে সাথেই প্রথম ডিজিট টি দেখা যাচ্ছে না।  ক্রমিক নাম্বার যেমন ০১, ০২ এইসব লিখতে গেলে ও একই ঘটনা ঘটবে। অর্থাৎ প্রথম শূন্যটি দেখাবে না। এর কারন হল প্রোগ্রামটি এতাকে নাম্বার ফরম্যাট এ দেখাচ্ছে। এ থেকে পরিত্রানের জন্য এটাকে টেক্সট ফরম্যাটে দেখাতে হবে। শর্টকাটে এটা করার জন্য প্রথমে শুন্য আছে এইরকম কিছু লিখতে হলে  ওই সেলে প্রথমে  Apostrophe symbol  (বাংলায় যাকে ইলেক বা লোপ চিহ্ন বলে। যা সাধারণত ইন্টার কি এর বাম পাশের বাটনে থাকে) দিয়ে তারপর সংখ্যাটি লিখুন।  অর্থাৎ '০১৭১১৪২০৪২০ লিখুন।

টিপস- ৬ঃ ধরুন আমরা যেকোন সেলে একটি ডাটা লিখলাম। এই ডাটাটি কি জন্যে লিখলাম বা কোথা থেকে পেলাম তা অন্য কোন ভিজিটর এমনকি পরবর্তীতে নিজে বুঝার জন্যে ও একটি টিউমেন্টস বা শর্ট নোট যুক্ত করে দিলে সুবিধা হয়। এমনটি করার জন্য সেলটি সিলেক্ট করে মাউসের রাইট বাটন ক্লিক করে Insert comment সিলেক্ট করুন। দেখবেন একটি বক্স এসেছে এতে আপনি আপনার টিউমেন্টস লিখুন। এর মাধ্যমে পরবর্তীতে ডাটাটি কি জন্যে লিখেছেন বুঝতে সুবিধা হবে।

টিপস- ৭ঃ বিগত পর্বগুলোতে আমি লিখেছি ফর্মুলা বার এ ফর্মুলা লিখে ইন্টার দিন। যেমন ৬ তম পর্বে salary sheet তৈরি করার সময় আমি লিখেছিলাম ফর্মুলা বার এ =(C2+D2+E2+F2)-(G2+H2+I2) লিখে ইন্টার দিন। বাস্তবতা হল যারা প্রফেশনালি কাজ করে তারা কখনও সেল এড্রেস  লিখেন না। তার পরিবর্তে Ctrl কি চেপে ওই সেলটি সিলেক্ট করে। এতে যেমনি সময় বাঁচে তেমনি ভুল হবার সম্ভাবনা কম থাকে।

টিপস- ৮ঃ F5 কী চাপলে একটা Go To ডায়ালগ বক্স আসবে। এতে আপনি যে কোন সেল এড্রেস লিখে ইন্টার চাপলে কার্সর ওই সেলে চলে যাবে। নেম ডিফাইন করা থাকলে ওগুলো দেখাবে। প্রয়োজনীয়টি সিলেক্ট করে ইন্টার দিলে নেম ডিফাইন করা ডাটাগুলো যেখানে আছে সেখানে চলে যাবে যেই ওয়ার্কশিটেই থাকুক না কেনও।

টিপস- ৯ঃ একটি সেল সিলেক্ট করে Shift চেপে শেষের সেলটি সিলেক্ট করলে মাঝখানের সবগুলো সেল সিলেক্ট হয়ে যাবে। যেমন আপনারা B2 সেল সিলেক্ট করে  Shift চেপে ধরে F6 সেল সিলেক্ট করে দেখুন। আশা করি বুঝতে পেরেছেন।

আমরা ওয়ার্কশীটের যেই প্রান্তেই কাজ করি না কেন Ctrl+Home চাপলে A1 সেলে কার্সর চলে যাবে। বড় ডাটা নিয়ে কাজ করতে গেলে এটি জানা আবশ্যক।

টিপস- ১০ঃ  নিচের চিত্রের দিকে লক্ষ্য করুন। আমরা ডাটা লিখা সেল সমূহ রিসাইজ করতে চাই। অর্থাৎ যতটুকু জায়গা দখল করেছে তার থেকে বেশি যাতে কোন কলামে ফাঁকা জায়গা না থাকে। সাধারণত আমরা তা প্রতিটা কলামের উপরে কার্সর নিয়ে গিয়ে মাউস দিয়ে ড্রাগ করে করি, যা সময় সাপেক্ষ। আমরা সহজে নিচের চিত্রের মত দুটি কলামের সংযোগকারী লাইনে ডাবল ক্লিক করে করতে পারি।

যদি আমরা পুরো শিটের সেলগুলোর Height, Length বাড়াতে বা কমাতে  চাই। তাহলে প্রথমে  Ctrl+A চেপে পুরো শিটটি সিলেক্ট করুন। অন্যভাবে করতে চাইলে উপরের চিত্রের সবুজ কালারের বৃত্তে ক্লিক করুন। এখন যেকোনো একটি কলামের Length বাড়ালে/কমালে সবগুলো সেলের Length বাড়বে/কমবে। একইভাবে  যেকোনো একটি রো এর Height বাড়ালে/কমালে সকল সেল এর Height বাড়বে/ কমবে।

একইকাজ আমরা কিছু নির্দিষ্ট সেল এর ক্ষেত্রে ও করতে পারি। কাংখিত সেলসমুহ সিলেক্ট করে যেকোনো একটি কলামের Length বা Height বাড়ালে/কমালে সবগুলো সেলের Length বা Height বাড়বে/কমবে।

আজ এই পর্যন্তই। আশা করি আপনারা বুঝতে পেরেছেন। এর মধ্যে অনেকগুলোই আপনারা আগেই জানতেন। আগামী পর্বে আরও কিছু টিপস নিয়ে হাজির হব।   সবাই ভালো থাকবেন।

====================================================================

credit : https://www.facebook.com/bdibrahim