এক্সেলে ফর্মুলা দিয়ে FOR Loop তৈরি: ৩টি প্র্যাকটিক্যাল উদাহরণ

আপনি কি এক্সেল VBA ব্যবহার করতে চান না এবং ফর্মুলা ব্যবহার করে এক্সেলে FOR Loop তৈরি করতে চান? এই আর্টিকেলে আমি দেখিয়েছি কীভাবে আপনি ফর্মুলা ব্যবহার করে FOR Loop তৈরি করতে পারেন।

আপনি যদি এক্সেল VBA-তে কোডিং জানেন, আপনি ধন্য 🙂। কিন্তু, আপনি যদি কখনো VBA-তে কোড লিখেননি বা আপনার এক্সেল ওয়ার্কবুককে এক্সেল VBA কোড থেকে মুক্ত রাখতে চান, তবে অধিকাংশ সময় আপনাকে বক্সের বাইরে চিন্তা করতে হবে একটি সহজ loop তৈরি করতে।

ওয়ার্কিং ফাইল ডাউনলোড

নিচের লিংক থেকে ওয়ার্কিং ফাইল ডাউনলোড করুন:

এক্সেলে ফর্মুলা দিয়ে FOR Loop তৈরির ৩টি উদাহরণ

এখানে, আমি ৩টি উদাহরণ দেখাব কীভাবে ফর্মুলা ব্যবহার করে এক্সেলে FOR Loop তৈরি করা যায়। বিস্তারিত উদাহরণগুলো দেখি।

১. কোম্বাইনড ফাংশন ব্যবহার করে এক্সেলে FOR Loop তৈরি

এখন, আমাকে জানাতে দিন যে পidumotivation আমাকে এই উদাহরণ লিখতে প্রোরিত করেছে।

আমি Udemy-তে কিছু কোর্সের লেখক। 그중 একটি কোর্স এক্সেল কন্ডিশনাল ফরম্যাটিং-এর ওপর। কোর্সের টাইটেল: Learn Excel Conditional Formatting with 7 Practical Problems. [এই কোর্সে ফ্রি এক্সেস পেতে এখানে ক্লিক করুন]।

কোর্স ডিসকাশন বোর্ডে একজন ছাত্র আমাকে নিচের মত একটি প্রশ্ন করলেন [স্ক্রিনশট ইমেজ]।

Udemy-এ একজন ছাত্রের প্রশ্ন।

উপরের প্রশ্নটি সাবধানে পড়ুন এবং সমাধান করার চেষ্টা করুন…

উপরোক্ত সমস্যা সমাধানের ধাপসমূহ:

এখানে, আমি এক্সেল ফর্মুলা হিসেবে OR, OFFSET, MAX, MIN, এবং ROW ফাংশন ব্যবহার করে একটি FOR Loop তৈরি করব।

• প্রথমে, আপনার কাজ হলো একটি নতুন ওয়ার্কবুক খুলুন এবং উপরের ভ্যালুগুলো এক এক করে ওয়ার্কশিটে ইনপুট করুন [সেল C5-থেকে শুরু করুন]।
• দ্বিতীয়, পুরো রেঞ্জ সিলেক্ট করুন [সেল C5:C34- থেকে]।
• তৃতীয়, Home রিবন >> Conditional Formatting কমান্ডে ক্লিক করুন।
• চতুর্থ, ড্রপ-ডাউন থেকে New Rule অপশন সিলেক্ট করুন।

এই সময়, New Formatting Rule ডায়লগ বক্স দেখাবে।

• এখন, Select a Rule Type উইন্ডো >> Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
• তারপর, Format values where this formula is true ফিল্ডে এই ফর্মুলা টাইপ করুন:

• এখন, ডায়লগ বক্সে Format… বাটনে ক্লিক করে উপযুক্ত ফরম্যাট টাইপ সিলেক্ট করুন।

এই সময়, Format Cells নামে একটি ডায়লগ বক্স দেখাবে।

