2주차 스터디 노트

🔥 실습 예제과 함께 공부하는 모습을 함께 올려보세요!
(마우스 드래그 & 스크린샷+붙여넣기로 편리하게 그림을 넣을 수 있습니다)

챌린지 2주차 - 엑셀이 쉬워지는 핵심 스킬, 하나만 기억하세요! - 데이터관리

코파일럿 기능 : 인공지능 기능을 오피스에 심을 거다. 그러나 Copilot를 알고 쓰는 것과 모르고 쓰는 것은 큰 차이가 있다.

엑셀에서는 '셀'이 중요하다. 즉 데이터가 중요하다. 모든 자동화는 올바른 데이터에서부터 시작된다. 데이터 기반

[목록상자 만들기]

내가 만든 파일을 지키는 방법 - '유효성 검사' 발주서를 만든다. 제품명에 제품을 입력하면 단가가 자동으로 입력되는 것을 만든다. 제품명에다 데이터를 넣을 때 원가표에 있는 제품들만 넣을 수 있도록 제한하는 것

적용할 범위설정 > 데이터 > 데이터유효성검사 > 적용할 대상 : 목록 > 목록에 들어갈 범위 선택 *목록상자를 편하게 쓰는 방법 실무에서 업무를 편하게 하려면 마우스를 적게 써야 한다. 단축키 활용 Alt + ↓ 다른 곳에서 활용 : 필터, 일반 셀에도 사용 가능-데이터 입력된 맨 아래 셀에서 Alt + ↓ 누르면 그 위에 있는 데이터를 자동으로 인식해서 편리하게 입력 가능

*수량에는 숫자만 입력하도록 제한 수량 범위 설정 > 데이터유효성검사 > 제한대상 : 정수 > 최소값 0 > 최대값 100 설정 가능 > 확인

데이터유효성검사 오류검사 메시지 수정하는 방법 : 범위 설정 > 오류메시지 탭 > 제목 : 숫자만 입력 가능합니다. > 오류메시지 : 0부터 100사이의 정수를 넣어주세요.

*시트보호 설정 편집이 안되게 잠금 기능 설정 왼쪽 상단 삼각형 클릭 > 홈택 > 서식 > 셀 잠금, 시트보호 설정

*데이터 관리가 편해지는, ‘표’ 사용법

목록상자 데이터 추가할 때 자동으로 추가 되도록 하는 기능 : 표 사용법 제품 원가표 쭉 드래그 > 표 Ctrl + T > 머리글 포함 체크! > 확인

오른쪽 하단 꺾쇠 기호는 데이터를 추가할 때마다 자동으로 늘어나는 것을 볼 수 있다.

장점 : 동적으로 범위 확장, 스타일을 자동으로 꾸며준다. 테이블 디자인 : 빠른 스타일 변경으로 표 스타일을 변경 가능 > 없음 선택 디자인 탭 왼쪽에 표 이름 : 표1 > ‘원가표’ 작명 =원가표[제품명 선택 > 탭키 누르면 제품명 범위가 자동 설정됨. vlookup 함수에서 범위 설정에 활용 구조적 참조 : 데이터가 늘어나거나 줄어들었을 때 자동으로 참조

*자동으로 확장되는 목록상자 만들기

제품명 범위 설정 > 데이터 > 데이터유효성검사 : 원본 =Indirect("원가표[제품명]") 입력 > 확인INDIRECT함수 : 간접 참조하는 함수

표가 아닌 것은 셀의 집합체이기 때문에, 자동화 할 수 없다.

*다중조건 VLOOKUP 간단 사용법

VLOOKUP : 세로 방향에서 찾는다. 찾을 값은 고유값이어야 한다. 고유값을 추가해서 활용 김지오1차 이런 식으로

*실전 SUMIF 함수 사용법 : 어떤 조건이 맞는 값들을 집계한다. 최신버전 = unique()

함수를 쓸 때 주의할 점은 항상 검산해봐야 한다.

셀 병합 기능은 되도록 쓰지 않는게 좋다. 왜 그럴까? 첫번째 셀만 데이터가 입력되어 있고 빈칸이 들어 있기 때문이다.

병합 해제 후 > 이동 옵션 Ctrl+G > 옵션 : 빈셀 선택 > 확인 > 활성된 셀 L6 밑에 들어가는 값들은 위에 있는 값들을 하나씩 불러오면 다 채워질 것이다. =L5 입력 후 Ctrl+Enter 누르면 한꺼번에 다 채워진다. 그리고 지역과 대분류가 수식으로 되어 있는 것을 값으로 변경해줘야 한다.

값으로 붙여넣기 : Ctrl+Alt+V > 값 > 확인

함수 vs 피벗테이블

[데이터구조]

표(Table) 기능

보고서 쓸 때 거의 합계/평균 정도의 함수만 쓴다. 99.99% 피벗테이블로 적용 가능하다. 피벗테이블 만들기 : 십자가만 기억하면 된다.

제품별 판매현황을 구해줘

올바른 데이터는 날짜별로 입고수량을 세로로 쭉 쌓는 것이다.

*효율적인 엑셀 작업을 위한 올바른 데이터 구조 3가지 규칙

1. 머릿글은 반드시 ‘한 줄’로 관리하기
2. 원본데이터에는 셀 병합 절대 사용 금지!!!
3. 집계 데이터는원본에서 제외하기

첫 번째 데이터 오류 : 머리글이 2줄로 만들어지는 이유는, 똑같은 필드를 표시하는 방법이 없기 때문에 그러면 어떻게 왼쪽의 다른 블럭으로 내려줘야 한다.

두 번째 데이터 오류 : 여러 개의 시트로 나눌 필요가 없다. 왜 가로로 쌓이냐 하면, 머리글이 아니고 날짜이다.

세로 방향 블록 쌓기 : 날짜, 입고수량, 금액 등 피벗 쿼리 참고한다.

*피벗데이블로 알아보는 데이터 구조의 중요성

Ctrl+A 범위 설정 > 삽입 > 피벗테이블

피벗테이블과 잘못된데이터와 똑같다. 즉 피벗테이블만으로 원하는 형태의 데이터를 만들 수 있다. 데이터 구조만 잘 관리해 주면 피벗테이블로 쉽게 데이터를 집계할 수 있다.

[2교시]

표+피벗테이블로 만드는 자동화 보고서

데이터 추가한다고 피벗테이블에 반영이 되지 않는다.

그래서 원본데이터를 선택(Ctrl+A) > 표(Ctrl+T)로 만들고, 한 셀을 선택, 피벗테이블을 만든다.

그러면 원본데이터에 데이터를 추가하고 피벗테이블에서 새로고침하면 새로운 데이터가 추가 가능함.

열레이블을 날짜를 추가했다. 그러면 날짜를 우클릭 그룹 > 일,월,연 선택 > 확인

천단위 구분기호 : Ctrl+Shift+1

날짜에 범위가 어떠냐에 따라 피벗테이블을 만들면 자동으로 그룹핑해준다.

*구분을 가져다가 필터에 넣거나, 행에다 갖다 놓으면

하나의 열에 지점과 구분이 다 들어가 있다. 그것이 불편한 경우는 어떻게 하냐면 열로 각각 분리하면 좋다. 피벗테이블 > 디자인 > 보고서 레이아웃 > 테이블 형식으로 표시 선택해준다.

그러나 필터를 실무에서 쓰지 않는게 좋다.

다른 방법 제시

피벗테이블 > 분석 > 슬라이서 삽입 > 지점, 구분, 연 선택 >

*데이터를 잘 관리하면 피벗테이블로 자동화 보고서 쉽게 만들 수 있다!!!

이해하기 쉽게 잘 가르쳐주셔서 감사합니다~~^^