엑셀 사용법 정리

오의석·2024년 1월 23일

0. 설정

파일 - 옵션 - 저장 - 자동복구간격(3분){자동복구파일위치 기억해두기(뻑나면)}
				  - 기본적으로 컴퓨터에 저장
				  - OneDrive사용시 '문서관리서버파일에 대한 오프라인 편집 옵션' 기억해두기

1. 엑셀의 불편한 기능 제거

이메일 링크제거 && 'rk-숫자'가 '가-숫자'로 바뀌는 거 
파일 - 옵션 - 언어교정 -자동고침옵션  - 자동교정 - 자동고침(한/영, 대소문자)
								   - 입력할때 자동서식 - 하이퍼링크 제거

2. 나만의 단축키 만들기

파일 - 옵션 - 언어교정 -자동고침 - 수식자동고침 -> 단축키 설정해서 사용할 수 있음

3. 나만의 단축키 만들기2

자주쓰는 기능은 우클릭 + 빠른실행도구모음 추가
(or 리본메뉴 사용자 지정에서 빠른실행도구모음을 추가)

쓸때는 ALT + 숫자 이용

4. 'F4'키는 이전작업 수행

5. 드래그를 통해 '자동채우기'시 수식에 사용되는 셀들은 고정되지 않는다.

'=A1/B2'수식이 적힌 셀을 드래그하면 '=A1/B2','=A2/B3'와 같이 수식이 변경된다.
B2를 고정하고 싶으면 '=A1/$B$2' 수식을 설정하고 자동채우기를 한다.

TIP. '=A1/B2'수식중에서 'B2'를 선택하고 F4를 누른면, '=A1/$B$2'가 된다.

6. 단축키 모음

컨트롤+쉬프트+1 = 회계숫자
컨트롤+쉬프트+3 = 날짜형식으로 변환
컨트롤+쉬프트+5 = 백분율
컨트롤+E = 자동채우기
컨트롤+쉬프트+L = 데이터 필드에 필터 설정
컨트롤+백스페이스 = 수식을 작성중 데이터범위가 너무 길어서 맨아랫줄에 있을때, 화면을 올리는 방법
알트+w+f+f (하나씩 차근차근 누른다) = 기준선 고정 (선택셀을 기준으로 고정됨)

7. $는 고정을 의미

표계산시 =$B6*C$5 이런식으로 고정해서 드래그하면 계산이 빠르다
(B열은 고정 6행은 이동. C열은 이동 5행은 고정을 의미)

8. 셀의 완성은 '셀서식'

셀서식 = 값은 유지하고 겉으로 보이는 표시형식은 그대로

9. 셀서식에서 숫자 형식 설정(사용자 지정)

