IF, Rank, OR

각 시도의 내림차순순위가상위10이 이상이거나, 각 시도의 시설수가2가아니면, 각 시도의 특수교사 값을 입력하고, 그외에는 시설수의 값으로 나타내라

=if(or(rank(해당셀,범위-절대)>=10,시설셀><2),특수교사셀,시설수셀)

sum,countif,round

제시한 수식으로 구하고, 각시도의 단거리부터 기타까지의 값중 20이상인건수의 자리에서 반올림하시오

=round(수식-sum 활용,countif(범위,판단조건->20))

=ROUND(B5/SUM($B$5:$B$20)*100,COUNTIF($B5:$E5,">=20"))

총 시ㆍ도수를 구하시오

①C23셀에서  를 클릭하여 함수마법사 대화상자에서 COUNTA함수를 선택합니다. ②value1에 D7:D20로 지정합니다. ※COUNTA함수는 공백이 아닌 셀의 개수를 셀 때 사용하는 함수입니다.범위내에서 데이터가 들어있는 셀의 개수만 셉니다.COUNT함수는 데이터가 숫자일 경우 숫자가 들어있는 셀의 개수를 셉니다.

부분: 표의 모든 데이터(평균포함)중 크기가 500,000 이하인 셀의 개수를  구하시오.(반드시 COUNTIF함수사용,미사용시 감점처리)

=COUNTIF(B4:I9,"<=500000")

(H6~H17셀)에 비고를 구하시오.비고는 반입건수가 반출건수보다 큰 경우 문자열로 ‘건수’를, 반입금액이 반출금액보다 큰 경우 문자열로 ‘금액’을, 반입건수가 반출건수보다 크고 반입금액이 반출금액보다 큰 경우 문자열로 ‘건수금액’이 나타나도록 하시오.

(반드시 수식 및 함수를 사용)

=IF(B6>E6,"건수","")&IF(D6>G6,"금액","")

(J5~J7셀)에 해역별로 생산량이 가장 많은 년도를 ‘~년’의 형태로 구하시오.

 =INDEX($B$4:$I$7,1,MATCH(MAX(B5:I5),B5:I5,0))&년"

 =INDEX($B$4:$I$7,1,MATCH(MAX(B6:I6),B6:I6,0))&"년"

 =INDEX($B$4:$I$7,1,MATCH(MAX(B7:I7),B7:I7,0))&"년"

해법]해역별로 생산량이 가장 많은 년도를 index함수, match함수, max함수를 이용하여 해결합니다.

index함수로 자료의 범위 내에서 ==> B4:I7

년도가 입력된 행번호를 입력하고 ==> 1  (데이터 표 중에서 첫번째 행에 해당)

max함수로 각 해역별로 1992년부터 1999년데이터 중에서 최대값을 찾고, ==> MAX(B5:I5)

match함수를 사용하여 최대값의 위치에 해당하는 열번호를 찾은 후 ==> MATCH(MAX(B5:I5),B5:I5,0)

년도가 입력된 행번호와 최대값의 위치에 해당하는 열번호를 이용하여 index함수로 생산량이 가장 많은 년도를 출력

1종 면허 소지자가 가장 많은 도시는 어디인가

1종면허소지자가 가장 많은 도시를 index함수,match함수, max함수를 이용

우선 max함수로 1종면허 소지자 중 최대값을 찾고, ==> MAX(D8:D21)

match함수를 사용하여 최대값의 위치에 해당하는 행번호를 찾은 후 ==> MATCH(MAX(D8:D21),D8:D21,0)

시도별 이름이 입력된 열번호를 이용하여 ==> 1

index함수로 자료의 범위 내에서  ==> A8:D21

1종 면허 소지자가 가장 많은 도시의 이름을 구해냅니다.

=INDEX(A8:D21,MATCH(MAX(D8:D21),D8:D21,0),1)

‘서울 여자’의 ‘최저 발생 연령군’을 ‘  –  세’로 구하시오.

①C27셀에서     를 클릭하고 마법사 대화상자에서 INDEX함수를 선택합니다.

②Array : 최저 발생 연령군의 값을 찾을 데이터 범위 A7:D25를 지정합니다.

③Row_num에서 이름상자의     버튼을 클릭하여 MATCH함수를 선택하고 Lookup_value에서

