Cách dùng kết hợp hàm index và match trong excel để lọc thông tin | Nttworks.vn

Tin Học 0 lượt xem

Chức năng chỉ mục và chức năng đối ứng kết hợp với nhau mang lại nhiều thuận lợi trong quá trình học tập và làm việc. Đặc biệt, bạn có thể dễ dàng thay thế chức năng tìm kiếm vlookup và hlookup để tìm ra kết quả tốt nhất trong quá trình tìm kiếm giá trị theo hàng và cột. Cùng khám phá bài viết này để biết thêm thông tin nhé!

Tìm kiếm dữ liệu trong Excel là một công việc quen thuộc, đặc biệt là đối với những người theo học ngành khoa học máy tính hoặc kế toán. Thông thường, hàm vlookup được áp dụng để tìm dữ liệu theo cột và hàm hlookup tìm kiếm theo hàng để tìm dữ liệu mong muốn.

Tuy nhiên, phương pháp này chỉ được sử dụng để so sánh mã dòng và các giá trị tương ứng trong một bảng tra cứu duy nhất. Vì vậy, nếu bạn cần tìm các giá trị trong cả hàng và cột, bạn nên áp dụng một chỉ mục. Đặc biệt, khi hàm chỉ mục được kết hợp với hàm đối sánh, nó sẽ hiệu quả hơn hàm vlookup và hàm hlookup.

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 1
Lập chỉ mục và so khớp các hàm trong Excel

Lập chỉ mục và đối sánh các chức năng

Hàm chỉ mục trong Excel

chỉ số chức năng là một hàm tìm kiếm giá trị trong một ô cụ thể hoặc chỉ định một cột trong một hàng cụ thể để trả về một mảng Excel.

Hàm chỉ mục mảng cú pháp lệnh:

(Mảng, row_number,[Column_num])

Ở đó:

  • Mảng: Phạm vi ô hoặc hằng số mảng (bắt buộc)
  • Số dòng: Chọn một hàng mảng để trả về giá trị
  • Column_num: Chọn một cột mảng để trả về giá trị
  • Điều kiện phải có một trong hai đối số Số dòng cột_số.

Chức năng chỉ mục để tham khảo cú pháp lệnh:

INDEX (tham chiếu, line_number,[Column_num],[Area_num])

Ở đó:

  • Người giới thiệu: Khu vực tham chiếu (bắt buộc)
  • Số dòng: Số hàng bắt buộc để trả về tham chiếu (bắt buộc)
  • Column_num: Số cột để trả về tham chiếu (tùy chọn)
  • Area_num: Số ô có chứa giá trị tham chiếu. Nếu area_number bị bỏ qua, hàm chỉ mục sử dụng vùng 1 (tùy chọn).

Hàm đối sánh trong Excel

khớp chức năng là một trong những hàm chính của Excel hay nói dễ hiểu là hàm này trả về kết quả về vị trí tương đối của giá trị được tìm kiếm trong vùng. Tuy nhiên, tính năng này có thể làm được nhiều hơn thế.

Cú pháp lệnh: MATCH (search_value, search_array,[Match_type])

Ở đó:

  • Tìm kiếm_giá_trị: Giá trị tìm kiếm bao gồm: số, văn bản, boolean, số tham chiếu ô, văn bản, boolean
  • Search_array: Mảng tìm kiếm (Bắt buộc)
  • Loại so khớp: loại tìm kiếm (tùy chọn)

