টেকটিউনসের সবাইকে জানাই আন্তরিক শুভেচ্ছা। আশা করছি সবাই খুব ভালো আছেন। আমার এই পার্বিক টিউটোরিয়াল এ থাকবে একদম বেসিক থেকে শুরু করে প্রফেশনাল স্টেজ পর্যন্ত এক্সেল এর ডিটেইলস টিউটরিয়াল। চলুন শুরু করি আজকের পর্ব।

সকল পর্বের লিস্ট টিউনের একদম শেষে দেয়া আছে।

এই টিউনে আমরা জানবো কিভাবে এক্সেলে রেফারেন্স ওয়ার্কশীট ব্যবহার করতে হয়। এক্সেলে বিভিন্ন সেল-এর মধ্যে ডাটা জমা থাকে। প্রতিটি ওয়ার্কশীটের 17, 179, 869, 184 গুলো সেল আছে এবং আপনি প্রতিটি এক্সেল ওয়ার্কবুকে 255 টি শীট এড করতে পারবেন। এক্সেলে কাজ করার সময়ে অনেক সময় আমাদের প্রয়োজন হতে পারে যে ওয়ার্কবুকে কাজ করছেন সেটার বা অন্য কোনো ওয়ার্কবুকের (খোলা বা বন্ধ) রেফারেন্স ব্যবহার করা। এই সব বিষয়েই আমরা এই টিউনে বিশদ আলোচনা করবো।

যা যা থাকছে

প্রাকটিস ওয়ার্কবুক ডাউনলোড করা
এক্সেলে ওয়ার্কশীটের রেফারেন্স ব্যবহার করার ৫টি উদাহরণ
১. একই ওয়ার্কবুক থেকে রেফারেন্স ওয়ার্কশীট
২. বিভিন্ন ওয়ার্কবুক থেকে রেফারেন্স ওয়ার্কশীট
২.১. বন্ধ ওয়ার্কবুক
২.২. খোলা ওয়ার্কবুক
৩. অন্যান্য ওয়ার্কশীট থেকে ডিফাইন নেইম রেফারেন্স করা
৩.১. একই ওয়ার্কবুক
৩.২. অন্য ওয়ার্কবুক
৩.২.১. খোলা ওয়ার্কবুক
৩.২.২. বন্ধ ওয়ার্কবুক
৪. ভ্যালুর উপর ভিত্তি করে ডাইনামিক্যালি অন্য শীটের সেল রেফারেন্স করা
৪.১. একই ওয়ার্কবুক
৪.২. অন্য ওয়ার্কবুক
৫. VBA ব্যবহার করে ওয়ার্কশীটের নামের মাধ্যমে রেফারেন্স করা
৫.১. শীটের কোড নামের মাধ্যমে রেফারেন্স করা
৫.২. শীটের নামের মাধ্যমে রেফারেন্স করা
৫.৩. সম্প্রতি দেখা হয়েছে এমন ওয়ার্কশীটকে রেফারেন্স করা
৫.৪. ওয়ার্কশীটের অবস্থান অনুযায়ী রেফারেন্স করা
৫.৫. সর্বশেষ ওয়ার্কশীটকে রেফারেন্স করা
৫.৬. অন্য ওয়ার্কবুকের ওয়ার্কশীট রেফারেন্স করা
উপসংহার

প্রাকটিস ওয়ার্কবুক ডাউনলোড করা

টিউনটি পড়ে সাথে সাথে প্রাকটিসের জন্য নিচের প্রাকটিস ওয়ার্কবুক দুটো ডাউনলোড করে নিন।

এক্সেলে ওয়ার্কশীটের রেফারেন্স ব্যবহার করার ৫টি উদাহরণ

এবার আমরা এক্সেলে ওয়ার্কশীট রেফারেন্সের কিছু উদাহরণ দেখবো।

১. একই ওয়ার্কবুক থেকে রেফারেন্স ওয়ার্কশীট

আপনি কাজ করছেন ঐ একই ওয়ার্কবুকের ওয়ার্কশীট রেফারেন্স করা খুবই সহজ। নিচের পদ্ধতি অনুসরণ করুন।

পদ্ধতিঃ

• যে সেলে রেফারেন্সটি রাখা হবে, সেই সেলটি সিলেক্ট করুন। এখানে সেল C5.
• রেফারেন্স তৈরি করতে, টাইপ করুন =. এখন নিচের ইমেজের মতো আমরা যেকোনো শীট সিলেক্ট করবো।

• যে ওয়ার্কশীটকে রেফারেন্স করবেন, সেখানে যেয়ে যে সেলটিকে (এখানে সেল G6) রেফার করবেন তা ক্লিক করুন এবং Enter চাপুন।

