پاکسازی داده با کمک پاور کوئری

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

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

 

  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

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

 

 

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

 

 

 

تحلیل افزوده

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

سوال اول و اصلی این بود که تحلیل افزوده چیست؟ و چه فرقی با تحلیل معمولی دارد؟

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

حالا در فضای هوش تجاری، الگوریتم یادگیری ماشین چه گره‌ای را باز میکند؟ تقریبا تمام کسانی که درگیر پروژ‌ه‌های هوش تجاری هستند، اذعان دارند که آماده کردن داده (Data Preparation) ، یکی از مراحل بسیار زمان بر و تکراری پروژه‌های هوش تجاری است. الگوریتم‌های هوش تجاری در این مرحله به کمک توسعه دهندگان و تحلیل گران میآیند و آنها را از شر بسیاری از کارهای تکراری نجات می‌دهد. به عنوان مثال فرض کنید که ۱۰۰ فایل اکسل دارید که در همه آنها شهر علی آباد به اشتباه سرهم (علیاباد) نوشته شده است. پیشتر تحلیل‌گر یا توسعه دهنده باید زمان زیادی صرف تصحیح این اشتباه می‌کرد. اما الگوریتم‌های یادگیری ماشین مسولیت تصحیح چنین کارهای تکراری و زمانبری را برعهده گرفته اند.

در تحلیل افزوده امکان پرس و جو به زبان طبیعی وجود دارد. به عنوان مثال اگر می‌خواهید قیمت متوسط اجناس را بدانید، فقط کافی که عبارت “Whats the average price of this item” را تایپ کنید. خود الگوریتم‌ عبارت شما را به اس کیو ال تبدیل می‌کند، اجرا می‌کند و نتیجه را برای شما نمایش می‌دهد. (متاسفانه بلاد کفر کلا زبان فارسی را نادیده گرفتند و امکان پرس و جو به زبان فارسی را در سیستم‌های هوش تجاری تعبیه نکردند.)

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

 

 

 

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

هوش تجاری چیست؟

هوش تجاری چیست؟

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

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

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

هر چند که تعریف گارتنر معتبر و مورد قبول است. اما بیشتر به زیرساخت فنی و تکنولوژی هوش تجاری اشاره می‌کند و جنبه‌ مدیریتی هوش تجاری را در نظر نگرفته است. توربن در کتاب “هوش تجاری، یک رویکرد مدیریتی” این جنبه از هوش تجاری را نیز در نظر گرفته و معتقد است که هوش تجاری یک رویکرد مدیریتی مبتنی بر داده است.

به نظر من تعریف دقیق هوش تجاری ترکیبی از دو تعریف بالا است. هوش تجاری در واقع یک رویکرد مدیریتی است که بر اساس آن مدیران سازمان نه بر اساس حدس و گمان بلکه بر اساس داده‌های واقعی موجود در داخل یا خارج سازمان، فکر می‌کنند و تصمیم می‌گیرند. اما بر اساس کدام داده‌ها؟ این داده‌ها چگونه، از کجا و طی چه فرآیندی جمع آوری می‌شوند و در کجا یکپارچه می‌شوند. اینجا است که تعریف گارتنر به کار میآید و هوش تجاری را مجموعه ای از ساختارها، ابزارها و برنامه‌های کاربردی می داند که امکان دسترسی، جمع آوری و یکپارچه سازی داده‌ها را فراهم می‌کند. “پاور بی آی”، “تبلو”، “کلیک ویو”، “انبار داده”، “داشبورد مدیریتی”، “Cube”، “Olap” … همه جزئی از همان ساختار، ابزار و برنامه های کاربردی هستند که امکان جمع آوری و یکپارچه کردن داده را فراهم می‌کنند اما به هیچ وجه معادل هوش تجاری نیست و فقط به یک وجه آن اشاره می‌کنند.

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