Microsoft Excel मे Formula Tab का उपयोग क्या है? (Microsoft Excel Formula Tab all options explanation in Hindi)

Topics in this Post show

Microsoft Excel मे Formula Tab का उपयोग

वर्कशीट या डाटाबेस मे संख्याओं या राशियों की गणना करने के लिए Microsoft Excel मे Formula Tab का उपयोग किया जाता है। इसमे कई Formula और Function उपलब्ध होते हैं जिनका इस्तेमाल करके आसानी से मूल्यों की गणना व तुलना आदि की जा सकती है। Formula Tab को Function Library, Defined Names, Formula Auditing और Calculation के साथ कई सेक्शन में विभाजित किया गया है। Formula Tab के सभी सेक्शन्स व उनके ऑप्शन्स के बारे में विस्तृत विवरण नीचे दिया गया है।

Formula Tab के सभी सेक्शन्स व उनके ऑप्शन्स

1. Function Library

इस सेक्शन के अंतर्गत Auto Sum, Financial, Logical, Text, Date & Time, Math and Lookup Reference आदि से संबंधित बड़ी संख्या में पूर्वनिर्धारित फार्मूला की लिस्ट होती है। इनका इस्तेमाल करके डाटा को आसानी से कैलकुलेट व मोडीफ़ाई किया जा सकता है। यह सेक्शन हाल ही में उपयोग किए गए Formulas के लिए एक अनुभाग भी प्रदान करता है, ताकि आप उन Function पर वापस जा सकें जिनका आप सबसे जल्दी उपयोग करते हैं। नीचे कुछ सबसे उपयोगी Formulas की लिस्ट दी गई है, जो निम्न प्रकार है।

Excel Operators

Excel मे Formula व Functions का इस्तेमाल करने से पहले Arithmetic Operators व Symbols की जानकारी होना जरूरी है, क्योंकि इनका इस्तेमाल फॉर्मूलों के साथ किया जाता है। नीचे टेबल मे Arithmetic Operators व Symbols का इस्तेमाल Example के साथ बताया गया हैं।

Operator Name	Operator	Example
Plus (Add)	+	=A1+B1
Minus (Subtract)	–	=A1-B1
Astric (Multiply)	*	=A1*B1
Divide	/	=A1/B1
Equal To	=	=A1=B1
Greater Than	>	=A1>B1
Greater Than Equal To	>=	=A1>=B1
Less Than	<	=A1<B1
Less Than Equal To	<=	=A1<=B1
Not Equal To	<>	=A1<>B1
Percent	%	=A1%
And	&	=A1&B1
Colon (Range)	:	=A1:A10
Power	^	=A1^B1
Comma	,	=Sum(A1:A10,B1:B5)

Formula List

1. Sum

=sum(range)

किसी रेंज को जोड़ने के लिए SUM फार्मूला का इस्तेमाल करते हैं। रेंज एक वैल्यूस का सेलेक्शन होता है, रेंज मे जितनी वैल्यू आती है वे सभी जुड़ जाती हैं। एक से अधिक रेंज जोड़ने के लिए , (Comma) का इस्तेमाल करते हैं।

Example : =SUM(B1:B5,C1:C5)

2. Sum If

=sumif(range, criteria, sum_range)

किसी रेंज को किसी क्राइटेरिया के आधार पर जोड़ने के लिए SUMIF फॉर्मूले का इस्तेमाल करते हैं। निम्न टेबल मे Expense के अंतर्गत Rent क्राइटेरिया को जोड़ना बताया गया है।

	A	B
1	Expense	Amount
2	Rent	2000
3	Food	900
4	Rent	2500
5	Recharge	700
6	Rent	3500

Formula : =sumif(criteria_range, criteria, sum_range)

Example : =SUMIF(A2:A6,A2, B2:B6)

Ans : 80000

Example 2 : =SUMIF(A1:A10,”>100″)

उपरोक्त उदाहरण मे A1 से A10 मे जितनी भी संख्या होंगी उनमे से वही जुड़ेंगी जो 100 से अधिक होंगी।

3. Count

=count(range)

किसी रेंज मे केवल संख्याओं को गिनने के लिए COUNT फॉर्मूले का इस्तेमाल करते है।

