Làm thế nào để sao chép các công thức với Tự động điền trong Excel 2016 - núm vú

Nếu bạn chỉ cần sao chép một công thức duy nhất trong Excel 2016, hãy sử dụng tính năng Tự động điền hoặc các lệnh Sao chép và Dán. Loại bản sao công thức, mặc dù khá phổ biến, không thể được thực hiện với kéo và thả.

Đừng quên tùy chọn Tổng số trên công cụ Phân tích Nhanh. Bạn có thể sử dụng nó để tạo một hàng hoặc một cột của tổng số ở cạnh dưới cùng hoặc bên phải của một bảng dữ liệu trong một đèn flash. Đơn giản chỉ cần chọn bảng làm dải ô và nhấp vào nút Phân tích Nhanh tiếp theo là Totals trong bảng màu của nó. Sau đó, nhấp vào nút Sum ở đầu palette để tạo các công thức tổng cộng các cột trong một hàng mới ở cuối bảng và / hoặc nút Sum ở cuối palette để tạo các công thức tổng các hàng trong một bảng mới cột bên phải.

Đây là cách bạn có thể sử dụng Tự động điền để sao chép một công thức vào một dải ô. Trong hình này, bạn có thể xem bảng tính Bán hàng của Doanh nghiệp Mẹ - 2016 với tất cả các công ty, nhưng lần này chỉ với tổng số hàng tháng trong hàng 12, đang trong quá trình sao chép qua ô E12.

Sao chép công thức vào phạm vi ô với Tự động điền.

Hình dưới đây cho thấy bảng tính sau khi kéo chốt điền vào ô B12 để chọn phạm vi ô C12: E12 (nơi cần sao chép công thức này).

Bảng tính sau khi sao chép công thức tổng cộng doanh thu hàng tháng (và hàng quý).

Tương đối

Hình vẽ bảng tính sau khi công thức trong ô được sao chép vào ô C12: E12 và ô B12 đang hoạt động. Lưu ý cách Excel xử lý việc sao chép các công thức. Công thức ban đầu trong ô B12 như sau:

= SUM (C3: C11)

Excel điều chỉnh tham chiếu cột, thay đổi nó từ B sang C, bởi vì bạn đã sao chép từ trái sang phải qua các hàng.

Khi bạn sao chép công thức vào dãy ô kéo dài các hàng, Excel sẽ điều chỉnh các số dòng trong các công thức được sao chép chứ không phải là các ký tự cột cho phù hợp với vị trí của mỗi bản sao. Ví dụ, ô E3 trong Doanh nghiệp Doanh nghiệp Con Mẹ - bảng tính Bán hàng năm 2016 chứa công thức sau:

= SUM (B3: D3)

Khi bạn sao chép công thức này vào ô E4, Excel sẽ thay đổi bản sao của công thức sang Sau đây:

= SUM (B4: D4)

Excel điều chỉnh tham chiếu dòng để giữ hiện tại với vị trí hàng mới 4. Bởi vì Excel điều chỉnh các tham chiếu ô trong các bản sao của công thức liên quan đến hướng sao chép, các tham chiếu ô được gọi là

các tham chiếu tế bào tương đối

. Một số điều là tuyệt đối! Tất cả các công thức mới mà bạn tạo ra đều có chứa tham chiếu tế bào tương đối trừ khi bạn nói khác. Vì hầu hết các bản sao của công thức yêu cầu phải điều chỉnh các tham chiếu tế bào của chúng, bạn hiếm khi phải đưa ra cách sắp xếp này một ý nghĩ thứ hai. Sau đó, mỗi một lần trong một thời gian, bạn gặp một ngoại lệ mà kêu gọi để hạn chế thời gian và cách tài liệu tham khảo tế bào được điều chỉnh trong bản sao.

Một trong những trường hợp ngoại lệ phổ biến nhất là khi bạn muốn so sánh một loạt các giá trị khác nhau với một giá trị duy nhất. Điều này xảy ra thường xuyên nhất khi bạn muốn tính phần trăm mỗi phần là tổng số. Ví dụ: trong bảng tính Doanh thu của Doanh nghiệp Người Mẹ - 2016, bạn gặp tình huống này trong việc tạo và sao chép một công thức tính tỷ lệ phần trăm mỗi tháng (trong dải ô B14: D14) là tổng số hàng quý trong ô E12.

Giả sử bạn muốn nhập các công thức này trong hàng 14 của Doanh nghiệp Mẹ Goose - 2016 Bảng tính bán hàng, bắt đầu từ ô B14. Công thức trong ô B14 để tính tỷ lệ phần trăm của tổng doanh thu từ tháng 1 đến đầu tiên là rất đơn giản:

