ITI COPA एडवांस्ड स्प्रेडशीट (MS Excel): सम्पूर्ण नोट्स

NCVT ITI COPA मॉड्यूल 3: स्प्रेडशीट एप्लिकेशन (MS Excel) के लिए आधिकारिक MockSensei अध्ययन गाइड में आपका स्वागत है।

MS Excel डेटा एंट्री और विश्लेषण की रीढ़ है। नवीनतम NSQF लेवल 3.5 सिलेबस के अनुसार, अब केवल बुनियादी डेटा एंट्री जानना पर्याप्त नहीं है। NCVT CBT परीक्षा अब एडवांस्ड एक्सेल कॉन्सेप्ट्स पर आपका परीक्षण करती है, जिसमें पिवट टेबल (Pivot Tables), मैक्रोज़ (Macros), कंडीशनल फॉर्मेटिंग, पावर क्वेरी (Power Query) और पावर बीआई (Power BI) शामिल हैं।

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

1. वर्कबुक, वर्कशीट और डेटा मैनेजमेंट (Workbooks & Worksheets)

डेटा की गणना करने से पहले, आपको यह जानना होगा कि एक्सेल फ़ाइल को कैसे नेविगेट और कॉन्फ़िगर किया जाए।

एक्सेल फाइल्स के बेसिक्स

• वर्कबुक (Workbook): संपूर्ण एक्सेल फ़ाइल (जिसे .xlsx के रूप में सहेजा जाता है)।
• वर्कशीट (Worksheet/Sheet): वर्कबुक के अंदर के व्यक्तिगत पृष्ठ। एक वर्कबुक में कई वर्कशीट हो सकती हैं।
• डेटा इम्पोर्ट करना (Import Data): एक्सेल प्लेन टेक्स्ट फाइल्स (.txt) या कॉमा सेपरेटेड वैल्यूज (.csv) फाइल्स से बाहरी डेटा इम्पोर्ट कर सकता है।

वर्कबुक के भीतर नेविगेट करना

• Search Data: एक बहुत बड़ी शीट में विशिष्ट डेटा खोजने के लिए Ctrl + F का उपयोग करें।
• Navigate to Named Cells/Ranges: आप Name Box (फ़ॉर्मूला बार के बगल में स्थित) का उपयोग करके सीधे किसी विशिष्ट क्षेत्र में जा सकते हैं।
• हाइपरलिंक (Hyperlinks): आप अन्य शीट, बाहरी फ़ाइलों या वेबसाइटों पर जाने के लिए लिंक (Ctrl + K) डाल सकते हैं।

व्यू और ऑप्शंस को कस्टमाइज़ करना

• Freeze Panes (फ्रीज़ पैन): बड़े डेटासेट को स्क्रॉल करते समय विशिष्ट पंक्तियों (जैसे हेडर पंक्ति) या स्तंभों को दृश्यमान (visible) रखता है।
• Change Window Views: आप Normal View, Page Break Preview और Page Layout view के बीच स्विच कर सकते हैं।
• Display Formulas: सेल के अंदर उनके गणना किए गए परिणामों के बजाय वास्तविक फ़ॉर्मूले देखने के लिए, Ctrl + ~ (Tilde) दबाएं।
• Modify Basic Workbook Properties: आप बेहतर संगठन के लिए फ़ाइल मेटाडेटा में शीर्षक (Title), लेखक (Author) या टैग (Tags) जोड़ सकते हैं।

कोलैबोरेशन के लिए कंटेंट कॉन्फ़िगर करना

• Set a Print Area: यह ठीक से हाइलाइट करता है कि किन सेल को प्रिंट किया जाना चाहिए, बाकी शीट को अनदेखा कर देता है।
• Alternative File Formats: आप वर्कबुक को .pdf (बिना संपादन के साझा करने के लिए), .csv (डेटाबेस इम्पोर्ट के लिए), या .xlsm (मैक्रो-सक्षम वर्कबुक) के रूप में सहेज सकते हैं।

2. सेल, रेंज और टेबल को फॉर्मेट करना (Format Cells & Ranges)

परीक्षा में डेटा की दृश्य प्रस्तुति (Visual presentation) का अत्यधिक परीक्षण किया जाता है।

डेटा सेल में हेरफेर (Manipulating Data Cells)

• ऑटो फिल (Auto Fill): यह समय बचाता है। किसी सेल के निचले-दाएं कोने (फ़िल हैंडल) को खींचकर, एक्सेल स्वचालित रूप से अनुक्रम (sequences) भर देता है (जैसे Monday, Tuesday, Wednesday... या 1, 2, 3...)।
• Paste Special (पेस्ट स्पेशल): केवल मानक टेक्स्ट पेस्ट करने के बजाय, यह आपको केवल फ़ॉर्मूले, केवल वैल्यूज पेस्ट करने, या पंक्तियों और स्तंभों को ट्रांसपोज़ (बदलने) की अनुमति देता है।
• Insert/Delete Cells: आप नई पंक्तियाँ या स्तंभ सम्मिलित कर सकते हैं और मौजूदा डेटा को ऊपर, नीचे, बाएँ या दाएँ खिसका सकते हैं।

सेल फॉर्मेटिंग (Formatting Cells)

• Merge and Unmerge Cells: दो या दो से अधिक आसन्न सेल को एक बड़े सेल में मिलाना (आमतौर पर मुख्य शीर्षकों के लिए उपयोग किया जाता है)।
• Wrap Text (रैप टेक्स्ट): लंबे टेक्स्ट को अगले कॉलम में जाने के बजाय, उसी सेल के भीतर कई लाइनें बनाकर एक ही सेल के अंदर फिट करने के लिए मजबूर करता है।
• Cell Alignment & Orientation: आप टेक्स्ट को क्षैतिज (Left, Center, Right), लंबवत (Top, Middle, Bottom) संरेखित कर सकते हैं, या इसके कोण (angle) को बदल सकते हैं।
• Number Formats: संख्याओं का मान बदले बिना उनके प्रदर्शित होने का तरीका बदलना (उदा., Currency ₹500.00, Percentages 50%, या Dates 12-May-2024)।
• Clear Formatting: सभी रंग, बॉर्डर और बोल्ड टेक्स्ट को हटा देता है, सेल को डिफ़ॉल्ट पर लौटा देता है।

एक्सेल टेबल (Excel Tables - Structured Data)

सेल की एक सामान्य सीमा को आधिकारिक Excel Table (Ctrl + T) में बदलने से शक्तिशाली सुविधाएँ अनलॉक होती हैं।

• Apply Table Styles: पंक्तियों को जल्दी से रंग-समन्वित (color-coordinate) करें।
• Insert Total Row: स्वचालित रूप से नीचे एक पंक्ति जोड़ता है जो बिना फ़ॉर्मूले टाइप किए योग (sum), औसत या गिनती की गणना कर सकती है।
• Convert to Range: एक एक्सेल टेबल को वापस सामान्य, अनलिंक्ड सेल में बदल देता है।

3. फ़ॉर्मूले, फ़ंक्शन्स और सेल रेफरेंसिंग (Formulas & Functions)

यह COPA एक्सेल सिलेबस का सबसे महत्वपूर्ण हिस्सा है। आपको यह समझना चाहिए कि रेफरेंस (references) कैसे काम करते हैं और इन विशिष्ट कार्यों को याद रखना चाहिए।

सेल रेफरेंसिंग के प्रकार (Highly Tested)

जब आप किसी फ़ॉर्मूले को एक सेल से दूसरे सेल में कॉपी करते हैं, तो एक्सेल सेल एड्रेस बदल देता है। रेफरेंसिंग इस व्यवहार को नियंत्रित करती है।

• Relative Reference (उदा., A1): जब आप फ़ॉर्मूले को नीचे या आर-पार कॉपी करते हैं तो सेल एड्रेस स्वचालित रूप से बदल जाता है।
• Absolute Reference (उदा., $A$1): सेल एड्रेस "लॉक" हो जाता है। आप फ़ॉर्मूले को कहीं भी कॉपी करें, यह हमेशा सटीक सेल A1 को ही इंगित करेगा। (डॉलर चिह्न जोड़ने के लिए F4 का उपयोग करें)।
• Mixed Reference (उदा., $A1 या A$1): यह या तो कॉलम या पंक्ति को लॉक करता है, लेकिन दोनों को नहीं।

