Việc thay thế ô trống bằng số 0 trong một truy vấn Pivot trong Google Sheets có thể cải thiện khả năng đọc và hiểu dữ liệu. Điều này có thể được thực hiện dễ dàng bằng cách sử dụng các hàm Sheets hiện đại như LET hoặc MAP.
Đáng lưu ý là bạn không cần phải hiểu hoàn toàn cấu trúc của công thức QUERY để áp dụng mẹo này. Dù bạn đã tự tạo công thức hay không, điều đó không quan trọng. Bạn vẫn có thể thay thế ô trống bằng số 0 trong kết quả truy vấn Pivot mà không cần chỉnh sửa trực tiếp công thức. Thay vào đó, chúng ta sẽ sử dụng một bọc để thay thế ô trống bằng số 0.
Hãy khám phá một tập dữ liệu mẫu bao gồm các cột văn bản, số và ngày. Điều này sẽ giúp bạn hiểu cách công thức xử lý các cột văn bản hoặc ngày trong quá trình thay thế ô trống bằng số 0.
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 có 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 (vui lòng tham khảo hình ảnh dưới đây), cột A chứa ngày thanh toán, cột B chứa mục hoặc loại chi phí và cột C chứa số tiền tương ứng của chúng.
{Image}(https://updf.com/wp-content/uploads/2023/05/copy-the-google-sheets-data.jpg)
Công thức QUERY sau đây trong ô E1 sẽ 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ục 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 dấu chấm xanh (được đặt thủ công) trong kết quả Pivot của QUERY. Làm thế nào chúng ta có thể điền số 0 vào những ô trống đó? Tất nhiên, chúng ta không thể nhập số 0 vào những ô đó vì điều đó sẽ làm hỏng công thức QUERY. Hãy đi tìm giải pháp.
Bạn có thể quan tâm: Tìm hiểu về Hàm Truy vấn của Google Sheets: Hướng dẫn từng bước.
Thay thế ô trống bằng số 0 trong một truy vấn Pivot bằng cách sử dụng hàm MAP
Hàm MAP là một hàm 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 một 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 hàm MAP, chúng ta có thể lặp qua 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 tổng quát:
=MAP(array1, lambda(cell_value, IF(cell_value=””, 0, cell_value)))
Chỉ cần thay thế 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)))
{Image}(https://updf.com/wp-content/uploads/2023/11/query-pivot-with-0-in-blanks.jpg)
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 Định dạng> Số> Ngày.
Công thức này hiệu quả biến đổi kết quả QUERY cụ thể, thay thế bất kỳ ô trống nào bằng số 0 trong khi giữ nguyên các giá trị khác.
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 điều chỉnh.
- lambda: Đây là một hàm xác định phép biến đổi đượ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 trong hàm lambda, đại diện cho mỗi giá trị ô trong mảng.
- IF(cell_value=””, 0, cell_value): Kiểm tra logic IF này là hàm lambda. Nó lấy mỗi cell_value từ Pivot QUERY làm đầu vào và áp dụng logic sau: IF(cell_value=””, 0, cell_value)
Thay thế ô trống bằng số 0 trong một truy vấn Pivot bằng cách sử dụng hàm LET
Hàm 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 tổng quát:
LET(name1, value_expression1, ArrayFormula(IF(name1=””, 0, name1)))
Trong công thức tổng quát này, hãy thay 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 truy vấn 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)))
Tương tự như công thức MAP, ở đây 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, 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) mà kết quả có thể được tham chiếu sau đó bằng 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 hàm LET. Nó kiểm tra xem giá trị của name1 có phải là một chuỗi trống hay không. Nếu đúng, nó trả về 0; nếu không, nó trả về giá trị ban đầu của name1.
Kết luận
Bây giờ bạn đã có hai giải pháp để thay thế ô trống bằng số 0 trong truy vấn Pivot, bạn có thể thấy mình hơi mơ hồ về cách lựa chọn giữa hai công thức này.
Hãy tự do chọn một trong hai, vì cả hai công thức đều đơn giản và tập trung vào hiệu suất. Đáng chú ý là cả hai công thức đều chuyển đổi các ngày / thời gian thành giá trị ngày. Chỉ cần đảm bảo áp dụng định dạng phù hợp từ menu Định dạng bằng cách chọn các giá trị ngày đó.
