(5) 함수 - 3 (찾기, 참조)

지난시간 복습

 

* 정의된 이름을 사용하여 입주일이 가장 빠른 업체가 1이 되도록 구하고, 결과 값 뒤에 "위"를 붙여라.(RANK.EQ함수 &연산자)

-----> 정의된 이름을 사용하라고 했으니, 이름정의를 먼저해주고

         = RANK.EQ(E5,입주일,1)&"위"

 

* (입주일+계약기간x365 - 시스템 오늘날짜)가 90이하면 재계약, 아니면 공백으로 하시오.(IF, TODAY함수)

-----> 함수식에서 곱셈은 *으로 표현

          = (=IF((E5+F5*365-TODAY())<=90,"재계약","")

 

* IT업종 계약비율(%)- it업종개수/업종전체개수 로 구한 결과값에 퍼센트수치로 표시하시오(COUNTIF,COUNTA함수이용)

-----> COUNTA함수는 인수들 중 비어있지 않은 셀의 개수를 구한다. (즉 전체셀이 개수를 센다)

       Fx=COUNTIF(C5:C12,"IT")/COUNTA(C5:C12)

 

* IT업종 입주업체 월 임대료 합계 구하기(DSUM함수 이용)

-----> DSUM함수는 (범위, 열 번호, 조건) -- 범위에서 조건에 맞는 자료를 대상으로 지정된 열의 합계를 구한다.

        Fx= DSUM(전체셀, 월임대료, 업종IT)

 

 

 

 

 

 

 

 

 

 

 

 

 

* 정비총액의 큰값이 되도록 구하되 1~4까지만 표시하고 나머지는 공백으로 표시하시오.(IF, RANK.EQ함수 사용)

---> RANK.EQ에서 순위를 매길 목록은 절대주소로 표현하는거 잊지말기

FX==IF(RANK.EQ(H5,$H$5:$H$12,0)<5,RANK.EQ(H5,$H$5:$H$12,0),"")

if함수는( 조건,참일때의 값, 거짓일때의 값)

 

* 엔진 정비부서의 정비총액평균(DAVERAGE함수, &연산자)

 

 

DATABASE= 데이터의 전체 범위

FIELD = 값을 구할 대상

criteria = 조건범위

 

 

 

 

 

 

 

 

* 정비부서가 도색인 정비총액 합 - SUMIF함수 활용

--->

SUMIF함수= 범위에서 조건에 맞는 자료의 합계를 구한다. 

 

RANGE - 정비부서 목록

Criteria - "도색"

sum_range - 정비총액 목록

 

 

 

 

+++DSUM에서 조건입력시 하나의 열과 열의 데이터가 같이 입력되어야하기 때문에

열 제목과 붙어있는 데이터인경우 DSUM이 사용가능하지만 떨어진 경우(도색의 경우) SUMIF를 이용할 수 있을 것 같다.

 

 

---------------------------------------------------------

(5) 함수-3 (찾기, 참조)

* 물건명의 오른쪽 한 문자가 1이면 " 안함" , 2이면 "부분개조", 3이면 "개조"로 표시하시오.(CHOOSE,RIGHT함수)

---> CHOOSE함수 = (index_num, value1, value2..)

                               = index_number가 1이면 value 1, 2이면 value2, 3이면 value3..

 

* '실거래가X수수료율'로 구하고, 수수료율은 셀영역을 참조하여 구하기. 소수점 이하는 절삭(HLOOKUP, TRUNC함수)

---> HLOOKUP함수 = (lookip_value, table_array, row_index_num,range_lookup)

                                  lookup_value - 조회하려는 값

                                  table_array - 조회값이 있는 범위

                                  index_num - 반환하려는 값이 있는 table_Array의 행 번호(가로줄)

                                  range_lookup - 0이면 정확히 일치, 1이면 근사값 반환

 

즉, 매매에는 매매에 대한 수수료율만 적용해야되니까 

1) 매매로된 셀에 대해서만보려고 함

2) 수수료율 전체 표 선택(절대주소)

3) 2번에서 선택한 값에서 2번째줄의 수수료율만 선택할거니까 2 입력

4) 0입력

 

 

 

**TRUNC함수 - 소수점을 버리고 값을 보여준다.

결과적으로 실거래값이랑 곱한 후 TRUNC함수에 적용해주면 된다.

fx= TRUNC(HLOOKUP(E5,$B$17:$E$18,2,0)*G5)

 

* 정의된 이름 (중개실적)을 사용하여 c15셀값에 따른 현시세를 구하시오.(vlookup함수)

--> VLOOKUP함수 = (lookup_value, table_array, col_index_num, range_lookup)

                              lookup_value - 조회하려는 값

                               table_array - 조회값이 있는 범위

                               col_index_num - 반환값이 포함된 범위의 열 번호

                               range_lookup - 0이면 정확히 일치, 1이면 근삿값 반환

 

c15셀값에 따른 현시세이므로, lookup value는 c15이다. 

 

 

 

 

 

 

 

* 실거래가- c15셀값에 따른 실거래가를 구하라. 단, 셀이 비어있거나 잘못된 물건명이 입력되어 오류가 발생하는 경우에는 "없음"으로 표시하시오. (IF,ISERROR,VLOOKUP함수)

----> ISERROR() = ()값에 에러가 있는지를 확인하는 함수

      =IF(ISERROR(VLOOKUP(C15,중개실적,6,0))  ,  "없음"   ,  (VLOOKUP(C15,중개실적,6,0)) )

      IF함수에서 ISERROR함수를 통해 에러가 있다면 없음으로, 없다면 VLOOKUP함수 결과값을 나타내도록 한다. 

 

 

* 현시세 셀 영역에서 현시세가 가장 낮은 데이터의 상대적 위치 번호를 구하시오.(MATCH, MIN 함수)

----> MIN() = ()의 범위에서 최소값을 구한다.

        MATCH() = (lookup_value,lookup_array,match_type)

                         lookup_value - 찾으려는 값

                         lookup_array - 검색할 셀 범위

                         match_type - 0이면 lookup_value와 같은 첫째값을 찾는다. 

MATCH함수는 범위에서 찾을값과 같은 데이터를 찾아 그 위치를 번호로 표시해준다.

                     

찾으려는 값은 범위에서의 최소값이므로

다음과 같이 입력해주면 된다. 

 

 

 

 

 

 

* 실거래가가 최고인 물건명 구하기(index, match함수)

----> INDEX함수 - (array, row_num, column_num)

                            범위, 행번호, 열번호

                            = 범위에서 행번호와 열번호에 위치한 데이터를 표시한다.

 

=INDEX( B5:B13 ,   MATCH(MAX(G5:G13) ,  G5:G13 , 0 )    ,1 )

즉, 물건명 목록에서, 실거래가 목록에서 최대값의 실거래값의 위치(몇번째인지), array의 첫번째 줄에서의 이름)

MATCH함수가 원하는 값의 위치를 행번호로 표시해주므로 INDEX함수에서 활용가능한것!