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

रेनाट लाइफहैकर पर पहली बार अतिथि लेखक नहीं है। इससे पहले हमने प्रशिक्षण योजना बनाने के लिए उनसे उत्कृष्ट सामग्री प्रकाशित की: मूलभूत किताबें और ऑनलाइन संसाधन, साथ ही एक प्रशिक्षण योजना बनाने के लिए एक चरण-दर-चरण एल्गोरिदम। 

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

VLOOK और GPR के सरल विकल्प, यदि मांग किए गए मान तालिका के पहले कॉलम में नहीं हैं: VIEW, INDEX + MATCH

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

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

1. लुकअप फ़ंक्शन का उपयोग करें।
   इसमें निम्नलिखित वाक्यविन्यास है: देखें (lookup_value खोजें वेक्टर; परिणाम वेक्टर)। लेकिन इसके सही संचालन के लिए यह आवश्यक है कि सीमा के मूल्य खोजें वेक्टर आरोही क्रम में क्रमबद्ध किया गया था:
2. MATCH और INDEX फ़ंक्शंस के संयोजन का उपयोग करें।
   मैच समारोह सरणी में तत्व का क्रमांक देता है (इसकी मदद से आप एक पंक्ति में तालिका में इच्छित प्रविष्टि पा सकते हैं), और सूचकांक समारोह एक पूर्व निर्धारित संख्या के साथ सरणी तत्व (जो हम मैच के माध्यम से जानें) देता है।फंक्शन सिंटैक्स:
   • मिलान (lookup_value खोज सरणी; match_type) – हमारे मामले के लिए हमें “सटीक मिलान” मिलान करने के प्रकार की आवश्यकता है, यह संख्या 0 से मेल खाती है।
   • INDEX (सरणी; रेखा संख्या; [Column_num])। इस मामले में, कॉलम संख्या निर्दिष्ट नहीं की जानी चाहिए, क्योंकि सरणी में एक पंक्ति होती है।

सूची में खाली कोशिकाओं को जल्दी से कैसे भरें

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

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

सूत्र में त्रुटियों को कैसे ढूंढें

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

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

1. फ़ॉर्मूला बार में फॉर्मूला भाग की गणना करने के लिए, इस भाग का चयन करें और F9 दबाएं:
   
   इस उदाहरण में, खोज फ़ंक्शन में कोई समस्या थी – यह तर्कों से उलझन में था। यह याद रखना महत्वपूर्ण है कि यदि आप फ़ंक्शन के किसी हिस्से की गणना रद्द नहीं करते हैं और एंटर दबाते हैं, तो गणना की गई संख्या एक संख्या बनी रहेगी।
2. रिबन पर “फॉर्मूला” समूह में “फ़ॉर्मूला की गणना करें” बटन पर क्लिक करें:

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

यह निर्धारित करने के लिए कि सूत्र किस पर निर्भर करता है या इसका क्या अर्थ है

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

तीर प्रकट होते हैं, यह दर्शाते हैं कि गणना का परिणाम क्या निर्भर करता है।

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

उस पर क्लिक करके, हम देखेंगे कि प्रभावशाली कक्ष या श्रेणियां कहां स्थित हैं:

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

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

कई चादरों से सेल मानों की राशि (संख्या, औसत) कैसे प्राप्त करें

आइए मान लें कि आपके पास उसी प्रकार की कई चादरें हैं जिन्हें आप जोड़ना चाहते हैं, गिनती या प्रक्रिया को अलग-अलग करना चाहते हैं:

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

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

यह संबोधन स्थित चादरों के लिए काम करता है क्रमिक. वाक्यविन्यास है: = समारोह (first_list: last_list! सीमा से लिंक)।

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

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

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

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

परिणाम:

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

टेक्स्ट (मूल्य; प्रारूप)

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

स्वचालित आप अधिक जटिल ग्रंथ भी कर सकते हैं। मेरे व्यवहार में मैं लंबे समय से स्वचालित करने के लिए गया था, लेकिन प्रारूप में प्रबंधन खातों पर नियमित टिप्पणी “/ गिर गया था मुख्य रूप से FAKTORA1 XX में वृद्धि / कमी, वृद्धि FAKTORA2 YY में / कमी के कारण योजना पर XX की वृद्धि हुई … कारकों में से बदलते सूची के साथ»। आप अक्सर इस तरह की टिप्पणियां लिख सकते हैं और लेखन की प्रक्रिया algorithmization कर सकते हैं – मूल्य एक बार एक सूत्र या मैक्रो है कि आप काम के कम से कम हिस्से से बचा लेगा बनाने हैरान।

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

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

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

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

कई डेटा स्रोतों का एक समग्र निर्माण कैसे करें

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

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

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

जब आप उस पर क्लिक करते हैं, तो एक संवाद बॉक्स प्रकट होता है:

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

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

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

सारांश तालिका रिपोर्ट तैयार है। “पृष्ठ 1” फ़िल्टर में, यदि आवश्यक हो, तो आप केवल डेटा स्रोतों में से एक का चयन कर सकते हैं:

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

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

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

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

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

डीएलस्ट्रैस्ट (“एमटीएस सुपर एमटीएस टैरिफ”) – डीएलस्ट्रैस्ट (“सुपर टैरिफ”) = 6

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

6 / डीएलस्ट्रैस्ट (“एमटीएस”) = 2

मूल में एक बार “एमटीएस” लाइन शामिल है।

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

= (डीएलस्ट्रै (पाठ) -DLSTR (सबमिट (प्रमोशनिंग (पाठ); प्रस्ताव (मांग के लिए); “”))) / ДЛСТР (मांग के लिए)

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

(ए 2) – डीएलएसटी (समर्थन (वर्तमान (ए 2); प्रीक्रिप्शन ($ ई $ 1); “”))) / डीएलस्ट्रैस्ट ($ ई $ 1)