रिपोर्टिंग और डेटा प्रोसेसिंग में शामिल लोगों के लिए एक्सेल लाइफ हैक्स

2024 लेखक: Malcolm Clapton | [email protected]. अंतिम बार संशोधित: 2024-01-13 01:38

इस पोस्ट में, मान, इवानोव और फेरबर पब्लिशिंग हाउस के सहायक जनरल डायरेक्टर रेनाट शगाबुतदीनोव ने कुछ शानदार एक्सेल लाइफ हैक्स साझा किए हैं। ये युक्तियाँ विभिन्न रिपोर्टिंग, डेटा प्रोसेसिंग और प्रस्तुतियाँ बनाने में शामिल किसी भी व्यक्ति के लिए उपयोगी होंगी।

इस आलेख में Excel में आपके कार्य को सरल बनाने की सरल तकनीकें शामिल हैं। वे उन लोगों के लिए विशेष रूप से उपयोगी हैं जो प्रबंधन रिपोर्टिंग में लगे हुए हैं, 1 सी और अन्य रिपोर्टों से डाउनलोड के आधार पर विभिन्न विश्लेषणात्मक रिपोर्ट तैयार करते हैं, प्रबंधन के लिए प्रस्तुतिकरण और आरेख बनाते हैं। मैं पूर्ण नवीनता का ढोंग नहीं करता - एक रूप या किसी अन्य रूप में, इन तकनीकों पर शायद मंचों पर चर्चा की गई थी या लेखों में उल्लेख किया गया था।

VLOOKUP और HLOOKUP के सरल विकल्प, यदि वांछित मान तालिका के पहले कॉलम में नहीं हैं: LOOKUP, INDEX + SEARCH

VLOOKUP और HLOOKUP फ़ंक्शन केवल तभी काम करते हैं जब वांछित मान तालिका के पहले कॉलम या पंक्ति में हों, जिससे आप डेटा प्राप्त करने की योजना बना रहे हैं।

अन्यथा, दो विकल्प हैं:

1. लुकअप फ़ंक्शन का उपयोग करें।

   इसमें निम्नलिखित सिंटैक्स है: लुकअप (लुकअप_वेल्यू; लुकअप_वेक्टर; परिणाम_वेक्टर)। लेकिन इसके सही ढंग से काम करने के लिए, view_vector श्रेणी के मानों को आरोही क्रम में क्रमबद्ध किया जाना चाहिए:

   

2. MATCH और INDEX फ़ंक्शंस के संयोजन का उपयोग करें।

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

   

   फ़ंक्शन सिंटैक्स:

   • SEARCH (search_value; search_array; match_type) - हमारे मामले के लिए, हमें एक मिलान प्रकार "सटीक मिलान" की आवश्यकता है, यह संख्या 0 से मेल खाती है।

   • इंडेक्स (सरणी; लाइन_नंबर; [कॉलम_नंबर])। इस मामले में, आपको कॉलम संख्या निर्दिष्ट करने की आवश्यकता नहीं है, क्योंकि सरणी में एक पंक्ति होती है।

किसी सूची में रिक्त कक्षों को शीघ्रता से कैसे भरें

कार्य शीर्ष पर मूल्यों के साथ कॉलम में कोशिकाओं को भरना है (ताकि विषय तालिका की प्रत्येक पंक्ति में हो, और न केवल विषय पर पुस्तकों के ब्लॉक की पहली पंक्ति में):

"विषय" कॉलम का चयन करें, "होम" समूह में रिबन पर क्लिक करें, "ढूंढें और चुनें" बटन → "कोशिकाओं का एक समूह चुनें" → "रिक्त कोशिकाएं" और सूत्र दर्ज करना शुरू करें (अर्थात, एक समान डालें साइन) करें और शीर्ष पर स्थित सेल को देखें, बस अपने कीबोर्ड पर ऊपर तीर पर क्लिक करके। इसके बाद Ctrl+Enter दबाएं। उसके बाद, आप प्राप्त डेटा को मानों के रूप में सहेज सकते हैं, क्योंकि सूत्रों की अब आवश्यकता नहीं है:

सूत्र में त्रुटियां कैसे खोजें

सूत्र के एक अलग भाग की गणना

