16 bài tập excel cơ bản nâng cao + Lời giải


Author : Hải Nguyên Created : 9/20/19, 6:40 PM



Thật tất cả các bạn đang tự học excel không thể bỏ qua được 16 bài tập excel cơ bản nâng cao + Lời giải có đáp án dưới đây của chúng tôi. Nó rất hay, hiệu quả trong việc nâng cao kỹ năng sử dụng excel của bạn.


16 bài tập excel cơ bản nâng cao + Lời giải

Bài 1:  Cho bảng dư liệu sau:
Đơn vị Số trường tiểu học Số trường
chỉ có 1 điểm chính
Số trường có điểm trường Vùng khó khăn Nông thôn Thị trấn, Thị xã Thành phố
Tỉnh Sơn La 269 23 246 142 119 23 0
Tỉnh Vĩnh Phúc 205 160 45 44 132 29 0
TP. Hải Phòng 230 150 80 19 142 16 53
Tỉnh Ninh Bình 154 91 63 11 126 20 0
Tỉnh Quảng Bình 246 134 112 98 108 14 23
Tỉnh Phú Yên 148 32 116 41 90 9 19
Tỉnh Kon Tum 117 24 93 59 26 32 0
Tỉnh Bình Phước 149 21 128 70 66 20 0
TP HỒ Chí Minh 428 290 138 3 102 8 317
Tỉnh Bến Tre 192 26 166 7 166 21 0

Dựa vào bảng sô liệu trên đê vẽ đô thị như sau:
Bài 2. Cho bảng dư liệu sau:
Dựa vào bảng số liệu trên để vẽ các đồ thị như sau:
a) Biểu đồ so sánh số trường tiểu học chia theo vùng miền của 10 tỉnh

b) Biêu đô so sánh sự phân bô các trường tiêu học chia theo vùng miên trên từng tỉnh
Tương tự cho các tỉnh khác ...

Bài 3. Cho bảng dư liệu sau:
 
Tỉnh /TP Giới tính Độ tuổi Thâm niên công tác
Nam Nữ Dirới
31
31-
40
41-
50
rp A
Iren
50
7 năm 2-5 năm 6-7Ỡ
năm
77-2Ớ năm 27-5Ớ năm Trên 30 năm
Tỉnh Sơn La 2660 4921 2177 2756 2275 373 5 240 2525 2749 1718 344
Tỉnh Vĩnh Phúc 537 3639 628 2042 955 551 76 299 729 1770 768 534
Thành phố Hải Phòng 389 6370 1906 2998 1477 378 37 868 1524 2729 1286 315
Tỉnh Ninh Bình 325 3237 160 2226 778 398 0 14 276 2277 635 360
Tỉnh                   Quảng
Bình
727 3422 662 1506 1499 482 0 233 748 1473 1381 314
Tỉnh Phú Yên 1938 3203 888 1905 1907 441 3 140 1064 1724 1735 475
Tỉnh Kon Tum 730 2368 960 1490 591 57 2 372 1041 1227 385 71
Tỉnh                      Bình
Phước
1027 4110 2001 2194 776 166 13 713 1855 1869 553 134
TP Hồ Chí Minh 2195 11324 2551 5665 4140 1163 185 1348 2401 5010 3894 681
Tỉnh Bển Tre 1816 3423 706 2243 1796 494 1 164 870 2129 1638 437
 
Dựa vào bảng sô liệu trên đê vẽ các đô thị như sau:
a) Tông hợp sô liệu giáo viên của 10 tỉnh chia theo giới tính
b) Tông hợp sô liệu giáo viên chia theo từng tỉnh chi theo giới tính
c) Biêu đô tông hợp sô lượng giáo viên chia theo độ tuôi
d) Biêu đô tông hợp sô lượng giáo viên chia theo thâm niên của từng tỉnh 

Bài 4. Cho bảng dư liệu sau:
Đơn vị Thạc sĩ ĐHSP tiểu học ĐHSP CN khác CĐSP tiểu học CĐSP CN khác THSP
12+2
THSP
9+3, 9+4
Dưới THSP Các ngành đào tạo khác
Tỉnh Sơn La 0.03 4.87 0.38 29.68 1.04 29.26 32.08 2.06 0.13
Tỉnh Vĩnh Phúc 0.05 19.35 1.22 22.41 3.5 47.22 1.72 1.15 2.8
Thành phố Hải
Phòng
0.03 18.15 2.62 47.86 5.89 16.85 0.64 0.33 6.63
Tỉnh Ninh Bình 0.03 15.3 0.25 43.77 1.66 36.33 0.76 1.29 0.53
Tỉnh Quảng Bình 0.05 10.32 1.25 16.29 2.63 61.89 5.04 1.83 0.39
Tỉnh Phú Yên 0 13.3 2.3 23.71 5.58 46.94 6.85 0.95 0.12
Tỉnh Kon Tum 0 23.11 0.9 14.46 0.16 18.98 35.02 6.58 0.19
Tỉnh Bình Phước 0 7.07 1.44 6.05 0.66 29.34 52.17 2.84 0.06
Thành phố Hồ
Chí Minh
0.02 16.36 3.66 19.38 4.04 46.43 4.82 1.01 1.29
Tỉnh Ben Tre 0.04 3.68 1.41 19.6 3.99 57.76 11.95 0.84 0.15
Tổng cộng 0.02 12.93 1.9 24.47 3.21 39.41 14.01 1.59 1.38
 
Dựa vào bảng số liệu trên để vẽ các đồ thị như sau:
Số liệu tổng hợp của 10 tỉnh
 Thạc sĩ
■ ĐHSP tiều học
 ĐHSP CN khác
CĐSP tiều học
■ CĐSP CN khác
 THSP 12+2
■ THSP 9+3, 9+4
 Dưới THSP
■ Các ngành đào tạo khác

Bài 5. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
BẢNG CHI TIẾT NHẬP XUẤT HÀNG HOÁ QUÍ IV/2012
 
 
Ngày Mã hàng Tên hàng Loại Đơn vị Số lượng Đơn giá Thuế Thành tiền
10/20/2012 DBH-DB-N     kg 150      
10/15/2012 GTL-TB-N     kg 700      
10/01/2012 DBH-TB-X     kg 500      
10/07/2012 GTL-DB-X     kg 1250      
09/05/2012 DQN-TB-N     kg 975      
09/08/2012 GNT-DB-X     kg 380      
08/16/2012 DQN-DB-N     kg 2375      
05/03/2012 DBH-DB-X     kg 3000      
07/13/2012 GNT-TB-N     kg 5320      
08/14/2012 DQN-TB-X     kg 680      
 
Mã hàng Tên hàng   Loại DB TB
DBH Đường Biên Hoà DBH 45000 42000
DQN Đường Quảng Nam DQN 4000 38000
GTL Gạo Thái Lan GTL 6000 56000
GNT Gạo Nếp Thơm GNT 62000 5800
 
  1. Dùng hàm VLOOKƯP điền cột Tên hàng dựa vào 3 ký tự đầu của Mã hàng và Bảng 1.
  2. Điền thông tin vào cột Loại dựa vào 2 ký tự thứ 5 và thứ 6 của Mã hàng, nếu là ĐB thì loại là Đặc Biệt nếu là TB thì loại là Trung bình.
  3. Điền thông tin vào cột Đơn giá dựa vào Mã hàng và Bảng 2 biết rằng:
Nếu ký tự cuối của Mã hàng là N (Nhập) thì đơn giá thực của mặt hàng đó thấp hơn đơn giá cho trong bàng 2 là 5%.
Nếu ký tự cuối của Mã hàng là X (Xuất) thì đơn giá thực của mặt hàng đó cao hơn đơn giá cho trong bàng 2 là 10%.
  1. Điền thông tin vào cột Thuế biết rằng:
Nếu mặt hàng là nhập thì thuế của 1 kg sẽ bằng 0.2% đơn giá thực.
Nếu mặt hàng là xuất thì thuế của 1 kg sẽ bằng 0.5% đơn giá thực.
  1. Tính cột Thành tiền, biết rằng: Thành tiền=số lượng * Đơn giá - Thuế
Thống kê tổng số tiền thu được theo từng mặt hàng; vẽ đồ thị so sánh theo kết quả
Bài 6. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
BÂNG THEO DÕI CHI TIẾT BÁN HÀNG CÔNG TY ABC
Mã hàng Tên hàng Loại hàng Ngày bán (mm/dd/yyyy) Đơn giá Số lượng Thành tiền
PEA     11/01/2002   90  
VTB     11/05/2002   120  
SGA     11/09/2002   70  
VTA     11/14/2002   160  
ELB     11/18/2002   60  
PEB     11/21/2002   75  
SGB     11/25/2002   65  
VTA     11/27/2002   98  
ELA     11/29/2002   130  
PEB     11/30/2002   180  
 
Bảng mã
 
Mã hàng Tên hàng Đơn giá
Loại 1 Loại 2
EL ElfGas 120000 100000
PE Petrolimex 115000 95000
SG Sài Gòn Petro 125000 110000
VT VT Gas 110000 90000
 
 
  1. Xác định ten hàng căn cứ vào hai ký hiệu đầu của Mã hàng và Bảng 1.
  2. Xác định lọai hàng căn cứ vào ký hiệu cuối của Mã hàng: nếu là A thì ghi 1, B thì ghi 2.
  3. Xác định đơn giá theo Mã hàng, Loại hàng và Bảng mã.
  4. Tính cột Thành tiền= số lượng * Đơn giá
  5. Hoàn thành bảng thống kê sau:
Tên hàng Số lượng Tổng số tiền
ElfGas    
Petrolimex    
Sài Gòn Petro    
VT Gas    
f) Vẽ biểu đồ so sánh số liệu trong bảng thống kê trên.

Bài 7. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
CÔNG TY XUẤT KHẢƯ Ô TÔ AZ
Mã hàng Tên xe Nước lắp ráp Giá xuất xưởng Thuế Giá thành
TOZAVN          
FOLAVN          
TOCONB          
MIJOVN          
TOCAVN          
FOLANB          
MIPAVN          
FOESVN          
MIJONB          
 
 
Bâng 1
Mã hiệu TO FO MI
Nhãn hiệu TOYOTA FORD MITSUBISHI
 
 
Bâng 2
Mã loại Loại xe Đơn giá (USD)
VN NB
CO COROLLA 20500 21500
CA CAMRY 36300 37000
ZA ZACE 20000 22000
LA LASER 21500 23000
ES ESCAPE 34000 35000
JO JOLIE 20000 21000
PA PAJERO 36000 38000
 
  1. Điền vào cột Ten xe: gồm hiệu xe và loại xe. Hiệu xe căn cứ vào 2 ký tự đầu của mã hàng tra trong bảng 1 (dùng HLOOKƯP), loại xe căn cứ vào ký tự 3, 4 trong mã hàng tra trong bảng 2 (dùng VLOOKUP) và được thể hiện như ví dụ sau: FORD LASER.
  2. Điền vào cột Nước lắp ráp: căn cứ vào 2 ký tự cuối của mã hàng, nếu VN thì ghi là Việt Nam, nếu NB thì ghi là Nhật Bản.
  3. Tính Giá xuất xưởng căn cứ vào Mã loại và nước lắp ráp, dò tìm trong Bảng 2.
  4. Tính Thuế: nếu xe được lắp ráp ở Viện Nam thì không có thuế ngược lại thuế bằng 10% giá xuất xưởng.
  1. Giá thành = Giá xuất xưởng + Thuế, định dạng tiền theo dạng VNĐ.
  2. Hoàn thành bảng thống kế sau. Gợi ý: Dùng hàm Sumif và dùng ký tự đại diện * trong điều kiện tính toán (Ví dụ: “MITSUBISHI*”).
