Bài tập Microsoft Excel

điểm một trong các môn không thấp hơn 3.5: xếp loại Trung bình
Còn lại xếp loại Yếu
Hd: =If(And(G2>=8,Min(D2:F2)>=6.5),“G“, If(And(G2>=6.5,Min(D2:F2)>=5), “K“,
If(And(G2>=5,Min(D2:F2)>=3.5), “TB“,“Y“)))
4. Thêm cột xếp vị thứ vào sau cột xếp loại và sau đó dựa vào ĐTB để xếp loại
Hd : =Rank(G2,$G$2:$G$10)
5. Sắp xếp danh sách trên theo aphabe theo Tên và tăng dần theo Điểm TB ( nghĩa là sắp xếp theo
Tên nếu trùng tên thì sắp xếp theo Điểm TB )
Hd : Chọn bảng , chọn lệnh Data/Sort trong mục Sort by ta chọn cột tên, trong mục then by chọn
Điểm TB
Bài tỏỷp Microsoft Excel
6
Bài thực hành 9 :
1. Lập bảng tính sau :
Công ty trách nhiệm vô hạn tài hoa
A B C D E F G H
1
2
Stt Họ và tên Chức
vụ
LCB mổi
ngày công
NLV Lương PCCV Cộng
3 1 Tạ Văn Ân GĐ 40000 25
4 2 Võ Thị Bích PGĐ 30000 18
5 3 Lê Minh Tâm TP 20000 28
6 4 Vũ Thị Lan NV 10000 30
7 5 Lê Thị Ninh NV 10000 18
8 6 Lê Văn Minh NV 10000 17
9 7 Đỗ Thanh Tùng NV 10000 16
10 Tổng
2. Tính cột lương = LCB*NLV
 Nếu NLV >25 thì mỗi ngày dư được tính gấp đôi
 Hd : =D3*If(E3<=25,E3,25+(E3 -25)*2)
3. Tính cột PCCV : Nếu Chức vụ là “GĐ” thì PCCV là 500000
Nếu Chức vụ là “PGĐ” thì PCCV là 400000
Nếu Chức vụ là “TP” thì PCCV là 300000
Còn lại thì PCCV là 100000
Hd : =If(C3=“GĐ“,500000,if(C3=“PGĐ“,400000 if(C3=“TP“,300000,100000)))
4. Tính tổng cột Lương, PCCV
Hd : =Sum(F3:F9)
=Sum(G3:G9),
5. Tính cột Cộng=Lương + PCCV
6. Trang trí lại bảng tính
7. Lưu bảng tính vào thư mục riêng của mình với tên Bt9.xls
Bài tỏỷp Microsoft Excel
7
Bài thực hành 10 :
Tạo bảng tính sau :
Khách sạn hồng hà
Bảng doanh thu phòng
A B C D E F G H
1
2
Stt Phòng Ngày đến Ngày đi Số ngày Loại
phòng
Số tt
phòng
Thành
tiền
3 1 A1 12/06/99 15/06/99
4 2 A1 12/06/99 15/06/99
5 3 C1 12/06/99 21/06/99
6 4 C2 15/06/99 25/06/99
7 5 A2 17/06/99 28/06/99
8 6 B2 01/07/99 04/07/99
9 Có bao nhiêu phòng loạI A
10 Tính tổng số tiền phòng loạI A thu được
Ghi chú : Cột phòng ký tự đầu là loại, ký tự thứ 2 là số thứ tự
1. Tính số ngày = ngày đi - ngày đến
 Hd : =D3-C3 , sau đó chọn cột E này và định dạng lại kiểu số
2. Định dạng lại ngày đi và ngày đến theo kiểu DD/MM/YY
3. Điền cột Loại phòng và Số tt phòng từ cột phòng
 Hd : =Left(B3,1)
 =Right(B3,1)
 4. Tính cột thành tiền =Số ngày * đơn giá
đơn giá được cho như sau :
Nếu loại phòng A đơn giá là 50
Nếu loại phòng B đơn giá là 40
Nếu loại phòng C đơn giá là 25
Hd : =E3*If(F3=“A“,50,If(F3=“B“,40,25))
5. Tính có bao nhiêu phòng loạI A , tổng số tiền phòng loạI A thu được
Hd : = CountIf(F3:H8, “A“)
= SumIf(F3:H8, “A“,H3:H8)
6. Kẻ khung bảng tính
7. .Đánh số trang, định lại lề cho trang in : lề trên 2 cm, lề dưới 1 cm, lề trái 2 cm, lề phải 1 cm ,
đánh số trang cho bảng tính
Hd : Thực hiện lệnh File/Page setup
8. Xem bảng tính ở dạng trang in, thực hiện lệnh in thử bảng tính
9. Lưu lại với tên Bt9.xls
Bài thực hành11 :
Bài tỏỷp Microsoft Excel
8
1.Tạo bảng tính như các mẩu sau :
Thống kê bán hàng
A B C D E F G H I J
1
2
3
Stt Họ lót Tên
MH Tên
hàng
Đơn giá
S.lượng
T.tiền
Tiền
giảm
Thành
tiền còn
lại
4 1 Trần Văn Anh A 20
5 2 Huỳnh Ngọc Hương B 10
6 3 Mai Tiến Dũng B 3
7 4 Tạ Duy Thành C 10
8 5 Lê Văn Mạnh D 15
9 6 Lê Trịnh Anh C 6
10 Tổng
11 Bảng mặt hàng
12 M H Tên hàng Đơn giá
13 A Thuốc lá 2000
14 B Sữa 6000
15 C Trà 10000
16 D Bánh 5000
Hãy tính toán theo các yêu cầu sau :
1. Hãy điền vào cột Đơn giá và tên hàng, đơn giá và tên hàng lấy từ bảng mặt hàng
Hd : =Vlookup(D4,$A$13:$C$16,2), =Vlookup(D4,$A$13:$C$16,3)
2 . Tính cột thành tiền=số lượng*đơn giá
3. Tính cột tiền giảm là : 5000 nếu thành tiền vược quá 50000 , ngược lại thì có giá trị bằng 0
Hd : =If(H4>=50000,5000,0)
4 .Tính cột thành tiền còn lại = thành tiền -tiền giảm
5. Tính tổng cộng các cột Thành tiền , Tiền giảm và Thành tiền còn lại .
6. Sắp xếp bảng trên theo Tên nếu tên trùng nhau thì sắp xếp theo Mã hàng
Ghi chú : Tính cột Tên hàng và Đơn giá dựa vào bảng mặt hàng (HD : Dùng hàm
VLOOKUP để điền vào )
Bài thực hành 12 :
Tạo bảng tính sau :
Năm 1992 1993 1994 1995 1996
LãI 100 130 160 240 300
Doanh thu 1000 1200 1500 2000 2400
Vẽ biểu đồ theo hình cột để biểu diển bảng tính trên
Baỡi thổỷc haỡnh 13 :
Bài tỏỷp Microsoft Excel
9
Haợy taỷo baớng tờnh nhổ dổồùi õỏy vaỡ õióửn tỏỳt caớ thọng tin vaỡo caùc ọ trọỳng vaỡ dổỷa vaỡo baớng danh
muỷc phờa dổồùi.
A B C D E F G H
1 BAẽO CAẽO BAẽN HAèNG
2 STT Maợ HÂ Màỷt haỡng Ngaỡy Âồn vở SLổồỹng Âồn giaù Thaỡnh tióửn
3 1 SDIM2 07-12-98 5
4 2 HDM64 12-12-98 2
5 3 CDM74 08-12-98 12
6 4 SDIM4 09-12-98 7
7 5 HDM64 11-12-98 3
8 6 CDM74 07-12-98 5
9 7 SDIM2 10-12-98 6
10 8 CDM74 14-12-98 4
11
12 Maợ Tón màỷt haỡng Âồn vở Âồn giaù
13 SM4 Âộa móửm 1.4Mb Thuỡng 140
14 SM2 Âộa móửm 1.2Mb Thuỡng 120
15 C74 Âộa CD 740 Mb Thuỡng 200
16 H64 Âộa cổùng 3.2Mb Caùi 120
17
YÃU CÁệU
1) Dổỷa vaỡo kờ tổỷ õỏửu vaỡ kờ tổỷ cuọỳi cuớa maợ HÂ ,tỗm trong b aớng danh muỷc õóứ õióửn vaỡo tón màt haỡng
,õồn vở, õồn giaù
 Hổồùng dỏựn
