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 अंक का ही हो)।

★ Ultimate MS Excel Shortcuts

F2 Edit Active Cell (एडिट करने के लिए)
F4 Repeat Action / Toggle Reference ($)
F11 Create Chart (नया चार्ट बनाना)