[Excel 개념 정리 1]엑셀을 활용한 추론 통계 개념 정리 1️⃣ (엑셀 기초 1)

이번 글은 코드잇 강의를 수강하면서 배운 내용을 주로 하여 정리되어 있습니다. (코드잇 스프린트 데이터 애널리스트 트랙 1기 훈련생)

---

엑셀 (Excel)

엑셀은 모든 데이터 파일의 통일된 형식을 제공하고, 데이터의 내용이 담겨있는 방식을 보여주는 프로그램입니다.

또한 그 내용을 통해 데이터를 이용하고 분석에 대한 상상을 가능하게 한다는 장점을 가지고 있습니다.

엑셀의 특성

1. 실제로 대부분의 국내외 연구자, 혹은 데이터 분석자들은 xlsx(엑셀 파일의 확장자)나 csv(comma separated values, 쉼표로 구분된 텍스트)의 형태로 정의된 파일을 활용하여 데이터 분석을 진행합니다.
   엑셀은 이러한 데이터들을 손 쉽게 열어볼 수 있는 프로그램입니다.
2. 분석에 활용되는 모든 데이터의 내용이 엑셀의 구조와 똑같은 모습을 하고 있다는 점에서도 데이터 형식의 시작으로서 엑셀이 중요한 것을 알 수 있습니다. 엑셀에서 사용하는 분석을 위한 데이터들은 행(row)과 열(column), 값(value)으로 이뤄진 표로 구성되어 있습니다.
3. 엑셀의 값들을 토대로, 자료를 이해하고 궁금한 점을 도출할 수 있습니다.

---

엑셀의 기초 목차

1. 정렬과 필터링
2. 상대 참조 / 절대 참조
3. 기본적이고 엑셀에서 자주 사용하는 함수
4. 가장 많이 사용하는 4가지 차트
5. 조건부 서식
6. 피벗 테이블

---

정렬과 필터링

---

정렬

오름차순이란, 순서가 낮은 것부터 높은 것 순으로 데이터의 순서를 정리하는 것을 말합니다.

내림차순은, 순서가 높은 것부터 낮은 것 순으로 데이터의 순서를 정리하는 방법을 말합니다.

위와 같은 기준을 설정하고 데이터의 순서를 정리하는 것을 정렬이라고 합니다.

 

우선 오름차순 및 내림차순으로 정렬 기준을 바꾸는 방법은 위 그림과 같이 상단의 메뉴탭 중 홈 -> 정렬 및 필터 -> 오름차순/내림차순 중 선택의 순서로 이뤄집니다. 

여기서 중요한 점은, 자신이 선택하고자 하는 기준 열에 마우스 커서를 클릭해 놓아야 한다는 것입니다.

밑의 예시를 보면, 나이를 기준으로 오름차순으로 정렬이 된 모습을 확인할 수 있습니다.

 

텍스트를 정렬하고 싶다면, 텍스트가 할당되어있는 열의 값 중 하나에 커서를 클릭하고 정렬 및 필터에 들어가면 됩니다.

텍스트 열이 클릭되어 있는 경우에 “텍스트 기준 오름차순/내림차순 정렬”로 문구가 바뀌게 됩니다.

즉, 영어나 한글로 된 글자 값을 기준으로 할 때는 정렬 표현이 “텍스트”로 바뀌게 됩니다.

데이터를 2개 이상의 기준으로 정렬할 때는 사용자 정렬 기능을 사용한다.

때로 데이터를 2개 이상의 기준으로 정렬할 경우가 생길 수 있으며, 이 경우에 “정렬 및 필터”의 옵션 중 “사용자 지정 정렬” 기능을 이용하면 됩니다.

 

“사용자 지정 정렬” 옵션을 선택하면 위와 같이 정렬 기준을 선택할 수 있는 새로운 창이 뜨게 됩니다.

현재 데이터를 사용자 정렬 기능을 활용하여 나이를 오름차순으로, 성명을 텍스트 오름차순으로 정렬한 결과를 확인해보겠습니다.

---

필터

데이터의 정렬뿐만 아니라, 내가 보고 싶은 데이터만 골라서 띄워주는 기능을 제공합니다.

---

상대 참조와 절대 참조

엑셀의 모든 셀은 셀의 위치 값을 가지고 있습니다.

‘참조’는 각 셀의 위치를 지정하여 호출하는 것을 말합니다.

• 상대 참조 : 호출하는 셀이 계속해서 변화하는 것 (위치가 변함에 따라 호출 셀도 변하는 방식)
• 절대 참조 : 호출하는 셀이 고정되어 있는 것

---

상대 참조

호출하는 셀이 계속해서 변화하는 것

 

이번에 수강과목들의 수강료의 합계가 얼마인지 확인하기 위해새로운 열 N에 “수강료합계”이라는 항목을 만들어 보려고 합니다.

이 때 수강료합계은 J열의 “수강료1”와 L열의 “수강료2”을 더한 값이 될 것입니다.

우선 N2에 첫 번째 수강료합계를 계산하는 수식을 써보겠습니다. (수식은 =J2+L2 입니다.)

 

엑셀에서는 동일한 형태의 수식을 다른 셀들로 확장해서 적용하려고 할 때, 셀의 오른쪽 아래 모서리를 클릭한 채 십자선을 드래그 하는 방식을 사용합니다.

 

계산을 적용하고자 하는 위치가 변함에 따라, 수식에 호출되는 셀들도 따라서 같이 변화하는 방식을 “상대 참조”라고 부릅니다.

엑셀은 셀을 호출할 때, 상대 참조 방식을 이용하도록 기본적으로 설정되어 있으니 이를 주의하시기 바랍니다.

---

절대 참조

호출하는 셀이 고정되어 있는 것

 

이번 데이터에서 수강료합계에 따라 10%씩 적립을 해준다고 가정해보겠습니다. 해당 적립율은 Q2에 적어놓았습니다.

이제 우리는 수강료합계 별로 얼마의 적립금이 발생하는지 계산할 수 있습니다. 계산 방법은 “수강료합계” X “적립율”일 것입니다.

이 결과를 O열에 “적립금”이라는 형태로 정리하겠습니다. 먼저, 첫 번째 값인 O2를 구해보겠습니다.

 

계산이 잘 된 것을 확인할 수 있고, 이제 나머지 행에 대해서도 적립금을 구하고자 O2의 모서리를 O26까지 드래그 해보겠습니다.

 

위와 같이, 오류가 발생하여 모든 값이 0으로 나오게 되며, 그 이유는 O26의 수식을 보면 알 수 있는데 수강료합계인 O26에 곱해진 적립율 값이 Q2가 아니라, 존재하지 않은 Q23으로 호출되었기 때문입니다.

따라서, 저희는 처음에 드래그 하는 셀인 O2의 수식에서 적립율 셀인 Q2를 고정시켜서 계산을 하게 할 필요가 있습니다.

이 때, 엑셀에서는 특정 셀을 고정하기 위해 $ 기호를 사용합니다. (절대참조 맥 엑셀 단축키 : command + t)

 

예를 들어, O2라는 위치 값에서 열인 O를 고정하고 싶다면 $O2, 행인 2를 고정하고 싶다면 O$2, 해당 셀 자체를 고정하고 싶다면 $O$2의 형태로 표현합니다. 

이번에는 절대 참조를 잘 활용하여 성공적으로 수식이 계산되는 것을 확인할 수 있습니다.

---

엑셀에서 기본적이고 자주 사용하는 함수들

가장 기본적으로 사용되는 함수 : MAX, MIN, SUM, AVERAGE, RANK.EQ, COUNT, COUNTA

실제 엑셀에서 자주 사용되는 함수 : IF, IFERROR, COUNTIF, COUNTIFS, SUMIF, SUMIFS

목록에서 조건에 따른 값을 찾아 추출하고, 원하는 위치에 옮기는 함수 : VLOOKUP, HLOOKUP, INDEX, MATCH

날짜 관련 함수 : DATE, YEAR, MONTH, DAY, WEEKDAY, DAYS360, TODAY, NOW

시간 관련 함수 : TIME, HOUR, MINUTE, SECOND

텍스트 관련 함수 : UPPER, LOWER, PROPER, LEFT, RIGHT, MIDDLE, FIND, SEARCH

---

엑셀의 논리 연산자와 기본적인 함수

MAX (최댓값)

