Liên kết excel tới sổ làm việc khác không hoạt động

Priyanka là một doanh nhân & chuyên gia tiếp thị nội dung. Cô ấy viết blog về công nghệ và có chuyên môn về MS Office, Excel và các chủ đề công nghệ khác. Nghệ thuật đặc biệt của cô ấy trong việc trình bày thông tin công nghệ bằng ngôn ngữ dễ hiểu rất ấn tượng. Khi không viết lách, cô ấy thích những chuyến du lịch không có kế hoạch

Trong bài đăng này, chúng tôi xem xét cách sử dụng Excel để tham chiếu một sổ làm việc khác mà không cần mở nó. Mặc dù đây có vẻ là một ý tưởng tuyệt vời, nhưng chúng ta cần phải rất cẩn thận. Khi sử dụng kỹ thuật này, rất nhiều lỗi có thể xảy ra mà chúng ta không nhận ra.

Hãy khám phá khu vực này. Vui lòng đọc đến cuối và đảm bảo bạn biết tất cả những nguy hiểm

Tải xuống tệp ví dụ

Tôi khuyên bạn nên tải xuống tệp ví dụ cho bài đăng này. Sau đó, bạn sẽ có thể làm việc cùng với các ví dụ và xem giải pháp đang hoạt động, ngoài ra tệp sẽ hữu ích để tham khảo trong tương lai

Tải tập tin. 0118 Tham khảo sổ làm việc khác. khóa kéo

Tất cả các ví dụ trong bài đăng, chúng tôi đang sử dụng hai sổ làm việc, Đã đóng. xlsx và Mở. xlsx

nội dung

Thay thế để tham chiếu sổ làm việc khác mà không cần mở

Trước khi chúng ta bắt đầu chủ đề chính về cách tham chiếu một sổ làm việc khác mà không cần mở nó, tôi muốn nói với bạn về một phương pháp thay thế

Ở phần sau của bài đăng này, có một danh sách các cạm bẫy và mối nguy hiểm tiềm ẩn khi tham khảo một sổ làm việc khác. Theo tôi, một tùy chọn tốt hơn là sử dụng Power Query

Power Query là một công cụ có thể trích xuất dữ liệu từ sổ làm việc Excel thay vì liên kết với các ô riêng lẻ

Trong hầu hết các trường hợp, Power Query đạt được kết quả tương tự như liên kết ô nhưng tránh được nhiều cạm bẫy. Để tìm hiểu thêm về Power Query, bấm vào đây. Giới thiệu về Power Query

Tuy nhiên, nếu bạn muốn tiếp tục tham chiếu một sổ làm việc khác theo cách cũ, hãy nhấn vào

Cách tham chiếu sổ làm việc khác

Có nhiều cách để thiết lập liên kết đến các sổ làm việc khác. Trong phần này, chúng ta sẽ khám phá ba phương pháp và sự khác biệt giữa việc sử dụng Excel Desktop và Excel Online

Sao chép và dán liên kết

Phương pháp đầu tiên để tạo liên kết đến sổ làm việc bên ngoài bao gồm sao chép và dán

1. Mở cả hai Mở. xlsx và đã đóng. sổ làm việc xlsx
2. Đang đóng. xlsx, sao chép các ô được yêu cầu bằng Home > Copy (hoặc Ctrl + C)
3. trong mở. xlsx, chọn ô cần thiết. Sau đó, dán liên kết vào các ô bằng cách nhấp vào Trang chủ > Dán (thả xuống) > Dán liên kết (hoặc Alt, H, V, N)
4. Thanh công thức hiển thị tham chiếu ô.
   =[Đã đóng. xlsx]Trang tính1. B4

Khi chúng tôi đóng Closed. sổ làm việc xlsx, công thức sẽ thay đổi thành tham chiếu tệp đầy đủ.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. B4

Phương pháp đầu tiên này dễ dàng nhưng yêu cầu cả hai sổ làm việc phải được mở để tạo liên kết ban đầu

liên kết ô

Liên kết ô với một tệp khác giống như liên kết với một ô trong cùng một sổ làm việc

1. Mở cả hai Mở. xlsx và đã đóng. sổ làm việc xlsx
2. Trong mở. xlsx, chọn ô được yêu cầu và nhập ký hiệu bằng (=)
3. Nhấp vào một ô trong Closed. sổ làm việc xlsx. Thanh công thức sẽ trông như thế này.
   =[Đã đóng. xlsx]Trang tính1. $B$4
4. Nhấn quay lại để chấp nhận công thức

