کار عملی روی فناوری olap در اکسل. مکعب اولاپ در اکسل. نوشتن فرمول از ابتدا

1 کار (عمومی) (2 امتیاز).

با استفاده از صفحه گسترده، داده ها را با استفاده از توابع آماری پردازش کنید.
1. اطلاعات در مورد دانش آموزان کلاس (10 نفر) به همراه نمرات یک ماهه در ریاضی داده می شود. تعداد پنج، چهار، دو و سه را بشمارید، میانگین نمره هر دانش آموز و میانگین نمره کل گروه را بیابید. نموداری ایجاد کنید که درصد نمرات یک گروه را نشان دهد.

2.1 کار (2 امتیاز).

چهار دوست با سه روش حمل و نقل سفر می کنند: قطار، هواپیما و کشتی. نیکولای 150 کیلومتر را با قایق طی کرد، 140 کیلومتر را با قطار طی کرد و 1100 کیلومتر را با هواپیما طی کرد. واسیلی 200 کیلومتر را با قایق طی کرد، 220 کیلومتر را با قطار طی کرد و 1160 کیلومتر را با هواپیما طی کرد. آناتولی 1200 کیلومتر با هواپیما پرواز کرد، 110 کیلومتر با قطار و 125 کیلومتر با قایق رفت. ماریا 130 کیلومتر را با قطار طی کرد، 1500 کیلومتر را با هواپیما طی کرد و 160 کیلومتر را با قایق پیمود.
بر اساس داده های بالا یک صفحه گسترده بسازید.

یک ستون به جدول اضافه کنید که تعداد کل کیلومترهایی را که هر یک از بچه ها طی کرده اند نشان دهد.

مجموع کیلومترهایی را که بچه ها با قطار طی کردند، با هواپیما پرواز کردند و با قایق رفتند (در هر نوع حمل و نقل جداگانه) محاسبه کنید.

تعداد کل کیلومتر همه دوستان را محاسبه کنید.

حداکثر و حداقل کیلومترهای پیموده شده توسط دوستان را با استفاده از انواع وسایل حمل و نقل تعیین کنید.

میانگین تعداد کیلومتر برای همه انواع حمل و نقل را تعیین کنید.

2.2 کار (2 امتیاز).

جدول "دریاچه های اروپا" را با استفاده از داده های زیر در مورد مساحت (کیلومتر مربع) و بیشترین عمق (متر) ایجاد کنید: لادوگا 17700 و 225. Onega 9510 و 110; دریای خزر 371000 و 995; Wenern 5550 و 100; Chudskoye با Pskovsky 3560 و 14; بالاتون 591 و 11; ژنو 581 و 310; وترن 1900 و 119; کنستانس 538 و 252; Mälaren 1140 و 64. بزرگترین و کوچکترین دریاچه در مساحت، عمیق ترین و کم عمق ترین دریاچه را تعیین کنید.

2.3 کار (2 امتیاز).

جدول «رودخانه‌های اروپا» را با استفاده از طول (کیلومتر) و مساحت حوضه (هزار کیلومتر مربع) زیر ایجاد کنید: Volga 3688 و 1350; دانوب 2850 و 817; راین 1330 و 224; البه 1150 و 148; ویستولا 1090 و 198; Loire 1020 و 120; اورال 2530 و 220; دان 1870 و 422; سنا 780 و 79; تیمز 340 و 15. طولانی ترین و کوتاه ترین رودخانه را تعیین کنید، مساحت کل حوضه های رودخانه، میانگین طول رودخانه ها در بخش اروپایی روسیه را محاسبه کنید.

وظیفه 3 (2 امتیاز).

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

مطالبی را برای هر درسی پیدا کنید،

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

اگر داده های منبع در یک DBMS دسکتاپ موجود باشد، محاسبه داده های انبوه توسط خود ابزار OLAP انجام می شود. اگر منبع داده های اولیه یک DBMS سرور باشد، بسیاری از ابزارهای OLAP کلاینت پرس و جوهای SQL حاوی عبارت GROUP BY را به سرور ارسال می کنند و در نتیجه داده های انبوه محاسبه شده روی سرور را دریافت می کنند.

به عنوان یک قاعده، عملکرد OLAP در ابزارهای پردازش داده های آماری (از محصولات این کلاس، محصولات StatSoft و SPSS به طور گسترده در بازار روسیه استفاده می شود) و در برخی از صفحات گسترده پیاده سازی می شود. به طور خاص، مایکروسافت اکسل دارای ابزارهای تجزیه و تحلیل چند بعدی است. با این محصول می توانید یک مکعب OLAP چند بعدی محلی کوچک ایجاد و به صورت فایل ذخیره کنید و مقاطع دو یا سه بعدی آن را نمایش دهید.

افزونه های بسته برنامه مایکروسافت آفیسداده کاوی مجموعه ای از توابع است که دسترسی به داده کاوی و قابلیت های پردازش را از برنامه های مایکروسافت آفیس فراهم می کند و در نتیجه امکان تجزیه و تحلیل پیش بینی را فراهم می کند. کامپیوتر محلی. با تشکر از این واقعیت که خدمات ساخته شده است پلتفرم های مایکروسافت SQL Serverبا استفاده از الگوریتم های داده کاوی و پردازش موجود در محیط برنامه مایکروسافت آفیس، کاربران تجاری می توانند به راحتی تنها با چند کلیک اطلاعات ارزشمندی را از مجموعه داده های پیچیده استخراج کنند. افزودنی‌های استخراج و دستکاری داده‌های آفیس، کاربران نهایی را قادر می‌سازد تا مستقیماً در Microsoft Excel و Microsoft Visio تجزیه و تحلیل را انجام دهند.

که در ترکیب مایکروسافت Office 2007 شامل سه جزء مجزای OLAP است:

1. Data Mining Client for Excel به شما امکان می دهد پروژه های داده کاوی مبتنی بر SSAS را از داخل Excel 2007 ایجاد و مدیریت کنید.
2. ابزار تجزیه و تحلیل جدول برای برنامه های کاربردی اکسلبه شما امکان می دهد از قابلیت های استخراج و پردازش اطلاعات داخلی SSAS برای تجزیه و تحلیل داده های ذخیره شده در صفحات گسترده Excel استفاده کنید.
3. قالب‌های داده کاوی Visio به شما امکان می‌دهند درخت‌های تصمیم، درخت‌های رگرسیون، نمودارهای خوشه‌ای و شبکه‌های وابستگی را در نمودارهای Visio تجسم کنید.

اولین رابط برای جداول محوری، همچنین به نام گزارش محوری، در اکسل در سال 1993 (نسخه اکسل 5.0) گنجانده شد. با وجود مفید بودن زیاد عملکرد، عملاً توسط اکثر کاربران اکسل در کار استفاده نمی شود. حتی کاربران باتجربه اغلب از اصطلاح "گزارش خلاصه" چیزی را می‌گویند که با استفاده از فرمول‌های پیچیده ساخته شده است. بیایید سعی کنیم استفاده از جدول محوری را در کار روزانه اقتصاددانان رایج کنیم. مقاله بحث می کند مبنای نظریایجاد گزارش های خلاصه، ارائه توصیه های عملی برای استفاده از آنها و همچنین ارائه نمونه ای از دسترسی به داده ها بر اساس جداول متعدد.

اصطلاحات تجزیه و تحلیل داده های چند متغیره

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

داده های چند بعدی، اندازه گیری ها

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

• انواع یا گروه کالاها؛
• برندها یا دسته بندی محصولات؛
• دوره (ماه، سه ماهه، سال)؛
• خریداران یا گروهی از خریداران؛
• مناطق فروش
• و غیره

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

• حجم فروش؛
• قیمت فروش؛
• تخفیف فردی
• و غیره

در تئوری، داده ها همچنین می توانند بعد استاندارد اطلاعات چند بعدی باشند (به عنوان مثال، می توانید داده ها را بر اساس قیمت فروش گروه بندی کنید)، اما داده ها معمولاً نوع خاصی از ارزش هستند.

بنابراین می توان گفت که در کار عملیاقتصاددانان از دو نوع اطلاعات استفاده می کنند: داده های چند بعدی (اعداد واقعی و برنامه ریزی شده که ویژگی های زیادی دارند) و کتاب های مرجع (ویژگی ها یا اندازه گیری داده ها).

OLAP

مخفف OLAP (پردازش تحلیلی آنلاین) در لغت به معنای "پردازش تحلیلی در زمان واقعی" است. این تعریف خیلی مشخص نیست. در معنای خود، OLAP متضمن یک فناوری برای کار با گزارش های خاص، از جمله نرم افزار، برای به دست آوردن و تجزیه و تحلیل داده های ساختار یافته چند بعدی. یکی از نرم افزارهای محبوبی که فناوری های OLAP را پیاده سازی می کند SQL Server Analysis Server است. حتی برخی به اشتباه او را تنها نماینده پیاده سازی نرم افزاری این مفهوم می دانند.

مکعب داده های مجازی

"مکعب مجازی" ( مکعب چند بعدی، مکعب OLAP) یک اصطلاح خاص است که توسط برخی از ارائه دهندگان نرم افزار تخصصی پیشنهاد شده است. سیستم‌های OLAP معمولاً داده‌ها را در ساختارهای خود آماده و ذخیره می‌کنند و واسط‌های تحلیل ویژه (مانند گزارش‌های خلاصه اکسل) به داده‌ها در این مکعب‌های مجازی دسترسی دارند. علاوه بر این، استفاده از چنین فضای ذخیره سازی اختصاصی برای پردازش اطلاعات چند بعدی اصلا ضروری نیست. به طور کلی، مکعب مجازی- این آرایه ای از داده های چند بعدی بهینه سازی شده است که برای ایجاد گزارش های خلاصه استفاده می شود. می توان آن را از طریق نرم افزارهای تخصصی یا از طریق دسترسی ساده به جداول پایگاه داده یا هر منبع دیگری مانند صفحه گسترده اکسل به دست آورد.

جدول محوری

Pivot Table یک رابط کاربری برای نمایش داده های چند بعدی است. با استفاده از این رابط می‌توانید داده‌ها را گروه‌بندی، مرتب‌سازی، فیلتر و بازآرایی کنید تا نمونه‌های تحلیلی مختلف را به‌دست آورید. به روز رسانی گزارش با استفاده از روش های ساده انجام می شود رابط کاربری، داده ها به طور خودکار طبق قوانین مشخص شده جمع آوری می شوند، بدون نیاز به وارد کردن اطلاعات اضافی یا مکرر. رابط جدول محوری اکسل شاید محبوب ترین باشد محصول نرم افزاریبرای کار با داده های چند بعدی هم از منابع داده خارجی (مکعب های OLAP و پایگاه های داده رابطه ای) و هم از محدوده های صفحه گسترده داخلی به عنوان منبع داده پشتیبانی می کند. با شروع نسخه 2000 (9.0)، اکسل همچنین از فرم گرافیکی نمایش داده های چند بعدی پشتیبانی می کند - نمودار محوری.

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

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

ویرایش PivotTables

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

با استفاده از ابزارهای اتوماسیون داخلی اکسل، می توانید بسیاری از مشکلات غیر استاندارد را حل کنید. نمونه ای از اجرای ویرایش برای جداول محوری اکسل بر اساس داده های کاربرگ را می توان در وب سایت ما یافت.

آماده سازی داده های چند بعدی

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

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

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

اولا، ما باید از شر این نقص خلاص شویم - یعنی. مکان از پیش تعریف شده یکی از ابعاد داده منبع را حذف کنید. نمونه ای از جدول صحیح - برگه حراجی.

جدول به شکل یک گزارش ورود اطلاعات است. در اینجا، تاریخ یک بعد مساوی از داده است. همچنین لازم به ذکر است که برای تجزیه و تحلیل بعدی در جداول محوری، موقعیت نسبی ردیف ها نسبت به یکدیگر (به عبارت دیگر مرتب سازی) کاملاً بی تفاوت است. رکوردهای موجود در پایگاه داده های رابطه ای این ویژگی ها را دارند. رابط جداول محوری در درجه اول با هدف تجزیه و تحلیل حجم زیادی از پایگاه های داده است. بنابراین، هنگام کار با منبع داده در قالب محدوده سلولی، باید این قوانین را رعایت کنید. در عین حال، هیچ کس استفاده از ابزارهای رابط اکسل را در کار خود ممنوع نمی کند - جداول محوری فقط داده ها را تجزیه و تحلیل می کنند و قالب بندی، فیلترها، گروه بندی ها و مرتب سازی سلول های منبع می تواند دلخواه باشد.

از فیلتر خودکار تا گزارش خلاصه

از نظر تئوری، انجام تجزیه و تحلیل در سه بعد با استفاده از داده های برگه فروش امکان پذیر است: کالاها، مشتریان و حامل ها. هیچ اطلاعاتی در مورد ویژگی های محصولات و مشتریان در این برگه وجود ندارد، که بر این اساس، اجازه نمایش آنها را در جدول خلاصه نمی دهد. در حالت عادی ایجاد یک جدول محوری برای منبع داده های اکسلبه شما اجازه نمی دهد که داده ها را از چندین جدول با استفاده از فیلدهای خاص پیوند دهید. می توانید از این محدودیت عبور کنید نرم افزار– نمونه مکمل این مقاله را در وب سایت ما ببینید. برای اینکه به روش های نرم افزاری برای پردازش اطلاعات متوسل نشوید (به ویژه از آنجایی که آنها جهانی نیستند)، باید ویژگی های اضافی را مستقیماً به فرم ورودی مجله اضافه کنید - به برگه SalesAnalysis مراجعه کنید.

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

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

اولین جدول تجزیه و تحلیل از طریق رابط Excel 2007 ساخته شد Ribbon\Insert\PivotTable(در منوی Excel 2000-2003 Data\PivotTable).

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

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

خواص و قالب بندی

علاوه بر نمایش مستقیم داده ها، طیف گسترده ای از گزینه ها برای نمایش ظاهر جداول محوری وجود دارد. می توانید داده های غیر ضروری را با استفاده از فیلترها پنهان کنید. برای یک عنصر یا فیلد، استفاده از آیتم منوی زمینه آسان تر است حذف(در نسخه 2000-2003 پنهان شدن).

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

علاوه بر این، اکسل 2007 بسیاری از سبک های نمایش PivotTable از پیش تعریف شده را معرفی کرد:

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

دسترسی به داده های خارجی

همانطور که قبلاً اشاره شد، شاید بیشترین تأثیر را از استفاده از جداول محوری هنگام دسترسی به داده ها بدست آوریم منابع خارجی- مکعب های OLAP و کوئری های پایگاه داده. چنین منابعی معمولاً مقادیر زیادی از اطلاعات را ذخیره می کنند و همچنین ساختار رابطه ای از پیش تعریف شده ای دارند که تعریف ابعاد داده های چند بعدی را آسان می کند (فیلدهای جدول محوری).

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

بیشترین تأثیر استفاده از منابع خارجی اطلاعات را می توان با استفاده از ابزارهای اتوماسیون (برنامه های VBA) هم برای به دست آوردن داده ها و هم برای پیش پردازش آنها در جداول محوری به دست آورد.

پردازش تحلیلی آنلاین (OLAP) یک فناوری است که برای سازماندهی پایگاه های داده کسب و کار بزرگ و پشتیبانی از هوش تجاری استفاده می شود. پایگاه‌های داده OLAP به یک یا چند مکعب تقسیم می‌شوند و هر مکعب توسط مدیر مکعب سازماندهی می‌شود تا با روش بازیابی و تجزیه و تحلیل داده‌ها مطابقت داشته باشد تا ایجاد و استفاده از گزارش‌های PivotTable و گزارش‌های PivotChart که نیاز دارید آسان‌تر شود.

