Ứng dụng Google Trang tính sao chép công thức xuống

Có vẻ như hoạt động nếu bạn kéo hộp lựa chọn đến nơi bạn muốn, chạm vào một nơi nào đó trong hộp, sau đó chọn "Tự động điền" trong phần "Cắt. Sao chép. Dán vv" cửa sổ bật lên

Trong bài đăng trên blog này, chúng tôi sẽ chỉ cho bạn tất cả các cách khác nhau để làm điều đó. Chúng tôi cũng sẽ giải thích lợi ích của từng phương pháp và thời điểm bạn có thể muốn sử dụng chúng

Google Trang tính là một công cụ năng suất tuyệt vời cung cấp nhiều cách khác nhau để xử lý dữ liệu bạn có. Điều này bao gồm việc tạo các phép tính mới với các công thức

Một việc mà bạn sẽ làm thường xuyên nhất là sao chép hoặc điền công thức xuống các hàng trong Google Trang tính. Điều này là do cấu trúc dạng bảng của dữ liệu của bạn trong đó mỗi cột chứa nhiều hàng khác nhau và bạn cần áp dụng một công thức cho từng hàng

Sao chép công thức xuống các hàng hoặc điền công thức xuống là một nhiệm vụ bạn có thể thực hiện trong vài giây, miễn là bạn biết kỹ thuật phù hợp

Đến cuối bài đăng này, bạn sẽ có thể điền vào các công thức một cách dễ dàng. Bạn đang chờ đợi điều gì?

Điền xuống một công thức với Sao chép và Dán

Phương pháp thô sơ nhất để điền công thức là sao chép và dán nó vào các ô

Bạn không cần phải thành thạo các phím tắt hoặc bất cứ thứ gì tương tự khi làm việc với phương pháp này. Nó chỉ là một bản sao và dán đơn giản

Giả sử chúng ta có dữ liệu như trong ảnh chụp màn hình ở trên, trong đó cột A bao gồm Số tiền bán hàng bằng USD

Trong cột B, bạn muốn áp dụng công thức tính Lợi nhuận, đây là phép tính đơn giản 5% của Số tiền bán hàng

Trong ô B2, nhập công thức

= QUERY ( A1:A11, "SELECT A*0.05" )

= QUERY ( A1:A11, "SELECT A*0.05" )

Cả hai công thức này đều tạo ra cùng một kết quả và tạo ra 5% Số tiền bán hàng trong ô B2

Sao chép ô B2 bằng Ctrl + C. Thao tác này sẽ sao chép công thức trong ô

Chọn toàn bộ phạm vi từ ô B3 đến B11 và thông qua bàn phím của bạn, nhấn Ctrl + V để dán nó trên các hàng

Công thức sẽ thực hiện công việc của nó và nắm bắt 5% doanh thu cho mỗi giá trị bán hàng có trong cột A

Miếng bánh?

Bạn có thể sử dụng phương pháp này để điền công thức xuống các hàng và tạo kết quả

Điền xuống một công thức bằng cách nhấp và kéo

Nếu bạn đang làm việc trên một tập dữ liệu tương đối nhỏ hơn, chẳng hạn như vài trăm hàng hoặc ít hơn, thì sử dụng phương pháp Nhấp và Kéo là cách tốt nhất để điền công thức xuống các hàng

Điều này cho phép bạn tiết kiệm thời gian vì bạn không cần phải sao chép và dán các giá trị xuống các hàng

Khi sử dụng tính năng này trong Google Trang tính, tính năng này sẽ nhận ra mẫu trên các hàng. Sau đó, nó thực hiện điều này trên các hàng còn lại để tạo danh sách có cùng công thức

Để thực hiện phương pháp này, bấm chọn ô B2 chứa công thức. Hãy chú ý đến hình chữ nhật màu xanh xuất hiện ngay khi bạn chọn ô. Đây là tay cầm điền

Di con trỏ chuột lên hình chữ nhật liền màu xanh nằm ở ngoài cùng bên phải của ô đã chọn và bạn sẽ thấy biểu tượng dấu cộng xuất hiện cùng với núm điều khiển điền

Khi bạn thấy biểu tượng dấu cộng, hãy nhấp và giữ phím bên trái của chuột hoặc bàn di chuột và kéo nó xuống cho đến ô B11

Google Trang tính xác định rằng bạn đang cố gắng điền công thức vào các ô và điền công thức đó vào các ô

Bạn sẽ thấy rằng công thức được điền vào các hàng và thu được lợi nhuận liên quan đến từng giá trị bán hàng

Điền xuống một công thức với Double Click

Một cách khác nhanh hơn việc kéo ô công thức xuống giữa các ô là sử dụng phím chuột trái hoặc bàn di chuột cùng với nhấp đúp vào núm điều khiển điền của ô công thức đã chọn

