[스프레드시트] 컴활실기 풀어보자 (REPeaT, FREQUENCY, 배열수식, VLOOKUP, HLOOKUP)

 

 

아래문제 출처 ( https://m.cafe.daum.net/hermingway/ZYwR/38)

 

​​​​​엑셀 컴활 문제를 계속해서 풀어보자.

 

 

 

 

=======

 

VLOOKUP, HLOOKUP 이용해서 계산하기

 

 

 

※ VLOOKUP 함수

-> 단순하게 "값을 찾아주는 함수"

-> 아래 4가지 구분에 유의해서 기억하자.

a) 검색할 대상 표의 맨 좌측에서

b) 찾으려는 값을 검색한 뒤,

c) 동일한 행에 위치한

d) 다른 값을 출력 해준다.

​

1번째 인자 : lookup_value => 찾으려는 값

2번째 인자 : table_array => 검색할 대상 표

3번째 인자 : col_index_num => 출력해줄 "동일한 행에 위치한 몇번째 열" 다른 값

4번째 인자 : range_lookup (기본값 : TRUE 유사한 일치 , FALSE 완벽한 일치)

 

 

위의 성질을 이용해서 풀어보면

=$L4*(

1

-

VLOOKUP(

$G4, => 찾으려는 값

$I$32:$J$35, => 검색할 대상 표

2, => 출력해줄 "동일한 행에 위치한 몇번째 열" 다른 값

FALSE

)

)

으로 공단부담금을 나타낼 수 있다.

 

 

그런데, 월한도액과 비교해서 초과할 경우

월한도액을 기입해줘야한다.

=IF(

($L4*(1-VLOOKUP($G4, $I$32:$J$35, 2, FALSE))) > HLOOKUP($F4, $B$32:$G$33, 2, FALSE), → 월 한도액 초과하면

HLOOKUP($F4, $B$32:$G$33, 2, FALSE), → 월 한도액 리턴

($L4*(1-VLOOKUP($G4, $I$32:$J$35, 2, FALSE))) → 월 한도액 초과하지 않으면 해당 값 리턴

)

 

 

 

===========

 

IF, SUM 이용한 배열 수식

대표사진 삭제

사진 설명을 입력하세요.

 

=SUM(

IF(

조건1*조건2,

값,

FALSE

)

)

위와 같은 형태로 만들었다.

 

=SUM( → 리턴된 값을 모두 합한다.

IF(

($B38=($F$4:$F$29)) * (C$37 =($H$4:$H$29)),  → 수급자등급이 1번째 열의 값과 일치하고, 서비스명이 1번째 행과 일치

($I$4:$I$29), → 위의 조건이 모두 참이여야만 해당 행의 이용일수를 리턴

FALSE

)

)

 

 

 

 

 

=======

 

FREQUENCY를 이용해 빈도수를 구하고

REPeaT함수를 이용해 특수문자(▣)를 빈도수만큼 반복해서 표시

 

 

FREQUENCY는 2번째 인자가 되는 범위설정을 잘해야한다.

여기서는 [표6]의 2번째 열을 범위로 설정해야한다.

 

=FREQUENCY(

$E$4:$E29, → 전체 표본

$I$39:$I$42 → 범위

)

 

 

그리고 숫자가 나오면 REPT함수를 이용해서

나타낸다.

=REPT(

"▣", 

FREQUENCY($E$4:$E29,$I$39:$I$42) → 반복 횟수(=빈도수)

)