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

چگونه از Data Model در اکسل استفاده کنیم؟

اکسل قادر است داده‌های منابع زیادی را تحلیل کند. در این آموزش یاد خواهید گرفت که چگونه یک Pivot table را با استفاده از 2 جدول و با استفاده از ویژگی‌های Data Model، در اکسل بسازید.

Data Model چیست؟

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

‘Model’ به‌عنوان بخشی از Data Model به ما نشان می‌دهد که روابط بین جداول به چه صورت است. در روش‌های سنتی در اکسل، از فرمول‌ها برای ساخت یک جدول بزرگ که شامل تمام داده‌ها برای تجزیه‌وتحلیل می‌باشند استفاده می‌شود. آن‌ها به این جدول بزرگ نیاز دارند تا Pivot Table ها بتوانند یک جدول واحد را به‌دست بیاورند. بااین‌حال با ایجاد روابط بین جداول، دیگر نیازی به استفاده از فرمول‌های VLOOKUP،SUMIF ،INDEX – MATCH نخواهیم داشت. به‌عبارت‌ دیگر، لازم نیست همه ستون‌ها را در یک جدول واحد قرار دهید. از طریق روابط، Data Model می‌تواند به تمام اطلاعات موردنیاز خود دسترسی پیدا کند. حتی زمانی که در چندین محل یا جداول قرار دارد. پس از ایجاد Data Model، اکسل اطلاعات موجود را در memory خود دارد؛ و با داشتن آن در حافظه خود، می‌توانید به داده‌ها به طرق جدید دسترسی پیدا کنید.

یک کار ساده:

تصور کنید رئیس شما می‌خواهد به بینشی درمورد فروش دست یابد، اما همچنین می‌خواهد جنسیت فروشنده را هم بداند. Data set ای که در زیر مشاهده می‌کنید شامل یک جدولی است که اطلاعات مربوط به فروشنده‌ها که چه چیزی را در چه تاریخی فروخته‌اند و … شامل می‌شود و جدول دیگری نیز می‌بینید که شامل فروشنده و جنسیت آن است. یک راه برای تجزیه‌وتحلیل داده‌های شما این است که از یک فرمول LOOKUP استفاده کنید و یک جدول بزرگ حاوی تمامی اطلاعات را ایجاد کنید و به‌عنوان گام بعدی از یک Pivot Table استفاده کنید تا داده‌ها را بر اساس جنسیت خلاصه کنید.

مزیت‌های Data Model:

1-بررسی و به‌روزرسانی فرمول‌ها هنگام کار با جداولِ بسیار ممکن است اختیاری بشود. بااین‌همه، باید مطمئن شوید که تمام فرمول‌ها در سلول سمت راست پرشده‌اند و بعد از افزودن ستون‌های جدید، فرمول‌های LOOKUP باید که گسترش پیدا کنند. data model نیاز به کار کمی در setup دارد تا یک جدول را ارتباط دهد که برای این کار از یک ستون مشترک در setup استفاده می‌کند. بااین‌حال ستون‌هایی که بعداً اضافه می‌کنید، به‌صورت خودکار به data model اضافه می‌شوند.

2-کار کردن با مقادیر زیاد داده اغلب به دلیل محاسبات، منجر به یک worksheet خیلی کند می‌شود. data model بااین‌حال به مقادیر زیاد داده بدون کند کردن سیستم کامپیوتر شما رسیدگی می‌کند.

3-حدود ردیف (row) در اکسل 2016، 1.048.576 می‌باشد. بااین‌حال مقدار سطرهایی که می‌توانید به حافظه data model اضافه کنید تقریباً نامحدود است. یک محیط 64 بیتی هیچ محدودیت سختی روی اندازه فایل تحمیل نمی‌کند. اندازه workbook تنها به‌وسیله حافظه در دسترس و منابع سیستم محدود می‌شود.

4-اگر داده فقط در data model قرار بگیرد، موجب صرفه‌جویی قابل‌توجهی در اندازه فایل می‌شود.

افزودن داده به Data Model:

