Google Sheets là công cụ mạnh mẽ để tổ chức và phân tích dữ liệu. Một trong những tính năng hữu ích của Sheets là bảng tổng hợp (Pivot Table), cho phép chúng ta tạo bảng tương tác để phân tích dữ liệu. Tuy nhiên, bảng tổng hợp trên Google Sheets có một hạn chế – không thể điền các ô trống bằng 0. Nhưng đừng lo lắng, bạn của tôi! Tôi có một giải pháp thay thế sẽ giúp bạn làm được điều này.
Hướng dẫn từng bước để điền các ô trống bằng 0 trong bảng tổng hợp trên Google Sheets
Để minh họa cho hướng dẫn này, hãy xem xét một dải dữ liệu mẫu từ A1:C và một bảng tổng hợp kế cạnh. Dưới đây là các bước chi tiết:
- Chọn dải dữ liệu từ A1:C.
- Nhấp vào Insert > Pivot table.
- Kiểm tra “Existing sheet”, nhập E1 và nhấp vào nút Create.
- Trong bảng điều chỉnh bảng tổng hợp, thêm “Date” vào nhóm hàng (Rows) và “Description” vào nhóm cột (Columns). Đặt “Quantity” phía dưới giá trị (Values).
- Dưới mục Filter, thêm “Date” và chọn Filter by Condition > Is not empty.
- Nhấp vào nút OK.
Và đây nó! Bảng tổng hợp của bạn đã sẵn sàng. Tuy nhiên, bạn có thể thấy rằng có các ô trống thay vì số 0 trong bảng.
Theo dữ liệu mẫu, giả sử chúng ta đã mua táo, cam và kiwi vào ngày 29/10/2023, và táo lại vào ngày 09/11/2023. Kết quả là, trong bảng tổng hợp, các ô dưới kiwi và cam trống cho ngày 09/11/2023.
Bây giờ, việc không có bản ghi cho kiwi và cam vào ngày 09/11/2023 cho thấy không có nhập liệu cho hai loại trái cây này vào ngày cụ thể đó. Nếu có hai bản ghi với số lượng là 0, ô tương ứng sẽ tự động được điền với số 0.
Nhưng làm thế nào chúng ta có thể xác định và tạo ra các bản ghi này để thêm vào dưới dữ liệu nguồn? Hãy để tôi giới thiệu cho bạn một công thức sẽ giúp bạn làm được điều đó.
Công thức để tìm các bản ghi bị thiếu trong bảng tổng hợp Google Sheets
Chúng ta sẽ tạo công thức bằng cách sử dụng trực tiếp bảng tổng hợp, chứ không phải dữ liệu nguồn. Công thức này yêu cầu ba trường: Rows, Columns và Values. Dưới đây là công thức:
=ArrayFormula(LET(
_rows, E3:E,
_columns, F2:I2,
_Values, F3:I,
IFERROR(SPLIT(TOCOL(FILTER(
IF(_values="",_rows&"|"&_columns,),
_rows<>""
),1),"|"))))Công thức này cho phép bạn điền vào các bản ghi bị thiếu và thay thế các ô trống bằng số 0 trong bảng tổng hợp.
Lưu ý:
- Nếu giá trị dưới mục Rows là ngày thì công thức sẽ trả về giá trị ngày. Chỉ cần chọn các ô và áp dụng Format > Number > Date.
- Nếu bạn thêm nhiều hạng mục hơn vào dữ liệu nguồn, độ rộng của bảng tổng hợp sẽ tăng. Trong trường hợp này, cần chỉnh sửa công thức thủ công.
Và đó là nó! Bằng cách sử dụng công thức này, bạn có thể điền các ô trống bằng số 0 trong bảng tổng hợp của bạn.
Bước cuối: Điền các ô trống bằng 0 trong bảng tổng hợp Google Sheets của bạn
Để hoàn thành quá trình, sao chép kết quả của công thức và dán chúng dưới dữ liệu nguồn của bạn, để trống một số hàng giữa chúng. Đảm bảo đặt các ngày (Rows) dưới cột A và tên các loại trái cây (Columns) dưới cột B. Điều này sẽ điền các ô trống bằng số 0 trong bảng tổng hợp và làm cho kết quả của công thức trở thành rỗng.
Để dán dữ liệu đã sao chép dưới dạng giá trị, làm theo các bước sau:
- Nhấp chuột phải vào nơi bạn muốn dán dữ liệu.
- Di chuột qua “Paste special” trong menu ngữ cảnh.
- Chọn “Paste values only”.
Và đó là xong! Bạn đã thành công điền các ô trống bằng 0 trong bảng tổng hợp Google Sheets của bạn.
Kết luận
Tôi hy vọng hướng dẫn này đã giúp bạn hiểu cách khắc phục các hạn chế của bảng tổng hợp trong Google Sheets và điền các ô trống bằng 0. Để giúp bạn hiểu quy trình tốt hơn, tôi đã chia sẻ một bảng mẫu dưới đây với hai bảng tổng hợp.
Hãy nhớ rằng cần cẩn trọng khi tự động điền các ô trống bằng số 0 trong các bảng tổng hợp. Ô trống cho thấy không có dữ liệu tại một điểm cụ thể trong thời gian, và việc điền sai có thể ảnh hưởng đến quyết định.
Với sự mạnh mẽ của bảng tổng hợp trong Google Sheets, hãy khám phá và sử dụng giải pháp này để nâng cao phân tích dữ liệu của bạn. Nếu có bất kỳ câu hỏi nào, đừng ngần ngại liên hệ với chúng tôi. Chúc bạn phân tích dữ liệu vui vẻ!
