Cách chèn công thức vào Google Sheets cho cả cột

Bạn có thể kéo thanh điều khiển điền xuống để tự động điền công thức. Tuy nhiên, sẽ không hiệu quả khi sử dụng núm điều khiển điền khi bạn đang làm việc với một tập dữ liệu lớn. Ngoài ra, bạn cần kéo thanh điều khiển điền xuống mỗi khi bạn thêm dữ liệu mới (hàng) vào tập dữ liệu của mình. Bài đăng này sẽ chỉ cho bạn cách áp dụng công thức cho toàn bộ cột trong Google Sheets mà không cần kéo. Nó sẽ giúp bạn tự động hóa một số tác vụ bằng Google Trang tính

Ghi chú. Blog của chúng tôi có một số bài đăng về cách tạo biểu mẫu nhập dữ liệu bằng Google Trang tính làm cơ sở dữ liệu. Bạn có thể sử dụng phương pháp được mô tả trong bài đăng này để thêm các trường được tính toán vào Google Trang tính của mình

Mục lục

CÔNG THỨC Mảng Google Trang tính

Trong bài đăng này, về cơ bản, chúng tôi tập trung vào Google Sheets ARRAYFORMULA. Theo định nghĩa, ARRAYFORMULA,

“Cho phép hiển thị các giá trị được trả về từ một công thức mảng thành nhiều hàng và/hoặc cột và sử dụng các hàm không thuộc mảng với mảng”

cú pháp

ARRAYFORMULA(công thức_mảng)

array_formula – Một dải ô, biểu thức toán học sử dụng một dải ô hoặc nhiều dải ô có cùng kích thước hoặc một hàm trả về kết quả lớn hơn một ô

Bạn có thể sử dụng ARRAYFORMULA này để mở rộng các hàm của mình sang các ô liền kề, trong trường hợp này là để tự động điền vào cột

Ghi chú. Không phải tất cả các công thức Google Trang tính đều hoạt động với ARRAYFORMULA theo cách này

Cách áp dụng công thức cho toàn bộ cột trong Google Sheets bằng ARRAYFORMULA

Trước tiên, hãy xem các ví dụ về cách mảng mở rộng sang các ô khác trong Google Trang tính

Sau đây là một số ví dụ về mảng mà bạn có thể chèn dưới dạng công thức trong Google Trang tính

Một mảng được mở rộng dọc theo cùng một hàng

={1,2,3,4,5,6}

Một mảng được mở rộng đến các ô bên dưới

={1;2;3;4;5;6}

Một mảng được mở rộng thành các hàng và cột liền kề

={1,2,3,4;2,3,4,5;3,4,5,6;4,5,6,7}

Bạn có thể sao chép và dán các công thức trên vào Google Trang tính của mình để xem cách chúng được mở rộng sang các ô liền kề

Hãy viết một công thức đơn giản bên trong ARRAYFORMULA để áp dụng nó cho toàn bộ phạm vi dữ liệu

Công thức sau đây nhân giá trị trong ô A2 và Ô B2. Bạn cần kéo phương trình này xuống tận hàng cuối cùng để áp dụng công thức cho toàn bộ phạm vi dữ liệu

=A2*B2

Khi sử dụng ARRAYFORKULA, bạn có thể viết công thức này như hình bên dưới. Lưu ý sự khác biệt;

=ARRAYFORMULA(A2:A12*B2:B12)

Bạn chỉ cần thêm công thức này vào ô trên cùng của cột. Nó sẽ tự động mở rộng ra toàn bộ phạm vi dữ liệu (đến hàng 12)

Áp dụng công thức cho toàn bộ cột bằng ARRAYFORMULA – Tự động tính toán khi bạn thêm dữ liệu mới

Trong ví dụ trên, công thức được áp dụng cho hàng cuối cùng của tập dữ liệu của bạn. Để áp dụng nó cho toàn bộ cột, bạn cần xóa tham chiếu cuối của phạm vi ô. Ví dụ, sau đây sẽ áp dụng công thức cho toàn bộ cột

=ARRAYFORMULA(A2:A*B2:B)

Xem video giới thiệu bên dưới

Ngừng thực hiện ARRAYFORMULA trong các hàng trống

Ở bước trên, chúng tôi đã xóa tham chiếu cuối để áp dụng ARRAYFORMULA cho toàn bộ cột. Tuy nhiên, không dừng ở hàng cuối cùng trong phạm vi dữ liệu của bạn. Nó thực hiện phép tính trong các hàng trống và hiển thị kết quả

Bạn có thể tránh điều này bằng cách chỉ chạy ARRAYFORMULA cho hàng có dữ liệu. Để thực hiện việc này, bạn cần xác định động các hàng có dữ liệu. Sau đó, bạn có thể chứa điều kiện IF bên trong ARRAYFORMULA để chạy công thức nếu có sẵn dữ liệu

Bạn có thể sử dụng ví dụ sau để ngừng chạy phép tính trên các hàng trống

Sử dụng chức năng ISBLANK để kiểm tra các hàng trống

Hàm ISBLANK kiểm tra xem ô được tham chiếu có trống không. Nó trả về TRUE nếu giá trị ô trống hoặc tham chiếu đến ô trống và FALSE nếu ô chứa dữ liệu hoặc tham chiếu đến dữ liệu

Bạn có thể sửa đổi ARRAYFORMULA ở trên như bên dưới để ngăn việc thực hiện ở các hàng trống

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",A2:A*B2:B))

Sử dụng hàm LEN để kiểm tra các hàng trống

Hàm LEN trả về độ dài của một chuỗi. Nếu ô được tham chiếu trống, hàm LEN trả về “0”. Vì vậy, chúng ta có thể sử dụng hàm LEN với ARRAYFORMULA để ngừng áp dụng nó cho các hàng trống

Bạn có thể sửa đổi công thức trên như bên dưới để ngăn việc thực hiện ở các hàng trống

=ARRAYFORMULA(IF(LEN(A2:A)=0,"",A2:A*B2:B))

Vì vậy, cấu trúc của công thức để áp dụng công thức cho toàn bộ cột trong Google Sheets có thể được viết như dưới đây

Thêm ARRAYFORMULA vào hàng tiêu đề của bảng và tạo tiêu đề

Bạn có thể xóa các hàng trong bảng dữ liệu sử dụng công thức mảng như đã giải thích ở trên mà không gặp vấn đề gì trừ khi nó chứa công thức mảng. Nếu lỡ tay xóa ARRAYFORMULA thì ảnh hưởng đến cả cột và bỏ phép tính

Bạn có thể tránh điều này bằng cách thêm công thức mảng vào hàng tiêu đề của bảng dữ liệu. Bạn cũng có thể tùy chỉnh công thức để hiển thị tiêu đề của cột đó trong cùng một ô mà bạn đã nhập công thức mảng

Bạn có thể sửa đổi công thức như bên dưới bằng cách sử dụng phương pháp dấu ngoặc nhọn mà tôi đã giải thích trước đó

Bằng cách sử dụng dấu chấm phẩy giữa các phần tử mảng, bạn có thể hiển thị các phần tử trong một cột. Trong công thức trên, phần tử đầu tiên (tiêu đề của cột) được viết trong ô chứa công thức. Và phần tử thứ hai ARRYAFORMULa được viết trong ô bên dưới như chúng ta muốn

kết thúc

Trong bài đăng này, bạn đã học cách áp dụng công thức cho toàn bộ cột trong Google Trang tính bằng ARRAYFORMULA. Khi bạn bọc công thức bằng ARRAYFORMULA, nó sẽ tự động được mở rộng thành phạm vi bạn đã chỉ định. Với ARRAYFORMULA, bạn chỉ cần nhập công thức vào hàng đầu tiên của tập dữ liệu. Bạn cũng có thể tùy chỉnh công thức này để tự động mở rộng sang các hàng mới khi bạn thêm nhiều dữ liệu hơn