এক্সেলে সার্কুলার রেফারেন্স সক্ষম করা: দুটি মূল ব্যবহারিক কেসসহ গাইড

"এক্সেলে সার্কুলার রেফারেন্স অনুমতি দিন" হলো এক্সেলে একটি সেলে সেলফ-রেফারেন্স ইনসার্ট করার একটি সহজ উপায়। মাইক্রোসফটের সংজ্ঞা অনুযায়ী, সার্কুলার রেফারেন্স তখন ঘটে যখন একটি এক্সেল ফর্মুলা সরাসরি বা পরোক্ষভাবে নিজের সেলকে রেফার করে। উদাহরণস্বরূপ, যদি আমরা দুটি এন্ট্রি যোগ করতে চাই (যেমন: B2 এবং C2) কিন্তু ফলাফল 그중 একটি এন্ট্রিতে দেখাতে চাই (যেমন: B2), তখন একটি সার্কুলার রেফারেন্স তৈরি হয়। প্রথমবার ENTER চাপলে এক্সেল একটি সতর্কতা দেখায় যে ফর্মুলায় এক বা একাধিক সার্কুলার রেফারেন্স রয়েছে।

সার্কুলার রেফারেন্স ইটারেশনগুলোতে ফর্মুলার ফলাফল ক্যালকুলেট করে। ফলে যদি Мак্সিমাম ইটারেশন টাইম অনেক বেশি সেট করা হয় তবে এটি একটি লুপ তৈরি করতে পারে।

এই আর্টিকেলে আমরা সার্কুলার রেফারেন্স, এর সমস্যা এবং ব্যবহার নিয়ে আলোচনা করব।

এক্সেল ওয়ার্কবুক ডাউনলোড করুন

সার্কুলার রেফারেন্স সক্রিয় আছে কিনা তা কীভাবে জানবেন?

পূর্বের সেকশন থেকে আমরা জানি সার্কুলার রেফারেন্স কী এবং কীভাবে তা মোকাবিলা করতে হয়। তবে সবচেয়ে প্রাসঙ্গিক প্রশ্ন হলো: একটি এক্সেল ওয়ার্কশিটে সার্কুলার রেফারেন্স সক্রিয় (Enabled) আছে কিনা অথবা নিষ্ক্রিয় (Disabled) তা কীভাবে জানব?

সবচেয়ে স্বাভাবিক উপায় হলো যদি আমরা নিজেরা রেফারেন্স দিই, তবে এক্সেল প্রথমবার একটি সতর্কতা দেখায় যেমন নিচের ছবিতে দেখানো হয়েছে। যদি এক্সেল সতর্কতা দেখায়, তবে আমরা নিশ্চিত হতে পারি যে ওয়ার্কশিটে সার্কুলার রেফারেন্স নিষ্ক্রিয়। অন্যথায়, ফর্মুলা Мак্সিমাম ইটেরেটিভ নম্বর ১-এর জন্য সঠিক ফলাফল রিটার্ন করবে।

সেল রেফারেন্স ফর্মুলায় ইনপুট দেওয়ার পর আপনি দেখতে পাচ্ছেন এক্সেল স্পষ্ট কারণ ছাড়া শূন্য (০) রিটার্ন করছে। যেকোনো ওয়ার্কশিটের নিচে Ready স্ট্যাটাসের ডান পাশের টেক্সট চেক করুন। যদি সেখানে Circular Reference: cell দেখায়, তবে ওয়ার্কশিটের জন্য সার্কুলার রেফারেন্স অপশনটি নিষ্ক্রিয়।

আমরা Formulas ট্যাবে যেতে পারি > Error Checking বেছে নিন ( Formula Auditing সেকশনে) > Error Checking অপশন থেকে Circular References বেছে নিন। এটি দেখাবে সার্কুলার রেফারেন্স এরর কোথায় ঘটছে।

আরও পড়ুন: এক্সেলে সার্কুলার রেফারেন্স খুঁজে বের করা (২ সহজ ট্রিক)

ফাইল রিবন ব্যবহার করে এক্সেলে সার্কুলার রেফারেন্স অনুমতি দিন

সার্কুলার রেফারেন্স অনুমতি দেওয়া এক ধরনের ফর্মুলায় রেফারেন্স এরর উপেক্ষা করা। যাইহোক, সার্কুলার রেফারেন্স সক্ষম করলে ফর্মুলাগুলো রেফারেন্স এররকে অতিক্রম করে সঠিক ফলাফল রিটার্ন করতে পারে।

সার্কুলার রেফারেন্স অনুমতি দিতে নিচের ধাপগুলো অনুসরণ করুন,

➤ যেকোনো ওয়ার্কশিটের File রিবনে যান।

➤ File মেনু অপশন থেকে Options বেছে নিন ( Excel Options উইন্ডো দেখাবে) > ডান পাশ থেকে Formulas বেছে নিন > Enable iterative calculation অপশনে টিক দিন (আপনার প্রয়োজনীয় Maximum iterations নম্বর এবং Maximum change ইনপুট দিন)।

OK ক্লিক করুন।

এই ধাপগুলো অনুসরণ করলে আপনি বিদ্যমান সার্কুলার রেফারেন্স সহ ফর্মুলাগুলোকে ফলাফল রিটার্ন করতে অনুমতি দিতে পারবেন।

ম্যাক্সিমাম ইটেরেশন সম্পর্কিত সমস্যাগুলো

যখন এক্সেলে সার্কুলার রেফারেন্স ঘটে, তখন এক্সেল প্রথমবার একটি সতর্কতা দেখায় নিচের স্ক্রিনশটের মতো।

তবে পরে এক্সেল সতর্কতা দেখায় না, এবং ব্যবহারকারীরা ক্যালকুলেশনে আটকে যান চিন্তা করে কী ভুল হয়েছে কারণ ফলাফল শূন্য (০) রিটার্ন করছে ফর্মুলার টাইপ নির্বিশেষে।

এই ক্যালকুলেশন সমস্যা সমাধান করতে, আমাদের Enable iterative calculation অপশনটি চেক করতে হবে। নিচের ছবির মতো File ট্যাব অপশনে যান।

File রিবনে ক্লিক করার পর, Options বেছে নিন ( Excel Options উইন্ডো দেখাবে) > বাম সেকশন থেকে Formulas বেছে নিন > Enable iterative calculation টিক দিন। তারপর OK ক্লিক করুন।

এখন আমরা ফর্মুলায় সার্কুলার রেফারেন্স ব্যবহার করতে পারি। কিন্তু আপনি যদি Maximum iterations বক্সে ১ ইনপুট দেন (যেমন আমরা দিয়েছি) তবে ফর্মুলা (যেমন: =B2+C2) ২০ রিটার্ন করবে (যা সঠিক কারণ B2 খালি এবং C2-এর ভ্যালু ২০) নিচের ছবির মতো।

তবে যদি আমরা Maximum iterations অপশন বক্সে ৫ ইনপুট দিন, যেভাবে নিচের স্ক্রিনশটে দেখানো হয়েছে।

তবে ফর্মুলা ১০০ রিটার্ন করবে কারণ এটি একই ক্যালকুলেশন ৫ বার ইটারেট করে। এটি এক্সেলের সবচেয়ে বাধাদায়ক ভুল ক্যালকুলেশন।

এক্সেলে সার্কুলার রেফারেন্সের ব্যবহার

সার্কুলার রেফারেন্স নির্দিষ্ট ক্ষেত্রে খুব উপকারী। কিছু ঘটনা থাকে যেখানে মানগুলো আরও কার্যকরভাবে উপস্থাপন করার জন্য সার্কুলার রেফারেন্স দরকার। এই আর্টিকেলে আমরা দুটি সবচেয়ে বেশি ব্যবহৃত সার্কুলার রেফারেন্স উদাহরণ আলোচনা করব।

উদাহরণ ১: স্ট্যাটিক টাইমস্ট্যাম্প ইনসার্ট করার জন্য এক্সেলে সার্কুলার রেফারেন্স অনুমতি দিন

