✔굿모닝 IT ✔

목차 (작성 예정)

(1) 100% 만족할 파이썬 엑셀 사무 자동화, 회사에서 안 된다면?

(2) 엑셀 보안 한 방에 뚫기

(3) 시간 50배 단축, 실무 엑셀 함수 구현 (vlookup, index match 등)

(4) 실무 엑셀 함수 응용

(5) 엑셀 실무용 유용한 함수 및 기능들 파이썬으로 해결

(6) 언제까지 수작업할래? 크롤링과 사진 자동으로 캡쳐, 스샷

회사내 보안이 심하지 않으면 목차 1, 2번은 아마 안 보셨을거라 생각합니다. (운이 좋은 경우) 3편에서는 실무에서 TOP3 안에 드는 엑셀 함수 VLOOKUP 사용법, 유용한 함수들을 파이썬으로 구현하는 방법에 대해서 소개하고자 합니다. 엑셀을 제대로 사용해보지 않은 신입들도 브이룩업?이라고 많이들 들어보셨을텐데 실무에서 엄청 사용하는 것 같습니다.

솔직히 제목 어그로를 어떻게 끌까 하다가, 파이썬 엑셀 사무 자동화라는 타이틀은 빼버리고 필요한 키워드들만 넣었습니다. 단순 조회수를 위해서가 아닌, 저처럼 효율충들을 위한 글입니다. 한 번 사용해보세요 제발...

네이버에서 이런 캐릭터와 함께 오늘은 ~하는 방법 알아볼까요? 하고 특별한 정보도 없이, '그냥 그렇다고 하네요~' 로 끝나는 글들을 혐오해서 유용하지 않으면 욕하셔도 됩니다. 저도 사수 분이 엑셀을 VLOOKUP 사용법과 데이터 전처리하는 방법들을 알려주셨는데, 수작업이 너무 심하고... 데이터가 많다 보니 VLOOKUP이나 다중 조건 함수를 처리하는데 시간이 너무 오래 걸렸습니다. 결국 답답해서 파이썬으로 해결했습니다.

서론이 길었는데 엑셀에서 함수를 처리하는데 멀뚱멀뚱할 동안, 파이썬은 작업을 완료할만큼 속도도 빠르고 시간도 단축된다는 것.

데이터가 적으면 VLOOKUP 함수를 사용하면 얼마 안 걸린다. 하지만 조건이 하나 둘씩 붙어서 다중 조건이 되고, 데이터가 몇 천개 ~ 몇 만개 정도 되면 시간이 기하급수적으로 늘어난다. 다중 조건 INDEX MATCH 조합을 사용했을 때 컴퓨터가 느려서인지 시간이 정말 오래 걸렸다. 하지만 파이썬에서는 다중 조건을 여러번 사용했음에도 몇분도 안 돼서 작업이 완료 됐다.

키워드 : #다중조건, #여러시트, #여러값가져오기

VLOOKUP

이미 아시겠지만 VLOOKUP은 (비교값, 비교대상 범위, 결과를 가져올 열 번호, 옵션)을 활용하지만, 기준값의 우측 데이터만 가져올 수 있다. 하지만 좌측 데이터를 가져오려면 #N/A가 뜨면서 오류가 난다. 다중조건을 사용하기 쉽지 않아서, 많은 분들이 INDEX MATCH를 사용한다. 다른 시트에서 값을 찾으려면 범위 앞에 시트명!을 붙인다. EX) =VLOOKUP($A2, 시트명!$A:$C, 2, FALSE) 

INDEX MATCH

INDEX 함수를 사용하여 특정 위치에 해당하는 값을 가져오고, MATCH를 사용해서 기준값이 어디에 위치하는지 정보를 얻을 수 있다. 실무에서 이 두 함수를 조합하는 것으로 보인다. 대부분 옵션은 0이나 FALSE가 들어가는데 정확히 일치한다는 뜻으로 쓰인다. 

-INDEX (검색범위, 행번호, 열번호)

-MATCH (찾는 값, 검색범위, 옵션) 

