نحوه استفاده از پایتون در اکسل – آموزش کامل به زبان ساده

به‌تازگی در نسخه جدید مایکروسافت «اکسل» (Excel) این امکان برای کاربران ایجاد شده است تا کدهای پایتون را به‌طور مستقیم در نوار فرمول «صفحه گسترده» (Spreadsheet) اکسل نوشته و اجرا کنند. با استفاده از این قابلیت، می‌توانیم ویژگی‌های قدرتمند زبان برنامه‌نویسی پایتون در تحلیل داده‌ و مصور‌سازی آن را در محیط اکسل به‌طور مستقیم به‌کار ببریم. در این مطلب از مجله فرادرس سعی بر این بوده است تا مقدمه‌ای کامل در مورد نحوه استفاده از پایتون در اکسل را شرح دهیم.

اکسل، حاوی ویژگی‌هایی از علم داده است که می‌تواند برای انجام کارهایی همچون «مصورسازی» (Visualizing) اطلاعات کسب و کار و یافتن مقدار میانگین خریدهای مشتری مورد استفاده قرار بگیرد. پشتیبانی اکسل از پایتون، که به‌تازگی معرفی شده است، ویژگی‌های مذکور را بیش از پیش گسترش می‌دهد. از زبان برنامه‌نویسی پایتون نیز می‌توان برای ایجاد «جریان‌های کاری» (Workflows) پیشرفته‌تری در علم داده استفاده کرد.

به‌عنوان مثال، می‌توانیم با استفاده از پایتون، اسکریپتی بنویسیم که داده‌های موجود در یک صفحه گسترده Excel را اسکن کرده و فیلدهای تکراری را پیدا کند. سپس می‌توانیم آن فیلدها را حذف کنیم تا دقت تحلیل داده‌هایمان را تحت تأثیر قرار ندهد. افزون بر این، با به‌کارگیری پایتون می‌توانیم مدل‌های یادگیری ماشین را آموزش دهیم یا برای مصورسازی داده‌ها از آن کمک بگیریم.

لزوم استفاده از پایتون در اکسل

بسیاری از ویژگی‌های علم داده پایتون به‌وسیله ماژول های پایتون پشتیبانی می‌شود. این ماژول‌‌ها در واقع، کدهایی هستند که از پیش، به‌وسیله کامینیوتی اوپن سورس توسعه یافته‌اند. عملیات نصب و تنظیم ماژول های پایتون به‌طور معمول به‌وسیله کاربران و به‌طور دستی صورت می‌گیرد که در نتیجه می‌تواند کار وقت‌گیری باشد اما جدیدترین نسخه اکسل از این مرحله بی‌نیاز است. به نقل از مایکروسافت، این نسخه از اکسل روی توزیعی از پایتون به‌نام «Anaconda Python» اجرا می‌شود که بسیاری از ماژول‌های پر کاربرد علم داده را به‌صورت از پیش نصب شده بر روی خود دارد. در نتیجه، نیازی نیست تا کاربران برای استفاده از ویژگی‌های پایتون در اکسل برنامه‌های اضافی را دانلود کنند یا اینکه تنظیمات خاصی را انجام دهند. بر خلاف گذشته که نیاز بود تا از کتابخانه‌های شخص ثالث و همچنین محیط «ژوپیتر نوت‌بوک» برای این منظور استفاده کنیم.

فیلم آموزش کتابخانه های پایتون برای کار با اکسل در فرادرس

کلیک کنید

Matplotlib، یکی از ماژول‌های پایتون است که با روش گفته شده، در اکسل نیز قابل استفاده است. با به‌کارگیری این ماژول می‌توانیم تحلیل‌های آماری پیشرفته‌ای را روی داده‌ها موجود در صفحه گسترده داشته باشیم. Matplotlib، روش‌های تحلیلی مختلفی همچون «رگرسیون خطی» (Linear Regression) را پشتیبانی می‌کند که توسط شرکت‌های مختلفی به منظور پیش‌بینی فروش مورد استفاده قرار می‌گیرد. اکسل همچنین از Seaborn نیز پشتیبانی می‌کند که می‌توانیم با آن، نتایج تحلیل‌هایی که با Matplotlib انجام دادیم را در قالب نمودارها به تصویر بکشیم.

از آنجایی‌که پایتون و اکسل هر دو، کاربرد گسترده‌ای در شرکت‌ها دارند، به‌همین دلیل، برخی از تیم‌های دیتا ساینس در حال حاضر برای تحلیل داده‌های صفحات گسترده خود به اسکریپت‌های پایتون روی آورده‌اند. در گذشته، اسکریپت‌های مذکور می‌بایست در کُد ادیتوری جداگانه نوشته و اجرا می‌شدند، در نتیجه، برنامه‌نویسان و دانشمندان داده مجبور بودند تا مرتباً بین این دو (اکسل و کد ادیتور) جا به جا شوند.

فیلم آموزش کتابخانه Matplotlib برای ترسیم و بصری سازی داده ها در پایتون در فرادرس

کلیک کنید

پشتیبانی داخلی پایتون توسط اکسل که جدیداً معرفی شده است، می‌بایست جریان‌کاری را ساده کند. بنا به گفته مایکروسافت، اکنون کاربران این تواناتی را پیدا کرده‌اند تا بدون نیاز به ترک محیط کاری اکسل، کدهای پایتون مورد نظر خود را به صفحه‌گسترده اضافه کنند. برای این منظور تنها کافی است تا سلولی را در صفحه‌گسترده خود، انتخاب و کدهای مورد نظر را با پیشوند =py  تایپ کنند و داده‌های مورد نظر - که کدها باید با در نظر گرفتن آن‌ها اجرا شوند - را مشخص کنند.

