এক্সেলে অফসেট ফাংশনের ব্যবহার [অফসেট – ম্যাচ কম্বো, ডায়নামিক রেঞ্জ]

আজ আমি আপনাকে এক্সেলের অফসেট ফাংশন এর সাথে পরিচয় করিয়ে দিতে চাই 3টি বাস্তব জীবনের উদাহরণ সহ।

প্রথমে, আমি সূত্রের সিনট্যাক্স বর্ণনা করব এবং তারপরে বাস্তব জীবনে সমস্যা সমাধানের জন্য কীভাবে অফসেট ফাংশন ব্যবহার করা যেতে পারে সে সম্পর্কে কথা বলতে যাচ্ছি।

পরিচয়

OFFSET ফাংশন একটি কক্ষে একটি রেফারেন্স ফেরত দিতে পারে (আসুন একে টার্গেট সেল বলি) বা ব্যাপ্তি (টার্গেট রেঞ্জ) যা অন্য একটি সেল (রেফারেন্স সেল) বা ব্যাপ্তি (রেফারেন্স রেঞ্জ) থেকে দূরে একটি নির্দিষ্ট সংখ্যক সারি এবং কলাম।

নীচের চিত্রটি ব্যাখ্যা করে যে কীভাবে OFFSET ফাংশনটি একটি কক্ষে (বাম অংশ) বা একটি পরিসরে (ডান অংশ) রেফারেন্স ফেরত দিতে হয়।

এটি আপনাকে একটি টার্গেট সেল কী এবং একটি রেফারেন্স সেল কী তা সম্পর্কে একটি স্বজ্ঞাত ধারণা দেবে৷

সবুজ রঙে হাইলাইট করা সেলটি একটি টার্গেট সেল যখন হলুদ রঙে হাইলাইট করা সেলগুলি একটি টার্গেট রেঞ্জ নিয়ে গঠিত।

নীল রঙে হাইলাইট করা কোষ হল রেফারেন্স সেল।

চিত্র 1

এক্সেল (সিনট্যাক্স) এ অফসেট মানে কি?

এখানে অফসেট ফাংশনের সিনট্যাক্স রয়েছে: OFFSET (রেফারেন্স, সারি, কল, [উচ্চতা], [প্রস্থ])

আচ্ছা, এখন আমি আপনাকে দেখাই কিভাবে বাস্তব জীবনে সমস্যা সমাধানের জন্য অফসেট ফাংশন ব্যবহার করতে হয়।

কেস 1:অফসেট এবং ম্যাচ ফাংশনগুলিকে একত্রিত করে ডান-থেকে-বামে লুকআপ

এটা সুপরিচিত যে আপনি VLOOKUP ফাংশন দিয়ে শুধুমাত্র বাম-থেকে-ডানে লুকআপ করতে পারবেন।

অনুসন্ধান করার জন্য মান আপনার টেবিল অ্যারের প্রথম কলামে স্থাপন করা আবশ্যক।

আপনি যদি একটি নতুন লুকআপ মান যোগ করতে চান বা আপনি যদি অন্য একটি কলাম লুকআপ মান হিসাবে ব্যবহার করতে চান তাহলে আপনাকে আপনার সম্পূর্ণ টেবিল পরিসরটি একটি কলাম দ্বারা ডানদিকে স্থানান্তর করতে হবে৷

কিন্তু ম্যাচ ফাংশনের সাথে অফসেটকে একত্রিত করার মাধ্যমে, VLOOKUP ফাংশনের সীমাবদ্ধতা দূর করা যেতে পারে৷

MATCH ফাংশন কী এবং আমরা কীভাবে OFFSET ফাংশনটিকে ম্যাচ ফাংশনের সাথে একত্রিত করতে পারি?

ঠিক আছে, ম্যাচ ফাংশন কক্ষের একটি পরিসরে একটি নির্দিষ্ট আইটেমের জন্য অনুসন্ধান করে এবং তারপর পরিসরে সেই আইটেমের আপেক্ষিক অবস্থান ফেরত দেয়৷

আসুন উদাহরণ হিসেবে চিত্র 2.1 (যা বিভিন্ন দেশের বিভিন্ন দেশের রাজস্ব দেখায়) থেকে রেঞ্জ B3:B8 নিই।

সূত্র “=MATCH (“USA”, B3:B8, 0)” ফিরবে 1 যেহেতু USA হল সীমার মধ্যে প্রথম আইটেম (সেল B10 এবং C10 দেখুন)।

অন্য একটি রেঞ্জ C2:F2 এর জন্য, সূত্র “=MATCH (2015, C2:F2, 0)” 3 প্রদান করে যেহেতু 2015 হল পরিসরের তৃতীয় আইটেম (সেল B11 এবং C11 দেখুন)।

