Giả sử bạn có một sổ làm việc với ba trang tính, Sheet1, Sheet2 và Sheet3. Trong cột A1 của trang tính Sheet2, bạn có công thức = Sheet1! A1. Khi bạn sao chép công thức đó từ Sheet2 sang ô A1 của Sheet3, công thức vẫn tham chiếu đến Sheet1. Làm thế nào có thể được, mặc dù vậy? Tại sao Excel không điều chỉnh tham chiếu trang tính, giống như tham chiếu ô?

Giống như các phạm vi được đặt tên, Excel coi tên trang tính là tuyệt đối. Mỗi đối tượng trang tính độc lập với tất cả các trang tính khác trong sổ làm việc.

Khi bạn dán công thức bao gồm tham chiếu trang tính, tham chiếu trang tính đó sẽ không thay đổi trong những gì được dán.

Có một số điều bạn có thể làm. Một là chỉ cần sửa đổi tham chiếu công thức sau khi nó được dán để nó tham chiếu đến đúng trang tính. Nếu bạn có nhiều công thức cần thay đổi, thì bạn có thể chọn tất cả các công thức trong trang tính đích (F5 | Special | Formulas) và sau đó sử dụng Find and Replace để thay thế tên trang tính ban đầu (Sheet1)

với tên trang tính chính xác (Sheet2).

Nếu nhu cầu tham chiếu của bạn không phức tạp, thì bạn có thể sử dụng cách tiếp cận macro. Ví dụ: nếu bạn muốn một công thức trong một ô cụ thể tham chiếu đến một ô trên trang tính trước đó đến trang tính hiện tại, thì bạn có thể thực hiện điều đó bằng macro khá dễ dàng. Hãy xem xét macro sau:

Function PrevSheet(rCell As Range)

Application.Volatile     Dim i As Integer     i = rCell.Cells(1).Parent.Index     PrevSheet = Sheets(i - 1).Range(rCell.Address)

End Function

Macro xem xét trang tính hiện tại và sau đó tìm ra trang tính nào nằm trước trang tính đó. Tham chiếu sau đó được thực hiện cho trang tính đó.

Khi bạn đã tạo macro PrevSheet, đây là một cách hàm có thể được sử dụng trong một ô:

=PrevSheet(A1)

Điều này trả về giá trị của ô A1 từ trang tính trước đó. Nếu bạn có Sheet1, Sheet2 và Sheet3 và bạn sử dụng công thức này trên Sheet3, thì nó trả về giá trị của Sheet2! A1. Nếu trang tính trước đó là trang tính đầu tiên của sổ làm việc hoặc nó không phải là trang tính, thì hàm trả về lỗi #Value.

Nếu sau đó bạn sao chép công thức này sang một trang tính khác (ví dụ như Trang tính 5), thì công thức này sẽ kéo giá trị lên so với vị trí mới của nó, có nghĩa là nó kéo giá trị lên từ Trang tính 4! A1.

Bạn cũng có thể bao gồm tên trang tính và chức năng sẽ hoạt động tốt:

=PrevSheet(Sheet3!A5)

Phiên bản này sẽ luôn trả về Sheet2! A5 vì sheet2 là trang trước đó của Sheet3.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3088) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: