VinaOnline.net

Bài tập áp dụng VLOOKUP và HLOOKUP : Minh họa trường hợp cụ thể

Bài tập áp dụng VLOOKUP và HLOOKUP là phân tích các tham số trong các hàm này để thực hiện đúng yêu cầu đề ra. Nếu sử dụng các tham số không đúng, bạn sẽ gặp khó khăn ngay các hàm này. Khi kết hợp với các hàm khác thì tính phức tạp càng tăng thêm. Vinaonline.net nêu bài tập áp dụng VLOOKUP và HLOOKUP là để củng cố thêm lý thuyết đã khảo sát Sử dụng hàm VLOOKUP và HLOOKUP.

Bài tập áp dụng VLOOKUP và HLOOKUP : Ví dụ minh họa

Bài tập áp dụng VLOOKUP và HLOOKUP : Ví dụ minh họa

Mô tả các ký tự trong MÃ HÀNG

– 1 là Sỉ (S), Lẻ (L) hoặc Khác (K) trong những trường hợp còn lại.

– 2 dùng để tham chiếu TÊN HÀNG.

– 3  cho biết loại hàng NHẬP hoặc XUẤT.

– Các ký tự còn lại là SỐ LƯỢNG.

Yêu cầu

1. Dựa vào BẢNG THAM CHIẾU để điền TÊN HÀNG tương ứng.

2. Lấy SỐ LƯỢNG dựa vào MÃ HÀNG.

3. Căn cứ BẢNG THAM CHIẾU, điền ĐƠN GIÁ tùy theo LOẠI HÀNG.

4. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ – GIẢM GIÁ (Kết hợp với BẢNG GIẢM GIÁ).

4. Tính tổng số tiền từng mặt hàng trong BẢNG THỐNG KÊ.

Nhắc lại cú pháp hàm VLOOKUP như sau:

VLOOKUP(Giá trị, Bảng tham chiếu, Số thứ cột cho kết quả, cách dò tìm)

I. Bài tập áp dụng VLOOKUP và HLOOKUP : Phân tích Giá trị

Khó nhất trong hàm VLOOKUP và HLOOKUP là xác định Giá trị. Khi không so khớp với CỘT / HÀNG đầu tiên của BẢNG THAM CHIẾU. Một số yếu tố cần xử lý như sau:

– Dùng hàm LEFT hoặc RIGHT hoặc MID để cắt chuỗi (MÃ HÀNG) cho phù hợp với CỘT / HÀNG trong BẢNG THAM CHIẾU.

– Nếu phù hợp NHƯNG báo lỗi #N/A, bạn tiếp tục dùng hàm VALUE để chuyển đổi kiểu dữ liệu. Chỉ thực hiện điều này khi gặp dạng ký tự số như “1”, “2”, “3”,…

– Khó hơn nữa, bạn phải kết hợp với hàm IF để cắt chuỗi.

Ví dụ:

Dựa vào MÃ HÀNG và BẢNG GIẢM GIÁ lấy % giảm giá

– Hàng 1 của BẢNG GIẢM GIÁ (từ C20:E20) có các ký tự “S”, “L”, “K”.

– So với ký tự 1 của MÃ HÀNG chỉ có “S”, “L”. Còn “K” thì sao?

– Trong trường hợp này, bạn không thể sử dụng hàm LEFT(B5,1).

– Muốn có “S”, “L”, “K” (trong những trường hợp còn lại), bạn sử dụng hàm:

IF(OR(LEFT(B5,1)=”S”,LEFT(B5,1)=”L”),LEFT(B5,1),“K”)

Nếu cắt MÃ HÀNG được “S” hoặc “L” thì để nguyên ký tự “S” hoặc “L”. Không phải một trong hai ký tự đó thì trả về ký tự “K”.

– Hàm lấy giảm giá như sau:

HLOOKUP(IF(OR(LEFT(B5,1)=”S”,LEFT(B5,1)=”L”),LEFT(B5,1),“K”),$C$20:$E$21,2,0)

II. Bài tập áp dụng VLOOKUP và HLOOKUP : Số thứ tự cột / hàng cho kết quả

Tham số này nhẹ nhàng hơn Giá trị, nếu không cho cụ thể Số thứ tự cột / hàng. Bạn cần kết hợp với hàm IF mà thôi. Khỏe lắm!

Ví dụ:

3. Căn cứ BẢNG THAM CHIẾU, điền ĐƠN GIÁ tùy theo LOẠI HÀNG.

Phân tích:

Nếu mặt hàng NHẬP thì chọn Số thứ tự cột 3, XUẤT thì cột 4.

Công thức như sau:

VLOOKUP(MID(B5,2,1),$B$15:$E$17,IF(MID(B5,3,1)=“N”,3,4),1)

III. Bài tập áp dụng VLOOKUP và HLOOKUP : Hoàn thành bảng tính

1. Dựa vào BẢNG THAM CHIẾU để điền TÊN HÀNG tương ứng.

C5 = VLOOKUP(MID(B5,2,1),$B$15:$E$17,2,1)

2. Lấy SỐ LƯỢNG dựa vào MÃ HÀNG.

D5 = VALUE(RIGHT(B5,LEN(B5)-3)) (3 ký tự đầu không phải ký tự số)

3. Căn cứ BẢNG THAM CHIẾU, điền ĐƠN GIÁ tùy theo LOẠI HÀNG.

Đã giải thích ở trên.

4. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ – GIẢM GIÁ (Kết hợp với BẢNG GIẢM GIÁ).

Phân tích:

SỐ LƯỢNG x ĐƠN GIÁ là một giá trị (số tiền) không thể trừ trực tiếp cho giảm giá (%) nên bạn tính như sau:

F5 = (100% – HLOOKUP(IF(OR(LEFT(B5,1)=”S”,LEFT(B5,1)=”L”),LEFT(B5,1),“K”),$C$20:$E$21,2,0))*D5*E5

5. Tính tổng số tiền từng mặt hàng trong BẢNG THỐNG KÊ.

– THÀNH TIỀN / DẦU LỬA

H15 = SUMIF($C$5:$C$10,G15,$F$5:$F10)

Bạn có thể thay G15=”Dầu lửa”, nhưng sao chép các ô còn lại phải chỉnh sửa thành “Xăng”,”Nhớt”.

Download dữ liệu mẫu

Lời kết

Bài tập áp dụng VLOOKUP và HLOOKUP cung cấp cho bạn một số dạng để rèn luyện. Nhưng CHƯA ĐỦ, bạn nên tìm thêm một số bài tập để luyện tập. Chúc bạn sử dụng thành thạo các hàm dò tìm VLOOKUP và HLOOKUP.