Excel × Python── Running pandas from cells with =PY() and xl(), and when to use openpyxl locally instead

# Python in Excel cell (=PY). Table name: RequestLedger
df = xl("RequestLedger[#All]", headers=True)

# Count requests and total findings by document type
summary = (
    df.groupby("種別")
      .agg(件数=("依頼ID", "count"), 指摘件数計=("指摘件数", "sum"))
      .sort_values("件数", ascending=False)
)
summary

# Python in Excel cell (=PY), output type: Excel value
df = xl("RequestLedger[#All]", headers=True)

by_dept = (
    df.groupby("依頼部署", as_index=False)   # department stays a column
      .agg(件数=("依頼ID", "count"), 平均指摘=("指摘件数", "mean"))
      .round({"平均指摘": 1})
)
by_dept

# --- Cell A1 (=PY, Python object): load and clean once ---
df = xl("RequestLedger[#All]", headers=True)
df["依頼日"] = pd.to_datetime(df["依頼日"])
df["完了日"] = pd.to_datetime(df["完了日"])
df

# --- Cell A2 (=PY, Excel value): calculated after A1 (row-major order) ---
done = df[df["状態"] == "完了"].copy()
done["日数"] = (done["完了日"] - done["依頼日"]).dt.days
done.groupby("種別")["日数"].median()

# Python in Excel cell (=PY): which optional libraries does this runtime provide?
import importlib.util

wanted = ["bs4", "sklearn", "scipy", "networkx"]
found = {name: importlib.util.find_spec(name) is not None for name in wanted}
pd.Series(found, name="available")

# Python in Excel cell (=PY)
# "RequestsQuery" is a Power Query connection (Load To... > Only Create Connection).
# pd.read_csv("C:/...") would not work here: the code has no file or network access.
df = xl("RequestsQuery")

open_items = df[df["状態"] != "完了"]
open_items.groupby("担当者")["依頼ID"].count().rename("未完了件数")

Review sheet (row 2; fill down for more cells)
A2: Sheet1!C1
B2: =FORMULATEXT(Sheet1!C1)
C2: =IF(RIGHT(B2,3)=",1)","Python object",IF(RIGHT(B2,3)=",0)","Excel value","check"))

Example of the text returned in B2 (syntax from the PY function reference):
=PY("xl(""Table1[#All]"", headers=True)",1)

from openpyxl import load_workbook

SRC = "samples/request_ledger.xlsx"        # input, not modified
AFTER = "samples/excel_ledger_after.xlsx"  # has a formula sheet saved by openpyxl

# 1) read_only: stream rows with low memory, then close explicitly
wb = load_workbook(SRC, read_only=True)
ws = wb.worksheets[0]
header = next(ws.iter_rows(max_row=1, values_only=True))
status_col = header.index("状態")
open_count = sum(1 for r in ws.iter_rows(min_row=2, values_only=True) if r[status_col] != "完了")
wb.close()
print("open requests:", open_count)

# 2) formula text vs stored value
formula = load_workbook(AFTER)["集計"]["B2"].value
stored = load_workbook(AFTER, data_only=True)["集計"]["B2"].value
print("formula:", formula)
print("stored value:", stored)  # None: openpyxl wrote the formula but never calculated it

import pandas as pd

SRC = "samples/request_ledger.xlsx"
OUT = "out_ledger_summary.xlsx"   # new file; the source is never overwritten

df = pd.read_excel(SRC, sheet_name=0)
df["依頼月"] = pd.to_datetime(df["依頼日"]).dt.strftime("%Y-%m")

by_type = df.groupby(["種別", "状態"])["依頼ID"].count().unstack(fill_value=0)
by_month = (df.groupby("依頼月", as_index=False)
              .agg(件数=("依頼ID", "count"), 指摘件数計=("指摘件数", "sum")))

with pd.ExcelWriter(OUT, engine="openpyxl") as writer:
    by_type.to_excel(writer, sheet_name="種別x状態")
    by_month.to_excel(writer, sheet_name="月別", index=False)

# Read back and reconcile with the source
check = pd.read_excel(OUT, sheet_name=None)
print("sheets:", list(check))
print(by_month.to_string(index=False))
print("rows in source:", len(df), "| total in 月別:", int(check["月別"]["件数"].sum()))