[Excel] Sử dụng hàm INDEX dò tìm theo hàng và cột trong nghiệp vụ Excel kế toán

d_khoa58

New Member
Hội viên mới
Nếu như hàm VLOOKUP trong Excel giúp bạn dò tìm theo cột, hàm HLOOKUP dùng để dò tìm theo hàng thì hàm INDEX có khả năng tùy biến trong tìm kiếm giá trị theo cả hàng và cột trong một bảng, một mảng, một dãy số hay thậm chí là nhiều vùng giá trị khác nhau.

Chính vì thế, hàm INDEX được ứng dụng linh hoạt hơn trong Excel cho kế toán nhưng cũng rất thiết thực với những người làm trong lĩnh vực khác muốn làm quen với các hàm dò tìm. Điều khó ở hàm này là hiểu cách dùng để bạn vận dụng hàm đúng mục đích trong bảng tính Excel. Hàm INDEX có 2 cú pháp theo dạng mảng và tham chiếu.

1. Hàm INDEX kiểu mảng
Với dạng mảng, hàm INDEX cho kết quả là giá trị của một thành phần trong mảng hoặc một nhóm ô, được xác định bởi thứ tự hàng và cột.

Cú pháp: =INDEX(array,row_num,column_num)

Trong đó:

  • array là mảng, có thể là một nhóm ô tính trong Excel hoặc một hằng số mảng dạng {value1,value2,value3,…} với value là các giá trị ở dạng bất kỳ (số, chuỗi văn bản, ngày tháng, thời gian…), cách nhau bằng dấu phẩy (,) nếu các giá trị trên cùng 1 hàng và chấm phẩy (;) nếu các giá trị chuyển sang hàng kế tiếp. Bạn có thể tìm hiểu thêm về mảng dữ liệu và hằng số mảng trong các bài học Excel 2010
  • row_num là số thứ tự hàng, tính từ trên xuống dưới
  • column_num là số thứ tự cột, tính từ trái qua phải
Hàm INDEX dạng mảng cho kết quả là giá trị của thành phần nằm ở vị trí giao nhau giữa hàng thứ row_num và cột thứ column_num.

Nếu row_num bằng 0 thì hàm cho kết quả là mảng giá trị cho toàn bộ cột column_num của array. Nếu vị trí hàng của ô chứa hàm INDEX này trùng với một hàng trong array thì row_num mặc định chính là hàng đó. Trong ví dụ bên dưới, ô H5 có công thức hàm là =INDEX(C4:E10,0,3) có cùng hàng 2 với array nên row_num thay thế là 2, kết quả là ô thuộc hàng 2, cột 3 với đáp án 1,050,000.

Nếu column_num bằng 0 thì hàm cho kết quả là mảng giá trị cho toàn bộ hàng row_num của array. Nếu vị trí cột của ô chứa hàm INDEX này trùng với một cột trong array thì column_num mặc định chính là cột đó. Ví dụ: ô E14 có công thức hàm là =INDEX(C4:E10,7,0) có cùng cột 3 với array nên column_num tương ứng là 3, trả kết quả là ô thuộc hàng 7 cột 3 với đáp án là 1,100,000.

Vì kết quả một ô Excel bình thường không phải là mảng giá trị nên bạn sẽ thường thấy hiện #VALUE! khi đặt row_num hoặc column_num bằng 0. Bạn phải chọn phạm vi ô có kích thước tương ứng với mảng kết quả (là 1xcolumn_num hoặc row_numx1) sau đó nhập cùng một công thức hàm INDEX dạng mảng với giá trị row_num hoặc column_num bằng 0 tại tất cả các ô trong vùng và nhấn Ctrl + Shift + Enter để hiện kết quả mảng. Bạn có thể tham khảo ví dụ minh họa hoặc học thêm Excel nâng cao để dễ hiểu hơn về mảng dữ liệu. Cụ thể, trong ví dụ bên dưới, chọn liên tiếp 3 ô H6:J6 và nhấn F2 nhập cùng một công thức =INDEX(C4:E10,3,0), sau đó nhấn Ctrl + Shift + Enter để có kết quả như trong hình.

Nếu mảng chỉ có 1 cột hoặc 1 hàng thì có thể không nhập giá trị column_num hay row_num. Bạn có thể tham khảo ô H10 trong hình với row_num là 6 và column_num mặc định là 1 (không cần nhập).

http://*******/images/stories/ham-do-tim-index-dang-mang-trong-excel.png

2. Hàm INDEX kiểu tham chiếu
Cũng như hàm INDEX dạng mảng, hàm INDEX kiểu tham chiếu trả kết quả là tham chiếu của ô nằm tại vị trí hàng, cột trong vùng chỉ định. Với hàm INDEX dạng tham chiếu, bạn có thể chọn các phạm vi ô không liền kề và dò tìm trên các vùng khác nhau. Ngoài ra, các yếu tố còn lại khá giống hàm INDEX kiểu mảng.

Cú pháp: =INDEX(reference,row_num,column_num,area_num)

Trong đó:

  • reference là tham chiếu, có thể gồm một hoặc nhiều phạm vi ô ở những vị trí khác nhau, mỗi phạm vi ô tương ứng với một vùng, được đặt trong dấu ngoặc đơn và cách nhau bằng dấu phẩy. Ví dụ: (C4:E5,C8:E10) gồm 2 vùng, vùng 1 là C4:E5, vùng 2 là C8:E10
  • row_num và column_num tương tự như dạng mảng, là thứ tự hàng và cột để dò tìm dữ liệu
  • area_num là số thứ tự của vùng chọn để tham chiếu, khi bỏ trống thì mặc định là vùng 1 (area_num = 1)
Hàm INDEX dạng tham chiếu sẽ dựa trên area_num của reference và trả kết quả là ô giao nhau giữa hàng row_num và cột column_num. Nếu row_num hay column_num bằng 0 thì hàm trả về toàn bộ cột hay hàng tương ứng như ở dạng mảng. Giả sử ta bàn đến ô H12 có công thức =SUM(INDEX((C4:E5,C8:E10),0,3,2)) thì Excel sẽ dò tìm ô nằm ở vùng 2 (C8:E10), lấy tất cả cột 3 của phạm vi ô C8:E10, tức là E8:E10, sau đó dùng hàm SUM tính tổng các giá trị E8, E9, E10 và cho kết quả là 3,000,000.

http://*******/images/stories/ham-do-tim-index-dang-tham-chieu-trong-excel.png

Các ví dụ trong bài sẽ giúp bạn hiểu rõ hơn về hàm INDEX dạng mảng và tham chiếu. Trong thực tế, hàm INDEX sẽ dùng kết hợp với hàm MATCH và một số hàm khác để tra cứu và tổng hợp dữ liệu, thường gặp trong Excel cho tài chính, kế toán… Để tìm hiểu thêm các hàm Excel đa dạng cho người đi làm, bạn có thể đăng ký khóa học trực tuyến Excel từ A đến Z và áp dụng ngay nội dung bài học vào thực tế công việc một cách nhanh chóng.

******* (Tổng hợp)
 
Bạn có thể sử dụng hàm INDEX và MATCH cùng nhau để nhận kết quả t như khi sử dụng LOOKUP hoặc VLOOKUP.
(=INDETable_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num))
 

CẨM NANG KẾ TOÁN TRƯỞNG


Liên hệ: 090.6969.247

KÊNH YOUTUBE DKT

Kỹ thuật giải trình thanh tra BHXH

Đăng ký kênh nhé cả nhà

SÁCH QUYẾT TOÁN THUẾ


Liên hệ: 090.6969.247

Top