Thành thạo GROUPBY – Tái định nghĩa cách bạn làm báo cáo Excel. Blog records

ERX Sticky Banner

Bảng tham số hàm GROUPBY

Tham số	Bắt buộc/ Tùy chọn	Giải thích chi tiết
row_fields	Bắt buộc	Mảng (array) chứa các trường (fields) dùng để nhóm dòng (giống như "Group by" theo cột nào).
values	Bắt buộc	Mảng giá trị mà bạn muốn thực hiện phép tính (như cộng tổng, đếm, trung bình...).
function	Bắt buộc	Hàm áp dụng: "SUM", "COUNT", "AVERAGE", "MAX", "MIN", hoặc LAMBDA.
field_headers	Tùy chọn	Tiêu đề cho các cột kết quả.
total_depth	Tùy chọn	Mức độ nhóm nhiều cấp (ví dụ theo "Vùng" rồi "Quốc gia").
sort_order	Tùy chọn	Sắp xếp tăng (1) hoặc giảm (-1). Mặc định là 1.
filter_array	Tùy chọn	Điều kiện lọc dữ liệu trước khi nhóm.
field_relationship	Tùy chọn	Mô tả quan hệ giữa các field khi nhóm phức tạp.

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY[[#All],[ProductName]], DATA_GROUPBY[[#All],[Amount]], SUM, 3)
    

Giải thích 1 chút thì ở đây chúng ta chọn row_fields là cột ProductName.

Values mình lựa chọn là cột Amount.

Function được sử dụng ở đây là SUM.

Với phần field_headers thì mình lựa chọn số 3 nghĩa là yes and show.

Mặc định với phần field_headers ta có 3 lựa chọn lần lượt là: 0 – No, 1 – Yes but don’t show, 2 – No but generate, 3 – Yes and show.

Đa phần khi viết công thức GROUPBY chúng ta sẽ lựa chọn tuỳ theo công thức mà chúng ta viết.

- Nếu bạn chọn field_headers = 0 – No thì bạn đang thông báo cho Excel biết là dữ liệu của chúng ta không có tiêu đề.

- Nếu bạn chọn field_headers = 1 – Yes but don’t show thì bạn đang thông báo cho Excel biết là dữ liệu của chúng ta có tiêu đề. Tức là chúng ta sẽ lấy dòng đầu tiên trong vùng dữ liệu bạn chọn để làm tiêu đề. Ví dụ như trên thì tiêu đề lần lượt sẽ là ProductName và Amount.

- Nếu bạn chọn field_headers = 2 – No but generate thì có nghĩa là dữ liệu của bạn không có tiêu đề, lúc này Excel sẽ tự tạo ra (generate) ra 1 tiêu đề theo ý nó cho bạn. Mình khuyến khích bạn không nên sử dụng tính năng này.

- Nếu bạn chọn field_headers = 3 – Yes and show. Lúc này thì bạn đã thông báo cho Excel biết bạn có tiêu đề (dòng đầu tiên mặc định được hiểu là dòng lấy làm tiêu đề) và bạn muốn hiển thị ra nó. Đây là 1 trong các lựa chọn mình rất hay sử dụng.

Bạn có thể thấy trong công thức mình đã đưa lên trên có đoạn “DATA_GROUPBY[[#All],[ProductName]]”. Đoạn này có ý nghĩa là bạn lấy ra toàn bộ cột ProductName bao gồm cả tiêu đề.

Thường khi viết công thức trong Excel thì các bạn khi muốn tham chiếu đến cột ProductName thì các bạn sẽ viết là DATA_GROUPBY[ProductName]. Vậy 2 phần tham chiếu trên khác nhau như thế nào?

Dưới đây là tham chiếu khi bạn chọn DATA_GROUPBY có [#All] và không có [#All]

Dưới đây là tham chiếu khi bạn chọn
DATA_GROUPBY[[#All],[ProductName]]

Còn đây là tham chiếu khi bạn chọn
DATA_GROUPBY[ProductName]

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY[ProductName],DATA_GROUPBY[Amount],SUM,3)

EXCEL FORMULA

=VSTACK({"Product Name","Amount"},GROUPBY(DATA_GROUPBY[ProductName],DATA_GROUPBY[Amount],SUM,0))
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=VSTACK({"Product Name","Amount"},GROUPBY(DATA_GROUPBY[ProductName],DATA_GROUPBY[Amount],SUM,,,,DATA_GROUPBY[ChannelName]="Online"))
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

Excel Code Block

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY[[#All],[ProductName]:[ChannelName]],DATA_GROUPBY[[#All],[Amount]],SUM,3)
      

Excel Code Block

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY[[#All],[ProductName]:[ChannelName]],DATA_GROUPBY[[#All],[Amount]],SUM,3,,{-1,2})
      

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY[[ProductName]:[ChannelName]],DATA_GROUPBY[Amount],SUM,0,0,-3,,0)
    

EXCEL FORMULA

=LET(
    group_by_array, GROUPBY(
        DATA_GROUPBY[[ProductName]:[ChannelName]],
        DATA_GROUPBY[Amount],
        SUM,
        0,
        0
    ),
    VSTACK(
        {"Product Name", "Channel Name", "Amount"},
        SORT(group_by_array, {1, 3}, {1, -1}),
        HSTACK(
            "Total",
            "",
            SUM(CHOOSECOLS(group_by_array, 3))
        )
    )
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY4[[#All],[ProductName]:[ChannelName]],DATA_GROUPBY4[[#All],[Amount]],SUM,3,1,-3,,1)
    

EXCEL FORMULA

=GROUPBY(DATA_GROUPBY4[[#All],[ProductName]:[ChannelName]],DATA_GROUPBY4[[#All],[Amount]],SUM,3,1,-3)
    

field_relationship là Table

field_relationship là Hierarchy (mặc định)

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=LET(
    result_array, SORT(
        GROUPBY(
            DATA_GROUPBY[[#All],[ProductName]:[ChannelName]],
            DATA_GROUPBY[[#All],[Amount]],
            SUM,
            3
        ),
        2,
        1
    ),
    report_array, DROP(result_array, 1),
    grand_total_array, TAKE(result_array, 1),
    VSTACK(report_array, grand_total_array)
)
    

EXCEL FORMULA

=LET(
    result_array, SORT(
        GROUPBY(
            DATA_GROUPBY[[#All],[ProductName]:[ChannelName]],
            DATA_GROUPBY[[#All],[Amount]],
            SUM,
            3
        ),
        {2, 3},
        {1, -1}
    ),
    report_array, DROP(result_array, 1),
    grand_total_array, TAKE(result_array, 1),
    VSTACK(report_array, grand_total_array)
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=GROUPBY(
    HSTACK(
        DATA_GROUPBY[[#All],[ChannelName]],
        DATA_GROUPBY[[#All],[ProductName]]
    ),
    DATA_GROUPBY[[#All],[Amount]],
    SUM,
    3,
    2
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=VSTACK(
    {"Phòng ban", "Email"},
    HSTACK(
        SORT(UNIQUE(Z4:Z23)),
        BYROW(
            SORT(UNIQUE(Z4:Z23)),
            LAMBDA(row,
                TEXTJOIN(
                    "; ",
                    TRUE,
                    FILTER(AA4:AA23, Z4:Z23 = row)
                )
            )
        )
    )
)
    

EXCEL FORMULA

=GROUPBY(Z3:Z23,AA3:AA23,ARRAYTOTEXT,3,0)

EXCEL FORMULA

=GROUPBY(Z3:Z23,AA3:AA23,LAMBDA(x, SUBSTITUTE(ARRAYTOTEXT(x),",",";")),3,0)

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=GROUPBY(Z46:Z346,AA46:AA346,LAMBDA(arr, ARRAYTOTEXT(SORT(UNIQUE(arr)))),,0)

EXCEL FORMULA

=VSTACK(
    {"Division", "Sales Manager"},
    HSTACK(
        SORT(UNIQUE(Z47:Z346)),
        BYROW(
            SORT(UNIQUE(Z47:Z346)),
            LAMBDA(row,
                TEXTJOIN(
                    ", ",
                    TRUE,
                    SORT(
                        UNIQUE(
                            FILTER(
                                AA47:AA346,
                                Z47:Z346 = row
                            )
                        )
                    )
                )
            )
        )
    )
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=GROUPBY(AA349:AA661,AC349:AC661,SUM,3,,,ISNUMBER(Y349:Y661))

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=VSTACK(
    {"Sales Manager", "% Sale"},
    GROUPBY(
        AA350:AA661,
        AC350:AC661,
        PERCENTOF,
        0,
        ,
        -2,
        ISNUMBER(Y350:Y661)
    )
)
    

EXCEL FORMULA

=VSTACK(
    {"Sales Manager", "Total Sales", "% Sales"},
    LET(
        pct_sales, GROUPBY(
            AA350:AA661,
            AC350:AC661,
            PERCENTOF,
            0,
            ,
            -2,
            ISNUMBER(Y350:Y661)
        ),
        total_sales, GROUPBY(
            AA350:AA661,
            AC350:AC661,
            SUM,
            0,
            ,
            -2,
            ISNUMBER(Y350:Y661)
        ),
        HSTACK(
            total_sales,
            BYROW(
                CHOOSECOLS(total_sales, 1),
                LAMBDA(row,
                    XLOOKUP(
                        row,
                        CHOOSECOLS(pct_sales, 1),
                        CHOOSECOLS(pct_sales, 2)
                    )
                )
            )
        )
    )
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=LET(
    result_array, VSTACK(
        {
            "Month / Year",
            "Sales Name",
            "Sales Amount"
        },
        DROP(
            GROUPBY(
                HSTACK(
                    Y349:Y661,
                    TEXT(Y349:Y661, "mmm/yyyy"),
                    AA349:AA661
                ),
                AC349:AC661,
                SUM,
                0,
                0,
                1,
                ISNUMBER(Y349:Y661)
            ),
            ,
            1
        )
    ),
    VSTACK(
        result_array,
        HSTACK(
            "Total",
            "",
            SUM(CHOOSECOLS(result_array, 3))
        )
    )
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=GROUPBY(
    DATA_GROUPBY5[[#All],[ProductName]],
    DATA_GROUPBY5[[#All],[Amount]],
    HSTACK(SUM, MIN, MAX, COUNTA, AVERAGE)
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

EXCEL FORMULA

=VSTACK(
    {"Sales Name", "Total Orders", "% Orders"},
    DROP(
        GROUPBY(
            B2:B38,
            SEQUENCE(ROWS(C2:C38), , , 0),
            HSTACK(COUNTA, PERCENTOF)
        ),
        1
    )
)
    

EXCEL FORMULA

=LET(
    orders_arr, VSTACK(
        {"Sales Name", "Total Orders", "% Orders"},
        DROP(
            GROUPBY(
                B2:B38,
                SEQUENCE(ROWS(C2:C38), , , 0),
                HSTACK(COUNTA, PERCENTOF)
            ),
            1
        )
    ),
    sales_arr, VSTACK(
        {"Sales Name", "Total Sales", "% Sales"},
        DROP(
            GROUPBY(
                B2:B38,
                D2:D38,
                HSTACK(SUM, PERCENTOF)
            ),
            1
        )
    ),
    sales_col, XLOOKUP(
        CHOOSECOLS(orders_arr, 1),
        CHOOSECOLS(sales_arr, 1),
        CHOOSECOLS(sales_arr, 2)
    ),
    pct_sales_col, XLOOKUP(
        CHOOSECOLS(orders_arr, 1),
        CHOOSECOLS(sales_arr, 1),
        CHOOSECOLS(sales_arr, 3)
    ),
    HSTACK(orders_arr, sales_col, pct_sales_col)
)
    

🚀 Mở toàn màn hình để thực hành trên Excel Online

📱 Nội dung thực hành Excel chỉ hiển thị trên máy tính. Vui lòng mở trên thiết bị có màn hình lớn hơn.

Hoang Trung

Tue 29 April 18:56

For comment —
please authorize

1. Cú pháp hàm GROUPBY

Bảng tham số hàm GROUPBY

2. Các ví dụ thực chiến sử dụng hàm GROUPBY trong thực tế

2.1. GROUPBY đơn giản theo 1 cột dữ liệu trong Excel

Dưới đây là tham chiếu khi bạn chọn DATA_GROUPBY[[#All],[ProductName]]

Còn đây là tham chiếu khi bạn chọn DATA_GROUPBY[ProductName]

Dưới đây là tham chiếu khi bạn chọn
DATA_GROUPBY[[#All],[ProductName]]

Còn đây là tham chiếu khi bạn chọn
DATA_GROUPBY[ProductName]