학생지도비 점검시, 유용한 엑셀 함수들

 

 

수업시간과 봉사시간 중복여부 체크(겹치는 시간범위 찾기) 

다음의 경우를 살펴보자, 

학생 홍길동은 다음과 같이 자신의 봉사활동 실적을 신고하였다. 

신고내역을 확인해보니, 수업시간과 봉사시간의 시간대가 겹쳐있다.

 

	A	B	C	D
1	봉사시간		수업시간
2	시작시(A)	종료시(B)	시작시(C)	종료시(D)
3	14:00	14:30	14:00	16:50
4	10:00	12:00	14:00	16:50

 

규칙상, 수업시간과 봉사시간은 서로 겹치면 안된다.  수업시간에 봉사활동을 한 꼴이기 때문이다. 

 

만약 홍길동과 같이  봉사시간을 잘못 신고한 사례가 많다면, 이러한 경우를 찾아내는 함수식을 어떻게 짜면 좋을까?

 

다음과 같이 봉사시간(A - B구간)과 수업시간(C - D구간)을 가정하자.

 

A = 봉사 시작시
B = 봉사 종료시
C = 수업 시작시
D = 수업 종료시

 

봉사시간과 수업시간이 겹치는 경우는, 

봉사의 시작시(A)는 수업의 종료시(D)보다 항상 커야 한다. 그리고 봉사의 종료시(B)는 수업의 시작시(C)보다 항상 작아야 한다. 

 

이를 표현하면 다음과 같다. 

 

A < D and B < C

 

위의 식을 엑셀 함수식을 표한하면 다음과 같다.

 

=IF(AND(A < D, C < B), "겹침", "겹치지 않음")

 

위의 엑셀 표에 적용하면 다음과 같다. 

 

	A	B	C	D	E
1	봉사시간		수업시간
2	시작시	종료시	시작시	종료시
3	14:00	14:30	14:00	16:50	=IF(AND(A3 < D3, C3 < B3), "겹침", "겹치지 않음")
4	10:00	12:00	14:00	16:50	=IF(AND(A4 < D4, C4 < B4), "겹침", "겹치지 않음")

 

 

---

텍스트에서 숫자만 추출하기

	A	B	C
1	월678화678수678화12화34
2
3

 

텍스트에서 숫자만 추출하는 엑셀 함수는 없다. 여러가지 함수를 조합하여 구현하여야 한다. 

아래는 엑셀 함수식의 예이다.

 

셀A1에 "월678화678수678화12화34"라는 텍스트가 있는 경우, 아래의 함수식을 사용하면, 반환값으로 6786786781234를 얻을 수 있다.

 

함수식	반환값
=SUMPRODUCT(MID(0&A1,LARGE(ISNUMBER(--MID(A1, ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)* 10^(ROW($1:$50)-1))	6786786781234

---

텍스트에서 특정 문자의 개수 세기

	A	B	C
1	월678화678수678화12화34	화
2
3

위와 같이 A1과 B1에 각각의 텍스트가 입력되어있다고 가정할 때, A1에서 "화"라는 문자가 몇번 사용되었지 셈하려 한다. 

 

이를 위해선 아래의 함수를 함께 사용해야 한다. 

 

  LEN은 텍스트의 길이를 반환하는 함수(반환값 = 정수)
  SUBSTITUTE은 텍스트의 일부를 새로운 텍스트로 바꾸는 함수

 

함수식	반환값
=LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))	3

함수식을 설명하면 다음과 같다. 

 

1. 전체 텍스트의 길이를 구한다. 
2. "화"라는 문자를 ""로 교체("화"를 제거한다.)한 후, "화"가 제거된 텍스트의 길이를 구한다.
3. 1에서 2를 뺀다. 

 

위 과정을 거치면, 전체 텍스트에서 "화"의 개수만을 얻을 수 있다.

---

문자열에서 저정한 문자열의 일부 반환(반환값 = 문자열, 텍스트)

함수 mid( )를 사용한다. 

 

	A	C
1	월678화678수678화12화34
2

 

예제는 다음과 같다. 

함수식	반환값
=mid(A1, 2, 3)	678

 

---

문자열에서 특정문자 찾기 (반환값 = 문자열 위치, 숫자)

 

함수 find ( )를 사용한다. 

	A	B
1	월678화678수678화12화34
2

 

셀 A1의 텍스트 "월678화678수678화12화34"를 살펴보자.

"화"라는 문자가 총 3번 사용되었다. 

 

만약, "월678화678수678화12화34"에서 "화"라는 문자가 사용된 각각의 위치를 반환하는 함수식을 작성하려한다면, 다음과 같다.   

 

함수식	반환값
=find("화", A1, 1)	5
=find("화", A1, find("화", A1, 1)+1)	13
=find("화", A1, find("화", A1, find("화", A1, 1)+1)	16