[데이터 정보 처리 입문] 7강 - 엑셀을 이용한 데이터 처리 2

💡해당 게시글은 방송통신대학교 김성수, 이기재 교수님의 '데이터 정보 처리 입문' 강의를 개인 공부 목적으로 메모하였습니다.

학습 개요

---

• 엑셀의 다양한 함수 기능을 활용하면 어려운 계산도 손 쉽게 할 수 있고, 데이터 분석 도구를 활용하면 기술 통계 량을 쉽게 구할 수 있음
• 엑셀의 연산자, 엑셀 함수의 기능과 형식 IF 함수 활용 방법 등에 대해서 공부함
• 엑셀의 데이터 분석 도구를 활용하여 데이터에 대한 기초적인 데이터 분석 방법에 대해서 공부함

학습 목표

---

• 엑셀의 연산자를 활용할 수 있음
• 엑셀 함수의 기능과 형식을 설명할 수 있음
• 엑셀 함수를 사용하여 계산할 수 있음
• 엑셀을 활용하여 데이터를 분석할 수 있음

주요 용어

---

• 케이스(case)
  • 특정 조사 단위로부터 얻어진 정보의 집합체를 말함
• 변수(variable)
  • 각 조사 단위로부터 측정 된 개별적인 속성들
• 상대 참조
  • 행 이름이나 열 이름만을 사용하여 셀을 참조하는 방법
  • A1, B2, A1:F1 등
• 절대 참조
  • 참조하는 셀이나 셀 범위를 표시할 때 “$“를 사용하여 참조하는 방법
  • $A$1, $B$2, $A$1:$F$1 등
• 혼합 참조
  • 행 이름이나 열 이름의 한 쪽에만 “$”를 사용하여 참조하는 방법
  • $A1, B$2, A$1:F$1 등
• IF문
  • 주어진 조건을 평가하여 참인 경우와 거짓인 경우에 어떤 값을 표시하는 엑셀 함수 문
• 기술 통계량(descriptive statistics)
  • 데이터의 전반적인 분포 형태와 특성을 쉽게 파악하기 위한 목적으로 데이터를 정리,요약하는 방법을 말함

강의록

---

함수 사용법

엑셀의 연산자

• 엑셀에서는 함수를 사용하여 데이터를 분석하고, 워크 시트 값에 대하여 산술 연산, 비교 연산, 문자 연산, 참조 연산 등을 수행
• 연산자의 종류
  • 산술 연산자, 비교 연산자, 문자 연산자, 참조 연산자 등

연산자의 종류

• 산술 연산자
  • +
    • 더하기
  • -
    • 빼기
  • /
    • 나누기
  • *
    • 곱셈
  • ^
    • 거듭 제곱, 지수
• 비교 연산자
  • =
    • 같다
  • >
    • 크다
  • <
    • 작다
  • >=
    • 크거나 같다
  • <=
    • 작거나 같다
  • <>
    • 같지 않다
• 문자 연산자
  • &
    • 두 값을 연결하여 하나의 문자열 값 산출
• 참조 연산자
  • 셀 범위를 참조하는 방식을 지정함
  • :
    • 콜론
    • 범위 연산자
    • 두 셀 참조 사이의 모든 셀을 포함하는 하나의 참조 영역을 만듬
    • ex) =SUM(A1:A10) (A1부터 A10까지 모든 셀의 합계)
  • ,
    • 쉼표
    • 합집합 연산자
    • 여러 개의 셀 참조를 하나의 참조 영역으로 결합함
    • ex) =SUM(A1:A3, B1:B5) (A1:A3 범위와 B1:B5 범위에 있는 모든 셀의 합계)
  • 공백
    • 교집합 연산자
    • 두 참조 영역에 공통적으로 포함되는 셀들의 참조 영역을 만듬
    • ex) =SUM(A1:A6 A5:A10) (A1:A6 범위와 A5:A10 범위의 교집합인 A5:A6 셀들의 합계)

함수 사용 방법

• 함수의 기능
  • 값, 셀 참조, 함수 등을 사용하여 새로운 값을 생성함
• 일반적인 함수의 형식
  • 함수 식은 등호(=)로 시작, =함수이름(인수1, 인수2, ..., 인수n) 형식
  • ex) =AVERAGE(A1:B3) (A1부터 B3까지 범위의 평균 계산)
