[Part 1,2] 엑셀로 끝내는 실전 데이터 분석 강의 - 혼합참조, 빠른 실행도구 모음, 중복항목 제거, 텍스트 나누기, 필터 활용
위나
2023. 2. 6. 16:40
목차
1. 엑셀 소개 및 기본 원리
행,열,셀의 특성 / 상대,절대,혼합 참조 / 수식값 서식, 함수의 특성 이해하기
2. 업무 속도 향상시키기
빠른 실행도구 모음 / 중복항목 제거 및 텍스트 나누기 & 합치기 / 모두 바꾸기 활용,필터 활용
3. 핵심 함수만 확실하게 알기
기본 함수 / IF 중첩을 통한 데이터 분류 / VLOOKUP / HLOOKUP / COUNTIF / SUMIF / 날짜 함수 / IFERROR
4. 시각화
다양한 차트 / 조건부 서식
5. 엑셀 전문가로 거듭나기
손익계산서(raw data다루기, 피벗테이블, 양식 만들기, SUMIFS로 합계 구하기[raw data연동], 분기와 누계 데이터 추가) / 월별 손익 조회 장표 / 재직증명서 만들기 / 상품재고현황 파악 / 간트차크 그리기 / 대시보드 만들기 / OFFSET 함수 / OFFSET함수와 동적 범위 / SUMPRODUCT / MATCH, INDEX
6. 리더에게 인정받는 자료 작성법 보고자료 만들기 / Superstore 대시보드 만들기 / 엑셀 총정리
7. 엑셀로 하는 데이터 분석 데이터 분석 기초 / EDA의 중요성 / 기초 통계량 게산 / 피벗테이블 활용 / BOXPLOT과 산점도 그리기 / 데이터 전처리(날짜 데이터 처리, 카테고리 지정하기, 필요한 데이터 불러오기[VLOOKUP]) / 데이터 가공(필요한 데이터 불러오기[VLOOKUP X MATCH], 피벗테이블 활용, COUNT 계열 함수로 개수 파악, 데이터 분석 도구 추가, 상관 분석, 분산분석, 회귀분석, 시계열 분석[FORCAST.ETS])
8. 실전 프로젝트 starwars 캐릭터 데이터 분석 / super store 데이터 분석 / 농수산물 가격 데이터 분석
9. 실무 필수 VBA VBA의 개념 / VBA 개발 환경 설정 / VBA 기초 / VBA 모듈,프로시저,변수 개념 / Sub 프로시저 작성 / Dim 변수 선언 / 범위 선택[RANGE, CELLS] / 데이터 입력[VALUE, FORMULA] / 데이터 지우기 / 기본 문법[조건문.순환문,사용자 정의 함수] / 업무 자동화 따라하기 : 여러 시트(파일)의 데이터를 한 시트에 통합하기
10. [부록] 웹 크롤링 웹사이트 구성의 이해 - 페이지 분석해보기 / 크롤링을 위한 준비 및 설치 / 관련 법적 이슈 / 이커머스 상품페이지 스크래핑
11. 반복 업무를 자동으로 WITH 엑셀,매크로 파이썬으로 엑셀을 다루기 위한 기본기 장착하기 / 고객사 월간 정산 내역 자동화하기 / 마우스,키보드 자동화 / 넷플릭스 오늘의 Top10 정보 크롤링하기
1
상대,절대,혼합 참조
모든 함수를 쓸 때, 해당 셀에 어떤 참조를 걸어야 하는지 알아야 정확한 값을 얻을 수 있다
엑셀의 기본 설정은 상대 참조이다.
Fn + F4를 누르면 자물쇠 역할을 하는 $가 생기며, 행과 열 모두 절대참조인 상태가 된다.
한번 더 누르면 행만 절대참조인 상태 (혼합 참조, F$5 => 5행 앞에만 자물쇠가 붙음)
또 한번 누르면 열만 절대참조인 상태
마지막으로는 누르면 다시 상대 참조로 돌아옴
Key points
함수를 다른 셀에 붙여넣기할 때에 정확한 값을 얻기 위해서, 효율적인 함수 사용을 위해 필수적임
옆인지, 밑인지, 옆으로 간 후 밑으로 가서 복사할지 복사할 방향을 항상 염두에 두고 함수를 작성해야 한다.
수식/값/서식의 개념 이해하기
서식 : 디자인 요소와 데이터의 표시 형식
단축키 Ctrl+1(우클릭-셀 서식)
Key point
서식은 모든 셀에 있다. 모든 셀에는 값/수식 둘 중 한가지 데이터만 들어갈 수 있는데, 해당 셀에 무슨 데이터가 들어가든 그 셀을 둘러싼 서식은 항상 존재한다.
2
빠른실행도구모음 설정 및 사용하기
처음 보는 데이터라면 드래그해서 우측 아래에 데이터 개수 확인하기
단축키 F12 : 다른이름으로 저장
좌측 상단에 빠른 실행 도구 모음 - 기타 명령(M) - 모든 명령
값 붙여넣기: 수식이 아닌 바뀌지 않는 값만 붙여넣고 싶은 경우,수식에 기밀이 있어 알리고 싶지 않은 경우 등 종종 많이 활용 됨
병합하고 가운데 맞춤
서식 붙여넣기
수식 붙여넣기
틀 고정(▶없는 명령) : 구분 데이터를 고정할 때 사용.
합계
화면에 보이는 셀 선택 : 숨겨져있거나 필터링된 데이터를 제외하고, 보이는 데이터만 선택해주는 기능. 실무에서는 규칙적이지 않게 숨겨져있는 데이터가 많기 때문에, 화면에 보이는 데이터만 선택해서 일괄처리할 수 있다.
=> 순서는 자주 쓰이는 순으로
합계 - 수식 붙여넣기 - 값 붙여넣기 - 서식 붙여넣기 - 화면에 보이는 셀 선택 - 틀 고정 - 병합하고 가운데 맞춤
위 기능 이외에도 각자 실무에서 자주 쓰는 기능을 빠른 도구 모음에 추가하여 사용
1. 합계 기능 관련
합계 서식을 평균에 적용 (3번째 빠른 도구, 서식 붙여넣기, Alt+4)서식이 복사된 모습
* 데이터가 끊임없이 변하는 시대에 데이터 자체가 아닌, 자리를 선택하여 연산할 수 있는 스프레드 시트를 사용하는 것
함수 자동완성 기능 활용 : 원하는 함수에 커서를 대고 Tab버튼 누르기, 끝 괄호는 자동으로 입력됨
자동합계 기능 활용 : 홉 탭의 우측 상단에 위치, 앞서 빠른 실행 도구 모음 1번째로 추가했어 좌측 상단에서 선택 가능
단축키 Alt키 + 1,2,...로 빠른 실행도구 모음 실행 가능 - 첫 번째로 설정했던 합계를 Alt+1로 실행 가능
Alt 키를 누른 상태에서 뜨는 알파벳을 누르면 간단히 키보드로만으로 기능을 활용할 수 있다. (나와있기에 암기할 필요X)
*빠른 실행 도구 모음 단축키가 빠른지, 함수 붙여넣기기가 빠른지(적합한지) 판단하여 사용
2. 수식 붙여넣기, 값 붙여넣기, 서식 붙여넣기 기능 관련
* Ctrl+V는 최대한 지양한다. 셀(테두리)까지 복사되기 때문, 셀 서식을 헤치지 않아야 할 경우가 많이 있다.
* 단축키 Ctrl + 0 : 선택한 열 숨기기 ( 열 숨기기 취소는 우클릭하여 마우스로 실행 가능 )
4. 틀 고정 기능 관련
고정시키고자하는 행의 '뒷'부분 선택
데이터를 내려도, 구분자 행은 사라지지 않아 데이터가 어떤 데이터인지 항상 확인할 수 있다.
(틀 고정 이전)커서를 내리면 구분자 행(강남점, 종로점,..합계)이 사라져 어떤 데이터인지 확인할 수 없다(틀 고정 이후) 커서를 내려도 구분자 행은 그대로 보여져 어떤 데이터인지 확인 가능한 모습(열의 틀 고정) 우측으로 가도 구분 열의 상품 명은 그대로 보여져 데이터가 어떤 데이터인지 확인 가능
행과 열 모두 틀 고정을 하고자 하는 경우, 만나는 셀 하나만 선택해주면 된다.
틀 고정은 Ctrl+z로 해제 불가, 아무 행에 커서를 놓고 다시 Alt+6 누르기
5. 병합하고 가운데 맞춤 기능 관련
단순히 가운데 정렬을 하려는 경우에도 위와 같은 경고문구가 떠서 병합된 셀을 풀었다가, 서식을 다시 설정해주고 다시 병합해야하는 번거로움이 있다. 이 문제를 해결해준다.
3
중복항목 제거해 데이터 파악하기
전체 선택 - 데이터 탭에 '중복된 항목 제거'클릭
모든 선택 취소 후, 중복값을 없앨 열 이름만 선택 (EX. 우리의 상품이 어떤 물류센터로 가는지 한 눈에 파악하고 싶다면, 물류 센터 열만 선택하여 중복값 삭제, 물류센터가 1개씩만 남고, 모든 행들이 삭제 됨
물류센터의 중복된 값들이 포함된 모든 행들이 삭제된 모습
2. 해당 열(수입국가)만 선택하여 중복된 항목 제거를 눌러도, '현재 선택 영역으로 정렬'에 체크하면 같은 결괏값 출력 => 단, 이 경우에는 수입국가의 중복된 값만 제거되고, 나머지 행들은 삭제되지 않음
물류센터의 중복된 값이 포함된 셀만 삭제되고 나머지 행은 남아있는 모습
텍스트 나누기_합치기
어떤 기능을 사용하더라도 실무에서 원본 데이터가 삭제되는 것은 좋지 않다
제품 코드와 제품 명 데이터 남겨놓기
데이터 탭에 '텍스트 나누기'클릭
'구분 기호로 분리됨' : :하이퍼나 슬래쉬, 콤마 등 구분 기호로 나누어야할 데이터가 분리되어 있을 때
'너비가 일정함' : 스페이스와 같은 일정한 공백으로 데이터가 분리되어 있을 때
성능이 좋지는 않다.
예를 들어 'OO대학교 OO학과'를 한 칸에서 두 칸으로 나누어야할 때에도 구분 기호에서 공백을 선택하는 게 더 정확한 경우가 많음
간단한 수식으로 텍스트 합치기 (엔드 & 이용)
=> 코드 분류 사이에 하이퍼 - 가 들어가야 하기 때문에 추가해주기
=> Ctrl + Shift+ ↓ 전체 선택 후 빠른 도구 모음 2번인 수식 붙여넣기 사용 (Alt+2로 제품 코드 수식 붙여넣기
날짜 구분
엑셀은 한 칸에 한 가지 정보만 들어가있는 것이 좋다 (연/월/일 분리 필요)
Ctrl + Shfit+ +로 열을 추가 한다 (연/월/일 총 3개) → 텍스트 나누기 → 날짜 형식으로 나누어지기 때문에, 홈 탭에서 형식을 '날짜'에서 '일반'으로 바꾸어주기
연도 뒤에 '년'을 붙여주기 위해서 엔드(&)를 이용해서 "년" 글자 추가해주기
직접 글자를 입력하지 않고, 구분자 셀을 선택하고 절대참조(Fn+F4)로 바꾸어주어 같은 결과 출력
끝까지 선택한 후 Alt+2로 수식 붙여넣기
4
모두 바꾸기, 필터 활용
모두 바꾸기로 데이터 일괄 변경하기
단축키 Ctrl + H : 특정 단어 검색 후 바꾸기
문자열이나 직접 입력한 숫자 값을 변경하는 경우, 위 방법으로 간단히 할 수 있다. 하지만 함수로 입력된 숫자를 위와 같은 방법으로 변경하는 경우, 함수로 계산된 값인 보여지는 데이터가 아니라, 수식 속의 숫자가 바뀜에 주의
필터로 데이터 정렬 및 필터링하기
단축키 Ctrl + Shift + L: 선택한 영역 필터 설정 및 해제
한 손으로 쓸 수 있는 단축키 Alt + DFF 가 더 편리
필터의 기능은 두 가지, 1) '정렬'과 2) '특정 데이터만 보고자 할 때'
'색 기준 필터' 기능 : 특정 색깔로 칠했던 데이터만 보거나 정렬할 수 있음
'고급 필터' 기능
데이터 탭에서 필터 옆에 '고급' 선택
'목록 범위'가 원본 데이터, '조건 범위'에는 이 중에서 필터링하고 싶은 조건 입력
조건에 맞는 데이터만 필터링하여 보여줌
'현재 위치에 필터'
'다른 장소에 복사' : 다른 셀에 해당 데이터만 포함된 새로운 표가 생성되어 활용할 수 있음
