Bật mí cách kết hợp VLOOKUP và HLOOKUP mà bạn cần biết đến

Việc kết hợp Vlookup và hlookup có thể giúp bạn giải quyết nhiều vấn đề cho công việc, tuy nhiên cần biết rõ chức năng và thông tin của từng hàm để tìm hiểu vai trò trong quá trình sử dụng Excel. Hãy cùng chúng tôi đọc bài viết để hiểu thêm về nhé

Có nhiều bạn hỏi cách sử dụng tính năng của hàm Vlookup và hàm Hlookup trong thủ thuật Excel và kỹ năng excel, cụ thể là cách phân biệt thời điểm sử dụng tính năng của sự kết hợp Vlookup và Hlookup, hôm nay chúng ta sẽ cùng nhau tìm hiểu chi tiết cách sử dụng tính năng bằng những kỹ năng tin học và kỹ năng văn phòng, cách phân biệt rõ ràng và dễ nhớ và có hình ảnh minh họa chi tiết về việc kết hợp vlookup và hlookup

I. Hàm Vlookup trong Excel 

Trước khi kết hợp vlookup và hlookup, chúng ta sẽ sử dụng những kỹ năng Excel để tìm hiểu về hàm vlookup trước nhé và những kỹ năng tin học cần biết nhé!

Tính năng Vlookup trong Micorsoft có công thức cú pháp trong thủ thuật Excel như sau sau:

= VLOOKUP (Giá trị tra cứu, Danh sách bảng, Số chỉ mục Col, [Phạm vi tra cứu])

GIẢI THÍCH:

- Giá trị tra cứu: Giá trị bạn muốn dò tìm. Bạn có thể chèn trực tiếp các giá trị trong công thức hoặc tham chiếu đến một ô trên bảng tính Excel. Đây cũng là một trong những kỹ năng văn phòng cần biết không chỉ kết hợp vlookup và hlookup.

-Mảng bảng: Bảng giới hạn dò tìm, với mục đích sao chép công thức tự động, bạn cần F4 để sửa giá trị cố định, nếu bạn dùng laptop thì có thể nhấn FN + F4 để sửa kết nối. Khu vực của bảng tính này.

-Số chỉ mục Col: Số cột từ trái sang phải để lấy dữ liệu từ bảng cần kiểm tra.

- Phạm vi tra cứu: Giá trị logic (TRUE = 1, FALSE = 0) quyết định so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn.

+ If Range_lookup = 1 (TRUE): So sánh tương đối.

+ Nếu Range_lookup = 0 (FALSE): So sánh chính xác.

+ Nếu bỏ qua đối số này, Excel sẽ hiểu rằng Range_lookup = 1 theo mặc định. Đây cũng chính là những điều cần biết trong kỹ năng Excel trước khi mình hướng dẫn các bạn kết hợp vlookup và hlookup bằng thủ thuật Excel nhé! 

II. Hàm Hlookup trong Excel 

Không chỉ kết hợp vlookup và hlookup , Hàm HLOOKUP là một trong những hàm cơ bản trong excel, như đã mô tả ở trên, hàm Hlookup được sử dụng khá nhiều hiện nay, tính hữu dụng của hàm Hlookup giúp ích rất nhiều trong kỹ năng văn phòng, công việc và phân tích. Cụ thể, với cú pháp và cách sử dụng khá rõ ràng, nó giúp tìm các giá trị trong một hàng của bảng hoặc danh sách các giá trị, chủ yếu được sử dụng khi các giá trị so sánh ở hàng ngang, khi các giá trị được so sánh bằng những kỹ năng tin học và kỹ năng Excel. Chúng tôi sẽ sử dụng tính năng Vlookup nằm trên một cột. Đây là một trong những tính năng được sử dụng khá nhiều trong Excel, cho phép bạn tham chiếu đến các cột và hàng sau khi thỏa mãn các điều kiện để trả về kết quả phù hợp. Vì sự phổ biến của nó, bài viết dưới đây với cú pháp và cách sử dụng tính năng HLOOKUP trong Excel có thể giúp bạn đọc nắm vững cách sử dụng, không chỉ là sự kết hợp vlookup và hlookup

1. Cú pháp hàm HLOOKUP trong Excel

Công thức: Hlookup (giá trị cần thăm dò, bảng giá trị phát hiện, số hàng được truy xuất, phạm vi tìm kiếm)

 -Bên trong:

Bảng giá trị phát hiện: Phải ở địa chỉ tuyệt đối (bằng cách chọn bảng và nhấn F4), nếu tiêu đề không được quét.
Phạm vi tìm kiếm: Nếu Sai (0) là một phát hiện chính xác, thì True (1) là một phát hiện tương đối, ngược lại.