최댓값을 뽑는 수식은 =MAX(시작셀, 끝셀) 의 형식으로 이뤄집니다.

MIN (최솟값)

최솟값을 뽑는 수식은 =MIN(시작셀, 끝셀) 의 형식으로 이뤄집니다.

SUM (합계)

합계를 계산하는 수식은 =SUM(시작셀, 끝셀) 의 형식으로 이뤄집니다.

AVERAGE (평균)

평균을 계산하는 수식은 =AVERAGE(시작셀, 끝셀) 의 형식으로 이뤄집니다.

RANK.EQ (순위)

순위를 산출하는 수식은 =RANK.EQ(순위를 알고 싶은 셀, 준거 집단, 정렬방식) 의 형식으로 이뤄집니다.

이때 준거 집단의 경우, 학생 플라타너스부터 이팝나무까지 동일한 집단 내에서 계산이 진행되어야 하기 때문에 절대 참조를 이용하여 범위를 고정시켜야 합니다.

이번 사례에서는 수강료1과 수강료2의 평균를 토대로 순위를 메기기 때문에, 준거 집단 범위는 $O$2:$O$26 로 작성됩니다.

정렬방식은 0(내림차순) 또는 1(오름차순)로 적어주면 되며, 보통 순위는 내림차순으로 정렬하기 때문에 0으로 적겠습니다.

COUNT (숫자의 개수 세기)

개수를 세는 수식은 =COUNT(시작셀, 끝셀) 의 형식으로 이뤄집니다.

이때, COUNT는 숫자가 적혀있는 셀의 개수만을 셉니다. 따라서 숫자 외의 텍스트 등이 적혀있는 셀은 계산되지 않습니다.

COUNTA (비어있지 않은 셀 세기)

COUNTA는 문자 값으로 된 데이터의 개수도 셀 수 있기 때문에 셀 내에 적혀있는 값의 형태에 영향을 받지 않습니다.(문자 값으로 된 데이터도 셀 수 있음)

COUNTA의 수식은 =COUNTA(시작셀, 끝셀) 의 형식으로 이뤄집니다.

---

실제 엑셀에서 자주 사용되는 함수

IF (조건 함수)

IF 함수는 셀 안의 값이 특정 조건을 만족할 때 “참”, 만족하지 못 할 때 “거짓”이라고 판단합니다. (조건을 적용할 수 있는 대상은 숫자 값 뿐이다.) 셀 값이 텍스트인 경우에는 IF 함수로 조건을 걸 수 없기 때문에 IF 함수는 숫자 간 대소 비교에 쓰는 경우가 많습니다.

IF 함수의 수식은 =IF(조건, “TRUE일 때의 표시”, “FALSE일 때의 표시”)의 형식으로 구성됩니다.

 

결과를 보면, 할미꽃은 강의를 수강하지 않아 수강과목과 수강료가 미수강으로 되어있기 때문에, 셀 안의 “미수강”라는 텍스트를 인식하지 못한 AVERAGE 함수가 “#DIV/0!” 이라는 오류 메시지를 띄웠습니다.

순위는 강의를 수강한 학생들 안에서만 메겨야 하기 때문에, 플라타너스부터 이팝나무까지의 평균 점수만을 이용해서 산출했습니다.

이제 평균금액이 높은 학생 5명에게 무료 수강 쿠폰을 준다고 가정했을 때, 쿠폰을 수령하는 것이 가능한지 여부를 IF 함수를 활용해 파악해보겠습니다.

조건: 학생의 순위 ≤ 5 / TRUE일 때의 표시: 가능 / FALSE일 때의 표시: 불가능

IFERROR (오류 표시)

위 IF 함수 예시 그림에서 할미꽃의 평균 점수가 오류로 표시되어 있는 것이 거슬릴 수 있습니다.

따라서, 해당 오류를 “미수강”으로 표시되도록 할 수 있으면 좋겠다는 생각이 들 때, 사용하는 함수가 바로 IFERROR입니다.

IFERROR는 오류(error)가 생겼을 때, 이를 어떻게 표시할지 결정해주는 함수입니다.

형식은 =IFERROR(value, value_if_error)와 같습니다. (value는 특정 셀이나 수식 자체를 가리키고, value_if_error는 만약 오류일 경우 어떻게 표시할 것인지에 대한 설정을 말합니다.)

 

