Google sheet chọn toàn bộ cột trong công thức

Bạn đang làm việc bên trong Bảng tính Google nơi 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 tự động thêm công thức khi một hàng mới được thêm vào Google Trang tính

Có một số cách để giải quyết vấn đề này

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

Cách đơn giản nhất để sao chép công thức là sử dụng núm điều khiển điền trong Google Trang tính. 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 núm điều khiển đ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 vào 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 qua các ô nhưng không có bất kỳ định dạng nào, hãy chọn ô chứa định dạng đó rồi nhấn Ctrl+C để sao chép vào bảng tạm. Tiếp theo, chọn vùng cần áp dụng công thức, nhấp chuột phải, chọn Paste Special và Paste Formula only

Áp dụng Công thức cho Toàn bộ Cột trong Google Trang tính

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ể, thì có một giải pháp hiệu quả hơn là sao chép-dán - 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 tôi 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à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 Trang tính sẽ tự động bao quanh công thức của bạn bằng hàm ARRAYFORMULA

Như vậy, chúng ta 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ý hàng loạt 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 một IF chứa vào ARRAYFORMULA của chúng ta để nó không áp dụng công thức cho bất kỳ hàng trống nào

Bảng tính Google cung cấp hai chức năng để giúp kiểm tra xem một ô có 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, ngược lại là FALSE

Do đó, Công thức mảng đã sửa đổi của chúng tôi sẽ đọc

Sử dụng ISBLANK(Tham chiếu ô)

Có một số cách khác để kiểm tra xem một ô có 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 Tiêu đề cột

Trong các ví dụ trước của chúng tôi, văn bản của tiêu đề cột (như Thuế, Tổng số tiền) đã được điền trước 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 thêm công thức của mình để có thể áp dụng chúng cho chính tiêu đề cột. Nếu chỉ mục 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 nếu không nó sẽ 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 các lần gửi biểu mẫu của Google

Các hàm ARRAYFORMULA đặc biệt hữu ích cho Google Biểu mẫu khi các phản hồi của biểu mẫu được lưu trong Google Trang tính. Bạn không thể thực hiện các phép tính trực tiếp bên trong Google Biểu mẫu 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 Trang tính 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 sao chép-dán nội dung

Cũng thấy. Chuyển đổi phản hồi biểu mẫu của Google sang tài liệu PDF

Cách sử dụng 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ó trang tính "Trái cây" liệt kê tên trái cây ở cột A và giá tương ứng ở cột B. Sheet thứ hai “Đơn hàng” có tên trái cây ở cột A, số lượng ở cột B và bạn phải tính số lượng đơn hàng ở 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, "")))

Trong tiếng Anh đơn giản, 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 VLOOKUP để lấy giá của mặt hàng từ trang Trái cây. Sau đó nhân giá đó với số lượng trong ô B và xuất 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 Trang tính Google khác

Xin lưu ý rằng bạn có thể phải sử dụng dấu chấm phẩy trong công thức bảng tính thay vì dấu phẩy đối với một số ngôn ngữ

Google Forms Các công thức và hàm của Google Sheets

Chia sẻWhatsAppTwitterFacebook

Amit Agarwal

Chuyên gia phát triển của Google, Nhà vô địch Google Cloud

Amit Agarwal là Chuyên gia nhà phát triển của Google về Google Workspace và Google Apps Script. Ông có bằng kỹ sư về Khoa học Máy tính (I. I. T. ) và là blogger chuyên nghiệp đầu tiên ở Ấn Độ

Amit đã phát triển một số tiện ích bổ sung phổ biến của Google bao gồm Mail Merge cho Gmail và Document Studio. Đọc thêm về Lifehacker và YourStory