Mở khóa Phân tích nâng cao:Python trong Excel tăng cường phân tích dữ liệu

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 sức mạnh của phân tích Python vào Excel. Bạn nhập Python trực tiếp vào một ô, các phép tính Python chạy trong Microsoft Cloud và kết quả của bạn sẽ được trả về trang tính. Bạn có thể kết hợp giao diện quen thuộc của Excel và tính toán lại với hệ sinh thái phong phú của Python để phân tích, làm sạch, thống kê và trực quan hóa dữ liệu mà không cần rời khỏi bảng tính hoặc cài đặt bất cứ thứ gì cục bộ.

Trong hướng dẫn này, chúng tôi sẽ chỉ ra cách sử dụng Python trong Excel cũng như thời điểm và cách thức sử dụng 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 có

• Đăng ký Microsoft 365: Có sẵn trên nhiều gói trả phí (Gia đình tiêu 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 điện 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 máy tính để bàn Windows Excel; hỗ trợ khác nhau cho web, Mac và thiết bị di động. Không có sẵn trên Excel dành cho iPad, Excel dành cho iPhone hoặc Excel dành cho Android.
• Không cần Python cục bộ: Mọi thứ đều chạy trên đám mây với các thư viện được 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 đặt phiên bản Python cục bộ trên máy tính của mình thì mọi tùy chỉnh bạn đã thực hiện cho bản cài đặt đó sẽ không được phản ánh trong Python trong các phép tính 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 trong Microsoft Cloud bằng phiên bản tiêu chuẩn của ngôn ngữ Python.

Bắt đầu sử dụng Python trong Excel

Bật Python trong ô:

• Chọn một ô
• Chuyển đến Công thức tab>> chọn Chèn Python
• Hoặc gõ =PY trong một ô và nhấn Tab
• Thanh công thức chuyển sang màu xanh lục, biểu thị chế độ Python

Hàm xl():Kết nối Excel và Python

Chìa khóa để sử dụng Python trong Excel là hàm xl(), hàm này cho phép mã Python đọc dữ liệu trực tiếp từ bảng tính của bạn:

• Khi ở chế độ chỉnh sửa, hãy nhấp và kéo để chọn một ô hoặc dải ô (ví dụ:A1:D100)
• Excel chèn một tham chiếu như xl(“A1:D100”) hoặc tương tự

# Tham chiếu một phạm vi dưới dạng DataFrame của gấu trúc
df =xl(“A1:D100”, headers=True)

# Tham chiếu một giá trị ô đơn
mục tiêu =xl(“F1”)

Đây là cách Python “nhìn thấy” dữ liệu bảng tính của bạn. Bạn viết nội dung này bên trong ô =PY() và xử lý kết quả như một đối tượng Python bình thường.

Tùy chọn đầu ra:

• Sử dụng danh sách thả xuống trong thanh công thức để trả về dưới dạng Giá trị Excel (chuyển đổi thành ô/bảng Excel gốc) hoặc giữ dưới dạng Đối tượng Python (để xâu 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 dữ liệu trong một số trường hợp

Tính toán lại: Các ô Python tự động tính toán lại, giống như các công thức khác, khi dữ liệu đầu vào thay đổi (ở chế độ tự động).

Mẹo: Bắt đầu nhỏ. Chọn dữ liệu của bạn, chèn Python, chuyển đổi nó thành DataFrame, sau đó khám phá bằng .head(), .describe() hoặc các thao tác đơn giản.

Khi Python trong Excel hữu ích

1. Làm sạch và chuyển đổi dữ liệu nâng cao

Python có thể dễ dàng sửa các ngày tháng lộn xộn, chuẩn hóa văn bản (viết hoa, giãn cách), xử lý các giá trị rỗng và trùng lặp, hủy xoay dữ liệu rộng sang định dạng dài và quản lý các định dạng không nhất quán hoặc các giá trị bị thiếu. Pandas làm cho các tác vụ này trở nên ngắn gọn và có thể lặp lại.

Python:

import pandas as pd
df = xl("A1:I56", headers=True)
df.columns = df.columns.str.strip().str.title()
# Fix names: Title Case
df["Customer_Name"] = df["Customer_Name"].str.strip().str.title()
# Fix region: Title Case
df["Region"] = df["Region"].str.strip().str.title()
# Standardize product: Title Case
df["Product"] = df["Product"].str.strip().str.title()
# Standardize status and feedback
df["Status"] = df["Status"].str.strip().str.capitalize()
df["Feedback"] = df["Feedback"].str.strip().str.capitalize()
# Parse all messy date formats into one
df["Order_Date"] = pd.to_datetime(df["Order_Date"], dayfirst=True, errors="coerce")
# Fill missing quantity with median
df["Quantity"] = pd.to_numeric(df["Quantity"], errors="coerce")
df["Quantity"] = df["Quantity"].fillna(df["Quantity"].median())
df

• Nhấp vào dấu kiểm hoặc nhấn Ctrl+Enter để chạy mã
• Nhấp vào biểu tượng thẻ trên ô đầu ra>> chọn arrayPreview

DataFrame đã được làm sạch sẽ tự động tràn trở lại trang tính. Trả về kết quả dưới dạng giá trị Excel để sử dụng dữ liệu sạch cho các phân tích và tính toán sâu hơn.

• Mở rộng danh sách thả xuống từ Thanh công thức>> chọn Giá trị Excel

• Bây giờ hãy sử dụng dữ liệu mới, đã được làm sạch trong các ví dụ khác

2. Phân tích và thống kê dữ liệu phức tạp

Python cho phép thực hiện các thao tác ngoài các hàm dựng sẵn của Excel, bao gồm các nhóm phức tạp, phép biế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 lệ, phân tích cảm tính về văn bản và mô phỏng Monte Carlo.

Python:

import pandas as pd
# Load data from the SalesData sheet
df = xl("SalesData!K1:S156", headers=True)
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
# Revenue summary + outlier flag (> 2 std devs)
summary = df.groupby("Region")["Revenue"].agg(["sum", "mean", "std"])
summary["outlier_threshold"] = summary["mean"] + 2 * summary["std"]
summary.round(2)

Kết quả sẽ tràn trực tiếp vào trang tính của bạn dưới dạng bảng. Bạn cũng có thể sử dụng df.describe() để hiển thị số liệu thống kê tóm tắt cho toàn bộ DataFrame.

Python:

import pandas as pd
# Load data from the SalesData sheet
df = xl("SalesData!K1:S156", headers=True)
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
df.describe()

3. Biểu đồ và hình ảnh trực quan tốt hơn

Thật dễ dàng để tạo các ô chuyên nghiệp như ô mật độ, ô bầy đàn, đám mây từ hoặc bội số nhỏ bằng Matplotlib, Seaborn hoặcplotnine. Chúng linh hoạt hơn nhiều so với biểu đồ Excel. Vượt xa các 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
# Load data from the SalesData sheet
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("Revenue Distribution by Region")
ax.set_xlabel("Region")
ax.set_ylabel("Revenue ($)")
fig

Biểu đồ hiển thị trực tiếp dưới dạng đối tượng hình ảnh trong trang tính, có thể thay đổi kích thước và định vị lại giống như bất kỳ biểu đồ Excel nào.

4. Học máy trên dữ liệu bảng tính của bạn

Python cho phép lập mô hình dự đoán và dự báo. Bạn có thể xây dựng các dự báo chính xác hơn hoặc các mô hình máy học đơn giản bằng cách sử dụng mô hình thống kê hoặc scikit-learn trực tiếp trên dữ liệu Excel của mình.

Chạy mô hình dự đoán mà không cần rời khỏi Excel:

Python:

import pandas as pd
from sklearn.linear_model import LinearRegression
# Load data from the SalesData sheet
df = xl("SalesData!K1:S156", headers=True)
# Convert to numeric and fill missing values with median
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())
# Calculate Revenue
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
# One-hot encode Product
X = pd.get_dummies(df[["Product", "Unit_Price"]], drop_first=True)
y = df["Revenue"]
# Fit the Linear Regression model
model = LinearRegression().fit(X, y)
# Add predictions
df["Predicted_Revenue"] = model.predict(X).round(2)
# Create final result with useful columns
result = df[["Order_Id", "Product", "Revenue", "Predicted_Revenue"]].copy()
result["Difference"] = (result["Revenue"] - result["Predicted_Revenue"]).round(2)
# Optional: Show model performance
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 học máy cho dữ liệu hiện có của họ mà không cần chuyển sang môi trường riêng.

5. Tính toán cuộn và chuỗi thời gian

Việc tính toán các đường trung bình động, tổng tích lũy hoặc các tính năng trễ chỉ sử dụng các công thức Excel là khó khăn. Python đơn giản hóa các tác vụ này.

Python:

import pandas as pd
# Load your full sales data
df = xl("SalesData!K1:S156", headers=True)
# Calculate Daily Revenue
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
# Rolling Calculations (Time Series)
# Sort by date first (important for time series)
df = df.sort_values("Order_Date").reset_index(drop=True)
# 7-day Rolling Metrics on Revenue (weekly trend)
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)
# 30-day Rolling Mean (monthly trend)
df["Revenue_30d_Mean"] = df["Revenue"].rolling(window=30, min_periods=1).mean().round(2)
# Cumulative Revenue (running total)
df["Cumulative_Revenue"] = df["Revenue"].cumsum().round(2)
# Select columns to display
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, điều chỉnh các biến động hàng ngày hoặc hàng tuần, phát hiện những điểm bất thường và dự báo động lượng.

