Bài Tập Excel Nâng Cao Kế Toán Trên Excel, Bài Tập Excel Nâng Cao Có Lời Giải

*
10 Bài tập excel nâng cao + lời giải cho ai cầnNế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ần10 Bài tập excel nâng cao + lời giải cho ai 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 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 cấp dưới Nữ, còn lại không được thưởng .Bạn đang xem : Bài tập excel nâng cao kế toánThưở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.Thưởng A : thưởng 300.000 cho những nhân viên cấp dưới 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 cấp dưới 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 12
2 BDNT 25
CT 10
BDGN 60
BDTS 22
BDGN 24
CT 100
BDTS 240
BDTS 15
CT 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
tiến sỹ 14.500
NT 16.000
GN 15.000
CT 3.000

Thực hiện : Thao tác tự động hóa điền tài 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 :

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 gia đình 1967 900 20
2 Ngô Nhu Phó Giám Đốc 1975 750 26
3 Mai Lan Phó Giám Đốc 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ưởngThêm vào cột Tuối bên phải cột Năm sinh theo ngày giờ mạng lưới hệ thống, sau đó tính tuối của cán bộ, nhân viên cấp dưới. Tính lương của nhân viên cấp dưới = 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ưởngTrong đó : Nếu chức vụ là gia đình 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.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 cấp dưới theo list trên ; Tính lương trung bình. 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áCăn cứ vào ký tự đầu tiến của Chứng từ để phân chia số lượng vào những 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 chia 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 chia vào cột Gas. Nếu ký tự đầu của chứng từ là L thì số lượng được phân chia 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 ứng ( CC ) và giá kinh doanh thương mại ( KD ) ; nếu ký tự phải của chứng từ là c thì lấy giá phân phối, ngược lại lấy giá kinh doanh thương mại .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.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 những số là ký tự thứ 4 của mã chứng từ. Trích xuất ra một list mới với điều kiện kèm theo số lượng > 100 .Xem thêm : Hướng Dẫn Cách Cài Mathtype Vào Word 2013 năm nay 2003, Chèn Mathtype Vào Word 2013

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
2 Phạm Hùng 201NOM
3 Lê Ngọc 205NOM
4 Phạm Hùng 209NOM
5 Nguyễn Thành 102NOM
6 Lê Hoài 107VIP
7 Vũ Ngọc 209NOM
8 Nguyễn Mai 210VIP
9 Mộng Mơ 202VIP

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ẻ .⇒ đề thi thương mại ngân hàng nhà nước 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 hạng sang 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 hạng mục “ Bảng mã ngạch và phụ cấp ” hãy dùng hàm VLOOKUP để điền tài 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+10Tí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”.Dựa vào mã hàng và Bảng mã, điền số liệu cho những 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 + 10T ính Tiền xuất dựa vào giá xuất và số lượng xuất, định dạng đơn vị chức năng tiền tệ là USD.Tính tổng số 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

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 100
2 AI Vải bông 200
3 DI Xi măng 300
4 C2 Gạch 120
5 A2 Vải bông 400
6 B3 Bìa 1500
7 D2 Xi măng 300
8 Cl Vôi 120
9 A3 Vải bông 400
10 B4 Giấy 1500

a ) Lập công thức điền giá trị vào cột Nhận xét theo lao lý sau :Nếu thời gian lưu kho Nếu 30 Nếu thời gian lưu kho >90 ghi nhận xét: Bán chậmDự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áNếu thời hạn lưu kho Nếu 30 Nếu thời hạn lưu kho > 90 ghi nhận xét : Bán chậmDự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

Tính cột Thành tiền = số lượng X Đơn giá X 1.1 ; định dạng tiền Nước Ta. Hoàn thành bảng thống kê sau :

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 3
2 B 1
3 A 2
4 B 4
5 c 1
6 A 2
7 B 4
8 A 1
9 A 2
10 B 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

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 .

Bài 10. Cho bảng dư liệu sau:

Tổng hợp doanh số bán hàng của 3 Trụ sở Công ty ANZ năm 2012

Chi nhánh

Quýl

Quý 2

Quý 3

Quý 4

Chi nhánh TP. Hà Nội 250 300 380 640
Chi nhánh Đà Nằng 350 280 400 560
Chi nhánh Hồ Chí Minh 520 480 350 500

a ) Vẽ đồ thị như sau :*Chỉnh sửa đồ thị theo yếu cầu 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/ColumnXoay chiều trình diễn của đồ thị. Gợi ý : Vào Design / Data / Switch Row / Column

Source: https://ta-ogilvy.vn
Category: Hỏi Đáp