در این مقاله

تجزیه و تحلیل کسب و کار چیست؟

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

مجموع فروش تمام محصولات در سال 2007 در مقایسه با فروش از سال 2006 چگونه است؟

این در مقایسه با تاریخ و زمان بر اساس دوره مزایای پنج سال گذشته چگونه است؟

مشتریان در سال گذشته چقدر پول برای 35 خرج کردند و این رفتار در طول زمان چگونه تغییر کرده است؟

چه تعداد محصول در دو کشور/منطقه خاص در این ماه در مقایسه با ماه مشابه سال گذشته فروخته شد؟

برای هر گروه سنی مشتری، تقسیم سود (هم درصد حاشیه و هم کل) بر اساس طبقه بندی محصول چگونه است؟

فروشندگان بالا و پایین، توزیع کنندگان، تامین کنندگان، مشتریان، شرکا و مشتریان را جستجو کنید.

پردازش تحلیلی آنلاین (OLAP) چیست؟

پایگاه های داده OLAP (پردازش تحلیلی آنلاین) پرس و جوهای هوش تجاری را ساده می کند. OLAP یک فناوری پایگاه داده است که برای پرس و جوها و گزارش ها به جای پردازش تراکنش بهینه شده است. منبع داده OLAP پایگاه داده پردازش تراکنش آنلاین (OLTP) است که معمولاً در انبارهای داده ذخیره می شود. داده های OLAP از این داده های تاریخی استخراج شده و در ساختارهایی ترکیب می شوند که امکان تجزیه و تحلیل پیچیده را فراهم می کند. داده های OLAP نیز به صورت سلسله مراتبی سازماندهی می شوند و به جای جداول در مکعب ها ذخیره می شوند. این یک فناوری پیچیده است که از ساختارهای چند بعدی برای ارائه استفاده می کند دسترسی سریعبه داده ها برای تجزیه و تحلیل در این سازمان، یک گزارش PivotTable یا یک گزارش PivotChart می‌تواند به راحتی داده‌های خلاصه سطح بالا، مانند مجموع فروش کل کشور یا منطقه را نمایش دهد، و همچنین اطلاعاتی را در مورد سایت‌هایی که فروش در آن‌ها قوی یا ضعیف است، نمایش می‌دهد.

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

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

بخش های زیر هر جزء را با جزئیات در زیر توضیح می دهند.

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

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

عضوعنصری در یک سلسله مراتب که یک یا چند مورد از داده ها را نشان می دهد. یک عنصر می تواند منحصر به فرد یا غیر منحصر به فرد باشد. به عنوان مثال، 2007 و 2008 اعضای منحصر به فرد را در سطح سال یک بعد زمانی نشان می دهد، در حالی که ژانویه اعضای غیر منحصر به فرد را در سطح ماه نشان می دهد زیرا بیش از یک ژانویه در بعد زمانی وجود دارد زیرا حاوی داده هایی برای بیش از یک سال است.

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

اندازه گیریمجموعه ای از یک یا چند سلسله مراتب مرتب شده از سطوح مکعب که کاربر می فهمد و به عنوان مبنایی برای تجزیه و تحلیل داده ها استفاده می کند. برای مثال، یک بعد جغرافیایی ممکن است شامل سطوح کشور/منطقه، ایالت/منطقه و شهر باشد. علاوه بر این، یک بعد زمانی می تواند شامل یک سلسله مراتب با سطوح سال، سه ماهه، ماه و روز باشد. در گزارش PivotTable یا گزارش PivotChart، هر سلسله مراتب به مجموعه‌ای از فیلدها تبدیل می‌شود که می‌توانید آن‌ها را گسترش داده و جمع کنید تا سطوح پایین‌تر یا بالاتر را نشان دهید.

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

مرحلهدر یک سلسله مراتب، داده ها را می توان در سطوح پایین تر و بالاتر از جزئیات، مانند سطوح سال، چهارم، ماه و روز در یک سلسله مراتب زمانی سازماندهی کرد.

توابع OLAP در اکسل

بازیابی داده های OLAPشما می توانید به همان روشی که به دیگر منابع داده خارجی متصل می شوید، به منابع داده OLAP متصل شوید. می توانید با پایگاه داده های ایجاد شده با استفاده از خدمات Microsoft SQL Server OLAP نسخه 7.0، Microsoft SQL Server Analysis Services نسخه 2000، و Microsoft SQL Server Analysis Services نسخه 2005، محصولات سرور Microsoft OLAP، کار کنید. اکسل همچنین می تواند با محصولات OLAP شخص ثالث که با OLE-DB برای OLAP سازگار هستند کار کند.

داده‌های OLAP فقط می‌توانند به‌عنوان گزارش PivotTable یا گزارش PivotChart یا در یک تابع کاربرگ تبدیل شده از یک گزارش PivotTable نمایش داده شوند، اما نه به عنوان یک محدوده داده خارجی. می‌توانید گزارش‌های OLAP PivotTable و PivotChart را در قالب‌های گزارش ذخیره کنید و فایل‌های Office Data Connection (ODC) را برای اتصال به پایگاه‌های داده OLAP برای جستارهای OLAP ایجاد کنید. هنگامی که یک فایل ODC را در اکسل باز می کنید، یک گزارش PivotTable خالی را می بینید که آماده قرار دادن است.

ایجاد فایل های مکعبی برای استفاده آفلاینمی توانید یک فایل مکعب مستقل (cub.) با زیرمجموعه ای از داده های پایگاه داده سرور OLAP ایجاد کنید. فایل‌های مکعب آفلاین برای کار با داده‌های OLAP زمانی که به شبکه متصل نیستید استفاده می‌شوند. با یک مکعب، می‌توانید با داده‌های بیشتری در گزارش PivotTable یا PivotChart نسبت به آنچه در غیر این صورت کار می‌کردید کار کنید و داده‌ها را سریع‌تر دریافت کنید. فقط در صورتی می توانید فایل های مکعبی ایجاد کنید که از یک ارائه دهنده OLAP مانند Microsoft SQL Analysis Services نسخه 2005 استفاده می کنید که از این ویژگی پشتیبانی می کند.

اقدامات سروریک عملکرد سرور یک تابع اختیاری است که یک مدیر مکعب OLAP می‌تواند روی سروری تعریف کند که از عنصر مکعب یا اندازه‌گیری به عنوان پارامتر در یک پرس و جو برای بازیابی اطلاعات در مکعب یا راه‌اندازی برنامه دیگری مانند مرورگر استفاده می‌کند. اکسل از URL ها، گزارش ها، مجموعه ردیف ها، دریل-down، و محفظه سمت سرور پشتیبانی می کند، اما بیانیه و مجموعه داده های خود را پشتیبانی نمی کند.

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

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

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

اجزای نرم افزار مورد نیاز برای دسترسی به منابع داده OLAP

ارائه دهنده OLAPبرای پیکربندی منابع داده OLAP برای اکسل، به یکی از ارائه دهندگان OLAP زیر نیاز دارید.

ارائه دهنده OLAP مایکروسافتاکسل شامل یک درایور منبع داده و نرم افزار سرویس گیرنده برای دسترسی به پایگاه داده های ایجاد شده با سرویس های Microsoft SQL Server olap نسخه 7.0، Microsoft SQL Server olap نسخه 2000 (8.0) و Microsoft SQL Server Analysis نسخه 2005 (9،0) است.

ارائه دهندگان OLAP شخص ثالثسایر محصولات OLAP نیاز به درایورهای اضافی و نرم افزار مشتری برای نصب دارند. برای استفاده از قابلیت‌های اکسل برای کار با داده‌های OLAP، محصول شخص ثالث باید با استاندارد OLE-DB برای OLAP مطابقت داشته باشد و با Microsoft Office سازگار باشد. برای اطلاعات در مورد نصب و استفاده از یک ارائه دهنده OLAP شخص ثالث، رجوع کنید مدیر سیستمیا فروشنده محصول OLAP شما.

پایگاه داده های سرور و فایل های مکعبینرم افزار سرویس گیرنده Excel OLAP از اتصال به دو نوع پایگاه داده OLAP پشتیبانی می کند. اگر پایگاه داده روی سرور OLAP آنلاین است، می توانید داده های منبع را مستقیماً از آن بازیابی کنید. اگر یک فایل مکعب مستقل دارید که حاوی داده های OLAP یا یک فایل تعریف مکعب است، می توانید به آن فایل متصل شوید و داده های منبع را از آن دریافت کنید.

منابع اطلاعاتیک منبع داده دسترسی به تمام داده های یک پایگاه داده OLAP یا فایل مکعب آفلاین را فراهم می کند. هنگامی که یک منبع داده OLAP ایجاد می کنید، می توانید گزارش ها را بر اساس آن قرار دهید و داده های OLAP را به عنوان یک گزارش PivotTable یا PivotChart یا در یک تابع کاربرگ تبدیل شده از یک گزارش PivotTable به Excel برگردانید.

مایکروسافت پرس و جوبا استفاده از Query، می توانید داده ها را از یک پایگاه داده خارجی، مانند Microsoft SQL یا دسترسی مایکروسافت. برای بازیابی داده ها از جدول محوری OLAP که با یک فایل مکعبی مرتبط است، نیازی به استفاده از پرس و جو ندارید. اطلاعات تکمیلی.

تفاوت در ویژگی های داده منبع OLAP و غیر OLAP

اگر با گزارش‌های PivotTable و PivotCharts از داده‌های منبع OLAP و دیگر انواع داده‌های منبع کار می‌کنید، تفاوت‌هایی در ویژگی‌ها مشاهده خواهید کرد.

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

در گزارش‌های مبتنی بر داده‌های منبع OLAP، گزینه‌های فیلد فیلتر گزارش موجود نیست، پرسش پس‌زمینه در دسترس نیست، و گزینه بهینه‌سازی حافظه در دسترس نیست.

توجه داشته باشید:گزینه بهینه سازی حافظه نیز برای منابع داده OLEDB و گزارش های PivotTable بر اساس طیف وسیعی از سلول ها در دسترس نیست.

انواع میدانداده های منبع OLAP. فیلدهای بعد فقط می توانند به عنوان ردیف (ردیف)، ستون (رده) یا فیلد صفحه استفاده شوند. از فیلدهای اندازه گیری فقط می توان به عنوان فیلدهای مقدار استفاده کرد. برای سایر انواع داده های منبع، همه فیلدها را می توان در هر بخشی از گزارش استفاده کرد.

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

ممکن است فیلدهای فیلتر گزارش OLAP نداشته باشند همهعناصر و تیم نمایش صفحات فیلتر گزارشدر دسترس نیست.

ترتیب مرتب سازی اولیهبرای داده های منبع OLAP، ابتدا عناصر به ترتیبی که توسط سرور OLAP برگردانده می شوند، نمایش داده می شوند. می توانید موارد را مرتب کنید یا به صورت دستی دوباره ترتیب دهید. برای سایر انواع داده های منبع، عناصر گزارش جدید ابتدا بر اساس نام عنصر به ترتیب صعودی مرتب می شوند.

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

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

برای داده‌های منبع OLAP، می‌توانید اعضای پنهان را هنگام محاسبه جمع‌های فرعی و کل کل اضافه یا حذف کنید. برای سایر انواع داده های منبع، می توانید عناصر پنهان فیلدهای فیلتر گزارش را در جمع های فرعی قرار دهید، اما عناصر پنهان در سایر فیلدها به طور پیش فرض حذف می شوند.