Khi đóng Đóng. xlsx, công thức sẽ trở thành một tham chiếu tệp đầy đủ.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. $B$4

Trong Excel Online, mỗi cửa sổ trình duyệt là một phiên bản riêng biệt của ứng dụng Excel. Do đó, không thể liên kết ô giữa các sổ làm việc bằng phương pháp này

Giống như phương pháp đầu tiên, điều này cũng yêu cầu cả hai sổ làm việc phải được mở ban đầu

Viết công thức

Với phương pháp thứ ba này, chúng tôi viết một công thức trực tiếp vào Open. sổ làm việc xlsx và không cần Closed. mở sổ làm việc xlsx

1. Trong mở. sổ làm việc xlsx, chọn ô cần thiết
2. Nhập công thức có cú pháp sau.
   =’filePath[fileName]sheetName’. ôRef
3. Sử dụng các tệp ví dụ của chúng tôi, công thức sẽ là.
   ='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. B4

Nếu bạn không chắc về tên trang tính, hãy nhập bất kỳ thứ gì làm tên trang tính

• Nếu có một trang tính trong sổ làm việc, Excel sẽ sử dụng trang tính đó
• Nếu có nhiều trang tính, Excel sẽ hiển thị danh sách để chọn

Trong ảnh chụp màn hình bên dưới, Unknown đã được nhập làm tên trang tính;

Tế bào liên kết quirks

Có một vài quirks thú vị để làm cho bạn nhận thức được

Tham chiếu tuyệt đối và tương đối

Các kỹ thuật tham khảo tiêu chuẩn được áp dụng

Do đó, nếu chúng ta đang làm việc với công thức sau.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. B4

Sao chép công thức xuống một ô và sang phải một ô sẽ thay đổi tham chiếu thành.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. C5

Tuy nhiên, nếu công thức ban đầu có tham chiếu tuyệt đối, việc chúng ta sao chép công thức ở đâu không quan trọng; .
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. $B$4

Sổ làm việc được lưu trong OneDrive hoặc SharePoint

Nếu đã đóng. sổ làm việc xlsx được lưu trên OneDrive hoặc SharePoint, đường dẫn tệp đầy đủ sẽ tham chiếu đến vị trí trực tuyến.
=’https. // exceloffthegrid-my. điểm chia sẻ. com/personal/mark/Documents/Documents/[Đã đóng. xlsx]Trang tính1′. B4

Ngay cả khi tệp tồn tại cục bộ, nó luôn trở lại đường dẫn tệp trực tuyến

Mảng động

Tham chiếu ô được liên kết sử dụng công cụ mảng động. Do đó, nếu chúng tôi cung cấp một phạm vi ô, các giá trị sẽ tràn vào các hàng và cột tiếp theo.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. B 4. E11

Tập tin được bảo vệ bằng mật khẩu

Nếu liên kết với tệp được bảo vệ bằng mật khẩu, Excel sẽ yêu cầu mật khẩu để cập nhật liên kết

Phạm vi được đặt tên

Có thể liên kết đến các phạm vi được đặt tên thay vì phạm vi ô

Các liên kết sau đến một dải ô được đặt tên gọi là myNameRange.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. myNamedRange

Đang cập nhật liên kết

Sau khi tạo một liên kết, câu hỏi tiếp theo là làm thế nào để cập nhật nó. Như với hầu hết mọi thứ trong Excel, có nhiều cách

Cập nhật tự động

Nếu cả Open. xlsx và đã đóng. sổ làm việc xlsx được mở đồng thời, các giá trị tự động cập nhật khi tính toán lại xảy ra

Mở tệp

Thông báo sau sẽ được hiển thị khi mở tệp Excel có chứa liên kết đến các sổ làm việc khác

Nhấp vào Cập nhật sẽ cập nhật tất cả các liên kết trong sổ làm việc, trong khi Không cập nhật sẽ không truy xuất các giá trị được liên kết

Cửa sổ Chỉnh sửa Liên kết

Để cập nhật bất kỳ liên kết hiện có

• Từ Ribbon, nhấp vào Dữ liệu > Chỉnh sửa Liên kết
• Hộp thoại Chỉnh sửa Liên kết mở ra. Cửa sổ này cũng có thể được tìm thấy trong File > Info > Edit Links to Files
• Chọn từng liên kết và nhấp vào Cập nhật giá trị

Chúng tôi cũng sử dụng hộp thoại Chỉnh sửa Liên kết để thay đổi bất kỳ liên kết hiện có nào

Cập nhật ô đơn

Một ô duy nhất có thể được cập nhật bằng cách vào và rời khỏi chế độ chỉnh sửa. Một số phương pháp để đạt được điều này là

• Bấm đúp vào một ô để vào chế độ chỉnh sửa, sau đó bấm vào một ô khác để rời khỏi
• Nhấn F2 để vào chế độ chỉnh sửa và nhấn enter để thoát
• Bấm vào một ô, bấm vào bên trong thanh công thức để vào chế độ chỉnh sửa và bấm đi để thoát

tính toán lại

Liên kết tới sổ làm việc đã đóng được cập nhật khi ô chứa liên kết yêu cầu tính toán lại. Chuỗi tính toán trong Excel là một khái niệm phức tạp. Nói chung, một ô chỉ tính toán lại nếu nó bao gồm một tham chiếu đến một ô hoặc ô trước đó đã thay đổi

Liên kết sau đây sẽ không bao giờ cập nhật thông qua phép tính lại tiêu chuẩn vì liên kết này không có ô đứng trước trong cùng một sổ làm việc.
='C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. A1

Tuy nhiên, liên kết sau sẽ tính toán lại mỗi khi Ô B1 (hoặc ô trước B1) thay đổi.
=IF(B1=1,=’C. \Users\marks\Documents[Đã đóng. xlsx]Trang tính1′. A1,"Không làm gì cả")

Tính toán với các ô được liên kết

Trong phần trước, chúng ta đã thấy một hàm IF với một ô được liên kết bao gồm trong đối số true. Nhưng chúng ta có thể đẩy mạnh điều này hơn nữa và sử dụng tệp được liên kết trong các công thức và hàm khác

Phần sau đây hiển thị hàm XLOOKUP với các đối số lookup_array và return_array tham chiếu các ô trong một sổ làm việc khác

=XLOOKUP(D3,
‘C. \Users\marks\Documents\[Đã đóng. xlsx]Trang tính1’. $B$4. $B$11,
‘C. \Users\marks\Documents\[Đã đóng. xlsx]Trang tính1’. $E$4. $E$11)

Hãy thử nó cho chính mình;

Nhiều hàm khác xử lý mảng cũng hoạt động với tham chiếu sổ làm việc bên ngoài. Tuy nhiên, các hàm yêu cầu một phạm vi, chẳng hạn như SUMIFS, không hoạt động với các tham chiếu ô bên ngoài

Sự nguy hiểm của sổ làm việc được liên kết?

Như đã đề cập trong phần giới thiệu, có một số vấn đề nghiêm trọng khi tham chiếu một sổ làm việc khác

Tôi đã nghe nhiều người nói. “Hãy nhìn xem, tôi đã liên kết với sổ làm việc này để không phải cập nhật một ô. cái này có tuyệt không. ”. Nhưng, thật không may, sự phấn khích sớm tắt khi họ nhận ra những rủi ro

Đây là những cái bẫy mà nhiều người dùng Excel rơi vào

Dữ liệu được liên kết thay đổi vị trí

Nếu một ô được tham chiếu trong sổ làm việc của chính nó, Excel sẽ theo dõi điều gì xảy ra với ô đó. Ví dụ: nếu Ô A1 được kéo đến Ô B1, bất kỳ ô nào tham chiếu đến A1 sẽ tự động cập nhật thành B1. Hoàn hảo

Nhưng đây không phải là điều xảy ra khi chúng ta tham khảo một sổ làm việc Excel khác

Nếu một sổ làm việc bị đóng, nó sẽ không biết khi nào một ô trong sổ làm việc khác di chuyển. Ví dụ

• Khi mở ra. xlsx đã đóng
• Đang đóng. xlsx, Ô A1 được kéo đến Ô B1
• Mở. xlsx tiếp tục liên kết với Ô A1 và sẽ không thay đổi. Nó không biết rằng A1 bây giờ là B1
• Mở. xlsx hiện đang liên kết đến sai ô – rất tiếc

Rủi ro có thể được giảm nhẹ bằng cách sử dụng Phạm vi được đặt tên. Tuy nhiên, nếu đó là tệp của đồng nghiệp, điều gì sẽ ngăn họ xóa Dãy đã đặt tên hoặc đổi tên trang tính, chỉ vì họ muốn (đồng nghiệp làm việc tốt như vậy. )

Do đó, việc liên kết đến bất kỳ tệp nào bạn không kiểm soát chặt chẽ là rất nguy hiểm

Các liên kết phụ không được cập nhật

