تجمیع ها روشی برای جمع کردن، خلاصه کردن یا گروه بندی داده ها هستند. وقتی با داده های خام از جداول یا سایر منابع داده شروع می کنید، داده ها اغلب مسطح هستند، به این معنی که جزئیات زیادی وجود دارد، اما به هیچ وجه سازماندهی یا گروه بندی نشده اند. این فقدان خلاصه یا ساختار ممکن است کشف الگوها در داده ها را دشوار کند. بخش مهمی از مدل‌سازی داده‌ها، تعریف تجمیع‌هایی است که الگوها را در پاسخ به یک سؤال تجاری خاص ساده، انتزاع یا خلاصه می‌کنند.
اغلب انبوه‌های متداول، مانند مواردی که از AVERAGE، COUNT، DISTINCTCOUNT، MAX، MIN، یا SUM استفاده می‌کنند، می‌توانند به صورت خودکار با استفاده از AutoSum ایجاد شوند. انواع دیگر تجمیع‌ها، مانند AVERAGEX، COUNTX، COUNTROWS یا SUMX یک جدول را برمی‌گردانند و به فرمولی نیاز دارند که با استفاده از عبارات تحلیل داده (DAX) ایجاد شده باشد.
درک تجمعات در Power Pivot
انتخاب گروه ها برای تجمع
وقتی داده ها را جمع می کنید، داده ها را بر اساس ویژگی هایی مانند محصول، قیمت، منطقه یا تاریخ گروه بندی می کنید و سپس فرمولی را تعریف می کنید که بر روی تمام داده های گروه کار کند. به عنوان مثال، وقتی یک کل برای یک سال ایجاد می کنید، در حال ایجاد یک تجمیع هستید. اگر نسبت امسال را نسبت به سال قبل ایجاد کنید و آن ها را به صورت درصد ارائه کنید، نوع دیگری از تجمیع است.
تصمیم گیری در مورد نحوه گروه بندی داده ها توسط سؤال تجاری هدایت می شود. برای مثال، تجمیع‌ها می‌توانند به سؤالات زیر پاسخ دهند:
شمارش چند تراکنش در یک ماه انجام شد؟
میانگین ها میانگین فروش در این ماه توسط فروشنده چقدر بوده است؟
حداقل و حداکثر مقادیر کدام منطقه فروش از نظر واحدهای فروخته شده پنج منطقه برتر بوده است؟
برای ایجاد محاسباتی که به این سؤالات پاسخ می دهد، باید داده های دقیقی داشته باشید که حاوی اعدادی برای شمارش یا جمع باشد، و آن داده های عددی باید به نحوی با گروه هایی که برای سازماندهی نتایج استفاده می کنید مرتبط باشد.
اگر داده ها قبلاً حاوی مقادیری نیستند که می توانید برای گروه بندی استفاده کنید ، مانند یک دسته محصول یا نام منطقه جغرافیایی که در آن فروشگاه قرار دارد ، ممکن است بخواهید با اضافه کردن دسته ها ، گروه ها را به داده های خود معرفی کنید. هنگامی که گروه هایی را در اکسل می سازید ، باید گروههایی را که می خواهید از آنها استفاده کنید از بین ستون های موجود در برگه خود استفاده کنید یا انتخاب کنید. با این حال ، در یک سیستم رابطه ای ، سلسله مراتب مانند دسته بندی محصولات اغلب در یک جدول متفاوت از جدول واقعیت یا ارزش ذخیره می شوند. معمولاً جدول دسته بندی با نوعی کلید به داده های واقعیت مرتبط است. به عنوان مثال ، فرض کنید متوجه می شوید که داده های شما شامل شناسه محصول است ، اما نام محصولات یا دسته آنها نیست. برای افزودن گروه به یک برگه Excel Flat ، باید در ستونی که شامل نامهای دسته بندی شده است ، کپی کنید. با استفاده از Power Pivot ، می توانید جدول دسته بندی محصولات را به مدل داده خود وارد کنید ، بین جدول با داده های شماره و لیست دسته بندی محصولات رابطه ایجاد کنید و سپس از دسته ها برای گروه بندی استفاده کنید. برای اطلاعات بیشتر ، به ایجاد رابطه بین جداول مراجعه کنید.
انتخاب یک تابع برای تجمع
پس از شناسایی و اضافه کردن گروه بندی ها برای استفاده ، باید تصمیم بگیرید که از عملکردهای ریاضی برای جمع آوری استفاده کنید. غالباً کلمه تجمع به عنوان مترادف برای عملیات ریاضی یا آماری که در تجمع استفاده می شود ، مانند مبالغ ، میانگین ، حداقل یا شمارش استفاده می شود. با این حال ، Power Pivot شما را قادر می سازد تا علاوه بر تجمع استاندارد موجود در Power Pivot و Excel ، فرمول های سفارشی را برای جمع آوری ایجاد کنید.
به عنوان مثال ، با توجه به همان مجموعه مقادیر و گروه بندی هایی که در مثالهای قبلی استفاده شده اند ، می توانید تجمع های سفارشی ایجاد کنید که به سؤالات زیر پاسخ می دهند:
تعداد فیلتر شده تعداد معاملات در یک ماه به استثنای پنجره تعمیر و نگهداری پایان ماه وجود دارد؟
نسبت ها با استفاده از میانگین با گذشت زمان ، درصد رشد یا کاهش فروش در مقایسه با مدت مشابه سال گذشته چیست؟
حداقل و حداکثر مقادیر گروه بندی شده کدام مناطق فروش برای هر دسته از محصولات در رده بالا قرار گرفتند ، یا برای هر ارتقاء فروش؟
افزودن تجمع به فرمول ها و pivottables
هنگامی که شما یک ایده کلی در مورد چگونگی گروه بندی داده های شما برای معنی دار بودن و مقادیری که می خواهید با آنها کار کنید ، می توانید تصمیم بگیرید که آیا یک Pivottable ایجاد کنید یا محاسبات را در یک جدول ایجاد کنید. Power Pivot توانایی بومی اکسل را در ایجاد تجمع مانند مبالغ ، شمارش یا میانگین ها گسترش و بهبود می بخشد. شما می توانید تجمع های سفارشی را در Power Pivot یا در پنجره Power Pivot یا در منطقه Pivottable Excel ایجاد کنید.
در یک ستون محاسبه شده ، می توانید تجمع هایی را ایجاد کنید که زمینه ردیف فعلی را برای بازیابی ردیف های مرتبط از جدول دیگر در نظر بگیرید ، و سپس آن مقادیر را در ردیف های مرتبط جمع کنید.
در یک اندازه ، شما می توانید تجمع پویا ایجاد کنید که از هر دو فیلتر تعریف شده در فرمول استفاده می کنند ، و فیلترهای تحمیل شده توسط طراحی pivottable و انتخاب برش ها ، عناوین ستون و عناوین ردیف. اقدامات با استفاده از مصالح استاندارد را می توان با استفاده از اتوسوم یا با ایجاد فرمول در محوری برق ایجاد کرد. همچنین می توانید با استفاده از تجمع استاندارد در یک pivottable در اکسل اقدامات ضمنی ایجاد کنید.
افزودن گروه بندی به یک pivottable
هنگامی که شما یک pivottable را طراحی می کنید ، زمینه هایی را که نمایانگر گروه بندی ها ، دسته ها یا سلسله مراتب است ، به بخش ستون ها و ردیف های Pivottable برای گروه بندی داده ها می کشید. سپس زمینه هایی را که حاوی مقادیر عددی هستند به منطقه مقادیر می کشید تا بتوانید آنها را شمارش ، میانگین یا خلاصه کنید.
اگر دسته هایی را به یک pivottable اضافه کنید اما داده های دسته بندی مربوط به داده های واقعیت نیست ، ممکن است خطایی یا نتایج عجیب و غریب دریافت کنید. معمولاً Power Pivot با تشخیص خودکار و پیشنهاد روابط ، سعی در اصلاح مشکل خواهد داشت. برای اطلاعات بیشتر ، به کار با روابط در Pivottables مراجعه کنید.
همچنین می توانید زمینه ها را به سمت برش ها بکشید تا گروه های خاصی از داده ها را برای مشاهده انتخاب کنید. برش ها به شما امکان می دهند تا به صورت تعاملی گروه ها را مرتب کنید ، مرتب کنید و نتایج را در یک pivottable قابل فیلتر کنید.
کار با گروه بندی ها در یک فرمول
همچنین می توانید با ایجاد روابط بین جداول ، از گروه ها و دسته ها برای جمع آوری داده هایی که در جداول ذخیره می شوند ، استفاده کنید ، سپس فرمول هایی را ایجاد کنید که از این روابط استفاده می کنند تا به دنبال مقادیر مرتبط باشند.
به عبارت دیگر ، اگر می خواهید فرمولی را ایجاد کنید که مقادیر را بر اساس یک دسته از آنها گروه بندی کند ، ابتدا از یک رابطه برای اتصال جدول حاوی داده های جزئیات و جداول حاوی دسته ها استفاده می کنید و سپس فرمول را می سازید.
برای کسب اطلاعات بیشتر در مورد نحوه ایجاد فرمول هایی که از جستجو استفاده می کنند ، به جستجوی فرمول های محوری قدرت مراجعه کنید.
با استفاده از فیلترها در جمع
یک ویژگی جدید در Power Pivot ، امکان استفاده از فیلترها در ستون ها و جداول داده ها ، نه تنها در رابط کاربری و در یک pivottable یا نمودار ، بلکه در فرمول هایی است که شما برای محاسبه تجمع استفاده می کنید. فیلترها را می توان در فرمول ها هم در ستون های محاسبه شده و هم در S استفاده کرد.
به عنوان مثال ، در توابع جدید جمع آوری DAX ، به جای مشخص کردن مقادیری که برای جمع بندی یا شمارش آنها مشخص شود ، می توانید یک جدول کامل را به عنوان آرگومان مشخص کنید. اگر هیچ فیلمی را در آن جدول اعمال نکرده اید ، عملکرد جمع آوری در برابر تمام مقادیر موجود در ستون مشخص شده جدول کار می کند. با این حال ، در DAX می توانید یک فیلتر پویا یا استاتیک را روی جدول ایجاد کنید ، به طوری که جمع بسته به شرایط فیلتر و زمینه فعلی در برابر زیر مجموعه های مختلف داده ها عمل می کند.
با ترکیب شرایط و فیلترها در فرمول ها می توانید جمعاتی ایجاد کنید که بسته به مقادیر ارائه شده در فرمول ها تغییر می کند ، یا بسته به انتخاب عناوین ردیف ها و عناوین ستون در یک pivottable تغییر می کند.
مقایسه توابع تجمیع اکسل و توابع تجمیع DAX
در جدول زیر برخی از توابع استاندارد جمع آوری شده توسط اکسل ذکر شده است و پیوندهایی برای اجرای این توابع در Power Pivot ارائه می دهد. نسخه DAX این توابع تقریباً مشابه نسخه اکسل رفتار می کند ، با برخی تفاوت های جزئی در نحو و رسیدگی به انواع داده های خاص.
توابع جمع آوری استاندارد
میانگین (میانگین حسابی) تمام اعداد موجود در یک ستون را برمی گرداند.
میانگین (میانگین حسابی) تمام مقادیر موجود در یک ستون را برمی گرداند. مقادیر متن و غیر عددی را کنترل می کند.
تعداد مقادیر عددی را در یک ستون شمارش می کند.
تعداد مقادیر موجود در ستونی را که خالی نیست ، شمارش می کند.
بزرگترین مقدار عددی را در یک ستون برمی گرداند.
بزرگترین مقدار را از مجموعه ای از عبارات ارزیابی شده در یک جدول باز می گرداند.
کوچکترین مقدار عددی را در یک ستون برمی گرداند.
کوچکترین مقدار را از مجموعه ای از عبارات ارزیابی شده در یک جدول برمی گرداند.
تمام اعداد را در یک ستون اضافه می کند.
توابع تجمع DAX
DAX شامل توابع تجمیع است که به شما امکان می دهد جدول را مشخص کنید که در آن قرار است تجمع انجام شود. بنابراین ، به جای اینکه فقط مقادیر موجود در یک ستون را اضافه یا میانگین کنید ، این توابع به شما امکان می دهند عبارتی را ایجاد کنید که به طور پویا داده ها را برای جمع بندی تعریف کند.
در جدول زیر توابع مصالح موجود در DAX ذکر شده است.
به طور متوسط مجموعه ای از عبارات ارزیابی شده در یک جدول.
مجموعه ای از عبارات ارزیابی شده را روی یک جدول می شمارد.
تعداد مقادیر خالی یک ستون را می شمارد.
تعداد کل ردیف های جدول را می شمارد.
تعداد ردیف های برگشتی از یک تابع جدول تودرتو، مانند تابع فیلتر را می شمارد.
مجموع مجموعه ای از عبارات ارزیابی شده روی یک جدول را برمی گرداند.
تفاوت بین توابع تجمع DAX و Excel
اگرچه این توابع نامهای مشابهی با همتایان اکسل خود دارند، اما از موتور تجزیه و تحلیل درون حافظه Power Pivot استفاده می‌کنند و برای کار با جداول و ستون‌ها بازنویسی شده‌اند. شما نمی توانید از فرمول DAX در کتاب کار اکسل استفاده کنید و بالعکس. آنها را فقط می توان در پنجره Power Pivot و در PivotTable هایی که بر اساس داده های Power Pivot هستند استفاده کرد. همچنین، اگرچه توابع نامهای یکسانی دارند، ممکن است رفتار کمی متفاوت باشد. برای کسب اطلاعات بیشتر، به موضوعات مرجع عملکرد فردی مراجعه کنید.
روشی که ستون‌ها در یک تجمیع ارزیابی می‌شوند نیز با روشی که اکسل تجمیع‌ها را مدیریت می‌کند متفاوت است. یک مثال ممکن است به توضیح کمک کند.
فرض کنید می خواهید مجموعی از مقادیر ستون Amount را در جدول Sales بدست آورید، بنابراین فرمول زیر را ایجاد می کنید:
در ساده‌ترین حالت، تابع مقادیر را از یک ستون بدون فیلتر دریافت می‌کند و نتیجه مانند اکسل است که همیشه مقادیر ستون Amount را جمع می‌کند. با این حال، در Power Pivot، فرمول به صورت "برای هر ردیف از جدول فروش، مقدار را در مقدار دریافت کنید، و سپس آن مقادیر فردی را جمع کنید. Power Pivot هر ردیفی را که تجمیع روی آن انجام می شود، ارزیابی می کند و یک مقدار اسکالر واحد را محاسبه می کند. برای هر ردیف، و سپس یک تجمیع بر روی آن مقادیر انجام می دهد. بنابراین، اگر فیلترها بر روی یک جدول اعمال شده باشند، یا اگر مقادیر بر اساس سایر تجمعاتی که ممکن است فیلتر شوند، محاسبه شوند، نتیجه یک فرمول می تواند متفاوت باشد. برای اطلاعات بیشتر.، به متن در فرمول های DAX مراجعه کنید.
توابع هوشمند DAX Time
علاوه بر توابع تجمع جدول که در بخش قبل توضیح داده شد، DAX دارای توابع تجمیع است که با تاریخ ها و زمان هایی که شما مشخص کرده اید کار می کند تا اطلاعات زمانی داخلی را ارائه دهد. این توابع از محدوده تاریخ برای بدست آوردن مقادیر مرتبط و تجمیع مقادیر استفاده می کنند. همچنین می توانید مقادیر را در محدوده تاریخ مقایسه کنید.
جدول زیر توابع هوش زمانی را که می توان برای تجمیع استفاده کرد فهرست می کند.
