ব্লগ

Excel-এ ডায়নামিক রেঞ্জ: কখন OFFSET বা টেবিল ব্যবহার করবেন

সারি যোগ হলে মোট ও ড্রপডাউন বিকল্প হালনাগাদ করুন। OFFSET ও COUNTA-এর ব্যবহার, ফাঁকা ID-এর প্রভাব এবং Excel টেবিল তৈরির পদ্ধতি দেখুন।

নতুন সারি যোগ হলে চিহ্নিত রেফারেন্স রেঞ্জ নিচের দিকে বাড়ে।

Excel-এ পণ্যের তালিকা বাড়ালেও মোট বা ড্রপডাউনের বিকল্প অপরিবর্তিত থাকতে পারে। রেফারেন্স A2:A10 থাকলে ১১ নম্বর সারি রেঞ্জের বাইরে। ডায়নামিক রেঞ্জ দিলে Excel নিজেই তালিকার শেষ নির্ধারণ করে, তাই প্রতিবার ফর্মুলা বদলাতে হয় না।

OFFSET দিয়ে উচ্চতা ও প্রস্থ বের করা

ডেটা একটানা হলে হেডার ও পণ্যের ID গুনে OFFSET-এর উচ্চতা ও প্রস্থ দিন। Sheet1-এ নমুনা তৈরি করুন এবং অন্য শিটে যোগফল বের করার ফর্মুলা দিন। পরের স্ট্রাকচার্ড রেফারেন্সগুলো কাজ করার জন্য জাপানি হেডারের লেখা ঠিক রাখুন।

সেল কলাম A কলাম B কলাম C
সারি ১ 商品ID (পণ্যের ID) 商品名 (পণ্যের নাম) 数量 (পরিমাণ)
সারি ২ A01 কলম 4
সারি ৩ A02 নোটবুক 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

A1 থেকে সারি বা কলাম না সরিয়ে শুরু করুন। A কলামের ডেটার সংখ্যা উচ্চতা, আর প্রথম সারির হেডারের সংখ্যা প্রস্থ। নমুনায় তিন সারি ও তিন কলামের A1:C3 রেঞ্জে মোট 10। চতুর্থ সারিতে A03, স্কেল ও 2 যোগ করলে রেঞ্জ হয় A1:C4 এবং মোট 12। পণ্যের ID টেক্সট এবং গণনার রেঞ্জে অন্য ডেটা নেই ধরে নেওয়া হয়েছে। Microsoft-এর OFFSET নির্দেশিকা

এবার শুধু A3-এর ID মুছুন। A কলামে তিনটি সেল গোনা হবে: হেডার A1 এবং ID থাকা A2 ও A4। ফলে OFFSET প্রথম তিন সারির A1:C3 ফেরত দেয়। তৃতীয় সারির 6 যোগ হয়, চতুর্থ সারির 2 বাদ যায়, তাই মোট আবার 10।

A3 ফাঁকা। OFFSET-এর A1:C3 রেঞ্জে তৃতীয় সারির 6 আছে ও চতুর্থ সারির 2 বাদ, তাই মোট 10।
সবুজ রেখা Windows-এর Excel-এ OFFSET-এর ফেরত দেওয়া A1:C3 রেঞ্জটি দেখায়।

মাঝখানে কোনো হেডার ফাঁকা থাকলেও শেষের দিকের কলাম রেঞ্জের বাইরে যেতে পারে। ফর্মুলা ফাঁকা স্ট্রিং ("") ফেরত দিলেও COUNTA সেলটি গণনা করে। পুরোনো ওয়ার্কবুকে হেডার ও ID একটানা রাখুন এবং সীমা ও ফাঁকা সেল পরীক্ষা করুন। ID দেওয়া থাকলে পরিমাণ ফাঁকা হলেও উচ্চতা বদলায় না। Microsoft-এর COUNTA নির্দেশিকা

হেডার দ্বিতীয় সারিতে শুরু হলে

প্রথম সারিতে রিপোর্টের শিরোনাম থাকলে তালিকা A2 থেকে শুরু করুন। উচ্চতা ও প্রস্থও দ্বিতীয় সারি থেকে গুনুন। নমুনা এক সারি নামালে হেডার ও দুটি পণ্যের রেঞ্জ A2:C4 হয় এবং মোট 10 থাকে।

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

সারি যোগ করলে বাড়ে এমন টেবিল

নতুন তালিকার জন্য হেডারসহ রেঞ্জ বেছে Insert → Table দিন। টেবিলের নাম Items রাখুন। নিচের ফর্মুলা টেবিলের বাইরের সেল থেকে পরিমাণ কলামের মোট বের করে। টেবিলে সারি যোগ করলে যোগফলের রেঞ্জও বাড়ে। স্ট্রাকচার্ড রেফারেন্স নিয়ে Microsoft-এর নির্দেশিকা

=SUM(Items[数量])

শুধু পণ্যের ID আছে এমন সারি যোগ করা

A1:C4 থেকে Items টেবিল বানালে A3 ফাঁকা হলেও ফর্মুলাটি 4 + 6 + 2 যোগ করে 12 দেয়। শুধু ID আছে এমন সারি যোগ করতে SUMIF-এ ID কলাম ফাঁকা না থাকার শর্ত দিন। উদাহরণে দ্বিতীয় ও চতুর্থ সারির 4 + 2 যোগ করে 6 হয়। Microsoft-এর SUMIF নির্দেশিকা

=SUMIF(Items[商品ID],"<>",Items[数量])

ড্রপডাউনের বিকল্প টেবিলের সঙ্গে যুক্ত করা

অন্য শিটে পণ্যের নামের ড্রপডাউন তৈরি করতে ডেস্কটপ Excel-এ Formulas → Name Manager খুলুন। পুরো ওয়ার্কবুকের জন্য ItemChoices নাম তৈরি করে Refers To-তে =Items[商品名] দিন। ইনপুট সেলে Data Validation থেকে List বেছে Source-এ =ItemChoices দিন। হেডার বিকল্পে আসে না এবং টেবিলের শেষে সারি যোগ করলে নতুন নাম দেখা যায়। ফাঁকা বিকল্প এড়াতে নতুন সারির নাম পূরণ করুন। ড্রপডাউন তালিকা তৈরির Microsoft নির্দেশিকা