들어가기 전에
동적 배열(dynamic array)의 도입은 Excel 수식 사용 방식에 가장 큰 변화를 가져왔습니다. 동적 배열은 하나의 수식으로 여러 결과를 인접한 셀에 분산하고 크기를 자동으로 조절할 수 있게 해 주며, 유연성이 떨어지는 기존의 Ctrl + Shift + Enter(CSE) 배열 수식을 대체합니다. 스프레드시트 프로그램과의 상호 작용 및 사용 방식을 혁신하는 유용한 동적 배열 함수들을 소개합니다.
[참고] 최신 동적 배열 함수를 사용하려면 Microsoft 365(Excel 365) 또는 웹용 Excel, 모바일 및 태블릿용 Excel이 필요합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 6 Functions That Changed How You Use Microsoft Excel
- URL: https://www.howtogeek.com/functions-changed-how-you-use-microsoft-excel/
1. FILTER: 동적으로 데이터 하위 집합 추출
FILTER 함수는 특정 기준에 따라 배열을 추출하여 수식이 입력된 셀의 결과를 분산시킵니다 . 이 동적 배열 함수가 도입되기 전에는 동일한 결과를 얻으려면 복잡하고 중첩된 수식을 만들거나 고급 필터 또는 파워 쿼리 와 같이 시간이 많이 걸리는 도구를 사용해야 했습니다. 그러나 이 동적 배열 함수는 작업을 훨씬 간편하게 만들어 줍니다.
FILTER 함수의 사용 구문은 다음과 같습니다.
=FILTER(a, b, c)
- a (필수): 필터링할 배열
- b (필수): 부울 인수(TRUE 또는 FALSE를 반환하는 인수)
- c (선택 사항): 필터가 결과를 반환하지 않을 경우 반환할 값
이 예에서 FILTER 함수는 70점 이상을 받은 사람의 이름과 나이를 반환하는 데 사용됩니다.
=FILTER(T_Results[[Name]:[Age]], T_Results[Score] > 70)
여기서 T_Results[[Name]:[Age]]는 Excel에서 Name 및 Age 열의 데이터를 반환하도록 지시하고, T_Results[Score]>70은 Score 열에 따라 70점 이상을 받은 사람만 포함하도록 이 배열을 필터링합니다.
이 수식은 구조화된 테이블 참조를 사용하므로 FILTER 함수는 새로운 데이터 행을 모두 선택합니다. FILTER 함수는 다른 함수와 함께 사용할 수 있을 뿐만 아니라 두 개 이상의 기준에 따라 필터링하는 데에도 사용할 수 있습니다. 이 경우, 더하기 기호가 두 개의 부울 인수에 추가되어 OR 논리를 생성하므로, 결과는 35세 이상이거나 65점 이상의 점수를 가진 개인의 이름을 반환합니다.
=FILTER(T_Results[Name], (T_Results[Age]>35) + (T_Results[Score]>65))
두 개의 부울 인수를 곱하는 것은 AND 논리를 나타내며, 두 가지 기준을 모두 충족하는 인수만 반환합니다.
=FILTER(T_Results[Name], (T_Results[Age]>35) * (T_Results[Score]>65))
두 경우 모두, 원본 데이터가 변경되어 기준을 충족하는 개인이 더 많거나 적어지면 결과의 크기를 조정할 수 있습니다.
2. RANDARRAY: 동적 배열의 난수 생성
RAND(Excel 2003 이상)와 RANDBETWEEN(2007 이상) 함수는 이전 버전의 Excel보다 난수 생성을 훨씬 쉽게 해 주었지만, 두 함수 모두 단일 결과만 반환합니다. 그러나 동적 배열 함수인 RANDARRAY는 여러 결과를 반환할 수 있으며 이전 함수들의 기능을 하나로 결합했습니다.
RAND, RANDBETWEEN, RANDARRAY는 모두 휘발성 함수 입니다 . 즉, 워크시트를 다시 계산하거나 다시 열 때마다 새로운 난수를 생성하기 위해 새로 고쳐집니다. 이러한 동작은 워크북 속도를 저하시킬 수 있으므로 가능하면 이러한 함수는 꼭 필요한 경우에 국한하여 사용하세요.
RANDARRAY 함수의 사용 구문은 다음과 같습니다.
=RANDARRAY(a, b, c, d, e)
- a: 반환할 행 수
- b: 반환할 열 수
- c: 난수 배열의 최소 숫자
- d: 난수 배열의 최대 숫자
- e: 10진수의 경우 FALSE(기본값), 정수의 경우 TRUE
모든 인수는 선택 사항이지만 특정 인수를 생략하면 어떻게 되는지 알고 있어야 합니다. 인수 a와 b를 생략하면 함수는 0에서 1 사이의 단일 소수점 값을 반환합니다. 인수 c와 d를 생략하면 최소값은 0, 최대값은 1로 기본 설정됩니다. 인수 e를 생략하면 소수점 값을 반환합니다.
=RANDARRAY(7, 1, 1, 40, TRUE)
최소값 1, 최대값 40을 갖는, 7개 행, 1열에 걸쳐 무작위 정수 배열을 생성합니다.
인수를 직접 입력하는 대신 셀을 참조하면 수식을 더 유연하게 만들 수 있습니다.
=RANDARRAY(D1, D2, D3, D4, D5)
여기서 D5 셀(e 인수)에는 체크하면 TRUE(정수)를 반환하고, 체크하지 않으면 FALSE(10진수)를 반환하는 체크박스가 포함되어 있습니다.
RANDARRAY를 사용하여 기존 배열을 무작위로 지정할 수도 있습니다. 여기서는 T_Groups 테이블의 그룹 번호를 무작위로 지정하고 싶습니다. 이를 위해 다음과 같이 입력합니다.
=SORTBY(T_Groups[Group], RANDARRAY(COUNTA(T_Groups[Group])))
여기서 RANDARRAY 및 COUNTA 함수는 11개 행 아래의 이론적인 난수 숫자 배열을 반환하며, 이는 SORTBY 함수가 반환하는 정렬 순서를 결정합니다.
생성된 동적 배열을 고정 배열로 바꾸려면 난수가 포함된 모든 셀을 선택하고 Ctrl + C를 눌러 복사한 다음, Ctrl + Shift + V를 눌러 값으로 붙여넣습니다.
RANDARRAY는 광범위한 활용이 가능하여 강력한 기능을 제공합니다. 예를 들어, 데이터 시뮬레이션 및 무작위 표본 추출에 사용하거나 무작위 텍스트 또는 날짜를 생성하는 데 사용할 수 있습니다.
3. SEQUENCE: 순차적 숫자의 동적 배열 생성
채우기 핸들이나 ROW 또는 COLUMN 함수를 사용해서 순차적인 숫자의 정적 목록을 직접 만들 수 있었던 (그리 좋지 않았던) 옛날을 기억하시나요? 하지만 이제는 그럴 필요가 없습니다. SEQUENCE 함수는 정의된 모양, 크기, 증분 값을 가진 순차적인 값 목록을 동적으로 생성해 주기 때문입니다.
SEQUENCE 함수의 사용 구문은 다음과 같습니다.
=SEQUENCE(a, b, c, d)
- a (필수): 시퀀스가 아래로 확장되는 행 수
- b (선택 사항): 가로로 펼쳐질 열 수
- c (선택 사항): 시퀀스의 시작 번호
- d (선택 사항): 시퀀스의 각 값 사이의 점프
선택적 인수를 생략하면 기본적으로 1개가 사용됩니다.
이 예에서는 다음 수식을 입력합니다.
=SEQUENCE(10, 1, 5, 5)
A2 셀에서 10행 아래로, 1행 가로로 구성된 목록이 생성됩니다. 5개로 시작하여 매번 5개씩 증가합니다.
수식에서 셀을 참조하면 수식을 조정하지 않고도 매개변수를 빠르게 변경할 수 있으므로 수식이 더 유연해집니다.
=SEQUENCE(E1, E2, E3, E4)
해당 셀의 값을 변경하면 펼쳐진 배열의 크기가 즉시 조정됩니다.
SEQUENCE 함수의 동적 특성은 다른 함수와 결합될 때 더욱 빛을 발합니다.
=SEQUENCE(COUNTA(B:B) - 1)
열 머리글을 고려하여 1을 빼고 열 B에 있는 비어 있지 않은 셀의 개수를 세어 시퀀스가 차지해야 하는 행의 개수를 결정합니다.
DATE 함수를 중첩하여 날짜 시퀀스를 생성할 수도 있습니다.
=SEQUENCE(1, 20, DATE(2024, 3, 1), 7)
첫 번째 행에서 20개 열에 걸쳐 날짜를 표시하고, 3월 1일부터 시작하여 매번 7일씩 건너뜁니다.
SEQUENCE는 여러 함수와 결합하여 다양한 조건에 따라 동적 목록을 만들 수 있으므로 2020년 Microsoft 365용 Excel에 도입된 것과 2021년 독립 실행형 버전의 프로그램은 획기적인 변화를 가져왔습니다.
4. SORTBY: 배열을 동적으로 추출하고 정렬
SORTBY 함수를 사용 하면 피벗 테이블 이나 복잡한 수식을 사용하여 데이터를 동적으로 정렬할 필요가 없습니다 . 고급 필터 도구는 단기적으로는 유사한 결과를 생성할 수 있지만, 장기적으로는 원본 데이터의 변경 사항을 반영하여 결과를 업데이트하지 않습니다. 간단히 말해, SORTBY를 사용하면 라이브 데이터를 쉽게 추출하고 정렬할 수 있습니다.
함수의 구문이 복잡해 보이지만 실제로는 매우 직관적입니다.
=SORTBY(a ,b¹ ,b² ,c¹ ,c²...)
- a (필수): 정렬하려는 범위 또는 배열
- b¹ (필수): 데이터를 정렬할 첫 번째 범위 또는 배열
- b² (선택 사항): b¹ 에 대한 정렬 순서
- c¹ 과 c² (선택 사항): 데이터를 정렬하는 데 사용되는 두 번째 배열이자 순서로, 최대 64개까지 지정 가능
다음 수식은 T_Results 테이블을 Score 열에 따라 내림차순으로 추출하고 정렬합니다.
=SORTBY(T_Results, T_Results[Score], -1)
SORTBY 함수는 Excel의 다른 함수와 함께 사용하여 결과를 더욱 세부적으로 조정할 수 있습니다. 여기서는 FILTER 함수와 함께 사용하여 Score 열을 기준으로 데이터를 정렬하지만, 전체 점수가 60점 이상인 사람만 표시하는 결과를 생성합니다.
=FILTER(SORTBY(T_Results, T_Results[Score], -1), SORTBY(T_Results[Score], T_Results[Score], -1)>60)
5. UNIQUE: 고유한 값의 동적 목록 반환
UNQIUE 함수는 Excel을 매일 사용하는 사람들이 오랫동안 기다려 온 기능입니다. 비록 그 사실을 깨닫지 못했을지라도요. 이 함수가 Excel에 추가되기 전에는 배열에서 고유 값을 추출하려면 기존 CSE 배열 수식에 최소 세 개의 함수를 중첩하거나, 피벗 테이블을 만들거나, 다양한 메뉴와 대화 상자를 클릭해야 했습니다. 이 모든 방법에는 시간이 많이 걸리는데, 많은 사람들이 그럴 시간이 없습니다.
하지만 UNIQUE 함수를 사용하면 몇 초 만에 동일한 결과를 얻을 수 있습니다. 게다가, 이 함수는 동적이며, 원본 데이터의 변경 사항을 반영하여 업데이트되고, 필요에 따라 확장되거나 축소되는 배열을 생성합니다.
UNIQUE 구문에는 세 개의 인수가 있습니다. 하나는 필수이고 두 개는 선택 사항입니다.
=UNIQUE(a, b, c)
- a (필수): 고유한 값을 반환할 배열
- b (선택 사항): 고유한 행을 반환하려면 FALSE(기본값), 고유한 열을 반환하려면 TRUE
- c (선택 사항): 모든 고유한 값을 반환하려면 FALSE(기본값), 한 번만 발생하는 값을 반환하려면 TRUE
이 예에서 E2 셀에 입력한 수식은 T_Players 테이블의 이름 열에서 고유한 이름을 추출합니다. 인수 b 와 c가 생략되었으므로 기본적으로 행(인수 b )에 고유한 값(인수 c ) 을 반환합니다.
=UNIQUE(T_Players[Name])
UNIQUE 함수를 SORT 및 FILTER와 함께 사용하여 특정 기준에 따라 고유한 아이템의 알파벳순 목록을 추출할 수 있습니다. 이 예시에서는 6점 이상을 획득한 모든 플레이어의 이름을 추출하여 결과를 알파벳순으로 정렬했습니다.
=SORT(UNIQUE(FILTER(T_Players[Name], T_Players[Score]>6)))
6. XLOOKUP: 동적으로 여러 관련 항목 검색
XLOOKUP은 위의 함수들보다 조금 더 오래 전부터 사용되어 왔지만, 동적 배열이 탄생하면서 유용성이 더욱 커졌습니다. XLOOKUP 함수는 VLOOKUP과 HLOOKUP 함수의 현대화된 버전으로, 데이터 집합에서 특정 값을 검색하여 해당 값을 반환합니다.
XLOOKUP 함수가 이전 함수보다 뛰어난 점은 수직 및 수평 조회를 수행하고, 모든 열이나 행에서 값을 조회하고, 조회 결과를 별도의 인수로 인식하여 배열을 반환하고, 오류를 처리하고, 선호하는 설정을 기본값으로 사용할 수 있다는 것입니다.
XLOOKUP 함수의 사용 구문은 다음과 같습니다.
=XLOOKUP(a, b, c, d, e, f)
- a (필수)는 조회 값
- b (필수)는 조회 배열
- c (필수)는 반환 배열
- d (선택 사항)는 조회 값(a)이 조회 배열(b)에서 발견되지 않을 경우 반환할 텍스트
- e (선택 사항): 일치 모드
- 0(기본값): 정확한 일치
- -1: 정확한 일치 또는 다음으로 작은 항목
- 1: 정확한 일치 또는 다음으로 큰 항목
- 2: 와일드카드 일치
- f (선택 사항): 검색 모드
- 1(기본값): 위에서 아래로 또는 왼쪽에서 오른쪽으로 검색
- -1: 역순으로 검색
- 2: 오름차순 배열에서 이진 검색
- -2: 내림차순 배열에서 이진 검색
XLOOKUP은 단일 결과를 반환하는 기능으로 가장 잘 알려져 있습니다. 이 수식은 F2 셀에 ID 번호를 입력하면 T_Results 테이블의 G2 셀에 있는 이름 열에서 플레이어의 이름을 반환합니다.
=XLOOKUP(F2,T_Results[ID], T_Results[Name])
하지만 두 개 이상의 결과를 반환할 수 있으므로 동적 배열 함수가 됩니다. 이번에는 F2 셀에 ID를 입력하면 XLOOKUP 함수가 선수의 이름, 나이, 점수를 I2 셀까지 확장되는 분산 배열로 반환하여 각 정보에 대해 별도의 수식을 사용할 필요가 없습니다.
=XLOOKUP(F2, T_Results[ID], T_Results[[Name]:[Score]])
XLOOKUP은 수평으로 스필링하는 것뿐만 아니라 수직으로 스필링할 수도 있습니다. 이 수식은 F2부터 F13 셀에 ID가 입력된 모든 플레이어의 점수를 반환합니다.
=XLOOKUP(F2, T_Results[ID], T_Results[[Name]:[Score]])
조회 값 인수의 콜론 뒤에 있는 마침표('트림 참조 연산자'라고도 함)는 참조에서 끝 공백을 제거하여 워크시트와 수식의 성능을 향상시킵니다.
마지막 예제에서는 단일 XLOOKUP 수식을 사용하여 점수에 따라 모든 학생의 성적을 반환하므로 C열의 모든 셀에 수식을 복사할 필요가 없습니다.
=XLOOKUP(B2:.B100, T_Boundaries[Score], T_Boundaries[Grade], "FAIL", -1)
Excel의 동적 배열 함수 및 수식에 대한 주의 사항
동적 배열 함수를 사용할 때 주의해야 할 중요한 사항은 다음과 같습니다.
- 동적 배열 수식의 결과는 Excel 표의 열에 나타날 수 없습니다 . 따라서 항상 일반 셀에 입력해야 합니다.
- 동적 배열을 분산해야 하는 셀에 이미 채워진 경우 수식은 #SPILL! 오류를 반환하므로 수식을 입력하기 전에 충분한 공간이 있는지 확인하세요.
- 다른 수식에서 동적 배열을 참조하려면 첫 번째 셀만 참조하기 전에 해시 기호(#)를 입력합니다. 이는 스필 범위 연산자 라고도 합니다.
- 다른 통합 문서를 참조하는 동적 배열은 두 통합 문서가 모두 열려 있는 경우에만 예상 결과를 생성합니다.
마치며
하나의 수식으로 여러 계산을 수행하는 또 다른 방법은 배열 상수를 사용하는 것입니다. 동적 배열 수식보다 유연성은 떨어지지만, 여러 개의 수식이나 긴 수식을 입력할 필요가 없습니다. 배열 상수와 관련해서는 [여기]를 참고하세요.
'Excel' 카테고리의 다른 글
| 여러분의 두뇌를 뒤흔들(좋은 의미로) 엑셀의 새로운 함수 4가지 (0) | 2026.01.08 |
|---|---|
| 내부적으로 발생하는 일을 알려주는 Excel 함수 사용법 (0) | 2026.01.06 |
| 신규 콘텐츠 오픈 안내 (0) | 2026.01.05 |
| "파워툴스" 업데이트 안내 (26년 1월 4일 현재) (0) | 2026.01.04 |
| 더 나은 결정을 내리고 후회를 피하는 데 도움이 되는 Excel 기능 (0) | 2026.01.02 |
