Các bài tập Excel nâng cao

Giới thiệu

Tổng hợp các bài tập Excel nâng cao với các công thức tính toán và các yêu cầu phức tạp hơn. Các bài tập sẽ tập trung vào chức năng sử dụng Hàm, công thức đơn giản, chức năng sắp xếp, lọc với auto filter và advanced filter và chức năng vẽ đồ thị trong Excel.

Tham khảo thêm

Các bài tập thực hành

Bài tập 1: Tính toán cước thuê bao di động
Bài tập 2: Tính toán quản lý bán hàng
Bài tập 3: Báo cáo doanh thu khách sạn

Bài tập 1

TÍNH TOÁN CƯỚC THUÊ BAO DI ĐỘNG

STT	Họ và tên	Địa chỉ	Gói cước	Cước thuê bao	Dung lượng SD	Số tiền	Xếp hạng
1	Trần Ngọc Anh	An Đào	Mega Basic		700
2	Nguyễn Hoài Thanh	Đào Nguyên	Mega Family		800
3	Nguyễn Thị Bình	Cửu Việt	Mega Easy		1000
4	Kiều Thành Chung	An Đào	Mega Easy		2000
5	Ngô Thị Hiền	Cửu Việt	Mega Basic		950
6	Nguyễn Văn Hiệp	Đào Nguyên	Mega Easy		800
7	Đào Anh Dũng	Cửu Việt	Mega Basic		1200
8	Nguyễn Tiến Thành	An Đào	Mega Family		900
9	Trần Thành Trung	Cửu Việt	Mega Basic		800
10	Vũ Quốc Việt	Đào Nguyên	Mega Family		1100
11	Lê Anh Dũng	Cửu Việt	Mega Basic		950
12	Nguyễn Văn Minh	Anh Đào	Mega Family		1000
Tổng						x
Trung bình						x
Max						x
Min						x

Điền dữ liệu vào các cột còn trống:

Cột Cước thuê bao: giá trị được tra cứu trong bảng sau: biết gói cước được kí hiệu bởi các chữ cái: B (Basic), E(Easy), F(Family)

Gói cước	Cước thuê bao
B	0
E	24000
F	35000

Cột Số tiền: sử dụng hàm if để tính dựa trên tổng cước thuê bao và dung lượng sử dụng:
- Nếu Gói cước là Mega Basic: 650MB đầu tính là 36363đ, các MB tiếp theo được tính 70đ/1MB.
- Nếu Gói cước là Mega Easy hoặc Mega Family: tính 48đ/1MB.
Cột Xếp hạng: xếp thứ hạng cho các người dùng theo Số tiền.
Tính Tổng, Trung bình, Max, Mintại các vị trí được đánh dấu x.

Tính tổng Số tiền cho nhưng người sử dụng gói cước Mega Basic. Đếm xem có bao nhiêu người sử dụng gói cước này.
Sắp xếp bảng dữ liệu trên theo Địa chỉ tăng dần và Số tiền giảm dần.
Lọc ra các bản ghi có Số tiền sử dụng nằm trong đoạn [80000,130000].
Lọc ra những người có Địa chỉ tại Đào Nguyên hoặc sử dụng Gói cước Mega Basic.
Vẽ đồ thị cột (Column) và đồ thị bánh tròn (Pie) để so sánh Số tiền giữa các người sử dụng trên.

Xem hướng dẫn

Đang cập nhật

Bài tập 2

TÍNH TOÁN QUẢN LÝ BÁN HÀNG

STT	Họ và tên	Địa chỉ	Mã mặt hàng	Số lượng	Tổng thanh toán	Ngày mua	Ngày hết hạn bảo hành	Ghi chú
1	Lê Ngọc Long	Hoàng Mai	KC-740	1		05/21/09
2	Nguyễn Sao Mai	Hoàn Kiếm	KC-742	3		02/06/09
3	Nguyễn Ngọc Đức	Gia Lâm	KC-748	1		05/04/09
4	Trần Hải Yến	Hoàn Kiếm	KC-742	2		06/13/09
5	Lê Minh Ngọc	Gia Lâm	KC-748	1		02/06/09
6	Đào Minh Chung	Hoàn Kiếm	KC-740	3		05/04/09
7	Nguyễn Văn Thanh	Gia Lâm	KC-742	1		05/21/09
8	Đào Thị Hồng	Hoàng Mai	KC-742	2		02/06/09
9	Phạm Việt Thành	Gia Lâm	KC-748	1		05/04/09
Tổng				x	x
Trung bình				x	x
Max				x	x
Min				x	x

Hãy điền giá trị vào những vị trí còn trống trong bảng trên:

Mã Sản Phẩm	Giá (USD)	Bảo Hành (Tháng)
KC-740	39	18
KC-742	46	12
KC-748	81	24

Cột Tổng thanh toán:Được tính bằng Số lượng * Đơn giá, trong đó Đơn giá được tra cứu trong bảng sau bằng hàm VLOOKUP.
Cột Ngày hết hạn bảo hành:Đưa ra hạn cuối bảo hành dựa trên Ngày mua và Số tháng bảo hành (được tra cứu trong Bảng trên).
Cột Ghi chú:Thông báo thời tình trạng Còn thời gian bảo hành hoặc Đã hết hạn bảo hành.
Các dòng Tổng, Trung bình, Max, Min: Chỉ điền các giá trị tương ứng vào các vị trí được đánh dấu x.

Hãy tính tổng số lượng hàng đã bán đối với mã mặt hàng KC-748. Tổng giá trị thanh toán trong ngày 06/02/2009 là bao nhiêu?
Sắp xếp bảng dữ liệu theo chiều tăng dần của Ngày mua, giảm dần về số lượng.
Lọc ra 3 người mua hàng gần đây nhất.
Lọc ra các khách hàng có địa chỉ tại Gia Lâm hoặc đã mua hàng ngày 13/06/2009.
Vẽ đồ thị bánh tròn (3D-Pie) biểu diễn tỉ lệ phần trăm đóng góp trong Tổng thanh toán của từng khách hàng.

Xem hướng dẫn

Đang cập nhật

Bài tập 3

BÁO CÁO DOANH THU KHÁCH SẠN

TT	Họ đệm	Tên	Mã phòng	Ngày đến	Ngày đi	Số ngày ở	Tiền phòng	Phí phục vụ	Giảm	Tiền thu
1	Nguyễn Thị	Yến	55B1	01.01.04	05.01.04
2	Nguyễn Thị Minh	Anh	04C6	01.01.04	10.01.04
3	Đào Anh	Tuấn	78A2	08.01.04	29.01.04
4	Trần Tuấn	Anh	95A1	05.01.04	25.01.04
5	Nguyễn Thị	Duyên	99B5	10.02.04	25.02.04
6	Lê Thị	Yến	60A3	01.02.04	12.02.04
7	Nguyễn Mai	Anh	88C1	15.02.04	27.02.04
8	Cao Hồng	Thái	56B2	15.02.04	02.03.04
9	Nông Thị	Yến	77C1	20.01.04	15.02.04
10	Nguyễn Đức	Tuấn	30A2	26.01.04	26.01.04

Sử dụng công thức và hàm của Excel để điền dữ liệu vào các cột còn trống:

Số ngày ở = Ngày đi – Ngày đến
Tiền phòng:
- Nếu Số ngày ở bằng 0 thì Tiền phòng = Đơn giá
- Nếu Số ngày ở khác 0 thì Tiền phòng = Số ngày ở * Đơn giá

Đơn giá tra cứu theo loại phòng trong Bảng đơn giá ngày và công phục vụ, bảng này để ở một bảng tính khác có tên là TraCuu. Loại phòng nằm ở ký tự thứ 3 trong Mã phòng.

Bảng đơn giá ngày và công phục vụ

Loại phòng	Đơn giá ngày	Phục vụ
B	100	5%
A	150	8%
C	80	3%

Phí phục vụ = Phần trăm phục vụ
Tiền phòng. Phần trăm phục vụ dùng hàm tra cứu trong Bảng đơn giá ngày và công phục vụ.
Giảm:
- Nếu 0 ≤ Số ngày ở < 10 thì Giảm = 0%
- Nếu 10 ≤ Số ngày ở < 20 thì Giảm = 2%
- Nếu 20 ≤ Số ngày ở < 30 thì Giảm = 4%
- Nếu Số ngày ở >= 30 thì Giảm = 6%
Tiền thu = Tiền phòng + Phí phục vụ – Giảm*(Tiền phòng + Phí phục vụ)

Sắp xếp bảng theo Tênvần ABC và Tiền thu giảm dần.
Tìm những khách đến thuê khách sạn trong khoảng thời gian từ 15/01/04 đến 15/02/04 và có Ngày đi trong tháng 03/2004.
Vẽ biểu đồ dạng XY biểu diễn mối tương quan giữa Tiền thu và Số ngày ở.
Định dạng bảng:
- Kẻ bảng, căn dữ liệu trong ô giống đề bài .
- Định dạng cột Ngày đến, Ngày đi theo dạng ngày trước, tháng sau và sử dụng dấu chấm để phân cách ngày, tháng, năm.
- Định dạng cột Tiền thu có 2 chữ số sau dấu chấm thập phân và có chữ đồng sau số tiền. Ví dụ: 150.00 đồng