All Scrap

시트명은 시간표입니다.

A1=0

A2~A10 시간입력

월~토 수강과목입력

J1=Now()        <<현재일시가 나타남 셀서식-hh:mm(aaa)

이름정의

현재=TIMEVALUE(TEXT(시간표!$J$1,"h:mm"))

시간=시간표!$A$1:$A$10

강의=OFFSET(시간,MATCH(현재,시간표!$A$1:$A$9,1),WEEKDAY(시간표!$J$1,3)+1)

이름설명

현재-현재일시의 시간값

시간-입력된시간

강의-오늘(요일)의 시간표에서 현재시간이후의 자료를 추출

MATCH()부분이 현재시간이후

WEEKDAY()부분이 요일별

수식

i3=IF(COUNTA(강의)<ROW(A1),"",INDEX(시간,SMALL(IF(강의<>"",ROW(강의)),ROW(A1))))

배열식입니다. 수식 입력후 Enter대신 Ctrl+Shift+Enter하면 {=수식}형태로 변함

j3=IF(I3="","",VLOOKUP(I3,$A$1:$G$10,COLUMN(강의),0))

i3:j3을 밑으로 채우기

아래를 참고해 응용해 보세요. (표를 마우스로 긁어 엑셀시트에 붙이고 수식 역시 해당 주소 수식 창에 긁어 붙인 후 보시면 이해하기 보다 편합니다.)

데이타 원본이 C3:F7에 있을 때,

B3 =IF(ISNUMBER(FIND(I$2,C3,1)),1,0)+MAX(B$2:B2) ; 해당 데이타 찾기. B3를 아래로 드래그 복사.

K2 =MAX(B3:B7) ; 담당 강좌 수

H4 =ROW(A1) ; 일련 번호

I4 =IF($H4>$K$2,"",VLOOKUP($H4,$B$3:$F$7,COLUMN(C$1),0)) ; 데이타 가져 오기

I4를 복사, I4:K7에 채워 넣으면 표가 완성됩니다. 이제 I2에 강사 이름을 일부만 입력하더라도 빠짐 없이 잡아냅니다. (예로써 예시에서 a만 입력하거나 빈칸으로 놔두면 모두 출력됨. dy, ex 등으로 시험해 보세요) 원본 데이타 시트에 보조열(예시의 B열) 하나를 사용하는 위 기법이 제가 아는 한 가장 코딩이 간단합니다. 그렇지 않고 시트 2에서 모든 것을 다 하려면 배열 수식을 사용해야 하는 바, 수식이 복잡해지고 속도가 느려져서 추천하고 싶지 않습니다.

=vlookup(과목명이있는셀,과목명을시작으로 선생님이 있는자료범위,과목명부터 몇번째,false)

이런식으로하시면되는데

아래예제를 보면

이것이 시간표시트고

이것이 범위시트라면

참고로 영구와 땡칠이는 선생님이름입니다 ㅡㅡ;;

그럼 시간표 월요일 1교시 과목1 아래 들어갈 함수는

=vlookup(b2,범위!$a$1:$b$2,2,false)

이런식으로 나오겠죠..

b2  와똑같은것을 범위시트의  a열에서 찾아서 찾은값의 두번째값을 나타내라

라는뜻입니다 ^^

응용해서 만들어보세요~