엑셀 오답 정리 계산작업 파트

[기본작업]

sum 배열함수 

SUM(($B$41:$B$67=H41)*($F$41:$F$67>AVERAGE($F$41:$F$67))*$F$41:$F$67)

 

배열함수가 되지 않을 때 노트북 오류를 의심하자. 입력기 - 한컴으로 되어있으면, 배열 함수 적용이 안되는 경우 다수. 

 

엑셀12회 모의고사 1,2,3번 다시 확인 필요

[계산작업]

엑셀16회 모의고사 2. [표1]의 입차시간, 출차시간을 이용하여 주차대수[M14:M18]를 표시하시오. (6점) ▶ (출차시간 - 입차시간)의 시각값을 이용하여 주차대수를 아래 예처럼 표시하시오. [표시 예 : (출차시간 - 입차시간)의 시각값이 3 이라면 ---> ★★★ ] ▶ FREQUENCY, HOUR, REPT를 이용한 배열수식
FREQUENCY 사용시 절대참조X 

FREQUENCY(데이터범위,조건범위)

조건 범위는 전체를 잡을 것. 

 

FREQUENCY(HOUR(E3:E27)-HOUR(D3:D27),K14:L18),

-> 이거 오답! 

-> 정답은 =REPT("★",FREQUENCY(HOUR(E3:E27)-HOUR(D3:D27),K14:L18))

FREQUENCY 함수에서 마지막 조건 범위 잡을 때, 마지막 범위만 잡아야함. 

'출차시간-입차시간'을 계산할 때 범위 설정 틀림. 

HOUR(E3:E27)-HOUR(D3:D27) => (X) 

HOUR(E3:E27-D3:D27)  => (O) 

 

엑셀16회 모의고사 4. [표1]의 입차시간과 출차시간을 이용하여 [표4]에 시간대별 주차가능대수[D33:G36]을 구하시오. (6점) ▶ 시간대별 주차가능대수 = 총 주차가능대수 - 해당 시간 이전 입차대수 + 해당 시간 이전 출차대수이며 수치 뒤에 ‘대’를 붙이시오. [표시 예 : 시간대별 주차가능대수가 3 이라면 ---> 3대 ] ▶ SUM, IF, TEXT 함수를 이용한 배열 수식

 

내 오답 =TEXT(D$31-SUM(IF(($C33<=$D$3:$D$27)*(D$32=$C$3:$C$27),1))+SUM(IF(($C33>=$E$3:$E$27)*(D$32=$C$3:$C$27),1)),"0대")

정답: =TEXT(D$31-SUM(IF(($C$3:$C$27=D$32)*($D$3:$D$27<=$C33),1))+SUM(IF(($C$3:$C$27=D$32)*($E$3:$E$27<=$C33),1)),"0대")

 

원인: 해당시간 이전 입차대수는 $D$3:$D$27<=$C3 인데 부등호 표기 반대로함 

* 다음의 보너스 문제들은 계산작업-2 시트에서 작업하시오. 6. [표1]에서 아래 조건에 맞는 자료의 평균을 “H14"셀에 나타내시오. (보너스) ▶ 게임종류가 ‘스타나라’이거나 ‘삼국장군’ 이면서 판매량이 200 이상인 자료의 판매량 평균을 구하시오. ▶ 조건은 [H3:H4]셀에 기술하시오. ▶ DAVERAGE, AND, OR 이용

 

daverage 함수는 절대값 참조를 하지 않음. 

조건이 다수 일때, 세로줄로 '조건' '논리식' 기재 

field에는 계산할 필드의 열 넘버 기재. 

 

=DAVERAGE(C3:F17,4,H3:H4)

 

엑셀15회 모의고사 2.  대출기간(월)과 이율(년),대출금을 참고하여 월 납부금을 구한 뒤 [표2-2]를 활용하여 월 납부금에 따른 등급을 구하시오. 

PMT 함수 주의 사항. 

PMT(rate,nper,pv) 

=> rate와 nper의 단위수준이 맞아야함. 

=> pv는 '-'값으로 기재! (오답 원인)

 

엑셀15회 모의고사 4.  

내 오답 =CONCATENATE(SUM(($H41=$B$41:$B$67)*($F41:$F67>=AVERAGE($F$41:$F$67))*$F$41:$F$67),"(세대수 : ",SUM(($H41=$B$41:$B$67)*($F41:$F67>=AVERAGE($F$41:$F$67))*1),")")

정답 =CONCATENATE(SUM(($B$41:$B$67=H41)*($F$41:$F$67>AVERAGE($F$41:$F$67))*$F$41:$F$67),"(세대수 : ",SUM(($B$41:$B$67=H41)*($F$41:$F$67>AVERAGE($F$41:$F$67))*1),")")

 

초과인데 부등호 >= 사용함. + 절대값 설정 오류. 

 

엑셀14회 모의고사 3번 [표3]을 이용하여 포지션별로 신장이 가장 큰 사람의 성명을 [I82:I85] 영역에 표시하시오.

▶ INDEX,MATCH,MAX 이용

서식순서: index(추출범위,match(max((조건1)*(조건2)),((조건1)*(조건2)),0),1)

 

엑셀14회 모의고사 4번 [표4]의 포지션과 나이를 이용해서 포지션/연령대별 인원수를 [I112:K115]영역에 구하시오.

▶ SUM,IF,INT 이용

=SUM(IF(($H112=$D$112:$D$131)*(I$111=INT($E$112:$E$131/10)),1))

 

엑셀13회 모의고사 1번 

▶LEFT,RIGHT,FIND,AND 이용 고급필터 적용하기.

=> find로 인해서 value오류값이 추출되도, 고급필터는 잘 적용된다.

 

엑셀13회 모의고사 2번 

▶COUNTIF, LEFT, IF 이용 누적수만큼 표기하기

=> 누적값구하는 방법 첫셀(절대값):첫셀 =SUM($B$22:B22)

=COUNTIF($C$22:C22,LEFT(C22,1)&"*")

첫번쨰문자와 동일한 것 가져와라. => left(C22,1) & "*" 주의! 

 

엑셀13회 모의고사 3번 

▶SUM,IF,&이용 순위구하기

▶ 시도가 같다면 지정점수가 높은 쪽이 1순위가 되도록 배정하시오.

=SUM(IF(($C$42:$C$57=$C42)*($E$42:$E$57>E42),1))+1&"순위"

전체의 값과 나의 값을 비교했을 때 전체의 값이 더 클 때로 식을 구한 이유?

마지막에 +1을 해야 0순위가 사라진다. 

 

엑셀13회 모의고사 6번(보너스문제)

▶HLOOKUP, MATCH 이용 총합계 구하기

=>대한은행, 그외 은행에 대한 MATCH함수 적용 

=D4*HLOOKUP(C4,$C$13:$G$15,MATCH(B4,{"한국은행","대한은행"},-1)+1,TRUE)

=> 내림차순 -1 {}괄호 내 조건 기재.

 

엑셀12회 모의고사 2-1번

▶LARGE,IF 이용 배열 수식 구하기

=> LARGE(IF(조건,계산범위,번호)) 

=> 번호에 해당하는 {1;2;3} 적용해도 답이 안나옴. 

 

[분석작업]

엑셀 16모의고사 1.

피벗테이블 전체 0점 . 

원인: B2가 시작셀인데 B3을 시작셀로 설정함. 

 

엑셀 16모의고사

▶ [데이터 유효성 검사] 기능을 이용하여 [표5]의 분류[C31:C40]에는 [H31:H33]에 기재된 항목만이 입 력 가능하도록 설정하고, 답례선물[F31:F40]에는 ‘사과’, ‘배’, ‘새우젖’만 입력 가능하도록 설정하시오. (제한 대상을 목록으로 이용할 것) - 유효하지 않은 데이터를 입력한 경우 <그림>과 같은 오류 메시지가 표시되도록 설정하시오.

데이터유효성검사 중 [H31:H33]에 기재된 항목만이 입력 -> 셀 값 설정 '목록'으로 변경 -> 수식에 "= 절대값포함범위" 표기