MS Excel: स्प्रेडशीट मास्टर

भाग-3: फॉर्मूला, फंक्शन, चार्ट और डेटा विश्लेषण

UPSC, UPSSSC, Bank & Technical Exams Special

1. परिचय और संरचना (Introduction & Limits)

MS Excel एक 'स्प्रेडशीट प्रोग्राम' है। इसका उपयोग डेटा को व्यवस्थित करने, गणना करने और विश्लेषण करने के लिए होता है।

Extensions: .xls (2003 तक), .xlsx (2007 से अब तक), .xlsm (Macro enabled).
Workbook vs Worksheet: एक्सेल फाइल को 'Workbook' कहते हैं। इसमें कई पेज होते हैं जिन्हें 'Worksheet' कहते हैं।
Default Sheets: पुराने वर्ज़न (2007/2010) में डिफ़ॉल्ट 3 शीट खुलती थीं, अब (2016/2019/365) में 1 शीट खुलती है।

📊 एक्सेल की सीमाएं (Limits - Exam Data)

फीचर	Old (2003)	New (2007 - 365)
Total Rows	65,536	10,48,576 (10 लाख+)
Total Columns	256 (IV)	16,384 (Last: XFD)
Column Width	255 characters	255 characters
Zoom Limit	10% - 400%	10% - 400% (Word में 500% होता है)

2. इंटरफेस शब्दावली

Formula Bar

रिबन के ठीक नीचे। यह एक्टिव सेल का कंटेंट और फॉर्मूला दिखाता है।
Shortcut to expand: Ctrl + Shift + U

Name Box

फॉर्मूला बार के बाईं ओर। यह एक्टिव सेल का एड्रेस (जैसे A1) दिखाता है।

Active Cell

जिस सेल पर अभी क्लिक किया गया है (मोटा हरा बॉर्डर)।

Range

सेल्स का समूह। इसे कोलन (:) से दर्शाते हैं। उदा: A1:A10

3. सेल रेफरेंसिंग (Most Important Concept)

जब हम किसी फॉर्मूले को कॉपी करके दूसरी जगह पेस्ट करते हैं, तो सेल एड्रेस कैसे बदलेगा, यह 'रेफरेंसिंग' तय करती है।
$ (Dollar Sign) का मतलब है 'लॉक' करना।

प्रकार	उदाहरण	विवरण (Description)
Relative	A1	डिफ़ॉल्ट। जब फॉर्मूला कॉपी होता है, तो एड्रेस बदल जाता है। (A1 → A2 → A3).
Absolute	$A$1	पूर्णतः लॉक। फॉर्मूला कहीं भी कॉपी करो, यह हमेशा A1 ही रहेगा। (Row और Column दोनों लॉक)।
Mixed	$A1 या A$1	एक लॉक, एक खुला। • $A1: कॉलम लॉक (Row बदलेगी)। • A$1: रो लॉक (कॉलम बदलेगा)।

Tip: रेफरेंस बदलने के लिए F4 की (Key) दबाएं।

4. फॉर्मूला और फंक्शन (50+ List)

नियम: एक्सेल में हर फॉर्मूला '=' (बराबर) चिन्ह से शुरू होना चाहिए।

A. गणितीय फंक्शन (Math Functions)

=SUM(A1:A10) : जोड़ (Total)।
=PRODUCT(5, 4) : गुणा (Multiply)। Output: 20
=MOD(10, 3) : शेषफल (Remainder)। Output: 1
=POWER(2, 3) : घात (2³)। Output: 8
=SQRT(16) : वर्गमूल (Square Root)। Output: 4
=ROUND(12.567, 1) : राउंड ऑफ। Output: 12.6
=INT(12.9) : पूर्णांक (दशमलव हटाता है)। Output: 12
=ABS(-50) : निरपेक्ष मान (Negative को Positive बनाता है)। Output: 50
=FACT(5) : फैक्टोरियल (5x4x3x2x1)। Output: 120

B. सांख्यिकीय फंक्शन (Statistical Functions)

=AVERAGE(10, 20, 30) : औसत। Output: 20
=MAX(A1:A10) : सबसे बड़ी संख्या।
=MIN(A1:A10) : सबसे छोटी संख्या।
=COUNT(A1:A10) : केवल नंबर वाले सेल गिनता है।
=COUNTA(A1:A10) : खाली सेल छोड़कर सब कुछ गिनता है (Text + Number)।
=COUNTBLANK(A1:A10) : केवल खाली (Empty) सेल गिनता है।
=COUNTIF(A1:A10, ">50") : शर्त के आधार पर गिनना (50 से बड़े कितने हैं)।

C. टेक्स्ट फंक्शन (Text Functions)

=LEN("Sudhir") : अक्षरों की लंबाई (Length)। Output: 6
=LOWER("HELLO") : छोटा (Small letter) करता है। Output: hello
=UPPER("india") : बड़ा (Capital letter) करता है। Output: INDIA
=PROPER("mr sudhir") : पहला अक्षर बड़ा। Output: Mr Sudhir
=TRIM(" Hello ") : फालतू स्पेस हटाता है। Output: "Hello"
=CONCATENATE("Ram", "Kumar") : दो शब्दों को जोड़ना। (Short: =A1 & B1)
=LEFT("Excel", 2) : बाईं ओर से 2 अक्षर। Output: Ex
=RIGHT("Excel", 2) : दाईं ओर से 2 अक्षर। Output: el

