Cách sao chép công thức trong Excel mà không làm thay đổi tham chiếu của ô

Nếu bạn cần sao chép công thức trong Excel mà không muốn thay đổi tham chiếu đến các ô, Siêu Marketing sẽ hướng dẫn bạn một số phương pháp để thực hiện điều này. Tùy vào nhu cầu công việc, hãy lựa chọn phương án phù hợp từ các bước sau đây.

Khi điền một công thức vào một ô và muốn sao chép nó qua các ô khác trong cùng một cột hoặc hàng để thực hiện các phép toán giống nhau cho một nhóm dữ liệu, bạn thường sẽ làm như vậy. Trong Excel, nếu công thức bao gồm tham chiếu ô tương đối (không có dấu $), chương trình sẽ tự động điều chỉnh chúng sao cho mỗi công thức đều xử lý dữ liệu trong cùng một ô và cột. Chẳng hạn, công thức =A1*2 nằm tại ô B1, khi bạn sao chép nó đến ô B3, công thức sẽ biến đổi thành =A3*2.

Cách sao chép hoặc di chuyển một công thức riêng lẻ mà không thay đổi tham chiếu ô

Để sao chép hoặc di chuyển đúng một công thức mà không thay đổi tham chiếu ô, bạn có thể làm như sau:

  • Tìm đến ô có công thức bạn muốn sao chép
  • Trên thanh công cụ, chọn công thức bằng cách sử dụng chuột và nhấn Ctrl + C để sao chép, hoặc nhấn Ctrl + X nếu bạn muốn di chuyển nó.

  • Ấn Esc để thoát khỏi chế độ chỉnh sửa công thức trên thanh công cụ.
  • Di chuyển đến ô mà bạn muốn dán công thức và ấn Ctrl + V.

Hơn nữa, bạn cũng có thể thực hiện cách sau:

  • Chọn ô chứa công thức cần sao chép
  • Ấn F2 hoặc chọn đôi chuột để chuyển sang chế độ chỉnh sửa
  • Bằng chuột, chọn và sao chép công thức với Ctrl + C.
  • Điều hướng tới ô cần dán công thức và ấn Ctrl + V để dán chính xác công thức mà không thay đổi tham chiếu ô, vì bạn sao chép công thức dưới dạng văn bản.

Lời khuyên. Để sao chép nhanh công thức từ các ô phía trên mà không làm thay đổi tham chiếu, bạn nên chọn ô cần sao chép và ấn Ctrl + ‘

Sao chép một loạt công thức mà không làm thay đổi các tham chiếu ô

Đối với việc di chuyển hoặc sao chép nhiều công thức cùng lúc mà không muốn thay đổi tham chiếu của các ô, bạn cần áp dụng các bước dưới đây.

Cách 1. Áp dụng tham chiếu tuyệt đối hoặc tham chiếu hỗn hợp

Trong trường hợp bạn cần sao chép chính xác một công thức có tham chiếu tương đối (ví dụ A1), bạn nên chuyển chúng thành tham chiếu tuyệt đối ($A$1) để tham chiếu này không thay đổi khi di chuyển công thức. Hay bạn cũng có thể sử dụng tham chiếu hỗn hợp ($A1 hoặc A$1) để cố định cột hoặc hàng. Bạn cảm thấy khá kỳ lạ với cách này? Hãy xem xét phương án sau đây.

Hãy tưởng tượng bạn đang có một bảng giá trị hoa quả tính bằng Euro dựa vào giá USD ở cột B và tỷ giá quy đổi ở ô C2:

Bạn sẽ thấy trong hình, công thức bao gồm một tham chiếu ô tuyệt đối ($C$2) để cố định tỷ giá ở ô C2, cùng tham chiếu tương đối B5 để điều chỉnh theo từng hàng. Điều này có hiệu quả miễn là công thức vẫn đặt trong cột C.

Tuy nhiên, nếu bạn cần chuyển giá từ Euro ở cột C sang cột F, khi sao chép công thức bằng cách thông thường, công thức ở ô C5 (=B5*$C$2) sẽ thay đổi thành =D5*$C$2 khi được dán vào ô F5, làm sai lệch kết quả tính toán.

Để ngăn chặn việc này, bạn cần chuyển tham chiếu tương đối (B5) thành tham chiếu hỗn hợp $B5 (cột

Khi bạn thêm dấu $ trước từng ký tự trong tên cột, bạn đã thiết lập tham chiếu cố định tới cột B, không phụ thuộc vào việc công thức được dịch chuyển như thế nào.

Khi bạn sao chép hoặc dịch chuyển công thức từ cột D sang cột F hay sang bất kỳ cột nào khác, tham chiếu cột sẽ giữ nguyên không đổi bởi vì bạn đã ‘cố định’ nó với dấu $ ($B5).

Học cách sử dụng tham chiếu ô trong Excel có thể hơi khó khăn lúc đầu, tuy nhiên việc này đáng giá với thời gian và nỗ lực bạn bỏ ra do nó giúp bạn tiết kiệm thời gian đáng kể trong dài hạn. Chẳng hạn, bạn có khả năng tính toán toàn bộ bảng tính chỉ với một công thức duy nhất nhờ vào việc dùng tham chiếu ô hỗn hợp.

Dẫu vậy, nếu bạn đang có một lượng lớn công thức với tham chiếu tương đối và bạn muốn sao chép chính xác nhưng không chắc chắn về cách tham chiếu, bạn có thể áp dụng một trong số phương pháp dưới đây như giải pháp.

Cách 2. Sao chép công thức trong Excel mà không làm thay đổi tham chiếu qua Notepad

1. Bật chế độ hiển thị công thức bằng cách nhấn Ctrl + `, hoặc thông qua các phương pháp được đề cập trong bài viết về cách hiển thị công thức trên Excel.
2. Lựa chọn tất cả các ô chứa công thức bạn muốn sao chép hay dịch chuyển.
3. Nhấn Ctrl + C để sao chép, hoặc Ctrl + X để cắt công thức.


4. Mở Notepad hay chương trình chỉnh sửa văn bản khác và nhấn Ctrl + V để dán công thức, tiếp theo chọn Ctrl + A nhằm chọn tất cả công thức và nhấn Ctrl + C để sao lại.
5. Quay lại Excel, chọn ô đích và nhấn Ctrl + V để dán công thức.

Lưu ý:
1. Bạn chỉ có thể dán công thức vào cùng một trang tính nơi công thức gốc tồn tại, trừ trường hợp tham chiếu bao gồm tên trang tính, nếu không công thức sẽ không còn hợp lệ.
2. Đảm bảo trang tính đang ở chế độ hiển thị công thức. Kiểm tra điều này bằng cách đi tới Formulas > Formula Auditing và xác nhận rằng mục Show Formulas đã được chọn. Khi hoàn tất quá trình dán, nhấn lại Ctrl + ` để tắt chế độ hiển thị công thức.

Cách 3. Đảm bảo sao chính xác công thức sử dụng Tìm kiếm và Thay thế của Excel

Để chép một loạt công thức trong Excel mà không làm biến đổi tham chiếu ô, hãy sử dụng tính năng Find and Replace của Excel.

1. Chọn các ô chứa công thức bạn muốn chép.
2. Tới thanh Home, chọn Editing, chọn Find & Select > Replace… Hay chỉ cần dùng Ctrl + H như đường tắt mở hộp thoại Find & Replace trong Excel.
3. Tại cửa sổ hộp thoại Find & Replace, nhập dấu = vào ô Find what. Nhập một ký tự hoặc chuỗi các ký tự không sử dụng trong công thức nào như ~, # hay . vào ô Replace with.

Mục tiêu ở đây là chuyển đổi công thức của bạn thành chuỗi văn bản, ngăn cản Excel thay đổi tham chiếu ô trong quá trình sao chép.

Ghi chú: Không nên sử dụng dấu (*) hay dấu (?) làm thay thế vì chúng có thể gây khó khăn trong các bước tiếp theo.

4. Click Replace All trong hộp thoại và đóng cửa sổ Find and Replace. Công thức trong vùng chọn sẽ chuyển đổi thành chuỗi văn bản:

5. Giờ đây, bạn có thể chọn bất kỳ ô nào, nhấn Ctrl + C để sao chép, chọn vị trí ô sản phẩm trên bảng tính bạn đang làm mà bạn muốn dán công thức vào, và nhấn Ctrl + V. Vì công thức không bắt đầu bằng dấu = sẽ không bị Excel thay đổi, chúng sẽ được sao y nguyên mà không hề bị biến đổi tham chiếu.

Hãy áp dụng Find & Replace, để hoán đổi lại thay đổi đã làm ở bước trước. Hãy chọn vùng dữ liệu đã sao chép cùng với vùng dữ liệu đã dán các công thức. Để mở hộp thoại Find & Replace, nhấn tổ hợp phím Ctrl + H. Trong ô Find what, nhập vào hoặc ký tự nào đó mà bạn sử dụng trong bước 3. Ghi dấu bằng “=” vào ô Replace with và sau đó nhấn vào Replace All để kết thúc quá trình này.

– – – – – –

Các biện pháp kể trên là những cách thức để sao chép công thức trong Excel mà không gây biến đổi các dữ liệu tham chiếu, hay còn gọi là phương pháp sao chép dữ liệu trong excel. Để có thể sử dụng Excel hiệu quả trong công việc, chúng ta cần không chỉ hiểu rõ về các hàm, mà còn phải thành thạo việc dùng các công cụ của Excel. Có những hàm cấp cao như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… góp phần áp dụng hiệu quả trong công việc. Các công cụ thường dùng bao gồm Data validation, Conditional formatting, Pivot table…

Bạn có thể học được toàn bộ những kiến thức này thông qua các khóa học Excel dành cho người đi làm từ cơ bản đến chuyên nghiệp.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop