Cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets

Kết hợp hàm INDEX và hàm SUMIF trong Google Sheets giúp bạn tính tổng dữ liệu linh hoạt theo cột động và điều kiện cụ thể. Nếu bạn là người mới và chưa hiểu rõ cách hoạt động của hai hàm này, bài viết dưới đây sẽ hướng dẫn chi tiết, hãy cùng tham khảo ngay nhé. 

Tổng quan chung về hàm INDEX trong Google Sheets

Khi làm việc với dữ liệu trong Google Sheets, bạn thường xuyên cần truy xuất một giá trị cụ thể nằm trong một bảng gồm nhiều hàng và nhiều cột. Hàm INDEX trong Google Sheets sẽ giúp bạn thực hiện điều đó một cách chính xác và linh hoạt.

Hàm INDEX thuộc nhóm hàm tra cứu và tham chiếu. Hàm này cho phép người dùng lấy giá trị giao điểm của một hàng và một cột trong một vùng dữ liệu xác định. Không giống như hàm VLOOKUP chỉ dò tìm theo chiều dọc từ trái sang phải. Hàm INDEX có thể truy xuất dữ liệu ở bất kỳ vị trí nào trong bảng, bạn cần phải cung cấp đúng số thứ tự hàng và cột. 

Điểm mạnh nổi bật của INDEX nằm ở khả năng:

• Lấy giá trị theo vị trí hàng và cột cụ thể
• Hiển thị đầy đủ dữ liệu của một hàng/cột
• Kết hợp với MATCH để dò tìm vị trí động
• Tạo vùng tham chiếu linh hoạt cho các hàm tính toán khác

Cú pháp sử dụng hàm INDEX

Trước khi áp dụng INDEX vào các công thức nâng cao, bạn cần hiểu rõ cấu trúc của hàm như sau:

=INDEX(reference, row, [column])

Trong đó:

• reference (vùng dữ liệu): Đây là phạm vi ô chứa dữ liệu mà bạn muốn truy xuất. Ví dụ: A2:D10.
• row (số hàng): Đây là số thứ tự hàng trong vùng dữ liệu, tính từ trên xuống.
• column (số cột): Đây là số thứ tự cột trong vùng dữ liệu, tính từ trái sang phải. Tham số này là tùy chọn nếu vùng dữ liệu chỉ có một cột.

Hướng dẫn dùng INDEX qua ví dụ

Giả sử bạn có bảng doanh thu sản phẩm theo từng tháng như sau:

*Yêu cầu:  Lấy doanh thu Tháng 2 của sản phẩm Gemini AI

Công thức:

=INDEX(B2:D4; 2; 2)

Trong đó:

• B2:D4 là vùng dữ liệu chỉ chứa số liệu doanh thu.
• Số 2 đầu tiên đại diện cho hàng thứ 2 trong vùng (tức là sản phẩm Gemini AI).
• Số 2 thứ hai đại diện cho cột thứ 2 trong vùng (tức là Tháng 2).

Kết quả:

Tổng quan chung về hàm SUMIF trong Google Sheets

 Trong quá trình làm việc với dữ liệu bán hàng, kế toán hoặc báo cáo doanh thu, bạn thường xuyên cần tính tổng theo một điều kiện cụ thể. Hàm SUMIF trong Google Sheets được thiết kế để xử lý chính xác nhu cầu này.

Hàm SUMIF cho phép cộng các giá trị trong một vùng dữ liệu khi các ô tương ứng thỏa mãn điều kiện xác định trước. Thay vì cộng toàn bộ dữ liệu như hàm SUM thông thường, SUMIF sẽ lọc dữ liệu trước, sau đó chỉ cộng những giá trị phù hợp. Hàm SUMIF thường được sử dụng nhằm mục đích:

• Tính tổng doanh thu cho từng đơn vị sản phẩm
• Tính tổng chi phí theo từng phòng ban
• Tính tổng số lượng theo từng trạng thái đơn hàng
• Phân tích dữ liệu nhanh chóng theo tiêu chí cụ thể

Cú pháp sử dụng hàm SUMIF

