डेटा विश्लेषण के लिए 10 आवश्यक Microsoft Excel कार्य

1. CONCATENATE

=CONCATENATE डेटा विश्लेषण के लिए सबसे महत्वपूर्ण कार्यों में से एक है क्योंकि यह आपको टेक्स्ट, नंबर, दिनांक आदि को संयोजित करने की अनुमति देता है। कई कोशिकाओं से एक में। यह फ़ंक्शन विभिन्न कोशिकाओं के डेटा को एक एकल कक्ष में संयोजित करने के लिए विशेष रूप से उपयोगी है। उदाहरण के लिए, यह मार्केटिंग अभियानों के लिए ट्रैकिंग पैरामीटर बनाने, एपीआई क्वेरी बनाने, संख्या प्रारूप में टेक्स्ट जोड़ने और कई अन्य चीजों के लिए उपयोगी है।

ऊपर के उदाहरण में, मैं एक ही कॉलम में महीना और बिक्री एक साथ चाहता था। उसके लिए, मैंने परिणाम के रूप में जनवरी $700 प्राप्त करने के लिए सेल C2 में सूत्र =CONCATENATE(A2, B2) का उपयोग किया है।

सूत्र: =जुड़ाव(वे सेल जिन्हें आप संयोजित करना चाहते हैं)

2. लेन

=LEN डेटा विश्लेषण के लिए एक और उपयोगी कार्य है जो अनिवार्य रूप से किसी भी सेल में वर्णों की संख्या को आउटपुट करता है। शीर्षक टैग या वर्ण सीमा वाले विवरण बनाते समय फ़ंक्शन मुख्य रूप से प्रयोग योग्य होता है। यह तब भी उपयोगी हो सकता है जब आप विभिन्न विशिष्ट पहचानकर्ताओं के बीच अंतर का पता लगाने की कोशिश कर रहे हों जो अक्सर काफी लंबे होते हैं और सही क्रम में नहीं होते हैं।

ऊपर दिए गए उदाहरण में, मैं हर महीने देखे जाने की संख्या के आंकड़े गिनना चाहता था। इसके लिए, मैंने परिणाम के रूप में 5 प्राप्त करने के लिए सेल D2 में सूत्र =LEN (C2) का उपयोग किया।

सूत्र: = एलईएन (कक्ष)

3. वीलुकअप

=VLOOKUP शायद डेटा विश्लेषण से परिचित किसी भी व्यक्ति के लिए सबसे अधिक पहचाने जाने योग्य कार्यों में से एक है। आप इसका उपयोग किसी इनपुट मान वाली तालिका से डेटा का मिलान करने के लिए कर सकते हैं। फ़ंक्शन मिलान के दो तरीके प्रदान करता है - सटीक और अनुमानित, जिसे लुकअप की सीमा द्वारा नियंत्रित किया जाता है। यदि आप सीमा को FALSE पर सेट करते हैं, तो यह एक सटीक मिलान की तलाश करेगा, लेकिन यदि आप इसे TRUE पर सेट करते हैं, तो यह एक अनुमानित मिलान की तलाश करेगा।

ऊपर के उदाहरण में, मैं किसी विशेष महीने में देखे जाने की संख्या देखना चाहता था। उसके लिए, मैंने सेल G4 में सूत्र =VLOOKUP("Jun", A2:C13, 3) का उपयोग किया और मुझे परिणाम के रूप में 74992 मिला। यहां, "जून" लुकअप मान है, A2:C13 तालिका सरणी है जिसमें मैं "जून" की तलाश कर रहा हूं और 3 कॉलम की संख्या है जिसमें सूत्र जून के लिए संबंधित दृश्य ढूंढेगा।

इस फ़ंक्शन का उपयोग करने का एकमात्र नकारात्मक पक्ष यह है कि यह केवल डेटा के साथ काम करता है जिसे कॉलम में व्यवस्थित किया गया है, इसलिए नाम - लंबवत लुकअप। इसलिए यदि आपने अपना डेटा पंक्तियों में व्यवस्थित कर लिया है, तो आपको सबसे पहले करने की आवश्यकता होगी पंक्तियों को स्तंभों में स्थानांतरित करें.

सूत्र: = वीलुकअप (लुकअप_वैल्यू, टेबल_एरे, col_index_num, [रेंज_लुकअप])

4. सूचकांक/मैच

VLOOKUP फ़ंक्शन की तरह, INDEX और MATCH फ़ंक्शन इनपुट मान के आधार पर विशिष्ट डेटा खोजने के काम आते हैं। INDEX और MATCH, जब एक साथ उपयोग किए जाते हैं, तो गलत परिणाम देने की VLOOKUP की सीमाओं को पार कर सकते हैं (यदि आप सावधान नहीं हैं)। इसलिए जब आप इन दो कार्यों को जोड़ते हैं, तो वे डेटा संदर्भ को इंगित कर सकते हैं और एकल आयाम सरणी में मान खोज सकते हैं। यह डेटा के निर्देशांक को एक संख्या के रूप में लौटाता है।