2. Ví dụ củ thể hàm Hlookup trong Excel

Ví dụ 1: Sử dụng trang thông tin sau để điền vào cột Xếp hạng Học lực của Học sinh:
Để làm điều này, bạn sẽ sử dụng công thức Hlookup mà không phải kết hợp vlookup và hlookup

Việc kết hợp vlookup và hlookup có thể giúp cho việc tính toán dễ dàng hơn

Với công thức ô E6: =HLOOKUP(D6,$B$16:$F$17,2,1)

Chú ý: Một lưu ý cho bạn là bạn scan bảng điểm rồi nhấn F4 khi nhập xong D6 trong HLOOKUP và chọn 2 là Xếp loại, 1 Có nghĩa là TRUE  (tức là tìm kiếm tương đối).

Sau đây là 1 số lý do khiến cho Hlookup bị mắc lỗi:

+HLOOKUP không thể tìm kiếm các hàng ở trên
Ngay cả khi bạn đã quên tất cả thông tin tìm kiếm Excel theo chiều ngang, hãy nhớ rằng Hlookup sẽ chỉ tìm kiếm ở hàng đầu tiên của bảng. Một lỗi N / A được trả về nếu các giá trị tìm kiếm nằm trong các hàng khác. Bạn nên sử dụng công thức INDEX / MATCH để sửa lỗi này. Cần để ý kỹ tới chi tiết này vì nó cũng tương tự như lỗi sai trong việc kết hợp vlookup và hlookup 

+Kết quả được ước tính và chính xác.
Trong hầu hết các trường hợp, cho dù bạn đang tìm kiếm một giá trị theo chiều ngang hay chiều dọc trong Excel, bạn đang tìm kiếm một giá trị cụ thể, vì vậy bạn cần một kết quả khớp chính xác. Đảm bảo đặt các giá trị trong hàng theo thứ tự tăng dần khi tìm kiếm kết quả gần đúng (tra cứu phạm vi được đặt thành TRUE hoặc để trống). Qúa trình kết hợp vlookup và hlookup có thể gây sai sót giống như vậy nên bạn hãy chú ý nhé

+ Thêm hoặc xóa các dòng mới

Bạn cần hiểu cách HLOOKUP lấy dữ liệu về giá trị tìm kiếm dựa trên số liệu của hàng bạn xác định để hiểu tại sao việc thêm một hàng mới có thể gây hại cho công thức Hlookup của bạn.

Giả sử bạn muốn nhận số liệu bán hàng dựa trên ID sản phẩm. Các số này ở hàng 4, vì vậy bạn nhập 4 vào chỉ số hàng tham số num. Nhưng nó sẽ trở thành hàng thứ 5 khi một hàng mới được chèn vào và Hlookup không hoạt động. Khi bạn xóa một hàng khỏi bảng, vấn đề tương tự cũng phát sinh. Cần để ý kỹ tới chi tiết này vì nó cũng tương tự như lỗi sai trong việc kết hợp vlookup và hlookup 
Để ngăn người dùng chèn một hàng mới hoặc sử dụng công thức INDEX / MATCH thay cho Hlookup, giải pháp là khóa bảng. Bạn phải xác định các hàng để định vị và trả về giá trị dưới dạng mảng tham chiếu trong Chỉ mục / Khớp và Excel có thể tự thay đổi các tham chiếu này. Do đó, khi sử dụng công thức Hlookup, bạn không phải suy nghĩ về việc thêm hoặc xóa các hàng như khi sử dụng nó.

+ Trùng lặp trong bảng

Trong Excel, hàm HLOOKUP chỉ có thể trả về một giá trị, là giá trị đầu tiên trong bảng khớp với giá trị tra cứu.
Nếu bảng chứa nhiều giá trị tương tự, hãy chọn một trong các giải pháp phù hợp nhất với bạn:

•    Xóa các bản sao bằng phần mềm từ Excel

•    Tạo PivotTable để nhóm và lọc dữ liệu theo nhu cầu của bạn nếu bạn muốn giữ lại các bản sao trong dữ liệu.

Để xóa các giá trị trùng lặp khỏi danh sách tra cứu, hãy sử dụng công thức mảng.

