فهرست
فرمول بندی مسئله
به عنوان داده ورودی، یک فایل اکسل داریم که یکی از برگه ها حاوی چندین جدول با اطلاعات فروش به شکل زیر است:
توجه داشته باشید که:
- جداول در اندازه های مختلف و با مجموعه های مختلف محصولات و مناطق در ردیف ها و ستون ها بدون هیچ گونه مرتب سازی.
- خطوط خالی را می توان بین جداول درج کرد.
- تعداد جداول می تواند هر کدام باشد.
دو فرض مهم فرض بر این است که:
- بالای هر جدول، در ستون اول، نام مدیری که جدول فروش او را نشان می دهد (ایوانف، پتروف، سیدوروف و غیره) وجود دارد.
- نام کالاها و مناطق در تمام جداول به یک صورت – با دقت حروف نوشته شده است.
هدف نهایی جمع آوری داده ها از همه جداول در یک جدول نرمال شده مسطح است که برای تجزیه و تحلیل بعدی و ایجاد یک خلاصه مناسب است، به عنوان مثال در این جدول:
مرحله 1. به فایل متصل شوید
بیایید یک فایل اکسل خالی جدید ایجاد کنیم و آن را در برگه انتخاب کنیم داده ها فرمان دریافت داده – از فایل – از کتاب (داده ها - از فایل - از کتاب کار). محل فایل منبع را با داده های فروش مشخص کنید و سپس در پنجره ناوبر برگه مورد نیاز خود را انتخاب کنید و روی دکمه کلیک کنید تبدیل داده ها (تبدیل داده ها):
در نتیجه، تمام داده های آن باید در ویرایشگر Power Query بارگذاری شود:
مرحله 2. سطل زباله را تمیز کنید
مراحل تولید شده به صورت خودکار را حذف کنید نوع اصلاح شده (تغییر نوع) и سربرگ های بالا (سربرگ های تبلیغ شده) و با استفاده از فیلتر از شر خطوط و خطوط خالی با مجموع خلاص شوید تهی и TOTAL توسط ستون اول در نتیجه، تصویر زیر را دریافت می کنیم:
مرحله 3. اضافه کردن مدیران
برای اینکه بعداً بفهمیم فروش چه کسی است، لازم است یک ستون به جدول خود اضافه کنیم، جایی که در هر ردیف یک نام خانوادگی مربوطه وجود دارد. برای این:
1. بیایید با استفاده از دستور یک ستون کمکی با شماره خطوط اضافه کنیم اضافه کردن ستون - ستون شاخص - از 0 (افزودن ستون - ستون فهرست - از 0).
2. یک ستون با فرمول با دستور اضافه کنید اضافه کردن یک ستون - ستون سفارشی (افزودن ستون - ستون سفارشی) و ساخت زیر را در آنجا معرفی کنید:
منطق این فرمول ساده است - اگر مقدار سلول بعدی در ستون اول "محصول" باشد، به این معنی است که ما به صورت تصادفی به ابتدای جدول جدید برخورد کرده ایم، بنابراین مقدار سلول قبلی را با علامت نشان می دهیم. نام مدیر در غیر این صورت چیزی نمایش نمی دهیم یعنی null.
برای بدست آوردن سلول والد با نام خانوادگی ابتدا به جدول مرحله قبل مراجعه می کنیم #"شاخص اضافه شد"و سپس نام ستون مورد نیاز خود را مشخص کنید [ستون 1] در پرانتز مربع و شماره سلول در آن ستون در پرانتز فرفری. شماره سلول یک کمتر از شماره فعلی خواهد بود که از ستون می گیریم شاخصبود.
3. باقی مانده است که سلول های خالی را با آن پر کنیم تهی نام از سلول های بالاتر با دستور تبدیل - پر کردن - پایین (تبدیل - پر کردن - پایین) و ستون مورد نیاز دیگر را با شاخص ها و ردیف هایی با نام خانوادگی در ستون اول حذف کنید. در نتیجه، دریافت می کنیم:
مرحله 4. گروه بندی به جداول جداگانه توسط مدیران
مرحله بعدی این است که ردیف های هر مدیر را در جداول جداگانه گروه بندی کنید. برای این کار در تب Transformation از دستور Group by (Transform – Group By) استفاده کنید و در پنجره باز شده ستون Manager و عملیات All rows (All rows) را انتخاب کنید تا به سادگی داده ها را بدون اعمال هیچ تابع تجمیع جمع آوری کنید. آنها (مجموع، میانگین، و غیره). پ.):
در نتیجه، جداول جداگانه ای برای هر مدیر دریافت می کنیم:
مرحله 5: تبدیل جداول تودرتو
حالا جداول را که در هر سلول از ستون به دست آمده است را می دهیم همه داده ها در شکل مناسب
ابتدا ستونی را که دیگر در هر جدول مورد نیاز نیست حذف کنید مدیر. دوباره استفاده می کنیم ستون سفارشی برگ دگرگونی (تبدیل - ستون سفارشی) و فرمول زیر:
سپس با یک ستون محاسبه شده دیگر، ردیف اول هر جدول را به عناوین بالا می بریم:
و در نهایت، تبدیل اصلی را انجام می دهیم - هر جدول را با استفاده از تابع M باز می کنیم Table.UnpivotOtherColumns:
نام مناطق از هدر به یک ستون جدید می رود و ما یک جدول باریک تر، اما در عین حال، یک جدول نرمال شده طولانی تر دریافت خواهیم کرد. سلول های خالی با تهی نادیده گرفته می شوند
برای خلاص شدن از شر ستون های میانی غیر ضروری، ما داریم:
مرحله 6 جداول تودرتو را گسترش دهید
باقی می ماند که همه جداول تودرتو نرمال شده را با استفاده از دکمه با فلش های دوتایی در سر ستون، در یک لیست واحد گسترش دهیم:
... و در نهایت به آنچه می خواستیم می رسیم:
با استفاده از دستور می توانید جدول به دست آمده را به اکسل برگردانید صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…).
- جداول را با هدرهای مختلف از چندین کتاب بسازید
- جمع آوری داده ها از تمام فایل های موجود در یک پوشه مشخص
- جمع آوری داده ها از تمام برگه های کتاب در یک جدول