फॉर्मूला के साथ एक्सेल में FOR लूप बनाएं: 3 व्यावहारिक उदाहरण

क्या आप एक्सेल VBA का उपयोग नहीं करना चाहते और फॉर्मूला का उपयोग करके एक्सेल में FOR लूप बनाना चाहते हैं? इस लेख में, मैंने दिखाया है कि आप फॉर्मूला का उपयोग करके FOR लूप कैसे बना सकते हैं।

यदि आप जानते हैं कि एक्सेल VBA के साथ कैसे कोड करना है, तो आप धन्य हैं 🙂। लेकिन, यदि आपने कभी VBA में कोड नहीं लिखा है या अपनी एक्सेल वर्कबुक को एक्सेल VBA कोड से मुक्त रखना चाहते हैं, तो ज्यादातर समय आपको एक सरल लूप बनाने के लिए बॉक्स से बाहर सोचना पड़ता है।

कार्य फ़ाइल डाउनलोड करें

नीचे दिए गए लिंक से कार्य फ़ाइल डाउनलोड करें:

फॉर्मूला का उपयोग करके एक्सेल में FOR लूप बनाने के 3 उदाहरण

यहां, मैं फॉर्मूला का उपयोग करके एक्सेल में FOR लूप बनाने के 3 उदाहरण प्रदर्शित करूंगा। आइए विस्तृत उदाहरण देखें।

1. एक्सेल में FOR लूप बनाने के लिए संयुक्त फ़ंक्शन लागू करना

अब, मुझे उस पृष्ठभूमि के बारे में बताएं जो मुझे इस उदाहरण को लिखने के लिए प्रोत्साहित कर रही है।

मैं उडेमी पर कुछ पाठ्यक्रमों का लेखक हूं। पाठ्यक्रमों में से एक एक्सेल सशर्त स्वरूपण पर है। पाठ्यक्रम का शीर्षक है: 7 व्यावहारिक समस्याओं के साथ एक्सेल सशर्त स्वरूपण सीखें। [इस पाठ्यक्रम तक मुफ्त पहुंच पाने के लिए, यहां क्लिक करें]।

पाठ्यक्रम चर्चा बोर्ड में, एक छात्र ने मुझसे नीचे दिए गए प्रश्न पूछा [स्क्रीनशॉट छवि]।

उडेमी में एक छात्र द्वारा पूछा गया प्रश्न।

उपरोक्त प्रश्न को ध्यान से पढ़ें और इसे हल करने का प्रयास करें…

उपरोक्त समस्या को हल करने के चरण:

यहां, मैं OR, OFFSET, MAX, MIN, और ROW फ़ंक्शन का उपयोग एक्सेल फॉर्मूला के रूप में FOR लूप बनाने के लिए करूंगा।

• सबसे पहले, आपका काम एक नई वर्कबुक खोलना और ऊपर दिए गए मानों को वर्कशीट में एक-एक करके इनपुट करना है [सेल C5 से शुरू करें]।
• दूसरे, पूरी रेंज [सेल C5:C34 से] चुनें।
• तीसरे, होम रिबन >> सशर्त स्वरूपण कमांड पर क्लिक करें।
• अंत में, ड्रॉप-डाउन से नया नियम विकल्प चुनें।

इस समय, नया स्वरूपण नियम डायलॉग बॉक्स दिखाई देता है।

• अब, नियम प्रकार चुनें विंडो >> कौन से सेल को फॉर्मेट करना है यह निर्धारित करने के लिए फॉर्मूला का उपयोग करें विकल्प चुनें।
• फिर, जहां यह फॉर्मूला सच है वहां मान फॉर्मेट करें फ़ील्ड में, यह फॉर्मूला टाइप करें:

=OR(OFFSET(C5,MAX(ROW(C$5)-ROW(C5)+3,0),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)-OFFSET(C5,MAX(ROW($C$5)-ROW(C5),-3),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)=3)

