Pivottables هر زمان که داده ها بسیار زیاد به نظر می رسید ، شبکه ایمنی من بود ، اما همیشه من را در ردیف های اعداد می چشید. مشکل این بود که همه چیز را به هم وصل کنیم. Pivottables سنتی من را وادار به کار با تکه های داده جدا شده ، که نیاز به تجزیه و تحلیل جداگانه برای جنبه های مختلف همان مجموعه داده است. سپس من Power Pivot را کشف کردم و همه چیز تغییر کرد.
Pivottables هر زمان که داده ها بسیار زیاد به نظر می رسید ، شبکه ایمنی من بود ، اما همیشه من را در ردیف های اعداد می چشید. مشکل این بود که همه چیز را به هم وصل کنیم. Pivottables سنتی من را وادار به کار با تکه های داده جدا شده ، که نیاز به تجزیه و تحلیل جداگانه برای جنبه های مختلف همان مجموعه داده است. سپس من Power Pivot را کشف کردم و همه چیز تغییر کرد.
این ویژگی Excel داخلی صفحه گسترده شما را به یک مدل داده رابطه ای تبدیل می کند که به طور خودکار چندین منبع داده متصل را کنترل می کند. به جای گذراندن ساعت ها برای تهیه داده های دستی ، اکنون می توانم روابط پیچیده را در عرض چند دقیقه تجزیه و تحلیل کنم!
Power Pivot هر آنچه را که Pivottables می تواند انجام دهد
و برخی دیگر
در حالی که Pivottables با منابع داده واحد کار می کند ، Power Pivot کل کتاب کار شما را به عنوان یک پایگاه داده متصل رفتار می کند. به جای مجبور کردن توابع و فرمول های اکسل من برای ایجاد ارتباط شبه ، می توانم چندین جدول مرتبط را وارد کنم و اجازه دهم که Power Pivot روابط مدل را بطور خودکار کنترل کند.
این رویکرد چرخه بی پایان به روزرسانی فرمولها و رفع منابع شکسته که گردش کار قدیمی من را گرفتار می کند ، از بین می برد. با Power Pivot ، اضافه کردن داده های جدید به یک عمل تازه سازی ساده تبدیل می شود که تمام تجزیه و تحلیل های من را به طور همزمان به روز می کند.
Power Pivot با اکثر نسخه های تجاری ، سازمانی و آموزش اکسل گنجانده شده است ، اما همیشه در مجوزهای خانه یا دانشجویی در دسترس نیست. اگر نسخه شما از آن پشتیبانی می کند ، می توانید این ویژگی را از منوی Add-Ins Excel فعال کنید.
برای فعال کردن Power Pivot ، به File> Options بروید ، روی Add-INS کلیک کنید ، از Dropdown Add-Ins را انتخاب کنید و کادر Microsoft Power Pivot را برای Excel بررسی کنید. پس از فعال کردن ، یک برگه Pivot Power جدید در روبان اکسل ظاهر می شود و به شما امکان دسترسی به ابزارهایی را می دهد که نحوه کار شما با داده ها را تغییر می دهد.
مدل سازی رابطه ای خلاصه ها و تجزیه و تحلیل را از همیشه آسانتر می کند
Power Pivot داده های شما را مانند یک پایگاه داده مناسب به جای جداول صفحه گسترده جدا شده رفتار می کند. شما به سادگی هر مجموعه داده را وارد می کنید و سپس روابط بین زمینه های مشترک را تعریف می کنید ، به اکسل اجازه می دهید به طور خودکار به جداول خود بپیوندد و گزارش های یکپارچه را بدون جستجوی دستی ارائه دهد. قبل از استفاده از Power Pivot (یا تقریباً در مورد هر چیزی در اکسل) ، مهم است که ابتدا کتابهای کار خود را تمیز کرده و آماده کنید تا از نتایج قابل اعتماد اطمینان حاصل کنید. من شخصاً از پرس و جو از Power از کارکردهای تمیز کننده سنتی استفاده می کنم ، زیرا این مقیاس بهتر می شود و باعث صرفه جویی در وقت زیادی برای مهار میزهایم می شود.
برای اینکه به شما نشان دهم مدل سازی رابطه قدرتمند چقدر قدرتمند است ، من از مجموعه ای از کتابهای کار استفاده می کنم که از آنها برای جمع آوری یک پایگاه داده با پس زمینه در طول توسعه استفاده می کنم. این یک بانک اطلاعاتی تجارت الکترونیکی با صفحات گسترده جداگانه برای مشتریان ، محصولات ، سفارشات و جزئیات سفارش است ، همه دارای زمینه های مشترک مانند مشتری_id ، order_id و product_id است.
ابتدا ، من با راه اندازی صفحه گسترده مشتریان ، با کلیک بر روی Power Pivot از روبان ، Power Pivot را باز می کنم ، سپس در بخش جداول گزینه ADD TO DATA را انتخاب می کنم. این منوی Power Pivot را باز می کند. از اینجا ، من با کلیک بر روی سایر منابع> Excel ، سایر صفحات گسترده خود را اضافه می کنم ، سپس پرونده هایم را مرور می کنم و باز می کنم ، بعدی را بزنید ، سپس تمام می کنم. من این کار را در تمام صفحه های گسترده خود انجام می دهم.
پس از افزودن همه چیز ، من به Diagram View ، واقع در قسمت نمایش Power Pivot می روم. این چهار کتاب کار من را نشان می دهد: مشتری ، سفارش_دتیل ، سفارشات و محصولات. Power Pivot اغلب می تواند روابط را به صورت خودکار تشخیص داده و پیشنهاد کند ، اما می توانید با کشیدن مزارع بین جداول در نمای نمودار ، آنها را به صورت دستی تعریف کنید.
در این مثال ، هر کتاب کار زمینه های کلیدی را به اشتراک می گذارد که جداول من را به هم پیوند می دهد. هر دو کتاب کاری مشتری و سفارش شامل یک قسمت مشتری_ید هستند. کتابهای کار سفارشات و سفارش_دتیلز یک قسمت order_id را به اشتراک می گذارند. کتابهای کار Order_Details و محصولات از همان قسمت Product_ID استفاده می کنند. این زمینه های مشترک روابط “یک تا بسیاری” را تشکیل می دهند. یک مشتری واحد می تواند سفارشات زیادی داشته باشد ، هر سفارش می تواند شامل چندین محصول باشد و هر محصول می تواند در بسیاری از جزئیات سفارش ظاهر شود. Power Pivot از این شناسه های منحصر به فرد برای اتصال خودکار داده های من به طور خودکار استفاده می کند.
هنگامی که روابط من برقرار شود ، گزارش به آسانی به عنوان کشیدن و رها کردن زمینه ها می شود. من دیگر Vlookups یا ستون های یاور را جمع نمی کنم ، و می توانم بلافاصله داده ها را در هر چهار جدول برش و تاس کنم.
به عنوان مثال ، برای دیدن کل فروش توسط مشتری ، روی Pivottable در پنجره Power Pivot کلیک می کنم ، کاربرگ جدید را انتخاب می کنم و جدول مشتریان را در لیست فیلد گسترش می دهم. سپس من مشتری را به ردیف ها و Line_Total از جدول Order_Details به مقادیر می کشم. بلافاصله ، من فروش طول عمر هر مشتری را بدون هیچ گونه پیوست دستی می بینم.
اگر می خواهم این فروش ها را بر اساس رده محصول تقسیم کنم ، دسته بندی از جدول محصولات را به ستون ها اضافه می کنم. اکسل به طور خودکار اتصالات را از طریق سفارشات و order_details پردازش می کند و مقادیر مناسب را در هر گروه جمع می کند.
برای مقایسه نحوه عملکردهای مختلف حمل و نقل ، حمل و نقل_میتود را از جدول سفارشات به فیلترها می کشم و Express یا Standard را انتخاب می کنم. محوری فوراً به روز می شود و فقط آن معاملات را نشان می دهد.
از آنجا که Power Pivot می داند که جداول من چگونه وصل می شود ، می توانم آزادانه آزمایش کنم. من برای دیدن روند جغرافیا یا اضافه کردن سفارش_دیت برای فیلتر کردن در دوره های زمانی ، از مشتریان در شهر رها می شوم. هر تغییر در زمان واقعی اتفاق می افتد و به من اجازه می دهد تا بدون بازسازی مدل داده خود یا بازنویسی فرمول های خود ، سؤالات را کشف کنم و بینش های خود را کشف کنم.
محاسبات DAX باعث انعطاف پذیری بهتر و بینش بهتر می شود
از آنجا که ما قبلاً روابط برقرار کرده ایم و نشان داده ایم که ساخت گزارش ها چقدر آسان است ، زمان آن است که DAX را رها کنیم. DAX (عبارات تجزیه و تحلیل داده ها) زبان فرمول پشت Power Pivot است که به طور خاص برای مدل سازی داده ها و محاسبات پیشرفته طراحی شده است. فرمول های DAX Power Pivot قابلیت های تحلیلی را باز می کند که با Pivottables تقریباً غیرممکن خواهد بود.
این فرمول ها به شما امکان می دهد محاسبات سفارشی ایجاد کنید که به طور خودکار از روابط جدول خود پیروی می کنند و تجزیه و تحلیل پیچیده را با نحو شگفت آور ساده انجام می دهند. اگر تازه وارد DAX هستید ، مستندات رسمی مایکروسافت مکانی عالی برای شروع است.
با سه اقدام ، می توانید محاسباتی را انجام دهید که تقریباً با قابلیت های سنتی غیرممکن است.
ابتدا اجازه دهید ارزش طول عمر مشتری را محاسبه کنیم. روی روبان Power Pivot در اکسل ، روی اقدامات کلیک می کنم ، اندازه گیری جدید را انتخاب می کنم و جدول مشتریان را انتخاب می کنم. من اندازه گیری “مشتری LTV” را نامگذاری می کنم و فرمول را وارد می کنم:
= جمع (Order_Details [line_total])
سپس روی OK کلیک می کنم. Power Pivot زنجیره ای را از مشتریان به سفارشات به سفارش_دیل ها دنبال می کند و به طور خودکار خریدهای مشتری را به طور خودکار جمع می کند.
در مرحله بعد ، من می خواهم اندازه سفارش متوسط هر مشتری را بدانم. باز هم ، من اندازه گیری جدیدی را در جدول مشتریان باز می کنم ، آن را “مقدار سفارش AVG” نامگذاری می کنم و از فرمول استفاده می کنم:
= تقسیم ([مشتری LTV] ، مجزا (سفارشات [سفارش_ید]))
با کلیک بر روی OK ، اقدامی را به من می دهد که هزینه کل را با سفارش برای هر مشتری و بدون هیچ ستون یاور تقسیم می کند.
سرانجام ، من ترجیحات حمل و نقل را براساس طبقه بندی می کنم. در جدول محصولات من یک اندازه گیری به نام “Audio Express ٪” با این فرمول ایجاد می کنم:
= تقسیم (محاسبه (جمع (order_details [line_total]) ، محصولات [دسته] = “صوتی” ، سفارشات [حمل و نقل_میتود] = “اکسپرس”) ، محاسبه (جمع (orde_details [line_total]) ، محصولات [دسته] = “صوتی”))
سپس کادر انتخاب را برای هر اندازه گیری می کنم تا آنها را روی جدول مشاهده کنم.
با استفاده از این اقدامات DAX ، من می توانم فوراً هزینه های کل مشتری را در کنار سهم دقیق سفارشات صوتی ارسال شده Express در یک pivottable واحد مشاهده کنم. در تصویر می توانید کل فروش های صوتی ، کابل ، رایانه و موارد دیگر را مشاهده کنید ، در حالی که ستون Audio Express ٪ به عنوان مثال نشان می دهد که الکسیس پارکر 75 ٪ از خریدهای صوتی خود را از طریق Express ارسال کرده است.
جمع کردن این بینش ها با استفاده از روشهای سنتی به معنای ساختن میزهای متعدد یاور و نوشتن ده ها VLOOKUP یا محاسبات دستی است. روش مدرن اکسل برای رسیدگی به کتابهای کاری مانند اینها استفاده از فرمول های DAX برای فیلتر و جمع آوری در جداول است.
من هیچ دلیلی برای بازگشت به Pivottables نمی بینم
محور قدرت اساساً نحوه نزدیک شدن به تجزیه و تحلیل داده ها در اکسل را تغییر داد. آنچه قبلاً ساعت ها آماده سازی دستی و ساخت فرمول را انجام می داد ، اکنون در چند دقیقه با مدیریت خودکار روابط و محاسبات DAX اتفاق می افتد. امکان اتصال چندین منبع داده ، ایجاد اقدامات پیچیده و ساخت گزارش های یکپارچه باعث می شود که با مقایسه ، Pivottables احساس ابتدایی شود.
اگر هیچ چیز دیگری نباشد ، من هنوز هم می توانم از Power Pivot دقیقاً مانند Pivottables معمولی استفاده کنم در حالی که از عملکرد بسیار سریعتر در کتابهای بزرگ لذت می برم. ترکیب آن از سرعت ، اتوماسیون و عمق تحلیلی باعث می شود که قدرت محوری برای هر کسی که در مورد دریافت اطلاعات بیشتر در اکسل بیشتر باشد ، یک ارتقاء اساسی باشد.