Như vậy trên đây các bạn đã được chúng tôi hướng dẫn cách sử dụng hàm HLOOKUP trong thủ thuật Excel bằng những kỹ năng Excel và kỹ năng tin học, các bạn có thể kiểm tra và tham khảo các giá trị bảng tính Excel, nắm được cú pháp và cách sử dụng hàm này hoặc sử dụng kết hợp vlookup và hlookup. Nó có thể được thực hiện rất hiệu quả trong thực tế. Ngoài ra, khi bạn biết cách sử dụng tính năng Vlookup, bạn sẽ thấy nó dễ dàng hơn nhiều. Hàm Vlookup cũng là một hàm tìm kiếm có điều kiện, luôn được chọn để làm việc và phân tích, và hàm Vlookup trả về giá trị chính xác phù hợp với điều kiện của bạn. Ngoài ra bạn có thể tìm hiểu sự kết hợp vlookup và hlookup có thể giúp bạn giải quyết những kỹ năng văn phòng một cách hiệu quả

III. Phân biệt hàm Vlookup và hlookup

Như chúng ta đã biết, việc kết hợp VLOOKUP và HLOOKUP là hai tính năng tham chiếu và tìm kiếm được sử dụng rộng rãi trong Excel. Nhưng rất nhiều bạn đôi khi nhầm lẫn về 2 vai trò này do cấu tạo và cách phát âm giống hệt nhau. Hãy xem cách Excel Online phân biệt giữa các hàm VLOOKUP và HLOOKUP trong bài viết này và chia sẻ chìa khóa về thời điểm nên kết hợp hàm VLOOKUP hoặc HLOOKUP và sử dụng tính năng của hai hàng này.

1. Cơ chế hoạt động

Cơ chế hoạt động là điểm phân biệt cơ bản nhất giữa hai chức năng này và việc kết hợp vlookup và hlookup . Bạn sẽ thấy hình sau để minh họa chính xác cơ chế hoạt động của hai chức năng này:

cần phân biệt tính năng và vai trò để hiểu và kết hợp vlookup và hlookup

Ví dụ trong hình trên mô tả hai hàm, VLOOKUP và HLOOKUP, có

•    Tra cứu giá trị là chữ A

•    Một bảng có 5 cột và 5 hàng là một chuỗi bảng.

•    Trong vlookup, số chỉ mục col và số chỉ mục hàng (trong hlookup) đều là 4

•    Cả hai giá trị 0 đều là tra cứu phạm vi

Tuy nhiên, quá trình tìm kiếm hơi khác bằng những kỹ năng Excel và thủ thuật Excel . Với các đối tượng sau, chúng ta có thể phân biệt rõ ràng:

Bạn có thể tham khảo một vài cơ chế trước khi kết hợp vlookup và hlookup

Bạn cũng có thể sử dụng kết hợp vlookup và hlookup để làm cho trường hợp này

2. Bí quyết sử dụng hàm

Khi nào nên sử dụng VLOOKUP và khi nào nên sử dụng HLOOKUP và thời điểm nào để kết hợp vlookup và hlookup? Với câu hỏi này, chắc hẳn bạn đang rất thắc mắc. Để dễ dàng nhận ra, chúng ta có một bí quyết đó là dựa vào đối tượng Lookup value và cấu trúc không gian bảng Table Array (bảng chứa dữ liệu tìm kiếm và kết quả tìm kiếm)

Sử dụng Vlookup nếu giá trị tra cứu nằm trong cột đầu tiên của bảng,

Sử dụng Hlookup nếu giá trị tra cứu nằm trên hàng đầu tiên của bảng,

Bạn có thể thấy sự hữu ích của nó bằng cách cố gắng sử dụng bí quyết này để thực hiện bài tập sau:

Sau đây là bài tập có thể sử dụng một trong hai hàm mà không kết hợp vlookup và hlookup

Đây là một dạng bài tập trong đó yêu cầu có thể được trả lời đầy đủ theo hai cách: sử dụng cả kết quả VLOOKUP và HLOOKUP. Vì vậy, chúng ta hãy xem làm thế nào để làm điều đó khi thực hiện cả hai chức năng này cùng một yêu cầu. Hãy xem xét bảng dữ liệu A1: G4 trong trường hợp này

Yêu cầu 1: Tìm doanh thu tháng 2

SỬ DỤNG VLOOKUP

Quy tắc của vlookup là cột đầu tiên của bảng dữ liệu phải chứa giá trị tra cứu. Trong cột A, vị trí A2, chúng tôi thấy 'Doanh thu'. Do đó, nó sẽ được đo từ cột A trở đi bằng cách sử dụng vlookup. Kết quả được đo lường là doanh thu tháng 2, trong đó tháng 2 xuất hiện ở cột thứ ba của cột A. Do đó, kết quả của hàm VLOOKUP như sau:

Bạn cũng có thể sử dụng hàm Hlookup cho bài tập này

SỬ DỤNG HLOOKUP

Lệnh này là để tìm doanh số cho tháng Hai. Trong hàng đầu tiên của bảng dữ liệu, chúng ta thấy tháng Hai. Vì vậy, nếu chúng ta coi giá trị tra cứu là tháng Hai, chúng ta có thể tìm kiếm bằng hàm HLOOKUP. Do đó, kết quả tìm kiếm là doanh số bán hàng thứ hai
Vì vậy, chúng tôi thấy và đánh giá rằng có cùng một kết quả cho cả hai chức năng.
Qua những thông tin về việc sử dụng hàm, bạn đã hiểu được những tính năng và cách sử dụng. Sau đây chúng ta cùng sử dụng việc kết hợp vlookup và hlookup trong quá trình đò tìm dữ liệu nhé!

IV. Cách kết hợp HLOOKUP và VLOOKUP dò tìm dữ liệu

Bạn chắc chắn đã quen với việc sử dụng các tính năng kết hợp Hlookup và Vlookup để tìm thông tin chi tiết khi đọc ở những phần trước rồi phải không? Hôm nay, chúng tôi sẽ cho bạn thấy cách kết hợp HLOOKUP và VLOOKUP để phát hiện và tính toán dữ liệu. Việc kết hợp các tính năng của Excel cũng như kết hợp vlookup và hlookup sẽ giúp chúng tôi tiết kiệm rất nhiều thời gian xử lý. Chúng tôi sẽ lấy một ví dụ cụ thể về sự kết hợp của các hàm Hlookup và Vlookup phát hiện dữ liệu để làm cho việc tính toán cuối cùng trở nên ngắn gọn và đơn giản hơn, để xem sự kết hợp của các hàm Vlookup và Hlookup. Vui lòng tham khảo bài viết sau đây nhé!

Quay trở lại việc lồng ghép, kết hợp VLOOKUP và HLOOKUP,. Trong bài này, chúng ta có ví dụ sau để có thể hiểu rõ hơn: Chúng ta cần đo ThanhTien với ThanhTien = SoLuong * Gia, với SoLuong trong bảng 2 và Gia trong bảng 3.

Định nghĩa để giải quyết yêu cầu này: chúng ta sẽ cần căn cứ MaHang trong Bảng 1, để phát hiện TenHang trong Bảng 2 (sử dụng hàm HLOOKUP), sau đó sử dụng TenHang từ kết quả trả về của hàm HLOOKUP ở trên. Đánh dấu vào bảng 3 để lấy Gia (hàm Vlookup) rồi nhân để lấy ThanhTien bằng SoLuong.

Nhớ kỹ những ký tự vì sử dụng kết hợp vlookup và hlookup có một chút phức tạp

Với việc hình thành các cột phụ, bạn có thể làm cho nó đơn giản hơn. Tuy nhiên, chúng ta sẽ sử dụng kết hợp các hàm VLOOKUP và HLOOKUP trong trường hợp này. Chính xác hơn, trong ô E3, chúng ta có công thức như sau:

=D3*VLOOKUP(HLOOKUP(B3,$C$9:$F$10,2,0),$B$14:$D$17,3,0)

Công thức trên có thể được hiểu như sau: Giá trị trả về của hàm HLOOKUP là giá trị tìm kiếm của hàm VLOOKUP.

Bạn có thể tham khảo bài này để thực hiện nhiều bài khác nhau

Nếu định nghĩa trên khiến bạn khó hình dung, bạn có thể vào tab Công thức, hộp thoại Đánh giá Công thức hiển thị trong nhóm Kiểm tra Công thức và bạn có thể từ từ nhấn Đánh giá để xem Excel xác định công thức trên như thế nào. Để giúp bạn hiểu rõ hơn trong việc kết hợp vlookup và hlookup 

V. Kết luận

 Chúng ta có thể thấy rằng Excel giúp người dùng sử dụng rất linh hoạt và hiệu quả các tính năng của thủ thuật Excel. Ngoài ra, khả năng viết hàm, đặc biệt là tích hợp hàm là một thành phần rất quan trọng để bạn có thể thực hiện nhanh chóng và hiệu quả các hàm trong Excel để giải quyết các yêu cầu công việc bằng kỹ năng tin học và kỹ năng văn phòng. Việc sử dụng kết hợp vlookup và hlookup cũng có thể giải quyết những vấn đề thời gian, tuy nhiên kết hợp vlookup và hlookup với cấu trúc công thức dài rất dễ gây sai sót trong quá trình sử dụng máy tính