Example : =COUNT(A1:A10)

4. Count Blank

=countblank(range)

किसी रेंज मे केवल Empty Cell को गिनने के लिए COUNTBLANK फॉर्मूले का इस्तेमाल करते हैं।

Example : =COUNTBLANK(A1:A10)

5. Count A

=counta(range)

किसी रेंज मे केवल Text वाली Cell को गिनने के लिए COUNTA फॉर्मूले का इस्तेमाल करते हैं।

Example : =COUNTA(A1:A10)

6. Count If

=countif(range,”text” or value)

किसी रेंज मे किसी विशेष Text या Value को गिनने के लिए COUNTIF फॉर्मूले का इस्तेमाल करते हैं।

Example 1 : =COUNTIF(A1:A10, “M”)

Example 2 : =COUNTIF(A1:A10, 10)

नीचे दी गई टेबल मे एक साथ सभी COUNT FORMULAS का इस्तेमाल करना बताया गया है।

7. Max

=max(range)

किसी रेंज मे सबसे बड़ी संख्या को जानने के लिए MAX फॉर्मूले का इस्तेमाल करते हैं।

Example : =MAX(A1:A10)

8. Min

=min(range)

किसी रेंज मे सबसे छोटी संख्या को जानने के लिए MIN फॉर्मूले का इस्तेमाल करते हैं।

Example : =MIN(A1:A10)

9. Average

=average(range)

किसी रेंज मे एवरेज संख्या को जानने के लिए AVERAGE फॉर्मूले का इस्तेमाल करते हैं।

Example : =AVERAGE(A1:A10)

10. Average If

=averageif(criteria_range, criteria, average_range)

किसी रेंज मे किसी क्राइटेरिया के आधार पर एवरेज संख्या जानने के लिए AVERAGEIF फॉर्मूले का इस्तेमाल किया जाता है। निम्न टेबल मे Expense के अंतर्गत Rent क्राइटेरिया का एवरेज निकालना बताया गया है।

	A	B
1	Expense	Amount
2	Rent	2000
3	Food	900
4	Rent	2500
5	Recharge	700
6	Rent	3500

Formula : =averageif(criteria_range, criteria, average_range)

Example : =AVERAGEIF(A2:A6,A2,B2:B6)

Ans : 2666.66

Example 2 : =AVERAGEIF(A1:A10, “>100”)

उपरोक्त उदाहरण मे सिर्फ उन्ही वैल्यूस का एवेरेज ज्ञात होगा जो 100 से अधिक होंगी।

11. If Condition

=if(logical_condition, value_if_true, value_if_false)

IF फॉर्मूले की का उपयोग कन्डिशन सेट करने के लिए किया जाता है। इस फॉर्मूले की मदद से किसी Value या Text को कन्डिशन के आधार पर रेप्रिज़ेन्ट कर सकते हैं। जैसे किसी स्टूडेंट का स्कोर 50 से ऊपर है तो पास, 50 से नीचे है तो फैल दिखा सकते हैं।

Example : =IF(A1<50,”Fail”,”Pass”)

12. Nested If

=if(logical_condition, value_if_true, if(logical_condition, value_if_true, if(logical_condition, value_if_true, value_if_false)))

NESTEDIF फॉर्मूले का उपयोग एक से अधिक कन्डिशन सेट करने के लिए किया जाता है। जैसे किसी स्टूडेंट का स्कोर 33 से कम है तो Fail, 45 से कम है तो Third, 60 से कम है तो Second तथा इसके अलावा कोई भी स्कोर है तो First प्रिन्ट करा सकते हैं।

Example : =IF(A1<33, ”Fail”, IF(A1<45, ”Third”, IF(A1<60,”Second”, ”First”)))

13. Concatenate

=concatenate(text1,text2,…)

CONCATENATE फॉर्मूले की मदद से एक या अधिक Cell के मैटर को या Text को आपस मे जॉइन सकते हैं।

Example : =CONCATENATE(“Mr. ” A1, B1)

14. Upper

=upper(cell_no or text)

किसी Cell के Text को UPPER फॉर्मूले की मदद से CAPITAL CASE मे बदल सकते हैं।

Example : =UPPER(A1)

15. Lower

