Ch09_함수를 사용하는 데이터 검색(2)_동적함수

Section 02. 동적 배열 함수

이번 절에서는 업로드한 실습 파일의 Section02_동적함수 시트를 기준으로 설명한다. 

 Microsoft가 공개한 MO-211 Excel Expert(Microsoft 365 Apps) 시험은 ‘고급 수식 및 매크로’ 영역을 전체 시험의 약 25~30%로 평가한다고 밝히고 있다.따라서 아래의 ★ 표시는 공식적인 ‘기출 빈도 통계’가 아니라, Expert 수준에서 데이터 추출·정렬·고유값 처리와 연결하여 우선 학습할 가치를 기준으로 표시하였다.

함수	학습	핵심역할
FILTER	★★★★★	조건을 만족하는 행만 추출
SORTBY	★★★★★	특정 열을 기준으로 결과 정렬
UNIQUE	★★★★☆	중복값 제거, 고유 목록 생성
SEQUENCE	★★★★☆	연속 번호·일련번호 자동 생성
RANDARRAY	★★★☆☆	여러 난수를 배열 형태로 생성
RANDBETWEEN	★★★☆☆	범위 내 정수 난수 1개 생성

---

1. 동적 배열 함수란?

기존 Excel에서는 하나의 셀에 수식을 입력하면 일반적으로 하나의 결과가 반환되었다. Excel 365에서는 하나의 수식이 여러 개의 결과를 반환하고, 필요한 셀 영역으로 자동 확장될 수 있다. 이를 동적 배열(Dynamic Array)이라고 한다.

예를 들어 다음 수식을 D11 셀에 입력한다고 하자.

=RANDARRAY(10,3,10,100,TRUE)

수식은 D11 하나에만 입력하지만 실제 결과는 다음과 같이 10행 × 3열, 총 30개의 셀에 자동으로 표시된다.

74    32    40
33    66    28
83    92    37
51    28    97
...

이처럼 결과가 주변 셀로 자동 확장되는 현상을 Spill(스필)이라고 한다.

동적 배열에서 반드시 알아둘 점

동적 배열 함수가 결과를 표시할 영역에는 다른 데이터가 없어야 한다. 결과가 확장되어야 할 영역에 이미 값이 입력되어 있으면 Excel은 다음 오류를 표시할 수 있다.

#SPILL!

따라서 FILTER, UNIQUE, SEQUENCE, RANDARRAY 등의 함수에서는 수식을 입력한 셀 아래와 오른쪽 영역을 비워 두는 것이 중요하다.

---

2. RANDBETWEEN 함수

RANDBETWEEN은 지정한 두 숫자 사이에서 임의의 정수 하나를 생성한다.

기본 형식

=RANDBETWEEN(최솟값, 최댓값)

예를 들어

=RANDBETWEEN(10,100)

이라고 입력하면 10 이상 100 이하의 정수가 하나 생성된다.

🧪 실습 01. 10~100 사이 난수 생성

① B10 셀 선택

② 다음 수식을 입력한다.

=RANDBETWEEN(10,100)

예를 들어 다음과 같은 결과가 나타날 수 있다.

27

다시 계산되면

81

처럼 값이 바뀔 수 있다.

알아두기

RANDBETWEEN은 휘발성 함수(Volatile Function)에 해당한다. 워크시트가 다시 계산되면 값이 변경될 수 있다. 
따라서 무작위 데이터를 만든 후 그 값을 고정하려면

복사 → 선택하여 붙여넣기 → 값

으로 변환할 수 있다.

---

3. RANDARRAY 함수

RANDBETWEEN이 난수 하나를 만든다면 RANDARRAY는 여러 개의 난수를 한 번에 배열 형태로 생성한다.

Excel 365에서 매우 유용한 함수이다.

 

=RANDARRAY([행],[열],[최솟값],[최댓값],[정수여부])

요소	설명
행	생성할 행의 수
열	생성할 열의 수
최솟값	난수의 최소값
최댓값	난수의 최대값
정수여부	TRUE이면 정수, FALSE이면 실수

