Hướng dẫn từ A – Z cách sử dụng hàm SUMIF kết hợp VLOOKUP

Hàm SUMIF kết hợp VLOOKUP là công cụ mạnh mẽ trong Google Sheets. Khi hai hàm kết hợp với nhau sẽ giúp bạn tính tổng dữ liệu theo điều kiện được tra cứu từ bảng phụ cùng nhiều lợi ích khác. Hãy cùng GCS Việt Nam khám phá chi tiết về cách sử dụng hàm SUMIF và VLOOKUP cùng các ví dụ thực tế cụ thể ngay trong bài viết dưới đây. 

Hàm SUMIF và cách sử dụng

Hàm SUMIF được biết đến là một trong những hàm tính tổng có điều kiện. Hàm này thường dùng để tính tổng các giá trị trong một phạm vi nhất định dựa trên một điều kiện duy nhất mà bạn cung cấp. Hàm SUMIF khá tiện lợi khi dùng để lọc và tổng hợp dữ liệu.

Cú pháp của hàm SUMIF là:

=SUMIF(vùng_điều_kiện, điều_kiện, [vùng_tính_tổng])

Trong đó:

• vùng_điều_kiện: phạm vi chứa điều kiện cần kiểm tra.
• điều_kiện: điều kiện cần áp dụng (ví dụ: “Nam”, “>100”, A2…).
• vùng_tính_tổng: phạm vi chứa giá trị cần tính tổng (có thể bỏ qua nếu giống vùng_điều_kiện).

*Ví dụ:

Có bảng dữ liệu

Yêu cầu: Tính tổng doanh thu của nhân viên nam đạt được

Công thức: =SUMIF(B2:B7; “Nam”; D2:D7)

Trong đó:

• B2:B7 là vùng chứa giới tính.
• “Nam” là điều kiện cần lọc.
• D2:D7 là vùng cần tính tổng doanh thu.

*Kết quả:

Hàm VLOOKUP và cách sử dụng

Nếu hàm SUMIF giúp tính tổng có điều kiện, hàm VLOOKUP lại giúp tìm kiếm và lấy dữ liệu từ một bảng tính khác dựa trên một giá trị tham chiếu. Hàm VLOOKUP cho phép tra cứu một giá trị trong cột đầu tiên của một bảng và sau đó trả về một giá trị tương ứng từ một cột khác trong cùng hàng đó. Hàm VLOOKUP được sử dụng nhiều trong Google Sheets, bởi vì nó có thể ứng dụng trong nhiều lĩnh vực khác nhau. 

Cú pháp của hàm VLOOKUP:

=VLOOKUP(giá_trị_tra_cứu, bảng_dữ_liệu, số_thứ_tự_cột, [tìm_kiếm_gần_đúng])

Trong đó:

• giá_trị_tra_cứu: giá trị cần tìm.
• bảng_dữ_liệu: vùng bảng chứa dữ liệu cần tra cứu.
• số_thứ_tự_cột: cột chứa giá trị cần trả về (tính từ cột đầu tiên của bảng).
• [tìm_kiếm_gần_đúng]: TRUE hoặc FALSE. Thường dùng FALSE để tìm chính xác.

*Ví dụ: Từ bảng dữ liệu trên, tìm doanh thu của nhân viên có tên Cao Ngọc Hiển

Công thức: =VLOOKUP(“Cao Ngọc Hiển”; A2:D7; 4; FALSE)

Trong đó:

• “Cao Ngọc Hiển” là tên cần tra cứu.
• A2:D7 là toàn bộ bảng dữ liệu.
• 4 là cột Doanh thu.
• FALSE đảm bảo kết quả tra chính xác.

*Kết quả:

Lợi ích của việc kết hợp hàm SUMIF và VLOOKUP