नेम्ड रेंज (Defining Named Ranges)

सेल को B2:B50 के रूप में संदर्भित करने के बजाय, आप उन्हें हाइलाइट कर सकते हैं और उन्हें "TotalSales" नाम दे सकते हैं। फिर आप =SUM(TotalSales) जैसा फ़ॉर्मूला लिख सकते हैं। इससे फ़ॉर्मूले पढ़ना बहुत आसान हो जाता है।

गणितीय और सांख्यिकीय कार्य (Math & Stat Functions)

• SUM(): एक सीमा (range) में सभी संख्याओं को जोड़ता है।
• AVERAGE(): एक सीमा का गणितीय औसत निकालता है।
• MIN() & MAX(): क्रमशः एक सीमा में सबसे छोटी और सबसे बड़ी संख्या खोजता है।
• COUNT(): गिनता है कि कितने सेल में संख्याएँ (numbers) हैं। (यह टेक्स्ट और खाली सेल को अनदेखा करता है)।
• COUNTIF(): यह सेल की गिनती केवल तभी करता है जब वे एक विशिष्ट शर्त को पूरा करते हैं (उदा., =COUNTIF(A1:A10, ">50"))।
• COUNTBLANK(): एक सीमा में पूरी तरह से खाली (empty) सेल की संख्या गिनता है।

तार्किक कार्य (Logical Functions)

• IF(): यह सशर्त (conditional) संचालन करता है। यह एक लॉजिकल टेस्ट की जांच करता है और TRUE होने पर एक मान (value) देता है, और FALSE होने पर दूसरा मान देता है।
  • Syntax: =IF(Logical_Test, Value_if_True, Value_if_False)
  • Example: =IF(A1>33, "Pass", "Fail")

टेक्स्ट फॉर्मेटिंग कार्य (Text Formatting Functions)

एक्सेल सिर्फ संख्याओं के लिए नहीं है। यह टेक्स्ट में भी हेरफेर कर सकता है।

• UPPER(): सभी टेक्स्ट को UPPERCASE (बड़े अक्षरों) में बदलता है।
• LOWER(): सभी टेक्स्ट को lowercase (छोटे अक्षरों) में बदलता है।
• LEN(): रिक्त स्थान सहित किसी सेल में वर्णों (characters) की कुल संख्या (लंबाई) देता है।
• RIGHT() & LEFT(): टेक्स्ट स्ट्रिंग के दाएँ या बाएँ तरफ से वर्णों की एक विशिष्ट संख्या निकालता है।
• MID(): एक विशिष्ट स्थान से शुरू होकर, टेक्स्ट स्ट्रिंग के मध्य से वर्ण निकालता है।
• CONCAT() & TEXTJOIN(): कई अलग-अलग सेल के टेक्स्ट को एक सिंगल सेल में जोड़ता है।

लुकअप और डेट फ़ंक्शन्स (Advanced)

• Lookup Functions: VLOOKUP (Vertical Lookup) एक तालिका के पहले कॉलम में एक मान खोजता है और दूसरे कॉलम से उसी पंक्ति में एक मान लौटाता है।
• Advanced Date Functions: TODAY() (वर्तमान तिथि लौटाता है), NOW() (दिनांक और समय लौटाता है), और DATEDIF() (दो तिथियों के बीच के अंतर की गणना करता है) जैसे कार्य।

4. सॉर्टिंग, फ़िल्टरिंग और डेटा वैलिडेशन (Sorting & Filtering)

बड़ी सूचियों (जैसे कर्मचारी डेटाबेस) के साथ काम करते समय, आपको डेटा को व्यवस्थित करना चाहिए।

