336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

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

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

(4) 실무 엑셀 함수 응용

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

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


https://0goodmorning.tistory.com/72

지난 편에 이어서 작성하겠습니다. 실무에서 자주 활용되는 엑셀 함수 VLOOKUP, INDEX/MATCH 기본적인 구현 방법에 대해서는 전 글을 확인해주세요^^

 

 

 

1
2
3
4
5
6
7
8
9
10
11
import pandas as pd     # pandas가 없으면 터미널에서 pip install pandas
 
# df =  pd.read_csv('위치/파일명.csv', encoding='utf-8-sig') # 직접 불러오기
df =  pd.read_clipboard(keep_default_na=False, sep="\t"# 복사해서 불러오기
 
for i in range(4) : #시트를 분리하지 않았기 때문에 매체 4개 값만
    col_A = df['매체'][i]
    col_D = df['노출'][i]
    
    col_E = df[ (df['매체명'== col_A) & (df['노출수'== col_D) ]['가격'].values[0]
    df['비용'][i] = col_E
cs

저번처럼 모듈을 불러오고 반복문을 사용합니다. 지난번에는 facebook, exposure라고 변수명을 지정했는데, 이번에는 간편하게 col_A, col_D 라고 지정하겠습니다. 지난 내용을 단지 for 문 안에 집어넣은 것이기 때문에 어려움은 없지만, 파이썬을 아예 처음 접하신 분들을 위해서 잠깐 설명을 하자면

 

 

 

값이 4개 밖에 되지 않아서 직접 쳐도 된다고 생각하지만, 저희가 다루는 데이터 양이 많습니다. 그래서 이를 반복문으로 돌리는 것입니다. for문의 기본 구조를 보자면,

 

for [변수] in [문자열, 리스트, 튜플] :

    [수행부분]

 

이런 식으로 구성이 되어 있는데, [문자열, 리스트, 튜플] 등을 순서대로 돌면서 [변수]에 넣어주게 됩니다. range(4)를 할 경우에 [변수]에 0,1,2,3 까지를 넣어주게 됩니다. range의 경우 0부터 시작해서 range 안에 숫자-1 까지의 숫자를 사용합니다.

 

col_A = df['매체'][i] 는 for문을 돌면서 변수가 0 일 때 페이스북, 1 일 때 구글, 2 일 때 네이버... 이런 식으로 변경이 되는 것입니다. 몇 번 사용하시다 보면 자연스레 익히게 됩니다. 어떤 식으로 돌아가는지만 생각하시면 될 것 같네요.

 

 

 

만약 페이스북의 노출이 400이 되면 어떻게 될까요? 엑셀에서도 #N/A 값이라고 하면서 오류가 뜹니다. 파이썬에서도 위와 같은 코드로 진행할 경우 마찬가지로 오류가 뜨게 됩니다.

 

 

 

이렇게 오류가 뜨게 되는 이유는 매체명이 페이스북이면서, 노출수가 400인 케이스가 존재하지 않기 때문입니다. 그런데 .values[0]으로 값을 가져오라고 해서 index 0 is out of bounds가 뜨게 되는 것입니다. 이 오류를 해결하기 위해서는 간단하게 한 줄만 추가하면 됩니다. 

 

 

 

1
2
3
4
5
6
7
for i in range(4) : #시트를 분리하지 않았기 때문에 매체 4개 값만
    col_A = df['매체'][i]
    col_D = df['노출'][i]  
    col_E = df[ (df['매체명'== col_A) & (df['노출수'== int(col_D)) ]['가격']
    
    if len(col_E) : #존재하는지 확인
        df['비용'][i] = col_E.values[0]
cs

len() 함수는 문자열의 길이를 구하는 함수인데, 아무것도 존재하지 않을 때 0 이 된다. 그리고 파이썬에서 0은 False로 인식하기 때문에, len(col_E)가 0이 되면 if 문이 실행이 되지 않는다. 그렇기 때문에 페이스북의 비용은 빈칸이 된 것이다. 그렇다면 페이스북 비용에 다른 값을 넣고 싶다면 어떻게 해야할까?

 

 

 

1
2
3
4
5
6
7
8
9
10
for i in range(4) : #시트를 분리하지 않았기 때문에 매체 4개 값만
    col_A = df['매체'][i]
    col_D = df['노출'][i]  
    col_E = df[ (df['매체명'== col_A) & (df['노출수'== col_D) ]['가격']
    
    if len(col_E) :
        df['비용'][i] = col_E.values[0]
    
    else : 
        df['비용'][i] = '비용 확인바람'
cs

else 구문을 활용하여 쉽게 자신이 원하는 문구를 집어넣을 수 있다. 파이썬을 많이 이용하신 분들에게는 완전 쌩기초기 때문에 그냥 웃으면서 넘기시면 됩니다. 이제 어느정도 활용을 해봤으니, 다른 시트에서 값을 가져오는 방법을 소개하고자 합니다.

 

 

 

파일을 불러오는 방식은 처음과 동일합니다. 다만 차이는 데이터를 한 번에 불러오지 않고, 나눠서 불러오는 것입니다. df를 쓰려다가 이해하기 쉽게 sheet1과 sheet2로 데이터를 읽었습니다. 

 

 

 

1
2
3
4
5
6
7
8
9
10
for i in range(len(sheet1)) : #시트를 분리했기 때문에 sheet1의 길이만큼 len()
    col_A = sheet1['매체'][i]
    col_D = sheet1['노출'][i]  
    col_E = sheet2[ (sheet2['매체명'== col_A) & (sheet2['노출수'== int(col_D)) ]['가격']
    
    if len(col_E) :
        sheet1['비용'][i] = col_E.values[0]
    
    else : 
        sheet1['비용'][i] = '비용 확인바람'
cs

이전 코드와 달라진 부분이 뭘까요?

 

df에서 sheet1으로 바뀐 부분과 어떤건 sheet2로 바뀌었습니다. 차이가 무엇일까 살펴보면, 우리가 비교하려는 값은 sheet1에서 뽑아줍니다. 그리고 비교하려는 값으로 얻는 것은 sheet2에서 찾기 때문에 col_E (원하는 것 = 원하는 매체 노출수에 따른 '가격')는 sheet2에서 조건을 걸게 된 것입니다.

 

그리고 우리는 sheet1 '비용' 컬럼을 채워야하기 때문에 sheet1['비용'][i]에 sheet2에서 받아온 '가격'을 입력하게 됩니다. 애초에 두 시트의 컬럼명이 '가격'이었으면 덜 헷갈렸을 수도 있는데, 어떤 부분을 체크해야하는지 알려드리기 위해서 일부러 다르게 사용했습니다. 코드는 작업하기 편하게 사용하시면 됩니다.

 

데이터가 몇개 되지 않으면 INDEX/MATCH 함수나 VLOOKUP 함수를 엑셀에서 돌려도 시간이 얼마 안 걸립니다. 하지만 데이터가 많아지면 많아질수록 웬만한 사무용 컴퓨터에서는 엑셀이 버벅입니다. 조금이라도 도움이 되셨으면 좋겠고 다음에는 엑셀을 파이썬으로 구현할 때 사용했던 기능들에 대해서 소개하겠습니다.

 

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

(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. 파일 불러오기

1
2
3
4
import pandas as pd     # pandas가 없으면 터미널에서 pip install pandas
 
df =  pd.read_csv('위치/파일명.csv', encoding='utf-8-sig'# 직접 불러오기
df =  pd.read_clipboard(keep_default_na=False, sep="\t"# 복사해서 불러오기
cs

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

 

 

 

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

 

 

 

2. 원하는 부분 가져오기

1
2
3
4
facebook = df['매체'][0]
exposure = df['노출'][0]
facebook, exposure
df[ (df['매체명'] == facebook) & (df['노출수'] == exposure) ]['가격'].values[0]
cs

 

=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. 값 대입하기

1
2
facebook_price = df[ (df['매체명'] == facebook) & (df['노출수'] == exposure) ]['가격'].values[0]
df['비용'][0] = facebook_price
cs

 

방금 구한 값을 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 값이 나올 때 예외 처리

- 다른 시트에서 값 확인

 

+ Recent posts