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

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

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

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

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

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

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

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

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

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

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

ऊपर दिए गए प्रश्न को ध्यान से पढ़ें और उसे हल करने का प्रयास करें…

उपरोक्त समस्या के समाधान के लिए कदम:

यहां, मैं या . का उपयोग करूंगा , ऑफसेट , अधिकतम , मिनट , और पंक्ति फॉर लूप . बनाने के लिए एक्सेल फॉर्मूला के रूप में कार्य करता है ।

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

इस समय, नया स्वरूपण नियम संवाद बॉक्स प्रकट होता है।

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

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

इस समय, स्वरूप कक्ष . नामक एक संवाद बॉक्स दिखाई देगा।

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

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

अंत में, आपको फ़ॉर्मेट किए गए नंबर मिलेंगे।

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

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

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

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

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

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

चरण:

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

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

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

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

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

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

• जब या फ़ंक्शन TRUE gives देता है तब आपको “जानकारी अनुपलब्ध . मिलेगा ” स्थिति . के रूप में . अन्यथा, आपको “हो गया . मिलेगा ” स्थिति . के रूप में ।

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

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

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

चरण:

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

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

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

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

• उसके बाद, आपको भरें हैंडल . को खींचना होगा शेष कक्षों में संबंधित डेटा को स्वतः भरने के लिए आइकन F8:F10 ।

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

निष्कर्ष

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

और पढ़ें

• Excel VBA में डू वाइल लूप का उपयोग कैसे करें
• वीबीए एक्सेल में अगले लूप के लिए (लूप को कैसे चरणबद्ध और बाहर निकलें)