công ty kế toán minh việt

Cách sử dụng Hàm HLOOKUP trong excel

Hướng dẫn Excel này giải thích cách sử dụng hàm Excel HLOOKUP với cú pháp và các ví dụ. Cách xử lý các lỗi như # N / A và truy xuất kết quả chính xác cũng được thảo luận.
Hàm Microsoft Excel HLOOKUP thực hiện tra cứu ngang bằng cách tìm kiếm một giá trị trong hàng trên cùng của bảng và trả về giá trị trong cùng một cột dựa trên chỉ số index_number .
Hàm HLOOKUP là một hàm dựng sẵn trong Excel được phân loại là hàm tra cứu / tham chiếu . Nó có thể được sử dụng như một hàm trang tính (WS) trong Excel. Là một hàm trang tính, hàm HLOOKUP có thể được nhập như là một phần của công thức trong một ô của trang tính.
 
Cú pháp cho hàm HLOOKUP trong Microsoft Excel là:
 
HLOOKUP( value, table, index_number, [approximate_match] )
Tham số hoặc đối số
Giá trị để tìm kiếm trong hàng đầu tiên của bảng .
Hai hoặc nhiều hàng dữ liệu được sắp xếp theo thứ tự tăng dần.
index_number
Số hàng trong bảng mà từ đó giá trị khớp phải được trả về. Hàng đầu tiên là 1.
approximate_match
Không bắt buộc. Nhập FALSE để tìm kết quả khớp chính xác. Nhập TRUE để tìm đối sánh gần đúng. Nếu tham số này bị bỏ qua, TRUE là mặc định.
Hàm HLOOKUP trả về bất kỳ kiểu dữ liệu nào như chuỗi, số, ngày, v.v. 
Nếu bạn nhập FALSE cho tham số gần đúng_match và không tìm thấy kết hợp chính xác, hàm HLOOKUP sẽ trả về # N / A. 
Nếu bạn chỉ định TRUE cho tham số approximate_match và không tìm thấy kết hợp chính xác, thì giá trị nhỏ hơn tiếp theo sẽ được trả về. 
Nếu index_number nhỏ hơn 1, hàm HLOOKUP sẽ trả về #VALUE !. 
Nếu index_number lớn hơn số cột trong bảng , hàm HLOOKUP sẽ trả về #REF !.
 
Áp dụng đối với: Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
 
Ví dụ (dưới dạng hàm bảng tính)
Hãy khám phá cách sử dụng hàm HLOOKUP như một hàm trang tính trong Microsoft Excel:

Dựa trên bảng tính Excel ở trên, các ví dụ HLOOKUP sau đây sẽ trả về:
 
=HLOOKUP(10251, A1:G3, 2, FALSE)
Result: $16.80    'Returns value in 2nd row
 
=HLOOKUP(10251, A1:G3, 3, FALSE)
Result: 6         'Returns value in 3rd row
 
=HLOOKUP(10248, A1:G3, 2, FALSE)
Result: #N/A      'Returns #N/A error (no exact match)
 
=HLOOKUP(10248, A1:G3, 2, TRUE)
Result: $14.00    'Returns an approximate match
Bây giờ, hãy xem ví dụ =HLOOKUP(10251, A1:G3, 2, FALSE)trả về giá trị $ 16,80 và xem xét kỹ hơn lý do tại sao.
 
Tham số đầu tiên
Tham số đầu tiên trong hàm HLOOKUP là giá trị để tìm kiếm trong bảng dữ liệu.
 
 
Trong ví dụ này, tham số đầu tiên là 10251. Đây là giá trị mà HLOOKUP sẽ tìm kiếm trong hàng đầu tiên của bảng dữ liệu.
Tham số thứ hai
Tham số thứ hai trong hàm HLOOKUP là bảng hoặc nguồn dữ liệu cần thực hiện tra cứu ngang.
 
 
Trong ví dụ này, tham số thứ hai là A1: G3. HLOOKUP sử dụng hàng đầu tiên trong phạm vi này (ví dụ: A1: G1) để tìm kiếm giá trị 10251.
Tham số thứ ba
Tham số thứ ba là số vị trí trong bảng nơi có thể tìm thấy dữ liệu trả về. Giá trị 1 cho biết hàng đầu tiên trong bảng. Hàng thứ hai là 2, v.v.

Trong ví dụ này, tham số thứ ba là 2. Điều này có nghĩa là hàng thứ hai trong bảng là nơi chúng ta sẽ tìm thấy giá trị trả về. Vì phạm vi bảng được đặt thành A1: G3, giá trị trả lại sẽ ở hàng thứ hai ở đâu đó trong phạm vi A2: G2.
Tham số thứ tư
Cuối cùng và quan trọng nhất là tham số thứ tư hoặc cuối cùng trong HLOOKUP. Thông số này xác định xem bạn có đang tìm kiếm kết quả khớp chính xác hoặc đối sánh gần đúng hay không.
 
 
Trong ví dụ này, tham số thứ tư là FALSE. Một tham số của FALSE có nghĩa là HLOOKUP đang tìm kiếm một đối sánh CHÍNH XÁC cho giá trị của 10251. Một tham số TRUE có nghĩa là một kết quả phù hợp sẽ được trả về. Vì HLOOKUP có thể tìm giá trị 10251 trong phạm vi A1: G1, nó trả về giá trị tương ứng từ A2: G2 là $ 16,80.

Kết hợp chính xác và so khớp gần đúng

Để tìm kết quả khớp chính xác, hãy sử dụng FALSE làm thông số cuối cùng. Để tìm đối sánh gần đúng, hãy sử dụng TRUE làm thông số cuối cùng.
 
