ksherajat: Formula Excel

Formula Excel

izfr'kr fudkyus dh fof/k %& dqy vad 500 es 365 vad izkIr gq,A dk izfr’kr fudkyus ds fy, %& 365 xq.kk 100 cVk 500 %& lq= =365*100/500 mRrj 73 izfr’kr

Øekd & 2- esa lq= fy[ks =GA55A!N31

Øekd & 3-- esa x`g fdjk;k] /kkjk 10¼13&A½ lq= fy[ks ='Other Deduction'!B3

Øekd & 4-- esa ewy jkf'k es edju fdjk;k dks ?kVk;s =Q4-Q5

Øekd & 5 esa ¼i½euksjatu Hkrk ='Other Deduction'!B4

¼ii½ O;o;k; dj ='Other Deduction'!B5

Øekd & 5 esa Øekd & 5 dk nksuks dk ;ksx djs =M7+M8

Øekd & 6 esa =Q6-Q8

fdjk;s dk 30% = ROUND(M10*0.3,0)

x`g _.k ij C;kt =GA55A!W31+'Other Deduction'!B9

x`gdj ='Other Deduction'!B7

rhuks dk ;ksx =E12+H12+K12

'ks"k &@$¼7¼v½ ,oa ;ksx 7¼c½ dk½ =M10-M12

dqy 'ks"k &@$¼6,oa 7½ =Q9+Q13

vU; vk; & ='Other Deduction'!E3

ldy vk; & ;ksx ¼8$9½ =Q14+Q15

80C dk ;ksx =SUM(I19:I27)+SUM(O19:O27)

150000 rd dVkSrh =IF(O28<150001,ROUND(O28,0),150000)

=O19

/kkjk 80CCD (1B)

=IF('Other Deduction'!E6>50000,50000,'Other Deduction'!E6)

;ksx 11(A+B+C) =SUM(Q29:Q31)

dqy ;ksx 12 ¼ 1 ls 6 rd ½ =SUM(Q34:Q41)

dqy dVkSrh ( 11 + 12) =Q32+Q42

dj ;ksX; vk; ( 10 - 13 ) =(Q16-Q43)

dqy vk; dh jkf'k dks lEiw.kZ djuk ¼ nl ds xq.kd esa ½ /kkjk 288A =ROUND(Q44,-1)

5% =IF(GA55A!P4="No",IF(Q45<250001,0,IF(Q45>500000,12500,((Q45-250000)*0.05))),IF(Q45<300001,0,IF(Q45>500000,10000,((Q45-300000)*0.05))))

20 % =IF(Q45<500001,0,IF(Q45>1000000,100000,((Q45-500000)*0.2)))

30% =IF(Q45<1000001,0,((Q45-1000000)*0.3))

;ksx vk;dj =SUM(Q48:Q51)

NwV ?kkjk 87¼A½ =IF(Q45>350000,0,IF(Q52<2501,Q52,2500))

=IF(N49>350000,0,IF(N55<2501,N55,2500))

=IF(N47>350000,0,IF(N53<2501,N53,2500))

=Q52-Q53

f'k{kk midj 2% ,oa mPp f'k{kk ds fy, vf/kHkkj 1% (;ksx 3%)

=ROUND(Q54*0.03,0)

dqy vk;dj ¼3$4½ =SUM(Q54:Q55)

jkgr /kkjk 89 ds rgr ='Other Deduction'!E15

dqy 'ks"k vk;dj =Q56-Q57

;ksx dkWye 19 =F60+J60+L60+M60+O60

=IF(Q58>P60,"Income Tax Payable",IF(Q58<P60,"Income Tax Refundable","Income Tax Payble/Refundable"))

=IF(Q58>P60,Q58-P60,P60-Q58)

sheet1 es uhps dk vfUre lSy es lq= yxkuk gSaA

=IF(N58>N60,N58-N60,N60-N58)

=IF(N61>N63,N61-N63,N63-N61)

=IF(N63>N65,N63-N65,N65-N63)

=IF(N60>N62,N60-N62,N62-N60)

=IF(sheet4!E6>50000,50000, sheet4!E6)

/kkjk 80ccD(1B) ds vUrxZr 50000 dh NwV dk lq=

=IF(sheet4!L6>50000,50000, sheet4!L6)

=IF((X53="YES"),IF(N47>350000,0,IF(N47<350000,2500)))

0 to 250000 and 0%

=IF(E20<=B24,E20-0,B24)

250000 to 500000 and 5%

=MAX(IF(E20<=B25,E20-B24,B25-B24),IF(E20>B24,,0))

500000 to 1000000 and 20%

=MAX(IF(E20<=B26,E20-B25,B26-B25),IF(E20>B25,,0))

1000000 To above 30%

=MAX(E20>B26,E20-B26,0)

10% on Tax if Income > Rs 50 Lakh less than 1 Cr

Surcharge =IF(AND(E20>5000000,E20<10000000),E28*D29,0)

15% on Tax if Income > Rs 1 Crore

Surcharge =IF(E20>10000000,E28*D30,0)

Under sacation 87 A 2500 Less

=IF(N47<350001,0,IF(N47>350000,2500))

=IF(AND(E20>=B24,E20<=B25),MIN(E25,5000),0)

=E4-SUM(E5:E7)

=15000+(1600*12)

=E4-SUM(E5:E7)

=IF(AND(E18>5000000,E18<10000000),E26*D27,0)

=IF(E18<=B22,E18-0,B22)

=MAX(IF(E18<=B23,E18-B22,B23-B22),IF(E18>B22,,0))

=MAX(IF(E18<=B24,E18-B23,B24-B23),IF(E18>B23,,0))

=MAX(E18>B24,E18-B24,0)

=IF(Q45>350000,0,IF(Q52<2501,Q52,2500))

=IF(O28<150001,ROUND(O28,0),150000)

=IF('Other Deduction'!E6>50000,50000,'Other Deduction'!E6)

=SUM(I19:I27)+SUM(O19:O27)

=IF(GA55A!P4="No",IF(Q45<250001,0,IF(Q45>500000,12500,((Q45-250000)*0.05))),IF(Q45<300001,0,IF(Q45>500000,10000,((Q45-300000)*0.05))))

=IF(Q45<500001,0,IF(Q45>1000000,100000,((Q45-500000)*0.2)))

=IF(Q45<1000001,0,((Q45-1000000)*0.3))

=IF(N47>350000,0,IF(N49<2500,N49,2500))

=IF(N49>350000,0,IF(N50<2500,N50,2500))

=IF(N49>350000,0,IF(N55<2500,N55,2500))

=IF(N47>350000,0,IF(N53<2500,N53,2500))

=IF(N47>350000,0,IF(N52<2500,N52,2500))

=ROUND(Q54*0.03,0)

=SUM(Q48:Q51)

Tax on Total Income – E26

10% - D27

50 lakh above and 1 cror

Surcharge 10% =IF(AND(E18>5000000,E18<10000000),E26*D27,0)

Tax on Total Income – E26

15% - D28

1 cror above

Surcharge 15% =IF(E18>10000000,E26*D28,0)

1. M.s Excel dh sheet es Print Area dk Print fudkyuk %& M.s Excel dh sheet es Åij dh gSfMUx ;k dksbZ Hkh VSDLV fy[kk gqvk gks mlds vykok uhps ds Hkkx dk fizUV fudkyus ds fy,] igys M.s Excel dh sheet es ,fj;k dks lysDV djs] ckn ehuw ckj es & Page Layout ij Dyhd djs] & ckn Page Layout es Print Area fodYi ds ikl ,jks vkbdu ij Dyhd djs] & Set Print Area fodYi ij Dyhd djds lysDV djss] & ckn Print Preview ns[ks] vkidk fizUV fdl izdkj vk,xk] LØhu ij 'kks djsxkA

2. Set Print Area dks Cencel djus ds fy, Page Layout es Print Area fodYi ds ikl ,jks vkbdu ij Dyhd djs] & Clear Print Area fodYi ij Dyhd djds lysDV djss] & Set Print Area dks Cencel fd;k tkrk gSaA

3. 01-03-2017 dks Mar - 2017 ds QkWjesUV es fy[kuk %& fnukad 01-03-2017 dks fy[kdj alignment ds Hkkx es forment Cell

rhj ds vkbZdu ij ;k ekml dk jkbZV cVu Dyhd djs] forment Cell dk p;u djs] & Formet cells Mk;ykWx ckWDl es Number VSc ij Dyhd djus] & Category Hkkx esa Custom fodYi dk p;u djs] & mmm - yy

dk p;u djs] & ok djs] & 01-03-2017 dks Mar - 2017 ds QkWjesUV es fy[kk tk,xkA

1. tqykbZ ekg esa lq= yxkuk gSa 100 :Ik;s ij 3 izfr'kr =ROUNDUP(ROUND(1.03*D15,0),-1)

vFkkZr =ROUNDUP(ROUND(1.03*D15,0),-1) xq.kk vkbZdu ds ckn tqu ekg dk lSy ,Mªsl fy[kuk gSa =ROUNDUP(ROUND(1.03*D15,0),-1)

2. Lis'ky is spl. Allonce 25 % - ACB ds fy, dk lq= &

=ROUND(Baisec pay cell address/100*25,0) Enter key nck;s

=ROUND(D12/100*25,0) Enter key nck;s

3. 1 DA =ROUND(132%*E9,0)

=ROUND(136%*E10,0)

=ROUND(139%*E16,0)

4. 2 DA =ROUND(E10/100*132,0)

=ROUND(E10/100*136,0)

=ROUND(E10/100*139,0)

5. HDA 12 % ds fy, =ROUND((C10+D10)/12,0) Enter nck;s

6. C.P.F 10 % ds fy, =ROUND((D13+H13)/100*10,0)

7. 10% va'knku jkf'k tek ds fy, lq= =ROUND((D13+H13)/100*10,0) Enter nck;s

9. DA ARREAR =(ROUND(136%*D12,0)-ROUND(132%*D12,0))*3 Enter nck;s

=ROUND(E9/100*136,0)*3-ROUND(E9/100*132,0)*3

nqqljk DA ARREAR =(ROUND(139%*E13,0)-ROUND(136%*E13,0))*3

=ROUND(E13/100*139,0)*3-ROUND(E13/100*136,0)*3

nqqljk Mh-, ,fj;j ds fy, lq=

=(ROUND(139%*D16,0)-ROUND(136%*D16,0))*3+IF (L3="NO",0,IF(AND(X3>6,X3<10),ROUND(D26*0.03,0),0)) Enter nck;s

10. Lkyas.Mj yho pay dk lq=

=IF(AA7="NO",0,IF(AA11=2,E20,VLOOKUP(AA11,C9:E20,3,FALSE)))/2

11. Lkyas.Mj yho dk Mh-,- yxkus dk lq=

=IF(AND(AA11>2,AA11<10),ROUND(136%*E23,0),ROUND(139%*E23,0))

=IF(AND(AA11>2,AA11<10),ROUND(136%*E21,0),ROUND(139%*E21,0))

2500 dh NwV ds fy, =IF((X53="YES"),IF(N47>350000,0,IF(N47<350000,2500)))

2500 dh NwV ds fy, YES o NO es =IF(X53="YES",2500,0)

12. cksul dk lq= =IF(Z4=”YES”,6774,0) ENTER

13.

14. vk;dj ij 5 izfr'kr VSDLk fudkyus dk lq=

=ROUND((N47-250000)/100*5,0) Enter key nck;sA

=ROUND(N48/100*5,0)

15. nksuks la[;k es ls NksVh la[;k Kkr djus ds fy, lq=

lq= =MIN(L29,N20)

lq= =MIN(L29,150000)

lq= =MIN(2500,0)

16. nksuks cell address ds chp dh la[;k es ls NksVh la[;k Kkr ¼ekyqe½ djus ds fy, lq=

=MIN(C6:G99)

17. nksuks la[;k es ls cM+h la[;k Kkr djus ds fy, lq=
=Max(L29,N20)

=Max(C6:G99)

18. dqy dj ;ksx vk; ¼nl ds xq.kuQy esa½ /kkjk 288 A

19.tSls la[;k 816 ds vfUre nks vad dks nl ds xq.kuQy esa fy[kus ds fy,

=ROUND(A1,-1) Enter press

tSls 816 dh txg 820 la[;k gks tk,xh

¼vfUre la[;k 5 ;k 5 ls vf/kd gSa rks 15 dks 20 es fy[kk tk,xk½

¼vfUre la[;k 5 ls de gSa rks 14 dks 10 es fy[kk tk,xk½

20. tSls la[;k 25816 ds vfUre rhu vad dks lkS ds xq.kuQy esa fy[kus ds fy,

=ROUND(A1,-2) Enter press

tSls 25816 dh txg 25900 la[;k gks tk,xh

¼vfUre la[;k 50 ;k 50 ls vf/kd gSa rks 150 dks 200 es fy[kk tk,xk½

¼vfUre la[;k 50 ls de gSa rks 140 dks 100 es fy[kk tk,xk½

21. tSls la[;k 25816 ds vfUre rhu vad dks gtkj ds xq.kuQy esa fy[kus ds fy,

=ROUND(A1,-3) Enter press

tSls 25816 dh txg 26000 la[;k gks tk,xh

¼vfUre la[;k 500 ;k 500 ls vf/kd gSa rks 1550 dks 1600 es fy[kk tk,xk½

¼vfUre la[;k 1550 ls de gSa rks 1540 dks 1500 es fy[kk tk,xk½

22. cksul ds fy, lq= & =IF(Z4="Yes",6774,0)

23. DA ds fy, lq= & =ROUND(132%*D12,0) Enter press

=ROUND(136%*D13,0) Enter press

=ROUND(139%*D19,0) Enter press

24. 100 ij 3 izfr'kr osru o`f) tqykbZ ekg esa &

