전처리
-
병합된 셀 A2, B2, C2 -> 하나의 행row으로 정리
-
합계가 들어가있는 행 row 삭제
구성
조직(팀(대))별 매출 목표에 따른 달성률 파악하기 쉽게 구성하겠습니다.
목표금액, 매출금액, 달성률, 신장률, 건수에 따른 신장률, 전년건수를 columns 열로 구성되어야 합니다.
날짜와 브랜드를 각각 구분하여, 증가/감소 수치에 대한 코멘트를 달도록 해야합니다.
브랜드별 월 매출 신장률 순위를 담도록 해야합니다.
SUMIF()
선택된 범위에서 여러 조건을 만족하는 값의 합을 구합니다.
=sumif(합계범위, 조건범위1, 조건1, 조건범위2, 조건2 ...)
- 합계범위 : 합계를 구할 범위
- 조건범위1 : 조건1을 만족하는 값을 찾을 범위
- 조건1 : 조건범위1에서 만족하는 값을 찾을 조건
조건범위에 조건을 만족하는 값이 있는 행의 합계범위 값을 더함
조건내 매출
일자별
첫번째 조건범위
우선 '란제리'의 '목표'값인 F12를 채우기위한 수식을 만듭니다.
=sumifs(합계범위, 조건범위1, 조건1...)
참고로 sumif에서 s 꼭 붙혀야 합니다.
ㄴ합계범위 (여기에선 목표 coloumn 전부) 클릭하고 , 추가
ㄴ조건범위 (여기에선 팀(소) columns 전부 ) 클릭하고 $절대$범위 , 추가
ㄴ 조건 (여기에선 란제리) 클릭하고 ) 추가 하고 엔터
밑에까지 수식복사
두번째 조건범위
'목표'에 날짜를 두번째 조건범위와 두번째 조건으로 추가합니다.
ㄴ 다시 , 추가
ㄴ 조건범위( 여기에선 날짜 column 전부 ) 클릭하고 , 추가
ㄴ 조건 (여기에선 원하는 날짜인 2023.2.5 W6 ) 클릭하고 $절대$참조 하고 ) 엔터
참고
문화홀 에서는 $혼합참조로 E열은 고정이되, 숫자값은 비고정
년누계
ㄴ 날짜 전체에서 2/5보다 작은 모든 값(2/5 이전 모든 날짜)의 모든 매출 합계
ㄴ 복사붙혀넣기로 서식 채워넣기
월누계
'$' 절대참조와 혼합참조
- 통틀어 1개만 지정된다면 굳이 x
- 같은 조건급으로 여러개가 지정된다면 절대참조 ($$)
- 하나의 열column에 행rows만 돌아가며 지정된다면 혼합참조($)
Vlookup
Vlookup이란, 찾고 싶은 값을 원하는 범위에서 찾아오는 함수입니다.
여기서 찾고 싶은 값은 1열에 와야 합니다.
=vlookup(찾을값, 범위, 범위 내 찾는열번호, True or False)참고로 True는 유사일치, False는 정확하게일치
만약 '수박'의 'C급가격'을 찾고 싶다!
= vlookup('수박' , '빨간색범위' , '범위 내에서 수박의 C급가격이 있는 column 순서', 정확히 일치하는 값)
=vlookup(G3, $B$e:$E$6, 4, FALSE)
브랜드별 순위
피벗테이블
생성
데이터 ctrl+A로 전체 선택 --> 피벗테이블 생성
ㄴ 브랜드별 매출과 전년매출, 그리고 날짜 지정필요
ㄴ 그 중 2월달 날짜만 체크
신장률 구하기
피벗테이블 분석 --> 필드,항목,집합 --> 계산 필드
작년매출이 0이라면 #DIV/0!으로 나오게 됩니다.
정렬
숫자가 소숫점까지 많이 나와, 셀 서식으로 숫자를 좀 더 보기 편하게 변경해야 합니다.
숫자를 큰 순서 -> 작은순서로 정렬하는 내림차순 형태로 만들겠습니다.
총합계 제외
총합계가 있으면 count할때 총합계가 가장 max값으로 나오기때문에 제거
2월달 매출top5
매출 top 1위
=large(범위,순위)
=large(C열의 숫자값맨위~맨아래 , 1)
2월달 매출top5의 브랜드
vlookup으로 위의 각 매출top5에 해당되는 브랜드명 추출
참고로 vlookup을 하기 위해선,
기준으로 하는 '매출'열이 가장 왼쪽에 있어야 합니다.
가장 왼쪽에 있는 브랜드명을 매출보다 오른쪽으로 복붙해줍니다.
절대참조 및 혼합참조도 잊지말고 해줍니다.
신장률
똑같이 vlookup으로 매출신장률을 기준으로 찾습니다.
그러곤 백분율스타일로 변경합니다.
일간 코멘트
당일 매출이 적년매출에 비교하여,
크게 증가/감소한 브랜드 정보에 대한 코멘트를 자동화 하는 서식을 만들어보겠습니다.
피벗테이블
데이터를 전체 복사붙혀넣기 하여, 피벗테이블 생성합니다.
날짜는 당일(가장 최신)날짜로 필터 선택합니다.
작년대비 매출증감을 계산해주는 컬럼 생성합니다.
편리하게 보기 위해, 소수점 한자리빼고 나머지 안보이도록 합니다.
총합계도 제거해줍니다.
디자인->총합계->총합계해제
매출증가/감소 top2씩
매출증감수치가 가장 큰 top1,2 수치 추출
=large(범위,k)
=small(범위,k)
$절대참조와 혼합참조 필요
그에 해당하는 팀(소)값 vlookup으로 추출
vlookup(
기준값, 범위, 범위내 열번호, FALSE
)
를 통해서 원하는걸 불러옵니다.
반드시 기준값이 맨 왼쪽에 와야 합니다.
문장으로 자동화 할 수 있습니다.
다음날엔 새로고침
날짜 기준으로 했을 때엔, 피벗테이블 새로고침을 한 번 해줘야 다음날로 적용된 데이터값이 업데이트 됩니다.