به منظور آنالیز و تحلیل، می‌توانیم داده‌ها را به‌وسیله مجموعه‌ای از اتصالات توکار، در اکسل «وارد» (import) کنیم. یا اینکه ممکن است بخواهیم از ابزار Microsoft Power Query برای این منظور استفاده کنیم. پاورکوئری، علاوه بر اینکه می‌تواند رکوردها را از برنامه‌های خارجی وارد اکسل کند، بلکه فرمت داده‌ها را نیز به منظور ساده شدن پردازش تغییر می‌دهد. پاور کوئری ابزاری برای اکسل است که با استفاده از آن می‌توانیم داده‌ها را از منابع مختلف وارد، تبدیل و یک خروجی یکپارچه تولید کنیم. منابع داده‌ای می‌توانند موارد مختلفی نظیر مانند فایل متنی، Workbook ،XML ،JSON اکسل و غیره باشند. خروجی نیز در قالب یک Workbook اکسل برگردانده می‌شود.

با توجه به اینکه استفاده از پایتون در اکسل تا زمان انتشار این پست در مرحله آزمایشی قرار دارد، این احتمال وجود دارد که در آینده برخی از ویژگی‌ها و توابع آن دچار تغییر شوند.

نحوه استفاده از پایتون در اکسل چگونه است؟

نوشتن دستورات پایتون در اکسل، بسیار آسان است. برای انجام این کار می‌توانیم کدهای خود را درون تابع =py()  در سلول مورد نظر بنویسیم. این مورد را در ادامه به‌طور کامل و با بیان مثال، شرح می‌دهیم.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

استفاده از پایتون در اکسل، قابلیت جدیدی است که با همکاری مورد اعتمادترین توزیع پایتون، یعنی آناکوندا و مایکروسافت اکسل، محبوب‌ترین برنامه دنیا برای کار با داده‌ها، صورت گرفته است. از مواردی که این ویژگی نوین برایمان فراهم می‌کند می‌توان به گزینه‌های زیر اشاره کرد.

• کارها و عملیات بیشتری را می‌توانیم روی داده‌هایمان انجام دهیم. برای مدل‌سازی و تحلیل داده‌ها می‌توانیم از کتابخانه‌های محبوب پایتون درون اکسل استفاده کنیم.
• مصورسازی‌های سفارشی ایجاد کنیم. به شکل بی‌سابقه‌ای می‌توانیم داده‌ها را کاوش و دستکاری کنیم و همچنین ارائه دهیم.
• آماده‌سازی داده‌ها را ساده‌تر می‌کند. می‌توانیم فرایند تبدیل داده‌های پیچیده را ساده‌تر کنیم و با تلاش کمتر، بهره‌وری را افزایش دهیم.
• کارهای صورت گرفته را به‌راحتی می‌توانیم به اشتراک بگذاریم. این ویژگی جدید، کارهایی که انجام می‌دهیم را دسترسی‌پذیر‌تر، درک آن را آسان‌تر و تکثیر آن را تسهیل می‌کند.

شروع کار با پایتون در اکسل

برای شروع کار با پایتون در اکسل و استفاده از این قابلیت شگفت‌انگیز، کافی است تا ابتدا سلولی را انتخاب کنیم و از نوار منوهای اکسل به زبانه Formulas برویم. سپس Insert Python را انتخاب کنیم. از این طریق به اکسل می‌گوییم که قصد نوشتن کدهای پایتون را در سلول انتخابی داریم.

همچنین، به‌جای استفاده از روش بالا می‌توانیم از تابع =PY  در یک سلول، برای شروع کار با پایتون استفاده کنیم. پس از اینکه =PY را در سلول مورد نظر نوشتیم، مانند تصویری که در ادامه آورده شده، PY را از منوی تکمیل خودکار - با کلیدهای جهت‌پایین و سپس Tab - انتخاب می‌کنیم.

