엑셀 공부 챕터 3, 4(230208, 230214)

*오빠두엑셀의 '실무엑셀 8시간 로드맵' 내용의 개인적 정리글. 편의에 따라 누락, 결합된 부분 있음

 

챕터3: 실무 적용하기

1. 조건부 서식

-방법: 적용 범위 선택 -> 홈 - 조건부 서식

-종류: 색조, 데이터 막대, 아이콘 집합

-색조: 조건부 서식 -> 셀 강조

-데이터 막대: 데이터 막대 적용 후 규칙 관리 or 새 규칙 -> 규칙 편집

(-> 최댓값/최솟값, 셀 중간, 그라데이션 설정 등)

-아이콘 집합: 조건부 서식 -> 아이콘 집합 -> 기타 규칙

(-> 숫자 전환, 기준 설정 등 가능)

 

2. 특정 단어 입력 셀 강조하기

-조건부 서식 이용: ctrl A -> 조건부 서식 -> 셀 강조 규칙 -> 텍스트 포함 -> 원하는 텍스트 입력 or 셀 선택

-필터 이용: 원하는 열 선택 -> ctrl shift L -> 원하는 항목만 표시 -> 채우기 -> 필터 해제

 

3. 스파크라인

-스파크라인이란? 셀 하나에 삽입하는 시계열 그래프

-방법: 삽입 -> 스파크라인 - 그래프 선택(꺾은 선, 열, 승패)

-꾸미는 법: 스파크라인 적용 후 전용 메뉴 표시됨

-주의사항: "시계열" 자료에만 사용할 것!

 

4. 목록상자

-목록상자란? 문서 공유 시 입력 가능한 값을 정해놓은 명단. 별도 표, 혹은 별도 시트에 작성(ex: 초코송이O, 초코 송이X)

-데이터 유효성 검사: 목록상자의 입력값만을 입력 가능하도록 제한하는 기능

-적용 방법: 적용 범위 선택 -> 데이터 - 데이터 도구 - 데이터 유효성 검사 -> 제한대상설정 -> 목록 -> 목록상자 지정

-작성자 입장에서 목록 조회하기: Alt + 아래 화살표 하면 목록 조회 가능

-출력 오류 메세지 바꾸기: 유효성 검사 -> 오류 메세지

 

5. 메모 입력하기

-기존 메모: 우클릭 -> 새 메모(대화형 메모) or 새 노트(기존 메모)

-표식 없는 메모: 범위 선택 -> 데이터 - 데이터 도구 - 데이터 유효성 검사 - 설명 메세지

-활용법: A1셀에 메세지 입력 시 추후 ctrl + home/pgup으로 마무리 했을 때, 타 사용자가 편집 전에 메세지를 볼 수 있게 할 수 있음

 

6. 시트 보호

-시트 전체 보호하기: 홈 - 셀 - 서식 -> 시트보호

-보호 해제: 홈 - 셀 - 서식 -> 시트 보호 해제

-클릭 방지: 홈 - 셀 - 서식 -> 시트보호 -> 허용 목록의 모든 체크 사항 제거

-특정 영역 보호: 전체 시트 선택 후 셀 잠금 해제(잠금이 디폴트이므로)

-> 적용 범위 선택 -> 홈 - 서식 - 잠금 -> 홈 - 서식 - 시트보호 -> 잠금 해제된 셀 선택 허용

-허용 목록 설정에 따라 시트 보호 상태에서 필터나 피벗테이블의 사용을 허용할 수도 있음

 

7. 시트 숨기기

-기본: shift click으로 여러 시트 동시 선택 -> 우클릭 -> 숨기기/숨기기 취소

-숨기기 취소 방지: 시트 숨기기 후, 검토 - 통합문서 보호 -> 보호대상 '구조' 체크 -> 암호 입력

(단, 이 경우 기존 시트 삭제나 새 시트 추가도 막힘)

-문서 전체 보호: 1) 파일 -정보 -> 문서보호. 2) 다른이름저장 - 추가옵션 -> 도구

 

8. 데이터 정렬

-방법: 원하는 셀 선택 -> 우클릭 -> 정렬

-활용법: 순번열(1, 2, 3, ...)이나 일련번호를 입력해놓아야 정렬 후 원형태 복구 가능

-정렬 오류의 원인: 숫자, 텍스트 혼란

-나만의 목록으로 해결하기: 문제 열 복사 -> 남는 셀에 붙여넣기 -> 데이터 - 데이터 도구 - 중복항목 제거 -> shift drag로 원하는 순서로 조정

-> 파일 - 더보기 - 옵션 - 고급 - 일반 - 사용자 지정 목록 편집 -> 가져오기

-> 문제 열 우클릭 -> 정렬 -> 사용자 지정 정렬

-주의점: 나만의 목록은 현재 PC에만 등록되어 다른 PC에서는 같은 정렬을 사용할 수 없음

 

9. 셀 병합

-주의점: 셀 병합은 서식을 작성할 때만 사용할 것. 데이터에 사용 시 오류 발생 가능

-오류 발생 기능: 피벗 테이블, 정렬, 함수, 붙여넣기, 자동 채우기

-가로 병합 대안: 적용 범위 선택 -> 우클릭 -> 셀 서식 -> 맞춤 - 가로 -> 선택영역의 가운데로

-세로 병합 대안: 병합 셀을 같은 행 다른 열에 복붙 -> 우클릭 -> 셀서식 -> 테두리: 없음. 채우기: 흰색

-> 기존 병합 셀 해제 -> 옮겨놓은 셀 복사 -> 우클릭 -> 기존 셀 위치에 선택하여 붙여넣기 -> 연결된 그림으로 붙여넣기

-> 그림 우클릭 -> 크기 및 속성 -> 속성 -> 위치와 크기 변함 체크

-함수, 피벗테이블용 대안: 병합하지 말고 모든 칸에 값 입력(컴퓨터, 컴퓨터, 컴퓨터, 마우스, 마우스, 키보드, 키보드, ...)

 

10. 한꺼번에 입력하기

-특정 셀 동시 선택: ctrl A -> ctrl G -> 옵션 -> 빈 셀/수식

-한꺼번에 채우기: 선택 셀 중 한 셀에 값 입력 -> ctrl Enter

 

11. 표시 숫자 단위 변경하기

1) 값 자체를 변경하기

-절사: 빈 셀에 원하는 숫자(1000, 10000, 등) 입력 후 복사 -> 적용 범위 선택 -> ctrl alt V -> 값 + 나누기 -> 확인

-소숫점 표시: 홈 - 표시 형식 - 자릿수 늘림/줄임

-주의점: 표시형식이 아니라 값 자체가 변경. 함수 셀에 적용 시 문제 발생

2) 표시 형식만 변경하기

-셀 서식 이용(챕터2의 5번 항목 참고)

-특징: 기존 값이 유지되며 함수 적용이 가능함

 

 

챕터4: 함수 정복하기

0. 함수 기본 개념

-함수 자동완성: 글자 일부 입력한 후 Tab

-배열함수: 함수 안에 여러 함수를 넣는 것

 

1. IF 함수와 VLOOKUP

-IF(조건, 참일 때 결과, 거짓일 때 결과)

-VLOOKUP(찾을 값, 표 범위 전체 드래그 후 F4, 출력할 열 번호, 일치옵션)

*주의: 1) 찾을 값은 항상 표 제일 왼쪽 열에 있어야. 2) 일치옵션은 항상 0

3) 출력할 열 번호는 선택한 범위 왼쪽부터 1, 2, 3, ...

-IFERROR(함수, "출력 내용")

 

2. SUMIF 함수

-SUMIF(찾는 범위, 조건, 합칠 값이 있는 열)

-SUMIFS(합칠 값이 있는 열, 조건 1 찾는 범위, 조건 1, 조건 2 찾는 범위, 조건 2, ...)

ex) 상품 분류 + 제작지역

-단, 실무 자체에서는 피벗 테이블을 사용하는 편이 쉬울 것

 

3. XLOOKUP

-VLOOKUP의 한계: 1) 찾을 값이 항상 제일 왼쪽에 있어야. 2) 여러 값 동시 출력 불가

3) 최초 값만 검색 가능하고 마지막 값은 검색 불가. ex) 최초접속일 조회O, 마지막 접속일 조회X

-XLOOKUP(찾을 값, 찾을 영역, 출력 영역, N/A 오류시 출력 값, 일치옵션, 검색옵션)

* 1) 일치옵션은 항상 0. 2)검색옵션을 비우면 최초 값, -1이면 마지막 값. 3) 출력 영역에 복수 열 선택 가능

 

4. LEFT, RIGHT, MID

-LEFT(대상 셀, 출력 글자 수)

-활용: 보호된 주민등록번호 출력 시, =LEFT(주민번호 셀, 8)&"******"

-> 991231-1****** 출력

-RIGHT(대상 셀, 출력 글자 수)

-MID(대상 셀, 출력 시작 위치, 출력 글자 수)

-IF(ISODD(대상 셀), 참 출력값, 거짓 출력값)

*ISODD는 해당 셀이 홀인지 짝인지를 판별하는 함수. 주민번호처럼 1, 3과 2, 4를 구분해야 하는 경우 사용

 

5. FIND 함수, SEARCH 함수와 문자추출

-용도: 해당셀의 값 내에서 찾기 원하는 글자의 위치를 확인

-차이점: FIND 함수는 대소문자 구분O, 와일드 카드X. SEARCH함수는 대소문자 구분X, 와일드 카드O

-FIND(찾을 값, 셀 위치)

-이메일 추출 예제: 아이디=LEFT(대상 셀, FIND("@", 대상 셀)-1), 도메인 주소=RIGHT(대상 셀, LEN(선택 셀)-FIND("@", 대상 셀))

*단, 자동채우기 ctrl E로 단순한 작업은 보다 쉽게 작업할 수 있음에 유의

 

6. FILTER 함수

-FILTER(대상 범위, 조건, 결과 없을 때 출력값)

*여러 조건 입력 시 ( )*( )로 가능

-FILTER(대상 범위, {1,1,0,0,1,1}): 해당 범위에서 1열, 2열, 5열, 6열만 추출

 

7. UNIQUE 함수, COUNTA함수

-UNIQUE(대상 범위): 선택한 범위에서 중복값 제외하고 목록으로 추출

-COUNTA(대상 범위): 선택 범위에서 중복되지 않는 값의 개수 표시

-COUNTA(UNIQUE 목록 시작 셀#): 현재의 UNIQUE 목록 뿐 아니라 나중에 추가될 값까지도 카운트

-특정 셀에 UNIQUE 목록의 내용을 선택 가능하게 하기: 데이터 유효성 -> 제한대상: 목록 -> 원본: =UNIQUE 목록 시작 셀#

 

8. 그 외 함수

-TRIM(적용 셀): 글자 양 옆의 불필요한 공백 제거

-SUBSTITUTE(대상 셀, 찾을 단어, 바꿀 단어): 찾기, 바꾸기 자동화

c.f) CHAR(10) : 줄 바꿈

-TEXT(대상 셀, 표시 형식)

*표시 형식은 셀 서식을 참고. ex) "m월 d일(aaa)", "(m/d)", "#,##0원"

 

9. 날짜 데이터 관리하기

-빠른 채우기 후 Alt AENNDF

-함수를 이용해 YYMMDD를 YYYY-MM-DD로 전환하기

-> DATE(LEFT(날짜 셀, 2)+2000, MID(날짜 셀, 3, 2), RIGHT(날짜 셀, 2))

-YEAR(날짜 셀): 연도만 추출

-TODAY(): 오늘 날짜 입력