Trong kỷ nguyên của dữ liệu lớn, việc xử lý và phân tích thông tin một cách hiệu quả trở thành kỹ năng thiết yếu đối với mọi chuyên gia. Excel, với bộ công cụ mạnh mẽ của mình, đã và đang là phần mềm không thể thiếu. Tuy nhiên, đôi khi các hàm cơ bản như SUM hay AVERAGE lại bộc lộ những hạn chế khi bạn cần tính toán trên dữ liệu có lỗi, hàng ẩn hoặc phức tạp hơn. Đây chính là lúc hàm AGGREGATE tỏa sáng, mang đến khả năng kiểm soát vượt trội so với người anh em SUBTOTAL, giúp bạn thực hiện các phép tính linh hoạt và chính xác hơn trên bảng tính của mình.
Hàm AGGREGATE không chỉ giúp bạn thực hiện các phép tính cơ bản mà còn cung cấp tùy chọn bỏ qua các hàng bị ẩn, giá trị lỗi hoặc các hàm SUBTOTAL và AGGREGATE lồng ghép khác trong dữ liệu. Điều này đặc biệt hữu ích khi bạn làm việc với các bảng tính lớn, thường xuyên lọc hoặc ẩn dữ liệu mà vẫn muốn có được kết quả tính toán chính xác dựa trên tập dữ liệu hiển thị hoặc không chứa lỗi.
Hàm AGGREGATE là gì?
Hàm AGGREGATE là một hàm linh hoạt trong Excel, cho phép bạn thực hiện nhiều loại phép tính (như tổng, trung bình, đếm, giá trị lớn nhất/nhỏ nhất) trong một dải dữ liệu hoặc một mảng, đồng thời cung cấp khả năng tùy chỉnh để bỏ qua các hàng ẩn, giá trị lỗi, hoặc các hàm tổng hợp lồng ghép khác. Điều này làm cho AGGREGATE trở thành một công cụ mạnh mẽ hơn hàm SUBTOTAL, vốn có ít tùy chọn bỏ qua hơn và chủ yếu được dùng để tính toán trên các hàng hiển thị. Với AGGREGATE, bạn có quyền kiểm soát tối đa đối với dữ liệu đầu vào, đảm bảo kết quả tính toán luôn đáng tin cậy.
Cú pháp hàm AGGREGATE trong Excel
Hàm AGGREGATE có hai cú pháp chính: một dành cho tham chiếu (reference) và một dành cho mảng (array). Tuy nhiên, bạn không cần phải quá lo lắng về việc lựa chọn giữa chúng, vì Excel sẽ tự động chọn cú pháp phù hợp tùy thuộc vào các đối số bạn nhập.
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(hàm_tính_toán, tùy_chọn_bỏ_qua, phạm_vi_dữ_liệu, [phạm_vi_thêm_1], ...)Trong đó:
hàm_tính_toán(bắt buộc): Một số nguyên đại diện cho hàm bạn muốn sử dụng trong phép tính (ví dụ: 1 cho AVERAGE, 9 cho SUM).tùy_chọn_bỏ_qua(bắt buộc): Một số nguyên định nghĩa những gì bạn muốn phép tính bỏ qua (ví dụ: hàng ẩn, lỗi).phạm_vi_dữ_liệu(bắt buộc): Phạm vi các ô mà hàm sẽ được áp dụng.[phạm_vi_thêm_1], ...(tùy chọn): Tối đa 252 đối số bổ sung để chỉ định các phạm vi khác.
Cú pháp dạng Mảng (Array Form)
Nếu bạn đang làm việc với các mảng, cú pháp sẽ là:
=AGGREGATE(hàm_tính_toán, tùy_chọn_bỏ_qua, mảng_giá_trị, [đối_số_thứ_hai])Trong đó:
hàm_tính_toán(bắt buộc): Một số nguyên đại diện cho hàm bạn muốn sử dụng (thường là các hàm như LARGE, SMALL, PERCENTILE).tùy_chọn_bỏ_qua(bắt buộc): Một số nguyên định nghĩa những gì bạn muốn phép tính bỏ qua.mảng_giá_trị(bắt buộc): Mảng các giá trị mà hàm sẽ được áp dụng.[đối_số_thứ_hai](bắt buộc đối với một số hàm): Đối số thứ hai cần thiết cho các hàm mảng như LARGE, SMALL, PERCENTILE.INC, v.v. (ví dụ: giá trị k trong LARGE(mảng, k)).
Các đối số chính của hàm AGGREGATE: Hàm tính toán và Tùy chọn bỏ qua
Khi nhập đối số hàm_tính_toán và tùy_chọn_bỏ_qua trong cả hai dạng cú pháp trên, bạn sẽ có nhiều lựa chọn để tùy chỉnh hàm AGGREGATE của mình.
Bảng dưới đây hiển thị các hàm khác nhau bạn có thể sử dụng trong phép tính AGGREGATE (đối số hàm_tính_toán). 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, không phải tên hàm. Các hàm từ 1 đến 13 được sử dụng với cú pháp dạng tham chiếu, và các hàm từ 14 đến 19 được sử dụng với cú pháp dạng mảng.
| Số | Hàm | Chức năng tính toán |
|---|---|---|
| 1 | AVERAGE | Tính trung bình cộng của các giá trị. |
| 2 | COUNT | Đếm số lượng ô chứa giá trị số. |
| 3 | COUNTA | Đếm số lượng ô không rỗng. |
| 4 | MAX | Tìm giá trị lớn nhất. |
| 5 | MIN | Tìm giá trị nhỏ nhất. |
| 6 | PRODUCT | Tính tích của các số. |
| 7 | STDEV.S | Tính độ lệch chuẩn mẫu. |
| 8 | STDEV.P | Tính độ lệch chuẩn của tổng thể. |
| 9 | SUM | Tính tổng các số. |
| 10 | VAR.S | Tính phương sai mẫu. |
| 11 | VAR.P | Tính phương sai của tổng thể. |
| 12 | MEDIAN | Tìm giá trị trung vị (ở giữa). |
| 13 | MODE.SNGL | Tìm giá trị xuất hiện nhiều nhất (mode) trong một tập dữ liệu. |
| 14 | LARGE | Tìm giá trị lớn thứ n. |
| 15 | SMALL | Tìm giá trị nhỏ thứ n. |
| 16 | PERCENTILE.INC | Tìm phân vị thứ n, bao gồm giá trị đầu và cuối. |
| 17 | QUARTILE.INC | Tìm tứ phân vị thứ n, bao gồm giá trị đầu và cuối. |
| 18 | PERCENTILE.EXC | Tìm phân vị thứ n, loại trừ giá trị đầu và cuối. |
| 19 | QUARTILE.EXC | Tìm 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 của mình (đối số tùy_chọn_bỏ_qua):
| Số | Nội dung sẽ bị bỏ qua |
|---|---|
| 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ì cả (tính toán tất cả). |
| 5 | Chỉ bỏ qua các hàng ẩn. |
| 6 | Chỉ bỏ qua các 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ế.
Ứng dụng thực tế của hàm AGGREGATE với các ví dụ cụ thể
Hãy cùng khám phá cách áp dụng hàm AGGREGATE thông qua ba ví dụ cụ thể, giúp bạn hình dung rõ hơn về sức mạnh và sự linh hoạt của hàm này.
Ví dụ 1: Sử dụng AGGREGATE để bỏ qua lỗi
Giả sử bạn có một bảng Excel chứa danh sách các cầu thủ bóng đá, số trận họ đã chơi, số bàn thắng ghi được và tỷ lệ bàn thắng trên mỗi trận đấu. Mục tiêu của bạn là tính toán tỷ lệ bàn thắng trên mỗi trận đấu trung bình cho tất cả các cầu thủ.
Bảng dữ liệu Excel về hiệu suất ghi bàn của cầu thủ, bao gồm số trận, số bàn thắng và tỷ lệ bàn thắng trên mỗi trận.
Nếu bạn chỉ sử dụng hàm AVERAGE thông thường bằng cách nhập công thức:
=AVERAGE(Player_Goals[Games per goal])vào ô C1, kết quả sẽ trả về một lỗi, chẳng hạn như #DIV/0!, bởi vì phạm vi được tham chiếu chứa các giá trị lỗi. Điều này xảy ra khi có cầu thủ chưa ghi bàn (Goals scored = 0) và tỷ lệ được tính bằng cách chia cho 0.
Công thức AVERAGE trong Excel trả về lỗi #DIV/0! do phạm vi dữ liệu tham chiếu chứa giá trị lỗi.
Thay vào đó, việc sử dụng hàm AGGREGATE sẽ 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 dữ liệu còn lại. Để thực hiện đ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 công thức này:
1(đối sốhàm_tính_toán) đại diện cho hàm AVERAGE.6(đối sốtùy_chọn_bỏ_qua) yêu cầu Excel bỏ qua các lỗi.Player_Goals[Games per goal]là phạm vi tham chiếu chứa tỷ lệ bàn thắng trên mỗi trận.
Hàm AGGREGATE tính toán trung bình tỷ lệ bàn thắng trên mỗi trận đấu, đã bỏ qua các ô chứa lỗi trong Excel.
Một cách khác để đạ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 hoặc 0 trước khi tính toán trung bình. Tuy nhiên, AGGREGATE cung cấp một giải pháp gọn gàng và trực tiếp hơn trong trường hợp này.
Ví dụ 2: Sử dụng AGGREGATE để bỏ qua hàng ẩn (Dạng Tham chiếu)
Với cùng bảng tính trên, 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, đặ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ố ở phía trên bảng tính, bên ngoài bảng đã đị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ố bàn thắng chung khi kết hợp tất cả các cầu thủ, và tổng số bàn thắng chỉ của những cầu thủ đang hiển thị trong bảng sau khi bạn áp dụng bộ lọc.
Bảng dữ liệu Excel hiển thị số bàn thắng và số trận đấu của mười cầu thủ, cùng với tỷ lệ ghi bàn của họ.
Để tính tổng số bàn thắng chung, trong ô C1, hãy nhập công thức:
=SUM(Player_Goals[Goals scored])
Công thức SUM được áp dụng trong bảng Excel, hiển thị tổng số bàn thắng của các cầu thủ 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 sẽ bao gồm các hàng đã bị lọc.
Kết quả của công thức SUM không thay đổi sau khi dữ liệu trong bảng Excel được lọc, cho thấy hàm SUM không bỏ qua hàng ẩn.
Đâ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 đã bị lọc. Trên thực tế, hàm AGGREGATE cũng 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, nhập công thức:
=AGGREGATE(9,5,Player_Goals[Goals scored])Trong công thức này:
9(đối sốhàm_tính_toán) đại diện cho hàm SUM.5(đối sốtùy_chọn_bỏ_qua) yêu cầu Excel bỏ qua các hàng ẩn.Player_Goals[Goals scored]là phạm vi tham chiếu chứa số bàn thắng.
Bây giờ, bạn sẽ nhận thấ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ỉ tính đến các hàng đang hiển thị.
Hàm AGGREGATE tính tổng số bàn thắng, chỉ tính các hàng đang hiển thị sau khi áp dụng bộ lọc dữ liệu trong Excel.
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 các cầu thủ đã chơi từ 20 trận trở xuống.
Bảng dữ liệu Excel với danh sách mười cầu thủ, số trận đấu, số bàn thắng và tỷ lệ ghi bàn của từng người.
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, hãy tạo công thức trước.
Trong ô C1, nhập công thức:
=AGGREGATE(14,5,Player_Goals[Goals scored],{1;2})Trong công thức này:
14(đối sốhàm_tính_toán) đại diện cho hàm LARGE, giúp tìm giá trị lớn thứ n.5(đối sốtùy_chọn_bỏ_qua) yêu cầu Excel bỏ qua các hàng ẩn.Player_Goals[Goals scored]là mảng các giá trị số bàn thắng.{1;2}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ị ngắt dòng trong mảng hằng số).
Khi bạn nhấn Enter, bạn sẽ nhận thấy 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ả dưới dạng mảng, hiển thị hai giá trị lớn nhất theo yêu cầu.
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 hoặc ít hơn, và bạn sẽ thấy kết quả của công thức AGGREGATE bạn đã nhập trước đó thay đổi để bỏ qua các hàng ẩn.
Hàm AGGREGATE tính toán hai số bàn thắng cao nhất trong bảng đã lọc, bỏ qua các hàng bị ẩn trong Excel.
Lưu ý quan trọng khi sử dụng hàm AGGREGATE
Trước khi bạn áp dụng hàm AGGREGATE vào các tệp Excel của riêng mình, hãy dành một chút thời gian để ghi nhớ các điểm sau:
- Phạm vi áp dụng: 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 ô trong các cột bị ẩn.
- Giới hạn tham chiếu 3D: Đối số
phạm_vi_dữ_liệutrong công thức AGGREGATE không thể là cùng một ô hoặc phạm vi ô trên nhiều trang tính (còn được gọi là tham chiếu 3D). - Không nên bỏ qua lỗi hoàn 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, nhưng đừng quá lạm dụng thói quen bỏ qua lỗi hoàn toàn. Các lỗi tồn tại vì một lý do và chúng có thể giúp bạn khắc phục sự cố với dữ liệu của mình. Hãy luôn kiểm tra nguyên nhân gốc rễ của lỗi khi có thể.
- Giới hạn của dạng mảng khi có phép tính: Dạng mảng của hàm AGGREGATE sẽ không bỏ qua các hàng ẩn, các hàm SUBTOTAL lồng ghép hoặc các hàm AGGREGATE lồng ghép nếu đối số mảng bao gồm một phép tính. Điều này có nghĩa là nếu bạn tạo một mảng bằng cách thực hiện một phép tính trước khi truyền nó vào AGGREGATE (ví dụ:
A1:A10*2), tùy chọn bỏ qua hàng ẩn sẽ không hoạt động như mong đợi.
Ngoài việc sử dụng AGGREGATE để quản lý các hàng ẩn, một cách khác để ẩn hàng trong bảng Excel sao cho hàm AGGREGATE chỉ tính toán những gì đang hiển thị là bằng cách chèn các slicer – các nút tương tác mà bạn có thể nhấp để làm cho việc lọc trở nên đơn giản hơn nhiều.
Hàm AGGREGATE thực sự là một công cụ mạnh mẽ, mang lại sự linh hoạt và kiểm soát đáng kể khi làm việc với dữ liệu trong Excel. Bằng cách hiểu rõ cú pháp và các tùy chọn của nó, bạn có thể thực hiện các phép tính phức tạp một cách hiệu quả, bỏ qua các yếu tố không mong muốn và đảm bảo tính chính xác cho các phân tích của mình. Nắm vững AGGREGATE sẽ giúp bạn tối ưu hóa quy trình làm việc và đưa ra các quyết định dựa trên dữ liệu đáng tin cậy hơn.