এক্সেল ডাইনামিক নেমড রেঞ্জ: ৪টি প্রমাণিত টেকনিক

এক্সেল ডাইনামিক নেমড রেঞ্জ এক্সেলের এমন একটি ফিচার जिसके সম্পর্কে অনেক এক্সেল ব্যবহারকারী জানেন না। এই আর্টিকেল ডাইনামিক নেমড রেঞ্জকে আপনার কাছে নিকট করিয়ে আনবে। আગের লাইনে এক্সেল ডাইনামিক নেমড রেঞ্জ সম্পর্কিত হিন্টস ও ট্রিকস দেখানো হবে।

এক্সেলের OFFSET ফাংশন দিয়ে ডাইনামিক নেমড রেঞ্জ

একটি ডাইনামিক রেঞ্জ তৈরির জন্য OFFSET ফাংশন ব্যবহার করা যায়।

এক্সেলের OFFSET ফাংশন

এক্সেলের OFFSET ফাংশনের সিনট্যাক্স নিচে দেওয়া হলো:

OFFSET (reference, rows, cols, [height], [width])

ফাংশনে আর্গুমেন্টগুলোয় অর্থ:

1. Reference – যে রেফারেন্স থেকে অফসেটের বেস তৈরি করতে চান।
2. Rows – রেফারেন্স সেল থেকে উপরে বা নিচে কতগুলো সারি।
3. Cols – রেফারেন্স সেল থেকে ডানে বা বামে কতগুলো কলাম।
4. Height, width – রেফারেন্স সেল থেকে সিলেকশনের উচ্চতা এবং প্রস্থ সংজ্ঞায়িত করে।

OFFSET ফাংশন দিয়ে ডাইনামিক নেমড রেঞ্জ

Name Manager dialog box-এ এটি ডাইনামিক নেমড রেঞ্জ সংজ্ঞায়িত করতে ব্যবহার করা যায়।

এটি কীভাবে করবেন:

Formulas ট্যাবে যান, Defined Names কমান্ড গ্রুপ থেকে Define name কমান্ডে ক্লিক করুন, এবং আপনাকে নিচের মতো New Name ডায়লগ বক্স मिलবে:

নিচের ছবির উদাহরণ অনুযায়ী একটি ডাইনামিক নেমড রেঞ্জ তৈরি হবে যা A1-এর এক সেল নিচ থেকে শুরু হয়ে একই কলামে, K1 এবং K2 সেলে দেওয়া সারি ও কলামের সংখ্যা অনুযায়ী সিলেক্ট করবে:

"Refers to" ফিল্ডের ফর্মুলা নিচের মতো হবে:

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

OFFSET ফাংশনের সকল উপাদান ডাইনামিক করা যায়। উদাহরণস্বরূপ, আমি কোনো নির্দিষ্ট দিনের জন্য কিছু তারিখ সিলেক্ট করতে চাই।

কয়েকটি আউটপুট উদাহরণ:

=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = এটি A2: B7 রেঞ্জকে রেফার করবে

K1, K2, K3, এবং K4-এ এন্ট্রি উপর ভিত্তি করে ফর্মুলাটি হবে:

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

এক্সেলের যেকোনো ফাংশনের মত OFFSET-ও অন্যান্য ফাংশনের সাথে কম্বাইন করা যায় যারা ফলাফল হিসেবে সংখ্যা দেয়। ধরুন, আমি মঙ্গলবারকে রেফারেন্স হিসেবে নিয়ে শেষ সপ্তাহ বাদ দিয়ে সকল মঙ্গলবারের তারিখ সিলেক্ট করতে চাই। একই উদাহরণ অনুযায়ী ফর্মুলাটি হবে:

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

আরও পড়ুন: Excel Dynamic Named Range Based on Cell Value (5 Easy Ways)

সাদৃশ্য পড়ুন

• Excel Dynamic Range Based On Cell Value
• Excel VBA: Dynamic Range Based on Cell Value (3 Methods)
• How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)

INDEX ফাংশন দিয়ে ডাইনামিক নেমড রেঞ্জ