조건문  : '양수일 때 ; 음수일 때 ; 0일 때 ; 텍스트일 때'
형식	   :  #,##0; (#,##0); 0   (형식을 지정할수 있음)
		 (다른 예시,   [빨강]▲#,##0;[파랑] ▼#,##0; 😂 )
단축키  : 컨트롤 + 1 , 우측클릭+셀서식

10. 수많은 데이터에서 특정 데이터만 조건에 따라 강조하고 싶을 때

범위지정 -> 홈버튼의 조건부서식을 눌러서 세팅 가능

TIP. 홈버튼 - 조건부서식 - 규칙관리 (내용은 유지하면서 스타일 변경가능)

11. 가로데이터 시각화

숫자표를 더 잘 보여주기 위해서 막대그래프 사용하면 좋음
범위지정 -> 홈버튼의 조건부서식-> 데이터막대 -> (기타규칙으로 색수정가능)

12. 데이터막대의 최대색상 설정

해당부분 선택 - 규칙관리 - 편집 - 최대값 숫자 변경
(ex. 이것을 함으로써 색상의 최대 최소 격차를 완화할 수 있다.)

13. 세로 데이터를 시각화(12번은 가로데이터)

세로데이터는 홈버튼 - 스파크라인으로 트렌드파악 가능
스파크라인은 표식색으로 최대,최소데이터만 색상 변경가능

14. 각 데이터 범위에 따른 아이콘설정

위지정 -> 홈버튼의 조건부서식-> 아이콘집합 -> 기타규칙

TIP. 아이콘만 표시를 누르면 데이터를 삭제하고 아이콘만 보이게 설정가능

15. 월화수목금, 12345와 같은 데이터처럼 자동채우기 및 정렬 등을 하고 싶을 때

파일 - 옵션 - 고급 - 사용자 지정 목록 - 추가
-> 이 작업을 통해 자동화 및 정렬 가능
(ex. 이 기능을 통해 직급의 순서를 둘 수 있다.)

16. 자동채우기

첫셀만 작성하고 '컨트롤+E' 누르면 알아서 세팅해줌
(단 해당 시트에 값이 존재해야함)

17. 특정데이터를 가지고 확인할 수 있는 시트만들기

(1) 해당 데이터선택 - 데이터탭 - 유효성검사 - 목록 - 원본데이터에서 범위 설정
(2) 데이터 범위 선택 - 조건부서식 - 새규칙 - 수식을 사용하여 서식을 지정할 셀 결정

(ex. =$B4=$H$4)

18. 자주사용하는 함수 리스트

IF
VLOOKUP ( 찾을값, 참조범위, 열번호, [일치옵션] )
          (찾을값 - 범위 F4 - 열번호 - 0)
	[ex.  VLOOKUP($G4,$B$4:$E$13,H$2,0) // G4는 목록이라서, H2는 열의 위치의미]
	=>> (upgrade) H2 부분에 "=MATCH(H3,B3:E3,0)"를 설정한다. 더나아가, H3을 목록화하면 더 편하다
SUMIF ( 조건범위, 조건, [합계범위] )
COUNTIF ( 조건범위, 조건 )
AVERAGEIF ( 조건범위, 조건, [평균범위] )
SUMIFS 여러조건의 합
SUM(SUMIFS(G:G,D:D,L10:L11)) (OR범위 덧셈)

19. 필터 (인덱스에서 선택해서 원하는 데이터만을 볼 수 있음)

컨트롤+쉬프트+L

20. 배열함수를 만들경우, '=H4#'와 같이 뒤에 #을 통해 동적으로 참조가능

이걸 가지고 데이터의 유효성검사, 목록을 통해 묶을 수 있다.
(이걸 할 경우, 배열함수의 내용이 늘어나도 자동으로 목록에도 추가된다.)
UNIQUE(E6:E233) -> 데이터목록

21. 셀서식 복사

홈 - 붓그림

22. 필터처리 예시

FILTER(B6:G233,E6:E233=L3) (선택한 목록으로 필터)
SORT(FILTER(B6:G233,E6:E233=L3),6,-1) (정렬까지 하고 싶을 때)

23. 날짜서식 단축키

컨트롤 + 쉬프트 + 3

24. 분류하기

중분류 				: unique함수 + 데이터목록화
소분류 				: 유니크함수 (필터함수(소분류,중분류=선택셀)) 
						(=UNIQUE(FILTER(F6:F68,E6:E68=M5)))
소분류해당 물품 뽑기 : FILTER(출력하고 싶은 물품내용,(첫번째조건)*(두번째조건))
						(=FILTER(B6:C68,(E6:E68=M5)*(F6:F68=M6)))

TIP. 멀티 조건하는법
두조건 모두 만족 : 첫번째조건*두번째조건 (곱하기를한다.)
하나라도 만족 : 첫번째조건+두번째조건 (덧셈를한다.)

25. 범위 지정중에 너무 아래에 있을 때, 올라가는 방법

컨트롤 + 백스페이스

26. 텍스트 분리 및 출력

(ex. =TEXTSPLIT(D4,">") )
(ex. 패션의류>남성의류>니트/스웨터   =>   패션의류	남성의류	니트/스웨터)

!!문제 발생!! 자동채우기 안됨
=> 따라서 join 후 자르기
(ex. =TEXTSPLIT(TEXTJOIN("|",,D4:D64),">","|"))
TEXTSPLIT(내용,행,열)

27. 복사한 데이터를 매핑하는 법

=WRAPROWS(TOCOL(A5:B154,1),7) // 7은 분류할 갯수

28. 나만의 수식 만들기

=LAMBDA(값1,값2,값1+값2)
=LAMBDA(값1,값2,값1+값2)(1,2) // 이렇게 만들자마자 사용 및 확인가능

수식탭 - 이름관리자 - "이름은 mySSUM, 참조대상은 '=LAMBDA(값1,값2,값1+값2)'" 을 넣으면 mySUM으로 사용가능
(ex. =LAMBDA(범위,WRAPROWS(TOCOL(범위,1),7)) 이런식으로도 만들 수 있다.)

29. 단어 포함여부 검색 공식 (ISNUMBER/SEARCH)

=ISNUMBER(SEARCH(단어,문장)) // 단어가 문장에 포함된 경우 TRUE를 반환합니다.

=> (upgrade) 단어와 같은 것들만을 필터하여 출력
(ex. =FILTER(출력할 범위,검색할 범위=선택셀))

=> (upgrade) 단어 포함한 것을 필터하여 출력
(ex. =FILTER(출력할 범위,ISNUMBER(SEARCH(단어,문장))))

30. 범위 표 변환

데이터 선택 -> 표 탭 -> 표 -> (머릿글 포함여부 주의)
(장점 : 아래 데이터가 추가되어도 알아서 표에 첨가 됨)

31. 선택 셀 기준으로 기준 라인 트리거 고정하기

알트 + w + f + f (하나씩 차근차근 누른다)

32. 검색필드 만들기

한쪽 필드에 CONCAT함수를 사용하여 셀 내용 합치기)

-> (upgrade) 표에서 검색하기
ex. =FILTER(표2[[제조사]:[가격]],ISNUMBER(SEARCH(검색보고서!B2,표2[검색필드])))

TIP. 'i7'이면서 '삼성'인것을 검색 => 'i7*삼성'

33. 표 이름 확인 방법

표 안의 셀 선택 -> 테이블 디자인 -> 표 이름 확인

TIP. '=표이름[필드 이름]' 으로 사용가능

34. 인터넷 검색 없이 아이콘 불러오기

삽입 - 아이콘 - 검색(ex. '검색'을 입력하면 돋보기 나옴)

35. 색상 검출

ppt에 이미지 붙여넣기 - 홈 탭 - 그리기 그룹 - 채우기아이콘 - 스포이트 - 중간반복 - 채우기아이콘 - 다른 채우기색으로 RGB값 검출

36. 개발도구 생성하기

리본메뉴에서 추가

-> (upgrade)
개발도구 - 삽입 - 텍스트상자 - 배치 - 속성 - linkedcell 선택 - specialEfect(?) 0설정

37. 3규칙

머릿글은 반드시 '한줄'
원본데이터에는 '셀병합' 절대 '금지'
'집계데이터'는 원본에서 '제외'

TIP. 세로방향 블럭쌓기 잘하기

37. 피벗테이블 만들기 = 십자가만들기!!!!!

테이블 선택 - 삽입탭 - 피벗테이블 -

38. 피벗테이블에 계산된 데이터가 필요할 때

(원본데이터는 변형해서는 안된다.)
피벗테이블 셀 선택 - 피벗 테이블 분석 탭 - 필드 항목 및 집합 - 계산 필드로 추가

39. 피벗테이블 정렬 | 그룹

필드 셀 선택 정렬 | 그룹

40. 슬라이서 기능

피벗테이블탭 - 슬라이서 삽입

41. 피벗테이블을 이용하여 각 시트 생성하기

피벗테이블탭 - 옵션 - 보고서 필터 페이지 생성

42. About 데이터시각화

디자인 30% (색감 중요)
업무관련지식 10%
근거자료 60%

so, '무엇을? 어떻게?'가 중요!

TIP 소소한 팁 (테이블 세팅)
단위 필요, 레이블은 위에서 아래로 내림차순.
누적막대와 꺽은선(시간)이 많이 사용된다.
색감 https://coolors.co/gradient-palette/ea98da-5b6cf9

43. 차트 보기좋게 설정하는 법

삽입 - 추천차트 -> 차트 간소화

	(제목지우고, 
	가로선 색상은 보일듯 말듯, 
	세로축 지우기, 
	선 색상변경, 
	데이터레이블 추가, 
	셀서식으로 데이터 간소화, 
	볼드설정, 
	글꼴설정, 
	데이터레이블 서식으로 위치변경,
	범레 위로 설정,
	표식옵션(기본제공 둥근, 7pt, 단색채우기, 너비2pt, ))