• अब, डायलॉग बॉक्स में फॉर्मेट... बटन पर क्लिक करके उपयुक्त फॉर्मेट प्रकार चुनें।

इस समय, सेल फॉर्मेट नामक एक डायलॉग बॉक्स दिखाई देगा।

• अब, भराव विकल्प से >> आपको किसी भी रंग का चयन करना होगा। यहां, मैंने हल्का नीला पृष्ठभूमि चुना। साथ ही, आप तुरंत नमूना देख सकते हैं। इस मामले में, कोई भी हल्का रंग चुनने का प्रयास करें। क्योंकि गहरा रंग इनपुट डेटा को छिपा सकता है। फिर, आपको फ़ॉन्ट रंग बदलने की आवश्यकता हो सकती है।
• फिर, गठन लागू करने के लिए ओके दबाएं।

• उसके बाद, नया स्वरूपण नियम डायलॉग बॉक्स पर ओके दबाएं। यहां, आप पूर्वावलोकन बॉक्स में तुरंत नमूना देख सकते हैं।

अंत में, आपको स्वरूपित संख्याएं मिलेंगी।

आइए मुझे उपरोक्त समस्या को हल करने का एल्गोरिदम दिखाएं:

• यहां, आपको एल्गोरिदम आसानी से समझाने के लिए, मैं दो संदर्भ सेल: सेल C11 और C17 के साथ पूरी चीज समझाऊंगा। सेल C11 और C17 में, मान क्रमशः 10 और 20 हैं (ऊपर की छवि)। यदि आप एक्सेल फॉर्मूला के आदी हैं, तो आप OFFSET फ़ंक्शन को सूंघ सकते हैं, क्योंकि OFFSET फ़ंक्शन संदर्भ बिंदुओं के साथ काम करता है।
• अब, कल्पना करें कि मैं सेल रेंज C8:C11 और C11:C14, और C14:C17 और C17:C20 को साथ-साथ ले रहा हूं [नीचे छवि]। संदर्भ सेल C11 और C17 हैं और मैं संदर्भ सेल के चारों ओर कुल 7 सेल ले रहा हूं। आपको निम्न जैसी एक काल्पनिक तस्वीर मिलेगी। पहले भाग से, आप छवि से एक पैटर्न ढूंढ सकते हैं। C9–C12=3, C10-C13=3, एक पैटर्न है। लेकिन दूसरे भाग के लिए, ऐसा कोई पैटर्न नहीं है।