④ 다시 이름상자의     버튼을 클릭하여 MIN함수를 선택하여 Number1을 D7:D25로 지정합니다. --> 현재 값을 찾는 기준은 서울여자의 데이터 중 최소치이므로  MIN함수를 이용

⑤수식 입력 줄에 있는 MATCH함수를 클릭하면 MATCH 함수 대화상자가 나타납니다.

Lookup_value : MIN(D7:D25) --> ④를 수행하여 나타난 값

Lookup_array : D7:D25 --> 데이터를 찾을 범위

Match_type: 0 --> 정렬되지 않은 상태에서 Lookup_value와 같은 첫번째 값

⑥수식 입력 줄에 있는 INDEX함수를 클릭하면 INDEX함수 대화상자가 나타납니다.

Array : A7:D25 --> ②를 수행하여 나타난 값

Row_num : MATCH(MIN(D7:D25),D7:D25,0) --> ⑤를 수행하여 나타난 값으로 Array에서 찾고자 하는 행을 의미

Column_num : 1 --> Array에 입력된 영역에서 연령군에 해당하는 첫번째 열을 의미

⑦수식입력줄의 맨뒤에 &”세”을 입력합니다. 따라서 함수식은  

 =INDEX(A7:D25,MATCH(MIN(D7:D25),D7:D25,0),1)&"세"

96년 수출 금액 중 최저 금액에 해당하는 물품명을 반드시 함수를 사용하여   구하시오.

①D열 머리글을 클릭하고 마우스 오른쪽버튼 클릭하여 삽입을 선택하여 열을 하나 추가합니다.

②A6:A26을 범위로 지정한 후 복사한 후 D6셀을 클릭 하고 붙여넣기 합니다.

③F28셀에서  를 클릭하고 VLOOKUP함수를 선택합니다.

Lookup_value : MIN(C6:C26)-->현재 값을 찾는 기준은 96년 금액 중 최소값이므로 MIN함수를 이용.

Table_array : C6:D:26 --> 데이터를 찾을 정보 표(기준표)--96년 금액과 어종이름

Col_index_num : 2 --> 기준표에서 구하고자 하는 열의 위치-숫자를 입력한다. 기준표 범위내에서 비교값인 96금액을 기준으로 어종이름은 2열에 해당. (C열과 D열에서 C열은 1번째열  D열은 2번째 열에 해당하므로 2를 입력합니다.)

Range_lookup : 0 --> 함수가 정확히 일치하는 값을 찾을 경우 0이나 FALSE를 입력한다.

따라서 함수식은=VLOOKUP(MIN(C6:C26),C6:D26,2,0)확인버튼을 클릭하면 정확하게 데이터에서 원하는 값인 "김"을 추출해 냅니다.

체육학과 여학생의 수가 가장 많은 해의 여학생 비율을 '~%'의 형태로 구하시오(반드시 수식 또는 함수 사용)

=VLOOKUP(MAX(E6:E11),E6:G11,3,0)&"%

환산금액은 환율 표를 이용하여 vlookup 함수로 구하시오.

환산금액=금액/각 일자에 해당하는 환율

①C11셀을 선택하고 수식입력줄에서 =A11/을 입력합니다.

②      를 클릭하여 함수마법사 대화상자에서 VLOOKUP함수를 선택합니다.

③Lookup_value : B11 --> 환율을 구할 기준이 되는 일자가 있는 셀을 선택합니다.

Table_array : A4:B6 --> 데이터를 찾을 정보 표(기준표)계산식을 복사할 경우 주소가 변하지 않아야 하므로 기준표는 F4키를 눌러 $A$4:$B$6의 절대주소형식으로 바꾼다.

Col_index_num : 2 --> 기준표에서 구하고자 하는 열의 위치-숫자를 입력한다. 기준표 범위내에서 비교값인 일자를 기준으로 환율은 2열에 해당

 Lookup_value에서 지정한 값을 Table_arry에서 찾아 지정한 열(2열)의 내용을 추출한다.  지금은 일자:05월02일 값을 찾아 그에 해당하는 환율값 '1110.00'을 돌려준다.

Range_lookup : 0 --> 함수가 정확히 일치하는 값을 찾을 경우 0이나 FALSE를 입력한다.

따라서 함수식은=A11/VLOOKUP(B11,$A$4:$B$6,2,0)

                       =A12/VLOOKUP(B12,$A$4:$B$6,2,0) 

                       =A13/VLOOKUP(B13,$A$4:$B$6,2,0)이 된다.