-MATCH (1, (첫번째 조건)*(두번째 조건), 옵션)   

ㄴ 조건이 충족하면 1(TRUE) 아니면 0(FALSE), 곱해서 1이 되는 조건을 찾는다로 이해하시면 됩니다

*파이썬으로 진행되기 때문에 파이썬(3.7기준), 주피터노트북, pandas 모듈 필수 설치 (csv로 진행)

이걸 어떻게 설명을 할까 생각하다가 직접 보여주는게 좋을거 같아서, 임시로 데이터를 만들었습니다. 오해는 말아주세요. 그리고  파이썬에서 VLOOKUP과 INDEX MATCH 사용법이 비슷해서 INDEX MATCH 기준으로 설명드리겠습니다. 

매체별 노출수에 따른 비용을 구하려고 할 때 엑셀 함수로는 =INDEX($J:$J,MATCH(1,($H:$H=$A2)*($I:$I=D2),0),1) 이런 식으로 구하면 별 문제 없이 5000, 7000, 30000, 11000이 입력이 될 것이다. 그렇다면 이것을 어떻게 파이썬으로 구현할까?

1. 파일 불러오기

pandas 모듈을 불러옵니다. 직접 파일의 위치를 찾아서 불러와도 되고, 편하게 원하는 부분만 드래그 후 복사하는 두번째 방법대로 하시면 됩니다. (첫번째 행이 컬럼명이 됩니다 / df는 DataFrame의 약자, 다른 값으로 해도 됩니다)

엑셀 시트를 나눠서 '매체~비용' / '매체명~가격'으로 하셔도 되는데 우선은 시트를 나누지 않고 설명드리겠습니다.

2. 원하는 부분 가져오기

=INDEX($J:$J,MATCH(1,($H:$H=$A2)*($I:$I=D2),0),1)

매칭을 시키자면, 우리가 필요로하는 A2의 값은 '매체명'컬럼에 0번째 인덱스인 '페이스북'을 facebook/ D2의 값은 '노출수'컬럼에 0번째 인덱스인 '100'을 exposure라고 칭하겠다는 소리입니다. 

안쪽에 있는 df들은 MATCH의 조건들이라고 생각하시면 될 것 같고, ['가격']은 $J :$J라고 생각하시면 됩니다. df[( 조건1 ) & ( 조건2 ) ][ $J:$J ]으로 이해하면 편할 듯합니다. MATCH에서 $H:$H=$A2 이 부분은 df['매체명'] == facebook 입니다. H컬럼('매체명')에서 facebook인 애를 찾아줘. $I:$I=D2 이 부분은 df['매체명'] == exposure를 뜻합니다. I컬럼('노출수')에서 exposure를 찾아줘.

그리고 마지막 '.values[0]'은 구한 값을 가져오는 것입니다. (중복값 없이 값이 유일할 때 첫번째 값)

어느정도 이해가 되시나요? 페이스북 체류시간을 구하는 VLOOKUP 함수를 구현한다고 하면 어떻게 하면 될까요? 

df[ df['이름'] ==  facebook ]['체류 시간']      =>      df [  ] 안에 조건을 하나만 넣으면 됩니다. 

3. 값 대입하기

방금 구한 값을 facebook_price라고 칭하고, '비용' 컬럼 0번째 인덱스에 집어넣겠다는 것입니다. 그런데 facebook_price 끝 부분 '.vlaues[0]'은 유의하셔서 보셔야 합니다. 만약에 매체가 페이스북이고 노출 400인 값이 들어왔을 때 비용을 구할 수 없기 때문에 #N/A 값이 생기는 것처럼 파이썬에서도 에러가 납니다.  

그렇다면 이 에러를 어떻게 잡을 수 있을까요?

다음 편에 이어서 작성하겠습니다. 원래 여러 내용을 같이 작성하려고 하였으나, 이 게시물을 읽으실 분들은 아직 파이썬이 능숙하지 않으실 것 같아서 우선 위에 내용부터 이해하면 좋겠네요! 

정리

VLOOKUP => df [ df['열 이름'] == A2] ['구하고자 하는 값이 있는 열 이름'].values[0]

