[파이썬 엑셀] python으로 excel 다루기 5편_함수(수식) 적용, 수식 결과값을 가져오는 법(data_only = True)

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "=SUM(5, 10, 15)" 
ws["A2"] = "=AVERAGE(5, 2, 3)"

# 주로 데이터가 입력된 셀의 값을 참조해서 수식 적용

ws["A3"] = 20
ws["A4"] = 30
ws["A5"] = "=SUM(A3:A4)" # 50

wb.save("formula.xlsx")

다음 내용을 진행하기 위해서 꼭 위의 코드를 실행해주시고, 엑셀 파일은 다음 실습을 위해서 확인하지 말거나 복사본을 만들어서 복사본을 확인 해주세요!

만들어진 엑셀 파일은 아래와 같이 수식이 들어가서 그 결과가 셀에 들어가 있는 것을 확인할 수 있습니다.

2. 수식 적용된 셀의 데이터를 가져오기

수식이 들어간 셀을 파이썬에서 다루려면 data_only를 알고 계셔야 합니다. 이게 무슨 말인지 차근차근 설명드려보겠습니다.

아래의 코드는 방금 만든 엑셀 파일(formula.xlsx)의 데이터들을 파이썬 터미널에서 확인하는 코드입니다.

from openpyxl import load_workbook

wb = load_workbook("formula.xlsx")
ws = wb.active

for row in ws.values:
    for cell in row:
        print(cell)

실행해보시면 아래와 같이 수식이 그대로 출력됩니다.

<실행 결과>

=SUM(5, 10, 15)
=AVERAGE(5, 2, 3)
20
30
=SUM(A3:A4)

만약 파이썬으로 수식으로 처리된 값을 사용해서 다른 계산을 하고싶은데 이렇게 출력되면 불가능하겠죠?

그래서 우리는 wb = load_workbook( )로 엑셀 파일을 불러올 때 data_only 설정을 하는 법을 알아둬야 합니다.

결론부터 말씀드리면 아래와 같이 data_only=True라는 설정을 추가해주시면 됩니다.

wb = load_workbook("formula.xlsx", data_only=True)

방금 전 실행했던 코드에 data_only 설정을 해서 실행하면 아래와 같은 결과가 나옵니다.

<실행 결과>

None
None
20
30
None

의도했던 결과와 다르게 수식이 들어가 있던 셀의 값들이 None으로 출력됩니다. 당황하지 마시고 "fomula.xlsx"를 한 번 열어서 저장해준 후에 다시 코드를 돌려보세요. 아마도 값이 정상적으로 나오는 것을 확인하실 수 있을거에요.

<실행 결과>

30
3.3333333333333335
20
30
50

앞으로도 파이썬을 통해 수식이 포함된 엑셀 파일을 만들었다면 한 번 직접 열어서 저장해주어야 의도한 엑셀 파일이 완성된다고 생각해두면 None이 떠서 당황하는 상황이 발생하지 않을 것 같네요!

<여기서 잠깐> load_workbook( ) 제대로 쓰기

우리가 엑셀 파일을 불러오기 위해서 사용해왔던 load_workbook( ) 함수는 사실 여러가지 설정(정확히는 매개 변수)이 있습니다.

(function) load_workbook: (filename, read_only=False, keep_vba=KEEP_VBA, 
			   data_only=False, keep_links=True) -> Workbook

1) filename

가장 첫 설정은 파일 이름입니다. 지금까지 배워왔 듯이 "엑셀 파일명.xlsx"와 같이 써주시면 됩니다.

2) read_only

읽기 전용 모드로 편집이 불가능한 대신, 데이터가 많아지면 버벅거리는 엑셀 파일을 비교적 가볍게 참조할 수 있습니다.

따로 설정하지 않으시면 기본적으로 False 값이 들어가 있어서 일반 모드로 불러옵니다.

3) keep_vba

vba는 간단히 말해 액셀 안의 매크로 프로그램입니다. keep_vba 기능은 해당 엑셀 파일에 존재하는 vba 파일을 보존해주는 설정이며, 기본적으로 KEEP_VBA 설정이 켜져 있습니다. 만약 vba를 불러오지 않아야 하는 상황이면, keep_vba = False를 넣어주시면 됩니다.

4) data_only

셀 안에 수식이 있다면 수식이 계산된 결과값을 가져오는 설정입니다. 이 설정도 기본값이 False로 들어가 있어, 사용하고 싶다면 data_only = True를 넣어 주어야 합니다.

5) keep_links

함수의 설명을 보면 외부 통합 문서에 대한 링크를 보존해야하는지에 대한 여부를 설정하는 것이라 합니다. 저는 엑셀과 친하지 않아서 무슨 말인지 잘 모르겠으니 맘편하게 기본값인 True로 사용하겠습니다. 참고로 기본값이란 함수를 사용할 때 별도로 값을 넣어주지 않아도 적용되는 값을 말합니다.

다음 시간에는 데이터를 가지고 차트를 만드는 법에 대해 공부해 보겠습니다.

감사합니다.

'파이썬 패키지 > 엑셀' 카테고리의 다른 글

[파이썬 엑셀/PyWin32] 개별 엑셀 파일의 특정 시트들을 새로운 엑셀 파일에 모두 합치기 (ft. Openpyxl로는 못함) (6)	2021.12.12
[파이썬 엑셀] Openpyxl의 ws.rows와 ws.max_row의 빈칸 인식 문제 원인과 해결책 (1)	2021.12.11
[파이썬 엑셀] python으로 excel 다루기 4편_셀 서식 설정(맞춤, 글꼴, 테두리, 채우기, 보호) (1)	2021.05.31
[파이썬 엑셀] python으로 excel 다루기 3편_셀 데이터 가져오기 (2)	2021.05.30
[파이썬 엑셀] python으로 excel 다루기 2편_엑셀 파일 불러오기, 셀 데이터 입력/삭제 및 빈칸 추가 (0)	2021.05.29

코딩유치원