초과근무시간 개인별 합계 구하기(데이터 형식 변환)

안녕하세요.

 

오랜만에 대면 수업을 하러 갔습니다.

고등학교에 근무하시는 분이 질문을 하셨습니다.

 

학교 업무 프로그램에서 다운로드한 엑셀 파일 데이터를 이용해서 일자별 초과근무시간으로 개인별 초과근무시간 합계를 구하려고 합니다.

 

실습 파일 다운로드

초과근무확인자료_실습.xlsx

0.01MB

 

완성 파일 다운로드

초과근무확인자료_완성.xlsx

0.02MB

 

실습 파일을 먼저 보겠습니다.

실습 파일 내용은 실제 사용하는 파일과 비슷하게 만든 파일로 이름, 일자등의 내용은 임의로 바꿨습니다.

 

J열 시간합이 초과근무시간입니다.

초과근무시간 합이 바로 구해지지 않는 이유는 J열에 표시된 시간이 텍스트 형식으로 되어 있기 때문이었습니다.

 

[J2] 셀을 선택한 뒤 [홈]-[표시 형식]을 보면 텍스트 표시 형식입니다.

계산을 하려면 계산이 가능한 데이터 형식인 숫자나 날짜/시간 형식이어야 하는데, 텍스트 형식이기 때문에 합계를 구할 수 없습니다.

텍스트 형식을 변환해야 초과근무시간 합을 구할 수 있습니다.

 

[K2] 셀에 커서를 두고 =va를 입력한 뒤 VALUE 함수를 더블 클릭합니다.

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Text 인수에 [J2] 셀을 선택해 셀 주소를 나타냅니다.

[확인]을 누르면 4시간에 해당하는 숫자값이 구해집니다.

엑셀은 하루 24시간을 숫자 1로 나타내고, 엑셀 내부적으로 시간을 숫자값으로 계산합니다.

예를 들어 6시간은 0.25가 되고 12시간은 0.5로 나타납니다.

 

[K2] 셀 채우기 핸들을 더블 클릭해 수식을 채우기 합니다.

 

이제 이름별 초과근무시간 합을 구하기 위해 B열 이름 항목을 M열에 복사하고 [중복된 항목 제거]를 합니다.

 

[B1] 셀에 커서를 두고 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 [B1:B82] 셀 범위를 선택합니다.

단축키 <Ctrl + C>를 눌러 복사하고 [M1] 셀을 선택하고 단축키 <Ctrl + V>를 눌러 붙여 넣습니다.

[데이터]-[데이터 도구]-[중복된 항목 제거]를 누릅니다.

[중복 값 제거] 대화상자에서 [확인]을 누릅니다.

총 81개 데이터 중 중복된 값 76개를 제거하고 5개 항목만 남았습니다.

[확인]을 눌러 결과를 확인합니다.

 

[N2] 셀을 선택해 SUMIF 함수로 이름별 초과근무시간 합을 구합니다.

 

[N2] 셀에 커서를 두고 =su를 입력한 뒤 나타나는 함수 목록에서 SUMIF를 더블 클릭합니다.

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Range 인수에 [B2:B82] 셀 범위를 나타낸 뒤 <F4>키를 눌러 절대 참조 합니다.

Criteria 인수에 [M2] 셀을 나타냅니다.

Sum_range 인수에 [K2:K82] 셀 범위를 나타낸 뒤 <F4>키를 눌러 절대 참조 합니다.

[확인]을 눌러 결과를 나타냅니다.

 

[N2] 셀에서 채우기 핸들을 더블 클릭해 [N6]셀까지 채웁니다.

숫자값으로 계산되어 나타난 결과를 시간 표시형식으로 나타내기 위해 단축키 <Ctrl + 1>을 눌러 셀 서식 대화상자를 나타냅니다.

[사용자 지정] 범주를 선택하고 [형식]에 [h]:mm을 입력합니다.

[확인]을 눌러 결과를 표시합니다.

형식에서 h는 시간을 의미하고, m은 분을 의미합니다.

h만 적으면 24시간을 초과한 부분을 제외하고 나머지만 나타내므로 24시간 이상도 포함해 시간으로 표시하도록 [h]로 나타냅니다.

 

[K2:K82] 셀 범위 시간에 해당하는 숫자로 변환된 값도 시간 형식으로 나타내려면 셀 서식에서 형식을 바꾸면 됩니다.

 

[K2] 셀에 커서를 두고 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 범위를 선택합니다.

단축키 <Ctrl + 1>을 눌러 셀 서식 대화상자를 나타냅니다.

범주에서 [사용자 지정]을 선택하고 [형식]에 h:mm을 입력합니다.

[K2:K82] 셀 범위 값은 24시간을 넘지 않기 때문에 형식으로 h:mm을 입력해도 결과가 바뀌어 나타나지 않습니다.

 

[확인]을 눌러 결과를 나타냅니다.

 

개인별 초과근무시간 합을 구했습니다.

 

필요하신 분께 도움되길 바라며 마치겠습니다.

수고하셨습니다.