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

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

---

이번 글에서는 저번 엑셀 개념 정리 글에서 못다한 엑셀 기초에 대한 내용 정리를 이어서 하려고 합니다.

저번 글에서는 엑셀에서 기본적이고 자주 사용하는 함수들에 대한 내용을 설명하던 중간에 마무리하게 되었으며, 엑셀에서 기본적이고 자주 사용되는 함수들인 MAX, MIN, SUM, AVERAGE, RANK.EQ, COUNT, COUNTA, IF, IFERROR, COUNTIF, COUNTIFS, SUMIF, SUMIFS 에 대해 설명했었습니다.

이번 글에서는 엑셀의 함수들을 마저 설명한 뒤에 이어 차트, 조건부서식, 피벗테이블을 설명하고 엑셀의 기초를 마무리하려고 합니다.

---

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

가장 기본적으로 사용되는 함수 : 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

---

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

 

VLOOKUP (수직 탐색 ; vertical lookup)

우리가 찾기를 원하는 정보가 열에서 찾아야 하는 정보일 때, VLOOKUP (Vertical Lookup), 수직 탐색 기능을 사용해야 합니다. VLOOKUP의 수식은 =VLOOKUP(조회 값, 준거 범위, 찾고 싶은 정보가 포함된 열 번호, 0) 과 같습니다.

마지막의 0은 정확히 일치하는 값을 찾으라는 의미라고 이해하시면 됩니다.

수강 금액표에 있는 각 과목의 비용을, 왼쪽 표에 적용시키고 싶을 때, 과목명을 통해 수강료를 찾아 왼쪽 표에 적용시키는 과정이 필요합니다.

이 예시에서는 ‘경영론’를 통해 ‘수강료’를 찾는 과정이 필요하며, G2를 구하는 수식에 들어갈 값들은 다음과 같습니다.

조회 값: F2 (경영론) / 준거 범위: $J$2:$K$8 / 찾고 싶은 정보가 포함된 열 번호: 2 (준거 범위인 금액표에서 ‘비용(원)’은 두 번째 열이기 때문) 따라서, 완성된 식은 =VLOOKUP(F2, $J$2:$K$8, 2, 0) 이며, 밑의 그림을 통해 잘 출력된 것을 확인할 수 있습니다.

HLOOKUP (수평 탐색)

만약 금액표가 가로로 주어져 있다면 HLOOKUP 함수를 사용합니다.

논리와 식 구조는 VLOOKUP과 똑같습니다.

추가적으로, 찾고 싶은 값이 포함된 열 번호를 적었던 부분만 행 번호로 바꾸면 됩니다.

HLOOKUP의 수식은 =HLOOKUP(조회 값, 준거 범위, 찾고 싶은 정보가 포함된 열 번호, 0) 과 같습니다.

INDEX 와 MATCH

저희는 위 두가지 경우까지 직관적으로 행 번호와 열 번호를 알고 있는 상태에서 수식을 작성했습니다.

하지만, 실제 데이터들의 크기는 엄청나게 크고, 수 천개의 행과, 수 십 개의 열을 담은 데이터를 이용하기 때문에 위의 두 방법들처럼 원하는 정보의 행 번호와 열 번호를 미리 알고 지정해주는 것은 현실적으로 많은 어려움이 있습니다.

그렇기 때문에, 함수를 활용하여 정보가 담겨있는 표에서 원하는 행/열 정보를 자동으로 찾아, 그 교차점에 있는 값을 옮겨와야 할 필요가 있습니다.

이런 경우에 MATCH는 우리가 눈으로 확인할 수 없는 행/열 번호를 자동으로 찾아주고, INDEX는 MATCH로 찾아낸 행과 열의 교차점인 셀값을 정보가 담겨있는 표에서 추출하도록 도와줍니다.

 

우선 INDEX의 수식은 =INDEX(준거 범위, 행 번호, 열 번호) 과 같습니다.

그리고 난 후, 행 번호와 열 번호를 MATCH를 통해 구할 수 있습니다.

MATCH의 수식은 =MATCH(검색할 값, 검색할 범위, 0) 과 같습니다.

아래에서 경영론의 행 번호를 추출하기 위해 MATCH함수를 적용한 모습을 확인할 수 있습니다.

그리고, 비용(원)의 열 번호를 추출하는 모습을 확인할 수 있습니다.

이제 이렇게 얻어낸 행, 열 번호를 index 함수에 집어넣어서 값들을 채워 넣을 수 있습니다.

수식에 준거 범위 J2:K8를 넣고, match를 통해 얻은 행, 열 번호를 넣어 성공적으로 채워진 것을 확인할 수 있습니다.

이제 왼쪽 표에 이 수식을 넣어서 표를 완성할 수 있게 =INDEX(J2:K8,3,2)식에 행, 열 번호에 각각 MATCH 식을 그대로 삽입해주는 과정을 진행합니다.

이 과정에서 주의할 점은 범위 값들을 전부 절대 참조로 바꿔줘야 한다는 것입니다.

수식에서 범위로 지정된 것들은 다 준거 범위, 즉 금액표이기 때문에, 이 부분은 식에서 변하지 않고 고정되어 있어야 합니다.

---

날짜 관련 함수

DATE (날짜를 생성)

수식은 DATE(연, 월, 일) 와 같습니다.

YEAR (날짜에서 연도를 추출)

수식은 YEAR(날짜) 와 같습니다.

MONTH (날짜에서 월을 추출)

수식은 MONTH(날짜) 와 같습니다.

DAY (날짜에서 일을 추출)

수식은 DAY(날짜) 와 같습니다.

WEEKDAY (날짜의 요일을 추출)

수식은 WEEKDAY(날짜,2) 와 같습니다.

WEEKDAY 함수는 제가 지정한 날짜가 무슨 요일이었는지를 알려주며, 날짜 뒤에 붙인 2라는 옵션은, 요일의 시작점을 무슨 요일로 잡을지에 대한 것입니다. (1을 지정하면 일요일을 1로, 2를 지정하면 월요일을 1로 지정한다.) 7월 1일 → 월요일

DAYS360 (두 날짜 간 경과일을 계산)

수식은 DAYS360(시작일, 종료일, TRUE/FALSE) 와 같습니다.

DAYS360는 원하는 두 날짜 간 며칠이 차이나는지를 계산하는 함수입니다.

TODAY (오늘의 날짜를 반환)

수식은 TODAY() 와 같습니다.

오늘의 날짜를 입력하고 싶을 때 사용하는 함수로, 괄호 안에 별다른 입력값을 요구하지 않습니다.

NOW (현재 날짜와 시간을 반환)

수식은 NOW() 와 같습니다.

현재의 날짜와 시간을 알고 싶을 때 사용하는 함수로, TODAY와 마찬가지로 괄호 안에 입력값이 필요 없습니다.

---

시간 관련 함수

TIME (원하는 시, 분, 초를 생성)

수식은 TIME(시, 분, 초) 과 같습니다.

오후 9시 35분 23초를 생성하는 예시를 들어봤습니다.

HOUR (시간에서 시를 추출)

수식은 HOUR(시간) 과 같습니다.

MINUTE (시간에서 분을 추출)

수식은 MINUTE(시간) 과 같습니다.

SECOND (시간에서 초를 추출)

수식은 SECOND(시간) 과 같습니다.

---

텍스트 관련 함수

UPPER (영어 문자들을 모두 대문자로 변환)

수식은 UPPER(텍스트) 와 같습니다.

LOWER (영어 문자들을 모두 소문자로 변환)

수식은 LOWER(텍스트) 와 같습니다.

PROPER (영어 문자들 중 맨 앞글자만 대문자로 변환)

수식은 PROPER(텍스트) 와 같습니다.

LEFT (글자들 중 맨 앞글자부터 원하는 개수만큼 반환)

수식은 LEFT(글자, 개수) 와 같습니다.

LEFT 함수는 맨 앞글자부터, 지정하는 개수만큼의 글자를 잘라서 추출해줍니다.

RIGHT (글자들 중 맨 뒷글자부터 원하는 개수만큼 반환)

수식은 RIGHT(글자, 개수) 와 같습니다.

RIGHT함수는 맨 뒷글자부터, 지정하는 개수만큼의 글자를 잘라서 추출해줍니다.

MID (글자들의 중간 지점부터 원하는 개수만큼 반환)

수식은 MID(글자, 시작점, 개수) 와 같습니다.

MID함수는 지정한 순번의 글자부터, 지정하는 개수만큼의 글자를 잘라서 추출해줍니다.

FIND (특정 셀에서 원하는 문자가 몇 번째 위치에 있는지 반환)

수식은 FIND(텍스트, “글자”) 와 같습니다.

FIND함수를 사용할 때 유의할 점은 영어의 대소문자를 구분한다는 것입니다.

다시 말하면, 수식 안에 글자를 “C”로 적었을 때, 소문자 c가 있는 경우는 찾지 못합니다.

SEARCH (FIND와 동일한 기능을 제공하지만, 대소문자를 구분하지 않음)

수식은 SEARCH(텍스트, “글자”) 와 같습니다.

FIND의 한계점을 보완하기 위해 사용하는 기능으로, “C”를 입력해도 대소문자를 가리지 않고 찾아줍니다.

---

엑셀에서 가장 많이 쓰이는 4가지 차트

엑셀에서 제공하면서 가장 많이 쓰이는 다양한 차트의 종류에는 막대 차트, 꺾은선 차트, 원형 차트, 상자 수염 등의 4가지가 있습니다.

---

막대 차트

표에 존재하는 여러 값들을 막대 모양으로 시각화하여, 그 분포를 파악하도록 도와주고 수치 간의 대소 비교를 보여주는 그래프입니다.

막대 차트를 그리는 과정 및 방법

표에서 성명에 해당하는 C4~C19를 드래그 한 후, ctrl(command)을 누른 채로 급여 지급액인 G4~G19를 함께 드래그 해줍니다.

그러고 나서, 상단 메뉴의 삽입 → 막대그래프 모양을 클릭 → 2차원 세로 막대형을 선택해주면 됩니다.

그러면 위와 같이, 직원 별 급여 지급액이 막대 형태로 나타나는 것을 볼 수 있습니다.

확실히 숫자들 간 크기 비교가 훨씬 쉬워진 것을 느낄 수 있습니다.

하지만 막대가 정확히 어떤 값인지 표시가 되어있지 않아 정보가 부족하다는 생각이 들 때는 아래 과정을 통해 막대 그래프에 데이터값을 직접 표시해 줄 수 있습니다.

그리고 그래프에서 가장 많이 조정하게 되는 값이 바로 왼쪽에 존재하는 y축 값들로, y축의 최댓값과 최솟값, 표시된 값들 간 간격이 마음에 들지 않을 때는 이를 직접 수정하게 됩니다.

y축을 클릭해준 후 축 서식을 들어가면, 각 수치들을 바꿔줄 수 있는데, 예시에서는 축의 시작점을 500000, 최댓값을 1500000, 축 간 간격은 200000으로 변경했습니다.

축 기준을 변경하는 이유는, 각 막대 간 차이를 좀 더 두드러지게 만들어주기 위해서입니다.

또는, x축의 기준이 마음에 들지 않는다면, 차트 디자인 탭에서 행/열 전환을 눌러줌으로써 막대로 그리는 값을 바꿔줄 수 있습니다.

---

꺾은 선 차트

꺾은선 그래프는 단순히 데이터를 시각화 한다는 것 보다 특정 데이터가 시간의 흐름에 따라 어떻게 변화하는지를 보여준다는 장점을 가진 그래프입니다.

그래서 시간적 특성이 없는 데이터라면 굳이 꺾은선을 쓰기 보다는 막대 그래프를 사용하면 됩니다.

분명 막대 그래프와 동일하게 직원 별 급여 지급액의 대소 비교가 가능한 것은 확인할 수 있습니다.

하지만 각 숫자 간 어떤 연속선 상의 관계가 없는데 굳이 선으로 이어놓아서, 데이터 간 어떤 일련의 관계가 있다는 불필요한 오해를 만들어 낼 가능성이 있습니다.

이를 통해, 변화의 경향성을 압축적으로 보여주는 것은 꺾은선 그래프인 것을 확인할 수 있습니다.

---

원형 차트

원형 그래프는 비율의 표시만을 위해 이용되며, 특정 항목에 대해서 개별 값들이 전체에서 몇% 차지하는지 시각화하여 나타내는 차트입니다. 따라서, 원형 그래프에 나타는 항목의 총합은 100% 입니다.

 

예시로, 1사분기 매출액에서 각 부서가 차지하는 비중을 구해보려고 합니다.

부서들과 1사분기 매출액 항목들을 드래그 한 후, 원형 그래프를 만들게 되면 아래와 같은 모양이 됩니다.

각 항목의 정확한 비율을 숫자로도 표시해주고 싶을 때는 데이터 레이블에서 백분율을 설정해줌으로써 실행할 수 있습니다.

하지만 원형 그래프는 하나의 원만 만들어주기 때문에, 담을 수 있는 데이터의 기준이 제한되어 있으며, 여러 항목에 대한 시간의 경과를 담지 못 하는 단점을 가지고 있습니다.

따라서 시간의 경과를 시각화하여 보고 싶다면, 원을 각각 추출하거나, 표를 새로 만들어서 꺾은선으로 표현하는 방법을 시도해야 합니다.

---

상자 수염 그림

상자 수염은 특정 범주에 속하는 숫자들의 분포를 보여줍니다.

뿐만 아니라, 중간값, 최댓값, 최솟값, 사분위수, 이상값(outlier)에 대한 정보까지 모두 제공합니다.

위 표는 각 학생의 시험 성적을 나열해 놓은 것입니다.

이 데이터를 통해 어느 학생이 점수가 더 좋은지 등의 의문을 갖게 될 것이고, 이러한 정보를 한눈에 시각화 해주는 것이 상자 수염입니다.

상자수염을 실행하는 방법은 범위를 드래그 한 후, 차트 구역 하단의 화살표를 눌러 [차트 삽입] 창을 띄우고 [모든 차트]에서 상자 수염을 선택해주면 됩니다.

박스 간 간격도 너무 가깝고, x축에 ‘과목’ 대신 1이 들어가 있는 것이 이상할 경우 조정을 위해 ‘과목’과 ‘점수’를 기준으로 열을 정리해 준 후 다시 상자 수염을 그려봤습니다.

최댓값, 3사분위수, 중간값(2사분위수), 1사분위수, 최솟값의 레이블을 표시하니 더 보기 좋은 상자그림이 완성된 것을 확인할 수 있습니다.

---

조건부 서식

조건부 서식 : 특정 조건에 맞는 셀을 표시해주는 기능

 

조건부 서식에서 많이 사용되는 기능

• 상위/하위 규칙
• 셀 강조 규칙

---

상위/하위 규칙

특정 셀의 값이 상위 혹은 하위 X개나 X% 안에 포함되는지를 표시해주고, 해당 값이 평균 초과인지 미만인지도 표기해 줄 수 있는 기능입니다.

아래 표에서 업무성과을 기준으로 점수가 높은 상위 3명에게 선물을 준다고 가정하겠습니다.

우선, 업무성과 범위인 B4:B27을 드래그 해줍니다. 이후, 홈 탭에 있는 조건부 서식 → [상위/하위 규칙]을 클릭한 후, 상위 10개 항목을 누릅니다.

그러면 위와 같이 옵션 창이 뜨는데 여기서 상위 3명의 셀에 빨간색 표시를 해주겠습니다.

그 결과, 박상중, 나문이, 이지헌의 업무 성과에 색칠이 된 것을 확인할 수 있습니다.

이번에는 업무성과가 평균 미만의 직원들을 찾아보려고 합니다.

조건부 서식에서 평균 미만을 선택한 후, 초록색으로 표시해서 김송인, 이명수, 최성수, 김수철, 김희정, 전미수, 이승철, 최은지, 이명수, 박상중 직원이 평균 미만인 것으로 나타났습니다.

 

그리고 기준을 두 개 이상으로 설정하고 싶을 때는 직접 서식을 만들어서 지정을 해주면 됩니다.

