Sử dụng Python trong VBA

Bài đăng này dựa trên video của Sigma Coding cộng với một vài bài đăng tôi tìm thấy trong Stackoverflow [nhưng bị mất] để làm cho mã của tôi hoạt động được

Điểm quan trọng của việc sử dụng python để tự động hóa tác vụ trong Excel, thay vì sử dụng VBA, là theo ý kiến ​​cá nhân của tôi, python [và các thư viện tương ứng của nó để phân tích dữ liệu] dễ viết, đọc và học hơn rất nhiều, đồng thời nhanh hơn. Theo kinh nghiệm cá nhân, một lần tôi đã thử lặp khoảng 9 nghìn hàng bằng VBA để sửa đổi một số giá trị dựa trên một điều kiện, không cần phải nói Excel bị đóng băng. Trong khi ở trong Python [với Pandas hoặc Numpy], các hoạt động được vector hóa làm cho việc lặp lại hoàn toàn không cần thiết và thậm chí là không mong muốn

Tóm lại lý do nên dùng python thay VBA để xử lý dữ liệu

• nhanh hơn
• Linh hoạt hơn [Từ khai thác văn bản đến ML]
• Tham khảo thêm các nguồn trên internet để tìm hiểu và tìm giải pháp cho vấn đề mà chúng ta muốn giải quyết
• Viết và đọc dễ dàng hơn

Cấu trúc sẽ có 3 phần

1. Tạo chương trình con VBA chạy bất kỳ tập lệnh python nào
2. Chạy tập lệnh đã nói lấy dữ liệu đầu vào từ excel
3. Viết đầu ra từ tập lệnh trong cùng một sổ làm việc

Tôi sẽ tập trung vào phần nắm tay và phần thứ ba vì bạn muốn python làm gì tùy thuộc vào những gì bạn cần

Cấu trúc của mã

1. Tạo một chương trình con trong VBA chạy bất kỳ tập lệnh python nào

Đầu tiên ta khai báo biến cần thiết cho chương trình con chạy script đó là đối tượng shell, đường dẫn thư mục và lệnh mà đối tượng shell sử dụng.

Sub run_python_script[]'Declare variables
Dim objShell As Object
Dim python_exe, script_path, folder_path  As String
Dim script_name, str_command As String'Create new shell object
Set objShell = VBA.CreateObject["Wscript.Shell"]

Bây giờ, chúng ta phải cung cấp cho shell lệnh để chạy python và tệp mà nó sẽ chạy dưới dạng chuỗi văn bản. Đầu tiên, chúng tôi cung cấp vị trí của tệp thực thi python ở bất cứ nơi nào chúng tôi đã cài đặt python trong máy tính của mình

'Provide the file path to the python exe
python_exe= """C:\Users\USER\XXXXXX\XXXXXX\Python38-32\python.exe"""

Giả sử tập lệnh ở cùng vị trí với sổ làm việc excel, chúng tôi lấy vị trí sổ làm việc và thêm tên tập lệnh vào đường dẫn

'Provide the file path to python script
folder_path= Application.ThisWorkbook.Path
script_name = "script.py"
script_path = """" & folder_path & "\" & script_name & """"

Và chúng tôi nối cả hai chuỗi với vị trí thực thi python và vị trí tập lệnh để nó có thể được chuyển đến trình bao đối tượng để chạy lệnh

str_command = python_exe & script_path'Run the python script
ChDir folder_path
objShell.Run str_command

Dòng cuối cùng này sẽ mở trình bao đối tượng và chạy lệnh của chúng tôi

2. Chạy tập lệnh đã nói lấy dữ liệu đầu vào từ excel

Đây là mã minh họa đơn giản, trong đó tôi sẽ sử dụng bộ dữ liệu lộ trình chuyến bay có sẵn trong Kaggle [9 cột và 67664 hàng] và chúng tôi sẽ thực hiện một bảng tổng hợp đơn giản sẽ hiển thị 10 sân bay hàng đầu có nhiều chuyến bay nối chuyến nhất

Tiêu đề tập dữ liệu trông như thế này

Đoạn mã tạo quy trình xử lý dữ liệu và tạo các bảng tóm tắt cuối cùng là đoạn mã sau

Chúng tôi nhận được đầu ra này

3. Viết đầu ra từ tập lệnh trong cùng một sổ làm việc Excel

Đối với điều này, chúng tôi sẽ sử dụng thư viện win32com, cho phép chúng tôi tương tác với các đối tượng cửa sổ từ python bằng mã VBA. Không phải theo cách tốt nhất có thể nhưng đó là một trong số ít các lựa chọn mà chúng tôi có

Điều quan trọng cần đề cập là chúng ta cần sử dụng lệnh ExcelApp. sách bài tập. Open[filepath] vì chúng tôi đang làm việc với tệp Excel hiện đang mở