• Sort Data (डेटा सॉर्ट करें): आप नामों को वर्णानुक्रम (A से Z) या संख्याओं (सबसे छोटे से सबसे बड़े) में सॉर्ट कर सकते हैं।
• Sort by Multiple Columns: उदाहरण के लिए, पहले "Department" (A-Z) द्वारा सॉर्ट करना, और फिर "Salary" (सबसे बड़े से सबसे छोटे) द्वारा सॉर्ट करना।
• Filter Records (फ़िल्टर रिकॉर्ड): उन पंक्तियों को छुपाता है जिन्हें आप नहीं देखना चाहते हैं। उदाहरण के लिए, केवल उन छात्रों को दिखाने के लिए सूची को फ़िल्टर करना जिन्होंने 80% से अधिक अंक प्राप्त किए हैं।
• Data Validation (डेटा वैलिडेशन): यह प्रतिबंधित करता है कि उपयोगकर्ता सेल में क्या टाइप कर सकता है। उदाहरण के लिए, आप एक ड्रॉप-डाउन मेनू बना सकते हैं, या किसी सेल को केवल 1 और 100 के बीच की संख्या स्वीकार करने के लिए प्रतिबंधित कर सकते हैं। यह डेटा प्रविष्टि (data entry) त्रुटियों को रोकता है।

5. डेटा विज़ुअलाइज़ेशन: कंडीशनल फॉर्मेटिंग और स्पार्कलाइन्स

• Conditional Formatting: यह किसी सेल के मान (value) के आधार पर स्वचालित रूप से सेल के स्वरूपण (रंग, पृष्ठभूमि, आइकन) को बदल देता है। उदाहरण के लिए, ₹10,000 से कम की सभी बिक्री को स्वचालित रूप से लाल कर देना।
• Sparklines (स्पार्कलाइन्स): छोटे, लघु चार्ट जो एक सेल के अंदर फिट होते हैं। वे पूर्ण चार्ट का स्थान लिए बिना डेटा को दृष्टिगत रूप से सारांशित करते हैं।

6. चार्ट और एडवांस्ड ग्राफ्स मैनेज करना

चार्ट कच्चे नंबरों को समझने योग्य ग्राफिक्स में बदल देते हैं।

• Create Charts: बार चार्ट, पाई चार्ट, लाइन ग्राफ, आदि।
• Chart Sheets: एक नियमित वर्कशीट से चार्ट को उसकी अपनी समर्पित, पूर्ण-स्क्रीन "चार्ट शीट" में ले जाना।
• Modify Charts: आप नई डेटा श्रृंखला (Data Series) जोड़ सकते हैं, पंक्तियों और स्तंभों को स्विच कर सकते हैं (X और Y अक्ष को बदलना), और शीर्षक तथा डेटा लेबल जैसे चार्ट तत्व जोड़ सकते हैं।
• Add Trend Lines (ट्रेंड लाइन जोड़ना): समय के साथ डेटा की सामान्य दिशा या प्रवृत्ति दिखाने के लिए चार्ट में एक रेखा जोड़ी जाती है (पूर्वानुमान के लिए उपयोगी)।
• Alternative Text for Charts: MS Word की तरह ही, चार्ट में Alt Text जोड़ने से यह सुनिश्चित होता है कि दृष्टिबाधित उपयोगकर्ता स्क्रीन रीडर के माध्यम से चार्ट को समझ सकें।

7. पिवट टेबल और पिवट चार्ट (Pivot Tables - Highly Tested)

• Create Pivot Tables: एक सेल रेंज चुनें और पिवट टेबल डालें।
• Manipulate Fields: आप कॉलम हेडर को चार क्षेत्रों में ड्रैग और ड्रॉप करते हैं: Filters, Columns, Rows, और Values।
• Represent Data: डिफ़ॉल्ट रूप से, पिवट टेबल डेटा का योग (sum) करती है। हालाँकि, आप Count, Average, या % of row/column दिखाने के लिए Value Field Settings बदल सकते हैं।
• Group Data: आप समग्र रिपोर्ट (aggregate reports) बनाने के लिए तिथियों को महीने या तिमाहियों में समूहित कर सकते हैं, या संख्याओं को श्रेणियों में समूहित कर सकते हैं (उदा., आयु 20-29, 30-39)।
• Pivot Charts: एक चार्ट जो सीधे पिवट टेबल से जुड़ा होता है। यदि आप पिवट टेबल को फ़िल्टर करते हैं, तो पिवट चार्ट तुरंत अपडेट हो जाता है।

