डेटा पर सुपर-गणना करने के लिए CSE फॉर्मूला (सरणी फॉर्मूला) का उपयोग करें - एक्सेल टिप्स

एक्सेल में अपने फॉर्मूले को सुपरचार्ज करने के लिए Ctrl + Shift + Enter (CSE फॉर्मूला) का उपयोग करें! हाँ, यह सच है-एक्सेल में सूत्रों का एक गुप्त वर्ग है। यदि आप जादू तीन कुंजियों को जानते हैं, तो आप हजारों अन्य फ़ार्मुलों को बदलने के लिए एकल एक्सेल सरणी सूत्र प्राप्त कर सकते हैं।

एक्सेल यूजर्स में से 95% को CSE फॉर्मूला के बारे में जानकारी नहीं है। जब ज्यादातर लोग अपना असली नाम सुनते हैं, तो उन्हें लगता है कि "यह कम से कम उपयोगी नहीं है" और उनके बारे में जानने की जहमत नहीं उठानी चाहिए। यदि आपको लगता है कि SumIf और CountIf शांत हैं, तो आपको जल्द ही पता चलेगा कि Ctrl + Shift + Enter (CSE) सूत्र, SumIf और CountIf के चारों ओर सर्किल चलाएंगे। सीएसई सूत्र आपको एक सूत्र के साथ 1000 के सूत्रों की कोशिकाओं को सचमुच बदलने की अनुमति देते हैं। हां, मेरे साथी एक्सेल गुरु के, हमारी नाक के ठीक नीचे यह शक्तिशाली कुछ है और हम इसका उपयोग कभी नहीं करते हैं।

इससे पहले कि सुमिफ़ था, आप सुमीफ़ जैसा ही काम करने के लिए एक सीएसई सूत्र का उपयोग कर सकते हैं। Microsoft ने हमें SumIf और CountIf दिया, लेकिन CSE सूत्र अप्रचलित नहीं हैं। अरे नहीं, और वे बहुत कुछ कर सकते हैं! अगर आप एवरेज करना चाहते हैं तो क्या होगा? GrowthIf के बारे में कैसे? AveDevIf? यहां तक ​​कि MultiplyByPiAndTakeTheSquareRootIf। बस कुछ के बारे में आप कल्पना कर सकते हैं कि इनमें से एक सीएसई सूत्र के साथ किया जा सकता है।

यहाँ मुझे लगता है कि CSE सूत्र कभी नहीं पकड़े गए। पहला, उनका असली नाम हर किसी को डराता है। दूसरा, उन्हें काम करने के लिए एक विदेशी, सहज ज्ञान युक्त हैंडलिंग की आवश्यकता होती है। CSE सूत्र में टाइप करने के बाद, आप Enter दर्ज नहीं कर सकते। आप एक तीर कुंजी के एक क्लिक के साथ सेल से बाहर नहीं निकल सकते। यहां तक ​​कि अगर आप सूत्र को सही पाते हैं और एंटर कुंजी दबाते हैं, तो एक्सेल आपको पूरी तरह से गैर-उपयोगकर्ता के अनुकूल "VALUE!" त्रुटि। यह नहीं कहता, "वाह - यह सुंदर है। आप 99.1% वहां हैं," जो आप शायद थे।

यह रहस्य है: CSE फॉर्मूला टाइप करने के बाद, आपको Ctrl और Shift कुंजियों को दबाए रखना होगा और फिर Enter को हिट करना होगा। एक चिपचिपा नोट पकड़ो और उसे नीचे लिखें: Ctrl Shift Enter। पावर एक्सेल उपयोगकर्ताओं के पास महीने में एक बार इन फॉर्मूलों का उपयोग करने का अवसर होगा। यदि आप अभी Ctrl Shift दर्ज नहीं करते हैं, तो आप इसे तब तक भूल जाएंगे, जब तक कुछ फिर से नहीं आ जाता।

इस उदाहरण की जाँच करें: मान लें कि आप स्तंभ C में केवल उन मानों को औसत करना चाहते हैं जहाँ स्तंभ A पूर्व क्षेत्र में था।

कक्ष A13 में सूत्र CSE सूत्र का एक उदाहरण है।

=AVERAGE(IF(A2:A10="East",C2:C10))

इसे प्लग इन करें और आपको 7452.667, पूर्व के मूल्यों का औसत मिलेगा। ठंडा? आपने अभी-अभी एक्सेलएक्सएक्स एक्सेल फ़ंक्शन के अपने संस्करण का निर्माण किया है। याद रखें: सूत्र दर्ज करने के लिए Ctrl + Shift + Enter दबाएं।

नीचे दिए गए अगले उदाहरण की जाँच करें।

इस उदाहरण में, हम CSE सूत्र को अधिक सामान्य बनाते हैं। यह निर्दिष्ट करने के बजाय कि हम "पूर्व" की तलाश कर रहे हैं, इंगित करें कि आप चाहते हैं कि A13 में जो भी मूल्य है। सूत्र अब है =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10))।

