Enjoy Life~~

회사 생활을 하면 웬만한 정보는 휴대폰에 다 들어가 있다고 생각하고, 또 그렇게 해야 관리가 용이하다. 

(개인정보 유출?  난 그런거 포기했다. 이미 구글은 내 모든 것을 알고 있다고 생각한다.)

그래서 연락처도 휴대폰의 연락처를 기본으로 해서 아웃룩도 동일하게 관리해야 한다.

리멤버나 명함입력 앱으로 연락처 입력하는 게 가장 간편하잖아?

그런데 주위를 보면, 휴대폰 따로, 아웃룩 따로 입력하는 거 같다. 

이전에는 실시간 동기화 프로그램도 있었는데, 이제는 없어졌다. 유료 app은 있나본데, 못해봤다.

그래서 나는 정기적으로 구글 연락처를 아웃룩으로 옮긴다. 

이 방법도 한참 헤맸다. 인터넷에는 아웃룩에서 CSV 파일을 읽으면 된다는데, 해봐라. 엉망으로 입력될거다. 

포인트는, PC 아웃룩이 아니라, web 아웃룩에서 CSV 파일을 읽어야 된다. 그러면 web 아웃룩과 PC 아웃룩이 동기화 되면서, 자동으로 주소록이 옮겨진다. 

두번째 포인트, 기존 아웃룩의 연락처를 다 삭제하고, CSV 파일을 읽어야 되는데, web 아웃룩에서는 연락처 전체 삭제가 안된다. 연락처 전체 삭제는 PC 아웃룩에서 한다. 

그러면, 연락처 옮기기 시작!

1. 구글 연락처에서 '내보내기' ,  형식은, 'outlook 전용 CSV'

2. PC 아웃룩에서, 연락처를 전체 선택하고 삭제. 그러면 web 아웃룩의 연락처가 동기화 되면서 모두 지워진다.

    새로고침 해서 아래의 화면이 되면 '연락처 가져오기 선택' 해서 위에서 저장된 파일을 선택. 

3. 연락처가 가져와지고, PC 아웃룩에서 연락처가 동기화 되는 상쾌한 경험을 할 수 있다.

내가 지금 해보니, 5분 정도 걸린 것 같은데, 1년에 두 번 정도만 휴대폰의 주소록을 옮겨주면 충분한 것 같다. 

인터넷 검색하면 PC 아웃룩에서 CSV 파일을 읽으라고 하는데, 다른 사람들은 잘 되나? 난 안되던데??

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

제목이 더 이해가 안되는데, 아래와 같이 데이터 형태를 바꾸는 문제이다. 

'월'을 행으로 빼서 가로로 주욱 늘어져있는 데이터를 세로형으로 변경했다. 

파워쿼리가 데이터 형태를 변경하는데 정말 요긴하다.

작업 순서는, 파워쿼리에서 데이터를 피벗해제 → '월'과 항목을 분리  → '월' 이외의 항목을 다시 피벗으로 전환

1. 데이터를 선택하고, ctrl+T로 테이블 형식으로 변경 (모든 데이터들은 테이블 형식으로 관리하는게 가시성부터 여러가지로 유리하다.)

2.  데이터 → 테이블/범위에서,  클릭해서 파워피벗 실행

3. 실행된 파워쿼리 편집기에서, 열 피벗을 해제하려는 데이터('Jan Qty'에서 끝까지)를 선택하고, '변환' → '열 피벗 해제' 실행

4. 이제 데이터가 세로로 준비되었고, 여기서 '월'과 항목을 분리한다. 

    '변환' → '열 분할' → '구분 기호 기준' 선택

5. '월'과 항목 사이에 공백이 공통적으로 있으니, 공백을 두고 열을 나누면 되겠다. 

6. '월'을 떼어냈으니, 이제 나머지 항목들을 다시 피벗으로 전환해서 열로 돌려준다. 

    '특성2'열 선택 → '변환' → '피벗 열' 선택 → 피벗 열의 값을 '값'으로 선택하고 '확인'

7. 원하는 형태의 데이터가 나왔으니 빠져나간다.  '홈' → '닫기 및 로드'

8. 다만, '월' 값들이 제대로 정렬이 안되어 있으니, 정렬을 해야겠는데, '사용자 정렬'이 필요하다. 

    첫 번째 정렬 조건은, P/N이나 customer로 두고, 두 번째 정렬에서 '사용자 지정 목록' 선택하고, 월 목록을 선택해서 정렬해서 완료!!  

