در مقاله قبلی گفتم که دو روش برای نمایش داده بر روی نقشه جغرافیایی وجود دارد. یکی استفاده از قابلیت Shape در اکسل و دیگری استفاده از پاور مپ (Power Map). در این مقاله بر روی نحوه نمایش داده‌ها بر روی نقشه با کمک پاور مپ می‌پردازم.

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

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

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

۱-از منو Data، گزینه New Query و سپس گزینه From Other Source و در نهایت From Web را انتخاب کردم.

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

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

۱-ستون‌های Map و Note را که اطلاعات مهمی نداشتند را پاک کردم.

۲-از ستون Data گزینه From Table را انتخاب کردم. این کار باعث می‌شود که داده‌های موجود در جدول جاری به محیط Power Query‌ منتقل شود. پاکسازی داده در محیط Power Query به سادگی انجام می‌شود.

۳-ستون مساحت (Area) داده‌ای مشابه ۵,۸۳۳ km2(2,252 sq mi) دارد. این داده برای محاسبه مناسب نیست چون ترکیب کاراکتر و عدد است و برای تهیه گزارش فقط به عدد احتیاج داریم. بنابراین بر ستون Area‌ کلیک می‌کنم و بعد گزینه Split را انتخاب می‌کنم. این گزینه، ستون جاری را بر اساس یک جداکننده به دو ستون تبدیل می‌کند. گزینه Custom‌ را انتخاب کردم و در کادر باز شده مقدار km را وارد کردم و به این صورت به پاور کوئری گفتم که این ستون را به دو ستون تبدیل کن. یک ستون قبل از km‌ و یک ستون بعد از km. بعد هم ستونی که حاوی km و مقادیر بعد از آن بود را پاک کردم.

۴-همین کار را برای ستون تراکم (Density) هم انجام دادم. با این تفاوت که “/” را به عنوان جداکننده معرفی کردم.

۵-در نهایت هم Load&Close را انتخاب کردم.

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

در مرحله آخر، برای نمایش داده‌ها بر روی نقشه (پاورمپ) باید داده‌ها را به Power Map (پاور مپ) اضافه کرده و تنظیمات لازم را انجام دهم. برای انجام این کار مراحل زیر را انجام دادم.

۱-از منو Insert گزینه ۳D Maps را انتخاب کردم. با انجام این کار داده‌ها به فضای پاورمپ اضافه می‌شود.

۲-ستونی در سمت راست نمایش داده می‌شود. در قسمت Location، گزینه Add Field را انتخاب کرده و سپس گزینه Capital را انتخاب کردم. با این کار برای پاور مپ لیست مشخص کردم که لیست نقاط جغرافیایی مورد نظر من بر اساس ستون Capital است. ستون Capital در این مثال به مرکز استان اشاره می‌کند.

۳-بعد از آن باید مقداری که در هر استان نمایش داده شود را مشخص کنم. در مرحله اول قصد دارم که مساحت هر استان بر روی نام آن نمایش داده شود. بنابراین در قسمت Height‌، گزینه Add Filed و بعد Area را انتخاب کردم.

۴-نحوه نمایش داده‌ها به صورت پیش فرض میله‌ای است. نحوه نمایش را به حبابی تغییر می‌دهم. نمودار حبابی بر روی نقشه خواناتر است. اندازه هر حباب بسیار بزرگ بود و خوانایی گزارش را کاهش داده بود. بنابراین از قسمت Option، گزینه Size را انتخاب کردم و سایز را کاهش دادم. این کار باعث شد تا مقیاس نمایش حباب‌ها کوچک تر شود.

۵- با کلیک بر روی Map Labels نام هر شهر بر روی نقشه نمایش داده می‌شود.

۶-در صورتی که موس را بر روی هر حباب(دایره) نگه دارید، نام نقطه جغرافیایی و مساحت آن استان نمایش داده‌ می‌شود.

می‌توانید مقدار فروش در هر استان را با مساحت یا جمعیت استان جایگزین کنید و گزارش مقدار فروش در هر استان را بر روی نقشه نمایش دهید.

 

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

