Gửi ticket
Khuyến mại
Kiến thức
Quản lý dịch vụ
Bạn có đang “đau đầu” với việc kéo công thức hàng trăm, thậm chí hàng ngàn ô trong Google Sheets mỗi khi dữ liệu thay đổi? Hay những lỗi #REF!, #VALUE! không mong muốn cứ liên tục xuất hiện làm gián đoạn công việc của bạn? Đừng lo lắng! Google Sheets đã trang bị một công cụ “ma thuật” thực sự, có khả năng biến đổi cách bạn làm việc với bảng tính mãi mãi: đó chính là hàm ARRAYFORMULA. Hãy cùng khám phá cách hàm tính mạnh mẽ này giúp bạn tự động hóa, tiết kiệm thời gian và nâng tầm hiệu quả công việc chỉ với một cú pháp đơn giản!
Hàm ARRAYFORMULA là gì?
Hàm ARRAYFORMULA là một hàm trong Google Sheets (và Excel với tên gọi khác là CSE – Ctrl+Shift+Enter array formulas) cho phép bạn thực hiện một công thức trên một phạm vi ô (một mảng) thay vì chỉ một ô duy nhất. Điều “ma thuật” ở đây là nó sẽ tự động điền kết quả vào các ô tương ứng trong phạm vi mà bạn chỉ định, mà không cần phải kéo công thức xuống từng dòng một.
Cú pháp chung của hàm này như sau:
=ARRAYFORMULA(công_thức_hoặc_mảng_dữ_liệu)
Sự khác biệt của hàm thông thường và hàm ARRAYFORMULA nằm ở chỗ:
- Công thức thông thường: Khi bạn nhập =A2*B2 vào ô C2, bạn sẽ phải nắm lấy góc vuông nhỏ ở dưới cùng bên phải của ô C2 và kéo xuống để áp dụng công thức cho C3, C4, C5,… Nếu bảng tính của bạn có hàng trăm, hàng ngàn dòng, việc này sẽ tốn rất nhiều thời gian và công sức.
- Với ARRAYFORMULA: Bạn chỉ cần nhập một công thức duy nhất vào ô C2, ví dụ: =ARRAYFORMULA(A2:A*B2:B). Ngay lập tức, cột C sẽ tự động được điền đầy đủ kết quả phép nhân của từng cặp ô tương ứng trong cột A và B. Bạn không cần làm gì thêm!
Lợi ích khi sử dụng hàm ARRAYFORMULA
Việc làm chủ hàm ARRAYFORMULA không chỉ giúp bạn giải quyết các bài toán trên bảng tính hiệu quả hơn mà còn mang lại vô vàn lợi ích chiến lược cho công việc của bạn. Đây là những lý do tại sao ARRAYFORMULA được coi là một công cụ “phải có” cho bất kỳ ai làm việc thường xuyên với Google Sheets:
1. Tự động hóa và tiết kiệm thời gian vượt trội
Đây là lợi ích rõ ràng nhất. Thay vì phải sao chép và dán (hoặc kéo) công thức cho hàng trăm, thậm chí hàng ngàn dòng, ARRAYFORMULA cho phép bạn thực hiện tất cả chỉ với một cú pháp duy nhất ở ô đầu tiên. Mọi dữ liệu mới được thêm vào sẽ tự động được tính toán mà bạn không cần động tay. Tưởng tượng xem, bạn sẽ tiết kiệm được bao nhiêu thời gian và công sức mỗi ngày!
2. Giảm thiểu lỗi và độ chính xác được nâng cao
Lỗi do “quên kéo công thức”, “kéo sai phạm vi” hay “vô tình xóa công thức” là nỗi ám ảnh của nhiều người dùng bảng tính. Với ARRAYFORMULA, chỉ có một công thức duy nhất kiểm soát cả cột/vùng, loại bỏ gần như hoàn toàn những rủi ro này. Điều này đảm bảo tính nhất quán và độ chính xác của dữ liệu trên toàn bộ bảng tính của bạn.
3. Bảng tính trình bày gọn gàng và dễ dàng quản lý
Khi nhìn vào một bảng tính mà mỗi ô đều có một công thức riêng biệt, bạn sẽ thấy nó rất “rối mắt” và khó theo dõi. ARRAYFORMULA giúp đơn giản hóa cấu trúc bảng tính bằng cách tập trung logic tính toán vào một ô duy nhất. Điều này không chỉ giúp bảng tính của bạn trông chuyên nghiệp hơn mà còn cực kỳ dễ dàng để kiểm tra, chỉnh sửa hoặc bàn giao cho người khác.
4. Xử lý dữ liệu lớn hiệu quả và mượt mà
Đối với các tập dữ liệu có quy mô lớn, việc sử dụng các công thức kéo xuống có thể làm cho Google Sheets của bạn trở nên ì ạch và dễ bị treo. ARRAYFORMULA thường được tối ưu hóa hơn trong việc xử lý các phép tính trên phạm vi lớn, giúp bảng tính hoạt động nhanh chóng và mượt mà hơn, ngay cả với hàng chục ngàn dòng dữ liệu.
5. Khả năng kết hợp mạnh mẽ với một số các hàm khác
Sức mạnh thực sự của ARRAYFORMULA bùng nổ khi nó được kết hợp với các hàm khác như IF, VLOOKUP, FILTER, UNIQUE, SUM, AVERAGE, v.v. Điều này cho phép bạn tạo ra các công thức cực kỳ linh hoạt và mạnh mẽ, giải quyết được những bài toán phức tạp mà các công thức thông thường khó lòng làm được, ví dụ như:
- Tra cứu nhiều giá trị cùng lúc bằng một công thức VLOOKUP duy nhất.
- Lọc dữ liệu động theo nhiều điều kiện mà không cần bộ lọc thủ công.
- Tự động phân loại hoặc gán trạng thái cho hàng loạt dữ liệu.
Tóm lại, ARRAYFORMULA không chỉ là một công cụ tiện ích mà còn là một phương pháp tư duy mới trong việc quản lý và phân tích dữ liệu. Việc ứng dụng nó sẽ giúp bạn nâng cao năng suất, giảm thiểu sai sót và đưa ra quyết định dựa trên dữ liệu một cách tự tin hơn.
Cách sử dụng hàm ARRAYFORMULA cơ bản nhất
Để bạn có thể dễ dàng hình dung về cách sử dụng của hàm ARRAYFORMULA, chúng tôi sẽ đưa ra một bảng tính đi kèm với ví dụ cụ thể như sau:
Ví dụ 1: Tính “Thành Tiền” tự động cho từng sản phẩm
*Bài toán: Bạn muốn tính Thành Tiền (Số Lượng * Đơn Giá) cho tất cả các sản phẩm trong danh sách.
Cách áp dụng ARRAYFORMULA:
- Tại ô D1 (hàng tiêu đề của cột “Thành Tiền”), bạn có thể gõ “Thành Tiền”.
- Tại ô D2 (ngay dưới tiêu đề), nhập công thức sau:
=ARRAYFORMULA(B2:B*C2:C)
Giải thích:
- B2:B và C2:C là các phạm vi cột “Số Lượng” và “Đơn Giá” bắt đầu từ hàng 2 và kéo dài xuống hết cột.
- * là phép nhân.
Kết quả: Cột D sẽ tự động hiển thị kết quả “Thành Tiền” cho từng dòng tương ứng. Bạn sẽ thấy bảng tính của mình trông như thế này:
Ví dụ 2: Phân loại “Ghi Chú” dựa trên Thành Tiền
*Bài toán: Bạn muốn tự động thêm “Ghi Chú” vào cột E, ví dụ:
- Nếu Thành Tiền >= 500: “Sản phẩm bán chạy”
- Nếu Thành Tiền < 500: “Cần đẩy mạnh”
Cách áp dụng ARRAYFORMULA (kết hợp với hàm IF):
- Tại ô E1 (hàng tiêu đề của cột “Ghi Chú”), bạn có thể gõ “Ghi Chú”.
- Tại ô E2 (ngay dưới tiêu đề), nhập công thức sau:
=ARRAYFORMULA(IF(D2:D>=500;”Sản phẩm bán chạy”;”Cần đẩy mạnh”))
Giải thích:
- D2:D là phạm vi cột “Thành Tiền” mà bạn vừa tính ở trên.
- IF(D2:D>=500;”Sản phẩm bán chạy”;”Cần đẩy mạnh”): Đây là hàm IF được áp dụng cho từng giá trị trong cột D. Nếu giá trị trong cột D (của từng hàng) lớn hơn hoặc bằng 500, nó sẽ trả về “Sản phẩm bán chạy”, ngược lại là “Cần đẩy mạnh”.
Kết quả: Cột E sẽ tự động được điền đầy đủ các ghi chú tương ứng:
Với hai ví dụ này trên cùng một bảng, bạn có thể thấy rõ ràng cách hàm ARRAYFORMULA giúp bạn tự động hóa việc tính toán và phân loại dữ liệu trên toàn bộ cột chỉ với một lần nhập công thức. Điều này thực sự “biến đổi” cách bạn làm việc với bảng tính đó!
Nâng cao với hàm ARRAYFORMULA: Kết hợp với các hàm khác
Sức mạnh thực sự của hàm ARRAYFORMULA bộc lộ khi bạn kết hợp nó với các hàm Google Sheets khác. Khả năng “phủ sóng” công thức trên toàn bộ dải ô giúp bạn tạo ra những giải pháp tự động hóa cực kỳ linh hoạt và hiệu quả. Chúng ta sẽ tiếp tục sử dụng bảng dữ liệu sản phẩm ở trên để khám phá các ứng dụng nâng cao này.
Dưới đây là bảng dữ liệu tương tự như bảng tính ở phần trên, sau đó chúng ta có thể thể áp dụng kết hợp với các hàm khác như sau:
Hàm ARRAYFORMULA kết hợp với VLOOKUP (hoặc XLOOKUP)
Đây được xem là một trong những ứng dụng mạnh mẽ nhất! Thay vì kéo VLOOKUP/XLOOKUP từng dòng, bạn có thể tra cứu hàng loạt dữ liệu chỉ với một công thức.
Giả sử bạn có một bảng thông tin khách hàng ở một Sheet khác (Sheet Khách Hàng) như sau:
Bảng Sheet Khách Hàng:
Bạn muốn thêm cột “Hạng Khách Hàng” vào bảng dữ liệu sản phẩm chính của mình.
Cách áp dụng ARRAYFORMULA với VLOOKUP:
- Thêm cột “Hạng Khách Hàng” vào vị trí H1 trong Sheet1.
- Tại ô H2 trong Sheet1, nhập công thức:
=ARRAYFORMULA(VLOOKUP(F2:F;’Sheet Khách Hàng’!A:B;2;FALSE))
Giải thích:
- F2:F: Phạm vi cột “Khách Hàng” trong bảng sản phẩm của bạn. Đây là giá trị tra cứu (lookup_value) cho VLOOKUP.
- ‘Sheet Khách Hàng’!A:B: Phạm vi bảng tra cứu trong Sheet Khách Hàng.
- 2: Chỉ số cột của kết quả bạn muốn lấy (cột “Hạng Khách Hàng” là cột thứ 2 trong bảng tra cứu).
- FALSE: Đảm bảo mức độ tìm kiếm chính xác.
Kết quả: Cột H trong Sheet1 sẽ tự động điền hạng khách hàng cho từng giao dịch:
Hàm ARRAYFORMULA kết hợp với UNIQUE và FILTER
Bạn muốn tạo một danh sách các “Vị trí kho” duy nhất hoặc lọc ra các sản phẩm “Cần đẩy mạnh” một cách tự động.
Bài toán 1: Lấy danh sách các “Vị trí kho” duy nhất.
- Trên một Sheet mới (ví dụ Sheet Báo Cáo), tại ô A1, nhập tiêu đề “Vị trí kho duy nhất”.
- Tại ô A2, nhập công thức:
=ARRAYFORMULA(UNIQUE(Sheet1!G2:G))
Giải thích:
- Sheet1!G2:G: Phạm vi cột “Vị trí kho” trong bảng sản phẩm chính.
- UNIQUE(): Hàm này sẽ trích xuất tất cả các giá trị duy nhất từ phạm vi đã cho.
Kết quả: Cột A trong Sheet Báo Cáo sẽ tự động liệt kê các vị trí kho không trùng lặp:
Bài toán 2: Lọc ra tất cả các sản phẩm có “Ghi Chú” là “Cần đẩy mạnh”.
Trên cùng Sheet Báo Cáo, tại ô C1, nhập tiêu đề “Sản phẩm cần đẩy mạnh”.
Tại ô C2, nhập công thức:
=ARRAYFORMULA(FILTER(Sheet1!A2:E;Sheet1!E2:E=”Cần đẩy mạnh”))
Giải thích:
- Sheet1!A2:E: Phạm vi dữ liệu bạn muốn lọc (toàn bộ các cột từ A đến E trong Sheet1).
- Sheet1!E2:E=”Cần đẩy mạnh”: Điều kiện lọc. Nó sẽ kiểm tra từng ô trong cột E của Sheet1 và chỉ giữ lại những hàng nào có giá trị là “Cần đẩy mạnh”.
Kết quả: Bắt đầu từ ô C2 trong Sheet Báo Cáo, một bảng mới sẽ tự động xuất hiện, chỉ chứa các sản phẩm cần đẩy mạnh cùng với các thông tin liên quan:
Hàm ARRAYFORMULA kết hợp với SUM
Thông thường, bạn sẽ dùng SUMIF hoặc SUMIFS để tính tổng có điều kiện. Tuy nhiên, trong một số trường hợp phức tạp hơn, hoặc khi muốn tính tổng của một mảng kết quả từ các phép tính khác, ARRAYFORMULA kết hợp với SUM và IF sẽ rất mạnh.
Bài toán: Tính tổng “Thành Tiền” của các “Sản phẩm bán chạy” (những sản phẩm có Ghi Chú là “Sản phẩm bán chạy”).
- Trên Sheet Báo Cáo, tại ô E1, nhập tiêu đề “Tổng SP bán chạy”.
- Tại ô E2, nhập công thức:
=ARRAYFORMULA(SUM(IF(Sheet1!E2:E=”Sản phẩm bán chạy”;Sheet1!D2:D;0)))
Giải thích:
- IF(Sheet1!E2:E=”Sản phẩm bán chạy”,Sheet1!D2:D,0): Hàm IF này sẽ duyệt qua từng ô trong cột “Ghi Chú” (Sheet1!E2:E). Nếu ghi chú là “Sản phẩm bán chạy”, nó sẽ lấy giá trị “Thành Tiền” tương ứng (Sheet1!D2:D). Ngược lại, nó sẽ trả về 0. Kết quả của phần này là một mảng các giá trị “Thành Tiền” và số 0.
- ARRAYFORMULA(…): Đảm bảo rằng hàm IF được thực thi trên toàn bộ phạm vi, tạo ra mảng kết quả.
- SUM(…): Hàm SUM sau đó sẽ tính tổng tất cả các số trong mảng đó (bỏ qua các số 0).
Kết quả: Ô E2 trong Sheet Báo Cáo sẽ hiển thị tổng doanh thu của các sản phẩm bán chạy:
Những ví dụ này cho thấy hàm ARRAYFORMULA không chỉ giới hạn ở các phép tính đơn giản mà còn là “xương sống” để xây dựng các công thức phức tạp, giải quyết những vấn đề đa chiều trên bảng tính. Khả năng kết hợp linh hoạt với các hàm khác chính là điều biến nó trở thành một công cụ “ma thuật” thực sự!
Các lỗi thường gặp và cách khắc phục khi dùng hàm ARRAYFORMULA
Mặc dù hàm ARRAYFORMULA là một công cụ cực kỳ mạnh mẽ, nhưng đôi khi người dùng vẫn gặp phải một số lỗi phổ biến. Việc hiểu rõ các lỗi này và cách khắc phục chúng sẽ giúp bạn sử dụng hàm hiệu quả hơn và tránh những tình huống “dở khóc dở cười” với bảng tính của mình.
1. Lỗi #REF! (Reference Error) – Lỗi tham chiếu vòng lặp hoặc dữ liệu bị đè
Đây là một trong những lỗi phổ biến nhất khi mới bắt đầu với hàm ARRAYFORMULA. Lỗi này xảy ra khi công thức ARRAYFORMULA cố gắng mở rộng kết quả của nó vào một ô đã có dữ liệu, hoặc tệ hơn là cố gắng đọc dữ liệu từ chính ô mà nó đang ghi vào (tham chiếu vòng lặp).
Ví dụ tình huống gây lỗi: Giả sử bạn có công thức =ARRAYFORMULA(B2:B*C2:C) ở ô D2 để tính cột “Thành Tiền” ở ví dụ của phần cách sử dụng hàm ARRAYFORMULA. Sau đó, bạn hoặc người khác vô tình nhập thủ công một giá trị bất kỳ vào ô D5. Ngay lập tức, công thức ở D2 sẽ báo lỗi #REF! hoặc hiển thị thông báo “Array result was not expanded because it would overwrite data”.
Lý do: Công thức ở D2 cần không gian trống từ D2 trở xuống để điền kết quả. Khi ô D5 có dữ liệu, nó bị chặn.
Cách khắc phục:
- Xóa dữ liệu thủ công: Duyệt qua các ô trong cột đích của ARRAYFORMULA (trong ví dụ này là cột D) và xóa bất kỳ dữ liệu nào không phải là một phần của công thức ARRAYFORMULA. Đảm bảo rằng toàn bộ phạm vi mà ARRAYFORMULA dự định ghi kết quả là trống.
- Kiểm tra phạm vi đầu ra: Luôn đảm bảo rằng cột hoặc các ô nơi bạn đặt công thức ARRAYFORMULA có đủ không gian trống bên dưới hoặc bên cạnh để công thức mở rộng.
- Tránh tham chiếu vòng lặp: Đừng bao giờ đặt công thức ARRAYFORMULA trong cùng cột hoặc phạm vi mà nó đang cố gắng đọc dữ liệu để tính toán. Ví dụ, công thức =ARRAYFORMULA(D2:D*2) đặt ở D2 chắc chắn sẽ lỗi.
2. Lỗi #VALUE! (Value Error) – Lỗi giá trị không hợp lệ
Lỗi này thường xuất hiện khi bạn cố gắng thực hiện một phép tính hoặc thao tác với các kiểu dữ liệu không tương thích.
Ví dụ tình huống gây lỗi: Tiếp tục với công thức tính “Thành Tiền” =ARRAYFORMULA(B2:B*C2:C). Nếu trong cột “Số Lượng” (B) hoặc “Đơn Giá” (C) có một ô chứa văn bản (ví dụ: “năm” thay vì số “5”), công thức sẽ báo lỗi #VALUE! ở hàng tương ứng.
Lý do: Bạn không thể thực hiện phép nhân giữa một số và một chuỗi văn bản.
Cách khắc phục:
- Kiểm tra kiểu dữ liệu: Đảm bảo tất cả các ô được tham chiếu trong công thức đều chứa kiểu dữ liệu phù hợp với phép tính bạn đang thực hiện (ví dụ: phải là số nếu đang nhân/cộng/chia).
Sử dụng hàm xử lý lỗi IFERROR(): Đây là một cách thanh lịch để ẩn lỗi và làm cho bảng tính trông chuyên nghiệp hơn. Bạn có thể bọc công thức của mình bằng IFERROR() để trả về một giá trị khác (ví dụ: ô trống “”) nếu có lỗi xảy ra. Ví dụ:
=ARRAYFORMULA(IFERROR(B2:B*C2:C; “”))
- Công thức này sẽ tính toán bình thường. Nếu ở bất kỳ hàng nào mà B2:B*C2:C trả về lỗi #VALUE!, nó sẽ hiển thị một ô trống thay vì lỗi.
3. ARRAYFORMULA không hoạt động như mong muốn (Kết quả rỗng hoặc không đầy đủ)
Đôi khi, công thức ARRAYFORMULA không báo lỗi nhưng lại không hiển thị kết quả cho tất cả các dòng, hoặc hiển thị 0 ở những dòng trống không mong muốn.
Ví dụ tình huống: Bạn sử dụng công thức phân loại “Ghi Chú” =ARRAYFORMULA(IF(D2:D>=500;”Sản phẩm bán chạy”;”Cần đẩy mạnh”)). Nếu cột D (Thành Tiền) có các ô trống ở cuối danh sách, cột E (Ghi Chú) sẽ hiển thị “Cần đẩy mạnh” cho các dòng đó, hoặc thậm chí là FALSE nếu bạn không xử lý điều kiện.
Lý do:
- Xử lý ô trống không đúng cách: Mặc định, các phép toán số học với ô trống thường coi là 0. Với hàm IF, ô trống có thể được coi là FALSE hoặc 0 tùy ngữ cảnh.
- Hàm không tương thích hoàn toàn với ARRAYFORMULA: Một số hàm tổng hợp như SUM, AVERAGE, COUNT (khi đứng độc lập) thường chỉ trả về một giá trị duy nhất và không cần sử dụng bởi ARRAYFORMULA. Khi bạn dùng chúng, chúng vẫn chỉ trả về một giá trị đó mà không “mở rộng” như mong muốn. ARRAYFORMULA phát huy hiệu quả nhất với các hàm xử lý từng phần tử trong một mảng (như các phép toán số học, IF, VLOOKUP, FILTER, UNIQUE).
Cách khắc phục:
Sử dụng IF kết hợp LEN() để kiểm tra ô trống: Đây là phương pháp tốt nhất để đảm bảo ARRAYFORMULA chỉ tính toán khi có dữ liệu ở ô đầu vào tương ứng. Ví dụ (cho cột “Thành Tiền” D):
=ARRAYFORMULA(IF(LEN(B2:B)*LEN(C2:C)>0; B2:B*C2:C; “”))
Công thức này chỉ thực hiện phép nhân B2:B*C2:C nếu cả ô trong cột B và cột C đều không trống (có độ dài ký tự lớn hơn 0). Nếu không, hệ thống sẽ trả về kết quả là ô trống “”. Ví dụ (cho cột “Ghi Chú” E):
=ARRAYFORMULA(IF(LEN(D2:D)>0; IF(D2:D>=500;”Sản phẩm bán chạy”;”Cần đẩy mạnh”); “”))
- Công thức này đảm bảo rằng chỉ khi cột D có dữ liệu thì cột E mới hiển thị ghi chú, các dòng trống sẽ là “”.
- Hiểu rõ cách hàm hoạt động với mảng: Nắm vững hàm nào tự động mở rộng và hàm nào cần ARRAYFORMULA để mở rộng.
Việc nắm vững các lỗi này và biết cách khắc phục chúng sẽ giúp bạn làm chủ hàm ARRAYFORMULA một cách tự tin, biến nó thành công cụ đắc lực trong việc tự động hóa và tối ưu hóa bảng tính Google Sheets của mình. Đừng ngần ngại thử nghiệm và thực hành để quen thuộc hơn nhé!
Lưu ý khi sử dụng hàm ARRAYFORMULA trong Google Sheets
Hàm ARRAYFORMULA là một công cụ mạnh mẽ trong Google Sheets, nhưng để sử dụng nó hiệu quả và tránh được các vấn đề không thực sự mong muốn, bạn cần lưu ý một số điểm sau:
1. Luôn để ý đến Phạm vi Đầu ra (Output Range)
Đây là lưu ý quan trọng nhất. ARRAYFORMULA cần một không gian trống hoàn toàn để mở rộng kết quả của nó.
- Không được có dữ liệu chắn đường: Nếu có bất kỳ ô nào trong phạm vi mà ARRAYFORMULA dự định ghi kết quả đã chứa dữ liệu (dù là nhập thủ công, một công thức khác, hay thậm chí chỉ là một khoảng trắng), công thức của bạn sẽ báo lỗi #REF! hoặc hiển thị thông báo “Array result was not expanded because it would overwrite data”.
- Cách khắc phục: Đảm bảo cột hoặc vùng mà bạn đặt ARRAYFORMULA là trống hoàn toàn, hoặc xóa bất kỳ dữ liệu nào đang “cản trở” đường đi của nó.
- Tham chiếu vòng lặp: Tránh để ARRAYFORMULA tham chiếu đến chính phạm vi mà nó đang ghi kết quả.
- Ví dụ sai: =ARRAYFORMULA(D2:D*2) đặt ở ô D2 sẽ gây lỗi vì công thức đang cố gắng đọc từ cột D (bao gồm chính nó) và ghi vào cột D.
2. Không phải hàm nào cũng hoạt động “tự động mở rộng” với ARRAYFORMULA
ARRAYFORMULA phát huy sức mạnh nhất với các hàm và phép toán có khả năng xử lý mảng từng phần tử (element-by-element operation) như:
- Các phép toán số học: +, –, *, /, ^.
- Hàm logic: IF, AND, OR.
- Hàm tra cứu: VLOOKUP, XLOOKUP (khi giá trị tra cứu là một mảng).
- Hàm xử lý văn bản: LEFT, RIGHT, MID, LEN, TEXT (áp dụng cho từng ô).
- Hàm mảng: FILTER, UNIQUE, SORT.
Tuy nhiên, một số hàm tổng hợp (Aggregate functions) như SUM(), AVERAGE(), COUNT(), MAX(), MIN() khi đứng độc lập thì không cần và không tự động mở rộng khi được bọc bởi ARRAYFORMULA. Chúng vẫn chỉ trả về một giá trị duy nhất.
- Lưu ý: Bạn vẫn có thể sử dụng các hàm tổng hợp này bên trong một ARRAYFORMULA khi bạn muốn chúng tính toán trên một mảng kết quả tạm thời.
- Ví dụ: =ARRAYFORMULA(SUM(IF(B2:B>10, C2:C, 0))) – Ở đây, IF sẽ tạo ra một mảng các giá trị dựa trên điều kiện, và SUM sẽ tính tổng của mảng đó (nhưng kết quả cuối cùng vẫn là một số duy nhất).
3. Cẩn trọng với các tham chiếu cố định (Absolute References) và tương đối (Relative References)
Khi bạn sử dụng ARRAYFORMULA, Google Sheets tự động điều chỉnh các tham chiếu để hoạt động trên phạm vi (mảng).
- Tham chiếu tương đối (ví dụ: A2): Sẽ tự động trở thành phạm vi (A2:A) nếu bạn nhập công thức và nhấn Ctrl+Shift+Enter hoặc khi bạn viết công thức ARRAYFORMULA trực tiếp.
- Tham chiếu cố định (ví dụ: $A$2): Sẽ giữ nguyên là một ô cố định. Điều này hữu ích khi bạn muốn nhân hoặc cộng toàn bộ một cột với một giá trị cố định từ một ô duy nhất.
4. Hiểu cách xử lý dòng trống
Mặc định, các phép toán số học trên ô trống sẽ coi ô trống là 0. Điều này có thể dẫn đến việc hiển thị các giá trị 0 ở cuối cột mà bạn không mong muốn.
- Giải pháp: Luôn sử dụng một điều kiện IF(LEN(ô_kiểm_tra)>0, công_thức, “”) để chỉ thực hiện tính toán khi có dữ liệu trong hàng đó.
5. Cú pháp phức tạp hơn khi kết hợp nhiều hàm
Khi kết hợp ARRAYFORMULA với nhiều hàm lồng nhau, cú pháp có thể trở nên dài và khó đọc.
- Mẹo:
- Viết từng phần: Xây dựng công thức từ trong ra ngoài hoặc từng phần một để dễ kiểm tra và gỡ lỗi.
- Sử dụng khoảng trắng: Google Sheets cho phép bạn thêm khoảng trắng vào công thức để dễ đọc hơn (ví dụ: =ARRAYFORMULA( IF ( LEN(B2:B)>0, … ))).
- Bình luận: Nếu công thức quá dài và phức tạp, hãy ghi chú lại mục đích của từng phần.
6. Cân nhắc hiệu suất với bảng tính rất lớn
Mặc dù ARRAYFORMULA thường hiệu quả hơn việc kéo công thức thủ công, nhưng đối với những bảng tính cực kỳ lớn (hàng trăm ngàn dòng) và các công thức ARRAYFORMULA quá phức tạp (nhiều hàm lồng nhau, tham chiếu đến nhiều sheet), nó vẫn có thể ảnh hưởng đến tốc độ tải và tính toán của bảng tính.
- Giải pháp:
- Giới hạn phạm vi nếu có thể (ví dụ: A2:A5000 thay vì A2:A nếu bạn biết dữ liệu không bao giờ vượt quá 5000 dòng).
- Tránh các phép tính quá “nặng” không cần thiết.
- Cân nhắc sử dụng Google Apps Script cho các tác vụ tự động hóa cực kỳ phức tạp hoặc xử lý dữ liệu lớn.
Nắm vững những lưu ý này sẽ giúp bạn khai thác tối đa sức mạnh của hàm ARRAYFORMULA, biến Google Sheets thành một công cụ tự động hóa đáng tin cậy và hiệu quả cho mọi nhu cầu công việc của bạn.
Một số câu hỏi thường gặp
1. ARRAYFORMULA có hoạt động trong Excel không?
Mặc dù ý tưởng tương tự có tồn tại trong Excel dưới dạng “công thức mảng CSE” (Array Formulas, được kích hoạt bằng Ctrl+Shift+Enter), nhưng cú pháp và cách hoạt động của ARRAYFORMULA như trong Google Sheets là độc quyền của Google Sheets. Excel hiện đại (Microsoft 365) có các hàm mảng động như SPILL, FILTER, UNIQUE có chức năng tương tự, nhưng không có hàm tên là ARRAYFORMULA.
2. Có thể dùng ARRAYFORMULA để làm gì mà các hàm thông thường không làm được?
Hàm ARRAYFORMULA không chỉ đơn thuần là thay thế việc kéo công thức. Nó cho phép bạn:
- Thực hiện các phép tính trên toàn bộ cột/phạm vi chỉ với một công thức duy nhất.
- Tạo ra các báo cáo động, danh sách duy nhất (với UNIQUE), và lọc dữ liệu tự động (với FILTER) mà không cần sử dụng Pivot Table hay bộ lọc thủ công.
- Tra cứu hàng loạt giá trị bằng một VLOOKUP/XLOOKUP duy nhất, tiết kiệm thời gian và tài nguyên bảng tính.
- Kết hợp các hàm phức tạp để xử lý mảng dữ liệu theo cách linh hoạt hơn.
3. Làm sao để chuyển đổi nhanh một công thức thông thường thành ARRAYFORMULA?
Trong Google Sheets, bạn có thể dễ dàng chuyển đổi một công thức thông thường (ví dụ: =B2*C2) thành ARRAYFORMULA bằng cách:
- Nhập công thức thông thường vào ô đầu tiên (ví dụ: D2).
- Sau khi nhập xong, nhấn tổ hợp phím Ctrl + Shift + Enter (trên Windows) hoặc Cmd + Shift + Enter (trên Mac). Google Sheets sẽ tự động thêm ARRAYFORMULA() và điều chỉnh phạm vi (ví dụ: B2:B*C2:C).
4. Tại sao ARRAYFORMULA của tôi lại báo lỗi #REF! hoặc không mở rộng?
Lỗi này thường xảy ra khi phạm vi đầu ra của ARRAYFORMULA bị chiếm chỗ bởi dữ liệu khác. Nghĩa là, các ô bên dưới hoặc bên cạnh nơi công thức ARRAYFORMULA cần điền kết quả đã có chứa giá trị (có thể do bạn nhập thủ công, hoặc một công thức khác). Cách khắc phục: Đảm bảo rằng toàn bộ phạm vi mà ARRAYFORMULA cần để hiển thị kết quả là trống hoàn toàn. Đơn giản là xóa dữ liệu trong các ô đó.
5. ARRAYFORMULA có làm chậm bảng tính của tôi không?
Trong hầu hết các trường hợp, ARRAYFORMULA thường giúp bảng tính của bạn hoạt động hiệu quả và nhanh hơn so với việc có hàng trăm, hàng ngàn công thức đơn lẻ được kéo xuống. Bởi vì Sheets chỉ cần tính toán một công thức duy nhất, thay vì rất nhiều công thức riêng biệt.
Tuy nhiên, nếu bạn sử dụng ARRAYFORMULA với những công thức cực kỳ phức tạp, lồng ghép quá nhiều hàm, hoặc áp dụng trên tập dữ liệu khổng lồ (hàng trăm ngàn dòng hoặc hơn) và tham chiếu chéo nhiều sheet, thì nó có thể ảnh hưởng đến hiệu suất. Trong những trường hợp đó, bạn có thể cần xem xét tối ưu hóa công thức hoặc sử dụng Google Apps Script.
6. Tôi có thể sử dụng ARRAYFORMULA với các hàm SUM, AVERAGE, COUNT không?
Hàm ARRAYFORMULA không làm cho các hàm tổng hợp như SUM(), AVERAGE(), COUNT() tự động mở rộng kết quả ra nhiều ô (vì bản chất chúng chỉ trả về một giá trị tổng hợp). Tuy nhiên, bạn hoàn toàn có thể sử dụng các hàm này bên trong một ARRAYFORMULA để tính toán trên một mảng tạm thời được tạo ra bởi các hàm khác.
>>> Xem thêm: Hướng dẫn sử dụng hàm đếm có điều kiện trong Google Sheet
Lời kết
Với những kiến thức và ví dụ trên, hy vọng bạn đã thấy rõ được sức mạnh “ma thuật” của hàm ARRAYFORMULA. Nó không chỉ là một công cụ giúp bạn thực hiện các phép tính tự động mà còn là một phương pháp tư duy mới trong việc quản lý và phân tích dữ liệu trên Google Sheets.
Bạn còn câu hỏi nào về hàm ARRAYFORMULA hay muốn khám phá thêm những ứng dụng đặc biệt nào khác không? Hãy chia sẻ trong phần bình luận bên dưới hoặc liên hệ ngay đến HVN Group qua địa chỉ:
- Fanpage: HVN Group
- Hotline: 024.9999.7777
