[Excel]월별 입고, 출고 갯수 세기. (feat.텍스트나누기.피벗테이블)

월별 입출고 갯수 파악이 필요한 상황!

하나하나 세면 너무 단순 노가다 같은데 뭔가 쉬운 방법이 있지 않을까 하여 지인분이 저에게 의뢰를 해주셨습니다.

먼저 제가 받은 데이터는 이랬습니다.

원본 데이터

오우~ 같은 자료가 여러 칸으로 나뉘어 있는 게 신기하군요. 일단 자료들을 한줄로 정리해주었습니다.

한줄로 1차적으로 정리된 자료

이렇게 모아놓으니까 마음이 한결 편안~해집니다.

저에게 의뢰하신 분은, 저 첫번째 열에서 "/" (슬래쉬) 기호를 중심으로 앞이 입고일, 뒤가 출고일이라고 말씀해주셨습니다. 여기서 엑셀의 가장 기본적인 원칙이 필요할 것 같아요

1. 셀 하나에 데이터 하나만
2. 셀병합은 노노!

2번 원칙은 이번 의뢰와는 상관 없지만, 초보자분들이 많이 하시는 실수이기에 한 번 끼워넣어 봤습니다. 종종 디자인때문에 셀 병합을 하시곤 하는데, 데이터를 다루면서 정렬 등 기본 기능을 쓸 때 어려울 뿐만 아니라, vba로 자동화 할 때도 굉장히 까다로워집니다. 후에 셀병합 대신 셀병합 '처럼 보이게' 하는 다른 방법을 소개해드릴텐데, 그걸 사용하시는게 좋습니다.

 

텍스트 나누기

위에서 셀 하나에 데이터 하나만 들어가게 하기 위해서, A열에 있는 두가지 데이터를 A, B열로 나누어 주겠습니다. 손으로 일일히 할까요? 당연히 아니죠~

먼저 칸 나누기를 할 자리를 만들어줍니다.

먼저 B열에서 오른쪽 클릭, 삽입을 눌러 칸 나누기한 날짜가 들어갈 자리를 만들어줍니다. 이렇게 안하면, 옆칸에 자료가 덮어 씌워지더라구요

B열을 선택한 뒤 (열 선택 단축키 Ctrl+Space) ,  오른쪽 클릭, 삽입을 눌러 행을 삽입해 줍니다. (행/열 추가 단축키 Ctrl+ '+')   참고로 행 선택은  Shift+Space 입니다.  단축키는 재미로 보시고 안 외워도 되는거 아시죠?

 

그리고 다시 A열을 선택하신 후,  상단에 [데이터] - [텍스트 나누기]를 클릭합니다.

엑셀 [텍스트 나누기] 버튼. 

다음과 같은 창이 나올겁니다.

텍스트 나누기를 위한 엑셀 텍스트 마법사

창의 이름은 중요하지 않습니다.

저희는 운이 좋게도 컴퓨터가 어디서 나눠야 할지 알기 쉽게 같은 기호로 나누어져 있습니다. 그래서 우리는 '구분 기호로 분리됨' 을 선택해줍니다. 다음을 눌러줍니다.

 

엑셀 텍스트 나누기 2단계

탭에 기본적으로 체크가 되어 있을텐데, 탭,세미콜론,쉼표,띄어쓰기 등 자료를 나누는 여러 기준에도 없는 "/" 를 넣기 위해 '기타'를 체크하고 (탭은 체크 해제해주세요)  "/" 를 입력해주세요.  "/" 기호가 아니라 다른 기호로 나누어져 있어도 이 기능을 활용하면 잘 나누어지겠죠?

다음을 클릭합니다.

엑셀 텍스트 나누기 3단계

다음은 나눈 데이터 서식을 정해주는 단계입니다. 사실 따로 안정해줘도 엑셀이 알아서 서식을 알아먹기도 한데, 많은 데이터는 뭔가 불안하므로 하나하나 설정해 주겠습니다. 가끔 날짜를 텍스트로 인지하면, 날짜와 관련된 엑셀의 기능들을 사용하기가 불편해집니다. 작업 후반부에 그런 문제를 발견하면 귀찮아지므로 미리미리 서식을 알려주는 습관을 들입시다. 앞 열을 선택하고 '날짜' 라고 알려줍니다.  뒷열도 마찬가지로 작업합니다.

마침을 눌러줍니다.

잘 나누어진 데이터

날짜가 말끔하게 잘 분리되었네요.

두가지 선택지

여기까지만 해도 월별로 데이터 어떻게 뽑을지 아시는 분들도 계실 겁니다.

첫번째로 전체를 선택하고, 앞의 입고날짜를 기준으로 정렬을 한 뒤 월별로 끊어서 하나하나 세는 방법이 있습니다.

