আমরা এক্সেলে প্রথম যা শিখি তার মধ্যে একটি হল $ প্রতীকের জাদু। এটি সারি বা কলাম হিমায়িত করে, তাই একটি সূত্র অনুলিপি করার সময় ঘরের রেফারেন্স পরিবর্তন হয় না। এটি একটি পরম রেফারেন্স হিসাবে পরিচিত। সারণী প্রবর্তনের সাথে সাথে রেফারেন্স সেলের একটি ভিন্ন (এবং আরও শব্দার্থিক) উপায় এসেছে, যাকে বলা হয় কাঠামোগত রেফারেন্স। যাইহোক, স্ট্রাকচার্ড রেফারেন্সগুলি আমরা সাধারণত যে স্ট্যান্ডার্ড A1 স্টাইল রেফারেন্সিং সিস্টেম ব্যবহার করি সেই নীতিগুলি অনুসরণ করে না। ফলস্বরূপ, $ প্রতীক পদ্ধতি কাজ করবে না। কিন্তু চিন্তা করবেন না; এই পোস্টের শেষে, আপনি জানতে পারবেন কিভাবে একটি এক্সেল টেবিল পরম রেফারেন্স তৈরি করতে হয়।
ফ্রিল্যান্সিং ক্যারিয়ার গড়তে এক্সেল ভিবিএ শিখুন
ভিডিও টিউটোরিয়াল পেতে চ্যানেলটি লাইক ও সাবস্ক্রাইব করে সাথেই থাকুন
https://youtube.com/@KarimExcelVba
#Karim_ExcelVba
দৃশ্যকল্প:
এই পোস্টের সমস্ত উদাহরণগুলি নিচের ছবির টেবিলে ব্যবহার করা হয়েছে।
ফার্মে বিভিন্ন ধরনের প্রাণী রাখার জন্য কত খরচ হতে পারে তা টেবিলে দেখানো হয়েছে। টেবিলের নামটি হল myTable, যদিও এটি একটি দুর্দান্ত নাম নয়, এটি এই উদাহরণের জন্য কাজ করবে।
কলামের জন্য এক্সেল টেবিল পুর্ন রেফারেন্স
স্ট্রাকচার্ড রেফারেন্স ব্যবহার করার সময়, পুরো কলামগুলি এই সিনট্যাক্সের সাথে উল্লেখ করা হয়:
tableName[columnName]
মোট কলাম যোগ করার জন্য নিচের উদাহরণের ডাটাতে যে সূত্রটি ব্যবহার হয়েছে:
=SUM(myTable[Total])
যদি এটি অন্য কলামে টেনে আনা বা অনুলিপি করা হয়, তাহলে সূত্রটি স্বয়ংক্রিয়ভাবে পরিবর্তিত হবে। বাম দিকে অনুলিপি করা হলে, এটি এতে পরিবর্তন হবে:
=SUM(myTable[Qty])
ডানদিকে অনুলিপি করা হলে, এটি টেবিলের প্রথম কলামে ফিরে আসবে এবং এতে পরিবর্তন হবে:
=SUM(myTable[Animal])
বিঃদ্রঃ: টেবিল কলামের সাথে কাজ করার সময় এটি একটি quirks. একবার একটি কাঠামোগত কলাম রেফারেন্স টেবিলের শেষে পৌঁছে গেলে, এটি আবার শুরুতে লুপ করে।
যদি স্ট্যান্ডার্ড A1 স্টাইল রেফারেন্সিং ব্যবহার করা হয়, আমরা $ চিহ্ন যোগ করতে পারি এবং G2:G9 (একটি আপেক্ষিক রেফারেন্স) থেকে $G$2:$G$9 (একটি পুর্ন রেফারেন্স) পরিসর পরিবর্তন করতে পারি। কিন্তু আমরা টেবিলে পাওয়া কাঠামোগত রেফারেন্সের সাথে $ ব্যবহার করতে পারি না।
একই ফলাফল পেতে, আমাদের আরও বর্গাকার বন্ধনী যোগ করতে হবে, একটি কোলন ( : ), এবং কলামের নামটি পুনরাবৃত্তি করতে হবে।
=SUM(myTable[[Total]:[Total]])
কলামগুলির জন্য আপেক্ষিক এবং পরম রেফারেন্সের মধ্যে পার্থক্য উপরে নীল রঙে দেখানো হয়েছে।
আপনি যদি কিছু সময়ের জন্য টেবিল ব্যবহার করে থাকেন, আপনি লক্ষ্য করবেন যে এটি একই সিনট্যাক্স যা একাধিক কলাম উল্লেখ করার সময়।
=SUM(myTable[[Food]:[Other]])উপরের রেফারেন্সটি উদাহরণের ডেটাতে খাদ্য থেকে অন্যান্য কলামগুলিকে কীভাবে যোগ করতে হয় তা দেখায়। এর মানে মাউস ব্যবহার করে নির্বাচিত মাল্টি-কলাম রেফারেন্স ডিফল্টরূপে পরম।
একটি আপেক্ষিক মাল্টি-কলাম রেফারেন্স তৈরি করতে, আমাদের বাইরের বর্গাকার বন্ধনীগুলি সরাতে হবে এবং নীচে দেখানো হিসাবে টেবিলের নামটি পুনরাবৃত্তি করতে হবে।
=SUM(myTable[Food]:myTable[Other])অথবা আপনি নীচের হিসাবে দেখানো হিসাবের মধ্যে প্রতিটি কলাম পৃথকভাবে অন্তর্ভুক্ত করতে পারেন, যা একই ফলাফল অর্জন করে।
=SUM(myTable[Food],myTable[Keepers],myTable[Other])একই সারিতে থাকা কক্ষগুলির জন্য আপেক্ষিক এবং পরম রেফারেন্স৷
টেবিল ব্যবহার করার সময়, একই সারিতে একটি ঘরের উল্লেখ করার জন্য সিনট্যাক্স নিম্নরূপ।
=[@Total] or =[@[Total Value]]দ্রষ্টব্য: উপরের উদাহরণের ২য় সিনট্যাক্স হল যেখানে হেডারে একটি স্পেস বা বিশেষ অক্ষর রয়েছে।
যদি সূত্রটি টেবিলের বাইরে একটি কক্ষে থাকে, তাহলে টেবিলের নামটিও যোগ করতে হবে:
=myTable[@Total] or =myTable[@[Total Value]একটি সারি রেফারেন্স পরম করতে, আমরা কলাম রেফারেন্সের জন্য যেমন দেখেছি একই নীতিগুলি প্রযোজ্য। রেফারেন্সটি টেবিলের ভিতরে বা বাইরে কিনা তা বিবেচ্য নয়; উভয়ের জন্য টেবিলের নাম প্রয়োজন।
=myTable[@[Total]:[Total]]একাধিক কলাম উল্লেখ করতে, সিনট্যাক্স একই রকম।
=SUM(myTable[@[Food]:[Other]])উপরের রেফারেন্সটি উদাহরণের ডেটা থেকে খাদ্য থেকে অন্যান্য কলামগুলিকে কীভাবে যোগ করতে হয় তা দেখায়। এটি একটি পরম কলাম রেফারেন্স.
একটি আপেক্ষিক মাল্টি-কলাম রেফারেন্স তৈরি করতে, আমাদের বাইরের বর্গাকার বন্ধনীগুলি সরাতে হবে।
=SUM([@Food]:[@Other]) or =SUM(myTable[@Food]:myTable[@Other])দ্রষ্টব্য: @ প্রতীকটি গতিশীল অ্যারেগুলির সাথে কাজ করার সময় একই উদ্দেশ্যে কাজ করে; এটা অন্তর্নিহিত ছেদ উপর ভিত্তি করে গণনা. আরও তথ্যের জন্য এই পোস্টটি দেখুন: Excel এ ডায়নামিক অ্যারে
যেকোনো কক্ষের জন্য আপেক্ষিক এবং পরম রেফারেন্স
উপরের বিভাগে, আমরা সেই পরিস্থিতি বিবেচনা করেছি যেখানে কলামটি পরম ছিল যখন সারিটি একই সারির একটি ঘরের উপর ভিত্তি করে ছিল। যাইহোক, একটি সারি উল্লেখ করতে যা একই সারিতে নেই ভিন্ন পদ্ধতির।
INDEX ফাংশন একটি পরিসর বা অ্যারেতে একটি নির্দিষ্ট অবস্থান থেকে একটি সেল রেফারেন্স বা মান প্রদান করে। একটি সাধারণ উদাহরণ হিসাবে, নিম্নলিখিত সূত্রটি দেখুন:
=INDEX($G$2:$G$9,1)এই সূত্রটি সর্বদা G2:G9 সেলে থেকে প্রথম সারি ফিরিয়ে দেবে। একই পরিসর থেকে দ্বিতীয় সেলে ফেরত দিতে, সূত্র হবে
=INDEX($G$2:$G$9,2)
অতএব, কাঠামোগত রেফারেন্স সহ INDEX ব্যবহার করা আমাদের পরিসরের যেকোন পৃথক সেলকে উল্লেখ করতে দেয়। উদাহরণস্বরূপ, নিম্নলিখিতটি মোট কলাম থেকে প্রথম আইটেমটি প্রদান করে।
=INDEX(myTable[Total],1)Tow একটি পরম রেফারেন্স, কিন্তু কলাম আপেক্ষিক; তাই এটি একটি মিশ্র রেফারেন্স।
একটি সম্পূর্ণ আপেক্ষিক রেফারেন্স তৈরি করতে, আমাদের অবশ্যই INDEX-এর মধ্যে সারি সংখ্যা গণনা করার উপায় খুঁজে বের করতে হবে।
নিম্নলিখিত একটি আপেক্ষিক সারি রেফারেন্স গণনা করতে ROW(A1) ব্যবহার করে। ROW(A1) মূল্যায়ন করে 1. যখন নিচে টেনে আনা হয়, ROW(A2) মূল্যায়ন করে 2 এ। তাই ROW INDEX ফাংশনে ব্যবহার করার জন্য আপেক্ষিক সংখ্যা তৈরি করে।
=INDEX(myTable[Total],ROW(A1))উপরের সূত্রটি একটি সম্পূর্ণ আপেক্ষিক টেবিল রেফারেন্স।
হেডার এবং টোটালে পরম রেফারেন্স
একটি টেবিলের মধ্যে হেডার এবং মোট সারি উল্লেখ করা আবার সামান্য ভিন্ন। কারণ শিরোনাম এবং মোটের অন্তর্নিহিতভাবে নিখুঁত সারি রেফারেন্স রয়েছে তবে কলামের উল্লেখ নেই।
হেডারের আপেক্ষিক রেফারেন্স নীচে দেখানো হয়েছে:
=myTable[[#Headers],[Food]]হেডার রেফারেন্সটিকে একটি পরম রেফারেন্সে পরিণত করতে, নীচে হাইলাইট করা বিভাগটি যোগ করুন।
=myTable[[#Headers],[Food]:[Food]]আপনি যদি এই পোস্টটি পুরোটা পড়ে থাকেন, তাহলে মাল্টি-কলামের পরম রেফারেন্স আপনাকে অবাক করবে না। কলামের শিরোনাম গণনা করার সূত্রটি নীচে দেখানো হয়েছে।
=COUNTA(myTable[[#Headers],[Food]:[Other]])এবং মাল্টি-কলাম আপেক্ষিক রেফারেন্স আপনাকে অবাক করবে না।
=COUNTA(myTable[[#Headers],[Food]]:myTable[[#Headers],[Other]])এই সেকশনের সব উদাহরণে হেডার সারি (#হেডার) অন্তর্ভুক্ত। মোট সারি উল্লেখ করতে, পরিবর্তে #Totals ব্যবহার করুন।
উপসংহার
একটি টেবিলের সাথে, আপেক্ষিক বা পরম রেফারেন্স প্রদানের জন্য বিভিন্ন সিনট্যাক্স বেশ সূক্ষ্ম হতে পারে। একটি @ বা বর্গাকার বন্ধনী স্থানের বাইরে একটি ত্রুটি সৃষ্টি করতে পারে যা খুঁজে পাওয়া অবিশ্বাস্যভাবে হতাশাজনক হতে পারে। স্ক্র্যাচ থেকে রেফারেন্স টাইপ করার পরিবর্তে একটি বৈধ রেফারেন্স দিয়ে শুরু করা এবং তারপরে এটি সামঞ্জস্য করা সহজ।
সমস্ত জিনিসের মতো, অনুশীলন অত্যাবশ্যক। অনেক আগেই, একটি এক্সেল টেবিল পরম রেফারেন্স তৈরি করা হবে দ্বিতীয় প্রকৃতি।
আমাদের সম্পর্কে জানতে এবং বিভিন্ন টিউটোরিয়াল পেতে লাইক ও সাবস্ক্রাইব করে সাথেই থাকুন
https://www.facebook.com/ExcelVbaCoding
https://youtube.com/@KarimExcelVba
https://www.instagram.com/karimexcelvba/
https://twitter.com/KarimExcelVBA
https://www.linkedin.com/in/karimexcel/
https://www.quora.com/profile/KarimExcelVBA
https://www.reddit.com/user/KarimExcelVBA
https://www.tumblr.com/karimexcelvba
https://www.flickr.com/photos/karimexcelvba/
https://www.pinterest.com/KarimExcelVBA/