일단 드롭다운 메뉴가 뭐냐면 

이거다. 이건 내 네이버 블로그 카테고리 선택 메뉴다. 아니 엑셀에서 이걸 만든다 이거요? 예.

드롭다운 메뉴를 어떨 때 쓸 수 있는가... 

이건 내가 포켓몬 홈 지역도감 할 때 뭐뭐 등록해야 할 지를 구글 스프레드시트에 정리해둔 것이다. 등록은 말 그대로 포켓몬 홈 도감에 등록된 것이고, 준비완료는 홈에 들어갈 준비가 된 거고, 미등록은 홈 도감에 등록이 안 된 포켓몬이다. 그러니까 등록여부 열에는 미등록, 준비완료, 등록 이 세 가지만 들어가게 되고, 저 세가지만 들어갈 수 밖에 없다. 근데 저런거 정리하다가 오타나면 아...

이런 초유의 사태를 막기 위해서 드롭다운 메뉴를 만드는 것이다. 위 상황처럼 특정 개수의 특정 값만 입력하는 상황에서 드롭다운 메뉴가 꽤 유용하다. (예: 합격/불합격, 미진행/진행중/완료)

여기서 D열에 드롭다운 메뉴를 만들어볼건데. 메뉴 안에 들어가는 값은 미진행/진행중/완료/취소됨 네 가지이다. 

D3부터 D7까지 블록을 잡고 데이터-데이터 유효성 검사를 누르자.

제한 대상을 목록으로 잡고 원본란에 값을 적는다. 우리는 여기서 미진행/진행중/완료/취소됨 네 가지로 드롭다운 메뉴를 만들거니까 네 가지를 콤마로 구분해서 넣어야 한다. 

이렇게만 하면 되는데... 솔직히 이렇게만 하면 한눈에 확 안 들어오죠? 그러니까 조건부서식을 주자.

미진행/진행중/완료의 경우 진행여부에서 색깔로 확인할 수 있게 했고, 취소된 프로젝트의 경우 프로젝트명에 취소선을 그었다.

저번주의 그 표다. 이걸 어떻게 만들거냐면 

이렇게 만들거다. 근데 이제 페인트통 없이. 

보기로 들어가서 표시란에 보면 눈금선이 있다. 아마 특별히 손대지 않았다면 눈금선에 체크표시가 되어 있을텐데, 이 체크표시만 없애주면 된다. 메뉴 찾아가기 귀찮으면 Alt+W+V+G를 누르면 된다.

깨끗해졌죠? 이렇게 해 두면 셀 색 바꾸다가 뻑날 염려도 없다. 그리고 눈금선을 다시 보고 싶다면 또 셀 씌워서 페인트 누르고 투명색으로 바꿀 필요도 없이 걍 눈금선 체크만 누르면 된다. 

https://www.instagram.com/p/DGQiwQCJ8vW/

이거 보고 띠용했다. 

영상을 보면 알겠지만, 룩업 함수는 찾는것보다 왼쪽에 있는 데이터를 못 찾는다.

왼쪽 표를 이용해 오른쪽 표에서 VLOOKUP 함수로 소속과 직책을 찾은 결과이다. 찾고자 하는 게 이름이고, 이름 우측에 있는 소속과 직책은 제대로 찾아주는데... 그럼 뭘 못 찾는다는건가요? 

VLOOKUP 함수를 이용해서 이름으로 홍길동씨 번호를 찾아달라고 하면 못 찾는다. 공식에서도 조회 값은 항상 VLOOKUP이 제대로 작동하는 범위에서 첫 번째 열에 있어야 한다는 것을 기억해 두세요. 라고 못박아뒀다. 그러니까 이런 경우 최후의 병기 룩업 함수로 데이터를 못 가져오는 사태가 터지게 된다. 번호를 찾아야 할 일이 없다고? 물론 그럴 일은 없지만, 업무를 하다 보면 최종병기 룩업으로 이상한 그림이 나오게 될 때가 온다. 그럼 엑셀팡션 없이 손으로 해야 하나요? 놉. 

H3셀로 커서를 가져가자. 

=dget을 입력한 다음 왼쪽 표를 쫙 잡고 F4를 한번 눌러준다. 

우리는 저 번호로 이름을 가져올거니까 H2를 눌러주고 

