Thủ thuật

Hướng dẫn cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheet dễ dàng và nhanh chóng

Trương Nguyễn Anh Thư

09-05-2023, 8:51 pm

Việc sử dụng thông thạo các hàm trong Google Sheet giúp bạn xử lý các truy vấn và tính toán một cách nhanh chóng trên máy tính. Tuy nhiên, trong nhiều trường hợp bạn bắt buộc phải sử dụng hàm kết hợp. Vậy nên bài viết này sẽ hướng dẫn bạn cách kết hợp hàm INDEX và SUMIF trên Google Sheet một cách dễ hiểu nhất.

Hàm INDEX trong Google Sheet được áp dụng thế nào?

Hàm INDEX là hàm gì?

INDEX là một trong những hàm khá nâng cao giúp người dùng thuận tiện hơn trong việc xử lý số liệu qua Google Sheet. Cụ thể, đây là hàm giúp trả về mảng, giúp bạn lấy ra các giá trị tại một ô trong bảng.

Vậy hàm INDEX được sử dụng thế nào?

- Công thức của hàm INDEX:

=INDEX(Array;Row_num;[Column_num])

- Trong đó:

+ Array: Đây là một vùng ô hoặc một hàng số mảng nào đó bắt buộc phải có.

+ Row_num: Là việc chọn ra hàng trong mảng từ đó kết quả trả về là một giá trị.

+ Column_num: Là việc chọn cột trong mảng từ đó kết quả trả về là một giá trị.

- Ví dụ minh họa: Hãy lấy về số tiền phòng có mã số là L1B.

=INDEX(A3:H12;5;6)

Lấy giá trị ô tính bằng hàm INDEX như hình trên

Hàm SUMIF trong Google Sheet được sử dụng ra sao?

SUMIF là hàm gì?

Hàm SUMIF có thể được hiểu là sự kết hợp giữa hai hàm là SUM và IF. Hàm này sẽ trả về kết quả là tổng của những dữ liệu phù hợp với một điều kiện nhất định, bằng cách quét qua tất cả các giá trị có trong bảng dữ liệu đã chọn.

Hàm SUMIF được sử dụng như thế nào?

- Công thức của hàm:

=SUMIF(range;condition;sum_range)

- Trong đó:

+ Range : Biểu thị phạm vi mà hàm được kiểm tra điều kiện.

+ Condition : Xác định ra điều kiện của các ô sẽ được tính tổng. Điều kiện này có thể là một giá trị như: số, văn bản, ngày tháng hoặc một tham chiếu đến một ô có chứa tiêu chí.

+ Sum_range: Đây là một dải ô được dùng để tính tổng. Nếu sum_range được viết vào công thức, thì đó sẽ là phạm vi ô có chứa các giá trị được tính tổng vào, nếu số tương ứng của nó nằm trong phạm vi được dùng để xét điều kiện đúng với điều kiện đã đề ra. Ngược lại, nếu sum_range không được viết thì nó được giả định rằng phạm vi xem xét các điều kiện range cũng là sum_range.

Ví dụ minh họa như sau: Bạn hãy tính tổng những mã số L1A.

=SUMIF(B3:B12;"L1A";H3:H12)

Tính tổng dãy bằng hàm SUMIF như hình trên

Các cách kết hợp hai hàm INDEX và SUMIF

- Công thức áp dụng của hàm kết hợp

=SUMIF(range;condition;INDEX(Array;Row_num;[Column_num]))

Trong đó:

+ Range : Chỉ ra phạm vi sẽ được kiểm tra điều kiện.

+ Condition : Biểu thị điều kiện xác định các ô sẽ được tính tổng. Điều kiện này có thể là một giá trị như: số, văn bản, ngày tháng hoặc một tham chiếu đến một ô có chứa tiêu chí.

+ Array: Đây là vùng ô hoặc một hàng số mảng nào đó bắt buộc phải có.

+ Row_num: Là chọn hàng trong mảng từ đó kết quả được trả về sẽ là một giá trị.

+ Column_num: Là chọn cột trong mảng từ đó kết quả được trả về sẽ là một giá trị.

Ví dụ minh họa như sau: Bạn hãy tính tổng doanh thu dựa theo loại phòng.

Công thức cần nhập:

=SUMIF($B$3:$B$12;B17;INDEX($H$3:$H$12;))

Tính doanh thu theo loại phòng như hình trên

Các lỗi thường gặp phải khi kết hợp hàm INDEX và hàm SUMIF

Lỗi #N/A

