Bai tap huong dan su dung Excel

BAØI THÖÏC HAØNH EXCEL

Bµi sè 1: Sö dông c¸c thao t¸c c¬ b¶n víi B¶ng tÝnh

Khëi ®éng Excel tõ mét workbook míi h·y t¹o b¶ng d÷ liÖu sau vµ thùc hiÖn thø tù c¸c yªu cÇu cña bµi thùc hµnh:










STT
HOÏ VAØ TEÂN
PHAÙI
NGAØY SINH
LCB
LÖÔNG
TAÏM ÖÙNG
COØN LAÏI

1
An
Nam
5/2/1965
3.26


 

2
Thuyû
Nöõ
23/12/64
2.14


 

3
Höông
Nöõ
17/03/66
3.12


 

4
Huøng
Nam
9/4/1964
2.46
 
 
 


C©u1: NhËp d÷ liÖu( Cét STT dïng kÜ thËt ®iÒn d·y sè tù ®éng)
C©u2: ChÌn mét dßng tr­íc ng­êi tªn H­¬ng, vµ nhËp thªm d÷ liÖu nh­ sau( §iÒu chØnh cét thø tù cho ®óng)

3
QUYØNH
NAM
15/10/1964
2.46
 
 
 



C©u3: L­¬ng b»ng LCB nh©n víi 210000
C©u4: T¹m øng b»ng 25% Luong
C©u5: Cßnl¹i b»ng = Luong - Tam ung
C©u6: Trang hoµng vµ l­u b¶ng tÝnh víi tªn lµ BTH1.XLS

H­íng dÉn thùc hµnh .
B­íc 1: Khëi ®éng m¸y .
B­íc 2: KiÓm tra hÖ th«ng ngµy thuéc d¹ng nµo, kiÓu: mm/dd/yy hay dd/mm/yy
C©u1: Dïng kü thuËt ®iÒn sè tù ®éng: vµo « ®Çu tiªn ®iÒn vµo « ®ã sè 1 sau ®ã ®­a con chuét vÒ « cuèi sè1 nhÊn mouse tr¸i ®ång thêi nhÊn Ctrl rª chuét theo cét däc tïy theo thø tù m×nh cÇn ®¸nh sè ®Õn bao nhiªu sau ®ã th¶ chuét vµ Ctrl.
C©u 2: §Æt con trá ë « thø tù lµ 3, nhÊn Mouse ph¶i vµ chän Insert trong mennu chän tiÕp Entire Row ®Ó chÌn mét hµng : HoÆc chän hµng b»ng c¸ch click vµo sè hiÖu hµng, sau ®ã dïng lÖnh Insert ®Ó chÌn .
C©u3: NhËp c«ng thøc : =[LCB]*210000, trong ®ã [LCB] lµ tham chiÕu ®Õn « l­¬ng c¬ b¶n (dïng Mouse chØ vµo « chøa gi¸ trÞ LCB t­¬ng øng víi hµng ®ang lËp c«ng thøc , kh«ng ph¶i chØ vµo « cã tªn LCB)










STT
HOÏ VAØ TEÂN
PHAÙI
NGAØY SINH
LCB
LÖÔNG
TAÏM ÖÙNG
COØN LAÏI

1
An
Nam
5/2/1965
3.26
= E2*210.000

 


C©u4: nhËp c«ng thøc :=[l­¬ng]*0.25.










STT
HOÏ VAØ TEÂN
PHAÙI
NGAØY SINH
LCB
LÖÔNG
TAÏM ÖÙNG
COØN LAÏI

1
An
Nam
5/2/1965
3.26
684600
= 0.25*G2
 


C©u5: NhËp c«ng thøc :=[L­¬ng]-[T¹m øng].
Sau khi thùc hiÖn b¶ng tÝnh ta ®­îc kÕt qu¶ nh­ sau:










STT
HOÏ VAØ TEÂN
PHAÙI
NGAØY SINH
LCB
LÖÔNG
TAÏM ÖÙNG
COØN LAÏI

1
An
Nam
5/2/1965
3.26
684600
171150
513450

2
Thuyû
Nöõ
23/12/64
2.14
449400
112350
337050

3
Höông
Nöõ
17/03/66
3.12
655200
163800
491400

4
Huøng
Nam
9/4/1964
2.46
516600
129150
387450



Bµi sè 2: Sö dông c¸c hµm IF,SUM,ROUND,...










