گزارشگیری از چند شیت در یک فایل
تجمیع داده های چند شیت در یک فایل
یکی از کارکردهای اصلی اکسل ذخیره اطلاعات و گزارشگیری از این داده ها در فواصل زمانی دلخواه هست. عموما افراد داده های مورد نظرشون رو بصورت دسته بندی شده ذخیره میکنن. مثلا ترجیح میدن که داده های فروش یک فروشگاه در هر ماه در شیت های جدا ذخیره بشه. یا مثلا هر فاکتور در یک شیت ثبت بشه یا مثلا داده ها رو در قالب یک ماتریس ثبت میکنن. درسته که این مسائل کار ما رو برای ورود داده در اکسل راحت تر میکنه اما مشکل اصلی موقع گزارشگیری اتفاق میفته که باید این دیتاها یکپارچه بشن و بعد گزارشگیری رو انجام بدیم. سابقا مشکلاتی برای این موضوع داشتیم و باید میرفتیم سراغ کدنویسی VBA اما الان روش های مختلف و جذابی وجود داره، قبلا توابعی رو در گوگل شیت (اکسل آنلاین) معرفی کردیم که میتونن دیتابیس های موجود در شیت های مختلف رو تجمیع کنن و ما میتونیم روی اونها گزارش بگیریم. برای این موضوع پیشنهاد میکنم حتما مقاله Vstack/Hstack رو مطالعه کنید. اما در این مقاله قصد داریم که با استفاده از پاورکوئری این کار و انجام بدیم. در واقع با استفاده از ابزار Power Query دیتاهای موجود در شیت های مختلف رو یکپارچه کنیم و گزارش رو روی داده های تجمیع شده بگیریم.
فرض کنید داده های فروش چند ماه در شیت های یک فایل ذخیره شده اند و قرار هست که ماه های بعدی هم به همین صورت ثبت بشن (هر داده ای در Table به نام همون ماه ذخیره شده مثلا داده های ماه تیر، در Table به نام Tir ذخیره شده اند). حالا میخواهیم روی کل داده ها گزارش بگیریم . اول باید این شیت ها رو با هم ترکیب کنیم که بتونیم این کار و انجام بدیم و ما میخوایم این کار رو با استفاده از پاورکوئری انجام بدیم. قبلا در آموزش مربوط به استفاده از دستور append گفتیم که وقتی بخواهیم از این دستور استفاده کنیم باید داده ها رو اول به پاورکوئری معرفی کنیم و بعد append کنیم. اما این کار برای زمانی مناسبه که تعداد تیبل ها ثابت و محدود باشه. اگر تعداد شیت ها زیاد و متغیر باشه این روش خیلی کاربردی نخواهد بود. پس برای اینکه کل فرایند کاملا خودکار انجام بشه و در صورت حذف و اضافه شیت به فایل، داده ها تجمیع بشه مطابق با روش زیر ادامه میدیم:
مرحله ۱: فراخوانی تیبل های موجود در فایل
برای این کار کافیه فایل اکسل رو باز کنیم، از مسیر Data/ Get Data/ From Other Sources روی گزینه Blank Query کلیک میکنیم. در پنجره باز شده، در نوار بالا (مطابق شکل ۱) تایپ میکنیم:
= Excel.CurrentWorkbook()
شکل ۱ – فراخوانی داده های موجود در فایل جاری
بعد از نوشتن این عبارت و زدن Enter همه Table های موجود در فایل برای ما لیست میشه.
شکل ۲- لیست Table های موجود در فایل
در جدول شکل ۲ ، همه Table های موجود در فایل با نام خودشون نمایش داده شدن.
مرحله ۲: باز کردن تیبل ها و قرار دادن داده ها زیر هم
حالا کافیه روی فلش دو طرف که در شکل ۳ نمایش داده شده کلیک کنیم و از پنجره باز شده تیک Use original column name as prefix رو برداریم و ok بزنیم.
شکل ۳- قراردادن داده های هر تیبل زیر هم
با این کار همه داده های موجود در همه تیبل ها به همراه نام table در ستون جدید زیر هم قرار میگیرن. (شکل ۴)
شکل ۴- داده های append شده
مرحله ۳: اعمال تغییرات مورد نظر
حالا فرش کنید میخواهیم بعضی ستون ها رو از دیتابیس حذف کنیم یا بعضی ردیف ها و … یا مثلا جنس داده های رو مشخص کنیم. این تغییرات رو در ایت مرحله انجام میدیم. مثلا اگر داده ها ردیف جمع کل داشته باشه، از بین داده ها حذف میکنیم. یا مثلا میخواهیم فرمت داده ها رو مشخص کنیم. ctrl+A رو میزنیم که همه داده انتخاب بشه و بعد از تب Transformروی گزینه Detect data type کلیک میکنیم.
شکل ۵- تعیین کردن فرمت داده های موجود
مرحله ۴: انتقال دیتا به اکسل
حالا که داده ها زیر هم قرار گرفتن و تغییرات مورد نظر روی اونها اعمال شد، کافیه که داده رو به اکسل منتقل کنیم و گزارش بگیریم. برای این کار از تب Home روی گزینه Close and load کلیک میکنیم تا داده ها به شیت اکسل منتقل بشن. شکل ۶
شکل ۶- انتقال دیتای آماده به اکسل برای گزارشگیری
با این کار کل دیتا رو بصورت تجمیع شده و در یک شیت خواهیم داشت بصورتی که به منبع اصلی هم متصل است و اگر مثلا داده ای در ماه خاصی نیاز به تغییر داشته باشه یا کم و زیاد بشه، کافیه روی دیتای نهایی کلیک راست کنیم و refresh رو بزنیم. تا همه داده ها مجددا زیر هم قرار بگیرن.
اما ی چیزی اینجا درست نیست. به تصویر زیر نگاه کنید. با هر بار رفرش کردن صفحه، به تعداد ردیف های داده ها اضافه میشه د رحالیکه ما دیتایی اضافه نمیکنیم و این بخاطر اینه که ما گفتیم همه شیت های موجود در فایل رو زیر هم قرار بده. پس میاد و دیتای append شده رو هم زیر هم قرار میده و ما باید این شیت رو از محاسبات خارج کنیم.
پس باید برگردیم به کوئری که بتونیم ویرایش کنیم. برای این کار روی کوئری کلیک راست کرده و edit رو میزنیم و وارد محیط پاورکوئری ادیتور میشیم. حالا از محیط ویرایش کوئری، روی Source کلیک میکنیم و Query1 که در واقع جدول مربوط به نتیجه نهایی هست رو مشاهده میکنیم. حالا باید کاری کنیم که این جدول در لیست تیبل ها نیاد.
شکل ۷- نمایش تیبل نهایی در لیست تیبل های فایل
برای این کار کافیه که طوری فیلتر کنیم که تیبل با این نام در لیست نیاد پس روی زبانه Name کلیک میکنیم و از قسمت text filter گزینه Doesn’t contain رو انتخاب میکنیم.(شکل ۸)
شکل ۸- فیلتر کردن جداول مورد نظر
چون اسم جدول نهایی query1 هست میتونیم فیلتر کنیم که جداولی رو بیار که شامل عبارت query نیستن. با این کار همه جداول میان به جز جدول نهایی که اسمش query هست.
شکل ۹ – حذف تیبل مورد نظر
نکته:اگر شیت های دیگه ای دی فایل وجود داره و Table داره باید بتونیم الگویی روی نام اونها پیدا کنیم که بتونیم فیلتر کنیم. مثلا نام table ها رو با کلمه خاصی شروع کنیم که بعدا بتونیم بهتر فیلتر کنیم. در اینجا چون فقط همین شیت ها و تیبل ها وجود داره، فیلتر کردنش راحته. پس بهتره که در نامگذاری Table و … همیشه اصولی رو رعایت کنیم و قوانینی برای نامگذاری داشته باشیم تا بعدا به مشکل برخورد نکنیم.
وقتی این مرحله رو اضافه کردیم، حالا همه جداول به جز Query1 وارد فرآیند تجمیع میشن. و نتیجه رو مطابق خواسته خودمون خواهیم داشت. حالا هر چقدر هم رفرش انجام بشه، دیگه رکوردهای جدول نهایی به گزارش اضافه نمیشه.
مرحله ۵: گزارشگیری
حالا میخواهیم از این دیتابیس گزارش بگیریم. هم میتونیم از روی دیتابیس ایجاد شده، پیوت تیبل اضافه کنیم. اینکار باعث میشه مجدد یک شیت اضافه بشه و باید بریم اون رو هم از لیست تیبل ها در بیاریم. پس برای اینکه شیت جدید اضافه نکنیم، کافیه که روی کوئری کلیک راست کرده و از قسمت load to گزینه pivotable report رو انتخاب کنیم مطابق شکل ۱۰.
شکل ۱۰- ایجاد گزارش از روی دیتابیس
حالا کافیه گزارش نهایی رو بسازیم. مثلا اینکه در مجموع هر محصول چقدر فروش رفته. برای ایجاد گزارش با استفاده از پیوت تیبل میتونید مقاله مربوط به پیوت تیبل رو مطالعه کنید.
شکل ۱۱- گزارش نهایی
مرحله ۶: بروزرسانی گزارش
حالا فرض کنید داده های ماه مرداد به دستمون رسیده و میخوایم به گزارش اضافه کنیم. کافیه شیت مرداد رو به فایل اضافه کنیم و داده ها رو در قالب Table به نام Mordad قرار بدیم و فقط گزارش نهایی رو refresh کنیم. (شکل ۱۲) به این ترتیب داده های ماه جدید به گزارش اضافه میشن.
شکل ۱۲- بروزرسانی گزارش
جهت تمرین بیشتر میتونید فایل زیر رو دانلود کنید و این مراحل رو روش انجام بدید.
مشاهده ویدئو گزارشگیری از چند شیت در یک فایل
در این ویدئو درباره نحوه گزارشگیری از چند شیت در یک فایل اکسل توضیح داده شده
مشاهده این ویدئو در کانال یوتیوب اکسل پدیا
دانلود فایل اکسل
توجه: جهت دانلود فایل حتما در حساب کاربری خود وارد شوید.
