Tin Nóng![[Video] Cách kết hợp hàm SUM với hàm IF trong Google Sheet](https://bachkhoavietnam.vn/wp-content/uploads/2024/09/video-cach-ket-hop-ham-sum-voi-ham-if-trong-google-sheet-thumb.jpg)
Hàm SUM và IF là hai hàm thường được dùng trong Google Sheet để tính toán. Khi hai hàm này kết hợp sẽ đem lại một hiệu quả đáng kể khi xử lý dữ liệu. Cùng theo dõi hướng dẫn bên dưới để biết hướng dẫn chi tiết hàm SUM kết hợp IF trong Google Sheet!
Sau đây là video hướng dẫn bạn cách sử dụng hàm SUM kết hợp hàm IF trong Google Sheet cực đơn giản:
1. Công thức hàm SUM
– Công thức:
=SUM(number1; [number2],…)
– Trong đó:
number1 và number2 là những số hoặc dãy số sẽ tính.
Để biết cách sử dụng hàm chi tiết bạn nên tham khảo bài viết Hàm SUM trong Google Sheet ngay nhé!
2. Công thức hàm IF
– Công thức:
=IF(biểu_thức_logic; giá_trị_nếu_đúng; giá_trị_nếu_sai)
– Trong đó:
+ biểu_thức_logic: Là biểu thức so sánh. Điều kiện nếu biểu thức đó xảy ra.
+ giá_trị_nếu_đúng: Trả về giá trị nếu biểu_thức_logic đúng.
+ giá_trị_nếu_sai: Trả về giá trị nếu biểu_thức_logic sai.
Để biết cách sử dụng hàm chi tiết bạn nên tham khảo bài viết Hàm IF trong Google Sheet ngay nhé!
3. Hàm SUM kết hợp hàm IF trong Google Sheet
Công thức
=SUM(IF(điều kiện;tiêu chí);Giá trị hợp lệ;giá trị không hợp lệ)
Để hiểu rõ hơn về cách hoạt động của công thức kết hợp hàm SUM với hàm IF trên, cùng xem ví dụ sau nhé!
Một số ví dụ
Cho 1 bảng dữ liệu có các trường dữ liệu về sản phẩm, nhà cung cấp và số lượng. Hãy thực hiện các yêu cầu sau đây.
|
SẢN PHẨM
|
NHÀ CUNG CẤP
|
SỐ LƯỢNG
|
|
Socola
|
Bánh kẹo
|
10
|
|
Bánh snack
|
Bánh kẹo
|
0
|
|
Trà ô long
|
Nước ngọt
|
5
|
|
Pepsi
|
Nước ngọt
|
10
|
|
Dầu gội
|
Hóa phẩm
|
0
|
|
Nước rửa chén
|
Hóa phẩm
|
16
|
|
Trái cây sấy
|
Bánh kẹo
|
0
|
|
Đường
|
Gia vị
|
34
|
|
Cà phê
|
Nước ngọt
|
22
|
|
Kẹo sữa
|
Bánh kẹo
|
0
|
|
Nước suối
|
Nước ngọt
|
35
|
|
Muối
|
Gia vị
|
0
|
|
Nước tẩy
|
Hóa phẩm
|
13
|
|
Sữa hộp
|
Nước ngọt
|
29
|
|
Socola
|
Bánh kẹo
|
20
|
Câu hỏi 1: Tính tổng số lượng có sản phẩm là Socola và nhà cung cấp là Bánh kẹo.
– Công thức:
=ArrayFormula(SUM((IF((A2:A16=”Socola”)*(B2:B16=”Bánh kẹo”);C2:C16;0))))
– Trong đó:
+ Điều kiện là giá trị từ ô A2:A16 có giá trị bằng Socola.
+ Điều kiện là giá trị từ ô B2:B16 có giá trị là Bánh kẹo.
+ Nếu hợp lệ sẽ trả về giá trị tương ứng trong ô từ C2:C16.
+ Nếu không hợp lệ sẽ trả về 0.
– Giải thích công thức: Nếu giá trị từ ô A2:A16 có giá trị là Socola và ô B2:B16 là Bánh kẹo thì cộng các giá trị đó lại. Ta dùng dấu * để kết hợp nhiều điện kiện cùng lúc.
Lưu ý: Để hàm IF trả về giá trị mảng phải nhờ hàm ArrayFormula hỗ trợ.
Ví dụ 1
Câu hỏi 2: Tính tổng số lượng của nhà cung cấp là Bánh kẹo hoặc Gia vị.
– Công thức:
=ArrayFormula(SUM(IF((B2:B16=”Gia vị”)+(B2:B16=”Bánh kẹo”);C2:C16;0)))
– Trong đó:
+ Điều kiện là giá trị từ B2:B16 có giá trị là Gia vị.
+ Điều kiện là giá trị từ B2:B16 có giá trị là Bánh kẹo.
+ Nếu hợp lệ sẽ trả về giá trị tương ứng trong ô từ C2:C16.
+ Nếu không hợp lệ sẽ trả về 0.
– Giải thích công thức: Giá trị từ ô B2:B16 nếu bằng Gia vị hoặc Bánh kẹo thì cộng các giá trị đó lại. Ta dùng dấu + để kết hợp điều kiện hoặc bằng Gia vị hoặc bằng Bánh kẹo.
Lưu ý: Để hàm IF trả về giá trị mảng phải nhờ hàm ArrayFormula hỗ trợ.
Ví dụ 2
4. Các lỗi thường gặp khi kết hợp hàm SUM và hàm IF
Lỗi #DIV/0!
– Giải thích: Lỗi #DIV/0! là lỗi chia cho 0.
– Cách khắc phục lỗi #DIV/0!: Thay thế 0 bằng giá trị khác, hoặc nếu bất cẩn thì hãy xóa đi giá trị này.
– Ví dụ sửa lỗi: Lúc này mình sẽ xóa phần chia cho 0 đi thì sẽ hết lỗi.
Lỗi #DIV/0!
Lỗi #ERROR
– Giải thích: Gặp lỗi #ERROR khi ô giá trị khi công thức bị dư đối số.
– Cách khắc phục lỗi #ERROR: Xóa bớt các đối số dư, chỉnh sửa lại công thức đúng.
– Ví dụ sửa lỗi: Ta thấy trong công thức sai bị dư giá trị là “Bánh kẹo” nên ta sẽ xóa đi đối số này.
Lỗi #ERROR
Lỗi #NAME
– Giải thích: Gặp lỗi #NAME khi công thức bị ghi sai tên.
– Cách khắc phục lỗi #NAME: Nhập lại đúng tên công thức.
– Ví dụ sửa lỗi: Trong cú pháp sai thì hàm SUM đã bị viết sai tên là “sume”, ta chỉ cần xóa đi chữ “e” là có thể sửa lỗi.
Lỗi #NAME
5. Một số lưu ý khi kết hợp hàm SUM và hàm IF
– Hàm IF không phân biệt chữ hoa hay chữ thường trong lúc nhập công thức.
– Nếu bạn không nhập giá trị trả về khi đúng và sai thì khi đúng sẽ trả về TRUE, khi sai sẽ trả về FALSE.
Lưu ý khi kết hợp hàm IF và SUM
– Phải nhập chính xác điều kiện như chữ hoa, chữ thường, số,…để có thể dò được giá trị chính xác nhất.
– Nếu điều kiện là text phải đặt trong dấu ngoặc kép.
6. Bài tập ví dụ về kết hợp hàm SUM và hàm IF
Cho bảng với các trường dữ liệu như sau: Ký hiệu lô, Sản phẩm, Giá, Số lượng, Thuế VAT. Hãy thực hiện các yêu cầu bên dưới.
|
Ký hiệu lô
|
Sản phẩm
|
Giá
|
Số lượng
|
Thuế VAT (10%)
|
|
G
|
iPhone X 64GB
|
10000000
|
5
|
1000000
|
|
BT
|
iPhone X 128GB
|
18000000
|
10
|
1800000
|
|
G
|
iPhone 11 256GB
|
21000000
|
12
|
2100000
|
|
BT
|
iPhone 12 mini 128GB
|
20000000
|
5
|
2000000
|
|
G
|
iPhone 12 Pro Max 512GB
|
42000000
|
7
|
4200000
|
|
G
|
iPhone 12 Pro 256Gb
|
29000000
|
8
|
2900000
|
Câu hỏi 1: Tính giá Tổng giá trị mỗi sản phẩm. Trong đó, nếu sản phẩm có ký hiệu là BT thì cộng thuế VAT 10% còn nếu mã là G thì lô hàng đó được miễn thuế.
Trả lời: =ARRAYFORMULA(SUM(IF(A2=”G”;0;E2);C2))
Giải thích: Nếu Ký hiệu lô (tức là giá trị của cột A) có mã là “G” thì cộng vào là giá trị thuế là 0, nếu không phải mã G thì cộng vào giá trị của E2. Sau đó cộng vào giá trị thực của sản phẩm ở cột C2.
Giải thích chi tiết câu hỏi 1
Sau khi thực hiện xong câu hỏi 1 thì ta có bảng giá trị mới để thực hiện tiếp các câu hỏi tiếp theo. Mình bổ sung thêm cột Tổng giá trị sản phẩm (Tổng giá trị mỗi sản phẩm*Số lượng).
|
Kí hiệu lô
|
Sản phẩm
|
Giá
|
Số lượng
|
Thuế VAT (10%)
|
Tổng giá trị mỗi sản phẩm
|
Tổng giá trị sản phẩm
|
|
G
|
iPhone X 64GB
|
10000000
|
5
|
1000000
|
10000000
|
50000000
|
|
BT
|
iPhone X 128GB
|
18000000
|
10
|
1800000
|
19800000
|
198000000
|
|
G
|
iPhone 11 256GB
|
21000000
|
12
|
2100000
|
21000000
|
252000000
|
|
BT
|
iPhone 12 mini 128GB
|
20000000
|
5
|
2000000
|
22000000
|
110000000
|
|
G
|
iPhone 12 Pro Max 512GB
|
42000000
|
7
|
4200000
|
42000000
|
294000000
|
|
G
|
iPhone 12 Pro 256Gb
|
29000000
|
8
|
2900000
|
29000000
|
232000000
|
Câu hỏi 2: Tính tổng giá trị sản phẩm của lô BT.
Trả lời: =ARRAYFORMULA(SUM(IF(A2:A7=”BT”;G2:G7;0)))
Giải thích: Ta sẽ cộng tổng giá trị mỗi sản phẩm của các sản phẩm có ký hiệu lô là BT (tức cột A) và tổng giá trị nằm ở cột G. Nếu không phải mã BT thì cộng 0.
Giải thích chi tiết câu hỏi 2
Câu hỏi 3: Tính tổng giá trị sản phẩm của lô BT và có số lượng từ 10 trở lên.
Trả lời:=ARRAYFORMULA(SUM(IF((A2:A7=”BT”)*(D2:D7>=10);G2:G7;0)))
Giải thích: Điều kiện là các ký hiệu lô là BT (tức cột A) và số lượng (tức cột D) từ 10 trở lên thì cộng lại tổng giá trị (ở cột G).
Giải thích chi tiết câu hỏi 3
Câu hỏi 4: Tính tổng số lượng iPhone 11 và iPhone 12.
Trả lời: =ArrayFormula(SUM(IF((LEFT(B2:B7;9)=”iPhone 11″)+(LEFT(B2:B7;9)=”iPhone 12″);D2:D7;0)))
Giải thích: Đầu tiên ta phải lấy tên sản phẩm bằng hàm Left với 9 ký tự đầu tiên để phân biệt là sản phẩm gì. Nếu giá trị bằng iPhone 11 hoặc iPhone 12 thì cộng lại tổng số lượng (ở cột D).
Giải thích chi tiết câu hỏi 4
7. Câu hỏi liên quan đến hàm SUM và hàm IF
Tại sao dùng dấu * hoặc + cho biểu thức có nhiều điều kiện mà không dùng AND/OR?
Đối với dữ liệu kiểu mảng thì ta sẽ dùng * và + vì hàm AND không hỗ trợ lấy giá trị trong mảng. And chỉ tham chiếu để các dữ liệu đơn.
Các câu hỏi thường gặp khi dùng SUM kết hợp với IF
Một số mẫu laptop đang kinh doanh tại Thế Giới Di Động:
Mình tin rằng sau bài viết này bạn đã có thể nắm được các công thức và cách kết hợp giữa hai hàm SUM và IF. Cám ơn đã theo dõi và hẹn gặp lại ở những bài viết tiếp theo!