Khi một số người dùng Excel phát hiện ra khả năng liên kết với các sổ làm việc khác, họ sẽ hơi bối rối; . Không lâu trước khi họ có một mạng lưới liên kết trên nhiều sổ làm việc. Tuy nhiên, họ không nhận ra rằng khi một liên kết cập nhật, nó không cập nhật bất kỳ liên kết phụ nào có trong tệp được liên kết

Hãy xem một ví dụ. Hãy gợi ý rằng

• Mở. xlsx chứa một liên kết đến Closed. xlsx
• đóng cửa. xlsx chứa một liên kết đến AnotherClosed. xlsx
• Khi mở Mở. xlsx, hộp thông báo Cập nhật liên kết xuất hiện
• Nhấp vào Cập nhật sẽ mang lại các giá trị từ Đã đóng. xlsx thành Mở. xlsx
• Không có gì xảy ra với các liên kết đến AnotherClosed. xlsx. Điều này là do các liên kết này không được cập nhật. Do đó, các giá trị cuối cùng trong Open. xlsx có thể không đúng
• Để tất cả các liên kết cập nhật chính xác, tất cả các tệp phải được mở đồng thời

Dữ liệu từ phiên bản đã lưu gần đây nhất

Các giá trị chỉ được truy xuất từ ​​phiên bản đã lưu cuối cùng của sổ làm việc.  

Hãy xem một ví dụ. Hãy gợi ý rằng

• Đồng nghiệp của bạn mở Đóng. xlsx và thay đổi một số giá trị nhưng để sổ làm việc mở
• Bạn đang làm việc tại Open. xlsx và đã cập nhật các liên kết
• Nếu đóng. xlsx được lưu cục bộ, các giá trị được liên kết có sẵn sẽ vẫn là giá trị cũ cho đến khi Đóng. xlsx đã được lưu
• Nếu đóng. xlsx được lưu trên OneDrive hoặc SharePoint, các giá trị mới sẽ sẵn dùng ngay sau khi tệp được đồng bộ hóa trở lại máy chủ

Bản thân nó không phải là vấn đề. Tuy nhiên, không có thông báo cảnh báo nào cho biết Đã đóng. xlsx hiện đang mở, vì vậy có thể không rõ liệu tệp có được cập nhật hay không

Excel lưu trữ dữ liệu bên ngoài

Sổ làm việc Excel là một tệp nén được xây dựng theo một cách cụ thể để ứng dụng Excel đọc được. Khi một liên kết được tạo, Mở. xlsx giữ một bản sao dữ liệu từ Closed. xlsx

Chúng tôi sẽ xem xét định dạng tệp Excel sau trong bài đăng, nhưng bây giờ, hãy chứng minh rằng Open. xlsx lưu trữ dữ liệu

• Tạo XLOOKUP (hoặc VLOOKUP nếu bạn thích) giữa Mở. xlsx và đã đóng. sổ làm việc xlsx
• Lưu cả hai tệp và đóng Closed. sổ làm việc xlsx
• Đổi tên Đã đóng. xlsx thành Closed_Backup. xlsx

Các công thức trong Open. xlsx không thay đổi; . tệp xlsx không tồn tại

Câu hỏi. Điều gì xảy ra nếu chúng ta thay đổi giá trị tra cứu?

Câu trả lời. Nó sẽ gây ra lỗi… phải không?. Không. Một giá trị được trả về

Hừm…. điều đó không nên làm việc, nhưng nó làm. Điều này có nghĩa là bạn có thể tiếp tục truy xuất các giá trị từ các tệp không còn tồn tại. Đó là vì Excel giữ một bản sao được lưu trong bộ nhớ cache của tất cả dữ liệu được liên kết

Trong khi làm việc với Excel, bạn có thể thấy thông báo này

“Chúng tôi không thể cập nhật một số liên kết trong sổ làm việc của bạn ngay bây giờ.
Bạn có thể tiếp tục mà không cần cập nhật giá trị của chúng hoặc chỉnh sửa các liên kết mà bạn cho là sai. ”

Nếu bạn nhấp vào Tiếp tục, các phép tính sẽ trả về các giá trị từ tệp nguồn được lưu trong bộ nhớ cache

Nếu Excel lưu trữ dữ liệu bên ngoài vào bộ đệm ẩn theo cách này, điều đó có nghĩa là

1. Bạn có thể đang sử dụng dữ liệu cũ mà không hề hay biết – đó là một rủi ro lớn
2. Bất kỳ người dùng nào nhận được tệp đều có thể truy cập bộ đệm và xem dữ liệu bí mật mà bạn không biết là có ở đó – đó có thể là rủi ro lớn hơn

Dữ liệu được lưu trữ ở đâu?

Vì vậy, bộ đệm dữ liệu mà tôi tiếp tục đề cập đến ở đâu?

1. Tạo liên kết giữa Closed. xlsx và Mở. xlsx
2. Lưu và đóng Mở. xlsx
3. Đổi tên Mở. xlsx để mở. khóa kéo
4. Mở tệp zip và tìm \xl\externalLinks\externalLink1. tập tin xml
5. Các tập tin sẽ trông giống như thế này
   

Trong ảnh chụp màn hình ở trên, các giá trị trang tính đã được đánh dấu. Tất cả dữ liệu bên ngoài từ Bản gốc đã đóng. sổ làm việc xlsx được bao gồm. Do đó, chúng tôi có thể dễ dàng vô tình gửi thông tin và làm tăng kích thước tệp

Để dừng Excel lưu dữ liệu vào bộ nhớ đệm theo cách này, hãy nhấp vào Tệp > Tùy chọn để mở hộp thoại Tùy chọn Excel. Sau đó, bỏ chọn lưu các giá trị liên kết bên ngoài trong phần Nâng cao

Khi nào thì được phép liên kết các tệp?

Như bạn đã thấy, việc liên kết các tệp không đơn giản như bạn nghĩ. Excel không phải lúc nào cũng hoạt động theo cách chúng ta có thể muốn. Vì vậy, khi nào thì có thể liên kết các tập tin?

• Tôi phải 'kiểm soát' tất cả các tệp liên quan
• Không được có liên kết phụ nào
• Phạm vi được đặt tên phải được sử dụng, không phải tham chiếu ô tiêu chuẩn
• Nếu chia sẻ tệp, nó không được chứa dữ liệu nhạy cảm

Các quy tắc này tránh được hầu hết các vấn đề tiềm ẩn

Như đã lưu ý khi bắt đầu; . Truy cập vào đây để tìm hiểu thêm về Power Query. Giới thiệu về Power Query

Phần kết luận

Liên kết các tập tin không dành cho những người yếu tim. Điều tưởng chừng như là một giải pháp đơn giản, lại mở ra cả một thế giới phức tạp. Mặc dù dễ dàng nhưng bạn cần đảm bảo dữ liệu chính xác và bạn không chia sẻ thông tin một cách bất ngờ

---

Giới thiệu về tác giả

Xin chào, tôi là Mark và tôi chạy Excel Off The Grid

Cha mẹ tôi nói với tôi rằng năm 7 tuổi, tôi đã tuyên bố rằng mình sẽ trở thành một kế toán viên có trình độ. Tôi hoặc là nhà ngoại cảm hoặc không có trí tưởng tượng, vì đó chính xác là những gì đã xảy ra. Tuy nhiên, phải đến năm 35 tuổi, hành trình của tôi mới thực sự bắt đầu

Vào năm 2015, tôi bắt đầu một công việc mới mà tôi thường xuyên làm việc sau 10 giờ tối. Kết quả là tôi hiếm khi gặp con trong tuần. Vì vậy, tôi bắt đầu tìm kiếm những bí quyết để tự động hóa Excel. Tôi phát hiện ra rằng bằng cách xây dựng một số lượng nhỏ các công cụ đơn giản, tôi có thể kết hợp chúng lại với nhau theo những cách khác nhau để tự động hóa gần như tất cả các tác vụ thông thường của mình. Điều này có nghĩa là tôi có thể làm việc ít giờ hơn (và tôi được tăng lương. ). Hôm nay, tôi dạy những kỹ thuật này cho các chuyên gia khác trong chương trình đào tạo của chúng tôi để họ cũng có thể dành ít thời gian hơn cho công việc (và có nhiều thời gian hơn cho con cái và làm những việc họ yêu thích)

---

Bạn có cần trợ giúp để điều chỉnh bài đăng này theo nhu cầu của mình không?

Tôi đoán các ví dụ trong bài đăng này không khớp chính xác với tình huống của bạn. Tất cả chúng ta đều sử dụng Excel khác nhau, vì vậy không thể viết một bài đáp ứng nhu cầu của mọi người. Bằng cách dành thời gian để hiểu các kỹ thuật và nguyên tắc trong bài đăng này (và những nơi khác trên trang web này), bạn sẽ có thể điều chỉnh nó theo nhu cầu của mình

Tại sao các liên kết của tôi trong Excel không hoạt động?

Tại sao Excel không cho phép tôi tham khảo trang tính khác?

Tại sao các liên kết của tôi không cập nhật trong Excel?