☰

서로 다른 두 통합 문서의 Excel 오프셋, 다른 통합 문서에 영향을 주지 않고 하나의 복사본 만들기

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
서로 다른 두 통합 문서의 Excel 오프셋, 다른 통합 문서에 영향을 주지 않고 하나의 복사본 만들기

저는 두 개의 서로 다른 통합 문서를 사용하고 있습니다. 첫 번째 통합 문서는 데이터베이스로 생성되었으며 두 번째 통합 문서는 송장 생성을 위해 생성되었습니다.

송장을 생성할 때 오프셋을 사용하여 고객 위치 목록 등을 읽습니다.

=OFFSET('[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

그리고 그것은 올바르게 작동하고 있습니다.

문제는 오프셋이 다음과 같이 변경되는 데스크탑에서 예를 들어 다른 경로를 갖는 통합 문서 [송장 템플릿]의 복사본을 생성하려는 경우입니다.

=OFFSET('C:\Users\anthony\Desktop\[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

그래서 [Excel 데이터 전용] 통합 문서의 경로가 자동으로 변경되었으며 다른 경로가 아닌 [송장 템플릿]의 경로만 변경했기 때문에 더 이상 접근할 수 없습니다.

그렇다면 [송장 템플릿]의 경로를 변경할 때 [Excel 데이터만]의 경로에 영향을 주지 않도록 이 수식을 어떻게 관리해야 할까요?

도와 줘서 고맙다!

답변1

외부 참조가 있는 Excel 파일을 열 때(그리고 보안 경고가 표시되면 업데이트를 수행하도록 허용할 때) Excel은 참조를 해결하기 위해 일부 자동 기능을 수행하는 것으로 보입니다. 소스와 대상을 모두 이동하는 경우, 특히 드라이브 문자를 변경하는 경우(아마도 플로피 시대의 유물일까요?) 에 최적화된 것 같습니다 .

불행하게도 대상 파일( Invoice Template.xlsx예제에서는)만 이동하면 문제가 발생할 수 있습니다.

아마도 문제를 해결하는 가장 빠른 방법은 -Ribbon edit links에서 찾을 수 있는 대화를 이용하는 것입니다 Data. 여기에는 모든 외부 파일의 테이블이 있으며 해당 파일이 가리키는 위치( change source)를 변경할 수 있습니다. 경로를 수정하면 Excel Data Only.xlsx스프레드시트에서 이 파일에 대한 모든 참조가 즉시 업데이트됩니다.

답변2

일괄적으로 업데이트해야 하는 엄청난 양의 파일에서 문제가 발생하는 경우(또는 단지 모험심을 느끼고 싶다면 먼저 백업하는 것을 잊지 마세요!) 원시 파일을 편집해 볼 수 있습니다 *.xlsx. 실제로는 zip 아카이브입니다.

아카이브 탐색기로 열면(7-zip예를 들어) 하위 디렉토리에서 외부 참조를 찾을 수 있습니다 \xl\externalLinks\_rels\. 아마도 이름이 지정된 파일이 있을 것입니다 externalLink1.xml.rels(번호는 다를 수 있음). (상대) 파일 경로와 이름을 포함하는 XML 파일입니다. 거기서 업데이트하시면 됩니다. 다음과 같이 입력됩니다..../externalLinkPath파일 위치를 기준으로 표시합니다. 해당 유형을 다음으로 변경하면 http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing문제가 해결될 수도 있습니다.

externalLink1.xml.rels는 해당 경로를 구체적으로 테스트하지 않은 것처럼 보일 수 있습니다 .

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
      <Relationship 
          Id="rId1" 
          Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing"
          Target="'C:\Users\anthony\Desktop\channels%20(BE%20VERY%20VERY%20PRECAUTIOUS%20WITH%20THIS%20FILE)!!!!\0000%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx "
          TargetMode="External"
      />
 </Relationships>

추신: Excel 파일이나 경로에 특수 문자(대괄호, 느낌표 등)를 사용하지 않는 것이 좋습니다. 그렇게 하면 문제가 발생할 수도 있습니다.

관련 정보