lq= =PRODUCT(1.03,D15) Enter key dks nck;s

=PRODUCT(1.03,D15(tqu ekg ds lsy ,Mªsl fy[kuk gS) ) Enter key dks nck;s

25. izfr’kr fudkyus dk lq= 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr] 1000000 ls Åij dh jkf’k ij 30 izfr’kr fudkyus dk lq=

1. 250000 ij ’kwU; izfr’kr =IF(N47<250000,N47*0%,0)

2. 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] =IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))

3. 500000 ls 1000000 rd 20 izfr’kr] =IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))

4. 1000000 ls Åij dh jkf’k ij 30 izfr’kr

=IF(N47>1000000,(N47-1000000)*30%,0)

lHkh dj ,d lkFk lq= yxkuk

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))+IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))+IF(N47>1000000,(N47-1000000)*30%,0)

=IF(AND(N49>250000,N49<500000),(N49-250000)*5%,IF(N49>500000,250000*5%,0))

=IF(N49<250000,N49*0%,0)+IF(AND(N49>250000,N49<500000),(N49-250000)*5%,IF(N49>500000,250000*5%,0))+IF(AND(N49>500000,N49<1000000),(N49-500000)*20%,IF(N49>1000000,500000*20%,0))+IF(N49>1000000,(N49-1000000)*30%,0)

