Excel Formula कैसे बनाएं: बेसिक से एडवांस तक की पूरी गाइड
Excel में formula बनाना step by step सीखें: cell references और core functions से लेकर nesting, array logic और debugging के वे तरीके तक, जो सच में आपका समय बचाते हैं।
आपने एक workbook खोला है जो देखने में काफी सरल लगता है, लेकिन totals मैच नहीं कर रहे, copy किया हुआ formula गलत column की ओर इशारा कर रहा है, और किसी ने एक note छोड़ रखा है: “बस IF ठीक कर दो।” आप Excel formulas बना तो सकते हैं जो सही गणना करते हैं, लेकिन भरोसेमंद spreadsheet काम के लिए सिर्फ़ syntax याद रखना काफी नहीं है। आपको references समझने, edge cases test करने, dependencies trace करने और किसी दूसरे व्यक्ति या AI द्वारा बनाए गए हर formula को verify करने की भी ज़रूरत है।
विषय-सूची
- Excel Formulas से आप क्या-क्या बना पाएंगे
- वे बुनियादी बातें जिन पर हर Formula टिका होता है
- वे Core Functions जो ज़्यादातर असली काम संभाल लेते हैं
- Nesting और Array Formulas को समझें
- जब कुछ गड़बड़ हो जाए तो Formulas कैसे debug करें
- बड़े पैमाने पर Formulas का audit और भरोसा
- Formula बनाने की आपकी Checklist
Excel Formulas से आप क्या-क्या बना पाएंगे
अगर आप पहले से Excel इस्तेमाल करते हैं, तो हो सकता है आप values manually भरते हों, साथियों से formulas copy करते हों, या तब तक cell references बदलते रहते हों जब तक result सही दिखने न लगे। एक झटपट calculation के लिए यह तरीका चल जाता है, लेकिन workbook बड़ा होने पर, या जब उसे आगे किसी और को संभालना हो, तो यह नाज़ुक साबित होता है। Formulas बार-बार इस्तेमाल होने वाले building blocks हैं, कोई one-off जवाब नहीं।
कई दशकों से Excel business के कामों का हिस्सा रहा है। Microsoft ने सबसे पहले September 1985 में Macintosh के लिए Excel लॉन्च किया था और 2025 में Excel के 40 साल मनाए। Microsoft की workbook statistics सुविधा अब worksheet और workbook दोनों स्तरों पर formulas की गिनती करती है, जो दिखाता है कि रोज़मर्रा की spreadsheet files में formulas कितने केंद्रीय हो गए हैं। एक बड़े spreadsheet corpus पर हुई research में deduplication से पहले IF कुल 30,798,987 बार मिला, जिसकी वजह से practical spreadsheet logic सीखने के लिए यह एक उपयोगी शुरुआती बिंदु है। Microsoft का Excel इतिहास और spreadsheet datasets और benchmarks research, दोनों बताते हैं कि formula literacy क्यों ज़रूरी है।
सीखने का व्यावहारिक क्रम
शुरुआत किसी column के total से करें, जैसे =SUM(B2:B20)। फिर conditions जोड़ें, जैसे SUMIFS से किसी चुने हुए region का revenue ही जोड़ना। इसके बाद आप एक nested IF बना सकते हैं जो records को categories में बांट दे, या XLOOKUP से transaction table में prices और categories ला सकते हैं।
आखिरी कदम एक छोटा dynamic dashboard है। B1 में बना एक dropdown उस formula को control कर सकता है जो sales filter करे, summary update करे और chart को data दे। यहाँ के examples छोटे ranges पर आधारित हैं ताकि logic साफ दिखे, लेकिन वही आदतें multiple sheets और असंगत source data वाले operational workbooks पर भी लागू होती हैं।
इस लेख के अंत तक आप इन चीज़ों में सहज हो जाएंगे:
- बुनियादी गणनाएं: Totals, differences, percentages और date arithmetic।
- Conditional logic: Nested
IFstatements और criteria आधारित summaries। - Lookups: पुरानी files के लिए
VLOOKUPऔर नई designs के लिएXLOOKUP। - Dynamic results:
FILTER,SORTऔरUNIQUEformulas जो पड़ोसी cells में spill होते हैं। - Quality control: References जांचने, असामान्य inputs test करने और failures trace करने की एक repeatable प्रक्रिया।
वे बुनियादी बातें जिन पर हर Formula टिका होता है
हर Excel formula equals sign से शुरू होता है। यह Excel को बताता है कि आगे लिखी चीज़ को सामान्य text नहीं, बल्कि expression की तरह पढ़ा जाए। जैसे, =B2*C2, B2 की quantity को C2 के unit price से गुणा करता है।
Cell references तय करते हैं कि formula copy करने पर कैसे behave करेगा। A1 जैसा relative reference हिलने पर बदल जाता है, यानी row two का formula नीचे भरने पर row three का formula बन सकता है। $A$1 जैसा absolute reference अपनी जगह स्थिर रहता है। Mixed references सिर्फ़ एक dimension lock करते हैं: A$1 में row fixed रहता है, जबकि $A1 में column fixed रहता है।
चार operator समूह
Arithmetic operators गणनाएं करते हैं:
- Arithmetic:
+,-,*,/और^।=B2*C2दो cells को गुणा करता है, जबकि=B2^2किसी value का वर्ग निकालता है। - Comparison:
=,<>,<,>,<=और>=।=B2>100तबTRUEreturn करता है जब value threshold से बड़ी हो। - Text concatenation:
&text जोड़ता है।=A1&A2दो cells की contents जोड़ देता है, जबकि=A1&" "&A2बीच में एक space डालता है। - Reference operators:
:range बनाता है, जैसेA1:A10; comma references को जोड़ता है, और space वहाँ intersection return करता है जहाँ referenced ranges overlap करते हों।
Relative और absolute references का फर्क एक tax example से साफ दिख जाता है। अगर tax rate F1 में रखा है, तो formula नीचे भरने से पहले =B2*$F$1 लिखें। Dollar signs न हों, तो F1 खिसककर F2, F3 और आगे बनता जाएगा, और तब तक results गलत आएंगे जब तक हर row में वही intended rate न हो।
Parentheses सही दिखने वाली गलतियों को रोकते हैं
Excel operations का एक निश्चित क्रम मानता है। जोड़ और घटाव से पहले गुणा और भाग होता है, इसलिए =A1+B1*C1 और =(A1+B1)*C1 एक नहीं हैं। जब business rule default क्रम से ज़्यादा मायने रखती हो, तब हमेशा parentheses लगाएं।
व्यावहारिक नियम: अगर कोई formula किसी वाक्य की तरह हो, जैसे “discount जोड़ो, फिर quantity से गुणा करो”, तो parentheses ऐसे लगाएं कि दूसरा व्यक्ति वह rule सीधे पढ़ सके।
वे Core Functions जो ज़्यादातर असली काम संभाल लेते हैं
कुछ ही functions का छोटा समूह बार-बार आने वाले कई काम निपटा देता है। Syntax मायने रखता है, लेकिन range का चुनाव और missing data से निपटना भी उतना ही मायने रखता है।
IF किसी condition के आधार पर फैसला लेता है। जैसे =IF(C2>100,"Review","Clear") देखता है कि C2 की value 100 को पार कर गई है या नहीं, और उसी हिसाब से “Review” या “Clear” लौटाता है।
Nesting और Array Formulas को समझें
Nesting में एक function के अंदर दूसरा function रखा जाता है, यानी भीतरी calculation का result बाहरी rule का input बन जाता है। जैसे =IF(SUM(B2:B5)>1000,"Review","Clear") पहले values का total निकालता है, फिर जांचता है कि वह threshold पार करता है या नहीं। Formulas को अंदर से बाहर की ओर पढ़ें, ठीक वैसे ही जैसे आप spreadsheet calculation के हर चरण की जांच करते हैं।
मान लीजिए B2 में customer type, C2 में unit price और D2 में quantity है। एक मान्य, संक्षिप्त pricing formula है =ROUND(IF(B2="Member",C2*D2*0.9,C2*D2),2)। यह line value निकालता है, जहाँ लागू हो वहाँ member discount लगाता है, और result को round करता है। कई rows पर फैली eligibility rules को helper column या range आधारित SUMIFS में रखना चाहिए, जहाँ sum range और criteria ranges साफ तौर पर लिखे हों और अलग-अलग जांचे जा सकें।
Workbook छोटा करने से पहले layers पढ़ें
छोटे formulas अपने आप maintain करने में आसान नहीं हो जाते। अगर कई rules एक साथ nest हों, तो reviewer के लिए यह पता लगाना मुश्किल हो सकता है कि अनजानी रकम किस condition से आई। जब formula कुछ logical layers से बड़ा होने लगे, तब helper columns हर step को test, audit और सौंपने में आसान बना देते हैं।
| Step | Function Layer | उद्देश्य | Example Output |
|---|---|---|---|
| 1 | SUMIFS | मैच होती sales चुनना | Eligible sales का total |
| 2 | IF | Business rule लागू करना | Discounted या standard value |
| 3 | ROUND | दिखने वाली precision तय करना | अंतिम रकम |
Dynamic array formulas एक ही cell से कई results देते हैं। =FILTER(A2:D100,D2:D100="Open") open records लौटाकर उन्हें पास के cells में spill कर देता है। =SORT(A2:D100,3,-1) लौटे हुए range को उसके third column से sort करता है, जबकि =UNIQUE(B2:B100) dropdown या summary के लिए एक distinct list बनाता है।
Formula पर राय बनाने से पहले उसका intended spill area जांचें। Filtering या sorting logic सही होने पर भी, गंतव्य cell में रुकावट डालने वाली एक value #SPILL! पैदा कर सकती है। पहले रुकावट हटाएं, फिर लौटी हुई rows को source data से मिलाकर जांचें।
पुराने workbooks में Ctrl+Shift+Enter से लिखे गए legacy array formulas हो सकते हैं। Excel उन्हें formula bar में curly braces के साथ दिखाता है, हालांकि वे braces आप type नहीं करते। पुरानी designs maintain करते समय उन्हें वैसे ही रहने दें, और जब workbook समर्थन करता हो तो नए काम के लिए dynamic arrays को तरजीह दें।
Draft करने में मदद के लिए Google Sheets formula generator syntax सुझा सकता है। Formula इस्तेमाल करने से पहले उसके ranges, conditions और sample output को असली workbook से मिलाकर जांच लें।
जब कुछ गड़बड़ हो जाए तो Formulas कैसे debug करें
गलत result हमेशा कोई साफ दिखने वाला error नहीं देता। Microsoft की formula guidance आम गलतियों की ओर ध्यान दिलाती है, जैसे शुरू का equals sign छूट जाना, parentheses बेमेल होना, operator precedence को गलत समझना, और hidden content वाले cell को blank समझ लेना। Microsoft Press 11 तरह के Excel errors दर्ज करता है, जिनमें #REF!, #VALUE!, #N/A, #SPILL! और #CALC! शामिल हैं, इसलिए दिखने वाला संदेश एक अहम सुराग है।
शुरुआत Excel के background error checking और Error Checking tool से करें। फिर formula के relationships की जांच करें। Microsoft की formula error detection guidance एक structured workflow के हिस्से के तौर पर Error Checking, Trace Precedents और Evaluate Formula इस्तेमाल करने की सलाह देती है।
Error को diagnosis की तरह पढ़ें
#DIV/0!का मतलब है कि calculation zero से भाग हो रहा है या denominator खाली है।#N/Aआमतौर पर बताता है कि lookup को कोई match नहीं मिला।#NAME?अनजाने text की ओर इशारा करता है, अक्सर function की spelling गलत होने या quotation marks छूट जाने की वजह से।#NULL!अमान्य range intersection की ओर इशारा करता है।#NUM!किसी अमान्य numeric operation का संकेत देता है।#REF!का मतलब है कि reference delete हो चुका है या अब मान्य नहीं है।#VALUE!आमतौर पर नामेल data type बताता है।#GETTING_DATAतब दिखता है जब Excel जानकारी ला रहा हो।#SPILL!का मतलब है कि dynamic result अपना output range नहीं ले पा रहा।#CALC!calculation की समस्या बताता है, अक्सर unsupported array result की वजह से।#UNKNOWN!बताता है कि Excel मांगी गई calculation या content पहचान नहीं पा रहा।
समस्या वाला cell चुनकर F2 दबाएं ताकि edit mode खुल जाए। हर range, parenthesis और argument जांचें। अगर formula bar इस्तेमाल करना मुश्किल हो जाए, तो Ctrl+Backspace editing view reset कर देता है। आप formula bar में किसी subexpression को highlight करके F9 दबा सकते हैं ताकि उस हिस्से का हिसाब दिख जाए। बाद में Esc दबा दें, ताकि formula गलती से displayed value से बदल न जाए।
अंदाज़े की बजाय calculation को trace करें
Trace Precedents input cells से चुने हुए formula तक arrows खींचता है। Trace Dependents दिखाता है कि result आगे कहाँ बह रहा है, जो summary cell बदलने से पहले खास काम आता है। Evaluate Formula nested expression को एक-एक टुकड़े में चलाकर दिखाता है, जिससे आप उस term तक पहुंच सकते हैं जहाँ सही-सी value अचानक error में बदल जाती है।
क्रम सरल है: error indicator देखें, inputs trace करें, expression evaluate करें, सबसे छोटा टूटा हिस्सा ठीक करें, और दोबारा calculate करें। पूरा formula शुरू से दोबारा लिखना शुरू न करें; इससे काम के सुराग अक्सर मिट जाते हैं।
Live workbook पर इन tools को लगाने से पहले उन्हें action में देख लें।
बड़े पैमाने पर Formulas का audit और भरोसा
Operational formula को उस code की तरह समझें जिसे आगे कोई और संभालेगा। Independent spreadsheet-auditing research में 50 operational spreadsheets के formula cells में 0.9% से 1.8% तक errors मिले, इस बात पर निर्भर करते हुए कि researchers ने error को कैसे परिभाषित किया। Workbooks के बीच इतना व्यापक अंतर ही असली सबक है, इसलिए यह auditing methodology ठीक-ठाक spot-checking नहीं, बल्कि व्यवस्थित workbook-level review की हिमायत करती है।
उन cases की जांच करें जिन पर formulas झूठ बोलते हैं
Report share करने से पहले जानबूझकर blank cells, numeric columns में घुसा text, negative values, zero-length strings और missing lookup keys test करें। हर result को किसी ज्ञात manual calculation से मिलाएं। ऐसा formula जो मान्य दिखने वाला number लौटा रहा हो, वह फिर भी गलत condition लगा रहा हो सकता है या गलत period का reference दे रहा हो सकता है।
ज़रूरी ranges को ApprovedSales या TaxRate जैसे मतलब वाले नाम देने के लिए Name Manager इस्तेमाल करें। =SUM(ApprovedSales) जैसा formula लंबे address की तुलना में मंशा ज़्यादा साफ बताता है, बशर्ते named range सही तरीके से maintain हो।
Watch Window दूसरी sheets edit करते समय ज़रूरी cells पर नज़र रखने में मदद करता है। Inquire add-in workbook versions की तुलना और relationships की जांच में मदद कर सकता है, जिसमें वे references भी शामिल हैं जो सामान्य navigation में साफ नहीं दिखते। उपलब्धता आपके Excel edition और organizational setup पर निर्भर कर सकती है, इसलिए उस पर कोई प्रक्रिया बनाने से पहले पक्का कर लें कि tool enabled है।
तैयार logic को protect करें
Review के बाद formulas पर गलती से होने वाले बदलाव रोकने के लिए sheet protection इस्तेमाल करें। जहाँ ज़रूरी हो वहाँ sensitive formula entries hide करें, लेकिन याद रखें कि protection एक control है, documentation या version management का विकल्प नहीं।
एक monthly revenue report की कल्पना करें जहाँ column insert करने से formula गलत field की ओर इशारा करने लगता है। Result फिर भी सही number जैसा दिख सकता है। एक named range, एक precedent trace, पिछले version से तुलना, और पहले से ज्ञात expected result वाली एक test row, ये सब मिलकर समस्या को executive meeting के बाद नहीं, बल्कि workbook बनाते समय ही पकड़ लेते।
Formula बनाने की आपकी Checklist
Live workbook में formula जोड़ने के हर बार यह checklist अपनाएं। यह formula बनाने को अंदाज़े का खेल नहीं, बल्कि एक छोटी review प्रक्रिया बना देती है।
- शुरुआत equals sign से करें: मंज़िल का target cell चुनें और
=से शुरू करें। इससे Excel expression को text की तरह save नहीं कर पाएगा। - Inputs परिभाषित करें: Cells, ranges, criteria और expected output का type लिख लें। तय करें कि result numeric हो, text, date, या फिर spilled range।
- Function चुनें: एक scratch cell से शुरू करें।
SUM,IF,SUMIFS,XLOOKUP,TEXT,DATEया कोई और function चुनें जो rule से मेल खाता हो। - References lock करें: Copy करने से पहले rates, lookup tables और fixed criteria cells में
$लगाएं।A$1और$A1जैसे mixed references जानबूझकर जांचें। - Logic test करें: Formula को किसी ज्ञात value से मिलाएं, फिर blanks, text, zeros, negative values और missing matches test करें। Result साफ न हो तो Error Checking और Evaluate Formula इस्तेमाल करें।
- सुरक्षित तरीके से commit करें: मंशा बताने वाला एक छोटा cell comment या पास का note जोड़ें, output को format करें, और जब दूसरे users को edit नहीं करना चाहिए तो तैयार formula को protect करें।
ये steps उन गलतियों को निशाना बनाते हैं जो सबसे ज़्यादा परेशानी देती हैं: copy करते समय खिसकती unanchored ranges, गलत argument संख्या, और चुपचाप गलत results जो महीनों बाद सामने आते हैं। अगर आप AI से formula draft करवाते हैं, तो वही जांच लागू करें। Microsoft की Excel Copilot guidance users से कहती है कि सुझाए गए formula की समीक्षा करें और लगाने से पहले पक्का करें कि उसके references और logic dataset से मेल खाते हैं। Google Sheets workflows के लिए Google Sheets में AI इस्तेमाल करना drafting और analysis में मदद कर सकता है, लेकिन जांच की ज़िम्मेदारी आपकी ही रहती है।
GPT Workspace, Google Workspace applications के अंदर काम करता है और Sheets formulas draft करने, चुने हुए ranges का analysis करने, data clean करने, और सादी भाषा की requests को spreadsheet logic में बदलने में मदद कर सकता है। GPT Workspace पर जाकर एक ऐसा workflow आज़माएं जहाँ formula बनाना और spreadsheet की समीक्षा एक ही workspace में होती है, और फिर अपनी अगली report publish करने से पहले validation checklist लागू करें।