10 Bài Tập Excel Nâng Cao + Lời Giải Cho Ai Cần

10 Bài tập excel nâng cao + lời giải cho ai cần 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.

1. 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.
2. 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

1. Thực hiện:

1. 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...).
2. Đ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”.

1. 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

1. 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.

1. 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

1. 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.
2. Tính lương của nhân viên = Lương CB * NGÀY.
3. Tính tạm ứng = 80% * Lương.
4. 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.

1. 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.
2. 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

1. 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.

1. 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.

1. Tính tổng cho mỗi cột.
2. 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ừ.
3. 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

1. 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.

1. Tính Thành tiền = Giá nhập * số lượng nhập.
2. 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
3. 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.
4. Tính tổng cộng cho mỗi cột.
5. Chèn thêm cột Ghi chú ở cuối.
6. Đ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:

1. Nếu thời gian lưu kho 90 ghi nhận xét: Bán chậm

1. 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

1. 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.
2. 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

1. 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.
2. 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.
3. Giá phụ thu dựa vào loại phòng và bảng giá phụ thu.
4. 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.
5. 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:

1. 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.

1. 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 !

Các tin cũ hơn

- Cách hiển thị và ẩn đường kẻ ô trên bảng trong Word

- Cách trừ ngày trong Excel

- Cách hiển thị các dấu định dạng trong Word

- Cách chuyển tệp MHT sang JPG

- Cách biến tệp WPS thành PDF

- Cách copy sao chép tệp PDF vào Office Word

- Ẩn văn bản và hiển thị văn bản ẩn trong Word

- Sắp xếp danh sách theo thứ tự bảng chữ cái trong Word