Embedded Files
"일정"sheet를 만들어 설정
법정공휴일 및 재량휴업일 - 차시계획에서 제외, 월간 달력에서 빨간날짜로 표시
학사일정 - 학년별, 교시별 적용하여 차시계획에서 제외, 월간 달력에서는 같은날 2개행에 일정표시
주간일정 - 월간달력 일요일에 표시
(초기 아이디어) Q열과 R열에 내용이 있다면 전체열에 표기
조건부 서식을 이용해 각 반별 수업요일과 해당일의 요일이 같다면 음영표시
음영이지만 학사일정이나 공휴일이 있다면(예: c8)수업못함. 음영된 부분중 빈 곳에 1차시부터 입력하여 맨 마지막 차시를 5열에 표시
(문제점) 각 반별 연명부와 차시계획을 작성할 수 있는 반별Sheet를 만들어서 수업일을 자동계산하고 싶은데 각반의 열에 빈칸이 아닌 날짜를 모두 가져오면 복잡해 질 것 같음.
(해결방안) 학사일정과 공휴일의 요일이 수업요일과 같을 경우만 표시되도록 하자.
수업일의 요일을 추출하기 위해 =LEFT(C2,1) 왼쪽에서 한글짜 추출하면 "목"
2학년1반과 같이 2개의 요일을 추출하려면 이름정의 이용: C1:O5를 선택후 이름정의하여 첫행을 이름으로 하면
=LEFT(_2학년1반,1) "수", "금" 이 추출됨
if를 이용해 요일을 숫자로 변환
=IF(LEFT(_2학년1반,1)="월",2,IF(LEFT(_2학년1반,1)="화",3,IF(LEFT(_2학년1반,1)="수",4,IF(LEFT(_2학년1반,1)="목",5,IF(LEFT(_2학년1반,1)="금",6,"")))))
참고문헌(엑셀 배열 수식의 개념과 사용법): https://blog.naver.com/sole_sp/222262407456
(아이디어) 만약 학사일정이 있고($w43<>"") 해당일($B43)과 수업요일을 숫자로 변환한 값이 같다면 학사일정($W43)을 입력, 아니면 공란("")
(문제점과 예상) 왜 G43에 학사일정이 안나오지?? 해당일의 요일을 숫자로 변환한 값(한개의 숫자)과 수업요일을 배열로 추출한 값이 OR로 같으면 학사일정이 나오도록 해야함.
=IFERROR(IF(AND($W43<>"",WEEKDAY($B43)=IF(LEFT(_2학년1반,1)="월",2,IF(LEFT(_2학년1반,1)="화",3,IF(LEFT(_2학년1반,1)="수",4,IF(LEFT(_2학년1반,1)="목",5,IF(LEFT(_2학년1반,1)="금",6,"")))))),$W43,""),"")
=IFERROR(IF(AND($W43<>"",OR(WEEKDAY($B43)=IF(LEFT(_2학년1반,1)="월",2,IF(LEFT(_2학년1반,1)="화",3,IF(LEFT(_2학년1반,1)="수",4,IF(LEFT(_2학년1반,1)="목",5,IF(LEFT(_2학년1반,1)="금",6,""))))))),$W43,""),"")
(문제점) 공휴일이 수업요일과 같을 경우 공휴일을 표시해야 함 - G71셀
=IFERROR(IF(AND(OR(W71<>"",X71<>""),OR(WEEKDAY(B71)=IF(LEFT(_2학년1반,1)="월",2,IF(LEFT(_2학년1반,1)="화",3,IF(LEFT(_2학년1반,1)="수",4,IF(LEFT(_2학년1반,1)="목",5,IF(LEFT(_2학년1반,1)="금",6,""))))))),OR(W71,X71),""),"")
안되네....
=IFERROR(IF(AND(OR(W71<>"",X71<>""),OR(WEEKDAY(B71)=IF(LEFT(_2학년1반,1)="월",2,IF(LEFT(_2학년1반,1)="화",3,IF(LEFT(_2학년1반,1)="수",4,IF(LEFT(_2학년1반,1)="목",5,IF(LEFT(_2학년1반,1)="금",6,""))))))),IF(W71<>"",W71,X71),""),"")
(개선해야할 사항) 이름이 _0학년0반으로 되어 있는데 이를 각 셀에서 다 수정해 주어야 함. 1행에서 추출한 방법을 적용하여 자동화 할 수 있는지 검토
학사일정 학년, 교시에 따른 일정반영 조건부여 필요
(아이디어)수업요일일 경우, 공휴일과 학사일정에 없는 경우 1차시, 2차시, ... 순서대로 들어가야 함.
1단계. 설정sheet에 1차시부터 입력해 놓는다.
2단계. 수업일에. 학사일정과 공휴일이 없으면 순서대로 데이터를 불러온다.
(한계점) 연속된 데이터를 띄엄띄엄(수업일)에 한개씩 순서데로 넣을 수 있는 방법을 모르겠다..
=IFERROR(IF(AND(OR($W7<>"",$X7<>""),OR(WEEKDAY($B7)=IF(LEFT(_1학년1반,1)="월",2,IF(LEFT(_1학년1반,1)="화",3,IF(LEFT(_1학년1반,1)="수",4,IF(LEFT(_1학년1반,1)="목",5,IF(LEFT(_1학년1반,1)="금",6,""))))))),IF($W7<>"",$W7,$X7),IF(AND(and($W7="",$X7=""),OR(WEEKDAY($B7)=IF(LEFT(_1학년1반,1)="월",2,IF(LEFT(_1학년1반,1)="화",3,IF(LEFT(_1학년1반,1)="수",4,IF(LEFT(_1학년1반,1)="목",5,IF(LEFT(_1학년1반,1)="금",6,""))))))),1차시부터 순서대로 들어가게,"")),"")
=IFERROR(IF(AND(OR($W7<>"",$X7<>""),OR(WEEKDAY($B7)=IF(LEFT(_1학년1반,1)="월",2,IF(LEFT(_1학년1반,1)="화",3,IF(LEFT(_1학년1반,1)="수",4,IF(LEFT(_1학년1반,1)="목",5,IF(LEFT(_1학년1반,1)="금",6,""))))))),IF($W7<>"",$W7,$X7),IF(AND(and($W7="",$X7=""),OR(WEEKDAY($B7)=IF(LEFT(_1학년1반,1)="월",2,IF(LEFT(_1학년1반,1)="화",3,IF(LEFT(_1학년1반,1)="수",4,IF(LEFT(_1학년1반,1)="목",5,IF(LEFT(_1학년1반,1)="금",6,""))))))),INDEX(설정!$M:$M,MATCH(ROW(설정!$M$1:$M$51),ROW(설정!$M$1:$M$51))),"")),"")
원하는 위치에 데이터가 들어가기는 했는데.
#SPILL! 경고
1차시 시작일 51차시까지 다 불러오는데
그 중간 걸리는 데이터가 있어서...
하나만 불러와야하는데 다 불러오는 문제..
네이버 지식인에 문의 결과
=IF(COUNTIF(D$2:D$3,TEXT($C4,"aaa")),IFERROR(SUBSTITUTE(OFFSET(D$2,MATCH(0,INDEX(1/(D$3:D3<>""),),-1),),"일차","")+1,1)&"일차","")
=IF(COUNTIF(D$2:D$5,TEXT($C6,"aaa")),IFERROR(SUBSTITUTE(OFFSET(D$2,MATCH(0,INDEX(1/(D$3:D5<>""),),-1),),"차시","")+1,1)&"차시","")
굻은표시를 바꾸면 여러 요일도 가능
text(해당셀,형태) - 셀을 원하는 형태로 변경
출처: https://blog.naver.com/jungah825/221800188900
https://blog.naver.com/pure_o_o/222253001769
substitute(선택셀, 기존문자, 바꿀문자)
countif(범위, 조건) - 범위 내 조건에 맞는 셀의 개수를 구함
MATCH(0, INDEX(1/(D$3:D3<>""),), -1) // 0을 찾아라, INDEX범위에서, 내림차순데이터에서 일치하는 값이 없으면 높은 값 중 가까운 값
바로 위 행까지 중에 공란이 아니었던 셀의 행번호
INDEX(1/(D$3:D3<>""), , )
D$3:D3~D100 범위(바로위 쎌까지)에서 공란(" ")이 아닌 행을 1로함
참고문헌(index와 match): https://m.blog.naver.com/PostView.nhn?blogId=villamoa003&logNo=220613136874&targetKeyword&targetRecommendationCode=1
offset(시작셀,위아래 이동값, 오른쪽왼쪽이동값, 높이, 넓이)
여기에서
시작셀: D$2
위아래이동값: MATCH(0,INDEX(1/(D$3:D3<>""),),-1)
오른쪽왼쪽이동값:공란
높이,넓이 미입력
출처: https://blog.naver.com/yjswlstjq/222252199353
OFFSET( match() ) 조합에 대한 이해를 높이려면
참고: https://kin.naver.com/qna/detail.nhn?d1id=1&dirId=102020101&docId=326751305&qb=7JeR7IWAIOybkO2VmOuKlCDsnITsuZjsl5Ag7J6F66Cl&enc=utf8§ion=kin&rank=21&search_sort=0&spq=0
substitute(변경전 위치, 검색할 문자, 변경후 문자, 몇번째 위치값)
"변경전 위치"에서 "몇번째 위치"에 있는 "검색할 문자"를 찾아 "변경후 문자"로 바꿔라
변경전위치: 현제셀 기준으로 가장 가까운 윗쪽 텍스트가 있는 셀위치
검색할 문자: "일차"
변경후문자: ""
//현제셀 기준으로 가장 가까운 윗쪽 텍스트가 있는 셀에서 "일차"를 지우고 숫자만 남김
출처: https://blog.naver.com/jjs100413/221549038492
=IFERROR(IF(VLOOKUP(W42,일정!F:G,2,FALSE)<>0,VLOOKUP(W42,일정!F:G,2,FALSE)&"학년",""),"")
Vlookup으로 찾으면 첫번째 검색되는 결과만 연달아 나옴.
(아이디어)
해당일을 일정에서 찾고 대상학년(G열)이 공란("")일 경우 일정입력
해당일을 일정에서 찾고 대상학년(G열)에 1이 있을 경우 일정입력
해당일을 일정에서 찾고 대상학년(G열)에 2가 있을 경우 일정입력
해당일을 일정에서 찾고 대상학년(G열)에 3이 있을 경우 일정입력
Page updated
Google Sites
Report abuse