Thành Thạo Excel Cấp Tốc: Giải Đề Thi Số 012 Chi Tiết (VLOOKUP, HLOOKUP, IF, SUMIFS) 🚀
Bạn đang luyện tập để thành thạo Excel và cần ôn tập các hàm tra cứu, tính toán phức tạp? Đề thi Excel số 012 là một bài tập tuyệt vời giúp bạn làm chủ các hàm cơ bản đến nâng cao như VLOOKUP, HLOOKUP, IF, MID và SUMIFS.
Bài viết này sẽ cung cấp hướng dẫn từng bước và công thức chính xác, chuẩn SEO để bạn có thể áp dụng ngay vào bài làm và công việc thực tế!
I. Tra Cứu Dữ Liệu Cơ Bản: Tên Hàng và Nhà Sản Xuất
Hai yêu cầu đầu tiên là tìm kiếm dữ liệu từ hai bảng tra cứu có cấu trúc khác nhau, đòi hỏi sự kết hợp linh hoạt giữa các hàm tách chuỗi và tra cứu.
1. Tìm Tên Hàng (VLOOKUP và LEFT)
Tên hàng được tra cứu dựa vào hai ký tự đầu của mã hàng và tra trong Bảng 1 (sắp xếp dọc).
- Logic: Dùng hàm
LEFTđể tách 2 ký tự đầu, sau đó dùngVLOOKUPđể tìm kiếm. Công thức:
=VLOOKUP(LEFT(Mã_Hàng, 2), Bảng_1, 2, 0)
(Tham khảo chi tiết tại [
2. Tìm Nhà Sản Xuất (HLOOKUP và RIGHT)
Nhà sản xuất được tra cứu dựa vào ký tự cuối của mã hàng và tra trong Bảng 2 (sắp xếp ngang).
- Logic: Dùng hàm
RIGHTđể tách ký tự cuối, sau đó dùngHLOOKUPđể tìm kiếm. - Lưu ý:
HLOOKUPthường ít được dùng trong thực tế so vớiVLOOKUPnhưng rất cần thiết khi dữ liệu tra cứu được tổ chức theo hàng ngang. Công thức:
=HLOOKUP(RIGHT(Mã_Hàng, 1), Bảng_2, 2, 0)
(Tham khảo chi tiết tại [
II. Tính Đơn Giá Phức Tạp: VLOOKUP Lồng Hàm IF
Đây là phần nâng cao đòi hỏi bạn phải xác định cột trả về một cách linh hoạt. Đơn giá phụ thuộc vào Tên Hàng, nhưng còn phụ thuộc vào việc hàng đó là Hàng Nhập (N) hay Hàng Xuất (X) (ký tự thứ 3 của mã hàng).
1. Xác định Cột Trả Về (IF và MID)
- Hàm MID: Dùng để tách ký tự thứ 3:
MID(Mã_Hàng, 3, 1) - Hàm IF: Nếu ký tự này là "N" (Nhập) thì đơn giá là Cột 2, ngược lại (là "X" - Xuất) thì là Cột 3 (Tính từ cột Tên Hàng trong Bảng 1).
2. Công Thức Đơn Giá
Công thức:
=VLOOKUP(Tên_Hàng, Vùng_Bảng_1, IF(MID(Mã_Hàng, 3, 1) = "N", 2, 3), 0)
(Tham khảo giải thích chi tiết tại [
III. Tính Thành Tiền và Ghi Chú Đặc Biệt
1. Tính Thành Tiền (IF và Phí Vận Chuyển)
Thành tiền không chỉ là Số lượng x Đơn giá, mà còn cộng thêm Phí Vận Chuyển dựa vào trạng thái Nhập/Xuất:
- Hàng Nhập (N): Phí là 1% của Đơn Giá.
- Hàng Xuất (X): Phí là 2% của Đơn Giá.
Công thức:
= (Số_Lượng * Đơn_Giá) + IF(MID(Mã_Hàng, 3, 1) = "N", 1% * Đơn_Giá, 2% * Đơn_Giá)
(Tham khảo giải thích chi tiết tại [
2. Ghi Chú (IF lồng AND)
Ghi chú sẽ điền "Tặng quà" nếu thỏa mãn ĐỒNG THỜI hai điều kiện sau:
- Là Hàng Xuất (ký tự thứ 3 là 'X').
- Số lượng Lớn hơn hoặc bằng 15.
Công thức:
=IF(AND(MID(Mã_Hàng, 3, 1) = "X", Số_Lượng >= 15), "Tặng quà", "")
(Tham khảo giải thích chi tiết tại [
IV. Xử Lý Dữ Liệu: Lọc và Thống Kê (SUMIFS)
1. Lọc Dữ Liệu (Filter)
Sử dụng tính năng Filter của Excel để lọc ra các mặt hàng có:
- Tên Hàng là Mouse.
- Số lượng Lớn hơn hoặc bằng 15.
(Tham khảo hướng dẫn thao tác lọc tại [
2. Hoàn Thành Bảng Thống Kê (SUMIFS)
Để tính Tổng Số Lượng theo Nhà Sản Xuất, bạn cần dùng hàm tính tổng có điều kiện: SUMIFS.
- Logic: Tính tổng cột Số Lượng, với điều kiện là cột Nhà Sản Xuất phải trùng khớp với tên nhà sản xuất cần thống kê.
Công thức:
=SUMIFS(Cột_Số_Lượng, Cột_Nhà_Sản_Xuất, Tên_Nhà_Sản_Xuất)
(Tham khảo hướng dẫn dùng SUMIFS tại [
3. Vẽ Biểu Đồ
Từ kết quả thống kê Tổng Số Lượng theo Nhà Sản Xuất, bạn có thể tạo ngay biểu đồ hình Tròn (Pie Chart) để trực quan hóa tỷ trọng của từng nhà sản xuất. Đừng quên tùy chỉnh Data Label để hiển thị cả tên nhà sản xuất (Category Name) và tỷ lệ phần trăm (Percentage) lên biểu đồ. [
Video Hướng Dẫn Chi Tiết
Để theo dõi từng thao tác và cách xử lý tình huống phát sinh trong quá trình giải đề, bạn có thể xem video gốc của kênh Gà Excel:
