엑셀에서 파이썬을 사용해 보자 (기초 사용법과 예제) [파이썬 분석 부록 B1]

마이크로소프트 엑셀의 최신 버전은 파이썬을 지원합니다. 모든 버전의 엑셀이 파이썬을 지원하지는 않습니다. 구독형 서비스인 마이크로소프트 365(Microsoft 365)에서 사용 가능합니다. 참고: Excel의 Python 소개 [마이크로소프트]

파이썬을 지원하기는 하지만, 엑셀이 파이썬 엔진을 내장하는 방식은 아닙니다. 마이크로소프트 클라우드(cloud) 서비스에 파이썬을 띄워놓고 불러 쓰는 방식입니다. 이 때문에 인터넷에 접속되어 있지 않으면 엑셀 파일에 있는 파이썬 기존 실행 결과나 코드를 볼 수 있지만, 실행할 수는 없습니다.

엑셀은 자체적으로 비주얼 베이직(Visual Basic)이라는 프로그래밍 언어를 지원하고 있습니다. 파이썬을 추가로 지원하는 이유는 여러 가지가 있겠지만, 데이터 처리를 해 보면 파이썬으로 훨씬 간결하게 코드를 작성할 수 있습니다.

아쉽게도 파이썬의 모든 기능을 활용할 수는 없습니다. 기본으로 지원하는 라이브러리(패키지) 외 추가 라이브러리를 설치할 수 없으며, 외부 데이터를 불러오는 기능은 엑셀의 파워 쿼리(Power Query)에 의존해야 합니다. 참고: 파워 쿼리를 사용하여 Excel의 Python용 데이터를 가져옵니다. [마이크로소프트]

이러한 한계점에도 엑셀에서 파이썬을 사용하면 편리한 경우가 있을 수 있습니다. 예를 들어 익숙한 파이썬으로 계산을 하고 싶은 경우도 있고, 이미 작성해 둔 파이썬 코드가 있을 수 있으며, 상황에 따라 엑셀의 다소 부족한 그래프 기능을 파이썬의 matplotlib 모듈로 보완할 수도 있기 때문입니다.

이 글에서는 엑셀에서 파이썬을 사용하는 방법을 간단한 예를 들어 살펴봅니다.

엑셀에서 파이썬 코드 삽입과 실행 방법

다음과 같이 엑셀에서 메뉴 > 수식 > Python(미리보기) > 편집기를 선택하면 파이썬 코드를 입력할 수 있습니다. 만일 이 메뉴가 보이지 않거나, 활성화되어 있지 않다면, 해당 엑셀 버전은 파이썬을 지원하지 않거나, 사용할 수 없는 상태인 것입니다.

엑셀에서 파이썬 편집기를 선택하는 화면

메뉴를 선택하면, 다음과 같이 오른쪽에 파이썬 편집기 창이 나오고, 맨 아래에 현재 셀 위치인 A1 셀에 파이썬 코드를 추가할 수 있는 버튼이 나옵니다. 이를 눌러봅니다.

파이썬 편집기에서 새 파이썬 셀 추가

다음과 같은 파이썬 코드를 입력할 수 있는 창으로 바뀝니다.

파이썬 코드 실행 결과

간단하게 다음의 코드를 입력합니다.

import numpy as np

a_l = [1, 2, 3, 4, 5]
np.mean(a_l)

list [1, 2, 3, 4, 5]에 대해 평균을 계산하는 코드입니다. 코드를 입력한 후 Ctrl + Enter 키를 누르면, 코드의 마지막 라인의 실행 결과인 3.0 출력되고, A1 셀에도 들어갔습니다.

엑셀 시트의 셀 참조 방법

엑셀을 사용하다면 기초 데이터는 모두 엑셀의 시트에 있을 것입니다. 파이썬 실행 결과도 시트에 표시할 것입니다. 시트에 있는 셀의 값을 가져와서 계산하여 돌려주는 파이썬 코드를 작성해 봅니다.

다음과 같이 A3:B6 셀에 3번의 시험 성적을 기록한 표가 있습니다. 이 표에서 점수를 가져와서 평균을 구해 B8 셀에 넣을 것이니다.

B8 셀에서 파이썬 편집기를 선택하면, 그림과 같이 기존 A1 셀에 있는 파이썬 코드를 보여주고, 아래에 B8 셀에 새로운 파이썬 코드를 추가할 수 있는 버튼이 나옵니다.

A1 셀에서 사용한 변수 a_l은 B8 셀에서도 참고할 수 있습니다. 다음과 같이 a_l을 입력하고 실행하면 a_l의 값이 나옵니다. 이 때문에서 코드를 작성할 때, 동일한 변수명을 다른 용도로 중복 사용하지 않도록 주의해야 합니다.

공유되는 파이썬 변수