Nhãn hiệu Số lượng
TOYOTA  
FORD  
MITSUBISHI  
 
 
  1. Vẽ biểu đồ so sánh số liệu trong bảng thống kê trên.
Bài 8. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:

 
STT Mã bưu kiện Noi đến Phương tiện Hình thức Trọng lượng (gram) Giá cước Thành tiền
1 01USN       500    
2 01USE       200    
3 02AUE       50    
4 01SEE       250    
5 02ƯSN       150    
6 01 SIN       800    
7 02AUN       250    
8 01AUE       600    
 
  • 2 ký tự đầu trong Mã bưu kiện cho biết Mã phương tiện
  • Ký tự 3, 4 trong Mã bưu kiện cho biết Mã nước
  • Ký tự cuối trong Mã bưu kiện cho biết hình thức gửi Điền dữ liệu vào những ô còn trống theo yếu cầu sau:
  1. Nơi đến: Dựa theo Mã nước dò tìm trong Bảng 1.
  2. Phương tiện: Dựa theo Mã phương tiện dò tìm trong Bảng 2.
  3. Hình thức: Nếu ký tự cuối là N -> “Bình thường”; nếu là E -> “Nhanh”
  1. Giá cước: Dựa theo mã nước và mã phương tiện dò tìm trong Bảng 1. Gợi ý: Dùng hàm VLOOKUP kết hợp hàm If đề dò tìm.
  2. Thành tiền =Trọng lượng*Đơn giá. Nếu hình thức gửi là Nhanh thì tăng 10% thành tiền. Định dạng cột thành tiền theo dạng Việt Nam đồng.
  3. Hoàn thành bảng thống kê dưới đây. Vẽ đồ thị so sánh số liệu trong bảng kết quả.
Thống kê doanh thu theo hình thức gửi
Phương tiện Bình thường Nhanh
Máy bay    
Tàu thủy    
 
 
Bài 9. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
SÔ THEO DÕI BÁN HÁNG THÁNG 4/2012, CÔNG TY ANZ
TT Ngày bán Mã hàng Tên hàng Tốc độ Số lượng Đơn giá Thành tiền Ghi chú
1 3/12/2003 PH40X     5      
2 3/12/2003 SA52X     12      
3 3/18/2003 PH56X     8      
4 3/25/2003 LG52X     7      
5 4/5/2003 AS40X     11      
6 4/10/2003 AS56X     6      
7 4/28/2003 AS52X     14      
8 5/2/2003 PH60X     9      
 
Cho biết:
 
• Hai ký tự đầu trong Mã hàng cho biết Mã sản xuất
• Ký tự còn lại cho biết tốc độ của CD-ROM
Bảng 1 
Mã sản xuất Tên hãng
PH Philips
SA Samsung
LG LG/Cyber
AS Asus

Bảng 2
Tốc đô Giá (USD) phụ thuộc vào số lượng
<=10 >10
40 19 17.5
52 28 26
56 34 32.5
60 38 36

Điền dữ liệu vào những ô còn trống theo yếu cầu sau:
a) Tên hàng: Dựa theo Mã hàng dò tìm trên Bảng 1 để lấy tên Hãng sản xuất và thể hiện
như sau “CD-ROM” + Tên hãng. Ví dụ: CD-ROM Samsung.
b) Tốc độ: Dựa vào 2 ký tự thứ 3,4 của Mã hàng và đổi thành kiểu số
 
  1. Đơn giá: Dựa theo tốc độ CD và số lượng, dò tìm trên Bảng 2 để lấy đơn giá.
  1. Thành tiền=Đơn Giá * số lượng * Tỷ giá; định dạng thành kiểu VND.
Tỷ giá: 1 USD = 21500 VNĐ.
  1. Ghi chú: ghi “Tặng ổ cứng” đối với mặt hàng của hãng Asus và có số lượng >5.
  2. Hoàn thành bảng thống kê sau. Vẽ đồ thị so sánh theo bảng số liệu tính toán được.
