Giải Đề Thi Excel 014: Thành Thạo VLOOKUP, IF, SUMIFS Chỉ Trong 8 Phút 🚀
Bạn đang tìm kiếm một bài tập thực hành toàn diện để nâng cao kỹ năng Excel? Đề thi Excel số 014 này là một ví dụ tuyệt vời, giúp bạn ôn tập và nắm vững các hàm cơ bản và nâng cao như VLOOKUP, IF, AND, DATE, và SUMIFS.
Bài viết này sẽ đi sâu vào giải pháp từng bước cho đề thi, giúp bạn thành thạo Excel qua các tình huống thực tế về quản lý dữ liệu nhập xuất hàng hóa.
Công thức Excel Cơ bản: Xử lý Tên Thuốc và Trạng thái Nhập/Xuất
Yêu cầu đầu tiên là tìm tên thuốc dựa vào hai ký tự đầu của mã hàng (tra cứu trong Bảng 1) và sau đó nối với trạng thái Nhập/Xuất (ký tự cuối là 'N' hoặc 'X').
Đây là sự kết hợp hoàn hảo giữa tra cứu (VLOOKUP) và kiểm tra điều kiện (IF).
1. Lấy Tên Thuốc (VLOOKUP và LEFT)
Để lấy tên thuốc, bạn cần tra cứu 2 ký tự bên trái của mã hàng:
- Hàm LEFT để lấy 2 ký tự đầu:
LEFT(Mã_Hàng, 2) - Hàm VLOOKUP để tra cứu mã vừa tìm được trong Bảng 1 (cột trả về là cột 2).
2. Xác định Nhập/Xuất (IF và RIGHT)
Để xác định trạng thái, bạn kiểm tra ký tự cuối cùng của mã hàng:
- Hàm RIGHT để lấy ký tự cuối:
RIGHT(Mã_Hàng, 1) - Hàm IF để trả về "Nhập" nếu ký tự đó là "N" và "Xuất" nếu là "X".
3. Công Thức Tổng Hợp Tên Thuốc
Công thức đầy đủ tại ô Tên Thuốc sẽ là:
=VLOOKUP(LEFT(Mã_Hàng, 2), Bảng_1, 2, 0) & " - " & IF(RIGHT(Mã_Hàng, 1) = "N", "Nhập", "Xuất")
(Tham khảo giải thích chi tiết trong video tại [
Tính Đơn Giá Linh Hoạt với Hàm VLOOKUP và IF Lồng Nhau
Đơn giá phụ thuộc vào mã thuốc, nhưng cột đơn giá trả về sẽ khác nhau: Cột 3 (Đơn giá Nhập) hoặc Cột 4 (Đơn giá Xuất).
Thay vì nhập số cột cố định, bạn sử dụng hàm IF ngay tại đối số thứ 3 (col_index_num) của VLOOKUP để tự động chọn cột:
=VLOOKUP(LEFT(Mã_Hàng, 2), Bảng_1, IF(RIGHT(Mã_Hàng, 1) = "N", 3, 4), 0)
Logic:
- Hàm LEFT tìm mã thuốc.
Hàm IF(RIGHT(Mã_Hàng, 1) = "N", 3, 4) kiểm tra ký tự cuối:
- Nếu là 'N' (Nhập), hàm IF trả về số
3(Cột Đơn giá Nhập). - Nếu là 'X' (Xuất), hàm IF trả về số
4(Cột Đơn giá Xuất). - VLOOKUP sẽ dựa vào số cột (3 hoặc 4) để trả về Đơn Giá chính xác.
(Tham khảo giải thích chi tiết trong video tại [
Công Thức Tính Thành Tiền và Điều kiện Giảm Giá 5% (IF, AND, DATE)
Thành Tiền = Số Lượng x Đơn Giá. Điều kiện giảm giá là phần phức tạp nhất, đòi hỏi sự kết hợp của nhiều hàm:
1. Điều kiện Giảm Giá
Bạn được giảm 5% (tức là nhân với 95% hay 0.95) nếu thỏa mãn ĐỒNG THỜI hai điều kiện sau:
- Điều kiện 1: Ngày nhập/xuất NHỎ HƠN ngày 10/05/2022.
- Điều kiện 2: Số lượng LỚN HƠN HOẶC BẰNG 50.
2. Sử dụng Hàm AND và DATE
- Hàm AND giúp kiểm tra hai hay nhiều điều kiện cùng lúc:
AND(Điều kiện 1, Điều kiện 2) - Hàm DATE(năm, tháng, ngày) đảm bảo việc nhập ngày tháng là chính xác.
3. Công Thức Tính Thành Tiền
=Số_Lượng * Đơn_Giá * IF(AND(Ngày_NX < DATE(2022, 5, 10), Số_Lượng >= 50), 0.95, 1)
Logic:
- Hàm AND kiểm tra hai điều kiện (Ngày và Số lượng).
Hàm IF sử dụng kết quả của AND:
- Nếu
ANDtrả vềTRUE(Thỏa mãn), nhân với0.95(Giảm 5%). - Nếu
ANDtrả vềFALSE(Không thỏa mãn), nhân với1(Không giảm giá).
(Tham khảo giải thích chi tiết trong video tại [
Kỹ năng Xử lý Dữ liệu: Sắp xếp và Lọc
Đây là các thao tác quan trọng để tổ chức dữ liệu:
Sắp Xếp Dữ liệu (Sort)
Thực hiện sắp xếp theo 2 cấp độ:
- Level 1: Tăng dần theo Tên Thuốc (A đến Z).
- Level 2: Giảm dần theo Số Lượng (Từ lớn đến nhỏ).
(Tham khảo hướng dẫn trong video tại [
Lọc Dữ liệu (Filter)
Sử dụng tính năng Filter để lọc ra các mặt hàng thỏa mãn điều kiện Số Lượng >= 100.
(Tham khảo hướng dẫn trong video tại [
Hoàn thành Bảng Thống Kê và Vẽ Biểu Đồ (SUMIFS)
Để hoàn thành bảng thống kê Tổng Tiền theo từng loại thuốc, bạn sử dụng hàm SUMIFS (tính tổng có nhiều điều kiện).
Công Thức Tính Tổng Tiền (SUMIFS)
=SUMIFS(Vùng_Tính_Tổng_Thành_Tiền, Vùng_Điều_Kiện_1, Điều_Kiện_1)
Trong đề này, bạn chỉ có một điều kiện là Tên Thuốc:
=SUMIFS(Cột_Thành_Tiền, Cột_Tên_Thuốc, Tên_Thuốc_Cần_Tính)
(Tham khảo hướng dẫn trong video tại [
Vẽ Biểu Đồ So Sánh
Sau khi có bảng thống kê, bạn có thể dễ dàng tạo biểu đồ (Cột hoặc Tròn) để trực quan hóa Tổng Tiền giữa các loại thuốc.
- Chọn dữ liệu: Chọn cột Tên Thuốc và cột Tổng Tiền.
- Insert Chart: Chọn biểu đồ Cột (Column) để so sánh giá trị một cách dễ dàng.
(Tham khảo hướng dẫn vẽ biểu đồ trong video tại [
Video Hướng Dẫn Chi Tiết
Để xem hướng dẫn trực quan, bạn có thể xem video gốc của kênh Gà Excel:
