Học cách dễ dàng đếm các giá trị duy nhất bằng cách sử dụng hàm QUERY trong Google Sheets với hướng dẫn từng bước sau đây.
Đếm các giá trị duy nhất sẽ giúp bạn hiểu rõ hơn về dữ liệu. Ví dụ, nó giúp xác định số lượng bán hàng sản phẩm duy nhất trong mỗi ngày.
Công thức QUERY thông thường thường cung cấp số lượng sản phẩm mỗi ngày.
Trong hướng dẫn này, tôi sẽ hướng dẫn bạn hai phương pháp để đếm các giá trị duy nhất trong Google Sheets QUERY. Một phương pháp liên quan đến một cột trợ giúp ảo với một bảng đếm liên tục, trong khi phương pháp khác sử dụng một phạm vi UNIQUE.
Tôi sẽ giải thích cả hai phương pháp và bạn có thể chọn phương pháp phù hợp với nhu cầu của bạn.
Giải mã Dữ liệu Mẫu
Mẫu bao gồm ba cột: Ngày, Mặt hàng và Số lượng, miêu tả số lượng hàng bán theo ngày của Quả táo, Chuối và Cam.
Vào ngày 10/11/2023, chúng ta có hai lần bán Quả táo và hai lần bán Cam. Trong khi số lượng bán hàng thực tế là 4, số lượng duy nhất là 2.
Bên cạnh việc đếm các giá trị duy nhất trong QUERY (truy vấn 1), chúng ta cũng cần tìm số lượng bán hàng trung bình (truy vấn 2). Làm thế nào chúng ta có thể đạt được điều này?
Như đã đề cập ở đầu hướng dẫn này, có hai phương pháp và đây là cách thực hiện.
Đếm giá trị duy nhất trong Google Sheets bằng cách sử dụng QUERY với UNIQUE
Trước khi tôi đào sâu vào công thức, hãy để tôi giải thích logic.
Đầu tiên, chúng ta cần thu được các giá trị duy nhất từ hai cột: cột chúng ta muốn nhóm, đó là cột ngày, và cột để đếm các mục duy nhất.
Trong khi chúng ta có thể sử dụng =UNIQUE(A2:B) để đạt được điều này, tôi thích một phương pháp linh hoạt hơn, đặc biệt là khi các cột nhóm và duy nhất có thể không nhất thiết phải kề nhau.
Việc chỉ định các phạm vi một cách riêng biệt là phương pháp lý tưởng. Vì vậy, tôi sẽ sử dụng công thức =UNIQUE(HSTACK(A2:A, B2:B)) để thu được các giá trị duy nhất từ hai cột này. Như bạn có thể biết, chức năng HSTACK trong Google Sheets được sử dụng để kết hợp hai hoặc nhiều phạm vi thành một phạm vi duy nhất.
Dữ liệu duy nhất này sẽ được sử dụng trong công thức QUERY để đếm các giá trị duy nhất. Đây là công thức QUERY:
=QUERY(UNIQUE(HSTACK(A2:A, B2:B)), “select Col1, count(Col2) where Col1 is not null group by Col1”)
Công thức này trong ô E2 sẽ trả về kết quả được hiển thị trong “truy vấn 1” trong bức ảnh chụp màn hình của chúng tôi.
Để thu được kết quả được hiển thị như “truy vấn 2”, đầu tiên, hãy chèn công thức trên vào ô E8. Sau đó, sử dụng một truy vấn QUERY khác trong ô G8 để tính trung bình của cột cuối cùng (Số lượng). Trong trường hợp này, không cần sử dụng hàm UNIQUE. Đây là công thức:
=QUERY(HSTACK(A2:A, C2:C), “select avg(Col2) where Col1 is not null group by Col1”)
Cấu trúc của Công thức:
Cú pháp của Hàm QUERY:
QUERY(data, query, [headers])
Trong trường hợp công thức “truy vấn 1”:
- data: Bộ dữ liệu mà truy vấn được thực hiện. Trong trường hợp này, đó là danh sách duy nhất của các sự kết hợp giữa ngày và mặt hàng được thu được bằng cách áp dụng hàm UNIQUE cho phạm vi được xếp chồng theo chiều ngang sử dụng HSTACK(A2:A, B2:B).
- query: Chuỗi truy vấn xác định phép toán được thực hiện trên dữ liệu. Trong ví dụ cung cấp, truy vấn là “select Col1, count(Col2) where Col1 is not null group by Col1”. Truy vấn này chọn các giá trị duy nhất trong Col1 (ngày) và đếm số lần xuất hiện của mỗi giá trị trong Col2 (sản phẩm), loại bỏ các giá trị null trong Col1 và nhóm kết quả theo Col1.
Trong công thức “truy vấn 2”:
- data: Bộ dữ liệu mà truy vấn được thực hiện. Trong trường hợp này, đó là phạm vi được xếp chồng theo chiều ngang sử dụng HSTACK(A2:A, C2:C).
- query: Trong trường hợp này, chuỗi truy vấn là “select avg(Col2) where Col1 is not null group by Col1”. Chuỗi truy vấn này sẽ trả về một cột duy nhất chứa giá trị trung bình của các giá trị trong Col2 cho mỗi giá trị trong Col1.
Sử dụng trạng thái đếm để đếm giá trị duy nhất trong Google Sheets QUERY
Phương pháp này liên quan đến việc sử dụng một cột trợ giúp, có thể được thực hiện dưới dạng một cột vật lý hoặc ảo. Để bắt đầu, chúng ta cần thiết lập cột trợ giúp.
Cột trợ giúp (Trạng thái đếm)
Để trả về số lượng đếm liên tiếp của các giá trị trong một cột, chẳng hạn như A2:A, chúng ta có thể sử dụng công thức COUNTIFS mảng sau đây (trong ô D2):
=ArrayFormula(COUNTIFS(A2:A, A2:A, ROW(A2:A), “<=”&ROW(A2:A)))
Tuy nhiên, để tìm số lượng đếm liên tiếp của các cột ngày và mặt hàng, chúng ta cần kết hợp hai cột thành một cột duy nhất. Đây là công thức đếm liên tiếp cho điều đó:
=ArrayFormula(COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), “<=”&ROW(A2:A)))
Công thức này sẽ trả về 1 cho lần xuất hiện đầu tiên của mỗi sự kết hợp ngày và mặt hàng duy nhất, 2 cho lần xuất hiện thứ hai, và cứ như vậy.
Chúng ta sau đó có thể thay thế bất kỳ giá trị nào lớn hơn 1 bằng 0 để có được số lượng duy nhất của các sự kết hợp ngày và mặt hàng. Đây là công thức cho điều đó:
=ArrayFormula((COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), “<=”&ROW(A2:A))<2)*1)
Đếm giá trị duy nhất trong QUERY
Bạn có thể sử dụng cột trạng thái đếm (D2:D) theo hai cách trong truy vấn duy nhất của bạn: là một cột vật lý hoặc là một cột ảo. Trong cả hai trường hợp, bạn chỉ cần tổng của cột trạng thái đếm để có được số lượng duy nhất của các mặt hàng theo ngày.
Đây là công thức “truy vấn 1” (ô E2):
=QUERY(A2:D, “Select Col1, sum(Col4) where Col1 is not null group by Col1”)
Giải thích:
Công thức QUERY này tổng hợp các giá trị trong Col4 nhóm theo các giá trị duy nhất trong Col1, loại bỏ các giá trị null. Kết quả là một bảng hiển thị mỗi giá trị duy nhất trong Col1 với tổng tương ứng của các giá trị trong Col4.
Đây là công thức “truy vấn 2” (ô E8):
=QUERY(A2:D, “Select Col1, sum(Col4), avg(Col3) where Col1 is not null group by Col1”)
Giải thích:
Công thức QUERY này chọn các giá trị duy nhất trong Col1 cùng với tổng của các giá trị trong Col4 và trung bình các giá trị trong Col3. Kết quả được nhóm theo các giá trị duy nhất trong Col1, loại bỏ các giá trị null. Kết quả là một bảng hiển thị mỗi giá trị duy nhất trong Col1, tổng tương ứng trong Col4 và trung bình trong Col3.
Nếu bạn muốn sử dụng một cột trợ giúp ảo thay thế, hãy thay thế A2:D trong cả hai công thức bằng sau:
HSTACK(A2:D, ArrayFormula((COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), “<=”&ROW(A2:A))<2)*1))
Chúng tôi đã khám phá hai phương pháp khác nhau để đếm các giá trị duy nhất bằng cách sử dụng hàm QUERY trong Google Sheets.
Sự lựa chọn sử dụng QUERY trong ngữ cảnh này là để tạo ra một đầu ra dạng bảng. Hoặc bạn có thể chọn COUNTUNIQUEIFS và MAP Lambda lồng nhau để đạt được kết quả tương tự mà không cần sử dụng QUERY. Tuy nhiên, để đơn giản, tôi đã trình bày phương pháp QUERY.
Ngoài ra, yêu cầu đếm duy nhất của bạn sử dụng QUERY có thể khác nhau. Nếu bạn đang tìm kiếm một giải pháp phù hợp, xin vui lòng chia sẻ bảng mẫu của bạn với chúng tôi trong phần bình luận dưới đây.
Hy vọng bạn đã thích thảo luận về cách đếm duy nhất trong Google Sheets QUERY. Xin vui lòng cho tôi biết nếu bạn có bất kỳ câu hỏi hoặc ý kiến nào.