برای نمایش داده‌ها بر روی نقشه دو روش اصلی وجود دارد استفاده از قابلیت Shape‌ در اکسل و قابلیت Power Map . در روش Shape‌ یک تصویر به اکسل اضافه می‌شود و سپس با کمک ابزار FreeForm شکل تصویر کشیده می‌شود و در روش Power Map از نقشه سایت Bing‌ استفاده می‌شود. در این مقاله روش اول بررسی می‌شود.

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

 

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

در مرحله بعد هم تصویر نقشه را که از اینترنت پیدا کرده بودم را با دکمه Delete روی کیبورد حذف کردم. با کلیدهای Ctrl + A‌ تمام Shape‌هایی که کشیده بودم را انتخاب کردم، سپس کلیک راست و گزینه Group. حالا یک Shape دارم که به شکل نقشه ایران است.

 

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

 

 

کنار نقشه رده بندی فروش و رنگ آن را مشخص کردم. در صورتی که فروش هر استان بین ۱ تا ۲۰ باشد، استان به رنگ قرمز نمایش داده شود. در صورتی که فروش استان بین ۲۰ تا ۷۰ باشد به رنگ زرد نمایش داده شود و در صورتی که بیشتر از ۷۰ باشد، به رنگ سبز نمایش دارد.

 

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

 

 

 

سپس با کمک ماکروها و اضافه کردن کد زیر ، رنگ رده بندی را به نقشه اضافه کردم.

 

 

Sub Macro1()

For i = 1 To 30
Ostan = Cells(i + 1, 3)
Colors = Cells(i + 1, 5)

ActiveSheet.Shapes.Range(Array(Ostan)).Select
Selection.ShapeRange.Fill.ForeColor.RGB = Cells(2 + Colors, 18).Interior.Color

Next i
End Sub

 

 

 

نتیجه نهایی به شکل زیر شد.

 

 

 

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

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

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

۱-جدول، نمودار یا پیوت تیبل را انتخاب کنید و بعد از منو 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‌ کلیک کردم. سلسله مراتب تاریخ در سمت چپ نمایش داده می‌شود.

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

 

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

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

 

 

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

 

 

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

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

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

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

دو ستون آخر هم برای ساخت سلسله مراتب مورد استفاده قرار نمیگیرد، بنابراین آنها را هم پاک کردم.

 

 

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

۱- تمام داده ها را انتخاب کرده و از تب Insert گزینه Table را انتخاب کردم.

۲- همانطور که داده‌ها در حالت انتخاب بودند، از تب Data گزینه From Table را انتخاب کردم تا داده‌ها به فضای پاور کوئری اضافه شود.

۳- در فضای پاور کوئری بر روی زبانه کوچک کنار ستون Abadi_Name کلیک کرده و تیک کنار Null را برداشتم. با این کار تمام سطرهای حاوی مقدار Null حذف می‌شوند. در نهایت Load and Close را کلیک کردم تا به فضای اکسل بر گردم.

 

 

 

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

۱- ابتدا داده‌ها را انتخاب کرده و سپس از تب Power Pivot گزینه Add to Data Model را انتخاب کردم.

۲- سپس نحوه نمایش را به Diagram View تغییر دادم.

۳- بر روی علامت گوشه بالا سمت راست جدول کلیک کرده و نام Hierarchy  را به GEO تغییر دادم و سپس به ترتیب نام ستون‌های Ostan_Name, Shahrestan_Name, Bakhsh_Name, Dehestan_Name, Abadi_Name را درگ کرده و بر روی نام Geo  رها  کردم. سلسله مراتب ساخته شد.

۴- بر روی Pivot Table کلیک کردم.

۵- گزینه GEO را در Pivot Table Field تیک زدم تا سلسله مراتب استان، شهرستان، بخش، دهستان و آبادی نمایش داده شود.

 

 

 

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

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

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

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