그 옆에 번호 있는 셀을 범위로 잡고 F4를 한번 눌러주자. 

다 됐으면 자동채우기 핸들 드르륵 해서 소속 직책까지 채우자. 

참 쉽죠? 

셀 너비 조절을 안 해서 짤렸는데 번호만 치면 해당 번호의 이름, 소속, 직책을 알아서 가져온다. 

왼쪽 표에 11번까지만 있는데 12번 찾아달라고 했더니 엑셀이 VALUE! 에러를 토하면서 미쳤습니까, 휴먼? 한다. 이건 필요하다면 IFERROR함수로 처리가 가능하다.

여기까지 읽으신 분들 중... 에이~ 이거 365만 되는 거 아니예요? 하신 분들이 계실텐데 

이거 365 아니어도 됩니다 여러분.

이게 약간 어떤 느낌이냐면... 보통 우리가 숫자를 합칠때는 

이런 식으로 한 셀에 숫자가 하나씩 있다. 근데 우리가 합할 숫자가 어떤 상태냐면

이런 식으로 한 셀에 들어있다. 이거 이 상태로는 SUM함수 안 먹히니까 쟤를 분리해야 한다.

Office 365 사용 시

C2셀에 커서를 가져가서 =sum(textsplit(B2, ","))를 입력했더니 여러분을 반기는 것은 0이었다. 나도 당황함. 여러분은 당황하지 말고 textsplit 앞에 --를 붙여서 =SUM(--TEXTSPLIT(B2,","))를 쳐 주자. 그러면 

합계가 정상적으로 계산되니까 이거 걍 자동 채우기로 쭈루룩 드래그하면 된다. 당신이 Office 365를 쓴다면 이거 하나면 땡이다. 하지만 이전 버전을 쓰고 있다면...?

Office 365 이전 버전을 사용 시 

왜 이걸 나눠놓았냐... TEXTSPLIT 함수는 이전 버전에 없다. 365만 사용 가능하니까 웹 오피스에서 365를 쓰던가(물론 본인이 microsoft 계정이 있다면...) 포기하던가... 아니 업무인데 포기하면 안된다. 그니까 이 방법 보고 그대로 하십시오.

콤마로 구별된 숫자들을 다 씌운 다음 데이터-텍스트 나누기로 들어가서 구분기호를 적어주자. 여기서는 콤마로 구별했기떄문에 콤마란에 체크했다.

그 다음 SUM함수로 합계 구하면 된다. 이전 버전 쓴다고 어 X됐다 이런 건 아니고, 그냥 해야 할 일 한 단계가 더 추가될 뿐이다.

이럴 일이 없을 것 같지만 오래된 파일을 열었을때나 csv 잘못 불러왔을 때 이런 심각한 참사가 터지는 경우가 있으니 알아두면 좋다. 생각보다 직장생활 하다 보면 없을법한 일을 많이 겪게 될 것이다.

https://www.instagram.com/p/DFK7DdcR94k/

여기 참고했다. 

일단 제목이랑 인별 제목을 보면 알겠지만, 함수 안 쓰고 두 열을 비교할거다. 솔직히 퇴근시간 10분 전에 이것좀 봐달라고 들어오는데 어느 세월에 함수 치고 앉아있겠음... 본인이 영타 2~300타 친다면 모를까.

저 표 안에 있는 건 차깨비들이다. 그리고 우리는 이 표에서 다른 부분을 찾아서 강조할거다. 그러니까 이게 함수 없이 된다고요? 네! 됩니다!

일단 비교할 표를 블록잡고 Ctrl+\를 누르자. 슬래시 아니고 엔터키 위에 있는 백슬래시를 눌러야 한다.

두 표에서 다른 부분을 찾았다. 

그 상태에서 아무것도 건들지 말고 글자색이나 배경색을 바꾸면 이런 식으로 다른 부분을 강조할 수 있다. 

이거 꼭 두 줄 아니어도 된다.

