[Excel] Excel 데이터 분석 1

1. 가격 차이 계산

• 목표: 평균가격(원)(G열)에서 평년 평균가격(원)(F열)을 빼는 수식을 입력.
• 방법:
  1. H1 셀에 열 이름 작성: 가격 차이라고 적는다.
  2. H2 셀을 선택하고 다음을 입력:

=G2 - F2

 

 

Enter 키를 누른다. H2에 계산 결과가 표시된다.

 

1. 오른쪽 아래 십자가를 더블클릭하여 모든열에 수식 적용

2. H2 셀을 선택한 상태에서 Ctrl + Shift + ↓(아래 화살표) 키를 눌러 H열의 끝까지 범위를 선택한다.

• 데이터가 있는 G열을 기준으로 끝까지 선택된다.

• Ctrl + D를 눌러 수식을 한 번에 아래로 복사한다.

2. 비율 계산 (퍼센트 구하기)

• 목표: 평균가격(원)이 평년 평균가격(원)에 비해 몇 퍼센트인지 계산.
• 방법:
  1. I1 셀에 열 이름 작성: 평균가격 비율(%).
  2. I2 셀을 선택하고 다음을 입력:
      
  3. Enter 키를 누르면 결과가 퍼센트 값으로 표시됨.
  4. 위와 동일하게 아래로 드래그하여 다른 행에 적용.

=(G2 / F2) * 100

 

 

3. 특정 열의 최대값 구하기

• 목표: 평균가격(원)(G열)의 최대값을 구함.
• 방법:
  1. 빈 셀(예: J1)을 선택하고, 수식을 입력:
  2. Enter 키를 누르면 평균가격(원)의 최대값이 표시된다.

=MAX(G:G)

 

4. 조건부 합계 계산

• 목표: 등급이 "상품"인 데이터의 평균가격(원) 합계를 구함.
• 방법:
  1. 빈 셀(예: K1)을 선택하고, 수식을 입력:
  2. Enter 키를 누르면 조건에 맞는 데이터의 합계가 표시된다.

=SUMIF(E:E, "상품", G:G)

 

5. 논리 함수

논리 함수는 조건에 따라 다른 결과를 반환하는 데 사용된다.

실습: 특정 조건에 따라 분류하기

• 목표: 평균가격(원)이 평년 평균가격(원)보다 높은 경우 "상승", 낮은 경우 "하락"으로 표시.
• 방법:
  1. 새 열 생성: J1 셀에 "가격 변동"이라고 입력.
  2. J2 셀에 다음 수식 입력:
  3. Enter 키를 누르고, 아래로 드래그하여 다른 행에도 적용.

=IF(G2 > F2, "상승", "하락")

7. 텍스트 함수

텍스트 데이터를 다룰 때 유용하다.

실습: 시점에서 연도만 추출하기

• 목표: 시점(A열)에서 연도(예: 2018)만 추출.
• 방법:
  1. 새 열 생성: K1 셀에 "연도"라고 입력.
  2. K2 셀에 다음 수식 입력

=LEFT(A2, 4)

 

8. 피벗 테이블

데이터를 요약하고 분석하는 데 사용.

실습: 품목별 평균 가격 요약

• 목표: 품목명별로 평균 가격(평균가격(원))을 계산.
• 방법:
  1. 데이터 전체를 선택(Ctrl+A).
  2. 리본 메뉴에서 [삽입] → [피벗 테이블] 선택.
  3. 새 워크시트에서:
     • 행: "품목명"을 드래그하여 행 필드에 추가.
     • 값: "평균가격(원)"을 드래그하여 값 필드에 추가.
     • 값 필드를 클릭해 "평균"으로 요약 유형 변경.

 

9. 조건부 서식

특정 조건에 따라 셀의 색상이나 서식을 변경.

실습: 평균가격이 높은 값 강조

