ksherajat: Excel Formula

Excel

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, 22 April 2018

Excel Formula

No comments:

Post a Comment