엑셀 시트 연동하기


얼마전 지니님께서 댓글로 엑셀 시트 연동에 대해 문의를 하셨었는데요. 이름 서로 다른 시트마다 해당 셀의 값을 하나의 시트에 옮기는 작업을 "시트목록" 만들기를 이용하여 작업하시고 계셨더라고요.



엑셀 시트 연동하기


예를 들어서 하루씩 일지를 고정되게 작성하는데 data라는 시트에 모두 나타나게 만드는 것입니다.


[불러오기] 버튼을 클릭하면 각 시트에 있던 반입량, 처리량 등 데이터가 data 시트에 모두 날짜별로 나타나게 만들어야 하는 것입니다.


저도 단순히 시트 이름을 목록으로 만드는 방법이 이렇게 응용이 될 수 있을 거라고는 생각하지 못했는데요. 정말 하나가 여러 가지 방법으로 응용이 가능하다는걸 새삼 느꼈습니다.



이 부분을 작업하는데 단 하나로는 불가능하다고 생각해서 각 시트 이름을 불러오는 방법과 이름이 다른 시트에서 동일한 셀의 값을 불러오는 INDIRECT 함수를 이용하여 엑셀 시트를 연동하기로 했습니다.


먼저 "시트 이름 불러오기" 오는 방법은 http://appadal.tistory.com/294 참조하시면 됩니다.


그리고 C7 셀에 '=INDIRECT(B7&"!c6") 라고 함수를 넣어줍니다.


문장을 해석하자면

'1일' 이라는 이름을 가지고 있는 시트에서 c6셀의 값을 넣어라 라는 뜻입니다.


앞에 시트 이름은 앞에 셀에서 불러오게 만들었습니다. 나머지 해당 처리량과 재고량도 같은 방법으로 수식을 넣어줬습니다.


각각 셀에 수식을 넣어준 다음 아래로 드래그를 하면 위에 처럼 해당 시트에 해당하는 값을 모두 불러 올 수 있습니다.


[불러오기]를 클릭하여 시트명 불러옵니다.


그리고 C7부터 H7까지 선택한 다음 아래로 드래그 해줍니다.


그럼 각 시트에 해당되는 셀의 값을 자동으로 가져올 수 있습니다.



댓글로 시작하게 되었지만 이번에 저도 가지고 있던 데이터를 쉽게 정리할 수 있게 되었습니다.

역시 배우는 것에는 끝이 없는거 같습니다. 이상으로 엑셀 시트 연동하는 방법을 알아보았습니다. ^^ 


댓글(10)

  • 2017.02.18 02:46 신고

    엑셀 관련 유용한 팁들 올려주셔서 항상 감사합니다. 오늘도 좋은 하루 되세요^^

  • 지니
    2017.02.20 13:43

    잘 정리해주셨네요~ 다시한번 감사드립니다.

  • 레이준
    2017.04.22 09:54

    덕분에 많이 배웠습니다. 만들어보던중 궁금한것이 있어서 실례가 안된다면 문의드립니다.
    일일업무보고를 쓰면 월업무보고에 시간과 내용이 자동으로 들어가게 만들어보고 있습니다.
    그런데 양식이 일주일단위로 되어야해서 시트를 5일정도씩 나눠서 원하는 곳으로 불러올 수는 없을지요?
    좋은 포스팅 감사드립니다.

    • 2017.04.22 10:52 신고

      저는 오늘 날짜와 시간일 입력할 때 ctrl+;
      키를 눌러서 입력을 합니다. 이렇게 입력을 하게되면 오늘 날짜가 바로 나타납니다.

      셀을 선택하고 ctrl+; 입력하면 2017-04-22 이렇게 나타납니다. 이렇게 안나올 수도 있는데요 이럴 때는 서식에서 형태를 바꿔주면됩니다. 그리고 시간은 ctrl+shift+; 시간이 자동으로 표시됩니다. 저는 이런 방식으로 사용하고 있습니다.

      원하시는 것은 엑셀을 키면 바로 날짜와 시간이 입력 되시는 것을 원하시는 것 같은데요 today 함수를 사용하면 원하시는 것처럼 되지만 문제는 함수를 사용하게 되면 계속 현재 날짜로만 된다는는 문제점이 있습니다.

      어제업무일지도 오늘 날짜로 변경이 되어 버리기 때문에 저는 그냥 단축키를 사용하여 입력하고 있습니다.

      말씀하신 것도 약식을 만들어 놓으면 편할 것 같은데 저도 한번 고민을 해보도록 하겠습니다. ^&^

      기준일을 만들고 하면 될 것 같기도합니다. 고민 고민~

  • 지니
    2018.01.30 13:47

    아빠달님 안녕하세요.
    아빠달님 도움덕에 편하게 업무보고 있습니다. 다시한번 감사드립니다.
    또 도움이 필요해서 연락드렸습니다.
    엑셀 파일이 여러개입니다 365개(일지입니다.^^;;;) 파일안에 4개씩의 시트가 있구요
    그 4개의 시트에서 필요한 데이터(약 30개)를 파일 하나로 가져오고 싶습니다.
    indirect랑 이것저것 해보는데 안되고 검색해보니 vba내용이 나오는데 어렵네요.
    검토 부탁드립니다.

    • 2018.01.30 16:07 신고

      1년치 일지내요.. 파일이 365개라고하면 1개의 파일에 4의 시트가 있다면 아마 각각시트가 서로 다른 작업일지로 생각이 되는데요. 예로 2017.01.30 이라는 파일에 영업일지,운송일지,OO일지,OO일지, 이런 식으로 구성이 되어 있다고 이해를 했는데요.

      우선 이 데이터를 365개의 데이터를 하나로 묶는 것이 먼저 일 것 같습니다. 예를 들어 일지라는 엑셀 파일에 하나의 일지라는 필드를 만들어 4개의 시트에 있는 데이트를 하나로 묶어 버리는 것입니다. 그럼 하나의 파일을 묶어 버리니 필터만으로도 찾을 수 있을 것 같습니다.

      그리고 또 다른 방법은 365개의 파일이 날자 예로 20170203.xls 등 구분되어 있다면 A라는 일지 시트에서 각 파일명을 입력하면 해당 파일을 읽어 A라는 시트에 옮겨오도록 만드는 방법이 있습니다.

      올해도 관리를 하는 것이라면 이번에 잘 구성해서 작업을 하시면 정말 편하실 것 같습니다. 이번주에 위에 셀에 파일 명을 입력하면 파일을 불러오는 포스팅을 한번 해보도록 하겠습니다. 이건 저도 잘만 만들어 놓으면 정말 편리하게 사용할 수 있겠다는 생각이드네요. 저에게도 영감을 주셔서 고맙습니다. 혹 또 궁금하신 것이 있다면 말씀해 주세요~ 저도 열심히 연구해 보겠습니다. ^^

  • 호놀루
    2018.06.04 09:55

    안녕하세요~ 좋은 정보 감사합니다.
    근데 "!oo"은 드래그를 해도 안바뀌는데 하는 방법있나요?ㅠ

Designed by JB FACTORY