INDEX/MATCH = >  df [ ( df['열 이름1'] == A2 ) & ( df['열 이름2'] == B2 ) ] ['구하고자 하는 값이 있는 열 이름'].values[0]

조건이 추가 되면 df [  (기존 조건)  &  df['열 이름3'] == C3   ] 안에 &와 함께 조건을 추가하면 됩니다.

다음 글

- 반복해서 값을 적용하는 방법 (이걸 일일이 다 칠 수 없으니...)

- #N/A 값이 나올 때 예외 처리

- 다른 시트에서 값 확인

목차 (작성 예정)

(1) 100% 만족할 파이썬 엑셀 사무 자동화, 회사에서 안 된다면?

(2) 엑셀 보안 한 방에 뚫기

(3) 시간 50배 단축, 실무 엑셀 함수 구현 (vlookup, index match 등)

(4) 실무 엑셀 함수 응용

(5) 엑셀 실무용 유용한 함수 및 기능들 파이썬으로 해결

(6) 언제까지 수작업할래? 크롤링과 사진 자동으로 캡쳐, 스샷

전 글에서 pip install 문제 때문에 ipykernel이 설치가 안 돼서 print(1)도 되지 않았다. 이후 문제를 해결하고 1이 출력 됐을 때의 기쁨은 엄청났다. 하지만 그 기쁨도 잠시뿐. 행복한 마음으로 csv 데이터 파일을 열었더니 오류가 이렇게나 길게 나왔다.

문제는 UnicodeDecodeError: 'utf-8' codec can't decode byte 0x9b in position 32: invalid start byte 라고 한다.  codec 문제라고 하는데, 예전에도 이런 문제와 부딪혀 봐서 금방 해결할거라 생각했다. 

encodig = 'utf-8' / 'euc-kr' / 'cp949' / 'utf-8-sig' 이 중에 한 놈이라도 걸려라 했는데, 한 명도 걸리지 않았다. 그 어떤 것도 되지 않아서.. csv 파일이 문제라고 생각해서 xlsx 확장자를 열어봤는데

ValueError: Excel file format cannot be determined, you must specify an engine manually. 라는 에러 메시지가 출력됐다. 그래서 또 열심히 구글링을 하기 시작했는데 도움이 되지 않았다. 그래도 참고하실 분은 봐주세요.

열심히 찾아봤는데 해결책은 의외로 간단했다.

openpyxl가 아닌,  xlwings을 설치하면 된다.

openpyxl 같은 경우 시트별로 불러와서 작업이 가능하지만, 애초에 회사 사내망 때문에 보안이 걸린 엑셀의 경우는 xlwings로만 불러 올 수 있었다. 하지만 xlwings를 사용하는 사람들이 많이 없어서 참고 자료를 찾기 어려워서 다른 방법을 선택했다.

자료는 회사와 관련이 없는 자료다. 이런 식으로 작업을 하면 바로 엑셀이 실행돼서 동시 작업이 된다. 엑셀 실행이 돼서 기뻤지만 작업을 하지 못 해서 다른 방법이 뭐가 있을까 찾아봤다.

그래도 혹시 궁금해하실 분들을 위해서 관련 링크 걸어놓습니다.

저는 xlwings 대신에 pd.read_clipboard()를 사용하였습니다. 이게 참 훌륭한 기능이라는 걸 깨달았네요. 그냥 가져오고자 하는 데이터를 드래그해서 복사하시면 dataframe이 만들어집니다. 자동으로 행, 열도 생성되고 이거 아니었으면 자동화는 힘들었을거 같네요 ㅠㅠ

그리고 이걸로 긁어와서 만든 파일의 경우 세션(?)이 끊기기 전까지는 csv로 불러서 읽을 수 있습니다! 다만 csv 파일을 열어서 수정을 하게 되면 다시 못 쓴다는 치명적인 단점이... 그래도 수작업을 할 시간에 코드로 자동화를 한다면 훨씬 더 편하니까 이렇게 삥삥 돌아서 갑니다. 

