OFFSET এবং INDIRECT ফাংশন এক্সেলে ডাইনামিক রেঞ্জ তৈরির জন্য অত্যন্ত কার্যকরী টুলস। এই ফাংশনগুলো ব্যবহার করে আপনি এমন রেঞ্জ তৈরি করতে পারেন যা ডেটা পরিবর্তনের সাথে সাথে স্বয়ংক্রিয়ভাবে আপডেট হয়। এটি বিশেষ করে বড় ডেটাসেটের সাথে কাজ করার সময় খুবই উপকারী।
OFFSET ফাংশন
OFFSET ফাংশনটি একটি নির্দিষ্ট রেফারেন্স পয়েন্ট থেকে নির্দিষ্ট সংখ্যক সারি এবং কলাম সরিয়ে একটি নতুন রেঞ্জ তৈরি করে। এটি ডাইনামিক রেঞ্জ তৈরিতে ব্যবহৃত হয় যা ডেটা যুক্ত বা বাদ পড়ার সাথে সাথে আপডেট হয়।
OFFSET ফাংশনের গঠন
=OFFSET(reference, rows, cols, [height], [width])
- reference: মূল সেল বা রেঞ্জ যা থেকে সরানোর শুরু হবে।
- rows: মূল রেফারেন্স থেকে কতটি সারি উপরে বা নীচে সরাতে হবে (নেতিবাচক মান উপরে, ধনাত্মক মান নীচে)।
- cols: মূল রেফারেন্স থেকে কতটি কলাম বামে বা ডানে সরাতে হবে (নেতিবাচক মান বামে, ধনাত্মক মান ডানে)।
- height: নতুন রেঞ্জের উচ্চতা (সারির সংখ্যা) নির্দিষ্ট করে।
- width: নতুন রেঞ্জের প্রস্থ (কলামের সংখ্যা) নির্দিষ্ট করে।
উদাহরণ
ধরা যাক, আপনার A1:A10 সেলে কিছু ডেটা রয়েছে এবং আপনি একটি ডাইনামিক রেঞ্জ তৈরি করতে চান যা সর্বশেষ ৫টি সেল অন্তর্ভুক্ত করবে।
=OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1)
- A1: মূল রেফারেন্স।
- COUNTA(A:A)-5: মোট পূর্ণ সেল সংখ্যা থেকে ৫ কমিয়ে সর্বশেষ ৫টি সেল নির্বাচন।
- 0: কলামে কোনো সরানো হবে না।
- 5: ৫টি সারি উচ্চতা।
- 1: ১টি কলাম প্রস্থ।
এই ফর্মুলা ব্যবহার করলে, A1 থেকে A10 এর মধ্যে সর্বশেষ ৫টি সেল নির্বাচন হবে এবং ডেটা বৃদ্ধি পেলে এটি স্বয়ংক্রিয়ভাবে আপডেট হবে।
INDIRECT ফাংশন
INDIRECT ফাংশনটি একটি টেক্সট স্ট্রিংকে রেফারেন্স হিসেবে ব্যবহার করে। এটি আপনাকে ডাইনামিক রেঞ্জ তৈরি করতে সাহায্য করে যেখানে রেঞ্জের নাম বা অবস্থান পরিবর্তনশীল হতে পারে।
INDIRECT ফাংশনের গঠন
=INDIRECT(ref_text, [a1])
- ref_text: একটি টেক্সট স্ট্রিং যা রেফারেন্স হিসেবে ব্যবহৃত হবে (যেমন, "A1", "B2:C10")।
- a1: ঐচ্ছিক, যদি TRUE বা বাদ দেওয়া হয়, তাহলে A1 স্টাইল রেফারেন্স ব্যবহার হয়, যদি FALSE হয়, R1C1 স্টাইল ব্যবহার হয়।
উদাহরণ
ধরা যাক, আপনি একটি সেল (যেমন, D1) এ একটি রেঞ্জের নাম লিখেছেন এবং আপনি সেই রেঞ্জের মোট যোগফল বের করতে চান।
- D1 সেলে "A1:A10" লিখুন।
তারপর অন্য একটি সেলে নিচের ফর্মুলাটি ব্যবহার করুন:
=SUM(INDIRECT(D1))
এই ফর্মুলাটি D1 সেলে উল্লেখিত রেঞ্জ (A1:A10) এর যোগফল নির্ধারণ করবে। যদি আপনি D1 সেলের রেঞ্জ পরিবর্তন করেন, তবে SUM ফাংশনটি স্বয়ংক্রিয়ভাবে নতুন রেঞ্জ অনুসারে আপডেট হবে।
OFFSET এবং INDIRECT দিয়ে Dynamic Ranges তৈরি করা
OFFSET এবং INDIRECT ফাংশনগুলো একসাথে ব্যবহার করে আপনি আরও শক্তিশালী ডাইনামিক রেঞ্জ তৈরি করতে পারেন। নিচে একটি উদাহরণ দেওয়া হলো:
উদাহরণ: বিক্রয় ডেটার উপর ভিত্তি করে ডাইনামিক চার্ট তৈরি
ধরা যাক, আপনার B1:B10 সেলে বিক্রয় সংখ্যা রয়েছে এবং আপনি একটি ডাইনামিক চার্ট তৈরি করতে চান যা ডেটা বৃদ্ধির সাথে সাথে আপডেট হবে।
OFFSET ফাংশন ব্যবহার করে ডাইনামিক রেঞ্জ তৈরি করুন:
=OFFSET($B$1, 0, 0, COUNTA($B:$B), 1)- $B$1: মূল রেফারেন্স।
- 0, 0: সারি ও কলামে কোনো সরানো হবে না।
- COUNTA($B:$B): মোট পূর্ণ সেল সংখ্যা নির্ধারণ করে উচ্চতা।
- 1: ১টি কলাম প্রস্থ।
- নামক রেঞ্জ তৈরি করুন:
- ফর্মুলা বার থেকে
Formulas>Name Manager>New। - নাম দিন (যেমন, "SalesData")।
- রেফারেন্স হিসেবে উপরের OFFSET ফর্মুলা ব্যবহার করুন।
- ফর্মুলা বার থেকে
- চার্ট তৈরি করুন:
- ডাইনামিক নামক রেঞ্জ ("SalesData") ব্যবহার করে চার্ট তৈরি করুন।
- যখন আপনি B1:B10 সেলের মধ্যে নতুন ডেটা যোগ করবেন, চার্ট স্বয়ংক্রিয়ভাবে আপডেট হবে।
Best Practices এবং টিপস
- ফর্মুলার স্থায়িত্ব:
- OFFSET ফাংশন স্বয়ংক্রিয়ভাবে আপডেট হয়, তবে এটি সেল অবরোধের কারণে ত্রুটি দেখাতে পারে। নিশ্চিত করুন যে স্পিল রেঞ্জে কোনো ডেটা অবরোধ নেই।
- INDIRECT ফাংশনের সীমাবদ্ধতা:
- INDIRECT ফাংশনটি রেফারেন্সকে টেক্সটে রূপান্তর করে, ফলে এটি নির্দিষ্ট কাজের জন্য ভালো হলেও, এটি স্বয়ংক্রিয় আপডেটের ক্ষেত্রে কিছু সীমাবদ্ধতা থাকতে পারে।
- নামক রেঞ্জ ব্যবহার:
- ডাইনামিক রেঞ্জকে নামকরণ করে রাখলে, ফর্মুলা ব্যবহারে সুবিধা হয় এবং ডেটা ম্যানেজমেন্ট সহজ হয়।
- কম্বিনেশন:
- OFFSET এবং INDIRECT একসাথে ব্যবহার করে আরও জটিল ডাইনামিক রেঞ্জ তৈরি করা যায়, যা আপনার ডেটা বিশ্লেষণকে আরও শক্তিশালী করে তোলে।
সারাংশ
OFFSET এবং INDIRECT ফাংশনগুলো এক্সেলে ডাইনামিক রেঞ্জ তৈরির জন্য অত্যন্ত কার্যকরী। OFFSET ফাংশনটি রেফারেন্স পয়েন্ট থেকে নির্দিষ্ট সংখ্যক সারি ও কলাম সরিয়ে নতুন রেঞ্জ তৈরি করে, যেখানে INDIRECT ফাংশনটি টেক্সট স্ট্রিংকে রেফারেন্স হিসেবে ব্যবহার করে। এই ফাংশনগুলো ব্যবহার করে আপনি স্বয়ংক্রিয়ভাবে আপডেট হওয়া ডাইনামিক রেঞ্জ তৈরি করতে পারেন, যা ডেটা বিশ্লেষণ, রিপোর্টিং, এবং চার্ট তৈরিতে অত্যন্ত উপকারী।
ডাইনামিক রেঞ্জ ব্যবহার করার মাধ্যমে আপনি এক্সেলে বড় ডেটাসেটের সাথে আরও দক্ষতার সাথে কাজ করতে পারবেন এবং আপনার কাজের কার্যকারিতা বৃদ্ধি করতে পারবেন।
Read more