Có ba loại tìm kiếm chính:

  • 1: nhỏ hơn (
  • 0: đối sánh chính xác (giá trị tìm kiếm chính xác)
  • -1: lớn hơn (> giá trị tìm kiếm)

Sử dụng kết hợp chỉ mục và kết hợp trong Excel

Để hiểu rõ hơn về cách kết hợp hàm chỉ mục và hàm tương đương trong Excel, bạn có thể thực hiện Bài tập 1 như sau:

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 2
Sử dụng kết hợp các hàm chỉ mục và so khớp trong Excel

Từ bảng dữ kiện, bạn cần tìm ra thủ đô Seoul của nước nào. Công thức được sử dụng để tìm kết quả như sau: = INDEX (B1: B10, MATCH (“Linh hồn”, C1: c10.0)

Ở đó:

  • B1: B10 là cột chứa dữ liệu cần tra cứu và tìm kiếm
  • MATCH (“Soul”, C1: C10.0) là một điều kiện cho biết kết quả nằm ở đâu trong bảng dữ liệu. Kết quả cho Seoul được hiển thị ở hàng 6 của bảng tính. (Công thức cho INDEX sẽ là: INDEX (B1: B10.6)
  • Nếu bạn kết hợp 2 hàm trên trang tính này, Linh hồn nằm trên cùng một dòng với đất nước có thủ đô là Seoul Nam Triều Tiên.

Tóm lại là:

Cú pháp lệnh: = INDEX (Cột tìm kiếm giá trị, (MATCH (giá trị để tìm kiếm, cột chứa giá trị này, 0))

Bài tập triển khai hàm đối sánh chỉ mục

trong bài tập 2đối với bảng dữ liệu cho chức năng chỉ số và chức năng đối ứng, phải sử dụng 2 chức năng để điền vào cột đơn giá cho từng hàng hóa.

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 3
Chỉ mục hàm và khớp với bảng dữ liệu hàm

Nhập công thức lệnh vào hộp E6 = INDEX ($ B $ 15: $ F $ 19, TRẬN ĐẤU (A6, $ B $ 15: $ B $ 19,0), TRẬN ĐẤU (B6, $ B $ 15: $ F $ 15,0))

Kết quả được hiển thị như sau:

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 4
Nhập công thức cho hàm chỉ mục và khớp với hàm

XEM THÊM: cách sử dụng các tính năng tìm kiếm của Excel [HƯỚNG DẪN]

Lọc thông tin bằng Vlookup và hàm chỉ mục / đối sánh

Sử dụng hàm Vlookup

Khi cần tìm giá trị của nhiều điều kiện, người ta thường sử dụng tính năng vlookup để tìm kiếm ngay lập tức. Với cú pháp lệnh cụ thể: = VLOOKUP (Phát hiện giá trị, vùng dữ liệu [có: Cột dò tìm + Cột kết quả]Trả về thứ tự cột, Tìm kiếm chính xác / gần đúng)

Chúng tôi sử dụng công thức trên Bài tập 3 như sau:

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 5
Hàm mẫu vlookup để tìm kiếm nhiều điều kiện
  • “Huynh Van Le” là giá trị tìm kiếm, phạm vi cột A1: A8, chứa cột, luôn ở bên trái dữ liệu (A1: C8).
  • A1: C8 Phải khóa vùng dữ liệu được quét bằng cách nhấn phím F4 trước khi thực hiện thao tác tiếp theo.
  • Cột chứa giá trị trả về từ trái qua phải, cột chứa giá trị tìm kiếm.
  • Nhập 0, tương ứng với FALSE, trả về kết quả tìm kiếm chính xác.

Đây là công thức lệnh: = VLOOKUP (E2, $ A $ 1: $ C $ 8, 3, 0)

  • E2: giá trị tìm kiếm trong vùng dữ liệu A1: C8, dấu $ chỉ vùng dữ liệu tìm kiếm
  • Cột thứ ba là cột chứa dữ liệu trả về ở bên phải của vị trí giá trị được phát hiện. Kết quả là 0

Sử dụng các tính năng Chỉ mục và Đối sánh

Do đó, chỉ số / chức năng khớp cho thấy sự thay đổi của sự khác biệt:

Cú pháp lệnh cụ thể: = INDEX (Cột kết quả, MATCH (giá trị tìm kiếm, cột tìm kiếm chứa giá trị mong muốn, Tìm kiếm chính xác / gần đúng)

Tại đây bạn có thể xem hàm theo cú pháp sau: = INDEX (vùng kết quả, hàng, cột).

  • $ C $ 1: $ C $ 8: Phạm vi kết quả hoàn toàn khác khi sử dụng hàm vlookup. Ở đây, bạn chỉ chọn vùng dữ liệu cho mỗi điểm trong cột, không chọn toàn bộ cột có chứa giá trị phát hiện.
  • Chức năng đối sánh (giá trị phát hiện, phạm vi tìm kiếm, chính xác / tương đối). Tại giá trị tìm kiếm E3, vùng tìm kiếm hiển thị phạm vi chính xác có chứa giá trị tìm kiếm (A1: A8). Như với hàm vlookup, vùng tìm kiếm phải luôn cố định ($ A $ 1: $ A $ 8) để nhận chính xác 0 hoặc BÁN.
  • Áp dụng công thức trên, có thể thấy hàm match trả về giá trị 2 của kết quả tương ứng với hàng tìm kiếm từ trên xuống dưới. INDEX ($ C $ 1: $ C $ 8, 2) => Kết quả là 7.
Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 6
Ví dụ: hàm chỉ mục / đối sánh tìm kiếm nhiều điều kiện

XEM THÊM NGAY: hàm vlookup trong Excel, cách sử dụng chi tiết và các ví dụ cụ thể

Tại sao hàm index / fit lại tốt hơn hàm vlookup?

So sánh vlookup và lập chỉ mục / đối sánh

Vlookup là công thức một chiều

Vlookup sử dụng tìm kiếm dữ liệu từ trái sang phải. Trở lại Bài tập 1 được thực hiện trong phần trên, chúng ta không thể sử dụng hàm vlookup để tìm kiếm kết quả. Chức năng ứng dụng hiệu quả nhất là chức năng chỉ mục và chức năng bộ đếm để tìm kết quả này.

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 7
Bảng dữ liệu so sánh hàm vlookup và hàm chỉ mục / đối sánh trong Excel

Vlookup không chính xác khi bạn thêm hoặc xóa các cột khỏi bảng tính

Khi sử dụng hàm vlookup, bạn phải chỉ định cột mà bạn muốn áp dụng để lấy lại giá trị. Nếu thêm một cột vào giữa cột đầu tiên sẽ làm thay đổi giá trị, khiến hàm vlookup trả về kết quả không chính xác. Ngược lại, sử dụng chức năng chỉ mục và chức năng bộ đếm không cho kết quả hiển thị chính xác khi chỉnh sửa dữ liệu.

Vlookup gặp rất nhiều khó khăn với tìm kiếm hai chiều

Nếu chúng ta muốn tìm kiếm dân số của Seoul vào năm 2001, thực hiện hàm vlookup là rất khó, nhưng sử dụng hàm chỉ mục và hàm đối sánh thì nhanh hơn. Công thức lệnh như sau:

= INDEX (A1: F10, MATCH (“Soul”, C1: C10.0), MATCH (“2001”, A1: F1.0))

Ở đó:

  • khớp chức năng đầu tiên hiển thị trong bảng dữ liệu nơi đặt Seoul Hàn Quốc.
  • Một chức năng kết hợp khác Trong đó cột là dân số Seoul năm 2001.
  • chỉ số chức năng Sử dụng 2 thông tin và tìm ra chính xác thông tin bạn cần.

Bài tập so sánh thực tế

Quay lại biểu dữ liệu tại Bài tập 3, hàm vlookup yêu cầu cột chứa giá trị tìm kiếm phải ở ngoài cùng bên trái của dữ liệu. Nếu dữ liệu ở bên phải thì phải kết hợp chức năng lựa chọn để tìm ra kết quả tương ứng.

Cách sử dụng kết hợp chỉ mục Excel và các hàm khớp để lọc thông tin - Hình 8
Ví dụ ứng dụng của chức năng phát hiện giá trị

Cú pháp cho lệnh Vlookup với chức năng chọn chức năng: = SELECT ({1,2}, cột giá trị tìm kiếm, cột kết quả)

Cú pháp lệnh phổ biến: = VLOOKUP (Xem giá trị, CHOOSE ({1,2}, cột giá trị tìm kiếm, cột kết quả), cột trả về[2]Tìm chính xác [0])

Trong trường hợp này, dấu phân cách dấu phẩy là dấu chấm phẩy “;” thì công thức cho lệnh sẽ là: = VLOOKUP (Giá trị Tìm kiếm; CHOOSE ({1 2}; Cột Giá trị Tìm kiếm; Cột Kết quả); Cột Trả về[2]; Tìm kiếm chính xác [0])

Đối với hàm chỉ mục / đối sánh trong ví dụ trên, công thức lệnh như sau: = INDEX (khu vực kết quả, ANSWER (giá trị phát hiện, khu vực tìm kiếm, tìm kiếm chính xác)

Chúng tôi hy vọng bạn tìm thấy những chia sẻ tốt nhất trên đây để hiểu rõ hơn về cách sử dụng nó lập chỉ mục và chức năng đối sánh trong Excel. Đây là 2 chức năng phổ biến và thường gặp trong quá trình làm việc và học tập không thể bỏ qua. Đừng quên cập nhật kỹ năng tin học văn phòng của bạn trên website này. Chúc may mắn!

READ  Cách gộp cột trong Excel đơn giản, nhanh chóng bạn biết chưa? | Nttworks.vn
Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai.

Protected with IP Blacklist CloudIP Blacklist Cloud