=PY( df = xl("SalesData!A1:F200000", headers=True); summary = df.groupby(["Year", "Region"]).agg( Total_Sales = ("Amount", "sum"), Avg_Order = ("Amount", "mean"), Transaction_Count = ("OrderID", "nunique") ).reset_index(); summary ) You get a compact aggregated table ready for reporting. Need to run a regression or forecast next quarter? Scikit-learn and statsmodels work inside Excel:
The xl() function pulls Excel ranges into a pandas DataFrame. After processing, Python returns the result – which can be a single value, a DataFrame (automatically spilled into cells), or a plot. 1. Rapid Data Cleaning (Seconds, Not Hours) Manually cleaning messy data is a nightmare. With pandas: Excel Python- fei su gao ding shu ju fen xi yu chu li
=PY( df = xl("A1:G10000", headers=True); # Remove duplicates df = df.drop_duplicates(); # Fill missing values with median df["Price"] = df["Price"].fillna(df["Price"].median()); # Standardize text df["Product"] = df["Product"].str.strip().str.lower(); df ) The cleaned DataFrame spills back into your grid instantly – 10,000 rows processed in under 1 second. VLOOKUP/XLOOKUP are great for one match. But merging three tables with different keys? Python’s merge() is your friend. =PY( df = xl("SalesData
=PY( from sklearn.linear_model import LinearRegression import numpy as np df = xl("HistoricalData!A1:B100", headers=True); X = df[["Month"]].values; y = df["Sales"].values; model = LinearRegression().fit(X, y); prediction = model.predict([[13]]) # next month prediction[0] ) Result appears in the cell – 95, 103.2, whatever your model predicts. No need to export. Excel charts are decent but limited. Python’s seaborn creates publication-quality plots directly in the worksheet: After processing, Python returns the result – which
=PY( orders = xl("Orders!A1:D5000", headers=True); customers = xl("Customers!A1:C2000", headers=True); products = xl("Products!A1:B1000", headers=True); merged = orders.merge(customers, on="CustomerID").merge(products, on="ProductID"); merged["TotalValue"] = merged["Quantity"] * merged["UnitPrice"]; merged ) One line of code replaces dozens of helper columns and volatile array formulas. Excel pivot tables are interactive but slow on large data. Python’s groupby + agg gives you the same results instantly: