Phân tích dữ liệu Excel - Hàm tra cứu

Bạn có thể sử dụng các hàm Excel để -

Tìm giá trị trong một phạm vi dữ liệu - VLOOKUP và HLOOKUP
Nhận một giá trị hoặc tham chiếu đến một giá trị từ trong bảng hoặc dải ô - INDEX
Có được vị trí tương đối của một mục được chỉ định trong một phạm vi ô - MATCH

Bạn cũng có thể kết hợp các chức năng này để nhận được kết quả cần thiết dựa trên các đầu vào bạn có.

Sử dụng hàm VLOOKUP

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

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Ở đâu

lookup_value- là giá trị bạn muốn tra cứu. Lookup_value có thể là một giá trị hoặc một tham chiếu đến một ô. Lookup_value phải nằm trong cột đầu tiên của dải ô bạn chỉ định trong table_array
table_array- là phạm vi ô trong đó hàm VLOOKUP sẽ tìm kiếm giá trị lookup_value và giá trị trả về. table_array phải chứa
- lookup_value trong cột đầu tiên và
- giá trị trả về bạn muốn tìm
  
  Note- Cột đầu tiên chứa lookup_value có thể được sắp xếp theo thứ tự tăng dần hoặc không. Tuy nhiên, kết quả sẽ dựa trên thứ tự của cột này.
col_index_num- là số cột trong table_array chứa giá trị trả về. Các số bắt đầu bằng 1 cho cột ngoài cùng bên trái của mảng bảng
range_lookup- là một giá trị lôgic tùy chọn chỉ định xem bạn muốn hàm VLOOKUP tìm một kết hợp chính xác hay một kết hợp gần đúng. range_lookup có thể là
- bị bỏ qua, trong trường hợp đó nó được giả định là TRUE và hàm VLOOKUP cố gắng tìm một kết quả phù hợp gần đúng
- TRUE, trong trường hợp đó hàm VLOOKUP cố gắng tìm một kết quả gần đúng. Nói cách khác, nếu không tìm thấy kết quả khớp chính xác, giá trị lớn nhất tiếp theo nhỏ hơn lookup_value sẽ được trả về
- FALSE, trong trường hợp đó hàm VLOOKUP cố gắng tìm một kết quả khớp chính xác
- 1, trong trường hợp đó nó được giả định là TRUE và hàm VLOOKUP cố gắng tìm một kết quả phù hợp gần đúng
- 0, trong trường hợp đó nó được giả định là FALSE và hàm VLOOKUP cố gắng tìm một kết quả khớp chính xác

Note- Nếu range_lookup bị bỏ qua hoặc TRUE hoặc 1, thì hàm VLOOKUP chỉ hoạt động chính xác khi cột đầu tiên trong table_array được sắp xếp theo thứ tự tăng dần. Nếu không, nó có thể dẫn đến các giá trị không chính xác. Trong trường hợp này, hãy sử dụng FALSE cho range_lookup.

Sử dụng Hàm VLOOKUP với range_lookup TRUE

Xem xét danh sách các điểm của học sinh. Bạn có thể lấy các điểm tương ứng với hàm VLOOKUP từ một mảng chứa các khoảng đánh dấu và loại vượt qua.

table_array -

Lưu ý rằng cột đầu tiên đánh dấu dựa trên đó các điểm thu được được sắp xếp theo thứ tự tăng dần. Do đó, sử dụng TRUE cho đối số range_lookup, bạn có thể nhận được đối sánh gần đúng là những gì được yêu cầu.

Đặt tên mảng này là Grades.

Một phương pháp hay là đặt tên mảng theo cách này để bạn không cần nhớ các phạm vi ô. Bây giờ, bạn đã sẵn sàng để tra cứu điểm cho danh sách các điểm bạn có như sau:

Như bạn có thể quan sát,

