3. <제1작업> 값 계산
≪출력형태≫
⑴ 판매사 ⇒ 제품코드의 네 번째 글자가 1이면 ‘한국’, 2이면 ‘대한’, 그 외에는 ‘온라인’으로 구하시오(IF, MID 함수).
☞ 상단 수식 입력 창에서 Fx를 클릭해 함수 마법사를 실행합니다.
☞ 두 개의 함수를 조합해서 사용하는 경우는 처음에 있는 함수를 먼저 실행합니다.
☞ IF 함수를 검색하고 실행합니다.
☞ 처음 함수의 첫 번째 인수에 나중 함수를 넣어 줍니다.
☞ IF 함수의 첫 번째 인수에 'MID("를 입력합니다.
- IF 함수의 첫 번째 인수(Logical test) : 논리 검사를 수행 - false / true로 반환
- IF 함수의 두 번째 인수(Value if true) : true일 경우 돌려 주는 값
- IF 함수의 세 번째 인수(Value if false) : false일 경우 돌려 주는 값
☞ 상단 수식 입력 창에서 'MID'를 클릭해서 MID 함수 인수 입력창으로 변환합니다.
☞ MID 함수의 첫 번째 인수에 문자를 추출할 문자열(B5)을 입력합니다(조건 : 제품코드).
- MID 함수의 첫 번째 인수(Text) : 문자를 추출할 문자열
- MID 함수의 두 번째 인수(Start num) : 추출할 문자의 위치
- MID 함수의 세 번째 인수(Num chars) : 추출할 문자의 개수
☞ MID 함수의 두 번째 인수에 '4'를 입력합니다(조건 : 네 번째 글자).
☞ MID 함수의 세 번째 인수에 '1'를 입력합니다(조건 : 한글자 추출).
☞ 상단 수식 입력 창에서 'IF'를 클릭해서 다시 IF 함수 인수 입력창으로 변환합니다.
☞ IF 함수의 첫 번째 인수는 반환될 값이 '1'인지 아닌지를 먼저 판단해야 하기에 MID(B5,4,1) 값 뒤에 ="1"을 넣어서 논리 검사식을 완성합니다.
☞ MID 함수에서 넘어온 값은 문자이기 때문에 비교 값을 ""로 묶어 주어야 합니다.
☞ IF 함수의 두 번째 인수에 true일 경우 반환될 값 '한국'을 입력합니다.(조건 : 1=한국).
☞ IF 함수의 세 번째 인수에 false일 경우 반환될 값을 입력합니다.(조건 : 2=대한, 그 외= 온라인).
☞ 반환해야 할 false 값이 두 개이니 첫 번째 인수의 논리 검사를 다시 실행해야 합니다.
☞ 수식 입력 창에서 IF(MID(B5,4,1)="1"를 복사해서 3번째 인수에 붙여 넣기 합니다.
☞ 상단 수식 입력 창에서 두 번째 'IF'를 클릭해서 IF 함수 인수 입력창으로 변환합니다.
☞ IF 함수의 첫 번째 인수는 반환될 값이 '2'인지 아닌지를 판단해야 하기에 MID(B5,4,1) 값 뒤에 ="2"을 넣어서 논리 검사식을 완성합니다.
☞ IF 함수의 두 번째 인수에 true일 경우 반환될 값 '대한'을 입력합니다.(조건 : 2=대한).
☞ IF 함수의 세 번째 인수에 false일 경우 반환될 값 '온라인'을 입력합니다.(조건 : 그 외= 온라인).
☞ 자동 채워 넣기 기능으로 나머지 값을 구하고 옵션에서 '서식 없이 채우기'를 선택합니다.
⑵ 비고 ⇒ 소비전력(W)의 오름차순 순위를 구하시오(RANK.EQ 함수).
☞ 상단 수식 입력 창에서 Fx를 클릭해 함수 마법사를 실행합니다.
☞ RANK.EQ 함수를 검색하고 실행합니다.
☞ RANK.EQ 함수의 첫 번째 인수에 비교할 값 G5를 입력합니다(조건 : 소비전력의 순위).
- RANK.EQ 함수의 첫 번째 인수(Text) : 비교할 값
- RANK.EQ 함수의 두 번째 인수(Start num) : 값을 비교할 범위
- RANK.EQ 함수의 세 번째 인수(Num chars) : 오름차순(1)/ 내림차순(0 또는 생략)
☞ RANK.EQ 함수의 두 번째 인수에 비교할 값의 범위 G5:G12를 입력합니다(조건 : 소비전력의 순위).
☞ RANK.EQ 함수의 세 번째 인수에 순위를 정할 방법 '1'을 입력합니다(조건 : 오름차순).
☞ 자동 채워 넣기 기능으로 나머지 값을 구하고 옵션에서 '서식 없이 채우기'를 선택합니다.
⑶ 엘큐전자의 제품 개수 ⇒ 결과값에 ‘개’를 붙이시오. 단, 조건은 입력데이터를 이용하시오(DCOUNTA 함수, & 연산자)(예 : 1개).
☞ 상단 수식 입력 창에서 Fx를 클릭해 함수 마법사를 실행합니다.
☞ DCOUNTA 함수를 검색하고 실행합니다.
☞ DCOUNTA 함수의 첫 번째 인수에 데이터베이스 범위 'B4:H12'를 입력합니다.
- DCOUNTA 함수의 첫 번째 인수(Database) : 데이터 베이스 범위
- DCOUNTA 함수의 두 번째 인수(Field) : 구할 값이 들어 있는 열의 위치(숫자 또는 열 레이블)
- DCOUNTA 함수의 세 번째 인수(Criteria) : 조건
☞ DCOUNTA 함수의 두 번째 인수에 비교할 값의 범위 'D4'를 입력합니다(조건 : 엘큐전자의 제품 개수).
☞ DCOUNTA 함수의 세 번째 인수에 순위를 정할 방법 'D4:D5'를 입력합니다(조건 : 엘큐전자의 제품 개수).
☞ 상단의 수식 입력 창에서 DCOUNTA 함수 뒤쪽을 클릭하고 & 연산자를 쓰고 "개"를 붙여 줍니다.
⑷ 2020년 이후 등록 제품의 소비전력(W) 평균 ⇒ 등록일자가 ‘2020-01-01’ 이후(해당일 포함)인 제품의 소비전력(W) 평균을 구하시오. 단, 정의된 이름(등록일자)을 이용하여 구하시오(SUMIF, COUNTIF 함수).
☞ SUMIF와 COUNTIF 함수의 조합은 SUMIF 값을 먼저 구하고 COUNTIF 값으로 나누어 주면 됩니다.
☞ 상단 수식 입력 창에서 Fx를 클릭해 함수 마법사를 실행합니다.
☞ SUMIF 함수를 검색하고 실행합니다.
☞ SUMIF 함수의 첫 번째 인수에 정의된 이름 '등록일자'를 입력합니다(조건 : 정의된 이름을 이용하여 구하시오).
- SUMIF 함수의 첫 번째 인수(Range) : 조건이 들어 있는 범위
- SUMIF 함수의 두 번째 인수(Criteria) : 조건
- SUMIF 함수의 세 번째 인수(Sum range) : 합계를 구할 범위
☞ SUMIF 함수의 두 번째 인수에 조건 ">=2020-01-01"를 입력합니다(조건 : 2020-01-01 이후(해당일 포함)).
☞ SUMIF 함수의 세 번째 인수에 합을 구할 범위 'G5:G12'를 입력합니다(조건 : 소비전력(W)의 평균).
☞ 수식 입력 창에서 SUMIF 함수 뒤에 '/'를 쓰고 COUNTIF 함수를 입력합니다.
☞ COUNTIF 함수의 첫 번째 인수에 정의된 이름 '등록일자'를 입력합니다.
- COUNTIF 함수의 첫 번째 인수(Range) : 조건이 들어 있는 범위
- COUNTIF 함수의 두 번째 인수(Criteria) : 조건
☞ COUNTIF 함수의 두 번째 인수에 조건 ">=2020-01-01"를 입력합니다.
☞ COUNTIF 함수의 인수는 SUMIF 함수의 인수와 같습니다.
⑸ 최대 사용면적(제곱미터) ⇒ (MAX 함수)
☞ 상단 수식 입력 창에서 Fx를 클릭해 함수 마법사를 실행합니다.
☞ MAX 함수를 검색하고 실행합니다.
☞ MAX 함수의 첫 번째 인수에 최대값을 구할 범위 'F5:F12'를 입력합니다(조건 : 최대 사용면적(제곱미터)).
- MAX 함수의 첫 번째 인수(Number 1) : 최대값을 구할 범위 또는 수
- MAX 함수의 두 번째 인수(Number 2) : 최대값을 구할 범위 또는 수
☞ ITQ 시험에서 MAX 함수의 인수는 첫 번째 값만 입력하면 됩니다.
⑹ 가격 ⇒ 「H14」셀에서 선택한 제품코드에 대한 가격을 구하시오(VLOOKUP 함수).
☞ 상단 수식 입력 창에서 Fx를 클릭해 함수 마법사를 실행합니다.
☞ VLOOKUP 함수를 검색하고 실행합니다.
☞ VLOOKUP 함수의 첫 번째 인수에 'H14' 를 입력합니다(유효성 검사를 통해 선택된 제품코드).
- VLOOKUP 함수의 첫 번째 인수(Lookup value) : 유효성 검사를 통해 선택된 값이 표시 된 셀
- VLOOKUP 함수의 두 번째 인수(Table array) : 표 머리글을 제외한 데이터 영역
- VLOOKUP 함수의 세 번째 인수(Col index num) : 값을 구할 열 레이블이 있는 위치
- VLOOKUP 함수의 네 번째 인수(Rnage lookup) : 정확하게 일치하는 값은 False, 비슷하게 일치하는 값은 true
☞ VLOOKUP 함수의 두 번째 인수에 'B5:H12'를 입력합니다(표 머리글을 제외한 데이터 영역).
☞ VLOOKUP 함수의 세 번째 인수에 '4'를 입력합니다(조건 : 제품 코드에 대한 가격 - 제품 코드로부터 4번째).
☞ VLOOKUP 함수의 네 번째 인수에 'false'를 입력합니다(ITQ 시험에서는 false만 사용합니다).
⑺ 조건부 서식의 수식을 이용하여 사용면적(제곱미터)이 ‘80’ 이상인 행 전체에 다음의 서식을 적용하시오(글꼴 : 파랑, 굵게).
☞ 조건부 서식을 적용할 영역을 선택합니다.
☞ 표 머리글을 제외한 데이터 영역의 행 전체를 선택하면 됩니다.
☞ 상단 메뉴의 홈 탭에서 조건부 서식을 클릭하고 새 규칙을 선택합니다.
☞ '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
☞ 수식 입력란에 '='을 쓰고 기준이 되는 셀을 선택합니다(조건 : 사용면적(제곱미터)).
☞ 비교해야 되는 값이 아래쪽으로 내려가면서 비교해야 되기 때문에 '$F$5'에서 '5'앞의 $를 삭제합니다.
☞ 수식을 완성합니다(조건 : '80'이상).
☞ 서식 버튼을 클릭해서 셀 서식을 설정합니다(조건 : 글꼴 - 파랑, 굵게).