Khi chúng tôi chỉ định phạm vi ô và gán giá trị cho các phạm vi đó, chúng tôi đã sử dụng mã VBA từ tập lệnh python của mình

• Trong dòng 17 và 19, chúng tôi đã sử dụng mã VBA để chỉ định phạm vi nơi các tiêu đề và dữ liệu sẽ được dán tương ứng trong excel
• Dòng 25. Tất cả các giá trị NaN phải được thay thế bằng dấu nháy đơn trước khi viết các giá trị trong excel
• Dòng 26 và 27. Trước khi ghi khung dữ liệu gấu trúc vào phạm vi excel, chúng ta cần chuyển đổi nó thành danh sách các bản ghi, nếu không chúng ta sẽ gặp lỗi nên hai dòng đó là cần thiết trước khi ghi các giá trị vào phạm vi excel ở dòng 28

Bây giờ chúng ta đã sẵn sàng, tôi hy vọng hướng dẫn nhỏ này sẽ giúp bạn tự động hóa ít nhất một phần công việc lặp đi lặp lại của mình bằng excel. Nếu có một số lỗi trong đoạn mã trên, tôi sẽ đánh giá cao phản hồi của bạn

Excel là một phước lành, nhưng nó cũng là một lời nguyền. Khi nói đến dữ liệu đủ nhỏ và các thao tác đủ đơn giản, Excel là vua. Tuy nhiên, một khi bạn thấy mình nỗ lực bên ngoài những khu vực đó, điều đó sẽ trở thành một nỗi đau. Chắc chắn rồi, bạn có thể sử dụng Excel VBA để giải quyết các sự cố như vậy, nhưng vào năm 2020, bạn có thể cảm ơn những ngôi sao may mắn của mình vì bạn không cần phải

Giá như có cách tích hợp Excel với Python để cho Excel… đôi cánh. Vâng, bây giờ có. Thư viện python có tên xlwings cho phép bạn gọi các tập lệnh Python thông qua VBA và truyền dữ liệu giữa hai tập lệnh

Tại sao nên tích hợp Python với Excel VBA?

Sự thật của vấn đề là bạn có thể làm bất cứ điều gì trong VBA. Vì vậy, nếu đúng như vậy, tại sao bạn lại muốn sử dụng Python?

1. Bạn có thể tạo một hàm tùy chỉnh trong Excel mà không cần học VBA [nếu bạn chưa biết]
2. Người dùng của bạn cảm thấy thoải mái trong Excel
3. Bạn có thể tăng tốc đáng kể hoạt động dữ liệu của mình bằng cách sử dụng Python
4. Có các thư viện dành cho mọi thứ trong Python [Machine Learning, Data Science, v.v.]
5. Bởi vì bạn có thể

Ảnh của Fotis Fotopoulos trên BaptThiết lập để sử dụng xlwings

Điều đầu tiên chúng ta cần làm, như với bất kỳ thư viện mới nào mà chúng ta muốn sử dụng, là cài đặt nó. Nó rất dễ làm; . Vì vậy, hãy tiếp tục và nhập vào thiết bị đầu cuối của bạn

Khi thư viện đã được tải xuống và cài đặt, chúng ta cần cài đặt phần tích hợp Excel. Đảm bảo bạn đã đóng tất cả các phiên bản Excel của mình và trong bất kỳ loại thiết bị đầu cuối nào

Giả sử bạn không gặp lỗi, bạn sẽ có thể tiếp tục. Tuy nhiên nhiều khi trên Win10 với Excel 2016 mọi người sẽ gặp lỗi sau

xlwings 0.17.0
[Errno 2] No such file or directory: 'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\xlwings.xlam'

Nếu bạn là một trong những người may mắn gặp phải lỗi trên, tất cả những gì bạn cần làm là tạo thư mục bị thiếu. Bạn có thể làm điều đó một cách dễ dàng bằng cách sử dụng lệnh mkdir. Trong trường hợp của tôi, tôi đã làm

mkdir C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART

Giả sử cài đặt thành công tích hợp excel với thư viện python, sự khác biệt chính mà bạn sẽ nhận thấy ngay là trong Excel

Kích hoạt chức năng do người dùng xác định cho xlwings

Đầu tiên, chúng ta cần tải Add-in Excel. Bạn có thể nhấn Alt, L, H rồi điều hướng đến thư mục trên để tải plugin. Khi bạn đã hoàn tất, bạn sẽ có thể thấy những điều sau đây

Cuối cùng, bạn cần Kích hoạt quyền truy cập Tin cậy vào mô hình đối tượng dự án VBA. Bạn có thể làm điều đó bằng cách điều hướng đến Tệp > Tùy chọn > Trung tâm tin cậy > Cài đặt trung tâm tin cậy > Cài đặt macro

