[Excel] 엑셀에서 다중 시트 일괄 계산 방법

 정보

• 업무명     : 엑셀로 다중 시트를 일괄 계산 방법 소개

• 작성자     : 박진만

• 작성일     : 2019-12-29

• 설   명      :

• 수정이력 :

 

 내용

[특징]

• 동일하거나 비슷한 서식을 내포하고 있는 "다중시트" 가 존재하고, 이들 시트간에 계산 방법을 소개한다.

 

[기능]

• 다중 시트 계산 방법

  • 전체 시트 형식이 동일한 경우

  • 일부 시트의 형식이 다른 경우

• 요점

 

[활용 자료]

• 없음

 

[자료 처리 방안 및 활용 분석 기법]

• 없음

 

[사용법]

• 없음

 

[사용 OS]

• Window 10

 

[사용 언어]

• Excel v2010

 

 세부 내용

• 셀에 입력한 합계를 구하는 경우 일반적으로 "SUM" 함수를 사용한다.

• 이 경우 동일한 시트에서 합계를 구하는 것은 간단한 작업이다. 그러나 다중 시트에 대한 합계는 복잡한 절차를 요구한다.

  • 일반적으로 이것을 엑셀에서 "3차원 계산" 이라고 칭한다.  (아래 이미지 참조)

 

 

• 그러나 계산 대상 셀의 위치가 전체 시트에서 동일한 경우 간단하게 수행할 수 있다.

• 이 포스팅에서는 "모든 시트에 형식이 동일한 경우" 와 "일부 다른 형식의 시트가 존재하는 경우" 로 구분하여 "3차원 계산"을 할 수 있는 방법을 소개하고자 한다.

 

[전체 시트의 형식이 동일한 경우에 다중시트 계산법]

• 일반적으로 "3차원 계산"의 경우 전체 시트의 형식이 같다고 가정한다. (계산 결과를 표시하는 시트 포함)

• 즉 아래의 그림과 같이 어떤 시트도 같은 형식으로되어 있기 때문에 계산의 대상이 되는 셀의 위치도 똑같다는 것이 (셀 B2) 전제가 된다.

 

 

• 일반적으로 "3차원 계산"의 경우 전체 시트의 형식이 같다고 가정한다. (계산 결과를 표시하는 시트 포함)

• 또한 또 다른 전제로서 "3차원 계산"의 대상이 되는 시트는 연속되어 있지 않으면 절차가 매우 복잡해지게 됨.

• 따라서 계산하고자 하는 시트들이 떨어져있는 경우에는 미리 시트를 이동하여 연속으로 시트가 나열되게 하여야 한다.

• 본 글에서는 전체 시트의 합계를 "SUM" 함수를 통해 구할 예정이다.

• SUM 함수에 대해 더욱 자세히 알고 싶다면 추후 업데이트 되는 글을 참고하길 바람 

 

(링크 추가 예정)

 

• 다시 본론으로 돌아가서 아래의 그림과 같이 「GS 25」~ 「CU」 의 시트에 10 월 ~ 12 월의 매출액이 입력되어 있다고 가정하자.

 

 

• 그리고 전체 점포의 10 월 ~ 12 월의 매출액의 총 합을 총 "총 매출액" 시트에 표시하는 작업을 수행한다고 가정하자

• 우선, "총 매출액 시트" (①) 에서 모든 셀 (B2 ~ B4)을 선택하고 (②), 매뉴의 "홈" 을 클릭한 후 (③) → 자동합계 명령 (Σ) (④) 을 클릭한다. (아래의 그림 참조)

 

 

• 다음으로는 아래의 번호 순서대로 작업을 수행한다.

  • ① 가장 첫 번째 시트 (GS25 매출액)을 클릭한다. ( 「GS25」의 시트로 전환)

  • ② "SHIFT"키를 누른 채 마지막 시트 (CU 매출액)을 클릭한다. (이제 모든 시트가 선택되었음)

  • ③ 계산되는 셀 (B2 ~ B4)에서 첫 번째 셀 (B2) 만 을 클릭한다.

  • ④ 마지막으로, 다시 오토 SUM 명령 (Σ)을 클릭한다.

 

 

 

• 위의 작업을 순서대로 진행하면 "총 매출액" 시트에 모든 지점의 합이 계산된다.

 

 

• 과정은 조금 번거롭지만 결과적으로 셀 선택과 자동합계의 반복으로 이루어져 있다.

• 참고로, 도중에 ESC 키를 누르거나, 계산의 대상이 아닌 시트를 클릭하지 않아야 한다.

• 즉 여기에 쓰여져있는 이외의 불필요한 작업을 하지 않는 것이 포인트이다.

• 또한 계산 대상 셀을 선택할 때 반드시 첫 번째 셀만 (B2) 선택 하여야 한다.

  • 만약 계산 대상 셀 선택시 B2 부터 B4 셀 까지 모든 셀을 선택한다면 제대로 계산이 이루어지지 않는다.

 

[자동합계 클릭 대신 단축키를 이용하는 방법]

• 위에서는 "자동합계" (Σ 버튼)를 사용하는 경우를 소개했지만, 오토 SUM 명령을 클릭하는 대신 바로 가기 키를 사용하여도 된다.

• "자동합계" 단축키는 "SHIFT"+ "ATL"+ "="(등호)

  • 즉 위에서 "자동합계" 라고 써있는 부분은 바로 가기 키의 "SHIFT "+"ATL "+"= "로 대체 할 수 있다.

  • 단축키를 사용하면 작업의 속도가 빨라지기에 기억해두는 것 역시 나쁘지 않다.

 

[일부시트의 포멧이 다른 경우 다중시트를 계산하는 방법]

• "3D 계산" 의 경우 "전체 시트가 동일한 포맷"인 경우를 가정하지만, 때로는 일부 시트 만 포맷이 다른 경우도 있을 수 있다.

• 즉 계산하려는 셀의 위치가 다른 시트와 다른 경우를 뜻한다.

• 그럴 때는 먼저 소개 한 방법을 사용할 수 없기 때문에 기본적으로는 "시트 중 하나를 선택하여 셀을 선택"이라는 조작을 계속 반복 하는 방법 밖에 없다.

• 하지만 약간의 발상의 전환이 있다면 굳이 직접 노가다를 하지 않을 수 있다.

• 이 때 형식이 다른 시트를 추가적으로 편집 하여야 하긴 하지만, 앞서 소개 한 절차와 유사하게 진행되기때문에 효율적으로 계산을 할 수 있게 된다.

• 예를 들어, 아래의 그림과 같이 마지막 시트의「CU 매출액 」의 계산 대상 셀의 위치가 다른 시트와 다르다고 가정 해 보자.

 

 

• 마지막 「CU 매출액」 시트만 불필요한 코멘트가 상단에 적혀있다.

• 이 첫 번째줄 때문에 불필요한 "3D 계산" 을 해야하므로 먼저, 해당 시트의 첫 번째 줄을 제거하고 전체 시트를 같은 형식으로 우선 변경하는 작업을 수행한다.

• 즉 가장 먼저 해야할 일은 "다른 형식의 시트를 편집하여 기존의 시트와 같은 형식으로 만드는" 작업이다.

• 물론 나중에 삭제 된 행은 다시 원래대로 만들어야 하기 때문에, 시트를 편집하기 전에 바꾸어야 할 시트를 미리 복사하여 백업을 해 두자.

• 먼저  「CU 매출액」 의 불필요한 첫 번째 행을 삭제하고 전체 시트를 같은 형식으로 만든다. (사전에 복사하여 백업)

 

 

• 다음으로 "총 매출액 시트" (①) 에서 모든 셀 (B2 ~ B4)을 선택하고 (②), 매뉴의 "홈" 을 클릭한 후 (③) → 자동합계 명령 (Σ) (④) 을 클릭한다. (상기한 것과 동일한 작업 우선 진행)

 

 

• 이후 처음부터 포맷이 같았던 시트 (GS25 와 7 ELEVEN)와 셀을 선택하는 작업을 수행한다.

  • ① 가장 첫 번째 시트 "GS25 매출액" 을 클릭한다.

  • ② "SHIFT"키를 누른 상태에서 "'7 ELEVEN 매출액" 을 클릭한다.

  • ③ 계산대상 셀 (B2 ~ B4)에서 첫 번째 셀 (B2) 만 을 클릭한다.

 

 

• 그리고 키보드로 쉼표 (,)를 입력한다. (①)

• ※ 아무것도 손대지 않고 키보드 입력

• 그리고 계산 대상 셀 (CU 매출액)의 시작 (B2) 만 클릭. (② -> ③)

 

 

• 여기서 SUM 함수 안의 쉼표 다음 셀의 위치 (이 예에서는 "CU 매출액" 의 B2)도 더해서 넣는 것을 의미한다.

• 그리고 마지막으로 "자동합계" (Σ) 버튼을 클릭 하면 합계가 계산된다.

 

 

• 결과적으로 "3D 계산"을 사용하여 합계를 구할 수 있다.

• 다음으로 할 작업은 형식이 다른 시트의 포맷을 원래대로 되돌리는 작업이다.

  • 백업 한 시트로부터 "CU 매출액 (백업)" 다시 1 행을 복사 한 후 원래 시트 "CU 매출액" 의 1행에 다시 붙여넣는다. 

 

 

• 이러한 작업을 수행한 이후에도 "총 매출액" 시트는 정상적으로 동작하는 것을 확인할 수 있다.

 

 

• 위 그림의 적색 사각형 내부의 SUM 수식의 원리는 다음과 같다.

  • = SUM (GS25매출액 : 7 ELEVEN 매출액!' B2, CU 매출액!' B3 )

• 즉, CU 매출액!의 뒷 부분이 B3으로 변화하였다.

• 다른 시트는 B2의 상태에서 행을 삽입하기 전 까지는 CU점도 B2 가 존재하였다.

• 그러나 CU점의 행을 삽입하는 순간 엑셀이 자동으로 B2에서 B3로 변경 해주는 원리이다.

• 이처럼 일부 시트 형식이 달라도 다른 시트에 맞추는 것으로 최소한의 절차로 "3D 계산" 이 가능하다.

 

 요점

• 형식이 다른 시트는 사전에 다른 시트와 같은 형식으로 편집한다.

• 원래 포맷이 같았던 시트와 형식이 다른 시트는 개별적으로 선택한다. (모든 시트를 일괄 선택하지 않음)

• "3D 계산"은 전체 시트가 같은 포맷 인 것이 전제조건.

• "3D 계산" 단계는 총 셀 선택 → 자동합계 → 계산 대상 시트 / 셀 선택 → 자동 합계

• 일부 형식이 다른 시트가 있어도 사전에 편집하고 다른 형식과 일치하는 것으로, 최소한의 절차로 계산을 수행할 수 있음.

 

 참고 문헌

[논문]

• 없음

[보고서]

• 없음

[URL]

• 없음

 

 문의사항

[기상학/프로그래밍 언어]

• sangho.lee.1990@gmail.com

[해양학/천문학/빅데이터]

• saimang0804@gmail.com

 

 

 

본 블로그는 파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음