Post Session: 엑셀 고급
단축키
- 사용자 정의 서식:
ctrl+1 - 셀을 열 단위로 선택:
ctrl+shift+오른쪽 방향키 - 셀을 행 단위로 선택:
ctrl+shift+아래쪽 방향키 - 참조 범위 고정:
F4 - 강제 개행:
alt+enter - 선택 영역 전체 채우기:
ctrl+enter
사용자 정의 서식
- 셀을 특정 문자로 채우기
@*특정 문자로 지정.@는 문자를 의미한다. 셀 서식 - 맞춤 - 균등 분할(들여쓰기)
들여쓰기 크기를 줘서 보기 좋게 정렬도 가능(균등 분할 정렬)
이름 지정
블록의 이름을 정의할 수 있다.
- 이름 정의: 왼쪽 상단의
이름 상자를 이용하거나,수식 - 이름 관리자에서 직접 정의한다. - 셀의 인덱스로 자동 이름 정의:
수식 - 선택 영역에서 만들기 - 이름 정의 수정:
수식 - 이름 정의
이름 지정 규칙
- 문자, 밑줄, \ 중 하나로 시작
- A3과 같은 셀 주소 형식으로 지정할 수 없다.
- 공백은 포함되지 않는다.
- 255자까지 대소문자 구분하지 않고 지정한다.
다양한 COUNT 함수
COUNT: 숫자만 카운트COUNTA: 공백을 제외한 나머지[1]를 카운트COUNTIF: 조건에 맞는 셀을 카운트COUNTIFS: 여러 조건에 맞는 셀을 카운트
채우기 핸들
숫자+문자
- 복사:
ctrl + 채우기 핸들 드래그 - 증가:
채우기 핸들 드래그
숫자
- 복사:
채우기 핸들 드래그 - 증가:
ctrl + 채우기 핸들 드래그
날짜, 요일
-
복사:
ctrl + 채우기 핸들 드래그 -
증가:
채우기 핸들 드래그 -
평일 단위 채우기체크하면 주말 자동 제외
자동 채우기 형식 추가, 수정, 삭제
파일 - 옵션 - 고급 - 사용자 지정 목록 편집에서 자동으로 채워지는
연속 데이터 단계 값 수정(단위 증가)
오른쪽 버튼 드래그 - 연속 데이터 - 단계 값
사용자 셀 서식
날짜 서식
- 년: YY, YYYY
- 월: M, MM, MMM[2], MMMM[3]
- 일: D, DD, DDD[4], DDDD[5], AAA[6], AAAA[7]
- 시: h, hh
- 분: m, mm
- 12시간제: 마지막에 am/pm 추가
문자 서식
아름다운@: 아름다운홍길동@아름다운: 홍길동아름다운
숫자(계산)
- &로 두 셀을 하나로 묶을 수 있다.
ex)=B1&B2
&로 묶인 셀 숫자 형식으로 바꾸기
- 1을 곱해 준다.
ex)=B1&B2*1
유효성 검사
-
셀의 유효성[8] 설정:
데이터 - 데이터 유효성 검사 -
이미 입력된 셀의 유효성 검사:
데이터 - 데이터 유효성 검사 디테일 - 잘못된 데이터
자동 필터
- 서로 다른 필드에 대해 OR 연산을 할 수 없다. (AND만 가능)
적용:데이터 - 필터
고급 필터
- 서로 다른 필드에 대해 OR 연산을 할 수 있다.
문자열 함수
LEFT
=LEFT(문자열, 자리수)
RIGHT
=RIGHT(문자열, 자리수)
MID
=MID(문자열, 시작위치, 자리수)
빠른 채우기
찾아내는 문자열 길이가 전부 다를 때 유용하다.
첫 셀에서 원하는 문자열만 셀에 입력하고 입력할 셀 전체 선택 후 데이터 - 자동 채우기
VLOOKUP(수직)
찾는 데이터가 찾을 셀 범위의 처음에 존재해야 함.
=VLOOKUP(분류할 셀 주소, 분류 기준 셀 범위, 출력할 셀의 위치를 정수로, TRUE/FALSE)
분류 기준 셀 범위는 이름을 정의해서 하면 된다.
HLOOKUP(수평)
MATCH
범위 내에서 상대적 위치를 반환함
=MATCH(찾는 값, 찾을 범위, 옵션)
옵션은 -1, 0, 1로 구분
INDEX
=INDEX(차트 전체 범위, 행, 열)
=INDEX(차트의 행이나 열, MATCH(값, 찾을 INDEX 범위, 0))