다 같이 화이팅 합시다!

목차 (작성 예정)

(1) 100% 만족할 파이썬 엑셀 사무 자동화, 회사에서 안 된다면?

(2) 엑셀 보안 한 방에 뚫기

(3) 시간 50배 단축, 실무 엑셀 함수 구현 (vlookup, index match 등)

(4) 실무 엑셀 함수 응용

(5) 엑셀 실무용 유용한 함수 및 기능들 파이썬으로 해결

(6) 언제까지 수작업할래? 크롤링과 사진 자동으로 캡쳐, 스샷

카테고리 분류 작업을 포함한 수작업이  많아서, 파이썬 응용AI 교육과정에서 900시간 배웠던 파이썬을 써보기로 했다. 우선 보안이 훌륭한 회사에서는 사내망에서 접속을 차단한 사이트들이 많다. Visual Studio Code(비쥬얼 스튜디오 코드)를 다른 팀에서 쓰고 있어서 vscode는 괜찮다는 것을 알았다. 그리고 다행히 python.org 는 접속이 가능했지만, 아나콘다(anaconda)는 접속이 차단 됐다. 

우선 파이썬을 설치하고 비쥬얼 스튜디오 코드를 설치했다. 확장 기능들을 추천하자면

-pyhthon (기본)

-jupyter (기본)

-Korean(사용법) Language Pack for Visual Studio Code (기본)

-Visual Studio IntelliCode (feature 들을 추천해줌)

-Python Indent(들여쓰기 할 때 위치 구분)

-Python Docstring Generator

기타 필요 기능들은 검색

아나콘다로 모듈들을 설치하면 더 편리한데, 그래도 pip install로 설치가 가능하니 문제가 없겠거니 생각했다. 그런데 파이썬을 설치하고 vscode까지 설치를 했는데 ipykernel이 설치가 되지 않았다. juptyer notebook을 활용하려고 했으나 실패했다.

이런 느낌의 오류와 함께 ipykernel이 없다는 식의 경고창이 떴다. 그래서 어떤거 문제일까 열심히 찾았는데 답이 없었다.

검색을 해보니 보안 인프라가 갖추어져 있기 때문에, pip 같은 패키지 매니저로 제대로 작동이 안 된다는 느낌? 그래서 프록시 서버를 무조건 신뢰한다는 옵션을 넣어서 설치하면 된다고 하는데.. 만약 위 링크들에서 해결이 되면 다행이고 안 되면 다른 방법을 소개해드리려고 한다.

우선 이 과정을 하기 전에 회사 IT팀과 연락을 필수로 해야한다. 원격으로 어떤 문제가 있는지 확인을 해봤는데, 목적지 IP 대역이 너무 많아서 정확한 목적지 IP대역 확인이 불가능하다는 결론이 나왔다. 목적지 IP/port를 알 수 있다면 방화벽 신청을 하면 되지만 이건 불가능했다.

그래서 임시방편으로 테더링을 추천해주셔서 그렇게 했다. 안 되면 핫스팟으로 pip install을 해보면 된다. 혼자서 파이썬을 설치하려고 하니.. 기본 구축이 왜 이렇게 어렵고, 에러가 많이 나지? 좌절했는데 알고보니 회사 프록시 서버 문제여서 황당했다. 이후에는 별다른 문제 없이 모듈들이 잘 설치 됐고 작업을 진행할 수 있었다.

사내망 막힌 사이트 볼 수 있는(뚫을 수 있는) 팁을 드리자면, 구글에서 사이트 주소 옆에 거꾸로된 삼각형을 클릭하여 저장된 페이지를 클릭한다. velog.io 사이트나 간혹 아예 차단이 된 사이트는 열리지 않는다. 그래도 코드를 혼자 짜다보면 무한 에러가 나기 때문에 구글에서 이렇게라도 검색할 수 있어서 다행이다 ㅠㅠ

하지만 더 큰 문제가 있었으니... 파이썬에서 엑셀, csv 파일들을 읽을 수 없었다. 진짜 산 넘어 산

다음 편에 계속