• Doanh thu_7d_Mean: Đường trung bình động 7 ngày (làm dịu xu hướng bán hàng hàng tuần)
• Doanh thu_7d_Sum: Tổng doanh thu trong 7 ngày qua
• Doanh thu_30d_Mean: Đường trung bình động 30 ngày (xu hướng dài hạn)
• Doanh thu tích lũy: Tổng doanh thu hiện tại tính đến ngày đó

Mẹo và phương pháp hay nhất

• Tế bào chuỗi: Tham chiếu các đối tượng Python trước đó (chúng xuất hiện dưới dạng biến) để xây dựng quy trình làm việc gồm nhiều bước mà không làm lộn xộn một ô
• Hiệu suất: Đối với các tính toán nặng, hãy chú ý đến hạn ngạch trên đám mây. Chia các nhiệm vụ lớn thành các bước hoặc sử dụng điện toán cao cấp nếu cần
• Bảo mật: Mã chạy trong các vùng chứa đám mây bị cô lập. 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 DataFrames từng bước. Lỗi xuất hiện trong ô
• Kết hợp với Excel: Sử dụng Python để xử lý công việc nặng nhọc và Excel để định dạng, tổng hợp hoặc bảng điều khiển
• Đường cong học tập: Nếu bạn mới làm quen với Python, trước tiên hãy tập trung vào gấu trúc. Có sẵn nhiều hướng dẫn và tệp mẫu miễn phí
• 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ả sẽ được chuyển đổi thành giá trị tĩnh

Những hạn chế cần biết

• Thực thi chỉ trên đám mây; cần có kết nối internet
• Tính hạn ngạch cho các gói tiêu chuẩn
• Môi trường Python được Anaconda quản lý; bạn không thể cài đặt các gói của riêng mình hoặc sử dụng các tùy chỉnh cục bộ
• Hỗ trợ trên thiết bị di động và web bị hạn chế so với máy tính để bàn
• Các tập dữ liệu rất lớn hoặc mã chạy dài có thể hết thời gian chờ

Kết luận

Python trong Excel hữu ích nhất khi công việc của bạn về cơ bản vẫn dựa trên bảng tính nhưng bản thân việc phân tích ngày càng trở nên nâng cao hơn. Nó vượt trội trong việ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ó ít hữu ích hơn cho các tác vụ bảng tính hàng ngày mà Excel đã xử lý tốt. Python trong Excel thu hẹp khoảng cách giữa người dùng bảng tính và nhà khoa học dữ liệu, giúp có thể truy cập các phân tích nâng cao mà không làm gián đoạn quy trình công việc quen thuộc. Thay vì thay thế Excel bằng Python, hãy sử dụng Python một cách có chọn lọc khi việc phân tích trở nên quá phức tạp đối với riêng các công thức.