فیلتر کردن چندین PivotTable به طور همزمان

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

روش 1: برش عمومی برای فیلتر کردن محورها در یک منبع داده

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

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

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

روش 2. برش کلی برای فیلتر کردن خلاصه ها در منابع مختلف

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

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

فرض کنید که دو جدول برای هزینه های فروش و حمل و نقل به عنوان داده ورودی داریم:

فرض کنید که ما با این وظیفه روبرو هستیم که برای هر یک از آنها خلاصه خود را بسازیم و سپس آنها را به طور همزمان توسط شهرهایی با یک برش مشترک فیلتر کنیم.

ما موارد زیر را انجام می دهیم:

1. تبدیل جداول اصلی خود به میزهای هوشمند پویا با میانبر صفحه کلید کلیدهای Ctrl+T یا دستورات صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول) و به آنها اسامی بدهید tablProdaji и tabTransport برگ سازنده (طرح).

2. با استفاده از دکمه هر دو جدول را به نوبه خود در مدل بارگذاری کنید به مدل داده اضافه کنید در تب Power Pivot.

پیوند مستقیم این جداول در Model امکان پذیر نخواهد بود، زیرا در حالی که Power Pivot فقط از روابط یک به چند پشتیبانی می کند، یعنی نیاز دارد که یکی از جداول در ستونی که ما به آن پیوند می دهیم هیچ تکراری نداشته باشد. در هر دو جدول در فیلد یکسان داریم شهر: تکرار وجود دارد بنابراین باید یک جدول جستجوی میانی دیگر با لیستی از نام‌های شهر منحصر به فرد از هر دو جدول ایجاد کنیم. ساده ترین راه برای انجام این کار با قابلیت افزودنی Power Query است که از نسخه 2016 در اکسل ساخته شده است (و برای Excel 2010-2013 به صورت رایگان از وب سایت مایکروسافت دانلود می شود).

3. با انتخاب هر سلول در جدول "هوشمند"، آنها را یکی یکی در Power Query با دکمه بارگذاری می کنیم. از جدول / محدوده برگ داده ها (داده ها - از جدول / محدوده) و سپس در پنجره Power Query گزینه on را انتخاب کنید اصلی تیم ها بستن و بارگذاری - بستن و بارگیری در (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…) و گزینه واردات فقط یک اتصال ایجاد کنید (فقط ایجاد اتصال):

4. هر دو جدول را با دستور یکی می کنیم داده ها - ترکیب پرس و جوها - اضافه کردن (داده ها - ترکیب پرس و جوها - پیوست). ستون هایی با نام های یکسان در هدر زیر یکدیگر قرار می گیرند (مانند یک ستون شهر:، و آنهایی که مطابقت ندارند در ستون های مختلف قرار می گیرند (اما این برای ما مهم نیست).

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

6. لیست مرجع ایجاد شده از طریق در مدل داده آپلود می شود صفحه اصلی — بستن و بارگیری — بستن و بارگیری در (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…) و گزینه را انتخاب کنید فقط یک اتصال ایجاد کنید (فقط ایجاد اتصال) و مهمترین چیز! - چک باکس را روشن کنید این داده ها را به مدل داده اضافه کنید (این داده ها را به مدل داده اضافه کنید):

7. اکنون می توانیم، به پنجره Power Pivot (برگه powerpivot - دکمه مدیریت)، تغییر به نمای نمودار (نمایش نمودار) و جداول فروش و هزینه های حمل و نقل ما را از طریق دایرکتوری میانی ایجاد شده شهرها (با کشیدن فیلدها بین جداول) پیوند دهید:

8. اکنون می توانید با استفاده از دکمه، تمام جداول محوری مورد نیاز برای مدل ایجاد شده را ایجاد کنید جدول جمع بندی (جدول محوری) on اصلی (خانه) تب در پنجره Power Pivot و با انتخاب هر سلول در هر محوری، در تب تحلیل و بررسی دکمه اضافه کردن برش چسباندن برش (تحلیل - درج برش) و برش را در کادر فهرست انتخاب کنید شهر: در دایرکتوری اضافه شده:

حال با کلیک بر روی دکمه آشنا گزارش اتصالات on برگه برش (برش - گزارش اتصالات) ما تمام خلاصه خود را خواهیم دید، زیرا آنها اکنون بر روی جداول منبع مرتبط ساخته شده اند. باقی مانده است که چک باکس های گم شده را فعال کنید و روی آن کلیک کنید OK – و اسلایسر ما همزمان شروع به فیلتر کردن تمام جداول محوری انتخاب شده می کند.