ऊपर के उदाहरण में, मैं जनवरी में देखे जाने की संख्या देखना चाहता था। उसके लिए, मैंने सूत्र =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3) का उपयोग किया। यहां, A2:C13 डेटा का कॉलम है जिसे मैं चाहता हूं कि सूत्र वापस आ जाए, "जनवरी" वह मान है जिसका मैं मिलान करना चाहता हूं, A2:A13 है कॉलम जिसमें सूत्र "जन" मिलेगा और 0 यह दर्शाता है कि मैं चाहता हूं कि सूत्र एक सटीक मिलान ढूंढे मूल्य।

यदि आप एक अनुमानित मिलान खोजना चाहते हैं तो आपको 0 को 1 या -1 से बदलना होगा। ताकि 1 को लुकअप मान से कम या उसके बराबर सबसे बड़ा मान मिलेगा और -1 को लुकअप मान से कम या उसके बराबर सबसे छोटा मान मिलेगा। ध्यान दें कि यदि आप 0, 1, या -1 का उपयोग नहीं करते हैं, तो सूत्र 1 का उपयोग करेगा।

अब यदि आप महीने के नाम को हार्डकोड नहीं करना चाहते हैं, तो आप इसे सेल नंबर से बदल सकते हैं। तो हम समान परिणाम प्राप्त करने के लिए ऊपर वर्णित सूत्र में "जन" को F3 या A2 से बदल सकते हैं।

सूत्र: = सूचकांक (डेटा का कॉलम जिसे आप वापस करना चाहते हैं, MATCH (सामान्य डेटा बिंदु जिसे आप मिलान करने का प्रयास कर रहे हैं, अन्य डेटा स्रोत का कॉलम जिसमें सामान्य डेटा बिंदु है, 0))

5. मिनी/मैक्सिफ़्स

=MINIFS और =MAXIFS बहुत हद तक =MIN और =MAX फ़ंक्शन के समान हैं, इस तथ्य को छोड़कर कि वे आपको न्यूनतम/अधिकतम मानों का सेट लेने की अनुमति देते हैं और विशेष मानदंडों पर भी उनका मिलान करते हैं। तो अनिवार्य रूप से, फ़ंक्शन न्यूनतम/अधिकतम मानों की तलाश करता है और इनपुट मानदंडों से मेल खाता है।

ऊपर के उदाहरण में, मैं छात्र के लिंग के आधार पर न्यूनतम अंक खोजना चाहता था। उसके लिए, मैंने सूत्र =MINIFS (C2:C10, B2:B10, "M") का उपयोग किया और मुझे परिणाम 27 मिला। यहां C2:C10 वह कॉलम है जिसमें सूत्र अंकों की तलाश करेगा, B2:B10 एक स्तंभ है जिसमें सूत्र मानदंड (लिंग) की तलाश करेगा, और "M" मानदंड है।

इसी तरह, अधिकतम स्कोर के लिए, मैंने सूत्र =MAXIFS(C2:C10, B2:B10, "M") का उपयोग किया और परिणाम 100 प्राप्त किया।

MINIFS के लिए फॉर्मूला: =मिनीफ्स(मिन_रेंज, क्राइटेरिया_रेंज1, क्राइटेरिया1,...)

MAXIFS के लिए सूत्र: =मैक्सीएफएस(मैक्स_रेंज, क्राइटेरिया_रेंज1, क्राइटेरिया1,...)

6. औसत

=AVERAGEIFS फ़ंक्शन आपको एक या अधिक मानदंडों के आधार पर किसी विशेष डेटा सेट के लिए औसत खोजने की अनुमति देता है। इस फ़ंक्शन का उपयोग करते समय, आपको यह ध्यान रखना चाहिए कि प्रत्येक मानदंड और औसत श्रेणी भिन्न हो सकती है। हालाँकि, =AVERAGEIF फ़ंक्शन में, मानदंड श्रेणी और योग श्रेणी दोनों का आकार सीमा समान होनी चाहिए। इन कार्यों के बीच एकवचन और बहुवचन के अंतर पर ध्यान दें? खैर, वहीं आपको सावधान रहने की जरूरत है।

इस उदाहरण में, मैं विद्यार्थियों के लिंग के आधार पर औसत अंक ज्ञात करना चाहता था। उसके लिए मैंने सूत्र का उपयोग किया, =AVERAGEIFS(C2:C10, B2:B10, "M") और परिणाम के रूप में 56.8 प्राप्त किया। यहाँ, C2:C10 वह श्रेणी है जिसमें सूत्र औसत की तलाश करेगा, B2:B10 मानदंड श्रेणी है, और "M" मानदंड है।

सूत्र: = औसत (औसत)औसत_रेंज, मानदंड_रेंज1, मानदंड1,...)

7. COUNTIFS

अब यदि आप उन उदाहरणों की संख्या की गणना करना चाहते हैं जो एक डेटा सेट विशिष्ट मानदंडों को पूरा करता है, तो आपको =COUNTIFS फ़ंक्शन का उपयोग करना होगा। यह फ़ंक्शन आपको अपनी क्वेरी में असीमित मानदंड जोड़ने की अनुमति देता है, और इस प्रकार इनपुट मानदंड के आधार पर गिनती खोजने का यह सबसे आसान तरीका बनाता है।

इस उदाहरण में, मैं उत्तीर्ण अंक प्राप्त करने वाले पुरुष या महिला छात्रों की संख्या ज्ञात करना चाहता था (अर्थात >=40)। उसके लिए मैंने सूत्र का उपयोग किया =COUNTIFS(B2:B10, "M", C2:C10, ">=40")। यहाँ, B2:B10 वह श्रेणी है जिसमें सूत्र पहले मानदंड (लिंग) के लिए दिखेगा, "M" पहला मानदंड है, C2:C10 वह श्रेणी है जिसमें सूत्र दूसरे मानदंड (अंक) की तलाश करेगा, और ">=40" दूसरा है मानदंड।

सूत्र: =काउंटीएफएस(मानदंड_रेंज1, मानदंड1,...)

8. SUMPRODUCT

=SUMPRODUCT फ़ंक्शन आपको श्रेणियों या सरणियों को एक साथ गुणा करने में मदद करता है और फिर उत्पादों का योग देता है। यह काफी बहुमुखी कार्य है और इसका उपयोग COUNTIFS या SUMIFS जैसे सरणियों को गिनने और योग करने के लिए किया जा सकता है, लेकिन अतिरिक्त लचीलेपन के साथ। आप SUMPRODUCT के अन्य कार्यों का उपयोग इसकी कार्यक्षमता को और भी आगे बढ़ाने के लिए कर सकते हैं।

इस उदाहरण में, मैं बेचे गए सभी उत्पादों का योग ज्ञात करना चाहता था। उसके लिए, मैंने सूत्र =SUMPRODUCT(B2:B8, C2:C8) का उपयोग किया। यहाँ, B2:B8 पहली सरणी है (बेचे गए उत्पादों की मात्रा) और C2:C8 दूसरी सरणी (प्रत्येक उत्पाद की कीमत) है। इसके बाद फ़ॉर्मूला बेचे गए प्रत्येक उत्पाद की मात्रा को उसकी कीमत से गुणा करता है और फिर कुल बिक्री देने के लिए उसे जोड़ता है।

सूत्र: = SUMPRODUCT (सरणी 1, [सरणी 2], [सरणी 3],...)

9. ट्रिम

=TRIM फ़ंक्शन विशेष रूप से तब उपयोगी होता है जब आप ऐसे डेटा सेट के साथ काम कर रहे होते हैं जिसमें कई रिक्त स्थान या अवांछित वर्ण होते हैं। फ़ंक्शन आपको अपने डेटा से इन रिक्त स्थान या वर्णों को आसानी से हटाने की अनुमति देता है, जिससे आप अन्य कार्यों का उपयोग करते समय सटीक परिणाम प्राप्त कर सकते हैं।

इस उदाहरण में, मैं A7 में माउस और पैड शब्दों के बीच के सभी अतिरिक्त रिक्त स्थान को हटाना चाहता था। उसके लिए मैंने सूत्र =TRIM(A7) का प्रयोग किया।

सूत्र ने केवल अतिरिक्त रिक्त स्थान को हटा दिया और परिणाम माउस पैड को एक स्थान के साथ वितरित किया।

सूत्र: = ट्रिम (मूलपाठ)

10. खोजें/खोजें

चीजों को गोल करना FIND/SEARCH फ़ंक्शन हैं जो डेटा सेट के भीतर विशिष्ट टेक्स्ट को अलग करने में आपकी सहायता करेंगे। एक प्रमुख अंतर को छोड़कर, दोनों कार्य काफी समान हैं, जो वे करते हैं - =FIND फ़ंक्शन केवल केस-संवेदी मिलान देता है। इस बीच, =SEARCH फ़ंक्शन की ऐसी कोई सीमा नहीं है। विसंगतियों या विशिष्ट पहचानकर्ताओं की तलाश में ये फ़ंक्शन विशेष रूप से उपयोगी होते हैं।

इस उदाहरण में, मैं यह जानना चाहता था कि गाइडिंग टेक में कितनी बार 'गुई' दिखाई दिया, जिसके लिए मैंने सूत्र =FIND(A2, B2) का उपयोग किया, जिसने परिणाम 1 दिया। अब अगर मैं इसके बजाय गाइडिंग टेक में 'गुई' के प्रकट होने की संख्या का पता लगाना चाहता हूं, तो मुझे = खोज सूत्र का उपयोग करना होगा क्योंकि यह केस संवेदनशील नहीं है।

खोजने के लिए सूत्र: =ढूंढें(find_text, भीतर_पाठ, [start_num])

खोज के लिए सूत्र: =खोज(find_text, भीतर_पाठ, [start_num])