• এখন, Fill অপশন থেকে >> আপনাকে যেকোনো একটি কালার বেছে নিতে হবে। এখানে, আমি Light Blue ব্যাকগ্রাউন্ড সিলেক্ট করলাম। আপনি sample ইনস্ট্যান্টলি দেখতে পাবেন। এই কেসে, যেকোনো হালকা কালার বেছে নেওয়া ভালো। কারণ গাঢ় কালার ইনপুট করা ডেটা লুকিয়ে দিতে পারে। সেক্ষেত্রে, আপনাকে Font Color চেঞ্জ করতে হতে পারে।
• তারপর, ফরমেশন অ্যাপ্লাই করতে OK চাপুন।

• उसके बाद, New Formatting Rule ডায়লগ বক্সে OK চাপুন। এখানে, আপনি Preview বক্সে ইনস্ট্যান্ট স্যাম্পল দেখতে পাবেন।

শেষে, আপনি ফরম্যাট করা নাম্বারগুলো পাবেন।

আমি আপনাকে উপরের সমস্যা সমাধানের অ্যালগরিদম দেখাতে চাই:

• এখানে, আপনাকে সহজে বুঝাতে দুটি রেফারেন্স সেল দিয়ে ব্যাখ্যা করব: সেল C11 এবং C17। সেল C11 এবং C17-এ ভ্যালু যথাক্রমে 10 এবং 20 (উপরোক্ত ছবি)। আপনি যদি এক্সেল ফর্মুলার অভ্যস্ত হন, তবে আপনি OFFSET ফাংশন সूंघে পাবেন, কারণ OFFSET ফাংশন রেফারেন্স পয়েন্টের সাথে কাজ করে।
• এখন, কল্পনা করুন আমি সেল রেঞ্জ C8:C11 & C11:C14, এবং C14:C17 & C17: C20 একসাথে নিচ্ছি [নিচের ছবি]। রেফারেন্স সেলগুলো C11 এবং C17 এবং আমি রেফারেন্স সেলের চারপাশে মোট ৭টি সেল নিচ্ছি। আপনি নিচের মত একটি কল্পনাত্মক ছবি পাবেন। প্রথম অংশ থেকে, আপনি ছবি থেকে একটি প্যাটার্ন খুঁজে পাবেন। C9–C12=3, C10-C13=3, একটি প্যাটার্ন রয়েছে। কিন্তু দ্বিতীয় অংশে এমন কোনো প্যাটার্ন নেই।

• তাহলে, উপরের প্যাটার্ন মনে রেখে অ্যালগরিদম বানাই। সাধারণ ফর্মুলা বানানোর আগে, আমি সেল C11 এবং C17-এর জন্য ফর্মুলা কী হবে সেটি দেখাব এবং তারপর ফর্মুলাটি মডিফাই করব যাতে এটি সব সেলের জন্য সাধারণ হয়। একটি রেফারেন্স পয়েন্টের জন্য (যেমন C11 বা C17), আমি এর চারপাশে মোট ৭টি সেল নিব (রেফারেন্স পয়েন্ট সহ) এবং সেগুলো ফর্মুলা বানাতে সাইড বাই সাইড প্লেস করব অ্যারে হিসেবে। তারপর আমি অ্যারেদের ডিফারেন্স বের করব যদি কোনো ডিফারেন্স ৩-এর সমান হয় তবে সেই রেফারেন্স সেল TRUE ভ্যালু দেবে।
• এখানে, আমি সহজে এটি করতে পারি OFFSET ফাংশন ব্যবহার করে কারণ OFFSET ফাংশন একটি অ্যারে রিটার্ন করে। সেল রেফারেন্স C11-এর জন্য, আমি ফর্মুলা এমন লিখতে পারি: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)। এই ফর্মুলা কী রিটার্ন করবে? ফর্মুলার প্রথম offset ফাংশন অ্যারে রিটার্ন করবে: {10; 11; 12; 15}, দ্বিতীয় offset ফাংশন অ্যারে রিটার্ন করবে {5; 8; 9; 10}। এবং আপনি জানেন {10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}। যখন এই অ্যারেকে লজিক্যালি =3 দিয়ে টেস্ট করা হয় তখন এক্সেল ইন্টারন্যালি এভাবে ক্যালকুলেট করে: {5=3; 3=3; 3=3; 5=3} = {False; True; True; False}। যখন এই অ্যারেতে OR ফাংশন অ্যাপ্লাই করা হয়: OR({False; True; False; True}, আপনি TRUE পাবেন। তাই সেল C11 TRUE ভ্যালু রিটার্ন করে।
• তাই আমি মনে করি আপনি পুরো কনসেপ্ট বুঝতে পেরেছেন কীভাবে এই অ্যালগরিদম কাজ করতে যাচ্ছে। এখন একটা সমস্যা রয়েছে। এই ফর্মুলা সেল C8-থেকে কাজ করতে পারে, সেল C8-এর উপরে ৩টি সেল রয়েছে। কিন্তু সেল C5, C6, এবং C7-এর জন্য এই ফর্মুলা কাজ করতে পারবে না। তাই এই সেলগুলো için ফর্মুলা মডিফাই করতে হবে।
• এখন, সেল C5 থেকে C7-এর জন্য, আমরা চাই ফর্মুলা উপরের ৩টি সেলকে কনসিডার করবে না। উদাহরণস্বরূপ, সেল C6-এর জন্য, আমাদের ফর্মুলা সেল C11-এর ফর্মুলার মতো নয়: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)।
• এখানে, সেল C5-এর জন্য ফর্মুলা হবে: OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3)।
• তারপর, সেল C6-এর জন্য ফর্মুলা হবে: OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3)।
• পরে, সেল C7-এর জন্য ফর্মুলা হবে: OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3)।
• আবার, সেল C8-এর জন্য ফর্মুলা হবে: OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [এটি সাধারণ ফর্মুলা]।
• তারপর, সেল C9-এর জন্য ফর্মুলা হবে: OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [এটি সাধারণ ফর্মুলা]।
• শেষে, উপরের ফর্মুলাগুলো থেকে কিছু প্যাটার্ন খুঁজে পাচ্ছেন কি? প্রথম OFFSET ফাংশনের rows argument ৩ থেকে 0-এ কমেছে; height argument ১ থেকে ৪-এ বাড়েছে। দ্বিতীয় OFFSET ফাংশনের rows argument ০ থেকে -৩-এ কমেছে এবং height argument ১ থেকে ৪-এ বাড়েছে।
• প্রথমে, প্রথম OFFSET ফাংশনের rows argument মডিফাই হবে এইভাবে: MAX(ROW(C$5)-ROW(C5)+3,0)
• দ্বিতীয়, দ্বিতীয় OFFSET ফাংশনের rows argument মডিফাই হবে এইভাবে: MAX(ROW(C$5)-ROW(C5),-3)
• তৃতীয়, প্রথম OFFSET ফাংশনের height argument মডিফাই হবে এইভাবে: MIN(ROW(C5)-ROW(C$5)+1,4)
• চতুর্থ, দ্বিতীয় OFFSET ফাংশনের height argument মডিফাই হবে এইভাবে: MIN(ROW(C5)-ROW(C$5)+1,4)
• এখন, উপরের মডিফিকেশনগুলো বুঝতে চেষ্টা করুন। এগুলো उतনা কঠিন নয়। এই চারটি মডিফিকেশন এক্সেল VBA-এর FOR LOOP হিসেবে কাজ করছে কিন্তু আমি এক্সেল ফর্মুলা দিয়ে এগুলো বানিয়েছি।
• তাহলে, আপনি বুঝতে পেরেছেন সাধারণ ফর্মুলা কীভাবে সেল C5:C34-এর জন্য কাজ করে।

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

২. IF ও OR ফাংশন ব্যবহার করে এক্সেলে FOR Loop তৈরি

এই উদাহরণে, ধরা যাক আপনি চেক করতে চান সেলগুলো কোনো ভ্যালু ধারণ করছে কিনা।再者, এক্সেল VBA FOR Loop-এ আপনি এটি সহজেই করতে পারেন কিন্তু এখানে, আমি এক্সেল ফর্মুলা ব্যবহার করে তা করব।

এখন, আপনি IF, এবং OR ফাংশন ব্যবহার করে এক্সেল ফর্মুলা হিসেবে FOR Loop তৈরি করতে পারেন।更何况, আপনি আপনার পছন্দমতো এই ফর্মুলা মডিফাই করতে পারেন। ধাপগুলো নিচে দেওয়া হলো।

ধাপসমূহ:

• প্রথমে, আপনাকে একটি আলাদা সেল E5 সিলেক্ট করতে হবে যেখানে আপনি Status দেখতে চান।
• দ্বিতীয়, আপনাকে E5 সেলে সংশ্লিষ্ট ফর্মুলা ব্যবহার করতে হবে।

• পরবর্তী, রেজাল্ট পেতে ENTER চাপুন।

Formula Breakdown

এখানে, OR ফাংশন TRUE রিটার্ন করবে যদি দেওয়া যেকোনো লজিক TRUE হয়।

• প্রথমে, B5=”” হলো ১ম লজিক, যা চেক করবে সেল B5 কোনো ভ্যালু ধারণ করছে কিনা।
• দ্বিতীয়, C5=”” হলো ২য় লজিক, যা চেক করবে সেল C5 কোনো ভ্যালু ধারণ করছে কিনা।
• তৃতীয়, D5=”” হলো ৩য় লজিক। একভাবেই, এটি চেক করবে সেল D5 কোনো ভ্যালু ধারণ করছে কিনা।

এখন, IF ফাংশন প্রদত্ত কন্ডিশন পূরণ করা রেজাল্ট রিটার্ন করবে।

• যখন OR ফাংশন TRUE দেবে তখন আপনি “Info Missing” পাবেন Status হিসেবে। অন্যথায়, আপনি “Done” পাবেন Status হিসেবে।

• उसके बाद, Fill Handle আইকন ড্র্যাগ করে বাকি সেল E6:E13-এ সংশ্লিষ্ট ডেটা AutoFill করুন। অথবা আপনি Fill Handle আইকনে ডাবল-ক্লিক করতে পারেন।

শেষে, আপনি সব রেজাল্ট পাবেন।

৩. SUMIFS ফাংশন ব্যবহার করে এক্সেলে FOR Loop তৈরি

ধরুন, আপনি একজন নির্দিষ্ট ব্যক্তির জন্য টোটাল বিল বানাতে চান। সেক্ষেত্রে, আপনি এক্সেল ফর্মুলা ব্যবহার করে FOR Loop ব্যবহার করতে পারেন। এখানে, আমি SUMIFS function ব্যবহার করে এক্সেলে FOR Loop তৈরি করব। ধাপগুলো নিচে দেওয়া হলো।

ধাপসমূহ:

• প্রথমে, আপনাকে একটি আলাদা সেল F7 সিলেক্ট করতে হবে যেখানে আপনি Status দেখতে চান।
• দ্বিতীয়, আপনাকে F7 সেলে সংশ্লিষ্ট ফর্মুলা ব্যবহার করতে হবে।

• পরবর্তী, রেজাল্ট পেতে ENTER চাপুন।

Formula Breakdown

• এখানে, $C$5:$C$13 হলো ডেটা রেঞ্জ যেখান থেকে SUMIFS ফাংশন সম্মেশন করবে।
• তারপর, $B$5:$B$13 হলো ডেটা রেঞ্জ যেখান থেকে SUMIFS ফাংশন দिये गये কRITEERIA চেক করবে।
• শেষে, E7 হলো কRITEERIA।
• তাই, SUMIFS ফাংশন E7 সেল ভ্যালুর জন্য পেমেন্টগুলো যোগ করবে।

• उसके बाद, Fill Handle আইকন ড্র্যাগ করে বাকি সেল F8:F10-এ সংশ্লিষ্ট ডেটা AutoFill করুন।

শেষে, আপনি রেজাল্ট পাবেন।

উপসংহার

আশা করি আপনি এই আর্টিকেলটি সহায়ক পেয়েছেন। এখানে, আমরা এক্সেলে ফর্মুলা ব্যবহার করে FOR Loop তৈরির ৩টি উপযুক্ত উদাহরণ ব্যাখ্যা করেছি। আপনি আরও এক্সেল-রিলেটেড কন্টেন্ট শিখতে আমাদের ওয়েবসাইট Exceldemy-এ ভিজিট করতে পারেন। অনুগ্রহ করে কমেন্ট সেকশনে আপনার কমেন্ট, সুপারিশ, বা প্রশ্ন ড্রপ করুন যদি থাকে।

আরও পড়ুন

• এক্সেল VBA-তে Do While Loop ব্যবহার করার পদ্ধতি
• VBA Excel-এ For Next Loop (কীভাবে Step এবং Exit Loop করবেন)