Fundamentals of Computers & Information Technology

3. HyperLink dk mi;ksx Hkh dj ldrs gSA

4. Screen Tips dks Show or Hide Hkh dj ldrs gSA *
5. Text & Graphics dk ckj&ckj iz;ksx dj ldrs gSA
6. Auto Text dh Entry Hkh Insert dj ldrs gSA
7. Error Hkh Check dj ldrs gSA

Work Sheet - MS Excel

Work Sheet Basics :
INTRODUCTION :

A
Microsoft Excel ,d vk/kqfud Worksheet Programme gS ftls ekbdzkslksQ~V dksjiksjs'ku] vesjhdk us fodflr
N
I S H
fd;k gSA lkj.khc) lwpukvksa dks rduhfd Hkk"kk esa Worksheet dgrs gSA tgkW lHkh lwpuk,W Rows & Columns esa O;ofLFkr
gksrh gSA Excel ,d ,slk lksQ~Vos;j gS tks Table cukus ] mUgs fofHkUu izdkj ls O;ofLFkr djus] fizUV djus rFkk mudh

R
lwpukvksa ij vk/kkfjr xzkQ cukus ds dke vkrk gSa Excel dh lgk;rk ls bruh cMh Vscy cukbZ tk ldrh gS ftlesa djhc 65-

K
What is Spread Sheet :-
B Y
536 Rows & 256 Columns gks ldrs gSa A vr% bl Software dks Electronic Spread Sheet Hkh dgrs gSaA

A L
,d Spread Sheet iafDr;ks vkSj dkWyeksa dk ,d tky (Grid) gksrk gS ftls Worksheet Hkh dgrs gSA

I
E C
Spread Sheet Program ds fuEufyf[kr dk;Z gSA
1. Make Spread Sheet : blesa iafDr;ks vkSj dkWyeksa esa Text vkSj la[;k,W dks Vkbi fd;k tkrk gS A vkSj la[;k,W dks

S P Vkbi fd;k tkrk gSA vkSj la[;kvksa ij x.kuk,W dh tkrh gSA

U P 2. Data Base :- blesa lwpuk dks ,d List dk :i nsdj Data ij vk/kkfjr fdz;k,W dh tkrh gSA
3. pkVZ cukuk %& lkjf.k;ksa esa Vkbi fd;s x;s MkVk dks xzkQ ds :i esa O;Dr djukA
4. Formatting djuk :- Excel esa Chart Wazards lqfpdk Hkh miyC/k ftuds }kjk viuh Worksheet dks lqUnj
<ax ls Formating djds fizUV dj ldrs gSA
Excel Data dks fofHkUu fLFkfr;ksa esa O;ofLFkr djrk gSA ,d Worksheet 65536 Rows vkSj 256 Columns ds tky
(Grid) dks iznf'kZr djrh gS Column Heading A,B,C, ...... ok AB vkSj blh izdkj vkxs odZ'khV dk vfUre dkWye IV
gksrk gS Rows ds Row Heading 1,2 ......... 65536 rd gksrs gSA
Row C Column ds izfrPNsn ls ,d vk;rkdkj ckWDl curk gS ftls Cell dk ,d Cell Address gksrk gS tks
mlds dkWye Number vkSj Row Number ls feydj curk gS tSls A1,A2, B4 etc blfy;s Worksheet ds lcls
igys Cell dk Address A1 vkSj lcls vfUre lSy dk Cell Address IV 65536 gksxkA

- 129 -
 Fundamentals of Computers & Information Technology
ftl Cell esa Data Type djrs gS og Active Cell ,oa Current Cell dgykrk gSA Worksheet esa ,d Cell ls nwljs
Cell esa tkus ds fy, Arrow Keys dk bLrseky fd;k tkrk gSA
MS Excel dks izkjEHk djuk :-
Start - Programs - MS Excel
Start - Programs - MS Office - MS Excel Icon ij nks ckj DyhdA

MS Excel - dks Ldzhu ij vkSj mlds Hkkx

MS Excel dh Ldzhu ij nks foUMks iznf'kZr gksrh gSA
1. Application Windows
2. Document Windows Or Work Sheet Window

A) Application Windows :-
;g ckgjh Window gksrh gS ftlesa MS Word ds leku gh Menu Bar , Standard Tool Bar, Formating
N A
S H
Tool Bar iznf'kZr gksrh gS ysfdu MS Excel esa ,d vU; Tool Bar Formulla Bar Hkh iznf'kZr gksrh gSA
I
MS Excel
K R X

B Y
Book 1
I A L
E C
S P
U P
1. Formulla Bar :-
;g izk;% Formating Tool Bar ds uhps iznf'kZr gksrh gSa ;g ToolBar rhu Hkkxksa esa cVh gqbZ gksrh gSA
A) Reference Area
B) Middle Area
C) Edit Area
2. Status Bar :-
;g Application Window dh uhpyh ifV~Vdk gksrh gS ftlesa fofHkUu lwpukvksa ds lUns'k iznf'kZr gksrk gSA tSls ;g
;fn Ready iznf'kZr djrh gSa mldk vFkZ ;fn Ready iznf'kZr djrh gS mldk vFkZ gS fd Excel Spread Sheet esa Data

- 130 -
 Fundamentals of Computers & Information Technology
Store djus ds fy, rS;kj gSA ;fn Adit 'kCn gS rks mldk vFkZ Excel la'kks/ku dh voLFkk esa gSaA bl izdkj vusd lans'k
le;&le; ij Status Bar iznf'kZr djrk gSA

B) Document/Work Sheet Window :-

;g Application Window ds vUnj iznf'kZr gksrh gS vkSj orZeku esa [kqyh gqbZ Work Sheet dks iznf'kZr djrh gSA
blds Hkh fuEu Hkkx gSA
1. Title Bar : ;g Window dh lcls mijh ifV~Vdk gksrh gS tks Work Book dk uke iznf'kZr djrh gSA Excel esa
File dks Work Book dgrs gSA loZizFke File dk uke Book 1 vkrk gS o bldk Extension .XLS gksrk gSA
2. Sheet Tab : Document Window ds uhpys Hkkx esa mifLFkr Sheet 1, Sheet 2 vkfn Sheet Tab dgykrs gSA
Sheet Tab ij Dyhd djds vki ,d WorkSheet ls nwljh esa tk ldrs gSA
3. Work Sheet Tab Scrol Button : Work Sheet Tab ds ck;h vkSj pkj ckWDl gksrs gS mUgs Work Sheet Tab
Scrol Button dgrs gS buds mi;ksx ls Work Sheet Tab vkxs fiNs f[kldk;s tk ldrs gSA

N A
4. Column Heading : Work Sheet ds izR;sd dkWye ds 'kh"kZd] Column Heading gksrs gSA A,B,C,,D ........
vkfn ds }kjk dkWye ds 'kh"kZ ij iznf'kZr gksrs gSA

I S H
:i esa gksrk gSA
K R
5. Rwo Headings : Work Sheet dh izR;sd iafDr dk 'kh"kZd tks ck;h rjQ gksrk gSA ;s 1,2,3, .................. ds

B Y
I A L
bl izdkj Work Sheet ds vUrxZr dk;Z fd;k tkrk gSA
1 Creating Worksheet - loZizFke creat esa dke djus ds fy, ,d work sheet dks creat dgrs gSA blds fuEu steps
gS -

E C
P
Step 1 loZizFke file ds New ij Dyhd djrs gSA rks ,d ubZ work book [kqy tkrh gSA ftldk uke book 1 vkrk gSA

S
U P
Step 2 mlds ckn tks data mlds fy[kus gS mudks fy[krs gS vksSj tc dk;Z iwjk gks tkrk gS rks ges file ds save command
ij click djrs gS rks ,d dialog Box vkrk gS ftlls Directory dks select dj ysrs gS rFkk file dk uke nsdj save
Button ij click dj nsrs gS rks ,d ubZ Work sheet cu tkrh gSA
2 Enterny data in work sheet -
Work sheet es Entry djus ds fy, ges loZizFke cell painter dks ml cell ij ys tkuk gksxk tgk ij entry
djuh gS mlds ckn key board ls entry djrs gSA Excel es entry rhu izdkj ls dh tkrh gS -
(A) Value Entry -
Value Entry vadks ls feydj curh gS bles 0 ls 9 rd ds lHkh vad o fpUg - fpUg rFkk n'keyo vk ldrs gS
tc value entry dh tkrh gS rks og 9 v{kj ftrus pkSMs cell es nka;h vkSj tkrh gSA
(B) Label Entry -
tc dksbZ entry fdlh alphabet ls vkjaHk gksrh gS rks og label cell ds cka;h vkSj vkrh gSA

- 131 -
 Fundamentals of Computers & Information Technology
(C) Formalla Entry % -
Formall Entry dks la[;kvks Cell Address rFkk fuEufyf[kr xf.krh; Operators dh lgk;rk ls cuk;k tkrk
gSA Fpr,a;;a Entry (=) ls izkjaHk gksuh pkfg, -
Example = (A+ S+ C+ ) \3 mijksRr cell entry ,d forulla entry gS rFkk ;fn bls cell A4 ij Vkbi fd;k gS rks
ogkWa cell A1 A2 ,oa A3 dh value dk vkSlr vkidks
Ms Excel es Data dks Hkjus dk dk;Z vPNh rjg ls fd;k tk ldrk gSA ;g dk;Z Key Board dh lgk;rk ls
fd;k tkrk gSA Data Board dh lgk;rk ls fd;k tkrk gSA Data Numbric & Albhatic gks ldrs gSA
(4) Text Dates , & Alphanumeric :-
Excel esa fdlh Text dks fy[kuk rFkk ;g dk;Z Alphanumeric gksrk gS fdlh Text dks Ket Board dh lgk;rk
ls fy[kr gSA Date Function }kjk Date Hkh Mky ldrs gSA
(5) Saving & Quitting Work Sheet :-

ftls fuEu izdkj djrs gS %&

N A
Excel fdlh ubZ cukbZ xbZ File dks fdlh uke ls Save djus ds fy, bl Command dk iz;ksx fd;k tkrk gSA

File
S H
Save rks fuEu Dialob Box appear gksrk gSA

I
;fn Work Sheet dks Close djus ds Hkh vusd rjhds gSA
K R
ftlesa Directory uke dks Select djds File Name Box esa File dk uke fy[kdj OK ij Dyhd dj nsrs gSA

B Y
(1) File Exit

I A L
(2) Title Bar esa Close Button ij Dyhd A

E C
(3) Alt + F4 ls Hkh Work Sheet dks Close dj ldrs gSA

P
bl izdkj ls Excel dks Quit fd;k tk ldrk gSA

S
U P
Opening :
Opening and Moving Around an Exiting Work Sheet

bl Command dk iz;ksx igys ls cuh gqbZ Qkby dks [kksyus ds fy, fd;k tkrk gSA bls djus ij ,d Dialog
Box Appear gksrk gSA ftlesa fuEu Function gksrs gSA
1. File Name : blds uhps Files dh lwph gksrh gS tks Files MS Excel ds vUrxZr [kksyh tk ldrh gSA
2. Directory Name : blesa Directory Name pquk tkrk gSA ftlesa gekjh Files fLFkr gksrh gSA
3. Drive : ml Drive dk uke pqurs gS ftlesa gekjh Files Save dh tkrh gSA
4. List of File Type : ftl Software dh Files dks [kksyuk gS blesa 19 izdkj dh Files lwph gksrh gS] mldks
pqurs gSA
5. Read Only : File dks dsoy Read Only djus ds dke vkrh gS blds Select dj ysrs gSA
6. vUr esa Open ij Dyhd dj nsrs gS rks og File Open gks tkrh gSA

- 132 -
 Fundamentals of Computers & Information Technology
Moving :
Work Sheet dks Move dj ldrs gS ftl Sheet ij dke djrs gSA bldks Mouse Pointer ds }kjk
Worksheet ds uhps Sheet1, Sheet2 ......... gksrk gSA mudks Move dj ldrs igys okyh ckn esa o ckn okyh igys yk
ldrs gSa

Toolbars and Menus, Key Board ......................

Toolbars :
bl Option dk iz;ksx Excel dh Screen ij iznf'kZr gksus okyh Toolbars dk fu/kkZj.k djus ds fy, fd;k tkrk
gSA bls djus ij Screen ij Toolbar Dialog Box Appear gksrk gSA

Tool Bars X

N A
I S H
K R
blesa 13 izdkj dh Tool Bar gksrh gS muds lkeus Check Box cus gksrs gS ftudks Ldzhu ij ykuh gksrh gS mudks Select
dj ysrs gSA bl Dialog Box esa uhps rhu cVu gksrs gSA Color Tool Bar ftlls Colored Tool Bar Appear gksrh gSA

B Y
Large Button dks Select djds Tool Bar ds Icon cMk dj ldrs gSA vkSj Tool Bar cukuh gS rks Cutomize djds

Menus & Shortkut Keys :-

I A L
New ij Dyhd djrs gSA vius uke dh ToolBar cuk ldrs gSA o vUr esa OK cVu ij Dyhd dj nsrs gSA

C
MS Excel esa Tltle Bar ds uhps Menu Bar gksrh gSA bl Menu Bar esa 9 fofHkUu izdkj ds Pull Down Menus gksrs
E
P
gSA bl Menus dks Mouse Pointer }kjk Dyhd djds vFkok Alt Key + js[kkafdr Menus okyh Key dks nckus ij Pull

S
U P
Down Menu dks ns[k ldrs gSA rFkk Arrow Key }kjk dk;Z dj ldrs gSA

MS Excel
File Edit View Insert Format Tools Date Window Help

(1) File Menu :

bl Menu esa File Management ls lEcfU/kr leLr Option gksrs gSA ;g Software dk igyk Menu gksrk gSA ;g
Menu 6 Hkkxksa esa cVk gksrk gSA rFkk blds 13 Option gksrs gSA budks Excel ds ge Command Hkh dg ldrs gSA

1. First Part : ubZ Qkby cukus] iqjkuh Qkby [kksyus rFkk Qkby dks cUn fd;k tk ldrk gSA

- 133 -
 Fundamentals of Computers & Information Technology
2. Second Part : Qkby dks Save djus dk dk;Z fd;k tkrk gSA rFkk Save As esa Save djus ds lkFk mlh Qkby
dh Copy Hkh dj nsrk gSA ftlls ge ml Qkby dks u Nwdj nwljh Qkby esa mlh izdkj dk dksbZ nwljk dk;Z dj
ldsaA
3. Third Part : bl Hkkx esa Qkby dh tkap dh tk ldrh gSA
4. Fourth Part : bl Hkkx esa Printing ls lEcfU/kr ,oa Page Setup dk dk;Z Hkh fd;k tk ldrk gSA vkSj ge
Paper Size ,oa Paper dh Margins vkfn Set dj ldrs gSA
5. Fifth Part : blesa pkj ;k mlls vf/kd File dh lwph tks Current gksrh gS crkrk gSA ftudks Select djds
Enter Key ls [kksy ldrs gSA
6. Sixth Part : bldks Exit Command gksrk gSA ftlls Excel ls ckgj vk ldrs gSA

New, Open, Close

Save, Save As, Save As Web Page, Save Workspace
Find File, Summery
N A
Page Setup, Print Preview, Print, Print Report
Exit

I S H
(2) Edit Menu :
K R
B Y
bl Menu esa File dh Editing ls lEcfU/kr Command gksrs gSA ;g Menu ikWap Hkkxks esa foHkkftr gksrh gSA

I A L
1. First Part : dk;Z dks lekIr djus o iqu% pkyq djus dk dke djrs gSA
2. Second Part : blesa Cut Copy Paste dk;Z fd;s tk ldrs gSaA Undo Redo Sellect All tSls dk;Z fd;s tk
ldrs gSA

E C
P
3. Third Part : Cell esa j[sk eku dh nwljs Cell esa Copy djus Transfor djus feVkus vFkok Work Sheet dks

S
U P feVkus dk dk;Z fd;k tkrk gSA
4. Forth Part : bldk iz;ksx 'kCn dks <qa<+us mls nwljs 'kCn esa cnyus rFkk Cell ij ;k Row o Column ij tkus ds
fy, fd;k tkrk gSA
5. Fifth Part : budk iz;ksx mUgh fLFkfr;ksa esa gksrk gS tc gekjs }kjk Work Sheet esa fdlh Object esa vU;
Package }kjk dksbZ Object Insert fd;k x;k gSA

(3) View Menu :

1. First Part : blesa Formulla, Status o Toolbar dks Screen ij iznf'kZr dj On/Off dj ldrs gSA

- 134 -
 Fundamentals of Computers & Information Technology
2. Second Part : blls Work Sheet dks iwjh Moniter Screen ij iznf'kZr dj ldrs gSA
3. Third Part : Work Sheet ds vkdkj dks ifjorZu djuk o izn'kZu dks vko';drk vuqlkj fu/kkZj.k dj ldrs gSaA

(4) Insert Menu :

bl Menu esa Inserting ls lEcfU/kr dk;Z fd;k tkrk gSA ;g pkj Hkkxks esa foHkkftr gksrk gSA
1. First Part : Cells , Rows o Columns dks Work Sheet esa tksMus ds dke vkrk gSa
2. Second Part : budk iz;ksx Work Sheet Chart o Macros dks Worksheet esa tksMus ds dke vkrk gSA
3. Third Part : bldk iz;ksx fofHkUu dk;kZ dks lEiu djus ds fy, fd;k tkrk gSA
4. Fourth Part : Work Sheet esa fdlh Hkh Photo vkSj Object dk iz;ksx djus ds fy, fd;k tkrk gSA

N A
I S H
K R
(5) Format Menu :
B Y
I A L
blesa Work Sheet dh Formating ls lEcfU/kr dk;Z fd;k tkrk gSA
1. First Part : blesa Cells, Row Column 'khV dh Formating dh tk ldrh gSA

E C
2. Second Part : bl dk iz;ksx Excel esa iwoZfu/kkZfjr Formating djrs gSaA

S P
3. Third Part : Work Sheet esa Insert fd;s x;s Object ,oa Picture ds Placement ds fy, fd;k tkrk gSA

U P
(6) Tool Menu :
blesa Work Sheet ds Protection vkSj Option vkfn ls lEcfU/kr dk;Z fd;s tkrs gSA ;g Menu ikWp Hkkxks esa
foHkkftr gksrk gSA
1. First Part : ftudk iz;ksx ge vius dk;Z esa dh xbZ xyfr;ksa dk lq/kkjus esa dj ldrs gSA
2. Second Part : bldk iz;ksx x.kuk ls lEcfU/kr gksrk gSA
3. Third Part : Work Sheet o Work Book dks Protect ,oa dk;Z ljy cukus ls lEcfU/kr dk;Z fd;s tkrs gSA
4. Fourth Part : Macros dks Executed vFkok mldk fuekZ.k djus esa fd;k tkrk gSA

- 135 -
 Fundamentals of Computers & Information Technology
5. Fifth Part : MS Excel dks Customize djus dk dk;Z fd;k tkrk gSaA

(7) Data Menu :

bldk iz;ksx [kqys gq, Data ds lUnHkZ esa fd;k tkrk gS rFkk blls Data dks Sort Hkh fd;k tk ldrk gSA blds rhu
Hkkx gksrs gSA ftlesa fuEu Command gksrs gSa A

(8) Window Menu :

N A
S H
bl Menu dk iz;ksx vius dk;Z dks vko';drk vuqlkj iz;ksx djus ds fy, fd;k tkrk gSA

I
New Window : ubZ Window tksM+ ldrs gSA

K R
Arrange Window : Work Sheet dh ,d ls vf/kd Window dk Moniter ij izn'kZu djuk A

B Y
I A L
E C
P
(9) Help Menu :

S
U P blds }kjk MS Excel ds ckjs esa Help ys ldrs gSA bldh Shortcut Key F1 gSA ftldh lgk;rk ls ge vius
Formulla vkfn esa Hkh lq/kkj dj ldrs gSA

Working With Single & Multiple Work Book

Window }kjk Hkh dj ldrs gSA
(1) Coping :

- 136 -
 Fundamentals of Computers & Information Technology
MS Excel esa ,d File dh nwljh File esa copy djus ds fy, Copy Command dk Use fd;k tkrk gSA ;g
Some Duplicate Copy curh gsA blds fu, Shortcut Key Ctrl + C dk Hkh iz;ksx dj ldrs gSA blds fy, File
Selected gksuk t:jh gSA rFkk ckn esa Paste djrs gSaA
(2) Move Or Coy :
bl Option dk iz;ksx djus ij fuEu izdkj dk Dialog Box [kqyrk gSA

Move or Copy

Excel esa [kksyh xbZ ubZ File ,d Work Sheet u gksdj 16 Work Sheet dk ,d lewg gS] blfy, bls Work Book
dgktkrk gSA blds nks eq[; Hkkx gS A

nsrs gS rks orZeku File ds Content Move gks tk;saxsaA

N A
To Book : blds Pull Dwon Button ij Dyhd djds Work Book Select djrs gSA o iq'k Button OK ij Dyhd dj

S H
Before Sheet : Sheet pqu ysrs gS ftl 'khV ij ges orZeku 'khV esa Content Copy djuk gS rks Create a Copy dks

I
pqudj OK dj nsrs gSaA

K R
(3) Removing :
B Y
I A L
tc ge Excel esa File dk uke Change djrs gS rks Save As Command dk iz;ksx djrs gSa bl Command
ij Dyhd djrs gh ,d Dialog Box vkrk gSA

E C
loZizFke mlesa ftl Directory esa uke j[kuk pkgrs gS mldks Select djrs gSA rFkk Drive Hkh Change dj ldrs

P
gSA File dk uke fy[k dj OK Button ij Click djrs gSa rks File dk uke Change gks tkrk gSA

S
U P
(4) Adding & Deleting :
Excel esa fdlh Data dks Add ,Oka Delete djuk gS rks Data fyk[kdj mudks Save dj nsrs gSA rks ,d Work
Book cu tkrh gS ftlesa gekjk Data Add tc rd jgrk gS rc rd Work Book dks fdlh us Delete ugh fd;k gSA
fdlh Hkh Work Book dks Delete djuk gSA rks igys mls Select djds Delete Button }kjk Delete dj ldrs gSA
bl izdkj gesa Excle esa Working Single & Multiple Work Sheet ij dj ldrs gSA fdlh Hkh Work
Book dh Copy, Move Add Delete & Renaming dj ldrs gSA ftlls vusd Commands dk iz;ksx fd;k tkrk gSA

Working with Formullas & Cell Referencing

Formullas :
Excel esa Formulla dk cgqr egRo gS tc ge dksbZ x.kuk djuk pkgrs gS rks dj ldrs gS x.kuk dk Result ns[kuk gS rks
Excel esa Formulla ges'kk Equal (=) ls 'kq: gksrk gSA

- 137 -
 Fundamentals of Computers & Information Technology
Formulla ge nks izdkj ls Hkj ldrs gS ;k ns ldrs gSA
(1) lk/kkj.k Text dh rjg Vkbi djds : blesa ftl Cell esa Result pkfg, mlesa Formulla fy[kdj dj ldrs gSA
(2) Mouse o Key Board ls : Cell ds irs Mouse ls Select djds Hkj ldrs gSA 'ks"k fpUg Vkbi djrs gSA
Insert Function

Formula Operater :
Formulla cukus ds fy, ge Operater dk iz;ksx djrs gS tSls %&

(1) vad xf.krh; Operator (Arithmatic Operator )

(+) = (Addition) tksM+uk (-) = (Subtraction) ?kVkuk
(*) = (Multipication) xq.kk (/) = (Division) Hkkx
(%) = (Percentage) izfr'kr (^) = (Expontations) ?kVkuk

N A
(2) rqyuk Operator (Comparision Operator )

I S H
(=) = (Equal To) cjkcj
(>=) = (Greater Than or Equal To)
(>) = (Greater Than) ls T;knk

K
(<) = (Less Than) ls de R
(<=) = (Less Than or Equal To)
B Y
(<>) = (No Equal To)
(3) ikB~; Operator (Text Operator )

I A L
& = Ampersand bldk iz;ksx Text tksMus ds fy, fd;k tkrk gSA

C
tSls %& ;fn Cell B4 esa ---------------- Hkjk gS o Cell esa Plane rks formulla = b4 & b5 esa Place rks Formulla =B4
E
P
&B5 rks vk;sxkA

S
U P
(4) Cekk Referencing
(lsyks dk lUnHkZ nsuk) Formulla fy[krs le; ge T;knkrj vdsys lsyks dk o dHkh dHkh lsyks dh jastks dk lUnHkZ nsrs gSSA
cell Reference nks rjg ls ns ldrs gSA (1) lh/kk Vkbi dj ds (2) lsy dks select djds - muds chp es dksye (%) dk
fpUg yxk;k tkrk gS tSls B2 % C6 bl rjg ds Reference dks ;k rks lh/ks Vkbi djds ;k ekml }kjk [khpadj ------------- ns
ldrs gSA
Cell Reference rhu izdkj ds gksrs gSA

(1) fujis{k (Abdolute) -

bles fdlh lSy ds irs ij iafRr Row la[;k vkSj Column uke ls igys Mkyj fpUg yxk;k tkrk gS tSls $B $2 ;g dHkh
cnyrk ugh cfYd ogh jgrk gSA
(2) Relative -

- 138 -
 Fundamentals of Computers & Information Technology
bl cell reference es irs lh/ks fn;s tkrs gSA ,sls lUnHkZ ml irs dks udy djrs le; fLFkfr ds vuqlkj cny tkrs gSA
mnkgj.k - ;fn vkius cell D4 es dksbZ formalla A5*A5 Hkjk gS ftles lSy A5 dk reference fn;k x;k gSA rks mls E4
es udy djus ij ;g formulla B5 * B5 gks tk;sxk vkSj D5 es udy djus ij ogh A6 * A6 gks tk;sxkA
(3) Mixel Cell Reference -
bles ;k rks dsry iafRr dh la[;k ;k dsoy collumn dk uke fujis{k (Absolute) gksrk gS tSls $B2 ;k B$2 bles udy
fd,s tkrs le; fujis{k Hkkx ogh jgrk gS nwljk Hkkx lUnHkZ dh rjg cny tkrk gSA

* Working With Range *

MS Excal es ,d cell ls nwljs cell rd select djrs gS rks og cells dh Range dgykrh gSA
Creating Range :-

N A
Cell dh Range Rows ,oa Column ls feydj curh gSA ftlds create djus ds fy, cell pointer dks cell es
ys tkrs gS tc ml cell es Data Hkjrs gS rks Address Bar es ;k formulla bar es show gksrk gSA Formulla Bar es

S H
vadh; o ik<; nksuks fy[k ldrs gSA fdlh nks Range ds chp dkWyu (%) dk sign vius vki vk tkrk gSA tSls A1 B6

I
etc-
Editing Range :-
K R
Y
bl cell range dh editing Hkh dj ldrs gS bldh editing djus ds fy, og range select gksuh t:jh gS bldks
B
Selecting Range :-
I A L
copy,cut,move o paste dj ldrs gSA bldh size change dj ldrs gS bles color Hkj ldrs gSA

E C
tc ge fdlh Range dh Editing djrs gS rks og Select gksuk t:jh gSA ftl Cells dks Select djuk gSA

P
Mouse Pointer Cell ds izkjEHk esa ys tkdj Mouse dks nck;s j[kdj vfUre Cell ;k tgkW rd Select djuk gS ys tkrs

S
U P
gSA rks Cell Range Select gks tkrh gSA bldks Shift + Arrow Key ls Hkh Selet dj ldrs gSA

* Formating of Work Sheet *

MS Excel esa Work Sheet dh Formatinhg djus ds fy, vusd Command dk iz;ksx fd;k tkrk gSA
(1) Auto Format :
bl dek.M dk iz;ksx iwoZ izk:iksa ds vk/kkj ij Table dh Formating djus ds dke vkrk gSA bls djus ij fuEu
izdkj dk Dialog Bpx Appear gksrk gSA

Format Cell X

- 139 -
 Fundamentals of Computers & Information Technology

Table Format : blesa 17 izdkj ds fofHkUu Table ds izdkj dh lwph gksrh gSA ftles ls Select dj ldrs gSA Sample :
mlesa Table dk izk:i ;k Preview fn[kkrk gSA
Options : bl ij Dyhd djrs gS rks Format To Apply Hkkx Hkh tqM tkrk gSA uhps 6 Option esa vko';drk vuqlkj pqu
ldrs gSA

(2) Column Width :-

bl dek.M ds }kjk ge Column dh pkSMkbZ dks Change dj ldrs gSA bls djus ij fuEu izdkj dk Dialog Box
Apear gksrk gSA ftlesa Column Width nsrs gS tks Inches esa ysrk gSA blesa tc Text fy[kk tkrk gSaA rks Text ds
vuq:i mldh pkSMkbZ Double Click djds Hkh dj ldrs gSA

N A
S H
(3) Alignment, Character Style, Date Format, Border & Colores and Currency Sign :

I
ij Screen ij ,d Format Cell Dialog Box Appear gksrk gSA
K R
bu lHkh dh Formating djus ds fy, Format Menu ds Cell Command dk iz;ksx fd;k tkrk gSA bls djus

B Y
Format Cell

Category

I A L
Format Codes
X

E C OK

S P Cancel
Help

U P Delete

Cade

blesa 6 eq[; Option gksrs gS Number dks Select djus ij mij tSlk Dialog Box [kqyrk gSA ftlesa ls tSls : Date
dks Select djrs gSA
Date Format Change djuk :
Date dks Select djrs gS Format Codes esa Date ds Sample fn[kkrs gSA mu ls tSlk pkfg, mldks Select
djds OK cVu ij Click dj nsrs gS rks Date Format Change gks tkrk gSA
Currency Sign Change ------- :-

- 140 -
 Fundamentals of Computers & Information Technology
blds fy, Hkh Category es ls Currency dks Select djrs gS rks Sample Format Code esa fn[kk;s tkrs gSA
ml es ls Select djds OK ij Dyhd djrs gS rks Currency Change gks tkrh gSA

Alignment Change djuk :-

mij crk;s x;s dialog Box es ls Alignment dks Select djrs gS rks Cell ij Work Book esa fy[h tkus okyh
lwpuk dk Alignment fu/kkZfjr djrs gSA
Horizontal :- blesa ls Left, Right, Center, General, Fill Justify es ls Select djrs gSA tSlk pkfg,

Vertical : bles ls Top, Bottom, Center Justify es ls tks pkfg, mldks Select djrs gSA
Orientation : Orientation {kSfrt m/okZ/kj tSlk pkfg, mudks Select djrs gSA vUr esa OK ij Dyhd djrs gS rks
Alignment Change gks tkrk gSA
Font Change (Character Style) djuk "

N A
S H
Format Cell Dialog Box esa Font dks Select djrs gS rks fuEu izfdz;k gksrh gSA

I
R
Font : Font es ls fdlh dks Hkh Select dj ldrs gS ;fn Hindi Font gS rks Hindi Font Select dj ysrs gSaA

K
Font Style : tSlh Font Style pkfg, oSlh ys ldrs gSA
B Y
I A L
Regular, Italic, Bold, Bold Italic etc.
Size : Font Size Hkh Change dj ldrs gSA Effect Hkh ------------------- ldrs gSA bldk Preview Hkh ns[k ldrs gSA vUr esa

E
PL ij Dyhd djrs gSA
C
P
Border & Color Pattern Change djuk :

S
U P
tc ge Format Cell ds Option Border ij Dyhd djrs gS rks fuEu dk;Z dj ldrs gSA
Border : tSls Border pkfg, Outline Left Right Top Bottom etc esa gS ml Side Border ns ldrs gSA

Border Sytle : Border dh Line dSlh ysuh gS mldks Select dj ysrs gSA
Color : Border dks Colored cukuk gSA rks Color Option es ls Color Select dj ysrs gSA vUr esa OK ij Click
dj nsrs gSA
bl izdkj ls MS Excel esa Work Sheet dh Formatting dj ldrs gSA

Previewing & Printing WorkSheet :

- 141 -
 Fundamentals of Computers & Information Technology
tc Hkh Excel esa dk;Z djrs gSaA Data dks vPNh rjg ls Print djokuk gS rks mlds fy, lcls igyk dk;Z Page dks
Set djuk pkfg, blds fy, Page Setup Command dk iz;ksx fd;k tkrk gSA

File Page Setup

Page Setting :
blesa ,d Dialog Box Appear gksrk gSAftlesa pkj Option gksrs gSA tks bl izdkj gSA
1. Page : ftles Page dk Orientation (a A Portrait B LandScape) es ls pqu fy;k tkrk gSA blds Paper
Size Hkh Select dj yh tkrh gSA
2. Margings : blesa Page gh margin set dh tkrh gSA ftles top, bottom, laft, right o center page
Horizontal o Vartical vkfn pqus tkrs gSA
3. Header/Footer : bl Hkkx esa Page ds mij o uhps D;k Print djuk gSA
Header : Header dks Select fd;k tkrk gSA
N A
Footor : blds Hkh Pupup Botten ij Dyhd djds Footer dks Select dj ysrs gSA
I S H
4. Sheet : blesa fuEu dk;Z dj ldrs gSA
(1) Print Area - ftldks Print djuk gS mldh Range blesa nh tk ldrh gSA
K R
(2) Print Titles - blesa Rows to Repaeat at Top rFkk Columns to Reapeat at Left esa Title ns ldrs gSA
B Y
(3) Page Order - blds }kjk Page dk Order dSls nsuk gS ns ldrs gSa

I A L
E C
tc Page Setting gks tkrk gSA rks mlds Print fudkyus ls igys mldk Preview ns[krs gSA blds fy, bl Dialog Box

P
ds Print Preview Push Button ij Dyhd dj ldrs gSA rks ,d Dialog Box Appear gksrk gSA ftlesa Next Preview,

S
U P
Zoom Print vusd Button gksrs gSA mldk Preview gS rks mls Printer ij Hkst ldrs gSA
Print : blesa Printer Name Select djrs gSA
Print What - esa ftldk Print ysuk gSA o tSlk ysuk bldks Select djrs gSaA
Copies Box -esa ftruh Printer ls dkWih fudkyuh gSA mldh la[;k fy[k ldrs gSA
Page Range - es ls Select djrs gS o vUr esa OK ij Dyhd dj nsrs gSA

Graphs & Charts :

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

- 142 -
 Fundamentals of Computers & Information Technology
MS Excel esa fdlh Work Sheet esa Hkjs gq, Data ds vk/kkj ij vusd izdkj ds Chart cuk ldrs gSA blds fuEufyf[kr
Steps gSA tks bl izdkj gSA
• loZizFke og Woor Sheet [kksfy, ftldk Chart cukuk gSA mlds Select dj yhft,A On This Sheet / As
New Sheet
• Insert Menu : Chart ij Dyhd rks ,d Dialob Box Appear gksrk gSA ftlesa og Range nh tkrh gSA tgkW ij
Chart cukuk gSA o Next Button ij Dyhd dj nsrs gSA
• rks ,d Chart Wazard Step 2 of 5 Appear gksrk gSA ftlesa fofHkUu rjg ds Chart gksrs gSA o Next ij Dyhd
dj nsrs gSA
• rks Chart Wazard Step 3 of 5 Dialog Box Appear gksrk gSA Step 2 of 5 es tSlk pkVZ Select fd;k mlds
gh fofHkUu izdkj gksrs gSA mudks Select djrs gSA o Next Button ij Dyhd djrs gSA
• ,d vksj Chart Wazard Step 4 of 5 Appear gksrk gSa ftlesa ,d rjg Chart dk Sample fn[kkrk gSA o nwljh

•
rjg Data Series in o row rFkk o column es ls ,d dks Select djrs gsa o Next ij Dyhd dj nsrs gSaA

N A
rks Chart Wizar step 5 of 5 Dialog Box Appear gksrk gSA blesa vusd dk;Z djrs gS tSls%&
(i) Add o Legend Yes or No
I S H
(ii) Chart Title Sales Report

K R
(iii) Axis Title
Category Data
B Y
Value 50

I A L
vkfn nsdj Finish Button ij Dyhd djrs gh gekjk pkVZ Screen ij tgkW geus Range Select dh Fkh ogkW ij

E
Appear gks tkrk gSA C
S P bl izdkj Wizard }kjk pkVZ cuk ldrs gSA

U P
Various Charts Type :
Graphics Adding : ge Drawing Tool Bar dh lgk;rk ls Excr; dh Work Sheet ds fdlh Hkkx esa ;k pkVZ esa Hkh dksbZ
fp= vkd`fr cukdj tksM ldrs gSA blds fy, Insert Menu esa Object Command dk iz;ksx fd;k tkrk gSA
Printing Chart : fdlh Work Sheet dks Niokrs gS ;k pkVZ dk dksbZ Hkh Hkkx Print djokuk gS rks ;g dk;Z Page Setup
ds Sheet esa dj ldrs gSA Print Area esa ftl Part dks Print djokuk gS mldh Rangnsrs gS ;g Setting djus ds ckn
Print Command nsxs rks mlh Range dk Hkkx Print gksdj vk tk;sxkA
: Data Base :
(1) What is Data Base :
Data Base lwpukvks dk ,d ,slk O;ofLFkr laxzg gksrk gSA ftles ge fdlh lwpuk dks ljyrk ls izkIr dj ldrs gSaA
Example : Telephone Directory------------------------------------------------------------- blesa dbZ fjdkMZ ,oa Field gksrs gSA
Data Base Range : fdlh WorkSheet dh ftl Range esa Data Hkjk gksrk gS mls DataBase Range dgk tkrk gSA

- 143 -
 Fundamentals of Computers & Information Technology
Reocrd : Excel esa DataBase Range dh ,d Row dh ,d Reocrd ekuk tkrk gSA
Field : Excel esa fdlh DataBase ds izR;sd dkWye dks ,d Rield dgk tkrk gSA

(2) Creating a Data Base :

Excel esa Data Base cukus ds fy, Field ,o Records dks Hkjk tkrk gSA
Entering Data : Excel esa Data Base esa MkVk Hkj ldrs gS] lq/kkj ldrs gS rFkk Format dj ldrs gSA ;g dk;Z fdlh
Cell esa djrs gSA
(i) Field Hkjuk : Data Base cukrs le; loZizFke ,d ubZ File [kksyrs gS mlesa Field tSls Name Fathername Roll
No, Hindi, English Total etc. Hkjrs gSA blesa ls Character dkas Excel ges'kk ck, rFkk Numeric Value dks nk,W
fn[kkrk gSA ijUrq Field esa ckn esa Editing }kjk lgh dj ldrs gSA
lkekU;r;k ge fdlh Hkh u;s Reocrd dks nwljs ;k vfUre Record ds uhps tksMrs gSA ;g dk;Z Data Form dh lgk;rk ls
djrs gSA

N A
S H
ysfdu tc chp esa Reocrd tksMuk gS rks ,d [kkyh Row Mkydj Record tksM ldrs gSa ;fn ge chp esa Field dks tksMuk

I
pkgrs gS rks ,d Column dks Mkydj tksM ldrs gSA

K
bl izdkj ge Row ,oa Column tksM dj chp esa Hkh Data Hkj ldrs gSA R
B Y
A
1
2
B C

I
D

A L E F G H I

E
3
4 C
S P 5
6

U P 7
8
9

Sorting Data Base : Database dks Sorting djus ds fy, Sort Command dk Use fd;k tkrk gSA
Data - Sort djrs gh ,d Dialog Box Appear gksrk gS ftlesa rhu Hkkx gksrs gSA

SSort By

- 144 -
 Fundamentals of Computers & Information Technology

rks bles lHkh izfdz;k iwjh djds OK ij Dyhd djrs gSA rks tks igys Database cuk;k x;k gS og Sort Name (A to Z)
FName (Z to A) o Roll Nol (A to Z) gks tk;saxsaA
Filtering a Database :
DataBase dks mlds dk;Z vuqlkj Filtering djds ns[kus ds fy, Filter Command dk Use fd;k tkrk gSA blds
Hkh rhu Option gksrs gSA
• Auto Filter : bldk iz;ksx Lo;a Filter djus ds fy, fd;k tkrk gSA bldk iz;ksx djus ij Work Sheet ds
igys Row ds izR;sd Field Column ij Pull Down Arrow esa fofHkUu izdkj ds fodYi tqM tkrs gSA ftldk
iz;ksx ftl Record dks ns[kuk gS ns[k ldrs gSA
• Show All : ;g Range dh lHkh Entries n'kkZ;sxkA
•
A
Advance Filter : ge ;g crk ldrs gS fd Filter djus ds ckn ifj.kke mlh Range esa ns[kus gSA ;k fdlh u;s
LFkku ij Copy djuk gSA
N
I S H
Query dk 'kfCnd vFkZ iwNrkN djuk MS Access esa Qirtu Table ls fdlh fo'ks"k izdkj ds Records esa lafpr lwpukvksa dks
tkuus dh fof/k gSA

K R
Qirtu es Database rS;kj djus ds fy, Query Command dk iz;ksx djrs gSA
New - New Query rks ,d Dialog Box Appear gksrk gSA
B Y
Data Base : Prewe
I A L
E C
S P
U P
blesa nks fodYi gksrs gSA Query Wizard ,oa New Query Query Wizard }kjk &
• rks ,d Dialog Box vkrk gSA ftles Query Wizard Select djrs gS o OK dj nsrs gSA
• rks U;k ---------------- Wizard Appear gksrk gS ftles ls og Table pqu ysrs gS ftldh Query cukrs gS o Next ij
Dyhd dj nsrs gS A
• ,d u;k Archive Wizard vkrk gS ftlesa This Value esa Field lwph pqu ysrs gSA
IS esa 'krZ (=) ;k ----------- izlUu gS pqu ysrs gSA
This Value esa Up Type djrs gSA o Next ij Dyhd djrs gSA

- 145 -
 Fundamentals of Computers & Information Technology
• rks ,d Archive Wizard vkSj Appear gksrk gS ftles Database feyrk gSA ij Record fooj.k ns[k ldrs gS bl
izdkj ls ge Query }kjk Hkh Database cuk ldrs gSA Database New Query ls Hkh cuk ldrs gSA

Function :
MS Excel esa Function ,d ,slh 'kfDr'kkyh lqfo/kk gS ftudh lgk;rk ls tfVy ls tfVy x.kuk,W dj ldrs gSA
blds ,sls lSdMks Function fn, gS ftudh lgk;rk ls baftfu;fjax x.kuk Hkh dh tk ldrh gSA

Function dks djus ds fy, = dk Sign yxkuk t:jh gS Function dk;Z nks izdkj lds dj ldrs gSA
(1) ,d lh/kk Function dks Formulla Bar esa fy[kdj
(2) ,d nwljk Insert Menu - Function ij Dyhd djus ij Function ds vkxs ------------------------------------------------------------------------------------
A

fdlh Function ds nks Hkkx gksrs gSA

N A
= Function dk uke rFkk Arguments

I S H
tSls %& Sqrt(16) rks bldk oxZewy fudkyrk gSA vkSj Answer 4 feysxkA
;fn Argument A rF;A ,d ls vf/kd gks rks mudks Comm(,) nsdj dj ldrs gSA
K R
tSls %& SUM(A2,52, B3)
B Y
I A L
ge rF; ds :i esa nwljs Functions dk Hkh iz;ksx dj ldrs gSA

C
tSlsa %& Sqrt (Sum(C3 : c8), = Round(Sqrt(c5),2)

E
P
blesa C3 ls C8 rd dh la[;kvks dk ;ksx fudkysxk rFkk ;ksx dk oxZewy nwljs mnkj.k esa C5 esa Hkjh la[;k dk oxZewy fudky

S
U P
;g n'keyo ds ckn --------------------------------------------- A

1. Data Base
2. Data and Time
3. Logical
4. Maths & Trignometry
5. Statistics
6. Text
7. Financial
8. Information
9. Look Up and Reference

- 146 -
 Fundamentals of Computers & Information Technology

(1) Date & Time :-

(i) Date ( ) :- fdlh Particular Data dks n'kkZrk gSA
Syntax :- Date (year, month,,Day)
Year :- o"kZ 1900 ls 2076 rd ns ldrs gSA
Month : blesa Monitor dh la[;k nsrs gSaA
Example : =Date (98,4,11)
rks vk;sxk April 11, 1998
;k = Date (90.14,2) rks Februery 2, 1991

(ii) Day ( ) :- blds Function ds {kjk fdlh rkjh[k dk fnu fudky ldrs gSA
Syntax :- Day
=Day ("4 Jan") Equal 4
N A
I S H
(iii) Month ( ) :- bldh lgk;rk ls ge eghuk Kkr dj ldrs gSA
Syntax :- Month(Searial Number)
K R
=Month("6 May") Equal 5
B Y
=Month(366) Equal 12
=Month(367) Equal 1
I A L
E C
P
(iv) Year ( ) :- blds }kjk fdlh Hkh rkjh[k esa ls o"kZ Kkr djus ds fy;s fd;k tkrk gSA

S
U P
Syntax :- Year(Searial Number)
=Year("7/5/90") Equal 1990
=Year(_________) Equal 1981

(v) Time ( ) :- fdlh Serial Mp ds }kjk Particular Time dks Return djrk gSA
Syntax :- Time(Hour,Minute,Second)
=Hour(0 to 23)
=Minute(0 to 59)
=Second(0 to 59)
Example :-
=Time(12,8,9) Equal to 12.00 PM

- 147 -
 Fundamentals of Computers & Information Technology
=Time(16,48,10) Equal to 4:48:10 PM

(vi) Second ( ) :- blds }kjk ge Time esa Second ns[k ldrs gSA
Syntax :- Second(Searial Number)
=Second(0.01) Equal 24
=Second(4.02) Equal 48

(vii) Minute( ) :- blds }kjk ge Time esa Minute ns[k ldrs gSA
Syntax :- Minute(Searial Number)
= Minute() Equal 24
= Minute(4.02) Equal 48

(viii) Hour( ) :- blds }kjk ge Time esa Hour ns[k ldrs gSA
N A
Syntax :- Hour (Searial Number)

I S H
= Hour (0.7) Equal 26
= Hour (29747.7) Equal 16
K R
= Hour ("3:30:30 PM") Equal
B Y
Syntax :- Now ( )
I A L
(xi) Now( ) :- blds }kjk ge Current Date & Time ns[k ldrs gSA

= Now ( ) 31778.52053
E C
P
(x) Today ( ) :- blds Function ds }kjk ge Current Date ns[k ldrs gSA

S
U P
Syntax :- Today ( )

(xi) Weekday ( ) :- blds Function ds }kjk ge fdlh Date dk fnu ;k okj fudky ldrs gSA
Syntax :- Weekday ( )

: .................................. :

(1) ABS ( ) :- ;g Function fdlh eku ;k Number dh Absolute Value gh Show djrk gSA
Syntax :- ABS (No)
= ABS (No)
= ABS (2) Equal 2

- 148 -
 Fundamentals of Computers & Information Technology
= ABS (-2) Equal 2
= ABS (A1) Equal 16

(2) INT ( ) :- ;g Function Decimal Number dh la[;k dks gh crkrk gSA

Syntax :- INT (No)
= INT (8.9) Equal 8
= INT (-8.9) Equal 9

(3) MOD ( ) :- ;g Function fdlh Hkh nh xbZ la[;k dk 'ks"k crkrk gSA
Syntax :- MOD (No) Divisor
= MOD (3,2) Equal 1
= MOD (-3,.2) Equal 1
= MOD (3,-2.) Equal -1
N A
I S H
(4) Power ( ) :- ;g Function fdlh Hkh nh xbZ la[;k dh ?kkr dks crkrk gSA
Syntax :- Power (Number, Power)
K R
= Power (5,2) Equal 5
B Y
= Power (98.6,3.2) Equal ----
= Power (4.5/4) Equal 5.656
I A L
E C
P
(5) Round ( ) :- ;g Function fdlh la[;k ds n'keyo dks Round dj n'kkZrk gSA vFkkZr Point ds ckn dh nks ;k rhu

S
U P
la[;k dks Round djrk gSA
Syntax :- Round (Number, Decimal Number)
= Round (2.15,1) Equal 2.2
= Round (-1.485,2) Equal -1.48
= Round (21.5,-1) Equal 20

(6) Sqrt ( ) :- ;g Function Positive esa Square Root crkrk gSA

Syntax :- Sqrt (Number)
= Sqrt (16) Equal 4
= Sqrt (-16) Equal .........
= Sqrt (ABS(-16)) Equal 4

- 149 -
 Fundamentals of Computers & Information Technology

(7) SUM ( ) :- ;g Function ls ge fdlh Hkh la[;k dk SUM dj ldrs gSaA

Syntax :- SUM (No1, No2......)
= SUM (1,6) Equal 7
= SUM (A2 : E2)

(8) PRODUCT( ) :- ;g Function ls ge fdlh Hkh la[;k dk Multiply dj ldrs gSaA

Syntax :- PRODUCT (No1, No2.......)
= PRODUCT (A12:A16)

(9) PI ( ) :- ;g Function ls ge f=T;k dk eku Kkr dj ldrs gSA

Syntax :- PI ( )
= PI ( )/2 Equal 1.57079.......
N A
= Sin (PI()/2) Equal ..............

I S H
K R
(10) .......... ( ) :- ;g Function ls ge fdlh Hkh la[;k------------------------------------------------------------------------------------------
Syntax :- .......... (No1, No2)
B Y
= .......... (1,6) Equal 7
= .......... (A2 : E2)
I A L
E C
P
(11) Exp ( ) :- ;g Function ls ge Exponential Value Kkr dj ldrs gSA

S
U P
Syntax :- Exp (No)
= Exp (1) Equal 2.718282
= Exp (2) Equal 2.7389056

(12) FACT ( ) :- ;g Function Factorial Number crkrk gSA

Syntax :- FACT (1) Equal 1
= FACT (0) Equal 1
= FACT (5) Equal 1 x 2 x 3 x 4 x 5

(12) Sign ( ) :- ;g Function .............................................. gSA

Syntax :- Sign ( )

- 150 -
 Fundamentals of Computers & Information Technology
= Sign ( )
= Sign ( )

: Trigonometry :-

(1) Sin ( ) :- ;g Function ls Sin Q dk eku crkrk gSA

Syntax :- Sign (No)
= Sign (30) Equal 0.5 or ½
= Sign (PI()/2) Equal 1

(2) Cos ( ) :- ;g Function ls Cos Angle dk eku crkrk gSA

Syntax :- Cos (No)

N A
= Cos (60*PI()/180)

I S H
R
(3) Tan ( ) :- ;g Function ls Tangen of given Equal 0.5 or ½ crkrk gSA

K
Syntax :- Tan (No)
= Tan (60*PI()/180)
B Y
I A L
(4) Average ( ) :- ;g Function ls fdlh Hkh la[;k dh Average Kkr dj ldrs gSA

E C
Syntax :- Average (No1,No2.........)

S P
= Average (A1 : A5)

P
= Average (1,2,4,6,5) Equal 3.6

U
(5) Max ( ) :- ;g Function ls Maximum la[;k dks fy[k ldrs gSaA
Syntax :- Max (Val1, Val2.........)
= Max (4,7,15,10,3) Equal 15

(6) Min ( ) :- ;g Function ls Minimumla[;k dks fy[k ldrs gSaA

Syntax :- =Min (Val1, Val2.........)
= Min (4,7,15,10,3) Equal 3

(7) STDEV ( ) :- ;g Function ls ge Standard Deviation dk eku Kkr dj ldrs gSA

- 151 -
 Fundamentals of Computers & Information Technology
Syntax :- =STDEV (10)
= STDEV (.......)

(8) VAR ( ) :- ;g Function ls Variance dk eku crkrk gSA

Syntax :- =VAR ("Prem")
= VAR ("Prem") Equal 4

: Data Base Function :

(1) Daverage ( ) :- ;g Function Database esa fdlh Field ;k Record dh Average fudkyrk gSA
Syntax :- =Daverage (Database, Field, Record)

N A
(a) Database : Database esa fdlh Cell dh Range nh tkrh gSA
(b) Field : ftl Field dk iz;ksx djuk gS mls ---------- gSA
I S H
(C) Criterial : blesa tgkW Average fudkyuk gS mldh Range nh tkrh gSA

K R
B Y
(2) Dcound ( ) :- ;g Function fdlh Database esa cus Cell dks Cpital djrk gSA

A L
Syntax :- =Dcound (Database, Field, Record)

I
E C
(3) Dmax ( ) :- ;g Function Data Base esas Field dh Maximum la[;k dks fy[k ldrs gSaA

S P
Syntax :- =Dmax (Val1, Val2.........)

U P
(4) Dmin ( ) :- ;g Function Data Base esas Field dh Minimum la[;k dks fy[k ldrs gSaA
Syntax :- =Dmin (Database, Field, Record)

(5) Dproduct ( ) :- ;g Function Data Base esas fdlh Field dh Value dks Multiple djrk gSA
Syntax :- =Dproduct (Database, Field, Record)

(6) Dsum ( ) :- ;g Function Data Base esas fdlh Field ds dkWye esa uEcj dks tksM ldrk gSA
Syntax :- =Dmin (Database, Field, Record)

- 152 -
 Fundamentals of Computers & Information Technology
: Text Function :

(1) Char ( ) :- ;g Function Data Base esas fdlh Numeric Value dks Character esa cnyus ds dke vkrk gSA
Syntax :- = Char (Value)
= Char (65) Equal A

(2) Proper( ) :- ;g Function fdlh Hkh Text dk First Letter Capital djus esa dke vkrk gSA
Syntax :- = Proper ("Text")
= Proper ("Prem") Equal Prem

(3) Lower ( ) :- ;g Function fdlh Hkh Text dk First Letter Small djus essa dke vkrk gSA
Syntax :- = Lower ("Text")

N A
= Lower ("PREM") Equal prem

I S H
(4) Upper ( ) :- blds }kjk Small Letter esa fy[ks Text dks Caapital Letter esa fy[k ldrs gSA
Syntax :- = Upper ("Text")

K R
= Upper ("prem") Equal PREM

B Y
A L
(5) Len ( ) :- blds }kjk fy[ks x;s Text dh Length Kkr dj ldrs gSA
Syntax :- = Len ("Text")
I
E C
= Len ("Creative System") Equal 14

S P
U P
(6) Left ( ) :- fdlh Hkh fy[ks x;s Text esa ls Left ds v{kjksa dks ys ldrs gSA
Syntax :- = Left (Text,No of Character)
= Left (Ravi is a Strong, 5) Equal Ravi

(7) Right ( ) :- fdlh Hkh fy[ks x;s Text esa ls Right ds v{kjksa dks ys ldrs gSA
Syntax :- = Right (Text,No of Character)
= Right (Ravi is a Strong, 5) Equal Strong

(8) Mid( ) :- fdlh Hkh fy[ks x;s Text esa ls Mid ds v{kjksa dks ys ldrs gSA
Syntax :- = Mid (Text,No of Character)
= Mid (Ravi, is a, 5) Equal is a

- 153 -
 Fundamentals of Computers & Information Technology

(9) Rept ( ) :- ,d le; fdlh Hkh Text dks fdruh ckj Repeat djkuk djk ldrs gSA
Syntax :- = Rept ("Text",No of Repeat)
= Rept ("Prem",5) Equal Prem Prem Prem Prem Prem

(10) Trime ( ) :- bl Function ds }kjk izkjEHk ls vUr ds lHkh [kkyh LFkku (Spaces) dks lekIr dj ldrs gSA
Syntax :- = Trime ("Text")
= Trime ("Prem Perkash") Equal PremPerkash

(11) Exact ( ) :- blds }kjk ge Text dh rqyuk dj ldrs gSA

Syntax :- = Exact (Text1,Text2)

N A
: Logical Function :

I S H
(1) AND ( ) :- (rFkk½ ;fn ,d rF; Argument lgh True gS rFkk nwljk rF; Flase gS rks mlds Check djus ds dke
vkrk gSA ;g lgh True ds fy, 1 o False ds 0 nsrk gSA
K R
Syntax :- = AND (True,True) Equal True

B Y
= AND (True,False) Equal False
= AND (2+2=4,2+3=5) Equal True
I A L
C
= AND (2+2=4,2+3=6) Equal False

E
S P
(2) OR ( ) :- ¼vFkok½ blls Hkh Condition dks lgh o xyr crk ldrs gSA bls fy, ,d True gksuk t:jh gSA

U P
Syntax :- = OR (Logical1, Logical2)
= OR (True,False) Equal True
= OR (2+2=4,2+2=5) Equal False

(3) IF ( ) :- ¼;fn½ blds }kjk nh xbZ Condition dks Check dj ldrs gSA blds fy, nks Value gksuk pkfg, ftlesa ,d
Ture o nwljh False gksuh pkfg,A
Syntax :- = IF (Logical-Test,Value-if-True,Value if False)
= IF (AND(F4>=48,F4<60)),II,III Equal II

(4) NOT ( ) :- ¼ugh ½ ;g Condition True gS mldks False o tks Condition False gS mldks Ture crkrk gSA

- 154 -
 Fundamentals of Computers & Information Technology
Syntax :- = NOT (False) Equal True

: Creates and Using Macros :

Macro og izfdz;k gS ftlds }kjk ge fd;s x;s dk;Z dks Record djds mldh dk;Z dks fdlh Hkh Documents ;k Sheets
esa ckj&ckj iz;ksx esa ys ldrs gSA mlds djus dh fuEu izfdz;k gSA

loZizFke] Tools Macro Record Macro

rks ,slk djrs gh gekjh Recording Start gks tkrh gS tks dk;Z gesa djuk gS og djrs gS lkjk dke Reocrd gks tkrk gSA

ysrs gSA o Macro dk uke ns nsrs gSA

N A
Screen ij vk;s Stop Button ij Dyhd dj nsrs gSA bl izdkj gekjk dke Record gks tkrk gS ckn esa mldks Save dj

S H
vc ;fn dke dHkh Hkh okfil pkfg, rks ge Use esa ys ysrs gSA blds fy, Macro dks Run dj ysrs gSA
blds nks Qk;ns gSA
I
1- blls cuh File Space Hkh de ysrh gSA
K R
2- dk;Z Hkh tYnh dj ldrs gSA

B Y
Record Macro
I A L ? X

E Macro Name C Store Macro in

S P Short Cut Key

U P Ctrl =

Discription
OK Cancel

Multiple Work Sheet : Concepts, Creating and During

Multiple Worksheets ij dk;Z djus ds fy, igys Sheets dks Multiple djuk iMrk gsA Sheet Multiple
djus dh izfdz;k fuEu izdkj gSA Sheet ij tkdj Right Button Click djrs gSA rks lkjh Sheet Select gks tkrh gSA vksj
ckn esa tks Hkh dk;Z ge Sheet ij djrs gS rks gekjk dk;Z lHkh Sheet ij vk tkrk gSA Sheet 2 o Sheet 3 vkfn ij Hkh og
dke ns[k ldrs gSA

- 155 -