26. izfr’kr fudkyus dk lq= 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr] 1000000 ls Åij dh jkf’k ij 30 izfr’kr fudkyus dk lq=

5. 250000 ij ’kwU; izfr’kr =IF(N47<250000,N47*0%,0)

6. 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] =IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))

7. 500000 ls 1000000 rd 20 izfr’kr] =IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))

8. 1000000 ls Åij dh jkf’k ij 30 izfr’kr

=IF(N47>1000000,(N47-1000000)*30%,0)

27. 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr]

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))

28. 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr]

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))+IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))

29. izfr’kr fudkyus dk lq= 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr] 1000000 ls Åij dh jkf’k ij 30 izfr’kr fudkyus dk lq=

/kkjk 87 A ds rgr NqV 2500 =IF(N47>350000,0,IF(N47<350000,2500))

Yes and No =IF(X53="YES",2500,0)

30. 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr]

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))

250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 ifr’kr] 500000 ls 1000000 rd 20 izfr’kr]

=IF(N47<250000,N47*0%,0)+IF(AND(N47>250000,N47<500000),(N47-250000)*5%,IF(N47>500000,250000*5%,0))+IF(AND(N47>500000,N47<1000000),(N47-500000)*20%,IF(N47>1000000,500000*20%,0))

31. izfr’kr fudkyus dk lq= 250000 ij ’kwU; izfr’kr] 250001 ls 500000 rd ¼250000 ij ½ 5 izfr’kr] 500000 ls 1000000 rd 20 izfr’kr] 1000000 ls Åij dh jkf’k ij 30 izfr’kr fudkyus dk lq=