방법을 모르면? 야근~~

샘플 데이터 파일 첨부

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

아래와 같은 (숫자이지만 스페이스들이 엉켜서 숫자로 처리할 수 없는) 자료를 처리해야 하는 경우가 꼭 생긴다.

저 숫자들을 가지고 뭔가 해야겠는데, 문제는 저게 숫자로 보이지만 숫자 앞뒤에 스페이스(공백)이 뒤섞여 있어서, 엑셀에서는 텍스트(문자열)로 인식되는 자료다. 

인터넷에서 '엑셀에서 숫자에 있는 공백 없애기' 어쩌고 해서 검색해보면 이런 방법들이 나온다.

1. trim 함수를 써서 공백 제거 

2. substitue 함수를 써서 공백 제거

3. ctrl + H (찾아 바꾸기)로 공백 제거

결론적으로 위 방법들은 아니다. 위 방법들은 숫자에 공백이 규칙적으로 붙어 있을 때 쓸 수 있는 방법들이다. 숫자 앞 뒤에 이쁘게 한 칸 씩만 공백이 있거나, 일정하게 두 칸 씩 붙어 있다거나...  

하지만 실무에서 맞닥뜨리는 자료들이 그렇게 이쁘게 되어있을리가 없지. 

우리는 파워쿼리를 써서 손쉽게 공백을 없앨 수 있다. 파워쿼리에서 데이터를 선택하고, 데이터를 10진수로 바꿔주면 끝!

1. 먼저 내가 필요한 영역을 선택한다. 숫자 쪽만 선택해도 되지만, 나중에 더 편하려면 항목 부분까지 함께 선택하자.

2. 선택부분을 파워쿼리로 가져간다. 데이터 → 테이블/범위에서

3. 파워쿼리가 실행되면, 숫자 영역의 첫 열과 끝 열까지를 선택한다. 드래그는 안된다. shift키로 선택하자.

    선택하고, 변환 → 데이터 형식이 '임의'로 되어 있을텐데, '10진수'를 클릭. 

     이제 공백들은 모두 없어지고 숫자들로 변했다. 

4. 파일 → 닫기 및 로드 ,   엑셀로 돌아가면 작업 끝

5. 새로운 시트에 아래와 같은 데이터가 나타난다.

    모두 깔끔한 숫자들이다.  중간에 필요 없던 텍스트가 삭제되는 건 덤...

어렵지 않은 팁인데, 이걸 모르고 숫자들을 손으로 다시 입력하면........... 퇴근이 늦어진다.

예제파일 첨부합니다.

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

엑셀 실무에서 vlookup은 기본중에 기본인데 (요새는 xlookup으로 사용하자)

가끔씩 2개 이상의 조건에 맞는 값을 가져와야 할 때가 있다. 아래의 경우에서는, code, part number, customer, 이 3개의 조건에 맞는 값을 오른쪽 녹색 표에 가져오는 것이겠다.

뭐 사실 vlookup/xlookup으로도 해결할 수는 있다. 조건 열들을 하나로 합쳐서 그 합친 셀로 vlookup을 하면 되는데...

보다시피, 열이 늘어나고, 없는 값은 #n/a가 표시된다.

그러나, 파워피벗을 사용하게 되면, 함수 사용 없이 깔끔한 표가 완성된다.