위의 예시에서는 value에 하나의 특정한 평균 수강료가 아니라, 평균을 계산하는 수식 자체를 집어넣어 주어야 합니다. 이 식을 드래그하여 할미꽃까지 적용하면, 오류였던 할미꽃의 평균이 “미수강”으로 바뀌어서 산출되고, 오류가 없는 다른 학생들의 평균은 그대로 계산되는결과를 확인할 수 있습니다.

COUNTIF (조건에 부합하는 값 개수 세기)

COUNTIF의 수식은 =COUNTIF(범위, “조건”)과 같습니다.

조건의 기준에 따라 범위에 들어갈 내용에 해당하는 열을 지정해줄 필요가 있습니다.

예시에서는 40,000원 이상의 수강료 결제 내역을 보유한 건수는 다음과 같은 방식으로 계산할 수 있습니다.

COUNTIFS (여러 개의 조건을 기준으로 세기)

한 가지 기준으로 count를 진행하기에 불편함이 있을 때, 한 번에 여러 개의 조건을 기준으로 개수를 세고 싶을 때 사용하는 함수가 COUNTIFS입니다.

COUNTIFS의 수식은 =COUNTIFS(조건 1의 대상 범위, “조건 1”, 조건 2의 대상 범위, “조건 2”, …)와 같습니다.

조건은 나열함에 따라 2개 이상도 가능합니다. 이번에는 앞서 언급한 40,000원 이상의 수강료 결제 내역에 더해, 수강과목1의 수강료가 15,000원 이상인 내역들의 개수를 세어 보겠습니다.

조건 1의 대상 범위: 수량 (N2:N26)

조건 1: ≥40000

조건 2의 대상 범위: 단가 (J2:J26)

조건 2: ≥ 15000 따라서, 수식은 =COUNTIFS(N2:N26, “≥40,000”, J2:J26, “≥ 15,000”) 입니다.

SUMIF (조건 합계)

SUMIF는 조건을 만족하는 내역의 합계를 구하는 함수입니다.

SUMIF의 수식은 =SUMIF(전체 범위, 기준 값, 합할 범위)과 같습니다.

주의해야 할 점은 전체 범위의 설정으로, 편하게 표 전체를 드래그할 경우 엑셀이 종종 오류를 보이는 경우가 있습니다.

만약 표 전체에 날짜 부분이 있으며, 날짜 부분을 시작점으로 잡으면, 엑셀이 기준점을 인식하지 못하는 오류를 보일 수도 있습니다.

사는 구 중에 중구를 기준으로 수강료의 합계를 구하고 싶다면, 기준 값은 “중구”로, 합할 범위는 N2:N26 설정하여 함수를 실행할 수 있습니다.

SUMIFS (여러 개의 조건 합계)

COUNTIFS와 마찬가지로, SUMIFS도 조건을 2개 이상으로 적용하여 조건을 만족하는 내역의 합계를 구할 수 있습니다.

SUMIFS의 수식은 =SUMIFS(합계를 구할 범위, 조건 1 범위, 조건 1, 조건 2 범위, 조건 2, …) 과 같습니다.

이번에는 실무론을 듣는 중구에 사는 학생들의 수강료의 합계를 산출해 보겠습니다. (=SUMIFS(N2:N26,F2:F26,"중구",I2:I26,"실무론"))

계산 결과, 실무론을 듣는 중구에 사는 학생들이 수강료 합계가 135,000원으로 나왔습니다.

---

이번 글에서는 엑셀 / 정렬과 필터 / 상대 참조와 절대 참조 / 엑셀에서 기본적으로 사용되는 함수들 중에 가장 기본적이고 자주 사용하는 함수가 포함된 내용을 정리했습니다. 엑셀 함수와 차트, 조건부서식, 피벗테이블의 내용을 더 정리해야 하지만 엑셀의 내용이 방대하다보니 글이 길어지는 것을 방지하려고 다음 글에서 이어 설명하려고 합니다.

이번 글도 읽어주셔서 감사합니다.

---

출처 및 참고자료 : 코드잇 사이트 강의 '엑셀을 활용한 추론 통계 실습' https://www.codeit.kr/topics/da-sprint-excel