Thành Thạo Excel: Giải Đề Thi Số 013 (VLOOKUP, HLOOKUP, SUMIFS Wildcard)
I. Kết Hợp Dữ Liệu Tra Cứu (VLOOKUP, HLOOKUP và Toán tử &)
Yêu cầu đầu tiên là tạo ra trường Tên Bánh và Nhãn Hiệu bằng cách nối chuỗi tên bánh và nhãn hiệu với nhau, phân cách bằng dấu gạch ngang (-).
1. Tìm Tên Bánh (VLOOKUP và LEFT)
- Logic: Tên bánh dựa vào hai ký tự đầu của Mã Hiệu, tra trong Bảng 1 (tra cứu dọc).
Công thức:
=VLOOKUP(LEFT(Mã_Hiệu, 2), Bảng_1, 2, 0)2. Tìm Nhãn Hiệu (HLOOKUP và RIGHT)
- Logic: Nhãn hiệu dựa vào hai ký tự cuối của Mã Hiệu, tra trong Bảng 2 (tra cứu ngang).
- Công thức:
=HLOOKUP(RIGHT(Mã_Hiệu, 2), Bảng_2, 2, 0)3. Nối Chuỗi Hoàn Chỉnh
Sử dụng toán tử & để nối ba thành phần: Tên Bánh, chuỗi phân cách (" - "), và Nhãn Hiệu.
Công thức Nối Chuỗi:
=VLOOKUP(...) & " - " & HLOOKUP(...)
(Tham khảo chi tiết trong video tại [
II. Tính Toán Giá Trị Phức Tạp (IF, AND, DATE)
1. Tính Đơn Giá (VLOOKUP lồng IF)
Đơn giá dựa vào tên bánh, nhưng cột đơn giá trả về phụ thuộc vào nhãn hiệu (Ví dụ: Thu Hương là cột 3, Anh Hòa là cột 4).
- Logic: Dùng VLOOKUP tra cứu theo tên bánh, sau đó dùng hàm IF để xác định số thứ tự cột:
- Dùng
RIGHT(Mã_Hiệu, 2)để kiểm tra mã nhãn hiệu (Ví dụ: 'TH' hoặc 'AH'). - Nếu là 'TH' thì trả về cột 3, ngược lại (là 'AH') thì trả về cột 4.
Công thức:
=VLOOKUP(LEFT(Mã_Hiệu, 2), Bảng_1, IF(RIGHT(Mã_Hiệu, 2)="TH", 3, 4), 0)
(Lưu ý: Bạn phải căn cứ vào cấu trúc bảng tra cứu thực tế của đề thi 013 để xác định chính xác số cột trả về. [
2. Tính Thành Tiền (Giảm giá 5%)
Thành tiền = Số Lượng x Đơn Giá. Khách hàng được giảm 5% (tức là nhân với 95%) nếu thỏa mãn ĐỒNG THỜI hai điều kiện:
- Ngày bán trước ngày 01/04/2022.
- Số lượng lớn hơn 20.
- Logic: Dùng IF lồng AND để kiểm tra hai điều kiện này. Hàm DATE được dùng để nhập mốc thời gian chính xác.
Công thức:
= Số_Lượng * Đơn_Giá * IF(AND(Ngày_Bán < DATE(2022, 4, 1), Số_Lượng > 20), 95%, 100%)
(Tham khảo chi tiết trong video tại [
3. Cột Khuyến Mại (IF lồng AND)
Điền "Tặng phiếu Khuyến mại" nếu thỏa mãn ĐỒNG THỜI hai điều kiện:
- Sản phẩm là Bánh Kem Socola (Kiểm tra mã sản phẩm).
- Số lượng lớn hơn 20.
Công thức:
=IF(AND(LEFT(Mã_Hiệu, 2)="MS", Số_Lượng > 20), "Tặng phiếu Khuyến mại", "")
(Tham khảo chi tiết trong video tại [
III. Sắp Xếp và Thống Kê (Custom Sort, SUMIFS Wildcard)
1. Sắp Xếp Nâng Cao (Custom Sort)
Sắp xếp dữ liệu theo nhiều tiêu chí:
- Mã Hiệu: Tăng dần.
- Nếu trùng Mã Hiệu: Sắp xếp theo Số Lượng giảm dần.
- Thao tác: Dùng chức năng Data > Sort (Custom Sort) và thêm hai cấp độ sắp xếp theo thứ tự yêu cầu.
(Tham khảo hướng dẫn thao tác tại [
08:42
2. Thống Kê Tổng Tiền theo Nhãn Hiệu (SUMIFS và Wildcard *)
Yêu cầu tính Tổng Thành Tiền theo Nhãn Hiệu (Thu Hương, Anh Hòa). Vì cột dữ liệu gốc chứa cả tên bánh và nhãn hiệu (Ví dụ: "Bánh Kem - Thu Hương"), bạn phải dùng ký tự đại diện (* - Wildcard) để SUMIFS có thể tìm kiếm.
- Logic: Tìm kiếm chuỗi kết thúc bằng tên nhãn hiệu.
Công thức (áp dụng cho Thu Hương):
=SUMIFS(Cột_Thành_Tiền, Cột_Tên_Bánh_Nhãn_Hiệu, "*" & Tên_Nhãn_Hiệu)
(Ví dụ: "*Thu Hương" để tìm bất kỳ chuỗi nào kết thúc bằng "Thu Hương")
- Lưu ý: Nếu cột điều kiện của bạn là cột kết hợp (Tên Bánh và Nhãn Hiệu), bạn cần sử dụng ký tự đại diện
*ở phía trước tên nhãn hiệu để khớp với mọi trường hợp. (Tham khảo chi tiết trong video tại [09:59
3. Vẽ Biểu Đồ So Sánh
Sử dụng kết quả thống kê (Tổng Tiền theo Nhãn Hiệu) để vẽ biểu đồ hình tròn (Pie Chart). Đừng quên tùy chỉnh Data Label để hiển thị cả tên nhãn hiệu và tỷ lệ phần trăm (Percentage) cho tính trực quan cao nhất. [
Xem Video Hướng Dẫn Chi Tiết
Để xem các bước thao tác thực hành trực quan và cách xử lý các lỗi phát sinh (như lỗi #N/A), bạn có thể theo dõi video gốc từ kênh Gà Excel:
