Câu 1 (6 điểm)
Cho số liệu để tính doanh thu bán hàng của một công ty mỹ phẩm như file đính kèm.
1. Tách cột Nhân viên bán hàng thành hai cột Họ và tên đệm và cột Tên Nhân viên
2. Các kí tự trong mã đơn hàng cho biết tên sản phẩm, khu vực bán hàng và ngày bán.
Lập các cột: Tên SP, Khu vực, Ngày bán, Đơn giá, Doanh thu, Hoa hồng chiết khấu, Tiền
thưởng, Thu nhập.
Cho biết các kí tự được mã hóa như sau :
Mã SP
Tên SP
Mã khu vực
Khu vực
SD
Son dưỡng
HM
Hồ Chí Minh
KN
Kem nền
HN
Hà Nội
SM
Son môi
NA
Nghệ An
KM
Kẻ mắt
HP
Hải Phòng
MC
Mascara
Ví dụ : Mã đơn hàng : SD_HM43556
→
Tên sản phẩm : SD ; Khu vực : HM, Ngày bán : 43556
= 1/4/2019
3. Lập công thức tính lương sản phẩm: Doanh thu = Số lượng SP * Đơn giá, biết đơn giá SP phụ
thuộc vào tên SP và khu vực như sau :
Bảng đơn giá sản phẩm
(đơn vị: đồng)
HM
HN
NA
HP
SD
120,000
108,000
111,000
100,000
KN
300,000
270,000
290,000
310,000
SM
230,000
207,000
250,000
200,000
KM
195,000
175,500
190,000
175,000
MC
140,000
126,000
145,000
135,000
3. Hoa hồng chiết khấu bán hàng được quy định bằng 5% đơn hàng cho những đơn hàng khách
có hợp đồng dài hạn và 3% cho những đơn hàng khách có hợp đồng ngắn hạn. Loại hợp đồng được
ghi trong Mã đơn hàng (2 kí tự cuối, DH: Dài hạn, NH: Ngắn hạn).
4. Tiền thưởng thêm được tính thêm 10% của hoa hồng chiết khấu bán hàng nếu đơn hàng được
bán vào thứ 2 trong tuần.
5. Tính Thu nhập: Thu nhập = Hoa hồng chiết khấu + Tiền thưởng
6. Trích xuất dữ liệu của những đơn hàng bán tại khu vực Hải Phòng và có số lượng sản phẩm
bán được trên 50 sản phẩm.
Câu 2 (2 điểm)
Dựa vào bảng dữ liệu ở câu 1, lập bảng pivot để tính
1. Tính tổng doanh thu theo sản phẩm, vẽ biểu đồ thể hiện tổng doanh thu theo sản phẩm, xếp thứ
tự các sản phẩm có doanh thu từ cao đến thấp.
