엑셀 함수를 이용하여 같은 값 찾기
이번 포스팅은 엑셀에서 자주 사용하는 같은 값, 동일 값을 찾을때 필요한 다양한 함수(COUNTIF, COUNTIFS, VLOOKUP, INDEX와 MATCH, UNIQUE, SORT, FILTER 함수)를 사용하는 방법에 대해 작성하였습니다.
COUNTIF 함수 활용
COUNTIF 함수는 특정 조건에 맞는 셀의 개수를 세는 데 사용됩니다. 예를 들어, 특정 단어나 숫자가 포함된 셀의 개수를 셀 때 유용합니다.
= COUNTIF(범위, 조건)
<실습 예시>
"사과"의 개수를 세고 싶을 때
=COUNTIF(A2:A8, "사과")
A2부터 A8까지의 범위에서 "사과"라는 텍스트가 들어간 셀의 개수를 세어 결과를 반환합니다.
VLOOKUP 함수 활용
VLOOKUP 함수는 표에서 특정 행을 찾아 해당 행의 다른 열에 있는 데이터를 가져옵니다. 이 함수는 수직 검색(Vertical Lookup)의 약자로, 열을 따라 아래로 이동하면서 값을 찾습니다.
= VLOOKUP(찾을_값, 테이블_배열, 열_인덱스_번호, [범위_검색])
<실습 예시>
"사과"와 일치하는 첫 번째 행을 찾고, 그 행의 B열에 있는 수량을 반환하고 싶을 때
=VLOOKUP("사과", A2:B8, 2, FALSE)
위 수식은 A열에서 "사과"를 찾은 후, 해당 행의 B열에 있는 값을 반환합니다. 'FALSE'는 정확한 일치를 의미합니다.
MATCH 함수 활용
MATCH 함수는 지정된 항목이 처음으로 나타나는 위치(인덱스)를 찾습니다. 이 함수는 대량의 데이터에서 특정 항목의 위치를 파악할 때 유용합니다.
= MATCH(찾을_값, 범위, [검색_유형])
<실습 예시>
"사과"가 처음으로 나타나는 위치를 찾고 싶을 때
=MATCH("사과", A2:A8, 0)
위 수식은 A2부터 A8까지의 범위에서 "사과"라는 텍스트가 처음으로 나타나는 위치(인덱스)를 반환합니다. '0'은 정확한 일치를 의미합니다.
COUNTIFS 함수 활용
COUNTIFS 함수는 여러 조건에 맞는 셀의 개수를 세는 데 사용됩니다. 이 함수는 COUNTIF 함수의 확장 버전으로 볼 수 있으며, 두 개 이상의 조건을 동시에 만족하는 셀의 개수를 셀 때 유용합니다.
= COUNTIFS(조건범위1, 조건1, [조건범위2, 조건2], ...)
<실습 예시>
"사과"이면서 수량이 "3"인 셀의 개수를 세고 싶을 때
=COUNTIFS(A2:A8, "사과", B2:B8, "3")
위 수식은 A열에서 "사과"라고 기록된 셀과 B열에서 "3"라고 기록된 셀이 일치하는 행의 개수를 반환합니다. 이 경우 결과값은 1이 됩니다.
INDEX와 MATCH 함수 조합
INDEX 함수는 특정 위치의 값을 반환하는 데 사용되며, MATCH 함수는 지정된 항목이 처음으로 나타나는 위치를 반환합니다. 이 두 함수를 조합하면 특정 조건을 만족하는 셀의 값을 가져올 수 있습니다.
INDEX(배열, 행_번호, [열_번호])
MATCH(찾을_값, 범위, [검색_유형])
<실습 예시>
"바나나"와 일치하는 첫 번째 행을 찾고, 그 행의 수량을 반환하고 싶을 때:
=INDEX(B2:B8, MATCH("바나나", A2:A8, 0))
이 수식은 A열에서 "바나나"를 찾은 후, 그 위치에 해당하는 B열의 값을 반환합니다. 이 경우 "바나나"는 두 번째 행에 있으므로, 결과값은 "5개"가 됩니다.
UNIQUE 함수 활용
UNIQUE 함수는 주어진 범위나 배열에서 중복 값을 제거하고 유니크한 값만을 반환하는 함수입니다. 이 함수는 데이터 내에서 중복되지 않는 항목들만을 추출할 때 유용하게 사용됩니다.
UNIQUE(배열, [by_col], [occurs_once])
<실습 예시>
A열에서 중복 없이 고유한 과일 이름 목록을 생성하고 싶을 때:
=UNIQUE(A2:A8)
이 수식은 A열에서 중복을 제거하고 고유한 값을 목록 형태로 반환합니다. 결과는 "사과", "바나나", "포도", "체리"가 됩니다.
SORT 함수 활용
SORT 함수는 배열이나 범위의 값들을 지정된 순서대로 정렬합니다. 이 함수는 데이터를 오름차순 또는 내림차순으로 정렬할 때 사용됩니다.
SORT(배열, [sort_index], [sort_order], [by_col])
<실습 예시>
B열 기준으로 오름차순 정렬을 하고 싶을 때
=SORT(A2:B8, 2, TRUE)
B열(수량)을 기준으로 전체 행을 오름차순으로 정렬합니다. 결과적으로, 가장 적은 수량의 과일이 위로 오게 됩니다.
FILTER 함수 활용
FILTER 함수는 주어진 배열에서 특정 조건을 만족하는 항목들만을 필터링하여 반환합니다. 이 함수는 조건에 부합하는 데이터만을 추출할 때 매우 유용합니다.
FILTER(배열, 포함_조건, [if_empty])
<실습 예시>
수량이 5개 이상인 과일만을 필터링하고 싶을 때
=FILTER(A2:B8, B2:B8>=5)
이 수식은 B열(수량)이 5개 이상인 행만을 반환합니다. 결과적으로, "바나나", "포도", "체리"가 필터링됩니다.
