Câu hỏi Có thể Excel cập nhật tài liệu tham khảo (giống như nó có thể với ô) khi tôi dán nội dung của một trang tính khác?


Tôi có một tài liệu mà tôi tạo một trang tính mới đại diện cho ngày (phải theo cách đó). Mỗi trang tính có tham chiếu đến cùng một ô trong trang tính đại diện cho ngày trước đó. Vì vậy, trang tính "Thứ ba" kéo một số giá trị từ trang tính "Thứ hai" và cứ tiếp tục như vậy. Công thức là:

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33

và kết quả tôi thấy là một cái gì đó như Saldo from 1 June 2016, Amount $55.00

Nếu tôi tạo một trang tính mới và đặt tên cho nó Day05 và dán nội dung của trang tính Day04, có cách nào để cập nhật công thức tham chiếu Day03 đến Day04? Ngay bây giờ, nội dung vẫn lấy dữ liệu từ trang tính Day03.

Tóm lại, khi tôi sao chép nội dung của trang tính Day04, Tôi muốn xem cập nhật công thức để:

            Description                              Amount

="Saldo from "&TEXT($'Day04'.C4,"DD.MM.YYYY.")        =$'Day04'.F33

Ngay bây giờ, tham chiếu tên trang tính không cập nhật và Day05 trang tính vẫn lấy dữ liệu từ Day03

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33 //<-- WRONG is 03 ref!

1
2017-07-08 12:18


gốc


Điều này thực sự là không thể? - JoeM
Bạn sử dụng phiên bản Excel nào? Câu trả lời phụ thuộc vào điều đó - Máté Juhász
Tôi đang sử dụng Excel 2010 - JoeM
Xem ĐÂYlàm thế nào để có được tên sheet của ô, sau đó sử dụng Right hoặc Mid để lấy số và thêm 1 rồi nối nó lại với nhau và sử dụng hàm INDIRECT (). - Scott Craner
Bạn đang tham khảo các tế bào với một cái gì đó như $ 'Day03'.C4 nhưng tôi chỉ biết về một cái gì đó như Day03! C4. Có phải là ngôn ngữ của bạn khiến bạn sử dụng ký hiệu đô la ($) và dấu chấm (.)? - Eduardo Poço


Các câu trả lời:


Vấn đề là tạo một trang tính mới, sao chép nội dung của trang tính hiện có và thay đổi tham chiếu đến cho các ô thành một trang tính thứ hai hiện có. Như Satya Mishra nói rằng, có thể được thực hiện bằng tay bằng cách thay đổi 'Day03'! đến 'Day04'! sau khi sao chép sang một trang tính mới, Day05.

Để thực hiện thay đổi tự động khi bạn sao chép từ trang tính trước sang trang tính mới, hãy giải quyết sự cố: Tạo trang tính mới và sao chép nội dung của trang tính trước đó. Sử dụng tên của trang tính mới để tạo tham chiếu đến trang tính đã đặt trước đó.

Đây là công thức trong trang tính hiện tại nhận nội dung ô F33 từ trang tính đã được đặt trước đó. Ví dụ: nếu trang tính hiện tại là Day05, công thức được F33 từ trang tính Day04.

= INDIRECT (CONCATENATE ("Ngày", TEXT (MID (CELL ("tên tệp", $ A $ 1), TÌM ("]", CELL ("tên tệp", $ A $ 1)) + 4,255) -1, "0 # ")," '! "," F33 "))

Cách thức hoạt động của công thức

Công thức sử dụng INDIRECT để lấy giá trị ô từ trang tính trước. Nếu trang tính hiện tại là Day05 và chúng tôi muốn di động F33, INDIRECT cần một chuỗi tham chiếu ô F33 trong trang tính Day04. Chuỗi chúng ta cần là 'Day04'!F33.

  1. Lấy tên trang tính cho trang tính hiện tại.
Ngày05 = MID (CELL ("tên tệp", $ A $ 1), FIND ("]", CELL ("tên tệp", $ A $ 1)) + 1,255)
  1. Sửa đổi công thức để lấy số ở cuối tên trang tính bằng cách thay đổi +1 thành +4. Điều này bỏ qua 3 ký tự "Ngày".
05 = MID (CELL ("tên tệp", $ A $ 1), FIND ("]", CELL ("tên tệp", $ A $ 1)) + 4.255)
  1. Trừ 1 để lấy phần số của trang tính trước.
4 = MID (CELL ("tên tệp", $ A $ 1), TÌM ("]", CELL ("tên tệp", $ A $ 1)) + 4,255) -1
  1. Sử dụng TEXT để đệm số bằng số 0 đứng đầu.
04 = TEXT (MID (CELL ("tên tệp", $ A $ 1), TÌM ("]", CELL ("tên tệp", $ A $ 1)) + 4,255) -1, "0 #")
  1. Sử dụng CONCATENATE để tạo tên trang tính mới có tham chiếu đến ô F33.
'Day04'! F33 = CONCATENATE ("Ngày", TEXT (MID (CELL ("tên tệp", $ A $ 1), TÌM ("]", CELL ("tên tệp", $ A $ 1)) + 4,255) -1 , "0 #"), "'!", "F33")

Sử dụng công thức

Bất cứ khi nào bạn muốn một giá trị ô từ trang tính trước, hãy sử dụng công thức thay cho tham chiếu ô và thay đổi chuỗi cuối cùng trong công thức thành tham chiếu ô. Ví dụ: để sử dụng ô C4để hiển thị Mô tả (thông báo C4 trong công thức):

= "Saldo from" & TEXT (INDIRECT (CONCATENATE ("Day", TEXT (MID (CELL ("tên tệp", $ A $ 1), FIND ("]", CELL ("tên tệp", $ A $ 1)) + 4.255 ) -1, "0 #"), "'!", "C4")), "DD.MM.YYYY.")

Kết quả là Saldo from 01.06.2016.

Điều kiện và cảnh báo

  • Mỗi tên trang tính phải kết thúc bằng hai số, ví dụ: Day02, Day03, v.v.
  • Công thức sẽ không hoạt động cho đến khi trang tính mới có tên chính xác của nó. Một nguyên nhân tên tờ sai #VALUE! lỗi.

2
2017-07-19 19:09





Tôi đã không tìm thấy bất kỳ cách nào để thực hiện việc này một cách tự động. Nhưng "Tìm kiếm và Thay thế" (Ctrl + H) có vẻ hoạt động.


0
2017-07-18 14:44





Tôi tìm thấy giải pháp nhờ nhận xét của Scott Craner. Tôi cảnh báo bạn nó sẽ là một công thức dài.

Ở khắp mọi nơi bạn có 'DayXX' với số XX bạn có thể thay đổi te XX thành

&MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("y";MID(CE("filename",A1),FIND("]";CEL("filename";A1))+1,255))+1,2)-1

Điều này tìm thấy tên tập tin và tìm kiếm y từ ngày và trích xuất các nummer từ tên tập tin và trừ 1 từ nó. Tôi có thể giải thích thêm nếu bạn muốn, chỉ cần thêm một bình luận.


0
2017-07-19 06:46





Excel điều chỉnh các tham chiếu đến một trang tính khi chúng ta thay đổi tên của trang tính này. Tất cả các tham chiếu được đổi tên tự động. Hãy cố gắng giải quyết vấn đề với tính năng này: thay vì đổi tên các tham chiếu, chúng tôi sẽ đổi tên trang tính.

  1. Mở sổ làm việc của bạn với các tờ ngày.
  2. Tạo trang tính có dữ liệu cho ngày mới (ví dụ: "Day05") như bạn đã làm trước đây. Cuối cùng, sao chép các công thức từ bảng Day04, bất kể chúng vẫn còn là gì đến Day03 sau khi sao chép và lưu nó. Đừng đóng sổ làm việc!
  3. Mở một sổ làm việc khác, có thể trống, để giữ tham chiếu tạm thời và di chuyển bảng Day05 (để làm điều này, bấm chuột phải vào Day05, chọn 'Di chuyển hoặc Sao chép ...' và chọn sổ làm việc khác làm số phận).
  4. Xóa trang tính "Day04" và thay đổi tên của sheet "Day03" thành "Day04". Đây là phần khó khăn!

Bây giờ bạn có thể thấy rằng tờ "Day05", trong sổ làm việc mới, sẽ có tham chiếu của nó được đặt thành Day04 (từ sổ làm việc đầu tiên), thay vì Day03, do đổi tên. Tiếp theo, hãy di chuyển trang tính này trở lại bảng tính gốc.

  1. Đóng sổ làm việc ban đầu, loại bỏ các thay đổi được thực hiện sau lần lưu cuối cùng.
  2. Mở lại sổ làm việc một lần nữa và di chuyển trở lại trang tính Day05 nằm trong sổ làm việc tạm thời. Các tham chiếu vẫn còn đến Day04 (từ sổ làm việc hiện tại), bởi vì chúng tôi đã loại bỏ các thay đổi thay vì đổi tên lại trang tính.

Voilà! Đây là một mẹo để làm cho Excel đổi tên các tham chiếu cho chúng ta.

Nó có vẻ không có vẻ dễ dàng để làm trong nháy mắt đầu tiên, nhưng tôi nghĩ rằng nó là dễ dàng hơn để đi với hơn bằng cách sử dụng công thức hoặc VBA, bởi vì bạn có thể kiểm soát các bước mà không cần kiến ​​thức lập trình. Ngoài ra, nó dễ dàng hơn đổi tên mỗi tham chiếu.

Hy vọng nó giúp!

CHỈNH SỬA 1

Tất nhiên, bạn có thể ghi lại một macro với hoạt động này, và thậm chí thích ứng với nó, để có thể gọi nó nhanh hơn.


0
2017-07-20 00:11