• तो, आइए उपरोक्त पैटर्न को ध्यान में रखते हुए एल्गोरिदम बनाएं। सामान्य फॉर्मूला बनाने से पहले, मैं सेल C11 और C17 के लिए फॉर्मूला क्या होंगे दिखाऊंगा और फिर फॉर्मूला को सभी के लिए सामान्य बनाने के लिए संशोधित करूंगा। एक संदर्भ बिंदु (जैसे C11 या C17) के लिए, मैं इसके चारों ओर कुल 7 सेल लूंगा (संदर्भ बिंदु सहित) और उन्हें फॉर्मूला में सरणी बनाकर साथ-साथ रखूंगा। फिर मैं सरणियों का अंतर ढूंढूंगा यदि कोई अंतर 3 के बराबर है तो वह संदर्भ सेल TRUE मान होगा।
• यहां, मैं OFFSET फ़ंक्शन का उपयोग करके इसे आसानी से कर सकता हूं क्योंकि OFFSET फ़ंक्शन एक सरणी लौटाता है। सेल संदर्भ C11 के लिए कहें, मैं फॉर्मूला इस तरह लिख सकता हूं: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)। यह फॉर्मूला क्या लौटाएगा? फॉर्मूला का पहला ऑफसेट फंक्शन सरणी लौटाएगा: {10; 11; 12; 15}, दूसरा ऑफसेट फंक्शन सरणी {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} = {गलत; सच; सच; गलत}। जब इस सरणी पर OR फ़ंक्शन लागू किया जाता है: OR({गलत; सच; गलत; सच}, आपको सच मिलता है। तो सेल C11 को वापस किए गए सही मान मिलते हैं।
• तो, मुझे लगता है कि आपको पूरी अवधारणा मिल गई है कि यह एल्गोरिदम कैसे काम करने जा रहा है। अब एक समस्या है। यह फॉर्मूला सेल C8 से काम कर सकता है, सेल C8 के ऊपर 3 सेल हैं। लेकिन सेल C5, C6, और C7 के लिए यह फॉर्मूला काम नहीं कर सकता। तो इन सेल के लिए फॉर्मूला को संशोधित किया जाना चाहिए।
• अब, सेल C5 से C7 के लिए, हम चाहते हैं कि फॉर्मूला ऊपरी 3 सेल को ध्यान में न रखे। उदाहरण के लिए, सेल 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 फंक्शन का पंक्ति तर्क 3 से 0 तक घट गया है; ऊंचाई तर्क 1 से 4 तक बढ़ गया है। दूसरे OFFSET फंक्शन का पंक्ति तर्क 0 से -3 तक घट गया है और ऊंचाई तर्क 1 से 4 तक बढ़ गया है।
• सबसे पहले, पहले OFFSET फंक्शन के पंक्ति तर्क को इस तरह संशोधित किया जाएगा: MAX(ROW(C$5)-ROW(C5)+3,0)
• दूसरे, दूसरे OFFSET फंक्शन के पंक्ति तर्क को इस तरह संशोधित किया जाएगा: MAX(ROW(C$5)-ROW(C5),-3)
• तीसरे, पहले OFFSET फंक्शन के ऊंचाई तर्क को इस तरह संशोधित किया जाएगा: MIN(ROW(C5)-ROW(C$5)+1,4)
• चौथे, दूसरे OFFSET फंक्शन के ऊंचाई तर्क को इस तरह संशोधित किया जाएगा: MIN(ROW(C5)-ROW(C$5)+1,4)
• अब, उपरोक्त संशोधन को समझने का प्रयास करें। ये समझने में इतने कठिन नहीं हैं। ये सभी चार संशोधन एक्सेल VBA के FOR LOOP के रूप में काम कर रहे हैं लेकिन मैंने उन्हें एक्सेल फॉर्मूला के साथ बनाया है।
• तो, आपको वे तरीके मिल गए कि सामान्य फॉर्मूला सेल C5:C34 के लिए कैसे काम करता है।

तो मैं एक्सेल स्प्रेडशीट में लूपिंग के बारे में बात कर रहा था। तो, यह एक्सेल में लूपिंग का एक आदर्श उदाहरण है। यहां, हर बार फॉर्मूला 7 सेल लेता है और विशिष्ट मूल्य ढूंढने के लिए सेल पर काम करता है।

2. एक्सेल में FOR लूप बनाने के लिए IF और OR फ़ंक्शन का उपयोग

इस उदाहरण में, मान लीजिए आप जांचना चाहते हैं कि सेल में कोई मान है या नहीं। इसके अलावा, एक्सेल VBA FOR लूप के साथ, आप इसे आसानी से कर सकते हैं लेकिन यहां, मैं एक्सेल फॉर्मूला का उपयोग करके ऐसा करूंगा।

अब, आप IF, और OR फ़ंक्शन का उपयोग एक्सेल फॉर्मूला के रूप में FOR लूप बनाने के लिए कर सकते हैं। इसके अलावा, आप इस फॉर्मूला को अपनी प्राथमिकता के अनुसार संशोधित कर सकते हैं। चरण नीचे दिए गए हैं।

चरण:

• सबसे पहले, आपको एक अलग सेल E5 चुनना होगा जहां आप स्थिति देखना चाहते हैं।
• दूसरे, आपको E5 सेल में संबंधित फॉर्मूला उपयोग करना चाहिए।

=IF(OR(B5="",C5="",D5=""),"जानकारी गुम","हो गया")

• बाद में, परिणाम प्राप्त करने के लिए ENTER दबाएं।

फॉर्मूला ब्रेकडाउन

यहां, OR फ़ंक्शन सच लौटाएगा यदि दिया गया कोई भी तर्क सच हो जाता है।

• सबसे पहले, B5="" पहला तर्क है, जो जांचेगा कि सेल B5 में कोई मान है या नहीं।
• दूसरे, C5="" दूसरा तर्क है, जो जांचेगा कि सेल C5 में कोई मान है या नहीं।
• तीसरे, D5="" तीसरा तर्क है। इसी तरह, जो जांचेगा कि सेल D5 में कोई मान है या नहीं।

अब, IF फ़ंक्शन परिणाम लौटाता है जो दी गई शर्त को पूरा करेगा।

• जब OR फ़ंक्शन सच देता है तो आपको स्थिति के रूप में "जानकारी गुम" मिलेगा। अन्यथा, आपको स्थिति के रूप में "हो गया" मिलेगा।

• उसके बाद, आपको फिल हैंडल आइकन को ऑटोफिल करने के लिए खींचना होगा E6:E13 सेल में संबंधित डेटा। या आप फिल हैंडल आइकन पर डबल-क्लिक कर सकते हैं।

अंत में, आपको सभी परिणाम मिलेंगे।

3. एक्सेल में FOR लूप बनाने के लिए SUMIFS फ़ंक्शन का उपयोग

मान लीजिए, आप किसी विशेष व्यक्ति के लिए कुल बिल बनाना चाहते हैं। उस स्थिति में, आप एक्सेल फॉर्मूला का उपयोग करके FOR लूप बना सकते हैं। यहां, मैं SUMIFS फ़ंक्शन का उपयोग करके एक्सेल में FOR लूप बनाऊंगा। चरण नीचे दिए गए हैं।

चरण:

• सबसे पहले, आपको एक अलग सेल F7 चुनना होगा जहां आप स्थिति देखना चाहते हैं।
• दूसरे, आपको F7 सेल में संबंधित फॉर्मूला उपयोग करना चाहिए।

=SUMIFS($C$5:$C$13,$B$5:$B$13,E7)

• बाद में, परिणाम प्राप्त करने के लिए ENTER दबाएं।

फॉर्मूला ब्रेकडाउन

• यहां, $C$5:$C$13 वह डेटा रेंज है जिससे SUMIFS फ़ंक्शन योग करेगा।
• फिर, $B$5:$B$13 वह डेटा रेंज है जहां से SUMIFS फ़ंक्शन दिए गए मानदंड की जांच करेगा
• अंत में, E7 मानदंड है।
• तो, SUMIFS फ़ंक्शन E7 सेल मान के लिए भुगतान जोड़ देगा।

• उसके बाद, आपको फिल हैंडल आइकन को F8:F10 सेल में ऑटोफिल करने के लिए खींचना होगा।

अंत में, आपको परिणाम मिलेगा।

निष्कर्ष

हमें उम्मीद है कि आपने इस लेख को उपयोगी पाया। यहां, हमने फॉर्मूला का उपयोग करके एक्सेल में FOR लूप बनाने के 3 उपयुक्त उदाहरण समझाए हैं। आप अधिक एक्सेल-संबंधित सामग्री सीखने के लिए हमारी वेबसाइट ExcelDemy पर जा सकते हैं। कृपया, नीचे कमेंट सेक्शन में टिप्पणी, सुझाव, या प्रश्न छोड़ें यदि आपके पास कोई है।

और पढ़ें

• एक्सेल VBA में डू व्हाइल लूप का उपयोग कैसे करें
• VBA एक्सेल में फॉर नेक्स्ट लूप (कैसे स्टेप और एग्जिट लूप)