Hàm AGGREGATE trong Google Sheets | Hướng dẫn sử dụng từ A-Z

Bạn đang tìm kiếm cách tối ưu hóa bảng tính nhưng gặp khó khăn với các hàng bị ẩn hoặc dữ liệu chứa lỗi. Hàm AGGREGATE trong Google Sheets là chìa khóa để giúp bạn xử lý mọi vùng dữ liệu phức tạp này. Để hiểu rõ hơn về cách sử dụng hàm AGGREGATE từ cơ bản đến nâng cao, đừng bỏ lỡ bài viết dưới đây nhé. 

Hàm AGGREGATE trong Google Sheets là gì?

Hàm AGGREGATE trong Google Sheets là một hàm dùng để tổng hợp và phân tích dữ liệu một cách linh hoạt trong bảng tính. Hàm này cho phép bạn thực hiện các phép tính quen thuộc như tính tổng, trung bình, giá trị lớn nhất, nhỏ nhất, đếm dữ liệu nhưng ở mức độ nâng cao hơn so với các hàm thông thường. 

Điểm nổi bật của hàm AGGREGATE trong Google Sheets là khả năng bỏ qua các giá trị không mong muốn trong quá trình tính toán. Chẳng hạn như ô lỗi, hàng ẩn hoặc dữ liệu được lọc. Nhờ đó, kết quả trả về luôn chính xác và phản ánh đúng dữ liệu bạn đang phân tích, kể cả khi bảng tính phức tạp hoặc thường xuyên thay đổi. 

Khi sử dụng hàm AGGREGATE, người dùng không cần kết hợp nhiều hàm riêng lẻ mà vẫn có thể xử lý dữ liệu một cách gọn gàng và hiệu quả. Đây là lựa chọn rất phù hợp cho người mới bắt đầu, dân văn phòng, kế toán hoặc bất kỳ ai thường xuyên làm việc với báo cáo và dữ liệu lớn trong Google Sheets. 

Sử dụng hàm AGGREGATE trong Google Sheets có lợi ích gì?

Việc sử dụng hàm AGGREGATE trong Google Sheets mang lại nhiều lợi ích thiết thực. Đặc biệt khi bạn làm việc với bảng tính có dữ liệu lớn, phức tạp hoặc thường xuyên thay đổi. Dưới đây là một số lợi ích mà hàm AGGREGATE mang lại cho người dùng:

• Thứ nhất là tính toán chính xác ngay cả khi dữ liệu có lỗi

Hàm AGGREGATE trong Google Sheets cho phép bỏ qua các ô bị lỗi như #DIV/0!, #N/A, #VALUE!… Nhờ đó, kết quả tính toán không bị gián đoạn hay sai lệch, giúp bạn yên tâm hơn khi phân tích dữ liệu thực tế.

• Thứ hai là bỏ qua hàng ẩn và dữ liệu đã lọc

Khi sử dụng hàm AGGREGATE, bạn có thể loại trừ các hàng đang bị ẩn hoặc dữ liệu không hiển thị do bộ lọc. Đối với các bản báo cáo, điều này cực kỳ quan trọng vì kết quả chỉ dựa trên dữ liệu đang được xem.

• Thứ ba là thay thế nhiều hàm tính toán riêng lẻ

Thay vì phải dùng nhiều hàm như SUM, AVERAGE, MAX, MIN… trong từng trường hợp khác nhau, hàm AGGREGATE cho phép gộp các phép tính này vào một công thức duy nhất. Từ đó, nó sẽ giúp bảng tính gọn gàng và dễ quản lý hơn.

• Thứ tư là phù hợp với bảng tính phức tạp và dữ liệu lớn

Với khả năng xử lý linh hoạt nhiều tình huống dữ liệu, hàm AGGREGATE hoạt động rất hiệu quả trong các bảng tính lớn. Chủ yếu nhất là bảng tính có nhiều cột, nhiều điều kiện hoặc thường xuyên cập nhật dữ liệu.

• Thứ năm là dễ tiếp cận với người mới bắt đầu

Cách sử dụng hàm AGGREGATE khá trực quan và logic. Người mới tiếp cận Google trang tính vẫn có thể làm quen nhanh và áp dụng hiệu quả trong công việc hàng ngày. 

Tóm lại, sử dụng hàm AGGREGATE giúp bạn tính toán linh hoạt hơn, chính xác và chuyên nghiệp hơn. Đặc biệt với các báo cáo, bảng dữ liệu phức tạp, hàm này thực sự rất hữu ích. 

Cú pháp hàm AGGREGATE trong Google Sheets chuẩn

Khi nắm rõ cấu trúc của hàm AGGREGATE, bạn sẽ hiểu được logic tổng hợp dữ liệu. Từ đó, bạn sẽ biết cách áp dụng linh hoạt hàm trong nhiều tình huống khác nhau. 

Cú pháp chuẩn

Trong Excel, hàm AGGREGATE có cú pháp chuẩn như sau:

AGGREGATE(function_num, options, ref1, [ref2], …)

Trong đó:

• function_num: Xác định loại phép tính cần thực hiện (tính tổng, trung bình, lớn nhất, nhỏ nhất…).
• options: Quy định các dữ liệu sẽ được bỏ qua trong quá trình tính toán (ví dụ: lỗi, hàng ẩn).
• ref1, ref2: Các vùng dữ liệu dùng để tính toán.

Trong Google Sheets, cú pháp này không tồn tại nguyên bản mà được chia nhỏ thành các thành phần tương đương:

• function_num → SUM, AVERAGE, COUNT, MAX, MIN, LARGE, SMALL…
• options → IFERROR, FILTER, SUBTOTAL, QUERY
• ref → Vùng dữ liệu đã được làm sạch hoặc lọc trước

Việc hiểu cú pháp chuẩn này giúp bạn tư duy đúng khi sử dụng hàm AGGREGATE trong Google Sheets, thay vì ghép công thức một cách cảm tính.

Danh sách function_num

Trong hàm AGGREGATE, function_num đại diện cho loại phép tính bạn muốn thực hiện. Trong cấu trúc tiêu chuẩn, có tổng cộng 19 mã số tương ứng với 19 hàm tính toán khác nhau. Tuy nhiên, đối với một người mới bắt đầu, bạn chỉ cần tập trung vào 3 nhóm chính sau đây:

Nhóm 1: Các phép tính cơ bản (Mã 1 – 9)

• 1 (AVERAGE): Tính trung bình cộng của vùng dữ liệu.
• 2 (COUNT): Đếm các ô có chứa dữ liệu kiểu số.
• 3 (COUNTA): Đếm tất cả các ô không trống (bao gồm cả chữ và số).
• 4 (MAX) & 5 (MIN): Tìm giá trị lớn nhất và nhỏ nhất.
• 9 (SUM): Tính tổng toàn bộ vùng dữ liệu. Đây là mã số được tìm kiếm nhiều nhất khi người dùng muốn sử dụng hàm AGGREGATE trong Google Sheets.

Nhóm 2: Các phép tính vị thế (Mã 12 – 15)

• 12 (MEDIAN): Tìm số trung vị (số nằm ở giữa dãy số đã sắp xếp).
• 14 (LARGE) & 15 (SMALL): Tìm giá trị lớn thứ k hoặc nhỏ thứ k. Ví dụ, bạn có thể tìm doanh thu cao thứ 2 hoặc thứ 3 trong danh sách một cách dễ dàng mà không cần sắp xếp lại bảng tính.

Nhóm 3: Các phép tính thống kê nâng cao (Mã 7, 8, 10, 11)

• 7 & 8 (STDEV.S & STDEV.P): Tính độ lệch chuẩn của mẫu hoặc tổng thể.
• 10 & 11 (VAR.S & VAR.P): Tính phương sai để đo lường mức độ biến động của dữ liệu.

Các tùy chọn options 

Việc chọn đúng mã Option không chỉ giúp kết quả tính toán chính xác mà còn giúp bạn tiết kiệm hàng giờ đồng hồ dọn dẹp bảng tính. Dưới đây là bảng phân tích chi tiết các tùy chọn để bạn áp dụng khi sử dụng hàm AGGREGATE trong Google Sheets:

Hướng dẫn sử dụng hàm AGGREGATE trong Google Sheet cơ bản

Trong Google Sheets, hàm AGGREGATE không tồn tại dưới dạng một hàm duy nhất. Nó được thay thế bằng cách kết hợp nhiều hàm khác nhau để đạt cùng mục tiêu tính toán dữ liệu nhưng bỏ qua lỗi, hàng ẩn hoặc dữ liệu đã lọc.

Tức là, khi bạn nhập các công thức dưới đây vào Google Sheets, hệ thống sẽ trả về lỗi Hàm không xác định “AGGREGATE” hoặc #NAME?. Điều này xảy ra vì Google Sheets chưa hỗ trợ AGGREGATE như một hàm gốc. Để áp dụng thành công, bạn cần cài đặt các Add-on hỗ trợ hoặc sử dụng công thức SUBTOTAL tương đương (Mã 101-111) như đã hướng dẫn ở các phần trên. 

Dưới đây là các ví dụ thực tế, trình bày theo đúng tư duy AGGREGATE, giúp newbie dễ hiểu và dễ áp dụng.

Ví dụ 1: Tính tổng, bỏ qua lỗi và hàng ẩn

Khi làm việc với bảng dữ liệu có sử dụng bộ lọc (Filter), hàm SUM thông thường sẽ tính cả những hàng đang bị ẩn, dẫn đến kết quả sai lệch. Đặc biệt, nếu vùng dữ liệu chứa lỗi phát sinh từ công thức khác, hàm SUM sẽ trả về kết quả lỗi toàn bộ.

*Ví dụ, bạn có bảng dữ liệu tính toán sau đây:

*Yêu cầu: Tính tổng doanh thu, chỉ dựa trên các hàng đang hiển thị và bỏ qua các ô lỗi.

Công thức:

Đối với Microsoft Excel:  

=AGGREGATE(9;7; B2:B6)

Đối với Google Sheets không hỗ trợ, thay thế bằng công thức

=SUM(IFERROR(B2:B6;0))

Trong đó:

• IFERROR(B2:B6,0) sẽ chuyển các giá trị lỗi thành 0
• SUM() chỉ tính các giá trị hợp lệ
• Đây chính là cách mô phỏng options bỏ qua lỗi của hàm AGGREGATE trong Excel

Kết quả: 

Ví dụ 2: Tìm giá trị lớn nhất, bỏ qua lỗi và hàng ẩn

Trong nhiều báo cáo, bạn cần tìm doanh thu cao nhất nhưng dữ liệu lại có lỗi hoặc đang bị lọc. Hàm AGGREGATE cho phép xác định giá trị lớn nhất một cách chính xác, chỉ dựa trên dữ liệu hợp lệ. 

Tuy nhiên, khi tìm giá trị lớn nhất, bỏ qua lỗi và hàng ẩn thì hàm AGGREGATE không hỗ trợ trong Google Sheets. Lúc này, bạn chỉ thay thế thành các công thức thay thế như sau:

*Ví dụ, bạn có bảng dữ liệu tính toán sau đây:

*Yêu cầu: Tìm giá trị doanh thu lớn nhất, bỏ qua các hàng bị ẩn và ô lỗi.

Công thức:

Đối với Microsoft Excel:  

=AGGREGATE(4; 7; B2:B6)

Đối với Google Sheets không hỗ trợ, thay thế bằng công thức

=MAX(INDEX(IFERROR(B2:B6 * SUBTOTAL(103; OFFSET(B2; ROW(B2:B6)-ROW(B2); 0)); -1)))

Trong đó:

• SUBTOTAL(103; OFFSET(…)): Kiểm tra từng dòng. Nếu dòng hiện, nó trả về 1; nếu dòng ẩn, nó trả về 0.
• B2:B6 * …: Nhân giá trị doanh thu với 1 hoặc 0. Hàng ẩn sẽ biến thành 0.
• IFERROR(…; -1): Nếu gặp ô #DIV/0!, nó sẽ biến ô đó thành -1 (để hàm MAX bỏ qua và không lấy nhầm vào kết quả).
• MAX: Tìm con số lớn nhất trong danh sách đã được lọc sạch này.

Kết quả: 

Hoặc

• Tại ô C2, nhập =IFERROR(B2; 0) và kéo xuống.
• Để tìm giá trị lớn nhất của các hàng đang hiển thị, dùng hàm 

= SUBTOTAL(104; C2:C6) 

Trong đó: 

• SUBTOTAL: Là hàm tính toán cho một nhóm dữ liệu con, có khả năng đặc biệt là nhận diện được trạng thái hiển thị hàng.
• 104 (Mã chức năng): Con số này đóng hai vai trò quan trọng:

Số 4: Đại diện cho hàm MAX (Tìm giá trị lớn nhất).

Số 100 phía trước (104): Ra lệnh cho hàm bỏ qua các hàng bị ẩn thủ công (Chuột phải -> Ẩn hàng) và các hàng bị ẩn do bộ lọc (Filter).

