우리는 엑셀을 사용하면서 엑셀함수를 종종 사용하게 됩니다. 그 중에서도, 특히 업무롤 하면서, countif( ), sumif( ), vlookup( ) 함수 그리고 index( match( ) ) 함수조합 등을 사용해야하는 경우가 많이 있습니다.
이번글에서는, countif( ), sumif( ), vlookup( ) 함수, 그리고 countifs( ), sumifs( ), hlookup, 그리고 index( match( ) ) 함수 조합, 배열함수를 사용한 다중조건의 index( match( ) ) 함수조합 을 사용하는 방법에 대하여 말씀드리겠습니다.
countif( ) 함수는, 어떤 벡터(예: 열 또는 행) (주로 열 임)에서, 성분값(벡터성분의 값)이 특정값과 같은 경우를 모든 성분에 대해 세어서 총수를 반환하는 함수입니다. 사용법은 다음과 같습니다.
countif( 대상 벡터, 특정값 )
또는
countif( 성분이 특정값과 일치하는지 알아보게 되는 벡터, 특정값 )
sumif( ) 함수는, 특정값과 같은 어떤 벡터의 성분들과 같은 위치(벡터에서의 위치)에 있는, 다른 벡터의 성분값을 모두 더하는 함수입니다. 보통 직사각형 영역의 열들을 대상으로 하고, 같은 위치는 같은 행이 됩니다. 실제 사용에서의 의미는, 행 형태의 레코드를 사용할 때, 어떤 항목이 특정값과 같은 레코드의, 다른 특정항목을 모두 더하는 것입니다.
sumif( 특정값과 성분을 비교하는 벡터, 특정값, 성분을 더하는 벡터)
또는
sumif( 성분이 특정값과 일치하는지 알아보게 되는 벡터, 특정값, 특정값과 일치하는 앞벡터 성분과 같은 위치의 성분
을 더하게 되는 벡터 )
countif( )와 sumif( )의 작성예는 다음과 같습니다.
countif( )의 작성예 :
단계 1 : '=countif(' 를 적음
단계 2 : 찾을값과 일치하는 성분을 찾을 열 영역을 선택 [ --> '=countif(' 뒤에 기입됨 ]
단계 3 : ',' 를 적고 찾을값을 기입
단계 4 : 엔터를 누름 [ --> ')' 가 기입되면서 입력이 완료됨. countif( )함수값이 보임. ]
sumif( )의 작성예 :
단계 1 : '=sumif(' 를 적음
단계 2 : 찾을값과 일치하는 성분을 찾을 열 영역을 선택 [ --> '=sumif(' 뒤에 기입됨 ]
단계 3 : ',' 를 적고 찾을값을 기입
단계 4 : 찾을 값과 일치하는 위 열의 성분과 같은 위치의 성분을 더하게 되는 열 영역을 선택
[ --> '=sumif(...,찾을값,' 뒤에 기입됨 ]
.
단계 5 : 엔터를 누름 [ --> ')' 가 기입되면서 입력이 완료 됨. sumif( )함수값이 보임. ]
이 때, 찾을 값은 문자열 형태의 대소관계(예: ">2")일 수도 있습니다.
vlookup( )함수는 특정 직사각형 영역에서, 첫째 열에서 특정값을 찾아, 해당성분과 같은 행의 다른 열의 성분값을 반환하는 함수입니다. 여기서 v는 vertical의 v(에서 유래한 것)입니다. 실제 사용에서의 의미는, 행 형태의 레코드를 사용할 때, 레코드의 제1열 항목이 특정값과 같은 것을 찾아, 이 레코드의 다른 특정 항목의 값을 반환하는 것입니다. 사용법은 다음과 같습니다.
vlookup( 특정값, 직사각형 영역, 반환할 셀이 있은 열의 직사각형 영역에서의 위치(즉, 몇번째 열), Range_lookup )
여기서,
Range
_lookup : 특정값과 일치하는지 찾을 때, 허용되는 일치값과의 차이정도
(0 또는 FALSE : 완전히 일치, 1 또는 TRUE : 어느정도 유사 임)
찾는 값이 여러 개인 경우, 제일 처음 값만 찾아, 작업을 수행합니다. 또한, vlookup( )은 sumif( ) 등과 달리, 찾을 값이 값이어야만 하며, 문자열 형태의 대소관계(예: ">2")는 사용할 수 없습니다. 이러한 vlookup( )함수는, 그 유용성 측면에서, 엑셀함수의 꽃이라고(도) 합니다. vlookup( ) 함수의 사용예는 다음과 같습니다.
countifs( ) 함수는, 다수의 벡터들에 대해, 한 같은 위치의, 각각의 성분들이 각각의 특정값들과 일치하는 경우를, 모든 위치에 대해 세어서 총수를 반환하는 합수입니다. 즉, countif( )에서, 단일조건이 다중조건으로 바뀐 것입니다. 사용법은 다음과 같습니다.
countifs(특정값1과 성분을 비교하는 벡터, 특정값1, 특정값2와 성분을 비교하는 벡터 특정값2, ...)
또는
countifs( 성분이 특정값1과 일치하는지 알아보게 되는 벡터, 특정값1, 앞의 특정값과 일치하는 앞벡터 성분과 같은
위치의 성분이 특정값2와 일치하는지 알아보게 되는 벡터, 특정값2, ... )
비슷하게, sumifs( ) 함수는, 다수의 벡터들에서, 같은 위치의, 각각의 성분들이 각각의 특정값들과 일치하는 경우가 있을 때, 이와 같은 위치에 있는, 다른 어떤 벡터의 성분을 모두 더하는 함수입니다. 즉, sumif( )에서, 단일조건이 다중조건으로 바뀐 것입니다.
sumifs(성분을 더하는 벡터, 특정값1과 성분을 비교하는 벡터, 특정값1, 특정값2와 성분을 비교하는 벡터, 특정값2, ...)
또는
sumifs( 특정값과 일치하는 다른 벡터의 성분들과 같은 위치의 성분을 더하게 되는 벡터, 성분이 특정값1과 일치하는지 알아보게 되는 벡터, 특정값1, 앞의 특정값과 일치하는 앞벡터 성분과 같은 위치의 성분이 특정값2와 일치하는지 알아보게 되는 벡터, 특정값2, ...)
여기서, sumif( )는 함수의 인자들의 순서가, '성분을 합산할 벡터' 다음에, '특정값과 비교할 벡터'이고, 반면에 sumifs( )는, '특정값과 비교할 벡터들' 다음에, '성분을 합산할 벡터' 인 것으로, 순서가 서로 반대라는 점은 특기할 만합니다.
또한, 다른 함수에 대해서도 함수이름if( ), 함수이름ifs( )가 가능하며 [ 예: averge( ) 에 대한 averageif( ), averageifs( ) ], 서로간의 관계는, sum( ), sumif( ), sumifs( ) 간의 관계와 같습니다.
.
hlookup( ) 함수는 vlookup( )에서 열이 행으로 바뀐 것이며, h는 horizontal의 h입니다. 즉, hlookup( )함수는 특정 직사각형 영역에서, 첫째 행에서 특정값을 찾아, 해당성분과 같은 열의 다른 행의 성분값을 반환하는 함수입니다. 실제 사용에서의 의미는, 열 형태의 레코드를 사용할 때, 레코드의 제1행 항목이 특정값과 같은 것을 찾아, 이 레코드의 다른 특정항목의 값을 반환하는 것입니다. 사용법은 다음과 같습니다.
hlookup( 특정값, 직사각형 영역, 반환할 셀이 있은 행의 직사각형 영역에서의 위치(즉, 몇번째 행), Range_lookup )
여기서, Range_lookup : 특정값과 일치하는지 찾을 때, 허용되는 일치값과의 차이정도
(0 또는 FALSE : 완전히 일치, 1 또는 TRUE : 어느정도 유사 임)
엑셀에서 하나의, 레코드 즉 자료는 주로 행으로 작성되고, 이에 따라 항목은 주로열로 만들어지기 때문에, hlookup( )은 거의 사용되지 않고 대부분 vlookup( )이 사용됩니다.
countifs( ), sumifs( ), hlookup( )의 사용예는 다음과 같습니다
마지막으로, index( match( ) ) 함수조합에 대해 말씀드리겠습니다.
먼저, match( ) 함수는 어떤 벡터에서 특정값을 찾아, 해당 성분의 위치를 반환합니다. 찾는 값이 여러 개인 경우 제일 처음 값만 찾아, 해당 결과값을 반환합니다. 사용법은 다음과 같습니다.
match( 대상벡터, 특정값, Range_lookup )
여기서, Range_lookup : 특정값과 일치하는지 찾을 때, 허용되는 일치값과의 차이정도
(0 또는 FALSE : 완전히 일치, 1 또는 TRUE : 어느정도 유사 임)
index( ) 함수는, 어떤 벡터에서 특정위치의 성분의 값을 반환합니다. 사용법은 다음과 같습니다.
index( 대상벡터, 성분위치 )
이러한 index( )함수와 match( )함수는, 대부분, 각자 사용되지 않고, 두 함수의 조합 즉, index( match( ) )형태로 사용됩니다.
index( match( ) ) 함수조합은 어떤 벡터에서 특정값과 일치하는 성분을 찾아, 그 성분과 같은 위치의, 다른 특정벡터의 성분값을 반환합니다. vlookup( )이나 hlookup( )과 비슷한 작용을 하며, vlookup( )이나 hlookup( )은, 성분값을 반환하는 벡터가, 각각 특정값을 찾는 벡터의 오른쪽이나 아래쪽에 위치하여야 하는 반면에, index( match( ) )는 위치의 제한이 없습니다. 사용법은 다음과 같습니다.
index( 성분의 값을 반환하는 벡터, match( 특정값, 특정값과 같은 성분을 찾게 되는 벡터, Range_lookup ) )
여기서, Range_lookup : 특정값과 일치하는지 찾을 때, 허용되는 일치값과의 차이정도
(0 또는 FALSE : 완전히 일치, 1 또는 TRUE : 어느정도 유사 임)
index( match( ) ) 함수조합의 사용예는 다음과 같습니다.
그밖에, countif( ), sumif( ), vlookup( ), hlookup( )함수, 그리고 index( match( ) )함수조합에서, 논리범위를, 특정값과 일치하는 성분을 찾게 된는 벡터로, 1을 찾을값으로 하면, 논리조건을 만족하는 성분에 대하여 작업을 수행하게 됩니다.
따라서, 다중논리 조건이나 대소관계 논리 조건도 가능하게 되면, 이 때 다중논리 조건은, 배열함수들의 연산을 사용하여 쉽게 작성할 수 있습니다.
이상으로, 엑셀에서 countif( ), sumif( ), vlookup( ) 함수, index( match( ) 함수조합 등을 사용하는 방법에 대하여 알아 보았습니다~
'꿀팁 모음 > 컴퓨터 생활' 카테고리의 다른 글
| 계산기 앱 추천 Big Button Calculator (0) | 2022.01.11 |
|---|---|
| 엑셀에서 배열수식, 논리식 등을 사용하는 방법 (1) | 2020.11.01 |
| 엑셀에서 필터를 사용하는 방법 (0) | 2020.04.03 |
| 엑셀에서 보이는 부분만 복사해 붙이는 방법 (0) | 2020.03.23 |
| 윈도우10에서 안전모드로 부팅하는 방법 (0) | 2020.02.27 |
댓글