등록번호별로 운행, 충전중, 완충과 구동시간이 표시된 데이터를 기준으로 월별 최소, 최대, 평균 충전 사용 시간을 구하려고 합니다. 그런데, 운행과 충전중, 완충 데이터가 연속되는 경우도 있고, 최초 충전의 경우는 이전 충전 기록이 없음에 따라 이들을 고려한 수식을 만들려고 합니다.
1. 문제
아래 표를 보면 A열이 구동시간, B열이 년-월, C열이 날짜, D열이 배터리 상태, E열이 등록번호, F열이 배터리 잔량, G가 최종시간으로 되어 있고,
오른쪽에 원하는 집계표 양식이 있습니다. 월별로 1회 충전 사용 시간의 최소, 최대, 평균 값을 구하는 것입니다.
2. 문제의 정의
문제를 풀려면 문제가 뭔지 알아야 합니다.
처음에는 완충이 왜 여러 번 나오지, 다른 차라서 그렇겠지 했는데, 차는 같은데 계속 꽂아 놓아서 10분 간격으로 완충이라고 표시되는 것이었다.
완충 시간이 연속될 경우 시간을 어떻게 구하는지 물어보니 첫번째 완충에서 이전 마지막 완충시간을 빼서 구한다고 한다.
완충이 아니라 충전중이다가 운행을 할 경우에도 최초 충전중 시간에서 이전 최종 완충 또는 충전중 시간을 뺀다
처음에는 충전중이었다가 완충이 되는 경우에 완충에서 이전의 충전중 또는 완충시간을 빼는 거라고 잘못 생각하고,
아래와 같은 경우 완충이 179행에 있고, 이전 충전중이 168행에 있으니 179행의 A열에서 168행의 A열을 빼니 1:49:35이고, 중간에 운행에서 충전중일 때는 게산 안하는 것인 줄 알았다.
그러나. 운행에서 충전중으로 바뀌는 시점에 충전중인 172행에서 168행을 빼는 것이 맞았다.
정리를 하면 운행에서 충전중 또는 완충으로 바뀔 때(신기하게 충전중을 거치지 않고 바로 완충 표시가 나옴) 운행 이전의 충전중 또는 완충 시간을 빼서 운행 시간을 구하는 것이다. => 기준 1
그리고, 이전 충전 기록이 없을 때 0으로 표시할 것이지, 아니면 최초 운행 시간을 기준으로 할 것인지 의문이 있는데, 0으로 표시하는 것보다는 최초 운행시간을 기준으로 하는 것이 맞다. => 기준 2
3. 해결방법
이제 문제와 기준을 알았으니 수식만 만들면 된다.
가. 텍스트로 된 날짜 데이터 변환하기
A2셀에서 A4셀까지 범위를 잡은 후 상태 표시줄을 보니 개수: 3이라고 표시된다. 날짜 형식이 아니라 텍스트이다.
(1) 날짜 시간 데이터 바꾸기
H2셀에 1이라고 입력하고 복사한 후 A2셀부터 맨 아래까지 선택한 후 마우스 오른쪽 버튼을 누른 후 선택하여 붙여넣기를 누르고(오른쪽 꺾기를 누르면 한번 더 선택하여 붙여넣기를 눌러야 하므로 불편),
곱하기를 누르고, 확인 버튼을 누른다.
그러면 모두 실수로 바뀌는데 날짜만 있으면 정수인데, 시간까지 있어서 실수가 되는 것입니다.
이제 A열의 표시형식을 yyyy-mm-dd hh:mm:ss로 바꿉니다.
그런데, 마우스 오른쪽 버튼을 누른 후 셀 서식을 선택하고 사용자 지정으로 들어가봐도 yyyy-mm-dd h:mm까지만 있고, hh:mm:ss는 없습니다. 그러면 형식 바로 아래에 있는 yyyy-mm-dd h:mm를
yyyy-mm-dd hh:mm:ss로 수정하면 됩니다. 형식을 수정하니 위의 보기도 바로 변경됩니다. 이제 확인 버튼을 누르면
A열은 날짜 표시 형식이 연-월-일 시:분:초로 바뀌었는데, B열과 C열은 여전히 실수입니다.
(2) 연-월 바꾸기
B2셀의 수식을 살펴보니 =LEFT(A2,7)이라고 문자열 기준으로 수식이 되어 있습니다.
따라서, 수식부터 Year와 Month 함수를 이용하거나 Text 함수를 이용해 수정해야 합니다.
Text 함수를 이용하면 =TEXT(A2,”yyyy-mm”)가 됩니다. 표시형식을 지정하는 것과 비슷합니다.
(3) 날짜(상세) 바꾸기
C2셀의 수식으 보면 =LEFT(A2,10)으로 문자열 기준으로 수식이 되어 있습니다.
이 때도 Text함수를 이용할 수 있는데, 이번에는 복잡하지만 Date 함수와 Year, Month, Day 함수를 이용해서 구해보겠습니다.
C2셀에 =DATE(YEAR(A2),MONTH(A2),DAY(A2))라고 입력하니 숫자가 아닌 날짜 표시형식으로 표시됩니다.
이제 B2셀과 C2셀을 마우스로 끌어서 선택한 후 C2셀의 채우기 핸들을
더블 클릭하면 맨 아랫줄까지 자동으로 수식이 복사됩니다.
이제 날짜 변환이 모두 끝났으니 H2셀의 1을 지웁니다.
나. 운행시간 구하는 수식 작성하기
그런데 https://overmt.com/pyhub-mcptools로-엑셀-문제-풀기/ 방법을 알고 나니 생각이 없어집니다.
claude desktop을 실행한 후 아래와 같이 질문하니, 이 때는 이전 완충 또는 충전중인 구동시간이 없을 때는 0으로 처리해야 하겠다고 생각할 때입니다.
계산을 잘 못하므로 H561셀에 수식을 =A561-A541이라고 넣고 다시 물어봅니다.
수식을 넣으면 실수로 표시돼서 E열의 표시형식을 [h]:mm:ss로 바꿔야 합니다.
그리고 시간이 좀 지나서 H168에 수식을 넣었다고 해서 확인해보니
=A168-A101로 계산한 값과 일치합니다.
J168셀의 채우기 핸들을 J90까지 끌고 값을 =A90-A2로 확인해보니 맞습니다.
중간에 아래와 같이 수식을 제공해서
아래와 같이 수정 요구하고,
수식에서 OFFSET(D561,-1,0)를 간단하게 D560으로 수정하는 등 약간의 손길이 가야합니다.
=IF(AND(E561=E561,OR(D561=”완충”,D561=”충전중”),OFFSET(D561,-1,0)=”운행”), A561-IFERROR( INDEX(A$2:A560,MAX(IF((E$2:E560=E561)*((D$2:D560=”완충”)+(D$2:D560=”충전중”)),ROW(A$2:A560)-1))), INDEX(A$2:A560,MIN(IF((E$2:E560=E561)*(D$2:D560=”운행”),ROW(A$2:A560)-1))) ),””)
그리고, J168셀의 수식을 채우기 핸들을 더블 클릭해서 다른 셀에도 채운 후 등록번호가 바뀌는 시점에서 어떻게 되는지 해보니 #VALUE!에러가 발생합니다.
다시 왜 오류가 발생하는지 물어보니
=IF(AND(E288<>””,OR(D288=”완충”,D288=”충전중”),D287=”운행”),A288-IFERROR(INDEX(A$2:A287,MAX((E$2:E287=E288)((D$2:D287=”완충”)+(D$2:D287=”충전중”))(ROW(E$2:E287)))-1),INDEX(A$2:A287,MATCH(E288,E$2:E287,0))),””)
라고 충전중 또는 완충이 없을 때 값을 등록번호가 일치하는 최초 행으로 바꿔줍니다.
이제 다른 셀에도 적용하니 문제없습니다.
좀 손은 가지만, 그리고, 엑셀에 대한 기본 지식이 있어야 제시한 수식을 이해하고 수정 요청을 할 수 있지만 대단합니다.
다. 집계표 만들기
필터를 없애고,집계표 왼쪽에 등록번호가 없으므로 추가합니다.
S5셀에
MINIFS($H:$H,$E:$E,$I5,month($B:$B),VALUE(LEFT(S$2,LEN(S$2)-1)),$H:$H,”>0″)
라고 입력하면 month($B:$B)때문에 에러가 발생하므로 B열을 월만 표시하던가, 월이 있는 다른 열을 만들어야 합니다. B열을 =month(A2)로 해서 월로 바꾸겠습니다.
그리고, 아래와 같이 입력하면
=MINIFS($H:$H,$E:$E,$I5,$B:$B,VALUE(LEFT(S$2,LEN(S$2)-1)),$H:$H,”>0″)
S5셀의 값이 구해집니다.
이런 식으로 다른 셀에도 수식을 복사해서 붙여넣는데, min은 max 또는 average로 수정하고, 집계표의 월을 지정하는 주소도 수정해야 합니다. 복사할 때는 최소,최대,평균 3개를 한꺼번에 복사해야 합니다.
그런데 해당 월의 데이터가 없을 때 AverageIfs 수식에서 #DIV/0!에러가 발생하므로 ifError함수를 추가해서 0으로 지정합니다.
=IFERROR(AVERAGEIFS($H:$H,$E:$E,$I5,$B:$B,VALUE(LEFT(J$2,LEN(J$2)-1)),$H:$H,”>0″),0)
완성된 파일은 아래와 같습니다.