노년코딩

피벗 테이블을 사용함으로 합계함수나 평균함수를 사용하지 않고 끌어다 쓰기만으로 함수를 대체할 수 있는 동영상을 보았다.

그래서 공부해 보는 피벗 테이블.

피벗 테이블

피벗 테이블로 확인 하고자 하는 데이터 영역 선택 > 삽입 > 테이블 > 피벗 테이블 클릭 > 새 워크시트 선택 후 확인.

피벗 테이블 필드에 만들고자 했던 데이터의 셀들이 필드로 선택할 수 있게 만들어졌다.
- 필터 : 특정 값에 대한 데이터만 보고 싶을 때.
- 행 : 데이터를 보고 싶은 기준.
- 값 : 기준에 맞추어 보고 싶은 값.

값 영역에서는 합계나 평균을 구할 수 있으니 값 필드 설정을 통해 필요한 유형을 선택해 준다.

합계 데이터였던 값을 평균으로 바꿔 사용한 결과 값 - 연령대에 따른 평균 키와 몸무게 값

< 피벗 테이블을 만들 수 없다고 오류가 뜨는 경우 확인해 봐야 되는 것. >
1. 표의 첫 번째 줄에 행 내용이 비어있을 경우 - 필드 이름이 잘못되어 있다는 오류로 필드명이 누락되었을 경우 나옴. >> 빈 내용 확인 후 넣어주면 됨.
2. 표의 첫번째 줄의 행이 두줄로 작성되어 있는 경우 - 필드가 쪼개질 수는 없어서 오류가 뜸.

< 데이터가 변경되었을 경우 >
- 데이터가 바뀌거나 변경되면 변경된 데이터를 사용한 피벗 테이블에 가서 마우스 오른쪽 버튼 클릭 후 새로고침 버튼 눌러줘야 수정한 내용 업데이트 됨.
- 새로운 데이터는 아래 방향으로 누적되게 하고, 새로운 필드는 오른쪽 방향으로 추가를 하는 습관을 들여 피벗 테이블 만들 때 문제가 없게 하는 게 포인트!

피벗 테이블을 꾸며 줄 수 있는 기능이 있음. 자동 디자인 기능.
: 메뉴 > 디자인 또는 메뉴 > 피벗 테이블

직장인이 해야 할 엑셀 활동 3가지

