엑셀 달력 자동화, 공휴일+일정까지 완벽 해결! | CALENDAR 함수 만들기

1. 예제파일 다운로드
2. 강의에 사용된 공식 빠르게 살펴보기
3. M365 버전 : CALENDAR 함수 사용법
4. 엑셀 기본 달력 만들기 (SEQUENCE + WEEKDAY 함수)
5. 개인 일정과 공휴일을 출력하는 달력 만들기
6. 이전/이후 월을 숨기고 공휴일을 강조하는 조건부서식 적용하기

=LAMBDA(년도,월,[개월수],[공휴일표],[일정표],[날짜서식],[요일표시],[전후월표시],[월요일시작],LET(oYear,년도,oMonth,월,sMonth,IF(개월수=0,1,개월수),fromMon,IF(월요일시작=0,0,1),dataA,IF(공휴일표=0,{"",""},공휴일표),dataB,IF(일정표=0,{"","",""},일정표),displayHeader,IF(요일표시=0,0,1),dFormat,IF(날짜서식=0,"m/d",날짜서식),displayPrev,IF(전후월표시=0,0,1),oDate,DATE(oYear,oMonth,1),sDate,oDate-WEEKDAY(oDate,1+fromMon)+1,DateList,SEQUENCE(EOMONTH(oDate,sMonth-1)-sDate+8-WEEKDAY(EOMONTH(oDate,sMonth-1),1+fromMon),,sDate),tDateList,IF(전후월표시=1,DateList,IF((DateList<oDate)+(DateList>EOMONTH(oDate,sMonth-1)),"",DateList)),tDate,WRAPROWS(tDateList,7),Ddata,tDate,Udata,TOCOL(Ddata),dataAC1,INDEX(dataA,,1),dataAC2,INDEX(dataA,,2),vA,UNIQUE(TRANSPOSE(IF(dataAC1=TRANSPOSE(Udata),dataAC2,"")),1),seqrvA,SEQUENCE(ROWS(vA)),sepA,CHAR(10),xA,MMULT((LEN(vA)+LEN(sepA))*(vA<>""),SIGN(SEQUENCE(COLUMNS(vA))))-LEN(sepA),yA,MMULT(--(TRANSPOSE(seqrvA)<seqrvA),xA+LEN(sepA)-1)+seqrvA,dataBC1,INDEX(dataB,,1),dataBC2,INDEX(dataB,,3)&TEXT(INDEX(dataB,,2)," (AM/PM HH:MM)"),vB,UNIQUE(TRANSPOSE(IF(dataBC1=TRANSPOSE(Udata),dataBC2,"")),1),seqrvB,SEQUENCE(ROWS(vB)),sepB,CHAR(10),xB,MMULT((LEN(vB)+LEN(sepB))*(vB<>""),SIGN(SEQUENCE(COLUMNS(vB))))-LEN(sepB),yB,MMULT(--(TRANSPOSE(seqrvB)<seqrvB),xB+LEN(sepB)-1)+seqrvB,DROP(VSTACK(IF(fromMon=0,{"일","월","화","수","목","금","토"},{"월","화","수","목","금","토","일"}),IFERROR(WRAPROWS("["&MID(TEXTJOIN(sepA,,vA),yA,xA)&"] ",7),"")&TEXT(Ddata,dFormat)&CHAR(10)&IFERROR(WRAPROWS(MID(TEXTJOIN(sepB,,vB),yB,xB),7),"")),(displayHeader=0)*1)))

=IFERROR("["&VLOOKUP(SEQUENCE(6,7,F3),휴일목록,2,0)&"] ","")&TEXT(SEQUENCE(6,7,F3),"m/d")&CHAR(10)&IFERROR(VLOOKUP(SEQUENCE(6,7,F3),일정목록,2,0),"")