Để lọc 3 giá trị hàng đầu trong mỗi nhóm trong báo cáo bảng tổng kết pivot trong Google Sheets, hiện tại chúng ta phải sử dụng trường công thức tùy chỉnh trong trình biên tập bảng tổng kết pivot. Không có tính năng pivot tích hợp cho việc đó.
Hướng dẫn này nhằm giúp bạn viết công thức cần thiết để sử dụng trong lĩnh vực công thức tùy chỉnh trong trình biên tập bảng tổng kết pivot.
Thực ra, chúng ta có thể sử dụng cùng một công thức (công thức mà chúng tôi sẽ viết) để lọc ‘n’ hàng đầu (ví dụ: hàng đầu 1, 2, 3, 4, 5, 6, … 10, và cứ thế) trong mỗi nhóm trong bảng tổng kết pivot.
Hướng dẫn này chứa hai công thức để sử dụng trong hai tình huống khác nhau. Tôi muốn nói là có hai loại báo cáo.
Hãy xem hai hình ảnh dưới đây cho hai loại báo cáo.
Báo cáo số 1 (Tổng kết doanh số theo công ty của 3 sản phẩm hàng đầu từ mỗi nhóm):
Báo cáo số 2 (Tổng kết doanh số của 3 sản phẩm hàng đầu từ mỗi nhóm):
Giải thích Báo cáo tổng kết pivot và Dữ liệu mẫu
Dữ liệu mẫu (trong Báo cáo số 1 và Báo cáo số 2 ở trên) thực ra là một phần của báo cáo doanh số sản phẩm nghiền.
Có hai mục bán hàng (doanh số sản phẩm).
- Cát màu đen 0-5 mm.
- Đá lót đường – Đá nghiền 0-50 mm.
Ba doanh số hàng đầu của mỗi sản phẩm như sau.
- Cát màu đen 0-5 mm – 800, 700 và 600.
- Đá lót đường – Đá nghiền 0-50 mm – 800, 500 và 300.
Chúng ta muốn lọc ra 3 giá trị hàng đầu này (doanh số) trong mỗi nhóm trong báo cáo bảng tổng kết pivot.
Báo cáo đầu tiên có ba cột trong dữ liệu mẫu – Sản phẩm, Công ty và Doanh số.
Vì vậy trong báo cáo bảng tổng kết đầu tiên (Báo cáo số 1), có một cột sản phẩm, một cột công ty sau đó là 3 giá trị hàng đầu (nếu có bất kỳ công ty xuất hiện hơn một lần trong 3 giá trị hàng đầu, thì các giá trị đó sẽ được tổng hợp).
Trong dữ liệu mẫu cho báo cáo bảng tổng kết thứ hai (Báo cáo số 2), có hai cột – Sản phẩm và Doanh số. Vì vậy, báo cáo bảng tổng kết chỉ có hai cột – Sản phẩm và tổng kết doanh số hàng đầu 3.
Hãy xem cách lọc 3 giá trị hàng đầu trong mỗi nhóm như trên trong báo cáo bảng tổng kết pivot trong Google Sheets.
Cách chọn 3 giá trị hàng đầu trong mỗi nhóm trong bảng tổng kết pivot trong Google Sheets
Trước khi bắt đầu viết công thức, hãy tạo báo cáo bảng tổng kết. Sau đó, chúng ta có thể viết và chèn công thức vào trường tương ứng trong trình biên tập bảng tổng kết.
Báo cáo bảng tổng kết
Tôi biết bạn quen thuộc với việc tạo báo cáo bảng tổng kết trong Google Sheets. Mặc dù vậy, tôi sẽ bao gồm các bước dưới đây cho những người mới.
Dưới đây là các bước cho báo cáo bảng tổng kết cần thiết để lọc 3 giá trị hàng đầu trong mỗi nhóm (sử dụng dữ liệu mẫu trong Báo cáo số 1 [vui lòng xem hình ảnh đầu tiên ở trên]).
- Chọn phạm vi A1: C14. Sau đó nhấp vào Dữ liệu> Bảng tổng kết.
- Kích hoạt / chọn “Bảng” hiện có vì chúng ta đang tạo báo cáo trong cùng một bảng chứa dữ liệu mẫu.
- Chọn ô E2 và nhấp vào “Tạo”.
Bạn sẽ nhận được một ‘đề cương’ báo cáo và bảng trình biên tập bảng tổng kết sẽ được mở trên màn hình.
Bây giờ làm theo các bước dưới đây để hoàn thành báo cáo.
- Trong bảng trình biên tập, nhấp vào “Thêm” phía sau “Các hàng” và chọn “Sản phẩm” (kích hoạt “Hiển thị tổng cộng”).
- Lặp lại bước trên và chọn “Công ty” (kích hoạt “Hiển thị tổng cộng”).
- Nhấp vào “Thêm” phía sau “Giá trị” và chọn “Doanh số”. Trong bước cuối cùng này, hãy đảm bảo rằng “TỔNG” được chọn bên dưới “Tóm tắt bởi” và “Hiển thị” được đặt thành “Mặc định”.
Bạn sẽ nhận được báo cáo bảng tổng kết như sau.
Từ bước này, làm sao để lọc ra 3 giá trị hàng đầu từ mỗi nhóm, tức là từ “Cát màu đen 0-5 mm” và “Đá lót đường – Đá nghiền 0-50 mm”?
Logic để lọc 3 giá trị hàng đầu từ mỗi nhóm trong Bảng tổng kết trong Google Sheets
Logic là như sau (nếu bạn thấy logic khó hiểu, bạn có thể bỏ qua phần logic này hoặc đọc sau khi hoàn thành viết công thức);
Chúng tôi sẽ sắp xếp sản phẩm theo doanh số theo thứ tự giảm dần. Tôi có nghĩa là sắp xếp cột “Sản phẩm” theo thứ tự tăng dần (A-Z) và sau đó sắp xếp cột “Doanh số” theo thứ tự giảm dần (Z-A). Chúng tôi sẽ thực hiện điều này bằng một công thức không thể hiện vật lý trên bảng tính.
Sau đó, chúng tôi sẽ sử dụng công thức mảng đếm chạy của tôi (RC) để trả về số chạy của “Sản phẩm”.
Bằng cách sử dụng bài kiểm tra logic IF, chúng tôi sẽ trả về các giá trị cho các hàng nơi số chạy nhỏ hơn hoặc bằng 3 hoặc “N”.
Bước tiếp theo là kết hợp các “Sản phẩm” và “Doanh số” tương ứng.
Cuối cùng, chúng tôi sẽ sử dụng Regexmatch các giá trị kết hợp trong phạm vi ban đầu A2: C trong bảng tổng kết.
Chúng tôi sẽ tuân theo logic trên để viết một công thức tùy chỉnh để sử dụng trong trình biên tập bảng tổng kết để lọc ra 3 giá trị hàng đầu từ mỗi nhóm trong báo cáo bảng tổng kết trong Google Sheets.
Viết công thức tùy chỉnh cho Báo cáo số 1
Tôi có một số bài hướng dẫn về số chạy. Đây là liên kết cụ thể để tìm hiểu cách làm tương tự như mục đích của chúng ta – Count tích lũy của tất cả các mục trong danh sách đã sắp xếp.
Đừng bị nhầm lẫn bởi “Đã sắp xếp” trong tiêu đề liên kết. Nó thực sự nhắc đến việc sắp xếp “Sản phẩm” chứ không phải “Doanh số”.
Bước 1 – Công thức RC
Trong ô D2, chèn công thức RC dưới đây (chúng tôi chỉ viết công thức trong bảng tính để sử dụng trong trình biên tập bảng tổng kết sau này).
=ARRAYFORMULA(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)))
Kết quả:
Tôi đã sử dụng một ArrayFormula dựa trên COUNTIFS cho số chạy. Xem số chạy khởi động lại trong kết quả khi “Sản phẩm” thay đổi.
Bước 2 – Sắp xếp số chạy (RC) theo doanh số
Đây là bước thứ hai để lọc ra 3 giá trị (“N”) doanh số hàng đầu trong mỗi nhóm trong báo cáo bảng tổng kết trong Google Sheets.
Ở đây, chúng tôi sẽ thay đổi nhẹ công thức trên để làm cho phạm vi đã sắp xếp theo “Sản phẩm” theo thứ tự A-Z và “Doanh số” theo thứ tự Z-A.
Đơn giản thay thế A2:A (hai đầu tiên) trong công thức bằng công thức sau.
array_constrain(sort(A2:C,1,1,3,0),9^9,1)
Công thức SORT sắp xếp cột đầu tiên trong A2:C theo thứ tự tăng dần và cột cuối cùng trong A2:C theo thứ tự giảm dần.
Hàm Array_Constrain giới hạn số cột trong A2:C thành 1, tức là A2:A.
Dưới đây là công thức sau khi được thay đổi như trên.
=ARRAYFORMULA(COUNTIFS(array_constrain(sort(A2:C,1,1,3,0),9^9,1),array_constrain(sort(A2:C,1,1,3,0),9^9,1),ROW(A2:A),"<="&ROW(A2:A)))
Bước 3 – Kết hợp Sản phẩm và Doanh số dựa trên RC đã sắp xếp
Công thức chung:
=Nếu(kết_quả_bước_2<=3,sắp_xếp_sản_phẩm&sắp_xếp_doanh_số,)
Để có được sắp xếp_sản_phẩm, sử dụng công thức SORT sau đây.
array_constrain(sort(A2:C,1,1,3,0),9^9,1)
Công thức cho sắp_xếp_doanh_số.
array_constrain(sort({C2:C,A2:A},2,1,1,0),9^9,1)
Đây là công thức bước #3 theo công thức chung ở trên.
=ArrayFormula(if(COUNTIFS(array_constrain(sort(A2:C,1,1,3,0),9^9,1),array_constrain(sort(A2:C,1,1,3,0),9^9,1),ROW(A2:A),"<="&ROW(A2:A))<=3,array_constrain(sort(A2:C,1,1,3,0),9^9,1)&array_constrain(sort({C2:C,A2:A},2,1,1,0),9^9,1),))
Bạn chỉ cần lọc các hàng trong A2:C khớp với các đầu ra trên cột D để chỉ lấy 3 giá trị hàng đầu trong mỗi nhóm trong bảng tổng kết trong Google Sheets. Đọc tiếp để biết cách làm điều đó.
Bước 4 – Regex Match để lọc giá trị Top “N” trong mỗi nhóm trong Bảng Tổng kết Google Sheets
Công thức chung Regexmatch sẽ như sau:
regexmatch(Sản_phẩm&Doanh_số,kết_quả_bước_3_dưới_dạng_biểu_thức_chính_quy)
Trong đó “Sản_phẩm” và “Doanh_số” là nhãn trường (A1:A và C1:C). Chúng ta có thể sử dụng nhãn trường thay vì tham chiếu mảng/phạm vi trong trường công thức Biên tập Pivot.
Kết quả của bước_3 (kết quả công thức bước 3) nên được định dạng dưới dạng biểu thức chính quy. Hàm TextJoin sẽ làm điều đó.
Xem phần được làm nổi bật là phần đã thêm vào công thức bước #3 để hình thành biểu thức chính quy.
Dưới đây là công thức tùy chỉnh đã được chờ đợi để lọc 3 giá trị hàng đầu từ mỗi nhóm trong bảng tổng kết trong Google Sheets.
=regexmatch(Sản_phẩm&Doanh_số,"^"&textjoin("$|^",1,ArrayFormula(if(not(len(A2:A)),,if(COUNTIFS(array_constrain(sort(A2:C,1,1,3,0),9^9,1),array_constrain(sort(A2:C,1,1,3,0),9^9,1),ROW(A2:A),"<="&ROW(A2:A))<=3,array_constrain(sort(A2:C,1,1,3,0),9^9,1)&array_constrain(sort({C2:C,A2:A},2,1,1,0),9^9,1),))))&"$")
Làm thế nào để sử dụng công thức này trong bảng tổng kết?
Để chèn công thức trên làm công cụ lọc/chọn 3 giá trị hàng đầu trong mỗi nhóm trong bảng tổng kết Google Sheets, làm theo các bước dưới.
- Nhấp vào ô E2 để kích hoạt bảng trình biên tập pivot.
- Cuộn lên đầu trình biên tập và thay đổi “Phạm vi dữ liệu” từ A1: C14 thành A1: C100 hoặc đến phạm vi bạn muốn (bạn có thể bao gồm các hàng trống để chứa các mục bán hàng trong tương lai).
- Cuộn xuống cuối trình biên tập và nhấp vào “Thêm” phía sau “Bộ lọc”.
- Chọn “Sản phẩm” là trường nhóm chính.
- Nhấp vào menu thả xuống (mặc định có thể hiển thị “Hiển thị tất cả các mục”) và chọn Lọc theo điều kiện> Công thức tùy chỉnh là.
- Chèn công thức trên và nhấp vào “OK”.
Đó là tất cả!
Viết công thức tùy chỉnh cho Báo cáo số 2
Ở đầu bài viết này, tôi đã chỉ cho bạn hai loại báo cáo. Tôi đã chi tiết Báo cáo số 1 (3 cột – Sản phẩm, Công ty và Doanh số) ở trên.
Trong Báo cáo số 2 (2 cột – Sản phẩm và Doanh số), chúng ta có thể sử dụng công thức trên với một số thay đổi. Những thay đổi này nằm ở Bước 2 và Bước 3.
Vì chúng ta có hai cột, chúng ta nên thay đổi phạm vi đã sắp xếp tương ứng. Điều đó có nghĩa là trong Bước 2, ở đây A2:A phải được thay bằng array_constrain(sort(A2:C,1,1,2,0),9^9,1).
Ở đây trong Bước 3, sorted_product sẽ là array_constrain(sort(A2:B,1,1,2,0),9^9,1) và sorted_sales sẽ là array_constrain(sort({B2:B,A2:A},2,1,1,0),9^9,1).
Không có sự thay đổi nào khác. Dưới đây là công thức cuối cùng (sau khi kết hợp các thay đổi nêu trên) để sử dụng trong trình biên tập bảng tổng kết để lọc ra 3 giá trị hàng đầu từ mỗi nhóm.
=regexmatch(Sản_phẩm&Doanh_số,"^"&textjoin("$|^",1,ArrayFormula(if(not(len(A2:A)),,(if(COUNTIFS(array_constrain(sort(A2:B,1,1,2,0),9^9,1),array_constrain(sort(A2:B,1,1,2,0),9^9,1),ROW(A2:A),"<="&ROW(A2:A))<=3,array_constrain(sort(A2:B,1,1,2,0),9^9,1)&array_constrain(sort({B2:B,A2:A},2,1,1,0),9^9,1),)))))&"$")
Làm thế nào để Lọc Top “N” Thay vì Top 3 trong mỗi nhóm trong Bảng Tổng kết Pivot?
Trong công thức, thay đổi <=3 thành <=n. Ở đây, thay “n” bằng 5 hoặc bất kỳ số hàng nào mà bạn muốn lọc ra từ mỗi nhóm trong bảng tổng kết.
Đó là tất cả. Hãy thử ngay!
