Wednesday, December 28, 2011

মাইক্রোসফট এক্সেল টিউটোরিয়াল সব টুকু ।


88.  Numbering Bar nvwo‡q †M‡j wdwi‡q Avbvi wbqg t
            Ø hw` †Kvb Kvi‡b Numbering Bar (1, 2, 3, 4 & A, B, C, D) nvwo‡q hvq Zv wdwi‡q Avb‡Z n‡j cÖ_‡g Tools-G Click Ki‡j Tools Menu-Gi Sub-Menu ‡`Lv hv‡e, Gevi Option-G Click Ki‡j c`©vq Option Dialog Box Avm‡e, GLv‡b View-†Z Click Ki‡j View-Gi Chart ‡`Lv hv‡e Gevi Row & Column heders-Gi mvg‡b wUK (ü) wPý w`‡q Ok-‡Z Click Ki‡j Work Seet-Gi Dc‡i Ges ev‡g †h Numbering Kiv BarwU Av‡Q Zv ‡`Lv hv‡e|

89.  Selling Sheet ‰Zix Kivi wbqg t
A
B
C
D
E
F
G
H
I
Sl.
Item
Jun
Feb
Mar
Total
Avg
Max
Min
01.
Book
2000
7000
8000
=Sum(C1:E1) &  8
@Average(C1:E1) &  8
=Max(C1:E1)  &  8
=Min(C1:E1)    &  8
02.
Pen
4000
4000
5000
03.
Disk
6000
6500
7000


9O.  Stock Ledger ‰Zix Kivi wbqg t
A
B
C
D
E
F
Sl.
Item
Op-Stock
Delivery
Purchase
Total
01.
Book
5000
4000
4000
+ C1 -  D1 + E1 & 8
02.
Pen
3500
3000
3000
03.
Disk
9000
7500
7500

91.  Inventory Sheet ‰Zix Kivi wbqg t
A
B
C
D
E
F
G
Sl.
Item
Qty
U-Price
Total
Damage
G-Total
01.
Computer
250
4000
+ C1*D1 & 8
O5

=(C1-F1)*D1                & 8
02.
Printer
300
15000
O6
03.
Disk
1500
500
O8

92.  Result Sheet Kivi wbqg t
                 
A
B
C
D
E
F
G
H
J
K
L
M
N
Sl.
B1
B2
E1
E2
M1
Bio1
Total
Avg
Max
Min
GPA
Gread
01.
40
42
50
75
80
55
=Sum               (B1 :  M1)
  & 8

@Average (B1 : M1)
  & 8

=Max           (B1 : M1)
 & 8

=Min          (B1 : M1)
  & 8



02.
44
44
55
77
82
54
03.
46
43
56
78
85
53
04.
50
47
65
70
86
52