col_index_num - cho biết cột của giá trị trả về trong table_array là 2
các range_lookup là đúng
- Cột đầu tiên chứa giá trị tra cứu trong các lớp table_array theo thứ tự tăng dần. Do đó, kết quả sẽ chính xác.
- Bạn cũng có thể nhận được giá trị trả về cho các kết quả phù hợp gần đúng. tức là hàm VLOOKUP tính toán như sau:

Điểm	Vượt qua loại
<35	Thất bại
> = 35 và <50	Lớp thứ ba
> = 50 và <60	Lớp thứ hai
> = 60 và <75	Lớp học đầu tiên
> = 75	Hạng nhất với sự khác biệt

Bạn sẽ nhận được kết quả sau:

Sử dụng Hàm VLOOKUP với range_lookup FALSE

Xem xét danh sách các sản phẩm có chứa ID sản phẩm và giá của từng sản phẩm. ID sản phẩm và giá sẽ được thêm vào cuối danh sách bất cứ khi nào sản phẩm mới được tung ra. Điều này có nghĩa là ID sản phẩm không cần theo thứ tự tăng dần. Danh sách sản phẩm có thể như hình dưới đây -

table_array -

Đặt tên mảng này là ProductInfo.

Bạn có thể nhận được giá của một sản phẩm được cung cấp ID sản phẩm bằng hàm VLOOKUP vì ID sản phẩm nằm trong cột đầu tiên. Giá ở cột 3 và do đó col_index_ num phải là 3.

Sử dụng Hàm VLOOKUP với range_lookup là TRUE
Sử dụng Hàm VLOOKUP với range_lookup là FALSE

Câu trả lời đúng là từ mảng ProductInfo là 171,65. Bạn có thể kiểm tra kết quả.

Bạn quan sát rằng bạn đã -

Kết quả đúng khi range_lookup là FALSE, và
Một kết quả sai khi range_lookup là TRUE.

Điều này là do, cột đầu tiên trong mảng ProductInfo không được sắp xếp theo thứ tự tăng dần. Do đó, hãy nhớ sử dụng FALSE bất cứ khi nào dữ liệu không được sắp xếp.

Sử dụng hàm HLOOKUP

Bạn có thể dùng HLOOKUP chức năng nếu dữ liệu nằm trong hàng chứ không phải cột.

Thí dụ

Hãy để chúng tôi lấy ví dụ về thông tin sản phẩm. Giả sử mảng trông như sau:

Đặt tên cho Array ProductRange này. Bạn có thể tìm giá của sản phẩm được cung cấp ID sản phẩm bằng chức năng HLOOKUP.

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

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

Ở đâu

lookup_value - là giá trị được tìm thấy trong hàng đầu tiên của bảng
table_array - là một bảng thông tin trong đó dữ liệu được tra cứu
row_index_num - là số hàng trong table_array mà từ đó giá trị khớp sẽ được trả về
range_lookup - là một giá trị lôgic chỉ định xem bạn muốn HLOOKUP tìm một kết hợp chính xác hay một kết hợp gần đúng
range_lookup có thể
- bị bỏ qua, trong trường hợp đó nó được giả định là TRUE và HLOOKUP cố gắng tìm một kết quả phù hợp gần đúng
- ĐÚNG, trong trường hợp này HLOOKUP cố gắng tìm một kết quả gần đúng. Nói cách khác, nếu không tìm thấy kết quả khớp chính xác, giá trị lớn nhất tiếp theo nhỏ hơn lookup_value sẽ được trả về
- FALSE, trong trường hợp đó HLOOKUP cố gắng tìm một kết quả phù hợp chính xác
- 1, trong trường hợp đó nó được giả định là TRUE và HLOOKUP cố gắng tìm một kết quả gần đúng
- 0, trong trường hợp đó nó được giả định là FALSE và HLOOKUP cố gắng tìm một kết quả phù hợp chính xác

