Chắc hẳn khi sử dụng Excel, các bạn đã gặp phải trường hợp như sau: Giả sử ô B2 có chứa công thức là =A2+1, bạn thực hiện thao tác sao chép công thức xuống ô bên dưới B3, lúc này công thức hiển thị ở ô B3 sẽ là =A3+1. Nhưng nếu như bạn muốn công thức ở ô B3 khi sao chép vẫn giữ nguyên giá trị tham chiếu là =A2+1 thì sao? Ở bài viết dưới đây, Tin học Trường Tìn sẽ gửi đến bạn đọc cách sao chép công thức mà vẫn giữ được tham chiếu tương đối với dữ liệu là ô đơn lẻ và vùng dữ liệu trong Excel.
Cách sao chép công thức với công thức đơn lẻ
Các thao tác dưới đây sẽ giúp bạn sao chép hoặc di chuyển chỉ một công thức một các dễ dàng
- Nhấn chọn ô chứa công thức mà bạn muốn sao chép
- Bôi đen toàn bộ công thức, sau đó nếu muốn sao chép bạn nhấn Ctrl + C hoặc nhấn chuột phải chọn tính năng Copy, nếu muốn di chuyển bạn nhấn Ctrl + X hoặc chuột phải và chọn tính năng Cut
- Nhấn phím Esc để thoát khỏi thanh công thức.
- Chọn ô bạn muốn dán công thức và nhấn Ctrl + V, hoặc nhấn chuột phải chọn tính năng Paste
Ngoài ra, có một cách khác bạn có thể sử dụng như sau:
- Chọn ô có công thức bạn muốn sao chép
- Nhấn F2 hoặc nháy đúp chuột trái vào ô để có thể sửa nội dung ô
- Sao chép trong ô bằng cách bôi đen và nhấn Ctrl + C
- Chọn ô bạn muốn dán công thức và nhấn Ctrl + V, việc sao chép này không hề làm thay đổi tham chiếu của công thức do mình dán công thức dưới dạng chữ.
Mẹo nhỏ (Tips): Bạn có thể nhấn phím tắt Ctr + ‘ vào ô ngay phía dưới ô chứa công thức, lưu ý chỉ có thể sao chép công thức bằng cách này vào các ô liên tiếp phía bên dưới ô chứa công thức.
Cách sao chép giữ nguyên tham chiếu với một vùng dữ liệu
Khi dữ liệu bạn muốn sao chép là một vùng dữ liệu mà bạn không muốn công thức thay đổi tham chiếu tương đối, hãy làm theo một trong những cách sau đây:
Cách 1. Sử dụng tham chiếu tuyệt đối hay tham chiếu hỗn hợp
Giả sử bạn cần giữ nguyên ô tham chiếu tương đối là A1, cách tốt nhất dành cho bạn là chuyển ô đó thành tham chiếu tuyệt đối là $A$1 để cố định tham chiếu này với ô, như vậy cho dù bạn có di chuyển hay sao chéo công thức đi đâu thì công thức cũng không thay đổi. Ngoài ra, bạn hoàn toàn có thể sử dụng tham chiếu ô hỗn hợp là A$1 hoặc $A1 để khóa cột hoặc hàng. Hãy cùng xem ví dụ sau đây nhé.
Giả sử bạn cần tính toán giá trị của các loại hoa quả trong bảng sang giá trị đồng Euro theo tỷ giá ở ô B2, do đó ô B2 là giá trị tham chiếu tuyệt đối cần được giữ nguyên.
Công thức cần điền sẽ là giá USD ở các ô cột B sau đó nhân với tỷ giá hối đoái cố định (giá trị tham chiếu cố định $B$2). Như vậy, công thức nào cũng đưa ra kết quả chính xác với yếu tố thay đổi là giá USD ở cột B tương ứng và yếu tố cố định là tỷ giá hối đoái B2. Miễn là các công thức cần điền vẫn ở cột C, thì công thức hoàn toàn đúng.
Nhưng nếu bạn sao chép vùng dữ liệu C5:C9 sang cột F thì lúc này công thức không còn đúng nữa, vì công thức lúc này sẽ thay đổi thành E5*$B$2 như hình dưới đây:
Để tránh tình trạng lỗi 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 tuyệt đối B và dòng tương đối 5). Bằng việc thêm ký tự $ trước dòng hay cột cần cố định trong công thức, cho dù có di chuyển tới vị trí nào thì ta cũng có được kết quả chính xác.
Tham chiếu ô trong Excel dường như khá phức tạp và khó khăn khi mới thực hành, tìm hiểu. Nhưng nó xứng đáng để bạn dành thời gian và công sức tìm hiểu chúng, sau khi hiểu được, bạn có thể tính toán toàn bộ trang tính chỉ với duy nhất một công thức sử dụng tham chiếu ô hỗn hợp.
Nhưng, điều gì sẽ xảy ra nếu bạn có rất nhiều công thức với tham chiếu ô tương đối trong trang tính và bạn cần sao chép chính xác những công thức đó nhưng bạn không chắc bạn có thể tham chiếu đúng. Hãy tham khảo những cách sau đây nhé.
Cách 2. Sao chép công thức giữ nguyên tham chiếu tương đối bằng NOTEPAD
Các thao tác sau sẽ giúp ích cho bạn:
- Nhấn Ctrl +` (nút ở góc trái trên cùng, ngay dưới nút Esc) để hiện thị các công thức đã có, hoặc bằng bất cứ phương thức nào trong bài viết Hướng dẫn hiển thị và cách in công thức trong Excel các phiên bản.
- Chọn tất cả các ô chứa công thức bạn muốn sao chép
- Nhấn Ctrl + C để sao chép hoặc Ctr +X để di chuyển
4. Mở NOTEPAD hay Word hay 1 trang chỉnh sửa văn bản bất kỳ và nhấn Ctrl + V để dán công thức vào đó, sau đó chọn Ctrl + A để chọn toàn bộ công thức và nhấn Ctrl + C để sao chép lại công thức.
5. Trong Excel, bạn chọn ô muốn dán công thức và nhấn Ctrl + V
Lưu ý:
- Bạn chỉ có thể dán công thức tại cùng 1 trang tính nơi chứa công thức gốc, trừ khi tham chiếu có bao gồm tên của trang tính, nếu không công thức sẽ bị hỏng.
- Trang chiếu cần ở chế độ hiển thị công thức. 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, nhấn Ctrl + ` (như đã hướng dẫn ở trên) để đóng chế độ hiển thị công thức.
Cách 3. Sao chép công thức chính xác bằng cách dùng FIND AND REPLACE của Excel
- Chọn ô chứa công thức mà bạn muốn sao chép
- Trên tab Home, bạn vào Editing, chọn tính năng Find & Select, sau đó chọn Replace… Hoặc đơn giản hơn, nhấn phím Ctrl + H là phím tắt để mở hộp thoại Find & Replace trên Excel.
3. Trong cửa sổ hộp thoại Find & Replace, đánh dấu = vào ô Find what. Trong ô Replace with, điền một vào ký hiệu hay dãy ký hiệu không được sử dụng trong bất cứ công thức nào như ~, # hay \.
Mục đích của cách làm này là biến công thức của bạn thành văn bản, khi đó công thức sẽ được hiển thị và ngăn không cho Excel thay đổi tham chiếu ô trong quá trình sao chép.
Lưu ý: Bạn không nên dùng dấu (?) hay dấu (*) vì chúng sẽ gây khó khăn cho bạn trong các bước tiếp theo sau.
4. Nhấn lệnh Replace All, và đóng của sổ Find and Replace lại, tất cả công thức trong trang tính sẽ chuyển thành văn bản
5. Bây giờ, bạn có thể chọn bất cứ ô nào để dán công thức. Dùng Ctrl + C để copy công thức sau đó nhấn Ctrl + V để dán công thức vào ô bạn muốn, do Excel không thay đổi công thức không có dấu = phía trước, chúng sẽ được sao chép y hệt mà không thay đổi tham chiếu.
6. Bạn sử dụng tính năng Find and Replace để đảo ngược lại dấu (\) trước công thức thành dấu (=) y như hướng dẫn trên. Vậy là, ta đã có công thức được sao chép y nguyên mà không thay đổi tham chiếu tương đối.
Kết luận
Qua bài viết trên, Tin học Trường Tìn đã cung cấp cho bạn đọc các cách để sao chép công thức giữ nguyên tham chiếu tương đối. Hy vọng bạn có thể kiên nhẫn đọc đến cuối bài viết của chúng tôi để hiểu rõ và thành thạo các thao tác này, hỗ trợ bạn đắc lực trong công việc với Excel. Để có thể làm tốt với công cụ Excel bạn cần tìm hiểu thêm các Bài tập Excel có đáp án (dùng hàm VLOOKUP), hàm IF, AND,… và đừng quên đón chờ các bài viết tiếp theo nhé.
Chúc các bạn ứng dụng thành công!
