Cách sử dụng hàm HLOOKUP trong Excel cho người mới

0

Tương tự như người anh em VLOOKUP, hàm HLOOKUP trong Excel cũng là một hàm vô cùng quan trọng và cần thiết mà người dùng Excel cần biết. Khi thành thạo HLOOKUP, bạn sẽ thấy mọi thứ dường như trở nên đơn giản hơn rất nhiều cho dù bảng tính có bao nhiêu hàng hay cột. Nếu bạn chưa biết rõ về chức năng này thì đừng ngần ngại đọc bài viết này của Thuthuat.tip.edu.vn?

HLOOKUP trong Excel

Hàm HLOOKUP trong Excel là gì?

Trong Excel, hàm HLOOKUP là một hàm sẽ giúp bạn tìm giá trị mong muốn từ một trang tính và tự động điền nó vào một trang tính mới hoặc một vị trí riêng biệt. Điều này có thể giúp bạn tiết kiệm rất nhiều thời gian do không phải tự mình tìm kiếm và sao chép dữ liệu từng cái một.

HLOOKUP là từ viết tắt của H – Ngang – đường ngangLOOKUP – Tra cứu – Phát hiện bằng tiếng Anh. Tức là nó sẽ tìm kiếm dữ liệu ở định dạng ngang, nhưng khi trả về, nó sẽ trả về ở định dạng dọc.

Khi nào sử dụng hàm LOOKUP trong Excel?

Hàm HLOOKUP được sử dụng khi bạn cần tra cứu dữ liệu trong bảng, theo phạm vi theo chiều ngang (từ trái sang phải) và trả về dữ liệu tương ứng theo chiều dọc (lên rồi xuống).

Bạn có thể sử dụng HLOOKUP khi muốn tìm tên sản phẩm, đơn giá, số lượng, .. dựa trên mã hàng có sẵn hoặc tìm tên nhân viên, phân loại nhân viên dựa trên một số tiêu chí cố định.

Công thức hàm HLOOKUP trong Excel

Hàm HLOOKUP có cú pháp sau:

= HLOOKUP (Lookup_value, Table_array, Row_index_num, Range_lookup)

Trong đó, bạn cần chú ý:

  • Lookup_value: Giá trị cần tìm
  • Table_array: Khu vực giới hạn phát hiện
  • Row_index_num: Số thứ tự của hàng để truy xuất dữ liệu trong bảng cần tìm kiếm, từ trên xuống dưới
  • Range_lookup (TRUE hoặc FALSE): tìm kiếm chính xác hoặc tương đối trong bảng giới hạn. Có thể được bỏ qua và khi bỏ qua, Excel sẽ mặc định là TRUE.

Với Range_lookup = TRUE: Excel sẽ trả về kết quả tìm kiếm gần đúng.

Với Range_lookup = FALSE: Excel sẽ trả về kết quả tìm kiếm chính xác. Nếu không tìm thấy, Excel sẽ trả về # N / A.

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

Cách sử dụng HLOOKUP và LEFT. chức năng

Cú pháp của hàm LEFT là:

= LEFT (văn bản; n)

Đây là hàm sẽ giúp bạn lấy ký tự thứ N tính từ ở bên trái của văn bản trong bản văn. Khi kết hợp hai hàm HLOOKUP và LEFT, công việc điền dữ liệu của bạn sẽ nhẹ nhàng và nhanh chóng hơn bao giờ hết!

Ví dụ, bạn cần nhập tên ngành khi biết mã lớp. Mã lớp bao gồm cả phần chữ và số, nhưng lớp chỉ bao gồm phần chữ và số. Sau đó, bạn sẽ cần sự kết hợp của HLOOKUP và LEFT.

Công thức bạn điền vào sẽ là:

= HLOOKUP (LEFT (C4; 4); $ A $ 12: $ F $ 13; 2; FALSE)

