들어가기 전에
새로운 데이터를 입력하다가 엑셀 테이블이 충분히 크지 않다는 걸 깨달을 때만큼 작업 흐름을 방해하는 건 없습니다. 이런 경우 사람들은 보통 테이블의 가장자리를 끌어당기며 조정합니다. 하지만 이 팁을 사용하면 테이블이 자동으로 확장됩니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: This Excel Trick Ends the Pain of Resizing Tables
- URL: https://www.makeuseof.com/excel-tables-expand-automatically-trick/
Excel의 동적 배열을 사용하여 테이블 확장
Excel의 동적 배열(dynamic array)은 테이블을 확장하는 가장 적합한 방법입니다. 동적 배열은 단일 값을 반환하는 대신, 사전에 정확한 범위를 정의하지 않아도 결과를 여러 셀에 걸쳐 자동으로 확장합니다. 이 기능은 자체 확장형 테이블을 생성하는 데 적합합니다.
예시로는 UNIQUE, SORT, FILTER 같은 함수가 있습니다. 이 함수들은 소스 데이터에 따라 크기가 확장되거나 축소되는 동적 배열을 반환합니다. 데이터셋에 새로운 항목을 추가하면 결과는 즉시 업데이트되며 수동 개입이 필요 없습니다.
동적 배열 공식을 한 셀에 입력하면 Excel은 모든 결과를 표시하기 위해 필요한 인접 셀을 자동으로 채웁니다. 이 범위 주변에 파란색 테두리가 표시되면 정상적으로 작동하고 있음을 알 수 있습니다. 이 확산 영역에 무언가를 입력하려고 하면 #SPILL! 오류가 발생하며, 이는 유용한 안전 장치입니다.
동적 배열을 사용하는 것은 단순히 편리할 뿐만 아니라 신뢰할 수 있습니다. 수동 테이블 관리는 오류, 데이터 누락, 그리고 좌절감을 초래할 수 있기 때문입니다.
고유 항목의 자동 확장 목록 생성하기
UNIQUE 함수는 작업을 크게 줄여주는 함수 중 하나입니다. 목록을 수동으로 확인하는 대신 이 함수를 사용해 데이터에서 고유 값을 자동으로 추출합니다.
기본 구문은 다음과 같습니다.
=UNIQUE(array, [by_col], [exactly_once])
- array: 직원 부서나 고객 이름 열과 같은 원본 데이터
- by_col: 비교할 기준이 열인지 행인지 결정(TRUE 또는 FALSE)
- exact_once: 한 번만 나타나는 값을 필터링
직원 데이터 시트에서 작업 중이고 모든 부서에 대한 정리된 목록이 필요한 경우 간단히 다음과 같이 입력하면 됩니다.
=UNIQUE(R2:R3004)
R열에는 행 2부터 3004까지의 부서 이름이 포함되어 있습니다. 누군가가 새로운 팀에 합류할 때마다 업데이트되는 고유한 부서의 동적 목록이 즉시 생성됩니다.
이 방법은 Excel에서 중복 항목을 제거하는 기존 방식보다 효율적입니다. 동적 배열은 원본 데이터와 연결된 상태를 유지하기 때문입니다. 반면, 수동으로 중복 항목을 제거하면 새 항목을 추가하는 순간 오래된 목록이 되는 정적 목록이 생성됩니다.
정확히 한 번만 발생하는 시나리오 의 경우, 다음 수식을 사용하여 직원이 한 명뿐인 부서를 찾을 수 있습니다. 이 수식은 조직 내에서 인력이 부족한 팀이나 고유한 역할을 파악하는 데 효과적입니다.
=UNIQUE(R2:R3004,, TRUE)
동적 목록 자동으로 정렬하기
SORT 함수는 동적 배열을 한 단계 더 발전시킵니다. 직원 데이터 예시를 계속해서 보면, 직원 이름이나 급여 데이터를 수동으로 정렬하는 대신 Excel은 데이터가 변경될 때마다 자동으로 정렬합니다. 다음은 SORT 함수의 구문입니다.
=SORT(array, [sort_index], [sort_order], [by_col])
- array: 데이터 범위
- sort_index는 정렬할 열 지정
- sort_order는 오름차순(1) 또는 내림차순(-1) 여부 지정
- by_col은 열(TRUE) 또는 행(FALSE) 기준으로 정렬할지 여부
더 나은 결과를 얻으려면 SORT와 UNIQUE를 함께 사용할 수 있습니다. 예를 들어, 다음 수식은 직원 명단에서 고유한 부서를 알파벳순으로 정렬한 목록을 제공합니다. HR에서 새 부서를 추가하면 자동으로 올바른 알파벳순 위치에 표시됩니다.
=SORT(UNIQUE(R2:R3004))
급여 분석을 해야 하는 경우 다음과 같은 수식을 사용할 수 있습니다.
=SORT(A:H, 8, -1)
위 수식은 직원 데이터를 급여를 기준으로 내림차순으로 정렬합니다. 8열에는 급여가 포함되어 있고, -1은 가장 높은 값에서 가장 낮은 값 순으로 정렬합니다.
SORT 함수는 대소문자를 구분하며, 텍스트로 저장된 숫자와 실제 숫자를 다르게 처리합니다. 정확한 정렬을 위해 데이터 유형이 일관되도록 하세요.
동적 보고서에 가장 많이 사용되는 FILTER 함수
Excel의 FILTER 함수를 한 번도 사용해 본 적이 없다면 , 정말 중요한 것을 놓치고 있는 것입니다. FILTER 함수는 가장 강력한 동적 보고서 중 하나를 만들어 줍니다. 이 함수를 사용하면 오래된 정적인 복사본을 만들지 않고도 조건에 맞는 데이터만 자동으로 표시할 수 있습니다.
FILTER 함수의 구문은 다음과 같습니다.
=FILTER(array, include, [if_empty])
- array: 전체 데이터 세트
- include: 조건 지정
- if_empty: 조건에 맞는 결과가 없으면 사용자 지정 메시지 표시
FILTER는 다양한 보고서에 사용할 수 있습니다. 예를 들어, 직원 데이터베이스의 모든 영업팀 구성원을 표시하려면 다음과 같이 사용할 수 있습니다.
=FILTER(A:Q, Q:Q="Sales")
누군가 영업 부서로 이직하면 필터링된 결과에 자동으로 표시됩니다. 마찬가지로, 급여 분석의 경우, 다음 수식을 사용하여 5만 달러 이상의 연봉을 받는 직원을 표시할 수 있습니다.
=필터(A:H, H:H>50000)
누군가 급여 인상을 받으면 이 고소득자 보고서에 갑자기 표시됩니다. 그 외에도 다음과 같은 기준을 결합할 수 있습니다.
=FILTER(A:Q, (Q:Q="Sales") * (H:H>50000))
위 수식은 $50,000 이상의 연봉을 받는 영업팀원을 보여줍니다. 별표(*)는 AND 연산자 역할을 하며, 두 조건이 모두 참이어야 합니다.
FILTER는 조건에 맞는 결과가 없으면 #CALC! 오류를 반환합니다. 대신 if_empty 매개변수를 사용하여 "결과가 없습니다"를 표시하세요.
마치며
동적 배열은 수동으로 테이블을 업데이트할 필요 없이 입력 누락을 방지해 주므로 편리합니다. UNIQUE, SORT, FILTER 함수를 함께 사용하면 Excel이 더욱 직관적으로 작동합니다.
'Excel' 카테고리의 다른 글
| Excel 최고의 조회 함수(또는 함수 조합) 3가지 (0) | 2025.12.02 |
|---|---|
| [강의 예고] 엑셀 수식 안에 "데이터 보관함" 넣는 방법 (0) | 2025.11.30 |
| 엑셀 수식의 복잡성을 절반으로 줄여주는 트릭 (0) | 2025.11.26 |
| VLOOKUP 대신 엑셀의 XMATCH 함수를 써야 하는 이유 3가지 (0) | 2025.11.25 |
| Excel 시트를 더욱 스마트하게 만들어 주는 조건 함수 6가지 (0) | 2025.11.24 |
