Cách sử dụng hàm ARRAYFORMULA sao chép công thức trong Google sheets

Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng hàm ARRAYFORMULA trong Google Sheets để nhanh chóng áp dụng công thức cho toàn bộ cột trong bảng tính. Công thức cũng được tự động thêm vào các hàng mới.

Bạn đang làm việc bên trong Bảng tính Google, nơi một công thức cần được sao chép xuống hàng cuối cùng của trang tính. Bạn cũng cần công thức được thêm tự động khi một hàng mới được thêm vào Google Sheets. Có một số cách đơn giản để giải quyết vấn đề này.

Sao chép công thức xuống trong Google Sheets

Cách dễ nhất để sao chép công thức là sử dụng chốt điền trong Google Sheets. Viết công thức của bạn vào hàng đầu tiên của bảng tính, sau đó trỏ chuột vào góc dưới bên phải của ô công thức.

Con trỏ thay đổi thành một chốt điền (biểu tượng dấu cộng màu đen) mà bạn có thể kéo đến hàng cuối cùng của trang tính. Ô điều khiển điền sẽ không chỉ sao chép công thức sang tất cả các ô liền kề mà còn sao chép định dạng trực quan.

Nếu bạn cần sao chép công thức giữa các ô nhưng không có bất kỳ định dạng nào, hãy chọn ô chứa định dạng và nhấn Ctrl + C để sao chép nó vào khay nhớ tạm. Tiếp theo, chọn phạm vi mà công thức đó cần áp dụng, nhấp chuột phải, chọn Paste Special và Paste Formula.

Áp dụng công thức cho toàn bộ cột trong Google sheets

Nếu bạn có hàng trăm hàng trong Bảng tính Google và bạn muốn áp dụng cùng một công thức cho tất cả các hàng của một cột cụ thể, có một giải pháp hiệu quả hơn là sao chép-dán thủ công, bạn có thể sử dụng  Công thức mảng.

Đánh dấu ô đầu tiên trong cột và nhập công thức như trước đó. Tuy nhiên, thay vì chỉ định một ô duy nhất làm tham số, chúng ta sẽ chỉ định toàn bộ cột bằng cách sử dụng ký hiệu B2:B (bắt đầu từ ô B2 và đi xuống hết hàng cuối cùng của cột B).

Sau đó nhấn Ctrl + Shift + Enter hoặc Cmd + Shift + Enter trên máy Mac và Google sheets sẽ tự động bao quanh công thức của bạn bằng hàm ARRAYFORMULA .

Do đó, chúng tôi có thể áp dụng công thức cho toàn bộ cột của bảng tính chỉ với một ô duy nhất. Công thức Mảng hiệu quả hơn khi chúng xử lý một loạt các hàng trong một lần. Chúng cũng dễ bảo trì hơn vì bạn chỉ cần sửa đổi một ô duy nhất để chỉnh sửa công thức.

Một vấn đề mà bạn có thể nhận thấy với các công thức trên là nó áp dụng cho mọi hàng trong cột mà bạn chỉ muốn thêm công thức vào các hàng chứa dữ liệu và bỏ qua các hàng trống.

Điều này có thể được thực hiện bằng cách thêm hàm IF vào ARRAYFORMULA của chúng ta để không áp dụng công thức cho bất kỳ hàng trống nào.

Google Spreadsheet cung cấp hai hàm để giúp kiểm tra xem một ô trống có đang trống hay không

• ISBLANK(A1) - Trả về TRUE nếu ô được tham chiếu trống.
• LEN(A1) <> 0 - Trả về TRUE nếu ô được tham chiếu không trống, nếu không trả về FALSE

Do đó, các Công thức Mảng sau khi được sửa đổi sẽ có dạng như sau nếu bạn sử dụng ISBLANK (Tham chiếu ô):

Có một số cách thú vị khác để kiểm tra xem một ô trống hay không:

=ArrayFormula(IF(ISBLANK(B2:B), "", ROUND(B2:B*18%, 2)))

=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), ""))

=ArrayFormula(IF(B2:B="", "", ROUND(B2:B*18%, 2)))

Sử dụng công thức mảng bên trong cột tiêu đề

Trong các ví dụ bên trên, văn bản của tiêu đề cột (như Tax, Total Amount) đã được điền sẵn và các công thức chỉ được thêm vào hàng đầu tiên của tập dữ liệu.

Chúng ta có thể cải thiện hơn nữa công thức của mình để chúng có thể được áp dụng cho chính tiêu đề cột. Nếu chỉ số của hàng hiện tại là 1, được tính bằng hàm ROW (), công thức sẽ xuất ra tiêu đề cột khác để thực hiện phép tính bằng công thức.

=ArrayFormula (IF (ROW(B:B)=1, "Tax", IF(ISBLANK(B:B),"", ROUND(B:B*18%, 2))))