엑셀에서 함수를 활용해서 원하는 조건에 맞는 표시가 될수있는 방법을 알려주세요!

안녕하세요!

엑셀 전문가 여러분들 도움을 주세요!

위에 보이시는 이미지를 보시면

학생의 이름과 첫 수강일/금액/전화번호/매달 결제일을 표시하는 엑셀파일이 있습니다.

그런데 첫수강일이 12월 5일이라 매달 5일마다 정기적으로 원비납부를 해야하는데

그렇지 못한경우들이 다수 발생합니다.

그리하여 매달 결제일 5일을 기점으로 5일에서 추가 5일이 더 지난 매달 10일까지

그 달의 원비 결제사항이 입력되지않으면, 학생의 이름이나 첫수강일에 빨간색의 표식이 될수있었으면 합니다.

여러분? 도와주세요

 

안녕하세요.

오늘 날짜가 납부일자 대비 5일 이상 경과 되었는데 학원비를 납부 안했으면 표시하는 기능을 원하시는 거네요

이렇게 풀어봤어요

위 그림에서, 매월 납입일이 공백이고, 오늘 날짜가 첫수강일보다 5일을 초과했으면 이름 왼쪽셀에 v표시하는 방식이구요

여기에 사용된 함수는 

day()

month()

todya()

if(), offset(), and()

입니다.

day()는 날짜형식 데이터에서 연/월 빼고 날짜만 보여주는 함수에요

month()는 월만 보여주죠

today()는 오늘날짜를 반환하는 함수

offset은 기준셀부터 몇칸 아래, 몇칸 우측으로 이동한 셀부터 높이와 넓이를 지정하는 함수에요

예를 들어=offset(A1, 1, 1, 1, 5)라고 하면 A1에서 아래로 1칸, 우측으로 1칸 이동한 B2부터 높이 1 넓이 5만큰 지정한거니까, offset(A1, 1, 1, 1, 5) = B2:B6 과 같아요

그럼 설명 드릴게요

오늘 날짜의 월을 계산해서 E2부터 월만큼 이동한 셀이 공백이고(납입 안했고)

오늘 날짜의 일이 첫수강일의 일+5보다 크면(두 조건이 모두 만족하면)

V표시 하도록 했어요

함수는 =IF(AND(OFFSET(E2,0,month(today()),1,1)="",DAY(TODAY())>DAY(첫수강일)+5),"v","")

E2(질문 그림에서 전화번호)부터 아래로 0칸, 우측으로 오늘의 월수 만큼 이동한곳부터 1칸높이1칸 넓이만큼의 영역, 그러니까 오늘이 3월이니까 3월달 납입일 셀이 되겠죠..

그 셀이 공백이고, 오늘날짜의 일수(day(today()))가 첫수강일의 일수(day(첫수강일))+5보다 크면 v표시하라는함수에요...

첨부파일4월이 되면 4월 납입일자 셀을 참조해서 계산하게 될거에요

그리고, v표시가 있으면 빨간색 음영은 조건부 서식으로...

파일 : 

학원비관리(offset사용).xlsx