Để sử dụng hiệu quả, bạn cần nắm rõ cấu trúc của hàm SUMIF và vai trò của từng tham số.

=SUMIF(range, criterion, [sum_range])

Trong đó:

• range (vùng điều kiện): Đây là dải ô chứa dữ liệu dùng để kiểm tra điều kiện.
• criterion (điều kiện): Đây là tiêu chí dùng để lọc dữ liệu. Điều kiện có thể là số, văn bản hoặc biểu thức so sánh.
• sum_range (vùng tính tổng): Đây là dải ô chứa các giá trị thực tế sẽ được cộng lại nếu thỏa điều kiện.

Hướng dẫn dùng SUMIF qua ví dụ

Ví dụ, bạn có bảng doanh thu như sau:

*Yêu cầu: Tính tổng doanh thu của sản phẩm Google Workspace

Công thức:

=SUMIF(A2:A4; “Google Workspace”; B2:B4)

Trong đó:

• A2:A4 là vùng điều kiện. Google Sheets sẽ kiểm tra từng ô trong vùng này.
• Điều kiện là “Google Workspace” nghĩa là chỉ chọn những hàng có sản phẩm Google Workspace.
• B2:B4 là vùng tính tổng. Nếu ô ở cột A thỏa điều kiện, thì giá trị tương ứng ở cột B sẽ được cộng vào tổng.

Kết quả:

Tại sao nên kết hợp hàm INDEX và hàm SUMIF trong Google Sheets

Khi làm việc với báo cáo doanh thu, chi phí hoặc dữ liệu theo nhiều tháng, việc chỉ sử dụng riêng lẻ từng hàm là chưa đủ linh hoạt. Hàm SUMIF giúp bạn tính tổng theo điều kiện, còn INDEX giúp bạn truy xuất dữ liệu theo vị trí. Tuy nhiên, khi bạn kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, bạn sẽ tạo ra một hệ thống tính toán động và giúp giải quyết nhiều vấn đề, như là:

• Tạo vùng tính tổng động theo từng cột

Trong các bảng báo cáo theo tháng, mỗi tháng thường nằm ở một cột khác nhau. Nếu bạn muốn tính tổng doanh thu của sản phẩm trong các tháng, sẽ phải thay đổi vùng tính tổng tương ứng. 

Nhưng khi bạn kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, hàm INDEX sẽ giúp bạn chọn đúng cột cần tính tổng dựa trên một điều kiện đầu vào, ví dụ như một ô dropdown chọn tháng. Hàm SUMIF sau đó sẽ thực hiện phép tính tổng theo điều kiện trên cột mà hàm INDEX trả về. Nhờ đó, bạn chỉ cần một công thức duy nhất, nhưng có thể áp dụng cho nhiều cột khác nhau.

• Tăng tính linh hoạt cho báo cáo sử dụng dropdown

Trong các báo cáo chuyên nghiệp, người dùng thường muốn chọn tháng hoặc khu vực từ một danh sách thả xuống. Nếu bạn sử dụng hàm SUMIF đơn thuần, công thức của bạn sẽ bị cố định vào một cột cụ thể. 

Khi áp dụng cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, bạn có thể tạo một ô chọn tháng. Sau đó, dùng hàm INDEX để xác định cột tương ứng với tháng được chọn và dùng SUMIF để tính tổng theo điều kiện trên cột đó. 

• Giảm số lượng công thức và hạn chế sai sót

Nếu bạn có 12 tháng dữ liệu và mỗi tháng cần một công thức SUMIF riêng, sẽ phải tạo 12 công thức khác nhau. Khi dữ liệu thay đổi cấu trúc, bạn phải chỉnh sửa từng công thức một cách thủ công. 

Việc kết hợp hàm INDEX và hàm SUMIF trong Google Sheets giúp bạn gom tất cả vào một công thức duy nhất. Khi cấu trúc bảng thay đổi hoặc khi bạn thêm cột mới, bạn chỉ cần điều chỉnh một lần thay vì sửa nhiều nơi.

• Nâng cao hiệu suất khi xử lý dữ liệu khối lượng lớn

Trong các bảng tính có hàng nghìn dòng dữ liệu, việc sử dụng nhiều công thức lặp lại có thể khiến file chạy chậm. Một số người dùng lựa chọn OFFSET để tạo vùng động, nhưng OFFSET là hàm biến đổi liên tục, có thể ảnh hưởng đến hiệu suất.

Hàm INDEX là một hàm ổn định và ít tiêu tốn tài nguyên hơn. Khi bạn áp dụng đúng cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, bạn có thể tạo vùng động mà vẫn giữ được hiệu suất mượt mà cho bảng tính.

Khi nào nên kết hợp hàm INDEX và hàm SUMIF trong Google Sheets

Việc kết hợp nhiều hàm trong Google Sheets không phải lúc nào cũng cần thiết và việc kết hợp hàm INDEX và hàm SUMIF cũng thế. Dưới đây là những trường hợp cụ thể mà bạn nên sử dụng cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets.

• Khi cần thay đổi cột tính tổng thường xuyên

Trong thực tế, bảng dữ liệu doanh thu thường được sắp xếp theo nhiều cột tháng hoặc nhiều khu vực khác nhau. Nếu bạn chỉ sử dụng hàm SUMIF thông thường, phải cố định vùng tính tổng vào một cột cụ thể. 

Nhưng khi bạn kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, bạn có thể xác định cột cần tính tổng dựa trên tiêu đề tháng. Hàm INDEX để trả về đúng cột tương ứng, hàm SUMIF để tính tổng theo điều kiện trên cột đó. Nhờ đó, bạn chỉ cần một công thức duy nhất cho nhiều cột khác nhau.

• Khi tạo báo cáo hoặc Dashboard có chọn tháng

Trong các báo cáo chuyên nghiệp, người dùng thường chọn tháng hoặc quý từ một menu thả xuống. Khi giá trị trong ô chọn thay đổi, kết quả phải tự động cập nhật. Nếu bạn chỉ dùng hàm SUMIF cố định, công thức sẽ không thay đổi theo lựa chọn của người dùng. Khi áp dụng cách kết hợp hàm INDEX và hàm SUMIF, bạn có thể dùng hàm MATCH để xác định vị trí cột của tháng được chọn và dùng INDEX để trả về toàn bộ cột tháng tương ứng, dùng SUMIF để tính tổng theo điều kiện trên cột đó. 

• Khi cấu trúc dữ liệu thay đổi

Trong quá trình làm việc, bạn có thể chèn thêm cột hoặc thay đổi vị trí các cột dữ liệu. Nếu công thức hàm SUMIF đang cố định vào một cột cụ thể, việc thay đổi cấu trúc sẽ làm công thức bị sai. Khi kết hợp hàm INDEX và hàm SUMIF, bạn có thể dùng hàm INDEX kết hợp với hàm MATCH để xác định vị trí cột dựa trên tiêu đề thay vì vị trí cố định. Điều này sẽ giúp công thức không bị lỗi khi chèn thêm cột và hạn chế việc sửa lại công thức thủ công.

• Khi thực hiện phân tích dữ liệu đa khía cạnh

Nếu bảng dữ liệu có 12 cột tháng hoặc nhiều cột khu vực, việc viết 12 công thức hàm SUMIF riêng biệt sẽ khiến file trở nên rối và khó kiểm soát. Thay vì viết nhiều công thức khác nhau, bạn chỉ cần một công thức duy nhất, kết hợp hàm INDEX để chọn cột động và dùng SUMIF để tính tổng theo điều kiện. 

• Khi tối ưu file có nhiều sheet và dữ liệu lớn

Trong các file có nhiều Sheet, việc lặp lại nhiều công thức tương tự nhau sẽ làm file nặng và khó quản lý. Khi kết hợp hàm INDEX và hàm SUMIF, bạn sẽ giảm số công thức trùng lặp, dễ dàng kiểm soát và bảo trì file,… Hàm INDEX sẽ giúp bạn chọn đúng vùng dữ liệu cần tính tổng mà không cần tạo nhiều vùng trung gian. 

