Trong thế giới phân tích dữ liệu ngày nay, việc xử lý và tổng hợp thông tin một cách hiệu quả là yếu tố then chốt giúp đưa ra các quyết định kinh doanh sáng suốt. Mặc dù các công cụ truyền thống như Pivot Table trong Excel đã trở thành người bạn đồng hành quen thuộc của nhiều người dùng, nhưng sự ra đời của hàm GROUPBY đã mang đến một phương pháp mới mẻ và mạnh mẽ hơn để nhóm, tổng hợp dữ liệu linh hoạt.
Hàm GROUPBY trong Excel cho phép bạn nhóm và tổng hợp dữ liệu dựa trên các trường cụ thể trong bảng của mình. Không chỉ vậy, nó còn cung cấp các đối số tùy chọn giúp bạn sắp xếp và lọc dữ liệu, tối ưu hóa kết quả đầu ra theo nhu cầu chính xác của bạn. So với việc sử dụng Pivot Table, ưu điểm nổi bật của GROUPBY là khả năng tự động làm mới khi dữ liệu gốc thay đổi hoặc được sắp xếp lại, đồng thời cho phép tích hợp các hàm khác để tinh chỉnh quá trình phân loại dữ liệu một cách chuyên nghiệp hơn. Đây chính là lý do tại sao GROUPBY đang dần trở thành công cụ không thể thiếu cho những ai muốn khai thác tối đa tiềm năng của dữ liệu trong Excel.
Cú pháp Hàm GROUPBY Excel: Hiểu Rõ Các Đối Số
Hàm GROUPBY sở hữu một cú pháp linh hoạt với tám đối số, giúp người dùng tùy chỉnh kết quả một cách chi tiết:
=GROUPBY(a,b,c,d,e,f,g,h)Trong đó, ba đối số đầu tiên là bắt buộc và không thể thiếu để hàm hoạt động:
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ố cần tổng hợp.c(function): Hàm được sử dụng để tổng hợp các giá trị trong đối sốb. Các hàm phổ biến bao gồmSUM,AVERAGE,COUNT,MAX,MIN, v.v.
Năm đối số còn lại là tùy chọn, cung cấp khả năng điều khiển nâng cao cho đầu ra:
d(field_headers): Một số chỉ định liệu bạn có chọn tiêu đề trường 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 hay không.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 totals) và/hoặc tổng phụ (subtotals) hay không, và vị trí của chúng.f(sort_order): Một số chỉ định cách kết quả được sắp xếp (tăng dần hoặc giảm dần) dựa trên một cột cụ thể.g(filter_array): Một công thức dựa trên mảng dùng để lọc bỏ thông tin không mong muốn khỏi dữ liệu gốc trước khi nhóm.h(field_relationship): Một số chỉ định mối quan hệ giữa các trường khi đối sốacung cấp nhiều cột.
Khai Thác Sức Mạnh GROUPBY với Các Đối Số Bắt Buộc
Nếu bạn cảm thấy choáng ngợp trước số lượng đối số mà hàm GROUPBY có, hãy yên tâm rằng hàm này vẫn hoạt động hoàn hảo ngay cả khi bạn chỉ cung cấp ba đối số bắt buộc: a, b, và c. Dưới đây là cách sử dụng cơ bản nhưng mạnh mẽ của GROUPBY với các đối số này.
Hãy hình dung bạn là chủ 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 được tổng doanh thu và điểm đánh giá trung bình của khách hàng cho mỗi sự kết hợp giữa ẩm thực và món ăn.
Bảng dữ liệu Excel với doanh số và đánh giá món ăn theo ẩm thực và món ăn
Mặc dù những số liệu 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 muốn tìm hiểu 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ề các mảng tràn (spilled arrays), bạn không thể sử dụng các bảng Excel đã định dạng cho kết quả của chúng.
Hai bảng trống sẵn sàng nhận kết quả từ hàm GROUPBY trong Excel
Hãy dành một chút thời gian để giải thích tại sao GROUPBY sẽ là lựa chọn tối ưu cho các tác vụ này với bộ dữ liệu cụ thể này. Nếu mỗi nền ẩm thực 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 và phân tích dữ liệu. Tuy nhiên, vì các nền ẩm thực và món ăn lặp lại, việc sử dụng hàm GROUPBY sẽ giúp bạn 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 tổng thể rõ ràng hơn về phân bố doanh số và điểm đánh giá.
Ví dụ 1: Tính Tổng Doanh Thu Theo Ẩm Thực
Để tìm tổng doanh thu cho mỗi nền ẩm thực, tại ô F2, bạn gõ:
=GROUPBY(Vì bạn muốn nhóm dữ liệu theo ẩm thực, 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 một bảng Excel đã định dạng tên là TabFood, 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, vì bạn muốn xem tổng doanh thu cho mỗi nền ẩm thực đó, 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 để tổng hợp dữ liệu. Trong trường hợp này, vì bạn muốn tìm tổng doanh thu cho mỗi nền ẩm thực, bạn cần chèn hàm SUM và đóng ngoặc:
=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)Ngoài việc 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 tùy chỉnh theo 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 thu 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 trong hàm GROUPBY, dữ liệu được sắp xếp theo thứ tự chữ cái mặc định theo các giá trị trong cột F và có một hàng tổng ở cuối dữ liệu đã trích xuất.
Vì các giá trị trong cột G là tiền tệ, hãy chọn dữ liệu và nhấp vào biểu tượng “Accounting” (Kế toán) trong nhóm Number (Số) trên tab Home (Trang chủ) của dải băng Excel.
Chọn dữ liệu số trong Excel và biểu tượng định dạng kế toán được tô sáng
Ví dụ 2: Tính Điểm Đánh Giá Trung Bình Theo 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, và quy trình thực hiện rất tương tự.
Trong ô I2, bạn gõ:
=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 khác nhau. 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 và thêm một dấu phẩy nữa:
=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 đánh giá trung bình của khách hàng cho từng loại món ăn, đối số hàm cần phải là AVERAGE.
=GROUPBY(TabFood[Dish],TabFood[Customer rating],AVERAGE)Sau khi nhấn Enter, Excel sẽ tính trung bình điểm đánh giá 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ự 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 tiện lợi ở cuối.
Hàm GROUPBY Excel hiển thị đánh giá trung bình theo món ăn
Vì các giá trị trong cột J là các số trung bình thập phân, hãy định dạng lại 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” (Tăng số thập phân) và “Decrease Decimal” (Giảm số thập phân) trong nhóm Number (Số) của tab Home (Trang chủ).
Chọn các ô chứa số thập phân và các nút tăng giảm số thập phân được tô sáng 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ể ngừng đọc tại đây. Tuy nhiên, hãy tiếp tục để tìm hiểu về các đối số tùy chọn của GROUPBY để nâng cao khả năng phân tích dữ liệu của mình.
Nâng Tầm Phân Tích với Các Đối Số Tùy Chọn của GROUPBY
Mặc dù hàm GROUPBY có năm đối số tùy chọn bên cạnh ba đối số bắt buộc, điều này có thể khiến nó trông phức tạp hơn, nhưng thực tế những tùy chọn bổ sung này chỉ nhằm giúp bạn tạo ra một đầ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 muốn sử dụng và bỏ qua những đối số không cần thiết.
Dưới đây, tôi sẽ đề cập đến 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 để chuyển 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 gõ [đố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ì trong khoảng trống đó, Excel hiểu rằng bạn đã cố ý để trống đối số này.
Đối số d (field_headers): Hiển Thị Tiêu Đề Dữ Liệu
Trong các ví dụ trên, chúng ta đã tự gõ 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á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 gõ công thức GROUPBY của bạn, bao gồm ba đối số bắt buộc đầu tiên. Trong trường hợp này, 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
Dữ liệu thô và tiêu đề được thêm vào công thức GROUPBY trong Excel
Lưu ý cách các hàng tiêu đề được bao gồm trong các lựa chọn. Thực vậy, khi chọn dữ liệu cho hai đối số đầu tiên, bạn nên nghĩ trước xem mình có muốn dữ liệu đầu ra sao chép các tiêu đề trong bảng gốc hay khô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 đề ở một trong hai, bạn phải chọn tiêu đề ở cái còn lại.
Cuối cùng, gõ dấu phẩy để chuyển sang đối số field_headers và gõ một trong các số sau:
- 1 nếu bạn đã chọn các tiêu đề trong hai đối số đầu tiên, nhưng 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 muốn Excel tạo tiêu đề chung trong kết quả.
- 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ả.
Dưới đây là kết quả khi tôi gõ:
=GROUPBY(A1:A21,D1:D21,AVERAGE,,3)
Kết quả của hàm GROUPBY với đối số Field Headers hiển thị tiêu đề
Giờ đây, tôi có thể định dạng các tiêu đề cột trùng lặp để chúng dễ dàng phân biệt 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 | Nhược điểm 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ẽ cập nhật theo. | Bạn không thể thay đổi tiêu đề đầu ra nếu muốn chúng cụ thể hơn tiêu đề bảng gốc. |
Đối số e (total_depth): Tùy Biến Tổng Cộng & Tổng Phụ
Đố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 (grand totals) hay không, và nếu có, liệu chúng có 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 có hiển thị tổng phụ (subtotals) hay không.
Đối với đối số total_depth, hãy gõ:
- 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 hiển thị tổng cộng ở 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 toàn bộ kết quả.
- -1 nếu bạn chỉ muốn hiển thị tổng cộng ở đầ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à tổng cộng ở đầu toàn bộ kết quả.
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 (tức là các trường con).
Trong ví dụ này, tôi đã gõ:
=GROUPBY(A1:B21,C1:C21,SUM,,2)Công thức này sử dụng các dấu phẩy để bỏ qua đối số field_headers và yêu cầu Excel hiển thị tổng phụ 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ụ để làm cho dữ liệu dễ đọc hơn.
Hàm GROUPBY với đối số Total Depth hiển thị các tổng phụ và tổng cộng được định dạng màu xanh
Đối số f (sort_order): Sắp Xếp Kết Quả Linh Hoạt
Trường sort_order cho phép bạn chỉ định cho Excel 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 sau 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ẽ 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 gõ 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 gõ -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 đã gõ:
=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ố g (filter_array): Lọc Dữ Liệu Nâng Cao
Đối số filter_array ít được sử dụng hơn so với các đối số tùy chọn trước đó, nhưng 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 ô A2, A8 và A17 làm gián đoạn kết quả của hàm GROUPBY.
Kết quả hàm GROUPBY bị gián đoạn bởi các ô chứa năm trong dữ liệu gốc
Tôi 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)
Sử dụng hàm ISNUMBER trong đối số filter array của GROUPBY để loại bỏ các năm
Đối số h (field_relationship): Quản Lý Mối Quan Hệ Giữa Các 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 đến nhiều hơn một cột.
Trong ví dụ này, khi đối số field_relationship chứa 0 (là 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 đại diện riêng lẻ bằng các hàng dữ liệu riêng biệt.
=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)
Bảng tính Excel hiển thị hàm GROUPBY với đối số field relationship bằng 0 (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 từng cột một cách độc lập. Nói cách khác, các danh mục không được lồng vào nhau, đó là lý do tại sao bạn cũng không thể bao gồm các 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)
Bảng tính Excel hiển thị hàm GROUPBY với đối số field relationship bằng 1 (không phân cấp)
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 PERCENTOF, biến dữ liệu thành tỷ lệ phần trăm để hiển thị tỷ lệ một tập hợp con tạo nên một tập dữ liệu tổng thể.
Kết Luận
Hàm GROUPBY trong Excel là một công cụ mạnh mẽ và linh hoạt, mang lại khả năng nhóm và tổng hợp dữ liệu vượt trội so với các phương pháp truyền thống như Pivot Table. Với khả năng tự động cập nhật và cú pháp tùy biến, GROUPBY giúp bạn nhanh chóng biến dữ liệu thô thành những thông tin chi tiết có giá trị, hỗ trợ đắc lực trong việc ra quyết định.
Dù bạn chỉ cần những tổng hợp cơ bản hay các phân tích phức tạp với điều kiện lọc và sắp xếp riêng, GROUPBY đều có thể đáp ứng. Bằng cách nắm vững các đối số bắt buộc và tận dụng tối đa các đối số tùy chọn, bạn sẽ trở thành một chuyên gia phân tích dữ liệu hiệu quả hơn, tiết kiệm thời gian và tối ưu hóa quy trình làm việc.
Hãy thử áp dụng hàm GROUPBY vào các bộ dữ liệu của riêng bạn ngay hôm nay để trải nghiệm sự khác biệt. Nếu có bất kỳ thắc mắc hay chia sẻ kinh nghiệm nào, đừng ngần ngại để lại bình luận phía dưới!
