Trong thế giới phân tích dữ liệu và quản lý bảng tính, Microsoft Excel là công cụ không thể thiếu. Và một trong những “viên ngọc ẩn” giúp người dùng nâng cao đáng kể hiệu quả tính toán chính là hàm AGGREGATE. Tương tự như hàm SUBTOTAL, nhưng AGGREGATE mang đến nhiều lựa chọn tính toán hơn và cung cấp khả năng kiểm soát vượt trội đối với những giá trị bạn muốn loại trừ khỏi phép tính, chẳng hạn như hàng bị ẩn, lỗi, hoặc các hàm lồng ghép khác.
Hàm AGGREGATE là một công cụ mạnh mẽ, cho phép bạn thực hiện các phép tính phức tạp một cách linh hoạt, loại bỏ các yếu tố gây nhiễu và đảm bảo kết quả chính xác, đáng tin cậy. Dù bạn là người dùng Excel cơ bản hay chuyên gia phân tích dữ liệu, việc nắm vững hàm này sẽ mở ra nhiều khả năng mới trong việc xử lý và trình bày dữ liệu của mình.
Hiểu Rõ Cú Pháp Hàm AGGREGATE
Trước khi đi sâu vào các ví dụ thực tế, hãy cùng tìm hiểu cách hoạt động của hàm AGGREGATE. Hàm này có hai dạng cú pháp – một cho tham chiếu (references) và một cho mảng (arrays). Tuy nhiên, bạn không cần phải quá lo lắng về việc lựa chọn dạng nào, vì Excel sẽ tự động chọn dạng phù hợp dựa trên các đối số bạn nhập vào. Chúng ta sẽ cùng xem xét cả hai cú pháp này qua các ví dụ minh họa chi tiết.
Cú Pháp Dạng Tham Chiếu (Reference Form)
Cú pháp cho dạng tham chiếu của hàm AGGREGATE là:
=AGGREGATE(a,b,c,d)Trong đó:
a(bắt buộc): Là một số đại diện cho hàm bạn muốn sử dụng trong phép tính (ví dụ: SUM, AVERAGE, COUNT).b(bắt buộc): Là một số xác định những gì bạn muốn phép tính bỏ qua (ví dụ: hàng ẩn, giá trị lỗi).c(bắt buộc): Là phạm vi ô mà hàm sẽ được áp dụng.d(tùy chọn): Là đối số bổ sung đầu tiên trong số tối đa 252 đối số khác để chỉ định thêm các phạm vi.
Cú Pháp Dạng Mảng (Array Form)
Mặt khác, nếu bạn làm việc với mảng, cú pháp sẽ là:
=AGGREGATE(a,b,c,d)Trong đó:
a(bắt buộc): Là một số đại diện cho hàm bạn muốn sử dụng trong phép tính.b(bắt buộc): Là một số xác định những gì bạn muốn phép tính bỏ qua.c(bắt buộc): Là mảng các giá trị mà hàm sẽ được áp dụng.d(bắt buộc): Là đối số thứ hai cần thiết cho các hàm mảng như LARGE, SMALL, PERCENTILE.INC và một số hàm khác.
Các Chức Năng và Tùy Chọn Loại Trừ (Đối Số a và b)
Khi nhập các đối số a và b vào một trong hai dạng cú pháp trên, bạn sẽ có nhiều tùy chọn để lựa chọn.
Bảng dưới đây hiển thị các hàm khác nhau mà bạn có thể sử dụng trong phép tính AGGREGATE (đối số a). Mặc dù bạn có thể muốn gõ tên hàm, hãy nhớ rằng đối số này phải là một số đại diện cho hàm bạn muốn sử dụng. Các hàm từ 1 đến 13 được dùng cho cú pháp dạng tham chiếu, và các hàm từ 14 đến 19 được dùng cho cú pháp dạng mảng.
| Số | Hàm | Tính toán gì? |
|---|---|---|
| 1 | AVERAGE | Giá trị trung bình số học |
| 2 | COUNT | Số ô chứa giá trị số |
| 3 | COUNTA | Số ô không trống |
| 4 | MAX | Giá trị lớn nhất |
| 5 | MIN | Giá trị nhỏ nhất |
| 6 | PRODUCT | Phép nhân |
| 7 | STDEV.S | Độ lệch chuẩn mẫu |
| 8 | STDEV.P | Độ lệch chuẩn tổng thể |
| 9 | SUM | Phép cộng |
| 10 | VAR.S | Phương sai mẫu |
| 11 | VAR.P | Phương sai tổng thể |
| 12 | MEDIAN | Giá trị trung vị |
| 13 | MODE.SNGL | Số xuất hiện thường xuyên nhất |
| 14 | LARGE | Giá trị lớn thứ n |
| 15 | SMALL | Giá trị nhỏ thứ n |
| 16 | PERCENTILE.INC | Phần trăm thứ n, bao gồm giá trị đầu và cuối |
| 17 | QUARTILE.INC | Tứ phân vị thứ n, bao gồm giá trị đầu và cuối |
| 18 | PERCENTILE.EXC | Phần trăm thứ n, loại trừ giá trị đầu và cuối |
| 19 | QUARTILE.EXC | Tứ phân vị thứ n, loại trừ giá trị đầu và cuối |
Bảng này liệt kê các số bạn có thể nhập để loại trừ các giá trị nhất định khi tạo công thức AGGREGATE (đối số b):
| Số | Bỏ qua những gì? |
|---|---|
| 0 | Các hàm SUBTOTAL và AGGREGATE lồng ghép |
| 1 | Hàng ẩn, và các hàm SUBTOTAL và AGGREGATE lồng ghép |
| 2 | Lỗi, và các hàm SUBTOTAL và AGGREGATE lồng ghép |
| 3 | Hàng ẩn, giá trị lỗi, và các hàm SUBTOTAL và AGGREGATE lồng ghép |
| 4 | Không bỏ qua gì |
| 5 | Chỉ hàng ẩn |
| 6 | Chỉ lỗi |
| 7 | Hàng ẩn và lỗi |
Bây giờ, hãy cùng xem xét một số ví dụ thực tế về cách bạn có thể sử dụng hàm AGGREGATE trong các tình huống thực tế trên Excel.
Ví Dụ 1: Sử Dụng AGGREGATE để Bỏ Qua Lỗi
Hãy tưởng tượng bạn có một bảng tính Excel chứa danh sách các cầu thủ bóng đá, số trận đấu họ đã chơi, số bàn thắng họ ghi được và tỷ lệ trận đấu/bàn thắng của họ. Mục tiêu của bạn là tính toán tỷ lệ trận đấu/bàn thắng trung bình cho tất cả các cầu thủ.
Bảng Excel hiển thị số bàn thắng của mười cầu thủ, số trận đấu họ đã chơi và tỷ lệ tương ứng.
Nếu bạn chỉ sử dụng hàm AVERAGE đơn thuần bằng cách nhập công thức:
=AVERAGE(Player_Goals[Games per goal])vào ô C1, công thức này sẽ trả về lỗi, vì phạm vi tham chiếu chứa các lỗi #DIV/0!.
Công thức AVERAGE trong Excel trả về lỗi DIV vì phạm vi tham chiếu chứa lỗi.
Thay vào đó, việc sử dụng hàm AGGREGATE cung cấp cho bạn tùy chọn để bỏ qua những lỗi này và trả về giá trị trung bình cho các dữ liệu còn lại. Để làm điều này, trong ô C2, bạn cần nhập công thức:
=AGGREGATE(1,6,Player_Goals[Games per goal])Trong đó:
1(đối sốa): Đại diện cho hàm AVERAGE.6(đối sốb): Yêu cầu Excel bỏ qua các giá trị lỗi.Player_Goals[Games per goal](đối sốc): Là phạm vi tham chiếu cần tính toán.
Công thức AGGREGATE được sử dụng để tính trung bình của một phạm vi, bỏ qua các lỗi.
Một cách thay thế để đạt được kết quả tương tự là sử dụng hàm IFERROR trong cột D để thay thế bất kỳ lỗi nào bằng một giá trị trống. Tuy nhiên, AGGREGATE cung cấp giải pháp tích hợp và hiệu quả hơn cho việc bỏ qua lỗi trực tiếp trong phép tính.
Ví Dụ 2: Sử Dụng AGGREGATE để Bỏ Qua Hàng Ẩn (Dạng Tham Chiếu)
Sử dụng cùng bảng tính, mục tiêu tiếp theo của bạn là tính tổng số bàn thắng mà cả đội đã ghi được.
Một cách để hiển thị tổng số là chọn “Total Row” trong tab “Table Design” trên ribbon, điều này sẽ đặt tổng số ở cuối bảng. Tuy nhiên, nếu bạn làm việc với một tập dữ liệu lớn, việc liên tục cuộn xuống để xem tổng số có thể tốn thời gian. Thay vào đó, hãy cân nhắc đặt tổng số ở đầu bảng tính, bên ngoài bảng được định dạng, để chúng luôn hiển thị.
Cụ thể, bạn muốn hiển thị hai tổng số. Tổng số thứ nhất là tổng chung khi bạn kết hợp số bàn thắng của tất cả các cầu thủ, nhưng tổng số thứ hai là tổng của chỉ những cầu thủ hiển thị trong bảng sau khi bạn áp dụng các bộ lọc.
Bảng Excel hiển thị số bàn thắng của mười cầu thủ, số trận đấu họ đã chơi và tỷ lệ ghi bàn.
Để tính tổng chung, trong ô C1, hãy nhập:
=SUM(Player_Goals[Goals scored])
Công thức SUM được áp dụng cho bảng Excel định dạng để tính tổng số bàn thắng trong cột C.
Bây giờ, ngay cả sau khi bạn áp dụng bộ lọc cho một trong các cột, chẳng hạn như chỉ hiển thị những cầu thủ đã chơi 15 trận trở lên, công thức SUM bạn vừa áp dụng vẫn bao gồm các hàng bị lọc.
Đây là lúc hàm AGGREGATE sẽ phát huy tác dụng, vì nó cho phép phép tính của bạn bỏ qua các hàng đã được lọc. Trên thực tế, hàm AGGREGATE cũng sẽ hoạt động nếu bạn muốn bỏ qua các hàng đã bị ẩn bằng cách nhấp chuột phải vào tiêu đề hàng và chọn “Hide”.
Trong ô C2, hãy nhập:
=AGGREGATE(9,5,Player_Goals[Goals scored])Trong đó:
9(đối sốa): Đại diện cho hàm SUM.5(đối sốb): Yêu cầu Excel bỏ qua các hàng bị ẩn.Player_Goals[Goals scored](đối sốc): Là phạm vi tham chiếu.
Bây giờ, hãy để ý rằng kết quả của công thức này khác với kết quả của công thức SUM bạn đã sử dụng trong ô C1, vì nó chỉ xem xét các hàng đang hiển thị.
Công thức AGGREGATE được sử dụng để tính tổng một cột trong bảng, bỏ qua các hàng đã bị lọc.
Ví Dụ 3: Sử Dụng AGGREGATE để Bỏ Qua Hàng Ẩn (Dạng Mảng)
Tiếp theo, giả sử bạn muốn liệt kê hai số bàn thắng cao nhất của những cầu thủ đã chơi từ 20 trận trở xuống.
Bảng Excel hiển thị mười cầu thủ, số trận đấu họ đã chơi, số bàn thắng họ ghi được và tỷ lệ trận đấu/bàn thắng.
Bạn có thể áp dụng bộ lọc trước rồi mới tạo công thức, nhưng vì mục đích minh họa, chúng ta hãy tạo công thức trước.
Trong ô C1, hãy nhập:
=AGGREGATE(14,5,Player_Goals[Goals scored],{1;2})Trong đó:
14(đối sốa): Đại diện cho hàm LARGE.5(đối sốb): Yêu cầu Excel bỏ qua các hàng bị ẩn.Player_Goals[Goals scored](đối sốc): Là mảng các giá trị.{1;2}(đối sốd): Yêu cầu Excel trả về giá trị lớn nhất (1) và giá trị lớn thứ hai (2) trên các hàng riêng biệt (dấu;biểu thị xuống dòng trong mảng dọc).
Khi bạn nhấn Enter, hãy lưu ý rằng kết quả là một mảng tràn (spilled array) bao phủ các ô C1 và C2 vì bạn đã yêu cầu Excel trả về hai giá trị cao nhất.
Hàm AGGREGATE trong Excel trả về kết quả mảng.
Bây giờ, hãy lọc cột “Games Played” để chỉ bao gồm những cầu thủ đã chơi 20 trận trở xuống, và bạn sẽ thấy kết quả của công thức AGGREGATE mà bạn đã nhập trước đó thay đổi để bỏ qua các hàng bị ẩn.
Những Lưu Ý Quan Trọng Khi Sử Dụng Hàm AGGREGATE
Trước khi bạn bắt đầu sử dụng hàm AGGREGATE trong các bảng tính Excel của riêng mình, hãy dành chút thời gian để ghi nhớ những điểm sau:
- Phạm vi dọc: Hàm AGGREGATE của Excel chỉ hoạt động với các phạm vi dọc, không phải phạm vi ngang. Vì vậy, khi bạn tham chiếu một phạm vi ngang, AGGREGATE sẽ không bỏ qua các hàng trong các cột bị ẩn.
- Tham chiếu 3D: Đối số
ctrong công thức AGGREGATE không thể là cùng một ô hoặc phạm vi ô trên nhiều bảng tính (còn được gọi là tham chiếu 3D). - Lỗi trong tính toán: Mặc dù hàm AGGREGATE là một cách tuyệt vời để bỏ qua lỗi trong các phép tính, đừng biến việc bỏ qua lỗi hoàn toàn thành thói quen. Các lỗi xuất hiện có lý do và có thể giúp bạn khắc phục sự cố với dữ liệu của mình.
- Tính toán trong đối số mảng: Dạng mảng của hàm AGGREGATE sẽ không bỏ qua các hàng bị ẩn, tổng phụ lồng ghép hoặc các AGGREGATE lồng ghép nếu đối số mảng bao gồm một phép tính.
Ngoài việc lọc trực tiếp, một cách khác để ẩn hàng trong bảng Excel để hàm AGGREGATE chỉ bao gồm những gì hiển thị là bằng cách chèn slicer, các nút tương tác mà bạn có thể nhấp để việc lọc trở nên đơn giản hơn nhiều.
Kết Luận
Hàm AGGREGATE trong Excel là một công cụ cực kỳ linh hoạt và mạnh mẽ, giúp bạn vượt qua những hạn chế của các hàm tính toán cơ bản như SUM hay AVERAGE, đặc biệt khi làm việc với dữ liệu có lỗi hoặc hàng bị ẩn. Khả năng tùy chỉnh cao của nó cho phép người dùng kiểm soát chính xác những gì được đưa vào hoặc loại trừ khỏi phép tính, từ đó đưa ra kết quả chính xác và đáng tin cậy hơn.
Việc nắm vững cách sử dụng các đối số a (hàm tính toán) và b (tùy chọn bỏ qua) sẽ giúp bạn giải quyết nhiều thách thức trong phân tích dữ liệu hàng ngày. Hãy áp dụng hàm AGGREGATE vào các bảng tính của mình để trải nghiệm sự tiện lợi và hiệu quả mà nó mang lại trong việc xử lý và tổng hợp dữ liệu phức tạp.
Bạn có thắc mắc nào về hàm AGGREGATE hay muốn chia sẻ kinh nghiệm sử dụng của mình không? Hãy để lại bình luận phía dưới để chúng ta cùng thảo luận nhé!
