Nếu bạn sử dụng một cột ngày làm cột tiêu chí, đôi khi bạn sẽ nhận được kết quả sai trong các công thức Đếm, Tổng, Tích, vv. khi sử dụng tháng mười hai. Bài viết này giải thích cách giải quyết vấn đề tháng 12 trong các công thức trong Google Sheets.
Bài viết này là một phần tiếp theo của bài viết trước của tôi có tiêu đề Làm thế nào để Trả về Ô Trống Thay vì 30-12-1899 trong Công thức Google Sheets.
Vui lòng đọc phần đó trước, nếu bạn chưa đọc. Bởi vì trong bài viết đó, tôi đã giải thích nguyên nhân gốc rễ của vấn đề tháng 12 trong các công thức trong Google Sheets.
Dưới đây là lý do tóm tắt cùng với một công thức ví dụ.
Tôi hy vọng bạn đã quen thuộc với các chức năng ngày trong Google Sheets. DATEDIF là một trong những chức năng ngày hữu ích. Cú pháp của hàm này như sau.
DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, đơn_vị)
Ở đây, tôi sẽ viết một công thức dựa trên hàm này. Đầu tiên, hãy đặt ô C3 trống, và trong ô D4, chèn công thức sau đây.
=datedif(hôm_nay(),C3)
Công thức sẽ trả về #SỐ vì ngày_bắt_đầu là ngày hôm nay và ngày_kết_thúc là trống. Trong Google Spreadsheets, ô trống đại diện cho 30/12/1899.
=datedif(hôm_nay(),C3,”D”)
Lý do gây lỗi là ngày_kết_thúc. Nó phải lớn hơn hoặc bằng ngày_bắt_đầu.
Vì vậy, giá trị của ô trống (hoặc 0 trong một ô) được coi như 30/12/1899 bởi các hàm ngày.
Bây giờ hãy đi vào nguyên nhân gốc rễ của vấn đề tháng 12 trong các công thức trong Google Sheets.
Hãy thử công thức dưới đây trong bất kỳ ô trống nào khác ô C3.
=month(C3)
Ngay cả khi C3 trống, công thức sẽ trả về giá trị 12, đó là số tháng của giá trị (trống) trong ô C3. Gõ 0 vào C3. Kết quả sẽ một lần nữa là 12.
Điều này có thể gây ra các vấn đề trong hầu hết các công thức. Ở đây, tôi sẽ giúp bạn giải quyết vấn đề tháng 12 trong một số công thức trong Google Sheets.
Vấn Đề Tháng 12 Trong Các Công Thức Trong Google Sheets
Tôi sẽ đưa ra một số ví dụ cơ bản sử dụng Countif, Sumif, Query và Sumproduct. Điều tương tự cũng áp dụng cho Countifs, Sumifs và một số hàm khác.
Bạn có thể thích: Hướng Dẫn Sử Dụng Các Hàm Phổ Biến Trong Google Sheets.
Countif – Số Lượng Sai Với Tháng 12
Hãy xem công thức COUNTIF dưới đây cho phạm vi B2: B5.
=ArrayFormula(countif(month(B2:B15),12))
Công thức đếm số lượng mục ngày trong B2: B15 rơi vào tháng mười hai.
Nhập thực tế là 6 (bạn có thể đếm thủ công để xác minh), nhưng công thức trả về sai với 8 vì hai ô B11 và B12. Để giải quyết điều này, chúng ta có thể sử dụng phương pháp dưới đây.
Áp dụng công thức trong một phạm vi đã lọc. Điều đó có nghĩa thay thế tham chiếu B2: B15 trong công thức bằng filter(B2:B15,B2:B15>0) như sau.
=ArrayFormula(countif(month(filter(B2:B15,B2:B15>0)),12))
Đọc thêm: Cách Sử Dụng Hàm Lọc Trong Google Sheets [Cách Sử Dụng Cơ Bản Và Nâng Cao]
Sumif – Vấn Đề Tháng 12 Trong Sumif
Hãy tổng các giá trị trong C2: C15 theo điều kiện trong cùng bộ dữ liệu trên. Ở đây, điều kiện là tháng mười hai.
Nghĩa là tổng C2: C15 nếu tháng của các ngày trong B2: B15 là 12.
Đây là công thức SUMIF trả về tổng sai cho tháng mười hai.
=ArrayFormula(sumif(month(B2:B15),12,C2:C15))
Lưu ý: Tôi đã sửa đổi bộ dữ liệu một chút. Nó không giống như chúng ta đã sử dụng trong COUNTIF.
Công thức sẽ trả về 1525 là tổng có điều kiện và đó là sai vì TỔNG bao gồm các giá trị trong C11 và C12.
Ở đây, chúng ta có thể sử dụng SUMIFS như sau.
=ArrayFormula(sumifs(C2:C15,month(B2:B15),12,B2:B15,”>0″))
Tức là, để giải quyết vấn đề tháng 12 trong công thức SUMIF trong Google Sheets, chúng ta có thể sử dụng hàm SUMIFS. Hãy lưu ý rằng chúng ta cũng có thể sử dụng một số công thức khác.
Tôi sẽ không đi vào chi tiết đó để tránh gây nhầm lẫn thêm.