در سطح دوم، مدیر فروش مستقیم، مدیر فروش اینترنتی و مدیر فروش تلفنی قرار دارند که به ترتیب با آی‌دی های ۲، ۵ و ۷ مشخص شده‌اند. از آنجاییکه سطح بالا این سه مدیر یا اصطلاحا والد آنها مدیرعامل است، ستون Parent آنها با عبارت مدیرعامل مشخص شده است.

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

تا الان فهمیدم که یک ساختار سلسه مراتبی چگونه تعریف می‌شود و چگونه جدول آن ساخته می‌شود. با ساختن صرفا جدول، نمی توانیم از سلسه مراتب در گزارش‌ها استفاده کنیم، بلکه باید سلسله مراتب (Hierarchy) را در مدل داده‌ای نیز تعریف کنیم. چگونه این کار را انجام می‌دهیم؟ در ادامه مقاله بررسی می‌کنیم.

فرض کنید که جدول فروش به صورت زیر داریم

این جدول داری سه ستون است. ستون اول با نام ID، آی‌دی هر سطر را مشخص می‌کند. ستون دوم با نام PersonnelID به آی‌دی پرسنلی اشاره می‌کند و ستون سوم با نام Sale Value مقدار فروش را مشخص می‌کند. به عنوان مثال سطر اول مشخص می‌کند که آی‌دی پرسنلی ۳ ( کارمند فروش مستقیم ۱) مقدار ۵۰۰ واحد فروش داشته است.

 

 

 

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

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

برای ساخت چنین گزارشی باید سلسله مراتب (Hierarchy) تعریف شود.

برای ساخت سلسه مراتب (Hierarchy) ابتدا مطابق آموزش مقاله، به تب Power Pivot بروید و گزینه Add to DataModel را کلیک کنید. این کار را برای هر دو جدول Sale و Personnel انجام دهید. سپس حالت نمایش را به حالت Diagram View تغییر دهید. طبق مقاله ارتباط بین دو جدول را برقرار کنید. برای این کار کافی است تا بر روی نام PesoonelId در جدول Sale کلیک کنید و آن را به سمت ستون ID در جدول Personnel کشیده و رها کنید. در این صورت ارتباط بین دو جدول برقرار می‌شود.

در مرحله بعد بر روی علامت مشخص شده در تصویر زیر کلیک کنید و گزینه Create Hierarchy را کلیک کنید.

یک سلسله مراتب جدید با نام Hierarchy1 ساخته می‌‌شود. به ترتیب ابتدا ستون Parent را انتخاب کنید و به سمت Hierarchy1 کشیده و رها کنید. سپس ستون Title را انتخاب کنید و به سمت Hierarchy1 کشیده و رها کنید.

نهایتا سلسله مراتب شما ساخته شد.

بر روی گزینه Pivot Table کلیک کنید تا داده ها را به شکل زیر مشاهده کنید.

بر روی Hierarchy1 و Sale Value کلیک کنید تا گزارشی مشابه تصویر زیر ببینید.

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

فایل را می‌توانید از اینجا دانلود کنید.

 

 

 

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

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

مدل داده‌ای در اکسل چیست؟

با کمک مفهوم مدل داده‌ای و ابزار پاور پیوت، می‌توان هر کدام از شیت‌های اکسل را به حافظه اکسل بارگزاری کرد و سپس با یک درگ اند دراپ ساده، بین ستون‌های مشترک ارتباط برقرار کرد. قبل از تولد ابزار پاور پیوت، اکسل کاران از فرمول جهت ارتباط بین شیت‌ها استفاده می‌کردند. گاهی این ارتباطات پیچیده بود و اکسل کاران مجبور بودند بارها از توابعی چون VlOOKUP, SUMIF, INDEX … استفاده کنند. اما با کمک این ابزار، ارتباط بین شیت‌ها به راحتی درگ اند دراپ انجام می‌شود.

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

 

 

