Trong thế giới của bảng tính Excel, việc xử lý và phân tích dữ liệu thường đi kèm với những thách thức như các ô chứa lỗi, hàng bị ẩn hoặc dữ liệu đã được lọc. Những yếu tố này có thể làm sai lệch kết quả của các hàm tính toán thông thường, khiến bạn khó có được cái nhìn chính xác nhất về tập dữ liệu của mình. May mắn thay, Excel cung cấp một công cụ mạnh mẽ: hàm AGGREGATE. Giống như hàm SUBTOTAL quen thuộc, AGGREGATE cho phép bạn thực hiện các phép tính trong khi bỏ qua các hàng ẩn, giá trị lỗi, hoặc các hàm lồng ghép khác xuất hiện trong dữ liệu. Tuy nhiên, AGGREGATE vượt trội hơn nhờ cung cấp nhiều tùy chọn tính toán và khả năng kiểm soát chi tiết hơn về những gì bạn muốn loại trừ khỏi phép tính, biến nó thành một trợ thủ đắc lực cho mọi nhà phân tích dữ liệu.
Cú Pháp Hàm AGGREGATE trong Excel: Hai Dạng Chính
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 thông qua cú pháp của nó. Hàm AGGREGATE có hai dạng cú pháp – một cho tham chiếu (reference) và một cho mảng (array) – mặc dù bạn không cần phải quá lo lắng về việc lựa chọn, vì Excel sẽ tự động chọn dạng phù hợp dựa trên các đối số bạn nhập. Chúng ta sẽ thấy cả hai cú pháp này được sử dụng trong các ví dụ sắp tới.
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ố biểu thị 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, 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 tối đa 252 đối số khác, dùng để chỉ định các phạm vi bổ sung.
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 là:
=AGGREGATE(a,b,c,d)Trong đó:
- a (bắt buộc) là một số biểu thị 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 là đối số thứ hai bắt buộc bởi các hàm mảng như LARGE, SMALL, PERCENTILE.INC, và các hàm khác.
Các Đối Số Quan Trọng: Hàm Tính Toán và Quy Tắc Bỏ Qua Dữ Liệu
Khi nhập các đối số a và b trong cả hai dạng cú pháp trên, bạn sẽ có nhiều tùy chọn để lựa chọn.
Đối Số a: Lựa Chọn Hàm Tính Toán
Bảng dưới đây thể hiện các hàm khác nhau 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 với cú pháp dạng tham chiếu, và các hàm từ 14 đến 19 được dùng với cú pháp dạng mảng.
| Số | Hàm | Chức năng Tính Toán |
|---|---|---|
| 1 | AVERAGE | Trung bình cộng |
| 2 | COUNT | Số lượng ô chứa giá trị số |
| 3 | COUNTA | Số lượng ô 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 các giá trị |
| 7 | STDEV.S | Độ lệch chuẩn mẫu |
| 8 | STDEV.P | Độ lệch chuẩn dựa trên tổng thể |
| 9 | SUM | Phép cộng |
| 10 | VAR.S | Phương sai mẫu |
| 11 | VAR.P | Phương sai dựa trên 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 | Phần tư thứ n, bao gồm giá trị đầu và cuối |
| 18 | PERCENTILE.EXC | Phần trăm thứ n, không bao gồm giá trị đầu và cuối |
| 19 | QUARTILE.EXC | Phần tư thứ n, không bao gồm giá trị đầu và cuối |
Đối Số b: Quy Tắc Bỏ Qua Dữ Liệu
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ố b):
| Số | Những 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 |
Bây giờ, hãy cùng xem xét một số ví dụ về cách bạn có thể sử dụng hàm AGGREGATE trong các tình huống thực tế.
Ví Dụ Thực Tế Cách Sử Dụng Hàm AGGREGATE Hiệu Quả
Ví Dụ 1: Bỏ Qua Lỗi Trong Tính Toán Trung Bình
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 đấu họ đã chơi, số bàn thắng họ ghi được và tỷ lệ bàn thắng trên mỗi trận của họ. 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 trung bình cho tất cả các cầu thủ.
Bảng Excel thể hiện dữ liệu cầu thủ bóng đá với số trận, số bàn và tỷ lệ trận trên bàn thắng, bao gồm một số lỗi #DIV/0!.
Nếu bạn chỉ sử dụng hàm AVERAGE bằng cách nhập:
=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/0! do phạm vi tham chiếu chứa các giá trị lỗi.
Thay vào đó, việc sử dụng hàm AGGREGATE cho phép bạn bỏ qua các 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:
=AGGREGATE(1,6,Player_Goals[Games per goal])Trong đó:
- 1 (đối số a) đại diện cho hàm AVERAGE (tính trung bình).
- 6 (đối số b) yêu cầu Excel bỏ qua các lỗi.
- Player_Goals[Games per goal] là phạm vi tham chiếu.
Công thức AGGREGATE được sử dụng để tính giá trị trung bình của một phạm vi, bỏ qua các ô chứa lỗi.
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.
Ví Dụ 2: Tính Tổng Chỉ Với Hàng Hiển Thị (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à độ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ể tốn thời gian. Thay vào đó, hãy xem xét đặt tổng ở 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ố. Đầu tiên là tổng chung khi bạn cộng các bàn thắng của tất cả các cầu thủ, nhưng thứ hai là tổng của chỉ các cầu thủ hiển thị trong bảng sau khi bạn áp dụng bộ lọc.
Bảng Excel hiển thị số bàn thắng của mười cầu thủ, số trận 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 từ 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.
Đây 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 đã ẩ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 (tính tổng).
- 5 (đối số b) yêu cầu Excel bỏ qua các hàng ẩn (bao gồm cả hàng bị lọc).
- Player_Goals[Goals scored] 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 đã được lọc ra.
Ví Dụ 3: Tìm Giá Trị Lớn Nhất Với Hàng Bị Ẩ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 20 trận trở xuống.
Bảng Excel hiển thị mười cầu thủ, số trận họ đã chơi, số bàn thắng họ ghi được và tỷ lệ trận trên 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 này, 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 (tìm giá trị lớn nhất).
- 5 (đối số b) yêu cầu Excel bỏ qua các hàng ẩn.
- Player_Goals[Goals scored] là mảng giá trị.
- {1;2} yêu cầu Excel trả về giá trị lớn nhất (1) và lớn thứ hai (2) trên các hàng riêng biệt (dấu chấm phẩy ;).
Khi bạn nhấn Enter, hãy để ý 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 tràn, hiển thị hai giá trị lớn nhất.
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 bạn đã nhập trước đó thay đổi để bỏ qua các hàng ẩn.
Những Lưu Ý Quan Trọng Khi Dùng Hàm AGGREGATE
Trước khi bạn 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:
- 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. Do đó, 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 ẩn.
- Đối số c 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 (còn gọi là tham chiếu 3D).
- 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 hình thành thói quen bỏ qua lỗi hoàn toàn. Chúng tồn tại 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.
- Dạng mảng của hàm AGGREGATE sẽ không bỏ qua các hàng ẩn, các tổng phụ 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.
Một cách khác để ẩn các hàng trong bảng Excel để hàm AGGREGATE chỉ bao gồm những gì đang 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.
AGGREGATE là một công cụ đắc lực để vượt qua những giới hạn của các hàm Excel truyền thống, giúp bạn thực hiện các phép tính một cách chính xác và linh hoạt hơn trên dữ liệu phức tạp. Với khả năng bỏ qua lỗi, hàng ẩn hay dữ liệu đã lọc, hàm này không chỉ giúp bạn có được kết quả đáng tin cậy mà còn tối ưu hóa quy trình làm việc. Hãy áp dụng ngay hàm AGGREGATE vào công việc của mình để nâng cao hiệu quả phân tích dữ liệu và tận dụng tối đa sức mạnh của Excel. Đừng ngần ngại chia sẻ những trải nghiệm hoặc thắc mắc của bạn về hàm AGGREGATE dưới phần bình luận, và hãy theo dõi thichcongnghe.net để khám phá thêm nhiều mẹo và hướng dẫn công nghệ hữu ích khác!
