INDEX là một trong những hàm nâng cao giúp người dùng Google Sheet được thuận tiện hơn trong việc xử lý số liệu. Cùng xem hướng dẫn cách sử dụng hàm INDEX trong Google Sheet để trả về giá trị cực đơn giản, dễ hiểu nhé!
1. Hàm INDEX là gì? Ứng dụng của hàm INDEX trong Google Sheet
Hàm INDEX là gì?
Hàm INDEX là hàm trả về mảng, giúp lấy các giá trị tại một ô trong bảng.
Công thức hàm INDEX
=INDEX(Array;Row_num;[Column_num])
Trong đó:
+ Array: Là vùng ô hoặc một hàng số mảng nào đó bắt buộc.
+ Row_num: Là chọn hàng trong mảng từ đó trả về một giá trị.
+ Column_num: Là chọn cột trong mảng từ đó trả về một giá trị.
Ví dụ về hàm INDEX
Cho bảng sau gồm các trường Tên học sinh, Lớp, Điểm trung bình, Kết quả. Hãy dùng hàm INDEX để lấy ra giá trị “Nguyễn Thị B” trong bảng.
Cách thực hiện: Mình lấy giá trị hàng thứ 2 và cột thứ 1 trong bảng.
Công thức: =INDEX(A2:D7;2;1)
Ví dụ về hàm INDEX
Ứng dụng của hàm INDEX
+ Hàm INDEX dùng để lấy giá trị trong một bảng.
+ Kết hợp với các hàm khác để giúp người dùng có thể tính toán một cách nhanh chóng.
2. Cách sử dụng hàm INDEX trong Google Sheet
Các cách sử dụng cơ bản
Cho bảng sau:
Tên học sinh | Lớp | Điểm trung bình | Kết quả |
Nguyễn Văn A | 7A | 4,1 | Rớt |
Nguyễn Thị B | 7A | 8,6 | Đậu |
Lê Văn A | 7A | 5,7 | Đậu |
Trần Thị C | 7A | 3,2 | Rớt |
Hoàng Văn D | 7A | 9,1 | Đậu |
Lý Thị E | 7A | 7,5 | Đậu |
Bạn có thể thực hành hàm INDEX cơ bản như sau:
CÁCH SỬ DỤNG HÀM INDEX | |||||
Ý nghĩa công thức | Công thức | Kết quả | |||
Lấy kết quả hàng 2 cột 1 | =INDEX(A12:D18;2;1) | Nguyễn Văn A | |||
Lấy kết quả hàng 2 | =INDEX(A12:D18;2;0) | Nguyễn Văn A | 7A | 4,1 | Rớt |
Kết hợp với hàm MATCH
Vẫn bảng dữ liệu trên, hãy thực hiện kết hợp hàm INDEX và hàm MATCH như ví dụ dưới đây.
Công thức: =INDEX(A13:D18;MATCH(“Hoàng Văn D”;A13:A18;0);4)
Ý nghĩa công thức: Lấy giá trị kết quả của bạn tên là Hoàng Văn D.
Kết hợp INDEX với MATCH
Kết hợp với hàm VLOOKUP
Công thức: =VLOOKUP(INDEX(A13:D18;1;1);A12:D18;3;FALSE)
Ý nghĩa công thức: Lấy giá trị điểm trung bình của bạn đầu trong danh sách.
Kết hợp INDEX với VLOOKUP
Kết hợp với hàm IMPORTRANGE
Công thức: =INDEX(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1XdJPmZFgEX6zGevYxAZsyJ4_1cMUT9HY-ZsBhZAvgcQ/fdfdedit#gid=0″;”Trang tính1!A4:B9”);1;1)
Ý nghĩa công thức: Lấy số báo danh của bạn Nguyễn Văn A ở cột 1 hàng 1 trong trang tính Số báo danh.
Kết hợp INDEX với IMPORTRANGE
Kết hợp với hàm COUNTIF
Công thức: =SUMIF(B13:B18;INDEX(A13:D18;1;2);C13:C18)/COUNTIF(B13:B18;INDEX(A13:D18;1;2))
Ý nghĩa công thức: Tính điểm trung bình của các bạn ở lớp 7A.
Kết hợp INDEX với COUNTIF
Kết hợp với hàm SUM
Công thức: =SUM(INDEX(A13:D18;1;3);INDEX(A13:D18;2;3))
Ý nghĩa công thức: Tính tổng điểm của bạn ở hàng 1 và hàng 2.
Kết hợp INDEX với SUM
Kết hợp với hàm AVERAGE
Công thức: =AVERAGE(INDEX(A12:D18;2;3);INDEX(A12:D18;3;3))
Ý nghĩa công thức: Tính điểm trung bình của bạn ở ở vị trí thứ 1 và thứ 2.
Kết hợp INDEX với AVERAGE
Kết hợp hàm VLOOKUP, MATCH
Công thức: =VLOOKUP(index(A2:F9;match(G28;A2:A9;0);2);B2:F9;5;false)
Ý nghĩa công thức: Tính trung bình doanh số của nhân viên theo mã số 155.
Kết hợp nhiều điều kiện với INDEX
3. Các lưu ý khi sử dụng hàm INDEX trong Google Sheet
– Hàm INDEX chỉ mạnh khi kết hợp với hàm MATCH.
– Bạn có thể dùng hàm VLOOKUP khi giá trị tra cứu nằm ở bên trái của thuộc tính mong muốn trả về. Bạn có thể dùng INDEX và MATCH bất kể vị trí của giá trị tra cứu trong tập dữ liệu là ở đâu.
– Nếu bạn đặt hàng hoặc cột thành 0, INDEX sẽ trả lại mảng giá trị cho toàn bộ cột hoặc hàng tương ứng.
Lưu ý gì khi sử dụng hàm Index
4. Một số lỗi thường gặp khi dùng hàm INDEX
Lỗi #N/A
– Giải thích: Lỗi #N/A là lỗi không tìm được giá trị cần tìm.
– Cách khắc phục lỗi #N/A:
+ Kiểm tra lại các bảng dữ liệu đối chiếu trong các hàm tìm kiếm và sắp xếp dữ liệu so sánh trong bảng theo giá trị từ nhỏ đến lớn.
+ Đảm bảo dữ liệu tìm kiếm và đối chiếu phải cùng một kiểu định dạng dữ liệu đồng thời giá trị của dữ liệu tìm kiếm không được nhỏ hơn giá trị nhỏ nhất của dữ liệu đối chiếu.
+ Lồng các hàm tìm kiếm với hàm xử lý lỗi IFNA hoặc IFERROR để giá trị trả về không bị lỗi #N/A.
– Ví dụ sửa lỗi:
+ Dùng IFNA: Chúng ta sẽ dùng cú pháp =IFNA(index(A2:F9;match(B2;A2:F9;0);1;5);”bị lỗi NA”) để phát hiện lỗi.
Dùng IFNA phát hiện lỗi
+ Dùng IFERRON: Chúng ta sẽ dùng cú pháp =IFERROR(index(A2:F9;match(B2;A2:F9;0);1;5);”bị lỗi NA”) để phát hiện lỗi.
Dùng IFERRON nhận biết lỗi
Lỗi #ERROR!
– Giải thích: Lỗi do cú pháp bị sai.
– Cách khắc phục: Kiểm tra lại cú pháp.
– Ví dụ: Trong bài viết, khi bạn nhầm lẫn giữa dấu “;” và dấu “,” thì Google Sheet sẽ báo lỗi #ERROR!. Lúc này bạn cần sửa lại cú pháp thành dấu “;” sẽ không còn bị lỗi nữa.
Cách sửa lỗi #ERROR!
Lỗi #NUM!
– Giải thích: Lỗi #NUM! thường có trong hàm sau đây: Phép nhân lũy thừa, hàm căn bậc 2, hàm LOGARIT, hàm IRR, hàm lãi suất RATE, giá trị nằm ngoài vùng chọn.
– Cách khắc phục: Chú ý điều kiện sử dụng số (số nguyên, số âm hay số dương,…) và vùng chọn của giá trị.
– Ví dụ: Trong bảng ta thấy chỉ quét vùng chọn có 4 cột, nhưng trong công thức lại chọn cột 5, điều này đã dẫn tới lỗi #NUM!, lúc này ta sửa lại giá trị cột thành 3 thì sẽ hết lỗi.
Cách sửa lỗi #NUM!
5. Các bài tập sử dụng hàm INDEX
Cho bảng sau: Bảng gồm các trường dữ liệu: ID, TEN_NV, DOANH_SO. Dùng hàm INDEX cho 1 số ví dụ sau để hiểu rõ cách sử dụng cơ bản nhé!
ID | TEN_NV | DOANH_SO |
102 | An | 300 |
143 | Anh | 150 |
113 | Huệ | 600 |
165 | Nhi | 500 |
189 | Tố | 210 |
137 | Tuyết | 40 |
155 | Vy | 190 |
MS_122 | Xuân | 55 |
Câu hỏi 1: Ai là người đứng thứ 3 trong danh sách nhân viên kế bên?
Trả lời: =index(A2:C9;3;2)
Giải thích: Lấy người thứ 3 nên giá trị hàng sẽ là 3, giá trị cột là cột chứ tên là 2.
Hướng dẫn và kết quả câu hỏi 1
Câu hỏi 2: Doanh số của Tố là bao nhiêu?
Trả lời: =index(A2:C9;5;3)
Giải thích: Mình thấy Tố ở hàng thứ 5 và cột doanh số ở cột 3.
Hướng dẫn và kết quả câu hỏi 2
Câu hỏi 3: Tìm ID của nhân viên có tên Vy?
Trả lời: =index(A2:C9;7;1)
Giải thích: Mình sẽ lấy hàng chứa người tên Vy là hàng 7 và cột ID là cột 1.
Hướng dẫn và kết quả câu hỏi 3
Câu 4: Lấy ra tên của tất cả các nhân viên trong danh sách kế bên?
Trả lời: =index(B2:B9)
Giải thích: Lúc này mình sẽ lấy vùng giá trị từ B2 đến B9.
Hướng dẫn và kết quả câu hỏi 4
7. Câu hỏi thường gặp khi sử dụng hàm INDEX
Tại sao hàm INDEX và MATCH trong Google Sheets tốt hơn hàm VLOOKUP?
– Trả lời:
+ VLOOKUP là công thức 1 chiều. Ví dụ khi tìm các giá trị bên trái nhưng ô điều kiện bên phải thì ta không thể dùng hàm VLOOKUP để tìm ra giá trị được.
+ VLOOKUP sẽ bị sai nếu chúng ta thêm hoặc bớt cột ở trong bảng tính. Bởi khi dùng VLOOKUP mình sẽ phải chỉ ra cột cần lấy giá trị nên khi thêm bớt cột sẽ có thể dẫn đến số liệu bị sai, với INDEX sẽ được điều chỉnh theo nên hạn chế sai sót.
VLOOKUP hay INDEX và MATCH tốt hơn?
Hy vọng sau khi tham khảo bài viết, bạn sẽ sử dụng thuần thục hàm INDEX trong công việc. Cám ơn đã theo dõi, hẹn gặp lại ở những bài viết tiếp theo!