해당 달이 끝나지 않으면 배제시키고 그 전 달까지의 값을 구하는 방법입니다
판매데이터를 만들 때 해당 월이 끝나지 않았다면 종종 계산 오류가 생깁니다
하지만 일일히 매달 초에 전 달 값을 일일히 넣는건 시간이 많이듭니다
결정적으로 여러번 파일 만드는건 귀찮습니다
오늘 사용할 함수는
조건을 설정하는 IF 함수와 날짜를 설정하는 DATE, DAY, MONTH, YEAR 함수 입니다
위 함수를 잘 몰라도 아래 내용에 따라 범위 설정만 잘 하면
사무실에서, 실생활에서 사용하기 좋습니다
만약 IF 함수나 날짜 서식 및 날짜 관련이 궁금하시다면 아래 글 참고해주세요
엑셀 IF함수 간단하게 사용하는법
엑셀 IF 함수 간단하게 사용하는법
IF 함수 생각보다 별거 없습니다 사실 기초 엑셀 문서 작업에는 잘 사용하지 않지만 엑셀 IF 함수를 이해를 해야 엑셀 함수 응용 범위가 늘어나 또 엑셀 함수들이 다 그렇지만 문서 작성 시간을
kaorouhaochi.tistory.com
엑셀 날짜 서식 및 지정하기
엑셀 날짜 서식 지정 엑셀 날짜 자동변환하기 날짜 자동 변환 및 응용하기
매주 보고서를 작성할 때 엑셀에서 날짜 기간을 일일히 적는건 매우 귀찮은 일입니다 엑셀 text함수와 엑셀 기능을 사용해서 자동으로 날짜계산하는 법을 알아보겠습니다 엑셀 날짜 서식만
kaorouhaochi.tistory.com
특정 날짜에 도달하지 않으면 계산에서 배제시키기
판매 데이터를 만들 때
매월 1일이 되야 그 전 달의 정확한 판매내역을 확인할 수 있으니
마감이 끝나지 않은 달을 배제시켜서 계산합니다
예를 들어 이렇게 4월~6월까지의 판매 금액이 있습니다
정확한 월별 평균 판매 금액을 구하려면
아직 판매가 마감되지 않은 미완성의 6월분을 배제시키고 구해야합니다
사실 노가다를 하면 수식이 매우 간단합니다
매달 AVERAGE 함수의 범위를 고쳐주면 됩니다
하지만 매번 수정하기에는 귀찮아지니
AVERAGEIFS 함수와 MONTH, TODAY를 사용해서
자동으로 알아서 계산하게 만들어보겠습니다
엑셀 특정 기간 계산하는 방법
=AVERAGEIFS(평균값 구할 범위, 평균을 구할 달, "<" & MONTH(TODAY()))
여기서 복잡해보이는 것을 가장 쉽게 설명하면 아래와 같습니다
TODAY() 오늘 날짜의
MONTH() 해당하는 달이
평균을 구할 달, "<" & 평균을 구할 달 보다 작은
AVERAGEIFS() 조건(들)을 만족시키는 평균을 계산
여기서 더 합쳐서 보면 아래와 같습니다
MONTH(TODAY()) 오늘 날짜를 월로 표시
여기서 더 합치면 아래와 같습니다
"<" & MONTH(TODAY()) 이번달보다 작은 달까지만
참고로 AVERAGEIFS는 AVERAGE 함수(평균)과 IFS함수(조건(들))이 합쳐진 함수로
엑셀로 특정 조건들에 부합하는 평균을 구할 때 사용합니다
추가로 말하자면
위 경우는 조건이 1개이기 때문에 AVERAGEIF도 사용이 가능하지만
역으로 조건이 1개 이상이면 조건들인 IFS에도 부합되기 때문에
AVERAGEIF를 사용하기보다는 AVERAGEIFS를 추천합니다
AVERAGEIFS를 사용하는게 언제 조건이 추가될지도 모르는 일이기도 하고
수식들을 통일하기 편합니다
참고로 IF로 끝나는 함수와 IFS로 끝나는 함수는 조건 지정 위치가 다릅니다
AVERAGEIFS와 AVERAGEIF 지정 범위를 비교하자면
아래와 같습니다
=AVERAGEIF( 평균을 구할 달, "<" & MONTH(TODAY()), 평균값 구할 범위 )
=AVERAGEIFS(평균값 구할 범위, 평균을 구할 달, "<" & MONTH(TODAY()))
엑셀 특정 기간 계산하는 법 적용하기
위 내용을 반영하면 아래와 같은 수식이 됩니다
=AVERAGEIFS(B3:B5, $A$3:$A$5, "<" & MONTH(TODAY()))
참고로 저는 평균을 구하려고 AVERAGEIFS 함수를 사용했지만
아래와 같이 SUMIFS로 변경하면 합계도 가능합니다
=SUMIFS(B3:B5, $A$3:$A$5, "<" & MONTH(TODAY()))
엑셀 증감률 계산하기
이번에는 특정 달에 도달하면 증감률을 계산하고
특정 달이 끝나지 않으면 "마감전"이라는 텍스트가 출력되는 수식입니다
크게 나누면 월별로만 지정하기와 년도에 맞는월 구분하기
두가지로 나뉘는데
보통은 년결산이 아니고서야 월별로만 비교하기 때문에
굳이 년도까지 정확하게 지정할 필요 없습니다
1) 엑셀 월별로 증감률 계산하기 (추천)
= IF (MONTH(TODAY()) < 7, "마감전", (B12-B11)/B11)
간단하게 설명하면 아래와 같습니다
IF 만약에
TODAY() 오늘 날짜에
MONTH() 해당하는 달이
< 7, 7월 전이라면
"마감전", 을 출력하고
아니라면 증감률을 계산
2) 엑셀 년도까지 정확하게 증감률 계산하기-)
=IF(TODAY() < DATE(년,월,일), "마감전", 증감률계산식)
위 수식도 가장 간단하게 설명하면 아래와 같습니다
IF 만약에
TODAY() 오늘날짜가
<DATE(년,월,일), 보다 이전 날짜라면
"마감전"을 출력하고
아니라면 증감률을 계산
위 내용을 반영하면 아래와 같은 수식이 됩니다
=IF(TODAY() < DATE(2024, 7, 1), "마감전", (B5-B4)/B4)
혹은
=IF(TODAY() < DATE(YEAR(TODAY()), 7, 1), "마감전", (B5-B4)/B4)
DATE(2024, 7, 1)와 DATE(YEAR(TODAY()), 7, 1)의 차이는
특정 년도를 고정 하는지 와 매년 년도가 변동되는지
2024를 쓰면 말그대로 24년도만 계산되고
예를들어 YEAR(TODAY())를 사용하면
24년에 파일을 켰을 때에는 24년 날짜가 인식이 되고
25년에 켰을 때에는 25년 날짜가 인식됩니다
증감률 오류 대처 방법
1) 월 합계나 월 평균 값 오류
이런 AVERAGEIFS 값이 맞지 않는 경우에는
조건이나 범위에 대한 서식이 올바르지 않아 생깁니다
4월, 5월, 6월 서식이 아래 이미지와 같게 설정되어 있는지 확인해주세요
"월"이라는 문자는 서식으로 설정해야합니다
위 이미지와 같이 A열에는 문자+숫자가 아닌 숫자만 들어가야
수식에서 인식할 수 있습니다
2) 특정 월 증감률 오류
해당하는 월에 맞게 날짜 지정했는지 확인해주세요
5월 증감률은 6월1일이 되어야 5월 마감이 되어
증감률이 계산 가능하니 6월 1일 전을 지정하는
YEAR(TODAY()), 6, 1 이 되어야하고
6월 증감률은 마찬가지로 7월 1일이 되야
6월분 마감이 되니
YEAR(TODAY()), 7, 1 이 되어야합니다
이렇게 엑셀로 데이터 분석을 할때나 증감률 계산을 위한
엑셀 자동 계산 파일 만들기 기본 수식이 완성되었습니다
아래 위 내용을 정리한 파일 첨부해두었으니 참고해주세요
'컴맹도 할 수 있다 > 엑셀' 카테고리의 다른 글
| SUMIFS 함수 엑셀 다중 조건의 합계 쉽게 구하는 방법 sum 다중조건 연습파일 첨부 (0) | 2024.05.25 |
|---|---|
| 엑셀 vlookup 다중조건 간단하게 구하기 vlookup 실무 활용하기 (0) | 2024.05.20 |
| 엑셀 날짜 서식 지정 엑셀 날짜 자동변환하기 날짜 자동 변환 및 응용하기 (0) | 2024.05.20 |
| 엑셀 지정 값에 자동으로 컬러 표시하기 엑셀 값 위치 표시 (0) | 2024.05.18 |
| 엑셀 매크로 만들기 선택한 셀 중심으로 자동 컬러 크로스라인 표시하기 예제파일 (0) | 2024.05.18 |
