엑셀에서 데이터를 다루다 보면 값이 변경될 때마다 수식을 다시 입력하거나 범위를 조정해야 하는 번거로움을 경험하게 됩니다. 하지만 동적 배열 함수를 활용하면 이러한 수작업을 완전히 자동화할 수 있습니다. 동적 배열은 단일 셀에 입력한 수식이 자동으로 여러 셀로 확장되어 결과를 표시하는 혁신적인 기능입니다. 본 글에서는 FILTER, SORT, UNIQUE 등 핵심 함수를 활용한 자동화 방법부터 SEQUENCE와 XLOOKUP을 결합한 고급 데이터 처리 기법, 그리고 실무에서 바로 적용 가능한 대시보드 구축 사례까지 상세히 다룹니다. 데이터가 추가되거나 변경되어도 별도의 수정 없이 자동으로 업데이트되는 스마트한 업무 환경을 구축하고 싶다면, 동적 배열 함수의 원리와 활용법을 정확히 이해하는 것이 필수적입니다.
FILTER와 SORT 함수로 조건별 데이터 자동 추출 시스템 만들기
FILTER 함수는 방대한 데이터에서 특정 조건을 만족하는 행만 자동으로 추출해주는 강력한 도구입니다. 예를 들어 판매 데이터베이스에서 특정 지역의 거래만 표시하고 싶을 때, 기존에는 자동필터를 수동으로 적용하거나 복잡한 IF 배열 수식을 작성해야 했습니다. 하지만 FILTER 함수를 사용하면 단 한 줄의 수식으로 원하는 결과를 얻을 수 있습니다. 수식의 기본 구조는 FILTER(배열, 조건1, 조건2)이며, 여러 조건을 곱셈 기호로 연결하여 AND 조건을, 덧셈 기호로 연결하여 OR 조건을 구현할 수 있습니다. SORT 함수는 추출된 데이터를 원하는 기준으로 자동 정렬해줍니다. SORT(배열, 정렬기준열, 정렬방향) 형태로 사용하며, 1은 오름차순, -1은 내림차순을 의미합니다. 두 함수를 결합하면 더욱 강력한 효과를 발휘합니다. SORT(FILTER(데이터범위, 조건), 2, -1)처럼 중첩하여 사용하면 조건에 맞는 데이터를 추출한 후 특정 열 기준으로 자동 정렬됩니다. 원본 데이터가 추가되거나 수정되면 결과도 즉시 반영되므로 월별 보고서나 실시간 대시보드 구축에 매우 유용합니다. 실무에서는 드롭다운 목록과 연동하여 사용자가 선택한 조건에 따라 데이터가 동적으로 변경되도록 설계할 수 있습니다. 데이터 유효성 검사로 만든 드롭다운 셀을 조건 참조로 활용하면 클릭 한 번으로 보고 싶은 데이터를 즉시 확인할 수 있는 인터랙티브 시트가 완성됩니다.
UNIQUE와 SEQUENCE로 중복 제거 및 자동 번호 매기기 구현하기
데이터 분석 과정에서 중복값 제거는 필수적인 작업입니다. UNIQUE 함수는 지정한 범위에서 고유한 값만 추출하여 새로운 배열로 반환합니다. 단순히 UNIQUE(범위)만 입력해도 중복이 제거된 목록이 자동 생성되며, 두 번째 인수로 열 방향(FALSE) 또는 행 방향(TRUE) 중복 제거를 선택할 수 있습니다. 세 번째 인수는 정확히 한 번만 나타나는 값만 추출할지 결정하는 옵션입니다. 고객 목록에서 거래처명만 추출하거나, 판매 데이터에서 제품 카테고리 목록을 자동 생성할 때 매우 효과적입니다. SEQUENCE 함수는 연속된 숫자 배열을 자동으로 생성해줍니다. SEQUENCE(행수, 열수, 시작값, 증가값) 형태로 사용하며, 1부터 100까지의 숫자를 한 번에 생성하거나, 5씩 증가하는 숫자 패턴을 만들 수 있습니다. 두 함수를 조합하면 더욱 강력한 기능이 구현됩니다. SEQUENCE(COUNTA(UNIQUE(범위)))를 사용하면 중복 제거된 항목 개수만큼 자동으로 번호가 매겨지며, 데이터가 추가되어도 번호가 자동으로 확장됩니다. 날짜 배열 생성에도 활용 가능합니다. 오늘 날짜에 SEQUENCE로 생성한 숫자를 더하면 향후 30일, 90일의 날짜 목록이 자동 생성되어 일정 관리나 프로젝트 타임라인 작성에 유용합니다. 이러한 동적 배열의 특성을 이해하고 활용하면 수작업으로 관리하던 마스터 데이터나 코드 목록을 완전 자동화할 수 있습니다.
XLOOKUP과 동적 배열을 결합한 다차원 데이터 검색 자동화
XLOOKUP은 기존 VLOOKUP의 모든 한계를 극복한 차세대 검색 함수입니다. 왼쪽 열 검색이 가능하고, 정확히 일치하는 값이 없을 때 근사값을 반환하거나 사용자 지정 메시지를 표시할 수 있습니다. 기본 구문은 XLOOKUP(찾을값, 검색범위, 반환범위, 없을때값, 일치모드, 검색모드)이며, 일치모드 0은 정확한 일치, -1은 작거나 같은 값, 1은 크거나 같은 값을 찾습니다. 검색모드는 1이 첫 번째 일치, -1이 마지막 일치, 2가 이진 검색입니다. 동적 배열과 결합하면 단일 수식으로 여러 열의 데이터를 한 번에 가져올 수 있습니다. XLOOKUP의 반환범위에 여러 열을 지정하면 자동으로 옆으로 확장되어 관련 정보를 모두 표시합니다. FILTER 함수 내부에 XLOOKUP을 중첩하면 더욱 정교한 검색이 가능합니다. 제품 코드로 카테고리를 찾은 후, 해당 카테고리에 속한 모든 제품을 자동 필터링하는 2단계 검색을 단일 수식으로 구현할 수 있습니다. XMATCH 함수와 조합하면 위치 기반 검색도 자동화됩니다. XMATCH로 찾은 행 번호를 INDEX 함수에 전달하거나, SEQUENCE와 결합하여 특정 행부터 N개의 연속된 데이터를 추출하는 고급 기법도 가능합니다. 실무에서는 재고 관리 시스템에서 제품명 입력 시 자동으로 재고량, 단가, 공급업체 정보가 표시되도록 설정하거나, 직원 번호 입력 시 관련 인사정보가 자동 조회되는 시스템을 구축할 수 있습니다. 동적 배열의 스필 기능 덕분에 검색 결과가 여러 행으로 확장되어도 별도의 수식 복사 없이 자동으로 채워지므로, 대량의 데이터를 처리하는 업무에서 생산성이 획기적으로 향상됩니다.