আজ আমি আপনাকে এক্সেলের OFFSET ফাংশন-এর সাথে ৩টি বাস্তব উদাহরণ দিয়ে পরিচয় করাতে চাই।
প্রথমে, আমি ফর্মুলার সিনট্যাক্স বর্ণনা করব এবং তারপর OFFSET ফাংশন কীভাবে বাস্তব জীবনের সমস্যাগুলো সমাধান করতে পারে তা আলোচনা করব।
ভূমিকা
OFFSET ফাংশন একটি সেল (চলুন এটাকে টার্গেট সেল বলি) বা রেঞ্জ (টার্গেট রেঞ্জ) এর রেফারেন্স রিটার্ন করতে পারে যা একটি নির্দিষ্ট সংখ্যক সারি ও কলাম দূরত্বে অবস্থিত অন্য একটি সেল (রেফারেন্স সেল) বা রেঞ্জ (রেফারেন্স রেঞ্জ) থেকে।
নিচের চিত্রটি দেখাচ্ছে কীভাবে OFFSET ফাংশন ব্যবহার করে একটি সেলের রেফারেন্স (বাম অংশ) বা একটি রেঞ্জের রেফারেন্স (ডান অংশ) রিটার্ন করা যায়।
এটি আপনাকে একটি স্বाभাবিক ধারণা দেবে যে টার্গেট সেল কী এবং রেফারেন্স সেল কী।
হরিত রঙে হাইলাইট করা সেলটি হলো টার্গেট সেল, এবং হলদে হাইলাইট করা সেলগুলো मिलে একটি টার্গেট রেঞ্জ তৈরি করে।
নীল রঙে হাইলাইট করা সেলগুলো হলো রেফারেন্স সেল।
চিত্র ১
এক্সেলে OFFSET কী অর্থ (সিনট্যাক্স)?
Offset ফাংশনের সিনট্যাক্স হলো: OFFSET (reference, rows, cols, [height], [width])
| Reference | আবশ্যক। রেফারেন্স হলো একটি সেল বা সেলের রেঞ্জ যেখান থেকে অফসেট শুরু হয়। দয়া করে নোট করুন যে সেলগুলো একে অপরের পাশে থাকতে হবে যদি আপনি সেলের একটি রেঞ্জ নির্দিষ্ট করেন। |
| Rows | আবশ্যক। রেফারেন্স সেল বা রেফারেন্স রেঞ্জের উপরের বাম সেল থেকে উপরে বা নিচে কতগুলো সারি। Rows পজিটিভ বা নেগেটিভ হতে পারে। চিত্র ১-এর বাম অংশটি দেখুন, যদি আমি ফাংশনটি OFFSET (C3, -1, -1) হিসেবে পরিবর্তন করি, তবে টার্গেট সেল হবে B2। B2, C3-র থেকে এক সারি উপরে। |
| Cols | আবশ্যক। রেফারেন্স সেল বা রেফারেন্স রেঞ্জের উপরের বাম সেল থেকে বামে বা ডানে কতগুলো কলাম। Rows আর্গুমেন্টের মতো, Cols-এর মানও পজিটিভ এবং নেগেটিভ উভয় হতে পারে। B4 কে রেফারেন্স সেল এবং C3 কে টার্গেট সেল সেট করলে OFFSET ফাংশনটি কীভাবে লিখব? উত্তর হলো OFFSET (B4, -1, 1)। এখানে আপনি দেখতে পাচ্ছেন Cols পজিটিভ এবং C3, B4-র থেকে এক কলাম ডানে। |
| Height | ঐচ্ছিক। শুধু তখন Height আর্গুমেন্ট ব্যবহার করুন যদি টার্গেট একটি রেঞ্জ হয়। এটি বলে টার্গেট রেঞ্জে কতগুলো সারি আছে। Height অবশ্যই পজিটিভ সংখ্যা হতে হবে। চিত্র ১-এর ডান অংশ থেকে দেখতে পাচ্ছেন টার্গেট রেঞ্জে দুটি সারি আছে। তাই সেটা কেসে Height ২ সেট করা হয়েছে। |
| Width | ঐচ্ছিক। শুধু তখন Width আর্গুমেন্ট ব্যবহার করুন যদি টার্গেট একটি রেঞ্জ হয় (চিত্র ১-এর ডান অংশ দেখুন)। এটি নির্দেশ করে টার্গেট রেঞ্জে কতগুলো কলাম আছে। Width অবশ্যই পজিটিভ সংখ্যা হতে হবে। |
ভালো, এখন আমি আপনাকে দেখাব কীভাবে OFFSET ফাংশন ব্যবহার করে বাস্তব জীবনের সমস্যাগুলো সমাধান করা যায়।
কেস ১: OFFSET এবং MATCH ফাংশন কম্বাইন করে রাইট-টু-লেফট লুকআপ
সব জানেন যে VLOOKUP ফাংশন দিয়ে শুধু বাম থেকে ডান দিকে লুকআপ করা যায়।
সার্চ করার মানটি আপনার টেবিল অ্যারেয়ের প্রথম কলামে থাকতে হবে।
আপনাকে পুরো টেবিল রেঞ্জটি এক কলাম ডানে শিফট করতে হবে যদি আপনি নতুন লুকআপ ভ্যালু যোগ করতে চান অথবা আপনার ডেটা স্ট্রাকচার পরিবর্তন করতে হবে যদি আপনি অন্য কোনো কলামকে লুকআপ ভ্যালু হিসেবে ব্যবহার করতে চান।
কিন্তু OFFSET এবং MATCH ফাংশন কম্বাইন করে VLOOKUP ফাংশনের এই সীমাবদ্ধতা দূর করা যায়।
MATCH ফাংশন কী এবং কীভাবে MATCH ফাংশনকে OFFSET ফাংশনের সাথে কম্বাইন করে লুকআপ করা যায়?
MATCH ফাংশন একটি নির্দিষ্ট আইটেমকে সেলের একটি রেঞ্জে সার্চ করে এবং তারপর সেই আইটেমের রিলেটিভ পজিশন রিটার্ন করে।
চিত্র ২.১-এর রেঞ্জ B3:B8 (যে বিভিন্ন বছরে বিভিন্ন দেশের রেভিনিউ দেখাচ্ছে) কে উদাহরণ হিসেবে নিই।
ফর্মুলা =MATCH ("USA", B3:B8, 0) রিটার্ন করবে ১ কারণ USA রেঞ্জের প্রথম আইটেম (সেল B10 এবং C10 দেখুন)।
অন্য রেঞ্জ C2:F2-এর জন্য, ফর্মুলা =MATCH (2015, C2:F2, 0) রিটার্ন করবে ৩ কারণ 2015 রেঞ্জের তৃতীয় আইটেম (সেল B11 এবং C11 দেখুন)।
আবার OFFSET ফাংশনে ফিরে যাই।
যদি আমরা সেল B2 কে রেফারেন্স সেল এবং সেল E3 কে টার্গেট সেল সেট করি, তবে OFFSET ফর্মুলা কীভাবে লিখব?
E3, B2-র থেকে ১ সারি নিচে এবং ৩ কলাম ডানে।
তাই ফর্মুলা লিখা যাবে =OFFSET(B2, 1, 3)। লাল রঙের সংখ্যাগুলো গৌরবদৃষ্টিতে দেখুন, কি আপনি খেয়াল করেছেন যে সেগুলো ম্যাচ হয়েছে?
এটিই হলো প্রশ্নের উত্তর - OFFSET ফাংশনকে MATCH ফাংশনের সাথে কীভাবে কম্বাইন করব - MATCH ফাংশনকে OFFSET ফাংশনের দ্বিতীয় বা তৃতীয় আর্গুমেন্ট হিসেবে ব্যবহার করা যায় (সেল C13 দেখুন)।
সেল C14 দেখাচ্ছে কীভাবে VLOOKUP ফাংশন ব্যবহার করে একই ডেটা রিট্রিভ করা যায়।
VLOOKUP ফাংশন লিখার আগে আমাদের জানতে হয় 2015 সালের রেভিনিউ টেবিল অ্যারে B2:F8-র ৪র্থ কলামে রেকর্ড করা আছে।
এটি মানে VLOOKUP ফাংশন ব্যবহার করার সময় আমাদের ডেটা স্ট্রাকচার খুব ভালোভাবে জানতে হয়।
এটি VLOOKUP-এর আরেকটি সীমাবদ্ধতা। কিন্তু MATCH ফাংশনকে OFFSET ফাংশনের আর্গুমেন্ট হিসেবে ব্যবহার করলে, আমাদের কলাম ইন্ডেক্স জানার দরকার নেই।
যদি অনেক কলাম থাকে তবে এটি খুব উপকারী।
চিত্র ২.১
এখন আরও একটি জটিল উদাহরণ দেখি।
ধরুন, আমাদের একটি টেবিল আছে যাতে কোম্পানির নাম, যোগাযোগের নাম, এবং ইমেইল অ্যাড্রেস আছে বিভিন্ন কোম্পানির জন্য।
আমরা জানা যোগাযোগের নাম থেকে কোম্পানির নাম বের করতে চাই বা জানা ইমেইল অ্যাড্রেস থেকে যোগাযোগের নাম বের করতে চাই। কী করব?
চিত্র ২.২ দেখুন, রেঞ্জ B5:E8 কোম্পানির তথ্য ধারণ করছে। সেল C2 এবং সেল B3-এ ইনপুট দিয়ে, লাল বাক্সের ফর্মুলার সাহায্যে, আমি যোগাযোগের নাম জানলে কোম্পানির নাম বের করতে পারি।
রেঞ্জ D2:E4 দেখাচ্ছে কীভাবে জানা ইমেইল অ্যাড্রেস থেকে যোগাযোগের নাম বের করা যায়।
সংক্ষেপে, এই দুটি উদাহরণ নির্দেশ করে যে আমরা রাইট-টু-লেফট লুকআপ করতে পারি এবং সার্চ ভ্যালুটি রাইটমোস্ট কলামে থাকতে হয় না। টেবিল অ্যারেতে যেকোনো কলামে সার্চ ভ্যালু থাকতে পারে।
চিত্র ২.২
কেস ২: OFFSET এবং COUNT ফাংশন কম্বাইন করে অটোমেটেড ক্যালকুলেশন
কैसे একটি কলামে নতুন সংখ্যা যোগ হলে ক্যালকুলেশন অটোমেটেড হয়, তার আগে আসে কিভাবে একটি কলামের শেষ সংখ্যা অটোমেটিক্যালি রিটার্ন করব।
নিচের চিত্রটি দেখুন যা Human Resources-এর এন্ট্রি দেখাচ্ছে। ধরি, আমরা কলাম B-এর শেষ সংখ্যা পেতে চাই, OFFSET ফাংশন প্রয়োগ করলে ফর্মুলা হবে =OFFSET (C2, 9, 0)।
ফর্মুলা থেকে জানা যায় ৯ হলো কী সংখ্যা।
যদি আমরা এই সংখ্যাটি অটোমেটিক্যালি রিটার্ন করতে পারি, তবে আমরা একটি কলামের শেষ সংখ্যা অটোমেটিক্যালি লোকেট করতে পারব।
৯ হলো কলাম C-এ সংখ্যা ধারণকারী সেলের সংখ্যা।
আপনি যদি COUNT ফাংশন পরিচিত হন, তবে জানেন COUNT ফাংশন একটি রেঞ্জে সংখ্যা ধারণকারী সেলের সংখ্যা গুনতে পারে।
উদাহরণস্বরূপ, ফর্মুলা =COUNT (C3:C11) সেল C3 থেকে C11-এ संख্যা ধারণকারী সেলের সংখ্যা গুনবে।
আমাদের কেসে, আমরা একটি পুরো কলামে কতগুলো সংখ্যা আছে তা জানতে চাই, তাই C:C এর মতো রেফারেন্স ব্যবহার করতে হবে যেটি কলাম C-র সব সারি অন্তর্ভুক্ত করে।
সেল G4 এবং H4 দেখুন, =COUNT(C:C) দিয়ে রিটার্ন হওয়া সংখ্যা ঠিক ৯।
তাই, উপরের OFFSET ফাংশনে ৯-এর জায়গায় COUNT(C:C) বসিয়ে আমরা একটি নতুন ফর্মুলা পাই =OFFSET (C2, COUNT(C:C), 0) (সেল H5-এ)।
এটি যে সংখ্যা রিটার্ন করে সেটি হলো 87000 যা কলাম C-এর সঠিক শেষ সংখ্যা।
এখন অটোমেটেড ক্যালকুলেশনে যাই। ধরি, আমরা কলাম C-এর সব সংখ্যার সমষ্টি চাই।
ফর্মুলা হবে =SUM (OFFSET (C2, 1, 0, 9, 1)) যদি SUM এবং OFFSET একসাথে ব্যবহার করি।
৯ হলো রেঞ্জ C3:C11-এ মোট সারির সংখ্যা এবং কলাম C-এ সংখ্যা ধারণকারী সেলের মোট সংখ্যাও।
তাই, আমরা ফর্মুলাটি নতুনভাবে লিখতে পারি =SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))।
সেল G10 এবং H10 দেখুন, এই ৯ কর্মচারির মোট বেতন $521,700।
এখন আপনি যদি সেল C12-এ $34,000 এর মতো একটি সংখ্যা লিখেন, তবে সেল G5 এবং G10-এ থাকা সংখ্যা আটোমেটিক্যালি $34,000 এবং $555,700-এ পরিবর্তন হয়ে যাবে।
এটিই আমি অটোমেশন বলছি কারণ আপনাকে সেল G5 বা G10-এ ফর্মুলা আপডেট করতে হবে না।
COUNT ফাংশন ব্যবহার করার সময় সাবধান থাকতে হবে কারণ COUNT ফাংশন শুধু সংখ্যা ধারণকারী সেলের সংখ্যা রিটার্ন করে।
উদাহরণস্বরূপ, =COUNT (B: B) রিটার্ন করে 0 বদলে ৯ কারণ কলাম B-এ কোনো সেল সংখ্যা ধারণ করে না (সেল G3 এবং H3 দেখুন)।
কলাম D-তে ১০টি সেল সংখ্যা ধারণ করে এবং COUNT (D: D) দিয়ে রিটার্ন হওয়া সংখ্যাও ১০।
কিন্তু যদি আমরা কলাম D-এর শেষ সংখ্যা বের করতে চাই যেমন কলাম C-এর জন্য করেছিলাম, তবে আমরা পাব 0 (সেল G8 এবং H8 দেখুন)।
স্পষ্টতঃ, 0 আমাদের চাওয়া নয়। কী ভুল হয়েছে? সেল D13, সেল D2-র থেকে ১১ সারি দূরত্বে আছে ১০ সারি নয়।
এটি ফর্মুলা =OFFSET (D2, COUNT (D: D) + 1, 0) দিয়ে সেল G7-এ প্রদর্শন করাও যায়।
সংক্ষেপে, যদি আমরা COUNT ফাংশন OFFSET ফাংশনের সাথে কম্বাইন করে ক্যালকুলেশন অটোমেট করতে চাই, তবে সংখ্যাগুলো একে অপরের পাশে থাকতে হবে।
চিত্র ৩
কেস ৩: ডাইনামিক রেঞ্জ তৈরি করতে OFFSET ফাংশন ব্যবহার
ধরুন, আমরা একটি কোম্পানির মাসিক ইউনিট সেলস চার্ট করতে চাই এবং চিত্র ৪.১ বর্তমান ডেটা এবং বর্তমান ডেটার উপর ভিত্তি করে তৈরি একটি চার্ট দেখাচ্ছে।
প্রতিমাস, সাম্প্রতিক মাসের ইউনিট সেলস কলাম C-এর শেষ সংখ্যার নিচে যোগ হবে।
কি চার্টটি অটোমেটিক্যালি আপডেট করার কোনো সহজ উপায় আছে?
চার্ট আপডেট করার কী হলো OFFSET ফাংশন ব্যবহার করে Units Sold কলামের জন্য ডাইনামিক রেঞ্জ نیم তৈরি করা।
ইউনিট সেলসের জন্য ডাইনামিক রেঞ্জ নতুন ডেটা এন্টার করা হলে আটোমেটিক্যালি সব সেলস ডেটা অন্তর্ভুক্ত করবে।
চিত্র ৪.১
ডাইনামিক রেঞ্জ তৈরি করতে, Formulas ট্যাবে ক্লিক করুন এবং তারপর Name Manager বা Define Name বেছে নিন।
নিচের New Name ডায়ালগ বাক্স প্রদর্শিত হবে আপনি Define Name-এ ক্লিক করলে।
আপনি যদি Name manager বেছে নেন, তবে আপনাকে New-এ ক্লিক করতে হবে নিচের New Name ডায়ালগ বাক্স প্রদর্শিত করতে।
চিত্র ৪.২
"Name:" ইনপুট বাক্সে, ডাইনামিক রেঞ্জের নাম দিতে হবে। এবং "Refers to:" ইনপুট বাক্সে, আমাদের OFFSET ফর্মুলা =OFFSET (Figure4!$C$2, 1, 0, COUNT (!$C: $C), 1) লিখতে হবে যা কলাম C-তে টাইপ করা Units Sold ভ্যালু অনুযায়ী ডাইনামিক রেঞ্জ জেনারেট করবে।
ডিফল্টভাবে, একটি নাম পুরো ওয়ার্কবুকে প্রয়োগ হয় এবং ওয়ার্কবুকের মধ্যে ইউনিক হতে হবে।
তবে, আমরা স্কোপকে একটি নির্দিষ্ট শীতে সীমাবদ্ধ করতে চাই।
তাই, আমরা "Scope:" ইনপুট বাক্সে Figure4 বেছে নিয়েছি। OK-এ ক্লিক করার পর, ডাইনামিক রেঞ্জ তৈরি হয়ে যাবে।
এটি নতুন ডেটা এন্টার হলে আটোমেটিক্যালি সব সেলস ডেটা অন্তর্ভুক্ত করবে।
এখন চার্টের যেকোনো পয়েন্টে রাইট-ক্লিক করুন এবং তারপর "Select Data" সিলেক্ট করুন।
চিত্র ৪.৩
প্রম্পট হওয়া Select Data Source-এ, Series1 বেছে নিন এবং তারপর Edit।
চিত্র ৪.৪
তারপর চিত্র ৪.৫-র মতো =Figure4!Units লিখুন।
চিত্র ৪.৫
শেষে, চেষ্টা করুন এবং সেল C13-এ 11 লিখুন। আপনি দেখবেন চার্টটি পরিবর্তন হয়েছে এবং মান 11 অন্তর্ভুক্ত হয়েছে।
নতুন ডেটা যোগ হলে চার্ট আটোমেটিক্যালি পরিবর্তন হবে।
চিত্র ৪.৬
আরও পড়ুন…
- Offset(…) Function in Excel with Examples
কাজের ফাইল ডাউনলোড
নিচের লিংক থেকে কাজের ফাইল ডাউনলোড করুন।
Excel-Offset-Function.rar