32. /kkjk 87 , ds rgr 2500 dh NqV 350000 ¼ 3-5 yk[k rd½ dk lq= %&

IF(D12>350000,0,IF(D12<350000,2500)) ENTER = 2500

= IF(N47>350000,0,IF(N47<350000,2500))

33. lse la[;k ykus ds fy, lq= =H10 Enter press

lse la[;k ykus dk lq= =IF(L$19=0,0,L19)

lse l[;k ykus ds fy, ;g lq= vizsy efguk es fy[ks

=IF(J$12=0,0,J12) =IF(J$12=0,0,J16)

34. Surrender Leave =IF(L3="NO",0,IF(X3=3,D23,VLOOKUP(X3,B12:D23,3,FALSE)))/2

35. HRA dk lq= =IF($AC$4=20%,ROUND(0.2*D13,0),ROUND(0.1*D13,0))

HRA =IF($AC$4=20%,ROUND(0.2*D12,0),ROUND(0.1*D12,0))

HRA =IF($AC$4=20%,ROUND(0.2*D19,0),ROUND(0.1*D19,0))

36. HDA dk lq= %& =ROUND(D7+E7)/ 12,0) ENTER

=ROUND(D7+E7)/100*12,0) ENTER

=ROUND(D7+E7)*0.1,0) ENTER

37. HRA dk lq= =IF(AA2="NO",0,IF($AA$4=20%,ROUND(0.2*E9,0),ROUND(0.1*E9,0)))

38. Lys.Mj yho =IF(AA7="NO",0,IF(AA11=2,E20,VLOOKUP(AA11,C9:E20,3,FALSE)))/2

Lys.Mj yho dk Mh-,- dk lq= IF(AND(X3>3,X3<10),ROUND(136%*D26,0),ROUND(139%*D26,0))

=IF(AA14="YES",6774,0)

39. jkT; chek dh dVkSfr dk fu;ekuwlkj =IF(D12<8501,400,IF(D12<11001,550,IF(D12<18001,1100,IF(D12<28001,1550,2650))))

40. ;fn 'khV dk uke ns j[kk gSa rks lq= uke ns ldrs gSaa ='Other Deduction'!B10

41. Åij ds lSy dh lse la[;k ykus dk lq=] =Q13

uhps ds lSy esa ,Mªsl fy[kuk gSaA ckjh ckjh ls =Q12 =Q13 =Q14

42. /kkjk 87, ds rgr] 2500 dh NqV ysus o ugh ysus dk lq= (YES/No) esaA =IF(X53="YES",2500,0)

43. 100% dk lq= & Åij okys lSy ds cjkcj jkf’k yjus ds fy, =ROUND(N49/100*100,0)

44. DA Arrear Gpf =(ROUND(136%*D12,0)-ROUND(132%*D12,0))*3

45. Fixation Arrear =IF(I$12=0,0,ROUND(0.1*D28,0))

46. Salary Arrear =IF(I$12=0,0,ROUND(0.1*D29,0))

47. RPMF =IF($Z$2="No",IF(D19<7001,205,IF(D19<13001,341,IF(D19<21001,511,680))),0)

HRA =IF($AC$4=16%,ROUND(0.2*D13,0),ROUND(0.1*D13,0))

1. si - ds ekpZ ds lSy es lq= fy[ks =IF(D12<8501,400,IF(D12<11001,550,IF(D12<18001,1100,IF(D12<28001,1550,2650))))

2. =IF(D12<350001,0,IF(D12>350000,2500)) ENTER = 2500

3. lys.Mj yho ds fy, lq= &

ekpZ pay =IF(L3="NO",0,IF(X3=3,D23,VLOOKUP(X3,B12:D23,3,FALSE)))/2

ekpZ dk DA =IF(AND(X3>3,X3<10),ROUND(136%*D26,0),ROUND(139%*D26,0))

GPF dk lq=

CPF

ekpZ =IF($Z$2="Yes",ROUND((D12+H12)*0.1,0),IF($Z$2="No",IF(D12<9001,500,IF(D12<11001,650,IF(D12<15001,1100,IF(D12<20001,1450,IF(D12<24001,2100,IF(D12<28001,3300,IF(D12<31001,4100,IF(D12<45001,5200,IF(D12<65001,5700,6200))))))))),0))

RPMF - =IF($Z$2="No",IF(D12<7001,199,IF(D12<13001,331,IF(D12<21001,496,660))),0) =IF($Z$2="No",IF(D13<7001,199,IF(D13<13001,331,IF(D13<21001,496,660))),0) =IF($Z$2="No",IF(D14<7001,205,IF(D14<13001,341,IF(D14<21001,511,680))),0)

=IF($E$12=0,0,ROUND(D16/2,0))

formulas dk irk

https://exceljet.net/excel-functions/excel-min-function

https://exceljet.net/formulas

DA ,fj;j GPF esa tek djus ds fy, =IF($Z$2="Yes",ROUND((ROUND(136%*D12,0)-ROUND(132%*D12,0))*10%,0)*3,IF($Z$2="No",H24,0))

Formula-lq=

© QaD’ku %& QaD’ku dk iz;ksx djds Excel esa odZ’khV ds vUrxZr tfVy ls tfVy x.kukvksa dks iqjk djus ds fy;s QaD’ku dk iz;ksx fd;k tkrk gSaA QaD’ku dk Formula fy[krs le; dks"Bd ij [kkl rksj ls /;ku nsosaA ;fn dks"Bd eSp u gks rks ,jj eslst fn[kk,xkA dbZ ckj lwpuk fn;s fcuk ml xyrh dks lgh dj nsrk gSaA

1- QaD’ku dk uke] mnkgj.k & sum ¼QaD’ku½ 2- vkxqesZV ¼Number 1 ½

ü QaD’ku dk uke %& tks iz;ksx esa fy;k tk jgk gSa] fd QaD’ku D;k djus okyk gSaA & mnkgj.k & sum

ü vkxqesZV %& vkxqesZV ¼cell address½ ges’kk NksVk dks"Bdks esa fy[ks tkrs gSa vkSj ;fn QaD’ku esa ,d ls T;knk vkxqesZV gks rks dkWek dk iz;ksx fd;k tkrk gSaA

ü QaD’ku dk uke VkbZi djsaA

ü vksiu dks"Bd VkbZi djsaA

ü eku VkbZi djsa ;k cell Address VkbZi djsa] ;k Dyhd djsA

ü ,d ls T;knk cell address dks fy[kuk pkgrs gSa rks dkWek dk iz;ksx djsa] eku VkbZu djsa ;k mfpr cell ij Dyhd djsaA

ü close dks"Bd VkbZi djsaA

ü Enter key nck,aa] QaD’ku ls izkIr ifj.kke cell esa vk tk;sxkA

© Mk;ykWx ckWDl }kjk QaD’ku fy[kuk %& Formula ehuw ij Dyhd djsa ª insert function uked dekUM ij Dyhd djsa ª insert function uke dk ckWDl LØhu ij [kqydj lkeus vk tk;sxkA ckWDl esa fyLV es ls ,d category ij Dyhd djsA ckWDl esa ,d QaD’ku dks pqus] ftl QaD’ku dks bLrseky djuk pkgrs gSaA mls <+<us ds fy;s LØksy ckj dk iz;ksx djuk i³sA ª ok cVu ij Dyhd djsA QaD’ku dk Mk;ykWx ckWDl fn[kkbZ nsxkA lHkh vkxqZesaV ds fy, text box esa eku ;k cell address fy[ksA QaD’ku ds vkxqZesaV fy[kus dk dke iqjk gksus ds ckn ok djus ls Mk;ykWx ckWDl cUn gks tk;sxkA ifj.kke izkIr gks tk;sxkA

© dSydqys’ku djuk %& Excel esa x.kuk dk vk/kkj la[;kvksa dks tks³uk ;k fQj mudh dkmUVhx djuk gksrk gSaA la[;kvks dks fdl rjg tks³rs gSa] fdl rjg ls ?kVkrs gSa] vkil esa mudk fdl rjg ls xq.kk fd;k tkrk gSaA vkSj ,d & nwljs dks vkil esa Hkkx dSls nsrs gSaA

ü dkWye A, B, C, D esa la[;kvks dks VkbZi ¼,UVj½ djsaA

ü dkWyeks ds ml jsUt dks lysDV djsa] tgka rd la[;kvks dks VkbZi fd;k gSaA

ü stetus bar iVVh ij ekml IokbaVj ys tkdj jkbZV Dyhd djs] ,d 'kksVZdV ehuw vksiu gksxkA ehuw lwfp esa N% vksilu Avwrage, count, numrical count, max, min sum gksrs gSaA ftl rjg dh x.kuk djuk pkgrs gSa] mlls lEcfU/kr fodYi ij Dyhd djsA

ü stetus bar iVVh ij ifj.kke izkIr gks tk;sxkA vFkkZr stetus bar iVVh ij 'kks djsxkA

© Sum ¼tks³uk½ QaD’ku dk iz;ksx ofVZdy vkSj gksfjtsUVy just esa djuk %& Excel esa 'khV esa ml just dks lysDV djsa] ftlls la[;kvks dks buiqV ¼VkbZi½ fd;k gSaA lysD’ku djrs le; ,d [kkyh dkWye vkSj ,d [kkyh Row dks lkFk esa lysDV djsaA Alt ds lkFk = key dks nck,a] ;k Home ehuw esa Auto sum ij Dyhd djsaA 'kksVZdV rjhdk & Alt+> key ds lkFk = key nckus ls] ifj.kke izkIr gks tk;sxkA

ü vxyk fjdkMZ ns[kus ds fy;s Mkmu ,jks key ij Dyhd djsaA

ü fiNyk fjdkMZ ns[kus ds fy;s up ,jks key ij Dyhd djsaA

ü 10 fjdkMZ dks Nks³dj 11 os fjdkMZ ij tkus ds fy;s LØksy ckj ds uhps fLFkr LØkWy ckWDl vkbdu ij Dyhd djsaA

ü 10fjdkMZ dks Nks³dj ihNs tkus ds fy;s LØksy ckj ds Åij fLFkr LØkWy ckWDl vkbdu ij Dyhd djsaA

© ,d Formula dks dbZ ckj iz;ksx djuk %& Excel esa fdlh ,d lsy esa iz;ksx fd;s x, Formulo dks odZ'khV ds nwljs lsy esa iz;ksx djus ds fy;s key bord 'kksVZdV dk iz;ksx djds fd;k tkrk gSaA Ž ftl lsy esa Formula fy[kk gSa ml lsy ij Dyhd djds lysDV djsa] vkSj fQj ctrl ds lkFk c key dks nck;sA Ž ftl lsy esa nl Formula dks ys tkuk gSa ml lsy ij Dyhd djds lysDV djsa] Ž ctrl ds lkFk v key dks nck,aA Formula dke djds ifj.kke lkeus vksiu gks tk;sxkA

© Addition - tks³uk] subrtraction - ?kVkuk] multiplication - xq.kk djuk] Division - Hkkx nsuk] Exponentiation - ?kkr] Equal = - cjkcj] >> Greater than - c³k] >>= Greater than of Equal to - c³k ;k cjkcj] << Lessthan - NksVk] <<= Lessthan of Equal to - NksVk ;k cjkcj] <> Not Equal - cjkcj ughA

