엑셀은 제 좋은 친구죠
엑셀 진짜 고수가 되는 법
아래 링크는 <내 손 안의 비서> 첫 글이자 전체 목차
또 상상해볼 시간이다. "나는 화장품을 주로 판매하는 회사의 뷰티케어 마케터다. 팀원 숫자가 적다보니 혼자서 많은 일을 담당하게 되는데, 마케팅 집행에서부터 세일즈 데이터를 관리하는 일까지 다 함께 처리하고 있다. 매달 올리브영에서 우리 회사 제품의 판매량 데이터를 일별로 정리해서 주는데, 그걸 다시 월별로 정리해서 보고 할 수 있는 자료로 만들어야 한다. 그런데 작은 팀이다 보니 일이 너무 많아서, 매달 나오는 이 데이터를 조금 더 쉽게 정리할 수 있으면 좋겠다."
데이터는 위의 그림과 같이 샘플을 만들어 봤는데, "sales_data.xlsx"라는 파일에는 각각 1월과 2월의 시트가 있고 그 안에는 A, B, C, D라는 제품의 일간 판매 실적이 기록되어있다. 이걸 따로 파일을 하나 "sales_analysis.xlsx"라는 이름으로 만들어서 월별로 총합을 만들었으면 좋겠다.
우리의 오픈소스 친구 OpenPyXL
데이터를 처리하는데 유명한 파이썬이라면 당연히 엑셀을 처리하는 오픈소스는 분명히 있을 것 같았다. 그래서 찾아보니 관련 내용을 잘 정리해놓은 블로그가 있어서 참고했다.
이 블로그에서는 성능 상의 이유로 XlsxWriter를 추천하고 있는데, 문제는 해당 라이브러리는 엑셀로 내용을 작성할 수 있을 뿐 읽어들이지를 못해서, PyExcelerate도 마찬가지 이유로 선택할 수 없었다. 만약에 엑셀 파일을 읽어서 처리해야하는 작업이 있다면 속도가 조금 느리더라도 OpenPyXL를 써야하는 상황이다.
본문에서는 openpyxl이 문서화가 잘 안되어있다고 했지만, 간단한 엑셀 작업을 하는데는 충분해 보인다. 그리고 튜토리얼을 통해서 살펴보니 사용법도 충분히 직관적이다. 그럼 이제 써보자.
파이썬 비서에게 부탁하고 빨리 퇴근하자
이번에는 코드가 길어서 코드 안의 주석 표시 "#"에서 설명을 보도록 하자.
~/Desktop/sales.py
# 1) 엑셀을 다룰 수 있는 openpyxl 이라는 도구상자를 가져오자.
#거기에서 load_workbook은 엑셀 파일을 열고,
# Workbook은 새로운 엑셀 파일을 만드는 역할을 한다.
from openpyxl import load_workbook, Workbook
# 2) 파이썬 파일과 함께 바탕화면에 있는 'sales_data.xlsx'이라는 파일을 연다.
wb = load_workbook(filename = 'sales_data.xlsx')
# 3) 결과를 저장할 엑셀 파일을 result라는 이름으로 하나 만들고,
# 자동으로 만들어지는 이름을 result_sheet라는 변수에다 담자.
result = Workbook()
result_sheet = result.active
# 4) 각각의 엑셀시트는 '1월'과 '2월'이라는 이름으로 되어 있으니,
# 각각 ws1과 ws2라는 변수에 담는다.
ws1 = wb.get_sheet_by_name("1월")
ws2 = wb.get_sheet_by_name("2월")
# 5) 1월 엑셀시트에 있는 제품명을 똑같은 위치의 결과 엑셀시트에 옮긴다.
### Column
for column in "ABCDE":
result_sheet["%s1" % (column)] = ws1["%s1" % (column)].value
# 6) 좌측에 1월, 2월과 같은 형태로 번호를 매긴다.
### Row
for num in range(1, 3):
result_sheet["A%d" % (num + 1)] = "%s월" % num
### Sum
# 7) 제품별로 1월의 총합을 구한다.
for column in "BCDE":
sum = 0
for row in ws1.iter_rows('%s2:%s10' % (column, column)):
for cell in row:
sum += cell.value
result_sheet["%s%d" % (column, 2)] = sum
# 8) 제품별로 2월의 총합을 구한다.
for column in "BCDE":
sum = 0
for row in ws2.iter_rows('%s2:%s10' % (column, column)):
for cell in row:
sum += cell.value
result_sheet["%s%d" % (column, 3)] = sum
# 9) 파일을 'sales_result.xlsx'라는 이름으로 저장한다.
result.save('sales_result.xlsx')
예시에서는 2월까지였지만, 만약에 12월까지 다 더해야 한다면? 주석 "### Sum" 아래의 7번과 8번을 계속 적어줘야 할 것이다. 이 부분을 12월까지 한 번에 처리하고 싶어서, 아래에 다시 정리해보았다.
from openpyxl import load_workbook, Workbook
wb = load_workbook(filename = 'sales_data.xlsx')
result = Workbook()
result_sheet = result.active
# 1') B부터 제품이다. 2개의 제품이라면, ABC라고 적으면 된다.
COLUMN_LIST = "ABCDEFGH"
# 2') 12월까지의 엑셀시트를 한 번에 리스트로 만들었다.
sheets = [wb.get_sheet_by_name("%d월" % month) for month in range(1, 13)]
### Column
for column in COLUMN_LIST:
result_sheet["%s1" % (column)] = sheets[0]["%s1" % (column)].value
### Row
for num in range(1, len(sheets) + 1):
result_sheet["A%d" % (num + 1)] = "%d월" % num
### Sum
for index, sheet in enumerate(sheets):
for column in COLUMN_LIST:
sum = 0
for row in sheets[0].iter_rows('%s2:%s31' % (column, column)):
for cell in row:
try:
sum += cell.value
# 3') 값이 없거나 숫자가 아닐 경우 자동으로 for문을 종료한다.
except TypeError:
break
result_sheet["%s%d" % (column, index + 2)] = sum
지나치게 코드가 복잡해보이는 것을 막기 위해서 간단하게 덧셈을 하고 새로운 엑셀 파일로 저장하는 것까지 해보았다. 더 관심 있는 사람은 위에 첨부해놓은 문서를 확인하면, 엑셀에서 제공하는 다양한 수식과 차트까지 파이썬 코드로 작업할 수 있으니 매번 반복적으로 하는 엑셀 작업은 이걸로 안녕이다. 얼른 퇴근하세요.
프로그래밍은 외계어가 아닙니다. 실생활에서 쓸 수 있는 프로그래밍을 알립니다. 그리고 댓글이 달릴수록 더 쉬워집니다.