마지막 인수에 TRUE를 사용하면 정수가 생성된다.

🧪 실습 02. 10행 × 3열 난수 만들기

현재 실습 시트에는 다음과 같이 입력되어 있다.

• E10 : 10
• F10 : 3

즉, 10행 × 3열의 난수 배열을 생성한다.

 

D11 셀에 다음 수식을 입력한다.

=RANDARRAY(E10,F10,10,100,TRUE)

수식을 풀어서 읽으면 다음과 같다.

E10에 입력된 행 개수와 F10에 입력된 열 개수만큼,
10에서 100 사이의 정수를 생성한다.

따라서

10행 × 3열 = 30개

의 난수가 생성된다.

Expert 포인트

다음과 같이 직접 숫자를 넣는 것보다

=RANDARRAY(10,3,10,100,TRUE)

다음처럼 셀을 참조하도록 작성하는 방법이 실무적으로 더 좋다.

=RANDARRAY(E10,F10,10,100,TRUE)

E10이나 F10의 값을 변경하면 배열 크기도 자동으로 변하기 때문이다.

---

4. FILTER 함수 ★★★★★

이번 절에서 가장 중요하게 학습할 함수이다.

FILTER 함수는 데이터 범위에서 조건을 만족하는 행만 골라서 새로운 배열로 반환한다. 기존의 자동 필터 기능은 원본 표를 화면에서 걸러 보여 주지만, FILTER 함수는 조건에 맞는 데이터 자체를 다른 위치에 추출한다는 차이가 있다.

 

=FILTER(배열, 포함조건, [결과없음])

배열	가져올 데이터 범위
포함조건	추출할 조건
결과없음	조건을 만족하는 데이터가 없을 때 표시할 내용

 

🧪 실습 03. 부산 지역 판매데이터 검색

A25	검색 지역
B25	최소 매출액
A26	부산
B26	1,500,000

원본데이터는 다음 범위를 사용한다.

원본데이터!A5:J124

여기에서

• E열 = 지역
• I열 = 매출액

이다.

A29 셀에 다음 수식이 사용되어 있다.

=FILTER(
'원본데이터'!A5:J124,
('원본데이터'!E5:E124=A26)*
('원본데이터'!I5:I124>=B26),
"검색 결과 없음"
)

한 줄로 작성하면 다음과 같다.

=FILTER('원본데이터'!A5:J124,('원본데이터'!E5:E124=A26)*('원본데이터'!I5:I124>=B26),"검색 결과 없음")

FILTER에서 AND 조건 사용하기 ★★★★★ (논리곱)

여기서 매우 중요한 부분이 다음의 * 연산자이다. (논리곱)

(조건1)*(조건2)

동적 배열 함수에서는 *를 이용하여 AND 조건을 만들 수 있다.

현재 수식은

(지역="부산")*(매출액>=1500000)

이므로

지역이 부산이고 동시에 매출액이 150만 원 이상인 데이터만 추출한다.

기억하기

*   → AND
+   → OR

 

---

6. FILTER에서 OR 조건 사용하기 (논리합)

서울 또는 부산 데이터를 찾고 싶다면 +를 사용할 수 있다.

=FILTER(원본데이터!A5:J124,(원본데이터!E5:E124="서울")+(원본데이터!E5:E124="부산"),"검색 결과 없음")

즉,

* → AND
+ → OR

은 동적 배열 문제에서 반드시 기억하도록 한다.

아래 뿌려질 빈행이 충분하지 않으면 이런 오류가 나옴. 이때는 충분한 행 수를 확보하자.

---

7. SORTBY 함수 ★★★★★

FILTER 다음으로 함께 사용하는 경우가 많은 함수가 SORTBY이다. SORTBY는 배열의 특정 열을 기준으로 데이터를 정렬한다.

 

=SORTBY(배열, 기준배열, 정렬순서)

정렬 순서는 다음과 같다.

1	오름차순
-1	내림차순

 

🧪 실습 04. 부산 판매데이터를 매출액 순으로 정렬

