Cách áp dụng công thức cho cả cột trong Google Sheet

Cách áp dụng công thức cho cả cột trong Google Sheet
Tải về bản PDF Tải về bản PDF

wikiHow hôm nay sẽ hướng dẫn bạn cách áp dụng công tức cho toàn bộ cột bằng website Google Sheets (Google Trang tính) phiên bản đầy đủ trên máy tính để bàn.

  1. 1

    Truy cập https://sheets.google.com bằng trình duyệt web. Nếu bạn đã đăng nhập Google thì danh sách tất cả tài liệu được liên kết với tài khoản sẽ hiện ra.

    • Đăng nhập tài khoản Google nếu bạn chưa tự động đăng nhập.

  2. 2

    Mở tài liệu Google Sheet từ trong danh sách.

  3. 3

    Nhập công thức vào ô đầu tiên trong cột.

    • Nếu bạn có hàng tiêu đề thì đừng nhập công thức vào đó.

  4. 4

    Nhấp vào một ô để chọn.

  5. 5

    Kéo tay cầm của ô về cuối dữ liệu trong cột. Nhấp vào ô vuông nhỏ màu xanh ở dưới cùng bên phải ô và kéo xuống qua toàn bộ ô mà bạn muốn áp dụng công thức. Khi bạn thả chuột, công thức từ ô đầu tiên sẽ được sao chép vào từng ô trong vùng chọn.

  6. 6

    Sử dụng tổ hợp phím tắt. Nếu cột quá dài để kéo hay bạn muốn áp dụng công thức cho toàn bộ cột trong trang tính, hãy:

    • Nhấp vào ô chứa công thức.
    • Nhấp vào chữ cái đầu cột.
    • Nhấn Ctrl+D (Windows) hoặc Command+D (Mac).

    Quảng cáo

Người dùng Google Trang tính thường cần áp dụng công thức (hoặc hàm) được nhập vào một ô bảng tính cho toàn bộ cột trong bảng. Ví dụ: bạn có thể cần thêm giá trị trên hai cột và 10 hàng trong cột bảng thứ ba. Trong trường hợp đó, bạn sẽ cần phải thêm hàm SUM vào 10 ô. Có nhiều cách khác nhau để bạn có thể nhanh chóng áp dụng các công thức cho toàn bộ các cột trong Trang tính mà không cần nhập chúng vào tất cả các ô.

Thêm công thức vào các cột trong bảng với nút điều khiển điền

Hầu hết các ứng dụng bảng tính đều có trình xử lý điền để bạn sao chép công thức ô trên các cột hoặc hàng bằng. Bạn có thể sử dụng bảng điều khiển điền của Trang tính bằng cách kéo ô của công thức trên một phạm vi để sao chép nó vào. Sau đó, các cột khác sẽ bao gồm cùng một hàm và tham chiếu ô cho các hàng trong bảng của chúng.

Mở bảng tính Trang tính trống trong trình duyệt của bạn. Để biết ví dụ về thao tác xử lý điền, hãy nhập '500' trong A1, '250' trong A2, '500' trong A3 và '1, 500' trong A4. Sau đó, nhập '500' vào ô B1, '1, 250' trong B2, '250' trong B3 và '500' một lần nữa trong B4 để bảng tính của bạn khớp với bảng tính trong ảnh chụp ngay bên dưới.

Cách áp dụng công thức cho cả cột trong Google Sheet

Bây giờ bạn có thể thêm một công thức vào cột C với nút điều khiển điền. Đầu tiên, chọn ô C1 trong bảng tính của bạn; và nhấp vào thanh fx. Nhập '= SUM (A1: B1)' vào thanh fx. C1 sẽ trả về giá trị 1.000 khi bạn nhấn Enter.

Để sao chép chức năng của C1 sang các hàng của bảng khác trong cột C với nút điều khiển điền, hãy chọn ô C1 và di chuyển con trỏ đến góc dưới cùng bên phải của nó. Nhấn và giữ nút chuột trái khi con trỏ biến thành hình chữ thập. Kéo con trỏ xuống ô C4 và thả chuột. Điều đó sẽ áp dụng hàm này cho ba hàng khác của cột C. Các ô sẽ thêm các giá trị được nhập vào các cột A và B như được hiển thị ngay bên dưới.

Cách áp dụng công thức cho cả cột trong Google Sheet

Hàm ArrayFormula

Công cụ xử lý fill của Sheets là tốt cho việc thêm công thức vào các cột bảng nhỏ hơn. Tuy nhiên, nếu bạn có một bảng lớn, có thể tốt hơn là áp dụng công thức cho toàn bộ cột bảng tính với hàm ARRAYFORMULA. Một số có thể tự hỏi có bao nhiêu hàng trong cột Trang tính. Bạn có thể cuộn xuống 1.000 hàng trong bảng tính Trang tính bằng thanh cuộn.

Cách áp dụng công thức cho cả cột trong Google Sheet

Mặc dù bạn có thể thêm nhiều hơn, 1.000 là số hàng mặc định trong Trang tính. Như vậy, 1.000 ô sẽ chiếm toàn bộ cột nếu bạn không sửa đổi giá trị mặc định. Bạn có thể nhanh chóng áp dụng công thức cho tất cả các hàng cột đó bằng hàm ARRAYFORMULA.

Thay thế hàm SUM trong cột C của bảng bằng công thức mảng. Đầu tiên, chọn phạm vi ô C1: C4 và nhấn phím Del để xóa SUM. Chọn ô C1 để bao gồm hàm. Nhập '= A1: A + B1: B' trong thanh fx và nhấn Ctrl + Shift + Enter để thêm ARRAYFORMULA vào công thức. Thanh fx sau đó sẽ bao gồm công thức mảng được hiển thị trong ảnh chụp ngay bên dưới.

Cách áp dụng công thức cho cả cột trong Google Sheet

Nhấn Enter sẽ sao chép công thức cho tất cả 1.000 hàng như trong ảnh ngay bên dưới. Tất cả 1.000 hàng trong cột C hiện thêm các giá trị được nhập vào cột A và B! Điều quan trọng cần lưu ý là bạn nên luôn nhấn Ctrl + Shift + Enter sau khi nhập hàm cơ bản trong thanh fx. Điều đó tự động chuyển đổi hàm thành công thức mảng.

Cách áp dụng công thức cho cả cột trong Google Sheet

Bạn cũng cần phải sửa đổi tham chiếu ô của hàm để công thức mảng hoạt động. Ô cột đầu tiên luôn được bao gồm trong tham chiếu. Tuy nhiên, nửa sau của tham chiếu thực sự là tiêu đề cột. Tham chiếu ô phải luôn giống như A1: A, B4: B, C3: C, v.v., tùy thuộc vào vị trí ô đầu tiên của bảng trong bảng tính.

Thêm công thức vào bảng bằng AutoSum

Power Tools là một add-on tuyệt vời cho Trang tính mở rộng ứng dụng web với các công cụ cho văn bản, dữ liệu, công thức, xóa nội dung ô và hơn thế nữa. AutoSum là một tùy chọn trong Power Tools, bạn có thể thêm các chức năng cho toàn bộ các cột với. Với điều đó, bạn có thể thêm các hàm SUM, COUNT, AVERAGE, MAX, PRODUCT, MODE, MIN và các hàm khác vào các cột đầy đủ.

Trước tiên, nhấn nút + Miễn phí trên trang web này để thêm Công cụ quyền lực vào Trang tính. Sau đó nhấp vào Tiện ích bổ sung trong Trang tính và chọn Công cụ quyền lực từ trình đơn. Nhấp vào Bắt đầu để mở thanh bên của tiện ích bổ sung. Chọn Công thức để mở các tùy chọn được hiển thị trong ảnh chụp màn hình ngay bên dưới.

Cách áp dụng công thức cho cả cột trong Google Sheet

Nhấp vào tiêu đề D trong bảng tính của bạn để chọn toàn bộ cột. Sau đó nhấp vào nút radio AutoSum trong thanh bên. Chọn SUM từ trình đơn thả xuống. Nhấn nút Run để thêm SUM vào cột D. Điều đó bổ sung hàm SUM vào tất cả 1.000 ô trong cột D như dưới đây.

