들어가기 전에
많은 사람들은 엑셀 작업 속도를 높이려면 수백 개의 수식과 함수를 암기해야 한다고 생각합니다. 반드시 그렇지는 않습니다. 3가지 함수만 알면 예전에는 복잡하게 얽혀 있던 수식 더미를 대체할 수 있습니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: I Stopped Wasting Time in Excel When I Learned These 3 Functions
- URL: https://www.makeuseof.com/excel-functions-time-saver/
1. XLOOKUP
VLOOKUP을 사용할 때 열 인덱스를 계산하기 위해 열 개수를 세던 때를 기억하시나요? 아니면 주요 열 왼쪽에 있는 데이터를 찾으려고 하다가 시트 전체를 다시 정렬해야 했던 때를 기억하시나요? 이제 그런 골치 아픈 일은 없어졌습니다.
XLOOKUP은 VLOOKUP의 모든 문제점을 해결하는 Excel의 최신 솔루션입니다 . 더 간단하고 유연하며 Excel 365와 Excel 2021 이상에서 사용할 수 있습니다. 열 번호나 방향 제한 때문에 고민할 필요 없이, 무엇을 찾을지, 어디에서 찾을지, 그리고 무엇을 반환할지, 이 세 가지만 지정하면 됩니다.
기본 구문은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array)
=XLOOKUP(O2, I2:I200, L2:L200)
값을 검색하고 일치하는 결과를 반환하는 데는 충분합니다. 다른 값을 검색할 때는 수식에서 lookup_value(예: O2셀)로 지정한 셀을 변경할 필요가 없습니다. 셀의 숫자(예: 3604)만 변경하면 결과가 즉시 업데이트됩니다. Excel에서 일치하는 값을 찾지 못하면 기본적으로 #N/A로 표시됩니다.
하지만 XLOOKUP에는 훨씬 더 유용하게 만들어 주는 추가 기능이 있습니다. 예를 들어 보겠습니다.
=XLOOKUP(O2, I2:I200, L2:L200, ”Figure not found”)
=XLOOKUP(O2, I2:I200, L2:L200, 0, 1, -1)
첫 번째 수식은 일치하는 항목이 없을 때 오류 대신 친절한 메시지를 표시합니다. 반면 두 번째 수식은 일치하는 항목이 없으면 0을 반환하고, 정확히 일치하지 않으면 다음으로 큰 값을 찾고, 위에서 아래로가 아닌 아래에서 위로 검색하도록 Excel에 지시합니다. 마지막 설정(search_mode)은 검색하는 값이 여러 번 나타나고 마지막 값만 원하는 경우에 적합합니다.
기본적으로 전체 XLOOKUP 구문은 다음과 같습니다.
=XLOOKUP(조회값, 조회배열, 반환배열, "찾을 수 없는 경우", 일치 모드, 검색 모드)
더욱 놀라운 점은 XLOOKUP이 여러 결과를 한 번에 반환할 수 있다는 것입니다. 예를 들어, 수익만 가져오는 대신 수익, 비용, 이익을 모두 한 번에 가져올 수 있습니다.
=XLOOKUP(O2, I2:I200, L2:N200)
직접 추가할 수도 있습니다.
=SUM(XLOOKUP(O2, I2:I200, L2:N200))
열 개수 계산, 절대 참조 조작, 조회 방향 제한이 없습니다. 팀원의 ID를 기준으로 이름과 부서를 조회한다고 가정해 보겠습니다.
=XLOOKUP(A2, TeamMemberID_column, Name:Department_columns)
정확히 일치하는 항목이 없는 경우, match_mode를 1 또는 -1로 설정하면 Excel에서 다음으로 큰 숫자나 다음으로 작은 숫자를 찾습니다. 또한, search_mode를 1 또는 -1로 설정하여 Excel에서 검색을 시작할지, 아니면 아래에서부터 시작할지 지정할 수 있습니다.
2. SUMIFS/COUNTIFS
빠른 합계나 개수 계산이 필요할 때마다 여전히 데이터를 필터링하고 있다면, 너무 많은 작업을 하고 있는 것입니다. SUMIFS와 COUNTIFS는 특히 판매 보고서, 예산 또는 조건부 계산이 필요한 모든 데이터 세트에서 숨겨진 영웅입니다.
상사가 "온라인으로 주문한 150달러 이상 제품의 아시아 지역 총 매출은 얼마였습니까?"라고 묻는다고 가정해 보겠습니다. 필터 세 개를 만들고 시트를 망치지 않기를 바라는 대신, 한 줄로 답할 수 있습니다.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)
=SUMIFS(Sales_column, Region_column, "Asia",Price_column, ">150", SalesChannel_column, "Online")
실제 데이터 세트에서는 다음과 같이 할 수 있습니다.
=SUMIFS(L2:L200, A2:A200, "Asia", J2:J200, ">150", D2:D200, "Online")
이 수식은 열 A가 "아시아"이고, 열 J가 150보다 크고, 열 D가 "온라인"인 경우 열 L의 모든 값을 합산합니다.
COUNTIFS는 동일한 방식으로 작동하지만, 조건을 충족하는 행의 개수를 반환합니다.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
=COUNTIFS(A2:A200, "Asia", J2:J200, ">150", D2:D200, "Online")
SUMIFS처럼 금액 대신 온라인으로 주문되어 단가가 150달러가 넘는 아시아 지역의 주문 수를 구합니다.
SUMIFS와 COUNTIFS도 와일드카드를 처리합니다. 예를 들어, "T"로 시작하는 모든 국가와 과일을 제외한 모든 품목의 개수를 셀 수 있습니다.
=COUNTIFS(B2:B200, "=A*", C2:C200, "Fruits")
와일드카드는 편리하지만, 데이터가 깨끗해야 합니다. 데이터세트에 대문자나 숨겨진 문자가 일관성이 없으면 결과가 잘못 표시될 수 있습니다. 지저분한 Excel 시트를 빠르게 정리하면 나중에 골치 아픈 일을 예방할 수 있습니다.
SUMIFS와 COUNTIFS의 장점은 비파괴적이라는 점입니다. 원본 데이터셋을 슬라이싱하거나 손상시킬 필요가 전혀 없습니다. 더 복잡한 작업이 필요한 경우, Excel은 최대 127개의 조건 쌍을 지원합니다.
따라서 "지난 30일 동안 500달러 이상을 지출한 고객이 몇 명이나 되었는가?" 또는 "북미에서 50달러 미만의 액세서리 온라인 주문 수익은 얼마인가?"와 같은 질문을 확인하든, 이 두 가지 기능을 사용하면 즉시 답을 얻을 수 있습니다.
3. FILTER
FILTER 함수는 Excel이 수년 만에 추가한 기능 중 가장 만족스러운 함수일지도 모릅니다. 데이터를 필터링할 때 메뉴를 일일이 클릭하고 조건을 설정하고, 실수로 잘못된 행을 숨기지 않기를 바라야 했던 시절을 기억하시나요? FILTER 함수를 사용하면 이 모든 작업이 하나의 수식으로 간소화되고 기본 구문도 간단합니다.
=FILTER(필터링할 범위, 필터링 기준)
단가가 600달러가 넘는 판매에서 발생한 수익을 보고 싶다고 가정해 보겠습니다.
=FILTER(Revenue_column, UnitsSold_column>600)
실제 데이터 세트에서는 다음과 같이 할 수 있습니다.
=FILTER(L2:L200, J2:J200>=600)
기준에 맞는 숫자만 즉시 표시됩니다. 조건에 맞는 숫자가 없는 경우, 오류 메시지 대신 대체 메시지를 추가할 수도 있습니다.
=FILTER(L2:L200, J2:J200>=1000, "No Match")
SUMIFS처럼 조건이 하나만 있는 것은 아닙니다. OR 논리(최소 하나의 조건이 참이어야 함) 또는 AND 논리(모든 조건이 참이어야 함)를 사용할 수 있습니다. 다음은 2가지 간단한 예입니다.
=FILTER(L2:L200, (J2:J200>=600) + (D2:D200="Online"), "No Match")
=FILTER(L2:L200, (J2:J200>=600) * (D2:D200="Online"), "No Match")
첫 번째 수식은 가격이 600달러 이상이거나 온라인에서 판매되는 값을 반환합니다. 두 번째 수식은 가격이 600달러 이상이고 온라인에서 판매되는 값만 반환합니다.
FILTER는 SORT 함수와 함께 사용하면 더욱 효과적입니다. 셀 O2에 "Meat"가 포함되어 있고 일치하는 모든 행을 국가별로 정렬하려는 경우를 가정해 보겠습니다.
=SORT(FILTER(A2:N200, C2:C200=O2,""), 2, 1)
이렇게 하면 모든 육류 구매 판매 기록이 지정된 열(저는 2번째 열, 즉 국가 열을 선택했습니다)을 기준으로 오름차순으로 정렬됩니다. 수식 맨 끝의 1을 -1로 변경하면 결과가 내림차순으로 표시됩니다.
FILTER는 실시간 범위를 반환합니다. 데이터가 업데이트되면 결과도 자동으로 업데이트됩니다. 범위를 공유하거나 다른 수식의 원본으로 사용할 수도 있습니다. FILTER가 없는 버전의 파일을 다른 사람에게 보내도 걱정하지 마세요. 결과는 계속 표시됩니다(실시간 수식은 표시되지 않습니다).
마치며
XLOOKUP, SUMIFS/COUNTIFS, 그리고 FILTER를 함께 사용하면 Excel 사용자들이 일반적으로 힘들게 수행하는 반복적이고 클릭이 많은 작업의 많은 부분을 줄일 수 있습니다. 이것 말고도 강력한 함수들이 많이 있으므로 다양하게 시도해 보시기 바랍니다.
'Excel' 카테고리의 다른 글
| 이 6가지 함수를 쓰지 않는다면 Excel을 잘못 사용하고 있는 것 (1) | 2025.12.15 |
|---|---|
| 야근 없는 삶? 엑셀 x AI 자동화로 업무 속도 확 끌어올리는 비법! (0) | 2025.12.14 |
| 꼭 알아야 할 4가지 Excel 함수 조합 (0) | 2025.12.10 |
| INDEX 및 XMATCH를 사용하여 Excel에서 양방향 조회 하는 방법 (0) | 2025.12.09 |
| 12개 기능 키 단축키로 스프레드시트 작업 빠르게 처리하기 (0) | 2025.12.08 |
