VietNetwork.Vn

Sắp xếp lại dữ liệu của bạn thành các nhóm và nhóm con.

Hàm GROUPBY của Excel cho phép bạn nhóm và tổng hợp dữ liệu dựa trên các trường nhất định trong bảng dữ liệu của bạn. Nó cũng cung cấp các đối số cho phép bạn sắp xếp và lọc dữ liệu của mình, do đó bạn có thể tùy chỉnh đầu ra để đáp ứng nhu cầu cụ thể của mình.


Mặc dù bạn có thể sử dụng bảng trục để đạt được kết quả tương tự như hàm GROUPBY, nhưng hàm GROUPBY sẽ tự động làm mới nếu dữ liệu của bạn thay đổi hoặc được sắp xếp lại và nó cũng cho phép bạn nhúng nhiều hàm hơn để phân loại dữ liệu chính xác hơn.

1. Cú pháp GROUPBY

Hàm GROUPBY có tám đối số:

=GROUPBY(a,b,c,d,e,f,g,h)
Các đối số từ a đến c là bắt buộc:

    a (trường hàng): Phạm vi (một hoặc nhiều cột) chứa các giá trị hoặc danh mục mà dữ liệu sẽ được nhóm theo.
    b (giá trị): Phạm vi (một hoặc nhiều cột) chứa các giá trị tổng hợp dữ liệu.
    c (hàm): Một hàm được sử dụng để tổng hợp các giá trị trong đối số b.

Các đối số từ d đến h là tùy chọn và bạn có thể tìm hiểu thêm về các đối số này trong phần cuối của bài viết này:

    d (tiêu đề trường): Một số chỉ định liệu bạn có chọn tiêu đề trong đối số a và b hay không và liệu chúng có được hiển thị trong đầu ra hay không.
    e (tổng độ sâu): Một con số quyết định liệu đầu ra có hiển thị tổng số hay không.
    f (thứ tự sắp xếp): Một con số cho biết kết quả được sắp xếp như thế nào.
    g (mảng lọc): Công thức theo mảng để lọc ra thông tin không mong muốn.
    h (mối quan hệ trường): Một số chỉ định mối quan hệ trường khi nhiều cột được cung cấp trong đối số a.

2. GROUPBY trong hành động: Chỉ sử dụng các đối số bắt buộc

Nếu bạn bị choáng ngợp bởi số lượng lớn các đối số mà hàm GROUPBY có, điều quan trọng cần lưu ý ở giai đoạn này là hàm GROUPBY hoạt động hoàn hảo ngay cả khi bạn chỉ điền các đối số a, b và c. Vì vậy, trước tiên, tôi sẽ chỉ cho bạn cách hàm GROUPBY hoạt động chỉ với ba đối số này.

Hãy tưởng tượng bạn sở hữu một chuỗi nhà hàng phục vụ nhiều món ăn khác nhau từ nhiều nền ẩm thực khác nhau và bạn đã tính tổng doanh số và đánh giá trung bình của khách hàng cho từng sự kết hợp ẩm thực-món ăn.


Mặc dù những con số này hữu ích, nhưng có thể bạn quan tâm hơn đến cách dữ liệu so sánh giữa các danh mục khác nhau. Cụ thể, bạn có thể muốn tìm hiểu tổng số tiền mặt mà mỗi món ăn mang lại và xếp hạng trung bình của khách hàng cho từng loại món ăn.

Vì hàm GROUPBY trả về các mảng bị tràn nên bạn không thể sử dụng các bảng Excel được định dạng để có kết quả.


Hãy để tôi dành chút thời gian để giải thích lý do tại sao GROUPBY sẽ là hàm tôi chọn để thực hiện các tác vụ này cho tập dữ liệu cụ thể này. Nếu mỗi món ăn và mỗi món ăn chỉ xuất hiện một lần trong bảng, bạn chỉ cần sử dụng các nút lọc để sắp xếp lại và phân tích dữ liệu của mình. Tuy nhiên, vì các món ăn và món ăn được lặp lại, nên việc sử dụng hàm GROUPBY sẽ cho phép bạn kéo dữ liệu từ các danh mục chung lại với nhau, giúp bạn có ý tưởng toàn cảnh rõ ràng hơn về doanh số và phân phối xếp hạng.

Để tìm ra tổng doanh số của từng món ăn, hãy nhập vào ô F2:

=GROUPBY(
Vì bạn đang muốn nhóm dữ liệu theo món ăn, hãy chọn các ô chứa biến này và thêm dấu phẩy. Trong trường hợp này, vì dữ liệu nằm trong bảng Excel được định dạng có tên là TabFood, nên tham chiếu có cấu trúc đến tên cột được thêm vào công thức của tôi:

=GROUPBY(TabFood[Cuisine],
Sau đó, vì bạn muốn xem tổng doanh số của từng món ăn đó, hãy chọn các ô chứa những số liệu này và thêm một dấu phẩy nữa:

=GROUPBY(TabFood[Cuisine],TabFood[Sales],
Đối số bắt buộc cuối cùng là hàm được sử dụng trên dữ liệu tổng hợp. Trong trường hợp này, vì bạn muốn tìm tổng doanh số cho từng món ăn, bạn cần chèn hàm SUM và đóng dấu ngoặc đơn:

=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)
Cũng như sử dụng các hàm đơn giản như SUM và AVERAGE trong đối số c, bạn cũng có thể sử dụng công cụ LAMBDA của Excel để tạo một hàm phù hợp với nhu cầu của mình.

Khi bạn nhấn Enter, bạn sẽ thấy Excel đã tổng hợp tổng doanh số cho từng món ăn. Vì bạn chưa bao gồm bất kỳ đối số tùy chọn nào trong hàm GROUPBY, nên dữ liệu được sắp xếp theo thứ tự bảng chữ cái theo các giá trị trong cột F theo mặc định và có một hàng tổng ở cuối dữ liệu đã trích xuất của bạn.


Vì các giá trị trong cột G là giá trị tài chính, hãy chọn dữ liệu và nhấp vào biểu tượng "Kế toán" trong nhóm Số của tab Trang chủ trên ruy-băng.


Bây giờ, bạn muốn tìm ra đánh giá trung bình của khách hàng cho từng loại món ăn và quy trình thực hiện cũng rất giống nhau.

Trong ô I2, nhập:

=GROUPBY(
Tiếp theo, chọn các ô chứa danh mục mà bạn muốn nhóm dữ liệu. Trong trường hợp này, đó là các món ăn khác nhau. Nhớ thêm dấu phẩy sau mỗi đối số để chuyển sang đối số tiếp theo.

=GROUPBY(TabFood[Dish],
Bây giờ, hãy chọn các ô chứa dữ liệu cần tổng hợp và thêm một dấu phẩy nữa:

=GROUPBY(TabFood[Dish],TabFood[Customer rating]
Cuối cùng, vì mục đích của bạn trong trường hợp này là tìm ra đánh giá trung bình của khách hàng cho từng loại món ăn nên đối số hàm phải là TRUNG BÌNH.

=GROUPBY(TabFood[Dish],TabFood[Customer rating],AVERAGE)
Sau khi bạn nhấn Enter, Excel sẽ tính trung bình xếp hạng của khách hàng cho từng loại món ăn. Một lần nữa, khi không có bất kỳ đối số tùy chọn nào, dữ liệu được sắp xếp theo thứ tự bảng chữ cái theo các giá trị ở cột bên trái theo mặc định và có một hàng tổng tiện dụng ở phía dưới.


Vì các giá trị trong cột J là giá trị trung bình thập phân, hãy dọn dẹp số chữ số thập phân hiển thị bằng cách nhấp vào nút "Tăng số thập phân" và "Giảm số thập phân" trong nhóm Số của tab Trang chủ.


Nếu bạn hài lòng với kết quả GROUPBY của mình ở giai đoạn này, bạn có thể dừng đọc ở đây. Tuy nhiên, hãy tiếp tục đọc để tìm hiểu về các đối số tùy chọn của GROUPBY.

3. GROUPBY trong hành động: Sử dụng các đối số tùy chọn

Mặc dù hàm GROUPBY có năm đối số tùy chọn cùng với ba đối số bắt buộc khiến nó có vẻ phức tạp hơn, nhưng các tùy chọn bổ sung này thực ra chỉ có tác dụng giúp bạn tạo ra đầu ra phù hợp hơn với nhu cầu của mình. Hơn nữa, bạn có thể chọn đối số tùy chọn nào bạn muốn sử dụng và bỏ qua những đối số bạn không muốn sử dụng.

Sau đây, tôi sẽ trình bày từng đối số tùy chọn để bạn có thể thấy chúng sẽ ảnh hưởng đến dữ liệu của bạn như thế nào khi bạn chọn đưa chúng vào.

Sử dụng dấu phẩy để nhảy từ đối số này sang đối số tiếp theo. Ví dụ, nếu bạn muốn bao gồm đối số thứ tư và thứ sáu, nhưng không bao gồm đối số thứ năm, hãy nhập [đối số thứ tư],,[đối số thứ sáu]. Đối số thứ năm sẽ nằm giữa vị trí của hai dấu phẩy, nhưng vì không có gì trong khoảng trống đó nên Excel biết bạn cố tình để đối số này trống.

3.1. Tiêu đề trường

Trong các ví dụ tôi sử dụng ở trên, tôi đã nhập thủ công tiêu đề cột đầu ra vì chúng không được bao gồm trong kết quả theo mặc định. Tuy nhiên, nếu bạn muốn dữ liệu đầu ra của mình bao gồm cả tiêu đề cột cũng như dữ liệu chúng chứa, hãy sử dụng đối số tiêu đề trường.

Bắt đầu bằng cách nhập công thức GROUPBY của bạn, bao gồm ba đối số đầu tiên (bắt buộc). Trong trường hợp này, giả sử bạn muốn nhóm các món ăn theo xếp hạng trung bình của khách hàng:

=GROUPBY(A1:A21,D1:D21,AVERAGE

Lưu ý cách các hàng tiêu đề được bao gồm trong các lựa chọn. Thật vậy, khi chọn dữ liệu của bạn cho hai đối số đầu tiên, bạn nên suy nghĩ trước về việc bạn có muốn dữ liệu đầu ra của mình trùng lặp các tiêu đề trong bảng hay không.

Các trường hàng và đối số giá trị phải có cùng kích thước. Nếu bạn chọn tiêu đề trong một trường, bạn phải chọn tiêu đề trong trường còn lại.

Cuối cùng, nhập dấu phẩy để di chuyển đến đối số tiêu đề trường và nhập:

    1 nếu bạn đã chọn tiêu đề trong hai đối số đầu tiên nhưng bạn không muốn chúng hiển thị trong kết quả,
    2 nếu bạn chưa chọn tiêu đề trong hai đối số đầu tiên nhưng bạn muốn Excel tạo tiêu đề chung trong kết quả hoặc
    3 nếu bạn đã chọn các tiêu đề trong hai đối số đầu tiên và bạn muốn Excel hiển thị chúng trong kết quả.

Đây là kết quả khi tôi gõ:

=GROUPBY(A1:A21,D1:D21,AVERAGE,3)

Bây giờ tôi có thể định dạng các tiêu đề cột trùng lặp để có thể phân biệt rõ ràng với dữ liệu, giống như trong bảng gốc.

Lợi ích của việc bao gồm Tiêu đề trường: Nếu bạn thay đổi tiêu đề trong bảng gốc, tiêu đề đầu ra sẽ áp dụng những thay đổi đó.
   
Nhược điểm của việc bao gồm các tiêu đề trường: Bạn không thể thay đổi tiêu đề đầu ra nếu bạn muốn làm cho chúng cụ thể hơn tiêu đề bảng gốc.

3.2. Tổng độ sâu

Đối số total depth cho phép bạn quyết định xem bạn có muốn kết quả hiển thị tổng cộng hay không và nếu có, liệu chúng sẽ nằm ở đầu hay cuối dữ liệu của bạn. Đối số này cũng cho phép bạn chọn hiển thị tổng phụ.

Đối với đối số độ sâu tổng thể, hãy nhập:

    0 nếu bạn không muốn hiển thị bất kỳ tổng số hoặc tổng phụ nào,
    1 nếu bạn chỉ muốn tổng số được hiển thị ở cuối kết quả,
    2 nếu bạn muốn tổng phụ xuất hiện ở cuối mỗi danh mục kết quả và tổng cộng ở cuối kết quả chung,
    -1 nếu bạn chỉ muốn tổng số được hiển thị ở đầu kết quả hoặc
    -2 nếu bạn muốn tổng phụ xuất hiện ở đầu mỗi danh mục kết quả và tổng cộng xuất hiện ở đầu kết quả chung.

Các tùy chọn hiển thị tổng phụ (2 và -2) chỉ hoạt động nếu đối số trường hàng chứa nhiều hơn một cột dữ liệu (hay nói cách khác là các trường phụ).

Trong ví dụ này, tôi đã nhập:

=GROUPBY(A1:B21,C1:C21,SUM,,2)
Sử dụng dấu phẩy để bỏ qua đối số danh mục trường và cho Excel biết rằng tôi muốn tổng phụ xuất hiện bên dưới mỗi danh mục và tổng cộng ở cuối dữ liệu. Sau đó, tôi đã áp dụng định dạng trực tiếp cho các hàng tổng phụ để dữ liệu dễ đọc hơn.


3.3. Thứ tự sắp xếp

Trường thứ tự sắp xếp cho phép bạn cho Excel biết liệu bạn có muốn sắp xếp kết quả hay không và cách sắp xếp. Sử dụng đối số này thực sự làm nổi bật lý do tại sao hàm GROUPBY có thể hữu ích hơn so với việc sử dụng bảng trục: ngay khi bạn thay đổi bất kỳ dữ liệu nào trong bảng gốc, toàn bộ dữ liệu đầu ra sẽ được sắp xếp lại theo đối số thứ tự sắp xếp, trong khi bảng trục yêu cầu phải làm mới thủ công.

Số bạn nhập cho đối số này biểu thị cột trong kết quả. Ví dụ, nếu bạn nhập 1, điều này sẽ sắp xếp kết quả theo cột đầu tiên theo thứ tự tăng dần hoặc theo thứ tự bảng chữ cái. Mặt khác, nhập -1 sẽ sắp xếp kết quả theo cột đầu tiên theo thứ tự giảm dần hoặc theo thứ tự bảng chữ cái ngược lại.

Trong ví dụ này, tôi đã nhập:

=GROUPBY(A1:A21,C1:C21,SUM,,,-2)
Sắp xếp cột thứ hai (doanh số) theo thứ tự giảm dần.


3.4. Mảng lọc

Đối số mảng bộ lọc ít có khả năng được sử dụng hơn các đối số tùy chọn trước đó, mặc dù nó có thể giải quyết vấn đề nếu bảng dữ liệu gốc của bạn chứa các hàng có thể làm gián đoạn dữ liệu của bạn.

Trong ví dụ này, các năm trong ô A2, A8 và A17 làm gián đoạn kết quả của hàm GROUPBY.


Tôi có thể sử dụng đối số mảng lọc để yêu cầu Excel bỏ qua bất kỳ ô nào trong cột A có chứa số thông qua hàm ISNUMBER:

=GROUPBY(A1:A24,C1:C24,SUM,,,,ISNUMBER(A1:A24)=FALSE)

3.5. Mối quan hệ lĩnh vực

Cuối cùng, đối số mối quan hệ trường kiểm soát cách dữ liệu được nhóm lại khi đối số trường hàng tham chiếu đến nhiều hơn một cột.

Trong ví dụ này, khi đối số mối quan hệ trường chứa 0 (là giá trị mặc định nếu đối số bị bỏ qua), GROUPBY trả về bảng kết quả phân cấp, trong đó mỗi cột được biểu diễn riêng bằng các hàng dữ liệu riêng biệt.

=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)

Mặt khác, khi đối số mối quan hệ trường chứa 1, GROUPBY trả về một bảng kết quả bỏ qua phân cấp và sắp xếp từng cột độc lập. Nói cách khác, các danh mục không được lồng nhau, đó là lý do tại sao bạn cũng không thể bao gồm tổng phụ trong kết quả khi bạn chọn tùy chọn mối quan hệ trường này.

=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)

Ngoài việc sử dụng SUM và AVERAGE trong đối số hàm GROUPBY, bạn có thể sử dụng hàm PERENTOF, hàm này chuyển dữ liệu thành phần trăm để hiển thị tỷ lệ một tập hợp con tạo nên toàn bộ một tập dữ liệu.