विचित्र रूप से पर्याप्त है, एक्सेल आपको बिना किसी विशेष कीस्ट्रोक्स के सीएसई फार्मूले की प्रतिलिपि बनाने और पेस्ट करने देगा। मैंने C13 को C14: C15 में कॉपी किया और अब मेरे पास सभी क्षेत्रों के लिए औसत है।

हमारा मेनफ्रेम सिस्टम क्वांटिटी और यूनिट प्राइस को स्टोर करता है, लेकिन विस्तारित कीमत को नहीं। निश्चित रूप से, कॉलम C को जोड़ना =A2*B2और उसके साथ कुल कॉलम C भरना काफी आसान है, लेकिन आपके पास नहीं है!

यहाँ, हमारा CSE फॉर्मूला है =SUM(A2:A10*B2:B10)। यह A2: A10 में प्रत्येक सेल लेता है, बी 2 में संबंधित सेल द्वारा गुणा करता है: B10 और परिणाम को योग करता है। फॉर्मूला टाइप करें, एंट्री मारने के दौरान Ctrl और Shift दबाए रखें, और आपके पास 10 के बजाय एक सूत्र में जवाब दें।

क्या आप देखते हैं कि यह कितना शक्तिशाली है? यहां तक ​​कि अगर मेरे पास 10,000 पंक्तियों का डेटा था, तो एक्सेल इस एकल सूत्र को ले जाएगा, 10,000 गुणा करें और मुझे परिणाम दें।

ठीक है, यहां नियम हैं: आपको इन फॉर्मूलों को दर्ज करने या संपादित करने के लिए कभी भी Ctrl + Shift + दर्ज करना होगा। पूरी तरह से अस्पष्ट # परिणाम में ऐसा करने में विफलता! त्रुटि। यदि आपके पास कई श्रेणियां हैं, तो उन सभी के लिए समान आकार होना चाहिए। यदि आपके पास एकल कक्षों के साथ मिश्रित श्रेणी है, तो एकल कक्ष आपकी श्रेणी के आकार से मेल खाने के लिए "प्रतिकृति" होंगे।

जब आप सफलतापूर्वक इनमें से किसी एक में प्रवेश करते हैं और इसे सूत्र पट्टी में देखते हैं, तो आपको सूत्र के चारों ओर घुंघराले ब्रेसिज़ होने चाहिए। आप कभी भी घुंघराले ब्रेस में खुद को दर्ज न करें। Ctrl + Shift + Enter दबाकर उन्हें वहां रखा गया है।

ये सूत्र गुरु के लिए कठिन हैं। सिर्फ उनके बारे में सोचकर सिरदर्द हो जाता है। यदि मेरे पास प्रवेश करने के लिए एक कठिन है, तो मैं टूल> विजार्ड्स> सशर्त योग विज़ार्ड पर जाता हूं और संवाद बक्से के माध्यम से चलता हूं। सशर्त राशि विज़ार्ड का उत्पादन एक सीएसई सूत्र है, इसलिए यह आमतौर पर मुझे पर्याप्त संकेत दे सकता है कि मुझे वास्तव में क्या चाहिए, कैसे दर्ज करें।

क्या आपको मिस्टर इरविन के साथ 11 वीं कक्षा में बीएएसआईसी लेना था? या, अभी तक बदतर, क्या आपको कॉलेज में श्रीमती डचेस के साथ रैखिक बीजगणित में "डी" मिला था? यदि ऐसा है, तो आप अच्छी कंपनी में हैं और जब भी आप शब्द "ऐरे" सुनेंगे तो झकझोर जाएंगे। - जब भी कोई भी सरणी कहता है मैं दूसरी दिशा में चिल्ला रहा हूं। मैं उन्हें समझता हूं, लेकिन यह एक्सेल है, मुझे यहां सरणियों की आवश्यकता नहीं है! बुराई रहस्य यह है कि एक्सेल और माइक्रोसॉफ्ट इन फॉर्मूलों को "सरणी सूत्र" कहते हैं। बंद करो - भागो मत। यह ठीक है, वास्तव में है। ने उन्हें CSE फ़ार्मुलों का नाम दिया है क्योंकि यह कम डरावना लगता है, और क्योंकि नाम आपको यह याद रखने में मदद करता है कि उन्हें कैसे दर्ज किया जाए - Ctrl Shift Enter। यदि आप Microsoft से किसी ऐसे व्यक्ति के बारे में बात करते हैं, जिसके पास आपने कभी भी मदद के लिए लेज़र बीजगणित के लिए श्रीमती डचेस, या कभी नहीं था, तो मैं असली नाम का उल्लेख करता हूँ। यदि आप मदद करने जाते हैं,"सरणी सूत्र" के दुष्ट उपनाम के तहत खोज करें, लेकिन हमारे दोस्तों के बीच, चलो उन्हें सीएसई - ठीक कहते हैं?

एक विशेष एक्सेल ऐरे फॉर्मूला का उपयोग करें दो स्थितियों के साथ SUMIF का अनुकरण करें।