Dynamic Data Ranges তৈরি করা Excel Dashboards তৈরির একটি গুরুত্বপূর্ণ অংশ, কারণ এগুলি ডেটার পরিবর্তনের সাথে সাথে চার্ট এবং রিপোর্টের আপডেট নিশ্চিত করে। OFFSET এবং INDIRECT ফাংশন দুটি শক্তিশালী টুল যা dynamic data range তৈরি করতে সাহায্য করে। এই ফাংশনগুলি ব্যবহার করে আপনি ডেটার আকার বা পরিসীমা পরিবর্তিত হওয়ার সাথে সাথে chart বা data analysis এর রেঞ্জ স্বয়ংক্রিয়ভাবে আপডেট করতে পারবেন।
এই প্রবন্ধে, আমরা OFFSET এবং INDIRECT ফাংশনগুলি কীভাবে কাজ করে এবং কীভাবে এগুলি ব্যবহার করে dynamic data range তৈরি করা যায়, তা নিয়ে বিস্তারিত আলোচনা করব।
১. OFFSET Function: Dynamic Data Range তৈরি করা
OFFSET ফাংশনটি এক্সেলে একটি ডায়নামিক রেঞ্জ তৈরি করার জন্য ব্যবহৃত হয়। এটি একটি নির্দিষ্ট সেল বা রেঞ্জের শুরু থেকে নির্দিষ্ট সংখ্যক সারি এবং কলাম সরিয়ে গিয়ে একটি নতুন রেঞ্জ তৈরি করতে সক্ষম। এটি সাধারণত ডেটার আকার পরিবর্তিত হলে, সেই রেঞ্জের মাধ্যমে ডেটার গঠন পরিবর্তন করার জন্য ব্যবহৃত হয়।
OFFSET Function Syntax:
OFFSET(reference, rows, columns, [height], [width])
- reference: শুরু সেল বা রেঞ্জ।
- rows: কতটা উপরে বা নিচে যেতে হবে।
- columns: কতটা ডানে বা বামে যেতে হবে।
- height (optional): রেঞ্জের উচ্চতা (সারি সংখ্যা)।
- width (optional): রেঞ্জের প্রস্থ (কলাম সংখ্যা)।
OFFSET Function Example:
ধরা যাক, আপনার একটি Sales টেবিল আছে এবং আপনি Total Sales এর জন্য একটি ডায়নামিক রেঞ্জ তৈরি করতে চান।
| Product | Sales |
|---|---|
| A | 500 |
| B | 750 |
| C | 600 |
| D | 400 |
আপনি Sales এর পরিসীমা ডায়নামিকভাবে তৈরি করতে OFFSET ফাংশন ব্যবহার করতে পারেন।
- Data Range: আপনার Sales কলামের ডেটার রেঞ্জ (ধরা যাক B2:B5 পর্যন্ত)।
Formula:
=SUM(OFFSET(B2,0,0,COUNTA(B:B)-1,1))- OFFSET(B2,0,0,COUNTA(B:B)-1,1): এটি B2 থেকে শুরু করে Sales কলামে যতগুলো ভ্যালু আছে তার পরিমাণ গণনা করবে।
- COUNTA(B:B) ফাংশনটি কলাম B তে মোট সেলের সংখ্যা গুনে Sales রেঞ্জের জন্য একটি ডায়নামিক পরিসীমা তৈরি করবে।
- এইভাবে, যখন আপনি নতুন ডেটা যোগ করবেন, OFFSET ফাংশনটি স্বয়ংক্রিয়ভাবে নতুন সেলের সংখ্যা গণনা করবে এবং পরিসীমা আপডেট হবে।
২. INDIRECT Function: Dynamic Data Range তৈরি করা
INDIRECT ফাংশনটি অন্য সেলের মাধ্যমে একটি রেঞ্জ বা সেল রেফারেন্স তৈরি করতে ব্যবহৃত হয়। এটি একটি টেক্সট স্ট্রিং হিসেবে সেল রেফারেন্স গ্রহণ করে এবং সেই রেফারেন্সে নির্দিষ্ট ডেটা প্রদান করে।
INDIRECT Function Syntax:
INDIRECT(ref_text, [a1])
- ref_text: যে সেলের রেফারেন্স আপনি চান সেটি একটি টেক্সট স্ট্রিং হিসেবে দিতে হবে।
- a1 (optional): যদি TRUE থাকে, তবে A1 রেফারেন্স স্টাইল ব্যবহার করা হবে। FALSE থাকলে R1C1 রেফারেন্স স্টাইল ব্যবহার হবে।
INDIRECT Function Example:
ধরা যাক, আপনি Sales টেবিলের একটি dynamic range তৈরি করতে চান এবং সেই রেঞ্জের ওপর ভিত্তি করে total sales গণনা করতে চান। আপনি একটি সেল F1 এ Sales রেঞ্জের শেষ সেল নাম্বার লিখবেন, এবং INDIRECT ফাংশন ব্যবহার করে ডায়নামিক রেঞ্জ তৈরি করবেন।
- Data Range: আপনার Sales কলামের ডেটা B2:B5।
Formula:
=SUM(INDIRECT("B2:B" & F1))- F1 সেলে আপনি Sales কলামের শেষ সেল (যেমন 5) লিখবেন।
- INDIRECT("B2:B" & F1) এই ফাংশনটি B2 থেকে B5 পর্যন্ত একটি ডায়নামিক রেঞ্জ তৈরি করবে, যেখানে F1 সেলটি পরিসীমার শেষ সেল নম্বরকে ডায়নামিকভাবে নির্ধারণ করবে।
৩. Using OFFSET and INDIRECT Together for Dynamic Range
আপনি OFFSET এবং INDIRECT ফাংশনগুলো একসাথে ব্যবহার করে আরও উন্নত ডায়নামিক রেঞ্জ তৈরি করতে পারেন, যাতে আপনি আরও কাস্টমাইজড ডেটা রেঞ্জ এবং পরিসীমা তৈরি করতে পারেন।
Example: Dynamic Sales Chart with OFFSET and INDIRECT
ধরা যাক, আপনি একটি Sales ড্যাশবোর্ড তৈরি করছেন, এবং আপনার Sales Data-এর জন্য ডায়নামিক চার্ট তৈরি করতে চান। আপনি OFFSET এবং INDIRECT ব্যবহার করে একটি dynamic chart range তৈরি করতে পারেন।
- Sales Data: আপনার Sales Data টেবিলটি সেল A2:B10 এর মধ্যে রয়েছে। আপনি Sales এর পরিসীমা ডায়নামিকভাবে Chart এর জন্য তৈরি করতে চান।
Formula:
=OFFSET(INDIRECT("Sheet1!B2"),0,0,COUNTA(Sheet1!B:B)-1,1)- INDIRECT("Sheet1!B2"): এটি Sales Data-এর প্রথম সেল (B2) কে রেফারেন্স হিসেবে ব্যবহার করবে।
- OFFSET: তারপর OFFSET ফাংশনটি ডায়নামিকভাবে সেই রেঞ্জ থেকে ডেটা বের করে এনে Sales Data-এর পরিসীমা তৈরি করবে।
৪. Best Practices for Using OFFSET and INDIRECT
- Ensure Correct Range References: OFFSET এবং INDIRECT ব্যবহারের সময় নিশ্চিত করুন যে আপনি সঠিক সেল রেফারেন্স ব্যবহার করছেন। ভুল রেফারেন্স ডেটার বিশ্লেষণকে ভুল প্রভাবিত করতে পারে।
- Combine with Named Ranges: Named Ranges ব্যবহার করে আপনি OFFSET এবং INDIRECT ফাংশনগুলির সাথে ডায়নামিক রেঞ্জ আরও সঠিকভাবে সেট করতে পারেন।
- Monitor Performance: OFFSET এবং INDIRECT ফাংশনগুলি বড় ডেটাসেটের সাথে ব্যবহার করলে Excel এর কর্মক্ষমতা কিছুটা কমাতে পারে, তাই খুব বেশি ডেটার সাথে একসাথে এগুলো ব্যবহার না করার চেষ্টা করুন।
- Keep Formulas Simple: Dynamic Data Range তৈরি করার সময় খুব জটিল ফাংশন ব্যবহার থেকে বিরত থাকুন। সহজ এবং পরিষ্কার ফাংশনগুলো ব্যবহার করা সবসময় সুবিধাজনক।
সারাংশ
OFFSET এবং INDIRECT ফাংশন ব্যবহার করে আপনি Excel Dashboards এ ডায়নামিক রেঞ্জ তৈরি করতে পারেন, যা স্বয়ংক্রিয়ভাবে পরিবর্তনশীল ডেটা রেঞ্জের জন্য উপযোগী। OFFSET ফাংশন ডেটা রেঞ্জের শুরুর পয়েন্ট থেকে নির্দিষ্ট সংখ্যক সারি বা কলাম সরিয়ে ডেটার আকার পরিবর্তন করতে সাহায্য করে, এবং INDIRECT ফাংশন একটি টেক্সট স্ট্রিং হিসেবে সেল রেফারেন্স গ্রহণ করে। এই ফাংশনগুলো একত্রে ব্যবহার করে আপনি আরও কাস্টমাইজড এবং ইন্টারঅ্যাকটিভ Excel Dashboards তৈরি করতে পারেন, যা ডেটার পরিবর্তনের সাথে সাথে আপডেট হবে।
Read more
