피벗 테이블 - 기본 사용법
Unique(B:B) 통해 원하는 고객사 열 생성
=SUMIFS(C:C,B:B,L3)통해 C 열에서 B 열이 L3인 Schultz Inc인 것 선택
=SUMIFS(C:C,B:B,L3,J:J,TRUE)같은 것
모든 셀 선택 후 alt, 삽입, 피벗테이블,테이블/범위에서 클릭
새로운 시트 생성됨
고객사에 따른 청구액에 해당하는 피벗 테이블
필터로는 청구 상태
피벗 테이블 - 여러 개의 필드를 각각의 항목으로 지정하는 방법
필터로 청구상태를 넣었는데 영업담당자도 넣을 수 있음
필터가 2개!
행에 고객사 이외에 다른 것도 넣을 수 있음
거래 시트에서 열의 이름을 대륙으로 바꿨으므로 피벗 테이블에서 새로고침 해줘야 함
대상 상태 -> 대륙
넣는 위치가 중요함
대륙 옆에는 고객사들의 청구액의 합계 금액이 나옴
피벗 테이블 각 영역에 둘 이상의 값을 넣을 수 있음! 필터, 열, 행, 값 모두 있음
필터와 값은 순서가 상관없지만 행 같은 경우에는 요약 정보가 나오기 때문에 순서에 따라 내용이 바뀜
피벗 테이블 - 데이터 오류 발생 시 수정하는 방법
하나의 값 오류로 인해서 여러 데이터에 오류가 날 경우 수정하는 방법
=ISERROR(F102)필터 걸고 TRUE인 경우 보면 입금일에 ERROR가 남
#VALUE!
=IFERROR(F2,"")ERROR 나면 공백으로 해라
근데 아래와 같이 나옴
F열이 입금일(날짜)이긴 하지만 서식이 숫자이기 때문이다.
복사 붙여넣기 -> 선택하여 붙여넣기 -> 서식
우클릭 -> 셀 서식 -> 날짜해도 같은 값이 나옴
피벗 테이블 - 유용한 기능들 알아보기
열에도 넣기
어떤 열을 삭제하고 싶다면 우클릭 후 서비스구분 부분 합 클릭
클릭해서 열 삭제하고 필터 변경하는 방법도 좋음
필터를 드래그 해서 하면 행이나 필터 중 하나 밖에 할 수 없기 때문에 행에 필드를 놓고 필터에도 그 열을 사용하고 싶다면 클릭으로!
피벗 테이블 구성하는 특정 데이터가 어떻게 추출된 것 인지 궁금할때 사용하는 방법 -> 해당 셀을 더블클릭
그때그때 시트를 지워줘야 한다는 거추장스러움이 있음
Product7의 값에 더하기 500 한걸 알고 싶을 때, 클릭하고 방향키 움직일 경우 영역이 range로 잡히지 않고 피벗데이터라는 함수로 자동으로 적용됨
그 수식을 복붙해 아래에 넣었을 때 절대참조처럼 같은 값만 나오게 됨
GETPIVOTDATA("청구액($)",$A$3,"고객사","Blick Inc","서비스구분","Unit3","상품","Product7","대륙","Europe")이걸 다 지우고 그냥
=H7*1300이걸 하면 상대참조처럼 값이 잘 나옴
정규표현식 - 숫자 형식 지정하기
엑셀 파일을 구글 시트로 불러들이면 됨
정규표현식 사용 시 구글시트로 불러들이고 다시 엑셀로 다운하면 됨
구글시트에서 REGEXEXTRACT(텍스트, 정규표현식)
숫자에 표현하는 정규표현식 => \d
0이 나오는데, 이유는 \d가 숫자를 나타내는 정규표현식이기 때문에 첫 숫자 하나만 찾아주었음
내가 원하는 것은 010-1234-5678의 전화번호 형태임
여러 개의 숫자를 표현하는 형태를 알아야 하는데, \d+를 하면 010이 나옴
정규표현식에서 +는 하나 또는 그 이상 연속해서 나오는 것을 찾으라는 것임
그 뒤에는 내가 원하는 것!
\d+-\d+-\d+
정규표현식 - 전화번호 형식 지정하기
01\d-\d{4}-\d{4}01로 시작하고 그 뒤에 숫자 하나, 그 다음 -, 그 다음 숫자 네 자리{4} 두 개 반복
이러면 010-1234-5678의 형태만 읽어옴!!!
있을 수도 있고 없을 수도 있는 경우!
대시(-)가 없는 전화번호의 경우 : 01012345678
01\d-?\d{4}-?\d{4}물음표는 앞에 있는 것들이 있을 수도 있고 없을수도 있다는 것을 의미함
물음표 앞에 있는 것이 있을 수도 있고 없을 수도 있다는 것을 의미 -> -가 있을수도, 없을수도 있다는 것을 의미
옛날 전화번호 경우 : 011-123-4567
가운데가 세개일수도 있고 네개일수도 있는 경우
01\d-?\d{3,4}-?\d{4}두 가지 모두 일 경우 중괄호 안에 콤마로 구분해서 지정해주면 됨
정규표현식 - 문자열 형식 지정하기
\w+알파벳, 숫자, _
_ 이외의 특수문자는 잘림
\s+공백을 나타냄
=regexextract 함수로는 별 쓸모가 없음
REGEXREPLACE 함수 사용
=REGEXREPLACE(A11,"\s+", " ")A11 셀의 내용을 가져와서, 여러 개의 공백을 가져와서, 공백 하나로 바꿈
[가-힣] : 모든 한글을 나타냄
하나 이상 이어진 것들을 가져옴
정규표현식 - 자동차 번호판 형식 지정하기(1)
숫자 3개 오고 한글 1개, 숫자 4개
\d{3}[가-힣]\d{4}
\d{3}\s?[가-힣]\s?\d{4}
# 공백여부 고려해서, 공백이 있을수도 없을수도 있는 모든 경우에 대해 대비정규표현식 - 자동차 번호판 형식 지정하기(2)
원하는 내용만 가져오려면 괄호를 사용해야 함
=REGEXREPLACE(A30,A27, "###")A30에 있는 내용을 A27 정규표현식을 참조해 ###으로 바꿈
이걸로 자동차 번호판 부분 추출
=find("###",D31)D31 셀에 해당하는 내용에서 60번째에 ###이 있음
=len(A30)-len(D31)+3
#이 3개이므로 마지막에 3을 더해주면
len(A30)-len(D31) -> 얼마만큼 ###을 했는지 알 수 있음
###의 길이가 3이므로 +3 해주면 전체 번호판의 길이가 12인걸 알 수 있음
MID 함수 이용해 문자열의 원본에서 번호판이 시작되는 위치인 60번째에서 12개의 글자를 가져오는 방법
=mid(A30,E31,E32)원본에서, 번호판이 시작되는 위치인 60번째에서, 12개의 글자 가져오기
>>> 번호판!