현재 Section02_동적함수 시트에는 다음 응용 수식이 포함되어 있다.

=SORTBY(FILTER(원본데이터!A5:J124,원본데이터!E5:E124=A26),FILTER(원본데이터!I5:I124,원본데이터!E5:E124=A26),-1)

이 수식은 두 단계로 이해하는 것이 좋다.

① FILTER

FILTER('원본데이터'!A5:J124,
'원본데이터'!E5:E124=A26)

먼저 부산 데이터만 추출한다.

② SORTBY

그 결과를 매출액 기준으로

-1

내림차순 정렬한다.

따라서 최종 결과는

부산 판매데이터 중 매출액이 높은 순

으로 표시된다. 

이러한 FILTER + SORTBY 조합은 Expert 수준에서 반드시 연습할 만한 패턴이다.

---

8. UNIQUE 함수 ★★★★☆

UNIQUE 함수는 데이터 범위에서 중복된 값을 제거하고 고유한 값만 반환한다.
즉, UNIQUE 함수는 이전 학습에서 열의 복사 → 중복 항목 제거 작업을 수식으로 자동화한 것이라고 보면 이해하기 쉽습니다.

 

=UNIQUE(배열)

🧪 실습 05. 지역 목록에서 중복 제거하기

현재 실습 시트의 L10 셀에서는 다음 수식을 사용한다.

=UNIQUE('원본데이터'!E5:E124)

---

9. SORT + UNIQUE 조합 ★★★★☆

고유 목록을 만들었지만 정렬 순서까지 정리하고 싶다면 두 함수를 중첩한다.

현재 M10 셀에서는 다음 구조를 사용한다.

=SORT(UNIQUE('원본데이터'!E5:E124))

계산 순서는 안쪽 함수부터이다.

① UNIQUE 실행

UNIQUE('원본데이터'!E5:E124)

중복 지역을 제거한다.

② SORT 실행

SORT(...)

그 결과를 정렬한다.

Expert 학습 포인트

함수 문제를 볼 때

=SORT(UNIQUE(...))

를 한꺼번에 외우기보다

① UNIQUE → 중복 제거
② SORT   → 정렬

순서로 해석하는 습관을 들이는 것이 좋다.

---

10. SEQUENCE 함수 ★★★★☆

SEQUENCE 함수는 연속된 숫자 배열을 자동으로 생성한다. 순번, 코드, 월 번호, 연도 등의 데이터를 만들 때 편리하다.

 

=SEQUENCE(행,[열],[시작값],[증가값])

행	생성할 행 수
열	생성할 열 수
시작값	첫 번째 숫자
증가값	증가 간격

 

🧪 실습 06. 100부터 5씩 증가하는 번호 생성

실습 시트에서는

• P10 = 100
• Q10 = 5

로 설정되어 있다.

O11에 다음 수식을 입력한다.

=SEQUENCE(10,1,P10,Q10)

10 → 10행
1  → 1열
P10 → 시작값 100
Q10 → 5씩 증가

---

 

11. SEQUENCE의 시험 대비 형태

🧪 1부터 50까지 순번 만들기

=SEQUENCE(50)

결과:

1
2
3
...
50

🧪 100부터 10씩 증가하는 20개의 번호

=SEQUENCE(20,1,100,10)

 

🧪 가로 방향 번호 생성

=SEQUENCE(1,12)

결과는 한 행에

1 2 3 4 5 6 7 8 9 10 11 12

가 생성된다.

---

참고. Expert 수준에서 특히 중요한 함수 조합

개별 함수를 외우는 것보다 다음 조합을 이해하는 것이 중요하다.

① 조건 추출

=FILTER(데이터범위,조건)

② AND 조건 추출

=FILTER(데이터범위,(조건1)*(조건2))

③ OR 조건 추출

=FILTER(데이터범위,(조건1)+(조건2))

④ 조건 검색 후 정렬

=SORTBY(FILTER(...),정렬기준,-1)

⑤ 중복 제거 후 정렬

=SORT(UNIQUE(...))

