Microsoft 365 배열함수 기초 이론 (4단계)
달의이성
•조회수 25
요약
고급 배열함수 기법과 최적화
🚀 고급 패턴과 최적화 전략
🔥 LET 함수 마스터리
변수를 활용한 복잡한 로직 구현과 성능 최적화
🧠 LAMBDA 함수 활용
사용자 정의 함수로 재사용성 극대화
⚡ 성능 최적화 기법
대용량 데이터에서도 빠른 처리 속도 보장
🔥 LET 함수 고급 활용
기본 개념
=LET(변수1, 값1, 변수2, 값2, ..., 최종계산식)패턴 1: 중간 계산 재사용
// 나쁜 예: 동일한 계산을 반복
=HSTACK(
FILTER(A2:D11, C2:C11>AVERAGE(C2:C11)),
FILTER(A2:D11, C2:C11>AVERAGE(C2:C11))
)
// 좋은 예: LET으로 최적화
=LET(
평균급여, AVERAGE(C2:C11),
고급여자, FILTER(A2:D11, C2:C11>평균급여),
HSTACK(고급여자, 고급여자)
)패턴 2: 복잡한 조건문 단순화
// 다단계 조건 처리
=LET(
// 1단계: 기본 데이터 필터링
유효데이터, FILTER(A2:D11, (B2:B11<>"")*( C2:C11>0)),
// 2단계: 등급별 분류
A등급, FILTER(유효데이터, INDEX(유효데이터,0,4)="A"),
B등급, FILTER(유효데이터, INDEX(유효데이터,0,4)="B"),
C등급, FILTER(유효데이터, INDEX(유효데이터,0,4)="C"),
// 3단계: 통계 계산
A등급통계, {ROWS(A등급), AVERAGE(INDEX(A등급,0,3))},
B등급통계, {ROWS(B등급), AVERAGE(INDEX(B등급,0,3))},
C등급통계, {ROWS(C등급), AVERAGE(INDEX(C등급,0,3))},
// 4단계: 최종 결과
VSTACK(
{"등급", "인원", "평균급여"},
{"A", INDEX(A등급통계,1), INDEX(A등급통계,2)},
{"B", INDEX(B등급통계,1), INDEX(B등급통계,2)},
{"C", INDEX(C등급통계,1), INDEX(C등급통계,2)}
)
)패턴 3: 동적 배열 크기 조정
// 가변 크기 데이터 처리
=LET(
원본데이터, A2:D100, // 실제 데이터는 일부만 있음
유효행수, COUNTA(A2:A100),
실제데이터, TAKE(원본데이터, 유효행수),
// 동적 처리
IF(유효행수=0, "데이터 없음",
SORT(실제데이터, 3, -1)
)
)🧠 LAMBDA 함수 마스터리
기본 문법
=LAMBDA(변수1, [변수2], ..., 계산식)패턴 1: 재사용 가능한 비즈니스 로직
// 급여 등급 계산 함수
급여등급 = LAMBDA(급여,
IF(급여>=4500, "S급",
IF(급여>=4000, "A급",
IF(급여>=3500, "B급", "C급")))
)
// 사용 예시
=BYROW(C2:C11, 급여등급)패턴 2: 복잡한 조건부 계산
// 성과급 계산 함수 (부서별 다른 기준)
성과급계산 = LAMBDA(부서, 급여, 평가,
LET(
기본성과급, 급여 * 0.1,
부서보정, SWITCH(부서,
"영업", 1.5,
"개발", 1.3,
"마케팅", 1.2,
1.0),
평가보정, SWITCH(평가,
"A", 1.5,
"B", 1.2,
"C", 1.0,
0.8),
기본성과급 * 부서보정 * 평가보정
)
)
// 사용 예시
=MAP(B2:B11, C2:C11, D2:D11, 성과급계산)패턴 3: 동적 집계 함수
// 유연한 집계 함수 (SUM, AVERAGE, MAX, MIN 등을 동적으로)
동적집계 = LAMBDA(데이터, 집계방식,
SWITCH(집계방식,
"합계", SUM(데이터),
"평균", AVERAGE(데이터),
"최대", MAX(데이터),
"최소", MIN(데이터),
"개수", COUNT(데이터),
"중앙값", MEDIAN(데이터),
"표준편차", STDEV(데이터),
"데이터 없음"
)
)
// 부서별 다양한 통계 한번에 계산
=LET(
부서목록, UNIQUE(B2:B11),
집계방식목록, {"평균", "최대", "최소", "표준편차"},
MAP(부서목록, TRANSPOSE(집계방식목록),
LAMBDA(부서, 방식,
LET(
부서급여, FILTER(C2:C11, B2:B11=부서),
동적집계(부서급여, 방식)
)
)
)
)⚡ 성능 최적화 기법
최적화 원칙 1: 불필요한 계산 제거
나쁜 예: 중복 계산
=VSTACK(
FILTER(A2:A1000, (B2:B1000="영업")*(C2:C1000>AVERAGE(C2:C1000))),
FILTER(A2:A1000, (B2:B1000="개발")*(C2:C1000>AVERAGE(C2:C1000))),
FILTER(A2:A1000, (B2:B1000="마케팅")*(C2:C1000>AVERAGE(C2:C1000)))
)좋은 예: LET으로 최적화
=LET(
전체데이터, A2:A1000,
부서데이터, B2:B1000,
급여데이터, C2:C1000,
평균급여, AVERAGE(급여데이터),
고급여조건, 급여데이터>평균급여,
VSTACK(
FILTER(전체데이터, (부서데이터="영업")*고급여조건),
FILTER(전체데이터, (부서데이터="개발")*고급여조건),
FILTER(전체데이터, (부서데이터="마케팅")*고급여조건)
)
)최적화 원칙 2: 조기 필터링
나쁜 예: 전체 데이터를 모두 처리
=TAKE(SORT(A1:D10000, 3, -1), 10)좋은 예: 필요한 데이터만 먼저 추출
=LET(
유효데이터, FILTER(A1:D10000, C2:C10001>0),
TAKE(SORT(유효데이터, 3, -1), 10)
)최적화 원칙 3: 배열 크기 최소화
나쁜 예: 고정된 큰 범위
=UNIQUE(A1:A10000) // 실제로는 100행만 데이터가 있음좋은 예: 동적 범위 조정
=LET(
마지막행, COUNTA(A:A),
실제범위, A1:INDEX(A:A, 마지막행),
UNIQUE(실제범위)
)🎯 고급 실무 패턴
패턴 1: 동적 피벗 테이블
// 부서×평가 교차표 (자동 확장)
=LET(
원본데이터, A2:D11,
부서목록, SORT(UNIQUE(INDEX(원본데이터,0,2))),
평가목록, SORT(UNIQUE(INDEX(원본데이터,0,4))),
교차표, MAP(부서목록, TRANSPOSE(평가목록),
LAMBDA(부서, 평가,
SUMPRODUCT(
(INDEX(원본데이터,0,2)=부서)*
(INDEX(원본데이터,0,4)=평가)
)
)
),
// 합계 행/열 추가
부서별합계, BYROW(교차표, LAMBDA(행, SUM(행))),
평가별합계, BYCOL(교차표, LAMBDA(열, SUM(열))),
전체합계, SUM(교차표),
완성표, VSTACK(
HSTACK("부서", 평가목록, "합계"),
HSTACK(부서목록, HSTACK(교차표, 부서별합계)),
HSTACK("합계", 평가별합계, 전체합계)
),
완성표
)패턴 2: 이동 평균과 트렌드 분석
// 월별 실적 이동평균 (3개월)
=LET(
월별실적, A2:B13, // 월, 실적
실적값, INDEX(월별실적,0,2),
이동평균3, MAP(SEQUENCE(ROWS(월별실적)-2,1,3),
LAMBDA(종료위치,
AVERAGE(INDEX(실적값, SEQUENCE(3,1,종료위치-2)))
)
),
트렌드, MAP(TAKE(이동평균3,-ROWS(이동평균3)+1), DROP(이동평균3,1),
LAMBDA(이전, 현재,
IF(현재>이전*1.05, "상승",
IF(현재<이전*0.95, "하락", "유지"))
)
),
VSTACK(
{"월", "실적", "3개월이동평균", "트렌드"},
HSTACK(
DROP(월별실적,2),
DROP(이동평균3,0,1),
트렌드
)
)
)패턴 3: 자동 예외 감지
// 급여 이상값 자동 감지 (표준편차 기준)
=LET(
전체데이터, A2:D11,
급여데이터, INDEX(전체데이터,0,3),
평균급여, AVERAGE(급여데이터),
표준편차, STDEV(급여데이터),
상한선, 평균급여 + 2*표준편차,
하한선, 평균급여 - 2*표준편차,
이상값표시, MAP(급여데이터,
LAMBDA(급여,
IF(급여>상한선, "높음",
IF(급여<하한선, "낮음", "정상"))
)
),
이상값목록, FILTER(
HSTACK(전체데이터, 이상값표시),
이상값표시<>"정상"
),
VSTACK(
{"이름", "부서", "급여", "평가", "이상여부"},
이상값목록
)
)🏆 마스터 레벨 종합 프로젝트
프로젝트: 전사 인사 분석 대시보드
다음 모든 기능을 포함한 종합 시스템을 구축하세요:
1. 동적 검색 및 필터링
다중 조건 검색 (이름, 부서, 급여범위, 평가등급)
실시간 결과 업데이트
검색 결과 통계 자동 계산
2. 고급 분석
부서별/평가별 교차 분석
급여 분포 및 이상값 감지
TOP/BOTTOM N 분석
트렌드 분석 (전월 대비 등)
3. 자동 보고서
요약 통계 테이블
KPI 지표 계산
예외 상황 자동 알림
4. 성능 최적화
대용량 데이터 처리 가능
빠른 응답 속도
메모리 효율적 처리
평가 기준
기능성: 모든 요구 기능이 정상 동작
사용성: 직관적이고 쉬운 인터페이스
성능: 1000행 이상 데이터에서도 빠른 처리
확장성: 새로운 조건이나 분석 추가 용이
안정성: 예외 상황에서도 에러 없이 동작
🎯 최종 마스터리 체크포인트
마스터 인증 조건
다음을 모두 완성하면 배열함수 마스터 레벨:
LET 함수 숙련도
복잡한 계산을 논리적으로 분해
중간 변수를 효과적으로 활용
가독성과 성능을 동시에 고려
LAMBDA 함수 활용도
재사용 가능한 사용자 정의 함수 작성
비즈니스 로직을 함수로 추상화
MAP, BYROW, BYCOL과 효과적 조합
성능 최적화 능력
대용량 데이터 처리 가능
불필요한 계산 제거
메모리 효율적 구현
실무 적용 능력
복잡한 비즈니스 요구사항 해결
사용자 친화적 인터페이스 구현
유지보수 가능한 코드 작성
🏆 축하합니다! 이제 당신은 Microsoft 365 배열함수의 진정한 마스터입니다!