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