데이터를 빠르게 찾아주는 6가지 엑셀 함수

1. FIND

Excel의 Find 함수는 더 큰 문자열 내에서 지정된 문자열의 숫자 위치를 반환합니다. FIND 함수의 구문은 다음과 같습니다.

여기서 '찾을 문자열'은 찾으려는 문자 또는 하위 문자열이고, '대상 문자열'은 검색하려는 더 큰 텍스트 문자열입니다. '시작 위치' 매개변수는 문자열에서 함수가 검색을 시작할 위치를 지정합니다. 대괄호 안에 있는 매개변수는 생략할 수 있습니다.

다음은 Find 함수의 실제 사용 예입니다.

위의 예에서 Find 함수는 "Excel is a powerful tool for data analysis" 문자열에서 "Excel"이 시작하는 위치를 반환합니다. 결과는 1이 됩니다.

Find 함수는 대소문자를 구분합니다. 대소문자를 구분하지 않고 검색하려면 Search 함수를 사용하세요. 사용 구문은 Find와 같습니다(함수 이름만 다릅니다).

2. FILTER

Filter 함수는 특정 조건을 만족하는 데이터만 필터링하는 데 사용됩니다. Filter 함수의 사용 구문은 다음과 같습니다.

여기서 '범위'는 필터링할 데이터가 포함된 범위입니다. '조건 범위'는 필터링할 조건이 포함된 범위입니다. 'if_empty'는 조건을 만족하는 데이터가 없을 때 반환할 값입니다. 대괄호 안에 있는 매개변수는 생략할 수 있습니다.

실제 사용 예제를 살펴보죠. 다음과 같은 데이터가 있습니다. 범위를 필터링하여 '영업' 부서 직원만 표시해 보겠습니다.

다음 수식을 사용하면 '영업' 부서 직원만 필터링합니다.

3. SORT

Sort 함수는 특정 문자열을 직접적으로 찾아주는 것은 아니지만 Excel 시트에서 특정 데이터를 쉽게 찾을 수 있도록 범위의 데이터를 오름차순 또는 내림차순으로 정렬해야 하는 경우에 도움이 될 수 있습니다. Sort 함수의 사용 구문은 다음과 같습니다.

여기서 '범위'는 정렬할 데이터가 포함된 범위입니다. '정렬 기준'은 정렬할 기준 열 또는 행 번호를 지정하며, 생략할 경우 기본값은 1입니다. '정렬 순서'는 데이터를 정렬할 순서를 지정합니다. 1은 오름차순, -1은 내림차순입니다(생략할 경우 기본값은 오름차순). '정렬 방향'은 정렬 방향은 기본값인 행(False)을 기준으로 정렬할지, 아니면 열(True)을 기준으로 정렬할지 지정합니다. 대괄호 안에 있는 매개변수는 생략할 수 있습니다.

실제 사용 예제를 살펴보죠. 다음과 같은 데이터가 있습니다.

두 번째 열('결과')을 기준으로 내림차순으로 범위를 정렬하려면 다음 수식을 사용합니다.

4. INDEX

Index 함수는 특정 행과 열에서 값을 반환합니다. 해당 셀이 위치한 행과 열을 지정하기만 하면 됩니다. Index 함수의 사용 구문은 다음과 같습니다.

여기서 '배열'은 데이터가 포함된 범위입니다. '행 번호'는 반환할 값이 있는 행 번호입니다. '열 번호'는 반환할 값이 있는 열 번호입니다. 대괄호 안에 있는 매개변수는 생략할 수 있습니다.

실제 사용 예제를 살펴보죠. 다음과 같은 데이터가 있습니다. 다음 수식을 사용하면 지정한 범위(A1:C11)에서 행 방향으로 4, 열 방향으로 3위치에 있는 결과인 C4 셀의 결과를 구해줍니다.

5. MATCH

Match 함수는 특정 값이나 조건을 만족하는 데이터가 있는 위치를 반환합니다. 그런 다음, 반환된 값을 Index 함수와 함께 사용하여 데이터를 동적으로 검색하고 조작(manipulate)할 수 있습니다. Match 함수의 사용 구문은 다음과 같습니다.

p>여기서 '찾을 값'은 찾으려는 값이고, '찾을 범위'는 검색할 범위입니다. '일치 유형'은 일치하는 값을 찾는 방법을 지정합니다. 0은 정확한 일치, 1은 가장 가까운 값, -1은 가장 가까운 값보다 작은 값, 1은 가장 가까운 값보다 큰 값입니다. 생략할 경우 기본값은 1입니다. 대괄호 안에 있는 매개변수는 생략할 수 있습니다.

실제 사용 예제를 살펴보죠. 다음 수식은 지정한 범위에서 '이채연'이 지정한 범위(A1:A11)에서 몇 번째에 있는지 위치를 찾아서 결과를 알려줍니다.

6. XLOOKUP

XLOOKUP 함수는 Excel 2019부터 도입된 강력한 함수입니다. XLOOKUP 함수를 사용하면 범위에서 검색할 값을 지정한 다음, 다른 범위에서 해당 값을 추출할 수 있습니다. Hlookup이나 Vlookup 함수와 달리 Xlookup 함수를 사용하면 모든 방향으로 검색할 수 있으므로 워크시트에서 데이터를 보다 유연하게 검색할 수 있습니다. XLOOKUP 함수의 사용 구문은 다음과 같습니다.

여기서 '찾을 값'은 찾으려는 값이고, '찾을 범위'는 검색할 범위입니다. '반환할 범위'는 반환할 범위입니다. '일치 유형'은 일치하는 값을 찾는 방법을 지정합니다. 0은 정확한 일치, 1은 가장 가까운 값, -1은 가장 가까운 값보다 작은 값, 1은 가장 가까운 값보다 큰 값입니다. 생략할 경우 기본값은 1입니다.

'검색 유형'은 검색 방법을 지정합니다. 1은 순차 검색, -1은 역순 검색, 2는 이진 검색입니다. 생략할 경우 기본값은 1입니다. '결과 옵션'은 결과를 제어하는 방법을 지정합니다. 0은 일치하는 값이 없을 때 #N/A를 반환, 1은 일치하는 값이 없을 때 가장 가까운 값 반환, 2는 일치하는 값이 없을 때 가장 가까운 값보다 작은 값 반환, 3은 일치하는 값이 없을 때 가장 가까운 값보다 큰 값 반환입니다. 생략할 경우 기본값은 0입니다.

다양한 기능이 있으므로 복잡해 보입니다만, 함수가 작동하려면 3가지 매개변수만 지정하면 되므로 여기서는 3가지 매개변수만 눈여겨 보시면 되겠습니다.

다음과 같은 데이터가 있습니다. '품목' 중에서 '바나나'를 조회하고, B열에서 '등급' 정보를 가져오려고 합니다.

실제로 수식을 적용할 때에는 범위를 구체화하는 것이 좋습니다. 예를 들어 위의 수식에서 A:A, B:B라고 하기보다는 A1:A7, B1:B7과 같이 범위를 지정하는 것이 바람직합니다.

마치며

Find, Sort, Filter, Index, Match, Xlookup 등 6가지 Excel 함수를 숙지하면 대규모 데이터 집합에서 정보를 빠르게 찾고, 정리하고, 추출하는 데 도움이 됩니다. Excel 생산성과 효율적인 데이터 분석 능력이 크게 높일 수 있는 중요한 함수이므로 반드시 익혀두세요.