Khi tách lẻ hai hàm SUMIF và VLOOKUP sẽ mang tới những lợi ích khác nhau. Tuy nhiên, khi kết hợp hàm SUMIF và VLOOKUP trong Google Sheets sẽ mang lại nhiều giá trị vượt trội trong việc xử lý, phân tích và tổng hợp dữ liệu. Đặc biệt, khả năng hàm SUMIF kết hợp VLOOKUP còn giúp mở rộng khả năng làm việc với dữ liệu phức tạp từ nhiều bảng, nhiều điều kiện khác nhau. Cụ thể như là:

Giúp tính tổng dữ liệu từ bảng phụ theo điều kiện linh hoạt

Trong nhiều trường hợp, dữ liệu bạn cần tính tổng không nằm trong bảng chính mà nằm ở các bảng tham chiếu. Do đó, khi hàm SUMIF kết hợp VLOOKUP, bạn sẽ lấy đúng giá trị cần tính từ bảng tham chiếu rồi áp dụng điều kiện trong bảng chính để tổng hợp lại. Thao tác này thường được áp dụng trong những ứng dụng thực tế là báo cáo doanh số, chi phí, tính hoa hồng, quản lý nhân sự,…

*Ví dụ:

Tính tổng doanh thu của một phòng ban nhưng dữ liệu doanh thu lại nằm trong bảng khác. Dùng hàm VLOOKUP để lấy doanh thu theo từng nhân viên. Sau đó, dùng hàm SUMIF để tổng hợp theo phòng ban. 

Xử lý dữ liệu từ nhiều nguồn hoặc nhiều Sheet khác nhau

Với hàm SUMIFS kết hợp VLOOKUP, bạn có thể áp dụng tổng hợp theo nhiều điều kiện dữ liệu nằm rải rác ở nhiều bảng hoặc nhiều Sheets. Trong các bản báo cáo tài chính hoặc quản lý các nhóm, khu vực, sự kết hợp của hai hàm SUMIF và VLOOKUP được sử dụng rất rộng rãi. 

*Ví dụ:

Tính tổng chi phí cho các nhân viên thuộc một nhóm cụ thể và đồng thời có doanh thu vượt một mức nhất định. Trong đó, dữ liệu nhóm và doanh thu nằm ở các Sheet khác nhau. 

Giải pháp tối ưu trong Dashboard (báo cáo động)

Trong xây dựng báo cáo động, hàm SUMIF kết hợp VLOOKUP cho phép tạo các bảng tổng hợp có tính động dựa vào danh sách (dropdown, slicer,…).  Tính năng đặc biệt này được các nhà phân tích dữ liệu, kiểm toán, kế toán, quản trị nhân sự,… sử dụng thường xuyên. 

Cách để sử dụng hàm SUMIF kết hợp VLOOKUP

Để kết hợp hàm SUMIF và VLOOKUP, bạn sẽ sử dụng hàm VLOOKUP để tìm điều kiện cần thiết. Sau đó, bạn sử dụng hàm SUMIF để tính tổng dựa trên điều kiện đó. 

Cú pháp hàm SUMIF kết hợp VLOOKUP

Công thức tổng quát:

=SUMIF(vùng_điều_kiện, VLOOKUP(giá_trị_tra_cứu, bảng_dữ_liệu, cột_kết_quả, phạm_vi_tìm_kiếm))

Trong đó:

• vùng_điều_kiện: cột hoặc vùng chứa các giá trị mà hàm SUMIF sẽ kiểm tra để xem liệu có khớp với điều kiện mà VLOOKUP trả về hay không.
• VLOOKUP(giá_trị_tra_cứu, bảng_dữ_liệu, cột_kết_quả, phạm_vi_tìm_kiếm): 
  • giá_trị_tra_cứu: Giá trị bạn muốn tra cứu
  • bảng_dữ_liệu: Bảng dữ liệu nơi VLOOKUP sẽ tìm kiếm giá_trị_tra_cứu và lấy thông tin tương ứng
  • cột_kết_quả: Số thứ tự của cột trong bảng_dữ_liệu chứa thông tin mà bạn muốn lấy làm điều kiện cho SUMIF
  • phạm_vi_tìm_kiếm: FALSE hoặc 0 để đảm bảo tìm kiếm chính xác.

