بیشتر افراد تصور میکنند برنامهنویسی به زبان پایتون در Excel قابلیتی است که باید برای تحلیلهای پیچیده داده یا هوش مصنوعی از آن استفاده کرد. اما من متوجه شدم کاربرد آن میتواند بسیار سادهتر باشد: انجام کارهای تکراری و خستهکنندهای که معمولاً انجامشان را به زمان دیگری موکول میکنم با پایتون ساده میشود.
در واقع جدا کردن نامهای نامرتب، مقایسه فهرستها و تبدیل اعداد به گزارشهای قابل فهم، بدون نیاز به فرمولهای پیچیده یا Power Query بسیار سادهتر میشود ولیکن باید با زبان پایتون آشنایی داشته باشید. در ادامهی این مقاله آموزشی سیارهی آیتی به نحوه استفاده از Python در اکسل میپردازیم.
Python در Excel چیست و چرا باید به آن اهمیت دهید؟
روشی سادهتر برای انجام کارهای دشوار در صفحات گسترده
Python مستقیماً در Excel ادغام شده است؛ بنابراین برای استفاده از این قابلیت نیازی به نصب جداگانه Python ندارید. هنگامی که یک فرمول Python را اجرا میکنید، Excel کد را در زیرساخت ابری مایکروسافت اجرا کرده و نتیجه را مستقیماً در سلولهای شما نمایش میدهد.
نکته مهم این است که Python در Excel برای کار با دادههای موجود در کاربرگ یا دادههایی که از طریق Power Query وارد شدهاند طراحی شده و بهصورت مستقیم به فایلهای موجود روی کامپیوتر شما دسترسی ندارد.
Python در Excel از یک محیط ارائهشده توسط Anaconda بهره میبرد که کتابخانههای محبوبی مانند pandas را در اختیار شما قرار میدهد. به همین دلیل، دستکاری و تحلیل دادههای ساختاریافته بدون نیاز به آمادهسازی پیچیده بسیار آسانتر میشود.
بهتر است Python در Excel را نه بهعنوان یادگیری یک زبان برنامهنویسی، بلکه بهعنوان ابزاری دیگر برای انجام کارهایی در صفحات گسترده در نظر بگیرید که حل آنها با فرمولهای معمولی دشوار است.
البته نوشتن اسکریپتهای اختصاصی Python به دانش برنامهنویسی نیاز دارد، اما برای شروع کار با Python در Excel الزاماً به چنین دانشی احتیاج ندارید. نمونههای زیر را میتوان متناسب با دادههای خود تغییر داد و عملکرد هر بخش از کد نیز مشخص خواهد شد.
برای امتحان کردن این قابلیت، به یک اشتراک واجد شرایط Microsoft 365 و مقداری داده در کاربرگ Excel نیاز دارید. تبدیل دادهها به Excel Table با میانبر Ctrl + T میتواند ارجاع دادن به دادهها در Python را سادهتر کند، هرچند امکان استفاده از محدودههای معمولی سلولها نیز وجود دارد.
برای شروع نوشتن کد Python، در یک سلول عبارت =PY( را وارد کنید یا از تب Formulas گزینه Insert Python را انتخاب کنید. سپس میتوانید با استفاده از xl("Table Name") یا xl("Cell References") دادههای کاربرگ را وارد محیط Python کنید. نتیجه نیز میتواند مستقیماً به سلولهای Excel بازگردانده شود.
Python مدیریت فهرست نامرتب مخاطبان را برایم سادهتر کرد
حتی موارد استثنا نیز بهراحتی قابل مدیریت هستند
یکی از کارهای صفحات گسترده که معمولاً انجام آن را به تعویق میانداختم، جدا کردن نام کامل افراد در دو ستون مجزا برای نام و نام خانوادگی بود.
در نگاه اول، این کار ساده به نظر میرسد؛ اما وقتی دادهها شامل حرف اول نام میانی، نامهای چندبخشی یا نام خانوادگی دارای خط تیره باشند، شرایط پیچیده میشود.
فرمولهای متنی معمولی مانند LEFT، RIGHT و FIND برای نمونههای ساده عملکرد خوبی دارند، اما وقتی نامها از الگوی یکسانی پیروی نمیکنند، منطق فرمولها بهسرعت پیچیده و نگهداری آنها دشوار میشود.
Power Query نیز گزینه دیگری است، اما هر بار که قالب نامها تغییر میکرد، لازم بود مراحل آن را اصلاح کنم.
Python به من اجازه داد قوانین موردنظر خودم را برای پاکسازی دادهها تعریف کنم. نمونه زیر از یک روش مبتنی بر قوانین ساده استفاده میکند و قرار نیست تمام الگوهای احتمالی نامگذاری را پوشش دهد:
import pandas as pd
df = xl("T_Names")
def split_name(name):
parts = name.split()
if "-" in parts[-1]:
return " ".join(parts[:-1]), parts[-1]
if len(parts) == 2:
return parts[0], parts[1]
return " ".join(parts[:-1]), parts[-1]
result = df.iloc[:, 0].apply(split_name)
pd.DataFrame(result.tolist(), columns=["First Name", "Last Name"])
از آنجا که در این نمونه به یک Excel Table ارجاع داده شده است، فرمول Python همچنان با دادههای بهروز جدول کار میکند. بنابراین اگر ردیف جدیدی به جدول اضافه کنید، نتیجه نیز بهصورت خودکار بهروزرسانی میشود.
این کد دقیقاً چه کاری انجام میدهد؟
| کد | عملکرد |
|---|---|
| import pandas as pd | کتابخانه استاندارد تحلیل داده برای کار با جداول را بارگذاری میکند. |
| df = xl("T_Names") | جدول Excel با نام T_Names را وارد Python میکند. |
| df.iloc[:, 0] |
نخستین ستون جدول واردشده را انتخاب میکند تا Python بتواند هر نام را بهصورت جداگانه پردازش کند. |
| def split_name(name): |
قوانین اختصاصی برای جدا کردن نام و نام خانوادگی را تعریف میکند و در عین حال نامهای چندبخشی و نامهای خانوادگی دارای خط تیره را حفظ میکند. |
| pd.DataFrame(..., columns=[...]) | نامهای جداشده را در قالب دو ستون مرتب برای نمایش در Excel قرار میدهد. |
Python دو فهرست را بدون دردسر پاکسازی معمول با یکدیگر مقایسه کرد
در یک نگاه ببینید چه مواردی اضافه، حذف یا بدون تغییر ماندهاند
هر زمان که نیاز داشتم فهرستهای قبل و بعد را با یکدیگر مقایسه کنم، معمولاً سراغ ستونهای کمکی، فرمولهای جستوجو یا Merge در Power Query میرفتم. همه این روشها کار میکردند، اما با بزرگتر شدن فهرستها، مدیریت آنها دشوارتر میشد.
در این نمونه، تنها چند خط کد Python کافی بود تا موارد اضافهشده، حذفشده یا بدون تغییر را میان دو فهرست موجودی شناسایی کنم. از آنجا که این روش از Set استفاده میکند، برای مقایسه موارد یکتا مناسبتر است و زمانی کاربرد دارد که نیازی به ردیابی موارد تکراری نداشته باشید:
import pandas as pd
old = set(xl("T_Old").iloc[:, 0])
new = set(xl("T_New").iloc[:, 0])
results = []
for item in sorted(old | new):
if item in old and item in new:
status = "Unchanged"
elif item in new:
status = "Added"
else:
status = "Removed"
results.append([item, status])
pd.DataFrame(results, columns=["Item", "Status"])
این کد چگونه کار میکند؟
| کد | عملکرد |
|---|---|
|
old = set(xl("T_Old").iloc[:, 0]) new = set(xl("T_New").iloc[:, 0]) |
موارد موجود در دو جدول Excel را وارد Python کرده و آنها را به Set تبدیل میکند تا مقایسه ورودیهای دو فهرست سادهتر شود. |
| sorted(old | new) |
هر دو مجموعه را با یکدیگر ترکیب کرده موارد یکتا را در یک فهرست کامل قرار میدهد و آنها را بهترتیب الفبایی مرتب میکند. |
| if item in old and item in new: status = "Unchanged" |
بررسی میکند که یک مورد در هر دو فهرست وجود دارد در این صورت وضعیت آن را «بدون تغییر» تعیین میکند. |
| elif item in new: status = "Added" |
مواردی را که فقط در فهرست جدید وجود دارند شناسایی کرده و وضعیت «اضافهشده» به آنها میدهد. |
| else: status = "Removed" |
مواردی را که فقط در فهرست قدیمی وجود دارند شناسایی کرده و وضعیت «حذفشده» را برای آنها ثبت میکند. |
| pd.DataFrame(results, columns=["Item", "Status"]) |
نتایج Python را به یک مجموعه داده جدید تبدیل میکند تا در کاربرگ Excel نمایش داده شود. |
پس از آن، از قابلیت Conditional Formatting در Excel برای مشخص کردن نتایج استفاده کردم. Python منطق مقایسه را انجام داد و ابزارهای داخلی Excel نیز خروجی نهایی را خواناتر کردند.
Python میتواند DataFrameهای خروجی را نیز قالببندی کند، اما برای یک گزارش ساده از وضعیت موارد، Conditional Formatting در Excel سریعترین روش برای مشخص کردن تغییرات بود.
Python باعث شد دیگر مجبور نباشم گزارش ماهانه را هر بار از ابتدا بنویسم
اعداد متغیر را به گزارشی تبدیل کنید که همراه با دادهها بهروزرسانی میشود
نوشتن گزارشهای ماهانه یکی از آن کارهای مربوط به صفحات گسترده بود که میدانستم باید انجامش دهم، اما هیچوقت از آن استقبال نمیکردم.
گزینههای معمول شامل محاسبه دستی تغییرات، انتقال اعداد به یک سند یا ساخت فرمولهای هرچه پیچیدهتر برای تبدیل اعداد به جملات قابل فهم بود. حتی میتوانستم از هوش مصنوعی برای نوشتن خلاصه گزارش کمک بگیرم، اما در آن صورت نیز باید بررسی میکردم که محاسبات و نتیجهگیریهای تولیدشده با دادههای واقعی مطابقت داشته باشند.
Python راهی در اختیارم گذاشت تا مستقیماً در همان Workbook، گزارشی تکرارپذیر و خودکار ایجاد کنم که بر اساس قوانین و محاسبات از پیش تعیینشده کار میکند.
کدی که استفاده کردم به این صورت بود:
import pandas as pd
df = xl("T_Budget")
df.columns = ["Category", "Last Year", "This Year"]
df["Change"] = df["This Year"] - df["Last Year"].
largest_up = df.loc[df["Change"].idxmax()]
largest_down = df.loc[df["Change"].idxmin()]
total_last = df["Last Year"].sum()
total_this = df["This Year"].sum()
pct = (total_this - total_last) / total_last * 100
summary = (
f"Household spending changed by {pct:.1f}% compared with last year. "
f"{largest_up['Category']} experienced the biggest increase, "
f"while {largest_down['Category']} decreased the most."
)
summary
بررسی بخشهای مختلف کد
| کد | عملکرد |
|---|---|
| df = xl("T_Budget") | جدول T_Budget را بهعنوان یک DataFrame از نوع pandas وارد Python میکند. |
| df.columns = ["Category", "Last Year", "This Year"] | نام ستونهای واردشده را تعیین میکند تا ارجاع دادن به آنها در کد سادهتر شود. |
| df["Change"] = df["This Year"] - df["Last Year"] |
اختلاف هر دسته را محاسبه میکند. افزایشها بهصورت اعداد مثبت و کاهشها بهصورت اعداد منفی نمایش داده میشوند. |
| .idxmax() / .idxmin() | دستههایی را که بیشترین افزایش و کاهش را داشتهاند بهصورت خودکار پیدا میکند. |
| f"Household spending changed..." | بر اساس نتایج محاسبات، یک خلاصه خوانا و قابل فهم ایجاد میکند. |
این تنها نمونهای ساده از قابلیتهای Python در Excel است. میتوان همین منطق را گسترش داد تا تغییرات تکتک دستهها، هشدارهای مربوط به هزینهها یا قالبهای مختلف گزارش را نیز بر اساس نوع گزارش ایجاد کند.
Python میتواند در کارهای روزمره Excel نیز مفید باشد
این نمونهها به من نشان دادند که Python در Excel لزوماً نباید فقط برای پروژههای پیچیده تحلیل داده مورد استفاده قرار بگیرد. این قابلیت میتواند ابزاری کاربردی برای انجام کارهایی باشد که پیش از این به دلیل تکراری، دشوار یا زمانبر بودن، انجام آنها با ابزارهای معمول صفحات گسترده آزاردهنده بود.
اگر میخواهید کاربردهای بیشتری از Python در Excel را امتحان کنید، میتوانید سراغ کارهایی مانند اصلاح فاصلههای نامنظم و حروف بزرگ و کوچک، یکسانسازی تاریخهای نامرتب، ایجاد نمودارها و تحلیل متن بروید.
در واقع، ارزش اصلی Python در Excel لزوماً در انجام محاسبات پیچیده نیست؛ بلکه در این است که میتواند بسیاری از کارهای تکراری و پردردسر را به فرایندهایی قابل تکرار و خودکار تبدیل کند.
سیارهی آیتی