एक जटिल सूत्र को समझने के लिए (जिसमें अन्य फ़ंक्शन फ़ंक्शन तर्क के रूप में उपयोग किए जाते हैं, अर्थात, कुछ फ़ंक्शन दूसरों में नेस्टेड होते हैं) या इसमें त्रुटियों के स्रोत को खोजने के लिए, आपको अक्सर इसके हिस्से की गणना करने की आवश्यकता होती है। दो आसान तरीके हैं:

1. सूत्र पट्टी में किसी सूत्र के भाग की गणना करने के लिए, उस भाग का चयन करें और F9 दबाएँ:

   

   इस उदाहरण में, खोज फ़ंक्शन के साथ एक समस्या थी - इसमें तर्कों की अदला-बदली की गई थी। यह याद रखना महत्वपूर्ण है कि यदि आप फ़ंक्शन के भाग की गणना को रद्द नहीं करते हैं और एंटर दबाते हैं, तो परिकलित भाग एक संख्या रहेगा।

2. रिबन पर सूत्र समूह में सूत्र की गणना करें बटन पर क्लिक करें:

   

   दिखाई देने वाली विंडो में, आप चरण दर चरण सूत्र की गणना कर सकते हैं और यह निर्धारित कर सकते हैं कि किस चरण में और किस फ़ंक्शन में त्रुटि होती है (यदि कोई हो):

   

यह कैसे निर्धारित किया जाए कि कोई सूत्र किस पर निर्भर करता है या किस पर निर्भर करता है

यह निर्धारित करने के लिए कि कौन से कक्षों पर सूत्र निर्भर करता है, रिबन पर सूत्र समूह में, प्रभावित कक्ष बटन पर क्लिक करें:

तीर यह इंगित करने के लिए प्रकट होते हैं कि गणना परिणाम किस पर निर्भर करता है।

यदि चित्र में लाल रंग में हाइलाइट किया गया प्रतीक प्रदर्शित होता है, तो सूत्र अन्य शीट या अन्य पुस्तकों के कक्षों पर निर्भर करता है:

उस पर क्लिक करके, हम ठीक से देख सकते हैं कि प्रभावित करने वाले सेल या रेंज कहाँ स्थित हैं:

"प्रभावित करने वाली कोशिकाओं" बटन के बगल में "आश्रित सेल" बटन है, जो उसी तरह काम करता है: यह सक्रिय सेल से तीरों को एक सूत्र के साथ उन कोशिकाओं को प्रदर्शित करता है जो उस पर निर्भर हैं।

बटन "निकालें तीर", एक ही ब्लॉक में स्थित है, जिससे आप कोशिकाओं को प्रभावित करने वाले तीरों को, आश्रित कोशिकाओं को तीर, या दोनों प्रकार के तीरों को एक साथ निकालने की अनुमति देते हैं:

एकाधिक शीट से सेल मानों का योग (संख्या, औसत) कैसे खोजें

मान लें कि आपके पास डेटा के साथ एक ही प्रकार की कई शीट हैं जिन्हें आप किसी अन्य तरीके से जोड़ना, गिनना या संसाधित करना चाहते हैं:

ऐसा करने के लिए, उस सेल में जिसमें आप परिणाम देखना चाहते हैं, एक मानक सूत्र दर्ज करें, उदाहरण के लिए SUM (SUM), और उन शीटों की सूची से पहली और अंतिम शीट का नाम निर्दिष्ट करें जिन्हें आपको संसाधित करने की आवश्यकता है तर्क, एक बृहदान्त्र द्वारा अलग किया गया:

आपको "डेटा 1", "डेटा 2", "डेटा 3" शीट से पते बी 3 के साथ कोशिकाओं का योग प्राप्त होगा:

यह एड्रेसिंग स्थित शीट्स के लिए काम करता है लगातार … वाक्य रचना इस प्रकार है: = समारोह (प्रथम_सूची: last_list! श्रेणी संदर्भ)।

टेम्प्लेट वाक्यांशों को स्वचालित रूप से कैसे बनाएं

एक्सेल में टेक्स्ट के साथ काम करने के बुनियादी सिद्धांतों और कुछ सरल कार्यों का उपयोग करके, आप रिपोर्ट के लिए टेम्पलेट वाक्यांश तैयार कर सकते हैं। पाठ के साथ काम करने के कई सिद्धांत:

• हम टेक्स्ट को & साइन का उपयोग करके जोड़ते हैं (आप इसे CONCATENATE फ़ंक्शन से बदल सकते हैं, लेकिन इसका कोई मतलब नहीं है)।
• पाठ हमेशा उद्धरणों में लिखा जाता है, पाठ के साथ कोशिकाओं के संदर्भ हमेशा बिना होते हैं।
• सेवा वर्ण "उद्धरण चिह्न" प्राप्त करने के लिए, तर्क 32 के साथ CHAR फ़ंक्शन का उपयोग करें।

सूत्रों का उपयोग करके टेम्पलेट वाक्यांश बनाने का एक उदाहरण:

नतीजा:

इस मामले में, CHAR फ़ंक्शन (उद्धरण प्रदर्शित करने के लिए) के अलावा, IF फ़ंक्शन का उपयोग किया जाता है, जो आपको सकारात्मक बिक्री प्रवृत्ति के आधार पर टेक्स्ट को बदलने की अनुमति देता है, और टेक्स्ट फ़ंक्शन, जो आपको प्रदर्शित करने की अनुमति देता है किसी भी प्रारूप में संख्या। इसका सिंटैक्स नीचे वर्णित है:

पाठ (मान; प्रारूप)

प्रारूप को उद्धरण चिह्नों में निर्दिष्ट किया गया है, जैसे कि आप स्वरूप कक्ष विंडो में एक कस्टम प्रारूप दर्ज कर रहे थे।

अधिक जटिल ग्रंथों को भी स्वचालित किया जा सकता है। मेरे व्यवहार में, प्रबंधन रिपोर्टिंग के प्रारूप में लंबी, लेकिन नियमित टिप्पणियों का स्वचालन था "इंडिकेटर योजना के सापेक्ष XX द्वारा गिर गया / गुलाब, मुख्य रूप से XX द्वारा FACTOR1 की वृद्धि / गिरावट, FACTOR2 की वृद्धि / गिरावट के कारण YY …" कारकों की बदलती सूची के साथ। यदि आप इस तरह की टिप्पणियाँ अक्सर लिखते हैं और उन्हें लिखने की प्रक्रिया को एल्गोरिथम बनाया जा सकता है, तो एक बार एक सूत्र या मैक्रो बनाने के लिए यह सार्थक है जो आपको कम से कम कुछ काम बचाएगा।

संयोजन के बाद प्रत्येक सेल में डेटा कैसे स्टोर करें

जब आप सेल मर्ज करते हैं, तो केवल एक मान बरकरार रहता है। कोशिकाओं को मर्ज करने का प्रयास करते समय एक्सेल इस बारे में चेतावनी देता है:

तदनुसार, यदि आपके पास प्रत्येक सेल के आधार पर एक सूत्र था, तो यह उन्हें संयोजित करने के बाद काम करना बंद कर देगा (उदाहरण के 3-4 पंक्तियों में # N / A त्रुटि):

कोशिकाओं को मर्ज करने के लिए और अभी भी उनमें से प्रत्येक में डेटा को संरक्षित करने के लिए (शायद आपके पास इस सार उदाहरण में एक सूत्र है; शायद आप कोशिकाओं को मर्ज करना चाहते हैं, लेकिन भविष्य के लिए सभी डेटा रखें या इसे जानबूझकर छुपाएं), शीट पर किसी भी सेल को मर्ज करें, उनका चयन करें, और फिर फ़ॉर्मेट पेंटर कमांड का उपयोग करके फ़ॉर्मेटिंग को उन कक्षों में स्थानांतरित करें जिन्हें आपको संयोजित करने की आवश्यकता है:

एकाधिक डेटा स्रोतों से पिवट कैसे बनाएं

यदि आपको एक साथ कई डेटा स्रोतों से पिवट बनाने की आवश्यकता है, तो आपको रिबन या त्वरित एक्सेस पैनल में "पिवोटटेबल और चार्ट विज़ार्ड" जोड़ना होगा, जिसमें ऐसा विकल्प होता है।

आप इसे निम्नानुसार कर सकते हैं: "फ़ाइल" → "विकल्प" → "त्वरित एक्सेस टूलबार" → "सभी कमांड" → "पिवोटटेबल और चार्ट विज़ार्ड" → "जोड़ें":

उसके बाद, रिबन पर एक संबंधित आइकन दिखाई देगा, जिस पर क्लिक करके वही विज़ार्ड कॉल करेगा:

जब आप उस पर क्लिक करते हैं, तो एक डायलॉग बॉक्स दिखाई देता है:

इसमें, आपको "कई समेकन श्रेणियों में" आइटम का चयन करना होगा और "अगला" पर क्लिक करना होगा। अगले चरण में, आप "एक पृष्ठ फ़ील्ड बनाएँ" या "पृष्ठ फ़ील्ड बनाएँ" का चयन कर सकते हैं। यदि आप स्वतंत्र रूप से प्रत्येक डेटा स्रोत के लिए एक नाम के साथ आना चाहते हैं, तो दूसरा आइटम चुनें:

अगली विंडो में, उन सभी श्रेणियों को जोड़ें जिनके आधार पर पिवट का निर्माण किया जाएगा, और उन्हें नाम दें:

उसके बाद, अंतिम डायलॉग बॉक्स में, निर्दिष्ट करें कि पिवट टेबल रिपोर्ट कहाँ रखी जाएगी - मौजूदा या नई शीट पर:

पिवट टेबल रिपोर्ट तैयार है। "पेज 1" फ़िल्टर में, यदि आवश्यक हो, तो आप डेटा स्रोतों में से केवल एक का चयन कर सकते हैं:

टेक्स्ट बी में टेक्स्ट ए की घटनाओं की संख्या की गणना कैसे करें ("एमटीएस सुपरएमटीएस टैरिफ" - संक्षेप में एमटीएस की दो घटनाएं)

इस उदाहरण में, कॉलम A में कई टेक्स्ट लाइनें हैं, और हमारा काम यह पता लगाना है कि उनमें से प्रत्येक में सेल E1 में स्थित खोज टेक्स्ट कितनी बार शामिल है:

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

1. DLSTR (LEN) - पाठ की लंबाई की गणना करता है, केवल तर्क पाठ है। उदाहरण: DLSTR ("मशीन") = 6.
2. SUBSTITUTE - किसी टेक्स्ट स्ट्रिंग में किसी विशिष्ट टेक्स्ट को दूसरे टेक्स्ट से बदल देता है। सिंटैक्स: स्थानापन्न (पाठ; पुराना_पाठ; नया_पाठ)। उदाहरण: स्थानापन्न ("कार"; "ऑटो"; "") = "मोबाइल"।
3. UPPER - एक स्ट्रिंग में सभी वर्णों को अपरकेस से बदल देता है। एकमात्र तर्क पाठ है। उदाहरण: अपर ("मशीन") = "कार"। केस असंवेदनशील खोज करने के लिए हमें इस फ़ंक्शन की आवश्यकता है। आखिर, UPPER ("कार") = UPPER ("मशीन")

किसी अन्य में एक निश्चित टेक्स्ट स्ट्रिंग की घटना को खोजने के लिए, आपको मूल में इसकी सभी घटनाओं को हटाना होगा और परिणामी स्ट्रिंग की लंबाई की तुलना मूल के साथ करनी होगी:

DLSTR ("टैरिफ एमटीएस सुपर एमटीएस") - DLSTR ("टैरिफ सुपर") = 6

और फिर इस अंतर को उस स्ट्रिंग की लंबाई से विभाजित करें जिसे हम ढूंढ रहे थे:

6 / DLSTR ("एमटीएस") = 2

यह ठीक दो बार है कि "एमटीएस" लाइन को मूल में शामिल किया गया है।

यह इस एल्गोरिथ्म को सूत्रों की भाषा में लिखना बाकी है (आइए हम "पाठ" द्वारा उस पाठ को निरूपित करें जिसमें हम घटनाओं की तलाश कर रहे हैं, और "मांग" द्वारा - जिसकी संख्या में हम रुचि रखते हैं):

= (DLSTR (पाठ) -LSTR (विकल्प (ऊपरी (पाठ); ऊपरी (खोज), "")) / DLSTR (खोज)

हमारे उदाहरण में, सूत्र इस तरह दिखता है:

= (DLSTR (A2) -LSTR (विकल्प (UPPER (A2)), UPPER ($ E $ 1), ""))) / DLSTR ($ E $ 1)