1. 바로가기 단축기
합계, 수식붙여넣기, 값붙여넣기, 서식붙여넣기, 화면에 보이는 영역 선택
Alt + 1, 2, 3... 등으로 단축기로 사용
2. 사용자 지정 기호
Ctrl + 1 → 셀 서식창
▷ 표시 형식
'#' = 숫자의 대표값(의미없는 0은 생략 → 화면에만 생략되는 것)
ex. 001 → '#' 표시형식 → 1
0 → '#' 표시형식 → (빈칸)
'0' = 숫자의 대표값(의미없는 0도 표시)
ex. 001 → '0' 표시형식 → 001
0 → '0' 표시형식 → 0
'@' 문자의 대표값
ex. 고객 → @"님" → 고객님
',' 숫자의 중간 : 1000 단위마다 쉼표
숫자의 끝 : 1000단위 반올림
사용자 지정 기호(심화)
1) 양수일때 ; 음수일때 ; 0일때 ; 문자일때
[빨강]#,##0 ; [파랑]#,##0 ; 0 ; "수치확인"
값이 양수일 때 글자색은 빨강
값이 음수일 때 글자색은 파랑
0일땐 0표시
값이 문자일 때 "수치확인" 이라고 표시
2) 첫번째 조건에 만족할때 ; 두번째 조건에 만족할때 ; 모든 조건에 만족하지 않을때
[빨강][>=2000]#,##0 ; [파랑][>=1000]#,##0 ; #,##0
값이 2000보다 크거나 같을 때
값이 2000보다 작고 1000보다 크거나 같을때
값이 1000보다 작을때
3. IF 함수
1) IF 함수를 N번 중첩 사용하면 데이터가 N+1가지로 구분됨
4. Count 함수 Series
1) Count : 숫자 데이터가 들어가있는 셀 개수 계산
2) Counta : 비어만 있지않으면 개수 계산
3) Countblank : 비어있는 셀 개수 계산
4) Countif : ex) =countif(C7:C15,">=5000")
일반적으로 함수안에 부등식을 사용할 때에는 따로 ""를 붙이지 않지만
Countif등의 함수의 조건으로 사용 할 때는 ""를 붙여야 함
5) Countifs : 여러 조건 동시에 충족하는 셀 개수 계산
5. Sumif 함수 Series
Sumifs는 조건이 1개일때도 쓸수 있으므로, sumif 보다 sumifs 활용
6. Sumproduct 함수
인수(배열)끼리 곱하여 곱들의 합계를 계산
배열 : 여러 셀들의 집합
ex) 상품들의 단가 X 수량을 곱한 판매액의 총 합계를 계산
응용 : 논리곱으로 응용
True x True = 1 / False x True = 0
활용해서 Sumifs를 대체할 수 있다.
sumproduct((C9:C10="Series7")(D9:D10)(E9:E10)
Series7의 판매단가X수량만 구함
7. Vlookup 함수
기준열의 왼쪽에 있는 Data는 Index, Match 함수 통해 극복 가능
8. Index/Match - 협동작전으로 데이터를 불러오자
1) Match : 찾고 싶은 값이 해당 범위의 몇번째 위치에 있는지 숫자로 산출
ex. Match(찾을값, 범위, 0)
범위 : 하나의 열 또는 행이어야함
1 = 찾을값보다 큰 근사치, 0 = 정확히 일치할때만, -1 = 찾을값보다 작은 근사치
2) Index : 특정 범위에서 행 번호와 열 번호로 원하는 데이터를 불러옴
ex. Index(범위, 행번호, 열번호)
Index(B$4:OB7,B$4:BCBB$24,0))
9. YEAR/MONTH/DAY/DAYS/DATEDIF 함수
엑셀이 인정하는 날짜 데이터는 '0000-00-00' 형식이다.
엑셀은 '1900-01-01'을 '1'로 인식함
종료일-시작일=기간 (종료일은 기간에서 제외됨→종료일 포함하려면+1 해야함)
1) YEAR/MONTH/DAY : 날짜데이터에서 각각 연도/월/일 을 추출
2) DAYS/DATEDIF 함수 : 시작일과 종료일 간의 날짜를 계산 (종료일 미포함)
DAYS(H3,D3) = 3 / (종료일, 시작일)
DATEDIF(D3,H3,"D") = 3
(시작일, 종료일, D=일수/M=월수/Y=연수/YM=올해의 월수/MD=이번달의 일수
10. FIND/SEARCH
1) FIND : 긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력
띄어쓰기까지 포함하여 문자를 세며 대소문자를 구분
ex)
첫번째 "아빠"위치 FIND("아빠",A1) = 100 / A1위치엔 긴 텍스트
두번째 "아빠"위치 FIND("아빠",A1,101) = 145
2) SEARCH : FIND와 동일하지만 대소문자를 구분하지 않음
보통 해당 부분에 Target 단어가 포함되어 있는지 아닌지 여부 파악
만약 Find("",,)>0 포함된것이니까
countifs와 혼용하여 사용
11. LEFT/RIGHT/MID/LEN
LEFT/RIGHT/MID → FIND/SEARCH와 조합하여 사용하면 활용성 ↑
EX)Wii-Spo-0001-Nintendo 를 구분
Platform - Left(B5,find("-",B5)-1)
Genre - MID(B5,find("-",B5)+1,find("-",find("-",B5)+1)-C5+1))
Publisher - RIGHT(B5,LEN(B5)-E5)
LEN : 텍스트 문자열의 총 길이
모델명 하이픈등 구분자로 구분되어 있으면, FIND 이용해서
구분자 위치 정해놓고 그걸 통해서 세부 내용 분해 가능
12. IFERROR/ISERROR
1) 엑셀 오류 종류 (외울 필요 없다!!!)
DIV/0! : 나누는 수(분모)가 0일때
N/A : 찾을 값이 없거나 배열 함수에서 범위의 인수가 서로 일치하지 않을때(VLOOKUP 등)
NAME? : 함수명이 잘못되었거나 잘못된 인수가 입력되었을때
NULL! : 범위 연산자의 사용이 잘못되었거나 교차하지 않는 영역을 참조할 때
NUM! : 함수의 인수나 수식이 잘못 입력되었을 때
REF! : 참조된 셀 주소가 잘못되었거나 삭제되었을 때
VALUE! : 논리값이나 숫자가 필요한 수식에 텍스트를 입력하였거나 배열 수식을
CTRL+SHIFT+ENTER로 입력하지 않았을때
2) ISERROR : 입력된 수식이나 합수의 결과가 오류값이면 TRUE, 아니면 FALSE를 출력
3) IFERROR를 더 많이 사용
13. PIVOT TABLE - 엑셀계의 맥가이버
피벗테이블 레이아웃 잡기 어려울때 손으로 그려서 구성해보는것도 좋은 방법
14. PIVOT TABLE 슬라이서
슬라이서 - 엑셀의 표와 피벗테이블의 데이터를 버튼을 눌러 변경할 수 있게 하는 기능
1) 피벗테이블 생성 - [피벗 테이블 분석] 탭 - [슬라이서 삽입]
2) 슬라이서 생성 → 피벗 차트 생성
→ 슬라이서 버튼을 누르면 피벗 차트가 자동으로 변경됨
슬라이서 서식도 바꿀수 있음(슬라이서 서식 오른쪽 클릭 -중복)
→ 피벗 차트 색이랑 일치 시키면 시각효과 ↑
15. 데이터 시각화 - 차트 (막대형/꺾은선형/원형 차트)
범위 선택 : 마우스 드래그와 CTRL 이용해 차트를 그릴 데이터 범위 선택
→ [삽입]탭 - [차트] - 원하는 차트 선택
이렇게 해야 나중에 계열 따로 이름 지정할 필요가 없다.
그리는 목적에 맞는 차트를 선택
16. 데이터 시각화 - 콤보(혼합) 차트
데이터 선택이 어려울때(너무 숫자가 작아서 등)는 범례를 더블클릭해서 선택 가능
같은 데이터를 그래프옆에 똑같이 그리려면
해당표에서 데이터 범위 복사 → 그래프 클릭 → CTRL + V
17. 조건부 서식
중복된 데이터 서식 적용도 가능
수식을 사용하여 서식을 지정한 셀 결정도 가능
→ 임의의 수식의 결과가 참(TRUE)이 되게 하는 데이터에만 서식 적용
EX) =I5:T5=1 / =And(T5<=5) → 1등, 2~5등
18. 데이터 유효성 검사
목록 활용