시트의 셀을 가져오기 위해서는 xl() 함수를 사용합니다. 다음과 같이 xl("A3:B6", headers = True)를 입력하면, 테이블을 가져와서 DataFrame으로 변환해 줍니다.

시트의 표를 파이썬에서 불러온 결과

headers = True로 지정하면 첫 행을 칼럼명으로 설정해 줍니다. 시트에서 표의 위치를 옮기면, 파이썬 코드에서 참조한 셀의 범위도 이에 맞춰 자동 변환됩니다.

시트에서 셀의 위치를 변경한 결과

Ctrl + X (잘라내기)와 Ctrl + Y (붙이기)를 사용하여 시험 성적표를 A4:C7 위치에서 B5:C8로 옮겼습니다. 그림과 같이 파이썬 코드의 셀 참조 범위가 자동으로 변경됩니다.

테이블에 행을 하나 추가해도 됩니다. 다음과 같이 표에서 행을 추가하면 이에 맞춰 파이썬의 셀 범위도 자동으로 조정됩니다.

표에 추가한 행이 반영되어 파이썬에서 셀 범위가 자동 조정된 결과

C11 셀의 파이썬 코드로 다음과 같이 평균을 계산합니다.

score_df = xl("B5:C9", headers = True)

np.mean(score_df.점수)

score_df의 점수 칼럼이 시험 점수를 담고 있습니다. score_df.점수는 이 칼럼만 사용하겠다는 의미입니다.

파이썬으로 계산한 평균 점수가 나타난 C11 셀

C12 셀에는 표준 편차를 계산해서 넣어 보겠습니다. 앞에서 설명한 것처럼 파이썬 셀 간의 변수가 공유되기에 score_df를 다시 만들 필요가 없습니다. 참고: 어떤 순서로 파이썬 셀이 실행되는 것인지 애매할 수 있기에 변수명 사용 시 주의를 기울여야 합니다.

C11에서 설정한 score_df를 이용하여 표준 편차를 계산한 C12 파이썬 셀

C12 셀에 계산된 표준 편차가 나타났습니다.

정리하며

마이크로소프트 엑셀에서 파이썬 코드를 사용하는 방법을 살펴보았습니다. 모든 엑셀 버전이 파이썬을 지원하지는 않습니다. 사용할 수 있는 라이브러리에도 제약이 있고 추가 라이브러리를 설치할 수도 없습니다. 실행을 위해서는 인터넷에 접속되어 있습니다.

이런저런 한계가 명확하지만, 나름 유용할 수 있습니다. 특히 엑셀의 기본 그래프 기능은 로그 스케일을 설정하면 미흡한 면이 있습니다. 이 부족한 점을 파이썬이 어느 정도 보완할 수 있기에 사용자에 따라서는 상당한 도움이 될 수 있습니다. 이어지는 글에서 살펴봅니다.
참고 서적:

• 왜 위험한 주식에 투자하라는 걸까? - 장기 투자와 분산 투자에 대한 통계학적 시각
• 파이썬으로 그려보는 투자 포트폴리오 분석 - 정량적 투자 분석을 위한 입문서

이어지는 글: 엑셀에서 파이썬으로 그래프를 그려보자 (+한글 사용) [파이썬 분석 부록 B2]
연재 목록: 자산 배분 분석 방법 책 소개, 연재글 및 사례 모음 [목록]
함께 읽으면 좋은 글 (최신 글)

1. 엑셀로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C2] (목표값 찾기)
2. 구글 시트로 임의 적립식 투자 수익률에 해당하는 거치식 CAGR을 구해보자 [데이터 분석 부록 C1] (구글 시크 Google Seek)
3. 임의 적립식 투자 수익률에 해당하는 거치식 성장률을 찾아보자 [파이썬 분석 부록 A4]
4. 파이썬을 이용한 수치 해석 (적립식 수익률에 해당하는 거치식 성장률을 찾아보자) [파이썬 분석 부록 A3]
5. 거치식과 적립식 수익률을 서로 환산해 보자 (어떻게 추정하면 적절할까?) [파이썬 분석 부록 A2]

함께 읽으면 좋은 글 (인기 글)

1. 구글 제미나이(Gemini)를 이용한 그림 생성하기 (vs. 마이크로소프트 이미지 크리에이터의 이미지 생성 AI)
2. 챗GPT(ChatGPT)로 사진을 지브리 만화 스타일로 변환하기
3. 스튜디오 지브리 만화 스타일로 그림 그리기 (구글 제미나이)
4. 커버드콜과 노벨상 (커버드콜에 투자하면 안 되는 간단하고 명확한 이유 + 노벨상을 받는 손쉬운 방법)
5. 당신이 JEPI/JEPQ를 사면 안되는 이유 (해외 상장 인컴 ETF의 배당소득세와 양도소득세)