Hãy tìm kiếm một giá trị không tồn tại trong dữ liệu của chúng tôi để chứng minh tầm quan trọng của tham số này!
 
Kết hợp chuẩn xác
Sử dụng FALSE để tìm đối sánh chính xác:
 
=HLOOKUP(10248, A1:G3, 2, FALSE)
Result: #N/A
Nếu không tìm thấy kết quả khớp chính xác, # N / A được trả về.
 
Trận đấu gần đúng
Sử dụng TRUE để tìm đối sánh gần đúng:
 
=HLOOKUP(10248, A1:G3, 2, TRUE)
  Result: $14.00
Nếu không tìm thấy kết quả phù hợp, nó trả về giá trị nhỏ hơn tiếp theo trong trường hợp này là $ 14,00.

HLOOKUP từ một trang tính khác

Bạn có thể sử dụng HLOOKUP để tra cứu một giá trị khi bảng nằm trên một trang tính khác. Hãy sửa đổi ví dụ của chúng ta ở trên và giả sử rằng bảng nằm trong một Trang tính khác được gọi là Sheet2 trong phạm vi A1:G3. Chúng tôi có thể viết lại ví dụ ban đầu của chúng tôi, nơi chúng tôi tra cứu giá trị 10251 như sau:
 
=HLOOKUP(10251, Sheet2!A1:G3, 2, FALSE)
Bởi trước phạm vi bảng với tên trang tính và dấu chấm than, chúng tôi có thể cập nhật HLOOKUP của chúng tôi để tham chiếu bảng trên một trang tính khác.
 
HLOOKUP từ một trang tính khác với dấu cách trong tên trang tính
Hãy ném thêm một biến chứng nữa. Điều gì sẽ xảy ra nếu tên trang tính của bạn chứa khoảng trắng? Nếu có không gian trong tên trang tính, bạn sẽ cần phải thay đổi công thức hơn nữa.
 
Giả sử rằng bảng nằm trên một Sheet được gọi là "Test Sheet" trong phạm vi A1: G3, chúng ta sẽ cần phải sửa đổi công thức của chúng ta như sau:
 
=HLOOKUP(10251, 'Test Sheet'!A1:G3, 2, FALSE)
Bằng cách đặt tên trang tính trong dấu nháy đơn, chúng tôi có thể chứa tên trang tính có dấu cách trong hàm HLOOKUP của chúng tôi.

Tại sao nên sử dụng tham chiếu tuyệt đối?

Bây giờ điều quan trọng là chúng ta phải đề cập đến một sai lầm khác thường được thực hiện. Khi mọi người sử dụng hàm HLOOKUP, họ thường sử dụng tham chiếu tương đối cho phạm vi bảng như chúng ta đã làm trong các ví dụ trên. Điều này sẽ trả về câu trả lời đúng, nhưng điều gì xảy ra khi bạn sao chép công thức sang một ô khác? Phạm vi bảng sẽ được điều chỉnh bởi Excel và thay đổi liên quan đến nơi bạn dán công thức mới. Hãy giải thích thêm ...
 
Vì vậy, nếu bạn có công thức sau trong ô J1:
 
=HLOOKUP(10251, A1:G3, 2, FALSE)
Và sau đó bạn đã sao chép công thức này từ ô J1 sang ô K2, nó sẽ sửa đổi công thức HLOOKUP thành công thức này:
 
=HLOOKUP(10251, B2:H4, 2, FALSE)
Vì bảng của bạn được tìm thấy trong phạm vi A1: G3 chứ không phải B2: H4, công thức của bạn sẽ trả về kết quả sai trong ô K2. Để đảm bảo rằng phạm vi của bạn không thay đổi, hãy thử tham khảo phạm vi bảng của bạn bằng cách sử dụng tham chiếu tuyệt đối như sau:
 
=HLOOKUP(10251, $A$1:$G$3, 2, FALSE)
Bây giờ nếu bạn sao chép công thức này sang một ô khác, phạm vi bảng của bạn sẽ vẫn là $ A $ 1: $ G $ 3.

Cách xử lý lỗi # N / A

Cuối cùng, hãy xem xét cách xử lý các cá thể trong đó hàm HLOOKUP không tìm thấy một kết quả phù hợp và trả về lỗi # N / A. Trong hầu hết các trường hợp, bạn không muốn thấy # N / A nhưng muốn hiển thị kết quả thân thiện với người dùng hơn.
 
Ví dụ: nếu bạn có công thức sau:
 
=HLOOKUP(10248, $A$1:$G$3, 2, FALSE)
Thay vì hiển thị lỗi # N / A nếu bạn không tìm thấy kết quả phù hợp, bạn có thể trả lại giá trị "Không tìm thấy". Để làm điều này, bạn có thể sửa đổi công thức HLOOKUP của bạn như sau:
 
=IF(ISNA(HLOOKUP(10248, $A$1:$G$3, 2, FALSE)), "Not Found", HLOOKUP(10251, $A$1:$F$2, 2, FALSE))
HOẶC LÀ
 
=IFERROR(HLOOKUP(10248, $A$1:$G$3, 2, FALSE), "Not Found")
HOẶC LÀ
 
=IFNA(HLOOKUP(10248, $A$1:$G$3, 2, FALSE), "Not Found")
Các công thức này sử dụng các hàm ISNA , IFERROR và IFNA để trả về "Không tìm thấy" nếu một hàm trùng khớp không được tìm thấy bởi hàm HLOOKUP.
 
Đây là một cách tuyệt vời để tạo bảng tính của bạn để bạn không thấy các lỗi Excel truyền thống.

 

107.21.16.70

Bình luận

Các bài viết mới

Các tin cũ hơn