Trong đa số các trường hợp thao tác với Excel, bạn đều có thể thực hiện điền công thức vào trong một ô, rồi sau đó sao chép nó vào trong những ô khác ở cùng cột hoặc hàng để có thể thao tác các phép tính tương tự đối với một nhóm dữ liệu. Nếu như công thức của bạn có chứa các ô tham chiếu tương đối, tức là không có chứa kí hiệu $, thì Excel lúc này sẽ tự động điều chỉnh để chỉ xử lí dữ liệu tại cùng ô và cùng cột. Trong bài viết này, chúng ta sẽ cùng nhau tìm hiểu về sao chép công thức giữ nguyên tham chiếu tương đối trong Excel, cùng đón đọc nhé!
Ví dụ, nếu bạn đang có công thức =A1*2 ở ô B1, và bạn thực hiện copy công thức tới ô B3, công thức lúc này sẽ đổi thành =A3*2.
Nội dung bài viết
Sao chép/di chuyển một công thức đơn lẻ mà không làm thay đổi tham chiếu ô
Nếu như bạn đang cần sao chép công thức ở Excel hoặc là di chuyển chỉ một công thức, thì việc sao chép y hệt rất đơn giản. Các bước như sau:
Bước 1: Đầu tiên, nhấn chọn ô với công thức mà bạn đang muốn sao chép
Bước 2: Nhấn chọn công thức tại thanh công thức bằng chuột, sau đó nhấn Ctrl + C để tiến hành sao chép. Nếu như bạn muốn di chuyển công thức, hãy nhấn Ctrl + X.
Bước 3: Nhấn phím Esc để thoát khỏi thanh công thức.
Bước 4: Cuối cùng, nhấn chọn ô bạn đang muốn dán công thức và nhấn Ctrl + V là xong.
Bên cạnh đó, bạn cũng có thể sử dụng cách 2:
Bước 1: Đầu tiên, nhấn chọn ô có công thức mà bạn muốn sao chép
Bước 2: Tiếp đó, nhấn F2 hoặc là click chuột đôi để có thể sửa đổi ô đó
Bước 3: Nhấn chọn công thức trong ô bằng việc dùng chuột và Ctrl + C để sao chép.
Bước 4: Chọn ô để dán công thức, sau đó nhấn Ctrl + V. Bạn sẽ có thể thực hiện dán công thức chính xác mà không thay đổi tham chiếu của ô vì công thức đang được copy ở dạng chữ.
Mẹo. Để nhanh chóng copy công thức từ các ô phía trên mà không thay đổi tham chiếu, bạn nhấn chọn ô bạn muốn dán công thức, rồi nhấn Ctrl + ‘
Sao chép một vùng dữ liệu công thức mà không làm thay đổi tham chiếu ô
Để thực hiện di chuyển hoặc là sao chép vùng dữ liệu công thức và không làm thay đổi tham chiếu ô nào, bạn có thể sử dụng cách thức sau.
Cách 1. Dùng tham chiếu tuyệt đối hoặc là tham chiếu hỗn hợp
Nếu như bạn cần phải thực hiện sao chép chính xác công thức với tham chiếu ô tương đối (như A1), thì cách làm tốt nhất đó là đổi nó sang tham chiếu tuyệt đối ($A$1) để bạn có thể cố định tham chiếu này đối với ô. Như vậy thì lúc này nó sẽ được giữ nguyên dù cho bạn có di chuyển công thức đi đâu chăng nữa. Ngoài ra, bạn cũng có thể dùng tham chiếu ô hỗn hợp ($A1 hoặc là A$1) để khóa cột hay hàng. Nếu bạn nghĩ rằng cách này không hợp lý cho lắm thì hãy cùng xem xét ví dụ bên dưới nhé.
Ví dụ bạn có một bảng tính toán giá hoa quả ở cột C tính theo đồng Euro dựa trên giá USD ở cột B và tỉ giá nằm ở ô C2:
Lúc này, bạn có thể thấy ở ảnh minh họa bên trên rằng công thức gồm một tham chiếu ô tuyệt đối ($C$2) giúp cố định tỷ giá ở ô C2, bên cạnh đó là một tham chiếu ô tương đối tại ô B5 vì bạn muốn tham chiếu này điều chỉnh ở mỗi hàng. Cách thức tiếp cận này sẽ có hiệu quả miễn sao khi nào công thức cũng vẫn ở cột C.
Nhưng bạn hãy xem thử điều gì sẽ xảy ra nếu như bạn cần chuyển giá theo Euro từ cột C về cột F. Nếu bạn tiến ahnhf copy công thức theo cách làm thông thường với công cụ copy/paste, thì công thức ở ô C5 (=B5*$C$2) sẽ được đổi thành =D5*$C$2 khi tiến hành dán vào ô F5, sẽ khiến cho việc tính toán bị sai.
Để khắc phục tình trạng này, bạn hãy tiến hành chuyển tham chiếu tương đối (B5) sang tham chiếu hỗn hợp $B5 (cột tuyệt đối và dòng tương đối). Với việc thêm ký hiệu $ ở trước mỗi ký tự của cột, bạn đã có thể cố định tham chiếu tại cột B dù là công thức lúc này có di chuyển như thế nào.
Nếu như bạn tiến hành sao chép hoặc là di chuyển công thức từ cột D về cột F, hoặc là bất cứ cột nào, thì tham chiếu cột sẽ không thay đổi bởi vì bạn đã ‘khóa’ tham chiếu bởi ký hiệu $ ($B5).
Tham chiếu ô tại Excel có thể khá khó để bạn có thể hình dung khi mới bắt đầu, nhưng nó sẽ rất xứng đáng nếu bạn chịu bỏ thời gian và công sức ra tìm hiểu vì nó giúp bạn tiết kiệm nhiều thời gian một cách lâu dài. Ví dụ như bạn hoàn toàn có thể tính toán được toàn bộ bảng chỉ với một công thức duy nhất là dùng tham chiếu ô hỗn hợp.
Tuy vậy, nếu như bạn có rất nhiều công thức với tham chiếu ô tương đối tại trang tính và bạn đang cần sao chép chính xác các công thức đó nhưng bạn lại không chắc là bạn có thể tham chiếu đúng, một trong các cách sau đây sẽ là giải pháp cho bạn.
Cách 2. Tiến hành sao chép công thức trong Excel mà không làm thay đổi tham chiếu bằng Notepad
Bước 1: Tiến hành điền công thức tại chế độ hiển thị bằng việc nhấn Ctrl + `
Bước 2: Nhấn chọn tất cả các ô với công thức bạn muốn sao chép hay di chuyển
Bước 3: Nhấn Ctrl + C để sao chép công thức, hoặc là Ctrl + X để di chuyển công thức.
Bước 4: Thực hiện mở Notepad hoặc bất cứ công cụ chỉnh sửa văn bản nào rồi nhấn Ctrl + C để dán công thức vào công cụ đó, tiếp đến nhấn chọn Ctrl + A để chọn tất cả công thức và nhấn Ctrl + C để sao chép chúng.
Bước 5: Trong Excel, bạn chọn ô muốn dán công thức rồi nhấn Ctrl + V.
Xem thêm: Hướng dẫn cách tô màu ô trong Excel
Chú ý:
1. Bạn chỉ được dán công thức tại cùng 1 trang tính, nơi có chứa công thức gốc, trừ khi tham chiếu có gồm tên của trang tính, nếu không thì công thức sẽ bị hỏng.
2. Trang chiếu nên ở chế độ hiển thị công thức. Để kiểm tra được, bạn hãy vào Formulas > Formula Auditing và kiểm tra phần Show Formulas đã có được chọn hay không. Sau khi đã dán công thức, bạn nhấn Ctrl + ` để đóng chế độ hiển thị công thức.
Cách 3. Thực hiện sao chép công thức chính xác bằng việc sử dụng Find and Replace của Excel
Để sao chép một dãy những công thức của Excel mà không làm thay đổi tham chiếu ô, bạn sử dụng công cụ Find and Replace của Excel.
Bước 1: Đầu tiên, bạn chọn ô có công thức bạn muốn sao chép.
Bước 2: Tại thanh Home, bạn vào Editing, nhấn chọn Find & Select > Replace… Hoặc đơn giản hơn, bạn nhấn phím Ctrl + H là đường tắt để mởi hộp thoại Find & Replace trên Excel.
Bước 3: Tại cửa sổ hộp thoại Find & Replace, tiến hành đánh dấu = vào ô Find what. Ở ô Replace with, bạn điền vào một ký hiệu hoặc là dãy ký hiệu không được dùng trong bất kì công thức nào như ~, # hay \.
Mục đích của bước làm này là biến công thức của bạn trở thành dãy văn bản, và ngăn không cho Excel làm thay đổi tham chiếu ô trong suốt quá trình tiến hành sao chép.
Lưu ý. Không dùng dấu hoa thị (*) hoặc là dấu hỏi (?) để thay thế vì những dấu này sẽ khiến các bước sau trở nên khó khăn hơn.
Bước 4: Tiến hành click vào ô Replace All ở hội thoại và đóng cửa sổ Find and Replace. Toàn bộ công thức nằm trong vùng được chọn sẽ biến thành dãy văn bản:
Bước 5: Lúc này, bạn có thể nhấn chọn bất kì ô nào, nhấn chọn Ctrl + C để copy, chọn tiếp ô trên cùng trong trang tính mà bạn đang làm việc, nơi mà bạn muốn dán công thức, sau đó nhấn Ctrl + V. Vì Excel không làm thay đổi công thức không có dấu = phía trước, nên chúng sẽ được sao chép y hệt mà không làm thay đổi tham chiếu.
Bước 6: Sử dụng Find & Replace để tiến hành đảo ngược sự thay đổi này. Nhấn chọn cả vùng dữ liệu copy cùng với vùng dữ liệu dán công thức. Nhấn tổ hợp phím Ctrl + H để mở cửa sổ Find & Replace. Tại ô Find what, bạn hãy điền \ hoặc bất kì ký hiệu nào bạn đã sử dụng ở bước 3. Tại ô Replace with, tiến hành điền vào đó dấu = và click nút Replace All để hoàn thành. Chúc bạn thực hiện thành công!
Xem thêm: Hướng dẫn tạo dãy số ngẫu nhiên trong Excel
