안녕하세요.
거래일자와 거래시간, 거래금액이 저장된 데이터가 있습니다.
이 데이터를 일자별 시간별로 나누어 거래금액 합계와 거래건수를 나타내려고 합니다.
실습 파일 다운로드
완성 파일 다운로드
1행에 날짜를 입력해 두었고, E, F열에 시간을 지정해 두었습니다.
[G1] 셀을 선택해 보면 [홈]-[표시 형식]이 [사용자 지정]으로 되어 있고, 수식입력줄에 날짜 형식으로 입력된 값이 표시됩니다.
[E3] 셀을 선택해 보면 [홈]-[표시 형식]이 [사용자 지정]으로 되어 있고, 수식입력줄에 시간 형식으로 입력된 값이 표시됩니다.
거래일자를 [G1] 셀에 있는 날짜와 같은지 비교하고, 거래시간을 [E3] 셀 시간보다 크거나 같고, [F3] 셀 시간보다 작은지 비교해서 매출 현황을 구하려고 합니다.
그런데 문제가 있습니다.
A열과 B열에 입력된 거래일자와 거래시간 데이터가 보기엔 날짜, 시간 형식인 것 같지만, [홈]-[표시 형식]에 [일반]으로 표시되는 문자 형식입니다.
비교를 하려면 같은 형식이어야 합니다.
입력된 데이터 모양이 날짜, 시간과 같기 때문에 A열 거래일자를 날짜 형식으로 바꾸려면 DATEVALUE 함수를, B열 거래시간을 시간 형식으로 바꾸려면 TIMEVALUE 함수를 이용할 수 있습니다.
함수를 이용해 값을 바꾸려면 새 열을 추가하고, 함수를 이용해 변환한 뒤 [선택하여 붙여넣기] 기능을 이용해 [값]으로 붙여넣는 작업을 거쳐야 해서 불편합니다.
[텍스트 나누기] 기능을 이용하면 휠씬 빠르게 날짜, 시간 형식으로 변환할 수 있습니다.
A열과 B열 데이터를 한꺼번에 바꿀 수는 없고 열 하나씩 선택해 각각 변환해야 합니다.
[A3] 셀에 커서를 두고 단축키 <Ctrl + Shift + 화살표아래쪽>을 눌러 [A3:A2381] 셀 범위를 선택합니다.
[데이터]-[데이터 도구]-[텍스트 나누기]를 선택합니다.
[텍스트 마법사] 1, 2단계를 기본값으로 [다음]을 눌러 넘기고, 3단계에서 [열 데이터 서식]에서 [날짜]를 선택한 뒤 마칩니다.
[A3] 셀을 눌러 범위 선택을 해제한 뒤 [홈]-[표시 형식]을 보면 [날짜] 형식으로 바뀌어 있습니다.
[B3] 셀에 커서를 두고 단축키 <Ctrl + Shift + 화살표아래쪽>을 눌러 [B3:B2381] 셀 범위를 선택합니다.
[데이터]-[데이터 도구]-[텍스트 나누기]를 선택합니다.
[텍스트 마법사] 1, 2단계를 기본값으로 [다음]을 눌러 넘기고, 3단계에서 [열 데이터 서식]에서 [날짜]를 선택한 뒤 마칩니다.
시간 형식을 선택하는 항목은 따로 없지만, 날짜를 선택하면 입력된 데이터가 시간에 해당하는 값이기 때문에 시간 형식으로 변환됩니다.
[B3] 셀을 눌러 범위 선택을 해제한 뒤 [홈]-[표시 형식]을 보면 [사용자 지정] 형식으로 바뀌어 있습니다.
시간 형식이 되었는데 표현되는 모양이 기본적인 시간 모양이 아니라서 사용자 지정 형식으로 표시되는 것입니다.
이제 수식을 작성하기 위한 필수적인 준비 작업이 마무리 되었습니다.
한가지 부수적인 작업을 하겠습니다.
수식에서 A, B, C열 데이터 범위를 절대참조로 나타내야 하는데, 셀 주소를 바로 쓰면 수식이 너무 복잡해 보이게 됩니다.
[이름 정의] 기능을 써서 수식을 알아보기 쉽게 작성하도록 작업을 합니다.
이름 정의할 범위를 한꺼번에 선택하기 위해 [A2] 셀을 선택하고 단축키 <Ctrl + *>를 선택합니다.
*는 키보드 오른쪽 숫자 키패드의 별표(*)를 의미합니다.
노트북이거나 숫자 키패드가 없는 키보드를 쓰는 경우엔 단축키 <Ctrl + Shift + 8>을 누릅니다.
단축키 <Ctrl + *>를 누르면 현재 커서가 놓여져 있는 [A2] 셀을 기준으로 데이터베이스 범위를 한 번에 범위 선택합니다.
[수식]-[정의된 이름]-[선택 영역에서 만들기]를 선택합니다.
[왼쪽 열] 체크는 해제하고 [첫 행]만 체크된 상태에서 [확인]을 누릅니다.
[이름 정의]가 되었습니다.
단축키 <Ctrl + F3>을 눌러 이름 정의된 항목을 확인 합니다.
수식을 작성한 뒤 데이터를 추가로 입력하거나 삭제하는 경우 [참조 대상]을 수정해야 합니다.
[닫기]를 누릅니다.
[G3] 셀에 커서를 두고 수식을 작성합니다.
=su 를 입력해 나타나는 함수 목록에서 SUMIFS를 더블 클릭합니다.
단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.
Sum_range 인수에 거래금액을 직접 입력하고, Criteria_range1 인수에 거래일자를 직접 입력합니다.
Criteria1 인수에 [G1] 셀을 선택해 나타내고 <F4>를 두 번 눌러 [G$1] 혼합참조로 나타냅니다.
다른 셀에도 수식을 채우기하기 위해 혼합참조를 사용했습니다.
Criteria_range2 인수에 직접 거래시간을 입력합니다.
Criteria2 인수에 ">=" & 를 입력한 뒤 [E3] 셀을 선택해 나타내고 <F4>를 세 번 눌러 [$E3]으로 나타냅니다.
Criteria2 인수에 작성된 내용은 ">=" & $E3 입니다.
Criteria_range3 인수에 직접 거래시간을 입력합니다.
Criteria3 인수에 "<" & $F3 으로 나타냅니다.
거래시간이 [E3] 셀의 0시보다 크거나 같고, [F3] 셀의 2시보다 작은 경우를 조건으로 지정했습니다.
[확인]을 눌려 결과를 봅니다.
올바른 결과 값이 나타났는데, 3월 29일 0시부터 2시 사이엔 거래 내역이 없어 0입니다.
[G3] 셀 채우기 핸들을 드래그해서 [G14] 셀까지 채웁니다.
[G14] 셀 수식은 수정을 해야 합니다.
[G14] 셀을 선택하고 단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.
[E14] 셀 22시보다는 크거나 같아야 하는 조건은 맞지만, [F14] 셀 값 0시는 [E3] 셀 값 0시와 같은 시간을 의미합니다.
조건표를 작성할 때 [F14] 셀 값 0시는 다음날 0시를 의미해서 입력했지만, 오늘 0시와 다음날 0시를 구분해서 입력할 수는 없습니다.
Criteria1 인수에서 날짜를 지정했기 때문에 Criteria2 인수에서 '22시보다 크거나 같다'라고만 지정해도 조건이 완성됩니다.
Criteria_range3 인수와 Criteria3 인수는 없어야 합니다.
[확인]을 누릅니다.
[H3] 셀에 커서를 두고 건별을 구합니다.
=cou 까지 입력한 뒤 함수 목록에서 COUNTIFS를 더블 클릭합니다.
단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.
Criteria_range1 인수에 거래일자, Criteria1 인수에 G$1, Criteria_range2 인수에 거래시간, Criteria2 인수에 ">=" & $E3, Criteria_range3 인수에 거래시간, Criteria3 인수에 "<" & $F3을 입력합니다.
[확인]을 눌러 결과를 나타낸 뒤 [H14] 셀까지 채우기 합니다.
[H14] 셀을 선택하고 단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.
Criteria_range3 인수와 Criteria3 인수 값을 지웁니다.
수식을 수정하는 이유는 [G14] 셀 수식을 수정한 이유와 같습니다.
[확인]을 누릅니다.
[G3:H14] 셀 범위를 선택한 뒤 복사합니다.
[I3:T14] 셀 범위를 선택합니다.
[붙여넣기] 합니다.
데이터가 모두 입력되어 있지 않았기 때문에 4월 2일부터 4월 4일까지 모두 계산되지 않았습니다.
데이터를 추가하고 [이름 정의]에서 [참조 대상]을 수정하면 자동으로 계산됩니다.
필요하신 분들께 도움이 되길 바랍니다.
'엑셀' 카테고리의 다른 글
| 임금대장에서 작성한 내용을 가져와 자동으로 급여명세서 출력용 시트 만들기 (0) | 2021.04.16 |
|---|---|
| 한 셀에 형식이 다른 값이 입력되어 있을 때 구분해서 계산하기 (0) | 2021.04.08 |
| 달력 만들기 3 - 공휴일, 특정일 내용 나타내기 (0) | 2021.03.03 |
| 달력 만들기 2 - 공휴일, 특정일을 휴일로 나타내기 (0) | 2021.02.22 |
| 조건부 서식으로 다섯줄마다 번갈아 색상 채우기 (1) | 2021.02.20 |