Python + Excel
Python으로 Excel 파일 스타일 화 및 자동화
초보자가 Python을 시작하는 방법 인 xlwings를 사용하여 양식화 된 Excel 보고서를 만드는 데 Python을 활용하는 방법.
Introduction
세계로의 여정을 처음 시작할 때 통과 할 기본 게이트웨이는 없습니다.피이썬.언어의 일반적인 사용 범위는 데이터 과학에서 기계 학습, 인공 지능에 이르기까지 다양합니다.이들은 Python을 빠르게 구현할 수있는 훌륭한 영역이지만 대부분의 회사에서 대다수의 인력을위한 사용 사례가 없습니다.금융 서비스는 다른 산업과 함께 여전히 기본적인 데이터 조작을 위해 Excel을 사용하고 있습니다.이런 방식으로 작업하는 사람은 Python이 이러한 복잡한 주제와 수년에 걸쳐 구축 한 연관성을 고려할 때 Python을 잠재적 인 솔루션으로 고려하지 않을 수 있습니다.
Python에 대한 노출은 수동 Microsoft Excel 작업에 투자하는 시간을 최소화해야하기 때문입니다.반복적 인 작업을 자동화하면 엄청난 양의 작업자 시간을 확보 할 수 있으므로 당면한 더 복잡한 문제에 집중할 수 있습니다.VBA는이 워크 플로의 속도를 높이는 데 이상적인 솔루션처럼 보였습니다.
VBA를 배우고 이해하기까지 몇 달이 걸렸습니다.그러나 그 역량의 지점에 도달하더라도이 접근 방식이 얼마나 효율적이고 효과적인지 엄격하게 정의하고 제한하는 융통성없는 한계가있는 것처럼 느껴졌습니다.그때 제가 파이썬에 관심을 돌렸고 파이썬이 제가 필요하다는 것이 분명해지기까지 오래 걸리지 않았습니다.
매일 Excel을 사용하고 이러한 작업의 자동화를 검토 중이며 현재 VBA를 배우려고하는 상황에 처해있는 경우이 문서에서 작업 과정을 변경할 수 있습니다.
VBA 대신 Python을 사용하는 Excel을 사용하는 이유
Python은 표준 라이브러리에 내장 된 강력한 패키지의 방대한 배열을 포함합니다.또한 설치하기 쉬운 타사 모듈을 활용할 수 있습니다.이들의 조합은 VBA에 비해 코드를 실행하는 데 걸리는 시간뿐 아니라 필요한 코드의 양을 크게 줄일 수 있습니다.이를 보여주는 가장 좋은 방법은 간단한 예입니다.
VBA와 Python 및 Pandas를 사용하여 데이터 세트의 열에서 각 고유 값을 계산하는 방법을 쉽게 비교할 수 있습니다.다음 두 가지 방법 모두 초보자가이를 달성 할 수있는 방법입니다.
VBA
이 코드는 Excel의= COUNTIF ()함수를 사용하고 결과를 새 통합 문서에 붙여 넣습니다.
하위 매크로 ()Workbooks ( "Book1"). 활성화
Workbooks ( "Book1"). Sheets ( "fruit_and_veg_sales"). Activate
열 ( "B : B"). 선택
선택. 복사
통합 문서. 추가
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range ( "$ A : $ A"). RemoveDuplicates 열 : = 1, 헤더 : = xlNoRange ( "B1") = "개수"
Range ( "B2"). Formula = "= COUNTIF ([Book1] fruit_and_veg_sales! $ B : $ B, A2)"
범위 ( "B2"). 복사
Range ( "B2 : B"& amp; ActiveSheet.UsedRange.Rows.Count) .PasteSpecial xlPasteAll
계산하다
Range ( "B2 : B"& amp; ActiveSheet.UsedRange.Rows.Count) .Copy
Range ( "B2 : B"& amp; ActiveSheet.UsedRange.Rows.Count) .PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
Python
df [ "col"]. value_counts ()Pandas를 사용하는 Python에서 필요한 모든 것입니다.(또한 추가 혜택으로 값을 정렬합니다)
Python에서 이미 사용 가능한 패키지를 사용하고 최종 데이터를 Excel로 출력하면 데이터의 모든 일반 사용자에 대해 동일한 출력을 유지하면서 전체 자동화 프로세스의 속도를 높일 수 있습니다.여기서 절약되는 시간은 코드를 적게 작성하는 것뿐만 아니라 훨씬 더 이해하기 쉬운 코드를 작성하는 것입니다.
이렇게하면 귀하와 향후 코드 사용자 모두에게 엄청난 시간을 절약 할 수 있습니다.
이제 알 수있을 것입니다. Python은 초보자가 쉽게 익힐 수있는 언어입니다.아직 뛰어 들지 않으려는 사람들에게는 Excel과 통합 할 수 있고 익숙한 인터페이스를 사용하는 것이 Python을 쉽게 사용할 수있는 완벽한 방법입니다.
Python과 Excel로 첫 걸음을 내 디딘 후 SQL 데이터베이스에서 언어를 사용하고 웹 사이트에서 데이터를 스크랩하고 대시 보드를 만드는 데 더 큰 진전을 이룰 것입니다.Python 모듈로 모든 것을 쉽게 달성 할 수 있습니다.
Python을 시작하는 것은 이러한 추가 기술을 배우는 데 큰 도움이 될 수 있습니다!
xlwings
이제이 보고서 작성을 어떻게 시작합니까?
큐 xlwings…
xlwings는 Python 코드를 사용하여 Excel을 쉽게 조작하고 자동화 할 수있는 오픈 소스 Python 라이브러리입니다.xlwings는 Python의 과학 패키지 (NumPy, Pandas, SciPy, scikit-learn 등)를 활용하고 VBA 대신 Python으로 도구를 작성하며 Python에서 Excel에서 사용할 사용자 정의 함수를 만들 수 있습니다.대시 보드를 만들어 Excel 데이터에 온라인으로 액세스 할 수도 있습니다!요약하면 xlwings는 현재 Excel 툴킷과 워크 플로를 크게 향상시킬 수있는 기능을 가지고 있습니다!
선호하는 Python 설치 방법이 Anaconda 인 경우 2020 년 11 월 릴리스부터 xlwings 0.20.8이 Anaconda의 사전 설치된 패키지에 내장됩니다.즉, Anaconda를 통해 Python을 설치하면 xlwings를 자동으로 가져 와서 사용할 수 있습니다.
이 사전 설치가없는 경우pip 설치 xlwings거기로 데려다 줄 것입니다.
xlwings는 Excel 기반 작업의 자동화와 보고서 작성에 매우 유용했습니다.Python을 사용하고 Excel과 상호 작용할 수있게되면서 Excel을 뒷좌석 역할로 밀어 붙이면서 더 복잡한 코딩 주제를 배우는 데 시간을 집중할 수있었습니다.
데이터를 표현 가능한 보고서로 포맷
이 튜토리얼에 사용하기 위해 csv 형식의 과일 및 채소 판매 테스트 데이터 세트를 만들었습니다.
데이터는 Github에서 다운로드 할 수 있습니다.링크
Steps to creating your report!
- Import Pandas and xlwings. We will be focusing on xlwings in this tutorial. Pandas will only be used to quickly import our data into Python.
import pandas as pd
Import xlwings as xw
2. 이제 Pandas를 사용하여 csv 파일을 DataFrame으로 가져옵니다.
df = pd.read_csv (r”path_to_csv / fruit_and_veg_sales.csv”)
팁:대신이 줄을 실행하여 Github에서 직접 csv 파일을 읽을 수도 있습니다.df = pd.read_csv (r "https://raw.githubusercontent.com/Nishan-Pradhan/xlwings_report/master/fruit_and_veg_sales.csv")
이제 Python에 데이터가 있으므로 조작이나 추가 계산을 자유롭게 수행 할 수 있습니다.이 튜토리얼의 목적을 위해 데이터를 그대로 유지합니다.
3. Excel 통합 문서를 초기화합니다.
wb = xw.Book ()
여기서 우리는 통합 문서의 이름을wb사용 된 xlwings도서()Excel 통합 문서를 초기화합니다.
4. 다음으로 워크 시트 참조를 변수로 설정하고 시트의 이름을 변경합니다.
sht = wb.sheets [ "Sheet1"]
sht.name = "fruit_and_veg_sales"
여기에서 Excel Sheet1에 대한 참조를 다음과 같이 설정합니다.쉿Sheet1의 이름을 fruit_and_veg_sales로 변경했습니다.
5. 이제 과일 및 채소 판매와 함께 Python으로 된 DataFrame이 있고 “fruit_and_veg_sales”라는 시트가있는 열린 Excel 통합 문서가 있습니다.다음 단계는 Python에서 Excel로 데이터를 가져 오는 것입니다.다음을 호출하여이를 수행합니다.
sht.range ( "A1"). options (index = False) .value = df
VBA를 알고 있다면 익숙한 구문을 인식해야합니다.sht.range ( "A1").여기에서 DataFrame을 얻으려면dfExcel 문서에 데이터를 저장할 위치를 지정했습니다 (이 경우 “A1″셀).
그만큼.options (index = False)원치 않는 색인 복사를 방지합니다.DataFrame의 인덱스가 필요한 경우 줄의이 부분을 제거 할 수 있습니다.진실기본적으로, 즉sht.range ( "A1"). value = df.
포함하는 것을 잊지 마십시오.값, 이것을 지정하지 않으면 구문 오류가 발생합니다.SyntaxError : cal에 함수를 할당 할 수 없습니다...값곧 다룰 다른 속성이 아니라 Excel 셀의 값에 대해 이야기하고 있음을 xlwings에 알립니다.
6. 이제 완전히 서식이 지정되지 않은 데이터가있는 Excel 파일을보고 있어야합니다.
7. Python에서 Excel로 데이터를 가져 왔습니다.이제 포맷을 시작하겠습니다!
Excel 내에서 데이터 서식을 지정할 때 가장 먼저해야 할 일은 서식을 지정할 데이터를 선택하는 것입니다.xlwings에서이를 수행하기 위해 시트의 범위와 관련된 변수를 생성합니다.
all_data_range = sht.range ( "A1"). expand ( 'table')
여기 사용sht.range ( "A1"). expand ( 'table')기본적으로 A1 셀에서 시작하여 CTRL + SHIFT + ↓ (아래쪽 화살표), CTRL + SHIFT + → (오른쪽 화살표)로 이동하여 데이터 (Windows)를 선택합니다.그러면 범위 A1 : I1001이 선택되고이 범위 참조는 이름이 지정된 변수에 저장됩니다.모든 _ 데이터 _ 범위.
Python에서 변수로 참조하는 범위를 확인하려면print (모든 _ 데이터 _ 범위. 주소).
8. 범위 참조 변수를 설정 했으므로 이제 보고서 스타일 지정을 시작할 수 있습니다.데이터의 행과 열 높이부터 시작하겠습니다.
all_data_range.row_height = 22.5
all_data_range.column_width = 12
22.5 및 12는 Microsoft 설명서에 따라 ‘기본 글꼴의 문자’에있는 Excel의 크기 측정을 나타냅니다.
9. 다음으로 셀의 배경색을 변경합니다.이를 위해 튜플에서 RGB 색상을 사용합니다.
all_data_range.color = (208,206,206)
이렇게하면 범위의 배경색이 밝은 회색으로 변경됩니다.
10. 텍스트를 편집하려면 필요한 기능이 약간 다릅니다.xlwings를 사용하여 Microsoft의 Excel API를 호출합니다.이것은 VBA를 사용하는 것과 거의 동일합니다.
all_data_range.api.Font.Name = 'Arial'
all_data_range.api.Font.Size = 8all_data_range.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenterall_data_range.api.VerticalAlignment = xw.constants.HAlign.xlHAlignCenterall_data_range.api.WrapText = True
여기에서는 데이터의 글꼴을 Arial로 변경하고 글꼴 크기를 8로 설정하고 텍스트 중앙을 가로 및 세로로 정렬하고 마지막으로 텍스트 줄 바꿈을 켜서 셀의 모든 텍스트를 봅니다.
11. 테이블을 더욱 매력적으로 보이게하기 위해 몇 가지 색상을 추가하고 헤더 형식을 지정합니다.
header_range = sht.range ( "A1"). expand ( 'right')
header_range.color = (112,173,71)
header_range.api.Font.Color = 0xFFFFFF
header_range.api.Font.Bold = True
header_range.api.Font.Size = 9
이 블록은 이전에했던 것처럼 범위를 선택합니다.이 선택의 주요 차이점은.expand ( 'right')CTRL + SHIFT + → (오른쪽 화살표)로만 전환됩니다.이를 통해 A1 셀에서 시작하여 Excel의 헤더 열인 I1을 선택할 수 있습니다.
여기서 또 다른 차이점은 글꼴 색상을 흰색으로 변경하도록 선택했다는 것입니다.이를 위해 16 진수 코드를 사용하여 색상을 지정했습니다.0xFFFFFF.
12. 이제 거래 ID 번호 열의 형식을 지정하겠습니다.나는 이것을 더 밝은 녹색 음영으로 만들고 싶습니다.앞서 언급 한 RGB 방법을 사용하여이를 수행 할 수 있습니다.이번에는확장 ( '아래')셀 A2에서.
id_column_range = sht.range ( "A2"). expand ( 'down')
id_column_range.color = (198,224,180)
13. 다음 단계에서는 데이터의 테두리 색상 만 변경합니다 (헤더 열 또는 트랜잭션 ID # 열 제외).Microsoft의 API는 경계 작업에 약간 어색하지만 문서화여기충분합니다.
요약, 위쪽, 아래쪽, 왼쪽 및 오른쪽 테두리는 각각 숫자 7,8,9,10으로 지정됩니다.
data_ex_headers_range = sht.range ( "A2"). expand ( 'table')범위 (7,13)의 border_id :
data_ex_headers_range.api.Borders (border_id) .Weight = 2
data_ex_headers_range.api.Borders (border_id) .Color = 0xFFFFFF
14. 마지막 단계는 보고서를 저장하는 것입니다.요구wb.save (), 저장하려는 경로와 원하는 파일 이름을 지정하십시오.파일 이름을 파일 형식으로 끝내는 것을 잊지 마십시오.이 경우 우리는.xlsx모든 형식을 유지합니다.
wb.save (r "폴더로 _ 경로 \ fruit_and_veg_report.xlsx")
Bonus!
추가로 탭 색상을 녹색으로 변경합니다.
sht.api.Tab.Color = 0x70AD47
Conclusion
몇 줄의 간단한 코드로 최종 사용자를 위해 깔끔하고 표현 가능한 형식으로 Excel 문서를 만들 수있었습니다.이 튜토리얼은 창의력을 발휘하고 자신 만의 미학적으로 만족스러운 보고서를 만들 수있는 충분한 xlwings 구성 요소를 제공해야합니다!빈 행을 삽입하고 글꼴 크기, 배경색, 테두리 두께 및 테두리 색상을 변경하여 보고서를 더욱 멋지게 만드십시오!
이것은 Python과 xlwings를 사용하여 달성 할 수있는 것의 시작일뿐입니다!