Thống kế theo từng mặt hàng
Mặt hàng Số lượng Tổng tiền
Philips    
Samsung    
LG/Cyber    
Asus    
 
 
Bài 10. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
BẢNG TỎNG HỢP TIỀN ĐIỆN
Công ty điện lực Thanh Xuân
TT Chủ hộ Hình thức sử dụng Chỉ số trước Chỉ số
sau
Điện tiêu thụ Tiền điện Tiền công tơ Tổng
Ấ ..X sô tiên
1 Hoàng Vân Sản xuất 125 650        
2 Thanh Bình Kinh doanh 15 90        
3 Duy Khánh Tiêu dùng 50 180        
4 Kinh Doanh Sản xuất 260 580        
5 Mai Lan Tiêu dùng 75 125        
6 Mùa Thu Tiêu dùng 0 80        
7 Quảng Bình Kinh doanh 125 325        
 
a) Tính Điện tiêu thụ cho mỗi hộ.
 
b) Dựa vào bảng danh mục dưới đây, sử dụng hàm VLOOKUP, tính Tiền điện (= Điện tiêu thụ X Đơn giá) và Tiền công tơ cho mỗi chủ hộ:
Bảng tiền công tơ và giá điện
Hình thức sử dụng Tiền công tơ (đ/tháng) Đơn giá (đ/số)
Sản xuất 20000 2000
Kinh doanh 10000 800
Tiêu dùng 5000 500
  1. Tính Tổng số tiền = Tiền điện + Tiền công tơ
  2. Hãy cho biết số hộ sử dụng trên 100 số điện.
e) Hoàn thành bảng thống kê sau:
Loại SỐ hộ Tiền
Sản xuất    
Kinh doanh    
Tiêu dùng    
 
 
f) Vẽ biểu đồ so sánh theo số liệu của bảng thống kê trên. Hiển thị biểu đồ theo 2 chiều dữ liệu (Design/Data/Switch Row/Column).
Bài 11. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
ĐIỂM THI HẾT HỌC KỲ I - NĂM 2011
TT Tên Mã khoa Tên khoa Toán Tin Điểm TB xếp loại Ghi chú
1 Hùng ENG   4 7      
2 Bình ITA   6 8      
3 Vân RUS   8 9      
4 Bình ITA   9 10      
5 Doanh ENG   5 8      
6 Loan ENG   5 4      
7 Anh FRA   9 6      
8 Thu RUS   4 10      
9 Khánh ITA   6 7      
10 Ngân ENG   10 8      
 
a) Dùng hàm VLOOKUP để điền vào cột Tên khoa: Dựa vào cột Mã khoa và bảng sau:
 
Mã khoa Tên khoa
ENG Tiếng Anh
FRA Tiếng Pháp
RƯS Tiếng Nga
ITA Tiếng Italia
 
 
b) Tính điểm trung bình, biết hệ số Toán là 2, Tin là 3.
c) Căn cứ vào Bảng xếp loại dưới đây thực hiện xếp loại học sinh:
0< Điểm TB <5 5< Điểm TB <7 7< Điểm TB <8 8< Điểm TB <9 Điểm TB >9
Kém Trung bình Khá Giỏi Xuất sắc
 
d) Điền vào cột Ghi chú thông tin học bổng theo kết quả xếp loại như sau: Khá -> Loại 3, Giỏi -> Loại 2, Xuất sắc -> Loại 1, còn lại bỏ trống.
 
e) Hoàn thành bảng thống kê sau; vẽ biểu đồ so sánh KQHT của sv theo xếp loại:
xếp loại Số lượng
Kém  
Trung bình  
Khá  
Giỏi  
Xuất sắc  
 
 
Bài 12. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
BẢNG LƯƠNG THÁNG 12-2018
Hệ số lương cơ bản: 3.000 USD
TT Tên Mã cv Tiền 1 ngày Số ngày LV Phụ cấp cv Lương Thu nhập
1 Tuấn 50.000 28      
2 Nga PGĐ 40.000 25      
3 Quỳnh TP 35.000 27      
4 Vân pp 30.000 24      
5 Chi NV 20.000 25      
6 Nga NV 20.000 27      
7 Sơn TP 32.000 26      
8 Hiền NV 20.000 25      
9 Lan pp 28.000 23      
10 Long BV 15.000 28      
 
