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))))

Tự động điền công thức vào Google Form

Các hàm ARRAYFORMULA đặc biệt hữu ích cho Google form khi bạn lưu các phản hồi b trong Google Sheets. Bạn không thể thực hiện các phép tính trực tiếp bên trong Google Form nhưng chúng có thể được thực hiện bên trong bảng tính đang thu thập phản hồi.

Bạn có thể tạo các cột mới bên trong Bảng tính Google và áp dụng ARRAYFORMULA cho hàng đầu tiên của các cột đã thêm.

Khi nhận được biểu mẫu mới gửi, một hàng mới sẽ được thêm vào Google Sheets và các công thức sẽ được sao chép và tự động áp dụng cho các hàng mới mà bạn không cần phải copy và paste nội dung.

Cách sử dụng hàm VLOOKUP bên trong ARRAYFORMULA

Bạn có thể kết hợp ARRAYFORMULA với VLOOKUP để nhanh chóng thực hiện tra cứu trên toàn bộ cột.

Giả sử bạn có tờ “Fruits” liệt kê tên trái cây trong cột A và giá tương ứng trong cột B. Bảng thứ hai “Orders” có tên trái cây trong cột A, số lượng trong cột B và bạn phải tính toán đơn đặt hàng số tiền trong cột C.

=ArrayFormula( IF(ROW(A:A)=1, "Total", IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, "")))

Công thức này nghĩa là, nếu hàng của ô hiện tại là 1, hãy xuất tiêu đề cột ở dạng văn bản thuần túy. Nếu hàng lớn hơn 1 và cột A của hàng hiện tại không trống, hãy thực hiện hàm VLOOKUP để lấy giá của mặt hàng từ trang Fruits. Sau đó nhân giá đó với số lượng trong ô B và xuất ra giá trị trong ô C. Nếu phạm vi VLOOKUP của bạn nằm trong Bảng tính Google khác, hãy sử dụng hàm IMPORTRANGE() với ID của Google sheets khác.

Hy vọng qua bài viết này, bạn đã biết cách sử dụng hàm ARRAYFORMULA trong Google Sheets và ứng dụng nó để sao chép công thức trong Google Sheets và Google Form. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.