• এবার ফিল হ্যান্ডেল দিয়ে এই কলামের বাকি সেলগুলো ফিল আপ করুন।

২. বিভিন্ন ওয়ার্কবুক থেকে রেফারেন্স ওয়ার্কশীট

বিভিন্ন ওয়ার্কবুক থেকে রেফারেন্স করার সময় সেগুলো খোলা বা বন্ধ থাকতে পারে। আমরা দুটো পদ্ধতি-ই শিখবো।

২.১. বন্ধ ওয়ার্কবুক

যখন আমরা অন্য কোনো ওয়ার্কশীট খুলে তাতে রেফার করবো, তখন সেল/রেঞ্জ এবং শীটের নামের সাথে সাথে ফাইলের নামও উল্লেখ করে দিতে হবে। তাই যদি সোর্স ওয়ার্কবুকটি খোলা না থাকে, তাহলে সেল/রেঞ্জ রেফার করলেও এক্সেল খুঁজে পাবে না কোন ওয়ার্কশীট থেকে সে রেফার নিবে। আর এ কারণেই, যখনই আমরা বন্ধ ওয়ার্কবুক থেকে রেফারেন্স তৈরি করবো, আমাদেরকে ঐ ফাইলটি কোথায় আছে, সেই এড্রেসও উল্লেখ করতে হবে।

📌 পদ্ধতিঃ

• Example File ওয়ার্কবুকে MARCH ওয়ার্কশীটে G6 সেলে একটি রেফারেন্স নিচে দেখানো হয়েছে। যেহেতু ফাইলটি এখন বন্ধ, তাই ফর্মুলাতে ফাইলটি কোথায় আছে তাও রেফার করা হয়েছে। ='C:\Users\Desktop\MARCH'!$G$6
• পূর্বের আলোচনা অনুযায়ী রেফারেন্সে নিচের ইলেমেন্টগুলো অন্তর্ভুক্তঃ

♣ File Path - ঐ ফাইলটির লোকেশন
♣ Filename - ঐফাইলটির নাম
♣ Sheet Name - G6 সেলটি যেখানে আছে সেই ওয়ার্কশীটের নাম
♣ Cell/Range Location - সেলের নাম। এই ক্ষেত্রে G6.

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

২.২. খোলা ওয়ার্কবুক

এক্সটারনাল খোলা ওয়ার্কবুককে রেফারেন্স করার সময়ে, আমাদেরকে অবশ্যই ওয়ার্কবুকের নাম, ওয়ার্কশীটের নাম এবং সেল/রেঞ্জের এড্রেস উল্লেখ করতে হবে। এক্সটারনাল খোলা ওয়ার্কশীট রেফার করার ফরম্যাটটি এমনঃ ='[FileName]SheetName!CellAddress

📌 পদ্ধতিঃ

• যে সেলে রেফারেন্সটি রাখা হবে, সেই সেলটি সিলেক্ট করতে হবে।
• রেফারেন্স তৈরি করতে = চাপুন।

• এরপর টাস্কবারে এক্সেলের উপর কার্সরটি রেখে খোলা ওয়ার্কবুকটি সিলেক্ট করতে হবে।

• এরপর সামারি টেবিলে আমরা যা সেলটি চাই, তাকে সিলেক্ট করতে হবে।

• এবার ফিল হ্যান্ডেল দিয়ে এই কলামের বাকি সেলগুলো ফিল আপ করতে হবে।

• Summary table এভাবে March এর cost এড করা হয়ে গেলো এবং summary table টি দেখতে নিচের ইমেজের মতো লাগবে।

• এখন মজার বিষয় হলো, সোর্স ওয়ার্কবুকটি বন্ধ করার পর খোলা ওয়ার্কবুকের রেফারেন্স ফর্মুলাটি অটোমেটিক্যালি পরিবর্তন হয়ে যাবে।

• এক্সেল তখন সোর্স ওয়ার্কবুকের ফাইল লোকেশনটিকে নিয়ে নেয়। এখন কথা হচ্ছে, আমরা কিন্তু এই একই ফর্মুলা বন্ধ ওয়ার্কবুকের ক্ষেত্রেও ব্যবহার করেছি।

৩. অন্যান্য ওয়ার্কশীট থেকে ডিফাইন নেইম রেফারেন্স করা

আমরা যদি এই ডিফাইন নেইম অনেক ফর্মুলাতে ব্যবহার করি এবং রেফারেন্সকে মডিফাই করার প্রয়োজন পড়ে, তাহলে আমাদেরকে শুধু একবারই করতে হবে। অনেকগুলো সেলে নেইম রেঞ্জ তৈরি করার পদ্ধতি নিম্নে দেয়া হলো।

📌 পদ্ধতিঃ

• নেইম রেঞ্জে অন্তর্ভুক্ত করা হবে এমন সেলগুলোকে সিলেক্ট করতে হবে।
• ফর্মুলা ট্যাবে যেতে হবে।
• Defined Names গ্রুপে Define Name অপশনটি সিলেক্ট করতে হবে।

• New Name ডায়লগ বক্সে সেলটিকে একটি নাম দিতে হবে; নিচের ইমেজে সেলটিকে Big-Mac নাম দেয়া হয়েছে। মনে রাখবেন নামের মাঝে কোনো স্পেস রাখা যাবে না।

৩.১. একই ওয়ার্কবুক

• আমরা অন্য একটি ওয়ার্কবুকে সেলের নামকরণ করেছি কিন্তু summary worksheet এ শুধুমাত্র সেলটির নাম দিয়েই তাকে কল করা যাবে।

• Enter চাপতে হবে এবং নিচের ইমেজের মতো একটি সেল ভ্যালু পাওয়া যাবে।

৩.২. অন্য ওয়ার্কবুক

৩.২.১. খোলা ওয়ার্কবুক

ধরে নিন, আমাদের কাছে source workbook.xlsx নামের একটি এক্সেল ওয়ার্কবুক আছে এবং Hamburger নামে একটি নেইম রেঞ্জ আছে। এবার আমরা নিচের ফর্মুলা ব্যবহার করে অন্য ওয়ার্কবুকে Hamburger এর কতগুলো সেল আছে, আমরা বের করবো।

যদি ওয়ার্কবুকের নামে কোনো স্পেস না থাকে, তাহলে আমরা নিচের ফর্মুলা ব্যবহার করবোঃ

৩.২.২. বন্ধ ওয়ার্কবুক

যদি আমরা বন্ধ ওয়ার্কবুকের নেইম রেঞ্জে প্রবেশ করতে চাই, তাহলে আমাদেরকে অবশ্যই ফাইল লোকেশন উল্লেখ করতে হবে। নিচের ফর্মুলাটি দেখুনঃ

৪. ভ্যালুর উপর ভিত্তি করে ডাইনামিক্যালি অন্য শীটের সেল রেফারেন্স করা

আমরা INDIRECT ফাংশন ব্যবহার করে অন্য ওয়ার্কশীটকে রেফার করতে পারবো। চলুন কিছু উদাহরণ দেখা যাক।

৪.১. একই ওয়ার্কবুক

ধরুন, APRIL ওয়ার্কশীটে আমাদের কিছু গুরুত্ত্বপূর্ণ তথ্য আছে যা Summary Datasheet এ আমরা বসাবো। নিচের ইমেজে দেখানো হয়েছে কিভাবে এক্সেলে একটি INDIRECT ফাংশন এই কাজটি করবেঃ =INDIRECT("'"&$G$6&"'!"&G7)

এখানে,

• শীটের নাম আছে $G$6 এ।
• G7 সেল থেকে ডাটা তুলে আনা হবে।

৪.২. অন্য ওয়ার্কবুক

এখন আমরা চাচ্ছি অন্য ওয়ার্কবুক থেকে একটি স্ট্যান্ডার্ড রেফারেন্স তৈরি করবো (যদি ওয়ার্কবুকে বা শীটের নামে স্পেস থাকে, তাহলে apostrophe ব্যবহার করতে হবে)

এখানে,

• ওয়ার্কবুকের নামে আছে $H$8 এ
• শীটের নাম আছে $I$8 এ
• সেল এড্রেস আছে J8 এ।

৫. VBA ব্যবহার করে ওয়ার্কশীটের নামের মাধ্যমে রেফারেন্স করা

VBA ব্যবহার করে বিভিন্ন উপায়ে ওয়ার্কশীটের রেফারেন্স দেয়া যায়। চলুন কিছু উদাহরণ দেখে নেয়া যাক।

৫.১. শীটের কোড নামের মাধ্যমে রেফারেন্স করা

শীটের নাম যদি পরিবর্তনও করা হয়, এক্সেল কোড নামের (sheet1, sheet2, ইত্যাদি) মাধ্যমে শীটগুলোকে চিনতে পারবে।

উদাহরণঃ

>> এই ম্যাক্রোটি sheet1 এর F9 সেলে 79 (numeric value) রিটার্ন করবে।

৫.২. শীটের নামের মাধ্যমে রেফারেন্স করা

