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।
মাঝখানে কোনো হেডার ফাঁকা থাকলেও শেষের দিকের কলাম রেঞ্জের বাইরে যেতে পারে। ফর্মুলা ফাঁকা স্ট্রিং ("") ফেরত দিলেও 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 নির্দেশিকা