• ex) 함수 사용
  • 숫자를 직접 인수로 사용
    • =SUM(2, 3, 4, 5, 5) (결과: 19)
  • 셀 또는 셀 범위에 대한 참조 영역을 인수로 사용 가능
    • =SUM(A1:A5) (A1부터 A5 셀까지의 합계 계산)

셀 참조 방법

• 엑셀 수식에서 다른 셀의 값을 사용하기 위해 셀 주소를 참조하는 방식
• 상대 참조 (Relative Reference)
  • 수식을 복사하여 다른 셀에 붙여 넣으면, 참조하는 셀 주소가 복사 된 위치에 따라 상대적으로 변경 됨
  • 행 이름이나 열 이름만을 사용하여 셀 참조
  • ex) A1, D3, =AVERAGE(A2:G2)
• 절대 참조 (Absolute Reference):
  • 수식을 복사하여 다른 셀에 붙여 넣어도 참조하는 셀 주소가 변경되지 않고 고정됨
  • 참조하는 셀이나 셀 범위를 표시할 때 $ 표시 사용
  • ex) $A$1, $D$3, =AVERAGE($A$2:$G$2)
• 혼합 참조 (Mixed Reference):
  • 열 또는 행 중 하나만 절대 참조로 고정하고 다른 하나는 상대 참조로 남겨둠
  • 행 이름이나 열 이름의 고정하려는 부분에만 $ 표시하여 참조
  • ex) $A1 (열 A는 고정, 행 번호는 상대적 변경), D$3 (열 D는 상대적 변경, 행 3은 고정), =AVERAGE($A2:$G2) (열 A와 G는 고정, 행 2는 상대적 변경)

함수 사용 예: 평균 계산

• 성적 데이터에 대해서 각 사람의 평균 점수를 구하는 과정
  1. 먼저 D1 셀에 ‘평균’이라고 입력하고, D2 셀에 커서를 위치한 후, 수식 입력줄에 =AVERAGE(B2:C2)를 입력

  

1. 엑셀의 채우기 기능 사용: 나머지 사람은 채우기 기능 이용

   

함수 사용 예: IF문 사용

• 통계학 점수가 60점 이상이면 ‘합격’, 60점 미만이면 ‘불합격’으로 표시
• IF문은 주어진 조건을 평가하여 참인 경우와 거짓인 경우에 다른 값 표시
• 구문: IF(주어진 조건, value if true, value if false)
  • 주어진 조건
    • TRUE 또는 FALSE로 판정될 수 있는 값이나 식 (조건)
  • value_if_true
    • 주어진 조건이 참일 때 얻게 되는 결과 값
  • value_if_false
    • 주어진 조건이 거짓일 때 얻게 되는 결과 값
      1. E1 셀에 변수명으로 ‘합격여부’라고 입력하고, E2셀에 =IF(B2<60, "불합격", "합격") 라고 입력

  

1. 나머지 셀은 채우기 기능을 이용하여 쉽게 처리할 수 있음

함수 마법사의 이용

• 함수 마법사를 이용해서 과목 별 평균 점수를 구하는 예
  1. 계산 된 함수 값이 입력될 셀 선택
  2. 함수 마법사를 클릭하여 대화 상자 이용

  

1. 함수의 인수로 사용될 셀의 범위를 직접 마우스로 드래그하여 설정

2. 올바르게 인수가 지정 되었는 가를 검토하여 확인 선택

   

자주 사용하는 엑셀 함수들

• AVERAG 함수
  • 인수의 산술 평균을 구함
  • ex)
    • =AVERAGE(B2:B30)
    • =AVERAGE(number1, number2, number3, …)
• VAR 함수
  • 표본 자료의 표본 분산을 구함
  • ex)
    • =VAR.S(B2:B30)
    • =VAR(number1, number2, number3, …)
• STDEV 함수
  • 표본 자료의 표준 편차를 구함
  • ex)
    • =STDEV.S(B2:B30)
    • =STDEV(number1, number2, number3, …)
• MEDIAN 함수
  • 인수의 중앙 값을 구함
  • ex)
    • =MEDIAN(B2:B30)
    • =MEDIAN(number1, number2, number3, …)
