शीर्ष 10 उन्नत एक्सेल फ़ार्मुलों और कार्यों की सूची
# 1 - Excel में VLOOKUP फॉर्मूला
यह उन्नत एक्सेल फ़ंक्शन एक्सेल में सबसे अधिक उपयोग किए जाने वाले फॉर्मूलों में से एक है। यह मुख्य रूप से इस सूत्र की सादगी और अन्य तालिकाओं से एक निश्चित मूल्य की तलाश में इसके आवेदन के कारण है, जिसका इन तालिकाओं में एक सामान्य चर है। मान लीजिए कि आपके पास कंपनी के कर्मचारी के वेतन और कर्मचारी आईडी के साथ प्राथमिक कॉलम होने के बारे में विवरण के साथ दो टेबल हैं। आप तालिका ए में टेबल बी से वेतन प्राप्त करना चाहते हैं।
आप नीचे के रूप में VLOOKUP का उपयोग कर सकते हैं।
यह नीचे दी गई तालिका में परिणाम देगा जब हम कर्मचारी वेतन स्तंभ के अन्य कक्षों में इस उन्नत एक्सेल फॉर्मूला को लागू करते हैं।
शेष कोशिकाओं के लिए सूत्र खींचें।
VLOOKUP के तीन प्रमुख परिसीमन हैं:
- आपके पास स्तंभ के दाईं ओर एक प्राथमिक स्तंभ नहीं हो सकता है जिसके लिए आप किसी अन्य तालिका से मान को पॉप्युलेट करना चाहते हैं। इस मामले में, कर्मचारी वेतन कॉलम कर्मचारी आईडी से पहले नहीं हो सकता है।
- तालिका बी में प्राथमिक कॉलम में डुप्लिकेट किए गए मानों में, पहला मान सेल में पॉप्युलेट हो जाएगा।
- यदि आप डेटाबेस में एक नया कॉलम सम्मिलित करते हैं (तालिका बी में कर्मचारी वेतन से पहले एक नया कॉलम डालें), सूत्र का आउटपुट उस स्थिति के आधार पर भिन्न हो सकता है जिसे आपने सूत्र में उल्लेख किया है (उपरोक्त मामले में, परिणाम खाली होगा)
# 2 - एक्सेल में INDEX फॉर्मूला
इस उन्नत एक्सेल सूत्र का उपयोग किसी दिए गए तालिका में पंक्तियों, स्तंभों या दोनों की संख्या निर्दिष्ट करके सेल का मान प्राप्त करने के लिए किया जाता है। Ex। 5 वें अवलोकन पर एक कर्मचारी का नाम प्राप्त करने के लिए , नीचे डेटा है।
हम नीचे के रूप में उन्नत एक्सेल सूत्र का उपयोग कर सकते हैं:
समान INDEX सूत्र का उपयोग पंक्ति के साथ मान प्राप्त करने में किया जा सकता है। पंक्ति और स्तंभ संख्या दोनों का उपयोग करते समय, सिंटैक्स कुछ इस तरह दिखाई देगा:
उपरोक्त सूत्र "राजेश वेद" लौटाएगा।
नोट: यदि आप 5 वीं पंक्ति में दूसरी पंक्ति सम्मिलित करते हैं , तो सूत्र "चंदन काले" लौटाएगा। इसलिए, आउटपुट समय के साथ डेटा तालिका में होने वाले किसी भी बदलाव पर निर्भर करेगा।
# 3 - एक्सेल में MATCH फॉर्मूला
यह एक्सेल उन्नत सूत्र पंक्ति या स्तंभ संख्या को लौटाता है जब दी गई सीमा में एक विशिष्ट स्ट्रिंग या संख्या होती है। नीचे दिए गए उदाहरण में, हम कर्मचारी नाम कॉलम में "राजेश वेद" खोजने की कोशिश कर रहे हैं।
फॉर्मूला नीचे दिया जाएगा:
MATCH फ़ंक्शन मान के रूप में 5 लौटाएगा।
3 rd तर्क का सटीक मिलान के लिए उपयोग किया जाता है। आप अपनी आवश्यकताओं के आधार पर +1 और -1 का उपयोग भी कर सकते हैं।
नोट: कोई INDEX और MATCH को मिला कर VLOOKUP की सीमा को पार कर सकता है।
# 4 - अगर और एक्सेल में सूत्र
ऐसे कई उदाहरण हैं जब किसी को कुछ बाधाओं के आधार पर झंडे बनाने की आवश्यकता होती है। हम सभी IF के मूल सिंटैक्स से परिचित हैं। हम मौजूदा फ़ील्ड पर कुछ अवरोध के आधार पर एक नया फ़ील्ड बनाने के लिए इस उन्नत एक्सेल IF फ़ंक्शन का उपयोग करते हैं। लेकिन क्या होगा अगर हमें ध्वज बनाते समय कई स्तंभों का उपयोग करने की आवश्यकता है। Ex। नीचे के मामले में, हम उन सभी कर्मचारियों को चिह्नित करना चाहते हैं, जिनका वेतन 50K से अधिक है, लेकिन कर्मचारी आईडी 3 से अधिक है।
हम ऐसे मामलों में IF और का उपयोग करेंगे। कृपया नीचे स्क्रीनशॉट उसी के लिए खोजें।
यह 0 के रूप में परिणाम लौटाएगा।
AND का उपयोग कर कई स्तंभों के आधार पर ध्वज बनाने के लिए हमारे पास कई शर्तें या बाधाएं हो सकती हैं।
# 5 - यदि एक्सेल में IF या फॉर्मूला है
इसी तरह, हम OR के बदले OR फ़ंक्शन का उपयोग भी कर सकते हैं और यदि हमें कई शर्तों में से एक को संतुष्ट करना है।
उपरोक्त मामलों में, यदि किसी एक स्थिति में संतुष्ट हो जाता है, तो हमारे पास सेल 1 के रूप में पॉपुलेटेड होगा 0. हम दोहरे उद्धरणों ("") के साथ कुछ सब्सट्रिंग्स द्वारा 1 या 0 को स्थानापन्न कर सकते हैं।
# 6 - एक्सेल में SUMIF फॉर्मूला
कुछ विश्लेषणों में, आपको योग या गणना फ़ंक्शन को लागू करते समय कुछ टिप्पणियों को फ़िल्टर करने की आवश्यकता हो सकती है। ऐसे मामलों में, एक्सेल में यह उन्नत एक्सेल SUMIF फ़ंक्शन हमारे बचाव में है। यह इस उन्नत एक्सेल फॉर्मूले में दी गई कुछ शर्तों के आधार पर सभी अवलोकनों को फ़िल्टर करता है और उन्हें सारांशित करता है। Ex। क्या होगा यदि हम केवल उन कर्मचारियों के वेतन का योग जानना चाहते हैं जिनके पास कर्मचारी आईडी 3 से अधिक है।
SUMIFS फॉर्मूला लागू करके:
फॉर्मूला 322000 के रूप में परिणाम देता है।
हम SUMIF के बजाय COUNTIF का उपयोग करते समय कर्मचारी ID वाले संगठन के कर्मचारियों की संख्या 3 से अधिक भी गिन सकते हैं।
# 7 - एक्सेल में सहमति फॉर्मूला
यह एक्सेल एडवांस्ड फंक्शन एक सूत्र है जिसे कई वेरिएंट के साथ इस्तेमाल किया जा सकता है। यह उन्नत एक्सेल फॉर्मूला हमें कई टेक्स्ट स्ट्रिंग्स को एक टेक्स्ट स्ट्रिंग में शामिल करने में मदद करता है। पूर्व, यदि हम एक ही कॉलम में कर्मचारी आईडी और कर्मचारी का नाम दिखाना चाहते हैं।
ऐसा करने के लिए हम इस CONCATENATE सूत्र का उपयोग कर सकते हैं।
उपरोक्त सूत्र "1Aman गुप्ता" में परिणाम देगा।
हमारे पास ID और NAME के बीच एक एकल हाइफ़न लगाकर एक और संस्करण हो सकता है। Ex। CONCATENATE (B3, "-", C3) का परिणाम "1-अमन गुप्ता" होगा। हम इसका उपयोग VLOOKUP में भी कर सकते हैं जब एक्सेल वैल्यू में LOOKUP एक से अधिक वेरिएबल का मिश्रण है।
# 8 - एक्सेल में LEFT, MID और राइट फॉर्मूला
हम इस उन्नत एक्सेल सूत्र का उपयोग किसी दिए गए स्ट्रिंग से एक विशिष्ट सबस्ट्रिंग निकालने के लिए कर सकते हैं। यह हमारी आवश्यकताओं के आधार पर इस्तेमाल किया जा सकता है। Ex। यदि हम कर्मचारी नाम से पहले 5 अक्षर निकालना चाहते हैं, तो हम स्तंभ नाम के साथ एक्सेल में दूसरा और 5 के रूप में दूसरा पैरामीटर का उपयोग कर सकते हैं।
आउटपुट नीचे दिया गया है:
एक्सेल में राइट फॉर्मूला का एप्लिकेशन भी यही है। यह सिर्फ इतना है कि हम चरित्र को स्ट्रिंग के दाईं ओर से देख रहे हैं। हालांकि, एक्सेल में MID फ़ंक्शन के मामले में, हमें आवश्यक टेक्स्ट स्ट्रिंग की प्रारंभिक स्थिति और स्ट्रिंग की लंबाई देनी चाहिए।
# 9 - एक्सेल में OFFSET फॉर्मूला
SUM या AVERAGE जैसे अन्य फ़ंक्शन के संयोजन के साथ यह उन्नत एक्सेल फ़ंक्शन गणनाओं को एक गतिशील स्पर्श दे सकता है। यह उन मामलों में सबसे अच्छा उपयोग किया जाता है जब हम मौजूदा डेटाबेस में निरंतर पंक्तियाँ डाल रहे हैं। OFFSET Excel हमें एक रेंज प्रदान करता है, जहाँ हमें संदर्भ सेल, पंक्तियों की संख्या और स्तंभों का उल्लेख करना होता है। Ex। यदि हम कंपनी में पहले 5 कर्मचारियों के औसत की गणना करना चाहते हैं, जहां हमारे पास कर्मचारी आईडी द्वारा छांटे गए कर्मचारियों का वेतन है, तो हम निम्नलिखित कार्य कर सकते हैं। नीचे की गणना हमेशा हमें वेतनभोगी देगी।
- यह हमें पहले 5 कर्मचारियों के वेतन का योग देगा।
# 10 - एक्सेल में TRIM फॉर्मूला
इस उन्नत एक्सेल सूत्र का उपयोग पाठ से महत्वहीन रिक्त स्थान को साफ करने के लिए किया जाता है। Ex। यदि हम किसी नाम की शुरुआत में रिक्त स्थान हटाना चाहते हैं, तो हम नीचे दिए गए अनुसार एक्सेल में TRIM फ़ंक्शन का उपयोग कर सकते हैं:
परिणामी उत्पादन चंदन से पहले बिना किसी स्थान के "चंदन कले" होगा।