Lỗi #N/A có nghĩa là trong công thức bạn sử dụng có chứa nội dung không có sẵn trong vùng dữ liệu của bạn, dẫn tới hàm không thể tiến hành tính toán, hoàn thành công thức được. Lỗi này thường xảy ra khi bạn sử dụng đến các hàm dò tìm, tham chiếu. Hay đối tượng mà bạn cần dò tìm, tham chiếu không có sẵn trong vùng dữ liệu cần tra cứu nên hàm sẽ báo lỗi #N/A.

Lỗi #N/A không phải là lỗi sai hàm hay sai công thức mà chỉ là lỗi mà hàm không tìm thấy đối tượng cần tìm. Chính vì vậy trong nhiều trường hợp chúng ta chỉ cần ẩn lỗi này đi trước, biện luận trước những trường hợp dữ liệu không tồn tại trước khi tiến hành thực hiện các hàm dò tìm, tham chiếu.

Lỗi #REF

Lỗi #REF! là lỗi hiển thị khi công thức tham chiếu đến ô không hợp lệ. Điều này thường xuyên xảy ra nhất là khi các ô được công thức tham chiếu đến bị xóa hoặc bị dán đè lên.

Để khắc phục lỗi này, bạn cần điều chỉnh lại công thức để công thức sử dụng tham chiếu dải ô thay vì ô riêng lẻ.

Lỗi #ERROR

Lỗi #ERROR này có nghĩa là do Google Sheet không thể hiểu được công thức bạn đã nhập vì nó không thể phân tích cú pháp công thức để thực thi việc tính toán.

Lỗi #VALUE

Lỗi #VALUE! hiển thị là cách để Excel thông báo: “Đã có lỗi xảy ra với công thức bạn vừa nhập” hoặc “ ô bạn đang tham chiếu tới đã xảy ra lỗi”. Lỗi này thông báo rất chung chung và khó có thể tìm thấy nguyên nhân chính xác cho lỗi nên rất khó để xác định.

Một số lưu ý quan trọng khi kết hợp hàm INDEX và hàm SUMIF

Khi sử dụng hàm kết hợp INDEX và SUMIF thì bạn phải lưu ý đến các dấu đóng ngoặc ), dấu nháy kép " " và dấu nháy đơn ' ' đối với việc truy vấn dữ liệu dạng text. Nếu bạn không cẩn thận sẽ dẫn đến việc lỗi hàm.

Khi tiến hành truy vấn dữ liệu bạn nên lưu ý đến khoảng cách, nếu bạn viết sát nhau thì sẽ dẫn đến hàm bị lỗi.

Truy vấn các dữ liệu có dạng là text bị bắt buộc phải có dấu nháy đơn ' ', còn dữ liệu là số thì không cần phải có dấu nháy.

Bài tập ví dụ về việc kết hợp hàm INDEX và hàm SUMIF

Đề bài: Bạn hãy tính tổng doanh thu của việc đặt phòng theo từng ngày.

Giải thích: Tiến hành tính doanh thu các dòng đặt hàng có cùng một ngày đặt phòng bằng hàm SUMIF với điều kiện là có ngày đặt trùng với ngày cho trước.

- Công thức của hàm:

=SUMIF($C$3:$C$12;C16;INDEX($H$3:$H$12;))

- Trong đó:

+ $C$3:$C$12 : Chỉ ra phạm vi vùng ô tính được dùng để kiểm tra điều kiện.

+ C16 : Chỉ ra địa chỉ có chứa giá trị điều kiện.

+ $H$3:$H$12: Đây là vùng ô tính được tính tổng sau khi đã xem xét điều kiện.

Tính tổng doanh thu của đặt phòng theo ngày như hình trên

Những câu hỏi thường gặp phải khi tiến hành kết hợp hàm INDEX và hàm SUMIF

Kết hợp hàm INDEX và SUMIF có lợi ích gì ?

Hàm INDEX được dùng để trả về kết quả là giá trị của một ô tính hoặc là một dãy ô tính nào đó, hàm SUMIF thì được dùng để tính tổng một hoặc một dãy ô tính với một điều kiện đã được cho trước.

Trong nhiều trường hợp, ta chỉ cần sử dụng có một hàm thì có thể không đạt được hiệu quả mong muốn. Việc kết hợp hai hàm này có thể giúp ta tiến hành việc tính toán một cách nhanh chóng và chính xác hơn.

Hàm SUMIF còn có thể được kết hợp với các hàm nào khác không?

Hàm SUMIF là một hàm được dùng để tính tổng giá trị có điều kiện, với đặc tính có nhiều đối số và dễ dàng sử dụng, hàm SUMIF còn có thể kết hợp với các hàm khác nữa như hàm VLOOKUP, HLOOKUP, QUERY,TODAY,...

Cảm ơn bạn đã theo dõi bài viết này của chúng tôi đến phần cuối cùng. Hy vọng bài viết sẽ mang lại cho bạn những thông tin hữu ích.