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

چطور اسلایسر را به اکسل اضافه کنیم؟

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

۱-جدول، نمودار یا پیوت تیبل را انتخاب کنید و بعد از منو Insert‌ گزینه slicer را انتخاب کنید. ستونی که قصد فیلتر کردن دارید را مشخص کنید و بعد دکمه Ok را کلیک کنید. به همین سادگی فیلتر به جدول/نمودار/پیوت تیبل شما اضافه شد.

۲-پیوت تیبل را انتخاب کنید. در سمت راست جدولی با نام Pivot Table Fields نمایش داده می‌شود. بر روی فیلدی که قرار است فیلتر شود کلیک راست کنید و گزینه Add Slicer را کلیک کنید. اسلایسر در کنار پیوت تیبل نمایش داده می‌شود.

 

۳-بر روی پیوت تیبل کلیک کنیدو تب Analyze در انتها ظاهر می‌شود. بر روی آن کلیک کنید و بعد هم گزینه Insert Slicer را کلیک کنید. از منویی که باز می‌شود، فیلدی که می‌خواهید اسلایسر برای آن تعریف شود را انتخاب کنید و تمام. اسلایسر به صفحه شما اضافه شد.

انتخاب چند گزینه در فیلتر

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

 

نمودار تعاملی با اسلایسر

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

 

 

 

چند اسلایسر برای یک پیوت تیبل یا جدول

خیلی راحت می‌توانید چند تا فیلتر برای پیوت تیبل یا جدول تعریف کنید. به عنوان مثال هم برای سال و هم استان اسلایسر تعریف کنید.

 

سفارشی کردن اسلایسر

وقتی که بر روی اسلایسر کلیک کنید، یک تب به نام Option‌ در انتها اضافه می‌شود. با کمک گزینه‌هایی موجود در این قسمت ‌می‌توانید اسلایسر را سفارشی کنید. به عنوان مثال:
-نام اسلایسر را تغییر دهید. در مثال زیر من از اسلایسر Ostan_Name برای فیلتر استان‌ها استفاده کردم. با کمک گزینه Caption‌ در Slicer Setting نام نمایشی را به “نام استان” تغییر دهید.
-ممکن است فروش در برخی استان‌ها انجام نشده باشد. بهتر است که این استان‌ها در اسلایسر نمایش داده نشود. برای این کار کافی است که تیک کنار گزینه Hide Items with no data از قسمت Slicer Setting را کلیک کنید.
-می‌توانید رنگ بندی اسلایسر را در قسمت Slicer Style تغییر دهید.
-می‌توانید مشخص کنید که اسلایسر به جای یک ستون، در چند ستون نمایش داده شود.

 

 

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

برای این کار یک فایل اکسل آماده کردم که در آن مقدار فروش در هر استان و در هر سال مشخص است.

 قصد دارم گزارشی آماده کنم که مقدار فروش به تفکیک هر سال و هر استان مطابق تصویر زیر نمایش داده شود.

 

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

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

 

 

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

۱-فایل اکسل تقسیمات کشوری که از نت دانلود کرده بودم را به فضای اکسل اضافه کردم.

۲-بقیه اطلاعات مربوط به آبادی، شهر، دهستان و بخش اضافه بود بنابراین آنها را حذف کردم.

۳-با حذف ستون‌های مربوط به آبادی، شهر، دهستان و بخش کلی از سطرها تکراری می‌شوند، برای حذف این تکراری به تب Data رفتم و بر روی گزینه Remove Duplicate‌ کلیک کردم. سطرهایی که مقدار تکراری داشتند، حذف شدند.

۴-با حذف سطرهای تکراری، ممکن است که برخی سلول‌ها مقدار Blank یا Null داشته باشند. برای حذف سلول‌های اینچنینی باید با کلیک بر روی گزینه From Table‌ در تب Data داده‌های موجود را به فضای پاور کوئری ببریم. در آنجا سلول‌های حاوی مقادیر Null یا Blank را فیلتر می‌کنیم.

۵-در محیط پاور کوئری با کلیک بر روی تب Add Column‌ و بعد Add Index Column برای جدول ایندکس تعریف می‌کنیم. نام ستون ایندکس را به Id تغییر میدیم. و بعد با کلیک بر روی گزینه Close&Load در تب Home به فضای اکسل برمیگردیم.

 

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

 

فایل نهایی را از اینجا دانلود کنید.

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

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

