کلیک‌ساز - تولید پرزنتیشن پاورپوینت بصورت خودکار(بخش یکم)

تولید پرزنتیشن پاورپوینت بصورت خودکار(بخش یکم)

موضوعات : آفیس, اکسل, آموزشی, تخصصی, برنامه‌نویسی

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

درس‌های این آموزش به قرار زیر است:(به مرور به مطالب زیر افزوده خواهدشد)

ایجاد یک اسلاید جدید و یا کپی‌کردن(Duplicate) اسلاید
ایجاد ارتباط با یک فایل اکسل
انتقال داده از فایل اکسل به یک نمودار در پاورپوینت
مرتب‌سازی اطلاعات و داده‌های نمودار(Sort)

در اینجا برای فهم بهتر و چگونگی کار لازم است فایل اکسل پیوست را شرح دهم، شما ممکن است فایل اکسل مربوط به خودتان را داشته‌باشد، برای همین یک فایل اکسل با تعداد رکوردهای بالا(حدود 3000 ردیف یا رکورد) دارم، در این فایل اکسل شیت RAW داده‌های خام ما را شامل می‌شود، ممکن است داده‌های شما متفاوت از این باشد اما روند کار یکسان خواهد بود. ستون‌های مربوط به داده‌های خام به شرح زیر است(در هر ماه با دریافت داده‌های جدید آن ماه، این داده‌ها را به انتهای شیت RAW اضافه می‌کنیم، لذا این داده‌ها کتغیر هستند)

ORDERNUMBER	شماره سفارش
QUANTITYORDERED	تعداد سفارش
SALES	مبلغ فروش
ORDERDATE	تاریخ سفارش
PRODUCTLINE	خط تولید
PRODUCTCODE	کد محصول
CUSTOMERNAME	نام مشتری
COUNTRY	کشور

فرض کنید بصورت ماهیانه قرار است شما در پاورپوینت این نمودارها را داشته باشید:

برای هر مشتری یک نمودار روند سفارش به تفکیک تاریخ سفارش داشته باشیم(باید لیستی از نام مشتریان تهیه گردد)
نمودار میله‌ای میزان سفارش مشتریان در یک سال خاص( دوره‌های مختلف به همراه مقایسه)
نمودار روند فروش هر محصول(باید لیستی از نام محصولات تهیه گردد)
نمودار میله‌ای میزان سفارش به تفکیک کشور

استخراج اطلاعات یکتا

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

در فایل اصلی یک شیت جدید با نام BaseData(برای نگهداری اطلاعات پایه) ایجاد کرده و در سلول A2 آن کد زیر را قراردهید:

=FILTER(UNIQUE(RAW!F2:F99999),UNIQUE(RAW!F2:F99999)<>0)

در شیت RAW ستون F مربوط به کد محصولات می‌باشد، از آنجایی‌که تعداد ردیف‌ها مشخص نیست از F2:F99999 استفاده‌شده تا هر افزایش در تعداد ردیف‌ها پوشش داده‌شود(اگر دستور FILTER را حذف کنیم در انتهای اطلاعات عدد 0 نمایش داده می‌شود و برای جلوگیری از آن، این دستور اضافه‌شده) شما خود برای PRODUCTLINE و CUSTOMERNAME و COUNTRY و YEARS این کار را به ترتیب در سلول های B2 و C2 و E2 و F2 انجام دهیددر هر صورت فرمول‌ها در فایل اکسل پیوست کامل می‌باشد).

ساخت شیت مخصوص گزارش‌ها

نخست فرض کنید می‌خواهیم گزارش‌های مرتبط با محصولات(کد محصولات) را ایجاد کنیم، یک شیت جدید ایجاد کنید با نام ReportProduct، سلول A1 را برای کد محصول کنار بگذارید(در این بخش می‌خواهیم با استفاده از کد یک محصول اطلاعات آن را نمایش دهیم لذا برای اینکه به مشکل بر نخوریم و هر گزارشی که می‌سازیم را بدون مظکل ویرایش کنیم، یک کد محصول را در A1 قرار می‌دهیم). به ترتیب عنوان های زیر را به در شیت قرار دهید:

B1 = ORDERDATE
C1 = QUANTITYORDERED
E1 = ORDERDATE
F1 = SALES
H1 = COUNTRY
I1 = QUANTITYORDERED
K1 = COUNTRY
L1 = SALES

حال فرمول‌های زیر را در مکان‌هایی که مشخص شده قراردهید.

B2 = =UNIQUE(FILTER(RAW!D2:D99999,RAW!F2:F99999=ReportProduct!A1))
C2 = =SUMIFS(RAW!$B$2:$B$99999,RAW!$D$2:$D$99999,ReportProduct!$B2,RAW!$F$2:$F$99999,ReportProduct!$A$1)
E2 = =UNIQUE(FILTER(RAW!D2:D99999,RAW!F2:F99999=ReportProduct!A1))
F2 = =SUMIFS(RAW!$C$2:$C$99999,RAW!$D$2:$D$99999,ReportProduct!$B2,RAW!$F$2:$F$99999,ReportProduct!$A$1)
H2 = =FILTER(UNIQUE(RAW!H2:H99999),UNIQUE(RAW!H2:H99999)<>0)
I2 = =IF(SUMIFS(RAW!$B$2:$B$99999,RAW!$H$2:$H$99999,ReportProduct!$H2,RAW!$F$2:$F$99999,ReportProduct!$A$1)<>0,SUMIFS(RAW!$B$2:$B$99999,RAW!$H$2:$H$99999,ReportProduct!$H2,RAW!$F$2:$F$99999,ReportProduct!$A$1),"")
K2 = =FILTER(UNIQUE(RAW!H2:H99999),UNIQUE(RAW!H2:H99999)<>0)
L2 = =SUMIFS(RAW!$C$2:$C$99999,RAW!$H$2:$H$99999,ReportProduct!$K2,RAW!$F$2:$F$99999,ReportProduct!$A$1)

تعریف هر فرمول(علامت مساوی چسبیده به فرمول را نیز باید درون سلول قراردهید):

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

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

مرحله نخست به پایان رسید حال فایل اکسل را در یک مسیر مشخص ذخیره‌کنید(بهتر است با پسوند xlsm ذخیره شود تا در صورت نیاز به نوشتن کد ماکرو امکان فراهم باشد، اما هیچ اجباری نیست).

ساخت فایل پاورپوینت مخصوص گزارش‌ها(Template)

برنامه پاورپوینت را باز کرده و یک پرزنتیشن جدید ایجاد کنید.

آن را با فرمت pptm ذخیره کنید(کل برنامه‌نویسی در محیط پاورپوینت انچام می‌شود لذا حتما باید از این نوع باشد).

یک اسلاید جدید برای نمایش دو نمودار ایجاد نمایید(می‌توانید از لی‌آوت Layout پیش‌فرض با دو نمودار در یک اسلاید استفاده کنید).

دو نمودار یکی روند خطی Line و یکی نمودار Pie را به اسلاید اضافه کنید(از آنجایی که این اسلاید پایه اسلایدهای اصلی خواهد بود لذا لازم است تمامی تنظیماتی که نیاز است را روی آن انجام داد، بهتر است برای انجام این تنظیمات اطلاعات یک محصول را درون آن قرارداد).