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

فرمول بندی مسئله

ما چندین فایل (در مثال ما - 4 قطعه، در حالت کلی - به تعداد دلخواه) در یک پوشه داریم. گزارش ها:

در داخل، این فایل ها به شکل زیر هستند:

که در آن:

• برگه داده ای که ما نیاز داریم همیشه فراخوانی می شود عکس، اما می تواند در هر جایی از کتاب کار باشد.
  
• فراتر از ورق عکس هر کتاب ممکن است برگه های دیگری داشته باشد.
• جدول‌های دارای داده دارای تعداد ردیف‌های متفاوتی هستند و ممکن است با یک ردیف متفاوت در کاربرگ شروع شوند.
• نام ستون های مشابه در جداول مختلف ممکن است متفاوت باشد (به عنوان مثال، مقدار = کمیت = تعداد).
• ستون ها در جداول را می توان به ترتیب متفاوتی مرتب کرد.

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

مرحله 1. آماده سازی دایرکتوری از نام ستون ها

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

ما این لیست را با استفاده از دکمه Format as table در برگه به ​​یک جدول "هوشمند" پویا تبدیل می کنیم صفحه اصلی (صفحه اصلی - قالب بندی به عنوان جدول) یا میانبر صفحه کلید کلیدهای Ctrl+T و با دستور آن را در Power Query بارگذاری کنید داده ها - از جدول / محدوده (داده ها - از جدول/محدوده). در نسخه های اخیر اکسل، نام آن به تغییر یافته است با برگ (از برگه).

در پنجره ویرایشگر کوئری Power Query، ما به طور سنتی مرحله را حذف می کنیم نوع تغییر کرد و یک مرحله جدید به جای آن با کلیک بر روی دکمه اضافه کنید f_xدر نوار فرمول (اگر قابل مشاهده نیست، می توانید آن را در برگه فعال کنید مرور) و فرمول را در زبان داخلی Power Query M وارد کنید:

=Table.ToRows(منبع)

این دستور دستور بارگذاری شده در مرحله قبل را تبدیل می کند منبع جدول مرجع به لیستی متشکل از لیست های تو در تو (List) که هر کدام به نوبه خود یک جفت مقدار هستند. شد- شد از یک خط:

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

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

مرحله 2. ما همه چیز را از همه فایل ها همانطور که هست بارگذاری می کنیم

حالا بیایید محتویات همه فایل های خود را از پوشه بارگیری کنیم - فعلاً همانطور که هست. انتخاب تیم ها داده – دریافت داده – از فایل – از پوشه (داده - دریافت داده - از فایل - از پوشه) و سپس پوشه ای که کتاب های منبع ما در آن قرار دارند.

در پنجره پیش نمایش، کلیک کنید تبدیل (تبدیل) or تغییر دادن (ویرایش):

و سپس محتویات همه فایل های دانلود شده را گسترش دهید (دودویی) دکمه با فلش های دوتایی در عنوان ستون محتوا:

Power Query در مثال فایل اول (Vostok.xlsx) از ما نام برگه ای را که می خواهیم از هر کتاب کار برداریم می پرسد - انتخاب کنید عکس و OK را فشار دهید:

پس از آن (در واقع) چندین رویداد که برای کاربر واضح نیست رخ می دهد که عواقب آن در پنل سمت چپ به وضوح قابل مشاهده است:

1. Power Query اولین فایل را از پوشه می گیرد (ما آن را خواهیم داشت Vostok.xlsx — دیدن نمونه فایل) به عنوان مثال و با ایجاد یک کوئری محتوای آن را وارد می کند تبدیل فایل نمونه. این پرس و جو دارای مراحل ساده ای مانند منبع (دسترسی به فایل) جهت یابی (انتخاب برگه) و احتمالاً بالا بردن عناوین. این درخواست فقط می تواند داده ها را از یک فایل خاص بارگیری کند Vostok.xlsx.
   
2. بر اساس این درخواست، تابع مرتبط با آن ایجاد خواهد شد تبدیل پرونده (با یک نماد مشخصه نشان داده شده است f_x)، که در آن فایل منبع دیگر یک ثابت نیست، بلکه یک مقدار متغیر – یک پارامتر است. بنابراین، این تابع می تواند داده هایی را از هر کتابی که به عنوان آرگومان وارد آن می کنیم استخراج کند.
   
3. تابع به نوبه خود برای هر فایل (باینری) از ستون اعمال می شود محتوا – استپ مسئول این امر است فراخوانی تابع سفارشی در کوئری ما که ستونی را به لیست فایل ها اضافه می کند تبدیل پرونده با نتایج واردات از هر کتاب کار:

   

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

   
   

مرحله 3. سنباده زدن

تصویر قبلی به وضوح نشان می دهد که مونتاژ مستقیم "همانطور که هست" از کیفیت پایینی برخوردار است:

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

شما می توانید همه این مشکلات را به راحتی حل کنید - فقط پرس و جو Convert Sample File را تغییر دهید. تمام تنظیماتی که روی آن انجام می‌دهیم به‌طور خودکار در تابع تبدیل فایل مرتبط قرار می‌گیرند، به این معنی که بعداً هنگام وارد کردن داده‌ها از هر فایل استفاده می‌شوند.

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

برای اینکه ستون‌های فایل‌های مختلف بعداً به‌طور خودکار زیر یکدیگر قرار گیرند، باید یک نام نامگذاری شوند. شما می توانید چنین تغییر نام انبوهی را با توجه به دایرکتوری ایجاد شده قبلی با یک خط M-code انجام دهید. بیایید دوباره دکمه را فشار دهیم f_x در نوار فرمول و یک تابع برای تغییر اضافه کنید:

= Table.RenameColumns (#"Elevated Headers", Headers, MissingField.Ignore)

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

در واقع، این همه است.

بازگشت به درخواست گزارش ها ما یک تصویر کاملا متفاوت خواهیم دید - بسیار زیباتر از تصویر قبلی:

• Power Query، Power Pivot، Power BI چیست و چرا یک کاربر اکسل به آنها نیاز دارد
  
• جمع آوری داده ها از تمام فایل های موجود در یک پوشه مشخص
• جمع آوری داده ها از تمام برگه های کتاب در یک جدول