Trong thế giới dữ liệu ngày nay, khả năng tổng hợp và phân tích thông tin một cách hiệu quả là chìa khóa để đưa ra các quyết định sáng suốt. 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 bổ sung mạnh mẽ gần đây chính là hàm GROUPBY trong Excel, một tính năng giú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. Mặc dù bạn có thể sử dụng Pivot Table (bảng xoay) để đạt được kết quả tương tự, hàm GROUPBY lại mang đến những lợi thế vượt trội về khả năng tự động cập nhật khi dữ liệu 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 việc sắp xếp dữ liệu một cách linh hoạt hơn.
Với GROUPBY, bạn không chỉ đơn thuần là xem xét các con số mà còn có thể khai thác sâu hơn vào các mối quan hệ và xu hướng ẩn giấu trong tập dữ liệu của mình. Bài viết này sẽ đi sâu vào cú pháp của hàm GROUPBY, từ những đối số bắt buộc đơn giả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 để tối ưu hóa quy trình phân tích dữ liệu trên Excel. Hãy cùng tincongnghemoi.com khám phá cách GROUPBY có thể biến đổi cách bạn tương tác với dữ liệu, mang lại cái nhìn tổng thể rõ ràng và hiệu quả hơn.
Cú Pháp Hàm GROUPBY Trong Excel
Hàm GROUPBY có tổng cộng tám đối số, mang lại sự linh hoạt đáng kinh ngạc trong việc tổng hợp dữ liệu:
=GROUPBY(a,b,c,d,e,f,g,h)Trong đó, ba đối số đầu tiên (a đến c) là bắt buộc và không thể thiếu:
a(row fields – trường hàng): Đây là phạm vi (một hoặc nhiều cột) chứa các giá trị hoặc danh mục mà bạn muốn nhóm dữ liệu theo. Ví dụ: loại sản phẩm, khu vực, ngày tháng.b(values – giá trị): Đây là phạm vi (một hoặc nhiều cột) chứa các giá trị số mà bạn muốn tổng hợp. Ví dụ: doanh số, số lượng, điểm đánh giá.c(function – hàm): Là 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ồm SUM (tổng), AVERAGE (trung bình), COUNT (đếm), MAX (giá trị lớn nhất), MIN (giá trị nhỏ nhất).
Năm đối số còn lại (d đến h) là tùy chọn, cho phép bạn tinh chỉnh kết quả đầu ra theo nhu cầu cụ thể:
d(field headers – tiêu đề trường): Một số quy đị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ả hay không.e(total depth – độ sâu tổng cộng): Một số xác định liệu kết quả có hiển thị tổng cộng (grand totals) hay không, và nếu có thì ở vị trí nào (đầu hoặc cuối). Đối số này cũng cho phép hiển thị tổng phụ (subtotals).f(sort order – thứ tự sắp xếp): Một số chỉ ra cách kết quả được sắp xếp. Bạn có thể sắp xếp theo cột bất kỳ trong kết quả theo thứ tự tăng hoặc giảm dần.g(filter array – mảng lọc): Một công thức mảng được sử 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 và tổng hợp.h(field relationship – quan hệ trường): Một số quy định mối quan hệ giữa các trường khi bạn cung cấp nhiều cột trong đối sốa(ví dụ: nhóm theo phân cấp hoặc độc lập).
GROUPBY Thực Hành: Chỉ Với Các Đối Số Bắt Buộc
Nếu bạn cảm thấy bối rối trước số lượng lớn các đối số của hàm GROUPBY, điều quan trọng cần lưu ý là hàm này vẫn hoạt động hoàn hảo ngay cả khi bạn chỉ điền các đối số a, b và c. Đầu tiên, chúng ta sẽ khám phá cách hàm GROUPBY hoạt động chỉ với ba đối số bắt buộc này.
Hãy tưởng tượng 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 tổng doanh số và điểm đánh giá khách hàng trung bình cho mỗi sự kết hợp giữa loại hình ẩm thực và món ăn.
Bảng dữ liệu Excel ví dụ về doanh số bán hàng và đánh giá khách hàng theo loại hình ẩm thực và món ăn.
Mặc dù những con số này hữu ích, có thể bạn quan tâm nhiều hơn đến việc so sánh dữ liệu giữa các danh mục khác nhau. Cụ thể, bạn có thể muốn tìm ra tổng doanh thu mà mỗi loại hình ẩm thực mang lại và điểm đánh giá khách hàng trung bình 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 sẵn cho kết quả của chúng.
Hai bảng trích xuất dữ liệu trống trong Excel, chuẩn bị được điền bằng hàm GROUPBY.
Hãy dành một chút thời gian để giải thích tại sao GROUPBY sẽ là hàm lựa chọn hàng đầu của tôi để thực hiện các tác vụ này cho bộ dữ liệu cụ thể này. Nếu mỗi loại hình ẩ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 bộ 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 loại hình ẩm thực và món ăn được lặp lại, việc sử dụng hàm GROUPBY sẽ cho phé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 phối doanh số và điểm đánh giá.
Tính Tổng Doanh Thu Theo Loại Ẩm Thực
Để tìm ra tổng doanh số cho từng loại hình ẩm thực, tại ô F2, bạn gõ:
=GROUPBY(Vì bạn đang muốn nhóm dữ liệu theo loại hình ẩ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 được đị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 số cho mỗi loại hình ẩm thực đó, hãy chọn các ô chứa các con số 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 sẽ đượ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 ra tổng doanh số cho mỗi loại hình ẩm thực, bạn cần chèn hàm SUM và đóng dấu ngoặc đơn:
=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)Khi bạn nhấn Enter, bạn sẽ thấy Excel đã tổng hợp tổng doanh số cho từng loại hình ẩ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ự 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.
Vì các giá trị trong cột G là số liệu tài chính, 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ố) của tab Home (Trang chủ) trên dải băng.
Định dạng tiền tệ cho cột doanh số trong Excel bằng cách chọn dữ liệu và nhấn biểu tượng Accounting.
Tính Đánh Giá Khách Hàng Trung Bình Theo Món Ăn
Bây giờ, bạn muốn tìm ra điểm đánh giá khách hàng trung bình cho từng loại món ăn, và quy trình thực hiện rất tương tự.
Tại ô 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 ra điểm 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 bạn 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ự bảng chữ cái theo các giá trị trong cột bên trái theo mặc định, và có một hàng tổng cộng tiện dụng ở cuối.
Kết quả sử dụng hàm GROUPBY để tính đánh giá khách hàng trung bình theo từng món ăn trong Excel.
Vì các giá trị trong cột J là điểm trung bình với phần thập phân, hãy điều chỉnh số 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 của tab Home.
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ể ngừng đọc tại đâ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, giúp bạn tùy chỉnh kết quả một cách chuyên nghiệp hơn.
GROUPBY Thực Hành: Khai Thác Các Đối Số Tùy Chọn
Mặc dù việc hàm GROUPBY có năm đối số tùy chọn bên cạnh ba đối số bắt buộc có vẻ làm cho nó 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 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 cần.
Hãy nhớ rằng, để bỏ qua một đối số tùy chọn, bạn chỉ cần gõ dấu phẩy để chuyển 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 phải đối số thứ năm, hãy gõ [đối số thứ tư],,[đối số thứ sáu]. Đối số thứ năm 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ố tình để trống đối số này.
Dưới đây, chúng 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 bao gồm chúng.
Đối Số Field Headers (Tiêu Đề Trường)
Trong các ví dụ đã trình bày ở trên, chúng ta đã gõ 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 gõ 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 loại hình ẩm thực theo điểm đánh giá khách hàng trung bình:
=GROUPBY(A1:A21,D1:D21,AVERAGE
Thêm dữ liệu thô và tiêu đề cột vào công thức hàm GROUPBY để nhóm dữ liệu.
Lưu ý cách các hàng tiêu đề được bao gồm trong các lựa chọn. Thực tế, khi chọn dữ liệu cho hai đối số đầu tiên, bạn nên nghĩ trước xem 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.
Lưu ý: Các đối số trường hàng và giá trị phải có cùng kích thước. Nếu bạn chọn tiêu đề ở một trong hai đối số, bạn phải chọn tiêu đề ở đối số 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 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ả.
- 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)
Kết quả trả về của hàm GROUPBY khi sử dụng đối số Field Headers để hiển thị tiêu đề cột.
Bây giờ tôi có thể định dạng các tiêu đề cột đã được sao ché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 bao gồm tiêu đề trường | Hạn chế khi bao gồm tiêu đề trường |
|---|---|
| Nếu bạn thay đổi tiêu đề trong bảng gốc, các tiêu đề đầu ra sẽ tự động thay đổi theo. | Bạn không thể thay đổi tiêu đề đầu ra nếu bạn muốn chúng cụ thể hơn các tiêu đề bảng gốc. |
Đố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 (grand totals) hay không, và nếu có, liệu 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ụ (subtotals).
Đối với đối số total depth, bạn 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 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ả chung.
- -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ả chung.
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ố trường hàng chứa nhiều hơn một cột dữ liệu (nói cách khác, có các trường con).
Trong ví dụ này, tôi đã gõ:
=GROUPBY(A1:B21,C1:C21,SUM,,2)Điều 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à một tổng cộng ở cuối dữ liệu. Tôi sau đó đã á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.
Hiển thị tổng phụ và tổng cộng trong kết quả GROUPBY với đối số Total Depth, dữ liệu được định dạng màu xanh để dễ đọc.
Đối Số Sort Order (Thứ Tự Sắp Xếp)
Đối số sort order cho phép bạn quy đị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, 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.
Con 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, kết quả sẽ được sắp xếp 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)Điều 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 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 ô 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 do dữ liệu năm không liên quan xen kẽ trong cột.
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 kết hợp với đối số Filter Array trong GROUPBY để loại bỏ các dòng chứa số không phải dữ liệu chính.
Đối Số Field Relationship (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 đế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)
Kết quả hàm GROUPBY với đối số Field Relationship được đặt thành 0, hiển thị cấu trúc phân cấp dữ liệu.
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 tổng phụ trong kết quả khi chọn tùy chọn quan hệ trường này.
=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)
Kết quả hàm GROUPBY với đối số Field Relationship được đặt thành 1, hiển thị dữ liệu độc lập, 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, giúp chuyển đổi dữ liệu thành tỷ lệ phần trăm để hiển thị phần nhỏ tạo nên 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ẽ và linh hoạt, mang lại khả năng nhóm và tổng hợp thông tin một cách tự động và chi tiết hơn so với các phương pháp truyền thống như Pivot Table. Với khả năng tự động làm mới, tích hợp các hàm tùy chỉnh và kiểm soát chi tiết qua tám đối số, GROUPBY giúp người dùng Excel nâng cao hiệu quả công việc, từ việc tổng kết doanh số đơn giản đến phân tích dữ liệu phức tạp với nhiều lớp tiêu chí.
Việc nắm vững từ ba đối số bắt buộc đến việc khai thác triệt để các đối số tùy chọn như Field Headers, Total Depth, Sort Order, Filter Array và Field Relationship sẽ giúp bạn tùy chỉnh kết quả đầu ra theo đúng nhu cầu, mang lại cái nhìn sâu sắc và chính xác về dữ liệu của mình. Hãy bắt đầu áp dụng hàm GROUPBY vào công việc hàng ngày của bạn để biến những bảng tính Excel tưởng chừng khô khan thành nguồn thông tin giá trị, hỗ trợ đắc lực cho mọi quyết định.
Bạn đã từng sử dụng hàm GROUPBY trong công việc của mình chưa? Hãy chia sẻ những kinh nghiệm hoặc mẹo hay của bạn về cách tận dụng tối đa hàm này trong phần bình luận bên dưới nhé!