계산기 기능에 충실한 엑셀, 칸 수 세기 기능도 있습니다.

이렇게 정렬된 데이터를 월별로 드래그해서, 밑에 나오는 갯수를 기록하면 되겠죠?

하지만 한가지 문제가 있습니다. 입고일뿐만 아니라 출고일까지 세야 한다는 것. 입고일을 하나하나 다 세고나서 다시 출고일을 기준으로 하나하나 세야합니다. 이 경우 왔다갔다 하는 작업이 추가되어 입고일을 잘 체크했나 불안하기도 하고, 체크시 다시 정렬을 해야합니다. 그래서 다른 방법을 생각해봤습니다.

 

두번째로, 피벗테이블을 통해 월별로 카운팅하는 것입니다. 이 방법을 따라해보겠습니다

 

피벗테이블로 월별로 세기

 

엑셀 피벗테이블 버튼

먼저 데이터가 있는 부분을 모두 선택하고 (드래그로 선택, 열을 드래그해서 선택, Ctrl+A로 선택 등 다양한 방법이 있습니다.) 위 리본메뉴에서 [삽입] - [피벗테이블]을 클릭합니다.  외부 데이터가 아니기 때문에 [테이블/범위에서] 를 클릭합니다.

엑셀 피벗테이블 기준 범위와 생성 위치 정하기

새로 뜬 창을 보시면, 피벗테이블을 만드는 기존자료가 어디에 있는지 알려주는 칸 (표/범위 선택) 이 있고, 어디에 생성할지 알려주는 칸이 있습니다. 여기서 기존 워크시트(새 워크시트를 클릭해도 상관은 없습니다. 다른 시트에 피벗테이블이 만들어질거에요) 를 누르고, 피벗테이블을 시작하고 싶은 셀을 클릭해주면, 위치가 알아서 입력됩니다.

확인을 누릅니다.

excel 피벗테이블 생성 화면

피벗테이블이 생성되었습니다. 아무것도 안보이네요. 근데 피벗테이블이 뭔지 왜 알려주지도 않냐구요? 사실 저도 모릅니다. 잘 쓸 줄도 모르구요. 하지만 제가 필요한 것만 당장 할 수 있으면 장땡이잖아요? 더 깊게 공부해보는 건 나중으로 미루고 일단 필요한 일을 해봅시다.

 

왼쪽 맨 위에 있는 행을 가져다가 '행'으로 가져다 주세요.

이렇게 가져다 주세요

피벗테이블이 뭔가 바뀌었습니다?? +버튼을 클릭해서 접었다 폈다도 해볼 수 있습니다.

excel 피벗테이블 뭔가 할 줄 알게 된 것 같다!

다음은 입고를 값에 끌어다가 줍니다.

합계를 똑똑하게 취합해서 알려주는 엑셀 피벗테이블

와! 벌써 년도별로, 월별로 입고의 갯수를 취합해서 보여주고 있네요. 피벗테이블은 이렇게 자료를 필요한 형태로 취합해주는 테이블입니다.  그런데 사분기별 취합은 우리가 필요하지는 않으니 한번 빼보겠습니다.

피벗테이블 분기별 구분 빼기

분기를 누르고 필드 제거를 눌러줍니다.

!짠.  

분기가 제거된 피벗테이블

이렇게 월별로 입고 갯수를 셀 수 있었습니다.

월별 출고는 같은 피벗테이블에서 어떻게 해보기보다는, 같은 자료를 가지고 피벗테이블을 하나 더 만들어서 출고를 기준으로 합계를 내는 것이 낫습니다.

 

---

엑셀 텍스트 나누기와 피벗테이블 기능을 통해 오늘의 노가다를 손쉽게 해결해 보았습니다.

 

피벗테이블의 가능성

피벗테이블에서 '값' 부분을 조금 더 만져보면 '평균'이나 '최대, 최소' 값도 구해볼 수 있습니다.

피벗테이블에서 제공해주는 값들의 조합 외에 사용자가 설정할 수도 있는 것 같습니다. 

취합 기간도 기본적으로는 연, 분기, 월, 일 등이 있는데요,  일단위로 끊는 수를 조정하여 7일로 조정하면 일주일 단위로 계산하는 것도 가능해 보입니다.

아직 안 해봤는데 위의 기능들을 좀 더 공부해보겠습니다. 

 

오늘의 tip 정리

• 한 셀에는 한가지 데이터만 있는 것이 좋다
• 셀 병합은 되도록 하지 않는 것이 좋다
• 행.열 선택, 추가 단축키
• 텍스트 나누기로 특정 문자를 기준으로 두칸으로 나눌 수 있다.
• 피벗테이블로 원하는 데이터를 빠르게 뽑아 취합할 수 있다.