엑셀의 데이터 통합(엑셀 데이터 합치기)

안녕하세요,봉자씨입니다.

오늘 알아볼 엑셀의 기능은 정말 많이 사용하는 기능중의 하나이며 모르면 고생하는 그런 기능이라고 생각합니다. 

엑셀의 데이터 통합기능

위에서 보는 것처럼 바로 데이터에 => 통합에 위치해 있습니다. 

그럼 이 기능을 언제 사용하는지 한번 알아 보겠습니다.

예를 들어서 수량과 단가를 조사 한다고 생각을 해보겠습니다. 

그럼 여러 지점에서 본인의 지점에 존재하는 물량을 적어서 보냅니다. 바로 아래와 같이 보낸다고 생각해 보겠습니다.

각 지점에서 올라온 데이터라서 어떤 물량은 있기도 하고 어떤 물량은 빠져 있기도 합니다.

그럼  이거 다 하나씩 비교 대조 해가면서 통합해서 관리해야하나 하는 생각이 듭니다. 

그렇지 않다면 어떻게 해야 한번에 모두 수량과 단가를 더 할 수가 있을 까요? 

이 것을 위해서 엑셀은 데이터 통합이라는 기능을 제공합니다.

한번 같이 해보도록 하겠습니다. 먼저 시트 하나를 열겠습니다.(통합해서 모두 더할 시트 입니다.)

그리고 A1셀의 위치에 가서 데이터의 통합을 클릭하면 아래와 같이 창이 뜨는 것을 확인 할 수 있습니다.

여기서 함수라는 것이 보일 겁니다. 이것은 데이터를 통합(여러 데이터를 하나로 모으는 것)하는데 있어서 

합계 또는 개수 또는 평균…등등을 구할 수 있다는 것을 보여 줍니다. 저희는 가장 간단한 합계로 해보겠습니다.

창을 열었으니 이제 통합의 대상이 되는 데이터를 지정해 줘야 합니다. 

그래서 아래와 같이 하나씩 지정을 해보겠습니다. 

참조 영역에 범위를 추가합니다.(그러기 위해서는 찾아보기 옆에 빨간 화살표를 클릭 합니다.) 

그리고 Sheet1로 이동을 하고 데이터를 아래와 같이 선택합니다.

그럼 위와 같이 선택이 되게 되고 다시 빨간 화살표를 누르게 되면 아래와 같이 창이 뜨게 됩니다.

 

이 상태에서 이제 추가를 누릅니다. 그럼 아래와 같이 됩니다.

이로써 첫번째 통합 할 대상의 데이터가 선택이 되었습니다. 

이와 마찬가지고 나머지 두개도 똑같이 해주시면 됩니다. 

그럼 아래와 같이 모두 추가 된 상태가 됩니다.

이제 우리가 통합하고자 하는 데이터는 모두 모였습니다. 

그리고 이제 우리는 첫 행 / 왼쪽 열을 클릭하고 확인을 누릅니다. 

