টেকটিউনসের সবাইকে জানাই আন্তরিক শুভেচ্ছা। আশা করছি সবাই খুব ভালো আছেন। আমার এই পার্বিক টিউটোরিয়াল এ থাকবে একদম বেসিক থেকে শুরু করে প্রফেশনাল স্টেজ পর্যন্ত এক্সেল এর ডিটেইলস টিউটরিয়াল। চলুন শুরু করি আজকের পর্ব।
সকল পর্বের লিস্ট টিউনের একদম শেষে দেয়া আছে।
এই টিউনে আমরা রিলেটিভ সেল রেফারেন্স সম্পর্কে গভীর আলোচনা করবো। এক্সেলে দুই ধরনের রেফারেন্স রয়েছে - রিলেটিভ রেফারেন্স এবং এবসলুট রেফারেন্স। রিলেটিভ এবং এবসলুট রেফারেন্স ভিন্ন ধরনের আউটপুট দিবে যখন তাদেরকে কপি করে অন্য সেলে পেস্ট করা হবে। যদি আপনি ফর্মুলাতে রিলেটিভ রেফারেন্স ব্যবহার করবেন, তাহলে ফর্মুলাটি পরিবর্তন হয়ে যাবে যদি আপনি ঐ একই ফর্মুলা অন্য সেলে কপি করেন। অন্যদিকে, এবসলুট রেফারেন্সে কোনো পরিবর্তন হবে না, তাতে আপনি যেখানেই কপি করেন না কেনো।
যা যা থাকছেঃ
এক্সেলে রিলেটিভ সেল রেফারেন্স বলতে কি বুঝায়
এক্সেলে কিভাবে রিলেটিভ সেল রেফারেন্স ব্যবহার করতে হয়
৫ টি যথোপযুক্ত উদাহরণ
১. Cost থেকে প্রাইস সহ ভ্যাট হিসাব করা
২. সর্বমোট মার্ক হিসাব করা
৩. গড় বের করা
৪. বয়স বের করা
৫. বেসিক বেতন এবং ইনক্রিমেন্ট থেকে বর্ধিত বেতন হিসাব করা
রিলেটিভ সেল রেফারেন্সের কীবোর্ড শর্টকাট
Excel OFFSET Function এ রিলেটিভ সেল রেফারেন্স
উপসংহার
রিলেটিভ সেল রেফারেন্স বুঝার আগে আমাদেরকে বুঝতে হবে ফর্মুলার সেল রেফারেন্স। ধরুন একটি ফর্মুলা ‘=B3*C3+D3’. এই ফর্মুলাতে তিনটি রেফারেন্স আছে - B3, C3, এবং D3. একটি ফর্মুলার প্রতিটি রেফারেন্সই এক্সেল ওয়ার্কশীটের একটি সেলের সাথে জড়িত। এক কথায়, প্রতিটি সেলের একটি ইউনিক রেফারেন্স আছে। অর্থাৎ B3 শুধুমাত্র একটি সেলের সাথেই সম্পর্ক স্থাপন করতে পারবে।
ডিফল্টভাবে, সকল সেল রেফারেন্সই রিলেটিভ রেফারেন্স। আপনি যখন রিলেটিভ রেফারেন্সের সাথে কোনো ফর্মুলা কপি করে অন্য আরেকটি সেলে বসাবেন, তাহলে রো এবং কলামের অবস্থানের উপর ভিত্তি করে রেফারেন্সগুলো পরিবর্তন হয়ে যাবে। ধরুন, E4 সেলে একটি ফর্মুলা B4*C4+D4. তাহলে ফর্মুলাটি হবে E4=B4*C4+D4. আপনি যদি AutoFill ব্যবহার করে এই ফর্মুলাটি E5 সেলে ড্র্যাগ করেন, তাহলে B4 হয়ে যাবে B5, C4 হয়ে যাবে C5 এবং D4 হয়ে যাবে D5. এক রো নিচে নামা যাক। এখন আপনি যদি এই ফর্মুলাটা (E4=B4*C4+D4) কে F4 এ কপি করেন তাহলে রেফারেন্সগুলো কি হবে? টিউমেন্টে জানাবেন। তখন ফর্মুলাটা হবে এমন F4=C4*D4+E4.
আপনার যদি একই ক্যাল্কুলেশন মাল্টিপল রো বা কলামে ব্যবহার করতে হয়, তখন রিলেটিভ রেফারেন্স ব্যবহার করা সুবিধাজনক। মুলত, প্রতিবারই ড্র্যাগ ফাংশন, অটো-ফিল ফিচার বা সেল কপি-পেস্ট করার মাধ্যমে এটি অটো আপডেট হতে থাকবে।
নিচের উদাহরণে আমরা মেনুর দাম এবং পরিমাণকে গুণ করে মেনু কস্ট (ছবিতে হেডিং খেয়াল করুন) বের করবো। আমরা প্রতিটি সেলে ফর্মুলা তৈরি করা করবো না, বরঞ্চ আমরা শুধুমাত্র E5 সেলে ফর্মুলা তৈরি করবো এবং অন্যান্য রো-তে এই ফর্মুলা কপি করবো। আমরা এখানে রিলেটিভ রেফারেন্স ব্যবহার করবো।
পদ্ধতিঃ
উপরের ইমেজটিতে খেয়াল করুন, প্রতিটি ভ্যালু তার বামের ভ্যালুর উপর ভিত্তি করে ক্যাল্কুলেট হয়েছে। E6 সেলটি সিলেক্ট করুন এবং এর ফর্মুলা বক্সটির দিকে তাকান, দেখবেন এই ভ্যালুটা C6 এবং D6 সেলের গুণফল।
এটাই হচ্ছে রিলেটিভ সেল রেফারেন্সের ক্ষমতা। আপনি শুধু সেল রেফারেন্স ব্যবহার করবেন আর এক্সেল অটোমেটিক্যালি অন্যান্য সেলে ফর্মুলাটা কপি করে যাবে।
তো আমরা রিলেটিভ সেল রেফারেন্সের প্রাথমিক ব্যবহার শিখে ফেলেছি। এবার আমরা এর আরো কিছু উদাহরণ এবং ব্যবহার শিখবো। নিচে ৫ টি উদাহরণ দেয়া হলো।
আসুন নিচের ডাটাসেটটি দেখি।
এই ডাটাসেটে আমরা দেখবো যদি আমরা Cost এর সাথে ভ্যাট এড করি, তাহলে প্রাইস কত দাঁড়ায়। আমরা সবগুলো প্রোডাক্টে এখানে ৫% ভ্যাট এড করবো। এখানে আমরা রিলেটিভ রেফারেন্স ব্যবহার করবো ফলে অন্য সেলগুলোতে অটোমেটিক্যালি ক্যাল্কুলেশন চলে আসবে।
পদ্ধতিঃ
এভাবে আপনি এক্সেলে রিলেটিভ সেল রেফারেন্স ব্যবহার করে ভ্যাট এড করার পরে প্রাইস কত হয় তা বের করতে পারবেন।
নিচের ডাটাসেটটি দেখুন।
এই ডাটাসেটে কয়েকজন ব্যক্তির বিভিন্ন বিষয়ে প্রাপ্ত নাম্বার দেখানো হয়েছে। আমরা প্রথম ব্যক্তির সর্বমোট নাম্বার হিসাব করবো। এবং রিলেটিভ রেফারেন্স ব্যবহার করার ফলে আমরা দেখতে পাবো বাকি সেলগুলোতে ফর্মুলা অটোমেটিক্যালি বসে যাবে।
পদ্ধতিঃ
এই ছিল আরেকটি উদাহরণ যেখানে রিলেটিভ সেল রেফারেন্স ব্যবহার করে অটোমেটিক্যালি ফর্মুলা কপি করার পদ্ধতি দেখানো হয়েছে।
এই উদাহরণে আমরা ঐ একই ডাটাসেট থেকে রিলেটিভ রেফারেন্স ব্যবহার করে নাম্বারগুলোর গড় বের করবো।
পদ্ধতিঃ
তো এভাবে আমরা রিলেটিভ সেল রেফারেন্স ব্যবহার করে মাল্টিপল ডাটার গড় বের করতে পারবো।
এবার আমরা যে ফর্মুলাটি ব্যবহার করতে যাচ্ছি, সেটার মধ্যে থাকবে একটি ফাংশন যা পরবর্তীতে রিলেটিভ রেফারেন্স ব্যবহার করবে। আর এর ফলে আমরা দেখতে পাবো যে, আমরা স্ব স্ব রেফারেন্সের মাধ্যমে ফর্মুলা কপি করতে পারি।
আমরা নিচের ডাটাসেটটি ব্যবহার করে দেখবো কারা কারা এডাল্ট আর কারা না।
এর জন্য আমরা ব্যবহার করবো IF ফাংশন। নিচের স্টেপগুলো অনুসরণ করুন।
পদ্ধতিঃ
এভাবে আমরা এক্সেলে রিলেটিভ সেল রেফারেন্স ব্যবহার করে ফাংশন সহ ফর্মুলা অটোমেটিক্যালি ব্যবহার করতে পারবো।
আসুন এবার রিলেটিভ রেফারেন্সের আরেকটি উদাহরণ দেখি। নিচের ডাটাসেটটি নিয়ে আমরা কাজ করবো।
নিচের পদ্ধতিগুলো অনুসরণ করুন যে কিভাবে রিলেটিভ সেল রেফারেন্স ব্যবহার করে বর্ধিত বেতন বের করা যায়।
পদ্ধতিঃ
এভাবেই এক্সেলে রিলেটিভ সেল রেফারেন্স ব্যবহার করে বর্ধিত বেতন হিসাব করা যায়।
রিলেটিভ এবং এবসলুট সেল রেফারেন্স সুইচ করার একটি কীবোর্ড কী আছে। যদিও বেশির ভাগ ক্ষেত্রেই এটা ব্যবহার হয় না। কী টি হচ্ছে F4.
যেকোনো একটি সেলে টাইপ করুন =B4. ডিফল্টভাবে এটা রিলেটিভ রেফারেন্স। এখন যদি আপনি F4 প্রেস করেন, তখন সেলটি হবে এরকমঃ =$B$4. এটা হচ্ছে এবসলুট রেফারেন্স। আপনি যদি আবার F4 প্রেস করেন, তাহলে সেলটি =B$4 এ পরিবর্তিত হবে। এখন যদি আমরা ফর্মুলাটিকে কপি করি, তাহলে কলামের রেফারেন্স পরিবর্তন হবে কিন্তু রো রেফারেন্স 4 এই ফিক্স হয়ে যাবে। এরপর যদি আপনি আবার F4 প্রেস করেন, তখন ফর্মুলাটি হবে =$B4. এবার কলাম রেফারেন্স ফিক্সড থাকবে এবং রো নাম্বার পরিবর্তন হবে।
এই অবস্থায় আপনি যদি আবার F4 প্রেস করেন, তাহলে এই প্যাটার্নটি রিপিট হবে।
রিলেটিভ এবং এবসলুট উভয়ের সাথেই আমরা OFFSET ফাংশন ব্যবহার করতে পারি। এই ফাংশনটি কতকগুলো argument নিতে পারে। যার মধ্যে সেল রেফারেন্স, রো এবং কলাম হচ্ছে মূল argument. এরপর ফাংশনটি রেফারেন্সের ডানে রো নাম্বার এবং কলাম সংখ্যার নিচে ভ্যালু রিটার্ন করে। এই ফাংশন সেকেন্ডারি argument ও নিতে পারে যেমন কতগুলো রো এবং কলাম এটি রিটার্ন করবে।
চলুন নিচের ডাটাসেটটি নিয়ে কাজ করা যাক।
এরপর নিচের পদ্ধতি অনুযায়ী OFFSET ফাংশন ব্যবহার করে কে পাশ করেছে আর কে করেনি সেটা বের করতে পারি।
পদ্ধতিঃ
🔎 ফর্মুলাটির বিস্তারিতঃ
IF(OFFSET(B5, 0, 1)>32, ”Yes”, ”No”)
👉 OFFSET(B5, 0, 1) - ফর্মুলার এই অংশটি যে ভ্যালু রিটার্ন করে তা হলো B5 সেলের শূন্য (0) রো নিচে এবং এক রো ডানে। অর্থাৎ C5 সেল।
👉 OFFSET(B5, 0, 1)>32 হচ্ছে IF ফাংশনের লজিক্যাল টেস্ট। OFFSET অংশ থেকে রিটার্ন করা নাম্বারটি যদি 32 এর চেয়ে বড় হয়, তাহলে এই ফাংশনটি রিটার্ন করবে TRUE. অন্যথায়, রিটার্ন করবে FALSE.
এবার Enter চাপুন।
এটা পরিষ্কার যে, OFFSET ফাংশন এখানে রিলেটিভ রেফারেন্স ব্যবহার করেছে। যে কারণে, আমরা যখন পরের সেলে যাই, তখন ফাংশনের সেল রেফারেন্সও ঐ অনুসারে পরিবর্তন হচ্ছে।
এই ছিল রিলেটিভ রেফারেন্স সম্পর্কে বিস্তারিত। আশা করছি, আপনারা সম্পূর্ণ ধারণাটি নিতে পেরেছেন। এরপরেও যদি আপনাদের কোনো কিছু জানার বা জানানোর থাকে, নিচে টিউমেন্টে জানাবেন।
পর্ব-৪ঃ এক্সেল আন্টোল্ড পর্ব-৪ –এক্সেল-এ সেল বলতে কি বোঝায়
পর্ব-৩ঃ এক্সেল আন্টোল্ড পর্ব-৩ – এক্সেল স্প্রেডশীটের আপাদমস্তক ২৯ ফ্যাক্টর
পর্ব-২ঃ এক্সেল আন্টোল্ড পর্ব-২ – কর্মক্ষেত্রে এক্সেল কোন কোন ক্ষেত্রে ব্যবহার করা হয়
পর্ব-১ঃ এক্সেল আন্টোল্ড পর্ব-১ – স্প্রেডশীট কি এবং এটা কিভাবে কাজ করে
টিউনটা ভালো লাগলে টিউমেন্ট করে জানাবেন। সবাইকে অসংখ্য ধন্যবাদ। ধন্যবাদ জানাই Techtunes কে। দেখা হচ্ছে পরের টিউটোরিয়ালে।
আমি ম্যাড গেমার। বিশ্বের সর্ববৃহৎ বিজ্ঞান ও প্রযুক্তির সৌশল নেটওয়ার্ক - টেকটিউনস এ আমি 11 বছর 2 মাস যাবৎ যুক্ত আছি। টেকটিউনস আমি এ পর্যন্ত 19 টি টিউন ও 26 টি টিউমেন্ট করেছি। টেকটিউনসে আমার 2 ফলোয়ার আছে এবং আমি টেকটিউনসে 1 টিউনারকে ফলো করি।