یا اینکه یک پرانتز باز به تابع =PY(  اضافه می‌کنیم. حالا می‌توانیم کدهای پایتون خود را به‌طور مستقیم در سلول انتخابی بنویسیم. با فعال شدن پایتون در سلول، یک آیکن سبز رنگ با محتوای PY، کنار آن نشان داده می‌شود. این آیکن در نوار فرمول هم ظاهر می‌شود که در تصویر زیر به وضوح قابل مشاهده است.

ترکیب پایتون با سلول‌ها و محدوده‌های اکسل

به منظور ارجاع به اشیا اکسل در سلول پایتون، ابتدا مطمئن می‌شویم که سلول پایتون در حالت ویرایش قرار دارد، سپس سلول یا محدوده‌ای که می‌خواهیم در کد پایتون قرار گیرد را انتخاب می‌کنیم. با این کار، سلول پایتون به‌طور خودکار با «آدرس سلول مورد نظر» یا «محدوده‌ منتخب» پُر می‌شود.

فیلم آموزش تجزیه و تحلیل و آماده سازی داده ها با پایتون Python در فرادرس

کلیک کنید

این نکته را می‌توانیم به‌عنوان ترفند به یاد داشته باشیم که از کلید میانبر F2 می‌توانیم برای جا به جایی بین حالات Enter و Edit در سلول‌های پایتون استفاده کنیم. با رفتن به حال Edit، می‌توان کد (یا اصطلاحاً فرمول) پایتون را ویرایش کرد و با ورود به حالت Enter، این امکان برایمان فراهم می‌شود تا سلول یا محدوده‌های بیشتری را از طریق صفحه‌کلید انتخاب کنیم.

پایتون در اکسل از تابع سفارشی پایتون به‌نام xl()  برای تعامل بین اکسل و پایتون استفاده می‌کند. تابع xl()  اشیا اکسل نظیر «محدوده‌ها» (Ranges)، «جداول» (Tables)، «پرس و جوها» (Queries) و «نام‌ها» (Names) را می‌پذیرد.

همچنین می‌توانیم به‌طور مستقیم با استفاده از تابع xl() ارجاعات را در سلول پایتونی بنویسیم. به‌‌طور مثال، برای ارجاع به سلول A1 از xl("A1")  و برای ارجاع به محدوده B1:C4 از xl("B1:C4")  استفاده می‌کنیم. همچنین برای جدولی به‌نام MyTable از xl("MyTable[#All]", headers=True)  استفاده می‌کنیم. شناساگر  [#All]  تضمین می‌کند که کل جدول در فرمول پایتون تحلیل می‌شود و headers=True  اطمینان می‌دهد که سرآیندهای جدول به درستی پردازش می‌شوند. تصویری که در ادامه آورده‌ایم، نمونه‌ای از محاسبات پایتون در اکسل را نشان می‌دهد که با جمع‌زدن مقادیر A1 و B1، خروجی پایتون را در C1 قرار می‌دهد.

نوار فرمول در اکسل

از نوار فرمول می‌توانیم برای ویرایش کدهای نوشته شده استفاده کنیم. مثلاٌ برای ایجاد خطوط جدید کلید Enter را می‌فشاریم و غیره. با کلیک روی آیکن فلش رو به پایین، می‌توانیم نوار فرمول را گسترش دهیم تا تمامی خطوط کدهایمان به‌طور یکجا مشاهده کنیم. همچنین از کلیدهای میانبر Ctrl+Shift+U  هم می‌توانیم برای این منظور استفاده کنیم. تصویری که در زیر آورده‌ایم، نوار فرمول را قبل و بعد از بزرگ‌کردن (به منظور نمایش خطوط کدهای پایتون) نشان می‌دهد.

کنترل نوع خروجی سلول پایتون

برای تعیین اینکه خروجی محاسبات پایتون به‌چه صورتی باشد می‌توانیم از منوی خروجی پایتون واقع در نوار فرمول کمک بگیریم. محاسبات را می‌توانیم به‌صورت اشیا پایتون برگردانیم یا اینکه آن‌ها را به مقادیر اکسل تبدیل و به‌طور مستقیم در سلول نمایش دهیم. تصویری که در زیر آورده‌ایم، کدهای پایتونی را نشان می‌دهد که به‌صورت مقدار اکسل برگردانده شده است.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

همچنین این امکان نیز وجود دارد تا از طریق منوی راست‌کلیک نوع خروجی پایتون را تغییر دهیم. برای این کار در منوی راست‌کلیک و خروجی پایتون، نوع خروجی مورد نظر را انتخاب می‌کنیم.

تصویر زیر، همان کدهای پایتون تصویر پیشین را نشان می‌دهد که اکنون به‌صورت یک شی پایتون برگردانده شده است. هنگامی‌که کدها به‌صورت شی پایتون برگردانده می‌شوند، یک آیکن «برگه» هم در کنار سلول ظاهر می‌شود.

نتایج کدهایی که به‌صورت مقدایر اکسل برگردانده می‌شوند، به نزدیک‌ترین معادل خود در اکسل تبدیل می‌شوند. اگر فکر می‌کنید که در‌ آینده قرار است تا نتیجه را دوباره در محاسبات پایتون استفاده کنید‌، بهتر است که آن را به‌صورت شی پایتون برگردانید. بازگرداندن نتیجه در قالب مقادیر اکسل این امکان را برایمان فراهم می‌کند تا از امکانات تحلیلی اکسل نظیر نمودارهای اکسل، فرمول‌ها، قالب‌بندی شرطی و غیره را روی مقادیر اجرا کنیم.

در نظر داشته باشید که شی پایتون، اطلاعاتی اضافی را نیز درون سلول نگه می‌دارد، برا دیدن این اطلاعات تکمیلی، روی آیکن برگه کلیک می‌کنیم. اطلاعاتی که در برگه‌ می‌بینیم در واقع نگاه کلی و پیش‌نمایشی از شی است که هنگام پردازش اشیا بزرگ می‌تواند برایمان مفید باشد. پایتون در اکسل می‌تواند انواع متعددی از داده‌ها را به‌عنوان اشیا پایتون بازگرداند. یه‌طور مثال، یکی از نوع داده‌های پرکاربرد پایتون در اکسل، شی دیتافریم است.

وارد کردن داده های اکسترنال به اکسل چگونه است؟

برای وارد کردن داده‌های «بیرونی» (External) از ویژگی Get & Transform موجود در اکسل استفاده می‌کنیم. Get & Transform با استفاده از پاورکوئری داده‌های بیرونی را import می‌کند. در واقع، تمامی داده‌هایی که با استفاده از پایتون در اکسل پردازش می‌کنیم می‌بایست از Worksheet یا پاورکوئری آمده باشند.

لازم است به این نکته نیز توجه داشته باشیم که برای حفظ امنیت ما، توابع رایج پایتون در رابطه با داده‌های اکسترنال، همچون pandas.read_csv  و pandas.read_excel  با «پایتونِ در اکسل» سازگار نیستند.

ترتیب محاسبات پایتونی در اکسل چگونه است؟

در حالت عادی، عبارات پایتونی از بالا به پایین محاسبه می‌شوند. هنگام نوشتن پایتون در سلول اکسل هم، روال اجرا به‌همین شکل است و از بالا به پایین محاسبه می‌شوند. اما در Worksheet پایتون در اکسل، سلول‌های پایتون به‌صورت سطری محاسبه می‌شوند. یعنی محاسبات سلول در یک سطر (از ستون A تا ستون XFD) و پس از آن در هر سطرهای بعدی به سمت پایینِ Worksheet پیش می‌رود.

فیلم آموزش رایگان اکسل Excel – محاسبات ریاضی و ماتریسی – ویژه ناشنوایان در فرادرس

کلیک کنید

دستورات پایتون دارای ترتیب هستند، بنابراین هر دستور پایتونی، به دستور پایتونی که بلافاصله قبل از آن در ترتیب محاسبه قرار گرفته است، وابستگی ضمنی (غیرصریح) دارد. این ترتیب محاسبه، هنگام تعریف و ارجاع به متغیرها در Worksheet اهمیت زیادی دارد، زیرا پیش از آنکه بتوانیم به متغیرها ارجاع دهیم می‌بایست آن‌ها را تعریف کرده باشیم.

دانستن این نکته نیز مهم است که ترتیب محاسبه به‌صورت سطری، در Worksheet-های درون یک Workbook نیز اعمال می‌شود و بر اساس ترتیب Worksheet-های داخل یک Workbook صورت می‌گیرد. اگر از Worksheet-های متعددی برای تحلیل داده‌ها با استفاده از پایتون در اکسل استفاده می‌کنیم، می‌بایست مطمئن شویم که داده و هر متغیری که داده را ذخیره می‌کند در سلول‌ها و Worksheet-های قبل از سلول‌ها و Worksheet-هایی که داده‌ها را تحلیل می‌کنند، موجود باشد.

تجدید محاسبه چیست؟

هنگامی‌که مقدار وابسته سلول پایتون تغییر می‌کند، تمامی کدهای پایتون به‌صورت متوالی به‌طور مجدد اجرا می‌شوند. برای متوقف ساختن محاسبات مجدد و افزایش کارایی باید از حالت «محاسبه دستی» (Manual Calculation) یا «محاسبه جزئی» (Partial Calculation) استفاده کنیم. با استفاده از این حالات می‌توانیم محاسبات را زمانی که آمادگی داشتیم، انجام دهیم. برای تغییر این تنظیمات به نوار منو‌های اکسل رفته و «گزینه‌های محاسبه» (Calculation Options) را باز، سپس حالت محاسبه مورد نظر را انتخاب می‌کنیم. با استفاده از حالات محاسبه دستی و محاسبه جزئی، محاسبه مجدد، هم برای پایتون و هم جداول داده‌های به‌طور خودکار متوقف می‌شود.

از کار انداختن محاسبه مجدد خودکار در یک Workbook، حین توسعه کدهای پایتون می‌تواند بهبود کارایی و سرعت محاسبه سلول‌های پایتونی مجزا را به‌دنبال داشته باشد. اگرچه، برای اطمینان از دقت در هر سلول پایتون می‌بایست Workbook را به‌صورت دستی به‌طور مجدد محاسبه کنیم. ۳ روش برای محاسبه مجدد یک Workbook به‌صورت دستی در حالات محاسبه جزئی یا محاسبه دستی وجود دارد که در ادامه آورده‌ایم.

• از کلید میانبر F9 روی صفحه‌کلید استفاده کنیم.
• از نوار منوها به Formulas و سپس به Calculate Now برویم.
• به سلولی که روی محتوای قدیمی آن خط کشیده شده است (قلب‌بندی Strikethrough دارد) می‌رویم و با کلیک روی نماد خطا را در کنار آن سلول،‌ از منوی باز شده، گزینه Calculate Now را انتخاب می‌کنیم.

خطاهای پایتون در اکسل

محاسبات پایتون در اکسل می‌توانند خطاهایی نظیر #PYTHON!  ، #BUSY  ، و #CONNECT!  را به سلول‌های پایتون برگردانند.

مصورسازی داده با پایتون در اکسل چگونه است؟

ادغام پایتون در اکسل، به‌عنوان افزونه‌ای پیشگامانه، نقش پُلی را دارد که ۲ اکوسیستم محبوبی که پیش از این مجزا بودند را به‌هم وصل می‌کند. ادغام کدهای پایتون در Workbook اکسل، این امکان را برایمان فراهم می‌کند تا بتوانیم از کتابخانه‌‌های گسترده پایتون برای تحلیل و مصورسازی داده‌ها در اکسل، نهایت استفاده را ببریم که این قضیه، ویژگی مذکور را به ابزار گرانبهایی برای علاقه‌مندان به داده‌ها و تحلیل‌گران تبدیل می‌کند.

فیلم آموزش کتابخانه Matplotlib برای ترسیم و بصری سازی داده ها در پایتون در فرادرس

کلیک کنید

در این مطلب از مجله فرادرس همچنین می‌خواهیم به این موضوع بپردازیم که چگونه با «استفاده از پایتون در اکسل» می‌توانیم داده‌ها را بدون اینکه از اکسل خارج شویم، به‌وسیله Seaborn - کتابخانه مصورسازی محبوب پایتون - به تصویر بکشیم.

شروع نوشتن کدهای پایتون در اکسل

همان‌طور که پیش‌تر نیز اشاره کردیم، استفاده از پایتون در اکسل بسیار ساده است. برای این منظور کافی است تا دستور خود را با پیشوند =py  شروع کنیم.

به عنوان مثال، فرض می‌کنیم که داده‌هایی را درون Workbook به‌نام data  داریم و می‌خواهیم آن را به‌صورت دیتافریم پانداس بخوانیم. در سلول مورد نظر اکسل می‌نویسیم =py("  و سپس به‌طور عادی شروع به نوشتن کدهای پایتون می‌کنیم. برای خواندن داده‌ها از Workbook data و ایجاد دیتافریم پانداس از دستور زیر استفاده می‌کنیم.

1df = xl("data!A1:I399", headers=True)

در این مثال، داده‌ها را از محدوده مشخص “data!A1:I399”  در data می‌خوانیم. آرگومان headers=True  بیان‌گر این است که اولین سطر شامل سرآیند‌های ستون است و به اکسل اجازه می‌دهد تا دیتافریم پانداس را - با ستون‌هایی که نام مناسبی دارند - ایجاد کند.

برای بررسی ۵ سطر اول دیتافریم در اکسل، می‌توانیم از دستور df.head()  کمک بگیریم. دیتاست مورد استفاده در این مثال، مشخصات فنی خودروها را در بر می‌گیرد.

چرا از Seaborn استفاده می کنیم؟

Seaborn کتابخانه‌ای برای مصورسازی داده‌ها است که روی Matplotlib، کتابخانه محبوب دیگری برای ترسیم نمودار در پایتون، ساخته شده است. Matplotlib، زیربنایی منعطف برای ایجاد نمودارهای ایستا، تعاملی و متحرک فراهم می‌کند و Seaborn با ارائه رابطی سطح بالاتر، فرایند تولید نمودارهای آماری جذاب و مفید را ساده می‌سازد.

ترسیم با Seaborn در اکسل چگونه است؟

یکی از دلایل محبوبیت Seaborn، سینتکس ترسیم کاربر پسند آن است. به‌طوریکه با تنها چندین خط کد می‌توانیم نمودارهای بصری خیره‌کننده‌ای را بسازیم که در صورت استفاده از کتابخانه‌های دیگر به تلاش بیشتری نیاز دارند. توابع سطح بالای Seaborn این امکان را برایمان فراهم می‌کنند تا به‌سرعت انواع مختلفی از نمودارها، همچون «نمودار پراکندگی» (Scatter Plots)، «نمودار میله‌ای» (Bar Plot)، هیستوگرام‌ها، «نمودار جعبه‌ای» (Box Plot) و غیره را با حداقل کدنویسی تولید کنیم. موارد آورده شده در ادامه، نمونه‌ای از این نمودارها هستند.

فیلم آموزش تحلیل داده های کسب و کار با اکسل Excel در فرادرس

کلیک کنید

نمودار هیستوگرام که توزیع «Miles» در «Gallon» یا «MPG» را نمایش می‌دهد.

نمودار چگالی کرنل که توزیع MPG را نمایش می‌دهد.

نمودار پراکندگی که رابطه بین MPG و وزن را نمایش می‌دهد.

ایجاد نمودارهای آماری چگونه است؟

کتابخانه Seaborn، یکی از بهترین گزینه‌ها برای ایجاد نمودارهای آماری محسوب می‌شود که بینش‌های سودمندی را از دیتاست‌های پیچیده آشکار می‌سازد. این کتابخانه، روش‌های تخمین آماری را در توابع ترسیمی خود لحاظ کرده است و به‌طور خودکار می‌تواند آمارهای چکیده ضروری را محاسبه و نمایش دهد. همچنین مدل‌های رگرسیون خطی را متناسب سازد که علاوه بر صرفه‌جویی در زمان، بینش‌های آماری بیشتری را از نمایش‌های بصری ما عرضه می‌کند.

فیلم آموزش کاربرد اکسل در حسابداری و مدیریت در فرادرس

کلیک کنید

• به‌طور مثال، برای رابطه بین وزن و MPG می‌توانیم مدل رگرسیون خطی مشابه تصور زیر را ترسیم کنیم.

• می‌توانیم رنگ و نشانگرهای هر مأخذ برای ۳ مدل رگرسیون خطی را تعیین کنیم.

مصورسازی روابط متعدد چگونه است؟

در تحلیل داده‌ها، اغلب با دیتاست‌هایی رو به رو می‌شویم که شامل متغیرهای متعددی هستند. درخشش قابلیت‌های کتابخانه Seaborn هنگام مصورسازی روابط بین این چندین متغیر - که ممکن است عددی باشند یا «دسته‌ای» (Categorical) - پدیدار می‌شود. با استفاده از ویژگی‌هایی نظیر jointplot()  و pairplot()  ، کتابخانه سیبورن این امکان را برایمان فراهم می‌سازد تا انواع متعددی از نمودارها را از ماژول‌های متفاوت به‌کار بگیریم تاجنبه‌های گوناگون دیتاست را در یک نمودار نشان دهیم.

• به‌عنوان مثال، می‌توانیم با استفاده از jointplot()  روی رابطه مشخصی بین وزن و MPG برای هر مأخذ تمرکز کنیم. این نمودار به ما امکان می‌دهد تا توزیع هر متغیر در هر مأخذی را همزمان با مصورسازی همبستگی بین وزن و MPG، مشاهده می‌کنیم.

پایتون در اکسل برای کسانی که با داده‌‌ها سروکار دارند و به اکسل و پایتون متکی‌اند، تحول بزرگی محسوب می‌شود. با ترکیب قدرت اکسل و Seaborn، دسترسی ساده‌تری به تحلیل داده‌های قدرتمند داریم و می‌توانیم بینش‌های مبتنی بر داده قوی‌تری را در اختیار داشته باشیم.

۵ نکته مفید برای استفاده از پایتون در اکسل

در این قسمت، نکات و ترفندهایی را در مورد استفاده از پایتون در اکسل، همچون نحوه گنجاندن اشیا پایتون در اکسل، چگونگی مدیریت خروجی‌های سلول و قالب‌بندی آن و همچنین نحوه استفاده از پایتون به منظور ایجاد منطق سفارشی قابل استفاده مجدد، بررسی کرده‌ایم که به‌راحتی می‌توانید آن‌ها را اجرا و آزمایش کنید.

فیلم آموزش تحلیل داده های کسب و کار با اکسل Excel در فرادرس

کلیک کنید

تابع print و خروجی های سلول اکسل چگونه است؟

یکی از اولین مواردی که می‌توانیم هنگام استفاده از پایتون در اکسل انجام دهیم، نوشتن برنامه «Hello world» است. اگر با زبان پایتون آشنایی داشته باشید، ممکن است کدی شبیه به دستور زیر را امتحان کنید.

1print(“Hello world, I am divanairuop”)

اگر این دستور را درون سلول پایتونی (یعنی با استفاده از =PY ) بنویسید و سپس اجرا کنید، ممکن است نتیجه‌ای متفاوت با آنچه انتظار دارید، تولید شود. یعنی به‌جای نمایش رشته داخل تابع print  ، مقدار سلول، برابر با None  خواهد بود. کدی که ما نوشتیم صحیح است اما این مشکل به یکی از ویژگی‌های منحصر به فرد اکسل بر می‌گردد. در واقع تابع print در اکسل، تنها برای اطلاعات «تشخیصی | عیب‌یابی» (Diagnostic) و «رویدادنگاری» (Logging) به‌کار می‌رود و نمی‌توان آن را برای تولید خروجی سلول استفاده کرد. بنابراین، اگر پنل «Diagnostics» را باز کنیم، آنگاه می‌توانیم پیام مورد نظر خود، یعنی Hello world, I am divanairuop  را ببینیم.

برای اینکه خروجی معتبری برای سلول مورد نظر داشته باشیم باید این نکته را بدانیم که هر سلول پایتونی به شکل «REPL» یا «Read–Eval–Print Loop» اجرا می‌شود (شبیه به اجرای کدهای پایتون در سلول‌های ژوپیتر نوت‌بوک). یعنی آخرین عبارت ارزیابی شده در سلول، به‌عنوان خروجی سلول را نشان می‌دهد.

به همین دلیل، برای اینکه پیام «Hello world» را به‌عنوان محتوای سلول پایتونی ببینیم، کافی است تابع print را حذف کنیم تا تنها رشته مورد نظر باقی بماند و ارزیابی شود.

خروجی سلول مقدار اکسل باشد یا شی پایتون؟

خروجی سلول‌های پایتونی را به ۲ شکلی که در ادامه آورده‌ایم، می‌توانیم تنظیم کنیم.

1. شی پایتون (Python object)
2. مقدار اکسل (Excel value)

خروجی به صورت مقدار اکسل چگونه است؟

همان‌طور که در تصویر ملاحضه کردید، خروجی سلول پایتونی به‌صورت پیش‌فرض و در لیست کشویی روی حالت «Excel value» قرار دارد. از این‌رو، مقدار هر شی پایتونی که به‌وسیله کدهای درون سلول برگردانده می‌شود به خروجی اکسلِ معادلِ آن تبدیل خواهد شد. هر «نوع‌داده اولیه» (Primitive Data Type) در این مورد، به‌صورت خودکار پشتیانی می‌شود. به‌طور مثال str  به‌عنوان متن، float  به‌صورت اعداد اعشاری و غیره نمایش داده می‌شود. اکسل، همچنین به‌طور ذاتی از مصورسازی «دیتافریم‌های کتابخانه پانداس» ( pandas.DataFrame ) و «آرایه‌های ۲ بُعدی نامپای» ( numpy.array  ) پشتیبانی می‌کند و آن‌ها را در قالب جداولی نمایش می‌دهد.

خروجی به صورت شی پایتون چگونه است؟

حال اگر خروجی سلول را روی «Python object» قرار دهیم، آیکن شی نیز در کنار بازنمایی شی (یا نام کلاس پایتون)، در سلول مورد نظر نشان داده می‌شود.

این نوع خروجی گاهی اوقات می‌تواند سودمند باشد و مصورسازی کنونی شی (مثلاً یک تصویر) را انعطاف‌پذیر کند، یا اینکه ارجاع به شی پایتونی - که قرار است بعدها در Workbook (فایل حاوی Worksheet-ها) دوباره استفاده شود - را نگه دارد.

لزوم استفاده از کدهای ساده و کوتاه پایتون به همراه خروجی

فارغ از اینکه چه نوع خروجی را برای سلول کدهای خود انتخاب می‌کنیم، لازم است تا روش‌های مطلوبی که برای کدنویسی پایتون در اکسل وجود دارد را به‌خاطر داشته باشیم. در اینجا به ۲ مورد اشاره می‌کنیم.

1. از نوشتن کدهای طولانی بپرهیزیم.
2. همیشه مقداری را به‌عنوان خروجی سلول مورد نظر برگردانیم.

لازم است تا به یاد داشته باشیم که نوشتن کدهای پایتون در سلول‌های اکسل، با کدنویسی در کد ادیتورها یا محیط‌های IDE تفاوت دارد. زیرا هدف اصلی استفاده از پایتون در اکسل، توسعه برنامه‌های پایتونی نیست، بلکه می‌خواهیم از این طریق بینش‌های قوی‌تری در مورد داده‌ها ایجاد کنیم.

به‌همین خاطر، بهتر است کدهایی که می‌نویسم، تا حد امکان کوتاه و سعی بر این باشد تا تنها کدهایی داشته باشیم که برای تولید خروجی مطلوب، ضروری است. همچنین، کوتاه و ساده نگه داشتن قطعه کدهایی که درون سلول‌ها می‌نویسیم، باعث ساده‌تر شدن فرایند نگهداری آن‌ها نیز می‌شود.

آخرین موردی‌که هنگام نوشتن کدهای پایتون در سلول‌های اکسل باید به یاد داشت این است که همیشه بهتر است تا کدی بنویسیم که منجر به تولید خروجی شود. زیرا خروجی تولید شده توسط کدهایمان، محتوای نهایی سلول را تشکیل می‌دهند. دستورات پایتون می‌توانند خروجی‌هایی از انواع مختلف، نظیر متن، اعداد، اشیا پایتون یا تصاویر داشته باشند و تمامی این Type-ها به‌طور خودکار توسط اکسل به‌عنوان محتوای سلول پشتیبانی می‌شوند. در مقابل، اگر کدهایی که می‌نویسیم هیچ نوع خروجی تولید نکند (مانند دستورات import  پایتون)، پس از اجرای کدها، محتوای سلول به‌صورت None  نمایش داده می‌شود. این قضیه کاملاً منطقی است، زیرا کدهای پایتون نوشته شده هیچ مقداری را به‌عنوان خروجی تولید نمی‌کنند. با این حال، نمایش None به‌عنوان خروجی سلول، ممکن است کمی ناخوشایند به‌نظر برسد یا اینکه از دید دیگران به‌عنوان یک خطا تلقی شود.

به‌همین دلیل، اگر کدهایی که می‌نویسیم، خروجی مشخصی را تولید نمی‌کنند، بهتر است تا یک رشته پایتونی را (مابین ۲ علامت "  ) تعریف کنیم و به‌عنوان برچسب محتوای آن سلول برگردانیم. تصویری که در ادامه آورده‌ایم، این مورد را به‌خوبی نشان می‌دهد.

به‌ کارگیری Cell Format برای نمایش صحیح خروجی پایتون

در نکته پیشین، تأکید زیادی روی این مورد داشتیم که کدهایمان حتماً یک خروجی را برای سلول تولید کنند. اکنون می‌خواهیم راجع به «قالب‌بندی» (Formatting) آن خروجی صحبت کنیم. برای مثال، ممکن است بخواهیم که رشته‌ای را روی چندین خط قالب‌بندی کنیم یا اعداد اعشاری - که مثلا در ستونی از دیتافریم Pandas موجود است - را تا ۲ رقم اعشار نمایش دهیم. خودِ پایتون گزینه‌هایی را برای کار با خروجی قالب‌بندی شده فراهم می‌کند. مثلاً، پایتون با پشتیبانی از f-strings  (یا Formatted string literals)، می‌تواند رشته‌هایی را ایجاد کند که از مقادیر تولیدی توسط دستورات پایتون تشکیل شده‌اند. در پایتون می‌توانیم رشته‌های چندخطی (مابین ۲ علامت '''  ) نیز داشته باشیم که شامل کاراکترهای قالب‌بندی و جدول‌بندی باشند.

به‌طور مثال، برای نوشتن کدهای پایتونی که در تصویر زیر ملاحضه می‌کنید، مشخص است که pi  با ۳ رقم اعشار قالب‌بندی شده و به بقیه رشته چسبیده است. محتوای سلول پایتون انتخابی، یک شی پایتون از نوع رشته‌ای (مانند str ) است. بدین ترتیب همانطور که انتظار داریم، محتوای سلول، رشته قالب‌بندی شده پایتون است.

ممکن است این سوال برایتان پیش بیاید که اگر مقدار pi را به‌طور مستقیم در سلول بنویسم، خروجی آن به‌ چه‌ صورتی می‌شود. همان‌طور که در تصویر زیر ملاحضه می‌کنید، اکنون، خروجی تولید شده در سلول، یک شی float  پایتون است و با ۹ رقم اعشار نمایش داده شده است.

تا اینجای کار همه چیز خوب پیش رفته است، اما با بررسی «گزینه‌های قالب‌بندی» (Format options) سلول، با موارد جالبی رو به رو می‌شویم. با اینکه خروجی تولید شده، عددی اعشاری است، اما قالب سلولی که احتمالاً خواهیم دید، «General» است.به بیان دیگر، هیچ ارتباط مستقیمی بین نوع بازگردانده شده از پایتون (در این مثال، float  ) و قالب سلول وجود ندارد. در واقع، اگر قالب «Number» را انتخاب کنیم و سپس ارقام اعشاری را برابر با ۳ قرار دهیم، محتوای جدید سلول می‌بایست شبیه به تصویر زیر باشد.

بدین‌ترتیب از الآن به بعد، محتوای سلول پایتون (در این مثال، B1) توسط اکسل به‌عنوان یک «عدد» تلقی می‌شود. نکات کلیدی این قسمت را می‌توانیم به‌صورت زیر بیان کنیم.

1. «انواع داده‌ای پایتون» و «قالب‌بندی سلول» دو مقوله متفاوت هستند.
2. برای اطمینان از اینکه اکسل خروجی تولید شده توسط کدهای پایتون را به‌درستی پردازش می‌کند، لازم است تا قالب سلول‌ها در Workbook را مشخص (و تنظیم) کنیم، به‌ویژه اگر خروجی سلول، «عدد» یا «تاریخ» باشد.

ایجاد منطق سفارشی در اکسل با پایتون چگونه است؟

در گذشته، هنگامی‌که از توابع درونی اکسل استفاده می‌کردیم و تابعی (نظیر SUM(…) ) را صدا می‌زدیم، خروجی را بلافاصله در سلول مشاهده می‌کردیم. همچنین اگر تابعی نیاز داشتیم که در سراسر Workbook قابل استفاده باشد، باید برای تعریف آن، زبان برنامه‌نویسی «VBA» یا همان «Visual Basic for Applications» را به‌کار می‌بردیم. اما اکنون می‌توانیم از پایتون برای این منظور استفاده کنیم.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

در واقع، حالا می‌توانیم با استفاده از پایتون، توابعی را درون سلول تعریف کنیم و هر گاه که لازم باشد در سلول‌های دیگری در Workbook از این توابع، به‌طور مجدد استفاده کنیم. می‌توان گفت که مدل اجرای پایتون در اکسل، فرض می‌کند که یک فضای نام سراسری بین هر سلول به اشتراک گذاشته شده است.

ترتیب اجرای دستورات، از سلول بالا و سمت چپِ اولین Worksheet شروع می‌شود و به‌صورت «ردیفی» (Row-major) ادامه می‌یابد. بنابراین هر «متغیر» (Variable)، «تابعِ» (Function) پایتون یا «شی» (Object) پایتونی که در یک سلول تعریف شده است، در تمام سلول‌هایی که بعد از آن در ترتیب اجرا قرار دارند، در دسترس و قابل استفاده خواهد بود.

به‌عنوان مثال، فرض کنید که می‌خواهیم تابعی را - با استفاده از پایتون - تعریف کنیم که آرایه‌ای حاوی تعداد مشخصی از اعداد اعشاری تصادفی تولید می‌کند.

1import numpy as np
2
3np.random.seed(42)
4
5def generate_numbers(how_many: int):
6return np.random.random(how_many)
7
8generate_numbers(10)

پس از اینکه تابع را تعریف کردیم، آن را فراخوانی می‌کنیم تا ۱۰ عدد تصادفی مختلف تولید کند. اگر این کدها را اجرا کنید. اعدادی را درست شبیه به تصویر زیر خواهید داشت.

دلیل اینکه علیرغم استفاده از تابع random پایتون، اعدادی کاملا مشابه به‌دست می‌آید این است که مقدار seed  را روی ۴۲ تنظیم کرده‌ایم. به‌همین دلیل، تولید کننده اعداد شبه‌تصادفی در صدد است تا تولید همان توالی از اعداد را تکرار کند.

همانطوری که انتظار می‌رود تابع ما، آرایه پایتونی نامپای را تولید می‌کند و تمامی مقادیر در ۱۰ سلول پایین آن قرار می‌گیرند. اکنون اگر دوباره تابع generate_numbers را در سلول دیگری (مثلاً B1) استفاده کنیم و این دفعه مقدار جدیدی را برای پارامتر ورودی how_many در نظر بگیریم (مثلاً ۵)، نتیجه‌ای مشابه با تصویر زیر خواهیم داشت.

سلول پایتونیِ B1، تنها حاوی فراخوانی تابع پایتون است که آرایه دیگری از ۵ عدد تصادفی جدید تولید می‌کند. بنابراین تابع پایتونی ساده ما همانند منطق سفارشی اکسل عمل می‌کند که می‌تواند در سراسر Workbook با مقادیر متفاوتی از پارامترها به دفعات تعریف شود و به‌طور مجدد مورد استفاده قرار بگیرد.

سوالات متداول

اکنون که با نحوه استفاده از پایتون در اکسل آشنا شدیم، برخی از سوالات متداول در این زمینه را با هم مرور می‌کنیم.

فیلم آموزش تجزیه و تحلیل و آماده سازی داده ها با پایتون Python در فرادرس

کلیک کنید

چرا از پایتون در اکسل استفاده می کنیم؟

استفاده از پایتون در اکسل به ما اجازه می‌دهد تا کدهای پایتون خود را به‌طور مستقیم در نوار فرمول اکسل اجرا کنیم. این ویژگی، قابلیت تحلیل و مصورسازی جدید و قدرتمندی را برای کاربران اکسل فراهم می‌کند. همچنین، برنامه‌نویسان پایتون می‌توانند کدهای پایتونی خود را به‌طور آسان‌تری با همکارانشان که از اکسل استفاده می‌کنند به اشتراک بگذارند بدین ترتیب از جا به جا شدن‌های متعدد بین Excel و ژوپیتر نوت‌بوک جلوگیری می‌شود.

نقش آناکوندا در پایتون در اکسل چیست؟

آناکوندا به‌دنبال ادغام توزیع آناکوندا برای پایتون، درون اکسل است. با این کار علاوه بر اینکه قابلیت‌های اصلی پایتون را در اختیار داریم، به بیش از ۴۰۰ بسته منتخب نیز دسترسی پیدا می‌کنیم که امکان تحلیل داده‌ها و مصورسازی پیشرفته را به‌طور مستقیم درون Workbook اکسل برایمان فراهم می‌کند.

مفهوم بسته، کتابخانه و توزیع چیست؟

پایتون، اکوسیستمی است که شامل زبان اصلی و بیش از صدها هزار کتابخانه (بسته) است که به‌طور مستقلی ایجاد شده‌اند. توزیع، مانند توزیع آناکوندا برای پایتون، در واقع زیر مجموعه‌ای از کتابخانه‌های شخص ثالث است که درون یک بسته نرم‌افزاری قابل نصب، جمع‌آوری شده‌اند.

import کردن به چه معناست؟

با استفاده از دستور import  در پایتون می‌توانیم قابلیت‌های بیشتری را به کدهایمان اضافه کنیم. به عبارت دیگر به ما اجازه می‌دهد تا به ابزارها و روش‌های عرضه شده توسط کتابخانه‌ها و بسته‌های شخص ثالث، دسترسی داشته باشیم.

کدام بسته های پایتون را می‌توانیم در اکسل به کار ببریم؟

برای استفاده از پایتون در اکسل می‌توانیم با بسته‌های زیر شروع کنیم.

• Pandas  به منظور کار با جداول استفاده می‌شود.
• Seaborn  و Matplotlib  برای ایجاد نمودارها (Seaborn بیشتر برای تازه‌کارها مناسب است و Matplotlib امکان سفارشی‌سازی بیشتری را فراهم می‌کند.
• statsmodels  یا SciPy  برای تحلیل‌های آماری به‌کار می‌روند.
• scikit-learn  برای یادگیری ماشین مورد استفاده قرار می‌گیرد.

این‌ها برای شروع عالی هستند و در ادامه موارد دیگری را نیز می‌توانید به‌کار ببرید.

فیلم آموزش پانداس pandas برای تحلیل اطلاعات در پایتون در فرادرس

کلیک کنید

نحوه نوشتن کدهای پایتون در سلول ها چگونه است؟

برای این منظور، کافی است تا در نوار فرمول روی دکمه Python کلیک کنیم یا اینکه با تایپ =PY(  شروع به نوشتن کدهای پایتون کنیم.

آیا استفاده از پایتون در اکسل امن است؟

پایتون در اکسل، از بسته‌های منتخب توزیع آناکوندا استفاده می‌کند. این بسته‌ها از موارد مخربی که به‌طور معمول در مخازن تحت رهبری کامیونیتی یافت می‌شوند، در امان هستند. گزینش آناکوندا، تضمین می‌کند که بسته‌ها سازگار هستند و بدون مشکل در کنار هم کار می‌کنند و یک منبع قابل اعتماد برای پایتون فراهم می‌کنند.

دیتافریم چیست؟

DataFrame، نمایش داده‌های جدولی است که با سطرها و ستون‌ها سازمان‌دهی شده‌اند. کتابخانه پایتونی پانداس، داده‌های جدولی را در قالب‌های مختلفی نظیر اکسل یا CSV خوانده و شی دیتافریم را می‌سازد. دیتافریم را می‌توان به منظور انجام تحلیل کاوشگرانه یا پردازش داده‌ها در پایتون به‌کار برد.

انجام عملیات VLOOKUP با پایتون و پانداس چگونه است؟

قابلیت VLOOKUP در اکسل برای ادغام ۲ جدول با حداقل یک ستون مشترک و تحت شرایط معینی استفاده می‌شود. در کتابخانه پانداس می‌توانیم عملیات مشابهی را با متدهای merge()  و join() انجام دهیم.

آیا جعبه ابزار آناکوندا همان پایتون در اکسل است؟

«جعبه ابزار آناکوندا» (Anaconda Toolbox)، ابزارهایی را به منظور کمک به استفاده از پایتون در اکسل ارائه می‌دهد و می‌توان گفت که مکمل آن است.

جمع‌بندی

در این مطلب از مجله فرادرس، یاد گرفتیم که استفاده از پایتون در اکسل چگونه است.

فیلم مجموعه آموزش برنامه نویسی پایتون Python – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

این قابلیت ویژه که به‌تازگی معرفی شده است، دامنه تحلیل در اکسل را گسترش می‌دهد. با استفاده از این ویژگی که متشکل از تنوع عملکردی اکسل و قدرت آناکوندا است می‌توانیم پایتون و اکسل را به‌صورت یکجا و در یک Workbook داشته باشیم و مواردی همچون تحلیل داده‌ها به‌همراه مصورسازی، یادگیری ماشین، تحلیل پیشگویانه و بسیاری موارد دیگر را روی داده‌ها انجام دهیم.