Note- Nếu range_lookup bị Bỏ qua hoặc TRUE hoặc 1, thì HLOOKUP chỉ hoạt động chính xác khi cột đầu tiên trong table_array được sắp xếp theo thứ tự tăng dần. Nếu không, nó có thể dẫn đến các giá trị không chính xác. Trong trường hợp này, hãy sử dụng FALSE cho range_lookup.

Sử dụng Hàm HLOOKUP với range_lookup FALSE

Bạn có thể lấy giá của sản phẩm được cung cấp ID sản phẩm bằng chức năng HLOOKUP vì ID sản phẩm nằm ở hàng đầu tiên. Giá ở hàng 3 và do đó row_index_ num phải là 3.

Sử dụng Hàm HLOOKUP với range_lookup là TRUE.
Sử dụng Hàm HLOOKUP với range_lookup là FALSE.

Câu trả lời đúng từ mảng ProductRange là 171,65. Bạn có thể kiểm tra kết quả.

Bạn quan sát thấy rằng như trong trường hợp của hàm VLOOKUP, bạn có

Kết quả đúng khi range_lookup là FALSE, và
Một kết quả sai khi range_lookup là TRUE.

Điều này là do hàng đầu tiên trong mảng ProductRange không được sắp xếp theo thứ tự tăng dần. Do đó, hãy nhớ sử dụng FALSE bất cứ khi nào dữ liệu không được sắp xếp.

Sử dụng Hàm HLOOKUP với range_lookup TRUE

Hãy xem xét ví dụ về điểm sinh viên được sử dụng trong hàm VLOOKUP. Giả sử bạn có dữ liệu theo hàng thay vì cột như trong bảng dưới đây -

table_array -

Đặt tên mảng này là GradesRange.

Lưu ý rằng các đánh dấu hàng đầu tiên dựa trên điểm số thu được được sắp xếp theo thứ tự tăng dần. Do đó, sử dụng HLOOKUP với TRUE cho đối số range_lookup, bạn có thể nhận được Điểm với đối sánh gần đúng và đó là điều bắt buộc.

Như bạn có thể quan sát,

row_index_num - cho biết cột của giá trị trả về trong table_array là 2
các range_lookup là đúng
- Cột đầu tiên chứa giá trị tra cứu trong table_array Grades theo thứ tự tăng dần. Do đó, kết quả sẽ chính xác.
- Bạn cũng có thể nhận được giá trị trả về cho các kết quả phù hợp gần đúng. tức là HLOOKUP tính toán như sau:

Điểm	<35	> = 35 và <50	> = 50 và <60	> = 60 và <75	> = 75
Vượt qua loại	Thất bại	Lớp thứ ba	Lớp thứ hai	Lớp học đầu tiên	Hạng nhất với sự khác biệt

Bạn sẽ nhận được kết quả sau:

Sử dụng hàm INDEX

Khi bạn có một mảng dữ liệu, bạn có thể truy xuất một giá trị trong mảng bằng cách chỉ định số hàng và số cột của giá trị đó trong mảng.

Hãy xem xét dữ liệu bán hàng sau, trong đó bạn tìm thấy doanh số bán hàng ở từng khu vực Bắc, Nam, Đông và Tây bởi những người bán hàng được liệt kê.

Đặt tên mảng là SalesData.

Sử dụng Hàm INDEX, bạn có thể tìm thấy -

Doanh số của bất kỳ Nhân viên bán hàng nào trong một Khu vực nhất định.
Tổng doanh số bán hàng trong một khu vực của tất cả các nhân viên bán hàng.
Tổng doanh số của một nhân viên bán hàng trong tất cả các Khu vực.

Bạn sẽ nhận được kết quả sau:

Giả sử bạn không biết số hàng cho nhân viên bán hàng và số cột cho các khu vực. Sau đó, bạn cần phải tìm số hàng và số cột trước khi lấy giá trị bằng hàm chỉ mục.

Bạn có thể làm điều đó với hàm MATCH như được giải thích trong phần tiếp theo.