• SUMIF(vùng điều kiện,…): Cột hoặc vùng chứa các giá trị số mà muốn tính tổng khi điều kiện (được trả về từ VLOOKUP) được thỏa mãn.

Ví dụ: Áp dụng hàm SUMIF kết hợp VLOOKUP để tra cứu tiêu chí một cách cụ thể

Từ bảng dữ liệu trên, bạn muốn biết tổng doanh số của phòng ban mà nhân viên “Nguyễn Quốc Khánh” đang làm việc. Bạn sẽ dùng VLOOKUP để tìm phòng ban của “Nguyễn Quốc Khánh”, sau đó dùng tên phòng ban đó làm tiêu chí cho SUMIF.

Công thức: =SUMIF(C1:C7; VLOOKUP(F2; A1:C7; 3; FALSE);D1:D7)

Trong đó:

• VLOOKUP(F2; A1:C7; 3; FALSE) sẽ tìm “Nguyễn Quốc Khánh” trong cột A và trả về “Marketing” từ cột C.
• Sau đó, công thức trở thành =SUMIF(C1:C7, “Marketing”, D1:D7)).
• SUMIF sẽ tìm tất cả các hàng có “Marketing” trong cột C và cộng tổng các giá trị tương ứng trong cột D.
• Nguyễn Tú Vi (Marketing): 12,000,000
• Nguyễn Quốc Khánh (Marketing): 10,000,000
• Bùi Ngọc Quốc (Marketing): 13,000,000
• Tổng doanh số sẽ là: 12,000,000+10,000,000+13,000,000=35,000,000.

*Kết quả: 

Hàm SUMIFS kết hợp VLOOKUP cho nhiều điều kiện nâng cao

Nếu hàm SUMIF giúp tính tổng với một điều kiện duy nhất, thì hàm SUMIFS giúp tính tổng dựa trên nhiều điều kiện đồng thời. Và khi kết hợp SUMIFS với VLOOKUP, sẽ mở khóa khả năng phân tích dữ liệu phức tạp hơn.

Cú pháp hàm SUMIFS kết hợp VLOOKUP

Công thức tổng quát:

=SUMIFS(vùng_tính_tổng, vùng_điều_kiện_1, tiêu_chí_1, vùng_điều_kiện_2, tiêu_chí_2, …, vùng_điều_kiện_N, VLOOKUP(giá_trị_tìm_kiếm, bảng_tra_cứu, số_thứ_tự_cột, [phạm_vi_tìm_kiếm]))

Trong đó:

• vùng_tính_tổng: là phạm vi chứa các số mà bạn muốn tính tổng. 
• vùng_điều_kiện_1, tiêu_chí_1: vùng_điều_kiện_1 là phạm vi mà bạn muốn kiểm tra điều kiện;  tiêu_chí_1 là giá trị hoặc biểu thức mà các ô trong vùng_điều_kiện_1 phải khớp. 
• VLOOKUP(giá_trị_tìm_kiếm, bảng_tra_cứu, số_thứ_tự_cột, [phạm_vi_tìm_kiếm]): là phần cốt lõi để tra cứu tiêu chí động.
  • giá_trị_tìm_kiếm: Giá trị bạn muốn tìm trong cột đầu tiên của bảng_tra_cứu. 
  • bảng_tra_cứu: Phạm vi của bảng hoặc dải ô mà VLOOKUP sẽ tìm kiếm. Cột đầu tiên của phạm vi này cần phải chứa giá_trị_tìm_kiếm.
  • số_thứ_tự_cột: Số thứ tự của cột trong bảng_tra_cứu mà bạn muốn VLOOKUP trả về giá trị.
  • [phạm_vi_tìm_kiếm]: TRUE để tìm kiếm gần đúng hoặc FALSE để tìm kiếm chính xác. Luôn nên sử dụng FALSE (0) cho hầu hết các trường hợp tra cứu dữ liệu cụ thể.

Ví dụ : Áp dụng hàm SUMIFS kết hợp với VLOOKUP để tra cứu nhiều tiêu chí.

Từ bảng dữ liệu trên, bạn muốn tính tổng doanh số của tất cả các nhân viên nam làm việc trong cùng phòng ban với một nhân viên “Nguyễn Quốc Khánh”.

Công thức: =SUMIFS(D1:D7; B1:B7; “Nam”; C1:C7; VLOOKUP(F2; A1:C7; 3; FALSE))

Trong đó:

• VLOOKUP(F2; A1:C7; 3; FALSE) sẽ tìm “Nguyễn Quốc Khánh” trong cột A và trả về “Marketing” từ cột C.
• Sau đó, công thức trở thành =SUMIFS(D1:D7; B1:B7; “Nam”; C1:C7;”Marketing”).
• SUMIFS sẽ thực hiện các bước sau:
  • Kiểm tra các hàng có “Nam” trong cột B.
  • Trong số các hàng đó, tiếp tục kiểm tra các hàng có “Marketing” trong cột C.
  • Cuối cùng, cộng tổng các giá trị tương ứng trong cột D cho các hàng thỏa mãn cả hai điều kiện trên.
• Dựa trên bảng dữ liệu:
  • Nguyễn Quốc Khánh: Nam, Marketing, 10,000,000 (Thỏa mãn cả hai tiêu chí)
  • Bùi Ngọc Quốc: Nam, Marketing, 13,000,000 (Thỏa mãn cả hai tiêu chí)
• Tổng doanh số sẽ là: 10,000,000+13,000,000=23,000,000.

*Kết quả:

Lỗi thường gặp khi sử dụng hàm SUMIF kết hợp VLOOKUP

Thông thường, khi hàm SUMIF kết hợp VLOOKUP sẽ gặp ra ba lỗi chính là #N/A, trả về 0, và #VALUE!. Mỗi lỗi đều có nguyên nhân và cách xử lý riêng như sau:

Lỗi #N/A

Lỗi #N/A thường xuất hiện khi hàm VLOOKUP không tìm thấy giá_trị_tìm_kiếm trong cột đầu tiên của bảng_tra_cứu. Khi hàm VLOOKUP trả về #N/A, thì toàn bộ công thức hàm SUMIF kết hợp VLOOKUP sẽ trả về lỗi #N/A.

Nguyên nhân để có thể gây ra lỗi #N/A là:

• Giá trị không tồn tại: giá_trị_tìm_kiếm trong hàm VLOOKUP không có trong cột đầu tiên của bảng_tra_cứu.
• Lỗi chính tả hoặc khoảng trắng: dấu cách thừa, hoặc ký tự không nhìn thấy được trong giá_trị_tìm_kiếm hoặc trong bảng_tra_cứu. 
• Định dạng dữ liệu không khớp: giá trị tìm kiếm là số nhưng được định dạng là văn bản, hoặc ngược lại, trong khi dữ liệu trong cột tìm kiếm lại có định dạng khác.
• Vùng bảng_tra_cứu không cố định: Khi sao chép công thức, vùng bảng_tra_cứu của hàm VLOOKUP bị thay đổi, dẫn đến việc tìm kiếm sai phạm vi.

Cách để có thể khắc phục tình trạng này mà bạn có thể tham khảo như sau:

• Kiểm tra chính tả và khoảng trắng
• Xác nhận giá_trị_tìm_kiếm có tồn tại
• Kiểm tra định dạng dữ liệu
• Khóa phạm vi hàm VLOOKUP bằng $

Lỗi trả về 0

Cách sử dụng hàm SUMIF kết hợp VLOOKUP trả về 0. Có nghĩa là công thức hoạt động đúng cú pháp, nhưng dữ liệu không thỏa mãn tất cả các điều kiện mà SUMIF đưa ra.

Nguyên nhân của lỗi trả về 0 là do:

• Không có dòng dữ liệu nào khớp tất cả các tiêu chí: Hàm VLOOKUP trả về giá trị đúng, nhưng khi kết hợp với các tiêu chí khác của hàm SUMIF, không có hàng nào thỏa mãn đồng thời.
• Tiêu chí số có dấu ngoặc kép: Các tiêu chí số hoặc ngày tháng được đặt trong dấu ngoặc kép không đúng cách (ví dụ: “>10000000” là đúng).

Cách để có thể khắc phục tình trạng này như sau:

• Kiểm tra lại vùng dữ liệu xem có dòng nào thực sự thỏa mãn tất cả các điều kiện không.
• Kiểm tra cú pháp hàm  SUMIFS có đảm bảo vùng điều kiện và tiêu chí được đặt đúng thứ tự.

Lỗi #ERROR

Lỗi #ERROR thường xảy ra khi đối số không hợp lệ trong công thức.

Nguyên nhân cụ thể là do:

• Tham chiếu ô sai: Vùng bảng_tra_cứu trong hàm VLOOKUP được định nghĩa không chính xác. Hoặc số_thứ_tự_cột trong hàm VLOOKUP lớn hơn số cột trong bảng_tra_cứu.
• Cột vùng_tính_tổng chứa lỗi:  Nếu cột mà hàm SUMIF tính tổng nhưng chứa các lỗi như #DIV/0!, #VALUE!, hoặc #N/A, thì kết quả tổng cũng sẽ là lỗi.

Cách khắc phục khi gặp phải lỗi này như sau:

• Kiểm tra lại bảng_tra_cứu và số_thứ_tự_cột của VLOOKUP
• Kiểm tra vùng_tính_tổng
• Kiểm tra tất cả các đối số của hàm SUMIF

Lưu ý quan trọng khi kết hợp hàm SUMIF và VLOOKUP

Khi sử dụng hàm SUMIF kết hợp VLOOKUP, bạn cần chú ý một số điểm quan trọng sau đây để tránh lỗi công thức như sau:

• Kiểm tra định dạng dữ liệu

Một lỗi phổ biến khi hàm SUMIF kết hợp VLOOKUP, đó là định dạng dữ liệu không thống nhất. Ví dụ như dữ liệu trong cột điều kiện là text nhưng kết quả từ hàm VLOOKUP là số. Hoặc có những giá trị chứa khoảng trắng thừa sẽ gây ra lỗi không khớp điều kiện.

• Ưu tiên sử dụng hàm SUMIFS kết hợp VLOOKUP khi có nhiều điều kiện

Khi có từ 2 điều kiện trở nên, bạn hãy sử dụng hàm SUMIFS thay vì SUMIF đơn lẻ. Điều này sẽ giúp giảm sai số so với việc dùng hàm lồng nhau phức tạp. Đồng thời, các điều kiện được liệt kê cũng sẽ rõ ràng hơn. 

• Tránh dùng VLOOKUP bên trong SUMIF quá nhiều lần

Nếu lồng hàm VLOOKUP quá nhiều lần trong công thức SUMIF, đặc biệt khi áp dụng cho hàng trăm dòng dữ liệu, hiệu suất xử lý từ Google Sheets sẽ bị giảm đáng kể. Do đó, bạn chỉ nên sử dụng hàm SUMIF kết hợp VLOOKUP khi cần tra cứu điều kiện từ bảng phụ trước khi áp dụng tổng, hay sử dụng bảng dữ liệu số dòng không quá lớn. 

Lời kết 

Hy vọng qua bài viết này, bạn đã hiểu rõ hơn về hàm SUMIF kết hợp VLOOKUP. Cũng như từ những ví dụ minh họa trong bài viết, các bạn biết cách áp dụng hàm vào thực tế một cách hiệu quả. Và nếu còn bất cứ thắc mắc gì liên quan đến chủ đề, các bạn có thể liên hệ ngay với GCS Việt Nam qua các kênh dưới đây để được hỗ trợ kịp thời từ chuyên gia.

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777