نحوه ساخت یک Pivot Table از چندین کاربرگ + مثال

نحوه ساخت یک pivot table: بسیاری از کاربران اکسل برای ساخت Pivot Table ها، تنها از یک جدول کمک می گیرند و جدول مربوطه که کامل شد، آنگاه از آن Pivot Table می سازند. این ایده برای نسخه های قدیمی تر اکسل صدق می کرد، اما در طول فرآیند تکمیل نسخه های اکسل، الان می توان گفت که اکسل تقریباً این مشکل را هم برطرف کرده و هم اکنون می توان از چند کاربرگ نیز Pivot Table ایجاد کرد و الزاماً نیازی به داشتن فقط یک جدول اطلاعات نیست.

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

اگر تا الان از جمله کاربرهایی نبوده اید که با Pivot Table ها کار کنند، امروز روزی است که یک بار برای همیشه با Pivot Table ها آشنا خواهید شد و شروع به ساخت جداولی می کنید که زمانی آرزویش را داشته اید.

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

1. روش اول: براساس روش Appending داده ها (اضافه کردن داده ها به یکدیگر) و با کمک Power Query ها

2. روش دوم: براساس اصل Relationship بین داده ها و ساخت مدل داده

ترکیب چندین جدول با استفاده از Power Query

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

این جدول جدید – که به تازگی انباشته شده – همان جدولی است که ما از این به بعد به عنوان مجموعه داده تغذیه کننده برای ساخت Pivot Table از آن استفاده خواهیم کرد.

ترفندی که می خواهیم اینجا بکار ببریم این است که جداول اولیه را به صورت جداگانه جایی نگه داریم، اما در عین حال هم نمی خواهیم جدول تغذیه کننده را به صورت فیزیکی ایجاد کنیم. قصد داریم که از نتایج تجمیع شده و انباشته شده داده های جداول اولیه، مستقیماً برای تولید Pivot Table و نهایتاً گزارش گیری استفاده کنیم.

آماده سازی دو مجموعه داده

اولین مجموعه داده مربوط به اطلاعات فروشگاه شماره 1 است و به صورت زیر آماده شده است:

همانطور که مشاهده می کنید، شماره تراکنش ها در ستون اول و مقدار فروش در ستون پنجم آورده شده است.

دومین مجموعه داده مربوط به اطلاعات فروشگاه شماره 2 است و به صورت زیر آماده شده است:

دقت داشته باشید که شماره تراکنش در اینجا در ستون شماره 2 و مقدار فروش در ستون شماره 4 آورده شده است.

در واقع این جابجایی ستونها در دو مجموعه داده برای ما مشکلی ایجاد نخواهد کرد. چون ابزارهای انتخابی ما قبل از ارسال داده ها به جدول منبع تغذیه، داده ها را براساس نام ستون ها دوباره مرتب سازی می کند.

بریم سراغ بهترین دوستمون یعنی Power Query

Power Query از جمله ابزارهای درون برنامه ای اکسل است که برای تبدیل داده هایی که در حالت عادی غیرقابل استفاده هستند به داده هایی که کاملاً قابل استفاده می شوند، کاربرد دارد.

اگر چه ضرورتی ندارد اما اگر داده های منبع را در قالب جداول اکسلی مناسب قرار دهیم، عملکرد Power Query بسیار کارآمدتر خواهد شد.

برای تبدیل دو جدول “ساده” موجود به جداول اکسلی مناسب، در هر نقطه از جدول اول (فروشگاه شماره 1) کلیک کنید و CTRL-T را روی صفحه کلید فشار دهید. یک کادر محاوره ای برای ایجاد جدول ظاهر می شود. برای انتخاب پیش فرض ها روی OK کلیک کنید:

وارد منوی Table Design شوید و روی فیلد Table Name (سمت چپ نوار) کلیک کنید و نام جدول را به «TableStore1» تغییر دهید (بدون فاصله).

مراحل ذکر شده را برای جدول دوم (فروشگاه شماره 2) تکرار کنید و نام جدول را به “TableStore2” تغییر دهید (بدون فاصله).

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

در مرحله بعد، دو جدول موجود را وارد Power Query می کنیم.

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

یکی از ویژگی های عالی فرآیند تجمیع داده ها این است که نیازی نیست ترتیب ستون ها در جداول مختلف یکسان باشند. Power Query به طور خودکار ترتیب ستون‌های هر جدول را مجدداً مرتب سازی می کند تا در یک جدول واحد همباد و تراز شود.

از آنجایی که ما باید به خاطر داشته باشیم که کدام تراکنش‌ها به کدام فروشگاه مرتبط هستند، یک «ستون کمکی» اضافه می‌کنیم که شامل شماره فروشگاه در کنار تراکنش های مرتبط با آن است.

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

روی هر نقطه از جدول اول کلیک کنید (جدول “TableStore1”) و به مسیر زیر بروید:

Data (tab) –> Get & Transform Data (group) –> From Table/Range