⑥ 자동 순번

=SEQUENCE(50)

특히 FILTER + SORTBY, SORT + UNIQUE처럼 여러 함수를 결합할 수 있어야 실무형 Expert 문제에 대응하기 쉽다.

🧪 실습 07. 시트의 Expert 연습 문제

Section02_동적함수 시트 오른쪽에는 다음 5개의 문제가 배치되어 있다.

문제 ① 서울 데이터 추출

원본데이터에서 지역이 서울인 데이터만 추출하시오.

핵심 함수

FILTER

풀이 예

=FILTER(원본데이터!A5:J124,원본데이터!E5:E124=M26,"검색 결과 없음")

FILTER 함수는 조건에 맞는 데이터만 추출하며, 열 머리글은 자동으로 포함하지 않는다.
따라서 헤더를 별도로 작성하거나, Excel 365에서는 VSTACK 함수와 함께 사용하여 헤더와 결과를 하나의 배열로 구성할 수 있다.

 

문제 ② 부산 + 200만 원 이상

지역이 부산이면서 매출액이 2,000,000원 이상인 데이터만 추출하시오.

=FILTER(원본데이터!A5:J124,(원본데이터!E5:E124=M27)*(원본데이터!I5:I124>=N27),"검색 결과 없음")

핵심

* = AND

이다.

 

문제 ③ 매출액 내림차순

판매데이터를 매출액이 높은 순서로 정렬하시오.

 

SORTBY

예를 들면

=SORTBY(원본데이터!A5:J124,원본데이터!I5:I124,-1)

 

문제 ④ 지역 고유 목록 정렬

지역 목록에서 중복을 제거한 후 정렬하시오.

=SORT(UNIQUE('원본데이터'!E5:E124))

이 문제에서는 두 함수가 필요하다.

UNIQUE → 중복 제거
SORT   → 정렬

 

문제 ⑤ 1~50 순번 자동 생성

1부터 50까지 연속 번호를 생성하시오.

=SEQUENCE(50)

---

⭐ Expert 종합 실습

다음 조건을 모두 만족하는 수식을 작성하여 보자.

원본데이터에서 부산 지역이면서 매출액이 2,000,000원 이상인 판매자료를 추출하고, 추출된 결과를 매출액이 높은 순서로 정렬하시오.

이 문제에서는 두 가지 기능이 필요하다.

FILTER → 조건에 맞는 데이터 추출
SORTBY → 추출된 데이터를 정렬

수식은 다음과 같은 구조가 된다.

=SORTBY(
FILTER(
'원본데이터'!A5:J124,
('원본데이터'!E5:E124="부산")*
('원본데이터'!I5:I124>=2000000)
),
FILTER(
'원본데이터'!I5:I124,
('원본데이터'!E5:E124="부산")*
('원본데이터'!I5:I124>=2000000)
),
-1
)

처음 보면 복잡하지만 실제 계산 과정은 단순하다.

1단계 FILTER로 부산이면서 200만 원 이상인 데이터를 찾는다.

2단계 같은 조건에서 매출액 열을 정렬 기준으로 지정한다.

3단계 -1을 사용하여 높은 매출액부터 내림차순으로 배치한다.

이와 같이 동적 배열 함수는 하나의 함수 사용법보다 여러 함수를 어떻게 결합하는지가 중요한 학습 포인트이다.

---

📌 요점 정리

RANDBETWEEN과 RANDARRAY는 실습 데이터를 빠르게 만드는 데 유용하며, 실제 데이터 검색에서는 FILTER가 핵심적인 역할을 한다. SORTBY를 함께 사용하면 조건에 맞는 데이터를 추출하면서 원하는 기준으로 정렬할 수 있다.

UNIQUE는 중복 목록을 제거하며 SORT와 결합하는 경우가 많다. SEQUENCE는 연속번호를 자동으로 생성한다. 또한 동적 배열 함수에서는 결과가 여러 셀에 자동으로 확장되는 Spill 개념과 #SPILL! 오류를 반드시 이해하여야 한다