home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> ซอฟต์แวร์ >> Office

5 วิธีอันทรงพลังในการรวม Excel และ Python สำหรับขั้นตอนการทำงานข้อมูลขั้นสูง

Excel เป็นเครื่องมือจัดการและวิเคราะห์ข้อมูลที่ทรงพลัง เหมาะสำหรับรายงานและการคำนวณแบบรวดเร็ว แต่ Python จะช่วยได้เมื่องานซับซ้อน ซ้ำซ้อน หรือมีขนาดใหญ่เกินไป Python เปิดโอกาสให้มีการทำงานอัตโนมัติ การวิเคราะห์ขั้นสูง และความเป็นไปได้ในการผสานรวมที่เกินกว่าคุณสมบัติในตัวของ Excel ไลบรารี Python เช่น pandas สำหรับการจัดการข้อมูล และ openpyxl สำหรับการจัดการไฟล์ Excel โดยตรง ทำให้การเชื่อมต่อนี้เป็นไปอย่างราบรื่น

ในบทช่วยสอนนี้ เราจะแสดง 5 สิ่งที่คุณสามารถทำได้ด้วย Excel + Python

พิจารณาข้อมูลการขายตัวอย่างเพื่อสำรวจ 5 สิ่งที่คุณสามารถทำได้กับ Excel และ Python

1. ทำความสะอาดและมาตรฐานข้อมูล Excel ที่ lộนล้อน (ทำซ้ำได้)

การมีข้อมูลที่ lộนล้อนใน Excel เป็นเรื่องปกติ เนื่องจากข้อมูลในโลกความเป็นจริงมักไม่สะอาด บ่อยครั้งข้อมูลประกอบด้วยช่องว่างเพิ่มเติม ตัวพิมพ์เล็ก-ใหญ่ปนกัน ตัวเลขที่จัดเก็บเป็นข้อความ รูปแบบไม่สอดคล้อง ค่าที่หายไป รายการซ้ำ หรือข้อมูลที่ต้องการโครงสร้างใหม่ก่อนการวิเคราะห์ ปัญหานี้ทำลายสูตรและการวิเคราะห์

Python เก่งเรื่องงานทำความสะอาดข้อมูล คุณสามารถเขียนสคริปต์ที่ทำให้รูปแบบข้อมูลมาตรฐานทั่วไฟล์ต่างๆ เติมค่าที่หายไปด้วยวิธีอัจฉริยะ ลบรายการซ้ำ แยกหรือรวมคอลัมน์ตามรูปแบบ และตรวจสอบข้อมูลตามกฎทางธุรกิจ ขั้นตอนเหล่านี้อาจใช้เวลาหลายชั่วโมงในการค้นหาและแทนที่ด้วยตนเองใน Excel แต่ด้วย Python คุณสามารถสร้างสคริปต์ที่ทำซ้ำได้ซึ่งประมวลผลหลายพันแถวในไม่กี่วินาที

สมมติว่าคุณได้รับข้อมูลการขายที่ lộนล้อน มาใช้สคริปต์ Python ที่อ่านข้อมูลที่ lộนล้อน ทำความสะอาดและมาตรฐานคอลัมน์ และเพิ่มฟิลด์ที่คำนวณแล้ว

  • Revenue = Units * UnitPrice
  • NetRevenue = Revenue * (1 – DiscountPct)
import pandas as pd
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="Sales Data")
# Clean types
df["Units"] = pd.to_numeric(df["Units"], errors="coerce").fillna(0).astype(int)
df["UnitPrice"] = pd.to_numeric(df["UnitPrice"], errors="coerce").fillna(0.0)
df["DiscountPct"] = pd.to_numeric(df["DiscountPct"], errors="coerce").fillna(0.0)
df["Returned"] = (
    df["Returned"].astype(str).str.strip().str.lower()
    .map({"yes": True, "no": False})
    .fillna(False)
)
# Add calculated fields
df["Revenue"] = df["Units"] * df["UnitPrice"]
df["NetRevenue"] = df["Revenue"] * (1 - df["DiscountPct"])
# Write back into the SAME file as a NEW sheet
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    df.to_excel(writer, sheet_name="CleanData", index=False)
print("Saved CleanData sheet inside:", file_path)

คุณจะได้ชีทใหม่พร้อมชุดข้อมูลที่สะอาดซึ่งจะไม่ทำลายพิโวต ชาร์ต หรือการค้นหา ใน Excel คุณสามารถยังคงใช้พิโวต/ชาร์ตจากข้อมูลที่ทำความสะอาดแล้ว ได้โดยรู้ว่ามันสม่ำเสมอทุกครั้งที่รัน

2. สร้างสรุปข้อมูลโดยอัตโนมัติ (รายงานที่ทำซ้ำได้)