این گزینه در نسخه های دیگر اکسل با نام “From Sheet” شناخته می شود:

پنجره جدیدی باز می شود که ویرایشگر Power Query را به ما نشان می دهد.

یک تنظیم کوچک: نیازی به داشتن اطلاعات زمان در ستون تاریخ نداریم، پس باید تنظیماتی را بر روی ستون “تاریخ” انجام دهیم.

این کار به راحتی با انتخاب سربرگ ستون «تاریخ» انجام می شود، سپس روی “Data Type: Date/Time” از نوار Home کلیک کنید و نوع داده را به “Data” تغییر دهید.

در پیامی که در پنجره بعدی ظاهر می شود روی گزینه “Replace Current” کلیک کنید.

گام دوم: اضافه کردن اطلاعات پیگیری برای هر تراکنش

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

در تب Add Column بر روی گزینه Custom Column کلیک کنید (سمت چپ پنجره).

در پنجره Custom Column باز شده در این مرحله، برای ستون جدید در قسمت New Column Name نام “فروشگاه” را بنویسید و فرمول زیر را در فیلد بزرگی که برای فرمولها در نظر گرفته شده بنویسید:

نتیجه، ایجاد یک ستون جدید است که برای هر کدام از تراکنش ها و جلوی آنها عبارت “Store 1” را نشان می دهد.

همانند ستون “تاریخ”، نوع داده ستون “فروشگاه” که به تازگی اضافه کرده اید را به نوع داده TEXT تغییر دهید:

گام سوم: وارد کردن دومین جدول به درون Power Query

ما می‌توانیم با ایجاد یک کپی از عبارت “TableStore1” و ایجاد چند تغییر هدفمند، چند گام را یکجا ذخیره کنیم.

اگر روی عبارت SOURCE در پنل “Applied Steps” (سمت راست پنجره ویرایشگر) کلیک کنید، می توانید منبع اطلاعاتی جدول “TableStore1” را ببینید.

قصد داریم از این Query یک کپی ایجاد کنیم و از آن منبع اطلاعاتی کپی شده، عبارت “TableStore1” را به “TableStore2” تغییر دهیم.

از پنل Queries (سمت چپ صفحه پنجره ویرایشگر)، بر روی کوئری “TableStore1” راست کلیک کنید و از آن یک Duplicate بگیرید.

کوئری دوم را به “TableStore2” تغییر نام دهید.

در حین اینکه TableStore2 را انتخاب کرده اید، به پنل “Applied Steps” بروید و بر روی SOURCE کلیک کنید.

در قسمت Formula Bar در منبع اطلاعاتی کوئری نوشته شده، “TableStore1” را به “TableStore2” تغییر دهید و سپس ENTER را بزنید.

Don’t forget to update the “Added Custom” step at the bottom of the Applied Steps panel to use the label “Store 2” in the “Store” column.

فراموش نکنید که قسمت “Added Custom” را (در پایین پنل Applied Steps) به روز کنید و به جای برچسب “Store 1” از برچسب “Store 2” در ستون “فروشگاه” استفاده کنید.

نمودار آبشاری (Waterfall Chart) و ترسیم آن در اکسل + مثال

گام چهارم: بستن کوئری ها

از آنجایی که این دو کوئری در نهایت، نتایج خود را به سومین کوئری تجمیعی (stacked) ارسال می کنند، فرآیند ساخت آنها را با Load کردن آنها در اکسل، در همین مرحله به پایان می رسانیم.

از آنجایی که نمی خواهیم جداول خروجی واقعی این دو کوئری را ایجاد کنیم، در تب Home، روی دکمه “Close & Load” کلیک کنید (سمت چپ صفحه ویرایشگر) و “Close & Load to” را انتخاب کنید.

در پنجره Import Data، گزینه “Only Create Connection” را انتخاب و کلیک کنید.

اکنون می توانید هر دو کوئری ساخته شده را در سمت راست صفحه کاری خود در پنل Queries & Connections ببینید.

گام پنجم: اضافه کردن یا تجمیع کردن دو جدول در قالب یک جدول

برای اضافه کردن دو جدول به یکدیگر و تبدیل به جدولی که قرار است در Pivot Table استفاده شود، به آدرس زیر مراجعه کنید:

Data (tab) -> Get & Transform Data (group) -> Get Data -> Combine Queries -> Append

در پنجره Append گزینه “Two Tables” را انتخاب و سپس از هر کدام از منوهای کشویی موجود، یکی از جداول خود را انتخاب و در انتها دکمه OK را فشار دهید.

در ویرایشگر Power Query، یک کوئری جدید ساخته شده به نام “Append1” را مشاهده می کنید که نتایج دو جدول کوئری های قبلی را روی هم چیده تا یک جدول یکپارچه را تشکیل دهند.

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

گام ششم: تغییر نام نام کوئری اضافه شده و خروجی آن به جدول Excel