= B12 / E12

Công thức này chia số bán hàng tháng một trong ô B12 theo tổng số hàng quý trong E12 (những gì có thể được dễ dàng hơn?). Tuy nhiên, hãy nhìn vào điều gì sẽ xảy ra nếu bạn kéo một thanh ghi điền vào ô bên phải để sao chép công thức này vào ô C14:

= C12 / F12

Việc điều chỉnh tham chiếu ô đầu tiên từ B12 đến C12 chỉ là những gì bác sĩ đã ra lệnh. Tuy nhiên, việc điều chỉnh tham chiếu ô thứ hai từ E12 đến F12 là một thảm hoạ. Không chỉ bạn không tính tỷ lệ phần trăm doanh số tháng 2 trong ô C12 là doanh số bán hàng quý I ở E12, nhưng bạn cũng kết thúc với một trong số đó là # DIV / 0! lỗi trong C14 tế bào.

Để ngăn Excel không điều chỉnh tham chiếu ô trong công thức trong bất kỳ bản sao nào bạn thực hiện, chuyển đổi tham chiếu ô tương đối so với tuyệt đối. Bạn thực hiện việc này bằng cách nhấn phím chức năng F4, sau khi bạn đưa Excel vào chế độ Chỉnh sửa (F2). Excel chỉ ra rằng bạn làm cho tham chiếu ô tuyệt đối bằng cách đặt các dấu hiệu đô la ở phía trước của cột chữ cái và số hàng. Ví dụ, trong hình này, ô B14 chứa công thức chính xác để sao chép vào phạm vi ô C14: D14:

Sao chép công thức tính tỷ lệ doanh thu hàng tháng / hàng quý với tham chiếu ô tuyệt đối.

= B12 / $ E $ 12

Xem bảng tính sau khi công thức này được sao chép vào dải C14: D14 với tay cầm đầy và ô C14 được chọn (xem hình sau). Lưu ý rằng thanh công thức cho biết ô này chứa công thức sau:

Bảng tính sau khi sao chép công thức với tham chiếu ô tuyệt đối.

= C12 / $ E $ 12

Bởi vì E12 đã được thay đổi thành $ E $ 12 trong công thức gốc, tất cả các bản sao đều có cùng tham chiếu tuyệt đối (không thay đổi) này.

Nếu bạn đi lên và sao chép một công thức mà một hoặc nhiều tham chiếu ô cần phải là tuyệt đối nhưng bạn đã để lại tất cả chúng tương đối, hãy chỉnh sửa công thức ban đầu như sau:

Nhấp đúp vào ô với công thức hoặc nhấn F2 để chỉnh sửa nó.

Vị trí điểm chèn một nơi nào đó trên tham chiếu bạn muốn chuyển đổi sang tuyệt đối.

1. Nhấn F4.

2. Khi bạn hoàn tất chỉnh sửa, nhấp vào nút Enter trên thanh công thức và sau đó sao chép công thức lên phạm vi ô sai số với tay cầm đổ đầy.

3. Hãy chắc chắn nhấn phím F4 chỉ một lần để thay đổi tham chiếu ô hoàn toàn tuyệt đối như tôi đã mô tả trước đó. Nếu bạn bấm phím chức năng F4 một lần nữa, bạn sẽ kết thúc với cái gọi là

4. tham chiếu hỗn hợp,

chỉ có phần dòng là tuyệt đối và phần cột là tương đối (như trong E $ 12). Nếu bạn nhấn F4 một lần nữa, Excel xuất hiện với một kiểu tham chiếu hỗn hợp, ở đó phần cột là tuyệt đối và phần dòng là tương đối (như trong $ E12). Nếu bạn tiếp tục và nhấn F4 một lần nữa, Excel sẽ thay đổi tham chiếu ô trở lại hoàn toàn tương đối (như trong E12). Sau khi bạn quay lại nơi bắt đầu, bạn có thể tiếp tục sử dụng F4 để xoay vòng qua cùng một tập hợp các tham chiếu ô này thay đổi lại. Nếu bạn đang sử dụng Excel 2016 trên thiết bị không có quyền truy cập vào một bàn phím vật lý với các phím chức năng (như một máy tính bảng màn hình cảm ứng), cách duy nhất để chuyển đổi các địa chỉ ô trong các công thức của bạn so với dạng tuyệt đối hoặc một số dạng địa chỉ hỗn hợp là mở bàn phím Touch và sử dụng nó thêm các dấu hiệu đô la trước chữ cái cột và / hoặc số hàng trong địa chỉ ô thích hợp trên thanh Công thức.