شیت‌ها حتما باید به صورت جدول باشند. شیت فروش (Sale) به صورت جدول است. اما شیت کارمندان (Employee) به صورت جدول نیست. در اولین گام باید این شیت را به جدول تبدیل کنیم. برای تبدیل داده‌های این شیت به جدول کافی است که ابتدا تمام داده‌ها را انتخاب کنیم و سپس از منو Insert گزینه Table را انتخاب کنیم. داده‌ها به جدول تبدیل می شوند.

 

 

در مرحله بعدی باید هر دو جدول Sale و Employee باید به دیتا مدل اضافه شوند. برای اضافه کردن این دو جدول کافی است آنها را انتخاب کنیم و سپس از منو Power Pivot گزینه Add to Data Model را انتخاب کنید. اگر که منو Power Pivot را مشاهده نمیکنید، باید طبق این مقاله آن را فعال کنید.

 

پس از اضافه کردن هر دو جدول به مدل داده‌ای در پاور پیوت، تصویری مشابه تصویر زیر را مشاهده میکنید.

1. آیکون پاور پیوت
   
2. دو جدول Sale و Employee که به فضای پاور پیوت اضافه شده است.
   
3. داده‌های جدول Employee
   
4. انواع روش‌های نمایش مدل داده‌ای. در حال حاضر نمایش داده‌ای در حالت Data View قرار دارد. اما در مرحله بعدی و ساخت ارتباط بین دو جدول Sale و Employee از حالت Diagram View استفاده می‌کنیم.
   

بر روی گزینه Diagram View که در تصویر بالا با شماره ۴ مشخص شده است کلیک ‌کنید. نحوه نمایش جداول تغییر می‌کنید و به صورت زیر نمایش داده می‌شود. جهت ارتباط بین این دو جدول کافی است که بر روی EmployeeID در جدول Sale کلیک کنید و آن را به سمت Id در جدول Employee بکشید و رها کنید. ستون EmployeeId در جدول Sale حاوی شماره پرسنلی هر کدام از پرسنل است. ستون Id در جدول Employee نیز حاوی شماره پرسنلی هر کدام از پرسنل است. بنابراین اطلاعات موجود در این دو ستون یکسان است و می‌توان این دو را به هم متصل کرد.

برای تهیه گزارش کافی است بر روی گزینه PivotTable که در تصویر زیر مشخص شده است کلیک کنید. در ابتدا از شما سوال می‌پرسد که PivotTable در یک شیت جدید ساخته شود و یا بر روی شیت جاری. گزینه شیت جدید را انتخاب کنید.

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

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

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

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

مشکلات زیر در داده وجود دارد که قصد داریم آنها را مرتب کنیم

 

1. داده ها با توجه به تاریخ دسته بندی شده اند و تاریخ مربوط به هر دسته یک بار و آن هم در ابتدای هر دسته تکرار شده است. در حالی که برای تحلیل، تاریخ در هر سطر باید مشخص باشد.
   
2. فضای خالی پشت نام فروشنده وجود دارد.
   
3. نام فروشنده و شماره پرسنلی فروشنده با هم ترکیب شده است. برای تحلیل بهتر است که نام فروشنده و شماره پرسنلی در دو ستون جدا باشد.
   
4. سطرهای خالی در بین داده ها وجود دارد که باید حذف شوند.
   
5. مقدار فروش با توجه به شهر، در چهار ستون مختلف قرار گرفته است. برای تحلیل ما نیاز داریم که مقدار فروش در یک ستون باشد و در ستون دیگر نام شهر مشخص شود.
   
6. سطری به نام مجموع وجود دارد که ما به آن نیاز نداریم و باید حذف شود.
   

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

ایمپورت کردن داده

در گام اول باید داده موجود در فایل اکسل را به پاور کوئری وارد کنیم. جهت این کار ابتدا وارد تب Data شوید و سپس مراحل زیر را طی کنید.

Data --> New Query --> From File --> From WorkBook

سپس فایل اکسل را انتخاب کنید.

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

ویرایشگر پاور کوئری

