시나공

시험에

나오는 것만

공부한다!

일단 저도 시험 준비 중이지만 답변 해드립니다.

1. 님이 작성한 식대로 하면,

F3이랑 (1-HLOOKUP(D3+F3,$B$14:$E$15,2) 곱한값에다가 D3이 더한 값으로 나올겁니다..

그래서, D3과F3을 더하고 괄호로 묶고서 하는게 맞는 방법입니다.

2.이 문제는 배열수식입니다. 배열수식 응용을 해야하는데요,

AVERAGE(IF( (조건1),(조건2)*(계산범위) ) 이걸 응용 하는 겁니다.

문제가 반별 최고 평균점수를 제외한 평균의 반별 평균을 구하는 건데요.

반별(조건1),최고평균 점수를 제외(조건2) 인데요,

조건2이가 ($I$3:$I$23=K16)*($F$3:$F$23)<>MAX(($I$3:$I$23=K16)*($F$3:$F$23)) 인데요,

이문제는 배열수식으로 풀어야 하는 문제이기 때문에 조건2도 배열수식으로 만들어야 합니다.

조건2 구하는 방법이 합계 구하는 배열수식에서 ( 조건*계산범위 ) 에다가 최대값(최고점수) 구하는 배열수식

MAX( (조건)*(계산범위) ) 인데요.

최고평균 점수를 제외 하고 이기 때문에 제외 표시인 <> 를 넣어 주는겁니다.

그레서 AVERAGE(IF(($I$3:$I$23=K16)*(($I$3:$I$23=K16)*($F$3:$F$23)<>MAX(($I$3:$I$23=K16)*($F$3:$F$23))),$F$3:$F$23)) 가 되는거구요,

INT는 소수점 아래를 버리는 함수 입니다. 이게 계산해보니 75,617 나오던데, 소수점 아래는 버림으로,

답이 75가 됩니다.

참고로 유사한 함수로 ROUNDDOWN이 있습니다.

사실 이문제는 저도 좀 의아한데,

합계를 구하고 최대값을 제외하는게 맞는건데, 사실 SUM( (조건)*(계산범위) )가 정확한 표현인데

이 책에서는 이렇게(조건 * 계산범위) 표시되어 있더라구요,, 이부분이 저도 제일 의문인 부분 입니다..;

이부분은 지은이의 설명이 더 정확할 것 같습니다.

배열 수식문제는 많이 풀어보면 어느게 조건1이 뭐고, 조건2가 뭔지 눈에 조금씩 조금씩 보이기

시작할겁니다. 컴활1급 자체가 원래 그렇지만, 특히 함수부분은 많이 풀어볼수록 눈에 보입니다.

3. 3에서 MID는 텍스트함수 입니다. 숫자가 아닙니다. 즉 반환해서 님의 화면에 보이는건 숫자입니다만.

엑셀프로그램에서 인식한건 문자로 인식한거죠. 엑셀에서 문자를 쓰면 왼쪽으로 글씨가 써지죠?

똑같은 겁니다. 근데 이걸 숫자로 인식하게 할려면 *1을 하면 된다고 합니다. 이 문제는 질문하신분들

많아서 잘 찾아보시면 지은이의 설명이 나와있는 글들이 있습니다. (저도 몰라서 함수 풀때 초반에 좀 해맸던 문제인지라..)

4. REPT는 아실거라봅니다. QUOTIENT는 나누기 할때 나눈 몫을 반환합니다.

즉 101나누기 4하면 25가 몫이 잖아요? 마찬가지 입니다. 문제에서 I3이 4050이라

4050나누기 1000하면 4가 몫임으로 4가 남습니다 . 이 4를 ★로 표시한게 앞부분입니다.

근데 이미 앞에서 검정색별로 표시를 했습니다. 문제에서는 총 별 10개로 ★과 ☆로 색깔을 표현해야 합니다.

(검정별이 앞으로 해서요)

근데 이미 검정색별로 6개가 표기가 되었습니다. 그래서 뒤에서는 4칸으로 나와야하기때문에,

QUQTIENT(4)앞에 "10- " 를 붙이는거죠.

5.TEXT는 형식을 바꿀때 쓰는겁니다. (C3,"YYMMDD-") 는 YYMMDD가 연월일이기 때문에,

"YYMMDD-"가 주민번호 앞번호에다 -까지 붙여서 되죠.

그다음 연결연산자&를써서 IF로 함수를 만들어 나가는 겁니다.

문제에서 2000년도 이상은 남자는 3, 여자는 4를 하라고 했기때문에,

일단 이부분을 IF로 해서 각각 만들어 버립니다.

IF( YEAR(C3)>=2003, 3,1 ) ㅡ> C3의 값이 2003 이상이라면 3 아니면 1을 쓰고,

IF (YEAR(C3)>=2003, 4,2 ) ㅡ> C3의 값이 2003 이상이라면 4 아니면 2를 쓰란 이야기죠.

근데 여기서 성별 표시를 하란 조건도 있습니다.

그래서 IF(E3="남", ) <ㅡ 이 부분이 있어야 합니다. E3이 "남" 이면,

이걸 다 합치면 그래서,

IF(E3="남",IF(YEAR(C3)>=2003,3,1),IF(YEAR(C3)>=2003,4,2)) 가 되는데,

E3이 "남"이면, C3셀이 2003 이상인 경우 3이고, C3셀이 2003 이하면 1이 되고,

E3이 "남"이 아니면("여" 이면) C3셀이 2003 이상인 경우 4이고, 이하면 2가 되는거죠.

그리고 뒤에다 연결연산자 &를 붙이고 "******"를 작성하면 됩니다.

이 문제 제 주관적으로는 어렵다고 생각되는 문제 중 하나입니다..

저도 처음에 풀때 몇번 해매고 풀었었습니다 ㄷ

(저도 컴활 계속 공부중이지만 ㅎ)

여기까지 제가 공부한걸 바탕으로 설명할 수 있는 만큼 설명 드렸습니다.

도움 되셨으면 합니다 .

1.(D3+F3)*(1-HLOOKUP(D3+F3,$B$14:$E$15,2))(124page)

D3+F3*(1-HLOOKUP(D3+F3,$B$14:$E$15,2))을 하면 출력형태랑 값이 틀려요

제가 스스로 판단해야 할까요?

-> +보다 * 가 먼저 계산되므로 f3*(1-hlookup ~~ 부분이 먼저계산 되고 d3이 더해지므로 같은 결과가 나오지 않는 것입니다.

먼저 계산되어야 하는 값은 () 로 묶어서 표시하세요.

2.INT(AVERAGE(IF(($I$3:$I$23=K16)*(($I$3:$I$23=K16)*($F$3:$F$23)<>MAX(($I$3:$I$23=K16)*($F$3:$F$23))),$F$3:$F$23))) (129page)

제가 이런 공식을 다 안다 한들 괄호수만 보면...ㅜㅜ

-> int(avearge(if((조건1)*((조건2)*(범위)<>max((조건3)*(값을구할범위))),값을구할범위)))

가 됩니다. 조건2와 조건3에서 범위가 지정된 것은 비교할 값을 구하기 위한 것이구요.

마지막의 값을 구할범위에서 결과 값이 나오게 됩니다.

max값에서도 반별로 조건을 적용하기 위한 것이죠.

=INT(AVERAGE(IF(($I$3:$I$23=K16)*(($I$3:$I$23=K16)*($F$3:$F$23)<>MAX(($I$3:$I$23=K16)*($F$3:$F$23))),$F$3:$F$23)))

IF( ($I$3:$I$23=K16) *

반이 같은지 비교합니다.

(($I$3:$I$23=K16)*($F$3:$F$23)<>MAX(($I$3:$I$23=K16)*($F$3:$F$23)))

반이 같은 사람의 평균과 반이 같은 사람의 평균 중 가장 큰 값을 비교하여 큰 값과 같지 않은 값을 추출합니다. 여기의 조건의 앞의 조건과 이어지는 것이 아닙니다. 평균 중 가장 큰 평균을 제외하는 조건으로 반은 값 안에서의 가장 큰 값을 제외하기 위한 조건입니다.

<> 은 같지않다, ~이 아닌 이므로 max값을 제외하기 위한 것입니다.

3.SUM((MID($A$4:$A$25,5,2)*1)=J29)*1)/COUNTA($A$4:$A$25)(133page)

MID($A$4:$A$25,5,2)*1)=J29)*이건 무슨 의미인지 감이 안 잡힙니다.

->

mid, right, left 함수는 텍스트 함수라서 결과가 숫자 모양이더라도 문자로 나타나게 됩니다.

문자로 나타난 숫자를 숫자로 사용하려면 *1 을 하시면 숫자로 사용하실 수 있으세요.

그래서 *1 을 한 것입니다.

*1 을 한 mid 값이 숫자가 되면 f34에 지정된 숫자와 비교하여 참인지 거짓인지 판단하게 됩니다.

*1 을 하지 않아도 정확한 결과가 나온다면 생략하셔도 됩니다.

sum(mid(조건)*1) 을 한것은 mid 에서 나온 결과와 j29 를 비교하여 나온 결과가 true 나 false 이기 때문에 이값이 1을 곱하여 숫자로 나타내기 위한 것입니다.

4. REPT("☆",10-QUOTIENT(I3,1000))(일부분)(135page)

10을 뺀 의미가 궁금합니다.

-> QUOTIENT 함수는 나눗셈 몫의 정수부분을 반환하는 함수 입니다.

여기서 나온 결과가 8이면 ★ 을 8개를 표시하고 ☆ 을 2개를 표시해야 하므로

10 - QUOTIENT(i3,1000) 으로 작성한 것입니다. 10-8 은 2가 되니까요.

즉 별이 10개가 표시될 수 있도록 하는 것입니다.

5.TEXT(C3,"YYMMDD-")&IF(E3="남",IF(YEAR(C3)>=2003,3,1),IF(YEAR(C3)>=2003,4,2)) & "******"(139page)

이 수식 자체가 감 안잡혀요. 설명 부탁드립니다.

->

text 함수는 숫자를 표시형섹에 맞는 텍스트로 변환하는 함수입니다.

text(인수,형식) 으로 사용되며 형식 부분에는 숫자를 어떤 형식으로 표시할지 지정하셔야 합니다.

"YYMMDD-" 로 작성했으므로 생년월일인 1980-05-03을 800503- 으로 표시하게 됩니다.

& if ~~ 인 if 문에서는 e3의 값이 남 인경우 다시 if 문을 실행하여 year의 값이 2000 이상일때 3을 반환하고 아니면 1을 반환합니다.

e3의 값이 남 이 아닌경우는 여자이므로 마지막 if 값을 실행하여 year의 값이 2000 이상이면 4, 아니면 2 를 반환하게 되죠.

그럼

"800503-" & "2" & "******" 가 되므로

이것을 모두 붙여서 800503-2****** 로 표시가 되는 것입니다.

좋은 하루 되세요.