শীটের নাম যদি পরিবর্তন করা হয়, এরপরেও VBA এর মাধ্যমে আপনি তাদেরকে রেফার করতে পারবেন। এক্ষেত্রে, নিচের ম্যাক্রোর মতো উদ্ধৃতি চিহ্নের মধ্যে শীটের নামকে রাখতে হবে।

উদাহরণঃ

>> যে ওয়ার্কবুকটি আপনি প্রেজেন্ট করছেন, এই ম্যাক্রোটি সেখান থেকে JANUARY নামের ওয়ার্কশীটটি খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।

৫.৩. সম্প্রতি দেখা হয়েছে এমন ওয়ার্কশীটকে রেফারেন্স করা

আপনি যদি মাত্র দেখেছেন এমন কোনো ওয়ার্কশীট থেকে সরেও যান, তারপরেও VBA এর মাধ্যমে আপনি তাদেরকে রেফার করতে পারবেন। সেক্ষেত্রে আপনাকে নিচের ম্যাক্রোটি ব্যবহার করতে হবে।

উদাহরণঃ

>> এই ম্যাক্রোটি ওয়ার্কবুক থেকে সম্প্রতি দেখেছেন এমন ওয়ার্কশীটটিকে খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।

৫.৪. ওয়ার্কশীটের অবস্থান অনুযায়ী রেফারেন্স করা

আপনি যদি মাল্টিপল ওয়ার্কশীট নিয়ে কাজ করেন, তারপরেও আপনি VBA এর মাধ্যমে ওয়ার্কশীটের অবস্থানের দ্বারা রেফার করতে পারবেন। এই ক্ষেত্রে, আপনাকে নিচের ফর্মুলাটি ব্যবহার করতে হবে।

উদাহরণঃ

>> এই ম্যাক্রোটি আপনার ওয়ার্কশীটগুলোর মধ্যে প্রথম ওয়ার্কশীটটিকে খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।

৫.৫. সর্বশেষ ওয়ার্কশীটকে রেফারেন্স করা

উদাহরণঃ

>> এই ম্যাক্রোটি আপনার ওয়ার্কশীটগুলোর মধ্যে শেষ ওয়ার্কশীটটিকে খুঁজে বের করবে এবং F9 সেলে 79 রিটার্ন করবে।

৫.৬. অন্য ওয়ার্কবুকের ওয়ার্কশীট রেফারেন্স করা

উদাহরণঃ

>> এই ম্যাক্রোটি relative-absolute-references নামের ওয়ার্কবুকটি থেকে Summary Data ওয়ার্কশীটটি খুঁজে বের করবে এবং ঐ ওয়্যার্কশীটের F9 সেলে 79 রিটার্ন করবে।

উপসংহার

এই পদ্ধতিগুলোর মাধ্যমে আপনি এক্সেলে ওয়ার্কশীট রেফারেন্স ব্যবহার করতে পারবেন। আপনি চাইলে প্রাকটিস ওয়ার্কবুক ডাউনলোড করে নিজে নিজে প্রাকটিস করতে পারেন। যদি কোনো কিছু জানার বা সাহায্য লাগে, টিউমেন্টে জানাবেন।

পর্ব-৬ঃ এক্সেল আন্টোল্ড পর্ব-৬ – এবসলুট সেল রেফারেন্স বলতে কি বুঝায় বিস্তারিত বিশ্লেষণ
পর্ব-৫ঃ এক্সেল আন্টোল্ড পর্ব-৫ – রিলেটিভ সেল রেফারেন্স বলতে কি বুঝায় বিস্তারিত বিশ্লেষণ
পর্ব-৪ঃ এক্সেল আন্টোল্ড পর্ব-৪ –এক্সেল-এ সেল বলতে কি বোঝায়
পর্ব-৩ঃ এক্সেল আন্টোল্ড পর্ব-৩ – এক্সেল স্প্রেডশীটের আপাদমস্তক ২৯ ফ্যাক্টর
পর্ব-২ঃ এক্সেল আন্টোল্ড পর্ব-২ – কর্মক্ষেত্রে এক্সেল কোন কোন ক্ষেত্রে ব্যবহার করা হয়
পর্ব-১ঃ এক্সেল আন্টোল্ড পর্ব-১ – স্প্রেডশীট কি এবং এটা কিভাবে কাজ করে

টিউনটা ভালো লাগলে টিউমেন্ট করে জানাবেন। সবাইকে অসংখ্য ধন্যবাদ। ধন্যবাদ জানাই Techtunes কে। দেখা হচ্ছে পরের টিউটোরিয়ালে।