[파이썬/업무자동화] Openpyxl로 엑셀 함수 사용하기, 엑셀 수식 넣기

※ 이 글을 쓰는 사람은 SW 비전공자입니다.

※ 개인 공부를 위해 정리하는 글이며, 작성한 코드들은 효율성, 깔끔함(?) 등과는 거리가 멀 수 있습니다.

 

※ OpenPyxl 관련 포스팅 모음

2021.03.19 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl로 엑셀 함수 사용하기, 엑셀 수식 넣기

2021.08.21 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl 통해 엑셀 함수 사용하기 2편

2021.08.30 - [코딩/업무자동화] - [파이썬/업무자동화] 엑셀에 입력 된 파일명 따라 이미지 삽입하기 - openpyxl

2021.10.04 - [코딩/업무자동화] - [파이썬/업무자동화] openpyxl.style 사용법 정리

2021.10.16 - [코딩/업무자동화] - [파이썬/업무자동화] openypyxl 통해 엑셀 조건부 서식 적용하기

2021.11.09 - [코딩/업무자동화] - [파이썬/업무자동화] openpyxl 시트 조작 방법 4가지

---

  Openpyxl 을 사용하면 엑셀에 직접 수식이나 함수를 입력할 수 있다.( ex) 엑셀 함수 SUM, IF, COUNTA, VLOOKUP 등)

이번 포스팅에서는 엑셀에 직접 수식과 함수를 입력하는 방법에 대해서 정리해보려고 한다. VLOOKUP, IF 등 귀찮은 함수작업들을 항상 반복했다면 Openpyxl을 통해 쉽게 자동화 할 수 있다. 

 

 

   설명을 위해 아래와 같이 임의의 엑셀 양식을 작성해보았다.  정리할 내용은 아래와 같다.

 

1) 정해진 위치에 수식을 넣는법

 

2) 정해진 위치에 함수 사용하기

 

3) 엑셀 행 data 개수에 맞게 함수 사용하기(엑셀 수식쓰고 드래그와 같은 효과)

 

 

<test 양식>

 

 

  그 전에 openpyxl을 import하고 workbook, worksheet 객체를 생성하는 코드를 작성하였다. openpyxl 객체를 생성하는 내용에 대해서는 이전에 정리한 바가 있다.(참고링크 : ybworld.tistory.com/11 )

  아래에서 생성한 ws 객체를 통해 엑셀 파일의 각 셀 위치에 접근할 수 있다.

import openpyxl as op

filepath = "C:/Users/Desktop/VS CODE/excel_test.xlsx"

#workbook 객체 생성
wb = op.load_workbook(filepath)
#worksheet 객체 생성
ws = wb.active

 

1. 정해진 위치에 수식을 넣는 법

  

  위 엑셀 양식에서 숫자의 합계를 수식으로 넣는 법은 아래와 같다. 파이썬을 통해 엑셀 수식이나 함수를 파일에 직접 입력하는 방법은 간단하다. 아래처럼 수식이나 함수 내용을 그냥 문자열 취급하면 된다. 

 

#직접 셀 위치로 접근하기
ws['F1'] = "수식넣기(1+2+3)"
ws['G1'] = "=B2+B3+B4"

#row, column 숫자로 접근하기
ws.cell(row=2, column=6).value = "수식 넣기(4+5+6)"
ws.cell(row=2, column=7).value = "=B5+B6+B7"

#파일 저장 : 저장해야 결과가 반영된다. 파일명은 result로 저장
wb.save("result.xlsx")

 

<결과>

  셀 위치를 접근하는 방법은 위 코드와 같이 2가지이다. 셀 위치( ex)A1,B2) 로 접근하거나 셀 숫자로 접근하는 방식이다. 셀 숫자로 접근하는 방법은 row = 행 위치값(숫자), column = 열 위치값(숫자) 로 이해하면 된다. 

 

 

 

#input으로 cell 위치 직접 입력받기
cellposition = str(input("위치 직접 입력하기 : "))
ws[cellposition] = "=B2+B3+B4"

<위치 직접 입력 받기>

  만약 입력하는 위치를 정하고 싶다면 input을 활용하면 된다. G1을 입력하면 위와 같이 수식값이 G1에 들어간다.

 

  변경 내용이 반영되게 하려면 마지막 줄의 wb.save( )를 반드시 넣어줘야 한다. 괄호 안은 저장될 파일명이다. 경로를 따로 지정하지 않으면 .py 파일이 있는 곳에 결과 파일이 생성된다.

 

 

2. 정해진 위치에 함수 사용하기

 

  함수 사용하는 방법도 1번의 수식을 넣는 방법과 크게 다르지 않다. 함수를 문자열 취급하여 입력하면 된다. 마찬가지로 마지막에 wb.save( )를 써줘야 결과가 반영된다.

 

# 셀 위치 직접 접근하기
ws['j1']= "SUM 함수 사용"
ws['K1'] = "=SUM(B1:B10)"

# 셀 위치값(row, column)으로 접근하기
ws.cell(row=2, column=10).value = "AVERAGE 함수 사용"
ws.cell(row=2, column=11).value = "=AVERAGE(B1:B10)"

# 저장하기
wb.save("result.xlsx")

 

<결과>

< 함수 사용하기 결과>

 

 

3) 엑셀 행 data 개수에 맞게 함수 사용하기(엑셀 수식쓰고 드래그와 같은 효과)

 

  openpyxl을 통해 엑셀 함수 사용을 자동화하고 싶다면 가장 중요한 부분이라 할 수 있다. 보통 엑셀 함수를 사용하는 경우 한줄에 함수를 작성 후 드래그를 하여 나머지 줄에도 반영되게 한다. 이 부분을 파이썬 코드를 통해 자동화할 수 있다.

 

#for 문 사용 : ws.rows는 엑셀 파일에 가장 마지막 행에 있는 data의 행 위치이다.
for r in ws.rows:
	#r은 행값이고, 중괄호의 [0]은 'A'열을 지칭한다. [1]은 'B'열이다.
    row_index = r[0].row
    print(r[0])
    ws['D'+str(row_index)] = "=IF(A"+str(row_index)+"=C"+str(row_index)+',"같다","")'
    
#저장
wb.save("result.xlsx")

 

< 엑셀 파일 결과>

 

-. 위 코드는 = IF(A2=C2, "같다", "")를 아래로 드래그한 것을 파이썬 코드로 표현한 것이다.

 

-.  for문 시작의 ws.rows는 엑셀 파일에 가장 마지막 행에 있는 data의 위치를 나타낸다. 

 

-.  코드 중간에 row_index = r[0].row 가 이해하기 어려울 수있는데 중간에 print(r[0])의 결과를 보면 아래 이미지와 같다. r[0]는 for문의 각 행값에 따른 열 위치를 의미한다. 숫자 0은 A열을 지칭한다. 

r[0] 출력시

  

-. 엑셀 함수를 자동화하는 부분은 아래 코드를 참고한다. 변수가 되는 부분을 제외한 함수의 모든 부분을 문자열 처리하면 된다. 

row_index는 integer type 정수이기 때문에 엑셀 함수에 사용하려면 str type으로 변경해야 한다.

 

    ws['D'+str(row_index)] = "=IF(A"+str(row_index)+"=C"+str(row_index)+',"같다","")'

 

  위와 같은 방식을 활용하면 엑셀에서 자주 사용하는 VLOOKUP 사용도 자동화할 수 있다.