프로그래밍에 관심이 있었고 개발자로 일했었을때 나는 개발을 할때 굳이 엑셀을 쓸 일이 없었다. 그러다 개발이 아닌 업무를 맡게 되었고 엑셀을 활요해서 자료를 만들고 분석하는 것이 나의 주 업무가 되었다. 

이 글을 쓰는 이유는 아마도 어떤 사람들은 인터넷의 광고를 보고 "파이썬을 배워서 업무를 처리하는게 가치가 있을까? 시간이 아깝지 않을까?" 하며 고민을 할 것이다.

엑셀과 파이썬을 둘 다 사용할 수 있는 나는 만약에 파이썬을 배울 수 있는 여유가 있으면 배워서 사용하라고 말하고 싶다.

이유는 다음과 같다.

1. 폼을 만드는거 빼고 엑셀로 할 수 있는 것들 대부분 파이썬으로 해결 할 수 있다.

엑셀에서 포뮬러를 사용해서 하던 것들을 파이썬으로 코드를 짜서 그걸 처리 할 수 있다. 폼을 만드는 것 역시 할 수 있지만 비효율 적일 수있다.

2. 엑셀 보다 덜 불안하다.

엑셀 포뮬러를 만들어서 다른 직원에게 사용하게 해줄 경우가 있는데 엑셀을 잘 모르는 직원이 포뮬러를 잘못 건드리는 경우 실제로 큰 문제가 발생 할 수 있다. 파이썬을 제작한 프로그램을 줄 경우엔 내부의 코드는 건들 가능성이 없기 때문에 실수로 인한 문제를 방지 할 수 있다.

3. 엑셀에서 반복되는 것들을 단 몇 번의 클릭으로 처리 가능하다.

이것이 가장 큰 이유다. 엑셀에서 여러 파일들에서 자료 가공하고 가져오고 vlookup으로 합치는건 정말로 따분한 작업이다. 실제로 이걸 집중해서 하면 시간이 얼마 걸리지 않을 지라도 여러 과정을 손으로 일일이 하는건 정말 귀찮다. 한숨 몇번 쉬면서 하다보면 실제로 걸려야 할 시간보다 몇 배가 걸릴 때가 있다.  이러한 반복되는 작업을 파이썬 프로그램을 한 번 만들어 놓으면 실행파일 클릭 합칠파일들 클릭 단 몇 번의 클릭으로 귀찮은 작업을 끝내 버릴 수 있다. 

뭐 어자피 평소에 반복한거 뭐 힘드냐 생각할지 몰라도 5 스텝 정도 엑셀 작업을 하면 정신적으로 지친다.

그런데 단 클릭 두 번 정도에 이런 것들을 끝내버리는 프로그램을 파이썬을 통해 만들 수 있다.

그러면 엑셀을 쓰지 말아야 하나?

사실 엑셀을 써야 하나 파이썬을 써야하나 둘 중 하나를 무조건 골라야 한다는 것은 말도 안되는 것이다. 단순한 작업이면 오히려 엑셀이 훨씬 편하다. 반복해서 쓰는게 아닌 한 번 두번쓰는 자료를 만들때 파이썬으로 프로그램을 만드는 것은 비효율 적이다.

개인적으로는 반복이 많지 않은 경우나 어떤 결과를 얻기 위한 과정이 적을 경우는 그냥 엑셀을 사용한다. 그렇게 사용하다가 사용빈도가 높고 번거로운 작업 ex) 파일합치기, 단순 변경이 많아지면 파이썬으로 프로그램을 만든다. 

엑셀을 쓰냐 안쓰냐가 아니라 어떤 걸 사용할때 더 업무가 쉬워지냐의 문제인데, 엑셀이 만들기 쉽기 때문에 개인적으론 엑셀로 먼저 사용하다가 그게 자주 사용되고 번거로워질 경우 프로그램화를 하는 것이 효율적이라 생각된다. (처음부터 프로그램을 만들 경우, 실제로 만들고도 안쓴느 경우도 생긴다.)

결론은

각각의 장점이 있기 때문에 둘 다 적절히 사용하는게 좋다. 그리고 프로그래머가 아닌 사무직이라도 이를 위해 파이썬을 배우는 것은 가치가 있다.

