Thành Thạo Excel: Giải Đề Thi 015 – Kết Hợp VLOOKUP, HLOOKUP và SUMIFS Nâng Cao 🚀
Bạn đang tìm kiếm một bài tập chuyên sâu để làm chủ các hàm tra cứu và thống kê trong Excel? Đề thi Excel số 015 là một thử thách hoàn hảo, yêu cầu bạn phải kết hợp linh hoạt VLOOKUP, HLOOKUP, IF, AND, và đặc biệt là SUMIFS với hai tiêu chí.
Bài viết này sẽ hướng dẫn chi tiết cách giải từng câu hỏi trong đề 015, giúp bạn không chỉ tìm ra công thức đúng mà còn hiểu rõ logic để áp dụng vào mọi tình huống thực tế.
I. Tìm Kiếm Dữ Liệu Kết Hợp (VLOOKUP & HLOOKUP)
Câu hỏi đầu tiên yêu cầu điền Tên Nước và Phương Tiện dựa trên mã cước và hai bảng tra cứu khác nhau.
1. Tên Nước (VLOOKUP và LEFT)
Tên nước được tra cứu theo hai ký tự đầu của Mã Cước trong Bảng 1 (tra cứu dọc).
- Công thức: Dùng
LEFT(Mã_Cước, 2)để lấy mã nước, sau đó dùngVLOOKUPđể tra cứu trong Bảng 1. - Ví dụ: Mã cước bắt đầu bằng "JP" sẽ trả về "Nhật".
2. Phương Tiện (HLOOKUP và RIGHT)
Phương tiện được tra cứu theo hai ký tự cuối của Mã Cước trong Bảng 2 (tra cứu ngang).
- Công thức: Dùng
RIGHT(Mã_Cước, 2)để lấy mã phương tiện, sau đó dùngHLOOKUPđể tra cứu trong Bảng 2. - Lưu ý quan trọng (Xử lý Text và Number): Nếu dữ liệu trong Bảng 2 là số (01, 02) và bạn tách ra bằng hàm RIGHT (trả về text), bạn cần nhân kết quả của hàm RIGHT với
1để chuyển nó về dạng số, giúpHLOOKUPtìm kiếm chính xác. [02:43
II. Công Thức Tính Toán Phức Hợp (VLOOKUP, IF, AND, DATE)
1. Đơn Giá Cước (VLOOKUP lồng IF)
Đơn giá cước dựa vào Tên Nước, nhưng cột trả về lại phụ thuộc vào Phương Tiện (Máy Bay hay Tàu Thủy).
- Logic: Dùng VLOOKUP để tra cứu theo Tên Nước. Thay vì nhập số cột cố định, bạn dùng hàm IF ngay tại đối số chỉ số cột:
- Nếu Phương Tiện là "Máy Bay" → Cột 2.
- Nếu Phương Tiện là "Tàu Thủy" → Cột 3.
Công thức:
=VLOOKUP(Tên_Nước, Bảng_Đơn_Giá, IF(Phương_Tiện="Máy Bay", 2, 3), 0)
(Tham khảo giải thích chi tiết trong video tại [
2. Thành Tiền và Điều Kiện Giảm Giá 5%
Thành tiền = Khối Lượng x Đơn Giá Cước. Có điều kiện giảm giá 5% (tức là nhân với 95%):
- Điều kiện: Gửi trước ngày 01/04/2022.
- Hàm DATE: Để nhập ngày tháng chính xác và so sánh, bạn nên dùng hàm
DATE(năm, tháng, ngày). Công thức:
=Khối_Lượng * Đơn_Giá_Cước * IF(Ngày_Gửi < DATE(2022, 4, 1), 95%, 100%)
(Tham khảo giải thích chi tiết trong video tại [
3. Khuyến Mại (IF và AND)
Cột khuyến mại chỉ ghi "Có ưu đãi" nếu thỏa mãn đồng thời hai điều kiện (hàm AND):
- Tên Nước là "Nhật".
- Phương Tiện là "Máy Bay".
Công thức:
=IF(AND(Tên_Nước="Nhật", Phương_Tiện="Máy Bay"), "Có ưu đãi", "")
(Tham khảo giải thích chi tiết trong video tại [
III. Lọc Dữ Liệu Chuyên Sâu (Advanced Filter)
Câu hỏi lọc yêu cầu phức tạp: Lọc danh sách thỏa mãn điều kiện HOẶC:
- Nước Nhật và Khối lượng > 150 HOẶC
- Nước Hàn và Khối lượng > 100
Bạn có thể dùng Lọc Thủ Công hai lần [
Cách tạo Vùng Điều Kiện cho Advanced Filter:
Trong Advanced Filter, điều kiện HOẶC được thể hiện bằng cách đặt các tiêu chí trên hai hàng khác nhau.
| Tên Nước | Khối Lượng |
| Nhật | >150 |
| Hàn | >100 |
Bạn bôi đen vùng này làm Criteria range và chọn Copy to another location để trích xuất dữ liệu.
(Tham khảo hướng dẫn dùng Advanced Filter trong video tại [
IV. Thống Kê Tổng Hợp (Hàm SUMIFS Hai Điều Kiện)
Hoàn thành bảng thống kê Tổng Thành Tiền theo Tên Nước và Phương Tiện là lúc bạn cần đến sức mạnh của SUMIFS.
Công Thức SUMIFS:
=SUMIFS(Vùng_Tính_Tổng, Vùng_Điều_Kiện_1, Điều_Kiện_1, Vùng_Điều_Kiện_2, Điều_Kiện_2)
- Vùng Tính Tổng: Cột Thành Tiền (cố định bằng F4).
- Điều kiện 1 (Tên Nước): Cột Tên Nước (cố định F4) và ô chứa tên nước "Nhật" (cố định cột để khi kéo công thức sang phải, cột vẫn giữ nguyên - ví dụ:
$G20). - Điều kiện 2 (Phương Tiện): Cột Phương Tiện (cố định F4) và ô chứa tên phương tiện "Máy Bay" (cố định hàng để khi kéo công thức xuống dưới, hàng vẫn giữ nguyên - ví dụ:
H$19).
- Lưu ý: Việc cố định cột (
$G20) và hàng (H$19) là cực kỳ quan trọng giúp bạn chỉ cần viết một công thức và kéo cho toàn bộ bảng thống kê. [10:14
Video Hướng Dẫn Chi Tiết
Để xem các bước thực hiện chi tiết và cách xử lý tình huống phát sinh (như lỗi #N/A của HLOOKUP), bạn có thể xem video gốc của kênh Gà Excel:
