Làm cách đặt công thức cho cả cột trong excel (5 cách dễ dàng)
Công thức là nguồn gốc và máu của bảng tính Excel. Và trong hầu hết các trường hợp, bạn không cần công thức chỉ trong một ô hoặc một vài ô. Trong hầu hết các trường hợp, bạn sẽ cần áp dụng công thức cho toàn bộ cột (hoặc nhiều ô trong một cột). Và Excel cung cấp cho bạn nhiều cách khác nhau để thực hiện việc này bằng một vài cú nhấp chuột (hoặc một phím tắt). Chúng ta hãy xem xét các phương pháp này. Bạn đang xem: Cách đặt công thức cho cả cột trong excel
1. Bằng cách nhấp đúp vào Auto
Fill Handle
Một trong những cách dễ nhất để áp dụng công thức cho toàn bộ cột là sử dụng thủ thuật nhấp đúp chuột đơn giản này
Giả sử bạn có tập dữ liệu như được hiển thị bên dưới, muốn tính hoa hồng cho mỗi đại diện bán hàng trong Cột C (trong đó hoa hồng sẽ là 15% giá trị bán hàng trong cột B).
Công thức cho tình huống này sẽ là:
=B2*15%
2. Với ô được chọn, bạn sẽ thấy một hình vuông nhỏ màu xanh lá cây ở phần dưới cùng bên phải của vùng chọn.
3. Đặt con trỏ trên hình vuông nhỏ màu xanh lá cây. Bạn sẽ nhận thấy rằng con trỏ thay đổi thành dấu cộng (đây được gọi là điều khiển Auto
Fill).
Các bước trên sẽ tự động điền vào toàn bộ cột cho đến ô mà bạn có dữ liệu trong cột liền kề. Trong ví dụ của chúng tôi, công thức sẽ được áp dụng cho đến ô C15.
Để điều này hoạt động, không được có dữ liệu trong cột liền kề và không được có bất kỳ ô trống nào trong đó. Ví dụ: nếu có một ô trống trong cột B (ví dụ như ô B6), thì nhấp đúp tự động điền này sẽ chỉ áp dụng công thức cho đến ô C5.
Khi bạn sử dụng trình điều khiển tự động điền để áp dụng công thức cho toàn bộ cột, điều đó tương đương với việc sao chép công thức theo cách thủ công. Điều này có nghĩa là tham chiếu ô trong công thức sẽ thay đổi tương ứng.
Ví dụ: nếu đó là một tham chiếu tuyệt đối, nó sẽ vẫn như cũ trong khi áp dụng công thức cho cột, nếu đó là một tham chiếu tương đối, sau đó nó sẽ thay đổi khi công thức được áp dụng cho các ô bên dưới.
2. Bằng cách kéo Auto
Fill Handle
Một vấn đề với phương pháp nhấp đúp ở trên là nó sẽ dừng ngay khi gặp ô trống trong các cột liền kề.
Nếu bạn có một tập dữ liệu nhỏ, bạn cũng có thể kéo chốt điền theo cách thủ công để áp dụng công thức trong cột.
Dưới đây là các bước để thực hiện việc này:
1. Tại ô A2, nhập công thức: =B2*15%
2. Với ô được chọn, bạn sẽ thấy một hình vuông nhỏ màu xanh lá cây ở phần dưới cùng bên phải của vùng chọn3. Đặt con trỏ trên hình vuông nhỏ màu xanh lá cây. Bạn sẽ nhận thấy rằng con trỏ chuyển thành dấu cộng
4. Giữ phím trái chuột và kéo nó đến ô mà bạn muốn áp dụng công thức5. Nhả chuột khi hoàn tất
3. Sử dụng tùy chọn Fill Down (nằm trong ribbon)
Một cách khác để áp dụng công thức cho toàn bộ cột là sử dụng tùy chọn Fill Down trong ribbon.
Để phương pháp này hoạt động, trước tiên bạn cần chọn các ô trong cột mà bạn muốn có công thức.
Dưới đây là các bước để sử dụng phương pháp Fill Down:
1. Tại ô A2, nhập công thức: =B2*15%2. Chọn tất cả các ô mà bạn muốn áp dụng công thức (bao gồm cả ô C2)
3. Nhấp vào tab Home
4. Trong nhóm chỉnh sửa, nhấp vào biểu tượng Fill
5. Nhấp vào ‘Fill down’
Các bước trên sẽ lấy công thức từ ô C2 và điền nó vào tất cả các ô đã chọn.
Thêm Fill Down trong Thanh công cụ Truy cập Nhanh
Nếu bạn cần sử dụng tùy chọn Fill Down thường xuyên, bạn có thể thêm tùy chọn đó vào Thanh công cụ truy cập nhanh để bạn có thể sử dụng tùy chọn này với một cú nhấp chuột (và nó luôn hiển thị trên màn hình).
Để thêm nó vào Thanh công cụ Truy cập Nhanh (QAT), đi tới tùy chọn ‘Fill Down’, nhấp chuột phải vào nó, sau đó nhấp vào ‘Add to the Quick Access Toolbar’.
Bây giờ, bạn sẽ thấy biểu tượng ‘Fill Down’ xuất hiện trong QAT.
Sử dụng phím tắt
Nếu bạn thích sử dụng phím tắt hơn, bạn cũng có thể sử dụng phím tắt bên dưới để thực hiện chức năng điền:
CTRL + D (giữ phím Ctrl rồi nhấn phím D).
Dưới đây là các bước sử dụng phím tắt để điền công thức:
1. Tại ô A2, nhập công thức: =B2*15%2. Chọn tất cả các ô mà bạn muốn áp dụng công thức (bao gồm cả ô C2)3. Giữ phím Ctrl rồi nhấn phím D
4. Sử dụng công thức mảng
Nếu đang sử dụng Microsoft 365 và có quyền truy cập vào mảng động, bạn cũng có thể sử dụng phương pháp công thức mảng để áp dụng công thức cho toàn bộ cột.
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn tính Hoa hồng trong cột C.
Dưới đây là công thức mà bạn có thể sử dụng:
=B2:B15*15%
Đây là công thức Mảng sẽ trả về 14 giá trị trong ô (mỗi giá trị cho B2: B15). Nhưng vì chúng ta có mảng động, kết quả sẽ không bị giới hạn trong ô đơn và sẽ tràn ra để lấp đầy toàn bộ cột.
Lưu ý rằng bạn không thể sử dụng công thức này trong mọi trường hợp. Trong trường hợp này, vì công thức của chúng tôi sử dụng giá trị đầu vào từ một cột liền kề và bằng cùng độ dài của cột mà chúng tôi muốn có kết quả (tức là 14 ô), nó hoạt động tốt ở đây.
Nhưng nếu không phải như vậy, đây có thể không phải là cách tốt nhất để sao chép công thức vào toàn bộ cột
Bằng cách sao chép-dán ô
Một phương pháp nhanh chóng và nổi tiếng khác để áp dụng công thức cho toàn bộ cột (hoặc các ô được chọn trong toàn bộ cột) là chỉ cần sao chép ô có công thức và dán nó lên các ô trong cột mà bạn cần công thức đó.
Dưới đây là các bước để thực hiện việc này:
1. Tại ô A2, nhập công thức: =B2*15%
2. Sao chép ô (sử dụng phím tắt Ctrl + C trong Windows hoặc Command + C trong Mac)
Trong bài hướng dẫn này, Học Excel Online sẽ hướng dẫn bạn một vài cách khác nhau để sao chép công thức trong excel – cách copy công thức bằng việc kéo cột dọc, cách copy với tất cả các ô được chọn, cách copy một công thức chính xác mà không làm thay đổi tham chiếu ô hay format, và nhiều cách thức khác nữa.
Sao chép công thức trong Excel là một trong những công việc đơn giản nhất, thông thường được thực hiện chỉ trong một cú click chuột. Nói “thông thường” là vì có những trường hợp điển hình cần đến những mẹo đặc biệt để thực hiện, ví dụ như copy một dãy các công thức mà không làm thay đổi tham chiếu ô, hoặc cùng nhập một công thức vào những ô không liên kề trong excel.
Xem thêm: Hướng Dẫn Cách Link Từ Excel Sang Word 2013, 2016 Cực Dễ Dàng
May mắn thay, trong Microsoft Excel có rất nhiều cách để ta thực hiện một công việc và cho ra một kết quả duy nhất, điều này áp dụng tương tự với việc sao chép các công thức. Trong bài hướng dẫn này, ta sẽ bàn đến những cách thức khác nhau để sao chép công thức trong Excel và bạn có thể chọn cách thức nào phù hợp nhất với nhu cầu của mình.
Sao chép công thức bằng cách kéo cột dọc:
Microsoft Excel cung cấp một cách rất nhanh để sao chép công thức bằng cách kéo cột dọc. Bạn chỉ cần làm như sau:Điền công thức vào ô trên cùngChọn ô có công thức, lúc này ô được chọn sẽ chuyển thành màu đen. Kéo chuột đến hình vuông nhỏ nằm ở góc bên phải dưới cùng của ô màu đen được chọn (hình vuông nhỏ này được gọi là Fill handle). Fill handle sẽ đổi thành một dấu cộng màu đen đậm.Giữ và kéo fill handle xuống cột dọc đến hết ô bạn muốn sao chép công thức.
Với cách thức tương tự, bạn có thể kéo công thức vào các ô liên kề ở phía bên phải, bên trái hay lên trên.
Nếu công thức có một tham chiếu ô tương đối (relative cell references – tham chiếu không có ký hiệu $), nó sẽ tự động thay đổi dựa trên vị trí tương đối của hàng và cột. Vì vậy, sau khi copy công thức, hãy chắc chắn rằng tham chiếu ô đã được điều chỉnh hợp lý và mang lại kết quả mà bạn cần. Nếu cần thiết, hãy chuyển đổi giữa tham chiếu ô tương đối, tuyệt đối và hỗn hợp bằng cách dùng phím F4.
Trong ví dụ nêu trên, để chắc chắn công thức đã được sao chép một cách chính xác, ãy chọn một vài ô ở cột C, ví dụ như C4 và xem tham chiếu ô trên thanh công thức. Như bạn có thể thấy trong ảnh chụp màn hình dưới đây, công thức đã đúng, hợp lý và chính xác với hàng 4:
Cách sao chép công thức bằng cách kéo cột dọc mà không sao chép định dạng:
Việc kéo cột dọc không chỉ giúp sao chép công thức mà còn sao chép luôn định dạng của ô gốc như màu nền, các ký hiệu tiền tệ, số thập phân được hiển thị,… Trong hầu hết các trường hợp, điều này không tạo thành vấn đề gì, nhưng đôi khi nó có thể làm rối loạn định dạng của ô nhận công thức được sao chép sang. Một ví dụ quen thuộc là việc toàn bộ cột dọc đã bị đổ màu như trong ảnh dưới đây.
Để tránh trường hợp này xảy ra, bạn hãy kéo fill handle như hướng dẫn ở trên, sau đó thả ra, click vào menu thả trong phần Auto Fill Option và chọn Fill Without Formating.
Sao chép công thức cho toàn bộ cột:
Như bạn đã thấy, fill handle giúp cho việc sao chép công thức trong Excel thực sự đơn giản. Nhưng nếu bạn cần sao chép công thức cho một trang tính dài 1000 dòng thì sao, việc kéo thả công thức đối với hàng trăm cột dường như không hợp lý cho lắm. Excel vì vậy sẽ có một vài phương án nhanh giải quyết trường hợp này.
Click chuột đôi vào dấu cộng để copy cho toàn bộ cột:
Để điền công thức cho toàn bộ cột, bạn hãy click chuột đôi vào biểu tượng dấu cộng thay vì kéo thả. Với những ai đã bỏ qua phần đầu tiên của bài hướng dẫn này, các bước chi tiết sẽ có ở dưới đây.
Để sao chép công thức cho toàn bộ cột, bạn hãy:
Điền công thức vào cột đầu tiênDi con trỏ chuột đến góc bên phải dưới cùng của ô được chọn sẽ xuất hiện một dấu cộng, click chuột đôi vào dấu cộng đó.
Lưu ý. Cách làm này sẽ sao chép công thức cho tới tất cả các cột có dữ liệu ở bên trái. Nghĩa là, miễn là ở các cột bên trái có dữ liệu, Excel sẽ tiếp tục copy công thức. Khi nào có một hàng trông, việc điền tự động sẽ dừng lại. Vì vậy, nếu excel của bạn có bất cứ một hàng trống nào, bạn sẽ phải lặp lại quy trình ở trên để tiếp tục copy công thức:
Cách sao chép một công thức Excel nhưng không định dạng:
Như bạn đã biết, khi sao chép một công thức bằng các kéo cột dọc trong Excel, bạn có thể dùng cách điền công thức mà không định dạng, giúp bạn copy công thức mà vẫn giữ được định dạng của ô. Công cụ copy – paste của Excel còn có cách thức linh hoạt hơn để có thể paste được công thức như bạn muốn.
Chọn ô có chứa công thứcSao chép ô đó bằng việc nhấn phím Ctrl + C. Cách khác, bạn có thể click chuột phải vào ô và chọn copy, hoặc click vào ô Copy trên thanh Home > Clipboard.Chọn tất cả các ô bạn muốn sao chép công thức.Click chuột phải vào các ô được chọn và chọn Formulas ở mục Paste Options.
Bạn sẽ có nhiều lựa chọn hơn bằng cách click vào hình mũi tên ở ô Paste nằm ở góc trái trên cùng của bảng tính. Tương tự, bạn có thể chọn Formulas & Number Formatting để paste duy nhất công thức và định dạng số như phần trăm, ký hiệu tiền tệ như ảnh dưới đây:
Mẹo. Nếu bạn không chắc cách paste nào là phù hợp nhất, bạn hãy di chuột đến những biểu tượng khác nhau mà không click vào, Excel sẽ hiện phần xem thử để bạn có thể lựa chọn.
Những cách làm tắt để sao chép công thức Excel đến các ô khác
1. Sao chép công thức đến ô ngay dưới
Ctrl + D – Sao chép một công thức từ một ô trong trang tính đến ngay ô dưới liền kề nó và điều chỉnh tham chiếu ô.
Ví dụ, nếu bạn có công thức ở ô A1 và bạn muốn sao chép công thức này vào ô A2, chọn A2 và nhấn Ctrl + D.
2. Sao chép công thức sang bên phải
Ctrl + R – Sao chép một công thức sang ô bên phải liền kề và điều chỉnh tham chiếu ô.
Ví dụ, nếu bạn có công thức ở ô A2 và bạn muốn sao chép đến ô B2, chọn B2 và nhấn Ctrl + R.
Mẹo. Cả 2 cách rên đều có thể dùng để copy công thức vào nhiều ô khác nhau, bạn chỉ cần chọn cả ô chứa công thức và ô nhận công thức, sau đó nhấn phím tắt. Ví dụ như bạn muốn sao chép công thức từ A1 đến 9 dãy sau đó, chọn ô A1:A10 và nhấn Ctrl + D.
3. Sao chép công thức vào ô ngay dưới một cách chính xác:
Ctrl + ‘ – Sao chép công thức từ ô bên trên vào các ô được chọn bên dưới một cách chính xác và hiển thị ô ở chế độ chỉnh sửa.
Đây là cách khá nhanh để sao chép công thức mà không thay đổi tham chiếu ô. Ví dụ, để sao chép một công thức từ ô A1 đến A2 mà không tham chiếu nào thay đổi, chọn A2 và nhấn Ctrl + ‘
Lưu ý. Đừng nhầm giữa Ctrl + ‘ (sao chép chính xác một công thức) với Ctrl + ` (hiển thị công thức trong Excel).
Bài viết này đã cung cấp cho bạn tất cả các cách thức để sao chép công thức trong Excel. Nếu bạn biết thêm bất cứ một phương pháp để có thể sao chép hay di chuyển công thức nhanh trong Excel, hãy chia sẻ nhé. Cảm ơn các bạn đã dành thời gian đọc bài viết này và hẹn bạn trong những bài viết tiếp theo.
Hướng dẫn cố định dòng và cột nâng cao trong Excel, nhiều cách mới hay và lạ
2 cách để loại bỏ dấu cách, khoảng trắng giữa các từ, các số trong ô tính Excel
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: Hoc
Excel.Online