D. लॉजिकल और लुकअप (Logical & Lookup)

=IF(A1>=33, "Pass", "Fail") : यदि A1 में 33 या ज्यादा है तो Pass, वरना Fail।
=AND(Cond1, Cond2) : जब दोनों शर्तें सही हों, तब TRUE।
=OR(Cond1, Cond2) : जब कोई एक शर्त सही हो, तब TRUE।
VLOOKUP (Vertical Lookup): किसी बड़े डेटाबेस में पहले कॉलम के आधार पर डेटा खोजना।
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

E. डेट और टाइम (Date & Time)

=TODAY() : आज की तारीख। (शॉर्टकट: Ctrl + ;)
=NOW() : तारीख और समय दोनों।
=DAY(A1), =MONTH(A1), =YEAR(A1) : तारीख से दिन, महीना, साल निकालना।

5. चार्ट और डेटा टूल्स

Charts (F11 to insert)

Column/Bar Chart: तुलना (Comparison) के लिए।
Pie Chart: 100% में हिस्सेदारी दिखाने के लिए (गोल)। केवल एक सीरीज़ का डेटा दिखाता है।
Line Chart: समय के साथ बदलाव (Trend) दिखाने के लिए (जैसे शेयर मार्केट, तापमान)।
Scatter Chart: दो वैरिएबल के बीच संबंध (Correlation)।

Data Tools

Sort: डेटा को क्रम में लगाना (A-Z या Z-A)।
Filter (Ctrl+Shift+L): बड़े डेटा में से काम का डेटा छांटना।
Pivot Table: बड़े और जटिल डेटा का सारांश (Summary) निकालने के लिए सबसे शक्तिशाली टूल।
Data Validation: सेल में गलत डेटा डालने से रोकना (जैसे मोबाइल नंबर 10 अंक का ही हो)।

7. एक्सेल एरर (Errors in Excel)

फॉर्मूला लगाते समय आने वाली सामान्य गलतियाँ:

Error	कारण (Reason)
#####	कॉलम की चौड़ाई कम है (डेटा फिट नहीं हो रहा)।
#DIV/0!	किसी संख्या को 0 (शून्य) से भाग देने पर।
#NAME?	फॉर्मूला की स्पेलिंग गलत होने पर (जैसे =SUM की जगह =SU)।
#VALUE!	गलत डेटा टाइप (जैसे नंबर के साथ टेक्स्ट जोड़ना: 5 + A)।
#REF!	सेल रेफरेंस गायब हो गया हो (डिलीट हो गया हो)।
#N/A	वैल्यू उपलब्ध नहीं है (Not Available - VLOOKUP में आता है)।

★ Ultimate MS Excel Shortcuts

F2 Edit Active Cell (एडिट करने के लिए)

F4 Repeat Action / Toggle Reference ($)

F11 Create Chart (नया चार्ट बनाना)

F12 Save As

Ctrl + ; Insert Current Date (तारीख)

Ctrl + Shift + : Insert Current Time (समय)

Ctrl + Space Select Entire Column

Shift + Space Select Entire Row

Ctrl + 9 Hide Row

Ctrl + 0 Hide Column

Ctrl + PgDn Next Worksheet

Ctrl + PgUp Previous Worksheet

Alt + = AutoSum (स्वतः जोड़)

Ctrl + ` Show Formulas (टिल्ड बटन)

Ctrl + 1 Format Cells Dialog Box

Ctrl + D Fill Down (ऊपर का डेटा नीचे भरना)

Ctrl + R Fill Right

Ctrl + Arrow Keys डेटा के अंतिम छोर तक जाना

🎯 परीक्षा विशेषांक (Exam Capsules)

Q. स्प्रेडशीट में 'सेल' (Cell) क्या है?
A. रो (Row) और कॉलम (Column) का मिलन बिंदु।

Q. Excel 2019 में अंतिम कॉलम का नाम क्या है?
A. XFD (16,384 वां कॉलम)।

Q. किसी सेल को एडिट करने की शॉर्टकट की?
A. F2

Q. 'By Default' कितनी शीट खुलती हैं?
A. नए वर्ज़न (2016+) में 1, पुराने (2010) में 3.

Q. पूरी रो (Row) को सेलेक्ट करने का शॉर्टकट?
A. Shift + Spacebar

Q. #DIV/0! एरर कब आता है?
A. जब किसी संख्या को शून्य (0) से भाग दिया जाता है।

Q. VLOOKUP में 'V' का अर्थ क्या है?
A. Vertical (लंबवत)।

Q. मैक्रो (Macro) इनेबल्ड फाइल का एक्सटेंशन?
A. .xlsm

Q. आज की तारीख डालने का शॉर्टकट?
A. Ctrl + ; (Semi-colon)

Q. चार्ट बनाने के लिए फंक्शन की?
A. F11