Trong bối cảnh dữ liệu ngày càng bùng nổ, khả năng phân tích và tổng hợp thông tin hiệu quả là một kỹ năng không thể thiếu. Excel, với vai trò là công cụ bảng tính hàng đầu, liên tục được cập nhật để đáp ứng nhu cầu này. Một trong những tính năng nổi bật giúp người dùng Excel nâng cao khả năng xử lý dữ liệu chính là hàm GROUPBY. Hàm này 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 của mình, đồng thời cung cấp các đối số tùy chọn để sắp xếp và lọc, giúp bạn tinh chỉnh kết quả đầu ra theo yêu cầu cụ thể.
Mặc dù Pivot Table (Bảng tổng hợp) cũng có thể đạt được những kết quả tương tự, nhưng hàm GROUPBY mang lại ưu điểm vượt trội về tính tự động. Khi dữ liệu nguồn thay đổi hoặc được sắp xếp lại, kết quả của GROUPBY sẽ tự động cập nhật mà không yêu cầu thao tác thủ công, điều mà Pivot Table không làm được. Hơn nữa, GROUPBY cho phép bạn nhúng nhiều hàm khác để phân loại dữ liệu tinh vi hơn, mở ra nhiều khả năng phân tích phức tạp. Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm GROUPBY trong Excel một cách toàn diện, từ những đối số cơ bản đến các tùy chọn nâng cao, giúp bạn làm chủ công cụ mạnh mẽ này.
Cú Pháp Hàm GROUPBY Trong Excel
Hàm GROUPBY có tổng cộng tám đối số, nhưng chỉ có ba đối số đầu tiên là bắt buộc. Cú pháp tổng quát như sau:
=GROUPBY(a,b,c,d,e,f,g,h)Các đối số bắt buộc:
a(row_fields): 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(values): Phạm vi (một hoặc nhiều cột) chứa các giá trị số hoặc văn bản sẽ được tổng hợp.c(function): Hàm được sử dụng để tổng hợp các giá trị trong đối sốb. Ví dụ: SUM, AVERAGE, COUNT, MAX, MIN, PERCENTOF, v.v. Bạn cũng có thể sử dụng hàm LAMBDA của Excel để tạo hàm tùy chỉnh.
Các đối số tùy chọn:
d(field_headers): Một số chỉ định liệu bạn có chọn tiêu đề trong đối sốavàbhay không, và liệu chúng có nên được hiển thị trong kết quả đầu ra.e(total_depth): Một số xác định liệu kết quả đầu ra có nên hiển thị tổng cộng (grand total) và/hoặc tổng phụ (subtotal) hay không.f(sort_order): Một số chỉ ra cách sắp xếp kết quả.g(filter_array): Một công thức dựa trên mảng để lọc bỏ thông tin không mong muốn.h(field_relationship): Một số chỉ định mối quan hệ trường khi có nhiều cột được cung cấp trong đối sốa.
Hướng Dẫn Sử Dụng Hàm GROUPBY Với Các Đối Số Bắt Buộc
Nếu bạn cảm thấy choáng ngợp bởi số lượng đối số của hàm GROUPBY, điều quan trọng cần lưu ý là hàm này hoạt động hoàn hảo ngay cả khi bạn chỉ điền ba đối số bắt buộc: a (row_fields), b (values) và c (function). Để minh họa, chúng ta sẽ xem xét một ví dụ thực tế.
Giả sử bạn là chủ sở hữu một chuỗi nhà hàng phục vụ nhiều món ăn từ các nền ẩm thực khác nhau. Bạn đã tổng hợp dữ liệu về tổng doanh số và điểm đánh giá trung bình của khách hàng cho từng sự kết hợp món ăn-ẩm thực.
Bảng Excel ví dụ dữ liệu nhà hàng với cột Món ăn, Doanh số và Đánh giá khách hàng.
Mặc dù các số liệu này hữu ích, bạn có thể muốn phân tích cách dữ liệu so sánh giữa các danh mục khác nhau. Cụ thể, bạn muốn biết tổng doanh thu mà mỗi nền ẩm thực mang lại và điểm đánh giá trung bình của khách hàng cho từng loại món ăn.
Vì hàm GROUPBY trả về mảng tràn (spilled arrays), bạn không thể sử dụng các bảng Excel được định dạng cho kết quả của chúng. Thay vào đó, bạn sẽ cần một vùng trống để chứa kết quả.
Hai bảng trống trong Excel được chuẩn bị để hiển thị kết quả của hàm GROUPBY.
GROUPBY là lựa chọn tối ưu cho tập dữ liệu này vì các món ăn và nền ẩm thực được lặp lại nhiều lần. Nếu mỗi mục chỉ xuất hiện một lần, bạn có thể chỉ cần sử dụng các nút bộ lọc để sắp xếp và phân tích dữ liệu. Tuy nhiên, với dữ liệu lặp lại, GROUPBY sẽ tổng hợp dữ liệu từ các danh mục chung lại với nhau, mang lại cái nhìn rõ ràng hơn về phân phối doanh số và đánh giá.
Ví dụ 1: Tính Tổng Doanh Số Theo Nền Ẩm Thực
Để tìm tổng doanh số cho mỗi nền ẩm thực, tại ô F2, bạn nhập công thức sau:
- Bắt đầu với hàm GROUPBY:
=GROUPBY( - Vì bạn muốn nhóm dữ liệu theo nền ẩm thực (
Cuisine), hãy chọn các ô chứa biến này. Trong ví dụ này, dữ liệu nằm trong một bảng Excel được định dạng tên làTabFood, nên một tham chiếu có cấu trúc đến tên cột sẽ được thêm vào công thức:=GROUPBY(TabFood[Cuisine], - Tiếp theo, bạn muốn xem tổng doanh số cho mỗi nền ẩm thực, hãy chọn các ô chứa số liệu doanh số và thêm dấu phẩy:
=GROUPBY(TabFood[Cuisine],TabFood[Sales], - Đối số bắt buộc cuối cùng là hàm được sử dụng để tổng hợp dữ liệu. Trong trường hợp này, vì bạn muốn tìm tổng doanh số cho mỗi nền ẩm thực, bạn cần chèn hàm SUM và đóng ngoặc đơn:
=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)
Khi bạn nhấn Enter, Excel sẽ tổng hợp doanh số cho mỗi nền ẩm thực. Vì bạn chưa bao gồm bất kỳ đối số tùy chọn nào, dữ liệu sẽ được sắp xếp theo thứ tự bảng chữ cái mặc định theo các giá trị trong cột F, và có một hàng tổng cộng ở cuối dữ liệu được trích xuất của bạn.
Tổng hợp doanh số theo loại ẩm thực sử dụng hàm GROUPBY trong Excel, hiển thị kết quả phân tích dữ liệu.
Vì các giá trị trong cột G là số tiền, hãy chọn dữ liệu và nhấp vào biểu tượng “Accounting” trong nhóm Number của tab Home trên ribbon để định dạng.
Nút định dạng Accounting được chọn trong tab Home của Excel để định dạng số tiền.
Ví dụ 2: Tính Đánh Giá Trung Bình Của Khách Hàng Theo Loại Món Ăn
Bây giờ, bạn muốn tìm điểm đánh giá trung bình của khách hàng cho mỗi loại món ăn. Quy trình thực hiện cũng rất tương tự.
- Tại ô
I2, bạn nhập công thức:=GROUPBY( - Tiếp theo, chọn các ô chứa danh mục mà bạn muốn nhóm dữ liệu theo. Trong trường hợp này, đó là các món ăn (
Dish). Hãy 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ờ, chọn các ô chứa dữ liệu cần tổng hợp (đánh giá của khách hàng) và thêm dấu phẩy:
=GROUPBY(TabFood[Dish],TabFood[Customer rating], - Cuối cùng, vì mục tiêu của bạn lần này là tìm điểm trung bình của khách hàng cho mỗi loại món ăn, đối số hàm cần là AVERAGE.
=GROUPBY(TabFood[Dish],TabFood[Customer rating],AVERAGE)
Sau khi bạn nhấn Enter, Excel sẽ tính trung bình điểm đánh giá của khách hàng cho mỗi 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 mặc định theo các giá trị trong cột bên trái và có một hàng tổng cộng tiện lợi ở phía dưới.
Hàm GROUPBY được dùng để tính điểm đánh giá trung bình của khách hàng cho từng món ăn trong Excel.
Vì các giá trị trong cột J là điểm trung bình có số thập phân, bạn có thể tinh chỉnh số lượng chữ số thập phân hiển thị bằng cách nhấp vào các nút “Increase Decimal” và “Decrease Decimal” trong nhóm Number của tab Home.
Các nút Tăng và Giảm số thập phân được đánh dấu trong nhóm Number của tab Home trong Excel.
Nếu bạn hài lòng với kết quả GROUPBY ở giai đoạn này, bạn có thể dừng lại. Tuy nhiên, nếu bạn muốn khám phá toàn bộ sức mạnh của hàm này, hãy tiếp tục đọc để tìm hiểu về các đối số tùy chọn.
Khám Phá Sức Mạnh Của GROUPBY Với Các Đối Số Tùy Chọn
Mặc dù việc có năm đối số tùy chọn bên cạnh ba đối số bắt buộc có thể khiến hàm GROUPBY trông phức tạp hơn, nhưng những tùy chọn bổ sung này thực chất chỉ giúp bạn tạo ra một kết quả đầu ra được điều chỉnh chính xác hơn theo 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 muốn sử dụng và bỏ qua những đối số không cần thiết.
Lưu ý: Để bỏ qua một đối số tùy chọn, hãy sử dụng dấu phẩy để chuyển từ đối số này sang đối số tiếp theo. Ví dụ, nếu bạn muốn đưa vào đối số thứ tư và thứ sáu, nhưng không phải đối số thứ năm, hãy nhập [đối số thứ tư],,[đối số thứ sáu]. Đối số thứ năm lẽ ra sẽ nằm giữa hai dấu phẩy, nhưng vì không có gì ở khoảng trống đó, Excel hiểu rằng bạn đã cố tình để trống đối số này.
Dưới đây, chúng ta sẽ đi sâu vào từng đối số tùy chọn để bạn 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 sử dụng chúng.
Đối Số field_headers (Tiêu Đề Trường)
Trong các ví dụ trước, bạn đã nhập tiêu đề cột đầu ra theo cách thủ công 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ác tiêu đề cột cũng như dữ liệu mà chúng chứa, hãy sử dụng đối số field_headers.
Bắt đầu bằng cách nhập công thức GROUPBY của bạn, bao gồm ba đối số bắt buộc đầu tiên. Ví dụ, giả sử bạn muốn nhóm các nền ẩm thực theo điểm đánh giá trung bình của khách hàng:
=GROUPBY(A1:A21,D1:D21,AVERAGE
Cú pháp hàm GROUPBY trong Excel bao gồm các đối số dữ liệu và tiêu đề.
Hãy chú ý cách các hàng tiêu đề được bao gồm trong các vùng chọn. Thật vậy, khi chọn dữ liệu cho hai đối số đầu tiên, bạn nên suy nghĩ trước về việc liệu bạn có muốn dữ liệu đầu ra của mình sao chép các tiêu đề trong bảng gốc hay không.
Quan trọng: Các đối số row_fields và values phải có cùng kích thước. Nếu bạn chọn tiêu đề trong một đối số, bạn phải chọn tiêu đề trong đối số kia.
Cuối cùng, nhập dấu phẩy để chuyển sang đối số field_headers và nhập:
0(hoặc bỏ qua): Nếu bạn không chọn tiêu đề trong hai đối số đầu tiên và không muốn hiển thị tiêu đề trong kết quả.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 các tiêu đề chung chung trong kết quả.3: Nếu bạn đã chọn tiêu đề trong hai đối số đầu tiên, và bạn muốn Excel hiển thị chúng trong kết quả.
Dưới đây là kết quả khi tôi nhập:
=GROUPBY(A1:A21,D1:D21,AVERAGE,3)
Kết quả hàm GROUPBY trong Excel khi sử dụng đối số field_headers để hiển thị tiêu đề cột.
Bây giờ bạn có thể định dạng các tiêu đề cột trùng lặp của mình để chúng dễ phân biệt với dữ liệu, giống như trong bảng gốc.
| Lợi ích khi đưa tiêu đề trường | Hạn chế khi đưa tiêu đề trường |
|---|---|
| Nếu bạn thay đổi tiêu đề trong bảng gốc của mình, các tiêu đề đầu ra sẽ tự động cập nhật theo các thay đổi đó. | 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 so với tiêu đề bảng gốc mà không làm mất tính tự động cập nhật. |
Đối Số total_depth (Độ Sâu Tổng Cộng)
Đối số total_depth cho phép bạn quyết định liệu bạn có muốn kết quả hiển thị tổng cộng hay không, và nếu có, chúng nên 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ố total_depth, nhập:
0: Nếu bạn không muốn hiển thị bất kỳ tổng cộng hoặc tổng phụ nào.1: Nếu bạn chỉ muốn tổng cộng đượ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à một tổng cộng ở cuối kết quả tổng thể.-1: Nếu bạn chỉ muốn tổng cộng được hiển thị ở đầu kết quả.-2: Nếu bạn muốn tổng phụ xuất hiện ở đầu mỗi danh mục kết quả và một tổng cộng ở đầu kết quả tổng thể.
Lưu ý: Các tùy chọn để hiển thị tổng phụ (2 và -2) chỉ hoạt động nếu đối số row_fields chứa nhiều hơn một cột dữ liệu (nói cách khác, các trường con – subfields).
Trong ví dụ này, tôi đã nhập:
=GROUPBY(A1:B21,C1:C21,SUM,,2)Công thức này sử dụng dấu phẩy để bỏ qua đối số field_headers và yêu cầu Excel hiển thị tổng phụ 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.
Kết quả hàm GROUPBY trong Excel hiển thị tổng phụ và tổng cộng được định dạng màu xanh, sử dụng đối số total_depth.
Đối Số sort_order (Thứ Tự Sắp Xếp)
Trường sort_order cho phép bạn chỉ định liệu và cách bạn muốn sắp xếp kết quả. Việc 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 Pivot Table: ngay khi bạn thay đổi bất kỳ dữ liệu nào trong bảng gốc của mình, toàn bộ dữ liệu đầu ra sẽ tự động sắp xếp lại theo đối số sort_order, trong khi Pivot Table yêu cầu làm mới thủ công.
Số bạn nhập cho đối số này đại diện cho 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 bảng chữ cái. Ngược lại, việ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 ngược bảng chữ cái.
Trong ví dụ này, tôi đã nhập:
=GROUPBY(A1:A21,C1:C21,SUM,,, -2)Công thức này sắp xếp cột thứ hai (doanh số) theo thứ tự giảm dần.
Kết quả hàm GROUPBY được sắp xếp theo cột thứ hai (doanh số) theo thứ tự giảm dần.
Đối Số filter_array (Mảng Lọc)
Đối số filter_array ít có khả năng được sử dụng hơn so với các đối số tùy chọn trước đó, mặc dù nó có thể hữu ích 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 các ô A2, A8 và A17 làm gián đoạn kết quả của hàm GROUPBY.
Kết quả hàm GROUPBY trong Excel bị gián đoạn bởi các dòng dữ liệu không mong muốn chứa năm.
Bạn có thể sử dụng đối số filter_array để yêu cầu Excel bỏ qua bất kỳ ô nào trong cột A chứa số thông qua hàm ISNUMBER:
=GROUPBY(A1:A24,C1:C24,SUM,,,,ISNUMBER(A1:A24)=FALSE)
Hàm GROUPBY với đối số filter_array sử dụng ISNUMBER để loại trừ các ô chứa số (năm).
Đối Số field_relationship (Mối Quan Hệ Trường)
Cuối cùng, đối số field_relationship kiểm soát cách dữ liệu được nhóm khi đối số row_fields tham chiếu nhiều hơn một cột.
Trong ví dụ này, khi đối số field_relationship chứa 0 (là giá trị mặc định nếu đối số bị bỏ qua), GROUPBY trả về một bảng kết quả phân cấp, với mỗi cột được biểu thị riêng lẻ bằng các hàng dữ liệu riêng biệt.
=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)
Kết quả hàm GROUPBY trong Excel với đối số field_relationship được đặt là 0, hiển thị cấu trúc phân cấp.
Mặt khác, khi đối số field_relationship chứa 1, GROUPBY trả về một bảng kết quả bỏ qua phân cấp và sắp xếp mỗi cột một cách độc lập. Nói cách khác, các danh mục không lồng vào 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 chọn tùy chọn mối quan hệ trường này.
=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)
Kết quả hàm GROUPBY trong Excel với đối số field_relationship được đặt là 1, hiển thị sắp xếp độc lập không phân cấp.
Ngoài việc sử dụng hàm SUM và AVERAGE trong đối số hàm GROUPBY, bạn có thể sử dụng hàm PERCENTOF, chuyển đổi dữ liệu thành phần trăm để hiển thị tỷ lệ của một tập con so với toàn bộ tập dữ liệu.
Kết Luận
Hàm GROUPBY trong Excel là một công cụ phân tích dữ liệu vô cùng mạnh mẽ, mang lại sự linh hoạt và khả năng tự động cập nhật vượt trội so với các phương pháp truyền thống như Pivot Table. Từ việc nhóm và tổng hợp dữ liệu cơ bản chỉ với ba đối số bắt buộc, đến việc tinh chỉnh kết quả bằng các đối số tùy chọn như tiêu đề trường, độ sâu tổng cộng, thứ tự sắp xếp, mảng lọc và mối quan hệ trường, GROUPBY cho phép bạn khai thác tối đa tiềm năng của dữ liệu.
Việc làm chủ hàm GROUPBY không chỉ giúp bạn tiết kiệm thời gian mà còn nâng cao độ chính xác và khả năng phân tích chuyên sâu. Cho dù bạn là người mới bắt đầu hay đã có kinh nghiệm với Excel, việc tích hợp GROUPBY vào quy trình làm việc của mình chắc chắn sẽ cải thiện đáng kể hiệu quả xử lý dữ liệu. Hãy thực hành các ví dụ đã trình bày và thử nghiệm với dữ liệu của riêng bạn để khám phá mọi khía cạnh của hàm này. Chắc chắn rằng, GROUPBY sẽ trở thành một trợ thủ đắc lực trong hành trình phân tích và đưa ra quyết định dựa trên dữ liệu của bạn.