نام کوئری اضافه شده را به “AllStores” تغییر دهید، سپس از قسمت “Close & Load” بخش “Close & Load To” را انتخاب کنید.

در پنجره Import Data گزینه Table و New Worksheet را به عنوان مقصد انتقال داده ها انتخاب کنید.

نتیجه کار به عنوان یک جدول به صورت زیر ارائه می شود:

ایجاد یک گزارش Pivot Table از داده های جدول اضافه شده

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

در پنل Queries & Connections روی کوئری “AllStores” کلیک راست کنید و “…Load to” را انتخاب کنید. در پنجره Import Data، گزینه PivotTable Report و همینطور گزینه New Worksheet را انتخاب و در نهایت OK را بزنید.

یا …

بر روی جدول نتایج کلیک کنید و به آدرس زیر بروید:

Insert (tab) -> Tables (group) -> PivotTable

اکنون می‌توانید یک Pivot Table کاملاً کارآمد ایجاد کنید تا بهتر بتوانید به سؤالات مربوط به کسب‌وکار خود پاسخ دهید:

اتصال سریع دو جدول با استفاده از مفهوم Relationship

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

اولین جدول که با نام “TableData” نامگذاری شده، دارای اطلاعات تراکنش ها است.

دومین جدول ما که با نام “TableMaster” ناگذاری شده، دارای اطلاعاتی توصیفی درباره هر محصولی است که در گاتالوگ فروشگاه قرار دارد.

سابقاً اینگونه معمول بود که هر ستونی در جدول کاتالوگ ها که نیازمند گزارش گیری در کنار اطلاعات جدول “Data” بود، یا باید با استفاده از تابعی مانند VLOOKUP این دو جدول را به گونه ای به یکدیگر متصل کرد و یا با استفاده از فرمول نویسی هایی که توابع INDEX و MATCH را در خود جای داده بودند.

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

کاری که ما می خواهیم انجام دهیم این است که این دو جدول را با استفاده از مفهومی که به عنوان یک رابطه (Relationship) شناخته می شود به هم وصل کنیم.

رابطه یا Relationship در تحلیل داده ها به چه معنا است؟

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

به عنوان مثال، ما یک ستون “ProductCode” در جدول “TableData” داریم. این ستون، برای هر محصول یک کد در نظر می گیرد و محصولات فروخته شده را برای ما لیست می کند.

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

در جدول “Table Master” برای هر کد محصول، یک ردیف به همراه اطلاعات تشریحی هر محصول داریم.

دقت داشته باشید که کاتالوگ محصولات باید فقط دارای یک ردیف برای هر محصول باشد.

اتصال جداول (ایجاد رابطه بین جداول)

برای ارتباط دو جدول توسط ستون ProductCode، مراحل زیر را انجام دهید:

1. به آدرس زیر بروید:

Data (tab) -> Data Tools (group) -> Relationships

2. از پنجره Manage Relationships روی گزینه New کلیک کنید:

با زدن کلید New، اکسل به صورت خودکار ارتباطی بین جداول TableData و TableMAster را تشخیص می دهد. در صورتی که این رابطه به صورت خودکار تشخیص داده نشد، این مراحل را طی کنید:

In the Create Relationship dialog box, select the two tables using the left-side dropdowns and the “ProductCode” column using both right-side dropdowns. Click OK when finished.

الف – پس از زدن کلید New، در پنجره Create Relationship، از قسمت منوهای کشویی سمت چپ، هر دو جداول TableData و TableMAster و از قسمت سمت راست نیز ستون “ProductCode” را انتخاب کنید. در انتها کلید OK را بزنید و پنجره را ببندید.

اتصال بین جداول به این شکل برقرار خواهد شد:

روی دکمه Close کلیک کنید تا پنجره Manage Relationships بسته شود.

This relationship adds the two related tables to what is known as the Data Model. Think of it as a back-end database of all related tables.

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

ایجاد یک گزارش Pivot Table از جداول متصل به یکدیگر

برای ساخت یک Pivot Table از دو جدول مرتبط با هم، به آدرس زیر می رویم:

Insert (tab) -> Tables (group) -> Pivot Table (dropdown arrow) -> From Data Model

گزینه New Worksheet را انتخاب کنید.

نتیجه نهایی، یک Pivot Table بسیار کارآمد است که با انتخاب آیتم های مختلف جداول از قسمت سمت راست صفحه، می توانید به سوالات کسب و کار خود پاسخ دهید.

کدام روش بهتر است؟ جدول تجمیع شده (Appending) یا اتصال جداول با کمک Relationships؟

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

انتخاب روش “درست” ارتباط مستقیمی با این موضوع دارد که در نگاه اول ببینیم داده های ما چگونه ساختاربندی شده اند و در نهایت اینکه ما می خواهیم چه چیزی را خلق کنیم و به خود شما نیز بستگی دارد که آیا با Power Query راحت تر هستید یا با Relationship.

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