Trong đó:

  • C4: địa chỉ của ô được phân tách khỏi ký tự
  • 4: Bạn cần tách từ trái sang phải ký tự nào?
  • TRÁI (C4; 4): trích xuất 4 ký tự đầu tiên (từ trái sang phải) của dữ liệu trong hộp Mã lớp
  • $ A $ 12: $ F $ 13: vùng dữ liệu để tìm kiếm
  • 2: số thứ tự của dòng Tên ngành sẽ được điền vào. Dòng Mã ngành sẽ được tính là 1 nên Dòng Tên ngành sẽ là 2
  • SAI: Dữ liệu nhập phải hoàn toàn chính xác

HLOOKUP trong Excel

Kết quả trong hộp:

HLOOKUP trong Excel

Sao chép công thức vào các dòng còn lại và bạn đã hoàn tất:

HLOOKUP trong Excel

Hàm HLOOKUP kết hợp IF. hàm số

Khi kết hợp HLOOKUP và IF, bạn sẽ dễ dàng sắp xếp và điền thông tin dựa trên điều kiện bạn đưa ra.

Ví dụ, bạn cần đánh giá xem học sinh của mình đạt hay không đạt trong một kỳ thi các cấp bằng dữ liệu trong hai bảng sau.
HLOOKUP trong Excel

Khi đó, nếu học sinh dự thi cấp Trường đạt từ 500 điểm trở lên thì học sinh điền chữ “Đạt” vào cột đánh giá, ngược lại học sinh điền chữ “Không đạt”. Đối với kỳ thi cấp Thành phố và cấp Tỉnh cũng vậy.

Công thức điền vào cột Thúc giục sẽ:

= IF (E4> = HLOOKUP (D4; $ C $ 12: $ F $ 13; 2; FALSE); “Đạt”; “Không đạt”)

Trong đó,

  • E 4: địa chỉ của ô chứa điểm của học sinh
  • D4: địa chỉ ô chứa điều kiện tìm kiếm, trong trường hợp này là tên kỳ thi
  • $ C $ 12: $ F $ 13: địa chỉ của vùng dữ liệu được tìm kiếm
  • 2: số thứ tự của dòng dữ liệu cần điền so với dữ liệu cần tìm. Dòng tên bài thi là dòng cần tìm sẽ là dòng thứ 1 nên dòng điểm đậu là dòng cần điền sẽ ở vị trí thứ 2
  • SAI: Dữ liệu được tìm kiếm phải chính xác tuyệt đối
  • “Vẫn tồn tại”: Nếu điều kiện là đúng, hãy ghi từ “Đạt”. Trong trường hợp này, nếu điểm của học sinh lớn hơn hoặc bằng điểm đạt của kỳ thi tương ứng, học sinh sẽ đạt.
  • “Không đạt”: Nếu điều kiện sai, hãy viết từ “Không đạt”. Trong trường hợp này, nếu điểm của học sinh nhỏ hơn điểm đạt của kỳ thi tương ứng, học sinh sẽ không đạt.

HLOOKUP trong Excel

Kết quả nhận được trong ô đầu tiên:

HLOOKUP trong Excel

Sao chép công thức vào các hàng còn lại để hoàn thành thông tin cột Thúc giục:

HLOOKUP trong Excel

Lưu ý khi áp dụng hàm HLOOKUP để tìm giá trị

Khi sử dụng hàm HLOOKUP trong Excel với mục đích tìm kiếm giá trị, bạn cần lưu ý một số điều sau để đảm bảo rằng dữ liệu tìm kiếm được nhận như mong đợi và không có bất kỳ lỗi nào.

  • Đảm bảo dữ liệu trong ô tìm kiếm và hàng đầu tiên trong bảng dữ liệu có cùng định dạng (văn bản hoặc số).
  • Đảm bảo khóa vùng dữ liệu tìm kiếm để địa chỉ không bị nhảy trong quá trình sao chép công thức.
  • Đảm bảo chọn đúng thứ tự các dòng cần điền so với các dòng cần tìm. Dòng đầu tiên trong phạm vi dữ liệu bạn chọn để tìm kiếm sẽ là 1.

Ví dụ về hàm HLOOKUP trong Excel

Sử dụng hàm HLOOKUP trong Excel để trích xuất bảng lương cho nhân viên