STT
HOÏ TEÂN
CV
LCB
NC
LÖÔNG
TAÏM ÖÙNG
THÖÏC NHAÄN

1
LINH
TP
3.24
23




2
VÖÔNG
NV
2.44
25




3
THUYÛ
PP
3.12
26




4
YEÂN
GD
1.92
29




5
TIEÂN
DV
2.56
30




6
LÔÏI
PGD
3.92
15




7
HUØNG
NV
1.92
18




8
TRÍ
NV
3.24
19




9
LOAN
TK
2.92
22




10
NHUNG
DV
3.14
26




11
Toång Coäng





C©u1: NhËp d÷ liÖu (cét STT dïng kÜ thËt ®iÒn d·y sè tù ®éng).
C©u2: TÝnh luong = 6000*LCB*Ngaycong
(Trong ®ã, NÕu NC >25 th× tõ ngµy 26 trë ®i mçi ngay ®­îc tÝnh thµnh hai ngµy, VÝ dô: nÕu NC lµ 27 th× sè ®­a ra lµ hai ngµy ®­îc tÝnh thµnh 4 ngµy, do ®ã tæng ngµy sÏ lµ: 25+4)
C©u3: TAÏM ÖÙNG = 1/3 LÖÔNG ( Nh­ng tèi ®a chØ cho TAÏM ÖÙNG 150000®)
C©u4: THÖÏC NHAÄN = luong - Tam ung
C©u5: TÝnh Tæng Céng c¸c cét LÖÔNG, TAÏM ÖÙNG, THÖÏC NHAÄN.
C©u6: Lµm trßn cét tam ung ®Õn ngµn ®ång.

H­íng dÉn thùc hµnh:
Bµi to¸n nµy chóng ta sö dông c¸c hµm: IF,SUM,ROUND,...
C©u 1: Häc sinh tù lµm.
C©u2: [L­¬ng] = 6000*[LCB]*IF([NC]<=25,[NC],25+([ NC ]-25)*2).










STT
HOÏ TEÂN
CV
LCB
NC
LÖÔNG
TAÏM ÖÙNG
THÖÏC NHAÄN

1
LINH
TP
3.24
23
= 6000*D2*(IF(E2<25,E2,E2+(E2-25)*2))


C«ng thøc trªn ®­îc hiÓu lµ: NÕu ngµy c«ng kh«ng lín h¬n 25 th× tr¶ l¹i ngµy c«ng, ng­îc l¹i th× lÊy 25 céng víi sè ngµy c«ng ®­îc tÝnh gÊp ®«i.
C©u3: [Tam ung]=if([luong]/3>150000,150000,[luong]/3).










STT
HOÏ TEÂN
CV
LCB
NC
LÖÔNG
TAÏM ÖÙNG
THÖÏC NHAÄN

1
LINH
TP
3.24
23
447120
=IF(F2/3>150000,150000,F2/3)


NÕu mét phÇn ba sè l­¬ng kh«ng vùot qu¸ 150000 th× cho t¹m øng ®óng b»ng mét phÇn ba l­¬ng, ng­îc l¹i chØ cho t¹m øng 150000®.
C©u4: [thuc nhan]=[luong]-[tamung].










STT
HOÏ TEÂN
CV
LCB
NC
LÖÔNG
TAÏM ÖÙNG
THÖÏC NHAÄN

1
LINH
TP
3.24
23
447120
149040
= F2 - G2


C©u5: Sö dông hµm SUM nhËp c«ng thøc "=SUM" vµ dïng mouse ®Ó chon vïng
cÇn tÝnh tæng. Sau ®ã kÐo sang c¸c « bªn c¹nh ®Ó t¹o c«ng thøc tù ®éng cho c¸c tæng kÕ tiÕp .

11
Toång Coäng
= SUM(F2:F11)




C©u6: S÷a c«ng thøc trong cét Tam øng thµnh: =ROUND([IF(...)],-3). L­u ý: Gi¸ trÞ -3 dïng ®Ó lµm trßn ®Õn hµng ngµn (3 sè 0). Sau khi söa xong kÐo xuèng phÝa d­íi ®Ó thay thÕ.
=ROUND(IF(F2/3>150000,150000,F2/3),-3)










STT
HOÏ TEÂN
CV
LCB
NC
LÖÔNG
TAÏM ÖÙNG
THÖÏC NHAÄN

1
LINH
TP
3.24
23
447120

= ROUND(IF(F2/3>150000
,150000,F2/3),-3)



Sau khi söa xong kÐo xuèng phÝa d­íi ®Ó thay. Ta ®­îc b¶ng tÝnh nh­ sau:








STT
HOÏ TEÂN
CV
LCB
NC
LÖÔNG
TAÏM ÖÙNG
THÖÏC NHAÄN

1
LINH
TP
3.24
23
447120
149000
298120

2
VÖÔNG
NV
2.44
25
366000
122000
244000

3
THUYÛ
PP
3.12
26
524160
150000
374160

4
YEÂN
GD
1.92
29
426240
142000
284240

5
TIEÂN
DV
2.56
30
614400
150000
464400

6
LÔÏI
PGD
3.92
15
352800
118000
234800

7
HUØNG
NV
1.92
18
207360
69000
138360

8
TRÍ
NV
3.24
19
369360
123000
246360

9
LOAN
TK
2.92
22
385440
128000
257440

10
NHUNG
DV
3.14
26
527520
150000
377520

11
Toång Coäng
4220400
1301000
2919400


Bµi sè 3: Sö dông c¸c hµm IF, Sum, Max, Min, Average











STT
TEÂN
LOAÏI
S.LÖÔNG
Ñ.GIAÙ1
Ñ.GIAÙ2
T.TIEÀN
THUEÁ
TOÅNG COÄNG


Lyù
1
20
3000
3200


 


Hoaù
1
15
2500
2800


 


Sinh
2
10
4000
4500


 


Söû
1
5
2000
2300


 


Ñòa
1
10
3500
3800


 


Vaên
2
25
2500
2600


 


Tieáng Vieät
1
15
4500
4700


 


Anh Vaên
1
10
3000
3300


 


Giaùo Duïc
2
20
5000
5200


 


Lyù
1
15
4000
4300


 






SUM









MAX









MIN
AVERAGE





C©u 1: Tinh Thµnh tiÒn b»ng S.L­îng*§.Gi¸ tïy thuéc vµo lo¹i ( NÕu LO¹I 1 th× ®¬n Ñ.GIAÙ1, ng­îc l¹i nÕu lo¹i 2 th× ®¬n Ñ.GIAÙ2 )
C©u 2: TÝnh ThuÕ b»ng 10% cña T.TiÒn.
C©u 3. TÝnh Tæng céng b»ng tæng cña Thµnh tiÒn víi ThuÕ
C©u 4: TÝnh tæng, gi¸ trÞ lín nhÊt, nhá nhÊt, trung b×nh cña c¸c cét Thµnh tiÒn, ThuÕ vµ Tæng céng.

H­íng dÉn thùc hµnh:
C©u1: [T.TiÒn] = [ S.L­îng]*IF([LO¹I 1] =1,[ Ñ.GIAÙ1],[ Ñ.GIAÙ2]).











STT
TEÂN
LOAÏI
S.LÖÔNG
Ñ.GIAÙ1
Ñ.GIAÙ2
T.TIEÀN
THUEÁ
TOÅNG COÄNG


Lyù
1
20
3000
3200
 = D2*IF(C2=1,E2,F2)


C©u 2: [ THUEÁ]=[ T.TIEÀN]*0.1.











STT
TEÂN
LOAÏI
S.LÖÔNG
Ñ.GIAÙ1
Ñ.GIAÙ2
T.TIEÀN
THUEÁ
TOÅNG COÄNG


Lyù
1
20
3000
3200
60000
= G2*0.1
 


C©u3: [Tcong]=[Thtien]+[Thue].











STT
TEÂN
LOAÏI
S.LÖÔNG
Ñ.GIAÙ1
Ñ.GIAÙ2
T.TIEÀN
THUEÁ
TOÅNG COÄNG

1
Lyù
1
20
3000
3200
60.000
6000
 = G2 + H2


C©u4: Tæng cña cét thµnh tiÒn ®­îc tÝnh theo c«ng thøc:

1




SUM
= SUM(G2:G11)



T­¬ng tù häc sinh tù týnh tæng c¸c cét THUEÁ vµ cét TOÅNG COÄNG
Gi¸ trÞ lín nhÊt cña cét Thµnh tiÒn ®­îc tÝnh theo c«ng thøc nh­ sau:






MAX
= MAX(G2:G11)




Gi¸ trÞ nhá nhÊt cña cét ThµnhtiÒn ®­îc tÝnh theo c«ng thøc:






MIN
= MIN(G2:G11)



Gi¸ trÞ trung b×nh cña cét ThµnhtiÒn ®­îc tÝnh theo c«ng thøc :






