Trông như mệnh lệnh DISTINCT không phải là một phần của Truy vấn Google Sheets. Vậy làm thế nào để chúng ta có thể tính tổng lượng giá trị khác nhau trên Google Sheets?
Tôi có một công thức cho bạn sử dụng trong Google Spreadsheets (không phải trong Excel Spreadsheets).
Trước tiên, hãy hiểu về lượng giá trị khác nhau tích lũy. Vì vậy, tôi sử dụng bảng dưới đây từ B1:C để giúp bạn hiểu rõ hơn.
Có một số ngày liên tiếp trong cột đầu tiên. Cột thứ hai chứa một số chuỗi (tên công ty).
Dữ liệu mẫu của tôi nằm trong B2:C11 và số lượng ước tính khác nhau từ dữ liệu này nằm trong E2:F5. Tôi đã tính toán số lượng khác nhau tích lũy của các công ty (cột B) như sau.
Nếu một công ty (tôi chỉ sử dụng một số chữ cái đơn giản để đại diện cho các công ty, bạn có thể sử dụng tên công ty thực) lặp lại vào một ngày sau, nó sẽ bị bỏ qua trong số lượng chạy.
Ví dụ, các công ty “A” và “B” được lặp lại trong các ô C5 và C6 (02/05/2020). Nó đã có trong C2 và C3 (01/05/2020).
Vì vậy, số lượng khác nhau tích lũy sẽ là giống nhau (3) vào ngày 01/05/2020 và 02/05/2020.
Nếu một công ty lặp lại vào cùng một ngày (không nằm trong dữ liệu mẫu của chúng tôi), ví dụ, “A” hai lần vào ngày 01/05/2020, nó chỉ được tính một lần.
Vui lòng tham khảo ảnh chụp màn hình dưới đây để hiểu cách tính tổng giá trị khác nhau tích lũy được tính toán. Cột D chứa lượng đếm khác nhau và E chứa lượng khác nhau tích lũy.
Công thức Lượng Đếm Tích Lũy (Chạy) Giá Trị Đặc Biệt trong Google Sheets
Ở đây, trong một số bước đếm, bạn có thể học cách viết một công thức mảng trong Google Sheets cho lượng đếm tích lũy aka chạy giá trị đặc biệt.
Có một số hàm được liên quan. Hãy tập trung vào kết quả thay vì các hàm đã liên quan. Sau đó, bạn có thể học về các hàm liên quan sau từ hướng dẫn về chức năng Google Sheets của tôi.
Trước tiên, mở một file mới (Google Sheets). Tham khảo ảnh trên đây cho dữ liệu mẫu trong B1:C11. Tạo nó trong cùng khoảng trong bảng là của bạn.
Sau đó, làm theo các bước được giải thích một cách chi tiết dưới từng tiêu đề khác nhau.
Loại bỏ hàng không khác biệt bằng cách Sắp xếp và Chèn Cột Số
Tôi đã làm nổi bật các giá trị đặc biệt cho việc tham khảo của bạn trong bảng trên. Xem cột C trong bảng.
Chúng ta phải loại bỏ các hàng 5, 6 và 10 để làm cho bảng trở nên đặc biệt. Hãy làm như vậy với công thức kết hợp SORTN và ROW.
Đây là công thức thứ nhất để tạo ra lượng đếm tích lũy / chạy lượng đếm giá trị đặc biệt trong Google Sheets.
=SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1)
Chèn công thức SORTN này loại bỏ các giá trị không đặc biệt trong ô E2 (nó chèn một cột số luôn).
Giải thích công thức
Trong đó, {B2:B,row(B2:B)^0,C2:C} tạo một bảng mới chứa một cột bổ sung với giá trị số (số 1 trong mỗi hàng). Công thức ROW được sử dụng để trả về cột bổ sung đó ở giữa hai cột B và C.
Đây là phạm vi trong SORTN.
Cú pháp SORTN (chỉ để tham khảo nhanh của bạn): SORTN (range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])
Trong công thức chỉ cần bỏ qua 9^9 (số lớn biểu thị ‘n’) và 2 (chế độ tie) và số 1 (sắp xếp theo thứ tự gọi là is_ascending). Nó sẽ giống nhau ngay cả khi phạm vi dữ liệu của bạn khác.
Nhưng bạn phải hiểu việc sử dụng số 3 (cột sắp xếp). Nó thực sự tham chiếu đến cột C chứa tên công ty. Vì vậy, nó phải là 2, phải không?
Không phải! Vì chúng ta đã sử dụng phạm vi là {B2:B, row(B2:B)^0, C2:C}, cột C đã trở thành cột thứ ba.
Bây giờ đến bước thứ hai của việc mã hóa công thức cho lượng đếm tích lũy giá trị đặc biệt trong Google Sheets.
Array_Constrain để giới hạn Hai Cột Đầu Tiên
Trong bước này thứ hai, chúng ta sẽ loại bỏ cột thứ ba (tên công ty) khỏi đầu ra trên. Chỉ cần sử dụng hàm Array_Constrain như sau. Tôi đang thay đổi công thức E2 luôn.
Công thức bước # 2 cho lượng đếm độc lập tích lũy:
=array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2)
Bây giờ chúng ta có cột ngày và cột số. Các tên công ty đã bị loại bỏ bởi Array_Constrain.
Chuẩn bị Dữ liệu để Tạo ra Lượng Đếm Tích Lũy Giá Trị Đặc Biệt Trên Google Sheets
Trong bước đầu tiên, chúng ta đã loại bỏ một số hàng không mong muốn (các hàng trùng lặp) để làm cho dữ liệu trở nên độc nhất. Chúng ta muốn các hàng đã xóa này được thêm vào dưới đầu ra công thức bước # 2 một cách “độc đáo”.
“Độc đáo”?
Vâng! Cột đầu tiên phải chứa ngày từ các hàng đã xóa và chúng tôi muốn 0 đối với mỗi ngày đã xóa trong cột thứ hai. Bạn sẽ hiểu mục đích của điều này trong bước tiếp theo.
Ở đây, chúng ta có thể tuân theo logic dưới đây bằng hai công thức UNIQUE.
Trích xuất tất cả các ngày duy nhất từ cột đầu tiên B2: B bằng unique(B2:B) và chèn một cột giá trị lỗi làm cột thứ hai của đầu ra này bằng unique(B2:B) / 0.
Sử dụng IFERROR để chuyển đổi các giá trị lỗi thành 0. Đây là công thức bước # 3 trong ô G2.
=ArrayFormula(iferror({unique(B2:B),unique(B2:B)/0},0))
Ghi chú: G2: G sẽ chứa giá trị ngày thay vì ngày. Chỉ bỏ qua nó. Tôi đã định dạng nó thành ngày bằng cách chọn G2: G và nhấp vào Định dạng> Số> Ngày.
Thực ra, chúng ta chỉ muốn trích xuất các ngày từ hàng số 5, 6 và 10. Nhưng công thức trả về tất cả các ngày. Điều này không phải là một vấn đề vì các giá trị đối với tất cả các ngày đều là 0.
Các bước tiếp theo rất quan trọng. Những gì chúng ta muốn là lượng đếm tích lũy của các giá trị riêng lẻ. Để làm được điều đó, trước tiên, chúng ta nên tạo ra một bảng lượng đếm giá trị đặc biệt. Làm thế nào?
Nhóm Truy vấn để Tạo ra Lượng Đếm Đặc Biệt
Trong các bước trên, chúng ta đã tạo ra dữ liệu cần thiết để trả về lượng đếm chạy các giá trị đặc biệt (bằng cách sử dụng Truy vấn) trong Google Sheets.
Hãy kết hợp hai bảng E2: F và G2: H (tôi có nghĩa là công thức bước 2 và công thức bước # 3).
=ArrayFormula( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)} )
Sau đó, nhóm nó bằng cách sử dụng Truy vấn.
Có hai cột. Cột đầu tiên là cột ngày và cột thứ hai là cột số học chứa 1 hoặc 0. Hãy nhóm cột đầu tiên và đếm cột thứ hai.
Vì vậy chúng ta sẽ có lượng đếm đặc biệt. Đây là công thức bước # 4 (sửa công thức E2 như sau).
=ArrayFormula( query( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)}, "Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''" ) )
Vì vậy chúng ta sẽ có lượng đếm đặc biệt, không phải lượng đếm chạy hoặc lượng đếm đặc biệt tích lũy.
Công thức Google Sheets để Tính Tổng Lượng Giá Trị Đặc Biệt Tích Lũy
Chúng ta đã gần hoàn thành nó! Tổng số chạy của các giá trị trong F2:F sẽ là lượng đếm tích lũy đã nói trên của dữ liệu trong B1: C.
Điều đó có nghĩa là cột F2: F (lượng đếm đặc biệt) sẽ vẫn được giữ làm một cột trợ giúp. Nhưng đừng lo lắng! Sau đó chúng ta có thể loại bỏ nó luôn.
Chúng ta có thể sử dụng MMULT hoặc SUMIF cho tổng chạy. Vì SUMIF không cho phép chúng ta sử dụng biểu thức như sum_range (sau đó chúng ta sẽ sử dụng biểu thức thay vì F2: F), tôi chọn MMULT.
Thông báo (Ngoài vấn đề): Do vấn đề về sum_range trên, Sumif không thể được sử dụng như một công thức tùy chỉnh trong Bảng tổng hợp cũng như trong lệnh Lọc. Tôi nghĩ rằng nhóm phát triển (kỹ sư) Google Sheets phải giải quyết vấn đề này để đưa Sumif đi xa hơn.
Trong ô G2, hãy sử dụng công thức tổng chạy / tích lũy dưới đây sẽ tạo ra lượng chạy giá trị đặc biệt (công thức bước # 5).
=ArrayFormula( if(len(E2:E), MMULT( IF(ROW(E2:E)>=TRANSPOSE(ROW(E2:E))=TRUE,1,0), n(F2:F) ), ) )
Để giải thích công thức này, vui lòng tham khảo phần “Tài nguyên” dưới đây. Chọn hướng dẫn cuối cùng. Nói rằng Excel. Đừng lo lắng. Công thức trong hướng dẫn đó cũng áp dụng cho Google Sheets.
Đến lúc hiển thị kết quả của công thức trên.
Chúng ta đã có kết quả cần thiết! Bây giờ tôi sẽ loại bỏ cột đếm đặc biệt F và chỉ giữ lại cột chạy tích lũy đặc biệt G.
Loại bỏ Cột Bổ Sung từ Công Thức Chạy Lượng Đếm Đặc Biệt (Công Thức Cuối Cùng)
Công thức E2 (công thức bước # 4) trả về hai cột – cột ngày và cột đếm đặc biệt. Hãy tách hoặc chẻ nó thành hai cột.
Cột Thứ Nhất (trong E2) – Truy vấn Cột 1
Tôi đã sử dụng một Truy vấn khác với công thức bước # 4 để trích xuất cột đầu tiên (thay thế, chúng ta có thể sử dụng Array_Constrain).
=query( ARRAYFORMULA( query( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)}, "Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''" ) ), "Select Col1" )
Chỉ cần hiểu rằng chúng ta có thể trích xuất cột thứ hai bằng cách thay đổi phần cuối cùng của công thức trên từ Col1 thành Col2 như dưới đây.
Cột Hai – Truy vấn cột 2:
=query( ARRAYFORMULA( query( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)}, "Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''" ) ), "Select Col2" )
Thay thế F2: F trong công thức tổng chạy bằng Truy vấn trên. Bạn sẽ nhận được lỗi #VALUE do kích thước ma trận không tương thích.
Vì chúng ta đã sử dụng E2: E trong công thức G2 là một phạm vi mở nhưng công thức đã thay thế F2: F trả về một phạm vi bị giới hạn / đóng.
Vì vậy, thay thế E2: E bằng indirect(“E2:E”&counta(unique(B2:B))+1) (hai lần trong công thức G2 – xem phần làm nổi bật màu xanh lam trên hình ảnh công thức dưới đây).
Cũng loại bỏ If(Len(E2:E) vì nó không cần thiết trong một phạm vi đã đóng. Vui lòng tham khảo hình ảnh bên dưới (làm nổi bật màu vàng).
Công thức cuối cùng để tạo ra lượng đếm tích lũy giá trị đặc biệt trong Google Sheets:
=ArrayFormula(MMULT(IF(ROW(indirect("E2:E"&counta(unique(B2:B))+1))>=TRANSPOSE(ROW(indirect("E2:E"&counta(unique(B2:B))+1)))=TRUE,1,0),n(query(ARRAYFORMULA(query({array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2);iferror({unique(B2:B),unique(B2:B)/0},0)},"Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")),"Select Col2"))))
Xem trạng thái bảng mẫu 7820
Tài nguyên:
- Normal and Array-Based Running Total Formula in Google Sheets
- Running Count of Multiple Values in a List in Google Sheets
- Running Count in Google Sheets – Formula Examples
- How to Calculate Running Balance in Google Sheets
- Array Formula for Conditional Running Total in Google Sheets
- Running Total Array Formula in Excel [Formula Options]
