조건 함수에서 날짜 범위 사용하기

실제 보고서에서는 거의 항상 기간을 기준으로 질문합니다. 이번 분기의 판매액, 지난달의 주문 수, 두 날짜 사이의 가입자 수 등이 그 예입니다. 지금까지 배운 조건 함수인 SUMIFS, COUNTIFS, AVERAGEIFS는 날짜 범위를 표현하는 방법만 알면 이러한 작업을 훌륭하게 처리합니다.

핵심은 날짜 범위가 실제로는 같은 날짜 열에 적용되는 두 개의 조건이라는 점입니다. 시작일과 같거나 이후이고, 종료일과 같거나 이전이어야 합니다.

스프레드시트는 날짜를 일련 번호로 저장합니다. 1일은 1900년 1월 1일(스프레드시트에서는 1899년)이고, 그 이후 날짜는 하루마다 1씩 증가합니다. 따라서 일반 숫자와 똑같이 >와 <로 날짜를 비교할 수 있습니다.

그러므로 1월 1일 이후라는 말은 단순히 해당 날짜의 일련 번호보다 큰 일련 번호라는 뜻입니다. 이 핵심 개념 덕분에 날짜 필터링이 가능합니다.

A열에 주문 날짜가 있고 C열에 금액이 있다고 가정해 보겠습니다. 2024년 1월의 판매액을 합산하려면 A열을 두 번 지정합니다. 1월 1일 이후라는 조건과 1월 31일 이전이라는 조건을 적용하는 것입니다.

지역별 형식에 관계없이 날짜를 명확하게 처리하려면 날짜를 DATE(year, month, day) 함수로 감싸십시오. 두 조건은 AND로 결합되어 해당 월의 행만 선택합니다.

">=1/1/2024"를 직접 입력하고 싶을 수도 있습니다. 이렇게 해도 작동하는 경우가 많지만 취약한 방법입니다. 스프레드시트가 이를 텍스트로 읽거나 일과 월의 순서를 잘못 해석할 수 있기 때문입니다.

안정적인 패턴은 ">="&DATE(2024,1,1)입니다. DATE 함수가 실제 일련 번호를 만들고, &가 연산자를 그 일련 번호에 연결합니다. 이 방법은 어떤 지역 설정에서도 Excel과 Google 스프레드시트 모두에서 안정적으로 작동합니다.

고정된 보고서라면 날짜를 직접 입력해도 괜찮지만, 유연한 보고서는 시작일과 종료일을 셀에서 읽습니다. F1에 시작일을 입력하고 F2에 종료일을 입력하십시오.

이제 시트가 기간을 결정합니다. F1이나 F2를 변경하면 모든 합계가 다시 계산됩니다. 항상 그렇듯이 &로 연산자를 셀에 연결하십시오. 셀 이름을 따옴표 안에 넣으면 안 됩니다.

경계 하나만 필요한 경우도 있습니다. 특정 날짜 이후의 모든 항목에는 크거나 같음 조건 하나만 사용합니다. 특정 날짜까지의 모든 항목에는 작거나 같음 조건 하나만 사용합니다.

이렇게 하면 F1의 날짜 이후에 주문된 모든 주문을 상한 없이 계산할 수 있습니다. 이는 '출시 후 매출'과 같은 지표에 유용합니다.

날짜 조건은 텍스트 조건 및 숫자 조건과 자유롭게 함께 사용할 수 있습니다. 특정 기간의 동부 지역 판매액을 합산하려면 두 개의 날짜 조건 쌍과 함께 지역 조건 쌍을 추가하십시오.

조건의 순서는 결과에 영향을 주지 않습니다. Excel은 모든 조건을 하나의 큰 AND로 평가합니다. 여기서는 세 조건 쌍이 같은 평균 범위 또는 합계 범위를 공유합니다.

한 해 전체를 합산하려면 해당 연도의 첫날과 마지막 날을 경계로 설정합니다. 시작일에는 DATE를 사용하고 기간의 마지막 날을 지정하십시오.

한 달만 계산할 때는 그달의 1일을 하한으로 사용하고, 다음 달의 1일을 엄격한 "<" 상한으로 사용하십시오. 이렇게 하면 해당 월이 28일인지 30일인지 31일인지 걱정하지 않아도 됩니다.

계속 갱신되는 보고서에는 TODAY()를 사용해 경계를 만드십시오. 최근 30일 동안의 주문을 계산하려면 하한은 오늘 날짜에서 30을 뺀 날짜이고, 상한은 오늘 날짜입니다.

시트가 다시 계산될 때마다 TODAY()가 갱신되므로 기간도 자동으로 앞으로 이동합니다. 수동으로 수정할 필요가 없습니다.

날짜 열에 실제로 날짜와 시간이 함께 저장되어 있다면 타임스탬프처럼 처리됩니다. 1월 31일 늦은 시간에 해당하는 행은 1월 31일 하루 전체의 값보다 일련 번호가 약간 큽니다. 따라서 "<="&DATE(2024,1,31) 경계는 해당 행을 제외합니다.

안전한 해결 방법은 다음 날을 기준으로 엄격하게 작은 값을 사용하는 패턴입니다. "<"&DATE(2024,2,1)을 사용하면 타임스탬프를 포함해 1월의 모든 순간을 선택할 수 있습니다.

같은 날짜 범위 패턴을 AVERAGEIFS에도 사용할 수 있습니다. 특정 기간 내 평균 주문 금액을 구하려면 금액 열을 평균 범위로 지정하고 날짜 열에 두 개의 날짜 조건을 추가하십시오.

빈 기간의 함정을 기억하십시오. 해당 날짜 사이에 주문이 없으면 AVERAGEIFS는 #DIV/0!을 반환합니다. 수식을 IFERROR로 감싸면 특정 기간에 데이터가 없을 때도 시간 필터가 적용된 대시보드를 깔끔하게 유지할 수 있습니다.

조건 함수에서 날짜를 비교하는 안정적인 방법을 떠올려 보십시오.

이제 SUMIFS, COUNTIFS, AVERAGEIFS를 시간 기준으로 필터링할 수 있습니다.

• 날짜 범위는 같은 날짜 열에 적용되는 두 개의 조건입니다(시작일 이상, 종료일 이하).
• DATE(y,m,d)로 날짜를 만들고 ">="&로 연산자를 연결하십시오.
• 월을 계산할 때는 타임스탬프를 처리할 수 있도록 다음 날을 기준으로 엄격하게 작은 값을 상한으로 사용하십시오("<"&DATE(...)).
• 최근 30일과 같이 계속 이동하는 기간에는 TODAY()를 사용하십시오.

이로써 다중 조건 IFS 계열에 대한 학습을 마칩니다.