10 Bài tập excel nâng cao + lời giải cho ai cần
Nếu bạn đang đi tìm danh sách, list bài tập excel hãy xem ngay 10 Bài tập excel nâng cao + lời giải cho ai cần, thực sự cần
Bài 1: Một số hàm cơ bản. Cho bảng dư liệu sau:
DANH SÁCH THƯỞNG THÁNG 3, CÔNG TY ANZ
|
TT |
Họ đệm |
Tên |
Giới tính |
Ngày công |
Thưởng 8-3 |
Thưởng A |
|
1 |
Trần Thanh |
Mai |
Nam |
25 |
||
|
2 |
Phạm Hùng |
Cường |
Nam |
24 |
||
|
3 |
Lê Ngọc |
Mơ |
Nữ |
26 |
||
|
4 |
Phạm Hùng |
Dũng |
Nữ |
22 |
||
|
5 |
Nguyễn Thành |
Công |
Nam |
27 |
||
|
6 |
Lê Hoài |
Bắc |
Nữ |
25 |
||
|
7 |
Vũ Ngọc |
Minh |
Nam |
21 |
||
|
8 |
Nguyễn Mai |
Lan |
Nữ |
26 |
a) Thưởng 8-3: thưởng 200.000 cho những nhân viên Nữ, còn lại không được thưởng.
- Thưởng A: thưởng 300.000 cho những nhân viên có ngày công >=24, còn lại không được thưởng.
- Thêm vào cột Thưởng B: thưởng 100.000 cho những nhân viến Nam có ngày công >26 hoặc nhân viên Nữ có ngày công >25.
Bài 2. Sử dụng MS Excel hoàn thành bảng dữ liệu dưới đây:
BẢNG CHI TIÉT BÁN HÀNG
|
STT |
Mã hàng |
Tên hàng |
Ngày bán |
Số lượng |
Đơn giá |
Thành Tiền |
|
1 |
BDDQ |
05/05/2012 |
12 |
|||
|
2 |
BDNT |
06/08/2012 |
25 |
|||
|
CT |
07/03/2012 |
10 |
||||
|
BDGN |
08/03/2012 |
60 |
||||
|
BDTS |
09/08/2012 |
22 |
||||
|
BDGN |
06/01/2012 |
24 |
||||
|
CT |
06/03/2012 |
100 |
||||
|
BDTS |
07/06/2012 |
240 |
||||
|
BDTS |
09/08/2012 |
15 |
||||
|
CT |
10/08/2012 |
5 |
- Thực hiện:
- Thao tác tự động điền dữ liệu vào cột STT (theo tứ tự tăng dần 1,2,3...).
- Điền cột Tên hàng: Nếu 2 ký tự đầu của Mã hàng là “CT” ghi là “Công tắc” còn lại ghi là “Bóng đèn”.
- Tính Đơn giá dựa vào 2 ký tự cuối của Mã hàng và bảng sau:
|
Mã hàng |
Đơn giá |
|
DQ |
12.000 |
|
TS |
14.500 |
|
NT |
16.000 |
|
GN |
15.000 |
|
CT |
3.000 |
- Thành tiền = số lượng * Đơn giá. Nếu ngày bán sau ngày 01/06/2012 thì giảm 10% thành tiền.
- Hoàn thành bảng thống kê sau:
|
Tên hàng |
Tổng số |
|
Công tắc |
|
|
Bóng đèn |
Bài 3. Một số hàm cơ bản. Cho bảng dư liệu sau:
DANH SÁCH LƯƠNG THÁNG 12 CHO CÁN Bộ CÔNG TY ABC
|
TT |
Họ tên |
Chức vụ |
Năm sinh |
Lương CB |
Ngày |
Lương |
Tạm ứng |
|
1 |
Đào Mai |
GĐ |
1967 |
900 |
20 |
||
|
2 |
Ngô Nhu |
PGĐ |
1975 |
750 |
26 |
||
|
3 |
Mai Lan |
PGĐ |
1968 |
600 |
25 |
||
|
4 |
Ngọc Lân |
TP |
1958 |
450 |
23 |
||
|
5 |
Nguyễn Hương |
TP |
1982 |
600 |
23 |
||
|
6 |
Quốc Khánh |
PTP |
1977 |
450 |
22 |
||
|
7 |
Phạm Thành |
PTP |
1956 |
300 |
19 |
||
|
8 |
Trần Thuỷ |
NV |
1972 |
300 |
18 |
||
|
9 |
Nguyễn Hương |
NV |
1985 |
300 |
27 |
||
|
10 |
Lê La |
NV |
1986 |
300 |
28 |
- Thêm vào cột Tuối bên phải cột Năm sinh theo ngày giờ hệ thống, sau đó tính tuối của cán bộ, nhân viên.
- Tính lương của nhân viên = Lương CB * NGÀY.
- Tính tạm ứng = 80% * Lương.
- Thêm vào một cột Thưởng kế cột Lương, tính thưởng
Trong đó: Nếu chức vụ là GĐ thưởng 500000, PGD thưởng 400000, TP thưởng 300000, PTP thưởng 200000, còn lại thưởng 100000.
- Thêm vào cột Còn lại ở cuối bảng tính, tính Còn lại =Lương + Thưởng - Tạm ứng.
- Tính tổng số tiền còn phải chi cho cán bộ, nhân viên theo danh sách trên; Tính lương bình quân. Tính lương cao nhất, Lương thấp nhất.
Bài 4. Một số hàm cơ bản. Cho bảng dư liệu sau:
BÁO CÁO TÔNG HỢP PHÂN PHỐI NHIÊN LIỆU
|
TT |
Chứng từ |
Đơn vị nhận |
số lượng |
Xăng |
Gas |
Dầu lửa |
|||
|
SỐ lượng |
Thành tiền |
SỐ lượng |
Thành tiền |
SỐ lượng |
Thành tiền |
||||
|
1 |
X001C |
Công ty Mây trắng |
100 |
||||||
|
2 |
G001K |
Mây xanh Co |
150 |
||||||
|
X002K |
Đội xe |
200 |
|||||||
|
L001C |
Công ty ABC |
100 |
|||||||
|
L002C |
Công ty ANZ |
50 |
|||||||
|
G002C |
XN cơ khí |
120 |
|||||||
|
G003K |
XN đóng tầu |
80 |
|||||||
|
Tổng cộng |
|||||||||
Bảng giá
|
Mặt hàng |
KD |
cc |
|
Xăng |
500 |
150 |
|
Gas |
450 |
120 |
|
Dầu lửa |
200 |
100 |
- Căn cứ vào ký tự đầu tiến của Chứng từ để phân bổ số lượng vào các cột số lượng của Xăng, Gas và Dầu lửa.
- Nếu ký tự đầu của chứng từ là X thì số lượng được phân bổ vào cột Xăng.
- Nếu ký tự đầu của chứng từ là G thì số lượng được phân bổ vào cột Gas.
- Nếu ký tự đầu của chứng từ là L thì số lượng được phân bổ vào cột Dầu lửa.
- Tính thành tiền cho mỗi cột = số lượng * Đơn giá
Trong đó đơn giá dựa vào bảng giá, có 2 loại giá: giá cung cấp (CC) và giá kinh doanh (KD); nếu ký tự phải của chứng từ là c thì lấy giá cung cấp, ngược lại lấy giá kinh doanh.
- Tính tổng cho mỗi cột.
- Tính tổng số chứng từ phải xuất HĐ = Tổng của các số là ký tự thứ 4 của mã chứng từ.
- Trích xuất ra một danh sách mới với điều kiện số lượng>100.
Bài 5. Một số hàm cơ bản. Cho bảng dư liệu sau:
TÔNG HỢP SỐ LIỆU KHÁCH THUÊ PHÒNG KHÁCH SẠN
|
TT |
Họ tên ỉdìách hàng |
số phòng |
Ngày vào |
Ngày ra |
Số ngày ở |
số tuần |
Số ngày lẻ |
|
1 |
Trần Thanh |
100VIP |
30/09/2012 |
05/10/2012 |
|||
|
2 |
Phạm Hùng |
201NOM |
23/09/2012 |
04/10/2012 |
|||
|
3 |
Lê Ngọc |
205NOM |
06/09/2012 |
15/09/2012 |
|||
|
4 |
Phạm Hùng |
209NOM |
12/09/2012 |
16/09/2012 |
|||
|
5 |
Nguyễn Thành |
102NOM |
28/09/2012 |
29/09/2012 |
|||
|
6 |
Lê Hoài |
107VIP |
25/09/2012 |
26/09/2012 |
|||
|
7 |
Vũ Ngọc |
209NOM |
18/09/2012 |
21/09/2012 |
|||
|
8 |
Nguyễn Mai |
210VIP |
10/09/2012 |
11/09/2012 |
|||
|
9 |
Mộng Mơ |
202VIP |
22/09/2012 |
26/09/2012 |
|||
|
Tổng cộng |
|||||||
a) Tính số ngày ở = Ngày vào - Ngày ra. Tính số tuần, số ngày lẻ (dùng hàm INT, MOD)
b) Tính tổng số ngày ở, Tổng số tuần, Tổng số ngày lẻ.
Xem thêm bộ 3 đề thi kế toán của học viện ngân hàng:
⇒ đề thi thương mại ngân hàng 2021
⇒ đề thi tài chính tiền tệ năm 2021
⇒ đề thi tài chính doanh nghiệp
Bài 6. Hàm dò tìm (VLOOKUP). Cho bảng dữ liệu sau:
BẢNG LƯƠNG CÁN Bô CÔNG TY ANZ
|
TT |
Họ tên |
Mã ngạch |
Tên ngạch |
Tên đơn vị |
Lương CB |
Thực lĩnh |
|
001 |
Đào Hoa Mai |
1003 |
Phòng Hành chính |
1,200,000 |
||
|
002 |
Ngô Văn Nhu |
1002 |
Phòng Hành chính |
1,850,000 |
||
|
003 |
Nguyễn Hương |
1001 |
Phòng QLCL |
1,600,000 |
||
|
004 |
Quốc Khánh |
1003 |
Phòng Khoa học |
950,000 |
||
|
005 |
Phạm Thành |
1002 |
Phòng Quản trị |
1,000,000 |
||
|
006 |
Trần Thuỷ |
6033 |
Phòng Tài chính |
2,000,000 |
||
|
007 |
Nguyễn Hương |
1003 |
Phòng Thiết bị |
2,200,000 |
||
|
008 |
Lê Dung |
1003 |
Phòng Kinh doanh |
1,800,000 |
Bâng mã ngach và phu cấp
|
Mã ngạch |
Tên ngạch công chức |
Phụ cấp (%) |
|
1001 |
Chuyên viên cao cấp |
0.25 |
|
1002 |
Chuyên viên chính |
0.20 |
|
1003 |
Chuyên viên |
0.10 |
|
6033 |
Kỹ sư |
0.05 |
Yêu cầu: Dựa vào Bảng danh mục “Bảng mã ngạch và phụ cấp” hãy dùng hàm VLOOKUP để điền dữ liệu vào 2 cột Tên ngạch và Thực lĩnh.
Trong đó: Thực lĩnh = Lương cơ bản + (Phụ cấp * Lương cơ bản)
Bài 7. Hàm dò tìm (VLOOKUP). Cho bảng dữ liệu sau:
BẢNG THỐNG KÊ MUA BÁN HÀNG HÓA
|
STT |
Mã hàng |
Tên hàng |
Giá nhập |
SL nhập |
Thành tiên |
Giá xuất |
SL xuất |
Tiền xuất |
|
1 |
PaOA12 |
|||||||
|
2 |
Pa0C15 |
|||||||
|
3 |
SaOB2 |
|||||||
|
4 |
ToOC23 |
|||||||
|
5 |
HĨ0C12 |
|||||||
|
6 |
HĨ0A13 |
|||||||
|
7 |
ToOB12 |
|||||||
|
8 |
SaOB2 |
|||||||
|
Cộng: |
||||||||
Bảng mã
|
Mã hàng |
Tên hàng |
Giá nhập |
SL nhập |
SL xuất |
|
Pa |
Máy điều hòa Parasonic |
300 |
115 |
95 |
|
To |
Máy điều hòa Tosiba |
250 |
85 |
56 |
|
Sa |
Máy điều hòa Samsung |
210 |
120 |
75 |
|
Hi |
Máy điều hòa Hitachi |
220 |
68 |
35 |
- Dựa vào mã hàng và Bảng mã, điền số liệu cho các cột: Tên hàng, Giá nhập, số lượng nhập, Số lượng xuất.
- Tính Thành tiền = Giá nhập * số lượng nhập.
- Tính Giá xuất dựa vào Mã hàng: nếu Mã hàng có ký tự thứ 4 (tính từ bên trái) là A thì Giá xuất=Giá nhập+15, nếu là B thì Giá xuất = Giá nhập+12,còn lại Giá xuất =Giá nhập+10
- Tính Tiền xuất dựa vào giá xuất và số lượng xuất, định dạng đơn vị tiền tệ là USD.
- Tính tổng cộng cho mỗi cột.
- Chèn thêm cột Ghi chú ở cuối.
- Điền thông tin cho cột ghi chú như sau: nếu SL nhập - SL xuất >=60 thì ghi “Bán chậm”, nếu SL nhập - SL xuất >=30 thì ghi “Bán được”, còn lại ghi “Bán chạy”.
Bài 8. Hàm dò tìm (VLOOKUP). Cho bảng dữ liệu sau:
BẢNG THEO DÕI VẬT LIỆU XÂY DƯNG
Năm 2012
|
TT |
Mã |
Tên hàng |
Ngày nhập |
Ngày bán |
Nhận xét |
SỐ lượng |
Đơn giá |
Thành tiền |
|
1 |
B2 |
Giấy |
12/05/2012 |
15/05/2012 |
100 |
|||
|
2 |
AI |
Vải bông |
01/07/2012 |
17/10/2012 |
200 |
|||
|
3 |
DI |
Xi măng |
30/07/2012 |
28/09/2012 |
300 |
|||
|
4 |
C2 |
Gạch |
01/02/2012 |
12/10/2012 |
120 |
|||
|
5 |
A2 |
Vải bông |
30/07/2012 |
28/09/2012 |
400 |
|||
|
6 |
B3 |
Bìa |
12/05/2012 |
15/05/2012 |
1500 |
|||
|
7 |
D2 |
Xi măng |
27/07/2012 |
28/09/2012 |
300 |
|||
|
8 |
Cl |
Vôi |
04/02/2012 |
15/10/2012 |
120 |
|||
|
9 |
A3 |
Vải bông |
22/08/2012 |
28/09/2012 |
400 |
|||
|
10 |
B4 |
Giấy |
12/07/2012 |
12/09/2012 |
1500 |
a) Lập công thức điền giá trị vào cột Nhận xét theo quy định sau:
- Nếu thời gian lưu kho =30 ghi nhận xét: Bán chạy
- Nếu 30
- Nếu thời gian lưu kho >90 ghi nhận xét: Bán chậm
- Dựa vào Ký tự đầu của Mã và Bảng tra cứu dưới đây, dùng hàm VLOOKƯP để điền thông tin vào cột Đơn giá
Bảng tra cứu giá
|
Mã đầu |
Đơn giá |
|
A |
100 |
|
B |
200 |
|
c |
300 |
|
D |
120 |
- Tính cột Thành tiền = số lượng X Đơn giá X 1.1; định dạng tiền Việt Nam.
- Hoàn thành bảng thống kê sau:
|
Tên hàng |
Tổng số lượng |
Tổng thành tiền |
|
Giấy |
||
|
Vải bông |
||
|
Xi măng |
||
|
Gạch |
Bài 9. Hàm dò tìm (HLOOKUP), các hàm thống kê. Cho bảng dư liệu sau:
BẢNG THEO DÕI THANH TOÁN PHÒNG Ở KHÁCH SẠN HOA HÒNG
|
TT |
Loại phòng |
Ngày đi |
Ngày đến |
Số ngày |
SỐ người |
Giá phòng |
Giá phụ thu |
Tiền phòng |
|
1 |
A |
10/05/2012 |
22/05/2012 |
3 |
||||
|
2 |
B |
23/07/2012 |
19/08/2012 |
1 |
||||
|
3 |
A |
12/06/2012 |
19/08/2012 |
2 |
||||
|
4 |
B |
26/05/2012 |
07/06/2012 |
4 |
||||
|
5 |
c |
19/08/2012 |
25/08/2012 |
1 |
||||
|
6 |
A |
12/08/2012 |
15/08/2012 |
2 |
||||
|
7 |
B |
23/09/2012 |
30/09/2012 |
4 |
||||
|
8 |
A |
14/09/2012 |
16/09/2012 |
1 |
||||
|
9 |
A |
24/11/2012 |
01/12/2012 |
2 |
||||
|
10 |
B |
20/10/2012 |
20/10/2012 |
2 |
||||
|
Cộng: |
||||||||
Bảng mã đơn giá phòng chia theo loại A, B, c
|
Loại |
A |
B |
c |
|
Giál |
80 |
65 |
50 |
|
Giá 2 |
100 |
85 |
60 |
|
Phụ thu |
35 |
25 |
15 |
- Số ngày = Ngày đi - Ngày đến, nếu ngày đi trùng với ngày đến thì tính 1 ngày.
- Giá phòng dựa vào loại phòng và bảng đơn giá phòng, nếu phòng có 1 người thuế thì lấy giá 1, nếu có từ 2 người trở lến thì giá 2.
- Giá phụ thu dựa vào loại phòng và bảng giá phụ thu.
- Tiền phòng = số ngày * (Giá phòng + Giá phụ thu), nhưng nếu khách thuê phòng trên 10 ngày thì được giảm 10% giá phụ thu.
- Thống kê số tiền thu được theo từng loại phòng.
|
Loại |
Số người ở |
Số tiền thu được |
|
A |
80 |
65 |
|
B |
100 |
85 |
|
c |
35 |
25 |
Bài 10. Cho bảng dư liệu sau:
Tổng hợp doanh số bán hàng của 3 chi nhánh Công ty ANZ năm 2012
|
Chi nhánh |
Quýl |
Quý 2 |
Quý 3 |
Quý 4 |
|
Chi nhánh Hà Nội |
250 |
300 |
380 |
640 |
|
Chi nhánh Đà Nằng |
350 |
280 |
400 |
560 |
|
Chi nhánh Sài Gòn |
520 |
480 |
350 |
500 |
a) Vẽ đồ thị như sau:
- Chỉnh sửa đồ thị theo yếu cầu sau:
Thay đối kiểu tô nền cho Series dữ liệu
Thêm tiêu đề cho đồ thị “Tổng hợp doanh số bán hàng của 3 chi nhánh Công ty
ANZ năm 2012”.
Hiện thị giá trị cho mỗi cột.
- Xoay chiều biểu diễn của đồ thị. Gợi ý: Vào Design/Data/Switch Row/Column
Xem thêm: thành lập công ty tnhh tại long biên && thu hồi nợ xấu là gì?
Chúc bạn thành công !