[PM스쿨] zero-base / 프리스쿨_01 엑셀
빠른실행도구모음
[파일]탭 - [옵션] - [빠른 실행 도구 모음]
Alt + 1,2,3 ...추천기능
1. 합계
2. 값 붙여넣기
3. 수식 붙여넣기
4. 서식 붙여넣기
5. 화면에 보이는 셀 선택표시 형식
셀 서식창
Ctrl + 1사용자 지정 기호
- # 숫자 (0 생략)
- 0 숫자 (0 표시)
- @ 문자
- , 쉼표 / 반올림(숫자 끝)조건 설정
양수 ; 음수 ; 0 ; 문자
[빨강]#,##0 ; [빨강]#,##0 ; 0 ; "수치 확인"
1번 조건 ; 2번 조건 ; 불만족
[빨강][>=2000]#,##0 ; [파랑][>=1000]#,##0 ; #,##0함수
SUM() / AVERAGE() / IF()
합계 / 평균 / 조건
COUNT() / COUNTA() / COUNTBLANK()
범위 내 숫자데이터 셀 수 / 범위 내 비어있지 않은 셀 수 / 범위 내 빈 셀 수
COUNTIF(범위, 조건) / COUNTIFS(범위1, 조건1, 범위2, 조건2, ...)
범위 내 1개 조건 만족하는 셀 수 / 범위 내 2개 이상 조건 동시 만족하는 셀 수
SUMIF(조건 범위, 조건, 더할 값 범위) / SUMIFS(더할 값 범위, 조건 범위1, 조건1, 조건 범위2, 조건2, ...)
특정 조건에 맞는 데이터 합계 / 2개 이상 조건 동시 만족하는 데이터 합계 - 사용성 더 좋음
SUMPRODUCT(배열 인수1, 배열 인수2)
인수(배열)끼리 곱하여 곱들의 합계를 계산
*단가 x 수량 = 판매금액
SUMPRODUCT((논리곱 조건)*(곱할 값 범위1)*(곱할 값 범위2))
논리곱을 활용해 조건에 맞는 합계 산출 가능VLOOKUP(찾을 기준 데이터, 원래 데이터 범위, 불러올 데이터 열 번호, 1 or 0)
공통 기준 열을 기준으로 N번째 데이터를 찾아 옴
열 번호 참조하기
=VLOOKUP($B1,Sheet1!$B$4:$O$24,$C$4+2,0)
$C$4+2: 숫자 1을 입력하면 3번째 열에 있는 1월을 불러오도록 함.MATCH(찾을 값, 범위, 0)
찾고 싶은 값이 해당 범위의 몇번째 위치에 있는지 숫자로 산출
*1: 큰 근사치
*0: 정확한 근사치
*-1: 작은 근사치
INDEX(범위, 행 번호, 열 번호)
특정 범위에서 X,Y 좌표 처럼 원하는 데이터 불러옴
*INDEX(범위, MATCH(행), MATCH(열))
INDEX 함수의 행, 열 번호를 MATCH 함수로 자동으로 데려옴
=INDEX(Sheet1!범위, A, B)
A =MATCH(행 조건, 범위, 0)
B =MATCH(열 조건, 범위, 0)날짜 데이터
엑셀의 날짜 기준
1900-01-01 -> "1"일날짜 데이터의 계산
종료일 - 시작일 = 기간 (종료일은 기간에서 제외)YEAR/MONTH/DAY
연도, 월, 일 분리
D3 = 2033-11-08
=YEAR(D3) -> 2033
=MONTH(D3) -> 11
=DAY(D3) -> 8DAYS/DAYSDIF
시작일과 종료일 간의 날짜 계산
시작일 D3 = 2033-11-08
종료일 H3 = 2033-11-11
=DAYS(종료일, 시작일) -> 3
=DATEDIF(시작일, 종료일, "D") -> 3
*D: 일 수
*M: 일 수
*Y: 월 수
*YM: 올 해의 월 수
*MD: 이번 달의 월 수 문자 데이터
텍스트 위치, 텍스트 일부 불러오기
FIND("찾을 텍스트",긴 텍스트,N)
긴 텍스트 중 찾을 텍스트가 시작하는 위치 N은 생략 가능, N부터 찾기
긴 텍스트 부분에 FIND함수를 한번 더 이용하여 두번째 값부터 찾기 가능
리뷰에서 맛, 배송 등 특정 키워드가 포함된 리뷰를 분석할 수 있음
COUNTIF 함수 활용해서 특정 키워드 포함된 리뷰 개수 합계
=COUNTIFS(범위,">0")SEARCH("찾을 텍스트",긴 텍스트 샐,N)
FIND와 동일, 대소문자 미구분
LEFT(텍스트, N) / RIGHT(텍스트, N) / MID(텍스트, 시작 위치, N)
왼쪽부터 N개 문자 / 오른쪽부터 N개 문자 / 시작위치(숫자)부터 N개 문자
LEN(텍스트)
텍스트 총 길이
*FIND/SEARCH와 함께 활용
제품번호에서 특성 분리하기
B5: att1-att2-att3-att4 제품 번호
C5: FIND("-",$B5) 첫번쨰 "-"위치
D5: FIND("-",$B5,C5+1) 두번쨰 "-"위치
E5: FIND("-",$B5,D5+1) 세번쨰 "-"위치
att1 = LEFT(제품번호,C5-1): 가장 왼쪽부터 C5-1개 문자 추출
att2 = MID(제품번호,C5+1,D5-C5-1): 첫번째 "-" 다음부터 D5-C5-1개 문자 추출
att3 = MID(제품번호,D5+1,E5-D5-1): 두번쨰 "-" 다음부터 E5-D5-1개 문자 추출
att4 = LEFT(제품번호,D5+1,LEN($B5)-E5): 가장 오른쪽부터 LEN($B5)-E5개 문자 추출오류
오류의 종류
외울 필요 없다.
#DIV/0! 분모가 0
#N/A 찾을 값이 없거나 배열 함수에서 벙뮈의 인수가 서로 불일치
#NAME? 함수명이 잘못되었거나, 잘못된 인수 입력
#NULL! 범위 연산자의 사용 잘못 or 교차하지 않는 영역 참조
#NUM! 함수의 인수나 수식 잘못 입력
#REF! 참조된 셀 주소 잘못 or 삭제됨
#VALUE! 논리 값이나 숫자가 필요한 수식에 텍스트를 입력 or 배열 수식을 Ctrl+Shift+Enter로 입력 안함
##### 셀 값보다 열 너비 좁음ISERROR(수식 입력된 셀)
오류를 판별
*오류 = TRUE
*오류X = FALSE
IFERROR(수식 입력된 셀, "오류 발생 시 표시 값")
오류 발생했을 때, 원하는 값 출력 가능
피벗 테이블
커다란 표의 데이터를 요약하는 통계 표
만드는 법
Raw data 선택 - [삽입]탭 - 피벗 테이블사용하기
[피벗 테이블 필드]의 필드를 필터/열/행/값 중 필요한 부분에 드래그해서 넣는다.슬라이서
엑셀의 표와 피벗테이블의 데이터를 버튼을 눌러 변경할 수 있게 하는 기능
만들기
피벗 테이블 생성 - [피벗 테이블 분석]탭 - [슬라이서 삽입]
*버튼 눌러 피벗 테이블이 변경 됨
사용하기(차트)
피벗 테이블 생성 - 슬라이서 생성 - 피벗 차트 생성
*버튼을 눌러 자동으로 피벗 차트가 변경데이터 시각화
차트
막대형/꺾은선형/원형 차트 등
[삽입]탭 - [차트] - 원하는 차트 선택| 막대형 | 꺾은선형 | 원형 |
| 막대 높낮이 이용해 수치 비교 | 선의 높낮이를 이용해 수치 비교 | 원형 그래프 넓이를 통해 수치의 비중 표현 |
콤보(혼합)차트
다양한 그래프 중 2개 이상의 그래프를 한 차트에 표현
막대형 or 꺾은선형 그린 후
오른쪽 마우스 클릭 [계열 차트 종류 변경]
두 그래프 간 단위, 지표 다를 시 보조축 활용
[보조 축] 체크박스 클릭조건부 서식
특정한 조건 만족하는 데이터에 서식 적용
[2가지 색조]
[데이터 막대]
[아이콘 집합]
1. 상위, 하위값
2. 평균보다 크거나 작은 값
3. 고유 또는 중복 값
4. 수식을 사용데이터 유효성 검사
셀이나 범위에 상황에 따라 내가 유효하다고 인정하는 데이터만 입력되게 하는 기능
대상이나 방법 지정
셀 or 범위 선택 - [데이터]탭 - [데이터 유효성 검사] - [제한 대상], [제한 방법] 지정
목록 중에 있는 값만 인정
셀 or 범위 선택 - [데이터]탭 - [데이터 유효성 검사] - [목록]