Phương pháp này thuận tiện hơn vì nó cho phép bạn sao chép công thức xuống ngay cả đối với một trang tính có hàng nghìn hàng. Bạn không cần phải lo lắng về hàng cuối cùng của công thức ở đâu

Google Trang tính đủ thông minh để kiểm tra hàng đang hoạt động cuối cùng và điền công thức cho đến thời điểm đó

Công thức ở ô B2 thu được lợi nhuận là 5% giá trị bán hàng từ ô A2

Chọn ô và núm điều khiển điền sẽ xuất hiện

Di chuột qua núm điều khiển điền và sau đó nhấp đúp vào phím chuột trái

Bạn sẽ thấy rằng công thức được điền trên các hàng trong cột B cho mỗi hàng trong cột A cho đến hàng làm việc cuối cùng bên cạnh cột A

Tiện ích này giúp bạn tiết kiệm thời gian vì nó tự động phát hiện hàng làm việc cuối cùng

Điền xuống một công thức với Tự động điền

Vào năm 2020, Google Trang tính đã thêm công cụ Tự động điền cho phép bạn tự động hoàn thành danh sách hoặc điền vào công thức. Đây là một trong những tính năng tốt nhất

Bạn có thể sử dụng điều này để hoàn thành một danh sách bằng cách phát hiện các mẫu. Nó cũng tự động điền công thức xuống các hàng và cung cấp cho bạn các đề xuất công thức thông minh

Nếu bạn muốn khám phá thêm về Tự động điền thì bạn có thể đọc bài đăng này về tất cả các cách bạn có thể sử dụng Smart Fill

Nhập công thức

= QUERY ( A1:A11, "SELECT A*0.05" )

Ngay khi bạn nhấn nút Enter để thực hiện công thức, cửa sổ Tự động điền sẽ bật lên

Nhấp vào nút đánh dấu để chấp nhận đề xuất. Bạn cũng có thể sử dụng phím tắt Ctrl + Enter để Tự động điền công thức xuống tất cả các hàng

Điền công thức với hàm ARRAYFORMULA

Có rất nhiều hàm bảng tính rất hữu ích mà bạn sẽ chỉ tìm thấy trong Google Trang tính và ARRAYFORMULA nên ở đầu danh sách này vì nó bổ sung tiện ích cho nhiều hàm khác

Bất cứ khi nào bạn gặp tình huống cần điền công thức trên các hàng (dưới dạng một mảng), thì hàm ARRAYFORMULA là cách để đạt được điều này

ARRAYFORMULA cho phép bạn chuyển đổi các công thức dẫn đến một giá trị ô thành một mảng các giá trị

= ARRAYFORMULA ( A2:A11 * 0.05 )

Chọn ô B2 và chèn công thức trên. Điều này sẽ xuất phép tính cho từng hàng trong dữ liệu của bạn

💡 Mẹo. Có một phím tắt sẽ tự động bọc công thức của bạn bằng hàm ARRAYFORMULA khi ở chế độ chỉnh sửa. Nhấn Ctrl + Shift + Enter trên bàn phím để chuyển đổi công thức thành CÔNG THỨC Mảng

Điền xuống một công thức với chức năng QUERY

Hàm QUERY rất mạnh do khả năng hoạt động với các mảng dưới dạng cơ sở dữ liệu giống như SQL

Nó cho phép bạn thao tác và biến đổi dữ liệu theo hầu hết mọi cách có thể tưởng tượng được. Hơn nữa, nó có thể trả về các giá trị mảng cho các phép tính theo hàng

Nếu bạn muốn biết thêm về chức năng này, hãy xem hướng dẫn chi tiết này về chức năng QUERY

Bạn có thể sử dụng hàm QUERY mạnh mẽ để điền phép tính xuống các hàng trong Google Trang tính

= QUERY ( A1:A11, "SELECT A*0.05" )

Chèn hàm QUERY ở trên với phạm vi A1. A11 làm đối số dữ liệu. Không cần sửa tham chiếu phạm vi khi làm việc với hàm QUERY vì công thức duy nhất sẽ trả về tất cả các kết quả

Trong đối số truy vấn, bạn có thể viết truy vấn

= QUERY ( A1:A11, "SELECT A*0.05" )

= QUERY ( A1:A11, "SELECT A*0.05" )

Công thức thực hiện phép tính cho từng hàng và trả về kết quả trong một mảng

Apps Script để tự động điền công thức

Apps Scripts cho phép bạn viết mã JavaScript để tự động hóa các hoạt động hàng ngày của bạn trong Google Trang tính

Điều thú vị là bạn có thể sử dụng Apps Script để tự động điền công thức xuống các hàng trong Google Trang tính

Nếu bạn muốn biết thêm, hãy xem hướng dẫn đầy đủ này để bắt đầu với Apps Scripts trong Google Trang tính

Chuyển đến menu Tiện ích mở rộng và chọn tùy chọn Apps Script để mở trình chỉnh sửa Apps Script trong Google Trang tính

