Excel फ़ॉर्मूलों की चीट शीट

नीचे Excel के बुनियादी फ़ॉर्मूलों को काम के अनुसार समूहों में रखा गया है। फ़ॉर्मूले अंग्रेज़ी नामों में दिए गए हैं, जो Excel के कई संस्करणों में सामान्य हैं। इंटरफ़ेस की भाषा के अनुसार फ़ंक्शन के नाम और आर्ग्युमेंट सेपरेटर अलग हो सकते हैं: कहीं अल्पविराम और कहीं सेमीकोलन इस्तेमाल होता है।

मरम्मत के अनुमान से जुड़ा उदाहरण चाहिए तो 101 की सामग्री Excel में गणना कैसे करें? देखें। इसमें बुनियादी तरीका साफ़ है: मात्रा × कीमत, फ़ॉर्मूला नीचे तक कॉपी करना और अंतिम कुल निकालना।

Excel फ़ॉर्मूले कैसे पढ़ें और गलतियाँ कैसे घटाएँ?

एक सेल में फ़ॉर्मूला अक्सर साफ़ दिखता है। उसे नीचे कॉपी करते ही दायरा खिसक सकता है, शीर्ष पंक्ति का रेफ़रेंस बदल सकता है या किसी दूसरी पंक्ति की कीमत जुड़ सकती है। तीन आदतें इन गलतियों को घटाती हैं।

पहली आदत है पहले से तय करना कि कॉपी करते समय कौन-से रेफ़रेंस बदलेंगे और कौन-से स्थिर रहेंगे। डॉलर चिह्न रेफ़रेंस को स्थिर करता है: $A$1 कॉलम और पंक्ति दोनों को, $A1 केवल कॉलम को और A$1 केवल पंक्ति को स्थिर रखता है।

दूसरी आदत है संख्याओं को संख्या के रूप में रखना। कीमत गलती से टेक्स्ट बन जाए तो कुछ फ़ॉर्मूले त्रुटि या शून्य दे सकते हैं। मानक फ़ॉर्मैट में संख्या आम तौर पर दाईं ओर और टेक्स्ट बाईं ओर दिखाई देता है।

अंकगणित और सेल रेफ़रेंस

अनुमान या लेखे वाली लगभग हर तालिका की शुरुआत इन्हीं फ़ॉर्मूलों से होती है।

उदाहरण के लिए अनुमान की एक पंक्ति लें: B2 में मात्रा, C2 में कीमत और D2 में कुल।

• =B2*C2 — गुणा, यानी पंक्ति का कुल।
• =B2+C2, =B2-C2, =B2/C2 — जोड़, घटाव और भाग।
• =ROUND(D2,0) — पूरी इकाई तक राउंड करना। दो दशमलव स्थानों के लिए =ROUND(D2,2)।
• =ABS(D2) — निरपेक्ष मान; नकारात्मक मान आ जाएँ तो उपयोगी।

राउंडिंग सोच-समझकर करें। हर पंक्ति को अलग राउंड करने पर अंतिम परिणाम पूरे कुल को राउंड करने से अलग हो सकता है। अनुमान में पंक्तियों के मानों को दशमलव सहित रखना और अंतिम कुल को राउंड करना अधिक सटीक रहता है।

कुल, औसत, न्यूनतम और अधिकतम

यह समूह “कुल कितना है” और “सामान्य कीमत क्या है” जैसे सवालों का उत्तर देता है। अनुमान में यह किसी खंड या पूरे प्रोजेक्ट का कुल हो सकता है। खर्चों में यह सप्ताह, महीने या प्रोजेक्ट की राशि हो सकती है।

• =SUM(D2:D200) — पूरे दायरे का कुल।
• =AVERAGE(C2:C200) — औसत कीमत।
• =MIN(C2:C200), =MAX(C2:C200) — न्यूनतम और अधिकतम सीमा।
• =COUNT(D2:D200) — संख्या वाले सेल की गिनती।
• =COUNTA(D2:D200) — सभी खाली न होने वाले सेल की गिनती, चाहे उनमें संख्या हो या टेक्स्ट।