• C2:C6: Vùng dữ liệu mà bạn muốn tìm giá trị lớn nhất.

Ví dụ 3: Tính trung bình, chỉ bỏ qua hàng ẩn

Trong thực tế xử lý dữ liệu trên Google Sheets, việc sử dụng các hàm tính toán thông thường như AVERAGE sẽ bao gồm tất cả các giá trị trong dải ô, ngay cả khi bạn đã ẩn hàng thủ công. Điều này thường dẫn đến kết quả sai lệch khi bạn chỉ muốn phân tích những dữ liệu đang hiển thị trên màn hình.

Để giải quyết vấn đề này, thay vì sử dụng hàm AGGREGATE (vốn chỉ có trên Excel), chúng ta sẽ kết hợp hàm SUBTOTAL với các hàm mảng hiện đại. Công thức dưới đây sẽ giúp bạn tính toán chuẩn xác giá trị trung bình bằng cách tự động loại trừ các hàng đã bị ẩn, đảm bảo tính linh hoạt và chính xác cho các báo cáo động.

*Ví dụ, bạn có bảng dữ liệu tính toán sau đây:

*Yêu cầu: Tính trung bình doanh thu đạt được trong các tháng, chỉ bỏ qua hàng ẩn.

Công thức:

Đối với Microsoft Excel:  

=AGGREGATE(1; 5; B2:B6)

Đối với Google Sheets không hỗ trợ, thay thế bằng công thức

• Tại ô C2, nhập =IFERROR(B2; 0) và kéo xuống.
• Để tìm giá trị lớn nhất của các hàng đang hiển thị, dùng hàm 

= SUBTOTAL(101; C2:C6) 

Trong đó: 

• 101: Mã hàm tính trung bình (AVERAGE) đồng thời bỏ qua các giá trị trong hàng bị ẩn.
• C2:C6: Vùng dữ liệu doanh thu.

Kết quả:

Ví dụ 4: Đếm số thứ tự tự động, bỏ qua hàng ẩn

Trong Microsoft Excel, người dùng thường dựa vào hàm AGGREGATE với các tùy chọn linh hoạt để tạo dãy số thứ tự thông minh, có khả năng tự điều chỉnh khi lọc dữ liệu. Tuy nhiên, vì Google Sheets không hỗ trợ hàm này, việc cố gắng áp dụng cấu trúc cũ sẽ chỉ dẫn đến những thông báo lỗi không mong muốn. Để duy trì tính chuyên nghiệp cho bảng tính mà không cần đến AGGREGATE, chúng ta sẽ sử dụng hàm SUBTOTAL có sẵn trong Google Sheets.

Trong một bảng dữ liệu lớn, bằng cách sử dụng SUBTOTAL, Google Sheets sẽ tự động nhận diện các hàng đang hiển thị để đánh số một cách liên tục. Ngay cả khi bạn ẩn đi một vài hàng, danh sách số thứ tự sẽ tự động cập nhật lại ngay lập tức, đảm bảo dãy số luôn bắt đầu từ 1 và liên tục cho đến hết.

*Ví dụ, bạn có bảng dữ liệu tính toán sau đây:

*Yêu cầu: Đếm số nhân viên được hiển thị khi lọc, bỏ qua hàng ẩn

Công thức:

Đối với Microsoft Excel:

=AGGREGATE(3; 5; A2:A6)

Đối với Google Sheets không hỗ trợ, thay thế bằng công thức:

=SUBTOTAL(103; A2:A6)

Trong đó:

• 103: Trong Google Sheets, mã này yêu cầu hàm thực hiện phép đếm COUNTA (đếm các ô không trống) nhưng có tính năng đặc biệt là tự động bỏ qua các hàng bị ẩn thủ công hoặc bị ẩn do bộ lọc (Filter).
• A2:A6: Dải ô chứa danh sách tên nhân viên bạn muốn đếm.

Kết quả: 

Lỗi và cách khắc phục khi sử dụng hàm AGGREGATE

Trong quá trình làm việc với bảng tính, việc phát sinh lỗi là điều không thể tránh khỏi khi sử dụng hàm AGGREGATE. Dưới đây là một số lỗi phổ biến khi sử dụng hàm AGGREGATE:

Lỗi #NAME?

Lỗi #NAME? xảy ra nguyên nhân là do Google Sheets hiện tại chưa cập nhật AGGREGATE vào danh sách hàm nội bộ. Khi bạn nhập =AGGREGATE(…), hệ thống sẽ không nhận diện được tên hàm này và trả về lỗi #NAME?.

Cách khắc phục lỗi này là thay vì cố gắng tìm cách sửa tên hàm, bạn hãy chuyển sang sử dụng các giải pháp thay thế được đề cập ở phần trên. Cụ thể, bạn hãy dùng hàm SUBTOTAL cho các phép tính cơ bản hoặc kết hợp bộ đôi FILTER + IFERROR cho các phép tính nâng cao. 

Lỗi #VALUE!

Khi thực hiện các phép toán tổng hợp, lỗi #VALUE! xảy ra do một hoặc nhiều ô trong vùng tham chiếu của bạn không phải là số mà là định dạng văn bản Text, hoặc có các khoảng trắng thừa mà mắt thường không thấy được. Khi hàm tổng hợp cố gắng xử lý một ký tự văn bản và gây ra lỗi. 

Để khắc phục, bạn hãy chuyển hàm VALUE() để chuyển đổi dữ liệu về dạng số. Bạn có thể sử dụng công cụ Dọn dẹp dữ liệu để loại bỏ khoảng trắng. 

Lưu ý quan trọng khi sử dụng hàm AGGREGATE trong Google Sheets

Không giống như các hàm đơn giản, hàm AGGREGATE vận hành dựa trên những nguyên lý về hướng dữ liệu và tham chiếu bộ nhớ rất đặc thù. Dưới đây là  một số lưu ý mà bạn cần thuộc lòng khi sử dụng hàm AGGREGATE trong Google Sheets:

• Ưu tiên phạm vi dọc và hướng dữ liệu

Hầu hết các giải pháp thay thế như SUBTOTAL hay QUERY được tối ưu hóa tốt nhất khi dữ liệu được sắp xếp theo chiều dọc (từ trên xuống dưới). Khi bạn áp dụng công thức lên một phạm vi dọc, Google Sheets có khả năng nhận diện các thay đổi về trạng thái của hàng (hàng đang hiển thị hay đang bị ẩn) một cách cực kỳ nhạy bén. Tuy nhiên, khi bạn xoay ngang dữ liệu, việc ẩn các cột thường không làm thay đổi kết quả của các hàm tổng hợp như cách nó làm với hàng.

• Giới hạn về tham chiếu 3D

Trong Excel, hàm AGGREGATE có thể xử lý các tham chiếu xuyên suốt nhiều trang tính theo kiểu Sheet1:Sheet3!A1:A10. Tuy nhiên, trong Google Sheets, cấu trúc này hoàn toàn không hoạt động. 

Google Sheets quản lý bộ nhớ và tham chiếu dữ liệu theo cách khác. Nếu bạn cố gắng nhập một dải ô xuyên trang tính vào các hàm giả lập AGGREGATE, bạn sẽ nhận về lỗi ngay lập tức. 

• Quy tắc về đối số để tránh lỗi #VALUE! hệ thống

Một trong những nguyên nhân hàng đầu dẫn đến lỗi #VALUE! khi thực hiện các phép toán tổng hợp là sự thiếu hụt các đối số bắt buộc. Đối với hàm AGGREGATE chuẩn, bạn phải cung cấp tối thiểu 3 thành phần:

• function_num: Mã số chức năng (ví dụ: 9 cho SUM).
• options: Tùy chọn bỏ qua (ví dụ: 6 để bỏ qua lỗi).
• ref1: Phạm vi dữ liệu thực tế.

Lời kết

Qua bài viết này, bạn đã có cái nhìn đầy đủ và đúng bản chất về hàm AGGREGATE trong Google Sheets. Mặc dù Google Sheets không hỗ trợ đa năng  hàm AGGREGATE giống như Excel, nhưng với các công cụ mạnh mẽ như SUBTOTAL, QUERY, FILTER, IFERROR. Tất cả các hàm mảng, bạn hoàn toàn có thể tổng hợp dữ liệu chính xác, bỏ qua lỗi, loại trừ hàng ẩn và xử lý dữ liệu phức tạp một cách linh hoạt. 

Nếu bạn là người mới bắt đầu với Google Sheets hoặc đang gặp khó khăn khi áp dụng các công thức tổng hợp dữ liệu nâng cao, GCS Việt Nam luôn sẵn sàng đồng hành và hỗ trợ bạn.  Liên hệ GCS Việt Nam ngay hôm nay để được hỗ trợ nhanh chóng qua các kênh:

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777