CÁCH SAO CHÉP CÔNG THỨC EXCEL MÀ KHÔNG BỊ THAY ĐỔI THAM CHIẾU Ô

Các hàm trong Excel được sử dụng rất nhiều trong các công việc hàng ngày. Mỗi hàm đều có những công thức khác nhau và sẽ thật mất thời gian nếu chúng ta ngồi đánh từng công thức một vào từng ô. Vì vậy, để tiết kiệm thời gian, bạn có thể sao chép công thức từ 1 ô đã có, không cần mất công nhập lại công thức từ đầu.

Tuy nhiên, bạn đã bao giờ gặp phải tình trạng Excel tự điều chỉnh vùng tham chiếu, ví dụ: Ở ô C1 có công thức =B1*3 nhưng khi sao chép công thức tới ô C2, công thức tự động đổi vùng tham chiếu thành = B2*3 và cho ra kết quả không như bạn mong muốn hay chưa? Ở bài viết dưới đây, Gitiho sẽ hướng dẫn các bạn những cách để sao chép công thức trong Excel không bị thay đổi tham chiếu ô.

Sao chép, di chuyển chính xác một công thức trong Excel mà không bị thay đổi tham chiếu ô

Thao tác sao chép hoặc di chuyển 1 công thức riêng lẻ rất đơn giản. Các công thức có thể được sao chép và dán chính xác, không bị thay đổi tham chiếu vì công thức được sao chép dưới dạng văn bản (chữ). Bạn có thể thực hiện 1 trong 2 cách sau:

Cách 1

• Nhấp chuột vào ô có chứa công thức muốn sao chép
• Bôi đen chọn công thức trên thanh công thức

• Nhấn tổ hợp phím Ctrl +C nếu muốn sao chép công thức; Ctrl + X nếu muốn di chuyển công thức, sau đó nhấn phím Esc để thoát khỏi thanh công thức
• Nhấp chuột vào ô muốn dán công thức và nhấn tổ hợp phím Ctrl + V để dán

Cách 2

• Nhấp chuột vào ô có chứa công thức muốn sao chép
• Nhấn phím F2 hoặc nhấp đúp chuột để sửa dữ liệu trong ô

• Bôi đen chọn công thức có trong ô và nhấn tổ hợp phím Ctrl + C để sao chép công thức
• Nhấp chuột vào ô muốn dán công thức và nhấn tổ hợp phím Ctrl + V.
• Lưu ý: Nếu bạn muốn sao chép công thức không bị thay đổi tham chiếu ô từ những ô phía trên, bạn chỉ cần nhấp chuột vào ô muốn dán công thức, sau đó nhấn tổ hợp phím Ctrl + `

Xem thêm: Hướng dẫn cách copy công thức trong Excel giữ nguyên giá trị

Sao chép chính xác vùng dữ liệu công thức mà không bị thay đổi tham chiếu ô trong Excel

Việc sao chép nhiều dòng có công thức có chứa tham chiếu không còn đơn giản như sao chép 1 công thức riêng lẻ, vì không thể sao chép dưới dạng văn bản được. Tuy nhiên, 3 cách mà Gitiho hướng dẫn dưới đây sẽ giúp bạn dễ dàng thực hiện được thao tác sao chép hoặc di chuyển vùng dữ liệu công thức mà không bị thay đổi tham chiếu ô

Cách 1: Sao chép, di chuyển vùng dữ liệu công thức bằng cách sử dụng tham chiếu tuyệt đối – tham chiếu hỗn hợp

Cách tốt nhất để sao chép chính xác công thức là biến công thức đó từ tham chiếu ô tương đối (A1) sang tham chiếu ô tuyệt đối ($A$1), hay còn gọi là cố định tham chiếu. Với cách này, công thức sẽ được giữ nguyên dù bạn di chuyển hay sao chép công thức vào bất cứ ô nào trong bảng tính. Ngoài ra, bạn cũng có thể dùng tham chiếu hỗn hợp để khóa cột hoặc hàng ($A1 hoặc A$1)

Bạn nghĩ cách này có hiệu quả không? Hãy cùng xem 1 ví dụ cụ thể:

Giả sử bạn muốn quy đổi giá các sản phẩm dưới đây từ đồng Yen (Nhật) sang VNĐ (Việt Nam) với tỉ giá Yen - VNĐ là 212,06. Để quy đổi, ta lấy giá sản phẩm theo đồng Yen nhân với tỉ giá (212,06). Ta có bảng sau:

           

Như vậy, ở sản phẩm đầu tiên (Táo), ta có công thức: = E5*F2 (trong đó: E5 là ô ứng với giá trị 500 Yen của Táo, còn F2 là tỉ giá Yen - VNĐ). Cho kết quả là 106030

Tuy nhiên, nếu chúng ta sao chép bằng cách kéo ô đó xuống để áp dụng công thức với các sản phẩm bên dưới sẽ nhận được kết quả như sau:

Như các bạn có thể thấy, công thức đã bị thay đổi. Thay vì nhân với ô F2 (tỉ giá) thì Excel lại tự động chuyển xuống nhân với ô F3, F4, F5.... Vì vậy đã xảy ra lỗi.

Nhưng nếu chúng ta sử dụng tham chiếu ô tuyệt đối, kết quả sẽ nhận được như sau:

Một ví dụ nhỏ thôi  những cũng đủ để thấy sức mạnh của cách làm này rồi phải không!

Xem thêm: Tác dụng ký hiệu $ (Dollar) trong công thức Excel - Tham chiếu ô tương đối và tuyệt đối là gì?

Cách 2: Sao chép, di chuyển vùng dữ liệu công thức bằng cách sử dụng Notepad 

• Nhấn tổ hợp phím Ctrl + ` để chuyển sang chế độ hiển thị công thức
• Bôi đen chọn tất cả những ô có chứa công thức mà bạn muốn sao chép
• Nhấn tổ hợp phím Ctrl + C để sao chép hoặc  Ctrl + X để di chuyển vùng dữ liệu công thức
• Mở ứng dụng Notepad hoặc các công cụ chỉnh sửa văn bản khác
• Nhấn tổ hợp phím Ctrl + V để dán công thức vào Notepad
• Bôi đen chọn toàn bộ công thức (Ctrl + A) rồi sao chép chúng (Ctrl + C)
• Quay trở lại trang tính Excel, nhấp chuột vào ô muốn dán công thức rồi nhấn tổ hợp phím Ctrl + V
• Nhấn Ctrl + ` để đóng chế độ hiển thị công thức

Lưu ý: Nếu tham chiếu không có tên có trang tính khác thì chỉ có thể dán công thức tại chính trang tính chứa công thức gốc.

Cách 3: Sao chép, di chuyển công thức bằng cách sử dụng chức năng Find and Replace của Excel

Ngoài chức năng tìm kiếm và thay thế trong Excel như các bạn đã biết, chức năng Find and Replace còn có thể dùng để sao chép chính xác dãy công thức có chứa tham chiếu trong Excel. Các bước thực hiện như sau:

• Nhấp chuột vào ô có chứa công thức muốn sao chép
• Mở hộp thoại Find and Replace: Home > Editing > Find & Select > Replace hoặc nhấn tổ hợp phím Ctrl + H
• Tại ô Find what: Nhập dấu “=”
• Tại ô Replace with: Nhập 1 ký hiệu hoặc dãy kí hiệu không sử dụng trong các công thức, ví dụ: ~, \... (trừ dấu hoa thị * và dấu hỏi chấm ? ) nhằm biến công thức đã chọn thành dãy văn bản để Excel không thay đổi tham chiếu ô khi sao chép.

• Nhấp chọn Replace All. Giờ đây, tất cả các công thức đã chọn sẽ biến hết thành dãy văn bản.
   

• Nhấp chọn 1 ô bất kì trong những ô chứa công thức đã chọn, nhấn tổ hợp phím Ctrl + C để sao chép công thức và Ctrl + V để dán công thức vào ô mà bạn muốn dán.
• Lưu ý: Ô dán công thức phải ở cùng trên 1 trang tính chứa công thức gốc
• Bôi đen toàn bộ vùng dữ liệu đã sao chép và vùng dữ liệu đã dán công thức, mở lại hộp thoại Find and Replace.
• Tại ô Find what: Nhập các ký tự mà đã nhập ở bước trước đó
• Tại ô Replace with: nhập dấu “=”
• Nhấp chọn Replace All

Giờ thì công thức của bạn đã được sao chép một cách chính xác mà không làm thay đổi tham chiếu ô rồi đó!

Xem thêm: Cách tìm kiếm dữ liệu Excel bằng công cụ Find & Select và VBA

Tổng kết

Như vậy, chúng ta có những cách sau để có thể sao chép hoặc di chuyển công thức trong Excel mà không làm thay đổi tham chiếu ô:

• Sao chép công thức dưới dạng văn bản từ thanh công thức hoặc nhấp đúp chuột vào ô
• Sử dụng tham chiếu tuyệt đối – tham chiếu hỗn hợp
• Sử dụng Notepad
• Sử dụng hộp thoại Find and Replace trong Excel

Hy vọng bài viết đã giúp các bạn bạn bớt lúng túng trong quá trình sao chép công thức hoặc vùng dữ liệu công thức mà không làm thay đổi tham chiếu ô. Gitiho chúc các bạn có thể vận dụng những cách sao chép này giúp công việc nhanh chóng và dễ dàng hơn. Đừng bỏ qua những cách làm và mẹo thú vị khác trong Excel được chia sẻ trên Gitiho nhé!