(첫 행을 기준으로 그리고 왼쪽 열을 기준으로 가져오겠다는 것입니다.

모두 체크하게 되면 가장 데이터가 많은 왼쪽 열 기준(Sheet 1의 왼쪽 열이 가장 많습니다. 23개 그래서 기준은 Sheet1이 됩니다.) 

가장 데이터가 많은 첫 행 기준(첫 행은 모두 동일합니다. 제품명, 단가 , 수량)으로 데이터를 통합하게 됩니다.

그럼 아래와 같은 결과를 확인 할 수 있습니다. 아주 간단하게 통합이 가능합니다.

(기준을 주지 않았기에 모든 데이터를 대상으로 데이터가 통합 된 것을 확인 할 수 있습니다.)

그런데 여기서 아래 보면 사용할 레이블 이란 말이 나옵니다.

바로 이부분 이 부분에 대해서 꼭 알아야 이 기능을 제대로 활용할 수 있습니다. 

첫 행을 기준으로 데이터를 가져오겠다 또는 왼쪽 열을 기준으로 데이터를 가져 오겠다 이런 말입니다.

아래의 결과를 주의 깊게 보도록 하겠습니다.

먼저 나왔던 결과를 첫 행 / 왼쪽 열 체크 없이 한번 해보겠습니다.

첫 행과 첫 열의 데이터가 모두 나타나지 않습니다.

그리고 이전의 첫 행/ 왼쪽 열을 기준으로 했을 때와 결과 값이 다릅니다. 

아래의 결과는 단지 같은 행의 위치에 있는 데이터를 합산 한 결과입니다.

이렇게 데이터가 나온 이유는 무엇 일까요? 어찌 보면 당연합니다. 

우리는 어떠한 기준도 주지 않아서 엑셀이 단지 행을 기준으로 잡아 계산을 해버린 것입니다.

이제 아래와 같은 사용할 레이블에 대해서 자세히 이해해 보도록 하겠습니다.

첫 번째로 첫 행을 기준으로 가지고 오기!

아래의 데이터 만을 가지고 오고 싶습니다. 

그럼 데이터를 최종적으로 통합 할 시트에 아래와 같이 데이터의 행과 열을 미리 만들어 놓습니다.

그리고 해당 데이터의 범위를 선택하고 데이터 통합을 누르게 됩니다.

그럼 아래와 같은 창이 뜨게 됩니다. 이 상황에서 이제 사용할 레이블을 첫 행으로 합니다.

그리고 확인을 누르게 됩니다. 어떤 결과가 나올까요? 첫 행을 기준으로 한다고 했습니다. 

그랬더니 결과는 아래와 같이 나왔습니다.

이 결과는 무엇을 의미하는지 생각해 보도록 하겠습니다. 

엑셀은 제품명과 수량을 기준으로 합계를 시도 합니다. 

하지만 제품명은 합을 할 수가 없는 대상입니다. 그 이유는 텍스트는 계산이 되지 않습니다. 

그래서 수량을 가져 옵니다. 수량을 더 합니다. 첫 행을 기준으로 했으니 수량 만을 가져 옵니다. 

기존의 단가는 가져 오지 않습니다. 첫 행에 단가는 없기 때문입니다. 

이제 이 상황에서 수량의 합은 어떻게 가져왔을 까요? 우리 기준 줬나요? 아니요 안 줬어요! 그럼 어떻게 가져온다 했죠?

3개의 시트에 행을 기준으로 수량을 더해서 가져온 것입니다. 그런데 여기서 왜 4행만 가져 왔을 가를 생각해 봐야 합니다. 

이유는 우리가 통합하기 전에 아래와 같이 4행 만을 선택하고 통합을 했기 때문입니다.

두 번째로 왼쪽 열을 기준으로 가지고 오기!

아까와 같은 상황의 데이터를 가져 오고 싶은데  이번에는 왼쪽 열을 클릭하고 데이터를 한번 가져와 보겠습니다.

어떤 결과가 나올지 먼저 예상을 해보도록 합니다. 그리고 결과를 한번 봅니다. 

아래의 결과가 왜 그렇게 나왔는지에 궁금해 집니다. 

왼쪽 열을 기준으로 합계를 구하기를 시도 합니다. 

그래서 현진 커피 밀크, 대관령 특제 버터 등을 기준으로 데이터를 합계를 구하려 합니다.

그런데 첫 행의 기준이 없었습니다.

 그 말은 엑셀은 기준이 없기에 데이터의 첫 번째 행에 속하는 단가를 더해서 가져오게 된 것입니다. 

근데 왜 단가만 가져 왔을 까요? 기준이 없으니 가격도 가져와야 하는 것이 아닌가 하는 생각이 듭니다. 네! 맞아요! 그래야 합니다.

그런데 우린 행을 두 개의 열만 선택하고 데이터 통합을 시도 했습니다. 

그래서 수량이 아닌 단가 까지만 나타나게 된 것입니다.

세 번째로 첫행 / 왼쪽 열을 기준으로 가지고 오기!

이제 첫 행과 왼쪽 열을 기준으로 데이터를 한번 가져와 보겠습니다. 

이전과 동일하게 아래와 같이 첫 행 / 왼쪽 열에 클릭을 하고 확인을 합니다.

아래와 같습니다.

우리가 원하는 모양이 나왔습니다. 왼쪽 행을 기준으로 첫 행의 대상 만을 가져오게 된 것입니다.

데이터 통합의 원리를 이해 하셔야 합니다. 원리가 이해가 간다면 여러 가지 응용도 가능한 것입니다.

응용 한번 해보겠습니다. 가장 많이 사용하는 응용입니다.

위와 같이 데이터가 존재합니다.

여기서 우린 치즈와 아이스크림 데이터를 기준으로 수량을 가져 오고 싶습니다.

한라 메론 아이스크림, 대관령 바닐라 아이스크림 이런 것들이 하나로 계산이 될 것입니다. 그럼 어떻게 하면 될까요? 먼저 통합 될 데이터의 시트에 아래와 같이 작성을 합니다.

여기서 "*아이스크림*" , "*치즈*" 이게 무슨 의미 인지 짚고 넘어 가겠습니다.

*이것은 여러 문자를 대표하는 문자입니다.

그래서 서일 아이스크림, 아이스크림 냠냠 등 앞에 뒤에 어떤 단어가 들어 있던 포함 된다는 의미를 하는 것입니다. 

치즈도 마찬 가지고 서일 치즈, 일산 치즈 나라 등 모든 치즈가 포함된 단어는 가능합니다. 

아래와 같이 작성한 데이터 범위를 선택하고 데이터 통합을 클릭 합니다. 

그럼 아래와 같이 창이 뜨는데 여기서 첫 행과 왼쪽 열을 클릭 합니다.

결과는 여러분이 예상한 대로 아래와 같이 나오게 됩니다.

간단하게 데이터 통합에 대해서 알아 보았는데 추가적으로 아래 몇 개만 더 보겠습니다.

찾아보기를 클릭하게 되면 외부의 파일도 통합이 가능합니다. 

즉, 같은 파일 내의 시트만 되는 것이 아니라 다른 파일의 시트의 데이터도 가능합니다.

아래와 같이 찾아보기로 파일을 선택하게 되면 아래처럼 파일의 경로가 나타나게 됩니다.

 "!" 뒤에 시트와 범위를 넣어주시고 추가를 눌러 주시면 됩니다. 빨간 글씨처럼 직접 추가해 주면 됩니다.  

엑셀블러그자료\데이터통합.xlsx'!Sheet1!$A$1:$C$23

우리가 하나 더 안 알아 본 것이 있습니다. 원본 데이터에 연결! 이게 무엇일 까요

여러분이 데이터를 통합하고 나면 그 데이터의 결과가 나타나는데 어떻게 어떤식으로 통합이 되었는지 

좀더 자세히 보고 싶을 경우에 사용합니다. 예를 들어 아래와 같이 한번 해보도록 하겠습니다.

결과가 아래와 같이 나옵니다. 부분합의 모양으로 데이터가 나타나는 것이 확인 됩니다. 

여기서 + 기호를 클릭하면 해당 수량이 12가 어떻게 나왔는지를 알 수 있게 됩니다.

어떻게 나타나게 되었는지 보여줍니다.

정말 길게 알아본 데이터 통합입니다. 길지만 원리를 꼭 이해하고 꼼꼼히 읽어 보셨으면 합니다. 

그리고 하나 더 하다가 이해 안가시면 댓글로 꼭 의문점을 해결하세요. 

감사합니다.