• MAX 함수
  • 데이터의 최대 값을 구함
  • ex)
    • =MAX(B2:B30)
    • =MAX(number1, number2, number3, …)
• MIN 함수
  • 데이터의 최소 값을 구함
  • ex)
    • =MIN(B2:B30)
    • =MIN(number1, number2, number3, …)

엑셀을 사용한 데이터 처리 예

데이터에 대한 통계 분석

• 기술 통계 량 (Descriptive Statistics)
  • 데이터의 전반적인 분포 형태와 특성을 쉽게 파악하기 위한 목적으로 데이터를 정리, 요약한 수치
• [데이터] 탭의 [분석] 그룹의 [데이터 분석]을 이용함
• 엑셀에서 제공하는 데이터 분석 도구
  • 분산 분석(ANOVA), 상관 분석, 기술 통계법, 이동 평균법, 지수 평활법, 히스토그램, 난수 생성, 순위와 백분율, 회귀 분석, t-검정, 표본 추출 등 거의 모든 기초적인 통계 분석 가능
• [데이터] 탭에서 [분석] 그룹에서 [데이터 분석]이 나타나지 않는 경우
  • 데이터 분석 도구 활성화

  • [파일] 탭 → [옵션] → [추가 기능] → 하단의 ‘관리:’ 드롭다운에서 ‘Excel 추가 기능’ 선택 후 [이동] 클릭 → ‘분석 도구’ 체크 → [확인]

    

    

  • 활성화되면 [데이터] 탭의 [분석] 그룹에 [데이터 분석] 메뉴가 나타남

    

기술 통계량 구하는 예

1. [데이터] 탭 → [분석] 그룹 → [데이터 분석] 선택

2. [통계 데이터 분석] 대화 상자에서 ‘기술 통계법’ 선택 → [확인]

   

3. [기술 통계법] 대화 상자 설정 → 입력 범위, 첫번째 행을 이름표로 사용, 요약 통계량 선택

   

   • 입력 범위
     • 분석할 데이터가 있는 셀 범위를 지정
   • 첫째 행 이름표 사용
     • 입력 범위에 변수 이름(제목)이 포함된 경우 체크
   • 출력 옵션
     • 결과를 표시할 위치(새 워크 시트, 현재 시트의 특정 셀 등)를 선택
   • 요약 통계량
     • 반드시 체크
     • 기술 통계 량 결과를 출력하는 옵션

4. 결과 확인

   

실습: 성적 데이터

• 상대 참조, 절대 참조, 혼합 참조 이용
• AVERAGE 문을 이용한 평균 계산

• IF 문을 이용한 ‘합격’, ‘불합격’ 판정

  

• [데이터 분석]을 이용한 기술 통계 분석

  

연습 문제

---

1. 엑셀에서 B2셀의 값이 60보다 작으면 “불합격”, 60이상이면 “합격”을 D2셀에 표시하고자 한다. 적합한 엑셀 함수 사용은?

   a. =IF(B2<60, "불합격", "합격")

2. B2와 C2 셀에 데이터를 입력한 후 두 값의 평균 점수를 구하고자 한다. 적합한 수식 입력은?

   a. =AVERAGE(B2:C2)

3. 학생들 30명에 대한 통계학 점수와 수학 점수 자료를 다음과 같이 입력하였다. 물음에 답하시오. 데이터를 입력한 후 그림과 같이 각 케이스에 대하여 평균 점수를 구하고자 한다. (A) 부분에 적합한 수식 입력은?

   

   a. =AVERAGE(B2:C2)

4. 학생들 30명에 대한 통계학 점수와 수학 점수 자료를 다음과 같이 입력하였다. 물음에 답하시오. D2 셀에 =IF(SUM(B2:C2)>=130, "합격", "불합격")를 입력하였을 때 그 결과는? 

   

   a. 합격

5. 학생들 30명에 대한 통계학 점수와 수학 점수 자료를 다음과 같이 입력하였다. 물음에 답하시오. D2 셀에 =$B$2+$C$2를 입력한 후 드래그 & 드롭 기능을 이용하여 D11 셀까지 채워 넣었다. 이 때 D3 셀의 결과는?

   

   a. 136