나. 다른 시트인 경우
이번에는 Sheet1과 Sheet2에 데이터가 있는 경우에 통합하는 것을 다뤄보겠습니다.
같은 시트에 데이터에 있는 경우나 방법은 같은데 참조 영역 지정시 시트의 이름이 다른 차이만 있습니다.
① 먼저 Sheet3에 통합된 데이터를 표시하기 위해 Sheet3의 A2셀을 클릭하겠습니다.
② 데이터 도구 그룹의 통합 명령을 누릅니다.
③ 그러면 함수가 있는데 이번에는 최소를 지정해 보겠습니다.
그리고, 참조 아래 셀 주소 입력 칸을 클릭한 후 Sheet1 탭을 클릭합니다.
그리고, A2셀부터 C5셀까지 마우스 왼쪽 버튼을 누른 상태에서 끌어 선택하고 마우스 왼쪽 버튼을 뗍니다.
그러면 참조 셀 입력 칸에 Sheet1!$A$2:$C$5라고 입력됩니다. '추가' 버튼을 눌러 모든 참조 영역에 입력합니다.
그러면 모든 참조 영역에 Sheet1!$A$2:$C$5가 추가됩니다.
이번에는 참조 셀 영역을 지운 후 Sheet2 탭을 클릭한 후 A2셀부터 D6셀까지 범위를 지정합니다.
추가 버튼을 누르고, 사용할 레이블로 첫 행과 왼쪽 열에 체크합니다.
④ 확인 버튼을 누르면 지점별, 상품별 최소값이 구해지는데, '라'상품은 값이 없습니다.
둘 중 하나의 데이터만 있을 경우 최소값이 0일 줄 알았더니 값이 하나만 있으면 그 값이 최소값이고, 둘 다 없을 경우도 0으로 표시되지 않고 공백이 됩니다.
다. 원본 데이터에 연결
이번에는 데이터 도구 그룹의 통합 명령을 누른 후 '원본 데이터에 연결'에 체크해보겠습니다.
같은 시트에서 '원본 데이터에 연결'에 체크했을 때는 에러가 발생했는데, 이번에는 발생하지 않고 부분합을 했을 때처럼 왼쪽에 + 표시가 있고, 위에는 1과 2의 버튼이 있습니다.
또한 자세히 보면 2행과 5행 사이가 숨겨져 있습니다. 이 때 왼쪽의 + 버튼을 누르면 '가' 상품에 대한 지점별, 월별 데이터가 표시됩니다. 133이 1월, 119가 2월 데이터입니다.
윗 부분 2를 누르면 전체 세부 데이터가 표시됩니다.
왜 B9셀에만 '데이터 통합2'라고 쓰여 있지 하고, B3셀을 클릭해보니 B3셀에도 데이터 통합2라고 되어 있습니다.
Sheet1 탭을 클릭한 후 A8셀을 클릭한 후 데이터 도구 그룹의 통합명령을 클릭해보니 A8셀 주위에 통합데이터는 보이지 않지만 데이터 통합에 대한 설정이 살아 있습니다. 따라서, 이것이 데이터 통합1이 되는 것입니다.
라. 데이터 통합 지우기
① '원본 데이터에 연결'이 아닌 경우
위와 같이 통합 데이터가 있는 영역을 Delete키를 눌러 지운다고 통합 설정은 지워지지 않는다는 것을 알 수 있습니다.
이 때는 통합 명령을 누른 후 모든 참조 영역에 있는 참조 영역을 선택한 후 삭제 버튼을 눌러
'모든 참조 영역'에 아무것도 없는 상태에서 확인 버튼을 누르면 됩니다.
그러면 '통합에 필요한 원본 참조가 지정되지 않았다'는 에러 메시지가 표시되는데, 확인 버튼을 누릅니다.
이제 통합 명령을 눌러도 함수만 합계라고 표시될 뿐 참조 영역이 하나도 없습니다.
② '원본 데이터에 연결'인 경우
이 경우는 범위를 설정한 후 Delete키로 통합된 내용을 지우고, 통합 명령을 눌러 모든 참조 영역의 데이터를 지우고, 레이블의 선택도 지우고, 원본 데이터에 연결의 체크를 지워도 왼쪽의 - 또는 + 표시와 1,2 버튼이 지워지지 않습니다.
따라서, 이 때는 A열과 1행 사이의 왼쪽 위를 클릭해서 모든 셀을 선택한 후 마우스 오른쪽 버튼을 누르고,
삭제 버튼을 눌러야 합니다.
그러면 시트의 설정이 초기화됩니다.
그렇다고 모든 참조 영역에 범위가 지정되어 있다면 위와 같이 모든 셀을 삭제해도 그 설정은 살아 있는 점 주의해야 합니다.
왼쪽의 + 버튼과 1,2 버튼은 없어졌지만 통합 명령을 누르면 설정 내용이 아래와 같이 살아 있습니다.
4. 수식을 이용한 통합
위와 같이 데이터 통합 명령을 이용해 합계, 최소, 최대값 등을 구할 수도 있지만 여러 시트의 데이터가 동일한 위치에 있다면 수식을 이용해 합계 등을 구할 수 있습니다.
그러나, 연속된 시트를 지정할 수 있고, 떨어진 시트를 지정할 수는 없습니다.
먼저 Sheet2의 내용을 복사해서 Sheet3에 붙여넣습니다.
그리고, B3셀을 클릭하고 =min(Sheet1:Sheet2!b3)라고 입력하면
Sheet1과 Sheet2의 B3값을 비교해서 최소값을 구해줍니다.
이제 B3셀 오른쪽 아래 네모 모양의 채우기 핸들을 D3셀까지 끌고,
다시 D3셀의 채우기 핸들을 끌어서 D6셀까지 끌면 수식이 모두 복사돼서
해당 셀에 대한 최소값을 구해줍니다.
데이터 통합과 다른 점은 C지점 '가'상품의 최소값이 공백이 아니라 0이라는 점입니다.
'Excel' 카테고리의 다른 글
| 수식이 텍스트로 입력될 때 (0) | 2022.12.22 |
|---|---|
| 숫자와 날짜가 텍스트로 처리될 때 (0) | 2022.12.21 |
| 데이터 도구 - 통합(1) (같은 시트에서 통합) (0) | 2022.12.01 |
| 데이터 유효성 검사와 컨트롤 서식(콤보 상자) (0) | 2022.11.30 |
| 데이터 유효성 검사(4) - 제한 방법, 모두 지우기, 이동 옵션 (0) | 2022.11.29 |