মান লইए আমাদের কিছু প্রোডাক্ট আছে যাদের অর্ডার ডেট এবং ডেলিভারি স্ট্যাটাস। এবং আমরা চাই টাইমস্ট্যাম্প সেলে ইনসার্ট করা যেসব প্রোডাক্ট গুলো ডেলিভার্ড। আমরা ডেলিভারি স্ট্যাটাস Yes বা No-এ দেখাচ্ছি।

সুতরাং, যদি ডেলিভারি স্ট্যাটাস Yes হয়, তবে টাইমস্ট্যাম্প তখনের সময় এবং তারিখ দেখাবে।

➤ নিচের ফর্মুলাটি যেকোনো খালি সেলে (যেমন: E5) পেস্ট করুন যেখানে আপনি টাইমস্ট্যাম্প দেখাতে চান।

ফর্মুলাটি logical_test চালায় D কলামের এন্ট্রি গুলোর সাথে Yes মিলিয়ে। তারপর যেসব সেল TRUE রিটার্ন করে সেখানে বর্তমান সময় দেখায় অথবা সেলটি খালি রাখে।

➤ ENTER চাপুন তারপর ফিল হ্যান্ডেল টেনে আনুন সব ডেলিভার্ড প্রোডাক্টের জন্য টাইমস্ট্যাম্প ইনসার্ট করতে।

যেহেতু সার্কুলার রেফারেন্স সেলফ-সেল রেফারেন্স সমস্যা তৈরি করে, কখনো কখনো লাগে ফর্মুলাটি সঠিকভাবে কাজ করছে না। সেক্ষেত্রে, ডেলিভারি স্ট্যাটাস বা শর্তাবলী পুনরায় লিখুন তারপর যেকোনো ফর্মুলা প্রয়োগ করুন। এবং আপনাকে ডেট-টাইম ফরম্যাট পাওয়ার জন্য সেল ফরম্যাট পরিবর্তন করতে হতে পারে।

উদাহরণ ২: ইটেরেটিভ ক্যালকুলেশনের জন্য এক্সেলে সার্কুলার রেফারেন্স অনুমতি দিন

কখনো কখনো আমাদের ইটেরেটিভ ক্যালকুলেশন করতে হয়। একটি ইটেরেটিভ ক্যালকুলেশনে, আমরা পুরো ক্যালকুলেশন না করেই সরাসরি ইটেরেটিভ ভ্যালু পাই।

মান লইए একটি পরিস্থিতিতে, আমরা একটি প্রাথমিক ক্যাশ পরিমাণ বিনিয়োগ করেছি (যেমন: $1500) এবং আমরা অ্যাসেট থেকে মাসিক কিস্তি চাই। আমরা ১^st মাসের কিস্তির পরিমাণ নিচের ফর্মুলা ব্যবহার করে ক্যালকুলেট করতে পারি।

তারপর আগের কিস্তির পরিমাণকে রেফার করে পরবর্তী কিস্তির পরিমাণ ক্যালকুলেট করুন নিচের ছবির মতো।

➤ যাইহোক, যদি আমরা সার্কুলার রেফারেন্স ব্যবহার করি, তবে আমরা n^th কিস্তির পরিমাণ পেতে পারি শুধু ইন্সটলমেন্ট নম্বরটি Maximum iterations নম্বর হিসেবে ইনপুট দিয়ে। সঠিক ফলাফলের জন্য, আমরা ৫ Maximum iterations হিসেবে ইনপুট দিই এবং ১^th অফ ১০০০ 허용যোগ্য পরিবর্তন হিসেবে, নিচের ছবির মতো।

আমরা Maximum iterations নম্বর ৫ ইনপুট দিয়েছি কারণ আমাদের ৬^th কিস্তি ক্যালকুলেট করতে হচ্ছে। এবং আমরা পরিবর্তনযোগ্য পরিমাণটিকে ১^th অফ ১০০০ অংশ ভেরি করার অনুমতি দিয়েছি।