들어가기 전에
스프레드시트를 오래 사용하다 보면 유용한 함수들을 찾을 수 있습니다. 많은 사람들이 수동으로 처리하는 지루한 작업을 자동화하여 시간을 절약해 주는 4가지 Excel 함수를 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: The Excel functions I use most (and why they’re so useful
- URL: https://www.makeuseof.com/most-useful-excel-functions/
1. XLOOKUP
몇 년 전 XLOOKUP을 알게 된 후 VLOOKUP 사용을 중단했습니다. VLOOKUP은 오른쪽으로만 검색하고 열을 이동하면 중단되는 반면, XLOOKUP은 어느 방향으로든 검색이 가능하고 유연성이 뛰어납니다. XLOOKUP은 스프레드시트에서 특정 데이터를 찾는 데 걸리는 시간을 절약해 주는 Excel 함수 중 하나입니다.
컴퓨터 부품 가격 데이터에서 제품 모델별 특정 GPU 가격을 찾아야 합니다. VLOOKUP을 사용하면 전체 테이블을 재구성해야 합니다. 하지만 XLOOKUP을 사용하면 다음과 같이 작성하면 됩니다.
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP은 전체 제품 열을 검색하여 제 GPU를 찾은 후 해당 가격을 반환합니다. 가격 열의 위치와 관계없이 작동하며, 나중에 열을 더 추가해도 오류가 발생하지 않습니다. 저는 이 기능을 여러 시트에서 제품 정보를 상호 참조할 때 형식을 변경하지 않고도 자주 사용합니다.
XLOOKUP의 기본 구문은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array)
- lookup_value: 찾으려는 값
- lookup_array: 값을 찾을 위치
- return_array: 반환하려는 값이 있는 열 또는 행
위의 경우, 찾고자 하는 값은 "GIGABYTE GeForce RTX 3060 12GB Gaming OC"였습니다. C:C 열에서 해당 값을 찾고, 일치하는 값이 있는 행의 D:D 열에서 해당 값을 반환하고 싶었습니다.
XLOOKUP의 또 다른 장점은 수식 끝에 ", -1"을 추가하면 아래에서 위로 검색하여 가장 최근 가격 항목을 자동으로 찾아준다는 점입니다. 덕분에 스프레드시트를 업데이트할 때마다 데이터를 수동으로 정렬할 필요가 없어졌습니다.
2. SUMIFS, COUNTIFS
기본적인 SUM과 COUNT 함수는 간단한 작업에는 괜찮지만, 실제 분석이 필요할 때는 부족합니다. 여러 조건의 가격 데이터를 분석해야 할 때는 SUMIFS와 COUNTIFS 함수를 주로 사용합니다. 이 함수들을 사용하면 수백 개의 행을 쉽게 분석할 수 있습니다.
Amazon US에서 판매 중인 AMD 프로세서의 개수를 세고 싶다고 가정해 보겠습니다. 수동으로 필터링하는 대신 다음과 같이 작성합니다.
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
이렇게 하면 제 데이터세트에 Amazon에 등록된 AMD 프로세서가 14개 있다는 것을 바로 알 수 있습니다. 여기서 좋은 점은 필요한 만큼 기준을 추가할 수 있다는 것입니다.
가격 분석의 경우 SUMIFS도 같은 방식으로 작동합니다. 현재 재고가 있는 모든 인텔 프로세서의 총 가치를 계산하려면 다음을 사용합니다.
=SUMIFS(D:D, K:K, "Intel", G:G, "In Stock")
이는 브랜드가 "Intel"이고 재고 상태가 "In Stock"인 D열의 모든 가격을 합산합니다. SUMIFS의 구문은 다음과 같습니다.
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)
- sum_range: 더하려는 열
- criteria_range1: 조건을 검사할 첫 번째 열
- criteria1: 첫 번째 범위의 조건
- criteria_range2, criteria2: 추가 범위 및 조건(선택 사항)
COUNTIFS는 값을 합산하는 대신 일치하는 행을 계산한다는 점을 제외하면 동일하게 작동합니다.
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
빠른 보고서를 작성하려면 SUMIFS와 COUNTIFS를 선호합니다. 새 데이터로 즉시 업데이트되고, 기존 수식에 깔끔하게 맞으며, 별도의 피벗 테이블을 설정하지 않고도 모든 내용을 그대로 유지할 수 있기 때문입니다.
3. TRIM, CLEAN
공백이나 보이지 않는 문자가 잔뜩 들어간 지저분한 데이터만큼 스프레드시트를 빨리 망치는 건 없습니다. 모델 이름 끝에 공백이 붙어서 검색이 계속 실패하는 걸 보고 뼈저리게 깨달았습니다.
TRIM은 텍스트의 시작과 끝에 있는 불필요한 공백과 단어 사이의 공백을 제거합니다. 여러 소스에서 데이터를 가져올 때 제품 이름의 공백이 일치하지 않는 경우가 많습니다. 각 셀을 직접 정리하는 대신, 보조 열을 만들어 다음과 같이 사용합니다.
=TRIM(C2)
그런 다음 마우스를 셀 가장자리에 올려놓고 더하기(+) 아이콘으로 바뀐 후, TRIM 함수를 적용하려는 모든 행으로 드래그합니다.
CLEAN은 눈에 보이지 않는 인쇄 불가능한 문자를 제거하여 더욱 심층적인 기능을 제공합니다. 이러한 보이지 않는 문제는 종종 데이터 내보내기 중에 몰래 들어와 알 수 없는 수식 오류를 발생시킵니다. 저는 보통 두 함수를 함께 사용합니다.
=TRIM(CLEAN(C2))
구문은 간단합니다.
=TRIM(text) ' 불필요한 공백 제거
=CLEAN(text) ' 인쇄할 수 없는 문자 제거
이렇게 데이터를 정리한 후, XLOOKUP 함수는 항상 완벽하게 작동합니다. 통합 문서를 정리하고 준비하는 데 파워 쿼리를 자주 사용하지만, 정리 작업이 거의 필요하지 않은 간단한 스프레드시트에는 TRIM 함수와 CLEAN 함수가 효과적입니다. 이러한 도구를 사용하면 새 스프레드시트 프로젝트에서 데이터 정리를 표준 첫 단계로 수행하여 디버깅 시간을 크게 단축할 수 있습니다.
4. TEXTBEFORE와 TEXTAFTER
TEXTBEFORE와 TEXTAFTER는 지저분한 스프레드시트를 정리하는 데 유용합니다. Excel의 새로운 텍스트 함수는 지저분한 문자열에서 특정 정보를 추출하는 데 탁월합니다.
TEXTBEFORE는 특정 구분 기호 앞에 있는 모든 내용을 추출합니다.
=TEXTBEFORE(D2, " USD")
이렇게 하면 즉시 "178.33 USD"에서 "178.33"이 인출됩니다.
TEXTAFTER는 역순으로 작동하여 구분 기호 뒤의 모든 내용을 추출합니다.
=TEXTAFTER(C2, "AMD ")
이 수식을 사용하면 "AMD Ryzen 5 5700X 8-Core AM4 Processor"에서 "Ryzen 5 5700X 8-Core AM4 Processor"를 추출해 줍니다.
복잡한 추출의 경우 두 함수를 결합합니다. "$177.52 USD"에서 숫자 가격만 구하려면 다음과 같이 합니다.
=TEXTBEFORE(TEXTAFTER(D8, "$"), " USD")
TEXTBEFORE와 TEXTAFTER의 구문은 다음과 같습니다(사용법 동일).
=TEXTBEFORE(text, delimiter)
=TEXTAFTER(text, delimiter)
이 함수들을 혁신적으로 만드는 것은 바로 정밀성입니다. MID, FIND, LEN 함수를 복잡하게 조합하는 대신, 간단하고 읽기 쉬운 수식을 사용하여 깔끔한 추출 결과를 얻을 수 있습니다. 이 함수들을 꾸준히 사용하여 모델 번호를 분리하고, 제품 사양을 추출하고, 가져온 텍스트에서 정확한 데이터를 추출합니다. 그렇지 않으면 몇 시간 동안 수작업으로 편집해야 했을 데이터입니다.
마치며
대부분의 사람들은 이러한 작업을 수동으로 처리하며, 적절한 수식을 사용하면 몇 분 안에 끝나는 작업에 몇 시간을 허비합니다. 이 4가지 함수는 Excel에서 시간 낭비가 심한 몇 가지 문제를 해결합니다. 유연한 조회를 통한 데이터 검색, 여러 기준으로 분석, 복잡하게 가져온 텍스트 정리, 복잡한 문자열에서 특정 정보 추출 등이 있습니다.
'Excel' 카테고리의 다른 글
| 더 스마트하게 일하는 10가지 간단한 엑셀 활용법 (0) | 2025.12.22 |
|---|---|
| Excel에서 피벗 테이블 데이터 쉽게 검색하는 방법 (1) | 2025.12.19 |
| 이 6가지 함수를 쓰지 않는다면 Excel을 잘못 사용하고 있는 것 (1) | 2025.12.15 |
| 야근 없는 삶? 엑셀 x AI 자동화로 업무 속도 확 끌어올리는 비법! (0) | 2025.12.14 |
| 시간 낭비를 멈추게 해 주는 3가지 Excel 함수 (0) | 2025.12.11 |