অফসেট ফাংশনে ফিরে যাচ্ছি।

যদি আমরা সেল B2 কে রেফারেন্স সেল হিসাবে সেট করি এবং সেল E3 কে টার্গেট সেল হিসাবে নিই, তাহলে আমরা কীভাবে অফসেট সূত্র লিখতে পারি?

E3 হল 1 B2 এবং 3 এর নিচে সারি B2 এর ডানদিকে কলাম।

অতএব, সূত্রটিকে “=OFFSET(B2, 1) হিসাবে লেখা যেতে পারে , 3 )"। লাল রঙের সংখ্যাগুলি ঘনিষ্ঠভাবে দেখুন, আপনি কি খুঁজে পাচ্ছেন যে সেগুলি মিলে গেছে?

এটাই প্রশ্নের উত্তর – ম্যাচ ফাংশনের সাথে অফসেট ফাংশনকে কীভাবে একত্রিত করা যায় – অফসেট ফাংশনের দ্বিতীয় বা তৃতীয় আর্গুমেন্ট হিসাবে পরিবেশনের জন্য ম্যাচ ফাংশন প্রয়োগ করা যেতে পারে (সেল C13 দেখুন)।

সেল C14 একই ডেটা পুনরুদ্ধার করতে কীভাবে VLOOKUP ফাংশন ব্যবহার করতে হয় তা প্রদর্শন করে৷

আমাদের অবশ্যই জানতে হবে যে 2015 সালে রাজস্ব 4 ^th এ রেকর্ড করা হয়েছে৷ VLOOKUP ফাংশন লেখার আগে টেবিল অ্যারে B2:F8-এর কলাম।

এর মানে হল VLOOKUP ফাংশন ব্যবহার করার সময় আমাদের ডেটা স্ট্রাকচার সম্পর্কে খুব ভালভাবে জানতে হবে।

এটি VLOOKUP এর জন্য আরেকটি সীমাবদ্ধতা। যাইহোক, OFFSET ফাংশনের যুক্তি হিসাবে MATCH ফাংশন ব্যবহার করে, আমাদের কলাম সূচক জানতে হবে না।

অনেক কলাম থাকলে এটি খুবই কার্যকর।

চিত্র 2.1

এখন চলুন এবং আরও জটিল উদাহরণ দেখি।

ধরুন আমাদের কাছে বিভিন্ন কোম্পানির জন্য কোম্পানির নাম, যোগাযোগের নাম এবং ইমেল ঠিকানা সম্বলিত একটি টেবিল আছে।

এবং আমরা একটি পরিচিত পরিচিতি নাম থেকে কোম্পানির নাম পুনরুদ্ধার করতে চাই বা পরিচিত ইমেল ঠিকানা থেকে একটি পরিচিতির নাম পেতে চাই৷ আমরা কি করতে পারি?

চিত্র 2.2 দেখুন, পরিসর B5:E8 কোম্পানির তথ্য অন্তর্ভুক্ত করে। সেল C2 এবং সেল B3-এ ইনপুট স্থাপন করে, লাল বর্গক্ষেত্রে সূত্রের সাহায্যে, আমি পরিচিতির নাম জানলে কোম্পানির নাম পুনরুদ্ধার করতে পারি।

পরিসর D2:E4 দেখায় কিভাবে পরিচিত ইমেল ঠিকানা সহ একটি পরিচিতির নাম পেতে হয়।

সংক্ষেপে, এই দুটি উদাহরণ দেখায় যে আমরা একটি ডান-থেকে-বাম সন্ধান করতে পারি এবং অনুসন্ধানের মানটি ডানদিকের কলামে স্থাপন করার প্রয়োজন নেই। টেবিল অ্যারের যেকোনো কলামে অনুসন্ধান মান থাকতে পারে।

চিত্র 2.2

কেস 2:অফসেট এবং COUNT ফাংশনগুলির সমন্বয়ে স্বয়ংক্রিয় গণনা

যখনই আমরা একটি কলামে একটি নতুন সংখ্যা যোগ করি তখন কীভাবে স্বয়ংক্রিয়ভাবে গণনা করা যায় তার সাথে পরিচয় করিয়ে দেওয়ার আগে, প্রথমে একটি কলামে শেষ সংখ্যাটি কীভাবে স্বয়ংক্রিয়ভাবে ফেরত দেওয়া যায় তা দিয়ে শুরু করা যাক৷

নীচের চিত্রটি দেখুন যা মানব সম্পদ থেকে এন্ট্রি দেখায়। ধরুন আমরা B কলামে শেষ সংখ্যা পেতে চাই, সূত্রটি হবে “=OFFSET (C2, 9) , 0)” যদি আমরা অফসেট ফাংশন প্রয়োগ করি।