অন্যান্য ফাংশনও ডাইনামিক নেমড রেঞ্জের জন্য ব্যবহার করা যায়, যেমন INDEX। উদাহরণস্বরূপ, আপনি সকল "সোমবার" সিলেক্ট করতে চান, তার সেরকত তারিখ কতোটা আছে সেটা যেন না হোক:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

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

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

আরও পড়ুন: Excel OFFSET Dynamic Range Multiple Columns in Effective Way

VBA দিয়ে ডাইনামিক নেমড রেঞ্জ

পেশাদাররা তাদের দৈনন্দিন কাজে একই অপারেশন বারবার করতে হয়। ডাইনামিক নেমড রেঞ্জ তৈরি করার সময়ও একই পরিস্থিতি হতে পারে। পুনরাবৃত্তি কাজ এড়াতে এর জন্য VBA ব্যবহার করা যায়।

VBA ফর্মুলার মতোই কাজ করে, শুধু নেমড রেঞ্জ যোগ করার জন্য নির্দিষ্ট সিনট্যাক্স মেনে চলতে হয়।

সিনট্যাক্সটি নিচের মতো:

ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”

উদাহরণস্বরূপ, আগের উদাহরণগুলো থেকে একটি সহজেই VBA-তে প্রয়োগ করা যায়। একটি সম্পূর্ণ ম্যাক্রো হবে:

Sub naming()

ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”

End sub

আরও পড়ুন: Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)

ব্ল্যাঙ্ক সেলসহ ডাইনামিক নেমড রেঞ্জ

আপনি প্রায়ই সমস্যায় পড়েন যখন সকল সেল পূর্ণ নয়, অর্থাৎ কিছু সেল ব্ল্যাঙ্ক থাকে। সবচেয়ে গুরুত্বপূর্ণ হলো রেফারেন্স খুঁজে বের করার জন্য ব্যবহৃত ফর্মুলার ডেফিনিশন জানা। উদাহরণস্বরূপ, COUNTA হলো একটি ফাংশন যা একটি রেঞ্জে সকল NON-BLANK! সেল গণনা করে। আপনি ব্ল্যাঙ্ক ইনক্লুড করতে চান কিনা সেটা আপনার ऊपर।

নিচের ছবিতে ব্ল্যাঙ্ক সেলসহ পরিস্থিতিটি দেখানো হলো:

আপনি যদি সকল "সোমবার" সিলেক্ট করতে চান, তবে আগের উদাহরণের মত ফর্মুলা ব্যবহার করলে ব্ল্যাঙ্ক সেল A5 থাকার কারণে শেষ সোমবারটি বাদ যাবে। এর সমাধান হলো ভিন্ন ফর্মুলা ব্যবহার করা - ফর্মুলার কম্বিনেশন:

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>””)*ROW(Sheet1!$A:$A)))-1;1)

এটি A2 থেকে A7 পর্যন্ত সকল সেল সিলেক্ট করবে।

আগের লাইনগুলো ও উদাহরণগুলো আপনার কাজ দ্রুত এবং কার্যকরভাবে সম্পন্ন করতে সাহায্য করবে। ডাইনামিক নেমড রেঞ্জ একটি ব্যাপক কর্মক্ষেত্র যেখানে অনেক অপশন এক্সপ্লোর করা যায় এবং আপনার দক্ষতা ও সৃষ্টিশীলতার উপর নির্ভর করে এটি একটি উপকারী টুল হতে পারে। দেখানো হয়েছে যে অনেক কম্বিনেশন সম্ভব, এবং সেগুলোর অ্যাপ্লিকেশন খুঁজে বের করা আপনার ऊपर।

আরও পড়ুন: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)

ওয়ার্কিং ফাইল ডাউনলোড করুন

নিচের লিংক থেকে ওয়ার্কিং ফাইল ডাউনলোড করুন:

সম্পর্কিত আর্টিকেল

• How to Use Dynamic Range VBA in Excel (11 Ways)
• Data Validation Drop Down List with Excel Table Dynamic Range
• Create a Dynamic Chart Range in Excel (2 Methods)
• How to Create a Range of Numbers in Excel (3 Easy Methods)