Cách sử dụng hàm GROUPBY trong Excel đơn giản và chi tiết, kèm ví dụ

Nếu bạn đang muốn gom nhóm và tính tổng dữ liệu báo cáo trên phần mềm Microsoft thì hàm GROUPBY sẽ giúp bạn giải quyết ngay chỉ với một công thức và tự động cập nhật khi có thay đổi. Cùng khám phá cách sử dụng hàm GROUPBY trong Excel để tối ưu thời gian làm việc trên chiếc laptop hằng ngày nhé!

1 Hàm GROUPBY trong Excel là gì?

Hàm GROUPBY là một hàm mảng động cho phép người dùng tạo bảng tóm tắt dữ liệu bằng cách nhóm các hàng theo tiêu chí cụ thể và thực hiện các phép tính tổng hợp.

Khác với Pivot Table, kết quả của hàm GROUPBY trong Excel sẽ tự động thay đổi ngay khi dữ liệu gốc có sự điều chỉnh mà không cần thao tác làm mới.

Hàm GROUPBY trong Excel giúp tóm tắt dữ liệu và thực hiện phép tính chỉ với một công thức

2 Công thức của hàm GROUPBY trong Excel

Công thức của hàm GROUPBY là:

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])

Trong đó:

(Lưu ý: Các biến số nằm trong dấu ngoặc vuông [...] là tùy chọn, bạn có thể nhập hoặc bỏ trống tùy theo nhu cầu thực tế).

3 Hàm GROUPBY có trong Excel nào?

Hàm GROUPBY là một trong những hàm mảng động mới và cực kỳ mạnh mẽ của Microsoft. Hiện tại, hàm này chỉ có mặt trên các phiên bản Excel sau:

• Microsoft 365: Bao gồm cả bản cài đặt trên các laptop chạy hệ điều hành Windows và MacBook chạy hệ điều hành macOS.
• Excel cho Web (Excel Online): Nền tảng web của Microsoft miễn phí (excel.cloud.microsoft/).

Nếu bạn đang sử dụng các dòng laptop văn phòng đời mới tích hợp sẵn bản quyền Office 365, việc trải nghiệm tính năng này sẽ trở nên mượt mà và ổn định hơn rất nhiều.

4 Cách sử dụng hàm GROUPBY trong Excel

Nhóm dữ liệu và tính tổng cơ bản

Giả sử bạn có bảng dữ liệu gồm cột "Ngành hàng" (Cột B) và "Số lượng bán ra" (Cột D), yêu cầu tính tổng số lượng sản phẩm đã bán ra theo từng ngành hàng.

Tại ô kết quả, nhập công thức:

Trong đó:

• A2:A16 (row_fields): Cột chứa dữ liệu Ngành hàng, gồm những dữ liệu trùng lặp (Tủ lạnh, Máy lạnh, Tivi) mà cần gom nhóm.
• C2:C16 (values): Cột chứa dữ liệu Số lượng bán, gồm những dữ liệu mà bạn muốn đem đi cộng lại với nhau.
• SUM (function): Phép tính tổng.

Sử dụng hàm GROUPBY để gom nhóm dữ liệu và tính tổng cơ bản

Nhóm dữ liệu theo nhiều cột cùng lúc

Cũng với bảng dữ liệu như trên nhưng yêu cầu phức tạp hơn: Tính số lượng sản phẩm theo từng ngành hàng và thương hiệu.

Tại ô kết quả, nhập công thức:

Trong đó:

• A2:B16 (row_fields): Quét chọn 2 cột liền nhau là cột Ngành hàng (A) và cột Thương hiệu (B). Excel sẽ tự động hiểu bạn muốn gom nhóm theo mức độ: Ngành hàng trước, Thương hiệu sau.
• C2:C16 (values): Quét chọn cột Số lượng bán để lấy số liệu tính toán.
• SUM (function): Hàm tính tổng.

Sử dụng hàm GROUPBY để nhóm dữ liệu theo nhiều cột cùng lúc

Áp dụng đồng thời nhiều hàm tổng hợp

Thông thường, đối số function trong hàm GROUPBY chỉ cho phép bạn tính một phép tính (như SUM hoặc AVERAGE).

Nhưng nếu bạn lồng hàm HSTACK (nối mảng theo chiều ngang) hoặc hàm VSTACK (nối mảng theo chiều dọc) vào vị trí này, bạn có thể ép Excel thực hiện nhiều phép tính cùng một lúc trên cùng một tập dữ liệu.

Giả sử yêu cầu: "Hãy thống kê xem mỗi Ngành hàng bán được tổng cộng bao nhiêu sản phẩm, và trung bình mỗi lần xuất kho là bao nhiêu?".

Tại ô kết quả, nhập công thức:

Trong đó:

• B2:B16 (row_fields): Quét cột Ngành hàng để gom nhóm.
• D2:D16 (values): Quét cột Số lượng bán để lấy số liệu tính toán.
• HSTACK(SUM, AVERAGE) (function): Hàm HSTACK sẽ yêu cầu Excel thực hiện đồng thời 2 lệnh: tính Tổng (SUM) và tính Trung bình (AVERAGE), sau đó xếp kết quả của 2 phép tính này thành 2 cột nằm ngang cạnh nhau.

Sử dụng hàm GROUPBY để thực hiện nhiều phép tính

Sử dụng Filter_array để lọc dữ liệu theo điều kiện