AVERAGE
=AVERAGE(G1:G11)



Sau khi thùc hiÖn c¸c phÐp tÝnh xong ta ®­îc kÕt qu¶ cña b¶ng tÝnh nh­ sau:











STT
TEÂN
LOAÏI
S.LÖÔNG
Ñ.GIAÙ1
Ñ.GIAÙ2
T.TIEÀN
THUEÁ
TOÅNG COÄNG


Lyù
1
20
3000
3200
60000
6000
54000


Hoaù
1
15
2500
2800
37500
3750
33750


Sinh
2
10
4000
4500
45000
4500
40500


Söû
1
5
2000
2300
10000
1000
9000


Ñòa
1
10
3500
3800
35000
3500
31500


Vaên
2
25
2500
2600
65000
6500
58500


Tieáng Vieät
1
15
4500
4700
67500
6750
60750


Anh Vaên
1
10
3000
3300
30000
3000
27000


Giaùo Duïc
2
20
5000
5200
104000
10400
93600


Lyù
1
15
4000
4300
60000
6000
54000






SUM
514000
51400
462600






MAX
104000
10400
93600






MIN
AVERAGE
10000
1000
9000


Bµi sè 4: Sö dông c¸c hµm IF lång, Rank...











SAÉP
HOÏ TEÂN
KHOÁI
TOAÙN
VAÊN
NNGÖÕ
TOÅNG
K.QUAÛ
X.LOAÏI

1
AN
A
9
10
8
 
 
 

2
THUYÛ
D
3
2
5
 
 
 

3
HÖÔNG
C
7
6
7
 
 
 

4
HUØNG
D
2
9
6
 
 
 

5
QUYØNH
C
6
8
8
 
 
 

6
VAÊN
A
7
7
8
 
 
 

7
PHUÙC
A
5
6
4
 
 
 

8
LINH
D
9
8
8
 
 
 

9
LÔÏI
C
5
6
8
 
 
 

10
LÒCH
C
1
8
3
 
 
 


C©u 1: NhËp d÷ liÖu.
C©u 2 : Cét tæng ®­îc tÝnh theo c¨n cø vµo Khoi:
NÕu Khèi A th× ®iÓm To¸n nh©n 2
NÕu Khè D th× ®iÓm NNg÷ nh©n 2
NÕu Khèi C th× ®iÓm V¨n nh©n 2
C©u 3: Cét KÕt qu¶ ®­îc tÝnh nh­ sau: Lµ §Ëu ®èi víi Tæng >=20 nh­ng kh«ng cã ®iÓm m«n nµo d­íi 3, ng­îc l¹i KÕt qu¶ sÏ lµ Rít.
C©u 4: Cét XÕp Lo¹i ®­îc tÝnh nÕu KÕt qu¶ lµ §Ëu vµ c¨n cø vµo Tæng:
Lµ giái nÕu Tong >=32
Lo¹i kh¸ nÕu Tong tõ 26 ®Õn 32
Lo¹i Trb×nh ®èi víi c¸c tr­êng hîp cßn l¹i.
NÕu K.qu¶ lµ Rít th× ®¸nh dÊu X vµo vÞ trÝ X.lo¹i.
C©u 5: Cét Sap xÕp thø tù theo cét tæng víi ®iÓm cao nhÊt lµ 1.
 H­íng dÉn thùc hµnh:
C©u 1: NhËp d÷ liÖu
C©u 2: Sö dông hµm IF, ta cã c«ng thøc sau : = IF(KHOÁI]="A", [To¸n]*2 + [V¨n] + [Nng÷], IF([KHOÁI]="D", [To¸n] + [V¨n] + [Nng÷]*2, [To¸n] + [V¨n]*2 + [Nng÷]))
§èi víi bµi to¸n nµy chóng ta sö dông hai hµm IF lång nhau bëi v× cã 3 ®iÒu kiÖn

A
B
C
D
E
F
G
H
I

SAÉP
HOÏ TEÂN
KHOÁI
TOAÙN
VAÊN
NNGÖÕ
TOÅNG
K.QUAÛ
X.LOAÏI

1
AN
A
9
10
8
 =IF(C2="A",D2*2+E2+F2,
IF(C2="D",D2+E2+F2*2,
D2+E2*2+F2))



C©u 3: §iÒu kiÖn ®Ó ®Ëu lµ :Tæng ®iÓm>=20,vµ ®iÓm cña tõng m«n >2,ta sö dô