Python trong Excel là một tính năng mạnh mẽ của Microsoft 365 cho phép bạn viết và chạy mã Python trực tiếp bên trong các ô Excel. Nó mang lại sức mạnh của phân tích Python vào Excel. Bạn gõ Python trực tiếp vào một ô, các phép tính Python chạy trên đám mây Microsoft, và kết quả được trả về bảng tính. Bạn có thể kết hợp giao diện quen thuộc và tính năng tính toán lại của Excel với hệ sinh thái phong phú của Python để phân tích dữ liệu, làm sạch, thống kê và trực quan hóa mà không cần rời khỏi bảng tính hoặc cài đặt bất kỳ thứ gì cục bộ.
Trong hướng dẫn này, chúng tôi sẽ chỉ cách sử dụng Python trong Excel và khi nào, cũng như làm thế nào nó hữu ích. Thay vì chuyển sang các công cụ khác như Jupyter hay VS Code, bạn có thể sử dụng Python trực tiếp trong Excel.
Yêu Cầu và Tính Sẵn Sàng
- Đăng ký Microsoft 365: Có sẵn trên nhiều gói trả phí (Family/Personal dành cho người dùng cá nhân, thương mại, giáo dục, doanh nghiệp). Một số tính năng hoặc khả năng tính toán cao hơn có thể yêu cầu tiện ích bổ sung.
- Nền tảng: Chủ yếu có sẵn trên Excel desktop Windows; hỗ trợ khác nhau cho web, Mac và di động. Không có sẵn trên Excel cho iPad, iPhone hoặc Android.
- Không Cần Python Cục Bộ: Mọi thứ chạy trên đám mây với các thư viện đã cài đặt sẵn. Bạn không cần phiên bản Python cục bộ để sử dụng Python trong Excel. Nếu bạn đã cài Python trên máy, bất kỳ tùy chỉnh nào của cài đặt đó sẽ không phản ánh trong tính toán Python trong Excel.
Quan trọng: Để sử dụng Python trong Excel, bạn cần truy cập internet. Điều này là do các phép tính Python trong Excel chạy trên đám mây Microsoft sử dụng phiên bản chuẩn của ngôn ngữ Python.
Bắt Đầu Sử Dụng Python Trong Excel
Kích Hoạt Python Trong Ô:
- Chọn một ô
- Đi đến tab Formulas >> chọn Insert Python
- Hoặc gõ =PY trong ô và nhấn Tab
- Thanh công thức chuyển sang màu xanh, cho biết chế độ Python
Hàm xl(): Cầu Nối Excel Và Python
Chìa khóa để sử dụng Python trong Excel là hàm xl(), cho phép mã Python đọc dữ liệu trực tiếp từ bảng tính của bạn:
- Trong chế độ chỉnh sửa, click và kéo để chọn ô hoặc phạm vi (ví dụ: A1:D100)
- Excel chèn tham chiếu như xl("A1:D100") hoặc tương tự
# Tham chiếu phạm vi dưới dạng DataFrame pandas
df = xl("A1:D100", headers=True)
# Tham chiếu giá trị ô đơn lẻ
target = xl("F1")
Đây là cách Python "nhìn thấy" dữ liệu bảng tính. Bạn viết điều này bên trong ô =PY() và làm việc với kết quả như một đối tượng Python bình thường.
Tùy Chọn Đầu Ra:
- Sử dụng menu thả xuống trên thanh công thức để trả về dưới dạng Excel Value (chuyển đổi thành ô/bảng Excel gốc) hoặc giữ lại Python Object (để nối chuỗi trong các ô Python khác)
- Nhấn Ctrl + Alt + Shift + M để chuyển đổi loại đầu ra
- Sử dụng print() để gỡ lỗi hoặc xuất trong một số trường hợp
Tính Toán Lại: Các ô Python tính toán lại tự động, giống các công thức khác, khi đầu vào thay đổi (ở chế độ tự động).
Mẹo: Bắt đầu từ nhỏ. Chọn dữ liệu, chèn Python, chuyển thành DataFrame, sau đó khám phá với .head(), .describe() hoặc các phép toán đơn giản.
Khi Nào Python Trong Excel Hữu Ích
1. Làm Sạch Và Chuyển Đổi Dữ Liệu Nâng Cao
Python dễ dàng sửa ngày tháng lộn xộn, chuẩn hóa văn bản (viết hoa, khoảng cách), xử lý giá trị null và trùng lặp, xoay dữ liệu rộng sang dạng dài, và quản lý định dạng không nhất quán hoặc giá trị thiếu. Pandas làm cho các tác vụ này ngắn gọn và có thể tái hiện.
Python:
import pandas as pd df = xl("A1:I56", headers=True) df.columns = df.columns.str.strip().str.title() # Sửa tên: Viết Hoa Chữ Cái Đầu df["Customer_Name"] = df["Customer_Name"].str.strip().str.title() # Sửa khu vực: Viết Hoa Chữ Cái Đầu df["Region"] = df["Region"].str.strip().str.title() # Chuẩn hóa sản phẩm: Viết Hoa Chữ Cái Đầu df["Product"] = df["Product"].str.strip().str.title() # Chuẩn hóa trạng thái và phản hồi df["Status"] = df["Status"].str.strip().str.capitalize() df["Feedback"] = df["Feedback"].str.strip().str.capitalize() # Phân tích tất cả định dạng ngày lộn xộn thành một df["Order_Date"] = pd.to_datetime(df["Order_Date"], dayfirst=True, errors="coerce") # Điền số lượng thiếu bằng trung vị df["Quantity"] = pd.to_numeric(df["Quantity"], errors="coerce") df["Quantity"] = df["Quantity"].fillna(df["Quantity"].median()) df - Click dấu tick hoặc nhấn Ctrl+Enter để chạy mã
- Click biểu tượng thẻ trên ô đầu ra >> chọn arrayPreview
DataFrame đã làm sạch tự động tràn trở lại bảng tính. Trả về kết quả dưới dạng giá trị Excel để sử dụng dữ liệu sạch cho phân tích và tính toán tiếp theo.
- Mở rộng menu thả xuống từ Thanh Công Thức >> chọn Excel Value
- Bây giờ sử dụng dữ liệu mới, đã làm sạch trong các ví dụ khác
2. Phân Tích Dữ Liệu Phức Tạp Và Thống Kê
Python cho phép các phép toán vượt quá hàm tích hợp của Excel, bao gồm nhóm phức tạp, chuyển đổi nhiều bước và tóm tắt thống kê. Nó cũng hỗ trợ phân tích chuỗi thời gian, hồi quy, phân cụm, phát hiện ngoại lai, phân tích cảm xúc trên văn bản và mô phỏng Monte Carlo.
Python:
import pandas as pd # Tải dữ liệu từ trang SalesData df = xl("SalesData!K1:S156", headers=True) df["Revenue"] = df["Quantity"] * df["Unit_Price"] # Tóm tắt doanh thu + cờ ngoại lai (> 2 độ lệch chuẩn) summary = df.groupby("Region")["Revenue"].agg(["sum", "mean", "std"]) summary["outlier_threshold"] = summary["mean"] + 2 * summary["std"] summary.round(2) Kết quả tràn trực tiếp trở lại trang tính dưới dạng bảng. Bạn cũng có thể sử dụng df.describe() để hiển thị thống kê tóm tắt cho toàn bộ DataFrame.
Python:
import pandas as pd # Tải dữ liệu từ trang SalesData df = xl("SalesData!K1:S156", headers=True) df["Revenue"] = df["Quantity"] * df["Unit_Price"] df.describe() 
3. Biểu Đồ Và Trực Quan Hóa Tốt Hơn
Dễ dàng tạo các biểu đồ chuyên nghiệp như biểu đồ mật độ, biểu đồ swarm, đám mây từ hoặc small multiples với Matplotlib, Seaborn hoặc plotnine. Chúng linh hoạt hơn nhiều so với biểu đồ Excel. Vượt qua biểu đồ gốc của Excel với matplotlib và seaborn:
Python:
import pandas as pd import matplotlib.pyplot as plt import seaborn as sns # Tải dữ liệu từ trang SalesData df = xl("SalesData!K1:S156", headers=True) df["Revenue"] = df["Quantity"] * df["Unit_Price"] fig, ax = plt.subplots(figsize=(8, 4)) sns.boxplot(data=df, x="Region", y="Revenue", palette="Set2", ax=ax) ax.set_title("Phân Phối Doanh Thu Theo Khu Vực") ax.set_xlabel("Khu Vực") ax.set_ylabel("Doanh Thu ($)") fig 
Biểu đồ hiển thị dưới dạng đối tượng hình ảnh trực tiếp trong bảng tính, có thể thay đổi kích thước và vị trí như bất kỳ biểu đồ Excel nào.
4. Machine Learning Trên Dữ Liệu Bảng Tính
Python cho phép mô hình dự báo và dự báo. Bạn có thể xây dựng dự báo chính xác hơn hoặc các mô hình machine learning đơn giản sử dụng statsmodels hoặc scikit-learn trực tiếp trên dữ liệu Excel.
Chạy mô hình dự báo mà không rời Excel:
Python:
import pandas as pd from sklearn.linear_model import LinearRegression # Tải dữ liệu từ trang SalesData df = xl("SalesData!K1:S156", headers=True) # Chuyển sang số và điền giá trị thiếu bằng trung vị df["Quantity"] = pd.to_numeric(df["Quantity"], errors="coerce") df["Unit_Price"] = pd.to_numeric(df["Unit_Price"], errors="coerce") df["Quantity"] = df["Quantity"].fillna(df["Quantity"].median()) df["Unit_Price"] = df["Unit_Price"].fillna(df["Unit_Price"].median()) # Tính Doanh Thu df["Revenue"] = df["Quantity"] * df["Unit_Price"] # Mã hóa One-hot cho Sản phẩm X = pd.get_dummies(df[["Product", "Unit_Price"]], drop_first=True) y = df["Revenue"] # Huấn luyện mô hình Hồi Quy Tuyến Tính model = LinearRegression().fit(X, y) # Thêm dự đoán df["Predicted_Revenue"] = model.predict(X).round(2) # Tạo kết quả cuối cùng với các cột hữu ích result = df[["Order_Id", "Product", "Revenue", "Predicted_Revenue"]].copy() result["Difference"] = (result["Revenue"] - result["Predicted_Revenue"]).round(2) # Tùy chọn: Hiển thị hiệu suất mô hình print("Model R² Score:", round(model.score(X, y), 4)) result 
Các nhà phân tích kinh doanh có thể áp dụng mô hình machine learning vào dữ liệu hiện có mà không cần chuyển sang môi trường riêng biệt.
5. Tính Toán Trượt Và Chuỗi Thời Gian
Tính trung bình động, tổng tích lũy hoặc đặc trưng lag rất khó chỉ dùng công thức Excel. Python đơn giản hóa các tác vụ này.
Python:
import pandas as pd # Tải toàn bộ dữ liệu bán hàng df = xl("SalesData!K1:S156", headers=True) # Tính Doanh Thu Ngày df["Revenue"] = df["Quantity"] * df["Unit_Price"] # Tính Toán Trượt (Chuỗi Thời Gian) # Sắp xếp theo ngày trước (quan trọng cho chuỗi thời gian) df = df.sort_values("Order_Date").reset_index(drop=True) # Chỉ Số Trượt 7 Ngày Trên Doanh Thu (xu hướng tuần) df["Revenue_7d_Mean"] = df["Revenue"].rolling(window=7, min_periods=1).mean().round(2) df["Revenue_7d_Sum"] = df["Revenue"].rolling(window=7, min_periods=1).sum().round(2) df["Revenue_7d_Max"] = df["Revenue"].rolling(window=7, min_periods=1).max().round(2) df["Revenue_7d_Std"] = df["Revenue"].rolling(window=7, min_periods=1).std().round(2) # Trung Bình Trượt 30 Ngày (xu hướng tháng) df["Revenue_30d_Mean"] = df["Revenue"].rolling(window=30, min_periods=1).mean().round(2) # Doanh Thu Tích Lũy (tổng chạy) df["Cumulative_Revenue"] = df["Revenue"].cumsum().round(2) # Chọn cột để hiển thị result = df[["Order_Id", "Order_Date", "Product", "Revenue", "Revenue_7d_Mean", "Revenue_7d_Sum", "Revenue_30d_Mean", "Cumulative_Revenue"]] result Cách tiếp cận này hữu ích để phân tích xu hướng bán hàng, làm mượt biến động ngày/tuần, phát hiện bất thường và dự báo đà.
- Revenue_7d_Mean: Trung bình động 7 ngày (làm mượt xu hướng bán hàng tuần)
- Revenue_7d_Sum: Tổng doanh thu trong 7 ngày qua
- Revenue_30d_Mean: Trung bình động 30 ngày (xu hướng dài hạn hơn)
- Cumulative_Revenue: Tổng chạy của tất cả doanh thu đến ngày đó
Mẹo Và Thực Hành Tốt Nhất
- Nối Chuỗi Ô: Tham chiếu các đối tượng Python trước đó (họ xuất hiện như biến) để xây dựng quy trình nhiều bước mà không làm rối một ô duy nhất
- Hiệu Suất: Đối với tính toán nặng, hãy chú ý hạn ngạch đám mây. Chia tác vụ lớn thành các bước hoặc sử dụng tính toán cao cấp nếu cần
- Bảo Mật: Mã chạy trong container đám mây cách ly. Tránh các hoạt động nhạy cảm; Microsoft quản lý quyền riêng tư
- Gỡ Lỗi: Sử dụng print() hoặc xuất DataFrame từng bước. Lỗi xuất hiện trong ô
- Kết Hợp Với Excel: Dùng Python cho phần nặng và Excel cho định dạng, PivotTable hoặc dashboard
- Đường Cong Học Tập: Nếu bạn mới với Python, hãy tập trung vào pandas trước. Nhiều hướng dẫn miễn phí và tệp mẫu có sẵn
- Chia Sẻ: Người nhận có Microsoft 365 tương thích có thể xem hoặc làm mới kết quả; nếu không, kết quả chuyển thành giá trị tĩnh
Hạn Chế Cần Biết
- Chỉ thực thi trên đám mây; cần kết nối internet
- Hạn ngạch tính toán trên gói tiêu chuẩn
- Môi trường Python do Anaconda quản lý; bạn không thể cài gói riêng hoặc dùng tùy chỉnh cục bộ
- Hỗ trợ di động và web hạn chế so với desktop
- Bộ dữ liệu quá lớn hoặc mã chạy quá lâu có thể bị timeout
Kết Luận
Python trong Excel hữu ích nhất khi công việc của bạn vẫn dựa trên bảng tính, nhưng phân tích trở nên nâng cao hơn. Nó xuất sắc ở làm sạch dữ liệu, phân tích kiểu DataFrame, thống kê nâng cao và trực quan hóa. Nó kém hữu ích cho các tác vụ bảng tính hàng ngày, mà Excel đã xử lý tốt. Python trong Excel cầu nối khoảng cách giữa người dùng bảng tính và nhà khoa học dữ liệu, làm cho phân tích nâng cao trở nên dễ tiếp cận mà không làm gián đoạn quy trình làm việc quen thuộc. Thay vì thay thế Excel bằng Python, hãy sử dụng Python có chọn lọc khi phân tích trở nên quá phức tạp cho các công thức đơn lẻ.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!