فهرست
بسیاری از فرآیندهای تجاری (و حتی کل مشاغل) در این زندگی شامل انجام سفارشات توسط تعداد محدودی از مجریان در یک مهلت مشخص است. برنامه ریزی در چنین مواردی، همانطور که می گویند، "از تقویم" اتفاق می افتد و اغلب نیاز به انتقال رویدادهای برنامه ریزی شده در آن (سفارش ها، جلسات، تحویل) به Microsoft Excel وجود دارد - برای تجزیه و تحلیل بیشتر توسط فرمول ها، جداول محوری، نمودار، و غیره.
البته، من می خواهم چنین انتقالی را نه با کپی احمقانه (که فقط دشوار نیست) اجرا کنم، بلکه با به روز رسانی خودکار داده ها به طوری که در آینده تمام تغییرات ایجاد شده در تقویم و سفارشات جدید در پرواز نمایش داده شود. برتری داشتن. شما می توانید با استفاده از افزونه Power Query ساخته شده در Microsoft Excel، از نسخه 2016، چنین وارداتی را در عرض چند دقیقه پیاده سازی کنید (برای Excel 2010-2013، می توان آن را از وب سایت مایکروسافت دانلود کرد و به طور جداگانه از لینک نصب کرد) .
فرض کنید از Google Calendar رایگان برای برنامه ریزی استفاده می کنیم، که در آن، برای راحتی، یک تقویم جداگانه ایجاد کردم (دکمه با علامت مثبت در گوشه سمت راست پایین در کنار تقویم های دیگر) با عنوان مهاجرت کاری. در اینجا تمام سفارشاتی را که باید تکمیل و به آدرس مشتریان تحویل داده شوند وارد می کنیم:
با دوبار کلیک کردن بر روی هر سفارش، می توانید جزئیات آن را مشاهده یا ویرایش کنید:
توجه داشته باشید که:
- نام رویداد است مدیرکه این دستور را انجام می دهد (الینا) و orderNumber
- نشان داد نشانی تحویل
- یادداشت حاوی (در خطوط جداگانه، اما به هر ترتیب) پارامترهای سفارش: نوع پرداخت، مبلغ، نام مشتری و غیره در قالب است. پارامتر=مقدار.
برای وضوح، دستورات هر مدیر با رنگ خاص خود برجسته می شود، اگرچه این ضروری نیست.
مرحله 1. یک پیوند به Google Calendar دریافت کنید
ابتدا باید یک پیوند وب به تقویم سفارش خود دریافت کنیم. برای این کار بر روی دکمه سه نقطه کلیک کنید گزینه های تقویم کار می کنند در کنار نام تقویم و دستور را انتخاب کنید تنظیمات و اشتراک گذاری:
در پنجره‌ای که باز می‌شود، می‌توانید در صورت تمایل، تقویم را عمومی کنید یا برای تک تک کاربران دسترسی به آن را باز کنید. همچنین برای دسترسی خصوصی به تقویم در قالب iCal به پیوند نیاز داریم:
مرحله 2. داده ها را از تقویم در Power Query بارگیری کنید
حالا اکسل را باز کنید و روی تب داده ها (اگر اکسل 2010-2013 دارید، در تب پرس و جو برق) یک دستور را انتخاب کنید از اینترنت (داده ها - از اینترنت). سپس مسیر کپی شده را در تقویم پیست کرده و OK کنید.
iCal Power Query فرمت را تشخیص نمی دهد، اما کمک به آن آسان است. اساسا، iCal یک فایل متنی ساده با یک دو نقطه به عنوان جداکننده است و در داخل آن چیزی شبیه به این است:
بنابراین می توانید فقط بر روی نماد فایل دانلود شده کلیک راست کرده و فرمتی را انتخاب کنید که از نظر معنی نزدیکتر است. CSV – و داده‌های ما در مورد همه سفارش‌ها در ویرایشگر پرس و جوی Power Query بارگیری می‌شوند و به دو ستون با دو نقطه تقسیم می‌شوند:
اگر دقت کنید، به وضوح متوجه می شوید که:
- اطلاعات مربوط به هر رویداد (سفارش) در یک بلوک با کلمه BEGIN شروع و با END ختم می شود.
- تاریخ شروع و پایان در رشته هایی با برچسب DTSTART و DTEND ذخیره می شود.
- آدرس حمل و نقل LOCATION است.
- یادداشت سفارش - قسمت DESCRIPTION.
- نام رویداد (نام مدیر و شماره سفارش) - قسمت SUMMARY.
باقی مانده است که این اطلاعات مفید را استخراج کرده و به یک جدول مناسب تبدیل کنیم.
مرحله 3. تبدیل به نمای عادی
برای انجام این کار، زنجیره اقدامات زیر را انجام دهید:
- بیایید قبل از اولین دستور BEGIN، 7 خط بالایی را که به آن نیاز نداریم حذف کنیم صفحه اصلی — حذف ردیف ها — حذف ردیف های بالا (صفحه اصلی - حذف ردیف ها - حذف ردیف های بالا).
- فیلتر بر اساس ستون Column1 خطوط حاوی فیلدهای مورد نیاز ما: DTSTART، DTEND، DESCRIPTION، LOCATION و SUMMARY.
- در برگه Advanced اضافه کردن یک ستون را انتخاب کنید ستون فهرست (افزودن ستون - ستون فهرست)برای اضافه کردن یک ستون شماره ردیف به داده های خود.
- همانجا روی برگه اضافه کردن یک ستون یک تیم انتخاب کنید ستون مشروط (افزودن ستون - ستون مشروط) و در ابتدای هر بلوک (سفارش) مقدار شاخص را نمایش می دهیم:
- سلول های خالی ستون حاصل را پر کنید مسدود کردنبا کلیک راست بر روی عنوان آن و انتخاب دستور پر کردن - پایین (پر کردن - پایین).
- ستون غیر ضروری را حذف کنید شاخص.
- یک ستون را انتخاب کنید Column1 و یک پیچیدگی از داده ها را از ستون انجام دهید Column2 با استفاده از دستور تبدیل - ستون محوری (تبدیل - ستون محوری). حتما در گزینه ها انتخاب کنید تجمیع نکنید (تجمیع نکنید)به طوری که هیچ تابع ریاضی روی داده ها اعمال نمی شود:
- در جدول دو بعدی (متقاطع) به دست آمده، اسلش های پشت ستون آدرس را پاک کنید (روی هدر ستون راست کلیک کنید - جایگزینی مقادیر) و ستون غیر ضروری را حذف کنید مسدود کردن.
- برای تبدیل محتویات ستون ها DTSTART и DTEND در یک تاریخ کامل، با برجسته کردن آنها، در برگه انتخاب کنید Transform – Date – Run Analysis (تبدیل - تاریخ - تجزیه). سپس کد موجود در نوار فرمول را با جایگزین کردن تابع تصحیح می کنیم تاریخ از on DateTime.Fromتا ارزش های زمانی را از دست ندهید:
- سپس با کلیک راست روی هدر، ستون را تقسیم می کنیم شرح با پارامترهای ترتیب توسط جداکننده - نماد n، اما در همان زمان، در پارامترها، تقسیم را به ردیف و نه به ستون انتخاب می کنیم:
- یک بار دیگر، ستون حاصل را به دو ستون جداگانه - پارامتر و مقدار، اما با علامت تساوی تقسیم می کنیم.
- انتخاب یک ستون DESCRIPTION.1 پیچیدگی را همانطور که قبلا انجام دادیم با دستور انجام دهید تبدیل - ستون محوری (تبدیل - ستون محوری). ستون مقدار در این مورد، ستونی با مقادیر پارامتر - خواهد بود DESCRIPTION.2 حتماً یک تابع را در پارامترها انتخاب کنید تجمیع نکنید (تجمیع نکنید):
- باقی مانده است که قالب ها را برای همه ستون ها تنظیم کنید و آنها را به دلخواه تغییر نام دهید. و با دستور می توانید نتایج را دوباره در اکسل آپلود کنید صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…)
و در اینجا لیست سفارشات بارگیری شده در اکسل از Google Calendar است:
در آینده، هنگام تغییر یا افزودن سفارشات جدید به تقویم، فقط کافی است درخواست خود را با دستور به روز کنیم. داده - تازه کردن همه (داده ها - بازخوانی همه).
- تقویم کارخانه در اکسل به روز شده از اینترنت از طریق Power Query
- تبدیل ستون به جدول
- ایجاد پایگاه داده در اکسل