=lower(cell_no or text)

किसी Cell के Text को LOWER फॉर्मूले की मदद से small case मे बदल सकते हैं।

Example : =LOWER(A1)

16. Proper

=proper(text or cell_no)

किसी Cell के Text को PROPER फॉर्मूले की मदद से Proper Case मे बदल सकते हैं।

Example : =PROPER(A1)

17. Left

=left(cell_no, num_chars)

किसी Cell मे बाएं से दिए गए num_chars के अनुसार उतने Character पिक करने के लिए LEFT फॉर्मूले का इस्तेमाल करते हैं।

Example : =LEFT(A1, 5)

18. Right

=right(cell_no, num_chars)

किसी Cell मे दायें से दिए गए num_chars के अनुसार उतने Character पिक करने के लिए RIGHT फॉर्मूले का इस्तेमाल करते हैं।

Example : =RIGHT(A1, 5)

19. Middle

=mid(cell_no, start_num, num_chars)

किसी Cell मे start_num और num_chars के अनुसार उतने Character पिक करने के लिए MIDDLE फॉर्मूले का इस्तेमाल करते हैं। start_num का मतलब जिस पोजीशन से Character पिक करने हैं, और num_chars का मतलब कितने Characters पिक करने हैं।

Example : =MID(A1, 3, 4)

20. Find

=find(find_text, within_text)

किसी Cell मे किसी Character या Number की पोजीशन पता करने के लिए FIND फॉर्मूले का इस्तेमाल करते हैं।

Example : =FIND(“@”, A1)

अगर A1 cell मे Email id लिखी है और पता करना है कि @ किस नंबर पोजीशन पर है, तो उपरोक्त फॉर्मूले की मदद से पता कर सकते हैं।

21. Replace

=replace(cell_num, sart_num, num_chars, “new_text”)

किसी Cell मे किसी Text या Number को रिप्लेस करने के लिए REPLACE फॉर्मूले का इस्तेमाल करते हैं। start_num का मतलब किस पोजीशन से रिप्लेस करना है, और num_chars का मतलब कितने Character रिप्लेस करना है।

Example : = REPLACE(A2, 9, 5, “hotmail”)

	A	B
1	Email	Replace Result
2	[email protected]	[email protected]

22. Repeat

=rept(“text”, number_times)

किसी Number, Text या Symbol को नंबर ऑफ टाइम्स रिपीट कराने के लिए REPT फॉर्मूले का इस्तेमाल करते हैं।

	A	B
1	Channel Name	Repeat Result
2	Star Plus	*****
3	Sony Tv	***

Formula : =rept(“char”, number or cell_no)

Example : = REPT(“*”, 5)

23. Substitute

=substitute(cell_num, “old_text”, “new_text”, instance_num)

	A	B	C
1	Data	Formula	Result
2	jan-feb-march	=substitute(A2,”-“,” “)	jan feb march
3	jan feb march	=substitute(A3,” ”,”-“)	jan-feb-march
4	pin 241303	=substitute(A4,” “,”-“)	pin-241303

किसी रिपीटेड मार्क, सिम्बल आदि को किसी अन्य मे रिप्लेस करने के लिए SUBSTITUTE फॉर्मूले का इस्तेमाल करते हैं।

24. Now

=now()

सिस्टम का वर्तमान Date & Time प्रिन्ट करने के लिए NOW फॉर्मूले का इस्तेमाल करते हैं।

25. Today

=today()

सिस्टम की वर्तमान Date प्रिन्ट करने के लिए TODAY फार्मूला का इस्तेमाल करते हैं।

26. Dated If

=datedif(birth_date_cell_no, today(),”y”)

DATEDIF फॉर्मूले की मदद से किसी भी पर्सन की Age पता कर सकते हैं। साथ ही वर्ष, महीने और दिन भी ज्ञात कर सकते हैं। इस फॉर्मूले को इस्तेमाल करने के लिए किसी Cell मे पर्सन की Date of Birth लिख ले उसके बाद निम्न तरह से फार्मूला लगाए।

	A	B	C
1	DOB	Formula	Age Result
2	11/05/1995	=DATEDIF(A2,TODAY(),”Y”)	27 (Year)
3		=DATEDIF(A2,TODAY(),”YM”)	6 (Month)
4		=DATEDIF(A2,TODAY(),”MD”)	15 (Days)