लंबी तालिका में खाली पंक्तियाँ हों तो दायरा थोड़ा बड़ा रखें और गणना ऐसे कॉलम पर करें जो हमेशा भरा जाता है, जैसे “कुल”।

शर्तों के आधार पर गिनती और जोड़ कैसे करें?

वर्गीकरण जुड़ते ही सामान्य जोड़ पर्याप्त नहीं रहता। वर्गीकरण में खंड, काम का प्रकार, प्रोजेक्ट, ठेकेदार या खर्च की मद हो सकती है। तब “केवल सामग्री का कुल”, “केवल प्रोजेक्ट 3 का कुल” या “टाइल लगाने वाले से जुड़ा खर्च” जैसी शर्तों के आधार पर परिणाम चाहिए।

• =SUMIF(A:A,"सामग्री",D:D) — एक शर्त के आधार पर कुल।
• =COUNTIF(A:A,"काम") — शर्त पूरी करने वाली पंक्तियों की संख्या।
• =AVERAGEIF(A:A,"डिलीवरी",C:C) — शर्त के आधार पर औसत।

एक से अधिक शर्तों के लिए नाम के अंत में “IFS” वाले फ़ंक्शन इस्तेमाल करें।

• =SUMIFS(D:D,A:A,"सामग्री",B:B,"प्रोजेक्ट 12") — दो शर्तों के आधार पर कुल।
• =COUNTIFS(A:A,"सामग्री",E:E,">0") — दो शर्तों को पूरा करने वाली पंक्तियों की संख्या।

खर्चों के लेखे में इसी तरीके को 101 की सामग्री Excel में खर्चों का लेखा कैसे रखें? में समझाया गया है। खर्च बढ़ने पर मदों और शर्तों के अनुसार समूह बनाए बिना संख्याओं का अर्थ जल्दी धुँधला पड़ जाता है।

तर्क और त्रुटियाँ

लॉजिकल फ़ंक्शन उन नियमों को स्वचालित करते हैं जिन्हें अक्सर शब्दों में बताया जाता है: छूट हो तो लागू करें, कीमत खाली हो तो कुल न निकालें और शून्य से भाग हो तो खाली परिणाम दिखाएँ।

• =IF(B2="","",B2*C2) — मात्रा भरी हो तभी कुल निकालना।
• =AND(A2<>"",C2>0) — दो शर्तों की एक साथ जाँच।
• =OR(A2="सामग्री",A2="काम") — दोनों में से किसी एक शर्त की जाँच।
• =IFERROR(expression,"") — त्रुटि की जगह खाली मान या तय टेक्स्ट लौटाना।

अनुमान में प्रतिशत वाली छूट या मार्जिन निकालते समय भाग का इस्तेमाल होता है। हर में शून्य हो तो Excel त्रुटि देता है। ऐसे स्थानों पर IFERROR तालिका को हाथ से साफ़ करने का समय बचाता है।

खोज और संदर्भ तालिकाएँ

संदर्भ तालिका वह शीट है जहाँ मानक जानकारी रखी जाती है: मूल्य सूची, कामों की सूची, मानदंड और गुणांक। गणना वाली शीट में केवल कोड या नाम रहता है और कीमत अपने-आप आ जाती है। इससे हाथ से डेटा भरना घटता है और अलग-अलग प्रोजेक्ट में कीमतें एक समान रहती हैं।

• =VLOOKUP(A2,PriceList!A:D,4,FALSE) — संदर्भ तालिका के पहले कॉलम में मान खोजना और चुने हुए कॉलम से परिणाम लौटाना।
• =HLOOKUP(A2,PriceList!A1:Z3,3,FALSE) — पंक्तियों में फैली संदर्भ तालिका के लिए क्षैतिज खोज।
• =INDEX(PriceList!D:D,MATCH(A2,PriceList!A:A,0)) — लचीली संदर्भ तालिकाओं में VLOOKUP की जगह अक्सर उपयोग होने वाला संयोजन।

Excel के आधुनिक संस्करणों में XLOOKUP उपलब्ध है। यह कॉलम नंबर दिए बिना खोज और परिणाम के दायरे तय करने देता है। फ़ंक्शन उपलब्ध न हो तो INDEX और MATCH का संयोजन व्यापक रूप से काम करता है।

मूल्य सूचियाँ और अनुमान पहले से Excel में हों तो 101 में जाते समय उन्हें दोबारा टाइप करने की ज़रूरत नहीं है। आयात का तरीका Excel से अनुमान का तेज़ आयात में दिया गया है।

टेक्स्ट, तारीख़ और डायनेमिक ऐरे

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

• =LEN(A2) — टेक्स्ट की लंबाई।
• =LEFT(A2,5), =RIGHT(A2,5), =MID(A2,3,4) — टेक्स्ट का हिस्सा निकालना।
• =TRIM(A2) — अतिरिक्त स्पेस हटाना।
• =TEXT(A2,"DD.MM.YYYY") — तारीख़ को तय टेक्स्ट फ़ॉर्मैट में बदलना, जैसे एक्सपोर्ट के लिए।
• =TODAY() और =NOW() — वर्तमान तारीख़ तथा वर्तमान तारीख़ और समय।
• =DATE(2026,2,3) — वर्ष, महीने और दिन से तारीख़ बनाना; तब उपयोगी जब ये हिस्से अलग कॉलम में हों।

Excel में डायनेमिक ऐरे उपलब्ध हों तो बदलते हुए परिणामों की सूची बनाने वाले फ़ॉर्मूले इस्तेमाल किए जा सकते हैं। वे पिवट तालिका के बिना डेटा फ़िल्टर करने, क्रम में लगाने और अलग-अलग मान निकालने में मदद करते हैं।

• =FILTER(A2:D200,A2:A200="सामग्री","") — शर्त के आधार पर पंक्तियाँ लौटाना।
• =SORT(A2:D200,4,-1) — चौथे कॉलम के अनुसार घटते क्रम में लगाना।
• =UNIQUE(A2:A200) — अलग-अलग मानों की सूची।

फ़ॉर्मूलों से छोटा अनुमान टेम्पलेट कैसे बनाएँ?

यह छोटा तरीका Excel में काम करने वाला अनुमान देता है। इसमें आगे खंड, छूट, मार्कअप, वास्तविक खर्च और भुगतान जोड़े जा सकते हैं।

1. इन कॉलमों वाली तालिका बनाएँ: खंड, मद, इकाई, मात्रा, कीमत, कुल, टिप्पणी।
2. “कुल” कॉलम में गुणा का फ़ॉर्मूला =D2*E2 डालें और नीचे तक कॉपी करें।
3. तालिका का अंतिम कुल जोड़ें: =SUM(F2:F200)।
4. SUMIF से खंड का कुल जोड़ें: =SUMIF(A:A,"प्रारंभिक काम",F:F)।
5. मूल्य सूची हो तो VLOOKUP या INDEX और MATCH से कीमत लाएँ।
6. जहाँ खाली मान या भाग की वजह से त्रुटि आ सकती है, वहाँ =IFERROR(…,"") लगाएँ।

ऐसी फ़ाइलें बढ़ने पर नवीनतम संस्करण, पहुँच के अधिकार और फ़ॉर्मूलों की शुद्धता सँभालना कठिन हो जाता है। इस विषय को अनुमान और वित्तीय लेखे के लिए Excel या 101 में से क्या चुनें? और प्रोजेक्ट लेखे के लिए Excel या 101 में विस्तार से समझाया गया है।

ग्राहक को हाथ से रिपोर्ट बनाए बिना साफ़ गणना दिखानी हो तो ऑनलाइन अनुमान गणना और 101 में शुरू से अनुमान बनाने का तरीका देखें।