[Part 5-3, 6] 엑셀로

by


색인

–

5. Excel 전문가 되기
손익 계산서(원시 데이터 처리, 피벗 테이블 생성, 양식 생성, SUMIFS(원시 데이터 조인)로 합계 계산, 분기별 및 누계 데이터 추가) / 월간손익조회서 / 근로명세서 작성 / 제품 재고현황 확인 / 간트차트 그리기 / 대시보드 만들기 / OFFSET 기능 / OFFSET 기능과 다이나믹 레인지 / SUMPRODUCT / MATCH, INDEX 개념 및 적용

'추천 관련글,

6. 리더가 인정하는 자료 작성법
보고서 데이터 생성 / Superstore 대시보드 생성 / Excel 요약

SUMPRODUCT

SUMPRODUCT ( 배열1, 배열2, … ) + Ctrl + Shift + Enter
: 조건을 만족하는 데이터를 더하여 곱하기

예를 들어 그러한 데이터에서 이천 센터에 캠프가 위치한 1월 데이터의 총 값만 더하고 싶다면


날것의

새 새 시트 입력

방법 1) 총

(찾을 상태의 범위, 상태 값, 추가할 값의 범위)

  • 총 (총 열 K 날것의연도 열 D 날것의1월 D$4 새로운물류창고 G열 날것의이천센터 $B6 새로운 )

방법 2) SUMPRODUCT

  1. 이름 관리자(Ctrl+F3)에서 다이나믹 레인지 생성 – 월간 범위, 총액 범위, 물류센터 범위
    • 기존과 동일 =OFFSET($G$33,,,COUNTA($G:$G)-1) ← OFFSET(이천센터 창고-1)
  2. SUMPRODUCT(월별 범위 = D$4 * 유통 센터 범위 = $B6 * 전체 범위)
  3. 단위를 1,000,000원으로 강조 표시하고 “셀 서식”(Ctrl+1)에서 “0,0,,_-“를 선택합니다(숫자 중간에 쉼표를 삽입하고 끝에 공백을 둡니다).
  4. 균일한 열 너비 및 중앙 정렬

고급 기능 COMPARE, INDEX

맞다 ( lookup_value, 검색 배열, (일치 유형) )
: 특정 행/열 범위에서 찾고자 하는 값이 몇 번째인지 알려주는 기능입니다.
INDEX와 같은 기능과 함께 사용되며 단독으로 사용되지 않습니다.

  • 맞다 (찾고 싶은 가치, 찾고 싶은 가치 싱글 열/또는 행 범위, 근사치 여부)
    • 일치 유형: 하나(기본)= 찾은 값 이상 작은 값의 최대값, 0= 찾고 정확한 값 똑같은 것값, -하나= 찾은 값 이상 훌륭한 가치 최저한의

색인( 배열, 행 번호, (열 번호), (영역 번호) )
: 선택 영역에서 찾을 값의 행/열 번호를 기준으로 vlookup과 같이 해당 값을 호출합니다.

  • 색인 (데이터의 영역, 조회할 데이터의 행 번호, 조회할 데이터의 열 번호, 영역 선택(배열이 2개 이상인 경우) )
    • 대부분의 경우 row_num과 col_num에 직접 숫자를 입력하는 대신 일치하여 찾은 행/열 번호를 포함하는 셀이 대신 입력됩니다.

VLOOKUP의 한계

  • VLOOKUP(조회할 데이터, 공통 기준 열을 기반으로 검색할 데이터 범위, 검색할 데이터 열 번호, 0 OR 1)
  • VLOOKUP의 한계는 참조 열의 “오른쪽”에 있는 데이터만 로드할 수 있다는 것입니다.

=> 이러한 제한 사항 추가 MATCH + INDEX 함수 조합

  • 다만, 함수를 겹쳐야 하므로 raw data 컬럼의 위치를 ​​바꾸거나 최대한 vlookup을 이용하여 오른쪽에 데이터를 로드하는 것이 좋습니다.