여기서 따로따로 강조하고 싶다면 일단 그룹 1, 2를 선택하고 ctrl+\를 누른 다음 셀 서식을 바꿔주고, 그룹 3과 그룹 1을 선택하고 ctrl+\를 누른 다음 다시 셀 서식을 바꾸면 된다. 1, 3 순서로 선택했더니 1번 표에 있는걸 선택하길래 찾아봤더니 선택한 범위의 좌/우를 비교해서 값이 다른 왼쪽의 셀을 선택하는 단축키란다. 인접한 셀은 그냥 드래그해서 몰랐는데, 저걸 한줄한줄 선택해서 저 상태로 만들려면 그룹 2, 그룹 1 순으로 선택해야 한다는 얘기가 된다.

아무튼 따로따로 비교하면 이런 식으로 줄별로 다른 색으로 주는 것도 가능하다.

https://www.instagram.com/p/DF7mG9yJeRw/

이거 해볼거다.

엑셀 IF함수 할 때 제일 흔한 예제가 

1. 점수 커트라인에 따라서 합격 불합격 표시하기

2. 점수별로 등급 매기기(IFS 나오기 전까지는 IF의 연속이었음)

이렇게 두 개 있는데, 오늘 해볼 건 밑에껄 IF함수 없이 해볼거다. 그게 돼요? 되니까 하죠.

여기 가상의 성적표가 있다. 그러면 보통은 IF_C3>95,"A+",IF(...)) 이런 식으로 줄줄이 IF의 연속이거나 IFS(C3,>95,"A+",...) 이런 식으로 쓸텐데... 진짜 IF를 안 쓰고 이게 된다고? 거두절미하고 일단 D3셀에 커서를 올리자. 그리고 D3셀에 =VLOOKUP(C3,$F$3:$G$11,2)를 입력하면 된다. 

됐다. 그대로 쭉 잡고 드래그하면

이런 식으로 성적을 다 매길 수 있다. 어, 근데 제갈명씨가 이의제기를 하러 왔다가 되려 점수가 깎여버렸다?

그럼 이렇게 점수를 수정하면 성적이 바뀐다.

참고로 이 방법을 쓸 때는 조심해야 할 게 있는데, 성적이랑 점수가 오름차순으로 정렬되어 있어야 한다. 내림차순으로 정렬되어 있으면 망합니다. 

아, 얘도 365만 된다... 아니 빌형... 

여기 표가 있다. 아오 드럽게 중구난방이다 그죠? 이 데이터를 주고 카테고리별로 주문량 합해달라고 하면 여러분들 어때요? 정리는 왜 또 이렇게 개판인가 싶고... 한숨부터 나오잖음? 근데 오피스 365면 이제 그럴 걱정이 싹 없어진다 이 얘기다. 뭘로? 그룹바이로!

일단 F3셀에 커서를 가져가자. 

=GROUPBY(를 치니까 어우 뭔가 많아... 이거 어떻게 해요? 아 이제 잘 보고 따라오면 된다. 

B3:B21은 중구난방으로 섞여있는 상품의 분류들이고, D3:D21은 각 상품별 주문량이다. 이 분류에 따라 주문량 합계를 보여줄거기때문에 그룹바이 괄호 안에 범위를 B3:B21, D3:D21로 준 것이다. 여기까지는 이해하셨죠? 그럼 저 SUM은 뭔데요? 합치라는 얘기죠. 그니까 저 세개만 따닥 입력하면 

요로코롬 합계가 뾰로롱 하고 나오는것이다. 그런데 이 함수... 괄호 치니까 뭔가 많이 보이던데, 저 세 개가 그 중에서 뭔지 모르겠죠?

그룹바이 설명문에 뭐가 많은데, 우리가 입력한 B3:B21, D3:D21, SUM은 각각 행 필드, 값, 함수이다. 그러니까 여기를 그룹화해서 여기를 합해달라 이 얘기. 함수에 합계 말고 다른것도 있는데 예시가 합계밖에 없었음... 

https://support.microsoft.com/ko-kr/office/groupby-function-5e08ae8c-6800-4b72-b623-c41773611505

설명문은 여기서 볼 수 있다. 저 표도 여기서 갖고온거고. 

이런 식으로 어떤 카테고리별로 세주는 것도 가능하다. 어떻게 했냐고? 

여기 음식점 별점이 있다. 우리는 고객명은 필요없고, 별점을 점수별로 몇 명이 줬는지 세볼거다. E3셀에 커서를 가져가서 =GROUPBY(C3:C20,C3:C20,COUNTA)를 입력해보자.

엔터키를 누르면 별점 3점 몇명, 3.5점 몇 명, 4점 몇 명, 4.5점 몇 명, 5점 몇 명 이런 식으로 세준다.

아, 이거 365만 됩니다.

여기 찰진 표가 있다. 뭘 예시로 해야 하나 몰라서 걍 내 컴퓨터에 있는 음악들 중 특정 아티스트 음악 몇개로 묶었는데... 이 표를 함수 한방이면 복붙 없이 합칠 수 있다고요? 네! 그렇습니다!

E2셀로 커서를 가져간 다음 =VSTACK(을 치고 

이렇게 합칠 표들을 선택해주면 된다. 드래그 콤마 드래그 콤마... 피곤하다고요? 에이 복붙보단 낫지.

이렇게 표들이 합쳐진 것을 볼 수 있다. 근데 이걸 왜 알아야 하느냐고요? 

생각해보자. 표를 일일이 복붙했는데 표 값이 바뀌었다... 그러면 복붙한 표를 찾아가서 일일이 수정해야 되잖아요? 근데 VSTACK으로 합친 표는 일일이 찾아서 다시 수정할 필요가 없다. 

여기 카페 메뉴판이 있다. 근데 홍차에서 잉글리시 브렉퍼스트를 빼고 다즐링을 넣을거다, 그러면 복붙한 표에서는 왼쪽 오른쪽을 다 손댔겠지만 

저기 왼쪽에 커서 있는거 보이쥬? VSTACK으로 합치게 되면 왼쪽 표를 수정했을 때 오른쪽에 바로 반영된다.

아, 본인이 합쳐야 하는 표가 누워있다고요?

누워있는 표는 HSTACK으로 합치면 된다. 예시는 대충 만들었음...

https://www.instagram.com/p/C_LfdTXNkaz/

이거 해볼거다. 

여기 가상의 면접 일정표가 있다. 이걸로 타임라인을 만들어볼거다. 원래 개인 일정으로 할까 했는데 그건 너무 개인정보라... 라고 해봤자 팔할이 포켓몬고 이벤트다 

데이터를 전부 씌운 다음 삽입-피벗 테이블로 들어간다. 그리고 이대로 피벗 테이블을 만들어주자. 

영상에서는 필드가 두개였는데 나는 항목이 하나라 항목만 가져왔음... 보니까 날짜 빼고 다 가져오는 듯 하다. 

피벗 테이블 분석-필터-시간 표시 막대 삽입을 누르자. 

체크박스에서 날짜를 선택하고 확인을 누르면 이런 게 나올텐데, 이게 타임라인이다. 이걸 그래서 어떻게 쓰냐고? 이걸 그래서 어떻게 쓰냐고? 스크롤바를 왼쪽으로 옮긴 다음 1을 눌러보자.

아까 예시로 입력한 면접 일정 중 1월 면접 일정만 표시됐다.

1의 오른쪽 끝을 드래그해서 2로 가져가면 1월부터 2월까지 면접 일정을 볼 수 있다.

1월부터 3월까지 했더니 1분기분기라고 나오는데... 빌형 이거 분기 하나만 써도 되는데...

저는 저 표 말고 타임라인만 보고 싶습니다! 그러면 방법이 하나 있다.

이런 식으로 표를 만들고 

피벗 테이블을 다른 시트에 만들면 되는데 일단 저건 조정좀 해야 할 듯.

아무튼 이런 식으로 정리한 표랑 피벗 테이블을 다른 시트에 만들면 원본을 안 보여줄 수 있다. 

이걸 어디다 쓰냐... 본인은 전에 성형외과 차트 스캔을 했다고 했는데, 그러면 차트 스캔을 어디서 어디까지 했는지 정리를 해 둔다. 그걸 10달동안 했으니까, 10달동안 어디까지 정리했는지 적어뒀다가 타임라인을 만들어서 보면 저번달에는 얼마나 했는지, 지난 3개월동안 얼마나 했는지 이런 식으로 확인할수 있는 것.

들어가기에 앞서, 해당 기능은 365에서만 사용 가능하다. 버전이 낮으신 분들은 애도...

나도 2019 깔려있는데 이걸 어떻게 하냐면 웹으로 하고 있다. 

여기 있는 회사들의 로고를 가져와볼건데... 엥? 이미지 검색 안 해도 돼요? 예, 됩니다.

C3셀에 =IMAGE(CONCAT("https://logo.clearbit.com/",B3,".com"))를 입력하자.

그리고 쭉 드래그하면 이런 식으로 로고가 나온다. 

셀 세로 길이를 늘리면 로고도 커진다. 근데 저 사이트 뭐 하는 사이트냐고? 

https://www.logo.dev/

찾아보니 이런 사이트가 나오는데 로고 API란다. 그래서 URL로 직접 접속이 안됐구만...

한국 기업도 세계에서 알아주는 회사면 검색은 된다. (예: 삼성, LG) 근데 

왜 삼성 디스플레이는 못 갖고오냐...

아, 그리고 회사명에 한글 쓰면 안먹힙니다. 사이트가 영어기반임... 그리고 회사명에 공백이 있는 경우가 있는데 

여기 하이라이트 해 둔 저거 입력해야 로고 가져오는거임.

에? 셀 병합을 안 쓰고 셀 병합을 한다고요? 뭐 테두리 없애는거임? 아니면 뭐 가운데 찾아서 손으로 하는거예요? 아니다. 병합하고 가운데 맞춤을 안 쓰고 그거랑 똑같이 할 거다.

이걸 근데 왜 알아야 하나요? 답은 간단하다. 

여기서 핸드폰과 태블릿PC의 판매 수량 합계를 구하려고 SUM함수를 썼더니... 범위가 왜 이따위여?

병합의 폐해는 여기서 끝나지 않는다. 데이터 한줄만 긁기도 안되고, 뭐 좀 할라치면 셀 크기가 동일해야 한다면서 안된다. 이쯤되면 대체 이럴거면 병합 왜 만들었나 싶게 사소한 부분에서 병합된 셀이 방해가 된다 이거요. 

병합할만큼 블록을 잡고 Ctrl+1을 눌러 셀 서식을 열자. 

맞춤-가로-선택 영역의 가운데로를 선택하자. 

이렇게 됐는데... 

잘 보면 우리가 병합해야 할 게 세개 더 있다. 아니 그럼 이것들도 다 드래그해서 컨트롤 1 하면 되나요? 

아뇨, 범위 잡고 걍 F4 누르면 됩니다. 엑셀에서 F4 누르면 직전에 실행했던 걸 반복할 수 있게 해 준다. 일러스트레이터의 Ctrl+D(맥은 cmd+D)랑 같은 기능. 아, 물론 셀 범위 고정해주는 것도 F4긴 함.

위에 병합하고 가운데 맞춤 했을 때와 달리 sum함수 범위 잡을때도 범위가 개발살나지 않은 것을 볼 수 있다.

이력서 양식같은 거 만들 때 보면

이런 식으로 양 끝에 균등분할 된 걸 볼 수 있는데, 이걸 해볼거다.

저 균등 분할을 띄어쓰기 노가다로 할 수 있는 거 아니냐고 할 수 있는데, 띄어쓰기로 노가다하게 되면 일단

보기만 해도 OME를 유발하는 이 일정하지 않은 너비를 보라...

너비 맞춘다고 글자 크기를 줄일수도 없는 노릇 아닌가? 그리고 저거 내용 바뀌거나 추가되면 또 띄어쓰기로 맞춰야되는데 항목이 한두개가 아니라면? 

균등분할할 셀을 선택해주자.

그리고 Ctrl+1을 눌러 셀 서식으로 들어간 다음 맞춤을 누르자.

텍스트 맞춤-가로를 눌러 균등 분할을 선택하고 확인을 누르면 된다.

쉽죠? 

B5셀에 주소가 추가됐는데 서식이 바로 안 바뀔것이다. B5셀에 Ctrl+D를 눌러서 위에꺼 가져온 다음 주소를 입력하면 저렇게 바뀐다. 

이게 뭔 소리냐고? 읽다보면 알게 된다. 

이런 식으로 표 제목이 길 때를 얘기하는거다. 이 표는 얼마 안되지만 저런게 막 쭉 있다고 생각해보자... 이럴 경우 엑셀이 너비를 맞출 때는 길이가 제일 긴 셀에 맞추기때문에 저기서 길이가 줄어들지도 않고, 숫자에 맞추려고 임의로 길이를 줄이다보면

이런 식으로 가려진다. 씁... 이러면 안되는데...?

당황하지 말고 제목을 쭉 선택하자. 

텍스트 정렬 옆에 보면 가나 밑에 화살표 있는데 그게 위로 올라가고 있는 게 있다. 그걸 누른 다음 시계 반대 방향 각도를 눌러주자. 

이렇게 제목을 회전시키고 셀 너비를 아까와 비슷하게 맞춰보면 날짜가 #####이 안되는 것을 알 수 있다. 

이럴 일이 없을 것 같지만 의외로 있다. 회사에 제출해야 하는 서류(예: 이력서) 양식이 엑셀이라거나, 출력해야 하는 양식(정확히는 출력해서 수기로 적는 양식)이 엑셀이라거나... 그 양식이 회사에 만들어진 게 있는 경우도 있지만 지금 이 글을 읽는 당신이 만들어야 할 수도 있다. 

그럴 때 빨리 양식 만들고 물 한잔 마시자. 요즘같은 날씨에는 물을 자주자주 마셔줘야 목이 안 아프다.

현미경이라는 것은 본격적으로 사려고 보면 무지하게 비싸다. 광학현미경도 비싸지만 형광현미경(옆에 컴퓨터 붙어있음) 이런걸로 넘어가면 있는 실험실에 가서 비는 시간에 대여를 해야 하고(비는 시간이어도 그 실험실 랩미팅 있으면 못간다...), 공초점현미경이나 주사터널현미경 이런건 그냥 공동기기실에나 구비해놓는 수준이지 랩실에는 없는 경우가 많다. 

아무튼 각설하고, 이걸 출력해서 수기로 적어야 하는데 저대로 출력하면 뭘 어따 쓰라는건지 칸이 안 보인다. 그니까 밑줄을 그어줘야 하는데...

밑줄 칠 범위를 잡고 컨트롤+1을 눌러서 셀 서식으로 들어간 다음, 사용자 지정으로 들어가자.

그리고 형식란에 @*_를 입력하면 된다. 저게 뭔 뜻이냐면 셀에 들어오는 내용 뒤에 _를 셀 너비만큼 붙이라는 얘기.

확인을 누르면 밑줄이 생겨있는 것을 볼 수 있다. 

내용을 바꿔도 유지되나요? 오브콜스.

B6의 사용시간을 이용 예정 시각(몇시에 현미경 쓸건지)으로 바꿨는데, 글자에 언더바가 침입하지도 않고 남은 너비만큼 알아서 언더바를 그려준다.

https://www.instagram.com/p/C_gMDJcSXtQ/

여기 참고했음. 

내가 제일 싫어하는 게 비효율적으로 똑같은거 반복하는건데 그걸 한방에 해결할 수 있는 방법이 나왔다. 이건 꼭 익혀두십쇼.

일단 행정동(읍면동)까지 써봤다... 이걸 진짜 자동으로 채울 수 있나요? 그럼요! 일단 C3셀로 커서를 가져가보자.

그리고 C3, D3, E3셀을 채워주면 된다. 이게 다냐고? 그렇습니다. 순차적으로 채워줬으면 C4셀로 커서를 가져간 다음 컨트롤 E를 누르면 된다.

참 쉽죠?

내 해봤는데 슬래시로 나눠둔것도 자동채우기가 된다.

전화번호나 생년월일의 경우 자동채우기를 하면 년월일이 어떻게 되냐면 

이렇게만 하면 숫자로 자동채우기가 된다. 근데 만약에 숫자로 채우면 안된다... (전화번호의 경우 숫자로 채우면 010이 10이 된다) 

자동채우기 패턴을 짤 때 '년' '월' '일' 접미어를 붙이거나 

숫자 앞에 '를 붙여주면 된다. 근데 자동채우기 과정에서 12월이 2월 된 게 함정... 앞부분을 잘랐나본데...?

월을 두자리로 써주니까 제대로 됐다. 코딩하면서 깨닫는거지만 여러분. 컴퓨터는 생각보다 멍청합니다... 패턴 잘 짜십쇼...