a) Dựa vào bảng dưới đây, dùng hàm VLOOKƯP tính Phụ cấp cv như sau:
 
Chức vụ Phụ cấp
0
PGĐ 0
TP 150
pp 200
NV 250
BV 400
 
 
  1. Tính Lương = Tiền 1 ngày X số ngày LV.
Trong đó, nếu số ngày LV > 25 ngày thì mỗi ngày vượt trội tính bằng 2 ngày LV.
  1. Tính Thu nhập = Lương + Phụ cấp cv.
  2. Hoàn thành bảng thống kê sau.
Chức vụ Số người Tổng thu nhập Thu nhập bình quân
TP      
pp      
NV      
BV      
e) Vẽ biểu đồ so sánh thu nhập của cán bộ theo chức vụ.
Bài 15. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
KÉT QUẢ THI CƯÓI KỲ LỚP TIN HỌC
Trung tâm Đào tạo XYZ
TT Tên Giói tính Toán Tin Đạo đức Tổng điểm xếp loại Học bổng
1 Hùng Nam 4 7 D      
2 Bình Nữ 6 8 A      
  Vân Nữ 8 9 B      
  Bình Nam 9 10 A      
  Doanh Nam 5 8 D      
  Loan Nữ 5 4 c      
  Anh Nam 9 6 A      
  Thu Nữ 4 10 B      
  Khánh Nam 6 7 c      
  Ngân Nữ 10 8 B      
 
  1. Đánh số thứ tự tự động cho cột TT.
  2. Tính Tổng điểm như sau: Tổng điểm = Toán + Tin và :
  3. Cộng thêm 1 điếm vào Tống điếm cho học sinh đạt Đạo đức A
  4. Cộng thêm 0.5 điểm vào Tổng điểm cho học sinh đạt Đạo đức B
  5. Trừ Tổng điểm đi 1 cho học sinh đạt Đạo đức D
  6. Giữ nguyên Tổng điểm cho Đạo đức C
Bài 16. Bài tập tổng hợp (hàm cơ bản, thống kê, VLOOKƯP). Cho bảng dư liệu sau:
KÉT QUẢ KINH DOANH BÁNH MÚI
Tháng 6 năm 2012
Mã hàng Tên hàng Đơn vị tính SỐ lượng Đơn giá Thành tiền Phí vận chuyển Tổng chi phí
BN00DP   Cái 20        
BN00DP   Kg 15        
BN00MX   Cái 20        
MƯ00MX   Kg 25        
BN00GI   Cái 55        
TROOBL   Kg 40        
CFOOBL   Kg 35        
CF00BA   Kg 80        
MU00BA   Cái 15        
 
a) Điền Tên hàng và Đơn giá dựa vào 2 ký tự đầu tiên của Mã hàng và Bảng danh mục sau:
 
Mã hàng Tên hàng Đơn giá Phí vận chuyển
BN Bánh ngọt 80000 1000
MU Mứt 50000 500
TR Trà 40000 300
CF Cà phê 70000 700
 
  1. Thành tiền = số lượng * Đơn giá. Nếu số lượng >25 thì giảm 10% của thành tiền, ngược lại không giảm.
 
  1. Tính Tổng chi phí = Thành tiền + (Phí vận chuyển * số lượng).
  1. Thực hiện bảng thống kê dưới đây:
Tên hàng Bánh ngọt Mứt Trà Cà phê
Tổng chi phí        
e) Vẽ biểu đồ với số liệu là bảng thống kê trên.

Bạn nên xem thêm: cấu trúc động từ + to + động từ nguyên mẫu  &&  sử dụng To be done
 
Chúc bạn thành công !