Cú pháp kết hợp hàm INDEX và hàm SUMIF trong Google Sheets

Khi bạn muốn tính tổng theo điều kiện nhưng lại cần thay đổi cột dữ liệu một cách linh hoạt, bạn nên sử dụng kết hợp hàm INDEX và hàm SUMIF trong Google Sheets. Cú pháp cơ bản như sau:

=SUMIF(dãy_điều_kiện, điều_kiện, INDEX(mảng_dữ_liệu, 0, số_cột))

Trong đó:

• dãy_điều_kiện: Là vùng dữ liệu chứa tiêu chí để so sánh (ví dụ: tên cửa hàng, tên sản phẩm).
• điều_kiện: Là giá trị dùng để lọc dữ liệu (có thể nhập trực tiếp hoặc tham chiếu tới một ô).
• mảng_dữ_liệu: Là vùng chứa các cột số liệu cần tính tổng (ví dụ: doanh thu theo từng quý).
• 0 (tham số hàng của INDEX): Giá trị 0 có nghĩa là lấy toàn bộ các hàng trong cột được chọn.
• số_cột: Là số thứ tự cột mà bạn muốn tính tổng. Tham số này có thể cố định hoặc tham chiếu tới một ô để tạo vùng động.

Cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets| Ví dụ cơ bản

Để bạn dễ hình dung hơn, có thể tham khảo ví dụ cụ thể về cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets như sau:

Giả sử bạn có bảng doanh thu của các cửa hàng theo từng quý. Bạn muốn tạo một công thức linh hoạt để:

• Chọn tên cửa hàng cần tính tổng
• Chọn quý cần tính
• Công thức tự động trả về tổng doanh thu tương ứng

*Yêu cầu: Tính tổng doanh thu cửa hàng A trong Qúy 2

Để công thức hoạt động linh hoạt, bạn tạo hai ô điều kiện:

• Ô F1: Nhập tên cửa hàng cần tính (ví dụ: Cửa hàng A)
• Ô F2: Nhập số thứ tự quý cần tính (ví dụ: 2 tương ứng với Quý 2)

Công thức:

=SUMIF(A2:A5;F1; INDEX(B2:D5; 0; F2))

Trong đó:

• A2:A5: Là vùng điều kiện. Hàm SUMIF sẽ kiểm tra từng ô trong vùng này để tìm cửa hàng trùng với F1.
• F1: Là điều kiện tính tổng. Ví dụ, nếu F1 là “Cửa hàng A”, hàm sẽ chỉ lấy các dòng có tên này.
• INDEX(B2:D5, 0, F2): Đây là phần quan trọng nhất.

Kết quả:

Lỗi và cách khắc phục khi kết hợp hàm INDEX và hàm SUMIF trong Google Sheets

Khi bạn bắt đầu áp dụng kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, việc xuất hiện lỗi là điều hoàn toàn bình thường. Dưới đây là những lỗi phổ biến khi thực hiện cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets, kèm theo nguyên nhân và hướng xử lý cụ thể.

Lỗi #N/A

Lỗi #N/A có nghĩa là công thức không tìm thấy dữ liệu cần tra cứu trong vùng được chỉ định. Lỗi #N/A này thường xuất hiện khi:

• Điều kiện trong hàm SUMIF không tồn tại trong dãy điều kiện.
• Hàm INDEX kết hợp với hàm MATCH trả về vị trí cột không hợp lệ.
• Giá trị trong ô điều kiện bị sai chính tả hoặc có khoảng trắng thừa.

Cách khắc phục lỗi #N/A khi kết hợp hàm INDEX và hàm SUMIF trong Google Sheets như sau:

• Kiểm tra lỗi chính tả và khoảng trắng trong dữ liệu.
• Kiểm tra lại giá trị trong ô chọn tháng hoặc cột khi dùng INDEX.
• Dùng IFERROR để ẩn lỗi nếu dữ liệu có thể không tồn tại.

Lỗi #REF