Ảnh của Pakata Goh trên BaptBắt đầu với xlwings

Có hai cách chính để bạn có thể chuyển từ Excel sang Python [và ngược lại]. Cách đầu tiên là gọi tập lệnh Python trực tiếp từ VBA, trong khi cách còn lại là thông qua Hàm do người dùng xác định. Hãy để chúng tôi có một cái nhìn nhanh về cả hai

Để tránh nhầm lẫn và luôn thiết lập chính xác, xlwings đề nghị tạo bảng tính Excel của bạn, sẵn sàng hoạt động. Sau đó chúng ta hãy sử dụng chức năng này. Sử dụng thiết bị đầu cuối, chúng tôi điều hướng đến thư mục chúng tôi thích và nhập

xlwings quickstart ProjectName

Tôi đang gọi đây là MyFirstPythonXL. Lệnh trên sẽ tạo một thư mục mới trong thư mục được điều hướng trước của bạn với một trang tính Excel và tệp python

khai trương. xlsm, bạn sẽ ngay lập tức nhận thấy một trang tính Excel mới có tên _xlwings. conf. Nếu bạn muốn ghi đè cài đặt mặc định của xlwings, tất cả những gì bạn phải làm là đổi tên trang tính này và xóa dấu gạch dưới bắt đầu. Và với điều đó, tất cả chúng ta đã thiết lập và sẵn sàng bắt đầu sử dụng xlwings

VBA sang Python

Trước khi bắt đầu viết mã, trước tiên chúng ta hãy đảm bảo rằng tất cả chúng ta đều ở trên cùng một trang. Để hiển thị trình soạn thảo VBA Excel của chúng tôi, hãy nhấn Alt + F11. Điều này sẽ trả về màn hình sau

Trình soạn thảo VBA với xlwings

Điều quan trọng cần lưu ý ở đây là mã này sẽ làm như sau

1. Tìm Tập lệnh Python ở cùng vị trí với bảng tính
2. Tìm Tập lệnh Python có cùng tên với bảng tính [nhưng có. phần mở rộng py]
3. Từ Python Script, gọi hàm “main[]”

Không có gì khó chịu nữa, chúng ta hãy xem xét một vài ví dụ về cách sử dụng điều này

ví dụ 1. Thao tác bên ngoài Excel và trả lại Kết quả

Trong ví dụ này, chúng ta sẽ thấy cách bạn thực hiện các thao tác bên ngoài Excel, nhưng sau đó trả về kết quả của bạn trong bảng tính. Điều này có thể có vô số trường hợp sử dụng

Chúng tôi sẽ lấy dữ liệu từ tệp CSV, thực hiện sửa đổi dữ liệu đã nói, sau đó chuyển đầu ra sang Excel. Hãy xem lại nó dễ dàng như thế nào

Đầu tiên, mã VBA

Tôi đã để cái này hoàn toàn không thay đổi so với mặc định

Sau đó, mã Python

Kết quả nào sau đây

xlwings trong hành động

ví dụ 2. Sử dụng đầu vào Excel để thúc đẩy các hoạt động

Trong ví dụ này, chúng tôi sẽ đọc đầu vào từ Excel, thực hiện điều gì đó với nó trong Python, sau đó chuyển kết quả trở lại Excel

Cụ thể hơn, chúng ta sẽ đọc Lời chào, Tên và vị trí tệp nơi chúng ta có thể tìm thấy truyện cười. Sau đó, tập lệnh Python của chúng tôi sẽ lấy một dòng ngẫu nhiên từ tệp và trả lại cho chúng tôi một trò đùa

Đầu tiên, mã VBA

Tôi đã để cái này hoàn toàn không thay đổi so với mặc định

Sau đó, mã Python

Điều đó mang lại cho chúng ta

Hàm do người dùng xác định với xlwings

Theo cách tương tự như trước đây, chúng tôi sẽ thay đổi mã trong tệp python. Để biến một thứ gì đó thành Hàm do người dùng xác định trong Excel, tất cả những gì chúng ta cần làm là bao gồm '@xw. func’ trước dòng chức năng đang bật

Mã Python

Kết quả

Phần kết luận

Tôi nghĩ bạn sẽ đồng ý rằng đây là một thư viện nhỏ tiện lợi. Nếu bạn giống tôi và bạn thích làm việc với Python hơn là VBA nhưng cần làm việc trong bảng tính, thì đây có thể là một công cụ đặc biệt

Tôi có thể sử dụng Python trong VBA không?

Chúng ta có thể sử dụng Python trong macro Excel không?

Tôi có thể sử dụng mã Python trong Excel không?