Y का इस्तेमाल वर्ष ज्ञात करने के लिए, YM का इस्तेमाल महीने ज्ञात करने के लिए तथा MD का इस्तेमाल दिनों की संख्या ज्ञात करने के लिए करते हैं।

अगर किसी पर्सन की केवल Age कैलकुलेट करनी है तो निम्न function की मदद से भी कर सकते हैं।

=(current_date_cell_no – birth_date_cell_no)/365.25

27. Transpose

=transpose(range)

इस फॉर्मूले के इस्तेमाल से रो मे लिखे डाटा को कॉलम मे और कॉलम मे लिखे डाटा को रो मे बदल सकते हैं। ऐसा करने के लिए रेंज सलेक्ट करें और पहली Cell मे निम्नलिखित फार्मूला लिखकर ctrl+shift+enter प्रेस करें।

Example : =TRANSPOSE(A1:H1)

इस प्रक्रिया को Past Special मे उपलब्ध Transpose ऑप्शन के द्वारा भी कर सकते हैं। इसके लिए पहले रेंज कॉपी कर लें फिर जहां पेस्ट करना हो वह सेल सिलेक्ट करें और Transpose ऑप्शन पर क्लिक करें।

28. Power

=power(number, power_value)

किसी नंबर की पावर वैल्यू के आधार पर उसकी पावर संख्या ज्ञात कर सकते हैं।

Example : =POWER(9, 3)

Result : 729

29. Even

=even(value or cell_no)

किसी विषम संख्या की सम संख्या ज्ञात करने के लिए EVEN फॉर्मूले का इस्तेमाल करते हैं।

Example : =EVEN(13)

Result : 14

30. Odd

=odd(value or cell_no)

किसी सम संख्या की विषम संख्या ज्ञात करने के लिए ODD फॉर्मूले का इस्तेमाल करते हैं।

Example : =ODD(12)

Result : 13

31. Modulus

=mod(number, diviser_value)

MOD फॉर्मूले के द्वारा किसी संख्या का डिवाईजर वैल्यू के आधार पर शेषफल ज्ञात कर सकते हैं।

Example : =MOD(171, 4)

Result : 3

32. Round

=round(number, num_digits)

	A	B	C
1	Values	Formula	Result
2	12.789	=ROUND(A2,2)	12.79
3	240.436	=ROUND(A3,0)	240
4	526.5	=ROUND(A4,-3)	1000

निम्नलिखित शर्तों के अनुसार ROUND फॉर्मूले की मदद से किसी भी संख्या की Rounded Value ज्ञात कर सकते हैं।

यदि num_digits 0 (शून्य) से अधिक है, तो संख्या को दशमलव स्थानों की निर्दिष्ट संख्या तक Round किया जाता है।
अगर num_digits 0 है, तो संख्या को निकटतम पूर्णांक तक Round किया जाता है।
अगर num_digits 0 से कम है, तो संख्या को दशमलव बिंदु के बाईं ओर Round किया जाता है। -1 रिटर्न 10 के करीब है, -2 रिटर्न 100 के करीब है और -3 रिटर्न 1000 के करीब है।

33. Integer

=int(value)

यह फ़ंक्शन किसी संख्या को निकटतम पूर्ण संख्या तक राउंड करता है।

Example : =INT(122.12)

Result : 122

34. Length

=len(text)

किसी Cell मे लिखे Text या Value की लेंथ को ज्ञात करने के लिए LEN फॉर्मूले का इस्तेमाल करते हैं।

	A	B
1	Email	Length Result
2	[email protected]	19

Formula : =len(text)

Example : =LEN(A2)

35. Char

=char(number)

CHAR फॉर्मूले के द्वारा 1 से 255 तक के ASCII Character ज्ञात कर सकते हैं।

Example : =CHAR(65)

Result : A

36. Trim

=trim(text)

किसी Cell मे लिखे Text के एक्स्ट्रा स्पेसेस को TRIM फॉर्मूले की मदद से रिमूव कर सकते हैं।

Example : =TRIM(A1)

37. Frequency

=frequency(data_array, bins_array)