Lỗi #REF! xảy ra khi công thức tham chiếu đến một ô hoặc một vùng không còn tồn tại. Lỗi #REF này thường gặp khi kết hợp hàm INDEX và hàm SUMIF là vì:

• Bạn xóa cột hoặc hàng mà công thức đang tham chiếu.
• Tham số số_cột trong hàm INDEX vượt quá số cột thực tế.
• Bạn di chuyển hoặc dán đè dữ liệu làm mất vùng tham chiếu ban đầu.

Cách khắc phục lỗi #REF khi kết hợp hàm INDEX và hàm SUMIF trong Google Sheets như sau:

• Kiểm tra số cột thực tế trong vùng hàm INDEX.
• Đảm bảo giá trị trong ô chọn cột không vượt quá giới hạn.
• Tránh xóa cột đang được tham chiếu.
• Sử dụng hàm MATCH để xác định vị trí cột thay vì nhập số thủ công.

Lỗi #ERROR

Lỗi #ERROR! trong Google Sheets thường xuất hiện khi công thức có lỗi cú pháp. Điều này có thể xảy ra khi kết hợp hàm INDEX và hàm SUMIF từ lý do sau:

• Bạn thiếu dấu ngoặc đóng hoặc mở.
• Bạn nhập sai dấu phân cách tham số (dấu phẩy hoặc dấu chấm phẩy tùy ngôn ngữ).
• Bạn đặt sai thứ tự tham số trong hàm.

Cách khắc phục lỗi #ERROR khi kết hợp hàm INDEX và hàm SUMIF trong Google Sheets như sau:

• Kiểm tra kỹ dấu ngoặc trong công thức.
• Kiểm tra dấu phân cách tham số phù hợp với cài đặt ngôn ngữ.
• Nhập lại công thức theo đúng cú pháp chuẩn.

Lỗi #VALUE

Lỗi #VALUE! xảy ra khi dữ liệu không đúng định dạng mà công thức yêu cầu. Lỗi #VALUE này thường xuất hiện khi kết hợp hàm INDEX và hàm SUMIF là do:

• Vùng tính tổng chứa dữ liệu văn bản thay vì số.
• Ô chọn cột (F2) không phải là số.
• SUMIF đang so sánh dữ liệu khác kiểu (ví dụ: số với văn bản)

Cách khắc phục lỗi #VALUE khi kết hợp hàm INDEX và hàm SUMIF trong Google Sheets như sau:

• Đảm bảo vùng tính tổng chỉ chứa số.
• Kiểm tra định dạng ô chọn cột là số.
• Dùng hàm VALUE để chuyển đổi dữ liệu nếu cần.
• Kiểm tra xem điều kiện có đúng kiểu dữ liệu với vùng điều kiện hay không.

Lưu ý quan trọng khi kết hợp hàm INDEX và hàm SUMIF trong Google Sheets

Dưới đây là những lưu ý quan trọng mà bạn nên ghi nhớ khi thực hiện cách kết hợp hàm INDEX và hàm SUMIF trong Google Sheets.

• Thứ nhất, kiểm tra kỹ dấu ngoặc và dấu nháy khi viết công thức

Hai hàm INDEX và SUMIF đều có nhiều tham số, vì vậy bạn cần đặc biệt chú ý dấu ngoặc mở và đóng phải đầy đủ. Dấu phân cách tham số phải đúng (dấu phẩy hoặc dấu chấm phẩy tùy cài đặt ngôn ngữ). Dữ liệu dạng text phải đặt trong dấu nháy kép ” “. Trong một số trường hợp đặc biệt khi truy vấn hoặc xử lý chuỗi, bạn cần dùng đúng dấu nháy đơn ‘ ‘.

• Thứ hai, đảm bảo kích thước vùng điều kiện và vùng tính tổng bằng nhau

Dãy_điều_kiện và vùng mà INDEX trả về phải có cùng số hàng. Nếu hai vùng không đồng bộ, kết quả tính tổng có thể sai hoặc phát sinh lỗi.

• Thứ ba, đồng bộ định dạng dữ liệu