FORMULA – CHART

Øea la[;k	x.kuk	QkasewZyk	mnkgj.k
1	tkasM+	=sum(a1:z1) enter	tgk (a1:z1) lasy ,Mªsl gSA
2	ckdh	=(b5-z5) enter	"
3	xq.kk	=(b3*z3) enter	"
4	Hkkx	=(b2/z2) enter	"
5	izfr'kr	=(b4/z4*100) enter	"
6	tkasM++++++ vkSj ckdh lkFk	=(b6+c6+d6)-(e6+f6) enter	"
7	tksM+ vkSj xq.kk	=(b5+c5+d5)*(a1+z1) enter	"
8	xq.kk o ckdh	=(b3c3)-(d3e3) enter	"
9	xq.kk dj Hkkx nsuk	=(c2*d2)/(f2) enter
10	tks³dj Hkkx nsuk	=(b5+c5+d5)/(e5) enter
dqy la[;k esa ls fdUgh nks dkaWyekasa dh la[;k dks vkil es xq.kk djuk o mleaaaaaaaaaas nks dkaaasyeks dh la[;k dks tksM+ dj dqy la[;k es ls ?kVkuk o cdk;k la[;k fudkyuk =(B2)- (c2d2)- (e2+f2+g2) enter (B2)dqy 600-(c2d2)-(e2+f2+g2) Enter

© cjkcj = dk fpUg nsdj QkWewZyk ¼lq=½ fyxkus dh 'kq:vkr gksrh gSaA o QaD’ku dk uke VkbZi djsa] dks"Bd VkbZi djsa] eku VkbZi djsa] ;k lsy address fy[ksA ,d ls T;knk gks rks dkWek iz;ksx djsa o eku VkbZi djsa] Dykst dks"Bd VkbZi djs] Enter nck;sA

ü ;g lq= fy[kdj Enter nckus ls tqM+ tk,xsaA

=sum(a1+b1+c1) ;k shift nckdj A,B,C ij Dyhd djus ls dks"Vd es A1+B1+C1 vk tk,xsaA ckn es dks"Vd cUn djds Enter nckus ls tqM+ tk,xsaA

ü nqljk lq= %& =sum(a1:c1) lq= fy[kdj Enter nckus ls tqM+ tk,xsaA

ü rhljk lq= %& m.s. Excel es [kM+h la[;k fy[kdj fups ,d [kkyh dkWye NksM+dj lHkh dkWye dks lysDV djus ds ckn Home es Auto-sum ij Dyhd djus ls fups tqM+ tk,xsaA

ü Åij ls fups dh rjQ tksM+us dk rjhdk %& =sum(f2:f6) lq= fy[kdj enter nckus ls tqM+ tk,xsaA =20+12+45+10 enter nckus ls tqM+ tk,xsaA

ü Auto sum (Excel es) :- Excel ds 'khV esa Row ds vUnj uke fy[kdj mlds vkxs cell es vadks es la[;k fy[kus ds ckn tks³us ds fy,] la[;k ds Last cell dlZj ls Dyhd djus ds ckn =sum( igys okys cell es tks la[;k fy[kh gqbZ gSa ml cell esa dlZj ls Dyhd djsa] ckn ml cell ij dlZj ys tkus ls $ iYl dk fpUg 'kks djsxkA ml cell ij dlZj ys tkdj Laft cVu nckdj] [khapdj Last cell rd ys tkdj Nks³us ij =sum(C2:G2 fy[kk 'kks djsxk] ckn dks"Bd cUn½ Enter nckus ls ml Row dh tks³ gks tk,xhA fups okyh Row dh la[;k dks tks³us ds fy, Total okys column esa la[;k ij dlZj ls Dyhd djsa] ckn ml cell ij dlZj ls tkus ls ,d $ iYl dk xgjk fpUg 'kks djus ij dlZj dk Left cVu nckdj fups fd rjQ [khapdj vfUre Row rd dlZj Nks³us ls lHkh dh tks³ gks tk,xhA

	uke	a	b	c	d	e
2	jtr	85	45	=(a2*b2)

lq= fy[kdj Enter nckus ls xq.kk gks tk,xkA

© Round Qhxj ¼Excel½ es%& fdlh Hkh lsy es n'keyo la[;k Round up djus ds fy, bldk iz;ksx fd;k tkrk gSaA dk tSls 20.85 dks 21 djus ds fy, lq= %& =Round(c5,.5) lq= fy[kdj Enter nckus ij Round Qhxj es vk tk,xsaA tSls 20.85 dks 21 o 20.45 la[;k gSa rks 20 gks tk,xhA

© Division :- fMohtu fudkyus ds fy, lq= %&=IF(K3>=60, "FIRST",IF(K3>=48,"SECOND",IF(K3>=36,"THIRD",IF(K3>36,IF(K3<36,"FALL",)))) Enter nckus ij FIRST, SECOND, THIRD, o FALL, fMohtu fudy tk,xkA 60 ds igys = dk fpUg yxkus ij]ª 60 izfr’kr gksxk rks izFke LFkku vk tk;sxk

© Sheet2 ds fdlh Hkh la[;k dks sheet1 ;k fdlh Hkh sheet esa la[;k dks Add djus dh fof/k%& sheet ds lsy esa Dyhd djs] Sheet1 esa lq= fy[kuk gksxkA lq= %& =(sheet2!M22) Enter key dks nckus ls M22 dh la[;k add gks tk,xhA

© Excel es sheet 1, column b8 (dksbZ Hkh) dh la[;k sheet 3 ;k 4 ;k dksbZ Hkh sheet es tkdj ekywe djus dk rjhdk %& lq= & =sheet1!b8 (sheet o cell dk address) fy[kdj Enter nckus ls sheet 1, column b8 dh la[;k vksiu gks tk,xhA

ü NksVh abcd ds uke dks c³h ABCD fy[kuk %& =upper(E3 lsy dk ,Mªsl) fy[kdj Enter nckus ls] NksVh abcd ds uke dks c³h ABCD gks tk,xkA tSls & mohan dk & MOHAN gks tk,xkA column ds lHkh uke dks NksVh abcd ds uke dks c³h ABCD djus ds fy, QkseZwyk yxk;s gq, lsy ij Dyhd djus ds ckn lsy ds jkbZV lkbZM es] fupys fdukj ij dlZj ys tkus ij ,d NksVk $ iYl dk fpUg 'kks djus ij Dyhd nckdj fups f[kpdj ys tkdj Nks³us ls dkWye ds lHkh 'kCn NksVh abcd ds uke dks c³h ABCD gks tk,xkA

ü c³h ABCD ds uke dks NksVh abcd fy[kuk %& =Lower(E3 lsy dk ,Mªsl) Enter nckus ls] c³h ABCD ds uke dks NksVh abcd gks tk,xkA tSls & MOHAN dk & mohan & gks tk,xkA column ds lHkh uke dks c³h ABCD ds uke dks NksVh abcd djus ds fy, QkseZwyk yxk;s gq, lsy ij Dyhd djus ds ckn lsy ds jkbZV lkbZM es] fupys fdukj ij dlZj ys tkus ij ,d NksVk $ iYl dk fpUg 'kks djus ij Dyhd nckdj fups f[kpdj ys tkdj Nks³us ls dkWye ds lHkh 'kCn c³h ABCD ds uke dks NksVh abcd gks tk,xkA

ü Upper ¼NksVh abcd ds uke dks c³h ABCD fy[kuk½ and Lower -¼c³h ABCD ds uke dks NksVh abcd fy[kuk½ djus ds ckn Ž ;fn ,d word ¼’kCn½ dks gVkus ls nksuks cell ls ,d lkFk gV tkrs Ž bls jksdus ds fy, rjhdk %& Upper and Lower djus ds ckn Ž fy[ks uke dks lysDV dj copy djsa Ž igys okys uke dks lysDV djsa Ž ekml dk jkbZV cVu Dyhd djus ij paste special ij Dyhd djus ls paste special uke dk ckWDl LØhu ij vksiu gks tk,xkA paste special ckWDl es Values ij Dyhd dj ok cVu djus ds ckn nksuks 'kCn ,d lkFk ugh gVsxsaA

© Proper :- -uke dk igyk 'kCn c³k] ckdh ¼'ks"k½ 'kCn NksVh abcd esa fy[ks tk,xsaA =proper(E3 lsy dk ,Mªsl) Enter nckus ls] tSls & mohan dk & Mohan fy[kk tk,xkA column ds lHkh uke dks djus ds fy QkseZwyk yxk;s gq, lsy ij Dyhd djus ds ckn lsy ds jkbZV lkbZM es] fupys fdukj ij dlZj ys tkus ij ,d NksVk $ iYl dk fpUg 'kks djus ij Dyhd nckdj fups f[kpdj ys tkdj Nks³us ls dkWye ds lHkh 'kCnks dk igyk 'kCn c³k ckdh ¼'ks"k½ 'kCn NksVh abcd esa fy[ks tk,xsaA

© Formula bar :- QkWeZwyk ckj dk iz;ksx djds rsth ls x.kuk djus ds fy;s Formula ehuw ij Dyhd djsa ’ cell esa = cjkcj VkbZi djs] ’ ;fn 8 esa 5 dk xq.kk djuk pkgrs gSa rks = ds fpUg ds next esa 8x5 VkbZi djsaA ’ F9 QaD’ku key dks nck,aa eku cell esa fn[kkbZ nsxkA

© Excel dhsa Sheet esa fy[ks gq, MsVk dks fupys jkso ;k lSy esa ykus dk rjhdk %& ftl jkso ;k lSy esa MsVk fy[kk gqvk gSa mlds uhps okys jkso dk lysDV djus ds ckn ctrl+D dks nckus ls lso gks tk;sxkA lsy ds vUnj ykus ds fy, uhpys lsy esa fDyd djus ds ckn ctrl+D dks nckus ij Åij okys lsy dk eSVj uhps okys lSy esa vk tk;sxkA

© uhpss okys jkso dk MsVk Åij okys lSy ;k jkso esa ys tkus dk rjhdk %& ftl jkso ;k lSy esa MsVk fy[kk gqvk gSa mlds Åij okys jkso dk lysDV djus ds ckn fill ij Dyhd djs] Up fodYi dk p;u dkus ij Åij okys jkso esa MsVk lso gks tk;sxkA lsy ds vUnj ykus ds fy, Åij okys lsy esa fDyd djus ds ckn Fill ij Dyhd djs] Up fodYi dk p;u djus ij MsVk Åij okys lSy esa lso gks tk;saxk

© Excel dhsa Sheet esa Column esa fy[ks MsVk dks jkbZV lkbZM ds Column esa MsVk ,UVj djuk %& fy[ks gq, Column ds jkbZV lkbZM okys] Column dks lysDV djus ds ckn Ctrl+R nckus ls MsVk ,UVj gks tk;sxkA lSy ds vUnj djus ds fy, jkbZV lkbZM okys lSy esa Dyhd djus ds ckn Ctrl+R nck;sA

© Excel dhsa Sheet esa Column esa fy[ks MsVk dks yS¶V lkbZM ds Column esa MsVk ,UVj djuk %& fy[ks gq, Column ds yS¶V lkbZM okys] Column dks lysDV djus ds ckn Fill ij Dyhd djsa] Left fodYi dk p;u djus ij] Left lkbZM MsVk ,UVj gks tk;sxkA Left lkbZM okys lSy ds vUnj djus Dyhd djus ds ckn Fill ij Dyhd djsa] Left fodYi dk p;u djus ij] Left lkbZM MsVk ,UVj gks tk;sxkA

© Excel esa lq= yxkuk %& Excel dh sheet esa igys ls yxk gq, lq= ds lkFk ª fdlh vU; cell dk lq= lkFk esa tks³us ds fy, Formula Bar esa Dyhd djs] ª $ iYl dk fpUg yxk;s]ª Cell dk address VkbZi djs] ;k Cell esa Dyhd djs] ª Enter key dks nckus ij cell dk lq= ª igys ls yxk gq, lq= ds lkFk tq³ tk;sxkA

ksherajat

Sunday 29 April 2018

Formula Excel

No comments:

Post a Comment