समझिए
1. किसी भी Excel सवाल का जवाब कैसे दें
परिभाषा → कब इस्तेमाल → छोटा उदाहरण → एक जाल। उदाहरण: "XLOOKUP एक कॉलम में वैल्यू ढूँढकर दूसरे से लौटाता है। मैं इसे कीमत lookup के लिए इस्तेमाल करता हूँ। =XLOOKUP(A2, Codes, Prices, "Not found")। VLOOKUP के उलट यह डिफ़ॉल्ट में exact match करता है और बाईं ओर भी देख सकता है।"
A. बुनियाद (1–10)
- Workbook बनाम worksheet? Workbook फ़ाइल है; worksheets उसके अंदर के टैब।
- Relative, absolute, mixed references?
A1कॉपी करने पर बदलता है,$A$1कभी नहीं,$A1/A$1सिर्फ़ कॉलम/रो लॉक करते हैं। F4 इनमें घुमाता है। - Formula बनाम function?
=से शुरू होने वाली कोई भी चीज़ formula है; function SUM जैसा बिल्ट-इन है जो formula के अंदर इस्तेमाल होता है। - अधिकतम रो और कॉलम? 1,048,576 रो और 16,384 कॉलम (A से XFD)।
- Excel Table (Ctrl + T) क्यों? अपने आप बढ़ती है, पढ़ने लायक structured references, हेडर दिखते रहते हैं, और pivots/Power Query को भरोसे से डेटा देती है।
- Excel तारीख़ें कैसे रखता है? Serial numbers के रूप में (1 = 1-Jan-1900); समय दशमलव हिस्सा है। इसीलिए तारीख़ें जोड़ी-घटाई जा सकती हैं।
- Paste Special के इस्तेमाल? सिर्फ़ values, formats, transpose, skip blanks, और operations (जैसे टेक्स्ट नंबर को नंबर बनाने के लिए 1 से गुणा)।
- Freeze Panes? View → Freeze Panes स्क्रॉल करते समय हेडर रो/कॉलम दिखते रखता है।
- Data Validation? टाइप होने वाली चीज़ सीमित करता है — ड्रॉपडाउन, नंबर रेंज, तारीख़ें — ताकि एंट्री पर ही गलत डेटा रुके।
- Conditional formatting का उदाहरण? बकाया इनवॉइस, डुप्लिकेट, टॉप 10 हाइलाइट करना, या status के लिए data bars और icon sets।
B. Formulas और lookups (11–24)
- VLOOKUP की सीमाएँ? Lookup वैल्यू पहले कॉलम में होनी चाहिए, बाईं ओर नहीं देख सकता, कॉलम जोड़ने पर कॉलम नंबर टूटता है, और डिफ़ॉल्ट approximate match है — exact match के लिए
FALSE/0 लगाएँ। - INDEX + MATCH क्यों? बाईं ओर देख सकता है, कॉलम खिसकने पर नहीं टूटता, और पुराने Excel में चलता है।
- XLOOKUP के फ़ायदे? डिफ़ॉल्ट exact match, बाएँ या दाएँ देखता है, बिल्ट-इन "if not found", नीचे से खोज सकता है, कई कॉलम लौटाता है।
- Approximate match कब काम का? स्लैब — टैक्स, कमीशन, ग्रेड — जब lookup टेबल निचली सीमा से बढ़ते क्रम में सॉर्ट हो।
- SUMIF बनाम SUMIFS? एक शर्त बनाम कई। SUMIFS में sum range पहले आती है।
- COUNT बनाम COUNTA बनाम COUNTBLANK? नंबर गिनता है / खाली नहीं वाले सेल / खाली सेल।
- IFERROR बनाम IFNA? IFERROR हर एरर छिपाता है (असली बग भी छिप सकते हैं); IFNA सिर्फ़ #N/A — lookups के आसपास ज़्यादा सुरक्षित।
- आम एरर वैल्यू? #N/A नहीं मिला · #VALUE! गलत टाइप · #REF! हटा रेफ़रेंस · #DIV/0! · #NAME? गलत लिखा फ़ंक्शन या नाम · #SPILL! spill रेंज अटकी · #NUM! अमान्य नंबर।
- Nested IF बनाम IFS बनाम SWITCH? IFS बिना nesting के शर्त/नतीजे के जोड़े लिखता है; SWITCH एक वैल्यू को सटीक विकल्पों से मिलाता है; nested IF हर जगह चलता है पर पढ़ना मुश्किल हो जाता है।
- TEXT क्या करता है? नंबर या तारीख़ को टेक्स्ट के रूप में फ़ॉर्मेट करता है, जैसे
=TEXT(A2,"dd-mmm-yyyy")। नतीजा टेक्स्ट है, इसलिए आगे गणित में इस्तेमाल न करें। - पहला नाम निकालें?
=LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ") - 1)या Microsoft 365 में=TEXTBEFORE(TRIM(A2)&" ", " ")। एक बार के काम के लिए Flash Fill (Ctrl + E)। - दो तारीख़ों का अंतर? दिन:
=B2-A2। पूरे साल:=DATEDIF(A2,B2,"y")। कार्य-दिवस:NETWORKDAYS। - SUMPRODUCT किसलिए? Arrays को गुणा करके नतीजे जोड़ना — जैसे कुल वैल्यू
=SUMPRODUCT(Qty, Price)— और पुराने Excel में कई शर्तों वाली गिनती। - Dynamic arrays क्या हैं? FILTER, UNIQUE, SORT जैसे फ़ॉर्मूले जो कई नतीजे लौटाते हैं और बगल के सेल में "spill" होते हैं; पूरे नतीजे को
A2#से रेफ़र करें।
C. डेटा सफ़ाई (25–32)
- डुप्लिकेट हटाएँ? key कॉलम पर Data → Remove Duplicates (पहले COUNTIF > 1 से जाँचें), या दोहराने लायक सफ़ाई के लिए UNIQUE / Power Query।
- टेक्स्ट में रखे नंबर? हरे त्रिकोण वाला "Convert to Number",
VALUE, 1 से गुणा, या Text to Columns → Finish। - अतिरिक्त स्पेस?
TRIMअतिरिक्त स्पेस हटाता है;CLEANन छपने वाले अक्षर हटाता है; वेब डेटा मेंSUBSTITUTE(A2, CHAR(160), " ")चाहिए हो सकता है। - कॉलम बाँटना? Text to Columns, Flash Fill,
TEXTSPLIT, या Power Query का Split Column। - गलत इम्पोर्ट हुई तारीख़ें (dd/mm बनाम mm/dd)? DMY फ़ॉर्मेट के साथ Text to Columns, या Power Query में "Change type using locale" English (India)।
- बिना हटाए डुप्लिकेट ढूँढें? Conditional Formatting → Duplicate Values, या helper
=COUNTIF($A$2:$A$500, A2) > 1। - खाली सेल ऊपर वाली वैल्यू से भरें? रेंज चुनें → Go To Special → Blanks →
=और ऊपर वाला सेल टाइप करें → Ctrl + Enter → फिर values के रूप में पेस्ट। - 12 मासिक फ़ाइलें जोड़ें? Power Query → From Folder → Combine; अगले महीने बस refresh।
D. Pivots और charts (33–40)
- Pivot table क्या है? फ़ील्ड को Rows, Columns, Values और Filters में खींचकर बड़े डेटा का सारांश बनाने का टूल — फ़ॉर्मूलों की ज़रूरत नहीं।
- Pivot नया डेटा नहीं दिखाता? Refresh (Alt + F5); स्रोत फ़िक्स रेंज हो तो Excel Table में बदलें ताकि बढ़े।
- Pivot में तारीख़ें महीने से ग्रुप करें? तारीख़ पर राइट-क्लिक → Group → Months (और Years)। "Cannot group" का मतलब तारीख़ कॉलम में खाली या टेक्स्ट।
- % of total दिखाएँ? Value Field Settings → Show Values As → % of Grand Total (या % of Parent, Running Total, Rank…)।
- Calculated field का जाल? यह रो पर नहीं, sums पर चलता है — Profit/Sales जैसे अनुपात के लिए ठीक, कमीशन स्लैब जैसे रो-स्तर लॉजिक के लिए गलत।
- Slicer बनाम filter? Slicers दिखने वाले बटन हैं, चुनाव दिखाते हैं, और Report Connections से कई pivots कंट्रोल कर सकते हैं।
- कौन-सा चार्ट कब? Trend → line · तुलना → bar/column · पूरे का हिस्सा (2–4 हिस्से) → stacked bar/pie · फैलाव → histogram · संबंध → scatter।
- Secondary axis कब? सिर्फ़ जब दो series की इकाइयाँ अलग हों (₹ और %)। एक इकाई वाली दो series के लिए कभी नहीं।
E. Power tools (41–46)
- Power Query क्या है? रिकॉर्ड होने वाला, दोहराने लायक डेटा-सफ़ाई टूल: import, transform, load — अगली बार refresh।
- Merge बनाम Append? Merge key पर टेबल अगल-बगल जोड़ता है (XLOOKUP जैसा); Append एक जैसे कॉलम वाली टेबल ऊपर-नीचे रखता है।
- Unpivot क्या है? महीने वाले कॉलम को रो (Region, Month, Value) में बदलना ताकि डेटा विश्लेषण लायक बने।
- Data Model / Power Pivot क्या है? बिल्ट-इन डेटाबेस जो कई जुड़ी टेबल (लाखों रो) रखता है और DAX measures लिखने देता है।
- Measure बनाम calculated column? Column हर रो के लिए गणना होकर सेव होता है; measure फ़िल्टर के आधार पर हर pivot सेल के लिए गणना होता है — टोटल और अनुपात के लिए measures।
- Excel या Power BI? लचीले विश्लेषण, मॉडल और छोटी टीम की रिपोर्ट के लिए Excel; बड़े डेटा, क्लाउड में शेड्यूल refresh और कई यूज़र्स से डैशबोर्ड शेयर करने के लिए Power BI।
F. Automation और scenarios (47–50)
- Macro क्या है? रिकॉर्ड या लिखा VBA कोड जो स्टेप दोहराता है; .xlsm में सेव करें; सिर्फ़ भरोसेमंद स्रोत से चालू करें।
- वर्कबुक धीमी है — क्या जाँचेंगे? Volatile functions (OFFSET, INDIRECT, TODAY), array फ़ॉर्मूलों में पूरे कॉलम के रेफ़रेंस, बार-बार lookups, बहुत conditional formats, calculation mode; भारी डेटा Power Query/Data Model में ले जाएँ।
- Goal Seek बनाम Data Table बनाम Scenario Manager? Goal Seek वह एक input ढूँढता है जो target नतीजा दे; Data Table कई input वैल्यू के नतीजे दिखाता है; Scenario Manager input के नाम वाले सेट सेव करता है।
- दो लिस्ट का मिलान (जैसे bank बनाम books) कैसे करेंगे? मेल खाती key बनाएँ, दोनों तरफ़ XLOOKUP/COUNTIF (या Left Anti के साथ Power Query merge) से हर तरफ़ छूटे आइटम ढूँढें, फिर मिले आइटम में रकम का अंतर जाँचें।
आम गलतियाँ
बिना उदाहरण के परिभाषाएँ रटना। "मुझे VLOOKUP आता है" कहना पर उसकी सीमाएँ न बता पाना। ऐसे skills का दावा जो practical test में न दिखा सकें।