Áp dụng Hàm HLOOKUP, SUMIF giải một số bài toán đơn giản

Nhatthienkt - Hàm HLOOKUP và SUMIF là một trong những hàm sử dụng khá phổ biến, trong bài viết này chúng ta cùng tìm hiểu  hàm này.

Hàm HLOOKUP

1. Cách sử dụng hàm HLOOKUP trong Excel

HLOOKUP dùng để tìm kiếm thông tin theo hàng trong bảng dữ liệu dựa theo điều kiện tìm kiếm cho trước.

2. Cú pháp hàm HLOOKUP

HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup])

Trong đó:

lookup_value: giá trị cần tìm kiếm, nằm ở hàng đầu tiên của vùng dữ liệu được cung cấp.

table_array: vùng hoặc bảng dữ liệu có chứa dữ liệu được tìm kiếm và có chứa giá trị trả lại.

row_index_num: số thứ tự dòng chứa giá trị muốn tìm kiếm, giá trị tương ứng sẽ được trả lại từ dòng này.

[range_lookup]: không bắt buộc, một tùy chọn lập luận logic, bạn có thể để TRUE hoặc FALSE với ý nghĩa như sau:

TRUE:     Nếu hàm không thể tìm thấy giá trị đã đưa thì sử dụng giá trị gần nhất với giá trị đã đưa. Lưu ý, nếu [range_lookup] được thiết lập TRUE thì hàng đầu của table_array phải được sắp xếp theo thứ tự tăng dần.

FALSE:    Nếu hàm không thể tìm thấy chính xác giá trị tìm thấy đã đưa thì trả về kết quả lỗi.

Xem ví dụ sau:

Trong ví dụ này bạn sẽ thấy những mục "?" là phần bạn phải sủ dụng công thức tìm kiếm và tính toán.

- Mỗi sản phẩm đều có mã riêng, sử dụng BẢNG PHỤ để điền thông tin vào bảng THỐNG KÊ BÁN HÀNG.

- Ta thấy bảng THỐNG KÊ BÁN HÀNG có ký tự đầu tiên là :G", "D" và "S" tướng ứng là Gạo, Đường, Sữa. Như vậy bạn nên nghĩ ra hàm lấy kí tự bên trái (left).

1. Tính toán bảng THỐNG KÊ BÁN HÀNG

Bước 1: Bạn cần sử dụng hàm Left để tách Mã số (Mã SP) cho giống với bảng phụ

=Left(C4;1)

Bạn sẽ dể dàng tách được mã SP giống bảng phụ

Bước 2: Lòng ghép hàm HLOOKUP để điền tên sản phẩm

=HLOOKUP(LEFT(C4;1);$B$15:$E$16;2;0)

Giải thích chút:

HLOOKUP(LEFT(C4;1): Tìm kiếm ký tự G, D, S trên bảng THỐNG KÊ BÁN HÀNG, vì "Mã số" có rất nhiều ký tự phía sau nữa "G01A" chẳng hạn nên ta dùng thêm hàm LEFT để tách "Mã Số" ra giống "Mã SP" ở BẢNG PHỤ. 

$B$15:$E$16 Tham chiếu đến BẢNG PHỤ để tìm ký tự G, D, S 

2;0  "2" có nghĩa là chúng ta lấy kết quả ở :"dòng thứ 2" tức là hàng C16 đến E16; "0" có nghĩa là LOGIC bạn nên chọn 0.

Dấu $ là tra cứu tuyệt đối, bạn phải lưu ý điều này vì nếu không là giá trị tuyệt đối khi kéo copy công thức sẽ sai. Nhấn F4 để đưa về giá trị tuyệt đối.

HÀM SUMIF

Hàm SUMIF được biết đến với chức năng tính tổng có điều kiện, được sử dụng khá nhiều khi học Excel và thực hành, bạn có thể sử dụng hàm này để thực hiện các công việc tính toán chính xác và hiệu quả.

CÁCH DÙNG HÀM SUMIF 

Cú Pháp:

SUMIF(range, criteria,sum_range)

Trong Đó:

Range: Là vùng được chọn có chứa các ô điều kiện.

Criteria: Là điều kiện để thực hiện hàm này.

Sum_range: Vùng cần tính tổng.

Trở lại ví dụ trên ta dùng hàm Sumif để tính BẢNG THỐNG KÊ SỐ LƯỢNG

Tại ô I5

=SUMIF($D$4:$D$11;E16;$E$4:$E$11)

Tại ô I6

=SUMIF($D$4:$D$11;C16;$E$4:$E$11)

Tại ô I7

=SUMIF($D$4:$D$11;D16;$E$4:$E$11)

Có nghĩa là:

$D$4:$D$11      Tra trong cột D4 đến D11

E16   Tìm giá trị là "Sữa" = E16, "Gạo" = C16 và "Đường" = D16

$E$4:$E$11 Tính tổng từ cột E4 đến E11

Vậy là xong, không quá khó đúng không nào, các bạn hãy nghiên cứu thêm và sử dụng các hàm HLOOKUP, SUMIF hiệu quả hơn.