مزایای Pivot توسط مدل داده

هنگام ساختن یک جدول محوری در اکسل، در اولین کادر محاوره ای، جایی که از ما خواسته می شود محدوده اولیه را تنظیم کنیم و مکانی را برای درج جدول محوری انتخاب کنیم، یک چک باکس نامحسوس اما بسیار مهم در زیر وجود دارد. این داده ها را به Data Model اضافه کنید (این داده ها را اضافه کنید به مدل داده) و کمی بالاتر سوئیچ از مدل داده این کتاب استفاده کنید (از مدل داده این کتاب کار استفاده کنید):

متأسفانه بسیاری از کاربرانی که مدت هاست با جدول های محوری آشنا هستند و با موفقیت از آنها در کار خود استفاده می کنند، گاهی اوقات واقعاً معنای این گزینه ها را درک نمی کنند و هرگز از آنها استفاده نمی کنند. و بیهوده از این گذشته، ایجاد یک جدول محوری برای مدل داده چندین مزیت بسیار مهم در مقایسه با جدول محوری کلاسیک اکسل به ما می دهد.

با این حال، قبل از در نظر گرفتن این "نان ها" از نزدیک، اجازه دهید ابتدا بفهمیم که در واقع این مدل داده چیست؟

مدل داده چیست؟

مدل داده (به اختصار MD یا DM = Data Model) یک ناحیه ویژه در داخل یک فایل اکسل است که می توانید داده های جدولی را در آن ذخیره کنید - یک یا چند جدول در صورت تمایل به یکدیگر مرتبط شده اند. در واقع، این یک پایگاه داده کوچک (مکعب OLAP) است که در یک کتاب کار اکسل تعبیه شده است. در مقایسه با ذخیره سازی کلاسیک داده ها به شکل جداول معمولی (یا هوشمند) روی برگه های خود اکسل، مدل داده چندین مزیت قابل توجه دارد:

• جداول می تواند تا 2 میلیارد خطو یک برگه اکسل می تواند کمی بیشتر از 1 میلیون جا بگیرد.
• با وجود اندازه غول پیکر، پردازش چنین جداول (فیلتر کردن، مرتب سازی، محاسبات روی آنها، خلاصه ساختمان و غیره) انجام می شود. خیلی سریع بسیار سریعتر از خود اکسل.
• با داده های مدل، می توانید محاسبات اضافی (در صورت تمایل، بسیار پیچیده) را با استفاده از آن انجام دهید زبان داخلی DAX.
  
• تمام اطلاعات بارگذاری شده در مدل داده بسیار است به شدت فشرده شده است با استفاده از یک بایگانی داخلی خاص و نسبتاً متوسط ​​​​اندازه فایل اکسل اصلی را افزایش می دهد.
  

مدل توسط یک افزونه ویژه ساخته شده در Microsoft Excel مدیریت و محاسبه می شود - powerpivotکه قبلاً در مورد آن نوشته ام. برای فعال کردن آن، در برگه توسعه دهنده کلیک افزونه های COM (توسعه دهنده - افزونه های COM) و کادر مربوطه را علامت بزنید:

اگر زبانه ها توسعه دهنده (توسعه دهنده)شما نمی توانید آن را روی روبان ببینید، می توانید آن را از طریق روشن کنید فایل - گزینه ها - راه اندازی نوار (فایل - گزینه ها - سفارشی کردن نوار). اگر در پنجره نشان داده شده در بالا در لیست افزونه های COM، Power Pivot ندارید، در نسخه Microsoft Office شما گنجانده نشده است.

در تب Power Pivot که ظاهر می شود، یک دکمه سبز روشن بزرگ وجود دارد مدیریت (مدیریت)، با کلیک بر روی آن پنجره Power Pivot در بالای اکسل باز می شود که در آنجا محتویات مدل داده کتاب فعلی را مشاهده خواهیم کرد:

یک نکته مهم در این راه: یک کتاب کار اکسل فقط می تواند شامل یک مدل داده باشد.

جداول را در مدل داده بارگیری کنید

برای بارگذاری داده ها در مدل، ابتدا جدول را به یک میانبر صفحه کلید پویا "هوشمند" تبدیل می کنیم کلیدهای Ctrl+T و در برگه یک نام دوستانه به آن بدهید سازنده (طرح). این یک مرحله ضروری است.

سپس می توانید از یکی از سه روش زیر استفاده کنید:

• دکمه را فشار دهید اضافه کردن به مدل (افزودن به مدل داده) برگ powerpivot برگ صفحه اصلی (خانه).
  
• انتخاب تیم ها درج - PivotTable (درج - جدول محوری) و چک باکس را روشن کنید این داده ها را به Data Model اضافه کنید (این داده ها را به مدل داده اضافه کنید). در این حالت، با توجه به داده های بارگذاری شده در Model، یک جدول محوری نیز بلافاصله ساخته می شود.
  
