Excel로 하는 데이터 분석 | 데이터 분석 실습 내용 전체 정리(참조, 함수, 피벗테이블, 차트, 전처리)

이 글은 코드잇 스프린트 데이터 분석가 과정 학습 기록입니다.
수업 내용과 느낀 점을 매일 정리하며 데이터 분석 공부 과정을 기록하고 있습니다.

🚩시작하며

첫 실습으로 이틀간의 짧은 실습 과정이었지만 'Excel로 하는 데이터 분석' 수업이 마무리된 기념으로 정리겸 글을 써본다.(지금 부트캠프 과정의 극초반인데다가 이틀이지만 거의 하루종일 하는 실습이라 나에겐 짧진 않았다는...) 앞으로도 꾸준히 코드잇 데이터 분석가 과정의 이론 수업이 하나씩 마무리 될 때마다 수업 기록을 정리해보겠다!

👩‍💻엑셀로 하는 데이터 분석 총정리

이번 엑셀 수업에서는 단순히 표를 만들고 꾸미는 방법만 배운 것이 아니라, 데이터를 어떤 기준으로 보고 어떻게 다뤄야 하는지까지 전반적으로 익힐 수 있었다. 파이썬이나 태블로 같은 도구들도 물론 중요하지만, 실제로는 데이터 분석가끼리만 일하는 것이 아니라 다양한 직무의 사람들과 협업하게 되기 때문에 엑셀은 생각보다 훨씬 더 중요한 도구라고 하셨다. 즉, 회사에서는 파이썬이나 태블로를 사용하지 않는 사람들과도 소통해야 하므로 가장 기본적이면서도 가장 자주 쓰이는 도구인 엑셀을 잘 다룰 줄 아는 것이 중요하다는 것이었다.
 
수업 중에는 피벗테이블과 VLOOKUP을 사용할 줄 알면 엑셀을 어느 정도 사용할 줄 안다고 볼 수 있다는 말씀도 있었다. 예전에 대학교 1학년 때 MOS 엑셀 자격증을 공부한 적이 있었지만... 지금은 거의 다 까먹은 상태라 이번에 다시 처음부터 차근차근 배운다는 마음으로 수업을 들었다. 전반적으로 이론보다는 실습 위주의 수업이었고 그래서 더 실무적으로 와닿았다. 강사님께서도 처음 입사했을 때 가장 많이 쓴 것이 엑셀이었다고 하셨는데, 막상 회사에 들어가면 아무도 엑셀 사용법을 하나하나 알려주지 않는다는 말이 특히 기억에 남았다.ㅎㅎ

❗️엑셀에서 가장 먼저 이해해야 하는 것: 행, 열, 셀, 테이블

엑셀을 켰을 때 가장 먼저 알아야 하는 것은 기능보다도 데이터가 어떤 구조로 이루어져 있는지 파악하는 것이다.
 
행(Row) 은 하나의 개체를 의미한다. 예를 들어 직원 정보 테이블이라면 한 행은 한 명의 직원을 의미하고, 상품 정보 테이블이라면 한 행은 하나의 상품을 의미한다. 즉, 행 하나하나는 하나의 대상이라고 이해하면 된다. 그래서 테이블을 처음 볼 때는 각 행의 데이터가 무엇을 의미하는지 먼저 파악하는 것이 중요하다. 직원 정보인지, 상품 정보인지, 주문 정보인지부터 정확히 봐야 이후 작업이 수월해진다.
 
반면 열(Column) 은 속성을 의미한다. 다루는 파일이 직원 정보 데이터라면 이름, 전화번호, 나이, 입사 일자, 팀, 직위, 직책, 연봉 같은 항목들이 모두 열이 된다. 즉, 열은 그 개체가 가진 특성이라고 볼 수 있다.
 
행과 열이 만나는 한 칸 한 칸이 바로 셀(Cell) 이고,
 
결국 이러한 행과 열이 모여 하나의 테이블(Table) 이 되는 것이다.
 
결론적으로 엑셀에서 데이터를 다루기 시작할 때는 무작정 값만 보기보다,
“각 행은 무엇을 의미하는가?”,
“각 열은 어떤 속성을 담고 있는가?”
를 먼저 파악하는 습관이 정말 중요하다는 것을 배웠다.

💻기본 조작을 익혀야 엑셀 작업 속도가 빨라진다

엑셀은 복잡한 함수만 중요한 것이 아니라 기본 조작을 얼마나 익숙하게 다루느냐에 따라 작업 속도가 크게 달라진다.
먼저 자주 사용한 단축키 중 하나는 Ctrl + Shift + 아래 방향키였다. 이 기능을 사용하면 해당 열의 데이터 범위를 한 번에 선택할 수 있다. 데이터가 길게 이어져 있을 때 매우 편리하다.
 
셀 선택 방식도 구분해서 알아둘 필요가 있다.
Ctrl은 일부만 선택할 때,
Shift는 이어서 범위를 선택할 때 사용한다.
또 Shift를 누른 채 처음 셀과 마지막 셀을 클릭하면 여러 셀을 한 번에 선택할 수 있다.
 
열을 정리하는 방법도 두 가지를 배웠다.
첫 번째는 열을 잘라낸 후 옮기고 싶은 자리 뒤의 열을 선택하고 잘라낸 셀 삽입하기를 누르는 방법,
두 번째는 열을 선택한 뒤 Shift를 누른 상태에서 십자 모양을 원하는 위치로 드래그하는 방법이다. 둘 다 열의 위치를 바꿀 때 유용하게 사용할 수 있다.
 
표 형식으로 정리할 때는 의미별로 열을 묶는 작업도 했다. 맨 위에 행을 삽입한 뒤 병합하고 가운데 맞춤을 이용해 관련 있는 열들을 하나의 제목 아래로 묶어주면 한눈에 구조가 더 잘 보인다.
 
또 이미 한 행에 적용한 서식을 다른 행에도 그대로 적용하고 싶을 때는 서식 복사를 사용하면 된다. 행을 선택한 뒤 서식 복사를 누르고, 적용하고 싶은 행을 선택하면 된다. 이런 기본 기능들이 사소해 보여도 실제로는 작업 흐름을 훨씬 빠르게 만들어준다.

🔢데이터 타입 지정이 중요한 이유

수업에서 계속 강조된 부분 중 하나가 바로 데이터 타입이었다. 데이터 타입은 단순히 보기 좋게 정리하기 위한 형식 지정이 아니라, 이후 계산과 가공 방식에 직접적인 영향을 주는 중요한 요소이다.
 
예를 들어 숫자로 이루어져 있어도 전화번호처럼 사칙연산이 필요 없는 값은 텍스트로 지정하는 것이 적절하다. 반대로 연봉처럼 계산이 필요한 값은 일반 숫자 형식으로 두거나, 더 보기 좋게 표현하기 위해 통화나 회계 형식으로 지정하는 것이 적절하다.
 
데이터 타입을 지정하는 이유는 크게 두 가지다.
첫째, 보기에 훨씬 깔끔하게 정리되기 때문이다.
둘째, 계산이나 처리 시 의도한 대로 정확하게 작동하게 하기 위해서이다.
 
즉, 엑셀에서 데이터 타입은 단순 서식 문제가 아니라 데이터의 의미를 반영하고, 이후 작업이 올바르게 이루어지도록 만드는 기본 설정이라고 볼 수 있다.

🖱️필터, 틀 고정, 정렬, 조건부 서식

엑셀에서 데이터를 빠르게 파악하고 정리할 때 가장 자주 쓰는 기능들이 바로 필터, 틀 고정, 정렬, 조건부 서식이다.
 
먼저 필터는 특정 데이터만 걸러서 보기 위한 기능이다. 단축키는 Ctrl + Shift + L 이다. 데이터를 분석할 때 전체를 다 보는 것보다 특정 조건에 맞는 데이터만 따로 보는 경우가 많기 때문에 매우 자주 사용하게 된다.
 
틀 고정은 데이터 양이 많을 때 유용하다. 아래로 스크롤을 내리다 보면 어떤 열이 어떤 의미였는지 헷갈릴 수 있는데, 이때 보기 → 틀 고정 → 첫 행 고정을 사용하면 첫 번째 행이 계속 보이기 때문에 훨씬 편하다. 데이터가 길어질수록 꼭 필요한 기능이라고 느꼈다.
 
정렬은 필터의 드롭다운 화살표를 눌러 오름차순, 내림차순으로 설정할 수 있다. 그런데 하나의 열 기준만이 아니라 여러 컬럼을 기준으로 정렬해야 하는 경우도 많다. 이럴 때는 사용자 지정 정렬을 사용한다. 여기에서 정렬 기준 추가를 눌러 1차 기준, 2차 기준처럼 여러 컬럼을 동시에 기준으로 삼을 수 있다.
 
조건부 서식은 조건에 따라 셀 서식을 자동으로 바꾸는 기능이다. 예를 들어 보다 큰 값, 상위 몇 개 항목, 중복 값 등을 강조할 수 있다. 상위 몇 개 항목을 설정했을 때 같은 값이 여러 개이면 하나로 묶여 처리될 수 있다는 점도 함께 알게 되었다. 색조를 활용하면 시각적으로 값을 훨씬 쉽게 구분할 수 있어서 보기에도 편했다.
 
중복값 확인 기능도 매우 유용했다. 다만 중복값이라고 해서 무조건 삭제하면 안 된다. 예를 들어 연봉처럼 같은 값이 여러 번 나오는 것이 자연스러운 데이터도 있지만, 회원번호나 주문번호처럼 원래 중복이 있으면 안 되는 데이터도 있기 때문이다. 결국 중복값은 데이터의 맥락을 보고 판단해야 한다는 점이 중요했다.(뒤에서 다시 설명한다.)

🤓엑셀 기초 실습에서 직접 다뤄본 내용

기초 수업이 끝난 뒤에는 직접 문제를 풀어보며 실습하는 시간도 있었다. 첫날이라 그런지 난이도는 비교적 쉬운 편이었고, 배운 기능들을 실제로 손으로 적용해보는 정도의 문제들이었다.
 
실습 내용은 다음과 같았다.

1. 표로 정리하기(셀 꾸미기)
2. 데이터 타입 지정하기
3. 필터 적용하기
4. 틀 고정하기
5. 조건부 서식 활용하기
6. 사용자 지정 정렬 활용하기

기초 기능들이라 어렵지는 않았지만, 엑셀은 직접 해보지 않으면 손에 잘 익지 않는 만큼 이런 실습이 확실히 도움이 되었다.

🔍수식 입력줄과 참조 개념 익히기

엑셀에서 데이터를 자유자재로 다루기 위해서는 수식 입력줄에 익숙해지는 것이 정말 중요하다고 한다.
 
기본적으로 수식은 셀을 클릭한 뒤 = 를 입력하고, 계산하고 싶은 셀을 클릭하여 연산자를 이용해 작성한다. 이렇게만 해도 기본적인 계산은 충분히 가능하다.
 
수식을 하나의 셀에 입력한 뒤 열 전체에 적용하는 방법도 여러 가지가 있었다.
첫 번째는 셀 우측 하단의 점을 드래그하는 방법,
두 번째는 셀을 복사한 뒤 Shift를 누른 채 방향키로 범위를 선택하고 붙여넣기 하는 방법이다.
이 외에도 여러 방법이 있지만, 기본적으로는 드래그와 복사-붙여넣기가 가장 많이 쓰인다.
 
숫자를 정리할 때는 셀 스타일에서 쉼표 형식을 사용해 소수점이나 숫자 표시 방식을 통일할 수 있었다.
 
여기서 중요한 주의점도 있었다. 수식을 드래그하면 참조되는 셀이 함께 밀리기 때문에, 참조 위치가 바뀌면 안 되는 셀은 반드시 고정 여부를 신경 써야 한다는 것이다.

📌상대 참조, 절대 참조, 혼합 참조

엑셀에서 가장 중요하면서도 헷갈리기 쉬운 개념 중 하나가 바로 참조이다.

• 상대 참조는 드래그를 할 때마다 셀의 위치가 함께 바뀐다.
• 절대 참조는 행과 열을 고정한다.
• 혼합 참조는 행과 열 둘 중 하나만 고정한다.

행이나 열을 고정하고 싶을 때는 수식에서 고정할 부분 앞에 $ 기호를 붙이면 된다. 행과 열을 둘 다 고정하려면 둘 다 붙여야 한다. 이때 매우 유용한 단축키가 F4 인데, 누를 때마다 참조 형식이 바뀌어서 빠르게 설정할 수 있다.
 
또 참조는 같은 시트 안에서만 사용하는 것이 아니다. = 를 입력한 뒤 다른 시트로 이동해서 다른 시트의 셀을 참조하는 것도 가능하다.
 
결국 중요한 것은 어떤 상황에서 행을 고정해야 하는지, 열을 고정해야 하는지, 둘 다 고정해야 하는지를 잘 판단하는 것이다. 실제로 함수보다도 이 참조 개념이 훨씬 중요하게 느껴졌다. 참조를 잘못 잡으면 수식이 의도와 다르게 복사되어 결과가 전부 틀어질 수 있기 때문이다.
 
추가로, 열을 선택한 뒤 경계선 부분에서 더블 클릭하면 열 너비가 데이터 길이에 맞게 자동 조정된다는 점도 함께 배웠다. 아주 기본 기능이지만 표를 깔끔하게 정리할 때 꽤 유용하다.

🧮대표값과 숫자 함수 정리

엑셀에서는 대표값과 기본 통계 함수도 자주 사용한다. 데이터 분석을 할 때는 어떤 값을 대표값으로 볼 것인지부터 정리해야 하기 때문에 이 파트도 중요했다.
 
먼저 평균(average, mean) 은 가장 익숙한 대표값으로, 데이터의 합을 데이터 개수로 나눈 값이다. 일상생활에서도 가장 많이 쓰이는 값이지만, 이상치가 있는 경우에는 평균이 극단적으로 왜곡될 수 있기 때문에 항상 적절한 대표값은 아닐 수 있다.
 
이럴 때는 중앙값(median) 을 사용하는 것이 더 적절할 수 있다. 중앙값은 데이터를 오름차순으로 정렬했을 때 가운데에 위치한 값이다. 데이터 개수가 짝수인 경우에는 가운데 두 값의 평균이 중앙값이 된다. 이상치의 영향을 덜 받기 때문에 실제 데이터 분석에서도 자주 비교하게 되는 값이라고 한다.
 
최빈값(mode) 은 가장 빈번하게 등장하는 값이다. 수치형 데이터보다는 혈액형 같은 범주형 데이터에서 더 자주 사용된다.
 
관련 함수는 다음과 같다.

• 평균: AVERAGE()
• 중앙값: MEDIAN()
• 최빈값: MODE.SNGL(), MODE.MULT()

MODE.SNGL 은 가장 먼저 찾아지는 최빈값 하나를 반환하고, MODE.MULT 는 최빈값이 여러 개일 경우 모두 반환한다.
 
데이터 개수를 세는 함수도 함께 배웠다.
COUNT() 는 숫자가 입력된 셀의 개수를 세고,
COUNTA() 는 숫자가 아닌 셀의 개수를 센다.
 
범주의 개수를 세고 싶을 때는 UNIQUE() 를 이용해 고유값을 먼저 추출한 뒤, 그 개수를 COUNTA() 로 세면 된다. 즉, COUNTA(UNIQUE()) 형태로 사용하는 것이다. (unique가 고유한이라는 뜻이다.)
 
절댓값은 ABS() 를 사용하고, 반올림/올림/내림 관련 함수도 함께 배웠다.

• ROUND() : 반올림
• ROUNDUP() : 올림
• ROUNDDOWN() : 내림

수업에서는 반올림이 가장 자주 쓰이고, 올림과 내림은 상대적으로 덜 사용한다고 하셨다.
 
또 TRUNC() 와 INT() 도 배웠다.
TRUNC() 는 특정 소수점 이하를 버리는 함수이고,
INT() 는 가장 가까운 정수 형태로 만드는 함수이다.
즉, 둘의 차이는 특히 음수 데이터에서 비교했을 때 더 분명하게 나타난다.
 
순서 함수도 많이 사용한다고 하셨다.

• RANK.EQ()
  : 동일한 값을 같은 순위로 처리하기 때문에 중복 순위가 나올 수 있다.
• RANK.AVG()
  : 동일한 값은 평균 순위로 처리하기 때문에 순위가 소수점으로 나올 수 있다.

또 몇 번째로 큰 값, 작은 값을 구할 때는 LARGE(), SMALL() 을 사용할 수 있다. 아주 자주 쓰이지는 않는다고 하셨지만 알아두면 좋다.
 
하위 몇 퍼센트 값을 구할 때는 PERCENTILE.INC() 을 사용한다. 예를 들어 10%라면 괄호 안 비율 자리에 0.1을 입력하면 된다.

💬텍스트 함수 정리

텍스트 함수 실습에서는 직원 이름, 영문 이름, 이메일 주소가 들어 있는 자료를 활용했다. 숫자 함수 못지않게 텍스트 함수도 실무에서 많이 사용된다고 하셨다.
 
먼저 대소문자를 정리하는 함수들이 있다.

• LOWER() : 영문을 모두 소문자로 바꾼다.
• UPPER() : 영문을 모두 대문자로 바꾼다.
• PROPER() : 영단어의 앞글자만 대문자로 바꾼다.

문자열을 연결할 때는 CONCAT() 를 사용한다. (concatenate 가 연결하다는 뜻이라고 하셨다.) 괄호 안에 연결하고 싶은 셀이나 문자를 넣으면 되고, 띄어쓰기나 괄호처럼 따로 넣고 싶은 문자가 있다면 따옴표를 활용하면 된다. 단순히 & 기호를 사용해서 문자열을 연결할 수도 있다. 이 함수와 연결 방식은 실무에서도 많이 쓴다고 하셨다.
 
왼쪽이나 오른쪽에서 글자를 잘라낼 때는 LEFT(), RIGHT() 사용한다. 이 역시 자주 쓰는 함수이다.
 
그런데 이때 앞뒤 공백이 있으면 원하는 결과가 제대로 나오지 않을 수 있다. 이럴 때는 TRIM() 을 사용해 공백을 제거한다. (trim이 다듬다라는 뜻이라고 한다.)
 
텍스트 길이가 일정하지 않은 경우에는 FIND()가 유용하다. 예를 들어 이메일 주소에서 아이디만 추출하거나 회사명만 추출할 때처럼, 문자열 길이가 서로 다를 때 많이 활용된다.

• FIND(찾을 문자열, 찾을 위치)

문자열은 당연히 따옴표로 감싸서 입력한다. FIND 함수는 문자열의 위치를 숫자로 반환하므로, 그 숫자를 이용해 LEFT, RIGHT, MID 함수와 조합하여 원하는 문자열을 추출할 수 있다. 예를 들어 =LEFT(C2,FIND("@",C2)) 와 같이 사용할 수 있다. (실습에서 사용한 수식이다..)
 
가운데 위치한 문자를 추출할 때는 MID() 를 사용한다.

• MID(셀, 위치, 길이)

즉, 특정 위치에서부터 원하는 길이만큼 텍스트를 가져오는 함수이다.
 
FIND()와 SEARCH()의 차이를 정리했다.
FIND()는 대소문자를 구분하고,
SEARCH()는 대소문자를 구분하지 않는다.
즉, 대소문자 구분 없이 찾고 싶은 상황에서 FIND()를 사용하면 #VALUE! 에러가 날 수 있다.
 
문자열을 다른 문자열로 바꾸고 싶을 때는 REPLACE()를 사용한다.

• REPLACE(기존 텍스트, 대체할 위치, 대체될 텍스트의 길이, 대체할 텍스트)

텍스트 길이를 구할 때는 LEN() 을 사용한다. 이 함수는 공백을 포함해 길이를 계산한다는 점이 중요하다. 공백을 제외하고 싶다면 TRIM() 을 먼저 적용해야 한다.

⏰날짜 및 시간 함수 정리

날짜와 시간 함수는 사내 인사 자료를 통해 실습하였다. 경과 일수, 햇수, 입사년도, 연차 등을 함수를 통해 구해보는 방식이었다.
 
먼저 현재 날짜와 시간을 구하는 함수가 있다.

• TODAY() : 현재 날짜 출력
• NOW() : 현재 날짜와 시간 출력

시간을 직접 입력할 때는
TIME(시, 분, 초)

특정 시간만 추출할 때는
HOUR() : 시간 추출
MINUTE() : 분 추출
SECOND() : 초 추출
을 사용한다.
 
날짜도 똑같다.
 
날짜를 직접 입력할 때는
DATE(년, 월, 일)

특정 날짜 요소를 추출할 때는
YEAR() : 연도 추출
MONTH() : 월 추출
DAY() : 일 추출
을 사용한다.
 
날짜 계산은 생각보다 단순했다. 날짜 셀끼리 연산을 하면 경과 일수를 구할 수 있다. 햇수나 연차는 YEAR() 함수를 활용해 계산할 수 있다.
 
요일을 구할 때는 WEEKDAY(날짜 셀, 반환 유형) 을 사용한다. 다만 반환값은 숫자이므로, 이를 문자로 바꾸기 위해 TEXT() 함수를 활용한다.

• TEXT(셀,"aaaa") → 월요일
• TEXT(셀,"aaa") → 월
• TEXT(셀,"dddd") → Monday
• TEXT(셀,"ddd") → Mon

여기서 중요한 점은 TEXT() 안에 들어가는 셀이 요일이 숫자로 변환된 값이어야 한다는 것이다.
 
추가로 아주 사소하지만 유용했던 팁도 있었다. 엑셀에서 함수 자동완성 목록이 뜰 때 엔터를 누르면 자동완성된 함수가 입력될 것 같지만, 실제로는 지금까지 쓴 텍스트만 그대로 입력된다. 이때는 엔터가 아니라 Tab 키를 눌러야 자동완성 목록의 함수가 입력된다. 자잘하지만 꽤 유용한 팁이었다.

☝️실무에서 많이 쓰는 조건 함수

이 파트부터는 실무 활용도가 특히 높다고 하셨다. 실습도 계속 사내 인사 자료를 기반으로 진행했다.
 
가장 기본이 되는 함수는 IF() 이다.

• IF(수식, 참일 때 반환할 값, 거짓일 때 반환할 값)

예를 들어
IF(I2>=10000,"1억 이상","1억 미만")
처럼 사용할 수 있다.
 
여기서 중요한 것은 초과인지 이상인지, 미만인지 이하인지를 정확하게 구분하는 것이다.

• 이상: >=
• 이하: <=
• 같지 않음: <> (<>은 이번 수업을 통해서 처음 보는 것이었다..)

한 가지, 수식을 드래그할 때는 언제나 의도치 않은 상대 참조가 생기지 않도록 조심해야 한다.
 
조건이 두 가지 이상일 때는 AND() 함수를 사용할 수 있다.
조건이 많아질 경우에는 IFS() 가 훨씬 편하다.
 
예를 들어
=IFS(H2>=40,"40대",H2>=30,"30대",H2>=20,"20대")
처럼 사용할 수 있다.
 
조건에 맞는 셀의 개수를 셀 때는 COUNTIF() 를 사용한다.
예시:

• 직위가 차장인 직원 수
  =COUNTIF(F2:F21,"차장")
• 연봉이 1억 이상인 직원 수
  =COUNTIF(I2:I21,">=10000")
• 연봉이 전체 평균 연봉 이상인 직원 수
  =COUNTIF(I2:I21,">="&AVERAGE(I2:I21))

조건이 여러 개면 COUNTIFS() 를 사용한다.
예시:

• 연봉이 전체 평균 이상이면서 직위가 과장인 직원 수
  =COUNTIFS(I2:I21,">="&AVERAGE(I2:I21),F2:F21,"과장")

조건을 만족하는 값들의 평균을 구할 때는 AVERAGEIF() 를 사용한다.
예시:

• 직위가 과장인 직원들의 평균 연봉
  =AVERAGEIF(F2:F21,"과장",I2:I21)

조건이 여러 개일 경우에는 AVERAGEIFS() 를 사용한다.
예시:

• 직위가 대리이면서 30세 이하인 직원들의 평균 연봉
  =AVERAGEIFS(I2:I21,F2:F21,"대리",H2:H21,"<=30")

조건을 만족하는 값들의 합을 구할 때는 SUMIF() 를 사용한다.
예시:

• 마케팅팀 직원들의 연봉 합산값
  =SUMIF(E2:E21,"마케팅팀",I2:I21)

조건이 여러 개면 SUMIFS() 를 사용한다.
예시:

• 마케팅팀 직원이면서 팀장이 아닌 직원들의 연봉 합산값
  =SUMIFS(I2:I21,E2:E21,"마케팅팀",G2:G21,"<>팀장")

이 파트에서 특히 중요하고 동시에 헷갈려했던 것은 따옴표와 &의 사용법이었다.
따옴표는 단순히 문자열을 표시할 때만 쓰는 것이 아니라, 조건식을 텍스트로 표현할 때도 사용한다. 즉, 따옴표의 쓰임은 크게 두 가지이다.

1. 문자열 자체를 표시할 때
2. 조건식을 텍스트로 표현할 때

예를 들어
">="&AVERAGE(I2:I21)
이렇게 써야 평균값이 실제로 계산되어 조건에 반영된다.
 
반대로
">=AVERAGE(I2:I21)"

처럼 함수까지 따옴표 안에 넣어버리면, AVERAGE() 함수가 계산되는 것이 아니라 그 자체가 텍스트로 인식되어버린다. 그래서 의도한 결과가 나오지 않는다.
 
즉, 조건식에 함수가 들어가는 경우에는 연산자 부분만 따옴표로 쓰고, 함수는 & 기호로 연결해야 한다는 점이 매우 중요했다.

🔗VLOOKUP과 MATCH

VLOOKUP() 은 회사에서 정말 많이 사용하는 함수라고 하셨다. 모르면 실수가 빈번하게 발생할 수 있기 때문에 꼭 익혀야 한다고 강조하셨다. 실습에서는 복지몰 구매내역 시트와 직원 정보 시트를 활용하였다.
 
VLOOKUP() 은 원하는 위치의 값을 특정값과 매치하여 가져오는 함수이다.

• VLOOKUP(검색할 값, 표 범위, 열 번호, 유사한 값 또는 정확한 값)

즉, 기준이 되는 값을 가지고 다른 표에서 원하는 정보를 찾아오는 것이다.
 
그런데 여기서 열 번호를 직접 입력해야 하다 보니, 열이 많을 경우 몇 번째 열인지 세는 것이 번거롭다. 이때 MATCH() 함수를 사용하면 열의 위치를 쉽게 찾을 수 있다.

• MATCH(찾을 값, 찾을 범위, 값 유형)

MATCH() 를 활용하면 열 위치를 한 번에 찾을 수 있다. 다만 MATCH로 위치를 구해 VLOOKUP에 넣어 사용할 때도 절대 참조를 꼭 신경 써야 한다.
 
VLOOKUP() 의 한계도 함께 배웠다.
가져오려는 자료가 기준이 되는 열의 오른쪽에 있어야만 조회 가능하다는 점이다. 즉, 왼쪽에 있는 값은 가져올 수 없다. 그래서 이후에 다른 함수들을 배우게 된다.

🖇️INDEX, MATCH, XLOOKUP

VLOOKUP() 의 한계를 보완하기 위해 INDEX, MATCH, XLOOKUP 도 함께 배웠다.
 
먼저 INDEX() 는
INDEX(테이블 범위, 행 위치, 열 위치)
형태로 사용한다.
 
하지만 INDEX()만 단독으로 사용하면 행 위치와 열 위치를 일일이 세어야 해서 비효율적이다. 그래서 보통은 MATCH() 와 함께 사용한다. MATCH() 로 행과 열 위치를 구한 뒤 INDEX() 와 결합하면 훨씬 효율적으로 값을 조회할 수 있다.
 
XLOOKUP() 도 구조가 직관적이었다.

• XLOOKUP(검색할 값, 검색할 범위, 반환할 범위)

💉에러 처리 함수: IFNA, IFERROR

조회 함수나 수식을 사용하다 보면 기준값에 오타가 있거나 데이터가 맞지 않아서 #N/A 에러가 발생할 수 있다. 예를 들어 직원 이름에 오타가 있다고 가정하면, 수식을 사용할 경우 조회가 되지 않아 #N/A 에러가 발생한다.
 
이럴 때 사용할 수 있는 함수가
IFNA() 와 IFERROR() 이다.

• IFNA(셀, 반환값)
• IFERROR(셀, 반환값)

둘 다 #N/A 오류를 처리할 수 있고, 특히 IFERROR() 는 더 다양한 종류의 에러까지 처리할 수 있다.
 
반환값으로는 예를 들어
“오타 의심”, “잘못 기재”
같은 문구를 넣을 수 있다.
 
따라서 애초에 전체 함수 바깥에 IFERROR() 를 감싸두면, 에러가 나는 경우에만 내가 설정한 반환값이 출력된다. 실무에서는 더 다양한 에러를 처리할 수 있는 IFERROR() 를 더 많이 사용한다고 하셨다.

📊피벗테이블과 차트

피벗테이블과 차트는 엑셀에서 데이터를 요약하고 시각화할 때 가장 핵심적인 기능 중 하나라고 느꼈다.
수업에서 가장 많이 사용하는 그래프 종류로는 다음 세 가지를 꼽으셨다.

• 막대 그래프
• 라인 그래프(선 그래프)
• 파이 차트

각 그래프가 적합한 상황도 함께 정리할 수 있었다.

• 시간의 흐름에 따른 추이를 보고 싶을 때 → 라인 그래프
• 범주별 수치를 비교하고 싶을 때 → 막대 그래프
• 전체를 100%로 보았을 때 범주별 비율을 보고 싶을 때 → 파이 차트

결국 시각화는 자료를 더 효과적으로 전달하기 위해 사용하는 것이다.
 
피벗테이블을 사용하는 이유는 원본 데이터만으로는 원하는 부분을 빠르게 찾기 어려운 경우가 많기 때문이다. 피벗테이블은 원본 데이터에서 필요한 정보만 뽑아 새로운 테이블 형태로 정리해주는 기능이다.
피벗테이블 만드는 순서는 다음과 같다.

1. 시트 전체 선택
2. 삽입 → 피벗테이블
3. 기존 시트 또는 새 시트에 생성

피벗테이블은 필터, 열, 행, 값의 네 가지 요소로 구성할 수 있다. 각 필드를 드래그 앤 드롭 방식으로 넣으면 된다.
 
특히 값 영역에서는 합계, 개수, 평균을 가장 많이 사용한다고 하셨다.
 
디자인 탭의 보고서 레이아웃에서는 피벗테이블 표시 형식을 바꿀 수 있고, 필요하다면 부분합을 제거하는 것도 가능하다.
 
피벗테이블을 만든 뒤에는 삽입 메뉴에서 그래프를 생성할 수 있다. 그래프를 만든 후에도 차트 요소를 통해 눈금선, 범례 등의 요소를 추가하거나 삭제할 수 있다. 라인 그래프의 완만한 선, 그래프 색상 변경, 강조 효과 등 다양한 꾸미기 기능도 활용할 수 있다.

⌨️엑셀을 활용한 데이터 전처리

데이터 분석 프로세스에서 전처리는 매우 중요한 단계이다. 수업에서는 이 전처리 작업을 엑셀로도 해볼 수 있다는 점을 실습을 통해 확인할 수 있었다.
 
핵심은 다음 세 가지였다.

• 중복값 처리
• 결측값 처리
• 이상치 처리

먼저 중복값은 말 그대로 같은 데이터가 여러 번 반복되어 있는 경우이다. 하지만 중복이라고 해서 무조건 삭제하는 것은 아니다. 어떤 데이터에서는 중복이 자연스러울 수 있고, 어떤 데이터에서는 반드시 제거해야 할 수도 있기 때문이다. 결국 중복값도 맥락을 보고 판단해야 한다.
 
결측값은 값이 입력되지 않았거나 알 수 없는 경우를 의미한다. 이 경우에는 행 삭제, 열 삭제, 다른 값으로 대체하기 등 여러 방식으로 처리할 수 있다. 만약 특정 열 전체에 결측값이 너무 많다면 열 자체를 삭제하는 것도 하나의 방법이 될 수 있다.
 
이상치(outlier) 는 대부분의 데이터와 비교했을 때 유난히 크거나 작은 값이다. 입력 실수 등으로 발생하는 경우가 많으며, 분석 결과를 왜곡할 수 있기 때문에 꼭 확인해야 한다. 이 경우 삭제하거나, 최대값/최소값/평균값 등으로 대체하는 방법이 있을 수 있다.
전처리에서 가장 중요한 것은 기계적으로 값을 없애는 것이 아니라, 데이터의 의미를 먼저 파악한 뒤 적절한 해결 방법을 선택하는 것이라고 느꼈다.

💻주문내역, 고객정보, 상품정보 시트로 진행한 전처리 실습

이번 전처리 실습은 이전에 보았던 인사 자료보다 훨씬 데이터 양이 많았다.

• 주문내역 시트: 2004개 행 × 9개 열
• 고객정보 시트: 151개 행 × 4개 열
• 상품정보 시트: 61개 행 × 5개 열

행 수가 많다 보니 하나하나 스크롤해서 보는 방식은 비효율적이었다. 그래서 Ctrl을 누른 채 방향키를 아래 또는 위로 눌러 한 번에 끝으로 이동하는 기능이 훨씬 중요하게 느껴졌다. 또 Ctrl + Shift + L 을 이용해 먼저 필터를 씌우고 전체 데이터를 파악하는 것이은 이제 기본이 되었다.

✂️중복값 확인과 제거

중복값을 확인할 때는 조건부 서식을 활용했다. 중복값에 색을 지정하면 눈에 잘 띄지만, 지금처럼 행 수가 아주 많은 경우에는 화면을 하나하나 스크롤해서 확인하는 것이 불가능하다.
 
이때도 필터 기능이 도움이 되었다. 필터는 값 기준뿐 아니라 색 기준으로도 적용할 수 있기 때문에, 중복값에 지정된 색만 따로 걸러서 훨씬 쉽게 확인할 수 있다.
 
중복값 제거는
시트 전체 선택 → 데이터 → 중복값 제거
순서로 진행한다. 제거가 끝나면 몇 개의 중복값이 제거되었다는 안내창도 나온다.
 
실습에서는 주문번호가 중복된 주문건을 제거하였다.

또 한 가지 알게 된 점은, 조건부 서식을 지정했는데도 색 필터가 적용되지 않는다면 그건 실제로 중복값이 없다는 뜻일 수 있다는 점이다.

🗂️결측값 확인과 처리