FREQUENCY फॉर्मूले की मदद से हम किसी भी रेंज मे कोई वैल्यू कितनी बार रिपीट हुई है, यह पता कर सकते है। मान लेते है की किसी Age कॉलम मे ये पता करना है की सबसे अधिक किस उम्र के व्यक्ति है। फॉर्मूले को इस्तेमाल करने के लिए निम्न स्टेप फॉलो करेंगे।

सबसे पहले हम जिस रेंज मे देखना चाहते है वह रेंज सलेक्ट करके कॉपी करेंगे फिर एक नए कालम मे पेस्ट कर लेंगे जिसका नाम Unique रखेंगे।
फिर unique कॉलम से डुप्लिकेट वैल्यू रिमूव कर लेंगे। Data Tab > Duplicate
अब नए कॉलम मे फार्मूला लगाएंगे =FREQUENCY(data_array, bins_array)
Data_array मे age कॉलम की रेंज देंगे और bins_array मे Unique कॉलम की रेंज देंगे।
इंटर करने के बाद सभी के लिए ड्रैग डाउन कर लेंगे, और वैल्यू सिलेक्ट रहने देंगे, अब f2 प्रेस करेंगे और ctrl+shift+enter दबाएंगे।
इस तरह हम देख सकेंगे की कौन सी Age कितनी बार रिपीट हुई है।

Person	Age	Unique	Frequency Result
A	33	33	3
B	23	23	1
C	54	54	2
D	33	65	1
E	65	76	1
F	76
G	33
H	54

38. PMT

=pmt(rate,nper,pv,fv)

किसी Loan की EMI को जानने के लिए PMT फॉर्मूले का इस्तेमाल करते हैं।

	A	B	C
1	Interest Rate	10%	Rate
2	Period in Month	10	Nper (No of Period)
3	Loan Amount	100000	Pv (Principal Value)
4	EMI	₹10,464.04	=PMT(B1/12,B2,-B3)
5	Total Paid	₹1,04,640.38	=B4*10
6	Total Interest	₹4,640.38	=B5-B3

39. Simple and Compound Interest

	A	B	C
1	Principal Value	500
2	Interest Rate Monthly	10
3	Time in Month	6
4	Simple Interest	300	=B1B2B3/100
5	Compound Interest	885.7805	=B1*(1+B2/100)^B3

40. Index

=index(array, row_no, column_no)

Row और Column की वैल्यू को INDEX फॉर्मूले के द्वारा ज्ञात कर सकते हैं।

	A	B	C
1	Time	Sony	Star Plus
2	8 am	movie	cricket
3	9 am	news	ramayan
5	10 am	cid	cartoon

Example : =INDEX(A1:C5, 3, 2)

Result : news

41. Match

=match(lookup_value, lookup_array, match_type)

Row या Column मे लिखी वैल्यू की Row या Column संख्या को MATCH फॉर्मूले की मदद से ज्ञात कर सकते हैं। match type मे 0 को exact match के लिये, 1 को less than तथा -1 को greater than के लिए इस्तेमाल करते हैं।

	A	B	C
1	Time	Sony	Star Plus
2	8 am	movie	cricket
3	9 am	news	ramayan
5	10 am	cid	cartoon

कॉलम संख्या ज्ञात करने के लिए –

Example : =MATCH(“news”, A3:C3, 0)

Result : 2

रो संख्या ज्ञात करने के लिए –

Row Example : =MATCH(“news”, B1:B5, 0)

Result : 3

42. Product

=product(number1, number2)

किसी प्रोडक्ट की मात्रा और प्राइस के अनुसार प्रोडक्ट का टोटल अमाउन्ट कैलकुलेट कर सकते हैं।

Example : =PRODUCT(quantity, price)

43. Sum Product

=sumproduct(range1, range2)

एक से अधिक प्रोडक्ट की मात्रा और प्राइस के अनुसार सभी प्रोडक्ट का टोटल अमाउन्ट कैलकुलेट कर सकते हैं।

Example : =SUMPRODUCT(quantity_range, price_range)

42. Vlookup

=vlookup(lookup_value, table_array, column_index_no, range_lookup)