6. 리더가 인정하는 자료 작성법

6-1 보고 자료 만들기

피벗 테이블 분석 – “슬라이서 삽입”


  • 사용 사례: 센터별로 슬라이서를 클릭할 때 해당 센터의 월별 금액을 보여주는 차트를 보고 싶을 뿐입니다.
    1. 슬라이서 삽입: 센터가 10개일 경우 슬라이서를 삽입하고 각 슬라이서가 옆으로 누를 수 있도록 열 수를 10으로 설정합니다.
    2. IF 함수 사용: =IF(센터 이름 = 피벗 테이블의 센터 이름, 1, 0)
    3. IF 함수를 다시 사용하십시오. IF(IF(센터 이름 = 피벗 테이블의 센터 이름, 1, 0)=1, 월간 금액, 0))
    4. 슬라이서 설정: 버튼으로 사용하려면 “Show Header”를 선택 해제하십시오.


선택시 IF함수 적용

슬라이서에서 선택한 가운데만 강조 표시됩니다.


피벗 테이블 슬라이서

조건부 서식 사용

  1. 위와 같은 기능 사용 => IF 함수 사용: =IF(센터 이름 = 피벗 테이블의 센터 이름, 1, 0)
  2. 조건부 서식에서 IF 함수를 적용할 셀이 1이면 적용할 특정 셀 서식을 지정합니다(예. =$P$24=1).

6-2 Superstore 대시보드 만들기

  1. Superstore 원시 데이터 살펴보기
    • ‘중복 제거‘ 데이터의 고유값을 확인하여 구조를 파악하는 기능
    • ‘피벗 테이블‘ 데이터의 구조를 결정하기 위해
  2. 데이터를 쓰지 않도록 (EX. 배송일자, 고객ID, 국가(동일국가), 우편번호, 상품ID, 상품명, 수량, 할인율..) 끄다
  3. 원시 데이터 처리
    1. 여러 정보가 들어 있는 Order Date 셀을 년, 월, 일 함수를 사용하여 연, 월, 일로 나눕니다.
  4. 개요 시트 만들기
    1. SUMIFS를 사용하여 지역별 매출 입력
      • 2017년 11월~에서 도시별 혜택 입력하다
      • 총 (수입 미가공, 연도 미가공, 2017년 신규, 월 미가공, 1월 신규, 도시 미가공, 도시명 신규)
    2. 연간 날짜를 확인하는 목록 만들기 – 데이터 유효성 검사 – 목록, 2014, 2015, 2016, 2017

과거 날짜를 만들어 대시보드 만들기 ~ 차트 그리기

  1. 피벗 테이블 설정 열기
  2. 이달의 피벗 테이블 설정
    • 각각 “연도”와 “월”만 포함하는 피벗 테이블을 만듭니다.
    • 1,2 => 각 경우에 슬라이서 삽입
    • ‘월’은 슬라이서를 가로로 정렬하기 위해 열 수를 12로 설정합니다.
  3. 지역 지역 피벗 테이블 설정
  4. 상태 상태 피벗 테이블 설정
  5. 도시 도시 피벗 테이블 설정
    • => 슬라이서 사용
    • ‘피벗 테이블 옵션’~에 성‘연간부’, ‘월간부’, ‘지역부’, ‘국무부’, ‘시부’
    • 지역 슬라이서의 나머지 부분을 슬라이서에 연결 (하위 카테고리만) – ‘보고서 연결‘
      • 지역은 지역구분, 주구분, 시구분을 확인하세요.
      • 국가는 도시 구분을 검토
    • 슬라이서 설정 – 헤더 표시 비활성화, 데이터 없이 항목 숨기기 활성화
  6. 원시 막대 차트 만들기
    • 월별 총매출 데이터 생성
    • 분할|1월, 2월… / 총량 / total_chart(슬라이서에 해당하는 데이터만 강조하기 위해)
      • 총액 버저 소리다음으로 가져오기(Sale Raw, Total New, Year Raw, 2015 New, Month Raw, January New)
      • total_chart if 함수‘Division’의 월이 피벗 테이블의 월과 같으면 출력 1로 설정하고 그렇지 않으면 0으로 설정합니다.
  7. 막대 차트 그리기
  8. 케이크 차트 생으로 만들다
    • 지역별 매출 비교
    • 분할|중부, 동부, 남부, 서부, 총량 위와 같이 합계가 있는 로드 합계
  9. 원형 차트 그리기
  10. 주별로 원시 피벗 만들기
    • 연도와 월을 일렬로
    • 열에는 지역 및 주가 포함됩니다.
    • 가치에는 판매가 포함됩니다.
  11. 상태별 영역 차트 그리기
    • 피벗 차트 분석 탭 – 박스 버튼 – 모두 숨기기
  12. 주별로 원시 피벗 만들기
  13. 주별 꺾은선형 차트 그리기
  14. 범주별로 피벗 만들기
  15. 범주별로 원형 차트 그리기
    • 생성된 모든 차트를 선택하고 오른쪽 클릭 – ‘크기와 속성‘ – 재산 – 불변확인하다
    • 윤곽 없애다, 색상 모든 것이 맞는다