Giả sử, bạn có một bảng tổng số và bạn muốn trích xuất dữ liệu của một số nhân viên như sau:

HLOOKUP trong Excel

Khi đó, bạn nên sử dụng hàm HLOOKUP để điền thông tin vào bảng dữ liệu mới thay vì sao chép từng dòng một. Ví dụ: nếu bạn muốn điền vào một ô có số ngày làm việc, bạn sẽ áp dụng công thức sau:

= HLOOKUP (B13; $ A $ 1: $ I $ 7; 6; FALSE)

Trong đó:

  • B13: là địa chỉ ô của dữ liệu cần tìm kiếm. Trong trường hợp này là tên của nhân viên.
  • $ A $ 1: $ I $ 7: là địa chỉ của vùng dữ liệu bạn cần tìm kiếm. Sau khi chọn vùng dữ liệu, nhấn F4 để khóa vùng dữ liệu này. Khi đó dấu $ sẽ xuất hiện và ngay cả khi bạn sao chép công thức xuống các dòng bên dưới thì vùng dữ liệu này vẫn không bị thay đổi.
  • 6: là số thứ tự của dòng để nhập các ký tự từ dòng chứa dữ liệu cần tìm trong bảng tính. Trong trường hợp này, dữ liệu cần tra cứu là họ và tên nên dòng họ trong bảng tính sẽ được tính là 1, dòng cần điền dữ liệu là dòng Số ngày làm việc, tính từ họ trở xuống là dòng thứ 6, vì vậy dữ liệu này sẽ cần nhập số 6.
  • SAI: Vì thông tin cần điền phải hoàn toàn chính xác, vui lòng nhập FALSE tại đây.

HLOOKUP trong Excel

Tương tự với phần Lương, bạn chỉ cần đổi số 6 thành số 7, tức là số liệu cần điền nằm ở dòng Lương thay vì dòng Số ngày làm việc. Sao chép công thức này vào các ô còn lại để hoàn thành trang tính:

HLOOKUP trong Excel

Sử dụng hàm HLOOKUP trong Excel để cho điểm học sinh dựa trên điểm

Ví dụ, bạn cần xếp loại học lực của sinh viên dựa trên điểm trung bình của họ, bạn cần chuẩn bị một bảng dữ liệu chứa các điểm số như sau:

HLOOKUP trong Excel

Bây giờ, trong cột xếp hạng, bạn sẽ sử dụng công thức sau:

= HLOOKUP (D4; $ B $ 11: $ F $ 12; 2; TRUE)

Trong đó:

  • D4: là địa chỉ ô của dữ liệu cần tìm kiếm. Trong trường hợp này là điểm của học sinh.
  • $ B $ 11: $ F $ 12: là địa chỉ của vùng dữ liệu bạn cần tìm kiếm. Sau khi chọn vùng dữ liệu, nhấn F4 để khóa vùng dữ liệu này. Khi đó dấu $ sẽ xuất hiện và ngay cả khi bạn sao chép công thức xuống các dòng bên dưới thì vùng dữ liệu này vẫn không bị thay đổi.
  • 2: là số thứ tự của dòng để nhập các ký tự từ dòng chứa dữ liệu cần tìm trong bảng tính. Trong trường hợp này, dữ liệu cần tìm là điểm nên dòng điểm trong bảng tính bên dưới sẽ được tính là 1, dòng cần điền dữ liệu là dòng đánh giá, đếm từ dòng điểm trở xuống là dòng thứ 2, vì vậy dữ liệu này sẽ cần Nhập số 2.
  • THÀNH THẬT: Vì bảng tính của bạn bên dưới đã được chia sẵn nên dữ liệu cần điền chỉ cần tương đối chính xác. Tức là, nếu điểm trên 0 và dưới 5, Excel sẽ nhập Yếu, trên 5 và dưới 7, Excel sẽ nhập Trung bình, v.v.

HLOOKUP trong Excel

Sao chép công thức xuống các dòng còn lại và kết quả là:

HLOOKUP trong Excel

HLOOKUP và VLOOKUP. hàm số

Cả HLOOKUP và VLOOKUP đều có thể tìm kiếm được. Tuy nhiên, hai chức năng này có những điểm khác biệt mà bạn cần phân biệt để tiện sử dụng cho công việc của mình.

TÊN NỔI TIẾNG HLOOKUP VLOOKUP
Định nghĩa Theo dõi dữ liệu theo chiều ngang Truy tìm dữ liệu ở định dạng dọc
Lookup_value

(Giá trị tìm kiếm, phần tử đầu tiên trong công thức)

Tìm trong dòng đầu tiên của dải dữ liệu Tìm kiếm trong cột đầu tiên của dải dữ liệu
Row / Column_index_ num

(Vị trí của giá trị được điền liên quan đến giá trị được tìm kiếm)

Tọa lạc tại vị trí hiện hành ngày từ dòng giá trị cần tra cứu trong bảng tính, với dòng giá trị cần tìm là 1 Tọa lạc tại vị trí cây cột số giá trị từ cột giá trị cần tìm trong bảng tính là bao nhiêu, với cột giá trị cần tìm là 1

Khi bạn đã phân biệt cẩn thận hai chức năng này, chắc chắn rằng công việc của bạn với bảng tính sẽ dễ dàng hơn bao giờ hết.

Những lý do chính khiến hàm HLOOKUP không hoạt động

Khi sử dụng hàm HLOOKUP trong Excel, sẽ có lúc hàm không chạy. Trong đó, lỗi chủ yếu do 3 nguyên nhân sau:

Lỗi này có nghĩa là khi ô mà bạn trỏ đến trong công thức chứa dữ liệu số, nhưng dòng đầu tiên lại chứa dữ liệu văn bản (hoặc ngược lại). Để khắc phục, bạn có thể sử dụng hàm TEXT trong công thức hoặc chỉnh sửa các giá trị ô cho phù hợp.

  • Khoảng trống ở cuối điều kiện tìm kiếm

Nếu bạn đã sử dụng hàm HLOOKUP trên Excel và kiểm tra kỹ không để xảy ra lỗi nhưng kết quả vẫn sai hoặc không có kết quả thì nguyên nhân có thể nằm ở việc dữ liệu của bạn bị dư thừa. cách cuối cùng. Tức là, thay vì “Thanh Minh”, dữ liệu trong ô là “Thanh Minh”, dẫn đến việc Excel không thể tìm thấy chính xác dữ liệu mà bạn muốn.

Để khắc phục lỗi này, bạn chỉ cần kiểm tra để đảm bảo 100% ô của bạn không có bất kỳ dấu cách nào ở cuối. Hoặc sử dụng công cụ Văn bản thành Cột trên tab Dữ liệu để loại bỏ toàn bộ khoảng cách.

  • Không khóa bảng dữ liệu tìm kiếm

Khóa bảng dữ liệu tìm kiếm đặc biệt quan trọng khi bạn sử dụng hàm VLOOKUP hoặc HLOOKUP trong Excel. Vì Excel có tính năng tự động thay đổi địa chỉ ô khi bạn copy công thức sang các dòng khác nên nếu bạn không khóa bảng dữ liệu thì khi sao chép công thức Excel sẽ thay đổi giá trị địa chỉ của bảng tra cứu này. Điều này dẫn đến việc bạn không tìm thấy giá trị mong muốn hoặc giá trị sai hoặc bạn nhận được lỗi #VALUE! lỗi.

Để khóa bảng dữ liệu, trong công thức, sau khi chọn vùng tìm kiếm, bạn chỉ cần nhấn F4 trên bàn phím (hoặc một số máy tính xách tay là sự kết hợp Fn + F4).

Xem thêm:

Đừng quên Like, Share nếu bạn thấy những cách sử dụng hàm HLOOKUP trong Excel này hữu ích nhé! Nếu truy cập Thuthuat.tip.edu.vn thường xuyên, bạn còn có cơ hội học hỏi thêm nhiều thủ thuật thú vị khác.

Excel –

Leave a comment