Hướng dẫn giải Dề 8 Excel Tin Học Văn Phòng

BÀI TẬP EXCEL BẢNG THANH TOÁN HÀNG THÁNG

1. Tên hàng: Dựa vào 2 ký tự đầu của Mã HĐ và tra trong bảng Tên hàng

2. Số lượng: Là các ký tự sau Dấu - của Mã HĐ (Kiểu số)

3. Đơn giá: Dựa vào Mã HĐ và tra trong bảng tên hàng biết nếu ngày mua = Ngày khai trương Thì giá 2 còn lại lấy giá 1.

4. Thành tiền: Bằng Đơn giá* số lượng tính lại (Nếu Số lượng >100 thì phần lớn hơn 100 được tính 90%, ngược lại giữ nguyên số lượng

5. Sắp xếp bảng thanh toán theo Tên hàng tăng dần. Nếu trùng thì sắp xếp theo số lượng giảm

6. Lập công thức tính tổng thành tiền trong bảng báo cáo

7. Thuế = Thành tiền * Thuế suất (Với thuế suất nếu là bia lon hoặc bia chai trì tra bảng thuế suất, Ngược lại = 0).

8. Trích danh sách Các mặt hàng là bánh mỳ hay bánh ngọt có ngày mua trước ngày 20/3/2018

Bài giải:

1.Tên Hàng: =VLOOKUP(LEFT(B4,2),$A$15:$D$18,2,0).  Trong đó LEFT(B4,2) Là hai ký tự đầu của Mã HĐ, $A$15:$D$18  là bảng tên hàng, 2 là thứ tự cột tên hàng trong bảng tên hàng. 0 là kiểu dò

2. Số lượng =VALUE(RIGHT(B4,LEN(B4)-FIND("-",B4))) trong đó Hàm RIGHT là hàm lấy một số ký tự bên phải (ký tự đầu). LEN là hàm đếm số ký tự trong chuỗi,FIND là hàm đếm số ký tự tới khi gặp điều kiện. FIND("-",B4) là đếm số ký tự tới khi nào găp dấu “-“ thì dừng. LEN(B4)-FIND("-",B4) là số ký tự sau dấu “-“ – tổng số ký tự trừ đi số ký tự từ đầu đến dấu “-“. Hàm VALUE là hàm chuyển ký tự số sang dạng số

3. Đơn giá  =VLOOKUP(LEFT(B4,2),$A$15:$D$18,IF(E4=$G$18,4,3),0) Giống Câu 1. Chỉ khác phần IF(E4=$G$18,4,3),0) là nếu Ngày mua = ngày khai trương (F18) thì lấy cột thứ 4 của bảng tên hàng còn lại lấy cột thứ 3. $G$18 là cố định cho giá trị F18 (Nhấn F4)

4. Thành tiền =F4*IF(D4>100,100+(D4-100)*90%,D4) . (D4-100) là số ngoài 100.

5. Sắp xếp: Giữ chuột kéo hết bảng chính (bảng thanh toán) Vào Data/ sort rồi sau đó:

Tại Sort By chọn Tên hàng. mục Sort on chọn Value. tại Order chọn A to Z

Tiếp tục chọn Add livel sau đó chọn

Tại Then By chọn Số lượng. tại Sort on chon value. tại Order chọn Smallest to Largest sao đó nhấn Ok là xong.

6.Tổng THành Tiền

Bia lon: =SUMIF($C$4:$C$12,”Bia Lon”,G4:G12)

Bia chai: =SUMIF($C$4:$C$12,”Bia chai”,G4:G12)

7. Thuế : =IF(OR(C5="Bia Lon",C5="Bia chai"), VLOOKUP(C5,$I$15:$J$16,2,0),0)

8. Trích Danh sách

Bước 1: Ta tạo một bảng nhỏ ở cạnh bảng chính gồm hai cột Tên hàng và Ngày mua 

Bước 2: Vào Data/ Addvanced
Tại Action: chọn Coppy...
Tại List Range Dữ chuột kéo hết bảng chính
Tại Creatian Range bôi đen bảng nhỏ mới tạo
Tại coppy to chon ô cột A bên dưới đề A32. xong nhấn Ok kết thúc. (các bạn có thể xem Video hướng dẫn chi tiết phía dưới)