6-3 엑셀 요약

  • Ctrl+9: 숨기기
  • 숨겨진 셀을 제외하려면 보이는 셀 선택 기능을 사용하여 다음과 같은 일괄 처리를 수행합니다. B. 셀 서식 지정
  • 만약에
    • 목적: 데이터 분류
    • IF(참/거짓 구분 조건 값, 참일 때 표시할 값, 거짓일 때 표시할 값)
  • VLOOKUP
    • 용도 : 주어진 컬럼을 기준으로 N번째 위치의 데이터를 찾는 함수
    • VLOOKUP(공통 기준 열을 기반으로 검색할 데이터의 검색, ‘영역검색할 데이터의 ‘ , ‘열 번호‘ , 0 또는 1 )
    • 행과 열의 고정에 주의(절대참조)
  • 카운티
    • 목적: 조건을 만족하는 데이터의 개수 찾기
    • COUNTIF (계산할 값이 있는 셀의 ‘전체 범위‘, ‘ 범위에 포함됩니다.상태 값‘)
    • COUNTIFS(모든 범위 1, 조건 값 1, 전체 범위 2, 조건 값 2…)
  • 총
    • 목적: 조건을 만족하는 데이터의 합 구하기
    • SUMIF(조건을 찾는 영역, 조건값, 더할 값이 있는 영역)
  • IFERROR
    • IFERROR(가치, value_if_error)
  • 조건부 서식
    • 규칙 유형: 셀 값을 기준으로 모든 셀 서식 지정 / 셀만 포함 / 상한, 하한 값만 지정 / 평균 이상, 하한 값만 지정 / 고유한 중복 값만 지정 / 수식을 사용하여 결정 형식을 사용할 셀
    • 서식 스타일: 데이터 막대, 아이콘 세트

엑셀을 사용할 때
사용자 입장에서 목적에 맞게 자료를 정리합니다.

  • 사용자 입장에서 : 사용자가 누구인지(계급, 연령, 상황), 선호 사항이 무엇인지 쉽게 알 수 있습니다.
  • 목적에 맞게 : 이 데이터가 필요한 이유는 무엇이며 어떤 관점을 가지고 있으며 결과는 무엇입니까?
  • 재료를 조립하다 : MECE, 시각화, 간단하고 직관적
  • 마지막 팁
    • 마우스 포인터는 항상 A1(문서 상단)에 있습니다.
    • 구분선 주변의 프레임 고정
    • 시트명을 꼭 적어주세요
    • 시트가 많으면 빈 시트로 구분하십시오.
    • 사용자 친화적 비율(75% ~ 100%)을 설정합니다.