결측값은 필터를 적용했을 때 ‘(필드 값 없음)’ 항목을 통해 확인할 수 있다. 또는 Ctrl 키를 누른 채 방향키를 사용해 빈 셀 구간을 확인할 수도 있다. 하지만 둘 다 아주 효율적인 방법은 아니다. 그래서 강사님께서도 데이터 양이 많아지면 결국 파이썬이 필요하다고 하셨다.
 
여기서도 중요한 것은 결측값이 있다고 해서 무조건 제거하는 것이 아니라, 왜 비어 있는지 의미를 먼저 파악해야 한다는 점이다.
 
실습에서는 주문내역 시트의 ‘할인유형’ 열에 결측값이 많았다. 그런데 할인금액 열이 0인 경우라면 할인유형이 비어 있는 것은 오히려 자연스러운 상황이었다. 즉, 이 결측은 문제가 아니었다.
 
반면 할인금액이 존재함에도 불구하고 할인유형이 결측인 값이 1개 있었고, 이 경우에는 여러 처리 방법 중 하나로 N/A 로 대체했다.
 
이 사례를 보면서 결측값도 무조건 삭제하거나 채우는 것이 아니라, 데이터 의미를 해석한 뒤 처리해야 한다는 점을 다시 느꼈다.

📝이상치와 오탈자 확인

이상치가 들어가 있으면 나중에 분석을 진행할 때 대표성이 떨어지기 때문에 반드시 처리가 필요하다.
 
실습에서는 주문내역 시트의 ‘수량’ 열에 100000이라는 값이 있었다. 다른 값들은 대부분 1, 2, 3, 4, 5 수준이었기 때문에 이 값은 명백한 이상치였다.
 
이 경우 해당 행의 고객번호를 확인한 뒤, 고객정보 시트에서 같은 고객번호를 찾아보았더니 존재하지 않는 값이었다. 즉, 잘못 입력된 주문 데이터라고 판단할 수 있었고, 이 경우에는 삭제 처리하였다. 주문 자체가 유효하지 않기 때문이다.
 
또 고객정보 시트에서는 한글로 표기되어야 하는 ‘지역’ 열에 한 셀만 영어로 적혀 있는 경우도 있었다. 이럴 때는 단순 오탈자 또는 표기 불일치 문제이므로 한글로 수정해주면 된다.

💡엑셀 전처리의 한계와 파이썬의 필요성

이번 전처리 작업을 하면서 느낀 점은, 엑셀로도 기본적인 전처리는 충분히 가능하지만 데이터 양이 많아질수록 시간이 꽤 많이 든다는 것이다. 직접 눈으로 보고 하나하나 필터를 걸고 확인하는 작업이 많기 때문이다.
 
그래서 이런 작업은 파이썬으로 처리하면 시간을 훨씬 단축할 수 있다고 하셨다. 엑셀은 데이터를 직접 눈으로 확인하고 구조를 이해하는 데 강점이 있고, 파이썬은 이런 반복 작업을 더 효율적으로 자동화할 수 있다는 점에서 둘이 자연스럽게 연결된다고 느꼈다.

🧩테이블 합치기

마지막으로 각 테이블에서 연결고리가 되는 컬럼을 찾아 VLOOKUP() 을 이용해 테이블을 하나로 합칠 수 있다는 내용도 다루었다. 이번에는 이 부분까지 실습을 진행하지는 않았지만, 데이터 분석에서는 여러 시트나 여러 테이블에 흩어진 정보를 하나로 합쳐서 분석용 테이블을 만드는 일이 많기 때문에 매우 중요한 개념이라고 느꼈다.

📚마무리

엑셀 실습에서는 단순히 기능 몇 가지를 배우는 데서 끝나지 않고, 데이터를 보고 구조를 이해하고, 정리하고, 계산하고, 조건을 걸고, 다른 시트의 값을 불러오고, 시각화하고, 전처리하는 전체 흐름을 한 번에 경험할 수 있었다.
 
특히 다시 정리해보면 다음 내용들이 핵심이었다.

• 데이터를 볼 때는 먼저 행과 열의 의미를 파악해야 한다.
• 데이터 타입은 단순한 서식이 아니라 이후 계산과 분석에 영향을 주는 중요한 요소이다.
• 상대 참조, 절대 참조, 혼합 참조를 상황에 맞게 구분해서 사용해야 한다.
• IF, COUNTIF, AVERAGEIF, SUMIF 같은 조건 함수는 실무 활용도가 높다.
• VLOOKUP, MATCH, INDEX, XLOOKUP 은 데이터를 연결하고 조회하는 핵심 함수이다.
• 피벗테이블은 많은 데이터를 빠르게 요약하고 정리하는 데 매우 유용하다.
• 전처리에서는 중복값, 결측값, 이상치를 무조건 기계적으로 처리하지 말고 데이터의 의미를 먼저 봐야 한다.

엑셀은 익숙한 프로그램처럼 느껴지지만, 막상 이렇게 다시 정리해보니 생각보다 훨씬 많은 기능이 있고 데이터 분석의 기본기를 다지는 데 정말 중요한 도구라는 것을 느낄 수 있었다. 기초 기능부터 함수, 조회, 차트, 피벗테이블, 전처리까지 전체 흐름을 한 번에 정리할 수 있었던 수업이었다.