과정은,

] 각각의 테이블을 '표'로 변환한다. 단축키 ctrl + t

] 먼저 가져올 자료인 녹색 테이블 위에서 '데이터' > '테이블 범위에서'를 눌러서  '파워쿼리'를 실행한다.

] 여기서는 할 일 없고, '파일' 탭을 눌러서 다시 닫아주는데, '닫기 및 다음으로 로드...'를 누르고, 다음 화면에서 '연결만 만들기'를 선택한다. 

  그냥 '닫기 및 로드'를 선택하면 별도의 시트가 생성되는데, 그럴 필요가 없으니...

] 이제 같은 방법으로, 기준이 되는 자료(파란색 표)에서 파워쿼리를 실행한다. ( '데이터' > '테이블 범위에서')

] 메뉴 중간에 있는 '쿼리 병합'을 누른다. 팝업 창에서 상단에는 현재의 표가 있고, 아래쪽에서는 아까 만들었던 표를 선택해서 가져온다.

  여기서 조건이 될 열들을 위 아래에서 각각 선택해주면 된다. 

  중간에 '조인 종류'라고 선택이 있는데, 기본 값이 우리가 vlookup에서 알고 있던 조건이므로 손대지 않는다.

] 이제 가져오려던 표가 몽땅 불러들여와졌다. 여기서 필요한 열만 선택한다. 나는 Revenue 2개 값만 선택했다. 

] 여기서도 조건에 맞는 값이 없던 셀들은 null이 나오는데, 상단의 '값 바꾸기'에서 0으로 바꿔준다. (이렇게 바꿔주면, 향후에 데이타가 추가되서 또 다른 null의 경우가 생기더라도 0으로 바꿔주는 놀라운 기능이다.)

] 이제 '닫기 및 로드'를 눌러주면 새로운 시트에 깔끔한 표가 완성된다.

  이후에 데이터가 추가되더라도, '모두 새로 고침' 버튼 한 번에 자료가 수정되는 행복을 느낄 수 있다.

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

제목으로 말이 이상한데... 이미 피벗테이블을 거쳐서 나온 데이터를 원래의 데이터로 돌린다는 말이다.

엑셀의 정식 표현은 '열 피벗 해제'이다.

아래와 같이 월별로 정리된 데이타를 다시 원래의 데이터로 돌린다는 의미이다.

원래 데이터는 애초에 오른쪽과 같이 만들어야 여러가지로 활용이 가능할텐데, 이미 왼쪽처럼 작성된 자료를 오른쪽처럼 회복(?) 시켜야 할 때, 엑셀의 '파워쿼리'가 손쉽게 해결해준다. 

] 먼저 원래의 데이터를 '표'로 지정해준다.  단축키 ctrl + t

] '데이터' > '테이블 범위에서'를 누르면  '파워쿼리'가 실행된다.

] 데이터의 중간중간에 null 값이 보이는데, 1월부터 6월까지 데이터 열을 전체 선택하고,  '홈' >'값 바꾸기'에서 null을 0으로 바꿔주자.  엑셀에서 '찾아 바꾸기'와 동일한 기능.

  null 값이면, 행을 열로 바꿀 때, null은 열로 바꾸지 않고 누락시킨다.

] 이제 null은 0으로 바꼈고, 아직 1월~6월이 선택되어 있을텐데, 이 부분이 행으로 바꾸고 싶은 부분이다.

 선택한 상태 그대로 두고, '변환'>'열 피벗 해제'를 누르면 우리가 기대했던 결과가 나온다.

] 이제 '홈'>'닫기 및 로드'를 선택하면, 별도의 시트에 결과 값을 얻을 수 있다. 

막상 결과를 얻고 나면, 별거 아니게 느껴지지만, 이게 실무에서 닥치면 상당히 난감한 일이다. 일일이 한 행 한 행에다가 개월 수 만큼 열을 삽입해서 값을 넣어준다??  말도 안되는 일이다.  (물론 애초에 데이터 형태를 잘 맞추면 일어나지 않을 일이다. ㅎ)

파워쿼리의 강력한 점은, 한 번 이렇게 작업을 정의해 놓으면 데이터가 추가되더라도 간단하게 처리된다는 점이다.

아래와 같이 추가 데이터를 붙여넣어도, '모두 새로 고침' 버튼 한 번에 자료가 수정된다.

9백만이었던 숫자가 데이터를 추가하고 10백만으로 고쳐지는 걸 볼 수 있다.

이번에는 자료에 수량만 있었지만, 수량과 금액이 있는 경우에는 어떻게할지 다음 포스팅에서...

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

회사에서 날짜를 요일로 바꾸기를 달력을 보면서 하다가 '이건 아니다!'라는 생각이 들었다. 

찾아보니 바로 간단한 방법이 '짠~'

날짜에 '셀 서식'을 적용해서 요일로 보이게 하는 방법이다.

먼저 셀에 일반적인 날짜를 넣어주자.

아래의 경우에는 월,일이 나눠져있어서 date함수를 써서 날짜를 입력해줬다.

 = date(년도,월,일)

여기서 날짜를 전체 선택해서 '셀 서식' 선택. (단축키 : ctrl + 1)

여기서 맨 아래 '사용자 지정'을 선택하고 '형식'에 필요한 요일 형태로 입력을 해주면 끝.

 aaa  -> 수

 aaaa -> 수요일

 ddd  -> Wed

 dddd -> Wednesday

