문제상황
매일의 판매량을 raw data로 관리하고 있는 회사에서
일별 판매량 raw data를 활용해 매월 주차별 판매량의 합계와
총 판매량 중 품목별 판매비율을 알아보도록 하겠습니다.
아래 예시 형태의 raw data가 23.07.01부터 23.09.30까지 있다고 해보겠습니다.
관련 내용
2023.04.17 - [업무 관련/엑셀] - [Year, Month, Day] 날짜 추출(활용) 함수
2023.04.24 - [업무 관련/엑셀] - [ifs] 조건 A라면 □□를, 조건 B라면 △△를, 조건 C라면 ○○를...(if 반복)
2023.04.06 - [업무 관련/엑셀] - [eomonth] 특정 월의 마지막 날 구하기 (1달 전의 마지막 날, 12달 후의 마지막 날 등)
2023.03.24 - [업무 관련/엑셀] - [sumifs] 여러 조건 동시에 해당하는 값만 더하기
2023.04.28 - [업무 관련/엑셀] - [Iferror] 조건에 해당하는지 검토해보고, 만약에 오류나면 이렇게 해라
설명
=weeknum(serial_number, [return_type])weeknum은 1년을 기준으로 특정 날짜가 몇 주째인지 알려주는 함수입니다.
매년 1월 1일은 1주차이고, 12월 31일은 52주 혹은 53주가 됩니다.
- serial_number : 날짜
- 날짜가 있는 셀 주소를 입력하는게 편해요.
- 날짜를 직접 입력할 때는 "따옴표" 안에 날짜를 입력해야합니다.
날짜 구분 기호는 슬래시/ 와 대쉬- 둘 다 가능합니다.
- return_type : 1주의 시작을 무슨 요일로 할지 선택
- return_type을 설정하지 않으면 weeknum은 자동으로 일요일을 한 주의 시작으로 계산합니다.
- 입력값은 숫자이고, 각 숫자별로 한 주의 시작요일을 선택할 수 있습니다.
매월 주별/품목별 판매량 & 판매비율 구하기
1. 표 만들기
결과물을 정리할 표를 만들어보겠습니다.
과일과 야채의 판매수량, 판매비율을 알아보려 한 것이니 각각 열을 입력합니다.
A열과 B열에는 한 주의 시작일과 종료일을 입력했습니다.
2023-07-02 ~ 2023-07-08 처럼 기간으로 적지 않은 것은
각 열들도 함수의 대상으로 활용해야하기 때문입니다.
만약 시작일 ~ 종료일 로 기준일자를 설정한다면 함수를 조금 더 복잡하게 (어려운거 X, 쉬운거 多) 써야할 수도 있습니다.
1-1. 표 만들기 조건
매월 1일을 첫 시작일로 한다.
1주의 기준은 일요일부터 토요일까지로 한다.
매월 말일과 1일은 다른 주로 구분한다.
1-2. 시작일과 종료일을 함수로 입력하기 (ifs, emonth)
자료의 첫 시작일은 수동으로 한 번 입력해주더라도
다른 칸의 시작일과 종료일이 자동으로 입력된다면 정말 편하겠죠.
한 주는 시작일 포함 7일입니다.
시작일이 일요일이라면 종료일은 시작일+6일 해서 총 7일이 한 주로 설정되어야합니다.
그런데 시작일이 반드시 일요이기만 하지는 않습니다.
한 주는 7개의 요일이 있으니 매월 1일은 7개 중에 한 개의 요일이 되겠죠.
그래서 ifs 함수를 활용해 종료일을 자동으로 나타내보겠습니다.
=IFS(
EOMONTH($A3,0)-$A3<6,EOMONTH($A3,0),
WEEKNUM($A3)=WEEKNUM($A3+6),$A3+6,
WEEKNUM($A3)=WEEKNUM($A3+5),$A3+5,
WEEKNUM($A3+4)=WEEKNUM($A3),$A3+4,
WEEKNUM($A3+3)=WEEKNUM($A3),$A3+3,
WEEKNUM($A3+2)=WEEKNUM($A3),$A3+2,
WEEKNUM($A3+1)=WEEKNUM($A3),$A3+1,
WEEKNUM($A3+1)>WEEKNUM($A3),$A3)엑셀에 입력한 함수를 알아보기 쉽게 조건별로 따로 작성한 것입니다.
+6, +5는 입력 형식이 같은데 +4부터는 등식의 순서가 바뀌었습니다.
하지만 어차피 등식은 뭘 먼저 쓰든 같기 때문에 순서는 상관이 없고,
부등식이라고 하더라도 >, <, >=, =<의 기호 방향만 잘 맞춰주면 됩니다.
아래에서 조건별로 설명을 하겠습니다.
=IFS(
ifs는 맨 앞 조건부터 우선적으로 적용합니다.
그래서 조건이 많으면 많을수록 큰 거름망부터 작은 거름망 순으로 입력해주어야합니다.
EOMONTH($A3,0)-$A3<6,EOMONTH($A3,0),
eomonth는 기준 날짜보다 n번째 미래(+n) 혹은 과거(-n)가 속한 달의 마지막 날을 불러옵니다.
종료일은 시작일+6 인데, 시작일+6이 다른 달이라면 종료일은 시작일의 마지막 날이 되어야합니다.
결국 해당 월의 마지막날-6이 음수가 되면
그 주는 시작일~종료일까지 1주일도 남지 않은 것이므로 그 달의 마지막 날을 불러와야합니다.
아래부터는 종료일이 해당 월의 마지막 날이 아닌 경우입니다.
WEEKNUM($A3)=WEEKNUM($A3+6),$A3+6,
시작일의 주수 = 시작일+6의 주수 라면 => 시작일과 종료일이 같은 주에 있습니다.
그렇다면 종료일은 시작일+6이 되어야합니다.
아래부터는 종료일이 시작일+6이 아닌 경우입니다.
종료일≠시작일+6 이지만 시작일 기준 한 주 내에서 가장 끝 날을 불러옵니다.
WEEKNUM($A3)=WEEKNUM($A3+5),$A3+5,
종료일≠시작일+6 이지만
시작일의 주수=시작일+5의 주수 라면
=> 시작일과 종료일이 같은 주에 있습니다.
그렇다면 종료일은 시작일+5이 되어야합니다.
WEEKNUM($A3+4)=WEEKNUM($A3),$A3+4,
종료일≠시작일+6, 종료일≠시작일+5 이지만
시작일의 주수=시작일+4의 주수 라면
=> 시작일과 종료일이 같은 주에 있습니다.
그렇다면 종료일은 시작일+4이 되어야합니다.
WEEKNUM($A3+3)=WEEKNUM($A3),$A3+3,
종료일≠시작일+6, 종료일≠시작일+5, 종료일≠시작일+4 이지만
시작일의 주수=시작일+3의 주수 라면
=> 시작일과 종료일이 같은 주에 있습니다.
그렇다면 종료일은 시작일+3이 되어야합니다.
WEEKNUM($A3+2)=WEEKNUM($A3),$A3+2,
종료일≠시작일+6, 종료일≠시작일+5, 종료일≠시작일+4, 종료일≠시작일+3 이지만
시작일의 주수=시작일+2의 주수 라면
=> 시작일과 종료일이 같은 주에 있습니다.
그렇다면 종료일은 시작일+2이 되어야합니다.
WEEKNUM($A3+1)=WEEKNUM($A3),$A3+1,
종료일≠시작일+6, 종료일≠시작일+5, 종료일≠시작일+4, 종료일≠시작일+3, 종료일≠시작일+2 이지만
시작일의 주수=시작일+1의 주수 라면
=> 시작일과 종료일이 같은 주에 있습니다.
그렇다면 종료일은 시작일+1이 되어야합니다.
WEEKNUM($A3+1)>WEEKNUM($A3),$A3)
시작일의 주수>시작일+6의 주수 라면
=> 시작일과 종료일이 같은 주에 있지 않습니다.
이번 주는 한 주에 시작일 하루만 있습니다.
그렇다면 종료일은 시작일과 같습니다.
시작일은 종료일+1 해주면됩니다.
종료일 바로 다음 날부터가 시작일이니까요 ㅎㅎ
1-3. raw data와 공통된 조건 만들기 (month, weeknum)
단순한 sum 같은 함수가 아닌 경우라면
엑셀을 이용해 자료를 만들 때, "조건"을 설정하는 상당히 중요합니다.
이 "조건"이 잘 설정되어있어야 함수를 적용할 수 있고,
결과를 볼 수 있고,
함수의 계산 논리를 검토할 수 있고,
raw data의 변경시 자동 변경이 되거나 에러를 확인할 수도 있습니다.
오늘은 결과물 표와 raw data의 공통 조건을 month와 weeknum으로 설정하고 있습니다.
시작일 열 왼쪽으로 열 2개를 추가해서 weeknum과 month함수를 입력합니다.
함수가 걸리는 대상은 C열 시작일로 했지만
C열이나 D열이나 어차피 weeknum과 month가 모두 같기 때문에 아무거나 골라도 A, B열 결과는 같습니다.
A, B열은 결과물에 필요한 내용이 아니니까 나중에 숨김처리를 해주겠습니다.
2. raw data 가공하기 (month, weeknum)
위에서 말씀드렸다시피
raw data와 결과물 사이에 공통 조건이 있어야 함수가 반응을 하고, 값을 구할 수 있습니다.
raw data에서 값을 추출해서 표에 적용시키기 위해 각 행의 weeknum과 month 값을 구합니다.
3. 표 채우기
3-1. 품목별 판매수량 채우기 (sumifs)
이제 판매수량을 채워보겠습니다.
여러 조건을 충족하는 값을 골라서 더해야하기 때문에 sumif를 사용합니다.
=SUMIFS('raw data'!$B:$B,'raw data'!$E:$E,$A3,'raw data'!$G:$G,$B3)raw data 시트의 weeknum과 결과표 시트의 weeknum이 일치하고
=SUMIFS('raw data'!$B:$B,'raw data'!$E:$E,$A3,'raw data'!$G:$G,$B3)
raw data 시트의 month와 결과표 시트의 month가 일치하는
=SUMIFS('raw data'!$B:$B,'raw data'!$E:$E,$A3,'raw data'!$G:$G,$B3)
raw data 시트의 과일 판매량을 더하면
=SUMIFS('raw data'!$B:$B,'raw data'!$E:$E,$A3,'raw data'!$G:$G,$B3)
각 주별 과일 판매량 표를 채울 수 있습니다.
야채 역시 같은 방법으로 해줍니다.
'raw data'!$B:$B가 'raw data'!$C:$C로 바뀌면 야채의 값을 더할 수 있겠죠.
3-2. 품목별 판매 비율 채우기 (iferror)
다음으로 주별 품목별 판매 비율도 채워보겠습니다.
주별 품목별 판매 비율 = 주당 품목별 판매수량 / 주당 전체 판매수량
의 식을 계산하면 품목별 판매비율이 구해집니다.
주별 과일 판매비율 = 주당 과일 판매수량 / (주당 과일 판패수량+야채판매수량) 이 되겠죠.
이렇게 해서 표가 채워졌습니다.
그런데 저는 판매비율 계산식 앞에 iferror 함수를 덮어줄거에요.
지금 예시로 만든 표는 9월까지 날짜와 수량이 모두 채워져있지만
실제 업무를 한다고 생각해보면
어차피 12월까지 이 양식 계속 쓸건데 날짜를 12월까지 채워놓는게 더 편하지 않을까요?
그렇게해서 표를 12월까지 채워놔도 실제 raw data는 미래의 값이 없기 때문에
판매비율 열은 에러가 나타납니다.
계산식 앞에 iferror 함수를 덮어주겠습니다.
=IFERROR(E19/(E19+G19),"")E19/(E19+G19) 식에서 에러가 발생하면 "" 공백으로 치환하라
는 의미가 됩니다.
저는 에러코드 보는걸 별로 안좋아해서
대부분의 상황에서는 iferror를 이용해 에러 발생시 공백을 만들어줍니다.
그냥 보기에 깔끔해서요.
다만,
정말로 함수에 오류가 발생해서 에러코드가 발생한 경우에도 공백으로 치환되니
이 수식에 정말 문제가 없다고 확신하거나
수식의 점검에 별다른 품이 들지 않을 때만 쓰시길 바랍니다.
3-3. 결과표 정리하기 (열 숨기기)
결과표에 값은 다 채워졌습니다.
그런데..
A열, B열은 함수를 위해 만들어놓은 조건이기 때문에 결과 표에서는 보이지 않는게 좋겠죠.
A열, B열을 삭제하면 표의 함수가 작동을 못하기 때문에
열을 숨겨서 "보이지만 않게" 하겠습니다.
숨기고 싶은 열을 선택 > 마우스 우클릭 > 숨기기 를 하면 됩니다.
그러면 숨겨진 열은 보이지 않게됩니다.
A열, B열을 숨긴거지 삭제한게 아니라서
이 시트에서 가장 첫 열은 C열이 되었네요.
만약 중간 열을 숨기기 한다면
마찬가지로 ABC순서로 진행되는 열 이름이 중간에 점프를 하게 됩니다 ㅎㅎ
이제 진짜 표를 완성했습니다.
아래는 결과물입니다!
설명을 쉽게 하고싶은데 그것도 재주인지라 늘 어렵게만 되는 것 같네요
쉽게 쓰도록 노력해볼게요 ㅎㅎ
긴 글 봐주셔서 감사합니다.
'업무 관련 > 엑셀' 카테고리의 다른 글
| 엑셀 파일 "만든 이", "마지막으로 저장한 사람" 등 개인 정보 지우기 (0) | 2023.10.30 |
|---|---|
| Sum의 범위를 Indirect로 지정하기 (0) | 2023.10.23 |
| 규칙이 있는 짧은 문장에서 원하는 글자 추출하기 (1) | 2023.10.11 |
| [Search, Find] 찾으려는 글자는 전체 글자 중에서 몇 번째 위치에 있는가 (0) | 2023.09.18 |
| [조건부서식] 간트 차트 만들기 (1) | 2023.09.12 |