Thành Thạo Excel Cấp Tốc: Giải Đề Thi Số 007 Chi Tiết (HLOOKUP, IF, SUMIFS) 🚀

Đề thi Excel 007 là bài thực hành tổng hợp các kỹ năng quan trọng nhất trong Excel, tập trung vào các hàm: HLOOKUP, IF lồng IF/AND, và SUMIFS với nhiều tiêu chí.

I. Tra Cứu Dữ Liệu và Tính Đơn Giá (HLOOKUP & IF)

Hai yêu cầu đầu tiên là nền tảng để điền vào bảng dữ liệu chính, sử dụng hàm HLOOKUP vì bảng tham chiếu (Bảng 1) được sắp xếp theo hàng ngang.

1. Tìm Tên Hàng

Tên hàng được tra cứu dựa vào Mã Hàng và tra trong Bảng 1 để lấy kết quả ở hàng thứ 2 của bảng tra cứu.

Hàm chính: HLOOKUP
Công thức:

Excel
=HLOOKUP(Mã_Hàng, Vùng_Bảng_1, 2, 0)

(Tham khảo chi tiết tại [01:16])

2. Tính Đơn Giá (HLOOKUP lồng IF)

Đơn giá dựa vào Mã Hàng, nhưng lại phụ thuộc vào Mã Công Ty (V - Vĩnh Hảo, F - Fanta) để xác định hàng trả về:

Vĩnh Hảo: Đơn giá nằm ở Hàng 3 của Bảng 1.
Fanta: Đơn giá nằm ở Hàng 4 của Bảng 1.
Logic: Dùng hàm IF để xác định số thứ tự hàng trả về dựa trên Mã Công Ty.
Công thức:

Excel
=HLOOKUP(Mã_Hàng, Vùng_Bảng_1, IF(Mã_Công_Ty = "F", 4, 3), 0)

(Tham khảo chi tiết tại [03:17])

II. Tính Toán Có Điều Kiện và Ghi Chú (IF, AND, ROUNDDOWN)

1. Tính Thành Tiền (Phí Hóa Đơn 2%)

Thành tiền được tính bằng Số lượng x Đơn giá, nhưng nếu cột Có Hóa Đơn có dấu 'X' thì phải cộng thêm 2% trên tổng giá trị.

Logic: Dùng IF kiểm tra điều kiện Có Hóa Đơn = "X".

Nếu đúng: nhân với 102% (1.02).
Nếu sai: nhân với 100% (1).

Công thức:

Excel
=Số_Lượng * Đơn_Giá * IF(Có_Hóa_Đơn = "X", 1.02, 1)

(Tham khảo chi tiết tại [05:27])

2. Ghi Chú (Tặng Nước Dâu)

Ghi chú "Tặng nước dâu" nếu thỏa mãn ĐỒNG THỜI 3 điều kiện:

Tên Hàng là "Cam".
Công ty là "Fanta" (Mã F).
Số Lượng lớn hơn 10.
Hàm chính: IF lồng AND
Công thức:

Excel
=IF(AND(Tên_Hàng = "Cam", Mã_Công_Ty = "F", Số_Lượng > 10), "Tặng nước dâu", "")

(Tham khảo chi tiết tại [07:21])

3. Số Hàng Tặng (Làm tròn xuống)

Chỉ tính số hàng tặng cho những mặt hàng được ghi chú "Tặng nước dâu". Nguyên tắc: Cứ 10 chai được tặng 1 chai nước dâu (làm tròn xuống).

Hàm chính: IF lồng ROUNDDOWN (hoặc INT)
Logic: Dùng IF kiểm tra cột Ghi Chú có phải là "Tặng nước dâu" không. Nếu đúng, tính ROUNDDOWN(Số Lượng / 10, 0).
Công thức:

Excel
=IF(Ghi_Chú = "Tặng nước dâu", ROUNDDOWN(Số_Lượng / 10, 0), "")

(Tham khảo chi tiết tại [11:13])

III. Sắp Xếp và Thống Kê (Custom Sort & SUMIFS)

1. Sắp Xếp Nâng Cao (Custom Sort)

Sắp xếp bảng dữ liệu theo nhiều tiêu chí:

Cấp độ 1: Sắp xếp tăng dần theo Số Lượng.
Cấp độ 2: Nếu Số Lượng trùng nhau, sắp xếp giảm dần theo Thành Tiền.

Thao tác: Dùng chức năng Data > Sort (Custom Sort) để đảm bảo độ chính xác. (Tham khảo hướng dẫn thao tác tại [13:04])

2. Lập Bảng Thống Kê Thành Tiền (SUMIFS 2 Điều Kiện)

Yêu cầu tính Tổng Thành Tiền theo hai điều kiện đồng thời: Tên Công Ty (dọc) và Tên Hàng (ngang).

Hàm chính: SUMIFS
Vùng tính tổng: Cột Thành Tiền.
Điều kiện 1 (Tên Hàng): Vùng Tên Hàng phải khớp với Hàng tiêu đề (Chanh, Xá Xị...).
Điều kiện 2 (Công Ty): Vùng Mã Công Ty phải khớp với Cột tiêu đề (Vĩnh Hảo, Fanta).
Công thức (áp dụng cho ô đầu tiên):

Excel

=SUMIFS(Cột_Thành_Tiền, Cột_Tên_Hàng, Hàng_Tên_Hàng_cố_định_Dòng, Cột_Mã_CTY, Cột_Tên_CTY_cố_định_Cột)

Lưu ý quan trọng: Bạn phải sử dụng tham chiếu hỗn hợp (Cố định Dòng cho Tên Hàng và Cố định Cột cho Tên Công Ty) để công thức có thể kéo ngang và kéo dọc mà vẫn đúng. (Tham khảo hướng dẫn và cố định tham chiếu tại [15:27])

Video Hướng Dẫn Chi Tiết

Để theo dõi trực quan từng bước giải, cách xử lý các hàm và cố định tham chiếu phức tạp, bạn có thể xem video gốc:

Video Giải Đề Thi Excel 007

Thành Thạo Excel Cấp Tốc: Giải Đề Thi Số 007 Chi Tiết (HLOOKUP, IF, SUMIFS)