[데이터 정보 처리 입문] 10강 - 엑셀 활용

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

학습 개요

---

• 엑셀 함수의 기능과 사용법에 대해서 살펴봄
• 엑셀 함수의 기본적인 기능, 날짜/시간 함수와 텍스트 함수를 활용한 다양한 사례를 살펴봄으로써 엑셀 활용 능력을 배양함

학습 목표

---

• 엑셀 함수의 기능을 이용할 수 있음
• 엑셀 함수의 기능을 활용하여 실제 문제를 해결할 수 있음
• 날짜/시간 함수를 이용할 수 있음
• 텍스트 함수 기능을 활용하여 실제 문제에 적용할 수 있음

주요 용어

---

• 채우기 기능
  • 연속된 숫자나 일정한 규칙을 갖고 변화하는 숫자들을 쉽게 입력할 수 있게 하는 엑셀 기능을 말함
• SUMPRODUCT 함수
  • 두 배열의 대응되는 값끼리 곱해서 합을 구해 주는 기능
• DATE(year, month, day) 함수
  • 특정 날짜의 일련 번호를 구해주는 엑셀 날짜/시간 함수 중 하나
• YEAR(날짜 또는 숫자) 함수
  • 날짜에 해당하는 연도를 표시해 주는 엑셀 함수
• NOW() 함수
  • 현재 시점을 나타내는 엑셀 함수로 인수가 필요 없는 날짜/시간 함수
  • 이 함수를 입력하는 시점마다 함수 결과 값이 변화함
• LEFT(text, num_chars) 함수
  • 문자열의 첫 문자부터 지정된 개수의 문자를 표시해 줌
• RIGHT(text, num_chars) 함수
  • 문자열의 마지막 문자부터 지정된 개수의 문자를 표시해 줌
• MID(text, start_num, num_chars) 함수
  • 문자열의 지정한 위치로부터 지정된 개수의 문자를 표시해 줌

강의록

---

함수의 기능과 형식

엑셀의 채우기 기능

• 연속된 숫자나 일정한 규칙으로 변화하는 숫자들을 쉽게 입력할 수 있도록 함

엑셀 함수 기능과 형식

• 기능
  • 값, 셀 참조, 함수 등을 사용하여 새로운 값 생성
• 형식
  • = 함수 이름(인수, 인수, …, 인수)
• 예
  • =SUM(A1:A3)

함수 사용의 규칙

• 수식은 등호(=)로 시작
• 함수 이름 다음에 인수를 묶는 양쪽 괄호는 반드시 필요
• 인수는 숫자, 셀 범위, 논리 값, 문자 값, 다른 함수도 가능
• 인수가 여러 개 사용되는 함수는 콤마(,)로 분리

함수 사용에 대한 예들

• =SUM(A1:A3)
• =SUM(2,3,4,5,5)
• =SUM(5+2,AVERAGE(5,7),10)

엑셀을 이용한 계산

엑셀 채우기와 함수 사용 예제

• 예제: 오늘 1원 저금하고 내일은 그 2배 그 다음은 전날의 두 배, 이렇게 저금하면 며칠 째 되는 날에 누적 액이 1억 원을 돌파할까?
  1. A1 셀에 ‘날짜’, B1 셀에 ‘저금액’, C1 셀에 ‘누적 저금액’ 입력
  2. A2 셀과 A3 셀에 각각 1과 2를 입력하고, 자동 채우기 기능을 이용해서 드래그 & 드롭 하여 30까지 채우기 실행
  3. B2 셀에 첫 날의 저금액인 1을 입력하고, C2 셀에 “=B2“을 입력
  4. B3 셀에 “=2*B2”, C3 셀에 “=C2+B3“을 입력

  5. B3:C3의 셀 범위를 선택하고, 채우기 핸들을 드래그 & 드롭 해서 30일째 되는 날까지 채우기 실행

     

  6. 누적 액이 처음으로 1억원을 넘는 날짜는 27일째임을 확인

     

SUM 함수를 이용하여 평점 평균 구하기

• 교과목의 성적 등급 및 평점

  등급	A+	A0	A-	B+	B0	B-	C+	C0	C-	D+	D0	D-	F
  평점	4.3	4.0	3.7	3.3	3.0	2.7	2.3	2.0	1.7	1.3	1.0	0.7	0

• 어느 학생의 성적표에 대한 평점 평균 구하기

  과목명	신청 학점	성적
  경영학개론	3	B0
  세계의 역사	3	A-
  컴퓨터의 이해	3	A0
  데이터정보처리 입문	3	A+
  인터넷서비스	3	B+
  계	15

  1. 해당 과목의 성적에 대응하는 평점을 각각 입력

  2. E1 셀에 ‘학점 * 평점‘을 입력, E2 셀에 함수 ‘=B2 * D2‘를 입력

     

  3. E2 셀을 선택하고 드래그 & 드롭으로 E6 셀까지 채움
     • 이 과정을 통해서 각 과목에 대해서 학점 * 평점의 값을 계산

  4. E7 셀에 SUM(E2:E6)/B7 을 입력하여 평점 평균 계산

     

     • 평점 평균 = 학점의 합계 / (학점 * 평점)의 합계 = ∑학점 / ∑학점 * 평점

SUMPRODUCT 함수

• 배열의 대응되는 값끼리 곱해서 합을 구해주는 기능

• 평점 평균 계산

  

  • =SUMPRODUCT(B2:B6,D2:D6)/B7을 D7 셀에 입력

날짜/시간 및 문자 함수 이용

날짜/시간 함수의 예

• DATE(year, month, day)
  • 특정 날짜의 일련 번호를 구해 주는 기능
• YEAR(날짜 또는 숫자)
  • 날짜에 해당하는 연도를 표시
• NOW()
  • 현재 시점을 나타내는 기능으로 인수가 필요 없는 날짜/시간 함수
  • 입력하는 시점마다 값이 변화함

텍스트 함수의 예

• LEFT(text, num_chars)
  • 문자열의 첫 문자부터 지정된 개수의 문자를 표시
  • ex) A1 셀에 “대한민국”이 입력되었다면 LEFT(A1,2) = "대한"
• RIGHT(text, num_chars)
  • 문자열의 마지막 문자부터 지정된 개수의 문자를 표시
  • ex) RIGHT("Sale Price",5) = "Price"
• MID(text, start_num, num_chars)
  • 문자열의 지정한 위치로부터 지정한 개수의 문자를 표시
  • ex) MID("대한민국", 3, 2) ="민국"

날짜/시간 및 문자 함수 이용 예제

• 각 사람의 주민등록번호와 입사일로부터 나이와 근속 년수 계산

• 월급은 통화표시 ‘₩‘를 넣고 3자리마다 단위 표시

  

1. G1 셀에 ‘생년월일‘을 입력하고, G2 셀에 =DATE(LEFT(C2,2), MID(C2,3,2), MID(C2,5,2))을 입력

   

2. H1 셀에 ‘나이‘를 입력
   • H2에 =YEAR(NOW( )) - YEAR(G2)를 입력

   

3. 근속년수도 I2 셀에 =YEAR(NOW( ))-YEAR(D2)을 입력하여 구함

   

4. 월급을 통화표시 ‘₩‘를 넣고 3자리마다 단위를 표시하기 위하여 F2:F6의 셀 범위를 선택하고, [홈]-[표시형식] 그룹을 클릭하여 셀 서식 대화 상자에서 ‘통화’ 서식을 선택

   

5. 서식 도구 모음에서 테두리를 선택하여 보기 좋은 표 형식으로 완성

   

실습

• 날짜/시간 및 문자 함수 이용

  

연습 문제 풀이

• 다음 물음에 답하시오.

  1. 학과 코드의 첫째 자리 알파벳이 A이면 법학과, B이면 경영학과, C이면 통계학과 학생이다. 학과 셀에 학과를 표기하여라. (Left 함수 사용)

     

     • =IF(LEFT(A2, 1) ="A", "법학과", IF(LEFT(A2, 1) = "B", "경영학과", "통계데이터과학과"))

  2. 시험 점수와 과제물, 태도 점수의 평균에서 결석 일수에 5를 곱한 수를 뺀 것으로 점수로 계산하여라.

     

     • =AVERAGE(D2:F2)-5*G2

  3. 평가 항목은 다음과 같이 장학생, 이수, 재수강으로 나뉘며, 평가 기준에 의해 평가 항목을 계산하여라.

     

     • =IF(AND(H2>=90,G2=0),"장학생",IF(OR(H2<=60,G2>=5),"재수강","이수")) - 평가 기준
     • 장학생: 점수가 90점 이상이고 결석을 한 번도 하지 않은 경우
     • 재수강: 점수가 60점 이하이거나 결석을 5회 이상인 경우
     • 이수: 재수강이 아닌 경우

연습 문제

---

1. A2 셀에 “대한민국”이라고 입력되어 있다. “=MID(A2, 2, 2)“의 결과로 알맞은 것은?

   a. 한민

2. G2 셀에 어떤 사람의 생년월일이 “1980-12-5”로 같이 입력되어 있다고 한다. 엑셀 함수 “=YEAR(NOW())-YEAR(G2))“의 결과를 바르게 설명한 것은?

   a. 현재 시점의 나이를 구한다.

3. 학과코드의 첫 번째 자리 알파벳이 A이면 통계 · 데이터과학과, B이면 컴퓨터학과 학생이다. 학과 셀에 학과를 표기하고자 한다. C2 셀에 알맞은 함수 식은?

   

   a. =IF(LEFT(A2,1)="A", "통계·데이터과학", "컴퓨터")

4. 최종 점수는 시험점수와 과제물, 태도점수의 합계에서 결석일수에 5를 곱한 수를 뺀 것으로 계산하고자 한다. H2 셀에 알맞은 함수 식은? 

   

   a. =SUM(D2:F2)-5*G2

5. 다음과 같이 워크 시트에 값이 입력되어 있을 때 ‘=SUMPRODUCT(A1:A3, B1:B3)’을 D1 셀에 입력하면 얻게 되는 계산 값은 얼마인가?

   

   a. 10