Microsoft 365 배열함수 기초 이론 (3단계)
달의이성
•조회수 197
요약
실무 시나리오별 배열함수 활용법
🎯 업무 영역별 핵심 패턴
📊 데이터 분석팀
TOP N 분석: 상위/하위 실적 추출
조건부 집계: 복합 조건으로 데이터 요약
동적 대시보드: 조건 변경시 자동 업데이트
💼 영업팀
고객 세분화: 구매이력 기반 등급 분류
실적 랭킹: 개인/팀별 성과 순위
목표 달성률: 동적 KPI 모니터링
👥 인사팀
직원 검색: 다중 조건 직원 찾기
급여 분석: 부서/직급별 통계
승진 대상자: 복합 평가 기준 적용
💡 시나리오 1: 동적 TOP N 분석
목표: 상위 N명의 실적자를 동적으로 표시
1단계: 기본 TOP N 구현
// 상위 3명의 급여자 정보
=TAKE(SORT(A1:D11, 3, -1), 3)2단계: 동적 N값 적용
// F1 셀의 값만큼 상위 실적자 표시
=TAKE(SORT(A1:D11, 3, -1), F1)3단계: 조건부 TOP N
// 개발부서 내에서 상위 2명
=TAKE(SORT(FILTER(A1:D11, B2:B11="개발"), 3, -1), 2)4단계: 완전 동적 대시보드
// F1: 부서명, G1: 표시할 인원수
=LET(
부서데이터, FILTER(A1:D11, B2:B11=F1),
정렬데이터, SORT(부서데이터, 3, -1),
TAKE(정렬데이터, G1)
)💼 실무 활용 예시
월별 매출 TOP 10 고객
부서별 성과 상위자
지역별 매장 순위
💡 시나리오 2: 고급 조건부 집계
목표: 복잡한 조건으로 데이터 요약 및 분석
1단계: 기본 조건부 집계
// 각 부서별 평균 급여
=BYROW(UNIQUE(B2:B11), LAMBDA(부서,
AVERAGE(FILTER(C2:C11, B2:B11=부서))
))2단계: 다중 조건 집계
// 평가 A 등급자들의 부서별 평균 급여
=LET(
A등급자, FILTER(A2:D11, D2:D11="A"),
부서목록, UNIQUE(INDEX(A등급자, 0, 2)),
HSTACK(
부서목록,
BYROW(부서목록, LAMBDA(부서,
AVERAGE(FILTER(INDEX(A등급자, 0, 3),
INDEX(A등급자, 0, 2)=부서))
))
)
)3단계: 동적 크로스탭 분석
// 부서 × 평가등급 교차표
=LET(
부서목록, UNIQUE(B2:B11),
평가목록, UNIQUE(D2:D11),
교차표, MAP(부서목록, TRANSPOSE(평가목록),
LAMBDA(부서, 평가,
SUMPRODUCT((B2:B11=부서)*(D2:D11=평가))
)
),
VSTACK(HSTACK("부서", 평가목록), HSTACK(부서목록, 교차표))
)💼 실무 활용 예시
제품별/지역별 매출 교차 분석
고객 세그먼트별 구매 패턴
시간대별/요일별 방문자 통계
💡 시나리오 3: 동적 검색 시스템
목표: 사용자가 조건을 입력하면 자동으로 결과 업데이트
1단계: 단일 조건 검색
// F1에 부서명 입력시 해당 부서원 자동 표시
=IF(F1="", "부서를 입력하세요",
FILTER(A1:D11, B2:B11=F1, "해당 부서 없음"))2단계: 다중 조건 검색 (AND)
// F1: 부서, G1: 최소급여
=LET(
부서조건, IF(F1="", TRUE, B2:B11=F1),
급여조건, IF(G1="", TRUE, C2:C11>=G1),
FILTER(A1:D11, 부서조건*급여조건, "조건에 맞는 데이터 없음")
)3단계: 유연한 텍스트 검색
// F1에 이름 일부만 입력해도 검색 (김 → 김철수, 김영희 등)
=LET(
검색어, F1,
검색결과, IF(검색어="", A1:D11,
FILTER(A1:D11, ISNUMBER(SEARCH(검색어, A2:A11)), "검색 결과 없음")
),
검색결과
)4단계: 고급 필터 인터페이스
// 여러 조건을 선택적으로 적용 (빈 칸은 무시)
=LET(
이름필터, IF(F1="", TRUE, ISNUMBER(SEARCH(F1, A2:A11))),
부서필터, IF(F2="", TRUE, B2:B11=F2),
급여최소, IF(F3="", TRUE, C2:C11>=F3),
급여최대, IF(F4="", TRUE, C2:C11<=F4),
평가필터, IF(F5="", TRUE, D2:D11=F5),
전체조건, 이름필터*부서필터*급여최소*급여최대*평가필터,
FILTER(A1:D11, 전체조건, "조건에 맞는 데이터가 없습니다")
)💼 실무 활용 예시
고객 관리 시스템
재고 검색 도구
프로젝트 진행 현황 조회
💡 시나리오 4: 자동 보고서 생성
목표: 데이터 변경시 자동으로 업데이트되는 보고서
1단계: 기본 요약 보고서
// 부서별 요약 통계
=LET(
부서목록, SORT(UNIQUE(B2:B11)),
요약테이블, BYROW(부서목록, LAMBDA(부서,
LET(
부서데이터, FILTER(C2:C11, B2:B11=부서),
HSTACK(
부서,
COUNT(부서데이터),
AVERAGE(부서데이터),
MAX(부서데이터),
MIN(부서데이터)
)
)
)),
VSTACK(
{"부서", "인원수", "평균급여", "최고급여", "최저급여"},
요약테이블
)
)2단계: 트렌드 분석 보고서
// 평가 등급별 분포와 급여 분석
=LET(
평가목록, SORT(UNIQUE(D2:D11)),
분석결과, BYROW(평가목록, LAMBDA(등급,
LET(
등급데이터, FILTER(A2:D11, D2:D11=등급),
인원수, ROWS(등급데이터),
평균급여, AVERAGE(INDEX(등급데이터, 0, 3)),
급여비중, 평균급여 / AVERAGE(C2:C11),
HSTACK(등급, 인원수, 평균급여, 급여비중)
)
)),
VSTACK(
{"평가등급", "인원수", "평균급여", "급여비중"},
분석결과
)
)3단계: 대시보드용 KPI 계산
// 핵심 지표 자동 계산
=LET(
전체인원, COUNTA(A2:A11),
평균급여, AVERAGE(C2:C11),
A등급비율, COUNTIF(D2:D11, "A") / 전체인원,
최고부서, INDEX(
BYROW(UNIQUE(B2:B11), LAMBDA(부서,
HSTACK(부서, AVERAGE(FILTER(C2:C11, B2:B11=부서)))
)),
MATCH(MAX(BYROW(UNIQUE(B2:B11), LAMBDA(부서,
AVERAGE(FILTER(C2:C11, B2:B11=부서))
))),
INDEX(BYROW(UNIQUE(B2:B11), LAMBDA(부서,
AVERAGE(FILTER(C2:C11, B2:B11=부서))
)), 0, 2), 0), 1
),
VSTACK(
{"지표", "값"},
{"전체 인원", 전체인원},
{"평균 급여", 평균급여},
{"A등급 비율", A등급비율},
{"최고 급여 부서", 최고부서}
)
)💼 실무 활용 예시
월간/분기별 자동 보고서
실시간 KPI 대시보드
성과 분석 리포트
✅ 3단계 체크포인트
종합 실습 과제
다음 요구사항을 모두 충족하는 동적 분석 시스템을 만드세요:
검색 인터페이스
부서별 필터링
급여 범위 설정
평가 등급 선택
자동 분석 결과
조건에 맞는 직원 목록
해당 그룹 통계 (평균, 최고, 최저)
TOP 3 순위 표시
시각적 요약
부서별 분포 테이블
평가별 현황 요약
성공 기준
조건 변경시 모든 결과가 자동 업데이트
에러 없이 모든 시나리오 동작
실무에서 바로 활용 가능한 수준
🎯 다음 단계: 고급 기법과 최적화 방법 학습