검색결과 리스트
글
방명록에서 어떤 분이 질문을 주셨습니다.
형식입니다. 이럴경우 하나의 시트 셀에서 a1값을 입력하면 모든 시트의 b1값이 합해진 값이 나오게 할 수 있나요? 물론 예를들어 이름이 '홍길동' 이라면 각 시트마다 있는 위치는 다릅니다. 시트마다 a1에 있을수도 있고 a6에 있을수도 있고 그런거죠. 모든 시트에서 해당입력이름의 오른쪽 셀의 값들의 합을 구하고 싶은겁니다. 가능할까요.
질문내용을 다시 설명하면, 아래 그림처럼 sheet1과 sheet2가 있는데
각각 사람 이름과 수량이 적혀있습니다.
이것을 sheet3에서 하나로 합하고 싶다는 것입니다. 사람 이름이 일정한 규칙에 의해 나열된 것이 아니기 때문에 각 시트마다 해당되는 사람의 이름을 찾아서 그 오른쪽 값을 모두 합해야하는 것입니다.
이런 기능을 하는 함수가 SUMIF함수입니다만, 유감스럽게도 여러시트에 걸쳐서 수식을 쓰지는 못합니다.
SUM함수를 쓰는 것처럼 SUMIF함수의 첫번째 인수에 여러시트를 주게 되면 위의 그림처럼 오류가 나게 됩니다.
결론부터 말하면, 함수를 사용하는 방법은 없습니다. 대신에, 원하는 결과를 얻는 다른 방법은 있습니다.
원하는 합계를 표시하고자하는 시트(여기서는 Sheet3)에 그림처럼 머리글을 만들어두고, 첫번째 데이터가 들어갈 부분을 클릭한 상태에서
데이터—데이터도구—통합을 실행합니다.
창이 뜨면, "참조"란의 오른쪽 사각형을 클릭하시고
Sheet1 시트에서 해당 데이터가 있는 영역을 선택하고 Enter를 누릅니다.
참조란에 해당 데이터 영역이 제대로 지정되었는지 확인하고 "추가"를 누릅니다.
"모든참조영역"란에 해당 시트의 범위가 들어왔는지 확인하고, 그 다음 시트의 범위를 선택하기 위해 참조란의 오른쪽에 있는 사각형을 클릭합니다.
아까와 마찬가지로 해당영역을 선택하고 Enter를 누르면 아까와 같은 창이 뜨는데, "추가"를 누르면 "모든 참조영역"에 해당 시트의 범위가 추가됩니다.
이런식으로 원하는 모든 시트를 추가해주고 난 다음, "왼쪽열"과 "원본데이터에 연결"에 체크하고 확인하면
각 사람마다의 수량이 모든 시트에서 계산된 결과가 나옵니다.
이 결과표는 원본데이타와 연동되므로, 수치가 바뀌었다고해서 새로 통합을 실시할 필요가 없습니다. 예를 들어, 위의 그림처럼 sheet1에서 연흥부의 수량이 1에서 100으로 수정되었다고하면,
결과를 보여주는 Sheet3에서의 연흥부의 합계값도 자동으로 반영됩니다.
설정
트랙백
아하! 정말 감사합니다!!
너무 자세히 글까지 작성해 주셔서 정말 감사하내요.
그런데 만약
a1 : 이름 b2 : 숫자 c3 : 이름 d4 : 숫자 이런형식으로
옆으로 이어지고 또 그대로 밑에까지 이어지는것은 합할수가 없을까요...
답변해주신 것을 토대로 셀 영역을 아예 다 선택도 해보고 구분지어서
선택도 해보는등 해보았지만, 위에서처럼 a1,b1 인 두개의 셀만
적용되는것 같내요.
참조에서 sheet1!$a$1:$b$9 이런식으로 서식이 되는대
sheet1!:sheet10$a$1:$b$9 이렇게 여러 시트에서 검색하도록은 불가능한가요?
저는 시도해봐도 되지 않내요.
여러시트를 지정할수만 있어도 작업 할만하겠는대..
이렇게 하다간 한시트에 참조를 10개씩주고 또
시트마다 또줘야해서 곤란하내요.
통합을 완료하면 자꾸 그룹설정이 되고 펼치면 빈 행이 많이 생기는대.
그냥 그룹없이 열 순서대로 합계만 나오게 할 수 있나요?
추가 질문하신 세 가지 모두 VBA를 사용하면 가능합니다. 하지만 그 작업은 통합을 사용하는 방법보다 좀 더 복잡하고, 따라서 불만족스러운 부분이 더 많이 생길 것입니다.
물론 좀 더 연구하여 세밀하게 다듬으면 아쉬운 부분 없이 완벽하게 만들 수도 있을 것입니다. 하지만, 그 작업은 시간이 매우 많이 걸리는 작업입니다.
애초에 원본데이터를 여러시트에 나누어 정리하지 말고 한 시트에 모아두었다면 원하시는 작업을 countif함수로 간단하게 끝낼 수 있었을 일입니다.
작업이 어려워지는 이유는 원본 데이터가 정돈되지 않은 상태일때가 많습니다.