얼마전 부터 기존에 하고있던 관리 업무에서 소싱영업까지 업무가 추가가 되었다. 영업에 대해는 잘 모르지만 영업관련 부서를 바로 옆에서 지켜본 결과 프로그램 없이 엑셀로 트랙킹을 하는 것은 쉽지 않았다. 

또 나 역시 제품 클레임 제품들을 트래킹해야 했는데 트래킹 프로그래밍을 만드는건 귀찮음에 미루고 미뤘다.

일단 결과 화면부터 보자

결과화면은 위와 같이 업체별로 진행상황이 나오도록 할 것이다.

일단 아주 원시적이지만 직관적으로 트래킹을 하는 경우를 보자

이방법은 쉽게 느껴질 수 있지만 이건 정말 비효율 적이다. 엑셀을 처음 하는 사람들은 아마 이렇게 할지도 모른다. (내가 그랬다..)

처음엔 쉽게 느껴질 수 있지만 나중가면 업체를 찾는거 조차 어렵고 정보가 좌우로 적혀 있어 보기가 많이 불편하다. 또한 상세정보 들이 길어지면 가독성은 더 떨어지게 된다.

그러면 이것을 어떻게 해결해야 하는가?

1. DB 형식으로 엑셀을 만든다.

날짜, 업체명, 진행상황, 상세로 칼럼을 나누었고 새로운 상황이 생길때 마다 새로운 열을 추가 시켰다.

이거만 보면 "에이 뭐야 이거 보기 더 어려워 졌잖아." 생각할 지도 모른다. 더 많은 정보가 들어갔고 입력하기 편하지만 시간상으로만 정렬이 되어있을 뿐 업체들이 섞여 있어서 한눈에 파악하기 어렵다. 

물론 이때 정렬 기능을 사용하여 이 데이타를 업체별로 정렬하여 보여지게 할 수도 있지만 그렇게하면 입력 할때마다 다시 날짜별로 정렬을 바꾸고 상황을 보고 싶을 또 정렬로 업체별로 수정해야 하는 번거로움이 있다.

2. 이 데이터를 바탕으로 피벗테이블을 만든다.

해결책은 피벗테이블을 만드는 것이다. 

1) 데이터가 지속적으로 입력되기 때문에 테이블로 만든다.

이렇게 테이블을 만들면 새로운 열을 입력할 때 마다. 자동으로 테이블의 범위가 변경된다.

2) 피벗테이블의 범위를 테이블로 선택한다.

3) 보여질 정보들을 row에다 넣는다.

 업체명 별로 분류가 되고 내부에 날짜가 있고 그 날짜에 대한 진행상황과 상세내용이 나와있지만 밑으로 주주룩 나와서 보기가 어렵다.

4) 피벗테이블 디자인 바꾸기

레포트 레이아웃에 들어가서 show in tabular form 을 클릭한다. 

단지 저 버튼을 클릭했을 뿐인데

위와 같이 보기 더욱 쉽게 보여진다.

이건 계산 관련 피봇이 아니기 때문에 grand totals 버튼을 클릭해서 안보이게 바꿀 수 있다.

5)피봇 스타일 바꾸기

이렇게 하면 업체별로 눈에 더 쉽게 들어오게 바꿀 수 있다.

3. 데이터 추가하기

이후 정보를 더 추가하게 되면

피봇테이블에 아직 데이터가 변경된게 적용 안됬는데

데이터를 리프레쉬하면

아래와 같이 추가가 된것을 볼 수 있다.

위의 방식대로 하면 영업뿐만이 아니라 재품별 클레임 상황도 쉽게 관리가 가능하다. 

+ 슬라이더 추가

아래와 쉽게 같이 업체 A, B만 선택하여 볼 수 있다.

이미 엑셀로 잘 사용하고 있는데 프로그래밍적인 기능이 필요한 경우, 아마 세가지 중 하나를 선택해야만 할 것이다. 

1. 그냥 프로그래밍적인 기능을 포기하고 엑셀에서 기본적으로 제공되는 포뮬러만 사용한다.

2. 파이썬으로 옮기고 모든 기능들을 구현한다. 혹은 추가로 필요한 기능만 하는 파이썬 스크립트를 만든다.

3. 지금까지 잘 쓰던건 나두고 필요한 프로그래밍적인 기능만 VBA로 추가한다. 

파이썬으로 다 바꾸면 나중을 위해서는 좋을것이다. 실행도 빠르고 간편하고 코드도 간단하다.

필요한 기능만 파이썬으로 만들면 엑셀과 파이썬을 둘다 관리해야하는 번거로움이 생긴다.

그래서 생각한게 간단한건 VBA로 처리하는 것이다. 인터넷을 찾아보니 회사에 파이썬이 보안에 막혀서 VBA를 쓰는 경우도 있다고 한다.

(개인적으로, 만일 복잡한 코드를 만들어야한다면 그냥 파이썬을 사용하는게 더 효율적이고 빠르고 구조적으로 설계하는게 쉬울것 같다.)

1. 에디터 실행하기

ALT + F11 를 눌러 visual basic for applications를 실행한다.

2. 구조는 다음과 같다.

VBAProject
- Microsoft Excel Objects
--sheet1
--thisWorkBook

오른쪽 화면에는 VBAProject(파일명) 하위 요소는 Microsoft Excel Objects폴더, 그 안에 sheet1와 thisWorkBook이 있다.

또한, 오른쪽 마우스를 클릭하여 UserForm, module, class module 을 추가 할 수 있다.

3. 코드 작성하기

모듈을 만든 뒤 코드를 아래와 같이 작성해 본다.

Sub Cyan()
Cells.Interior.ColorIndex = 28
End Sub

4. 간단히 메크로를 통해 실행해보기

Alt + F8를 누루거나 저 Macros 아이콘을 클릭한다.

실행을 누르면 만들어 놓은 기능이 실행이 된다. 

참고로 모듈이 아니라 sheet object에 코드를 넣어도 되는데 이 경우 아래와 같이 이름이 나온다.

5. module과 sheet object 용도의 차이

아래의 코드를 보듯이 공용으로 사용될 수 있는 기능과 세부적인 로직은 모듈로 만들어 저장하고,

sheet object는 그 sheet에만 사용하는 코드와 모듈함수, 서브실행을 한다.

Module: DataAccess:

Function GetData(strTableName As String, strCondition1 As String) As Recordset
    'Code Related to getting data from the database'
End Function

Module: PopulateSheet:

Sub PopulateASheet(wsSheet As Worksheet, rs As Recordset)
    'Code to populate a worksheet '
End Function

Sheet: Sheet1 Code:

Sub GetDataAndPopulate()
    'Sample Code'
     Dim rs As New Recordset
     Dim ws As Worksheet
     Dim strParam As String
     Set ws = ActiveSheet
     strParam = ws.Range("A1").Value

     Set rs = GetData("Orders",strParam)

     PopulateASheet ws, rs
End Sub

Sub Button1_Click()
    Call GetDataAndPopulate
End Sub

6. function과 sub의 차이.

Sub로 만든 코드는 메크로를 통해 실행할 수 있다.

function으로 만든 코드는 셀에서 실행 할 수 있으며 반환값을 가져야 한다.

7. 저장하기

그냥 엑셀파일로 저장하게되면 만들어 놓은 메크로(기능)이 사라지게 된다. 

그러니 저장할땐 *. xlsm 로 저장해야한다.

[3. 엑셀 대신 파이썬: 재고찾기 프로그램] 사용한 코드와 설명

#!/usr/bin/env python
# coding: utf-8

# In[1]:

#판다스를 불러온다.
import pandas as pd


# In[2]:

# 데이터의 모든 row를 보이게 한다. 이 설정을 안하면 생략하고 보여줌.
pd.set_option('display.max_rows', None)


# In[3]:

# 불러올 파일을 명시함
file = 'INV_CurrentStock.csv'


# In[4]:

# csv파일을 판다스의 데이타프레임으로 변환하여 반환.
df_csv_data = pd.read_csv(file)


# In[5]:

# df_new_data에 칼럼 Location과 Quantity를 삭제한 데이터프레임을 넣음
df_new_data = df_csv_data.drop(columns=['Location', 'Quantity'])


# In[6]:

# drop_duplicates()를 사용해서 중복되는 열을 삭제 후 Item, Description, Category 칼럼만 남김
df_new_data = df_new_data.drop_duplicates().filter(['Item','Description','Category'])


# In[7]:


# print(df_new_data)


# In[8]:

# 로케이션 칼럼인 Warehouse 인 row의 인덱스를 반환 
is_warehouse = df_csv_data['Location']=='Warehouse'


# In[9]:

# 로케이션 칼럼인 Display 인 row의 인덱스를 반환 
is_display = df_csv_data['Location']=='Display'


# In[10]:

# df_csv_data[is_warehouse]를 사용하여 칼럼에 웨어하우스 라고 적힌 열들을 얻어내고
# 필요한 정보인 Item 과 Quantity만 보이게 한다.
warehouse_qty = df_csv_data[is_warehouse].filter(['Item','Quantity'])


# In[11]:

# df_csv_data[is_display]를 사용하여 칼럼에 디스플레이 라고 적힌 열들을 얻어내고
# 필요한 정보인 Item 과 Quantity만 보이게 한다.
display_qty = df_csv_data[is_display].filter(['Item','Quantity'])


# In[12]:


# print(warehouse_qty)


# In[13]:

#df_new_data와 arehouse_qty합치기
marged_warehouse= pd.merge(df_new_data, warehouse_qty, how='left', left_on=['Item'], right_on=['Item'])


# In[14]:

#marged_warehouse와 display_qty를 합치고 중복되는 칼럼 이름에 왼쪽 _warehouse 오른쪽 _display를 달아줌
marged_data=pd.merge(marged_warehouse, display_qty, how='left', left_on=['Item'], right_on=['Item'], suffixes=('_warehouse', '_display'))


# In[15]:

# 이렇게 합쳤을때 데이터가 없는quantity_warehouse칼럼과 qauntity_display칼럼에
# NA라고 나오는데 fillan를 통해 0으로 바꿔준다.
na_to_zero_data = marged_data.fillna(dict(Quantity_warehouse=0, Quantity_display=0))


# In[16]:

# 웨어하우스에는 0개 이상, 디스플레이는 0개인 제품을 확인한다.
data = na_to_zero_data.loc[(non_to_zero_data['Quantity_warehouse'] > 0) & (non_to_zero_data['Quantity_display'] == 0)]

print(data)

첫글을 쓰고 나서 필요한 코드들을 찾데 2-3시간 걸린 것 같다.

이렇게 한번 고생해서 해놓으면 다음엔 실행만 하면 내가 원하는 결과를 얻을 수 있는게 파이썬으로 스크립트를 만드는 것의 장점이다.

엑셀을 이용했을 때

1. 재고관리 프로그램에서 재고 데이터를 내려받는다. 

내려받아진 CSV 데이터의 형식은 위와 같다.

Quiz. 매장에 진열이 가능한 제품은 무엇들 인가?

진열이 가능한 제품을 찾는데 얼마나 걸렸는가?

만일 내려 받아진 데이터가 아래와 같으면 매장 진열이 필요한 제품을 쉽게 찾을 수 있을텐데... 아쉽게도 데이터를 그렇게 전달해 주지 않는다.

2. 새로운 시트를 만든 뒤 제품명들을 복사해 넣는다.

3. 중복되는 이름을 제거한다.

4. 창고와 매장의 열에 SUMIFS 함수를 사용하여 위와 같은 테이블을 만든다. 

5. 필터를 사용하여 매장의 0 창고의 0 이상인 것들을 선택한다.

물론 어렵지는 않다. 그냥 번거롭고 쓸때 없는 시간이 소비될 뿐이다. 직원한테 이미 공식이 있는 포맷을 만들어서 

그 포멧에 맞게 데이터를 껴 넣기만 하라고 해도 컴퓨터가 버벅거리고 귀찮아서 잘 안한다.

그러면.... 우리가 팔아야 할 제품은 창고에서 세상밖으로 나오질 못하게 된다. 

파이썬을 사용했을 때

1. 엑셀을 다운 받는다.

2. 주피터노트북을 실행한다.

3. 코드를 실행한다.

아래의 데이터가 

이런 형태로 변환이 된다.