1. 기획(하나로 모으기 위해 VLOOKUP함수사용)
2. 가공(숫자/ 텍스트/ 날짜 시간을 입맛에 맞게
3. 집계(집계 함수, SUMIF, COUNTIF함수와 피벗 테이블)

1. VLOOKUP함수

서로 다른 테이블에서 특정 키값을 기준으로 내가 원하는 값을 불러와서 적어주고 싶다면 VLOOKUP 함수!

형식 :
=VLOOKUP(조회하려는 항목, 찾고자 하는 위치, 반환할 값이 포함된 범위의 열 번호, 근사값 또는 정확히 일치)

엑셀은 범위가 상대참조를 기본으로 동작. 절대 참조로 걸어주는 방법은 F4클릭(숫자 앞에 $를 넣어 고정해 줄 수 있음)

그렇게 해봤는데 오류가 남.

찾을 이름을 입력하고 찾고자 하는 위치를 절대 참조로 달아두고 열번호 넣고 정확히 일치로 찾기 위해 false를 넣어 해봤는데 #Ref! 오류라니... 뭐가 문제일까.

왜 오류 나는지 알았다. 당연히 I열만 범위에 넣어 줬으니 답이 없지. J열까지 넣어 줘야 제대로 된 값이 나옴. 

이런 말같지도 않은 상황도 다 제대로 알지 못한 상태에서 해서 그런 거라 생각하면서 다시 집중하자.

찾고자 하는 위치에 불러오고 싶은 테이블만 존재한다면 열과 열로 범위를 정해줘도 된다 함. 이런 상황이면 이 방법이 오류가 더 안 날 것 같음.

분할해줘야 되는 데이터를 위해

=LEFT(분할 값, 왼쪽에서 몇 글자까지 가져올지), =RIGHT(분할 값, 오른쪽에서 몇 글자까지 가져올지)

이렇게 잘라주면 값들이 텍스트로 변경되기 때문에 숫자로 인식시켜 주기 위해  *1을 해준다.

이걸 하던 중에 뭔가 몸무게가 3자리 수일 수도 있고 c#에서 Split 함수가 생각나서 엑셀에서도 있는지 찾아봤더니 있다!

여기서도 숫자를 텍스트로 만들었기 때문에 =VALUE()를 사용해 숫자로 값 변경. ex > =VALUE(TEXTSPLIT(F13,"/")) 

근데 이 함수를 쓰면 자동으로 나누어져서 바로 옆칸에 들어가짐. 옆에 셀은 다른 함수를 쓸 수가 없다. 아마 매개변수로 이것도 선택해 줄 수 있을 듯했지만 찾아보니 나눈 문자열을 가로로 분리할지 세로로 분리할 지만 나온다.

그래서 찾아본 TEXTSPLIT함수의 매개변수들. 

TEXTSPLIT 함수

형태 : =TEXTSPLIT(텍스트, 열 구분 문자, 행 구분 문자, 공백 무시, 일치 모드, 에러 표시 문자)
열구분 문자와 행 구분 문자로 열로 나눌지 행으로 나눌지 선택됨.
공백 무시에 TRUE를 쓸 경우 공백 무시, FALSE일 경우 공백 그대로 유지, 당연히 생략하면 FALSE로 인식.
일치 모드에 0 또는 FALSE 구분 문자 대소문자 구분, 1 또는 TRUE면 대소문자 구분 X
에러 표시 문자는 에러가 발생할 상황에 표시될 문자를 지정.

SUMIF 함수, COUNTIF 함수

SUMIF함수 형태 :  =SUMIF(주어진 조건을 찾을 영역(범위), 선택한 영역에서 찾을 조건(값), 위의 영역에서 조건에 맞는 셀에서 실제로 더할 값의 영역)
COUNTIF함수 형태 : =COUNTIF(범위, 선택한 범위에서 찾을 값)

모든 함수를 입력할 때는 = 함수명(입력값) 으로 입력해야 한다.

날짜/ 시간 함수

수학 함수

통계학 함수

논리/ 찾기 함수

직접 해보면서 정리하는 엑셀 단축키
참고 엑셀 단축키 도움말 URL

++

Ctrl, Shift로 시작하는 단축키는 Ctrl, Shift 잡고 단축키 누르면 되고, Alt로 시작하는 단축키는 Alt 누르고 뗀 다음 단축키들 눌러야 됨.

< 자주 사용하는 단축키 모음 >

직전 행동 반복 : F4
회계 형태 표시 : Alt + H + K
콤마 찍기 : Ctrl + Shift + 1
틀고정 : Alt + W + F + F
문서닫기 : Ctrl + W
문서 열기 : Ctrl + O
홈 탭 이동 : Alt + H
통합 문서 저장 : Ctrl + S
선택 항목 복사 : Ctrl + C
선택 항목 붙여넣기 : Ctrl + V
마지 작업 취소하기 : Ctrl + Z
취소한 걸 취소하기(이전으로 돌아가기) : Ctrl + Y
셀 내용 제거하기 : Delete
채우기 색상 선택하기 : Alt + H, H라고 나오는데 Alt를 클릭하고 뗐다가 H를 눌러야 됨.
선택 항목 잘라내기 : Ctrl + X
삽입탭 이동 : Alt + N
굵은 글꼴 적용 : Ctrl + B
셀 내용 가운데 정렬 : Alt + H, A, C
페이지 레이아웃 탭으로 이동 : Alt + P
데이터 탭으로 이동 : Alt + A
보기 탭으로 이동 : Alt + W
상황에 맞는 메뉴 열기 : Shift + F10 or Window 메뉴키
테두리 추가 : Alt + H, B
열 삭제 : Alt + H, D, C
수식 탭으로 이동 : Alt + M
선택한 행 숨기기 : Ctrl + 9
선택한 열 숨기기 : Ctrl + 0

< 셀 서식 자정을 위한 단축키 모음 >

셀 서식 대화 상자 열기 : Ctrl +1
셀 서식 대화 상자에서 글꼴 서식 지정 : Ctrl + Shift + F or Ctrl + Shift + P
메모 삽입 : Shift + F2
스레드에 댓글 삽입 : Ctrl + Shift + F2
빈 셀을 삽입(삽입 대화 상자 열기) : Ctrl + Shift + 더하기 기호(+)
선택한 셀 삭제하기 위해 삭제 대화 상자 열기 : Ctrl + 빼기 기호(-)
현재 시간 입력 : Ctrl + Shift + 콜론(:)
현재 날짜 입력 : Ctrl + 세미콜론(;)
소수점 두 자리까지 통화 서식 적용(음수는 괄호로 표시) : Ctrl + Shift + $
소수점 자릿수 없이 백분율 서식 적용 : Ctrl + Shift + %
소수점 두 자리까지 과학적 숫자 형식 적용 : Ctrl + Shift + ^
일, 월, 년도 의 날짜 형식 적용 : Ctrl + Shift + #
시, 분, 오전 또는 오후를 사용하여 시간 형식을 적용 : Ctrl + Shift + @
표 만들기 대화상자 표시 (테이블) : Ctrl + L or Ctrl + T
통합 문서 통계 대화상자 열기 : Ctrl + Shift + G

< 선택 및 작업 수행을 위한 단축키 모음 >

셀 선택 범위를 한 셀 확장 : Shift + 화살표 키
같은 셀에 새줄 시작 : Alt + Enter
셀 입력 완료후 바로 위의 셀 선택 : Shift + Enter
워크 시트에서 전체 열 선택 : Ctrl + Space Bar
워크 시트에서 전체 행을 선택 : Shift + Space Bar

< Power Pivot 키보드 단축키 모음 >

표 전체 선택 : Ctrl + A
선택한 데이터 복사 : Ctrl + C
표 삭제 : Ctrl + D
표 옮기기 : Ctrl + M
표 이름 바꾸기 : Ctrl + R
파일 저장 : Ctrl + S
선택한 행의 첫번째 셀로 이동 : Ctrl + 왼쪽 화살표키
선택한 행의 마지막 셀로 이동 : Ctrl + 오른쪽 화살표키
선택한 열의 첫번째 셀로 이동 : Ctrl + 위쪽 화살표키
선택한 열의 마지막 셀로 이동 : Ctrl + 아래쪽 화살표키
대화상자를 닫거나 붙여 넣기 작업 등의 프로세스를 취소 : Ctrl + Esc
이전 표로 이동 : Ctrl + Page Up
다음 테이블로 이동 Ctrl + Page Down
현재 위치부터 열의 첫번째 셀까지 모든 셀 선택 : Shift + Page Up
현재 위치부터 행의 마지막 셀까지 모든 셀 선택 : Shift + End 버튼
현재 위치부터 행의 첫번째 셀까지 모든 셀 선택 : Shift + Home 버튼

엑셀 워크시트 - 이름 바꾸기, 삽입, 삭제, 이동, 복사, 색상 변경

마우스 더블클릭해서 이름 바꾸기.
시트 복사 오른쪽 버튼클릭 - 시트 복사, 붙여 넣기.
시트 탭 색상 변경 - 마우스 오른쪽 버튼 클릭 시트 탭 색 변경.

연산하여 붙여 넣기와 테두리는 제외하고 붙여 넣기.

온라인 엑셀에서는 아무래도 선택하여 붙여 넣기 기능이 없는 것 같다. 
원래의 엑셀에서만 가능한 것 같아 단축키와 방법을 적어둔다.
1. 단축키 : Ctrl + Alt + V , 오른쪽 버튼 클릭 >> 선택하여 붙여넣기 클
2. 표에 연산하고 싶은 숫자를 다른 셀에 입력 후, 표의 셀들을 선택 >> 선택하여 붙여 넣기에서 원하는 연산 선택 후 테두리만 제외 선택 후 확인버튼 클릭.

여기서 테두리만 제외를 선택하는 이유는, 아래의 첫 번째 사진을 보면 1000의 테두리가 없기 때문에 선택하여 붙여 넣기를 할 경우 표의 숫자들의 테두리가 사라지기 때문이다.

++
숫자를 계산할 때는 홈 >> 숫자 >> 쉼표를 클릭해서 세 자릿수에 ','가 들어가도록 적용해서 보기 편하게 하자.

셀 병합 후 가운데 맞춤을 해서 표의 제목 정하기

금액을 나타낼 셀을 선택 후 = 를 입력 후 단가 셀을 클릭 +  연산자 입력 + 수량 셀 클릭

수량과 금액 드래그 해서 합계를 표시할 셀까지 드래그 후 수식 >> 자동합계 클릭 

구성비 구하기
= 금액 / 합계 금액(절댓값>> 단축키 f5클릭)

표로 설정하기위해 테두리 만들어 주기

굵은 바깥쪽 테두리 만들어서 마무리

++

셀 서식에서 다른 테두리 클릭 후 대각선 클릭해서 선택해 주면 셀안에 대각선 줄 넣어 줄 수 있다는데. 나는 온라인 엑셀로 해보고 있는데 아무리 찾아도 셀 수식이 안 보여서 대각선 줄을 못 넣었다.

마무리 결과

(추가) 내꺼에선 안되지만 셀 서식 실행하기 누르려면 4가지 방법이 있는 거 같음.

1. 마우스 우클릭 + "셀 서식" 클릭.
2. (위 사진 참고 빨간색 동그라미 있는 곳에 있는 아이콘) 홈 탭에서 모서리에 있는 아이콘 클릭.
3. Ctrl +1 클릭.
4. 마우스 우클릭 + F.

유튜버 짤막한 강좌분의 엑셀 강좌를 보고 공부했다. 내용이 이해가 잘 간다.

데이터 입력

1. 문자 데이터

1. 입력한 문자의 길이가 셀 너비 보다 길면 오른쪽 빈 셀을 넘어서 화면에 표시, 오른쪽 셀에 데이터가 있으면 셀 너비만큼만 화면에 표시 >> 열 너비 조절 가능 (직접 드레그해서 조절 or 셀 경계선 더블클릭 자동 조절) 
2. 0으로 시작하는 학번, 고객번호 등을 입력하면 유효하지 않는 값으로 인식해서 0이 입력 안됨 >> 숫자 앞에 '(작은 따옴표)를 입력, '로 숫자가 문자 형식으로 변경되어 정상적으로 데이터가 입력.
3. 한개의 셀에 2줄 이상의 내용을 입력할 때는 Alt + Enter를 한 후 줄 바꿈 후 데이터를 입력.(줄바꿈 기능)

2. 숫자 데이터

1. 숫자 데이터를 입력하면 셀 오른쪽으로 정렬.
2. +, -, 소수점, 콤마, 괄호와 같은 기호와 함께 사용할 수 있다.
3. 12자리 이상의 숫자를 입력하면 지수 형태로 표시 >> 정상적인 숫자로 표시하고 싶다면 단축키 ctrl +1 클릭후 숫자로 변경해주고 확인.  16자리 이상 입력하면  나머지 자릿수는 0으로 채워짐.
4. 서식이 적용된 데이터의 경우 열 너비가 부족하면 #으로 표시 >> 열 너비 조절해주면 #으로 보이던게 정상적으로 보임.
5. 분수 1/2를 입력하면 날짜로 인식(/를 날짜로 인식) >> 0을 입력하고 한 칸을 띄운 다음 1/2를 입력 (그래야 소수 0.5로 처리되어 계산 가능)

3. 날짜 데이터

1. 날짜는 - 또는 /로 구분해서 입력.
2. 현재 날짜를 자동으로 입력 >> Ctrl +;(세미콜론) 클릭.
3. 현재 시간을 자동으로 입력 >> Ctrl + :(콜론) 클릭.
4. TODAY,NOW 함수를 사용하여 현재 날짜 또는 날짜와 시간을 입력.

데이터 표시 형식

단축키 ctrl + 1 >> 사용자 지정 범주 클릭 형식에서 원하는 형식으로 변경

자동 채우기 기능

셀에 숫자나 날짜 입력후 채우기 핸들에 마우스를 두고 원하는 셀까지 드래그한 후 연속 데이터 채우기 선택.

텍스트, 숫자, 날짜 사용하기

1. 텍스트는 왼쪽정렬, 숫자는 오른쪽 정렬이 기본, 날짜는 년-월-일 또는 년/월/일로 입력.
2. 숫자 앞에 ' 입력할 경우 텍스트로 인식 (셀의 왼쪽 위 녹색 삼각형).
3. 날짜 입력을 제대로 하면 피벗 테이블 날짜 관련 옵션 설정 가능, 필터 옵션 날짜 단위(년, 월, 일)로 설정 가능, 날짜 간의 연산(덧셈, 뺄셈) 가능.

텍스트로 서식이 지정된 숫자를 숫자로 변환하는 수식 오류 해제방법 4가지

인프런에서 찾아서 공부하고 있었는데 없어진 기능들이 있는 것 같아 찾아보니 무려 4년전의 강의.

유튜브에서 최근 강의로 다시 강의를 찾았다. 다음주 부턴 그걸로 다시 공부 시작해야겠다.