Excel มีขีดจำกัดแถวและอาจช้าลงเมื่อการคำนวณซับซ้อน ไลบรารี pandas ของ Python จัดการชุดข้อมูลขนาดใหญ่ได้อย่างมีประสิทธิภาพและทำการคำนวณเร็วกว่ามาก

ด้วย pandas คุณสามารถทำงานกับชุดข้อมูลที่มีหลายล้านเรคคอร์ด ดำเนินการจัดกลุ่มและรวมข้อมูลที่ซับซ้อน และรันการวิเคราะห์ทางสถิติที่อาจเป็นไปไม่ได้ใน Excel Python สามารถสร้างสรุปแบบพิโวตและส่งออกไปยัง Excel สมมติว่าคุณต้องการสรุปเร็วๆ ตาม Region และ Category แต่ไม่อยากสร้างพิโวตใหม่ทุกครั้ง

import pandas as pd
file_path = "SalesData.xlsx"
clean_sheet = "CleanData"
out_sheet = "Summary"
df = pd.read_excel(file_path, sheet_name=clean_sheet)
summary = (
    df.groupby(["Region", "Category"], as_index=False)
    .agg(
        Orders=("OrderID", "count"),
        Units=("Units", "sum"),
        NetRevenue=("NetRevenue", "sum"),
        Returns=("Returned", "sum")
    )
)
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    summary.to_excel(writer, sheet_name=out_sheet, index=False)
print(f"✅ Saved '{out_sheet}' sheet inside: {file_path}")

คุณจะได้สรุปรายได้ตามภูมิภาค—ชีทแบบพิโวตที่พร้อมแชร์ซึ่งอัปเดตทุกครั้งที่คุณรันสคริปต์ใหม่

3. สร้างชาร์ตจากข้อมูล Excel (โดยไม่ต้องจัดรูปแบบด้วยตนเอง)

ชาร์ตมักเป็นส่วนที่ใช้เวลามากที่สุดในการรายงาน Excel มีชาร์ตมาตรฐาน แต่ไลบรารีการแสดงผลของ Python เช่น Matplotlib, Seaborn และ Plotly ให้ความยืดหยุ่นและความซับซ้อนมากกว่ามาก คุณสามารถสร้างการแสดงผลแบบกำหนดเองที่อัปเดตอัตโนมัติเมื่อข้อมูลเปลี่ยน สร้างแดชบอร์ดแบบโต้ตอบที่ผู้ใช้สำรวจได้ หรือผลิตกราฟิกคุณภาพเผยแพร่ด้วยการควบคุมทุกรายละเอียดอย่างแม่นยำ

ลองแสดงภาพประสิทธิภาพตามภูมิภาค (NetRevenue ตาม Region)

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
file_path = "SalesData.xlsx"
source_sheet = "CleanData"
output_sheet = "RegionChart"
# data + chart in this one sheet
# Prepare chart data (NetRevenue by Region)
df = pd.read_excel(file_path, sheet_name=source_sheet)
chart_data = (
    df.groupby("Region", as_index=False)["NetRevenue"]
    .sum()
    .sort_values("NetRevenue", ascending=False)
)
# Write chart data into output_sheet (same workbook)
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    chart_data.to_excel(writer, sheet_name=output_sheet, index=False)
# Add the native Excel chart on the same sheet
wb = load_workbook(file_path)
ws = wb[output_sheet]
chart = BarChart()
chart.title = "Net Revenue by Region"
chart.y_axis.title = "Net Revenue"
chart.x_axis.title = "Region"
values = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
labels = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(labels)
ws.add_chart(chart, "D2")  # place chart to the right of the data table
wb.save(file_path)
print(f"✅ Chart Created: {output_sheet}")

ตอนนี้คุณมีสรุปรายได้ตามภูมิภาคพร้อมกับแผนภูมิแท่ง

4. รวมไฟล์ Excel หลายไฟล์เป็นตารางหลักเดียว

การรวมข้อมูลรายสัปดาห์ รายเดือน หรือรายไตรมาสจากแหล่งต่างๆ เป็นเรื่องปกติ การรวมไฟล์ Excel จากคนหรือทีมต่างๆ ช้าและพลาดได้ง่าย Python สามารถรวมได้ในไม่กี่วินาทีและติดตามไฟล์ต้นทาง

ลองรวมไฟล์รายสัปดาห์ในโฟลเดอร์ชื่อ Weekly Reports/ (ทั้งหมดมีคอลัมน์เหมือนกัน)

import pandas as pd
from pathlib import Path
base_folder = Path(__file__).resolve().parent
folder = base_folder / "Weekly Reports"
files = sorted(folder.glob("*.xlsx"))
files = [f for f in files if not f.name.startswith("~$")]  # ignore Excel lock files
print("Looking in:", folder)
print("Files found:", [f.name for f in files])
frames = []
for f in files:
    temp = pd.read_excel(f)
    temp["SourceFile"] = f.name
    frames.append(temp)
master = pd.concat(frames, ignore_index=True)
master.to_excel(base_folder / "master_report.xlsx", index=False)
print("Saved: master_report.xlsx")

คุณจะได้ตารางรวมพร้อมคอลัมน์ SourceFile สำหรับการตรวจสอบ ทุกสัปดาห์คุณเพียงแค่รันสคริปต์

5. ทำนายสิ่งที่ Excel ทำได้ยาก (ตัวอย่าง Machine Learning)

คุณสามารถประมาณความเสี่ยงการคืนสินค้าโดยใช้รูปแบบ (ส่วนลด หมวดหมู่ จำนวน ราคา) แล้วเขียนความน่าจะเป็นกลับไป เพื่อให้ผู้ใช้ Excel กรองและจัดเรียงได้ Python สามารถทำการดำเนินการ Machine Learning แบบนี้ได้ง่าย

ชุดข้อมูลของเราเล็ก อยู่ดี แสดง workflow

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import OneHotEncoder
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.linear_model import LogisticRegression
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="CleanData")
X = df[["Region", "SalesRep", "Category", "Units", "UnitPrice", "DiscountPct"]]
y = df["Returned"].astype(int)
cat_cols = ["Region", "SalesRep", "Category"]
num_cols = ["Units", "UnitPrice", "DiscountPct"]
preprocess = ColumnTransformer(
    transformers=[
        ("cat", OneHotEncoder(handle_unknown="ignore"), cat_cols),
        ("num", "passthrough", num_cols),
    ]
)
model = Pipeline(steps=[
    ("prep", preprocess),
    ("clf", LogisticRegression(max_iter=1000))
])
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)
model.fit(X_train, y_train)
# Predict probability of return for all rows
df["ReturnProb"] = model.predict_proba(X)[:, 1]
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    df.to_excel(writer, sheet_name="WithReturnRisk", index=False)

ใน Excel สำรวจ WithReturnRisk และกรอง ReturnProb จากสูงไปต่ำ เพื่อดูว่าคำสั่งซื้อไหนดูเสี่ยง

Python in Excel (ถ้ามีใน Excel ของคุณ)

ถ้า Excel บนคอมพิวเตอร์ของคุณมี Python (Preview) คุณสามารถรัน Python โดยตรงในเซลล์และส่งผลลัพธ์กลับไปที่ชีท (ดูภาพรวม Python in Excel ของ Microsoft) นี่คือตัวอย่างง่ายๆ ที่อ่านช่วงเล็กๆ ทำความสะอาดข้อความ คำนวณ Revenue และส่งคืนตารางที่สะอาด

  • ใน Excel ใส่ชุดข้อมูลของคุณ
  • คลิกเซลล์ว่าง
  • ไปที่แท็บ Formulas >> เลือก Insert Python
  • วางสคริปต์ Python
import pandas as pd
# Read the Excel range A1:J21 (including headers)
df = xl("A1:J21", headers=True)
# Clean text columns
for col in ["Region", "SalesRep"]:
    df[col] = df[col].astype(str).str.strip().str.title()
# Fix data types
df["OrderDate"] = pd.to_datetime(df["OrderDate"], errors="coerce")
df["Units"] = pd.to_numeric(df["Units"], errors="coerce").fillna(0).astype(int)
df["UnitPrice"] = pd.to_numeric(df["UnitPrice"], errors="coerce").fillna(0.0)
# Add a calculated column
df["Revenue"] = df["Units"] * df["UnitPrice"]
df

มันจะคืนค่า DataFrame ซึ่งเป็นออบเจ็กต์ตารางของ Python Excel แสดงเป็นตัวอย่างตาราง (และการ์ด)

ตอนนี้ "spill" เอาต์พุตลงในเซลล์เป็นตารางที่สะอาด

  • คลิก Insert Data จาก DataFrame >> เลือก Show DataType Card เพื่อดูตัวอย่างตาราง
  • เลือก arrayPreview เพื่อนำตารางเข้าสู่ Excel
  • ตอนนี้คุณมีข้อความที่มาตรฐานและคอลัมน์ Revenue ใหม่

สรุป

บทความนี้แสดง 5 สิ่งที่คุณสามารถทำได้กับ Excel + Python Excel กลายเป็นทรงพลังมากขึ้นด้วย Python ทำให้การทำความสะอาดชุดข้อมูลที่ lộนล้อน สร้างสรุปแบบพิโวต ทำชาร์ตอัตโนมัติ รวมไฟล์ Excel หลายไฟล์ และเพิ่มข้อมูลเชิงลึก Machine Learning ง่ายขึ้น การรวม Excel และ Python ปรับปรุงขั้นตอนการทำงาน ตั้งแต่การนำเข้า/ส่งออกข้อมูล ไปจนถึงการทำงานอัตโนมัติและการแสดงผล เริ่มต้นด้วยสคริปต์เล็กๆ และทดลองกับไลบรารีเพิ่มเติม