بعد از آنکه گزینه Edit را انتخاب کردید، ویرایشگر پاور کوئری مشابه تصویر زیر ظاهر می‌شود. هر تغییری که بر روی داده‌ها اعمال کنید، به صورت گام به گام بر روی ستون سمت راست که با فلش مشخص شده است، ثبت می‌شود.

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

 

جداسازی تاریخ

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

بر روی ستون کلیک راست کنید. از منو ظاهر شده گزینه Split Column را انتخاب کنید و سپس By Delimiter را انتخاب کنید.

 

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

 

 

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

 

جدا کردن نام فروشنده و شماره پرسنلی

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

 

ستون جدید، هنوز پرانتز بسته را دارد. بنابراین با کمک گزینه Replace Values پرانتز بسته را حذف می‌کنیم.

 

 

حذف فضای خالی قبل از نام فروشنده

برای جذف فضای خالی قبل از نام فروشنده کافی است ستون مورد نظر را انتخاب کنید و مطابق تصویر زیر عمل نمایید.

 

 

حذف سطرهای غیر ضروری

ستون مورد نظر را انتخاب کنید و مطابق تصویر زیر عمل نمایید.

 

 

تغییر نام ستون ها

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

 

 

آنپیوت کردن نام شهر

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

 

 

تغییر فرمت تاریخ

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

 

 

در نهایت هم گزینه Close & Load را کلیک کنید.

 

 

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

به سادگی. فقط کافی است که مراحل زیر را طی کنید.

۱-  طبق مقاله “نصب ابزارهای هوش تجاری در اکسل ۲۰۱۶” ابزار پاور کوئری را در اکسل نصب کنید.

۲- مطابق تصویر به آدرس زیر بروید و بر روی گزینه From Web کلیک کنید.

Data --> New Query --> From Other Sources --> From Web

 

 

 

 

۳- به سایت ورزش ۳ بروید و در آنجا دنبال جدول لیگ برتر بگردید. آدرس صفحه را کپی کنید و مطابق تصویر در کادر زیر URL وارد کنید. سپس گزینه OK‌را کلیک کنید. می‌توانید از آدرس زیر استفاده کنید.

https://www.varzesh3.com/table/%D8%AC%D8%AF%D9%88%D9%84-%D9%84%DB%8C%DA%AF-%D8%A8%D8%B1%D8%AA%D8%B1-98-97

دقت کنید که نمی‌توانید از آدرس‌های کوتاه شده استفاده کنید.

 

۴ – بر روی گزینه Connect کلیک کنید.

 

 

۵- اکسل ادرسی را که شما وارد کردید را بررسی می‌کند و اطلاعاتی را که به فرمت جدول هستند را در ستون سمت چپ مطابق تصویر مشخص می‌کند. در آدرس فوق دو جدول قرار دارد. یکی جدولی که در تصویر زیر با نام “Document” مشخص شده است و دیگری جدولی که با نام “جدول لیگ برتر (۹۷-۹۸)” مشخص شده است. در ستون سمت راست نمونه‌ای از اطلاعات هر کدام از جداول نمایش داده شده است. همانطور که در تصویر می‌بینید جدول Document فاقد اطلاعات مورد نظر ما است. اما “جدول لیگ برتر (۹۷-۹۸)” دقیقا حاوی اطلاعات مورد نظر ما است. ابتدا این جدول را‌ در حالت انتخاب شده قرار دهید و سپس دکمه Load را کلیک کنید.

 

 

 

۶- تبریک می گویم. شما اطلاعات جدول لیگ برتر را در اکسل وارد کردید.

 

۷- می‌توانید با توجه به این داده‌ها نمودارهای متنوعی رسم کنید. به عنوان مثال در تصویر زیر تعداد برد/باخت/تساوی تیم‌ها با هم مقایسه شده است. برای این کار کافی است ستون‌های  نام تیم/ برد/باخت/ تساوی را انتخاب کنید و مسیر زیر را طی کنید.

Insert --> Recommanded Charts --> All Chart --> Column

نهایتا نموداری به صورت زیر رسم می‌گردد.

 

 

و از نمای نزدیک تر