노년코딩

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

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

피벗 테이블

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

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

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

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

< 피벗 테이블을 만들 수 없다고 오류가 뜨는 경우 확인해 봐야 되는 것. >
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(범위, 선택한 범위에서 찾을 값)

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

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

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

구성비 구하기
= 금액 / 합계 금액(절댓값>> 단축키 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년전의 강의.

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

액셀 수식의 개념과 만드는 방법

1. 셀 선택(클릭하기)
2. 등호(=) 입력(수식 입력줄) - 모든 수식은 등호로 시작
3. 수식 입력 (= 1+2)
4. 엔터 클릭 (완성값 : 3)
수식 입력줄에 = 1+2 라는 수식이 나타나있는 것을 확인할 수 있음.

수식의 구성 요소 : 상수와 연산자

상수: 계산되지 않은 값(value) 자체. 입력한 값이 변하지 않음.
연산자: 수식의 각 요소에 더하기, 빼기 등 정해진 기능을 수행하는 =, + 등의 기호

"참조"를 활용한 수식 작성법

참조(References) : 다른 셀이나 범위의 값을 가져오거나 사용하도록 설정하는 엑셀의 핵심 개념

셀을 참조할 때는 셀 주소를 입력하고 범위를 참조할 때는 해당 범위의 시작과 끝을 콜론(:)으로 결합해서 입력
셀참조 = A1
범위 참조 = A1:A2
수식에는 셀또는 범위를 참조할 수 있음.

"함수"를 활용한 수식 작성법

함수 : 단독으로 또는 더 긴 수식의 일부로 사용할 수 있는 미리 만든 수식.

"자동합계"를 활용한 스마트한 계산

자동 합계(Autosum) : 홈 탭의 편집그룹에 있는 "자동 합계"를 누르게 되면  or 수식 탭의 함수 라이브러리 그룹에서 "자동 합계"를 누르게 되면 자동으로 범위가 만들어짐. 
행단위, 열단위로 잡고 눌러도 자동 합계 가능. 행과 열 둘다 필요할 경우 행과 열을 하나씩 더 추가해서 범위 선택.

단축키 : Alt + = (But, 한글 윈도우에서는 실행 ㄴㄴ), 한글 윈도우에서 쓰고 싶다면 자동합계를 구할 범위 선택 후 Alt + H + U +S 입력하면 단축키로 활용 가능.