나눔터  
  HOME > 나눔터 > 묻고답하기 > 엑셀
엑셀
엑셀에 대한 질문과 답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
 "000 님, 도와주세요", "부탁 드립니다.", "급합니다!" 등과 같이 막연한 제목을 달지 말아주세요.
[필독] 빠르고 정확한 답변을 얻는 16가지 Tip !
[필독] 저작권법 개정에 따른 이용안내
작성자:  

 조삿갓 (choga21)

추천:  2
파일:     해답.zip (43.8KB) 조회:  2737
제목:   [RE]다른 파일에서 데이타 가져오기
     
  * 아래줄에 질문을 작성하세요 >>
원본수식에서는 대분류와 소분류에서 데이터유효성검사로 다른 시트에 있는 데이타를 가져올 수 있는데 그렇게 하면 
폴더를 공유하고 여러직원이 사용하기에 불편한 점이 있어
같은 폴더안에 데이타파일을 두고 양식시트를 복사해서 사용하면 여러직원 사용할 수 있고 데이타를 일괄 관리하기에 편리할 거 같아서 문의합니다.
단가는 데이터파일에서 그대로 불러올 수 있는데
대분류와 소분류는 불러올수가 없네요
해결방법을 알려주시면 감사합니다.
==============[차알돌이님 글에 대한 답변입니다]==============

원칙적으로
데이터유효성검사 목록에서 외부참조를 사용하는 것은
금지되어 있습니다.
다만, 소분류 목록에서 현재 시도하신 것처럼
이름을 정의해 놓고 INDIRECT 함수와 결합하면 가능하긴 한데
이는 공식적인 방법이 아니고, 엑셀이 속는 것 같습니다.

따라서, 두 가지 방법을 생각할 수 있으며
두 가지 방법을 모두 구현해 보았습니다.

방법1: 데이타 파일의 목록을 일지 파일의 별도 시트에
       수식으로 참조하여 가져와서(미러링) 활용하기
       => 일지-A.xlsx에 구현됨

방법2: 이름을 정의하고 INDIRECT 함수로 불러오기
       => 일지-A.xlsm에 구현됨(매크로 포함)


그리고, 이런 문제를 해결하려 할 때 고려해야 할 점은
확장성입니다.

뭐 질문하신 것처럼 실제 대분류명이 AA, AB,... 인 것은
아닐테고... 딱 다섯개 뿐인 것도 아닐테고...
또 각 대분류에 소속된 소분류가 꼭 다섯개씩인 것만도
아닐테고...

그러므로 분류명이 달라지거나 확장되어도 똑같은 원리로
동작하도록 수식이나 코드를 작성해야 하지요.

이러한 관점에서 목록과 단가를 설정하고 관리하는
데이터 파일을 살펴보건대
공통 시트는 문제가 있습니다.

각 대분류 목록에 따른 소분류를 별도로 표를 만든다는 것..
이것은 분류 목록의 갯수가 많아지거나 나중에 변경하려하면
엄청난 삽질을 해야 하지요.

따라서 제 해법에서는 공통 시트는 아예 날려버립니다.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

그 대신,
단가 시트에서 각 분류명에 따른 단가표만 작성하면
목록은 자동으로 구성될 수 있도록 참조표를 설계하였습니다.
H:K 영역의 표가 그것입니다.

A:E 영역에 각 분류별 단가표만 쭈욱 작성해 내려가면
그에 따라 I열에는 대분류 목록이 자동으로 만들어지고
J열과 K열에는 각 대분류에 따른 소분류 목록의
D열에서의 시작 위치(offset)와 개수(RowSize)가 계산됩니다
이 값을 나중에 OFFSET 함수의 인수로 참조하게 되지요.

또 한 가지
제가 추가하거나 수정한 수식은 음영 표시가 되어 있습니다.
그리고, 음영색이 같은 셀들은 동일한 수식을 복사한 것입니다.
즉 예를 들면 A2 셀에 잘 설계해서 입력한 수식을
A3 셀 아래로 쭈욱 복사한 것입니다.
현재 입력된 끝 줄 26행보다 한 행 더 아래까지 복사해 둔 것은
데이터가 추가될 경우 추가된만큼
아래쪽으로 쭉 복사해서 쓰면 된다는 뜻입니다.

물론 질문자께서 원래 설계해 둔 B열의 색인키 역시
아래로 복사 가능하다는 것은 아시지요?

한 가지 주의할 점은 도중에 행을 삽입하는 경우입니다.
A열의 수식은 바로 위 셀을 참조하고 있으므로
행을 삽입할 경우 참조 관계가 흐트러지게 됩니다.

즉, 예를들어 22행에 한 줄을 삽입하여
대분류 AD에 대한 소분류 코드를 하나 더 만든다면
A21의 수식을 A22와 하나 더 지나 A23까지 다시 복사해
주어야 합니다.
왜냐하면, A23 셀의 수식이
=A22+(COUNTIF(C$2:C23,C23)=1)
이어야 하는데, 한 줄 삽입으로 말미암아
A22셀에 있던 수식이 A23 셀로 옮겨지면서
=A21+(COUNTIF(C$2:C23,C23)=1)
로 되어 버리기 때문이지요.

방법1을 구현한 일지-A.xlsx에서는
앞에 설명한대로 분류 목록을 미러링해 오기 위해서
[목록] 시트가 추가되어 있습니다.

그리고 데이터 유효성 검사의 목록 지정은
바로 로컬 문서의 [목록] 시트에서 가져오게 됩니다.

방법1을 사용한다면
데이타.xlsx 파일에 있는 H:K 영역의 참조표는
삭제해도 무방합니다.
목록 시트의 A:D의 참조표는 완전 독립적으로
직접 계산해 오도록 설계했기 때문입니다.

방법1에서는
목록이 변경되거나 추가되는 경우에도
필요한만큼 음영부분의 수식들을 확장만 해주면 됩니다.

방법1에서는
데이타.xlsx 파일이 꼭 동시에 열려있지 않더라도
동일한 폴더에 존재하기만 하면 실시간 참조가 가능하다는 게
원칙적으로는 그렇습니다만
실제로 실험해 본 바에 의하면
동시에 열려있지 않은 경우 [업데이트] 버튼을 눌러도
일부 참조 수식이 오류가 발생하는 현상이 있네요.
안전을 위해서는 동시에 열어 놓고 작업하는 것이 좋겠습니다.

---------------------------

방법2에서는
데이타.xlsx 파일이 꼭 동시에 열려있어야 합니다.
목록을 미러링하지 않고 INDIRECT 함수를 이용한 트릭을 쓰기 때문에
[목록] 시트가 따로 필요하지는 않습니다.
그 대신
목록이 갱신되었을 때 이를 자동으로 반영하지 못하므로
대신 VB 코드를 작성하고 버튼을 배치하였습니다.

우상단에 있는 [목록 재설정] 버튼을 누르면
유효성검사 목록이 데이타.xlsx 파일을 참조하여
재설정됩니다.
 
[불량 게시물 신고]  
        
  

작성일 : 2018-04-07(17:12)
최종수정일 : 2018-04-07(17:12)
 


 ◎ 관련글

  제 목   작성자   날짜
다른 파일에서 데이타 가져오기 차알돌이 2018-04-06
[RE]다른 파일에서 데이타 가져오기 조삿갓 2018-04-07
[RE]다른 파일에서 데이타 가져오기 차알돌이 2018-04-07