টেকটিউনসের সবাইকে জানাই আন্তরিক শুভেচ্ছা। আশা করছি সবাই খুব ভালো আছেন। আমার এই পার্বিক টিউটোরিয়াল এ থাকবে একদম বেসিক থেকে শুরু করে প্রফেশনাল স্টেজ পর্যন্ত এক্সেল এর ডিটেইলস টিউটরিয়াল। চলুন শুরু করি আজকের পর্ব।
সকল পর্বের লিস্ট টিউনের একদম শেষে দেয়া আছে।
এই টিউনে আমরা জানবো কিভাবে এক্সেলে রেফারেন্স ওয়ার্কশীট ব্যবহার করতে হয়। এক্সেলে বিভিন্ন সেল-এর মধ্যে ডাটা জমা থাকে। প্রতিটি ওয়ার্কশীটের 17, 179, 869, 184 গুলো সেল আছে এবং আপনি প্রতিটি এক্সেল ওয়ার্কবুকে 255 টি শীট এড করতে পারবেন। এক্সেলে কাজ করার সময়ে অনেক সময় আমাদের প্রয়োজন হতে পারে যে ওয়ার্কবুকে কাজ করছেন সেটার বা অন্য কোনো ওয়ার্কবুকের (খোলা বা বন্ধ) রেফারেন্স ব্যবহার করা। এই সব বিষয়েই আমরা এই টিউনে বিশদ আলোচনা করবো।
যা যা থাকছে
প্রাকটিস ওয়ার্কবুক ডাউনলোড করা
এক্সেলে ওয়ার্কশীটের রেফারেন্স ব্যবহার করার ৫টি উদাহরণ
১. একই ওয়ার্কবুক থেকে রেফারেন্স ওয়ার্কশীট
২. বিভিন্ন ওয়ার্কবুক থেকে রেফারেন্স ওয়ার্কশীট
২.১. বন্ধ ওয়ার্কবুক
২.২. খোলা ওয়ার্কবুক
৩. অন্যান্য ওয়ার্কশীট থেকে ডিফাইন নেইম রেফারেন্স করা
৩.১. একই ওয়ার্কবুক
৩.২. অন্য ওয়ার্কবুক
৩.২.১. খোলা ওয়ার্কবুক
৩.২.২. বন্ধ ওয়ার্কবুক
৪. ভ্যালুর উপর ভিত্তি করে ডাইনামিক্যালি অন্য শীটের সেল রেফারেন্স করা
৪.১. একই ওয়ার্কবুক
৪.২. অন্য ওয়ার্কবুক
৫. VBA ব্যবহার করে ওয়ার্কশীটের নামের মাধ্যমে রেফারেন্স করা
৫.১. শীটের কোড নামের মাধ্যমে রেফারেন্স করা
৫.২. শীটের নামের মাধ্যমে রেফারেন্স করা
৫.৩. সম্প্রতি দেখা হয়েছে এমন ওয়ার্কশীটকে রেফারেন্স করা
৫.৪. ওয়ার্কশীটের অবস্থান অনুযায়ী রেফারেন্স করা
৫.৫. সর্বশেষ ওয়ার্কশীটকে রেফারেন্স করা
৫.৬. অন্য ওয়ার্কবুকের ওয়ার্কশীট রেফারেন্স করা
উপসংহার
টিউনটি পড়ে সাথে সাথে প্রাকটিসের জন্য নিচের প্রাকটিস ওয়ার্কবুক দুটো ডাউনলোড করে নিন।
| Source Workbook.xlsx |
| Destination File.xlsx |
এবার আমরা এক্সেলে ওয়ার্কশীট রেফারেন্সের কিছু উদাহরণ দেখবো।
আপনি কাজ করছেন ঐ একই ওয়ার্কবুকের ওয়ার্কশীট রেফারেন্স করা খুবই সহজ। নিচের পদ্ধতি অনুসরণ করুন।
পদ্ধতিঃ
বিভিন্ন ওয়ার্কবুক থেকে রেফারেন্স করার সময় সেগুলো খোলা বা বন্ধ থাকতে পারে। আমরা দুটো পদ্ধতি-ই শিখবো।
যখন আমরা অন্য কোনো ওয়ার্কশীট খুলে তাতে রেফার করবো, তখন সেল/রেঞ্জ এবং শীটের নামের সাথে সাথে ফাইলের নামও উল্লেখ করে দিতে হবে। তাই যদি সোর্স ওয়ার্কবুকটি খোলা না থাকে, তাহলে সেল/রেঞ্জ রেফার করলেও এক্সেল খুঁজে পাবে না কোন ওয়ার্কশীট থেকে সে রেফার নিবে। আর এ কারণেই, যখনই আমরা বন্ধ ওয়ার্কবুক থেকে রেফারেন্স তৈরি করবো, আমাদেরকে ঐ ফাইলটি কোথায় আছে, সেই এড্রেসও উল্লেখ করতে হবে।
📌 পদ্ধতিঃ
♣ File Path - ঐ ফাইলটির লোকেশন
♣ Filename - ঐফাইলটির নাম
♣ Sheet Name - G6 সেলটি যেখানে আছে সেই ওয়ার্কশীটের নাম
♣ Cell/Range Location - সেলের নাম। এই ক্ষেত্রে G6.
নোটঃ যখন আমরা খোলা ওয়ার্কবুকে এক্সটারনাল রেফারেন্স তৈরি করি, এরপর বন্ধ করে দেই, খেয়াল করবেন রেফারেন্সটি অটোমেটিক্যালি পরিবর্তন হয়ে যাচ্ছে। এক্সটারনাল ওয়ার্কবুকটি বন্ধ করার পর, এক্সেল অটোমেটিক্যালি রেফারেন্সের মধ্যে তার লোকেশন এড করে দেয়।
এক্সটারনাল খোলা ওয়ার্কবুককে রেফারেন্স করার সময়ে, আমাদেরকে অবশ্যই ওয়ার্কবুকের নাম, ওয়ার্কশীটের নাম এবং সেল/রেঞ্জের এড্রেস উল্লেখ করতে হবে। এক্সটারনাল খোলা ওয়ার্কশীট রেফার করার ফরম্যাটটি এমনঃ ='[FileName]SheetName!CellAddress
📌 পদ্ধতিঃ
আমরা যদি এই ডিফাইন নেইম অনেক ফর্মুলাতে ব্যবহার করি এবং রেফারেন্সকে মডিফাই করার প্রয়োজন পড়ে, তাহলে আমাদেরকে শুধু একবারই করতে হবে। অনেকগুলো সেলে নেইম রেঞ্জ তৈরি করার পদ্ধতি নিম্নে দেয়া হলো।
📌 পদ্ধতিঃ
৩.২.১. খোলা ওয়ার্কবুক
ধরে নিন, আমাদের কাছে source workbook.xlsx নামের একটি এক্সেল ওয়ার্কবুক আছে এবং Hamburger নামে একটি নেইম রেঞ্জ আছে। এবার আমরা নিচের ফর্মুলা ব্যবহার করে অন্য ওয়ার্কবুকে Hamburger এর কতগুলো সেল আছে, আমরা বের করবো।
| ='source workbook.xlsx'!Hamburger |
যদি ওয়ার্কবুকের নামে কোনো স্পেস না থাকে, তাহলে আমরা নিচের ফর্মুলা ব্যবহার করবোঃ
| =sourceworkbook.xlsx!Hamburger |
৩.২.২. বন্ধ ওয়ার্কবুক
যদি আমরা বন্ধ ওয়ার্কবুকের নেইম রেঞ্জে প্রবেশ করতে চাই, তাহলে আমাদেরকে অবশ্যই ফাইল লোকেশন উল্লেখ করতে হবে। নিচের ফর্মুলাটি দেখুনঃ
| ='C:\Users\Desktop\source workbook.xlsx'!Hamburger |
আমরা INDIRECT ফাংশন ব্যবহার করে অন্য ওয়ার্কশীটকে রেফার করতে পারবো। চলুন কিছু উদাহরণ দেখা যাক।
ধরুন, APRIL ওয়ার্কশীটে আমাদের কিছু গুরুত্ত্বপূর্ণ তথ্য আছে যা Summary Datasheet এ আমরা বসাবো। নিচের ইমেজে দেখানো হয়েছে কিভাবে এক্সেলে একটি INDIRECT ফাংশন এই কাজটি করবেঃ =INDIRECT("'"&$G$6&"'!"&G7)
এখানে,
এখন আমরা চাচ্ছি অন্য ওয়ার্কবুক থেকে একটি স্ট্যান্ডার্ড রেফারেন্স তৈরি করবো (যদি ওয়ার্কবুকে বা শীটের নামে স্পেস থাকে, তাহলে apostrophe ব্যবহার করতে হবে)
| =INDIRECT("'["&$H$8&"]"&$I$8&"'!"&J8) |
এখানে,
VBA ব্যবহার করে বিভিন্ন উপায়ে ওয়ার্কশীটের রেফারেন্স দেয়া যায়। চলুন কিছু উদাহরণ দেখে নেয়া যাক।
শীটের নাম যদি পরিবর্তনও করা হয়, এক্সেল কোড নামের (sheet1, sheet2, ইত্যাদি) মাধ্যমে শীটগুলোকে চিনতে পারবে।
উদাহরণঃ
| Sheet1.Range("F9").Value = 79 |
>> এই ম্যাক্রোটি sheet1 এর F9 সেলে 79 (numeric value) রিটার্ন করবে।
শীটের নাম যদি পরিবর্তন করা হয়, এরপরেও VBA এর মাধ্যমে আপনি তাদেরকে রেফার করতে পারবেন। এক্ষেত্রে, নিচের ম্যাক্রোর মতো উদ্ধৃতি চিহ্নের মধ্যে শীটের নামকে রাখতে হবে।
উদাহরণঃ
| ThisWorkbook.Worksheets("JANUARY").Range("F9").Value = 79 |
>> যে ওয়ার্কবুকটি আপনি প্রেজেন্ট করছেন, এই ম্যাক্রোটি সেখান থেকে JANUARY নামের ওয়ার্কশীটটি খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।
আপনি যদি মাত্র দেখেছেন এমন কোনো ওয়ার্কশীট থেকে সরেও যান, তারপরেও VBA এর মাধ্যমে আপনি তাদেরকে রেফার করতে পারবেন। সেক্ষেত্রে আপনাকে নিচের ম্যাক্রোটি ব্যবহার করতে হবে।
উদাহরণঃ
| ActiveSheet.Range("F9").Value = 79 |
>> এই ম্যাক্রোটি ওয়ার্কবুক থেকে সম্প্রতি দেখেছেন এমন ওয়ার্কশীটটিকে খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।
আপনি যদি মাল্টিপল ওয়ার্কশীট নিয়ে কাজ করেন, তারপরেও আপনি VBA এর মাধ্যমে ওয়ার্কশীটের অবস্থানের দ্বারা রেফার করতে পারবেন। এই ক্ষেত্রে, আপনাকে নিচের ফর্মুলাটি ব্যবহার করতে হবে।
উদাহরণঃ
| ThisWorkbook.Worksheets(1).Range("F9").Value = 79 |
>> এই ম্যাক্রোটি আপনার ওয়ার্কশীটগুলোর মধ্যে প্রথম ওয়ার্কশীটটিকে খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।
উদাহরণঃ
| ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Range("F9").Value = 79 |
>> এই ম্যাক্রোটি আপনার ওয়ার্কশীটগুলোর মধ্যে শেষ ওয়ার্কশীটটিকে খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।
উদাহরণঃ
| Workbooks("relative-absolute-references").Worksheets("Summary Data").Range("F9").Value = 79 |
>> এই ম্যাক্রোটি relative-absolute-references নামের ওয়ার্কবুকটি থেকে Summary Data ওয়ার্কশীটটি খুঁজে বের করবে এবং ঐ ওয়্যার্কশীটের F9 সেলে 79 রিটার্ন করবে।
এই পদ্ধতিগুলোর মাধ্যমে আপনি এক্সেলে ওয়ার্কশীট রেফারেন্স ব্যবহার করতে পারবেন। আপনি চাইলে প্রাকটিস ওয়ার্কবুক ডাউনলোড করে নিজে নিজে প্রাকটিস করতে পারেন। যদি কোনো কিছু জানার বা সাহায্য লাগে, টিউমেন্টে জানাবেন।
পর্ব-৬ঃ এক্সেল আন্টোল্ড পর্ব-৬ – এবসলুট সেল রেফারেন্স বলতে কি বুঝায় বিস্তারিত বিশ্লেষণ
পর্ব-৫ঃ এক্সেল আন্টোল্ড পর্ব-৫ – রিলেটিভ সেল রেফারেন্স বলতে কি বুঝায় বিস্তারিত বিশ্লেষণ
পর্ব-৪ঃ এক্সেল আন্টোল্ড পর্ব-৪ –এক্সেল-এ সেল বলতে কি বোঝায়
পর্ব-৩ঃ এক্সেল আন্টোল্ড পর্ব-৩ – এক্সেল স্প্রেডশীটের আপাদমস্তক ২৯ ফ্যাক্টর
পর্ব-২ঃ এক্সেল আন্টোল্ড পর্ব-২ – কর্মক্ষেত্রে এক্সেল কোন কোন ক্ষেত্রে ব্যবহার করা হয়
পর্ব-১ঃ এক্সেল আন্টোল্ড পর্ব-১ – স্প্রেডশীট কি এবং এটা কিভাবে কাজ করে
টিউনটা ভালো লাগলে টিউমেন্ট করে জানাবেন। সবাইকে অসংখ্য ধন্যবাদ। ধন্যবাদ জানাই Techtunes কে। দেখা হচ্ছে পরের টিউটোরিয়ালে।
আমি ম্যাড গেমার। বিশ্বের সর্ববৃহৎ বিজ্ঞান ও প্রযুক্তির সৌশল নেটওয়ার্ক - টেকটিউনস এ আমি 11 বছর 9 মাস যাবৎ যুক্ত আছি। টেকটিউনস আমি এ পর্যন্ত 19 টি টিউন ও 26 টি টিউমেন্ট করেছি। টেকটিউনসে আমার 2 ফলোয়ার আছে এবং আমি টেকটিউনসে 1 টিউনারকে ফলো করি।