Cách áp dụng công thức cho cả cột trong Google Sheet

Vì vậy, bạn có thể nhanh chóng thêm các chức năng vào tất cả các ô trong cột bảng của bạn trong Trang tính có tay cầm điền, ARRAYFORMULA và tùy chọn Tự động sửa trong Công cụ Power. Bảng điều khiển điền bảng thường thực hiện thủ thuật cho các bảng nhỏ hơn, nhưng ARRAYFORMULA và AutoSum thì tốt hơn cho việc áp dụng các chức năng trên toàn bộ các cột bảng tính. Hãy xem video YouTube này để biết thêm chi tiết về cách sao chép các chức năng bằng nút điều khiển điền và trang này bao gồm video về các công thức mảng.

Trong bài viết này, mình sẽ chia sẻ cách bạn sử dụng hàm ARRAYFORMULA trong Google Sheets để có thể nhanh chóng điền công thức tính toán cho cả một cột trong Google Sheets. Công thức sẽ tự động mở rộng khi có dữ liệu mới.

Cách áp dụng công thức cho cả cột trong Google Sheet

kéo công thức trong google sheet

Trong trường hợp bạn đang làm việc với Google Sheets và cần phải copy công thức cho tới dòng cuối cùng mỗi khi dữ liệu mới được thêm vào, ví dụ khi bạn sử dụng Google Form để thu thập dữ liệu, và với mỗi dòng dữ liệu mới, bạn cần copy công thức và áp dụng cho dữ liệu mới.

Có một vài cách để xử lý vấn đề này.

Cách dễ nhất để copy công thức xuống cho các ô bên dưới là sử dụng fill handle trong Google Sheets. Fill handle là ô vuông ở góc dưới bên tay phải khi bạn chọn một vùng dữ liệu, bạn có thể dùng chuột để nhấn giữ và kéo fill handle này để copy nội dung hoặc công thức cho các ô bên dưới. Fill handle không chỉ copy giá trị, công thức mà còn copy cả định dạng.

Cách áp dụng công thức cho cả cột trong Google Sheet
Copy công thức bằng fill handle

Nếu bạn muốn copy công thức và bỏ qua bất cứ định dạng dữ liệu nào, bạn có thể sử dụng lựa chọn Paste special khi bấm chuột phải vào một vùng như hình minh hoạ dưới đây

Cách áp dụng công thức cho cả cột trong Google Sheet
Copy công thức nhưng không copy định dạng

Trong trường hợp bạn đang xử lý một vài trăm dòng dữ liệu trong Google Sheets, mỗi lần có dữ liệu mới, chúng ta lại cần kéo lại công thức sử dụng Fill Handle, thì thật mất thời gian và không hiệu quả. Chúng ta có cách giải quyết vấn đề này.

Đầu tiên, bạn hãy chọn ô đầu tiên trong cột, sau đó vẫn nhập công thức cần thiết cho cột đó như bình thường. Tuy nhiên, thay vì chỉ gõ B2 như công thức ở phần trên, thì bạn hãy tham chiếu tới vùng B2:B (ý nghĩa: tham chiếu từ ô B2 tới ô cuối cùng của cột B). Sau khi đã hoàn thành công thức, hãy bấm tổ hợp phím Ctrl+Shift+Enter trên máy tính Windows hoặc Cmd+Shift+Enter nếu bạn đang sử dụng máy Mac, và Google Sheets sẽ tự động thêm công thức ARRAYFORMULA bao ra ngoài công thức của bạn.

Cách áp dụng công thức cho cả cột trong Google Sheet
Công thức mảng trong Google Sheets

Sau khi áp dụng công thức trên, bạn thấy rằng, chúng ta chỉ cần nhập một lần công thức vào ô đầu tiên của cột, cả cột sẽ được điền công thức tương tự. Công thức mảng này sẽ hiệu quả hơn vì chúng ta chỉ cần nhập vào một ô, khi cần thay đổi hay cập nhật, chúng ta cũng chỉ cần cập nhật một ô này.

Một vấn đề duy nhất mà chúng ta đang có hiện tại là: khi chúng ta áp dụng công thức như trên, toàn bộ cột đều được áp dụng công thức, những dòng không có dữ liệu cũng được áp dụng công thức.

Cách xử lý vấn đề này như sau: thêm một hàm IF vào công thức mảng ARRAYFORMULA của chúng ta để biết khi nào chúng ta cần điền công thức và khi nào không cần. Chúng ta sẽ chỉ điền công thức khi một ô trong cột khác không trống. Google Sheets có hai cách để kiểm tra một ô có rỗng hay không.

  • ISBLANK(A1) – trả về TRUE nếu ô A1 rỗng
  • LEN(A!) <> 0 – trả về TRUE nếu ô A1 không rỗng, FALSE nếu A1 rỗng

Do vậy, chúng ta có thể sửa công thức cho cột C như sau:

=ArrayFormula(IF(ISBLANK(B2:B), “”, ROUND(B2:B*18%, 2)))

Sử dụng kết hợp isblank với công thức mảng

Ngoài cách sử dụng ISBLANK như trên, chúng ta còn có cách khác như sau:

=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), “”))

Hoặc

=ArrayFormula(IF(B2:B=””, “”, ROUND(B2:B*18%, 2)))

Trong ví dụ trước của chúng ta, dòng tiêu đề (Product, Price, Tax, Amount) được điền trước, và công thức của chúng ta bắt đầu từ dòng thứ hai.

Chúng ta có thể làm tốt hơn công thức của chúng ta như sau: nếu công thức đang ở dòng thứ nhất, chúng ta sẽ điền tiêu đề là giá trị công thức đó. Với suy nghĩ này, chúng ta có thể kiểm tra xem công thức đang đứng ở dòng thứ mấy bằng cách sử dụng hàm ROW(), nếu kết quả của ROW(B:B) = 1, ta sẽ trả về “Tax”, nếu kết quả không phải là 1, chúng ta sẽ thực hiện tính toán như bình thường.

Công thức có thể viết như sau:

=ArrayFormula(IF(ROW(B:B)=1,”Tax”,IF(ISBLANK(B:B),””,ROUND(B:B*18%, 2))))

Cách áp dụng công thức cho cả cột trong Google Sheet
Sử dụng công thức mảng trong tiêu đề bảng

Như vậy, với kiến thức học được trong bài này, bạn có thể áp dụng cho trường hợp xử lý dữ liệu về từ Google Forms. Khi một dòng dữ liệu mới được thêm vào Google Forms, bạn có thể thiết lập công thức sẵn để công thức trong các cột tự tạo được mở rộng ra một cách tự động.

Một ứng dụng nữa của kỹ thuật sử dụng công thức mảng này trong Google Sheets là kết hợp hàm VLOOKUP với công thức mảng ARRAYFORMULA

See also  Crdi Là Gì - Khác Nhau Giữa Crdi Và Evgt | Bostonenglish.edu.vn

Giả sử bạn có một bảng “Fruits” liệt kê tên hoa quả trong cột A và giá của hoa quả đó trong cột B. Bảng thứ hai là bảng “Orders” có tên sản phẩm trong cột A, số lượng trong cột B. Chúng ta sẽ muốn điền giá trị hàng đặt vào cột C. Chúng ta có thể sử dụng công thức như sau

=ArrayFormula( IF(ROW(A:A)=1, “Total”, IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!$A$2:$B$10, 2, FALSE) * B:B, “” )))

Cách áp dụng công thức cho cả cột trong Google Sheet
Sử dụng VLOOKUP và công thức mảng trong Google Sheets

Nếu vùng tra cứu không ở cùng trong 1 tài liệu Google Sheets, bạn có thể sử dụng hàm IMPORTRANGE để kết hợp vào trong công thức mảng phía trên.

Lưu ý: trong bài viết sử dụng dấu phẩy là dấu ngăn cách giữa các thành phần trong hàm, có thể trên tài liệu của bạn được thiết lập locales khác, nếu nhập công thức gặp lỗi, bạn có thể xem lại thiết lập này hoặc chuyển dấu phẩy thành dấu chấm phẩy khi nhập công thức.

– Rê chuột hiện dấu + ở mép phải ô Google Sheet rồi kéo từ ô đầu đến ô cuối cần kéo

Đây là cách khá phổ biến nếu bạn đã từng sử dụng qua Excel, đối với Google Sheet cũng thực hiện tương tự:

Bước 1: Di chuyển chuột vào mép phải của ô chứa công thức chung của cả cột đến khi hiện dấu +.

Cách áp dụng công thức cho cả cột trong Google Sheet

Di chuyển chuột đến mép phải

Bước 2: Kéo đến ô cuối của cột đó là được.

Cách áp dụng công thức cho cả cột trong Google Sheet

Kéo đến cuối cột

– Nhấn đúp dấu + ở mép phải ô Google Sheet để sao chép công thức

Một cách khác để sao chép công thức một cách nhanh chóng cho cột của bạn là nháy đúp chuột vào mép phải ô đầu tiên chứa công thức chung, sau đó công thức sẽ được tự động copy cho các ô còn lại.

Cách áp dụng công thức cho cả cột trong Google Sheet

Nháy đúp chuột vào mép phải

– Sử dụng hàm ARRAYFORMULA để sao chép công thức

Đối với laptop Windows nếu đã có sẵn một công thức chung cho cả cột thì thay vì phải nhập thủ công hàm ARRAYFORMULA bạn có thể thực hiện như sau:

Bước 1: Chọn vào ô chứa công thức sau đó nhấn tổ hợp phím Ctrl + Shift + Enter.

Cách áp dụng công thức cho cả cột trong Google Sheet

Sử dụng phím tắt để gọi hàm

Bước 2: Chọn lại phạm vi cho cả cột.

Cách áp dụng công thức cho cả cột trong Google Sheet

Sửa lại phạm vi

– Sử dụng cách 1: Đây là cách thủ công thông thường mà các bạn hay làm vì khá dễ dàng nhưng bạn chỉ nên dùng nếu chỉ cần copy vài dòng thôi. Nếu bạn có rất nhiều ô, tới hàng chục nghìn dòng thì cách kéo thủ công không khuyến khích.

– Sử dụng cách 2: Nếu trong danh sách của cột nằm kế bên trái của cột cần sao chép của bạn có 1 ô trống thì công thức sẽ tự động dừng sao chép ngay trước ô đó vì Google Sheet sẽ phát hiện đó là ô cuối cùng của cột dữ liệu dẫn đến các ô tiếp theo sau sẽ không được sao chép công thức nữa.

– Sử dụng cách 3: Là cách tối ưu nhất dành cho bạn khi muốn cách sao chép công thức tự động trong Google Sheet cho cả dải ô. Vừa nhanh, tiết kiệm thời gian, lại tiện.

Cách áp dụng công thức cho cả cột trong Google Sheet

Lưu ý với các cách thực hiện

Cách đơn giản nhất để sao chép công thức đó sử dụng Fill Handle. Đây là một tính năng trong GG Sheets có thể copy công thức cho toàn bộ cột. Nếu dữ liệu của bạn không lớn, hãy kéo Fill Handle ô công thức xuống toàn bộ cột bạn cần sao chép.

Dưới đây sẽ là ví dụ một danh sách các số ở cột A và kết quả của công thức bình phương của số đó được hiển thị ở cột B.

Cách áp dụng công thức cho cả cột trong Google Sheet
Cách áp dụng công thức cho cả cột trong Google Sheet
Cách áp dụng công thức cho cả cột trong Google Sheet

Bây giờ, mình sẽ từng bước hướng dẫn bạn sử dụng Fill Handle:

1. Đầu tiên, mình chọn ô trên cùng của cột và nhập công thức. Hàm mình nhập ở ô B2 là một công thức tính bình phương của ô: =A2^2

Cách áp dụng công thức cho cả cột trong Google Sheet

2. Sau đó, bạn hãy trỏ con chuột đến góc phải dưới của ô bạn nhập công thức. Bạn sẽ thấy có 1 hình vuông xanh nhỏ xuất hiện, đó chính là Fill Handle. Và khi bạn di chuyển Fill Handle, hình vuông nhỏ ấy sẽ chuyển thành hình dấu cộng đen.

Cách áp dụng công thức cho cả cột trong Google Sheet

3. Bạn nhấp giữ chuột trái và kéo xuống toàn bộ ô trong cột bạn cần sao chép công thức.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Cách áp dụng công thức cho cả cột trong Google Sheet
Cách áp dụng công thức cho cả cột trong Google Sheet

4. Lúc này, các ô trong cột B đã cùng một công thức. Các ô tham chiếu đều mang giá trị tương đối, nghĩa là Fill Handle đã tự động điều chỉnh các tham chiếu theo vị trí các ô.

Ví dụ, ở hàng thứ 4, mình muốn tính căn bậc hai của ô A4 nhưng công thức mình nhập lúc đầu là tham chiếu đến ô A2. Không sao cả! Vì GG Sheets đã linh động quản lý việc này cho ta: ô B2 sẽ tham chiếu ô A2, B3 tham chiếu A3, B4 tham chiếu A4,… Tất nhiên, với mỗi hàng thì GG Sheets sẽ luôn hiển thị kết quả chính xác miễn là bạn nhập đúng công thức chứ không cần phải nhập công thức từng ô một cách thủ công.

Cách áp dụng công thức cho cả cột trong Google Sheet

Rất đơn giản phải không nào? Đây là bản COPY của ví dụ trên để cho bạn luyện tập.

Bạn có thể nhận được kết quả tương tự như trên bằng cách nhấn đúp chuột Fill Handle. Bằng cách này, Fill Handle sẽ tự động sao chép công thức đến ô cuối cùng trong danh sách mà bạn không cần phải kéo như cách trên.

Bạn đang xem bài viết tại: https://bostonenglish.edu.vn/

Cách áp dụng công thức cho cả cột trong Google Sheet

Tuy nhiên, cách này thoạt đầu nghe có vẻ rất tiện lợi hơn nhiều nhưng cũng có 1 số hạn chế: đó là nếu trong danh sách/dữ liệu của bạn có 1 ô trống thì công thức sẽ tự động dừng sao chép ngay trước ô đó vì GG Sheets sẽ phát hiện đó là ô cuối cùng của cột dữ liệu rồi và các ô tiếp theo sau sẽ không được sao chép công thức nữa.

Khi mình nhấn đúp Fill Handle thì Fill Handle chỉ sao chép công thức đến ô B5 vì GG Sheets phát hiện ô B6 trống (tức dữ liệu đã hết) nên B5 sẽ là hàng cần điền cuối cùng.

Cách áp dụng công thức cho cả cột trong Google Sheet

Nếu cột dữ liệu của bạn có các ô trống như thế mà bạn lại không muốn xóa hay điền vào ô đó thì tốt nhất là bạn nên sử dụng phương pháp kéo Fill Handle xuống cho tiện.

Một cách khác để sao chép công thức xuống toàn bộ cột trong GG Sheets 1 cách nhanh chóng và hiệu quả là sử dụng hàm ARRAYFORMULA. Công cụ Fill Handle chỉ thích hợp cho các cột nhỏ nhưng nếu bảng tính của bạn lớn thì tốt nhất bạn nên sử dụng hàm ARRAYFORMULA để sao chép công thức.

Hàm ARRAYFORMULA rất hữu ích khi chỉ cần nhập công thức trong ô đầu tiên và toàn bộ ô còn lại trong cột sẽ tự động sao chép công thức. Đây là công thức duy nhất có thể lấp đầy toàn bộ cột của mình mà không cần phải sao chép hoặc kéo xuống như 2 cách trên.


Bây giờ, mình sẽ từng bước hướng dẫn bạn sử dụng hàm ARRAYFORMULA này nhé:
1. Đầu tiên, ta chọn ô trên cùng của cột để nhập hàm ARRAYFORMULA và lại một lần nữa là ô B2 như trong ví dụ trên.
2. Để bắt đầu, bạn hãy nhập một dấu bằng “=”, theo sau là tên hàm “ARRAYFORMULA” và dấu mở ngoặc “(“.