Đối số Filter_array đóng vai trò như một bộ lọc thông minh giúp loại bỏ dữ liệu rác hoặc dữ liệu không đạt yêu cầu trước khi tính toán.

Giả sử bạn cần lập báo cáo tổng số lượng bán theo từng Ngành hàng, nhưng chỉ tính những sản phẩm đạt Tỷ lệ hài lòng lớn hơn 95%. Các sản phẩm có tỷ lệ hài lòng thấp hơn sẽ bị bỏ qua.

Tại ô cần xuất báo cáo, bạn nhập công thức sau:

Trong đó:

• A2:A16 (row_fields): Quét cột Ngành hàng để gom nhóm.
• C2:C16 (values): Quét cột Số lượng bán để tính tổng.
• SUM (function): Hàm tính tổng.
• 0 ([field_headers]): Không có tiêu đề (bỏ qua).
• 0 ([total_depth]): Không hiển thị dòng Tổng cộng (Grand Total).
• ; ([sort_order]): Bỏ trống (không yêu cầu sắp xếp).
• D2:D16>95% ([filter_array]): Đây là điều kiện lọc. Quét cột Tỷ lệ hài lòng (cột D) và đặt điều kiện phải lớn hơn 95% (>95%).

Sử dụng hàm GROUPBY có điều kiện lọc

4 Ưu điểm và nhược điểm khi sử dụng hàm GROUPBY

Ngoài ra, để vận hành tốt các hàm mảng động trên file dữ liệu lớn, thiết bị của bạn cần một bộ vi xử lý đủ mạnh. Việc trang bị một chiếc laptop có dung lượng RAM 8GB trở lên sẽ giúp các thao tác tính toán này diễn ra tức thì.

5 Không tìm thấy hàm GROUPBY trong Excel?

Dưới đây là một số lý do khiến bạn không tìm thấy hàm GROUPBY trong Excel:

Phiên bản Excel không hỗ trợ

Như đã đề cập trước đó, hàm GROUPBY là một hàm mới và chỉ khả dụng trên: Microsoft 365 và nền tảng Excel cho Web (Excel Online). Nếu bạn đang sử dụng các phiên bản Excel vĩnh viễn (Excel 2013, 2016, 2019, 2021), bạn sẽ không thể dùng hàm này.

Lỗi cú pháp hoặc đánh vần sai

Hãy kiểm tra lại xem bạn đã nhập đúng tên hàm GROUPBY chưa. Nếu nhập sai tên, Excel sẽ không nhận diện được hàm và báo lỗi #NAME?.

Vùng dữ liệu không hợp lệ

Đảm bảo rằng các vùng dữ liệu bạn chọn cho row_fields (cột nhóm) và values (cột tính toán) phải có cùng số lượng hàng. Nếu chúng không khớp nhau, hàm sẽ không hoạt động.

Excel chưa được cập nhật

Ngay cả khi bạn đang sử dụng Microsoft 365, có thể bạn vẫn chưa nhận được bản cập nhật chứa hàm GROUPBY. Bạn có thể thử cập nhật Office bằng cách:

Mở Excel > Vào File > Chọn Account (Tài khoản) > Chọn Update Options (Tùy chọn cập nhật) > Update Now (Cập nhật ngay).

Một số lý do khiến bạn không tìm thấy hàm GROUPBY trên Excel của mình

6 Cách khắc phục tạm thời khi không tìm thấy hàm GROUPBY trong Excel

Nếu bạn bắt buộc phải nhóm dữ liệu và tính tổng mà không có hàm GROUPBY, bạn có thể sử dụng các giải pháp thay thế truyền thống:

• Pivot Table: Đây là công cụ mạnh mẽ và phổ biến nhất để nhóm, tóm tắt và phân tích dữ liệu trong mọi phiên bản Excel.
• Hàm SUMIFS, COUNTIFS, AVERAGEIFS: Các hàm này cho phép bạn tính toán dựa trên nhiều điều kiện (tương đương với việc gom nhóm).
• Power Query: Công cụ này nâng cao hơn, giúp bạn biến đổi và gộp dữ liệu từ nhiều nguồn khác nhau (Vào Data > Chọn Get & Transform Data > Chọn Group By).

7 Các câu hỏi liên quan đến hàm GROUPBY

Hàm GROUPBY có thay thế hoàn toàn được Pivot Table không?

Dù rất mạnh mẽ, hàm GROUPBY không thay thế hoàn toàn Pivot Table trong các tác vụ cần giao diện kéo thả trực quan hoặc báo cáo có định dạng phức tạp. Tuy nhiên, đối với các báo cáo cần sự nhanh gọn và tự động hóa cao, GROUPBY là sự lựa chọn ưu việt hơn.

Tại sao hàm GROUPBY trả về lỗi #SPILL!?

Lỗi #SPILL! xảy ra khi vùng không gian xung quanh ô chứa công thức không đủ chỗ để hiển thị toàn bộ kết quả mảng trả về. Bạn hãy kiểm tra và xóa bỏ các nội dung hoặc định dạng nằm cản trở đường xuất dữ liệu của hàm để khắc phục sự cố này.

Việc làm chủ hàm GROUPBY trong Excel sẽ giúp bạn nâng tầm kỹ năng xử lý dữ liệu và chuyên nghiệp hóa các báo cáo hàng ngày. Hy vọng bài viết có thể giúp ích cho bạn. Cảm ơn bạn đã theo dõi và chúc bạn thực hiện thành công trên bảng tính của mình!