2026년 4월 30일 오전 10:30 EDT 게시
필자는 기계공학 엔지니어 출신으로, MUO에서 Windows, 생산성, 보안, 인터넷 관련 기술 아티클을 집필하고 있습니다. 자율 시스템에 대한 관심 덕분에 하드웨어와 소프트웨어를 끊임없이 만져보는 습관이 있습니다.
기술 글쓰기는 공학도 3학년 때 시작했으며, Android Police를 거쳐 MUO에 합류하게 되었습니다. Windows 문제 해결부터 생산성 도구 탐색, 보안 위험 요소를 쉬운 언어로 설명하는 일까지, 기술을 누구나 접근하기 쉽게 만드는 데 집중하고 있습니다. 독자들이 겪는 문제를 직접 겪어보고 실제 도구를 사용해보는 것이 가장 좋은 방법이라고 믿습니다.
글쓰기나 엔지니어링 외의 시간에는 Impractical Jokers를 보며 이미 수십 번 본 장난 영상에도 진심으로 웃고 있는 필자를 발견할 수 있을 것입니다.
동적 배열 함수, 왜 지금 배워야 할까
주변 엑셀 사용자들은 대부분 저와 같은 방식으로 수식을 익혔습니다. 함수를 하나씩 배우고, 이미 알던 내용 위에 차곡차곡 쌓아 올리는 방식이었죠. 동적 배열 함수는 그런 기존 실력을 대체하는 것이 아니라, 불필요한 우회 작업들을 없애주는 역할을 합니다.
필자는 TAKE와 DROP 함수로 자동 갱신되는 TOP 5 목록을 운영해 왔는데, 아래 소개할 네 가지 함수에서도 똑같은 변화가 일어났습니다. 하나하나가 여러 단계에 걸쳐 반사적으로 수행하던 작업을 수식 한 줄로 압축해 주었습니다.
FILTER — 도우미 열과 배열 수식의 시대를 끝낸 함수
여러 함수가 나눠서 하던 일을 이제 한 줄로 처리
예전 엑셀에서 조건에 맞는 행을 추출하려면 INDEX, MATCH, SMALL, IFERROR를 중첩한 수식을 Ctrl + Shift + Enter로 입력해야 했습니다. 물론 작동은 했지만, 나중에 유지보수하기가 골칫거리였습니다. 또 다른 방법은 자동 필터(AutoFilter)를 적용한 뒤 보이는 행만 복사해서 다른 곳에 값으로 붙여넣는 것이었습니다. 이것도 원본 데이터가 바뀌기 전까지만 유효한 임시방편이었죠.
FILTER 함수는 이 모든 과정을 한 줄로 처리합니다. 지역, 제품 카테고리, 영업 담당자 정보가 담긴 32행짜리 매출 스프레드시트에서 '서부(West) 지역의 Electronics 판매 내역 전체'를 뽑아내는 수식은 다음과 같습니다.
=FILTER(A2:G33, (B2:B33="West")*(C2:C33="Electronics"))
첫 번째 인수는 반환할 범위입니다. 두 번째 인수는 조건인데, 두 조건 사이의 곱셈(*)이 AND 연산 역할을 합니다. 즉, 두 조건이 모두 참이어야 결과에 포함됩니다. 곱하기 기호를 더하기(+)로 바꾸면 OR 조건이 되고, 결과는 자동으로 인접 셀에 흘러 넘칩니다(spill). 원본에 새 행을 추가하면 Enter만 누르는 순간 결과 범위도 함께 갱신됩니다.
조건에 맞는 결과가 없을 때는 IFERROR로 감싸서 처리할 수 있습니다. =FILTER(range, condition, "No matches")처럼 세 번째 인수를 넣으면, 해당되는 행이 없을 때 #CALC! 오류 대신 지정한 메시지가 표시됩니다.
UNIQUE — 3단계 중복 제거 작업을 단 한 셀로
'중복된 항목 제거'는 편하지만, 스스로 갱신되진 않는다
데이터 탭의 '중복된 항목 제거(Remove Duplicates)' 기능은 일회성 정리에는 충분히 좋습니다. 문제는 결과물이 고정된 정적 목록이라는 점입니다. 열을 새 위치로 복사하고, 대화 상자를 실행하고, 결과를 정렬한 후에 누군가 새 행을 추가하면 그 모든 과정을 처음부터 다시 반복해야 합니다. 필자도 그런 작업을 몇 번이나 반복했는지 세는 것이 지저분할 정도였습니다.
UNIQUE는 그 모든 단계를 건너뜁니다. 매출 스프레드시트의 영업 담당자 열을 지정하는 방법은 다음과 같습니다.
=UNIQUE(D2:D33)
결과는 John Smith, Sarah Johnson, Mike Wilson, Lisa Brown, David Chen, Emma Davis, Tom Rodriguez, Amy Foster 여덟 명이 흘러 넘쳐 출력됩니다. 대화 상자 없이 이름 여덟 개가 완성된 셈이죠. =SORT(UNIQUE(D2:D33))처럼 감싸면 같은 목록이 알파벳 순으로 정렬되어 반환됩니다.
핵심은 출력이 원본 데이터와 계속 연결되어 있다는 점입니다. 데이터에 새 이름이 추가되면 넘쳐흐른 목록도 자동으로 확장됩니다. 이 구조는 데이터 유효성 검사 드롭다운 목록의 깔끔한 소스로도 활용할 수 있습니다. D35#처럼 해시(#) 기호로 넘침 범위를 참조하면, 원본에 새 이름이 등장할 때마다 드롭다운 목록이 스스로 늘어납니다.
SORTBY — 복사, 붙여넣기, 정렬의 무한 반복을 끝내다
원본 데이터는 그대로 둔 채 보기 화면만 정렬하기
엑셀에서 정렬은 늘 약간의 리스크를 동반했습니다. 원본을 재배열하면 고정된 행을 참조하던 수식들이 깨질 수 있었기에, 필자의 기본 대응 방법은 데이터를 안전한 곳에 통째로 복사한 뒤 사본만 정렬하는 것이었습니다. SORT 함수도 도움이 되긴 했지만, 정렬 기준 열까지 모두 화면에 표시해야 하는 경우에만 유용했습니다.
SORTBY는 이 제약을 없애줍니다. 한 범위를 다른 범위의 값을 기준으로 정렬하며, 기준이 되는 두 번째 범위는 출력에 나타나지 않아도 됩니다. 매출액을 기준으로 영업 담당자 순위를 매기기 위해 필자가 사용한 수식은 다음과 같습니다.
=SORTBY(D2:D33, G2:G33, -1)
첫 번째 인수는 반환할 대상(영업 담당자), 두 번째 인수는 정렬 기준(매출액), -1은 내림차순을 의미합니다. 이름이 최고 매출액부터 최저 매출액 순으로 정렬되어 반환되며, 매출액 열은 원하지 않는 한 출력에 전혀 등장하지 않습니다.
FILTER와의 궁합도 훌륭합니다. FILTER(D2:G33, B2:B33="North")를 SORTBY 안에 감싸면, 북부(North) 지역 기록만 추출하면서 동시에 매출액 높은 순으로 정렬까지 한 번에 처리됩니다. 원본 데이터는 손대지 않은 채로요.
SORTBY는 다단계 정렬도 지원합니다. 범위/순서 쌍을 추가하면 먼저 지역별로 정렬한 다음, 각 지역 내에서 매출액 순으로 정렬하는 식의 복합 정렬이 가능합니다.
SEQUENCE — 채우기 핸들과 ROW 트릭을 대체하다
셀 하나도 드래그하지 않고 연속된 값 생성하기
번호 시리즈를 만들려면 예전에는 채우기 핸들을 끝없이 드래그하거나 =ROW(A1)을 입력한 뒤 아래로 복사해야 했습니다. 둘 다 작동은 하지만, 기반이 되는 데이터 크기가 바뀌면 자동으로 맞춰지지 않는다는 한계가 있었습니다.
SEQUENCE는 이 작업을 단 한 셀에서 처리하며, 인수에 따라 행, 열, 혹은 전체 그리드를 생성합니다. 구문은 다음과 같습니다.
=SEQUENCE(rows, [columns], [start], [step])
매출 데이터의 고유 영업 담당자 여덟 명에게 1001번부터 번호를 매기려면 다음 수식을 사용했습니다.
=SEQUENCE(8, 1, 1001, 1)
이렇게 하면 1001부터 1008까지가 한 열에 출력됩니다. 열 수를 1로, 증가값을 1로 설정하면 연속된 정수의 세로 목록이 됩니다.
SEQUENCE의 진가는 다른 함수 안에 들어갈 때 드러납니다. 2026년 1월의 31일 날짜 범위를 만드는 데 필요한 수식은 딱 하나입니다.
=DATE(2026, 1, SEQUENCE(31))
생성된 넘침 배열을 TEXT, INDEX 등 범위를 받는 어떤 함수에든 넘기면, 예전에는 도우미 열이 필요했던 작업이 사라집니다. 필자가 SEQUENCE로 날짜 열을 채우는 방법을 다룬 적이 있는데, 지금도 달력이나 일정표를 만들 때마다 시간을 크게 아껴주고 있습니다.
다음으로 파볼 함수들
기존 방법들이 망가진 것은 아닙니다. 다만 더 오래 걸리고, 파일을 유지보수하기 어렵게 만들 뿐입니다. 나중에 워크북을 열어볼 사람에게도 마찬가지고요. 필자에게 이제 동적 배열 방식이 기본 선택지가 되었습니다. 새 스프레드시트는 처음부터 동적 배열 사고방식으로 설계하고, 낡은 도구 상자는 남의 파일을 수정할 때만 꺼내면 됩니다.
다음 학습 목록에는 문자열을 분해하는 TEXTSPLIT, 범위를 결합하는 VSTACK과 HSTACK, 그리고 피벗 테이블 자체를 건너뛰게 해주는 PIVOTBY와 GROUPBY가 있습니다. 업데이트가 거듭될수록 또 하나의 옛 습관이 은퇴하는데, 필자는 그걸 환영합니다.