Bạn đang tìm cách sử dụng công thức mảng COUNTUNIQUEIFS trong Google Sheets?
Rất tiếc, chức năng riêng cho điều này không hỗ trợ mở rộng kết quả. Nhưng, với sự giúp đỡ của một công thức kết hợp, chúng ta có thể có được hàng COUNTUNIQUEIFS trong Google Sheets.
Hãy làm rõ hơn với một ví dụ.
Giả sử bạn có năm sản phẩm để bán và bạn đang theo dõi doanh số bán hàng trong một Sheet như ngày bán, sản phẩm được bán và số lượng đã bán.
Bạn có thể xử lý dữ liệu như vậy theo nhiều cách khác nhau trong Google Sheets. Một trong số đó là tìm số lượng sản phẩm duy nhất được bán hàng ngày.
Tôi có nghĩa là, trong số năm sản phẩm, có bao nhiêu sản phẩm duy nhất được bán hàng ngày.
Điều đó có ý nghĩa gì?
Nếu một mặt hàng được gửi đi nhiều hơn một lần trong một ngày, chỉ tính nó là 1.
Công thức để có được Row-Wise COUNTUNIQUEIFS trong Google Sheets
Trong ví dụ dưới đây, chúng ta sẽ xem xét hai cột đầu tiên (phạm vi ô A2: B) cho bài kiểm tra.
Theo dữ liệu này, có hai mặt hàng duy nhất được bán vào ngày 01/10/2021, ba mặt hàng vào ngày 03/10/2021 và mỗi ngày một vào ngày 04/10/2021 và 05/10/2021.
Làm thế nào để thực hiện tính toán này?
1. Công thức không mảng
Chúng ta có thể làm điều này trong hai bước.
-
Trong ô E2, chèn
=unique(A2:A)
để trả về các ngày DUY NHẤT. Điều này đóng vai trò như tiêu chí. -
Trong ô F2, chèn
=COUNTUNIQUEIFS($B$2:$B,$A$2:$A,E2)
và kéo xuống.
Cú pháp:
COUNTUNIQUEIFS (phạm vi, phạm vi_tiêu_khối 1, tiêu_khối_1, [phạm vi_tiêu_khối_2, ...], [tiêu_khối_2, ...])
Do đó, chúng tôi có thể có được hàng COUNTUNIQUEIFS theo hàng trong Google Sheets. Nhưng đó không phải là một công thức mảng.
Chúng ta không thể sử dụng toàn bộ tiêu chí trong phạm vi ô E2: E5 như dưới đây.
=ArrayFormula(COUNTUNIQUEIFS($B$2:$B,$A$2:$A, **E2:E5**))
Nó sẽ không mở rộng kết quả COUNTUNIQUEIFS vào các hàng liền kề.
Nếu bạn muốn có một công thức mảng COUNTUNIQUEIFS theo hàng trong Google Sheets, vui lòng làm theo các bước dưới đây.
2. Công thức Mảng
Hãy để ô E2: F trống/vô nghĩa vì chúng ta muốn chèn công thức mảng tự mở rộng vào ô E2.
Sau khi hoàn thành, chèn công thức kết hợp QUERY và SORTN sau vào ô F2.
=QUERY( sortn(A2:B,9^9,2,B2:B&A2:A,1), "Select Col1,count(Col2) where Col2 <>'' group by Col1 label Count(Col2)''",0 )
Công thức trên hoạt động như một công thức mảng COUNTUNIQUEIFS theo hàng thay thế trong Google Sheets.
Giải thích công thức
Chúng tôi đã sử dụng hai hàm Google Sheets trong công thức kết hợp này. Đó là SORTN và QUERY.
SORTN – loại bỏ bản sao dựa trên ngày và mục. Nếu một mục được bán nhiều lần trong một ngày, chỉ có mục xuất hiện đầu tiên được giữ lại.
Cú pháp: SORTN (phạm vi, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, ...], [is_ascending2, ...])
QUERY có nhiệm vụ tóm tắt dữ liệu này, và đó là công thức cuối cùng.
Cú pháp: QUERY (data, truy vấn, [headers])
Công thức trả về dữ liệu hai cột chứa cột ngày và cột đếm.
Mẹo bổ sung – Cách gán hàng COUNTUNIQUEIFS theo hàng đối với các ngày đã nhập
Trong phần công thức không mảng, chúng tôi đã đặt trước các ngày trong cột E và sau đó gán hàng COUNTUNIQUEIFS theo hàng đối với những ngày đó trong cột F.
Nhưng, ở đó, chúng tôi đã sử dụng công thức sao chép-dán vào ô F2.
Liệu chúng ta có thể sử dụng công thức mảng trong ô F2 thay vào đó không?
Có! Nó không phức tạp như vậy.
Chúng ta có thể sử dụng VLOOKUP với Truy vấn trước của mình (hoạt động như phạm vi trong Vlookup) như dưới đây.
Cú pháp: VLOOKUP (search_key, range, index, [is_sorted])
Điều kiện tiên quyết: Trong ô E2, chèn
=unique(A2:A)
(hoạt động như “search_key” trong Vlookup).
Sau đó, trong ô F2, chèn công thức mảng COUNTUNIQUEIFS theo hàng sau đây.
=ArrayFormula( IFNA( vlookup( E2:E, query( sortn(A2:B,9^9,2,B2:B&A2:A,1), "Select Col1,count(Col2) where Col2 <>'' group by Col1",0 ) ,2,0) ) )
Chúc bạn có một niềm vui thúc đẩy!
