엑셀 자료정리할 때 알면 은근히 유용한 함수

https://www.dmitory.com/work/180621888

저번에 엑셀 자료정리하는 법 반응이 좋아서

평소 내가 자주쓰는 함수들도 올려봐

엑셀의 고급함수, 피벗, VBA같은 기능이 아니더라도

쉬운 함수와 요령(꼼수... ㅎㅎ)만으로도 종종 해결이 되는 일도 많거든!

sum / if 이런 기초 함수는 제외할게!

그리고 수식에 $ 표시 붙으면 절대값 되어서 수식 끌어서 복사하고 이럴 때도 변경 안 되는 건 다들 알거라 믿엉!

아, 스펠링은 미묘하게 틀릴 수 있어ㅋㅋㅋ;;;

이미 엑셀의 자동완성 기능에 익숙해져버린 고인물임을 감안해죠ㅠㅠ

위 기본데이터를 기본으로 설명할게!

ps / 내 기준 엑셀에서 셀 병합해도 예쁘고 무방한 건

저 상단 타이틀같은 것뿐임ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

데이터값에 1도 영향 없는 거...

1. vlookup

데이터값 찾을 때 이 함수처럼 효도하는 함수가 있을까 싶다

내 최애함수야 ㅋㅋㅋ

앞자리가 v인것도 사랑스러워보여... ㅋㅋㅋㅋㅋ (미친사람 아니에용....)

가장 왼쪽에 있는 데이터 열 기준으로 조건과 일치가는 데이터값을 찾는 함수야

예를 들어 토리가 휴가 기간에 자리를 비운 동안 신입삐약이가 어설픈 전화메모를 남긴거야

혹은 여러건의 데이터를 조합해야할 때!

혹은 어느 회사인지는 모르겠지만 회사명만 있고

담당자 연락처를 건건이 찾아야하는 형태일 때 유용해!

함수 쓰는 건 이렇게 생겼어

(주)강아지사의 담당자 직통번호를 찾을 땐 이런 형태가 되는거지

(주)고양이사의 담당자 번호도 이렇게 찾을 수 있어!

함수 제일 끝의 FALSE는 '정확히 일치'할 때만 값을 반영하는거고

사람이 살다보면 회사명 앞에 (주) 이런 거 제낄 때도 있잖아?

비슷한 사명이 거의 없다는 가정하에 가장 끝자리를 'TRUE'로 설정하면

100% 정확하게 일치하지 않아도 비슷한 값으로 가져와줘! 

나는 데이터 몇만개 단위로 볼 때가 많은데

회사 업무 특성상 vlookup 함수를 정말 제일제일 많이 쓰는 거 같아 ㅋㅋㅋ

2. replace

이건 전에 쓴 글에서도 아주 짧게 언급했는데

* 와 같은 특수문자를 다른 특수문자, 혹은 아예 없애고 싶을 때

일반적인 바꾸기 기능으로 안 된다는 걸 알게 될거야... ㄱ- ㅋㅋㅋ

이 비고란 앞에 *표시가 붙어있는데

갑자기 토리 마음이 바뀌어서 ##으로 바꾸고 싶어

하지만 *표는 특이성질이 있는 특수문자야 (되도록 최초 데이터정리할 때부터 안 쓰는 거 추천함)

다들 엑셀 켜서 테스트해보면 알겠지만

* 이 특수문자를 다른 문자로 치환하려고 하면....

셀이 공백이 되는 대참사를 보게 될거야 ㅋㅋㅋ

몇 개 안 되면 모르겠지만 일일이 더블클릭해서 여러 데이터를 다 바꾸려면 빡치겠지? ㅠㅠ

이런 경우 그럼 어떡하냐!

우리 엑셀씨는 저보다 똑똑하기 때문에 이런 경우도 대비한 함수가 있습니다

위에처럼 Replace 함수를 쓰면 돼

