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 đó
________số 8Bằ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