Trong thế giới phân tích dữ liệu ngày càng phức tạp, việc sắp xếp và tổng hợp 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. Với vai trò là chuyên gia tại tinmoicongnghe.com, chúng tôi luôn tìm kiếm và giới thiệu những công cụ, thủ thuật công nghệ giúp độc giả tối ưu hóa công việc. Hàm GROUPBY trong Excel là một trong số đó – một công cụ mạnh mẽ, được thiết kế để đơn giản hóa quá trình nhóm và tổng hợp dữ liệu, vượt trội hơn các phương pháp truyền thống như Pivot Table ở nhiều khía cạnh.
Hàm GROUPBY 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 mình. Ngoài ra, nó còn cung cấp các đối số tùy chọn để sắp xếp và lọc dữ liệu, giúp bạn điều chỉnh đầu ra theo nhu cầu cụ thể. Mặc dù bạn có thể sử dụng Pivot Table để đạt được kết quả tương tự, nhưng hàm GROUPBY có ưu điểm vượt trội là tự động làm mới khi dữ liệu gốc thay đổi hoặc được sắp xếp lại. Hơn nữa, GROUPBY còn cho phép bạn nhúng nhiều hàm khác nhau để phân loại dữ liệu tinh vi và linh hoạt hơn, khẳng định vị thế dẫn đầu trong các công cụ phân tích dữ liệu của Excel.
Cú Pháp Hàm GROUPBY Trong Excel
Hàm GROUPBY có tám đối số, cung cấp sự linh hoạt tối đa cho người dùng:
=GROUPBY(a,b,c,d,e,f,g,h)Trong đó, các đối số từ a đến c là bắt buộc:
- a (row fields – trường hàng): Là dải ô (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 – giá trị): Là dải ô (một hoặc nhiều cột) chứa các giá trị cần tổng hợp.
- c (function – hàm): Là 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. Việc hiểu rõ các đối số này sẽ giúp bạn tùy chỉnh kết quả đầu ra chi tiết hơn, phù hợp với mọi yêu cầu phân tích dữ liệu phức tạp:
- d (field headers – tiêu đề trường): Một số chỉ định liệu bạn có chọn tiêu đề trong các đối số a và b hay 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 – độ sâu tổng hợp): Một số xác định liệu kết quả có nên hiển thị tổng cộng hay không.
- f (sort order – thứ tự sắp xếp): Một số chỉ ra cách sắp xếp kết quả.
- g (filter array – mảng lọc): Một công thức theo mảng để lọc bỏ thông tin không mong muốn.
- h (field relationship – mối quan hệ trường): Một số chỉ định mối quan hệ giữa các trường khi nhiều cột được cung cấp trong đối số a.
GROUPBY Trong Thực Tế: Chỉ Với Các Đối Số Bắt Buộc
Nếu bạn cảm thấy quá tải với số lượng lớn các đối số mà hàm GROUPBY có, điều quan trọng cần lưu ý là hàm GROUPBY 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. Trước tiên, chúng tôi sẽ hướng dẫn 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 từ các nền ẩm thực khác nhau, và bạn đã tổng hợp tổng doanh số bán hàng cùng đ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 mẫu về doanh số bán hàng và đánh giá khách hàng của các món ăn theo từng loại ẩm thực, dùng để minh họa hàm GROUPBY.
Mặc dù những con số này hữu ích, nhưng 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 hiểu tổng số tiền mà mỗi loại ẩ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 trong Excel chờ kết quả được điền tự động bằng hàm GROUPBY, hiển thị tổng doanh số và đánh giá trung bình.
Chúng tôi sẽ giải thích lý do GROUPBY 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ếu mỗi ẩ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ì ẩm thực và món ăn được 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 phối doanh số và điểm đánh giá.
Ví dụ 1: Tổng doanh số theo ẩm thực
Để tìm tổng doanh số cho mỗi loại ẩm thực, tại ô F2, hãy nhập:
=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],
Biểu tượng Microsoft Excel trên nền xanh lá cây, minh họa công cụ bảng tính mạnh mẽ này.
Tiếp theo, vì bạn muốn xem tổng doanh số cho mỗi loại ẩm thực đó, hãy chọn các ô chứa những 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 tổng doanh số cho mỗi loại ẩ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)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 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 mỗi loại ẩ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 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 đã trích xuất của bạn.
Kết quả của hàm GROUPBY trong Excel, tổng hợp tổng doanh số bán hàng theo từng loại ẩm thực với tổng cộng cuối cùng.
Vì các giá trị trong cột G là tài chính, 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 thanh ribbon để định dạng tiền tệ chuyên nghiệp hơn.
Các số liệu tài chính được chọn trong Excel và nút định dạng Accounting được đánh dấu để làm tròn và hiển thị tiền tệ.
Ví dụ 2: Đánh giá khách hàng 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ự.
Tại ô I2, hãy 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 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 đích 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 mỗi loại món ăn, đối số hàm cần phải là AVERAGE.
=GROUPBY(TabFood[Dish],TabFood[Customer rating],AVERAGE)
Biểu tượng Microsoft Excel trên nền màu sắc rực rỡ, đại diện cho khả năng tính toán trung bình của phần mềm.
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.
Kết quả của hàm GROUPBY trong Excel, tổng hợp điểm đánh giá trung bình của khách hàng theo từng món ăn.
Vì các giá trị trong cột J là điểm trung bình dạng thập phân, hãy điều 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 để làm cho dữ liệu trở nên dễ đọc và gọn gàng hơn.
Dải ô trong Excel chứa số thập phân được chọn, với các nút Tăng và Giảm số thập phân được đánh dấu để điều chỉnh hiển thị.
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, hãy tiếp tục đọ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 bạn.
GROUPBY Nâng Cao: Sử Dụng 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 khiến nó có vẻ 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 đầ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 cái bạn không cần.
Dưới đây, chúng 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 bao gồm chúng.
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 nhập [đố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 bỏ trống đối số này.
Đối Số Field Headers (Tiêu Đề Trường)
Trong các ví dụ trên, chúng tôi đã tự nhập các 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 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. Trong trường hợp này, giả sử bạn muốn nhóm các loại ẩ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 chưa định dạng cùng tiêu đề được thêm vào công thức GROUPBY trong Excel để nhóm và tổng hợp.
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 cho hai đối số đầu tiên, bạn nên suy nghĩ trướ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.
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ố còn lại.
Cuối cùng, nhập dấu phẩy để chuyển sang đối số field headers và nhập:
- 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ự động tạo các 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 nhập:
=GROUPBY(A1:A21,D1:D21,AVERAGE,**3**)
Kết quả của công thức GROUPBY trong Excel bao gồm đối số field headers, hiển thị tiêu đề cột cùng với dữ liệu tổng hợp.
Bây giờ, tôi có thể định dạng các tiêu đề cột đã được sao ché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 | Hạn 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, các tiêu đề đầu ra sẽ tự động cập nhật. | 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ố Total Depth (Độ Sâu Tổng Hợp)
Đố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 lớn (grand totals) 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ụ (subtotals).
Đối với đối số total depth, hãy 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 hiển thị tổng cộng lớn ở 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 lớn ở cuối toàn bộ kết quả.
- -1 nếu bạn chỉ muốn hiển thị tổng cộng lớn ở đầ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 lớn ở đầ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 (nói cách khác, có các trường con – subfields).
Trong ví dụ này, tôi đã nhập:
=GROUPBY(A1:B21,C1:C21,SUM,,**2**)Điều này sử dụng dấu phẩy để bỏ qua đối số field categories, và báo cho Excel rằng tôi muốn các tổng phụ xuất hiện dưới mỗi danh mục và một tổng cộng lớn ở 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.
Đối số Total Depth trong hàm GROUPBY Excel được sử dụng để hiển thị các hàng tổng phụ và tổng cộng lớn, được định dạng màu xanh dương.
Đối Số Sort Order (Thứ Tự Sắp Xếp)
Đối số 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, 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**)Điều này sắp xếp cột thứ hai (doanh số) theo thứ tự giảm dần.
Kết quả của công thức GROUPBY trong Excel đã đượ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ả của công thức GROUPBY trong Excel bị gián đoạn bởi các ô chứa dữ liệu năm, gây ảnh hưởng đến việc tổng hợp.
Chúng tôi có thể sử dụng đối số filter array để báo cho 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 ISNUMBER được sử dụng trong đối số filter array của hàm GROUPBY để loại bỏ các giá trị số (năm) khỏi cột A.
Biểu tượng Microsoft Excel trên nền xanh lá cây, minh họa công cụ bảng tính mạnh mẽ này.
Đố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 đế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 minh họa hàm GROUPBY với đối số field relationship được đặt là 0, cho kết quả 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 độ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 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 minh họa hàm GROUPBY với đối số field relationship được đặt là 1, cho kết quả nhóm độc lậ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, hàm này 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 thành một phần của toàn bộ tập dữ liệu.
Kết thúc với hàm GROUPBY trong Excel, bạn đã khám phá một công cụ mạnh mẽ giúp đơn giản hóa việc nhóm và tổng hợp dữ liệu, mang lại sự linh hoạt và tự động hóa cao hơn so với các phương pháp truyền thống. Từ việc chỉ sử dụng các đối số cơ bản để nhanh chóng có được cái nhìn tổng quan, đến việc tận dụng các đối số tùy chọn để tùy chỉnh sâu sắc kết quả đầu ra, GROUPBY cho phép bạn biến những bộ dữ liệu phức tạp thành những thông tin dễ hiểu và có giá trị.
Nắm vững 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 của mình trong Excel. Hãy bắt đầu áp dụng hàm này vào công việc hàng ngày để trải nghiệm sự khác biệt. Đừng ngần ngại thử nghiệm với các đối số khác nhau và các hàm tổng hợp đa dạng để khám phá toàn bộ tiềm năng của GROUPBY.
Hãy chia sẻ những trải nghiệm của bạn khi sử dụng hàm GROUPBY trong phần bình luận bên dưới, hoặc tham khảo thêm các hướng dẫn thủ thuật Excel khác của tinmoicongnghe.com để nâng cao kỹ năng công nghệ của bạn!