8. एक्सेल मैक्रोज़ और फॉर्म कंट्रोल्स (Macros & Form Controls)

स्वचालन (Automation) एडवांस्ड एक्सेल का एक महत्वपूर्ण हिस्सा है।

• Macros (मैक्रोज़): मैक्रो क्लिक, कीस्ट्रोक्स और क्रियाओं का एक रिकॉर्ड किया गया क्रम (sequence) है। यदि आप हर दिन एक ही फॉर्मेटिंग कार्य करते हैं, तो आप एक बार मैक्रो रिकॉर्ड कर सकते हैं, और कार्य को स्वचालित करने के लिए इसे बाद में चला सकते हैं।
• Form Controls: बटन, चेकबॉक्स और स्क्रॉल बार जिन्हें आप एक्सेल शीट में डाल सकते हैं। इनका उपयोग उपयोगकर्ता के अनुकूल Data Entry Forms बनाने के लिए किया जाता है, जिससे एक्सेल एक कस्टम सॉफ्टवेयर एप्लिकेशन की तरह दिखता है।

9. डेटा एनालिसिस: पावर क्वेरी और पावर बीआई

NSQF लेवल 3.5 सिलेबस आधुनिक Microsoft डेटा टूल का परिचय देता है।

• Power Query (पावर क्वेरी): एक उन्नत डेटा कनेक्शन तकनीक। यह आपको विभिन्न स्रोतों (वेबसाइटों, डेटाबेस, एकाधिक एक्सेल फाइलों) से डेटा आयात करने, डेटा को साफ करने (रिक्त स्थान हटाना, कॉलम प्रारूपित करना), और प्रश्नों को एक साफ तालिका में संयोजित/मर्ज करने की अनुमति देता है।
• Power BI (पावर बीआई): जबकि एक्सेल स्प्रेडशीट के लिए है, पावर बीआई इंटरैक्टिव डेटा विज़ुअलाइज़ेशन के लिए Microsoft का समर्पित सॉफ़्टवेयर है। सिलेबस आपसे उम्मीद करता है कि आप सरल डेटा विज़ुअलाइज़ेशन बनाने के लिए पावर बीआई का उपयोग करना जानते हों।
• Dashboarding (डैशबोर्डिंग): व्यावसायिक रिपोर्टिंग के लिए एक इंटरैक्टिव "डैशबोर्ड" बनाने के लिए एक ही स्क्रीन पर उन्नत चार्ट, पिवट टेबल, फॉर्म कंट्रोल और कंडीशनल फॉर्मेटिंग का संयोजन।

10. सुरक्षा: एक्सेल प्रोटेक्शन (Security)

• Workbook Protection: आप वर्कबुक की संरचना को लॉक कर सकते हैं (ताकि उपयोगकर्ता शीट जोड़, हटा या छिपा न सकें)।
• Worksheet Protection: आप विशिष्ट सेल को लॉक कर सकते हैं ताकि उन्हें संपादित न किया जा सके, जबकि डेटा-एंट्री सेल को अनलॉक रखा जा सके। इसके लिए आमतौर पर एक पासवर्ड सेट करने की आवश्यकता होती है।

Time to Prove Your Knowledge (अपने ज्ञान को साबित करें)

आपने सम्पूर्ण एडवांस्ड MS Excel सिलेबस की समीक्षा कर ली है। NCVT परीक्षा विशिष्ट फ़ॉर्मूला सिंटैक्स, रेफरेंसिंग नियमों और पिवट टेबल कार्यक्षमता पर आपकी स्मृति का परीक्षण करेगी।

क्या आप COUNT() और COUNTBLANK() के बीच का अंतर जानते हैं? क्या आप जानते हैं कि कौन सी कुंजी एब्सोल्यूट रेफरेंसिंग (Absolute Referencing) को टॉगल करती है?

एक बार जब आप MS Excel में महारत हासिल कर लेते हैं और मॉक टेस्ट में 80% से ऊपर स्कोर कर लेते हैं, तो मॉड्यूल 4: प्रेजेंटेशन (MS PowerPoint) पर आगे बढ़ें।