엑셀 구간 값 찾기와 관련해서는 이전 글 “엑셀 구간 값 가져오기 index match 함수 수식.“에서 기본 예제와 관련하여 확인해 볼 수 있습니다.
하지만 데이터값이 숫자값의 비교가 아닌 시간, 날짜등의 데이터값이 해당하는 구간 값을 가져오려고 하면 동일한 수식이여도 헷갈려 하는 경우가 많습니다.
구간 값뿐만 아니라 숫자가 아닌 데이터값(시간, 날짜등)을 참조해서 결과값을 가져오기 위한 수식을 작성하는데 어려움을 겪는 경우를 종종 발견할 수 있습니다.
이번 글에서는 구간 값 가져오기 수식을 시간의 구간에 해당하는 결과값을 가져오는 수식을 작성해 보도록 하겠습니다.
예제.
이번 글에서 확인해볼 예제로 A,B열에는 실제 출근, 퇴근시간을 입력하고 C,D열에는 F,G열의 시간대에 해당하는 구간을 찾아서 H열 기준 변경 시간으로 출근시간과 퇴근시간이 입력되도록 수식을 작성해 보려고 합니다.
예제 이미지에서 보는것과 같이 A3셀 실제로 출근한 시간은 5:50이므로 5:00~7:00 구간에 해당하는 값 H3셀의 7:00를 C3셀의 결과값으로 가져오게 됩니다.
이렇게 시간의 범위 구간에 해당하는 데이터값을 결과값으로 가져오는 수식을 작성하려는 경우 if함수등으로도 수식을 작성 할 수 있고, 시간과 관련되어 있기 때문에 time함수를 사용해서 수식을 작성하는 경우도 있습니다.
하지만 어떠한 연산을 통해서 결과값을 가져오는것이 아닌 조건이 어떤 구간에 해당하는지를 찾아서 정해진 데이터값을 결과값으로 가져오는것이기 때문에 index, match함수로 구간을 찾아 결과값을 가져올 수 있습니다.
시간 범위 구간 값 수식.
A3셀 5:50이 해당하는 구간은 05:00~07:00구간이고 이 구간은 07:00로 데이터값이 정해져 있기 때문에 정해져 있는 데이터값을 결과값으로 가져오기만 하면 됩니다.
따라서 C3셀 수식은 다음과 같이 작성할 수 있습니다.
| =INDEX($H$3:$H$14,MATCH($A3,$F$3:$F$14,1)) |
결과값을 가져올 범위는 H3:H14, 찾을 조건은 A3셀, A3셀의 데이터값이 해당 하는 구간을 찾을 참조 범위는 F3:F14입니다.
그리고 정확히 일치하는값이 아닌 구간에 해당하는 값을 찾아야 하기 때문에 Match함수의 match_type(일치유형)을 1로 수식을 작성하면 찾아야 하는 조건의 시간이 해당하는 구간을 찾아 결과값을 반환할 수 있습니다.
마찬가지로 퇴근시간도 해당하는 구간을 찾아 결과값을 가져오면 되기 때문에 D3셀의 수식을 작성해 보면 다음과 같습니다.
| =INDEX($H$3:$H$14,MATCH($B3,$F$3:$F$14,1)) |
실제 퇴근 시간이 해당하는 구간의 변경 기준 데이터값의 참조 범위는 H3:H14로 동일하고 찾을 조건은 실제 퇴근 시간 B3셀, B3셀 조건을 찾을 참조 범위는 F3:F14, match_type은 1로 수식을 작성해서 퇴근시간의 변경 기준값을 가져올 수 있습니다.
시간 범위 구간 추가하기.
위와 같이 C열 출근 시간 수식 =INDEX($H$3:$H$14,MATCH($A3,$F$3:$F$14,1)), D열 퇴근 시간 수식 =INDEX($H$3:$H$14,MATCH($B3,$F$3:$F$14,1))으로 작성하고 수식을 복사하면 예제 이미지에서 보는것과 같이 10행의 퇴근시간이 07:00로 입력되는것을 볼 수 있습니다.
B10셀에 입력된 실제 퇴근 시간이 13:59분으로 구간 참조 범위내에서 13:59분이 해당하는 구간은 설정해 주지 않았기 때문에 Match함수의 match_type인수값 1에 의해서 조건보다 작거나 같은값중 큰값인 07:00를 결과값으로 반환하게 됩니다.
따라서 F~H열에 실제 퇴근시간이 13:59분일 경우에 변경 기준 퇴근시간을 11:00로 결과값을 입력하고 시간 범위 구간은 11:00~14:00로 가정할 경우 F7셀은 11:00, G7셀은 14:00, H7셀은 11:00로 시간 범위 구간을 추가해 주도록 합니다.
하지만 이렇게 시간 범위 구간을 추가하는 경우 D열의 모든 값이 원하지 않는 값으로 반환될 수 있는데 Match함수의 match_type 1은 오름차순으로 정렬되어 있을때 작동하기 때문에 추가하고자 하는 시간 범위 구간 11:00~14:00는 참조범위내 7행이 아닌 4행에 추가를 하고 나머지 구간들은 1행씩 아래에 복사해 주어야 원하는 값으로 작동하게 됩니다.
글을 마치며…[행아아빠]
이번 글에서 작성한 수식은 사실 이전 글에서 작성한 index match함수 수식으로 구간 값 찾기 수식과 크게 다르지 않습니다.
그러나 데이터값의 형식이 숫자일 경우 index match함수 수식을 작성해서 잘 사용했다 하더라도 데이터값이 시간 형식으로 또는 날짜 형식등으로 변경되는 경우 시간은 time함수를 날짜는 date함수등을 사용해서 수식을 작성하려는 경우가 종종 있습니다.
이번 글 예제처럼 데이터값의 형식이 바뀌었더라도 연산을 하는 경우가 아닌 단순히 구간 값을 찾는것이라면 우리가 알고 있는 index match 구간 값 찾는 수식으로 작성해 보시길 바랍니다.
