Excel로 만든 보고자료 (센터별)

엑셀로 만드는 보고자료

 

 

위 사진과 같은 보고자료를 만들어보려고 한다

 

센터를 누르면 센터별 그래프의 색상이 변경되면서 강조되게 할 것이다.

 

먼저 빈 시트에 

 

위 사진 처럼 작성해 준 뒤

 

raw data에 있는 

 

센터들을 불러와보자 

 

 

많은 센터들이 있는데 여기서

 

중복된 항목 제거를 눌러 

 

센터를 확인하고 시트에 센터 부분에 넣어주자

 

창고코드도 마찬가지다.

 

똑같이해주자

 

그러고 이제

 

sumifs를 활용해서 

 

 

=SUMIFS(rawdata의 총액,rawdata의 월, 우리가 작성중인 시트의 "1월",rawdata의 물류창고,우리가 작성중인 시트의 "이천센터") 를 하게되면 

완성된다.

 

절대참조 상대참조 혼합참조를 잘 걸어주고 

 

수식을 붙여넣어주면

 

전부 불러와졌다

 

합계도 추가해주자

 

.

 

완성.

 

이제 피벗테이블을 사용해 그래프를 만들어보자

 

먼저 센터이름을 복사하고 아무곳에 넣어주자

 

복사 후 

 

해당 시트 빈 곳에 넣어주기

 

붙여넣은곳에 위 사진처럼 만들어 준 뒤

 

VLOOKUP으로 센터별 금액을 불러와주자

 

수식에 임의로 3을 넣었는데

 

3을 넣기보다는 목록을 만들어주는게 좋기에

 

월별 목록을 만들어주자  

 

이제 3부분을 지우고 

 

2+ "1이 있는 열행"을 해주자

 

 

이제 우리는 1월을 2월로 바꾸면 자동으로 데이터가 바뀌게되었다.

 

센터별 금액 다 붙여넣기로 넣어주고

 

해당 부분 전부 드래그해서 

 

차트를 만들어주자

 

 

월별 금액 차트에 월별금액과 동일하게 넣어주면 

 

주황색으로 그래프가 하나 튀어나온다.

 

우리는 이걸 겹치게해서 

 

포인트 컬러를 줄 생각이다.

 

파란그래프를 눌러 

 

계열차트 종류 변경에 들어가주자

 

월별금액_차트에 보조축을 체크해주면

 

이제 겹쳐서보일것이다.

 

이제 피벗테이블을 기존 워크시트로 설정해 만들어주자

 

센터를 행 레이블에 넣어주고 

 

만들어진 행 레이블을 슬라이서삽입을 눌러주면

 

슬라이서 하나가 만들어지는데

 

해당 슬라이서를 세로로 두어야 하기에

 

열을 10으로 늘려주자

 

 

 

이제 수식을 만들어주자

 

IF를 사용해서 

 

이천센터가 =행 레이블이천센터와 일치하면 1, 아니면0 

 

추가로 

 

IF를 하나 더 사용해서

 

만약 =1이라면 월별금액을 불러와라 아니면 0을 불러와라 로 만들어주면된다.

 

 

 

이제 센터를 누르면 막대그래프가 변하면서 

 

우측 차트도 변경된다.

 

대충 위치 조정및 

 

디자인적인 부분을 해주고 

 

그래프에서 필요없는 부분을 전부 날려주자

 

깔끔하다

 

우측 상단에 월부분도 

 

데이터유효성 검사로 목록을 만들어주면된다.

 

 

이제 센터별로 포인트를 주면서 깔끔하게 보여지는 

 

보고자료를 만들었다.