در مرحله بعد باید جدول تاریخ را پیاده سازی کرده و داد‌های مرتبط را در آن درج کنیم. از آنجاییکه برخلاف مقاله قبلی که جدول و داده‌های مربوط به سلسله مراتب جغرافیایی در اینترنت موجود بود، جدول و داده‌های مربوط به سلسله مراتب تاریخ در اینترنت موجود نیست. بنابراین دست به کار شدم و نمونه جدول و داده های آن برای سال ۱۳۹۷ و ۱۳۹۸ را ساختم. برای ساخت این جدول، مراحل زیر را طی کردم:

۱- شش سر ستون به نام‌های Id, Year, HalfYear, Season, Month, Day ساختم. Id شمارنده سطرها است و بقیه سرستون‌ها به ترتیب به سال، ماه، نیمسال، فصل، ماه و روز اشاره می‌کند. از فروردین ۹۷ شروع کردم. فروردین ۹۷، ۳۱ روز داشت. در ستون Day، ۳۱ روز را درج کردم.
۲-در ستون مربوط به ماه، فصل، نیمسال و سال، به ترتیب مقادیر فروردین، بهار، ۱ و ۱۳۹۷ را یادداشت کردم، کپی کردم و برای تمام ۳۱ روز فروردین Paste کردم.
۳-مشابه همین کار را برای تمام ماه‌های سال ۱۳۹۷ و ۱۳۹۸ انجام دادم.
نتیجه را می‌توانید از اینجا دانلود کنید.

جدول تاریخی که در اینجا ساختم صرفا مربوط به سال ۱۳۹۷ و ۱۳۹۸ و داده‌های شمسی است. می‌توانید این جدول را توسعه دهید و در هر سطر تاریخ میلادی و قمری معادل آن را هم نمایش دهید، می‌توانید سال‌های بیشتر به آن اضافه کنید. مثلا از سال ۱۳۸۰ تا ۱۴۰۰٫ یا اینکه سطوح بیشتر در آن در نظر بگیرید. مثلا به بالای سطح سال یک سطح اضافه کنید به نام دهه. دهه ۸۰، دهه ۷۰ یا دهه ۹۰٫ یا اینکه فرمت‌های نمایش مختلف به آن اضافه کنید مثلا فرمت نمایش ” ۰۱/۰۱/۱۳۹۷″ یا ” اول فرودین هزار سیصد و نود و هفت”.
من از ساده‌ترین و دم دست ترین روش برای ساخت این جدول استفاده کردم. اما شما می‌توانید این جدول را با کمک توابع اکسل و ابزارهای حرفه‌ای تر مثل اس کیو ال سرور نیز بسازید.

بعد از اینکه جدول تاریخ ساخته شد، باید داده‌ها را به فضای پاور پیوت اضافه کرده و سپس هایرارکی را بسازم. برای این کار مراحل زیر را طی کردم.
۱-ابتدا داده‌ها را انتخاب کردم. دقت کنید که فقط باید سطرهای حاوی داده انتخاب شود و نه سطرهای خالی. چون اگر که سطر خالی انتخاب کنیم، در سلسله مراتب نهایی یک سطح blank خواهیم داشت. چون انتخاب فقط سطرهای حاوی داده سخت بود، برای راحتی کار من از تب View گزینه Page Break Preview را انتخاب کردم تا فقط سطرهای حاوی داده انتخاب شود.
۲-در مرحله داده‌های انتخاب شده را باید به جدول تبدیل می‌کردم. بنابراین در حالی که داد‌ه‌ها در حالت انتخاب بودند، از تب Insert گزینه Table را انتخاب کرد.
۳-در مرحله بعد باید داده‌ها به محیط پاورپیوت اضافه شوند. در حالی که داده‌ها در حالت انتخاب هستند از تب Power Pivot‌ گزینه Add to Data Model را انتخاب کردم.
۴-در محیط پاور پیوت نحوه نمایش را به Diagram View تغییر دادم. بر روی علامت در گوشه بالا سمت راست جدول کلیک کردم و گزینه Create Hierarchy را کلیک کردم و نام آن را به Date تغییر دادم. سرستون‌های Year, HalfYear, Season, Month, Day را به ترتیب درگ کرده و بر روی رها کردم.
۵-از تب Home گزینه PivotTable را انتخاب کردم.
۶-گزینه Date را در Pivot Table Filed‌ کلیک کردم. سلسله مراتب تاریخ در سمت چپ نمایش داده می‌شود.

فایل اکسل نهایی را از اینجا دانلود کنید.