아예 특수문자를 없애고 싶다면 "##" 값 대신 "" 이라고 기재하면 돼

*표 대신 한글문서처럼 - 표시 넣는 경우도 많은데

앞에 ' 이거 붙이고 - 쓰면 -값부터 출력되니까 참고해줭

3. left, right, mid

이거 진짜 단순 함수인데

전화번호 오류같은 거 잡아낼 때 정말 은근히 유용한 함수야

left와 right 함수는 셀 내의 몇번째 텍스트를 추출해서 보여줘

left(C5, 3)  / right(C5, 3)

이런 식으로 쓰는데

전화번호의 경우 이렇게 되면 앞자리가 010 인지 011인지 추출할 수 있어

그 이후에는 기본 필터기능으로 010이 아닌 다른 데이터만 뽑아서 관리할 수 있겠지?

mid(C5,3,4)

mid 함수는 셀 내의 텍스트 가운데값을 가져오는거야

C5셀 안에 3번째 글자부터 시작해서 4개째 글자를 반영하는거야

휴대폰이면 가운데 자리 숫자 4개가 쫜 하고 나옴!

4. len

이건 특정 셀 안에 글자가 몇개 있냐 따지는거야

함수 자체도 단순해

len(c5) 이게 끝임ㅋㅋㅋ

위 데이터로 보면 데이터값은 11이 나올거야

나는 left 함수랑 len 함수 이용해서 오류 번호 정리할 때 자주 써

일단 len 함수로 1차 거르고, 2차로 left 함수로 거르면 완벽하더라고

(역순으로 사용하기도 함)

번호가 아니더라도 글자수 규칙이 있는 데이터에 응용해서 활용하기 좋아

5. 각종 만능 꼼수 &

A1와 B1라는 셀의 내용을 서로 합칠 때 conce~ 뭐시기 함수를 쓰라고 배울거야

솔직히 이거는 함수 쓸때부터 구찮음...

일단 영타가 많으면 자신감이 중동으로 사라지는 나토리는 저 함수가 너무 싫었어

그래서 정말 단순하게 생각함ㅋㅋㅋ

그냥 =A1&B1 해 ㅋㅋㅋ

가운데 꼭 공백이 필요하면

=A1&" "&B1 이렇게 단순하면 쓰는 게 더 편하다

그리고 데이터값 전부 만들면 그대로 복사해서 값으로 붙여넣기 하면 갓벽....

그리고 휴대폰 번호 넣을 때 하이픈 없이 작성해야하는데 앞에 0이면 자꾸 10으로 시작하잖아

그 때 텍스트 모드로 작성해도 되는데

꼭 일반 모드를 유지하고 싶으면

다른 셀에 0 하나 작성해놔

0이 적힌 셀에 절대값 씌워놓고 &로 연결하면 됨ㅋㅋㅋ

그리고 역시 그대로 데이터 복사해서 값으로 붙여두기 해두면 0이 유지됨!

되게 간단하지만 의외로 이 0 유지 못하는 사람들 있어서 혹시나 싶어 남겨보앙

&가 어지간한 함수보다 더 편하다 ㅋㅋㅋ

- - - - - - - - - - - - - - - - - - -

엑셀 자료 정리가 묘하게 노가다 삽질인 게 많거든

그래도 곰곰히 생각해보면 어떻게든 꼼수는 나오더라

데이터 안에서 중복값 찾는거랑 (중복값 지우기 말고)

엑셀알못이 작성한 셀병합 당한 데이터 최대한 건져내는 방법은 다음 기회에 작성해볼게!

하지만 이것도 최대한 일정 규칙이 있어야 가능하다는 조건이 붙는다는 게 함정 ㅠㅠ

혹시라도 일하면서 그간의 엑셀 활용하면서 궁금한 거나 또 다른 편법 아는 토리들

서로 댓글로 공유해서 신입들과 엑셀알못들에게 광명찾아주자 +_+ ㅋㅋㅋ

나도 배워갈게!