엑셀에서 방대한 매출 장표나 정산 대장을 다루다 보면 일자별 데이터 외에 주간 흐름 분석을 위해 요일별 통계 지표를 도출해야 하는 상황을 자주 마주하게 됩니다. 월요일부터 일요일까지 요일별로 매칭되는 누적 매출이나 판매 금액의 합계를 구하는 방법은 다양합니다. 많은 분들이 날짜 옆에 요일을 구하는 별도의 보조 열을 새로 추가한 뒤 필터를 걸거나 일일이 수작업으로 더하곤 하지만 데이터 양이 많아지면 연산 속도가 느려지고 표의 레이아웃이 지저분해지기 일쑤입니다. 오늘 별도의 보조 열을 지저분하게 만들지 않고 날짜 자체에서 요일을 추출하여 합계를 단 5초 만에 일괄 계산하는 엑셀 전문가의 실무 결합 수식 노하우를 상세히 공유해 드리겠습니다.
1. 날짜 데이터에서 요일을 시각적으로 변환하는 셀 서식 사용자 지정 메커니즘
엑셀 워크시트에 기입된 날짜 데이터는 겉보기에는 연, 월, 일 형태이지만 내부적으로는 고유한 일련번호 숫자로 처리되는 성격을 가지고 있습니다.
이러한 일자 원본 데이터에서 요일별 통계를 정확하게 도출하기 위한 첫 단계는 요일을 표시할 기준 셀의 표시 형식을 조율하는 것입니다.
요일을 입력해 둘 빈 셀 범위를 마우스 블록으로 길게 지정한 상태에서 격자 서식 창 단축키인 [Ctrl + 1]을 동시에 타건해 줍니다.
화면에 셀 서식 대화상자가 전면에 활성화되면 첫 번째 표시 형식 탭의 맨 아래 카테고리인 [사용자 지정]을 마우스로 클릭합니다.
오른쪽에 배치된 형식 입력 칸에 영어 소문자로 aaa를 타이핑해 넣으면 일, 월, 화 형태의 짤막한 요일 레이아웃이 생성됩니다.
만약 보고서의 가시성을 한층 더 높여서 월요일, 화요일처럼 요일 글자까지 무결하게 표현하고 싶다면 aaaa를 입력해 주시면 됩니다.
이 조작은 셀에 입력된 수치 데이터의 알맹이를 훼손하지 않으면서 오직 화면에 노출되는 시각 포맷만 요일 형태로 바꾸어 주는 유용한 기본 기능입니다.
2. WEEKDAY 함수와 일련번호 매칭을 통한 요일 기준축 설계 및 입력 수칙
셀 서식 설정을 마쳤다면 이제 요일 형태의 서식을 씌운 셀에 엑셀 시스템이 인지할 수 있는 기준 숫자를 기입하여 규칙성을 수립해야 합니다.
사용자 지정 서식을 aaa나 aaaa로 맞춘 셀에 마우스 커서를 두고 숫자 1을 입력한 뒤 엔터를 누르면 화면에는 자석처럼 일 또는 일요일이 표시됩니다.
엑셀 내부의 날짜 판독 엔진은 요일을 계산할 때 일요일을 시작점으로 인지하여 숫자 1부터 토요일인 숫자 7까지 순차적인 정수 코드를 부여하기 때문입니다.
따라서 월요일 데이터를 모으고 싶다면 옆 칸에 숫자 2를 입력하고 화요일은 3을 입력하는 방식으로 일요일부터 토요일까지의 일련 코드를 확장해야 합니다.
채우기 핸들을 가동해 원하는 요일 구간까지 마우스 드래그로 늘려주면 화면에는 월, 화, 수 순서대로 정돈된 가로 기준축 표 서식이 완성됩니다.
겉으로는 글자로 보이지만 실제 셀 주소가 품고 있는 본질은 1부터 7까지의 상숫값이므로 향후 함수 연산 시 부딪히는 텍스트 미매칭 오류를 원천 차단해 줍니다.
인원이 많거나 항목이 거대해지는 대형 프로젝트 장표일수록 이러한 숫자 기반의 데이터 형식을 확립해 두어야 후속 조건 정산이 매끄럽게 종결됩니다.
3. SUMPRODUCT와 WEEKDAY 함수의 상호작용을 이용한 요일별 합계 결합 수식 가이드
요일 기준축 설계가 무결하게 종결되었다면 이제 날짜 배열을 순회하며 특정 요일에 부합하는 금액만 쏙 골라 합산하는 마스터 수식을 작성할 차례입니다.
합계 결과가 도출될 빈 셀을 선택하고 수식 입력줄에 등호와 함께 다중 배열 연산을 수행하는 =SUMPRODUCT(( 구문을 명확하게 타이핑합니다.
여기서 주의할 점은 SUMPRODUCT 함수명 바로 뒤에 오는 괄호를 한 개가 아니라 반드시 두 개를 연속으로 열어주어야 배열 조건절이 성립된다는 점입니다.
이어서 날짜에서 요일 숫자를 발라내는 주 함수인 WEEKDAY(를 입력하고 가공할 날짜 데이터 범위 전체를 마우스로 길게 블록 지정해 줍니다.
수식을 아래나 옆 행으로 자동 채우기 할 때 데이터 영역이 밀려 수치가 깨지는 것을 막기 위해 반드시 [F4] 키를 한 번 눌러 절대 참조($A$2:$A$20) 서식으로 묶어줍니다.
괄호를 닫은 뒤 일대일 매칭 연산자인 등호(=)를 기입하고 앞서 2단계에서 일련번호로 정돈해 두었던 요일 기준 셀 주소를 마우스로 지정합니다.
그다음 배열 조건 괄호를 닫아준 뒤 곱하기 기호인 별표(*)를 누르고 최종 합산할 실제 수치 금액 데이터 범위 전체를 블록 지정한 뒤 마찬가지로 [F4] 키로 절대 참조를 설정합니다.
최종 공식은 =SUMPRODUCT((WEEKDAY(날짜절대참조)=요일셀)*금액절대참조) 형태가 되며 엔터를 치는 순간 요일별 누적 합계 수치가 한치의 오차도 없이 일괄 산출됩니다.
💡 엑셀 전문가의 한끗 차이 실무 팁 (주말 제외 평일 합계 구하기)
실무에서는 월요일부터 일요일까지 개별적으로 합계를 구하는 상황 외에도 "토요일과 일요일 주말을 제외한 순수 평일(월~금) 실적의 총합"을 한 번에 요약해야 하는 상황이 자주 발생합니다.
이럴 때도 동일한 메커니즘을 응용하면 복잡한 다중 조건 함수를 여러 번 더할 필요 없이 단 한 줄의 수식으로 깔끔하게 마감할 수 있습니다.
수식 입력창에 =SUMPRODUCT((WEEKDAY(날짜절대참조, 2)<6)*금액절대참조) 형태로 포맷 코드를 설계해 보세요.
WEEKDAY 함수의 두 번째 옵션 인수에 숫자 2를 부여하면 시스템은 월요일을 숫자 1로 인지하고 토요일을 6, 일요일을 7로 재정렬하게 됩니다.
따라서 6보다 작다(<6)라는 조건을 결합해 주면 백스테이지에서 월요일(1)부터 금요일(5)까지의 데이터만 자석처럼 골라내어 금액 열과 일괄 연산해 줍니다.
불필요한 보조 행을 개설하지 않고 장표의 가시적 노이즈를 완벽하게 통제할 수 있는 전문가들의 숨겨진 단골 치트키 자산입니다.
4. 데이터 무결성 보존을 위한 값 서식 고정 및 최종 마감 수칙
결합 수식 작성을 통해 요일별 합계 레이아웃을 성공적으로 빌딩했다면 마우스 조작 한 번으로 채우기 핸들을 늘려 나머지 요일 칸까지 수식을 전파해 줍니다.
순식간에 일주일 간의 모든 정산 수치가 정형화되어 나열되는 경이로운 완료 화면이 나타나지만 문서를 배포하기 전에는 사후 검증 작업을 이행해야 합니다.
가장 먼저 선결해야 할 과제는 요일별로 산출된 개별 합계 값들의 총액 합산 수치와 원본 로우 데이터 전체의 총합계 수치가 단 일 원의 오차도 없이 일치하는지 크로스 체크를 이행하는 것입니다.
또한 현재 상태는 원본 시트의 날짜와 금액 좌표를 실시간으로 추적하는 동적 참조 배열 상태이므로 향후 원본을 수정하거나 행을 삭제하면 수식이 밀리는 리스크가 상시 존재합니다.
데이터 신뢰도를 최고 수준으로 확립하기 위해 합계 결과 열 전체를 블록 지정하여 복사 단축키인 [Ctrl + C]를 눌러 줍니다.
그 자리에 곧바로 선택하여 붙여넣기 단축키인 [Ctrl + Alt + V]를 타건하고 목록 옵션 중에서 [값] 라디오 단추를 체크한 뒤 확인을 눌러 마감합니다.
무겁게 구동되던 계산 공식 껍데기를 소거하고 순수한 상숫값 데이터로만 완벽하게 고정 박제해 주어야 향후 다른 시트로 이동하더라도 서식이 안전하게 보존됩니다.
단순 반복 복사 노가다에 소모되던 비효율적인 편집 시간을 혁신적으로 단축시키는 화면 제어 기술을 온전히 본인의 자산으로 흡수하셔서 신속하게 담당 업무를 종결하고 매일 기분 좋게 칼퇴를 성취하시기 바랍니다.