किसी डाटाबेस की वैल्यूज को वर्टिकली प्रदर्शित कराने के लिए VLOOKUP फॉर्मूले का इस्तेमाल करते हैं। नीचे दी गई टेबल मे बताया गया है कि इसका इस्तेमाल कैसे करते हैं। lookup_value मे वह वैल्यू देंगे जिसे देखना है, table_array मे पूरा डाटाबेस सिलेक्ट करेंगे, column_index_no मे कॉलम नंबर देंगे जिस कॉलम मे lookup_value है और range_lookup मे 0 टाइप करेंगे। Vlookup का इस्तेमाल किसी बड़े डाटाबेस के किसी रिकार्ड को किसी स्पेसिफिक वैल्यू के द्वारा देख सकते हैं। जैसे किसी स्टूडेंट का पूरा रिकार्ड उसके Roll No के द्वारा देख सकते हैं।

43. Hlookup

=Hlookup(lookup_value, table_array, row_index_no, range_lookup)

Hlookup भी Vlookup की तरह ही कार्य करता है, अंतर इतना है कि किसी डाटाबेस की वैल्यूज को होरीजंटली प्रदर्शित कराने के लिए HLOOKUP फॉर्मूले का इस्तेमाल करते हैं। Vlookup मे column_index_no देना होता है और Hlookup मे row_index_no. नीचे दी गई टेबल मे बताया गया है कि इसका इस्तेमाल कैसे करते हैं।

2. Defined Names

Name Manager – इस विकल्प के द्वारा किसी रेंज व सलेक्टेड डाटा को किसी नाम से डिफाइन कर सकते है। Names Manager के द्वारा क्रीऐट किए गए नामों को एडिट, सर्च और डिलीट भी कर सकते हैं। क्रीऐट गए Names का उपयोग किसी फॉर्मूले के साथ भी कर सकते है। जैसे =SUM(A1:A10) के बजाय =SUM(Sale), यहाँ Sale एक Name है जिसे A1:A10 को सिलेक्ट करके डिफाइन किया गया है।

Define Name – इस विकल्प के द्वारा किसी रेंज को किसी Name से डिफाइन कर सकते हैं।

Used in Formula – इस फ़ंक्शन के द्वारा किसी फॉर्मूले मे क्रीऐट किए गए Names का उपयोग कर सकते हैं।

3. Formula Auditing

Trace Precedents – यह विकल्प फॉर्मूलेटेड सेल को तीर से इंगित करता है जो दिखाता है कि कितने Cell प्रभावित हैं।

Trace Dependence – इस विकल्प के द्वारा यह पता कर सकते है, कि किसी Cell का इस्तेमाल किस फॉर्मूलेटेड Cell मे किया गया है। यह फॉर्मूलेटेड Cell को तीर से इंगित करता है।

Remove Arrows – यह विकल्प सभी एरो को हटा देता है।

Show Formula – यह विकल्प किसी Calculated Cell के Formula को दिखाता है।

Error Checking – यह विकल्प फॉर्मूलों या Cells में होने वाली सामान्य त्रुटियों की जांच करता है।

Evaluate Formula – यह विकल्प एक डायलाग बॉक्स खोलता है जो प्रदर्शित करता है कि Formula ने Step by Step मान की गणना कैसे की।

Watch Window – यह विकल्प सक्रिय सेल में एक वॉच विकल्प जोड़ता है जो सूचना को सेल नंबर, शीट नंबर, वर्कबुक नंबर, वैल्यू और एप्लाइड फॉर्मूला के रूप में प्रदर्शित करता है।

4. Calculation

यह सेक्शन वर्कबुक या वर्कशीट में फॉर्मूलों के लिए Automatic Calculation को चालू या बंद करने के लिए उपयोग किया जाता है। यदि आप Automatic Calculation बंद कर देते हैं तो आप इस सेक्शन का उपयोग वर्तमान सेल या पूरी शीट को मैन्युअल रूप से गणना करने के लिए कर सकते हैं।

नोट – Microsoft Excel के अंतर्गत इस भाग मे आपने Microsoft Excel मे Formula Tab का उपयोग जाना। Microsoft Excel के अगले भाग मे आप Data Tab का उपयोग क्या होता है, यह जानेंगे।

अन्य पोस्ट –