Formla :
Cells Pointer ‡K Result Gi N‡i ivL‡Z n‡e|
GPA    :  =If(H1>=75O,”Gpa5”,@If(H1>=6OO,”Gpa4”,@If(H1>=5OO,” Gpa3.5O”,@If(H1>=4OO,”Gpa3”,If(H1>=333,”Gpa2”,”Fail”))))) & 8
Gread  : =If(H1>=80,”A+”,@If(H1>=70,”A”, @If(H1>=60,”A-” ,@If(H1>=50,”B”, @If(H1>=40,”C”, @If(H1>=33,”D”,@If(H1<=33,”F”))))) & 8


93.  Electric Bill ‰Zix Kivi wbqg t
                 
A
B
C
D
Meter No.
Name
Unit
Amount
055356556
Asraf
120

96358885
Rahim
75

822666
Mahbub
210

745826
Jamal
406

85555566
Masum
550

568998666
Rubal
700


Formla :
Cells Pointer ‡K Amount Gi N‡i ivL‡Z n‡e|
Amount : =If(C2<=200,C2*1.75,IF(C2<=400,C2*2.50,IF(C2<=500,C2*3.75,C2*4355))) & 8

94.  Salary Sheet ‰Zix Kivi wbqg t
A
B
C
D
E
F
G
H
I
J
K
Sl.
Name
Post
Basic
H/Rent
M/A
Salary
P/Found
Tex
Nsal
Grade
01.
Haider
MD
8000
15%
20%
?
10%


?
02.
Masud
GM
7000
13%
15%
?
9%


?
03.
Abu
ENG
5000
10%
12%
?
8%


?
Formla :
H/Rent              :           @If(C1=”MD”,D1*15%,@If(C1=”GM”,D1*13%,@If(C1=”ENG”,D1*10%))) & 8
M/A                 :           @If(C1=”MD”,D1*20%,@if(C1=”GM”,D1*15%,@If(C1=”ENG”,D1*12%))) & 8
SALARY          :           =Sum (D2:F2) & 8
P/Found                        :           @If(C1=”MD”,D1*10%,@If(C1=”GM”,D1*9%,@If(C1=”ENG”,D1*8%))) & 8
Tex                   :           =If((D1<2000,0,If(and(D1>=2000,D1<=5000),D1*5%,If(D1>5000.D1*10%)))& 8
Nsal                  :           =F1+G1 & 8 / =D1-(H1+I1) & 8
Grade               :           @If(G1=7000,”A”,@If(G1=6000”B”,@If(G1=4000,”C”,@If(G1=3000,”D”,”E”))))& 8

95.  Garments Factory’s Production Worker’s Salary Sheet ‰Zix Kivi wbqg t
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
Sl.
Name
Post
Card No
Ttt Day
W D
Basic
H/ Rent
M / A
Total
Produ Salary
ATT Bonus
Produ Bonus
TotalBasic
A/C Pay
Adv
Stamp
Net Pay
Signature
01.
Haider
OP
01
31
31
1038

150
1500

250







02.
Masud
OP
02
31
31
1038

150
1500

250







03.
Abu
OP
03
31
31
1038

150
1500

0







Formla :
Basic                 :           = (J1-I1)/1.3
H/Rent              :           = G1*30%
Att Bonus          :           =IF(E7=F7,”250”,”0”)

96.  Garments Factory’s Fised Worker’s Salary Sheet ‰Zix Kivi wbqg t
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Sl.
Name
Post
Card No
Ttt Day
W D
Basic
H/ Rent
M / A
Total
ATT Bonus
A/C Pay
Adv
Stamp
Net Pay
Signature
01.
Haider
OP
01
31
31
1038

150
1500
250





02.
Masud
OP
02
31
31
1038

150
1500
250





03.
Abu
OP
03
31
31
1038

150
1500
0





Formla :
Basic                 :           = (J1-I1)/1.3
H/Rent              :           = G1*30%
Att Bonus          :           =IF(E7=F7,”250”,”0”)



97.  Overtime mn Salary Sheet ‰Zix Kivi wbqg t
†Kvb K¤úvwbi cÖwZ N›Uv wnmv‡ei Kg©Pvwi‡`i gRywi cÖ`vb K‡i| cÖwZw`b 8 N›Uv ev Zvi †P‡q Kg mg‡qi gRywi cÖwZ N›Uvq 60 UvKv| 8 N›Uvi AwZwi³ cÖwZ N›Uvi gRywi 20 UvKv A_©vr †KD hw` 12 N›Uv KvR K‡i Zvn‡j †m cv‡e- 60*8 = 480,       20*4 = 80, GLb 12 N›Uv = 480 + 80 = 560 UvKv|
A
B
C
D
E
Code No
Name
Working Hour
Overtime
Payment
01.
Haider
12
4

02.
Masud
12
4

03.
Abu
10
2

04.
Mahabub
11
3

05.
Jamal
14
6

Formla :
Over Time         :           =C1-8 & 8
Payment            :           =If(D1>3,D2*20+8*15,C1*15) & 8

98.  Commission Sheet ‰Zix Kivi wbqg t
†Kvb K¤úvwb Zv‡`i cÖwZwbwa‡`i weµ‡qi Dci wfwË K‡i Kwgkb †`‡e|
weµq 10,000 UvKvi Kg n‡j Kwgkb 2.5%,
weµq 10,000 UvKv †_‡K 30,000 UvKv n‡j Kwgkb 5%,
weµq 30,000 UvKvi Dc‡i Ges 50,000 UvKvi g‡a¨ n‡j Kwgkb 7%,
weµq 50,000 UvKvi Dc‡i n‡j Kwgkb 10% nv‡i †`Iqv n‡e|
A
B
C
D
Sl. No.
Sales in Person
Sales
Commission
01.
Rani
25000

02.
Rukan
23000

03.
Rubal
9000

04.
Ruma
51000

05.
Khakon
13500

Formla :
Commision        :             If(C1<10000,C1*2.5%,If(and(C1>=10000,C1<30000),C1*5%,If(and(C1>30000,C1<=50000),C1*7%,If(C1>50000,C1*10%)))) & 8

99.  Commission mn Salary Sheet ‰Zix Kivi wbqg t
†Kvb K¤úvwb Zv‡`i cÖwZwbwa‡`i weµ‡qi Dci wfwË K‡i Kwgkb †`‡e|
weµq 10,000 UvKvi Kg n‡j Kwgkb 2.5%,
weµq 10,000 UvKv †_‡K 30,000 UvKv n‡j Kwgkb 5%,
weµq 30,000 UvKvi Dc‡i Ges 50,000 UvKvi g‡a¨ n‡j Kwgkb 7%,
weµq 50,000 UvKvi Dc‡i n‡j Kwgkb 10% nv‡i †`Iqv n‡e|
A
B
C
D
E
Sl. No.
Sales in Person
Sales
Commission
Total
01.
Rani
5000
900

02.
Rukan
4500
800

03.
Rubal
4000
700

04.
Ruma
3700
600

                          
Formla :
Commision        :           = C1+D1*10% & 8


1OO.  AvqKi wnmve Kivi wbqg t
†Kvb e¨w³i, gvwmK Avq hw` 10,000 UvKv nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 2%,
     gvwmK Avq hw` 15,000 UvKvi g‡a¨ nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 3%,
     gvwmK Avq hw` 20,000 UvKv nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 5%,
     gvwmK Avq hw` 20,000 UvKvi Dc‡i nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 8%,
A
B
C
D
Sl. No.
Name
Monthly Incom
Income Tax
01.
Rukan
25,000

02.
Rubal
22,300

03.
Ruma
20,000

04.
Khakon
15,000

05.
Bulbul
13,000

Formla :
Commision        :           If(C1=10000,C1*2%,If(C1<=15000,C1*3%,If(C1<=20000,C1*5%,If(C1>20000,C1*8%)))) & 8

1O1.  my‡`i wnmve †ei Kivi wbqg t
†Kvb e¨w³i, FY hw` 100,000 UvKvi g‡a¨ n‡j 5%,
                 FY hw` 300,000 UvKvi g‡a¨ n‡j 7%,
     FY hw` 500,000 UvKvi g‡a¨ n‡j 9%,
                 FY hw` 1,000,000 UvKvi g‡a¨ n‡j 10%,
     FY hw` 1,000,000 UvKvi g‡a¨ n‡j 15%,
A
B
C
D
Sl. No.
Name
Loan Amount
Interest
01.
Rukan
25,000

02.
Rubal
22,300

03.
Ruma
20,000

04.
Khakon
15,000

05.
Bulbul
13,000

Formla :
Commision        :                 If(C1<=10000,C1*5%,If(C1<=30000,C1*7%,If(C1<=500000,C1*9%,If(C1<=1000000,C1*10%If(C1>1000000,C1*15%))))) & 8


1O2.  Auto Filter Kivi wbqg t
            Ø Data-G Click Ki‡j Data Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Filter-G Click Ki‡j c`©vq    Filter-Gi kvLv Menu Avm‡e, Gevi Auto Filter-G Click Ki‡jB Auto Filter-Gi mvg‡b GKwU wUK (ü) wPý em‡e|

1O3.  Auto Filter cªZ¨vnvi Kivi wbqg t
            Ø Data-G Click Ki‡j Data Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Filter-G Click Ki‡j c`©vq    Filter-Gi kvLv Menu Avm‡e, Gevi Auto Filter-G Click Ki‡jB Auto Filter-Gi mvg‡b GKwU wUK (ü) wPý DwV‡q w`‡Z n‡e|

1O4.  Sorting Gi gva¨‡g Ascending/ Descending Kivi wbqg t
            Ø Data-G Click Ki‡j Data Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Sort-G Click Ki‡j c`©vq Sort Dialog Box Avm‡e, GLv‡b hw` Ascending Ki‡Z PvB Zvn‡j Ascending-G Click Ki‡Z n‡e Avi hw` Descending Ki‡Z PvB Zvn‡j Descending -G Click K‡i Ok-‡Z Click Ki‡Z n‡e|

1O5.  wbwÏ©ó Cells-G Date hy³ Kivi wbqg t
            Ø †h Cells-G Date hy³ Kivi `iKvi cª_‡g †m Cells-G Cursor †i‡L =Today ( ) Type K‡i Enter [ 8 ] Pvc‡Z n‡e| hw` †Kvb Box Av‡m Zvn‡j YES-G Click Ki‡j Cursor Gi ¯’v‡b AvR‡Ki ZvwiL hy³ n‡e|

1O6.  wbwÏ©ó Cells-G Date & Time hy³ Kivi wbqgt
            Ø †h Cells-G Date & Time hy³ Kivi `iKvi cª_‡g †m Cells-G Cursor †i‡L = Now ( ) Type K‡i Enter  [ 8 ] Pvc‡Z n‡e| hw` †Kvb Box Av‡m Zvn‡j YES-G Click Ki‡j Cursor Gi ¯’v‡b AvR‡Ki ZvwiL I mgq hy³ n‡e|

1O7.  Work Sheet Hide Kivi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Freeze Kivi `iKvi, Cursor cÖ_‡g †m ¯’v‡b ivL‡Z n‡e Zvici Window-G Click Ki‡j Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Hide-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Freeze n‡e|

1O8.  Work Sheet Unhide (cÖZ¨vnvi) Kivi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Freeze Kiv Av‡Q, Cursor cÖ_‡g †m ¯’v‡b ivL‡Z n‡e Zvici  Window-G Click Ki‡j Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Unhide-G Click Ki‡j c`©vq Unhide Dialog Box Avm‡e GLv‡b †hUv‡K Unhide Kiv n‡q‡Q ‡mUv‡K Select Kivi ci Ok-†Z Click Ki‡Z n‡e|

1O9.  Split (‡gvUv `vM) Kivi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Split Kivi `iKvi, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Split-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Split n‡e|

11O.  Remove Split (‡gvUv `vM) cÖZ¨vnvi Kivi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Split Kiv Av‡Q, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Remoce Split-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Split Kiv _vK‡j Zv Open n‡e|

111.  Freeze Panes (wPKb `vM) Kivi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Freeze Kivi `iKvi, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Freeze Panes-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Freeze n‡e|

112.  Unfreeze Panes (wPKb `vM) cÖZ¨vnvi Kivi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Freeze Kiv Av‡Q, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Unfreeze Panes-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Freeze Kiv _vK‡j Zv Unfreeze n‡e|
113.  New Window Avbvi wbqg t
            Ø Document-Gi ‡h ¯’v‡b Freeze Kiv Av‡Q, Cursor cÖ_‡g †m ¯’v‡b ivL‡Z n‡e Zvici Window-G Click Ki‡j Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi New Window-G Click Ki‡jB New Window Avm‡e|

ba------XX------ba               



G‡·‡ji         †gvU Row-Gi msL¨v            t        256
†gvU Column-Gi msL¨v      t        65536




m~Îvejx :

‡hvM Kivi wbqg (GK mvwii me¸‡jv) :           = Sum (C2 : J2) &  8
‡hvM Kivi wbqg (wbÏ©ó mvwi ¸‡jv)                :           = Sum (C2 + E2 + G2 + I2) &  8
we‡qvM Kivi wbqg                                     :              Sum (C2 - D2) &  8
¸b Kivi wbqg                                          :           + C2*D2 &  8
fvM Kivi wbqg                                        :           = C2 / D2 &  8
% Kivi wbqg (‡ekx)                                 :           = C2*10% + C2 &  8 / =C2*0.01% + C2 & 8
% Kivi wbqg (Kg)                                  :           = C2*10% - C2 &  8 / =C2*0.01% - C2 & 8
Maximun Kivi wbqg                              :           = Max (C2 : J2) &  8
Minimun Kivi wbqg                               :           = Min (C2 : J2) &  8
Average Kivi wbqg                                :           @ Average (C2:J2) &  8  /  =Average (C2/3) &  8


Short Cut Command

1.         CTRL + A                                                       =          All Select
2.         CTRL + B / 2                                                  =          Bold
3.         CTRL + I  / 3                                                  =          Italic
4.         CTRL + U / 4                                                  =          Underline
5.         CTRL + 5                                                       =          Text the Strikethough
6.         CTRL + SHIFT + 1                                        =          Only for Nuemaric
7.         CTRL + SHIFT + 2 / CTRL + SHIFT +  ;    =          Only for Time
8.         CTRL + SHIFT + 3 / CTRL + :                     =          Only for Date
9.         CTRL + SHIFT + 4                                        =          Only for Dollar ($) Icon
1O.      CTRL + SHIFT + 5                                        =          Only for Persent (%) Icon
11.       CTRL + SHIFT + F                                        =          Select the Font Box
12.       CTRL + SHIFT + P                                        =          Select the Font Size Box
13.       CTRL + SHIFT +   ~                                                  =          Normal Format
14.       CTRL + SHIFT +   !                                =          Comma Format
15.       CTRL + SHIFT +   @                                                =          Time Format
16.       CTRL + SHIFT +   #                                     =          Date Format
17.       CTRL + SHIFT +   $                                     =          Taka Format
18.       CTRL + SHIFT +   %                                                =          Parsent Format
19.       CTRL + SHIFT +   _                                     =          No Border
2O.      CTRL + N                                                       =          New Document.
21.       CTRL + S / SHIFT + F12                               =          Save Box
22.       CTRL + O                                                      =          Open Box
23.       CTRL + K                                                      =          Insert Hyperlink Box
24.       CTRL + X                                                       =          Cut
25.       CTRL + Z                                                       =          Undo
26.       CTRL + C                                                       =          Copy
27.       CTRL + N                                                       =          New Document / New Book Sheet
28.       CTRL + V                                                       =          Paste
29.       CTRL + P                                                       =          Print Box
3O.      CTRL + 1                                                       =          Font Box
31.       CTRL + 9                                                       =          Hide the Row
32.       CTRL + O                                                      =          Hide the Column
33.       CTRL + F5                                                     =          Small Size the Wark Sheet
34.       CTRL + G                                                      =          Go To Box
35.       CTRL + F / SHIFT + F5                                =          Find Box
36.       CTRL + H / SHIFT + F5                                =          Replace Box
37.       CTRL + ALT + B                                           =          Change the Key Board.
38.       CTRL + W                                                      =          Book Close.
39.       CTRL +   +                                                     =          Sate Up the Any
4O.      CTRL +   -                                                      =          Column Delete
41.       SHIFT + F3                                                    =          Formula Box.
42.       F7                                                                    =          Spelling & Grammar.
43.       F10                                                                  =          Select the Menubar
44.       ALT + F4                                                        =          Program Close.
45.       ALT +   =                                                        =          Formula (+)
46.       SHIFT +   _                                                     =          Delete the Row / Column by Text.
47.       SHIFT +   +                                                    =          Increase the Row / Column by Text.
48.       SHIFT + F11                                                  =          Increase Sheet by Text.

‡gv t Rv‡n`ybœex Ry‡qj

0 comments:

Post a Comment