❇️고급필터
● [A3:J30] 영역에서 '거래일'이 3월 이전이고, '할부개월"이 상위 5위 이내인 데이터를 표시하시오.
▶ 조건은 [A32:A34] 영역에 입력하시오. (AND, MONTH, RANK.EQ 함수 사용)
▶ 결과는 [A36] 셀부터 표시하시오.
=AND(MONTH(A3) <= 3, RANK.EQ(C3, $C$3:$C$30) <= 5)
❗RANK.EQ 순위를 구하려는 수(Number)가 수 목록(Ref)에서 몇 번째 값인지를 표시한다.
❇️조건부 서식
● [A3:H28] 영역에서 '성별'이 "남"이고, '과목'이 "수학" 또는 "영어"가 아닌 전체 행에 대해 글꼴 스타일은 '기울임꼴', 글꼴 색은 '표준 색 - 빨강'으로 적용하시오.
▶ 단, 규칙 유형은 '수식을 사용하여 서식을 지정할 셀 결정'을 사용하고, 한 개의 규칙으로만 작성하시오.
▶ AND 함수를 사용하시오.
=AND($B3 = "남", $D3 <> "수학", $D3 <> "영어")
❗같지 않음을 나타낼 때에는 <> 기호를 사용한다.
● 사용자 지정 서식으로 1000000을 ‘★ 100만’으로 되게 하시오.
"★ "0,,"00만"
❗, 기호를 이용하여 뒤의 숫자를 생략할 수 있다.
❇️함수
● [표1]의 판매일에서 주말을 제외한 5일 후 날짜를 반품 가능일로 계산하여 [C4:C27] 영역에 표시하시오.
=TEXT(WORKDAY(C4,5), "MM월 DD일")
❗WORKDAY 특정 일(시작 날짜)의 전이나 후의 날짜 수에서 주말이나 휴일을 제외한 날짜 수, 즉 평일 수를 반환한다.
● [표1]과 [표2]를 이용하여 [I3:I27] 영역에 수당을 계산하여 표시하시오.
▶ 수당 = 급여액 x 수당률
▶ 수당률은 [표2]를 참조하여 수강인원과 전체 근무시간에 따라 계산
▶ 전체 근무시간 = 근무시간 x 근무일수
▶ 계산한 수당이 150,000 미만이면 150,000을 입력
=MAX(G4 * VLOOKUP(C4, $A$32:$E$36, MATCH(F4 * E4, $B$31:$E$31, 1) + 1), 150000)
❗MATCH 비교하는 셀 범위가 오름차순이면 1, 내림차순이면 -1, 정확하면 0을 입력한다.
❗VLOOKUP Col_index_num에 +1을 해준 이유는 +1을 하지 않았을 때, MATCH 결과가 1인 경우 셀 범위의 데이터가 아닌 [표2]의 열 제목이 선택되기 때문이다.
● [표1]의 대출금액, 월기간, 연이율과 [표2]를 이용하여 [H3:H28] 영역에 월상환금액에 따른 가계부담을 계산하여 표시하시오.
=VLOOKUP(PMT(F3 / 12, E3, -D3), $A$32:$B$35, 2)
❗PMT Rate, Nper, Pv, Fv, Type으로 이루어져 있다. 이중 Rate, Nper, Pv를 사용한다.
❗Rate 대출에 대한 기간별 이자율이다. 연이율을 월이율로 고치기 위해 F3 / 12를 해주었다.
❗Nper 갚을 기간이다. 갚을 월 기간인 E3을 선택했다.
❗Pv 얼마를 빌렸는가이다. 빌린 값은 앞에 -기호가 들어가야 매달 얼마나 갚을 것인지 표시된다.
● [표1]의 구분, 이벤트날짜, 기준일(H2)을 이용하여 [D4:D34] 영역에 이벤트주차를 표시하시오.
▶ 이벤트주차는 구분과 이번달주차를 연결하여 표시
▶ 이번달주차는 일년 중 이벤트날짜의 주차에서 기준일의 주차를 뺀 값으로 계산 [표시 예: 회원-1주차]
▶ 월요일부터 주가 시작하도록 계산
▶ CONCATENATE, WEEKNUM 함수 사용
=CONCATENATE(A5, "-", WEEKNUM(E4, 2) - WEEKNUM($H$2, 2), "주차")
❗CONCATENATE 여러 텍스트를 한 텍스트로 조인시킨다. 문자를 연결하는 &와 같다.
❗WEEKNUM 지정한 주가 일년 중 몇째 주인지를 나타내는 숫자를 반환한다. 일요일이 제일 앞이면 Return_Type을 1, 월요일이 제일 앞이면 2로 설정한다.
● [표1]의 이벤트날짜, 참가인원과 [표2]를 이용하여 [G4:G34] 영역에 참가비를 계산하여 표시하시오.
▶ 참가비: 참가인원 × 기본요금 × (1 - 할인율)
▶ 기본요금과 할인율은 이벤트날짜의 요일과 참가인원을 이용하여 [표2]에서 찾아 계산
▶ INDEX, MATCH, WEEKDAY 함수 이용
=F4 * INDEX($L$5:$L$11, WEEKDAY(E4, 2), 1) *
(1 - INDEX($M$5:$P$11, WEEKDAY(E4, 2), MATCH(F4, $M$3:$P$3, 1)))
❗참가인원 * INDEX(기본요금, WEEKDAY(이벤트날짜 요일), 1) * (1 - INDEX(할인율, WEEKDAY(이벤트날짜 요일), MATCH(참가인원, 기준셀, 1)))
❗WEEKDAY 일정 날짜의 요일을 나타내는 1에서 7까지의 수를 구한다. 일요일부터 시작할 것인지, 월요일부터 시작할 것인지 Type을 선택해야 하는데, [표2]에서 요일이 월요일부터 시작하므로 2로 입력한다.
● [표1]의 이벤트날짜를 이용하여 [표4]의 [L23:L26] 영역에 날짜구간별 전체에 대한 참가비율을 계산하여 표시하시오.
▶ 참가비율은 백분율로 소수점 첫째 자리까지 표시 [표시 예 : 3.2%]
▶ FREQUENCY, COUNT, TEXT 함수를 이용한 배열 수식
{=TEXT(FREQUENCY(E4:E34, K23:K26) / COUNT(E4:E34), "0.0%")}
❗FREQUENCY 도수 분포를 세로 배열의 형태로 구하는 함수로, 적용 범위를 처음에 모두 잡고 시작한다.
❗배열수식이므로 Ctrl + Shift + Enter 키를 눌러 완성한다. 만약 키가 눌리지 않을 경우 Window + Space 키를 실행한 뒤에 다시 시도한다.
● [표1] 의 분류와 배출일을 이용하여 [표3]의 [J23:N25] 영역에 분류별 요일별 배출건수를 계산하여 표시하시오.
▶ SUM, WEEKDAY, CHOOSE 함수를 사용한 배열 수식
{=SUM(($A$3:$A$31 = $I23) * (CHOOSE(WEEKDAY($E$3:$E$31, 2), "월", "화", "수", "목", "금") = J$22))}
❇️데이터 유효성 검사
● [데이터 유효성 검사] 기능을 이용하여 [F3:F25] 영역에는 두 번째 글자 이후에 반드시 “@”가 포함된 메일주소가 입력되도록 제한 대상을 설정하시오.
▶ [F3:F25] 영역의 셀을 클릭한 경우 그림 과 같은 설명 메시지를 표시하시오.
▶ SEARCH 함수 사용
=SEARCH("@", F3) >= 2
❗SEARCH 문자열에서 특정문자의 시작 위치를 찾는 함수이다.
❗화면에 띄우는 것이 설명 메시지인지, 오류 메시지인지를 확인한다.
❇️사용자 정의 함수
● 'fn할인액'은 판매일과 금액을 인수로 받아 할인액을 계산하시오.
▶ 할인액은 마지막 할인 날짜(C2)까지 판매된 경우만 금액의 20%를, 그렇지 않은 경우에는 0%로 계산하시오.
▶ 'fn할인액' 함수를 이용해 [H4:H20] 영역에 할인액을 표시하시오.
❗[계산작업!C2] 워크시트의 셀을 참조하려면 시트 이름과 셀 주소를 느낌표(!)로 구분한다.
❇️매크로
● [B4:C13] 영역에 사용자 지정 표시 형식을 설정하는 '서식적용' 매크로를 생성하시오.
▶ ‘사망 증감률'이 양수면 '빨강색’, 음수면 '파랑색'으로 소수점 이하 둘째 자리까지 표시하고, 0과 텍스트면
아무것도 표시하지 마시오.
[표시 예 '사망 증감률'이 : 0.1일 경우 → 0.10, -0.2일 경우 → -0.20]
[빨강]0.00;[파랑]-0.00;
❗형식 (양수; 음수; 0; 문자)
❇️VBA
● '등록' 단추를 클릭하면 <등록>폼이 나타나고, 폼이 초기화되면 [A1:A5] 영역의 값이 '목록'(lst목록) 목록 상자의 목록으로 표시되고, 첫 번째 데이터가 선택되도록 프로시저를 작성하시오.
❗자동으로 첫 번째 항목이 선택되도록 하는 것은 lst목록.Listindex = 0 로 작성할 수 있다.
● '등록' 단추를 클릭하면 <현황> 폼이 나타나고, 폼이 초기화되면 '분류'(cmb분류) 콤보 상자의 목록에 '소설', '컴퓨터', '취미'가 추가되도록 프로시저를 작성하시오.
▶ With, Additem을 이용하시오.
● 폼이 초기화(Initialize)되면 ‘구분/기본요금(cmb구분)’ 목록에는 [M6:N8] 영역의 값이 표시되고, ‘보호자동반’은 ‘유(opt유)’가 초기값으로 선택되도록 프로시저를 작성하시오.
cmb구분.Rowsource = "m6:n8"
opt유 = True
● 폼이 초기화되면 현재 날짜를 표시하고, ‘할부기간(cmb할부기간)’ 목록에는 “일시불”, “3개월 할부”, “12개월 할부"가 표시되도록 프로시저를 작성하시오.
txt판매일 = Date
cmb할부기간.AddItem "일시불"
cmb할부기간.AddItem "3개월 할부"
cmb할부기간.AddItem "일시불"
● ‘종료(cmd종료)’ 단추를 클릭하면 <그림>과 같은 메시지 박스를 표시한 후 폼을 종료하는 프로시저를 작성하시오.
▶ 시스템의 현재 날짜와 시간 표시
MsgBox Now, , "등록화면을 종료합니다."
Unload Me
❗MsgBox “메세지 내용”, 메세지 종류, “메세지 제목”
● 아이콘 종류
VbYesNo: 예 / 아니오
VbYesNoCancel: 예 / 아니오 / 취소
VbAbortRetryIgnore: 중단 / 다시시도 / 무시
VbInformation: 알림
VbCritical: 경고
VbExclamation: 느낌표
VbQuestion: 물음표
● 캡션
Msgbox Me.Caption & “폼을 종료합니다”
● 조건
a = msgbox(“메시지”, VbYesNo, “제목”)
If a = vbyes then
조건
End If
● 입력되어 있는 행 출력
▶ 전체 건수를 표시하시오.
Msgbox "전체 건수는 " & [C4].CurrentRegion.Rows.Count - 1 & "건 입니다."
❗-1을 해주는 이유는 행 제목을 제외하기 위함
● 셀에 내용 입력, 글꼴 수정
[A1] = "셀에 내용 저장"
[A1].Font.Bold = True
[A1].Font.Size = 12
[A1].Font.Name = "궁서체"
● 날짜 형식 Format(값, "형식")
표시 예: 2023-May-05 01:15:26 AM
결과: txt시간 = Format(Now(), “yyyy-mmm-dd hh:nn:ss AM/PM”)
표시 예: AM 09:20
결과: txt시간 = Format(Now(), "AM/PM hh:mm")
m: 1
mm: 01
mmm: Jan
mmmm: January
d: 1
dd: 01
ddd: Sun
dddd: Sunday
aaa: 일
aaa: 일요일
AMPM: 오전 오후
AM/PM: AM PM
● 워크시트의 값이 변경되면 셀 포인터 이동
▶ 셀의 데이터가 변경되면 해당 셀로 셀 포인터가 이동되도록 하시오.
▶ 글꼴은 궁서체, 글꼴크기는 10으로 지정하시오.
Target.Activate
Target.Font.Name = "궁서체"
Target.Font.Size = 10
❗Worksheet, Change
❗Target.Activate 셀 포인터 이동
● 워크시트가 활성화되면 데이터 입력
▶ 워크시트가 활성화되면 [A1]셀에 "데이터"를 입력하시오.
[A1] = “데이터”
❗Worksheet, Acitvate
● 폼이 초기화되면 날짜(cmb날짜) 콤보상자의 목록에 현재부터 2일전까지 날짜를 추가하시오.
cmb날짜.Additem Date - 2
cmb날짜.Additem Date - 1
cmb날짜. Additem Date
● 폼이 초기화 되면 목록(cmb목록)에 “기타작업“시트 I3:I10셀을 추가하시오.
cmb목록.Rowsource = “'기타작업' ! I3:I10”
