Ⅰ. 엑셀 실무 중·고급 기능 교육 강의안 (2시간)
1. 전체 구성 개요
시간
주제
주요 내용
실습 파일
0:00~0:10
오리엔테이션
교육 목표 및 흐름 안내
-
0:10~0:40
데이터 정리 및 고급 필터
조건부 필터, 고급 필터, 중복 제거
예시1_고급필터.xlsx
0:40~1:10
함수 실무 활용
IF, VLOOKUP, INDEX/MATCH, TEXT, IFERROR 등예시2_함수실습.xlsx
1:10~1:30
피벗 테이블 분석
피벗 테이블 생성, 그룹화, 슬라이서 활용
예시3_피벗테이블.xlsx
1:30~1:50
조건부 서식 & 데이터 유효성 검사
시각적 강조, 입력 제한 설정
예시4_조건부서식.xlsx
1:50~2:00
Q&A 및 마무리
실무 적용 팁 공유, 질의응답
-
2. 실습 중심 강의 흐름
1. 고급 필터 및 데이터 정리 (예시1_고급필터.xlsx)
목표 : 실무에서 자주 쓰이는 조건 기반 필터링과 정리 방법 익히기실습 내용 :
고급 필터로 다중 조건 검색
중복 데이터 제거
텍스트 나누기(예: 이름/부서 분리)
정렬 및 사용자 지정 정렬
2. 함수 실무 활용 (예시2_함수실습.xlsx)
목표 : 실무에서 자주 쓰이는 함수들을 조합하여 문제 해결실습 내용 :
IF 함수로 조건 분기 처리VLOOKUP vs INDEX/MATCH 비교 실습TEXT 함수로 날짜/숫자 포맷 변경IFERROR로 오류 처리실무 시나리오 기반 계산 예제
3. 피벗 테이블 분석 (예시3_피벗테이블.xlsx)
목표 : 대량 데이터를 요약하고 인사이트 도출실습 내용 :
피벗 테이블 생성 및 필드 설정
날짜 그룹화 및 사용자 지정 계산
슬라이서 삽입으로 인터랙티브 분석
피벗 차트 생성
4. 조건부 서식 & 데이터 유효성 검사 (예시4_조건부서식.xlsx)
목표 : 시각적 강조 및 입력 오류 방지실습 내용 :
조건부 서식으로 이상값 강조
색상 막대, 아이콘 집합 활용
데이터 유효성 검사로 입력 제한 (예: 드롭다운 목록)
오류 메시지 설정
3. 교육 마무리
실무 적용 팁 공유 (예: 자동화, 템플릿 활용)
참가자 질문 응답
다음 단계 추천: 매크로, Power Query, VBA 등
예시1 고급 필터와 데이터 정리 사회복지대상자 명부
수혜자ID,성명,주민등록번호,생년월일,성별,구,동,소득구간,가구원수,서비스코드,서비스명,등록일,최근사정일,활성여부
B001,김하늘,900101 -1234567 ,1990 -01 -01 ,남,북구,운암동,차상위,3 ,S001,방문요양,2024 -01 -10 ,2025 -07 -15 ,Y
B002,이소라,420315 -2234567 ,1942 -03 -15 ,여,남구,주월동,기초생활,1 ,S002,도시락,2023 -11 -22 ,2024 -12 -30 ,Y
B003,박민수,780602 -1239876 ,1978 -06 -02 ,남,서구,화정동,차상위,4 ,S003,주거개선,2024 -05 -05 ,2025 -01 -20 ,N
B004,최지민,360910 -2231234 ,1936 -09 -10 ,여,광산구,수완동,기초생활,2 ,S001,방문요양,2024 -02 -14 ,2025 -05 -01 ,Y
B005,정수빈,500212 -1232222 ,1950 -02 -12 ,여,동구,산수동,차상위,1 ,S004,긴급의료,2025 -01 -03 ,2025 -06 -30 ,Y
B006,김하늘,900101 -1234567 ,1990 -01 -01 ,남,북구,운암동,차상위,3 ,S005,상담,2024 -07 -01 ,2025 -07 -15 ,Y
B007,오세훈,820909 -1235555 ,1982 -09 -09 ,남,광산구,신가동,일반,2 ,S002,도시락,2024 -10 -10 ,2025 -03 -31 ,Y
B008,한예린,470725 -2236666 ,1947 -07 -25 ,여,남구,진월동,기초생활,1 ,S001,방문요양,2024 -06 -18 ,2025 -08 -01 ,Y
목표
목표: 중복 식별/제거, 텍스트 나누기, 사용자 지정 정렬, 고급 필터로 다중 조건 추출.
실습 절차
중복 검사/제거
범위 선택: A1:N9.중복 기준: “주민등록번호”. 데이터 탭 → 중복된 항목 제거 → “주민등록번호”만 체크 → 확인.확인: B001과 B006은 동일 주민번호. 하나만 남는지 확인.
텍스트 나누기 성명 분리
목표: ‘성명’을 ‘성’과 ‘이름’으로 분리.방법: B열 오른쪽에 열 2개 삽입 → B열(성명) 선택 → 데이터 → 텍스트 나누기 → 고정 너비 → 첫 글자 뒤에 분할선 → 완료.대안 함수: 성: =LEFT(B2,1) / 이름: =RIGHT(B2,LEN(B2)-1)
사용자 지정 정렬
목표: 소득구간을 실무 우선순위(기초생활 > 차상위 > 일반)로 정렬.방법: 데이터 → 정렬 → ‘소득구간’ → ‘사용자 지정 목록’ → 새 목록에 “기초생활,차상위,일반” 입력 → 오름차순 정렬.
고급 필터 다중 조건 추출
조건: 구=남구 또는 광산구, 소득구간=기초생활, 활성여부=Y, 최근사정일이 2025-06-30 이전.조건 영역 작성: 시트 상단 빈 영역에 아래와 같이 입력.
H1: 구, I1: 소득구간, J1: 활성여부, K1: 최근사정일
H2: 남구, I2: 기초생활, J2: Y, K2: <2025-06-30
H3: 광산구, I3: 기초생활, J3: Y, K3: <2025-06-30
실행: 데이터 → 고급 → 목록 범위 A1:N? → 조건 범위 H1:K3 → ‘다른 위치에 복사’ 선택 → 복사 위치 지정.
확인과 체크포인트
중복 제거 결과: 주민등록번호 기준으로 B006 행 제거.조건 추출 예상: 광산구/남구 + 기초생활 + Y + 최근사정일 < 2025-06-30에 해당하는 대상만 결과에 나타남.함수 대체 팁: 고급 필터가 어려우면 ‘필터’ + ‘검색’ + ‘정렬’ 조합으로 동일 결과 도출 가능.
예시2 함수 실무 사회복지 급여·서비스 조회와 판정
수혜자ID,성명,구,동,소득구간,가구원수,서비스코드,최근사정일,월소득,나이
B001,김하늘,북구,운암동,차상위,3 ,S001,2025 -07 -15 ,2100000 ,35
B002,이소라,남구,주월동,기초생활,1 ,S002,2024 -12 -30 ,500000 ,83
B003,박민수,서구,화정동,차상위,4 ,S003,2025 -01 -20 ,2500000 ,47
B004,최지민,광산구,수완동,기초생활,2 ,S001,2025 -05 -01 ,700000 ,89
B005,정수빈,동구,산수동,차상위,1 ,S004,2025 -06 -30 ,900000 ,75
B007,오세훈,광산구,신가동,일반,2 ,S002,2025 -03 -31 ,2800000 ,43
B008,한예린,남구,진월동,기초생활,1 ,S001,2025 -08 -01 ,600000 ,78
참고 테이블 서비스 코드표 별도 시트 또는 우측에 배치
서비스코드,서비스명,월한도,우선순위
S001,방문요양,600000 ,1
S002,도시락,200000 ,2
S003,주거개선,1500000 ,3
S004,긴급의료,800000 ,1
S005,상담,100000 ,3
목표
목표: 코드 기반 조회, 오류 처리, 자격 판정, 날짜·텍스트 포맷, 대안 조회 함수 비교.
실습 절차
XLOOKUP 또는 INDEX/MATCH로 서비스명·한도 조회
서비스명: =XLOOKUP(G2,코드표!A:A,코드표!B:B,"없음")월한도: =XLOOKUP(G2,코드표!A:A,코드표!C:C,0)대안 INDEX/MATCH: =INDEX(코드표!B:B,MATCH(G2,코드표!A:A,0))
IF와 AND/OR로 자격 판정
규칙: 소득구간=기초생활 또는 차상위, 나이≥65, 활성화 후보.공식: =IF(AND(OR(E2="기초생활",E2="차상위"),J2>=65),"대상","재검토")
IFERROR로 안전한 조회
예시: =IFERROR(XLOOKUP(G2,코드표!A:A,코드표!B:B),"코드확인필요")
TEXT/날짜 함수로 보고서 표준화
월 표기: =TEXT(H2,"yyyy-mm")사정만료 경고일 365일 초과: =IF(TODAY()-H2>365,"만료","정상")
소득 대비 한도 초과여부 계산
비율: =I2/L2판정: =IF(I2>L2,"한도초과","한도내")
확인과 체크포인트
XLOOKUP 성공: 서비스코드별 서비스명/월한도 정확히 매칭.판정 결과: 65세 이상 + 소득구간 충족 시 “대상”으로 표기.오류 처리: 코드 미등록 시 “코드확인필요”로 표시되어야 함.
예시3 피벗 테이블 분석 수혜 현황 요약
수혜자ID,성명,구,동,서비스코드,서비스명,월한도,지급월,지급금액,연도,분기
B001,김하늘,북구,운암동,S001,방문요양,600000 ,2025 -01 ,580000 ,2025 ,Q1
B001,김하늘,북구,운암동,S001,방문요양,600000 ,2025 -02 ,600000 ,2025 ,Q1
B002,이소라,남구,주월동,S002,도시락,200000 ,2025 -01 ,180000 ,2025 ,Q1
B004,최지민,광산구,수완동,S001,방문요양,600000 ,2025 -03 ,600000 ,2025 ,Q1
B005,정수빈,동구,산수동,S004,긴급의료,800000 ,2025 -02 ,500000 ,2025 ,Q1
B007,오세훈,광산구,신가동,S002,도시락,200000 ,2025 -04 ,200000 ,2025 ,Q2
B008,한예린,남구,진월동,S001,방문요양,600000 ,2025 -05 ,600000 ,2025 ,Q2
B003,박민수,서구,화정동,S003,주거개선,1500000 ,2025 -06 ,1200000 ,2025 ,Q2
목표
목표: 지역·서비스별 요약, 분기 분석, 한도 대비 사용률 파악, 대시보드 요소 추가.
실습 절차
피벗 테이블 생성
범위 선택: A1:K9 → 삽입 → 피벗 테이블 → 새 워크시트.필드 배치: 행: 구, 서비스명 / 값: 지급금액 합계.
분기별 분석
열에 배치: 열: 분기.값 형식: 지급금액 합계 → 값 필드 설정 → 표시형식 ‘통화’ 0자리.
서비스별 한도 대비 사용률
데이터 모델 또는 계산 필드 사용: 지급금액 합계 / 월한도 평균.방법: 피벗 값 영역에 월한도 → 요약값 ‘평균’
상위 서비스 파악(서비스명에서 상위 3개)
행 레이블 클릭 → 값 필터: → 상위10 → 상위, 3, 항목, 지급금액 합계 기준.상세설명
피벗 테이블 — 상위 서비스 파악 (상세 가이드)
4. 상위 서비스 파악 — 매우 자세한 단계 (초보자용)
이 문서는 피벗 테이블에서 ‘서비스명’에 대해 지급금액 합계 기준으로 상위 3개를 표시하는 방법 을 단계별로 설명합니다. 또한 전체 기준 상위 3개 와 구별(부모 항목별) 상위 3개 의 차이와 각각을 구현하는 방법도 포함합니다.
전제 확인 (먼저 확인할 것)
피벗 테이블의 행(Row) 영역에 구와 서비스명이 있고(구가 먼저, 서비스명이 그 아래에 있는 계층 구조).
값(Values) 영역에 지급금액이 있고 요약 방식은 합계(Sum) 로 되어 있어야 합니다.
A. 기본 — 서비스명 옆 드롭다운으로 상위 3개 선택 (단계별)
피벗 테이블에서 "서비스명" 레이블의 오른쪽에 있는 작은 화살표(▼) 를 클릭합니다. (화살표가 보이지 않으면 피벗 테이블이 선택된 상태인지 확인하세요.)
나타나는 메뉴에서 "값 필터" (Value Filters)를 선택합니다. 서브메뉴가 나타나면 "상위 10개..." 또는 "상위 10개 항목"을 클릭하세요.
"상위 10개 항목 필터" 대화상자가 열리면 다음을 설정합니다:
첫번째 드롭다운: 상위(Top) 선택
숫자 입력칸: 10 → 3 으로 변경
항목 기준: 항목(Items) 선택 (백분율이 아님)
기준 드롭다운: 합계: 지급금액 (Sum of 지급금액 / 지급금액 합계) 선택
설정 완료 후 [확인] 클릭.
결과: 피벗 테이블의 서비스명에 대해 지급금액 합계 기준 상위 3개 서비스만 표시 됩니다.
주의: 위 방식은 피벗의 계층 구조(예: 구 → 서비스명)에 따라 동작이 달라집니다. 아래 섹션을 꼭 확인하세요.
B. 중요한 차이 — 전체 기준 Top 3 vs 구(부모)별 Top 3
동일한 "상위 3개"라는 표현이라도 피벗 구조에 따라 결과가 다릅니다.
구 → 서비스명(계층 구조)일 경우 : 위에서 설명한 값 필터 적용은 각 ‘구’별로 상위 3개 를 보여줍니다. 즉, "구 A의 상위 3개 서비스", "구 B의 상위 3개 서비스"처럼 각 구마다 상위 3개가 표시됩니다.전체(전 구 합계) 기준 Top 3를 원할 경우 에는 다음 방법을 사용하세요:
권장 방법(간단) : 새 피벗 테이블을 만듭니다. 서비스명만 행에 넣고, 지급금액을 값에 넣은 뒤 값 필터 → 상위 3개 적용. 이렇게 하면 모든 구를 합산한 결과 기준 Top3가 나옵니다.기존 피벗에서 강제로 전체 기준 적용 : 서비스명을 구보다 위로 끌어올려 최상위 필드로 변경하면 서비스 수준에서 필터가 적용됩니다. 단, 표 구조가 바뀌므로 주의가 필요합니다.
C. 정렬해서 보기 (보기 편하게)
상위 3개가 적용된 후, 숫자 높은 순서로 정렬하려면 지급금액 합계 셀에서 오른쪽 클릭 → 정렬(Sort) → 내림차순(내림차순: 합계: 지급금액) 선택.
또는 서비스명 헤더 드롭다운에서 정렬 옵션을 사용해도 됩니다.
D. 필터 제거(원래 상태로 되돌리기)
서비스명 옆 화살표(▼) 클릭 → 메뉴에서 "모두 선택(모든 항목 표시)" 체크 → [확인] 클릭.또는 드롭다운 메뉴에서 ‘필터 지우기’ 항목을 선택하면 필터가 해제됩니다.
E. 추가 팁 & 주의사항
다른 필터(슬라이서, 타임라인, 보고서 필터 등)를 이미 사용 중이면 상위 3개는 현재 적용된 필터 상태 에 따라 달라집니다. 예: 연도 필터가 2024로 설정되어 있으면 2024 데이터 기준으로 Top3가 적용됩니다.
동점 처리 : 동일한 합계값(동점)이 있으면 Excel 동작에 따라 동점 항목이 포함되거나 순위가 달라질 수 있습니다. 동점이 많은 경우 별도 정렬/검토를 권장합니다.여러 값 필드(예: 지급금액, 건수 등)가 값 영역에 있을 경우, 상위 필터 대화상자의 기준 드롭다운에서 반드시 ‘합계: 지급금액’ 을 선택하세요.
결과를 기존의 구별·분기별 피벗에 적용하려면: 전체 기준 Top3를 구한 뒤 상위 3개의 서비스명을 복사해 기존 피벗의 서비스명 슬라이서(또는 보고서 필터)에 수동으로 선택하세요.
F. 예시 (짧게)
데이터 합계 예시:
서비스A ₩1,200
서비스B ₩900
서비스C ₩700
서비스D ₩300
→ 전체 기준 Top3 적용 시: A, B, C 만 표시됩니다.
→ 구 → 서비스 구조에서 값 필터 적용 시: 각 구별로 상위 3개가 개별적으로 계산되어 표시됩니다.
슬라이서/타임라인
슬라이서: 구, 서비스명 추가.타임라인: 연도 필드로 타임라인 추가하여 연·분기 탭 이동.상세설명
5. 슬라이서 / 타임라인 추가 & 7. 피벗 차트 작성 — 자세한 단계별 가이드
이 문서는 슬라이서(구·서비스명) 과 타임라인(연도/분기/월) 을 피벗 테이블에 추가하는 방법과, 이를 활용한 피벗 차트(누적 세로막대형) 작성 및 '값+사용률(%)'을 함께 표시하는 실전 팁을 자세히 정리한 문서입니다.
사전 확인 & 버전 정보
슬라이서 는 Excel 2010 이상에서 피벗 테이블과 함께 사용 가능합니다.타임라인 은 Excel 2013 이상에서 지원되며, 필수 조건은 '날짜' 형식의 필드(실제 Date 타입)가 있어야 합니다.피벗 차트/데이터 레이블의 일부 고급 옵션(예: Value From Cells )은 Excel 2013 이상 또는 Office 365에서 더 편리하게 사용할 수 있습니다.
5-A. 슬라이서 추가 — 단계별
피벗 테이블 안의 아무 셀이나 선택합니다.
리본 메뉴에서 삽입 → 슬라이서 를 클릭합니다.
표시되는 대화상자에서 필드 목록 중 구, 서비스명을 체크한 뒤 [확인]을 클릭합니다.
시트에 버튼 형태의 슬라이서가 각각 생성됩니다. 드래그하여 위치를 조정하고, 크기를 변경하세요.
기본 동작: 슬라이서의 버튼을 클릭하면 해당 값으로 피벗이 필터링됩니다. Ctrl+클릭으로 다중 선택, Shift+클릭으로 연속 선택이 가능합니다.
슬라이서 세부 설정 (실무 팁)
슬라이서 스타일 변경: 슬라이서를 선택하면 상단에 슬라이서 도구 > 옵션 탭이 나타납니다. 미리 정의된 스타일을 선택하거나, 버튼 크기(열 수, 버튼 높이) 등을 조정할 수 있습니다.다중 피벗 연결: 여러 피벗 테이블에 동일한 슬라이서로 동기화하려면 슬라이서 도구 > 옵션 > 보고서 연결(또는 피벗테이블 연결) 을 클릭하고 연결할 피벗 체크박스를 선택합니다.단일 선택으로 제한: 슬라이서 도구 → 슬라이서 설정 → 다중 항목 선택 허용 옵션의 체크를 해제하면 단일 선택 모드로 고정됩니다.슬라이서 초기화(모든 해제): 슬라이서 우상단의 필터 아이콘(지우기 버튼)을 클릭하면 필터가 초기화됩니다.
5-B. 타임라인 추가 — 단계별
피벗 테이블의 아무 셀을 선택합니다.
리본에서 삽입 → 타임라인 을 클릭합니다.
날짜 필드(예: 연도 또는 실제 날짜 열)를 선택하고 [확인]을 클릭합니다.
시트에 타임라인 컨트롤(스크롤 막대 형태)이 생깁니다. 막대를 드래그하여 원하는 기간을 선택하세요.
타임라인 레벨 전환: 타임라인의 오른쪽 상단 또는 컨트롤에 있는 레벨 선택기에서 연도(Years) / 분기(Quarters) / 월(Months) / 일(Days) 을 선택하여 표시 단위를 바꿀 수 있습니다.
타임라인 세부 설정
날짜 형식 확인: 원본 데이터의 날짜 열이 텍스트로 되어 있으면 타임라인이 동작하지 않습니다. 이 경우 날짜 열을 선택 → 데이터 → 텍스트 나누기 또는 =DATEVALUE() 등을 사용해 Date 형식으로 변환하세요.여러 피벗에 연결: 타임라인을 클릭 → 상단의 타임라인 도구 → 옵션 → 보고서 연결 에서 다른 피벗들을 체크하면 동일한 기간으로 동기화됩니다.타임라인 크기 조정: 컨트롤을 좌우로 늘리면 한 번에 보여지는 기간 범위를 넓힐 수 있습니다.
피벗 차트
차트: 누적 세로막대형으로 분기별 지역 합계 비교.서식: 데이터 레이블 백분율 추가로 사용률 함께 표시.상세설명
6-A. 피벗 차트 기본 작성 (누적 세로막대형)
피벗 테이블의 아무 셀을 선택합니다.
리본에서 삽입 → 피벗차트(또는 차트) → 세로 막대형 → 누적 세로 막대형(또는 100% 누적) 을 선택합니다.
차트가 바로 생성됩니다. 차트는 피벗 테이블과 링크되어 있어 피벗가 변경되면 차트도 자동 갱신됩니다.
차트 종류: 일반 누적(스택) 막대는 분기별/구별 합계를 누적해서 보여주기에 적합합니다. 비율(%)을 강조하려면 100% 누적 세로막대형 을 사용할 수 있습니다.
6-B. 데이터 레이블: '값(통화)' + '사용률(%)' 같이 표시하는 방법
Excel에서는 기본적으로 하나의 데이터 레이블에 서로 다른 두 값을 섞어 자동으로 보여주지 않습니다. 아래 세 가지 방법 중에서 상황에 맞게 선택하세요.
방법 1 — 계산필드(사용률) 추가 후 콤보 차트(컬럼 + 라인)로 표시 (추천)
피벗 테이블에서 계산필드(또는 별도 값 필드) 로 '사용률'을 추가(예: 지급금액 합계 ÷ 월한도 평균).
피벗차트를 그린 뒤 차트에서 우클릭 → 차트 종류 변경 → 콤보(Combo) 를 선택합니다.
지급금액 시리즈는 '누적 세로막대(또는 묶음 세로막대)'로 두고, '사용률' 시리즈는 선(Line) 으로 변경하고 보조 축(Secondary Axis) 을 체크합니다.
보조 축(오른쪽 축)을 선택 → 우클릭 → 축 서식 → 숫자 → 백분율 로 설정합니다.
차트 요소(+ 아이콘) → 데이터 레이블을 켜고, 원하는 시리즈에 대해 각각 레이블을 추가합니다. (컬럼에는 '값', 라인에는 '값 또는 백분율')
이 방법의 장점: 값(통화)은 왼쪽 축으로, 사용률(%)은 보조 축으로 분리해 보여주므로 시각적으로 해석하기 쉽습니다. 피벗와 연동되며 갱신도 자동입니다.
방법 2 — 보조 셀(helper range)에 '₩값 (XX%)' 텍스트 생성 후 데이터 레이블 'Value From Cells' 사용
피벗 표 옆에 보조 영역을 만들어 각 데이터 포인트별로 텍스트 라벨을 만들어 차트의 데이터 레이블로 사용하는 고급 방식입니다. 특히 '₩120,000 (80%)'처럼 하나의 레이블에 둘을 합쳐 보여주고 싶을 때 유용합니다.
피벗 테이블의 결과 셀들을 참조하여 아래와 같은 문자열을 만드는 보조 열을 만듭니다. 예: =TEXT(GETPIVOTDATA("지급금액", $A$3, "구", $A5, "서비스명", $B5), "₩#,##0") & " (" & TEXT(GETPIVOTDATA("사용률", $A$3, "구", $A5, "서비스명", $B5), "0%") & ")"
※ $A$3 은 피벗 테이블의 아무 셀(또는 피벗 테이블의 좌측 상단 셀)을 가리킵니다. GETPIVOTDATA는 피벗의 값을 안정적으로 참조합니다.
차트에서 데이터 레이블을 켠 뒤, 레이블 옵션에서 Value From Cells 을 선택하고 위 보조 범위를 지정합니다.
기본 '값' 레이블은 숨기고(체크 해제) 'Value From Cells' 만 보이도록 설정하면, 각 막대 위에 복합 라벨이 표시됩니다.
주의: 보조 범위는 피벗 구조가 바뀌면 위치가 변할 수 있으므로 GETPIVOTDATA나 동적 참조를 잘 구성해야 합니다. 또한 이 방법은 피벗 차트와 보조 범위가 함께 이동하면 깨지기 쉬우니 주의하세요.
방법 3 — VBA를 이용한 커스텀 라벨(고급)
반복적으로 라벨 형식을 자동화해야 하거나, 복잡한 형식(통화 문자, 괄호 등)을 매번 적용해야 한다면 간단한 VBA 스크립트를 작성해 데이터 레이블을 갱신할 수 있습니다. 다만 매크로 보안, 배포 환경(예: 다른 사용자) 등을 고려해야 합니다.
6-C. 데이터 레이블 추가·서식(실제 단계)
차트를 선택 → 오른쪽 상단의 차트 요소(+) 아이콘 클릭 → 데이터 레이블 체크.
추가 옵션: 레이블 옆의 화살표 → 추가 옵션... 으로 들어가면 위치(Inside End, Outside End 등)를 세부 설정할 수 있습니다.
특정 시리즈 레이블만 선택하려면, 레이블을 한 번 클릭(전체 선택) → 다시 클릭(해당 시리즈의 특정 레이블 선택) 후 우클릭하여 레이블 서식 을 선택합니다.
라인 시리즈의 경우 레이블을 추가하고 숫자 형식(백분율)을 적용하려면, 레이블 선택 → 레이블 서식 → 숫자 → 백분율 로 설정합니다.
차트 꾸미기 팁
차트 제목: 제목을 명확히 작성(예: "분기별 지역 지급금액 및 사용률(%)").축 서식: 가로축(범주)은 분기/구 표시를 명확히, 세로축(값)은 통화로 설정: 축 선택 → 축 서식 → 숫자 → 통화 → 소수 0자리.범례 위치: 범례를 상단 또는 오른쪽으로 두어 가독성 확보.색상/스타일: 차트 도구 → 디자인 → 차트 스타일에서 깔끔한 색상 조합을 선택하거나, 특정 시리즈의 색을 클릭해서 변경하세요.템플릿 저장: 자주 사용하는 형식이면 차트 디자인 → 템플릿으로 저장 을 이용해 재사용하세요.
문제 해결 & 체크리스트
문제 원인 및 해결법 타임라인이 보이지 않음 Excel 버전이 2013 미만이거나 날짜 필드가 올바른 Date 형식이 아님. 날짜 형식으로 변환 후 다시 시도. 슬라이서의 '보고서 연결'이 회색으로 비활성 슬라이서가 선택되지 않았거나, 연결하려는 피벗과 슬라이서의 데이터 소스가 다름. 같은 통합 데이터 소스를 사용하는지 확인. 차트에 백분율이 안보임 100% 누적 차트를 쓰거나, 사용률을 보조 축에 추가한 뒤 라인에 데이터 레이블(백분율)을 별도로 추가하세요. 'Value From Cells'가 안보임 Excel 버전 제한일 수 있음(Office 365/Excel 2013 이상 권장). 또는 레이블 대상이 적절한 차트 시리즈가 아닐 수 있음(일반적으로 막대/컬럼 시리즈에서 사용).
마무리 요약
슬라이서와 타임라인으로 사용자가 원하는 범위를 직관적으로 필터링할 수 있습니다.
피벗차트(누적 막대)는 분기별·구별 합계를 시각화하기 적합합니다.
값(통화)과 사용률(%)을 함께 표시하려면 '콤보 차트(컬럼+라인, 보조축)' 방식이 가장 실용적입니다. 더 정교한 레이블(예: "₩120,000 (80%)")은 보조 셀을 만들어 'Value From Cells'로 지정하는 방법을 권장합니다.
확인과 체크포인트
피벗 합계: 원본 합과 일치.사용률: 100% 초과 시 한도 초과 가능성. 관리 포인트 표시.필터 조합: 구·서비스 슬라이서로 즉시 비교 가능해야 함.
예시4 조건부 서식과 데이터 유효성 사례관리 모니터링
수혜자ID,성명,위험등급,위험점수,최근사정일,다음방문예정,방문상태,담당자,서비스코드,비고
B001,김하늘,중,45 ,2025 -07 -15 ,2025 -09 -10 ,예정,A-01 ,S001,
B002,이소라,상,82 ,2024 -12 -30 ,2025 -01 -10 ,완료,A-02 ,S002,겨울철 추가지원
B003,박민수,중,60 ,2025 -01 -20 ,2025 -02 -10 ,미완료,A-03 ,S003,
B004,최지민,상,88 ,2025 -05 -01 ,2025 -05 -20 ,완료,A-01 ,S001,의료 연계
B005,정수빈,중,55 ,2025 -06 -30 ,2025 -07 -05 ,미완료,A-02 ,S004,복약 확인 필요
B007,오세훈,하,30 ,2025 -03 -31 ,2025 -04 -05 ,완료,A-04 ,S002,
B008,한예린,상,90 ,2025 -08 -01 ,2025 -08 -10 ,예정,A-03 ,S001,폭염 주의
목표
목표: 위험 신호를 색상으로 강조, 입력값 표준화(드롭다운·형식 제한), 기한 경과 경고.
실습 절차
색상 스케일·아이콘 집합으로 위험 시각화
위험점수 색상: D열 선택 → 조건부 서식 → 색조합(빨강?노랑?초록) → 높은 값=빨강.아이콘 집합: 조건부 서식 → 아이콘 → 3색 신호등 → 규칙 편집에서 임계값(>=80 빨강, >=60 노랑, 그 외 초록).
텍스트 규칙 위험등급 강조
규칙: 위험등급=“상”이면 행 전체 붉은 배경.방법: A2:J8 선택 → 조건부 서식 → 새 규칙 → 수식 사용 → =$C2="상" → 서식 채우기 빨강(연한) + 굵은 글씨.
기한 경과 경고
규칙: 다음방문예정 < 오늘, 방문상태 ≠ “완료”.수식: =AND($F2"완료") → 글자색 빨강, 굵게.
데이터 유효성 검사로 드롭다운
목록: 방문상태에 “예정,완료,미완료”.방법: G2:G8 선택 → 데이터 → 데이터 유효성 → ‘목록’ → 원본에 “예정,완료,미완료”.입력 메시지/오류 경고: “방문상태는 ‘예정/완료/미완료’만 입력하세요.”
서비스코드 형식 제한
규칙: ‘S’로 시작하고 3자리 숫자. 사용자 지정 → =AND(LEFT($I2,1)="S",LEN($I2)=4,ISNUMBER(VALUE(RIGHT($I2,3))))오류 경고: “형식 예: S001”
담당자 코드 패턴 검사
규칙: 알파벳-숫자 두 자리(예: A-01).수식: =AND(LEN($H2)=4,ISNUMBER(VALUE(RIGHT($H2,2))),MID($H2,2,1)="-")상세설명
1. 색상 스케일 · 아이콘 집합으로 위험 시각화
위험점수(D열)를 시각적으로 한눈에 파악하려면 색상 스케일 또는 아이콘 집합 을 사용합니다.
A. 색상 스케일 (Color Scale)
D열의 점수 영역을 선택합니다. 예: D2:D100 (데이터 범위에 맞게 조정).
리본 메뉴에서 홈 → 조건부 서식 → 색조합(색상표) 을 클릭합니다.
3색(또는 2색) 스케일을 선택하고, 색을 원하는 조합으로 변경합니다. 권장: 높은 값 = 빨간색, 중간 = 노랑, 낮음 = 초록.
세부 설정을 바꾸려면 조건부 서식 → 규칙 관리 → 규칙 편집 → 형식 규칙 편집 에서 최소/최대/중간 값을 '숫자'로 고정하고 임계값을 직접 입력합니다. 예: 최소 = 0, 중간 = 60, 최대 = 100.
컬러 스케일은 상대값(백분위) 기준이 기본입니다. 특정 임계값을 고정하려면 규칙 편집에서 '숫자'로 지정하세요.
B. 아이콘 집합 (3색 신호등)
같은 범위 D2:D100를 선택합니다.
홈 → 조건부 서식 → 아이콘 집합 → 3색 신호등 을 선택합니다.아이콘 집합 규칙을 바꾸려면 조건부 서식 → 규칙 관리 → 규칙 편집 을 클릭합니다.
규칙 편집에서 '아이콘 형식'을 확인하고, 아래와 같이 임계값을 설정합니다:
빨강(위험): >= 80
노랑(주의): >= 60 (그리고 <80)
초록(정상): 나머지 (<60)
옵션: '아이콘만 표시'를 체크하면 숫자는 숨고 아이콘만 보입니다. 숫자와 아이콘을 같이 보려면 체크 해제하세요.
아이콘 규칙은 '백분율'/'숫자'/'수식' 등으로 지정할 수 있습니다. 데이터 범위의 스코어가 고정된 절대값(0~100)이면 '숫자'로 설정하세요.
2. 텍스트 규칙으로 위험등급(상)인 행 전체 강조
위험등급 열(C열)에 '상'이 입력된 행은 전체 행을 붉은 배경으로 강조합니다.
전체 데이터 영역을 선택합니다. 예: A2:J100 (열 끝까지). 꼭 헤더 행(A1:J1)은 제외한 데이터 영역을 선택하세요.
리본에서 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정 을 선택합니다.
수식 입력 칸에 다음을 입력합니다:
= $C2 = "상"
설명: 열 C는 '위험등급'이고, '$C2'에서 행 번호는 선택 영역의 첫 행 번호로 맞춰야 합니다. '$'는 열을 고정하겠다는 뜻입니다.
서식 버튼을 눌러 채우기 색을 연한 빨강(예: 연분홍)으로 선택하고, 글꼴을 굵게 로 지정합니다. 확인.확인하면 '상' 등급인 모든 행이 강조됩니다.
만약 데이터가 Excel 테이블(삽입 → 표)로 구성되어 있다면, 규칙을 표의 구조(예: =[$C]="상")로 작성하면 행 추가 시 자동으로 규칙이 적용됩니다.
3. 기한 경과(다음방문예정 < 오늘) 경고
다음 방문 예정일이 오늘보다 이전(과거)이고 방문상태가 '완료'가 아니면 글자색을 빨강/굵게 표시합니다.
데이터 영역 선택: 예: A2:J100.
홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정 선택.수식 입력 칸에 아래 수식을 입력합니다 (다음방문예정이 F열, 방문상태가 G열인 경우):
=AND($F2 < TODAY(), $G2 <> "완료")
설명: $F2는 날짜(실제 Excel Date 타입)여야 합니다. 텍스트 날짜인 경우 DATEVALUE로 변환하세요.
서식에서 글꼴 색을 빨강 , 굵게 설정합니다. (원하면 배경색도 옅은 노랑 등으로 지정 가능)
확인하면 조건에 해당하는 행의 텍스트가 강조됩니다.
만약 '완료' 대신 '완료(수정)' 등 변형 텍스트가 섞여 있으면, 수식을 $G2 <> "완료" 대신 NOT(ISNUMBER(SEARCH("완료", $G2))) 같이 부분 문자열 검색을 사용할 수 있습니다.
4. 데이터 유효성 검사: 방문상태 드롭다운
방문상태 열(G열)에 입력 가능한 값(예정/완료/미완료)을 드롭다운으로 제한합니다.
대상 범위 선택: G2:G100 (필요한 범위)
리본에서 데이터 → 데이터 유효성 검사 클릭.
허용(Allow)에서 목록(List) 을 선택하고, 원본(Source)에 다음을 입력합니다:
예정,완료,미완료
입력 메시지 탭에서 제목/메시지 입력(선택): 예: 방문상태 입력 / 방문상태는 ‘예정/완료/미완료’만 선택하세요.
확인과 체크포인트
시각화: ‘상’ 등급 행이 한눈에 보이고, 기한 경과 건은 빨강으로 경고.입력 오류 방지: 드롭다운 외 값 입력 시 경고 표시.
실습 중 체크리스트와 정답 가이드
고급 필터 결과 검증 포인트
중복 제거: 주민등록번호 중복 1건 제거되어 행 수 감소.조건 필터: 남구/광산구 + 기초생활 + 활성 Y + 2025-06-30 이전만 출력.
함수 실습 정답 예시 주요 열 공식
서비스명: =XLOOKUP([@서비스코드],코드표[서비스코드],코드표[서비스명],"없음")월한도: =XLOOKUP([@서비스코드],코드표[서비스코드],코드표[월한도],0)대상판정: =IF(AND(OR([@소득구간]="기초생활",[@소득구간]="차상위"),[@나이]>=65),"대상","재검토")사정만료: =IF(TODAY()-[@최근사정일]>365,"만료","정상")한도초과: =IF([@월소득]>[@월한도],"한도초과","한도내")
피벗 분석 확인
분기별 합계: 원본 ‘지급금액’ 총합과 일치.사용률: 계산필드 추가 후 100% 초과 항목 식별.
조건부/유효성 확인
방문상태 드롭다운: 3개 값만 선택 가능.기한 경과: 오늘 날짜 기준 자동 업데이트.
강의 운영 팁과 과제
진행 팁
데모-따라하기-적용 반복: 3~5분 데모 → 7~10분 실습 → 2분 점검.숏컷 공유: Ctrl+T(표 만들기), Alt+A+Q(고급 필터 대화상자?버전에 따라 다름), Alt+N+V(피벗).
마무리 과제
과제 1: 예시3에서 ‘동’까지 내려가 동·서비스별 상위 5개 지출 찾기.과제 2: 예시4에 “연락필요” 플래그 열 추가, 규칙: 위험점수≥80 또는 미완료+기한경과 → “연락필요”.