Nếu cột điều kiện là Text nhưng ô nhập điều kiện lại ở định dạng khác, công thức có thể không trả về kết quả đúng. Nếu bạn làm việc với ngày tháng, hãy đảm bảo tất cả đều ở định dạng Date.

Bạn có thể dùng các hàm như VALUE hoặc DATEVALUE để chuyển đổi định dạng khi cần. Khi định dạng dữ liệu không đồng nhất, việc kết hợp hàm INDEX và hàm SUMIF trong Google Sheets sẽ không cho kết quả chính xác dù công thức đúng cú pháp.

• Thứ tư, tránh tham chiếu toàn bộ cột

Mặc dù, cách này tiện lợi nhưng có thể làm file chậm nếu dữ liệu lớn. Bạn nên giới hạn phạm vi cụ thể như A2:A1000 thay vì tham chiếu toàn bộ cột. Khi dữ liệu tăng lên hàng nghìn dòng, tối ưu phạm vi tham chiếu sẽ giúp bảng tính chạy mượt hơn.

FAQ – Câu hỏi thường gặp

Dưới đây là một số câu hỏi thường gặp mà người dùng hay thắc mắc khi kết hợp hai hàm INDEX và SUMIF vào sử dụng:

1 – Có thể thay hàm SUMIF bằng hàm SUMIFS để kết hợp hàm INDEX không?

Có. Có thể dùng SUMIFS để cộng dữ liệu theo nhiều điều kiện đồng thời. Khi kết hợp với INDEX, bạn có thể tạo ra một báo cáo cực kỳ tùy biến: vừa chọn được cột cần tính tổng (động), vừa lọc được nhiều tiêu chí (tên sản phẩm, khu vực, trạng thái…).

2 –  Tôi có thể dùng VLOOKUP thay cho INDEX được không?

Không. Giữa hai hàm VLOOKUP và INDEX có sự khác biệt như sau:

• Hàm VLOOKUP: Trả về giá trị của một ô duy nhất tại nơi giao nhau của hàng và cột.
• Hàm INDEX: Có khả năng trả về toàn bộ một mảng (một cột hoặc một hàng) nếu bạn đặt tham số hàng/cột bằng 0.

Để hàm SUMIF hoạt động, tham số thứ 3 bắt buộc phải là một vùng dữ liệu (một dải ô). Hàm VLOOKUP chỉ ném ra một con số cụ thể, nên nếu bạn bỏ vào SUMIF, hàm sẽ báo lỗi vì nó không tìm thấy một dải ô để tính tổng.

3 – Tôi có thể dùng tổ hợp INDEX-MATCH-SUMIF để tính tổng theo hàng thay vì theo cột không?

Có. Bằng cách đổi tham số trong hàm INDEX. Thay vì để hàng bằng 0 (INDEX(vùng; 0; số_cột)), bạn hãy để cột bằng 0 và dùng MATCH để tìm số hàng: INDEX(vùng; số_hàng; 0). Lúc này, SUMIF sẽ thực hiện quét ngang để cộng tổng.

>>>> Xem thêm: kết hợp hàm FILTER với hàm COUNTA trong Google Sheets

Lời kết

Tóm lại, việc kết hợp hàm INDEX và hàm SUMIF trong Google Sheets giúp bạn xử lý dữ liệu linh hoạt. Khi đã nắm vững được cách sử dụng hai hàm khi kết hợp, bạn sẽ dễ dàng làm chủ bảng tính của mình. Người dùng cũng nên sở hữu ứng dụng Google Sheet chính hãng với nhiều tính năng nâng cao và nhận sự hỗ trợ mạnh mẽ từ Gemini AI bằng cách đăng ký ngay gói Google Workspace Business chính hãng. Tham khảo bảng giá tại đường link này: https://gcs.vn/google-workspace-business/

Nếu trong quá trình thực hiện xử lý các bài toán trong Google Sheets gặp khó khăn, đừng ngần ngại để lại bình luận hoặc liên hệ với GCS Việt Nam qua các kênh sau để nhận hỗ trợ nhé. 

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777