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) — शर्त के आधार पर औसत।

एक से अधिक शर्तें हों तो कई शर्तें स्वीकार करने वाले फ़ंक्शन इस्तेमाल करें।

• =SUMIFS(D:D,A:A,"सामग्री",C:C,">0") — सकारात्मक कीमत वाली सामग्री का कुल।
• =COUNTIFS(A:A,"सामग्री",C:C,">0") — सकारात्मक कीमत वाली सामग्री की पंक्तियों की संख्या।

खर्चों के लेखे का मिलता-जुलता तरीका 101 के लेख Excel में खर्चों का लेखा कैसे रखें? (रूसी भाषा में) में बताया गया है: खर्च बढ़ने पर मदों और शर्तों के अनुसार समूह बनाए बिना संख्याओं का अर्थ जल्दी खो जाता है।

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

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

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

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

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

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

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

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

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

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

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

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

अगर आपके 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(VLOOKUP(B2,PriceList!A:D,4,FALSE),"")।

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

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