우선, 두 평가의 범위를 드래그 한 후, 조건부 서식에서 [새 규칙]을 눌러줍니다.

[규칙 유형 선택]에서, [수식을 사용하여 서식을 지정할 셀 결정]을 클릭하면, 그 밑에 수식을 적는 빈칸이 생기게 됩니다.

빈칸에 AND라고 하는 괄호 안의 조건을 모두 동시에 만족시키는 값을 반환하라는 함수를 활용하여 업무성과와 기여도가 동시에 20점 미만인 직원을 선택하라는 식을 만들어 새 규칙을 만들어 주었습니다. (수식 : =AND($B5<20, $C5<20))

---

셀 강조 규칙

셀 강조 규칙은 셀의 조건에 따라 셀을 강조해주는 기능입니다.

우선, 특정 숫자보다 큰 셀을 표시하는 방법으로 셀 강조 규칙을 사용하겠습니다.

예시에서는 최은지보다 업무수행 점수가 좋은 직원들을 표시해보았습니다.

다음으로는, 특정 두 숫자 사이에 있는 값을 찾기 위해 [셀 강조 규칙]에서 [다음 값의 사이에 있음]을 활용했습니다.

이번에는 업무수행 평가 점수가 10점~20점 사이의 직원을 빨간색으로 표시보았습니다.

---

피벗테이블

엑셀을 통해 데이터를 가공할 때, 피벗 테이블은 많이 사용되는 파트이고, 잘 다룰줄 알아야 하는 분야입니다.

---

피벗테이블의 의미

피벗(Pivot)은 “중심”, 또는 “(회전) 축”이라는 뜻을 갖고 있으며, 테이블(Table)은 데이터에 관련된 상황에서는 “표”를 가리킵니다.

두 단어가 합쳐진 피벗 테이블(Pivot Table)은 “특정 기준을 중심 축으로 삼아 데이터들이 정렬되어 있는 표”라는 뜻이 됩니다.

즉, 우리가 관심있는 항목을 축으로 잡고, 표로 만들어 주는 기능이 피벗 테이블이라고 볼 수 있습니다.

---

피벗테이블 만들기

피벗 테이블을 만들기 위해서는, 우선 원하는 데이터 범위를 드래그를 통해 지정해줘야 합니다.

드래그를 완료한 후, [삽입] → [피벗 테이블] → [테이블/범위에서] 순으로 선택해 줍니다.

선택을 마치면, 만든 피벗 테이블을 현재 시트 위에 만들 것인지, 새로운 시트에 만들 것인지만 결정하면 됩니다.

새로운 시트를 선택하여 새로운 시트에 피벗 테이블 필드가 생성하고, 오른쪽 필드에서 확인하고 싶은 항목을 클릭하면, 엑셀이 자동으로 해당 항목을 기준삼아 데이터를 자동으로 정렬해줍니다.

피벗 테이블은 자신이 궁금한 기준에 맞춰 데이터를 자동으로 가공해 준다는 점에서 무척 편리합니다.

마지막으로, 피벗 테이블은 “필터”라는 기능을 지원하는데, 이는 우리가 원하는 항목에 해당하는 데이터만 표에 나타나게 해줍니다.

예를 들어, “직급”이라는 기준을 “필터”에 적용해봤습니다. B열 1행을 보면, (모두) 라는 기준과 함께 화살표가 생긴 것을 볼 수 있습니다. 이 화살표를 눌러 과장만을 선택하게 되면, 아래와 같이 과장과 관련된 급여액만을 골라서 볼 수 있게 됩니다.

짧게 정리하자면, 피벗 테이블은 기존의 정보를 우리가 원하는 기준에 따라 자동으로 정리하여 표를 만들어주는 기능입니다.

이러한 피벗 테이블은 아주 효율적이고 편리한 데이터 정렬 수단을 제공해줍니다.

---

이번 글에서는 전 글에 이어 엑셀의 함수 / 차트 / 조건부 서식 / 피벗테이블이 포함된 내용을 정리했습니다.

엑셀은 데이터 분석이나 데이터를 다루는 경우에 모두 필수적인 프로그램으로 개념을 확실히 숙지하고 활용할 필요가 있습니다.

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

---

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