• 목표: 평균가격(원)이 50만 원 이상인 셀을 강조.
• 방법:
  1. G열 전체를 선택.
  2. [홈] → [조건부 서식] → [새 규칙] 선택.
  3. 규칙 유형: "셀 값이 다음보다 큼" 선택.
  4. 조건 입력: 500000.
  5. 강조할 서식 지정(예: 빨간 배경, 굵은 글씨).

 

10. 데이터 정렬 및 필터링

데이터를 조건에 따라 정리.

실습: 품목명별 데이터 정렬

• 목표: 품목명(B열) 기준으로 오름차순 정렬.
• 방법:
  1. 데이터를 선택(Ctrl+A).
  2. [데이터] → [정렬] 클릭.
  3. 정렬 기준: "품목명" 선택, 오름차순 확인 후 "확인".

11. 차트 만들기

데이터를 시각화한다.

실습: 품목별 평균가격 차트

• 목표: 품목별 평균가격을 막대형 차트로 시각화.
• 방법:
  1. 데이터를 선택: 품목명(B열)과 평균가격(원)(G열).
  2. [삽입] → [차트] → [막대형 차트] 선택.
  3. 차트 제목과 축 레이블을 적절히 설정.

 

12. OFFSET 함수로 동적 범위 생성

• 목표: 평균가격(원)의 데이터 범위를 동적으로 정의.
• 방법:
  1. 빈 셀(예: M1)에 다음 수식 입력

=AVERAGE(OFFSET(G2, 0, 0, COUNTA(G:G)-1, 1))

 

• OFFSET: 기준점(G2)에서 시작해 동적으로 범위를 확장.
• COUNTA: G열에 데이터가 있는 행 수를 계산.

G열에서 빈 셀을 제외하고 평균값을 계산

 

OFFSET 함수는 특정 셀에서 시작하여 지정된 행, 열, 높이(행 개수), 너비(열 개수)의 범위를 반환

 

13. INDEX와 MATCH로 데이터 검색

• 목표: 품목명이 "건고추"인 행의 평균가격(원) 가져오기.
• 방법:
  1. 빈 셀(예: N1)에 다음 수식 입력

=INDEX(G:G, MATCH("건고추", B:B, 0))

 

 

14. 배열 함수로 고급 데이터 계산

• 목표: 평균가격(원)의 고유값 목록 생성.
• 방법 (엑셀 365 이상에서 가능):
  1. 빈 셀(예: O1)에 다음 수식 입력

=UNIQUE(G:G)

 

15. 피벗 테이블로 복합 데이터 요약

• 목표: 품목명과 등급별 평균가격(원) 요약.
• 방법:
  1. 데이터 선택 후 [삽입] → [피벗 테이블] 클릭.
  2. 새 워크시트에서:
     • 행: 품목명.
     • 열: 등급.
     • 값: 평균가격(원) (요약 유형을 "평균"으로 설정).
  3. 결과 확인.

 

16. 시나리오 관리자

• 목표: 가격 변동 시 예상 결과를 분석.
• 방법:
  1. [데이터] → [가상 분석] → [시나리오 관리자] 선택.
  2. 새로운 시나리오 추가:
     • 변경할 셀: 평균가격(원) 열.
     • 다른 값으로 변경하고 결과를 저장.
  3. 여러 시나리오를 비교.

17. 대시보드 제작

• 목표: 품목별 가격 데이터를 대시보드로 시각화.
• 방법:
  1. pivot table 생성
  2. 차트(막대형, 원형, 꺾은선 등)를 만든다.
  3. 슬라이서와 연결:
     • 피벗 테이블에 슬라이서를 추가.
     • 슬라이서를 클릭해 차트와 데이터가 동적으로 연결되도록 설정.
  4. 대시보드를 하나의 시트에 정리.

18. 고급 차트 만들기

• 목표: 이동 평균(Moving Average) 그래프 추가.
• 방법:
  1. 평균가격(원) 열의 이동 평균을 계산:
     • 새 열에 수식 입력

=AVERAGE(G2:G4)  // 이동 평균(3개 값 기준)

 

차트에 이동 평균 데이터 추가:

• 기존 꺾은선 차트에 데이터 계열 추가.