단, 이것은 '2020-12-02'라는 값을 '수요일' 형식으로 보여주는 것이지, 

'수요일'이라는 텍스트 값이 있는 것은 아니다. 

이런 간단한 팁이 모여서 내 실력이 되고, 내 시간을 절약해준다. 

모르면 찾아보고, 찾아보는 것도 해볼 수록 잘 찾을 수 있다.

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받을 수 있습니다.

실무에서 자주 발생하는 경우다.

아래와 같이 여러 셀에서 각 표의 첫번째 행의 값을 반복해서 입력해야 할 경우가 생긴다. 

이전에는 if 함수로, 빈 셀이면 윗 셀의 값을 넣고, 빈 셀이 아니면 그대로 놔두고 어쩌고. 복잡하게 했었는데, 간단한 방법이 있었다. 아래 그림처럼...

입력할 영역을 선택하고, '홈' 탭에서 '찾기 및 선택' -> '이동옵션(S)' -> '빈 셀(K)' 선택 -> 수식 입력 ( =(첫번째 셀의 바로 윗 셀)) -> ctrl+enter (중요!!) -> 전체 선택해서 값 복사로 수식 없애고 마무리

요약해서 말하면, 빈 셀들만 선택하고, 그 빈셀들이 모두 바로 위의 셀을 참조하게 하는 방법이다. 

단축키로 하자면, 영역선택 -> ctrl + G -> alt + S -> K -> enter -> '='입력 후 방향키 위로 한 번 -> ctrl + enter 

한 번 연습해두면 나중에 업무시간 절약에 도움 될 일이 분명히 생긴다. 

비슷하게 활용하면, 여러 곳의 빈 셀에 값을 채워넣을때도 활용 할 수 있겠다.

배우는 건 금방이지만, 그 잠깐의 시간은 앞으로 많은 시간을 절약해 줄 수 있다. 

예제 파일 첨부. 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받을 수 있습니다.

실무를 하다 보면 아래와 같이 자료를 정리해야할 필요가 생긴다.

보통 왼쪽의 데이터는 ERP에서 얻어지는 자료 형태이고, 

오른쪽의 데이터는 보고서를 작성하기 위해 만들어야 하는 자료 형태이다.

언제나 반복적인 업무가 생기면, 이걸 개선할 수 없을까 고민을 해봐야 한다.

수식은 아래와 같다.

=TEXTJOIN(", ",TRUE,OFFSET($D$1,MATCH(J5,C:C,0)-1,0,COUNTIF(C:C,J5),1))

함수 설명을 하자면,

TEXTJOIN(a,b,c,d) : c~d의 텍스트들을 a의 구분자를 중간에 삽입해가면서 합친다. b는 중간에 빈 칸이 있으면 포함할까, 말까의 옵션이다.

쉽게 보면 아래와 같다.

MATCH(a,b,0) : a값을 b 범위에서 찾아서 몇 번째에 있는지 알려준다.

아래에서 D25:28범위에 '현대'가 두 번째에 있기 때문에 2값을 보여준다.

OFFSET(a,b,c,d,e) : 엑셀 중수 이상으로 올라가기 위한 함수라고 생각한다.

기준점 a에서 시작해서 b행,c열만큼 떨어진 셀부터 d행,e열만큼 범위를 잡는다.

아래에서는 B4부터 시작해서 3x1만큼 떨어진 C7에서 2x2의 범위를 가져왔다.

이제 다시 처음으로 돌아가서,

오른쪽 '품목'을 왼쪽에서 찾아서, 그 찾아진 셀부터 동일한 품목이 몇 개나 있는지 확인해서 그 숫자만큼 '고객사'의 범위를 잡고 TEXTJOIN 함수로 합쳐주면 된다.

위 식을 풀어 말하자면, 'KO1745' 품목(J5)을 C열에서 찾았더니(MATCH) 5번째 행에 있고, 그 품목이 3개 있어서(COUNTIF), 고객사들이 있는 열, $D$1부터 아래로 5 x 0 만큼 이동하고 그 셀부터 3 x 1만큼 범위로 잡아서(OFFSET), 콤마로 구분해서 합쳐줬다.(TEXTJOIN)
(설명 해놓고 보니 정말 설명 잘 한거 같다. ^O^)

예제 파일 첨부한다.

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받을 수 있습니다.