- Âỏửu tión õởnh nghộa baớn :
- Âaùnh dỏỳu baớng danh muỷc choỹn INSERT -> NAME ->DIFINE ->NAME IN WORKBOOK ->
goớ tón vaỡo laỡ BANG ->ADD -> OK
- Trong ọ màỷt haỡnh goớ
= VLOOKUP(LEFT(B3;1)&RIGHT(B3;2);BANG;2;0)
- Trong ọ õồn vở goớ
= VLOOKUP(LEFT(B3;1) &RIGHT(B3;2);BANG;3;0)
- Trong ọ õồn giaù goớ
= VLOOKUP(LEFT(B3;1)&RIGHT(B3;2);BANG;4;0)
Bài tỏỷp Microsoft Excel
10
Bài thổỷc haỡnh 14:
1. Taỷo baớng tờnh sau :
A B C D E F G H I J
1 BAẽO CAẽO THUÃÚ THAẽNG 1
2 Sọỳ õàng
Kyù kinh
Doanh
Tón chồỹ Màỷt
Haỡng
Kinh
Doanh
Tón Màỷt
Haỡng
Vọỳn kinh
Doanh
Tióửn
Thuóỳ
Ngaỡy hóỳt
Haỷn õoùng
Thuóỳ
Ngaỡy õoùng
thổỷc tóỳ
Tióửn
phaỷt
Thuóỳ
phaới
nọỹp
3 001/DK VKT 10.000.000 01/01/98 01/01/98
4 001/BT QA 2.000.000 15/01/98 19/01/98
5 002/BC TP 5.000.000 13/01/98 15/01/98
6 004/TD MP 15.000.000 20/01/98 25/01/98
7 004/PN GD 2.000.000 20/01/98 29/01/98
8 002/DK QA 10.000.000 18/01/98 17/01/98
9 002/TD GD 1.500.000 02/01/98 09/01/98
10 003/BC MP 9.000.000 10/01/98 10/01/98
13
14 BAÍNG1 BAÍNG 2
15 Maợ chồỹ Tón chồỹ M.haỡng Tón haỡng Thuóỳ%
16 TD Tỏn Âởnh TP Thổỷc Phỏứm 5
17 BT Bóỳn Thaỡnh GD Giỏửy deùp 3
18 BC Baỡ Chióứu MP Myợ phỏứm 10
19 DK Âa Kao VKT Vaới Kate 9
20 PN Phuù Nhuỏỷn QA Quỏửn Aùo 8
YÃU CÁệU
1) Âióửn tón chồỹ vaỡ màỷt haỡng dổỷa vaỡo baớng 1vaỡ baớng 2 theo maợ õàng kyù kinh doanh vaỡ maợ màỷt
haỡng kinh doanh
2) Tióửn thuóỳ = vọỳn kinh doanh * Laợi suỏỳt thuóỳ
3) Tióửn phaỷt nóỳu ngaỡy nọỹp quaù haỷng laỡ 3 thỗ lỏỳy sọỳ ngaỡy quaù haỷng x2%
4) Thuóỳ phaới nọỹp = Tióửn thuóỳ + Tióửn phaỷt
5) Trờch ra danh saùch nhổợng ngổồỡi bở phaỷt
HặÅẽNG DÁÙN :
Cọỹt tón chồỹ = VLOOKUP(RIGHT(A3;2);$A$16:$B$20;2;0)
Tón màỷt haỡng =VLOOKUP(C3;$D$16:$F$20;2;0)
Tióửn thuóỳ =E3*VLOOKUP(C3;$D$16:$F$20;3;0) %
Tióửn phaỷt =IF (H3-G3>3;(H3-G3)*F3*2%;0)
Tióửn thuóỳ phaới nọỹp =F3+I3
5. Lóỷnh Data  Filter
Bài tỏỷp Microsoft Excel
11
Baỡi thổỷc haỡnh 15 :
A B C D E F G H
1 BAẽO CAẽO TầNH HầNH DANH THU CAẽC CLB TRONG NÀM
2 STT Maợ CLB Tón CLB Tón nổồùc SL
CÂV
Giaù veù Doanh
thu
3 IAC1 80000
4 EMU2 60000
5 FBS2 50000
6 SBA1 100000
7 SRE1 80000
8 PBI2 40000
9 Baớng maợ nổồùc Maợ Cỏu laỷc bọ ỹ
10 Maợ Tón nổồùc Maợ Tón CLB Giaù veù
11 I Yẽ MU Machaster 10
12 G Âổùc PS Paris SG 12
13 S Tỏy Ban nha RE Real Madrit 10
14 P Bọử õọử nha BI Benfica 7
15 F Phaùp AC AC Milan 12
16 E Anh BA Barcelena 10
17 Baớng thọỳng kó
18 Haỷng Tọứng D Thu Tọứng sọỳ õọỹi
19 1
20 2
YÃU CÁệU
1) Nhỏỷp vaỡ trang trờ baớng tờnh sau õoù thổỷc hióỷn caùc yóu cỏửu sau
2) Âióửn tón caùc cỏu laỷc bọỹ tổỡ baớng maợ cỏu laỷc bọỹ . Âióửn tón nổồùc tổỡ baớng maợ nổồùc
( Giaiớ thờch : Vồùi cọỹt Maợ CLB Kyù tổỷ õỏửu laỡ maợ nổồùc kyù tổỷ 2 vaỡ 3 maợ cỏu laỷc bọỹ , kyù tổỷ cuọỳi laỡ
haỷng)
3) Âióửn cọỹt giaù veù tổỡ baớng maợ cỏu laỷc bọỹ
4) Tờnh doanh sọỳ : = sọỳ lổồỹng CÂV * Giaù veù
5) Âởnh daỷng giaù veù vaỡ doanh thu theo daỷng tióửn tuóỷ $
6) Sàừp xóỳp theo thổù tổỷ giaớm dỏửn cuớa CÂV
7) Tờnh baớng thọỳng kó
HặÅẽNG DÁÙN
1) Tổỷ laỡm
2) =VLOOKUP(MID(B3;2;2);$E$11:$G$16;2;0)
3) =VLOOKUP(MID(B3;2;2);$E$11;$G$16;3;0)
4) =E3*F3
5) =IF(OR(AND(RIGHT(B3;1) =”1”;G3>900000);AND(RIGHT(B3;1)
=”2”;G3>600000);”Laợi”;”Lọự”)
6) Duỡng haỡm Dsum vaỡ DcountA, phaới cho thóm vuỡng õióửu kióỷn
Bài tỏỷp Microsoft Excel
12
Baỡi thổỷc haỡnh 16 :
A B C D E F G H I
1 STT Maợ chuyóỳn Tuyóỳn
DL
Khồới
haỡnh
Kót thuùc Phờ àn ồớ Phổồng
tióỷn
Phờ
P.tióỷn
Tọứng
cọỹng
2 HUE-01A 03/01/98 11/01/98
3 VTA-01C 01/02/98 04/01/98
4 NTR-01B 05/01/98 15/01/98
5 DLA-02C 07/01/98 09/01/98
6 HUE-02C 18/01/98 25/01/98
7 Baớng phuỷ
8 Chi phờ
9
3 kyù tổỷỷ
õỏửu
Tuyóỳn
Maùy bay Taỡu Xe
Chi phờ
àn , ồớ
10 HUE 500000 300000 50000 50000
11 NTR 200000 100000 50000 80000
12 DLA 180000 30000 70000
13 VTA 15000 60000
I. YÃU CÁệU
1)Nhỏỷp vaỡ trỗnh baỡy baớng tờnh sau õoù thổỷc hióỷn caùc yóu cỏửu sau :
*Giaới thờch maợ chuyóỳn
-3 kờ tổỷ õỏửu tuyóỳn du lởch
-2 kờ tổỷ cuọỳi laỡ sọỳ chuyóỳn
-1 kờ tổỷ cuọỳi laỡ phổồng tióỷn
A: Maùy bay ;B;laỡ taỡu ;C:xe
2) Tuyóỳn DL dổỷa 3 kờ tổỷ õỏửu cuớa cọỹt maợ chuyóỳn vaỡ tra trong baớng phuỷ
3) Phờ àn ồớ :Dổỷa vaỡo 3 kờ tổỷ õỏửu cuớa cọỹt maợ chuyóỳn vaỡ trong baớng phuỷ = chi phờ A25/ngaỡy * sọỳ
ngaỡy
4) Phổồỹng tióỷn dổỷa 1 kờ tổỷ cuọỳi cuỡng cuớ a cọỹt maợ chuyóỳn
5) Phờ phổồng tióỷn tra trong baớng phuỷ
6) Tọứng cọỹng bàũng caùc chi phờ
7) Sàừp xóỳp baớng tờnh theo thổù tổỷ tàng cuớa ngaỡy khồới haỡnh ,nóỳu cuỡng ngaỡy khồới haỡnh thỗ sàừp xóỳp
theo thổù tổỷ tàng cuớa maợ chuyóỳn
8) Trờch ra danh saùch caùc chuyóỳn du lởch õi Huóỳ
II. HặÅẽNG DÁÙN
1)Tổỷ laỡm
2) = VLOOKUP(LEFT(B3;3);$A$12;$B$15;2;0)
3) = (E3-D3)*VLOOKUP(LEFT (B3;3);$A$12;$F$15;6;0)
4) = IF(RIGHT(B3;1)=”A”;”Maùy bay “;IF(RIGHT(B3;1)=”B”;”Taỡu “;”Xe”))
5) =VLOOKUP(LEFT(B3;3);$A$12;$E$15;IF(RIGHT(B3;1)=”A”;3;IF(RIGHT(B3;1)=”B”;4;5));0)
6) =F3+H3
7) Data ->sort ->Sort by -> rọửi theo caùch phổồng õaợ hoỹc