فرض کنید شما چندین پروژه را با بودجه های مختلف اجرا می کنید و می خواهید هزینه های خود را برای هر یک از آنها تجسم کنید. یعنی از این جدول منبع:
.. چیزی شبیه به این دریافت کنید:
به عبارت دیگر، شما باید بودجه را در روزهای هر پروژه تقسیم کنید و یک نسخه ساده شده از نمودار گانت پروژه را دریافت کنید. انجام این کار با دستان شما طولانی و خسته کننده است، ماکروها دشوار هستند، اما Power Query برای اکسل در چنین شرایطی قدرت خود را با شکوه تمام نشان می دهد.
پرس و جو برق افزونه ای از مایکروسافت است که می تواند داده ها را تقریباً از هر منبعی به اکسل وارد کند و سپس آن را به روش های مختلف تبدیل کند. در اکسل 2016، این افزونه قبلاً به طور پیش فرض داخلی است و برای اکسل 2010-2013 می توان آن را از وب سایت مایکروسافت دانلود کرد و سپس بر روی رایانه شخصی خود نصب کرد.
ابتدا، بیایید جدول اصلی خود را با انتخاب دستور به یک جدول "هوشمند" تبدیل کنیم قالب بندی به صورت جدول برگ صفحه اصلی (صفحه اصلی - قالب بندی به عنوان جدول) یا با فشار دادن میانبر صفحه کلید کلیدهای Ctrl+T :
سپس به برگه بروید داده ها (اگر اکسل 2016 دارید) یا روی برگه پرس و جو برق (اگر اکسل 2010-2013 دارید و Power Query را به عنوان یک افزونه جداگانه نصب کرده اید) و روی دکمه From Table / Range کلیک کنید. :
جدول هوشمند ما در ویرایشگر پرس و جو Power Query بارگذاری می‌شود، جایی که اولین گام این است که قالب‌های اعداد را برای هر ستون با استفاده از کرکره‌های هدر جدول تنظیم کنید:
برای محاسبه بودجه در روز، باید مدت زمان هر پروژه را محاسبه کنید. برای انجام این کار، کلید (کلید را نگه دارید کلیدهای Ctrl) ستون اول پایان، و سپس آغاز و یک تیم انتخاب کنید اضافه کردن ستون - تاریخ - کم کردن روز (افزودن ستون - تاریخ - کم کردن روزها):
اعداد به دست آمده 1 کمتر از مقدار لازم است، زیرا قرار است هر پروژه را در روز اول صبح شروع کنیم و در روز آخر عصر به پایان برسانیم. بنابراین، ستون حاصل را انتخاب کرده و با استفاده از دستور یک واحد به آن اضافه کنید تبدیل – استاندارد – افزودن (تبدیل - استاندارد - افزودن):
حالا بیایید یک ستون اضافه کنیم که در آن بودجه در روز را محاسبه می کنیم. برای انجام این کار، در برگه اضافه کردن ستون من بازی نمی کنم ستون سفارشی (ستون سفارشی) و در پنجره ای که ظاهر می شود، نام فیلد جدید و فرمول محاسبه را با استفاده از نام ستون های لیست وارد کنید:
اکنون ظریف ترین لحظه - ستون محاسبه شده دیگری با لیستی از تاریخ ها از ابتدا تا انتها با یک مرحله 1 روزه ایجاد می کنیم. برای انجام این کار، دوباره دکمه را فشار دهید ستون سفارشی (ستون سفارشی) و از زبان داخلی Power Query M که فراخوانی می شود استفاده کنید لیست. تاریخ:
این تابع سه آرگومان دارد:
- تاریخ شروع - در مورد ما، از ستون گرفته شده است آغاز
- تعداد تاریخ هایی که باید تولید شوند - در مورد ما، این تعداد روزهای هر پروژه است که قبلاً در ستون شمارش کردیم. تفریق
- مرحله زمانی - تنظیم شده توسط طراحی #دوره (1,0,0,0)به معنی در زبان م – یک روز، ساعت صفر، صفر دقیقه، صفر ثانیه.
پس از کلیک روی OK ما یک لیست (فهرست) از تاریخ ها را دریافت می کنیم که می توان آن را با استفاده از دکمه موجود در سرفصل جدول به خطوط جدید گسترش داد:
… و دریافت می کنیم:
اکنون تنها چیزی که باقی می ماند این است که جدول را جمع کنید و از تاریخ های ایجاد شده به عنوان نام ستون های جدید استفاده کنید. تیم مسئول این است. ستون جزئیات (ستون محوری) برگ تبدیل (تبدیل):
پس از کلیک روی OK ما نتیجه ای بسیار نزدیک به مطلوب می گیریم:
Null، در این مورد، آنالوگ یک سلول خالی در اکسل است.
باقی مانده است که ستون های غیر ضروری را حذف کنید و جدول حاصل را در کنار داده های اصلی با دستور تخلیه کنید بستن و بارگیری – بستن و بارگیری در… (بستن و بارگذاری - بستن و بارگذاری به…) برگ صفحه اصلی (خانه):
در نتیجه می گیریم:
برای زیبایی بیشتر، می توانید ظاهر میزهای هوشمند به دست آمده را روی برگه سفارشی کنید سازنده (طرح): یک سبک تک رنگ را تنظیم کنید، دکمه‌های فیلتر را غیرفعال کنید، مجموع‌ها را فعال کنید، و غیره. صفحه اصلی — قالب بندی شرطی — مقیاس های رنگی (صفحه اصلی - قالب بندی شرطی - مقیاس های رنگی):
و بهترین بخش این است که در آینده می توانید با خیال راحت موارد قدیمی را ویرایش کنید یا پروژه های جدید را به جدول اصلی اضافه کنید و سپس جدول سمت راست را با تاریخ ها با دکمه سمت راست ماوس به روز کنید - و Power Query تمام اقداماتی را که انجام داده ایم به طور خودکار تکرار می کند. .
وجود دارد!
- نمودار گانت در اکسل با استفاده از قالب بندی شرطی
- تقویم نقطه عطف پروژه
- ایجاد ردیف های تکراری با Power Query