সূত্র থেকে, আমরা জানতে পারি যে 9 মূল সংখ্যা।

যতক্ষণ না আমরা স্বয়ংক্রিয়ভাবে এই নম্বরটি ফেরত দিতে পারি, আমরা স্বয়ংক্রিয়ভাবে একটি কলামে শেষ নম্বরটি সনাক্ত করতে সক্ষম হব।

9 কলাম C এ সংখ্যা ধারণ করে এমন কক্ষের সংখ্যা মাত্র।

আপনি যদি COUNT ফাংশনের সাথে পরিচিত হন তবে আপনি জানতে পারবেন যে COUNT ফাংশনটি একটি পরিসরে সংখ্যা ধারণ করে এমন কক্ষের সংখ্যা গণনা করতে পারে৷

উদাহরণস্বরূপ, সূত্র “=COUNT (C3:C11)” কক্ষের সংখ্যা গণনা করবে যেগুলি C3 থেকে C11 কক্ষে সংখ্যা রয়েছে।

আমাদের ক্ষেত্রে, আমরা একটি সম্পূর্ণ কলামে কতটি সংখ্যা জানতে চাই, তাই C:C এর মতো রেফারেন্স যাতে C কলামের সমস্ত সারি অন্তর্ভুক্ত থাকে।

অনুগ্রহ করে G4 এবং H4 কক্ষগুলি দেখুন, “=COUNT(C:C)” দ্বারা প্রত্যাবর্তিত সংখ্যাটি ঠিক 9 এর সমান .

সুতরাং, উপরের OFFSET ফাংশনে COUNT(C:C) এর সাথে 9 কে প্রতিস্থাপন করে, আমরা একটি নতুন সূত্র পেতে পারি “=OFFSET (C2, COUNT(C:C) , 0)” (সেলে H5)।

এটি যে সংখ্যাটি প্রদান করে তা হল 87000 যা কলাম C এর শেষ সংখ্যা।

এখন স্বয়ংক্রিয় গণনার দিকে এগিয়ে যাওয়া যাক। ধরুন আমরা C কলামের সমস্ত সংখ্যার মোট চাই।

সূত্রটি হবে “=SUM (OFFSET (C2, 1, 0, 9) , 1))" যদি আমরা অফসেটের সাথে SUM ব্যবহার করি।

9 হল C3:C11 রেঞ্জের মোট সারির সংখ্যা এবং এছাড়াও কলাম C-এর মোট কক্ষের সংখ্যা রয়েছে।

অতএব, আমরা সূত্রটিকে একটি নতুন উপায়ে লিখতে পারি যেমন “=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))”।

সেল G10 এবং H10 দেখুন, এই 9 জন কর্মচারীর মোট বেতন হল $521,700৷

এখন আপনি যদি সেল C12-এ $34,000 এর মতো একটি সংখ্যা রাখেন, তাহলে সেল G5 এবং G10 উভয় নম্বরই যথাক্রমে $34,000 এবং $555,700 এ পরিবর্তিত হবে৷

এটাকেই আমি অটোমেশন বলি কারণ আপনাকে G5 বা G10 সেলের সূত্র আপডেট করতে হবে না।

আপনি যখন COUNT ফাংশন ব্যবহার করেন তখন আপনাকে সতর্ক থাকতে হবে কারণ COUNT ফাংশন শুধুমাত্র সংখ্যা ধারণ করে এমন কক্ষের সংখ্যা প্রদান করে।

উদাহরণ স্বরূপ, “=COUNT (B:B)” 9 এর পরিবর্তে 0 প্রদান করে কারণ B কলামে এমন কোনো ঘর নেই যেখানে সংখ্যা রয়েছে (সেল G3 এবং H3 দেখুন)।

কলাম D-এ 10টি কক্ষ রয়েছে যেখানে সংখ্যা রয়েছে এবং "COUNT (D:D)" দ্বারা প্রত্যাবর্তিত সংখ্যাটিও 10৷

কিন্তু যদি আমরা কলাম C এর মতো করে শেষ নম্বরটি D-এ পুনরুদ্ধার করতে চাই, তাহলে আমরা 0 নম্বর পাব (সেল G8 এবং H8 দেখুন)।

স্পষ্টতই, 0 আমরা যা চাই তা নয়। কোনো সমস্যা? সেল D13 সেল D2 থেকে 10 সারির পরিবর্তে 11 সারি দূরে।

এটি "=OFFSET (D2, COUNT (D:D)) + 1 সূত্র দ্বারাও প্রদর্শিত হতে পারে , 0)” সেল G7 এ।

সংক্ষেপে, সংখ্যাগুলি একে অপরের সংলগ্ন হওয়া উচিত যদি আমরা গণনার স্বয়ংক্রিয়তা সক্ষম করতে OFFSET ফাংশনের সাথে COUNT ফাংশন একসাথে ব্যবহার করতে চাই৷

চিত্র 3

কেস 3:একটি গতিশীল পরিসর তৈরি করতে অফসেট ফাংশন ব্যবহার করুন

ধরুন আমরা একটি কোম্পানির মাসিক ইউনিট বিক্রয় চার্ট করতে চাই এবং চিত্র 4.1 বর্তমান ডেটা এবং বর্তমান ডেটার উপর ভিত্তি করে তৈরি একটি চার্ট দেখায়৷

প্রতি মাসে, সাম্প্রতিক মাসের ইউনিটের বিক্রয় কলাম C-তে শেষ সংখ্যার নীচে যোগ করা হবে।

চার্ট স্বয়ংক্রিয়ভাবে আপডেট করার একটি সহজ উপায় আছে?

চার্ট আপডেট করার চাবিকাঠি হল ইউনিট বিক্রি হওয়া কলামের জন্য গতিশীল পরিসরের নাম তৈরি করতে অফসেট ফাংশন ব্যবহার করা।

ইউনিটের বিক্রয়ের জন্য গতিশীল পরিসর স্বয়ংক্রিয়ভাবে নতুন ডেটা প্রবেশের সাথে সাথে সমস্ত বিক্রয় ডেটা অন্তর্ভুক্ত করবে৷

চিত্র 4.1

একটি গতিশীল পরিসর তৈরি করতে, সূত্র ক্লিক করুন৷ ট্যাব এবং তারপরে নেম ম্যানেজার বেছে নিন অথবা নাম সংজ্ঞায়িত করুন .

নিচে নতুন নাম আপনি নাম সংজ্ঞায়িত করুন এ ক্লিক করলে ডায়ালগ বক্স প্রম্পট করবে .

আপনি যদি নাম পরিচালক চয়ন করেন , আপনাকে নতুন-এ ক্লিক করতে হবে নিচের নতুন নাম করতে ডায়ালগ বক্স প্রদর্শিত হবে।

চিত্র 4.2

“নাম:-এ ” ইনপুট বক্সে, গতিশীল পরিসরের নামটি পূরণ করতে হবে। এবং “উল্লেখ করে:” ইনপুট বক্সে, আমাদের অফসেট সূত্র টাইপ করতে হবে “=OFFSET (চিত্র4!$C$2, 1, 0, COUNT (!$C:$C), 1)” যা ইউনিট বিক্রি করা মানগুলির উপর ভিত্তি করে মানগুলির একটি গতিশীল পরিসর তৈরি করবে। কলাম C এ টাইপ করা হয়েছে।

ডিফল্টরূপে, পুরো ওয়ার্কবুকের জন্য একটি নাম প্রযোজ্য হবে এবং ওয়ার্কবুকের মধ্যে অবশ্যই অনন্য হতে হবে।

যাইহোক, আমরা একটি নির্দিষ্ট পত্রকের মধ্যে সুযোগ সীমাবদ্ধ করতে চাই।

অতএব, আমরা এখানে “স্কোপ: এ Figure4 নির্বাচন করি " নিবেশ বাক্স. ঠিক আছে এ ক্লিক করার পর , গতিশীল পরিসর তৈরি করা হয়।

নতুন ডেটা প্রবেশের সাথে সাথে এটি স্বয়ংক্রিয়ভাবে সমস্ত বিক্রয় ডেটা অন্তর্ভুক্ত করবে৷

এখন চার্টের যেকোনো পয়েন্টে ডান-ক্লিক করুন এবং তারপর "ডেটা নির্বাচন করুন" নির্বাচন করুন।

চিত্র 4.3

প্রম্পটে ডেটা নির্বাচন করুন উৎস, Series1 বেছে নিন এবং তারপর সম্পাদনা করুন৷৷

চিত্র 4.4

এবং তারপর টাইপ করুন “=Figure4!Units” যেমন চিত্র 4.5 দেখায়।

চিত্র 4.5

অবশেষে, আসুন C13 সেল এ 11 টাইপ করার চেষ্টা করি। আপনি দেখতে পাচ্ছেন চার্ট পরিবর্তিত হয়েছে এবং মান 11 অন্তর্ভুক্ত করা হয়েছে।

নতুন ডেটা যোগ হলে চার্ট স্বয়ংক্রিয়ভাবে পরিবর্তিত হবে।

চিত্র 4.6

আরো পড়ুন...

• অফসেট(…) ফাংশন এক্সেলে উদাহরণ সহ

কাজ করা ফাইল ডাউনলোড করুন

নিচের লিঙ্ক থেকে কাজের ফাইল ডাউনলোড করুন।