function fillDown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Example 7");
  ss.getRange("B2").setFormula("=A2*0.05");
  var lastRow = ss.getLastRow();
  var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);
  ss.getRange("B2"). copyTo(fillDownFormulaRange);
}

Sao chép và dán mã script trên vào giao diện

Giải trình

Đoạn mã này sẽ chèn một công thức vào ô B2 và sau đó sao chép nó xuống B11

• = QUERY ( A1:A11, "SELECT A*0.05" )

  9 là tên được đặt cho mã này bằng cách sử dụng từ khóa

  function fillDown() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Example 7");
    ss.getRange("B2").setFormula("=A2*0.05");
    var lastRow = ss.getLastRow();
    var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);
    ss.getRange("B2"). copyTo(fillDownFormulaRange);
  }

  0. Bạn có thể đặt tên cho nó bất cứ điều gì bạn muốn, nhưng có một cái tên mô tả luôn là tốt nhất
• Phương thức

  = QUERY ( A1:A11, "SELECT A*0.05" )

  0 cho phép bạn truy cập trang tính có tên Ví dụ 7. Điều này được lưu trữ trong một biến có tên là

  = QUERY ( A1:A11, "SELECT A*0.05" )

  1
• Phương thức

  = QUERY ( A1:A11, "SELECT A*0.05" )

  2 gọi trên

  = QUERY ( A1:A11, "SELECT A*0.05" )

  1 và cho phép bạn truy cập ô B2. Sau đó, mệnh đề

  = QUERY ( A1:A11, "SELECT A*0.05" )

  4 áp dụng công thức

  = QUERY ( A1:A11, "SELECT A*0.05" )

  4 trong ô đó
• Phương thức

  = QUERY ( A1:A11, "SELECT A*0.05" )

  6 gọi đến

  = QUERY ( A1:A11, "SELECT A*0.05" )

  1 và cung cấp cho bạn hàng làm việc cuối cùng từ trang tính của bạn. Các kết quả được lưu trữ trong một biến có tên là

  = QUERY ( A1:A11, "SELECT A*0.05" )

  8
• Phương pháp

  = QUERY ( A1:A11, "SELECT A*0.05" )

  2 được sử dụng để nắm bắt phạm vi B2. B11 trong một biến có tên

  function fillDown() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Example 7");
    ss.getRange("B2").setFormula("=A2*0.05");
    var lastRow = ss.getLastRow();
    var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);
    ss.getRange("B2"). copyTo(fillDownFormulaRange);
  }

  0
• Sau đó, mệnh đề

  function fillDown() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Example 7");
    ss.getRange("B2").setFormula("=A2*0.05");
    var lastRow = ss.getLastRow();
    var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);
    ss.getRange("B2"). copyTo(fillDownFormulaRange);
  }

  1 sẽ sao chép công thức được lưu trữ trong ô B2 xuống phạm vi B2. B11

Sau khi bạn sao chép và dán mã, hãy sử dụng nút Chạy từ dải băng bên trong Apps Script để chạy tập lệnh này

Nhật ký thực thi sẽ cho bạn biết tiến trình của mã này đang chạy. Sau khi hoàn thành, bạn sẽ thấy công thức được điền trên các hàng trong cột B

💡 Mẹo. Bạn có thể sử dụng Apps Scripts để xây dựng các hàm tùy chỉnh của riêng mình, chẳng hạn như hàm tổng đang chạy tự động điền kết quả này

kết luận

Có nhiều cách để điền vào các phép tính công thức của bạn trong Google Trang tính

Một bản sao và dán đơn giản là cách dễ nhất và sẽ hoàn thành công việc

Các phương pháp nhấp và kéo hoặc nhấp đúp cũng có thể được sử dụng để sao chép và dán công thức xuống các hàng nhanh hơn

Tự động điền có thể nhận dạng công thức và hàng làm việc cuối cùng để tự động điền công thức trên các hàng đó. Phương pháp này linh hoạt và nhanh chóng. Nó cũng hiển thị cho bạn bản xem trước và cho phép bạn chấp nhận hoặc từ chối các đề xuất

Hàm ARRAYFORMULA cho phép bạn chuyển đổi các công thức giá trị đơn lẻ thành các mảng để thực hiện các phép tính trên cơ sở từng hàng. Bạn có thể tham chiếu tất cả các hàng cùng một lúc mà công thức sẽ được áp dụng và sẽ tự động điền kết quả vào mỗi hàng

Hàm QUERY cũng hữu ích trong việc điền kết quả tính toán trên các hàng bằng cách sử dụng truy vấn SQL để đạt được kết quả này

Cuối cùng, bạn có thể viết mã của riêng mình, mã này hoạt động linh hoạt để điền công thức xuống các hàng chỉ bằng một lần bấm nút

Phương pháp nào bạn sử dụng để điền vào công thức của bạn?