Câu hỏi về cách lấy giá trị năm trước trong công thức Excel

Tôi có một sổ làm việc có nhiều trang tính và mỗi trang tính được đặt tên theo năm mà nó tham chiếu. ví dụ: 2020, 2019, 2018, v.v.

Trong mỗi trang tính, tôi có một cột giá trị và một cột khác mà tôi muốn hiển thị sự khác biệt so với cùng một giá trị của năm trước.

Tôi muốn lấy tên trang tính, trừ 1, rồi sử dụng tên đó trong công thức tính chênh lệch. Sau đó, tôi chỉ có thể sao chép trang tính, cập nhật tên trang tính cho năm mới, xóa các giá trị của năm trước và nó sẽ sẵn sàng để sử dụng.

Tôi có thể lấy năm trước với công thức này:

=TEXT(VALUE(RIGHT(CELL("tên tệp")),LEN(CELL("tên tệp"))-SEARCH("]",CELL("tên tệp"))))-1,"0000")

Vì vậy, đối với bảng tính năm 2020, nó sẽ có giá trị là năm 2019.

Nhưng, làm cách nào tôi có thể sử dụng điều này để tham chiếu một ô từ năm trước? Nói cách khác, tôi muốn lấy giá trị từ công thức trên, rồi nối thêm một cái gì đó như "!$C$4" để lấy giá trị từ ô $C$4 từ năm trước để tham chiếu trong trang tính này.

Về cơ bản, tôi muốn một cái gì đó như:

=TEXT(VALUE(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))))-1,"0000")!$C$4

Và, cuối cùng, trong ô $C$5 trên bảng tính năm hiện tại, hãy tính toán như sau:

=$C$4-TEXT(VALUE(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))))-1,"0000") !$C$4

Sau đó, giá trị này sẽ đánh giá, trên bảng tính năm 2020, là: =$C$4-2019!$C$4

Nhưng, điều đó không hiệu quả. Tôi chắc chắn có thể có một cách để làm điều này, nhưng tôi không tìm thấy nó.

Tôi có một số phép tính như thế này trên mỗi trang tính và tôi không muốn phải xem và thay đổi tất cả các công thức mọi lúc.

Có cách nào để tự động lấy tên trang tính hiện tại, trừ đi 1, rồi sử dụng tên đó làm tên trang tính cho một tham chiếu ô trong cùng một công thức đó không?

Cảm ơn bạn,

alan

Câu trả lời:

Sử dụng

=$C$4-INDIRECT(TEXT(VALUE(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))))-1,"0000 ")&"!C4")

Câu trả lời:

Thay đổi công thức thành

=IFERROR(INDIRECT(TEXT(VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))))- 1,"0000")&"!$C$4"),"")

1) Việc chỉ định một ô trên trang tính đang hoạt động trong hàm CELL buộc công thức phải tham chiếu đến trang tính đang hoạt động.

2) IFERROR là một cách hiệu quả hơn để tránh lỗi.