Việc thay thế ô trống bằng 0 trong QUERY Pivot trên Google Sheets có thể nâng cao tính đọc và hiểu dữ liệu. Điều này có thể được thực hiện một cách dễ dàng bằng cách sử dụng các công thức hiện đại của Google Sheets như LET hoặc MAP.
Dữ liệu mẫu và Công thức QUERY: Thiết lập bảng mẫu của bạn
Hãy làm việc với một tập dữ liệu mẫu tối thiểu bao gồm ba loại dữ liệu: Ngày, Văn bản và Số.
Trong tập dữ liệu mẫu được cung cấp, cột A chứa ngày thanh toán, cột B chứa mặt hàng hoặc loại chi phí, và cột C chứa số tiền tương ứng.
Công thức QUERY sau đây trong ô E1 tạo ra một bảng Pivot, sắp xếp các ngày thanh toán theo hàng, các mặt hàng theo cột và tổng hợp số tiền thanh toán.
=QUERY(A1:C, “Select A, Sum(C) where A is not null group by A Pivot B”, 1)
Hãy xem hai chấm xanh (được đặt bằng tay) trong kết quả của QUERY Pivot. Làm thế nào để chúng ta điền ô trống đó bằng số 0? Tất nhiên, chúng ta không thể nhập số 0 vào các ô đó vì điều đó sẽ làm sai công thức QUERY. Hãy tìm hiểu các giải pháp của chúng ta.
Thay thế ô trống bằng 0 trong QUERY Pivot bằng cách sử dụng chức năng MAP
Chức năng MAP là một chức năng trợ giúp LAMBDA hữu ích trong Google Sheets, cho phép chúng ta ánh xạ từng giá trị trong một mảng thành giá trị mới.
Trong ngữ cảnh này, mảng là kết quả của một công thức QUERY, chẳng hạn như một bảng Pivot. Bằng cách sử dụng chức năng MAP, chúng ta có thể lặp lại từng giá trị trong bảng Pivot và thay thế bất kỳ ô trống nào bằng số 0.
Cú pháp:
MAP(array1, [array2, …], lambda)
Công thức chung:
=MAP(array1, lambda(cell_value, IF(cell_value=””, 0, cell_value)))
Thay đổi đơn giản array1 bằng công thức QUERY cụ thể của bạn.
Ví dụ, trong trường hợp của tôi, công thức để thay thế ô trống bằng giá trị 0 sẽ là:
=MAP(QUERY(A1:C, “Select A, Sum(C) where A is not null group by A Pivot B”, 1), lambda(cell_value, IF(cell_value=””, 0, cell_value)))
Lưu ý: Công thức sẽ chuyển đổi các ngày thành giá trị ngày. Do đó, hãy chọn các ngày trong bảng Pivot và áp dụng Format > Number > Date.
Công thức này hiệu quả biến đổi kết quả QUERY đã xác định, thay thế bất kỳ ô trống nào bằng 0 trong khi để lại các giá trị khác không bị ảnh hưởng.
Phân tích công thức:
- QUERY(A1:C, “Select A, Sum(C) where A is not null group by A Pivot B”, 1): Đây là mảng hoặc phạm vi các giá trị mà bạn muốn xử lý.
- lambda: Đây là một hàm xác định việc chuyển đổi sẽ được áp dụng cho mỗi phần tử trong mảng.
- cell_value: Đây là tên do người dùng xác định bên trong hàm lambda, đại diện cho từng giá trị ô trong mảng.
- IF(cell_value=””, 0, cell_value): Đây là kiểm tra logic IF trong hàm lambda. Nó lấy từng giá trị ô từ QUERY Pivot làm đầu vào và áp dụng logic sau đây: IF(cell_value=””, 0, cell_value)
Thay thế ô trống bằng 0 trong QUERY Pivot bằng cách sử dụng chức năng LET
Chức năng LET giúp đánh giá các biểu thức công thức bằng cách sử dụng các đối số được đặt tên.
Cú pháp:
LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)
Công thức chung:
LET(name1, value_expression1, ArrayFormula(IF(name1=””, 0, name1)))
Trong công thức chung này, thay thế value_expression1 bằng công thức QUERY của bạn.
Ví dụ, để thay thế các ô trống bằng số 0 trong một QUERY Pivot, bạn có thể sử dụng công thức sau:
=LET(name1, QUERY(A1:C, “Select A, Sum(C) where A is not null group by A Pivot B”, 1), ArrayFormula(IF(name1=””, 0, name1)))
Giống như công thức MAP, ở đây bạn cũng cần định dạng các giá trị ngày thành ngày trong kết quả Pivot.
Phân tích công thức:
- name1: Đây là tên được gán cho công thức QUERY (value_expression1). Bạn có thể chọn một tên có ý nghĩa hơn, chẳng hạn như “query_formula”.
- QUERY(A1:C, “SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B”, 1): Công thức (value_expression1) của bạn, kết quả của nó có thể được tham chiếu sau này với name1 được khai báo trước.
- ArrayFormula(IF(name1=””, 0, name1)): Biểu thức này được đánh giá bằng cách sử dụng chức năng LET. Nó kiểm tra xem giá trị của name1 có phải là một chuỗi rỗng hay không. Nếu đúng, nó trả về 0; nếu không, nó trả về giá trị gốc của name1.
Kết luận
Bây giờ bạn đã có hai giải pháp để thay thế ô trống bằng 0 trong Query Pivot, bạn có thể cảm thấy một chút không chắc chắn về việc chọn giải pháp nào.
Hãy chọn một trong hai, vì cả hai đều là các công thức đơn giản và tối ưu về hiệu suất. Đáng chú ý rằng cả hai công thức đều sẽ chuyển đổi ngày / thời điểm thành giá trị ngày. Chỉ cần đảm bảo áp dụng định dạng phù hợp từ trình đơn Format bằng cách chọn các giá trị ngày đó.
Liên quan:
- Cách điền các ô trống bằng 0 trong bảng Pivot trên Google Sheets
- Cách sử dụng hàm QUERY tương tự như bảng Pivot trong Google Sheets
- Cách định dạng hàng tiêu đề Query Pivot trong Google Sheets
- Cách Pivot nhiều cột trong Query trên Google Sheets
- Cách giữ lại tất cả các nhãn cột trong Query Pivot trên Google Sheets