اکنون یاد می‌گیرید که چگونه جداول را به Data Model اضافه کنید. برای آغاز مطمئن شوید که داده شما درون یک جدول می‌باشد. با استفاده از Power Query می‌توانید به‌آسانی جداول را درون data model بارگذاری کنید.

بر روی تب Date کلیک کنید—» بر روی سلولی از جدول که می‌خواهید import کنید، کلیک کنید.
From Table/Range را انتخاب کنید. (در تب home در Power Query Editor)

Close & Load را انتخاب کنید—» سپس …Close & Load to

Only Create Connection را انتخاب کنید.
اطمینان حاصل کنید که گزینه Add this data to the Data Model تیک خورده باشد.

این کار باعث می‌شود داده به Data Model اضافه شود. لطفاً مطمئن شوید که این مراحل را برای هر دو جدول انجام دهید.

ایجاد روابط بین داده‌ها:

بعد از افزودن داده‌تان به Data Model، می‌توانید ستون‌های مشترک را به یکدیگر ارتباط دهید. برای ایجاد روابط بین جداول:

به تب Data بروید—» Manage Data Model را انتخاب کنید.

صفحه Power Pivot ظاهر می‌شود.

Diagram view را کلیک کنید. آن به شما نمایش تمام جداول در Data Model را می‌دهد.
سپس ستون مشترک ‘Seller’ در جدول اول را با ستون ‘Seller’ در جدول دوم ارتباط می‌دهد. شما می‌توانید این کار را با کلیک و کشیدن (drag) یک ستون به دیگری انجام دهید. یک رابطه باید ظاهر شود.

توجه:

زمانی که رابطه‌ای را بین دو ستون به وجود می‌آورید واضح است که مقادیر منحصر در یکی از ستون‌ها(Seller در Table2) داشته باشید. این یک رابطه one-to-many نامیده می‌شود. داشتن نسخه تکراری در دو طرف ممکن است موجب خطا شود. در محاسبات پیشرفته روابط many-to-many ممکن است وجود داشته باشد (مثلاً در Power BI). که به دلیل پیچیده بودن در این بخش به آن نمی‌پردازیم. اگر به این موضوعات علاقه دارید درمورد روابط many-to-many تحقیق کنید.

استفاده از Data Model:

اکنون به بخش هیجان‌انگیز می‌رسیم. برای استفاده از Data Model در یک Pivot Table مراحل زیر را انجام دهید:

به تب Insert بروید—-» Pivot Table را کلیک کنید. صفحه ‘Create Pivot Table’ ظاهر می‌شود. به دلیل اینکه یک دیتا مدل دارید اکنون می‌توانید آن را انتخاب کرده و به‌عنوان منبع داده از آن استفاده کنید.
بر روی Use this Workbook’s Data Model کلیک کنید.

در Pivot Table Fields تمامی منابع داده‌ای ممکن را برای PivotTable خود می‌توانید ببینید. آیکن پایگاه داده زردرنگ در گوشه پایین سمت راست جداول نشانه‌گذاری شده، نشان می‌دهد که آن بخشی از Data Model اکسل می‌باشد.

به دلیل اینکه دو جدول با یکدیگر رابطه دارند می‌توانید از فیلدهای دو جدول که در Pivot مشابه هستند استفاده کنید! دوباره جمله قبلی را بخوانید. تعجب‌آور نیست؟!؟ مثال زیر فیلد Sales و Seller از جدول ProductSales را استفاده می‌کند، درحالی‌که فیلد Sex از جدول دیگری می‌آید؛ و شماره‌ها هنوز صحیح هستند!!

باکمک مدلسازی داده شما می‌توانید دیتای چندین جدول را تحلیل کنید. بدون اینکه نیاز به فرمول‌های LOOKUP، SUMIF یا INDEX MATCH برای ترکیب جداول داشته باشید. اما داده آنالیز شده همچنین می‌توانست از یک پایگاه داده، فایل متنی یا بستر cloud دریافت شود.

Power Query یک ابزار شگفت انگیزی برای به حداقل رساندن استفاده از فرمول های LOOKUP می‌باشد. شما می‌توانید از Power Query برای ایجاد ترکیبات منحصر به فرد یا برای تبدیل ستون های انباشته استفاده کنید.