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

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

 

 

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

 

 

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

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

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

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

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