• در برگه Advanced داده ها (تاریخ) روی دکمه کلیک کنید از جدول/محدوده (از جدول/محدوده)تا جدول خود را در ویرایشگر Power Query بارگیری کنیم. این مسیر طولانی ترین است، اما، در صورت تمایل، در اینجا می توانید تمیز کردن داده های اضافی، ویرایش و انواع تبدیل ها را انجام دهید که در آن Power Query بسیار قوی است.

  سپس داده های شانه شده توسط دستور در Model آپلود می شوند صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…). در پنجره باز شده گزینه را انتخاب کنید فقط یک اتصال ایجاد کنید (فقط ایجاد اتصال) و مهمتر از همه، یک تیک بگذارید این داده ها را به Data Model اضافه کنید (این داده ها را به مدل داده اضافه کنید).
  

خلاصه ای از مدل داده را می سازیم

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

• دکمه را فشار دهید جدول جمع بندی (جدول محوری) در پنجره Power Pivot.
• دستورات را در اکسل انتخاب کنید درج - PivotTable و به حالت تغییر دهید از مدل داده این کتاب استفاده کنید (درج - جدول محوری - از مدل داده این کتاب کار استفاده کنید).
• انتخاب تیم ها درج - PivotTable (درج - جدول محوری) و چک باکس را روشن کنید این داده ها را به Data Model اضافه کنید (این داده ها را به مدل داده اضافه کنید). جدول "هوشمند" فعلی در مدل بارگذاری می شود و یک جدول خلاصه برای کل مدل ساخته می شود.

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

مزیت 1: روابط بین جداول بدون استفاده از فرمول

یک خلاصه معمولی فقط با استفاده از داده های یک جدول منبع ساخته می شود. اگر چندین مورد از آنها را دارید، به عنوان مثال، فروش، لیست قیمت، فهرست مشتریان، ثبت قراردادها و غیره، ابتدا باید داده ها را از همه جداول با استفاده از توابعی مانند VLOOKUP جمع آوری کنید. (VLOOKUP)، فهرست مطالب (فهرست مطالب)، بیشتر در معرض (همخوانی داشتن)SUMMESLIMN (SUMIFS) و مانند آن این طولانی، خسته کننده است و اکسل شما را به یک "فکر" با مقدار زیادی داده سوق می دهد.

در مورد خلاصه ای از مدل داده، همه چیز بسیار ساده تر است. کافی است یک بار در پنجره Power Pivot روابط بین جداول را تنظیم کنید - و تمام شد. برای انجام این کار، در برگه powerpivot دکمه را فشار دهید مدیریت (مدیریت) و سپس در پنجره ای که ظاهر می شود - دکمه نمای نمودار (نمایش نمودار). برای ایجاد پیوندها، باید نام ستون‌ها (فیلدها) رایج (کلید) را بین جداول بکشید:

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

مزیت 2: شمارش مقادیر منحصر به فرد

یک جدول محوری منظم به ما این فرصت را می دهد که یکی از چندین تابع محاسباتی داخلی را انتخاب کنیم: مجموع، میانگین، شمارش، حداقل، حداکثر و غیره. در خلاصه مدل داده، یک تابع بسیار مفید به این لیست استاندارد اضافه شده است تا تعداد تعداد منحصر به فرد (مقادیر غیر تکراری). برای مثال با کمک آن می توانید به راحتی تعداد اقلام منحصر به فرد کالا (محدوده) را که در هر شهر می فروشیم بشمارید.

روی فیلد – فرمان راست کلیک کنید گزینه های فیلد ارزش و روی زبانه عمل را انتخاب کنید تعداد عناصر مختلف (تعداد مشخص):

مزیت 3: فرمول های سفارشی DAX

گاهی اوقات باید محاسبات اضافی مختلفی را در جداول محوری انجام دهید. در خلاصه‌های معمولی، این کار با استفاده از فیلدها و اشیاء محاسبه‌شده انجام می‌شود، در حالی که خلاصه مدل داده‌ها از معیارها در یک زبان خاص DAX (DAX = عبارات تحلیل داده‌ها) استفاده می‌کند.

برای ایجاد یک اندازه گیری، در برگه انتخاب کنید powerpivot فرمان اندازه گیری ها - اندازه گیری را ایجاد کنید (اقدامات - اندازه گیری جدید) یا فقط روی جدول در لیست Pivot Fields کلیک راست کرده و انتخاب کنید اندازه گیری را اضافه کنید (افزودن اندازه گیری) در منوی زمینه:

در پنجره ای که باز می شود، تنظیم کنید:

• نام جدولجایی که اندازه ایجاد شده ذخیره خواهد شد.
• نام اندازه گیری - هر نامی که برای فیلد جدید می فهمید.
• توضیحات: - اختیاری.
• فرمول - مهمترین چیز، زیرا در اینجا ما یا به صورت دستی وارد می شویم، یا روی دکمه کلیک می کنیم fx و یک تابع DAX را از لیست انتخاب کنید، که وقتی اندازه گیری خود را به ناحیه Values ​​می اندازیم باید نتیجه را محاسبه کند.
• در قسمت پایین پنجره، می توانید فوراً قالب عددی را برای اندازه گیری در لیست تنظیم کنید دسته بندی.
  

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

مزیت 4: سلسله مراتب فیلدهای سفارشی

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

در پنجره Power Pivot، با دکمه به حالت نمودار بروید نمای نمودار برگ صفحه اصلی (صفحه اصلی - نمای نمودار)، با انتخاب کنید کلیدهای Ctrl فیلدهای مورد نظر و کلیک راست بر روی آنها. منوی زمینه حاوی دستور خواهد بود ایجاد سلسله مراتب (ایجاد سلسله مراتب):

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

مزیت 5: شابلون های سفارشی

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

برای انجام این کار، در برگه تجزیه و تحلیل جدول محوری در لیست کشویی فیلدها، آیتم ها و مجموعه ها دستورات مربوطه وجود دارد (تحلیل - فیلدز، Itemps & Sets - ایجاد مجموعه بر اساس موارد ردیف / ستون):

در پنجره ای که باز می شود، می توانید به طور انتخابی هر عنصر را حذف، اضافه یا تغییر دهید و مجموعه به دست آمده را تحت یک نام جدید ذخیره کنید:

همه مجموعه‌های ایجاد شده در پانل PivotTable Fields در یک پوشه جداگانه نمایش داده می‌شوند، از جایی که می‌توان آزادانه به مناطق ردیف‌ها و ستون‌های هر PivotTable جدید کشیده شد:

مزیت 6: مخفی کردن انتخابی جداول و ستون ها

اگرچه این یک مزیت کوچک، اما در برخی موارد بسیار خوشایند است. با کلیک راست بر روی نام فیلد یا تب جدول در پنجره Power Pivot، می توانید دستور را انتخاب کنید. از Client Toolkit پنهان شوید (پنهان شدن از Client Tools):

ستون یا جدول پنهان از پنجره PivotTable Field List ناپدید می شود. اگر نیاز به پنهان کردن برخی از ستون های کمکی (به عنوان مثال، محاسبه شده یا ستون هایی با مقادیر کلیدی برای ایجاد روابط) یا حتی کل جداول دارید، بسیار راحت است.

مزیت 7. آموزش پیشرفته

اگر روی هر سلولی در ناحیه مقدار در یک جدول محوری معمولی دوبار کلیک کنید، اکسل یک کپی از قطعه داده منبع را که در محاسبه این سلول نقش داشته است را در یک صفحه جداگانه نمایش می دهد. این یک چیز بسیار مفید است که رسماً Drill-down نامیده می شود (در آنها معمولاً "شکست" می گویند).

در خلاصه مدل داده، این ابزار مفید با ظرافت بیشتری کار می کند. با ایستادن بر روی هر سلولی که نتیجه مورد علاقه ما باشد، می توانید روی نماد با ذره بین که در کنار آن ظاهر می شود کلیک کنید (به نام روندهای اکسپرس) و سپس هر فیلدی که به آن علاقه دارید را در جدول مربوطه انتخاب کنید:

پس از آن، مقدار فعلی (Model = Explorer) به قسمت فیلتر می رود و خلاصه توسط دفاتر ساخته می شود:

البته، چنین رویه‌ای را می‌توان بارها تکرار کرد، و به طور مداوم در داده‌های خود در جهتی که به آن علاقه دارید، کاوش کنید.

مزیت 8: تبدیل توابع Pivot به Cube

اگر سلولی را در خلاصه برای Data Model انتخاب کنید و سپس در برگه انتخاب کنید تجزیه و تحلیل جدول محوری فرمان ابزارهای OLAP - تبدیل به فرمول (تجزیه و تحلیل - ابزارهای OLAP - تبدیل به فرمول)، سپس کل خلاصه به طور خودکار به فرمول تبدیل می شود. اکنون مقادیر فیلد در ناحیه سطر-ستون و نتایج در ناحیه مقدار با استفاده از توابع مکعب ویژه: CUBEVALUE و CUBEMEMBER از مدل داده بازیابی می شوند:

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

در عین حال، ارتباط با داده های منبع، البته باقی می ماند و در آینده با تغییر منابع، این فرمول ها به روز خواهند شد. زیبایی!

• تجزیه و تحلیل پلان-واقعیت در جدول محوری با Power Pivot و Power Query
• جدول محوری با هدر چند خطی
• با استفاده از Power Pivot یک پایگاه داده در اکسل ایجاد کنید