بخش ۱ از ۳:مبانی پیشرفتهٔ تحلیل زمان، اتصال به منابع خارجی (اکسل، وب، PDF) و الحاق کوئری‌ها (Append)


بخش ۱ از ۳:
مبانی پیشرفتهٔ تحلیل زمان، اتصال به منابع خارجی (اکسل، وب، PDF) و الحاق کوئری‌ها (Append)

ویکی مطالعاتی جامع: تحلیل داده و ابزار پاور
کوئری (Power Query) در اکسل

جلسهٔ دوم (فایل ۱ از ۳) | استخراج منحنی S از P6،
مدیریت جامع تاریخ و زمان، استخراج داده از وب و PDF، پاک‌سازی فایل‌های نامنظم و الحاق ساختاری کوئری‌ها
(Append)

عنوان درس / کارگاه مدرس مدت زمان صوت ساختار آموزشی
هوش تجاری و کنترل پروژه پیشرفته (Excel & Power Query) در صوت ذکر نشد (استاد دورهٔ کنترل پروژه) حدود ۲ ساعت و ۵ دقیقه (۱۲۴ دقیقه و ۴۸ ثانیه) جلسه ۲ (بخش ۱ از ۳)

فهرست موضوعات کلیدی بررسی‌شده در این
جلسه:

  • حل گام‌به‌گام تمرین کلاسی شیت ۳-۳: تبدیل داده‌های توزیع بودجه روزانه پریماورا P6 (TBL_P6) به منحنی S هفتگی تجمعی درصد پیشرفت
  • مهندسی ترنسپوز برای ماتریس‌های بودجه‌ای و حذف ردیف متادیتای مازاد با دستور Remove Top Rows
  • تکنیک اجتناب از استپ‌های تکراری: ویرایش مستقیم نوع داده در فرمول‌بار زبان اِم (تبدیل دستی
    type datetime به type date)
  • تحلیل تفاوت بنیادین داده‌های تجمعی (Cumulative) و دوره‌ای (Interval) در گروه‌بندی هفتگی: چرایی انتخاب تابع Max به جای Sum در
    انتهای هفته (End of Week)
  • محاسبه درصد پیشرفت در پاور کوئری از طریق عملگر Standard -> Divide و نحوه مدیریت قالب درصدی در
    تنظیمات فیلد پیوت‌تیبل (Value Field Settings) به جای ستون اکسل
  • حل باگ گروه‌بندی خودکار ماهانه در پیوت‌چارت از طریق دستور Ungroup برای بازیابی تاریخ‌های واقعی
    هفتگی
  • تفکیک اجزای تاریخ در جدول مشتریان (TBL_Customer): استخراج سال، شماره ماه، نام ماه، روز،
    و نام روز از تب Add Column در برابر تفکیک با دلیمیتر در تاریخ‌های شمسی
  • ابزار تولید ستون بر مبنای الگو (Column From Examples) معادل فلش‌فیل (Flash Fill) پویا و نحوه الگودهی با چند نمونه در صورت عدم تشخیص هوش نرم‌افزار
  • محاسبه سن پرسنل و مدت‌زمان سپری‌شده پروژه (Elapsed Time) با ابزار
    Date -> Age و تبدیل واحد پیوسته آن به سال و روز با دستور
    Duration -> Total Years / Days
  • مدیریت گزارش‌های مالی نامنظم و فیلتر کردن ردیف‌های سرجمع (Total) با فیلتر هوشمند
    null بدون خطر حذف رکوردهای جدید در زمان رفرش
  • فراخوانی داده از ورک‌بوک اکسل خارجی (From Excel Workbook – Daily Report.xlsx) و
    کالبدشکافی رفتار پاور کوئری با سلول‌های ادغام‌شده (Merged Cells)
  • پاک‌سازی گزارش احجام نیروی انسانی مستقیم و غیرمستقیم کارگاهی، ترکیب دسته‌های کارگری (ماهر، نیمه‌ماهر، ساده) و
    فعال‌سازی Total Row در جدول اکسل
  • بررسی خطای اشتراک‌گذاری (Sharing Violation)، قفل شدن فایل مبدا در زمان رفرش و
    آسیب‌شناسی نسخه‌های غیراستاندارد آفیس
  • اتصال زنده به وب‌سایت‌های اینترنتی (From Web) جهت استخراج جداول HTML، ادغام ستون‌های
    نام و نام خانوادگی، و بررسی محدودیت‌های داده‌های لحظه‌ای (کریپتوکارنسی)
  • استخراج مستقیم جداول از فایل‌های PDF (From PDF) و تمرین کلاسی پاک‌سازی ساختار شکست کار
    (WBS) از گانت‌چارت چاپی پریماورا
  • مبانی و معماری یکپارچه‌سازی چند فایل مشابه با تکنیک الحاق (Append Queries) در برابر
    اتصال افقی (Merge)
  • تنظیمات پیش‌فرض بارگذاری کوئری‌ها به عنوان Only Create Connection و تجمیع داده‌های سه‌ماهه
    (ژانویه، فوریه، مارس) در کوئری نهایی Final_Report

📋 خلاصهٔ اجرایی کلاس

در این جلسه، آموزش پاور کوئری وارد فاز پیشرفته، ساختاری و تعامل با منابع چندگانه درون‌سازمانی و برون‌سازمانی
گردید. نخست، چالش استخراج منحنی S پیشرفت پروژه‌ای از جدول توزیع منابع نرم‌افزار پریماورا P6 پیاده‌سازی شد. مدرس
نشان داد که برای تبدیل داده‌های روزانه به هفتگی، به دلیل ماهیت تجمعی بودجه، عملیات Group By باید
الزاماً روی شاخص بیشینه (Max) قرار گیرد نه جمع (Sum)، و جهت جلوگیری از استپ‌های زاید،
نوع داده‌ها مستقیماً در فرمول‌بار اِم ویرایش شود. سپس جعبه‌ابزار توابع تاریخی تشریح و اثبات گردید که با ابزار
Age و زیرمجموعه Duration می‌توان سن افراد یا روزهای سپری‌شده پروژه را به شکلی کاملاً
پویا و متصل به ساعت و تاریخ جاری سیستم استخراج نمود. در ادامه، نحوه مقابله با فرمت‌های شلختهٔ سازمانی (سلول‌های
مرج‌شده، لوگوها، ردیف‌های توتال مزاحم) از طریق بازخوانی فایل‌های اکسل خارجی و استخراج جداول از منابع وب و
فایل‌های PDF آموزش داده شد. در ثلث پایانی جلسه، معماری الحاق (Append Queries) تدریس شد؛
مدرس با تغییر تنظیمات پیش‌فرض به Only Create Connection، داده‌های سه‌ماهه را بدون اشغال فضای شیت به
حافظه منتقل کرده و پس از الحاق در یک کوئری نهایی، گزارش و نمودار تجمیعی پیوت‌تیبل را استخراج نمود.

📑 فهرست مطالب تفصیلی (کل جلسه ۲ – بخش‌های ۱ و ۲)

۱. مقدمه
و پیوستگی با جلسه اول

در جلسه اول، زیربنای کار با پاور کوئری شامل شناخت محیط ویرایشگر، خط‌لوله مراحل اعمال‌شده (Applied
Steps)، اصول عدم استفاده از Ctrl+Z، کیس‌سنسیتیو بودن و تفاوت تبدیل درجا (Transform) با تولید ستون نو (Add Column) پایه‌ریزی شد. اما در محیط‌های
واقعی پروژه‌ای، داده‌ها به ندرت در قالب یک جدول پاکیزه در شیت اکسل جاری آماده هستند؛ بلکه داده‌ها در قالب فایل‌های
خروجی پریماورا P6، فایل‌های گزارش روزانه ارسال‌شده توسط پیمانکاران، صفحات وب سازمانی و حتی گزارش‌های متنی اسکن‌شده
یا پی‌دی‌اف قرار دارند.

هدف جلسه دوم، آموزش روش‌های پیشرفتهٔ ورود داده از منابع بیرونی به اکسل، آماده‌سازی داده‌های زمانی جهت ترسیم
منحنی‌های پیشرفت پروژه‌ای (S-Curve)، مهندسی محاسبات سن و روزهای سپری‌شده با استفاده از
توابع Age و Duration، و در نهایت تسلط بر تکنیک بسیار مهم الحاق
کوئری‌ها (Append Queries) جهت تجمیع فایل‌های دوره‌ای بدون دخالت دست است [حدود ۰۰:۰۵ تا
۰۲:۴۰].

۲. سناریوی
تخصصی: تبدیل بودجه روزانه پریماورا P6 به منحنی S هفتگی

در شیت ۳-۳، جدولی به نام TBL_P6 حاوی خروجی تخصیص منابع (Resource Assignment)
از نرم‌افزار پریماورا پی‌سیکس قرار دارد. داده‌های بودجه به تفکیک روزها در راستای افقی چیده شده‌اند و هدف ما تبدیل
این داده‌ها به یک منحنی S پیشرفت هفتگی است [حدود ۰۲:۴۵].

آماده‌سازی جدول خام P6 و ترنسپوز عمودی تاریخ‌ها [حدود ۰۲:۵۰ تا ۰۶:۳۵]

برای ورود داده‌ها به پاور کوئری:

  1. محدوده سلولی با زدن کلید Ctrl + T به جدول اکسل تبدیل می‌شود. از آنجا که ردیف اول حاوی تاریخ‌های
    متوالی است و همان‌طور که در جلسه پیش تأکید شد نباید تاریخ در سرستون اکسل قرار گیرد، تیک گزینهٔ My table
    has headers را برمی‌داریم     تا سرستون‌های عمومی اکسل ایجاد شوند [حدود ۰۳:۱۵].
  2. نام جدول به TBL_P6 تغییر داده شده و با گزینهٔ Data -> From Table/Range وارد ویرایشگر
    پاور کوئری می‌شویم.
  3. حذف ستون‌های اضافی: ستون‌های ۲ تا ۸ که حاوی اطلاعات متادیتای پروژه هستند انتخاب و حذف می‌شوند
    (Remove Columns)؛ فقط ستون نام پروژه و ستون‌های تاریخ (از ستون ۹ به بعد) باقی می‌مانند
    [حدود ۰۵:۱۵ تا ۰۵:۴۵].
  4. چرخش داده‌ها (Transpose): از تب Transform دکمهٔ Transpose فشرده می‌شود تا سطر تاریخ‌ها و ردیف مقادیر مصرف بودجه متناظر با آن‌ها،
    تبدیل به دو ستون عمودی گردند [حدود ۰۶:۱۵].
  5. حذف سطر اول مازاد: پس از ترنسپوز، سطر اول جدول شامل عناوین متنی مازاد است. از تب
    Home -> Remove Rows -> Remove Top Rows عدد 1 وارد می‌شود تا ردیف اول پاک گردد [حدود
    ۰۷:۴۵ تا ۰۷:۵۵].
  6. تغییر نام دو ستون حاصل به: Data Date (ستون تاریخ) و Budget (ستون بودجه تجمعی) [حدود
    ۰۸:۰۰].

ترفند حرفه‌ای: اصلاح نوع داده در فرمول‌بار اِم بدون ایجاد استپ اضافی [حدود ۰۸:۳۵ تا
۱۰:۳۵]

⭐ ترفند سرعت و پاکیزگی پایپ‌لاین (ویرایش مستقیم کد M در نوار فرمول):
اگر در استپ تشخیص خودکار نوع داده (Changed Type)، ستون Data Date به فرمت تاریخ و
ساعت (Date/Time) شناسایی شده باشد، بیشتر کاربران روی آیکون کلیک کرده و گزینهٔ Date
را انتخاب می‌کنند که این کار یک استپ جدید در پنل Applied Steps می‌سازد.روش مهندسی استاد: کافی است در همان مرحلهٔ Changed Type به نوار فرمول‌بار (Formula Bar) نگاه کنید. در کد زبان اِم، عبارت {"Data Date", type datetime} نوشته
شده است. کافی است واژهٔ datetime را به date ویرایش کرده و تیک سبز رنگ سمت چپ فرمول‌بار
را بزنید. استپ موجود درجا اصلاح می‌شود و هیچ استپ اضافی تولید نمی‌گردد [حدود ۱۰:۱۰ تا ۱۰:۳۰].

یکسان‌سازی هفتگی (End of Week) و منطق ریاضی انتخاب تابع Max به جای Sum [حدود ۱۱:۰۳
تا ۱۶:۳۰]

هدف مدیریت، تهیه گزارش پیشرفت هفتگی است. مدرس دو گام اساسی را تشریح نمود:

  • گام اول – تبدیل تاریخ‌ها به پایان هفته: روی ستون Data Date کلیک راست کرده -> منوی
    Transform -> Week -> End of Week را انتخاب می‌کنیم [حدود ۱۱:۳۵]. این ابزار تمام روزهای پراکنده
    متعلق به یک هفته میلادی را به آخرین روز همان هفته تبدیل می‌کند تا یک کلید شناسایی واحد شکل گیرد.
⭐ نکته کلیدی کنکوری و تحلیلی کنترل پروژه (تفاوت داده تجمیعی با دوره‌ای):
چالش بزرگ کلاسی: در زمان گروه‌بندی هفتگی، چه عملیاتی (Operation) باید روی ستون بودجه اعمال
شود؟
  • اگر داده‌های بودجه از نوع دوره‌ای / بازه‌ای (Interval) بودند (یعنی
    میزان مصرف روزانه به تنهایی)، عملیات باید مجموع (Sum) انتخاب می‌شد.
  • اما چون داده‌های خروجی پریماورا از نوع تجمعی (Cumulative) هستند،
    بودجه روز جمعه شامل بودجه تمام روزهای ماقبل خود در آن هفته است! اگر شما دستور Sum را بزنید، اعداد
    تجمعی را دوباره با هم جمع می‌زند و مقدار بودجه به شکلی نجومی و کاملاً غلط چندبرابر می‌شود.
  • بنابراین، عملیات صحیح برای داده‌های تجمعی در پنجره Group By منحصراً مقدار بیشینه (Max) است؛ زیرا ماکسیممِ مقادیر تجمعی یک هفته، دقیقاً بیانگر رقم پایانی بودجه در
    پایان آن هفته است [حدود ۱۲:۲۵ تا ۱۲:۵۵].

برای اجرای این فرآیند: کلیک راست روی ستون Data Date -> انتخاب Group By -> نام
ستون جدید: Weekly Budget -> نوع عملیات: Max -> ستون مبنا: Budget [حدود
۱۲:۵۸].

محاسبه ستون درصدی با تقسیم بر عدد ثابت و فرمت‌بندی ایمن در پیوت‌تیبل [حدود ۱۷:۴۰ تا
۲۱:۳۰]

با بررسی انتهای ستون بودجه مشخص گردید که کل بودجه پروژه عدد 1000 است. برای تبدیل مبالغ ریالی به
درصدهای پیشرفت فیزیکی بین ۰ تا ۱:

  1. انتخاب ستون Weekly Budget -> ورود به تب Transform -> Standard -> Divide [حدود ۲۰:۰۵].
  2. در کادر مقسوم‌علیه، عدد 1000 تایپ شده و OK می‌شود.
  3. اکنون اعداد بین ۰ تا ۱ هستند. استاد تأکید کرد که در پاور کوئری نباید فرمت ستون را روی Percentage بگذارید، زیرا
    در زمان لود به اکسل دوباره به عدد اعشاری (Decimal) بازمی‌گردد و سربار ایجاد می‌کند [حدود ۲۱:۱۰].

رفع باگ تجمیع ماهانه ناخواسته پیوت‌چارت با دستور Ungroup [حدود ۲۲:۰۰ تا ۲۴:۳۰]

کوئری از طریق Close & Load To... -> PivotChart در یک شیت جدید بارگذاری می‌شود. فیلد Data
Date در محور افقی (Axis) و فیلد Weekly Budget در بخش مقادیر (Values) قرار داده شده و نوع چارت به
نمودار خطی (Line Chart) تغییر می‌یابد [حدود ۲۲:۱۵].

📌 باگ اکسل در تاریخ‌های پیوت‌تیبل و راه‌حل آن:
اکسل به صورت خودکار تاریخ‌ها را در پیوت‌تیبل به «ماه» دسته‌بندی می‌کند (Auto-Grouping) و در
نتیجه به جای تاریخ‌های هفتگی، ماه‌های میلادی روی نمودار دیده می‌شوند! راه‌حل: روی یکی از تاریخ‌ها درون جدول
پیوت‌تیبل کلیک راست کرده و گزینهٔ آن‌گروپ (Ungroup) را می‌زنیم تا
خوشه‌بندی ماهانه اکسل بشکند و همان تاریخ‌های هفتگی خروجی پاور کوئری نمایان شوند [حدود ۲۲:۴۵].سپس برای درصدی کردن نمایش: در پنل PivotTable Fields روی فلش فیلد مقادیر کلیک کرده ->
Value Field Settings -> Number Format -> Percentage را انتخاب می‌کنیم تا صرفاً بازهٔ جدول درصدی شود و
منابع اکسل هدر نرود [حدود ۲۵:۲۰].

۳.
پردازش و تفکیک ساختار تاریخ در جدول مشتریان (TBL_Customer)

در شیت ۴، جدولی شامل محصولات، خریداران، تاریخ سفارش (Date Order) و قیمت به نام TBL_Customer
قرار دارد. ابتدا با کلید Ctrl + T جدول ساخته شده و وارد پاور کوئری می‌شود [حدود ۲۷:۰۵]. در استپ
Changed Type، بخش ساعت تاریخ با ویرایش کلمه type datetime به type date در
فرمول‌بار اِم حذف می‌گردد [حدود ۲۸:۰۰].

مقایسه تفکیک متنی دلیمیتر با ابزارهای تب Add Column -> Date [حدود ۲۸:۵۰ تا ۳۱:۰۰]

اگر داده‌های تاریخ ما شمسی باشند، به دلیل اینکه موتور پاور کوئری توابع گاه‌شماری خورشیدی را به
صورت ذاتی نمی‌شناسد، چاره‌ای جز استفاده از ابزار Split Column by Delimiter (با
جداکننده اسلش /) نداریم که تاریخ را به سه ستون عدد سال، ماه و روز تفکیک می‌کند.

اما اگر تاریخ از نوع میلادی استاندارد (Date) باشد، پاور کوئری ابزارهای
توکار بسیار هوشمندی در تب Add Column فراهم کرده است که محاسبات تقویمی را بدون قطعه‌قطعه کردن متن انجام
می‌دهند [حدود ۲۹:۴۰].

استخراج اجزای سال، ماه، نام ماه، روز و نام روز [حدود ۳۱:۰۰ تا ۳۳:۳۰]

با انتخاب ستون Date Order و ورود به تب Add Column، منوی کرکره‌ای Date امکانات زیر را ارائه می‌دهد:

  • استخراج سال: Date -> Year -> Year (تولید ستونی حاوی عدد سال، مثلاً ۲۰۲۲) [حدود
    ۳۱:۰۸].
  • استخراج شماره ماه: Date -> Month -> Month (تولید عدد ماه، مثلاً ۱ برای ژانویه، ۲
    برای فوریه).
  • استخراج نام کامل ماه: Date -> Month -> Name of Month (تولید رشته متنی لاتین،
    مثلاً January, February) [حدود ۳۲:۱۰].
  • استخراج شماره روز: Date -> Day -> Day (تولید عدد روز ماه از ۱ تا ۳۱ [حدود
    ۳۲:۴۵].
  • استخراج نام روز هفته: Date -> Day -> Name of Day (تولید نام متنی روز هفته، مثلاً
    Monday, Friday) [حدود ۳۳:۱۵]. این ابزار برای تحلیل‌های پروژه‌ای مانند بررسی روزهای دارای بیشترین تاخیر
    در تحویل مدارک یا روزهای وقوع حوادث کارگاهی کاربرد فراوان دارد.

۴.
استخراج الگو با ابزار Column From Examples (فلش‌فیل پویا)

در صورتی که ترکیبی از متن، تاریخ یا کدها مد نظر باشد و کاربر تمایلی به فرمول‌نویسی نداشته باشد، ابزار Column From Examples در تب Add Column معادل ابزار فلش‌فیل (Flash Fill) اکسل اما با ماهیت داینامیک است [حدود ۳۴:۲۰].

نحوه کارکرد:

  1. ستون مبنا (مثلاً تاریخ سفارش) را انتخاب کرده و از منوی Add Column -> Column From Examples گزینهٔ
    From Selection را می‌زنیم.
  2. یک ستون خالی در سمت راست جدول باز می‌شود. کافی است در ردیف اول، فرمت دلخواه خود را تایپ کنیم؛ مثلاً اگر تاریخ
    2022-01-15 است، تایپ کنیم: Jan-2022 [حدود ۳۵:۰۰].
  3. با زدن کلید اینتر، هوش مصنوعی پاور کوئری الگو را شناسایی کرده و پیش‌نویس خاکستری سایر ردیف‌ها را پر می‌کند و
    تابع متناظر در زبان اِم (مثلاً Date.ToText([Date Order], "MMM-yyyy")) را در بالای جدول نشان می‌دهد.
⭐ ترفند کلاسی در زمان عدم تشخیص هوش نرم‌افزار:
اگر ساختار داده‌ها پیچیده بود و نرم‌افزار با یک نمونه الگو را اشتباه تشخیص داد، دکمه OK را نزنید! کافی است به سطر
دوم یا سوم بروید و یک نمونهٔ دیگر را نیز به صورت دستی تایپ کنید. با دو یا سه نمونه (Sample
Training)، ضریب خطای هوش نرم‌افزار به صفر می‌رسد [حدود ۳۶:۱۰ تا ۳۶:۴۰].

۵. محاسبه سن
پرسنل و روزهای سپری‌شده پروژه با ابزارهای Age و Duration

در سیستم‌های اداری و کنترل پروژه، محاسبه سن پرسنل بر مبنای تاریخ تولد یا محاسبه روزهای سپری‌شده از شروع پروژه
(Elapsed Time) چالش همیشگی است. در اکسل سنتی از فرمول‌های نامطمئن مانند
DATEDIF یا تفریق از تابع TODAY() استفاده می‌شود [حدود ۳۸:۱۰].

فرآیند استاندارد در پاور کوئری:

  1. انتخاب ستون تاریخ تولد پرسنل -> ورود به تب Add Column -> Date -> Age [حدود ۳۹:۰۰].
  2. ماهیت خروجی Age: این دستور ستونی با نوع داده مدت‌زمان (Duration)
    ایجاد می‌کند. فرمت این ستون به شکل روز.ساعت:دقیقه:ثانیه است (مثلاً 14250.08:30:00) که
    نشان‌دهنده فاصله زمانی دقیق میان آن تاریخ تا ثانیهٔ جاری فراخوانی است.
  3. تبدیل به سال یا روز کامل: روی ستون ساخته‌شده کلیک کرده و از تب
    Transform -> Duration گزینهٔ Total Years را انتخاب می‌کنیم تا طول زمان به سال
    اعشاری (مثلاً ۳۹.۰۴ سال) تبدیل شود [حدود ۴۰:۱۵].
  4. در نهایت، روی آیکون نوع داده کلیک کرده و آن را به عدد صحیح (Whole Number) تبدیل
    می‌کنیم تا اعشار اضافه حذف گردد. این فرآیند با هر بار رفرش، سن واقعی را نسبت به تاریخ روز جاری سیستم بازنشانی
    می‌کند [حدود ۴۱:۲۰].

۶.
پاک‌سازی گزارش‌های مالی نامنظم و فیلتر هوشمند ردیف‌های توتال

در شیت ۵، یک گزارش مالی پر از سلول‌های خالی، خطوط عنوان گزارش و ردیف‌های جمع (Subtotal /
Total) در انتهای دسته‌ها قرار دارد [حدود ۴۳:۰۰]. اگر این داده‌ها مستقیماً وارد پیوت‌تیبل شوند، ردیف‌های
توتال با ارقام اصلی جمع شده و گزارش‌های مالی دو برابر نشان داده می‌شوند.

⭐ تکنیک مهندسی فیلتر کردن ردیف‌های Total:
اشتباه رایج: باز کردن فیلتر ستون دسته‌بندی و برداشتن دستی تیک کلمهٔ Total. این روش خطرناک است زیرا
اگر در ماه آینده کلمه‌ای به نام Subtotal یا جمع نهایی اضافه شود، در فیلتر باقی
می‌ماند!
روش پایدار: ستون شماره سند یا تاریخ را بررسی کنید. ردیف‌های توتال و سطرهای خالی عنوان، معمولاً
فاقد کد تراکنش یا تاریخ هستند و در آن ستون‌ها مقدار null دارند. کافی است روی فلش فیلتر ستون تاریخ
کلیک کرده و گزینهٔ Remove Empty را انتخاب کنید یا تیک مقدار null را بردارید. کل
سطرهای جمع و خطوط خالی در سراسر جدول با یک استپ پاک‌سازی می‌شوند و در ماه‌های بعد نیز خطا نخواهند داد [حدود ۴۵:۳۰
تا ۴۷:۱۰].

۷.
فراخوانی و استانداردسازی گزارش روزانه نیروی انسانی از فایل اکسل بیرونی

در پروژه‌ها، گزارش روزانه کارگاه (Daily Report) در قالب فایل‌های اکسل مستقل توسط
پیمانکاران ارسال می‌شود. هدف ما فراخوانی فایل خارجی Daily Report.xlsx و پاک‌سازی جدول احجام نیروی
انسانی مستقیم و غیرمستقیم است [حدود ۵۰:۱۰].

مسیر فراخوانی: تب Data -> Get Data -> From File -> From Excel Workbook و انتخاب فایل از روی حافظه
رایانه [حدود ۵۱:۲۰].

  • پنجره راهبر (Navigator): ساختار فایل را نشان می‌دهد. اگر جدول رسمی اکسل تعریف شده باشد با
    آیکون جدول (آبی) و اگر شیت خام باشد با آیکون برگ نمایان است. شیت مورد نظر انتخاب و دکمهٔ Transform Data
    فشرده می‌شود.
  • رفتار پاور کوئری با سلول‌های Merge: پاور کوئری مفهوم مرج سلولی را متلاشی می‌کند. محتوای متن
    در سلول بالا-چپ باقی مانده و سایر سلول‌های ادغام‌شده تبدیل به مقادیر خالی (null) می‌شوند
    [حدود ۵۳:۱۰].

حذف ردیف‌های سربرگ و فیلتر کردن ردیف‌های جمع و مقادیر null [حدود ۵۵:۰۰ تا ۵۹:۰۰]

فایل دارای ۴ ردیف سربرگ شرکتی و تاریخ گزارش است. مراحل استانداردسازی:

  1. دستور Remove Rows -> Remove Top Rows و وارد کردن عدد 4 جهت پاک کردن هدرهای اضافه
    [حدود ۵۵:۳۵].
  2. دستور Use First Row as Headers تا ردیف عناوین واقعی (دیسیپلین، رده‌های کارگری و…) به عنوان
    سرستون رسمی قرار گیرند [حدود ۵۶:۱۰].
  3. ستون دسته‌بندی کارگری فیلتر شده و تیک گزینه‌های Total Direct، Total Indirect و
    null برداشته می‌شود تا ردیف‌های خالص پرسنلی باقی بمانند [حدود ۵۸:۱۵].

تجمیع دسته‌های کارگری مستقیم و لود جدول تمیز در شیت فعال [حدود ۵۹:۳۰ تا ۶۶:۰۰]

ستون‌های کارگر ساده (Unskilled)، نیمه‌ماهر (Semi-Skilled) و ماهر
(Skilled) با کلید Ctrl انتخاب شده و از تب
Add Column -> Statistics -> Sum ستون مجموع نفرات مستقیم تحت عنوان Total Labor تولید
می‌شود [حدود ۶۰:۱۵].

سپس با زدن Close & Load داده‌ها در فایل اصلی لود می‌شوند. در جدول اکسل، مدرس ویژگی ردیف سرجمع جدول
را فعال کرد: کلیک درون جدول -> تب Table Design -> Total Row تا در پایین جدول مجموع کل نیروی انسانی
حاضر در کارگاه با تابع ایمن SUBTOTAL محاسبه گردد [حدود ۶۴:۳۰].

آزمون رفرش داینامیک و عیب‌یابی خطای Sharing Violation [حدود ۶۷:۰۰ تا ۷۱:۳۰]

⚠️ خطای سیستمی اشتراک‌گذاری فایل (Sharing Violation):
مدرس فایل مبدا Daily Report.xlsx را در ویندوز باز کرد، تعداد کارگران را تغییر داد و بدون بستن فایل
مبدا، در فایل مقصد دکمهٔ Refresh را زد. عملیات متوقف شد و ارور قرمز زیر صادر گردید:«The process cannot access the file because it is being used by another process.»

ریشه و راه‌حل: وقتی یک فایل اکسل در سیستم باز است، سیستم‌عامل ویندوز یک قفل موقت روی آن
ایجاد می‌کند. موتور پاور کوئری برای خواندن بیت‌های داده نیازمند دسترسی خواندن انحصاری است. برای رفرش موفق، فایل
مبدا اکسل باید حتماً ذخیره و بسته (Close) شود [حدود ۶۸:۲۰ تا ۶۹:۱۰].

۸.
استخراج داده‌های زنده از بستر وب (From Web) و شیرپوینت

یکی از قابلیت‌های برجسته پاور کوئری، وب‌اسکرپینگ (Web Scraping) ساختاریافته بدون نیاز به
برنامه‌نویسی پایتون است [حدود ۷۴:۰۰].

  • مسیر اجرا: Data -> Get Data -> From Other Sources -> From Web.
  • در کادر بازشده آدرس اینترنتی (URL) صفحه حاوی جدول وارد می‌شود.
  • موتور تحلیل HTML: پاور کوئری کدهای صفحه را اسکن کرده و تمام تگ‌های <table>
    را به صورت جداول شماره‌گذاری‌شده (Table ۱, Table ۲) در Navigator فهرست می‌کند [حدود ۷۵:۴۰].
  • با انتخاب جدول و ورود به محیط Transform، می‌توان ستون‌های نام و نام خانوادگی را با دستور Merge
    Columns     ادغام کرد و داده‌های وب را در اکسل لود نمود [حدود ۷۸:۱۰].
💡 محدودیت مهم در استخراج داده‌های زنده رمزارز و بورس:
در پاسخ به سوال دانشجو پیرامون اتصال به قیمت لحظه‌ای بیت‌کوین یا طلا، استاد توضیح داد که پاور کوئری ساختار ایستا
(Static DOM) صفحات وب را می‌خواند. داده‌های لحظه‌ای که با فناوری جاوااسکریپت و وب‌سوکت مدام
رندر می‌شوند با وب‌کانکتور ساده اکسل خوانده نمی‌شوند و برای آن‌ها باید از وب‌سرویس‌های ارائه‌دهنده API
(فرمت JSON) استفاده کرد [حدود ۷۹:۳۰ تا ۸۱:۰۰].

۹. استخراج،
تبدیل و پاک‌سازی داده‌ها از فایل‌های PDF

در بسیاری از مناقصات و پروژه‌ها، گانت‌چارت یا صورت‌وضعیت مالی صرفاً به صورت فایل غیرقابل ویرایش PDF در اختیار
واحد کنترل پروژه قرار می‌گیرد [حدود ۸۳:۰۰].

  1. مسیر اجرا: Data -> Get Data -> From File -> From PDF.
  2. پاور کوئری سند پی‌دی‌اف را آنالیز کرده و دو ساختار ارائه می‌دهد: جداول شناسایی‌شده در هر صفحه (Table 001) یا کل داده‌های صفحه (Page 001) [حدود ۸۴:۲۰].
  3. تمرین کلاسی ساختار شکست کار (WBS): مدرس یک فایل پی‌دی‌اف خروجی چاپ پریماورا P6 را فراخوانی
    کرد. با حذف سطرهای متفرقه و جداسازی کدهای فعالیت از طریق Split Column، کل داده‌های محبوس در PDF در کمتر
    از دو دقیقه تبدیل به جدول ساختاریافته اکسل شدند [حدود ۸۵:۱۰ تا ۸۸:۳۰].

۱۰.
معماری یکپارچه‌سازی و الحاق کوئری‌ها (Append Queries)

در پروژه‌های سازمانی، داده‌ها معمولاً ماهانه، هفتگی یا بر اساس ایستگاه‌های کاری در شیت‌ها یا فایل‌های مجزا ثبت
می‌شوند. چالش اساسی، قرار دادن این داده‌ها زیر پای هم در یک جدول واحد است [حدود ۹۰:۰۰].

مفهوم الحاق ساختاری ردیف‌ها و مدیریت ستون‌های ناهمسان [حدود ۹۰:۵۰ تا ۹۴:۰۰]

📖 تعریف الحاق (Append):
عملیات الحاق (Append) به معنای متصل کردن رکوردهای دو یا چند جدول به صورت عمودی زیر همدیگر
است (معادل UNION ALL در زبان SQL). این عملیات با پیوند افقی (Merge / Join) که
ستون‌ها را بر اساس کلید مشترک کنار هم می‌گذارد، تفاوت ماهوی دارد.
⭐ قانون طلایی الحاق (تطابق کامل هدرها):
پاور کوئری ردیف‌ها را بر مبنای نام ستون (Column Header) زیر هم می‌چیند نه
بر مبنای ترتیب فیزیکی آن‌ها! بنابراین:
  • اگر در جدول اول ستونی به نام Cost باشد و در جدول دوم با حرف کوچک cost نوشته شده
    باشد، به دلیل حساسیت به حروف، پاور کوئری دو ستون مجزا در جدول نهایی می‌سازد که یکی برای ماه‌های اول پر و برای
    ماه دوم خالی است!
  • قبل از اجرای Append، املای تمام سرستون‌ها و حتی فواصل خالی آن‌ها باید یکسان‌سازی شوند [حدود ۹۳:۲۰ تا ۹۴:۱۰].

تغییر تنظیمات پیش‌فرض نرم‌افزار به بارگذاری اتصالی (Connection Only) [حدود
۹۵:۱۰ تا ۹۸:۳۰]

اگر بخواهیم ۱۲ فایل مربوط به ۱۲ ماه سال را وارد اکسل کنیم، به صورت پیش‌فرض اکسل ۱۲ شیت جدید باز می‌کند و فایل
فوق‌العاده سنگین و شلوغ می‌شود. استاد تغییر این رفتار سیستمی را آموزش داد:

  • مسیر تنظیمات: File -> Options and settings -> Query Options -> Current Workbook -> Data Load.
  • گزینهٔ پیش‌فرض را از حالت Default Load Settings خارج کرده و روی Specify custom default load
    settings     می‌گذاریم.
  • تیک Load to worksheet را برداشته و تیک Only Create Connection را فعال می‌کنیم [حدود
    ۹۶:۴۰ تا ۹۷:۲۰].
  • از این پس، هر جدولی که وارد پاور کوئری شود، بدون ایجاد شیت فیزیکی در اکسل، صرفاً به حافظهٔ موقت موتور کوئری
    متصل می‌گردد.

الحاق کوئری‌های ژانویه، فوریه و مارس و ساخت پیوت‌تیبل جامع Final_Report [حدود ۹۹:۰۰
تا ۱۰۷:۰۰]

سناریوی عملیاتی: سه جدول فروش ماهانه به نام‌های Jan، Feb و Mar وارد کوئری
می‌شوند:

  1. از تب Home -> Append Queries فلش کوچک کنار آن را زده و گزینهٔ Append
    Queries as New     را انتخاب می‌کنیم تا جداول اولیه دست‌نخورده باقی بمانند [حدود ۱۰۱:۱۵].
  2. در پنجره بازشده، رادیوباتن Three or more tables را انتخاب می‌کنیم.
  3. جداول Jan، Feb و Mar را از کادر چپ به کادر راست اضافه کرده و OK می‌کنیم [حدود
    ۱۰۲:۰۰].
  4. نام کوئری جدید به Final_Report تغییر داده می‌شود. تمام ردیف‌های سه‌ماهه با حفظ یکپارچگی زیر هم
    مستقر شده‌اند.
  5. از تب Home گزینهٔ Close & Load To... -> PivotTable Report انتخاب شده و گزارش تحلیلی
    سه‌ماهه همراه با اسلایسر در یک شیت پاکیزه پیاده‌سازی می‌گردد [حدود ۱۰۵:۰۰ تا ۱۰۷:۰۰].

۱۱.
واژه‌نامهٔ جامع و تخصصی جلسه دوم

اصطلاح (فارسی و لاتین) تعریف طبق محتوای کلاس توضیح و زمینهٔ استفاده تأکید امتحانی؟
الحاق کوئری‌ها (Append
Queries)		 عملیات تجمیع عمودی و قرار دادن سطرهای چند جدول هم‌ساختار
زیر یکدیگر.		 معادل UNION ALL؛ تجمیع فایل‌های ماهانه یا گزارش‌های
دوره‌ای.		 بله
الحاق به عنوان کوئری جدید (Append
Queries as New)		 دستوری که خروجی الحاق را در یک کوئری مستقل جدید می‌سازد و
جداول پایه را دست‌نخورده نگه می‌دارد.		 روش استاندارد جهت حفظ ساختار ماژولار پایپ‌لاین. بله
مدت‌زمان (Duration) نوع داده زمانی در پاور کوئری که طول فاصله بین دو زمان را با
مقیاس روز، ساعت، دقیقه و ثانیه نگه می‌دارد.		 خروجی دستور Age؛ تبدیل به سال کامل با دستور Total Years. بله
ستون بر مبنای الگو (Column From
Examples)		 ابزار تولید خودکار ستون با کدنویسی M هوشمند با تایپ نمونه
خروجی توسط کاربر.		 معادل پویا و داینامیک Flash Fill در محیط اکسل سنتی. بله
پایان هفته (End of
Week)		 تبدیل روزهای مختلف یک هفته به تاریخ آخرین روز همان هفته. کلید گروه‌بندی جهت تجمیع هفتگی بودجه‌های روزانه P6. بله
نال / تهی (null) نشان‌دهنده فقدان کامل مقدار در دیتابیس؛ حاصل شکستن سلول‌های
ادغام‌شده اکسل.		 معیار فیلتر کردن ردیف‌های خالی و ردیف‌های جمع گزارش‌های
مالی.		 بله
آن‌گروپ (Ungroup) دستوری در پیوت‌تیبل اکسل برای شکستن دسته‌بندی خودکار ماهانه
و نمایش تاریخ‌های خام.		 جهت نمایش صحیح تاریخ‌های هفتگی در نمودار S پیشرفت. بله
تفکیک با دلیمیتر (Split by
Delimiter)		 شکستن رشته متنی بر مبنای کاراکتر جداکننده مانند اسلش یا
خط‌تیره.		 تنها راهکار تفکیک اجزای تاریخ‌های شمسی در پاور کوئری. خیر
منوی تاریخ (Date
Menu)		 مجموعه ابزارهای استخراج بخش‌های تقویمی از داده‌های
استاندارد Date.		 استخراج مستقیم Year ،Month ،Name of Month و Name of Day. بله

۱۲.
جدول ماتریس سناریوها و مطالعات موردی

عنوان سناریو شرح رویداد و مسئله کلاسی راه‌حل و نتیجه‌گیری استاد لینک
استخراج منحنی S از TBL_P6 تبدیل بودجه تجمعی روزانه افقی به پیشرفت درصدی هفتگی برای
رسم چارت S.		 ترنسپوز، استفاده از Max به جای Sum در Group By هفتگی، تقسیم
بر ۱۰۰۰ و Ungroup در پیوت.		 بخش ۲
محاسبه سن پرسنل با تابع Age محاسبه سن دقیق و پویا بر مبنای ستون تاریخ تولد پرسنل بدون
فرمول‌های متزلزل سنتی.		 اعمال Age، تبدیل واحد با
Duration -> Total Years و گرد کردن به Whole Number.		 بخش ۵
فیلتر ردیف‌های جمع گزارش مالی وجود ردیف‌های Total که باعث دوبرابر شدن ارقام در تحلیل‌های
بعدی می‌شد.		 فیلتر کردن مقادیر null روی ستون تاریخ یا شناسه جهت حذف
مطمئن تمام ردیف‌های جمع.		 بخش ۶
خطای Sharing Violation در فایل خارجی بروز ارور قرمز به دلیل باز بودن فایل منبع اکسل Daily Report
حین رفرش در فایل مقصد.		 بستن و آزادسازی فایل مبدا در ویندوز جهت امکان دسترسی موتور
کوئری به سکتورهای داده.		 بخش ۷
الحاق فایل‌های سه‌ماهه (Jan, Feb, Mar) تجمیع داده‌های فروش سه ماهه مجزا بدون ایجاد شیت‌های اضافه و
متفرقه در فایل.		 تنظیم بارگذاری اتصالی (Connection Only) و استفاده از Append
Queries as New.		 بخش ۱۰

۱۳.
جدول شناسه‌ها، آمار، تنظیمات نرم‌افزاری و مسیرها

مورد / عبارت مشخصه یا مقدار دسته‌بندی فنی توضیح کاربرد در کلاس
بودجه کل پروژه عدد ۱۰۰۰ مقسوم‌علیه ریاضی مبنای تقسیم مبالغ بودجه در ابزار Divide جهت تبدیل به درصد
پیشرفت فیزیکی.
Daily Report.xlsx نام فایل اکسل بیرونی منبع خارجی داده گزارش روزانه کارگاهی جهت آموزش پیمایش، حذف سربرگ و پاک‌سازی
داده نیروی انسانی.
Final_Report نام کوئری تجمیعی شیء پاور کوئری کوئری حاصل از دستور الحاق ۳ ماهه (Jan, Feb, Mar) که به
پیوت‌تیبل متصل شد.
تنظیم Only Create Connection تنظیم Global / Current Workbook پیکربندی سیستم جلوگیری از ساخت شیت‌های ناخواسته هنگام ورود جداول پایه برای
عملیات Append.
Three or more tables گزینه پنجره Append گزینه عملیاتی امکان انتخاب هم‌زمان نامحدود جدول جهت الحاق یکپارچه در یک
گام.

۱۴.
کپسول نکات کلیدی امتحانی و استخدامی

  1. منطق انتخاب Max در داده‌های تجمعی: در عملیات گروه‌بندی
    زمانی (مانند هفتگی یا ماهانه)، اگر داده‌ها ماهیت Cumulative داشته باشند، نباید از تابع Sum
    استفاده شود؛ زیرا ارقام ماقبل را تکراری جمع می‌زند. تابع انحصاری صحیح Max است.
  2. اصلاح تایپ در کد زبان M: برای تغییر تاریخ و ساعت به تاریخ
    خالص، به جای ایجاد یک استپ جدید، کلمه datetime را در فرمول‌بار استپ Changed Type به
    date ویرایش می‌کنیم.
  3. دستور Ungroup در پیوت‌تیبل: پیوت‌چارت به طور خودکار
    تاریخ‌ها را به ماه خوشه‌بندی می‌کند. برای دیدن تاریخ‌های هفتگی دقیق، کلیک راست روی تاریخ پیوت و زدن گزینهٔ
    Ungroup الزامی است.
  4. تفاوت بنیادین Append و Merge: ابزار Append ردیف‌ها را به
    صورت عمودی زیر هم اضافه می‌کند (سطر به سطر) و نیازمند یکسانی نام سرستون‌هاست؛ در حالی که Merge ستون‌ها را به
    صورت افقی بر اساس یک ستون کلید مشترک کنار هم قرار می‌دهد.
  5. استراتژی فیلتر پایدار برای Total: به جای جستجو و
    حذف متن Total، فیلتر کردن ردیف‌های null در ستون شناسه تراکنش یا تاریخ، پایدارترین شیوه حذف سرجمع‌ها
    و سطرهای خالی است.
  6. علت ارور Sharing Violation: باز بودن فایل اکسل
    مبدا در نرم‌افزار اکسل هنگام اجرای دستور Refresh در فایل مقصد.

۱۵.
جزئیات تکمیلی، توصیه‌های نرم‌افزاری و حاشیه‌های استاد

💡 توصیه‌های مدیریتی در طراحی جداول پروژه‌ای:استاد متذکر شد که در گزارش‌های کنترل پروژه هرگز نباید داده‌های تجمیعی و دوره‌ای در یک شیت بدون برچسب مخلوط
شوند. همچنین استفاده از سلول‌های ادغام‌شده (Merge) در فایل‌های خامی که قرار است وارد سیستم‌های هوش تجاری شوند
اکیداً ممنوع است؛ زیرا هزینه‌های پاک‌سازی داده را به شدت افزایش می‌دهد [حدود ۵۳:۴۰].

۱۶. میز
پرسش و پاسخ‌های فنی دانشجویان

سؤال دانشجو: اگر در عملیات Append یکی از جداول ستونی اضافه داشته باشد چه اتفاقی می‌افتد؟ [حدود
۹۲:۱۵]

پاسخ استاد: کوئری با خطا متوقف نمی‌شود؛ بلکه آن ستون اضافه در جدول نهایی ایجاد شده و برای
ردیف‌های متعلق به جداولی که آن ستون را نداشتند، مقدار null ثبت می‌گردد.

سؤال دانشجو: آیا می‌توانیم وب‌اسکرپینگ را طوری تنظیم کنیم که بدون باز کردن فایل هر ۱۰ دقیقه
رفرش شود؟ [حدود ۸۰:۱۰]

پاسخ استاد: در اکسل دسکتاپ فقط زمانی که فایل باز باشد می‌توان در تنظیمات کانکشن (Connection
Properties) رفرش دوره‌ای مثلاً هر ۱۵ دقیقه تعریف کرد؛ اما رفرش مداوم پس‌زمینه بدون باز بودن سیستم متعلق به سرویس
ابری Power BI Service و Gateway است.

۱۷.
تحلیل عمیق و منطق جریان داده (نمودار فلوچارت Mermaid)

فلوچارت زیر مسیر کامل پردازش ماتریس پریماورا به منحنی S و پایپ‌لاین چندمنبعی الحاق (Append Pipeline) تدریس‌شده در
این جلسه را به تصویر می‌کشد:

        flowchart RL
            subgraph S1["فرآیند استخراج منحنی S از پریماورا P6"]
                A["داده‌های روزانه افقی P6"] -->|"Ctrl+T بدون هدر"| B["جدول TBL_P6"]
                B -->|"حذف ستون‌های اضافی و ترنسپوز"| C["دو ستون عمودی تاریخ و بودجه"]
                C -->|"اصلاح کد M به type date"| D["حذف ساعت از تاریخ"]
                D -->|"ابزار End of Week"| E["تاریخ‌های یکپارچه هفتگی"]
                E -->|"Group By با تابع MAX"| F["بودجه تجمعی پایان هفته"]
                F -->|"Divide بر ۱۰۰۰ و لود در PivotChart"| G["منحنی S درصدی پیشرفت پروژه"]
            end
        
            subgraph S2["معماری الحاق فایل‌های دوره‌ای Append"]
                H["فایل‌های Jan / Feb / Mar"] -->|"تنظیم پیش‌فرض Connection Only"| I["اتصال‌های سه‌گانه در حافظه"]
                I -->|"دستور Append Queries as New"| J["کوئری واحد Final_Report"]
                J -->|"تطابق اسامی ستون‌ها و مدیریت null"| K["گزارش جامع پیوت‌تیبل و داشبورد"]
            end

۱۸. نتیجه‌گیری اصلی و پیام راهبردی جلسه دوم

فایل اول جلسه دوم اثبات کرد که تسلط بر پاور کوئری مرزهای اکسل را از یک صفحه گسترده ساده به یک ابزار جامع ادغام
داده (ETL Platform) ارتقا می‌دهد. مهندس داده با اتکا به تفکیک داده‌های تجمعی از دوره‌ای،
بهره‌گیری از توابع مدرن تقویم و سن، استخراج مستقیم از اسناد محبوس در وب و PDF و استقرار معماری سبک الحاق کوئری‌ها
(Append)، قادر است سیستم‌های گزارش‌گیری پروژه‌ای را به موتورهایی خودران، پیوسته و مصون از خطای انسانی تبدیل کند.

۱۹.
خودآزمون جامع سنجش یادگیری (تستی، کوتاه‌پاسخ و تشریحی)

۱. (تستی) چرا در تجمیع هفتگی بودجه خروجی نرم‌افزار پریماورا P6 در پاور کوئری باید از عملگر Max استفاده
کرد نه Sum؟

الف) چون عملگر Sum روی تاریخ‌ها کار نمی‌کند.
ب) چون داده‌های بودجه ماهیت تجمعی (Cumulative) دارند و Sum مبالغ روزهای قبل را مکرراً جمع می‌زند.
ج) چون تابع Max سرعت پردازش بالاتری در اکسل دارد.
د) فرقی ندارد و هر دو نتیجه یکسانی می‌دهند.

مشاهدهٔ پاسخ

پاسخ صحیح: گزینه (ب). در داده‌های تجمعی، عدد ثبت‌شده
در انتهای هفته بیشینه و در برگیرنده بودجه روزهای قبل همان هفته است؛ بنابراین تابع Max نشان‌دهنده بودجه واقعی
آن مقطع است.

۲. (تستی) برای پاک‌سازی بدون خطر ردیف‌های Total در یک گزارش مالی، کدام رویکرد مهندسی و پایدار
است؟

الف) انتخاب سلول‌های Total و زدن کلید Delete روی کیبورد.
ب) برداشتن دستی تیک کلمه Total در فیلتر ستون دسته‌بندی.
ج) فیلتر کردن و حذف مقادیر null در ستون‌های تراکنشی مانند تاریخ یا شماره سند.
د) حذف دستی ۱۰ ردیف آخر فایل.

مشاهدهٔ پاسخ

پاسخ صحیح: گزینه (ج). زیرا سطرهای سرجمع معمولاً فاقد
کد یا تاریخ هستند و فیلتر null مانع از بازگشت خطا در ماه‌های بعد با تغییر احتمالی عناوین می‌شود.

۳. (تستی) خروجی دستور Date -> Age در پاور کوئری دارای چه نوع داده‌ای (Data Type) است؟

الف) Whole Number (عدد صحیح سال)
ب) Duration (مدت‌زمان شامل روز، ساعت، دقیقه و ثانیه)
ج) Text (متن توضیحی)
د) Date (تاریخ میلادی)

مشاهدهٔ پاسخ

پاسخ صحیح: گزینه (ب). دستور Age داده‌ای از جنس
Duration تولید می‌کند که فاصله زمانی را تا لحظه حاضر نشان می‌دهد.

۴. (تستی) علت بروز خطای Sharing Violation حین Refresh کردن داده‌های فراخوانی‌شده از یک فایل اکسل
چیست؟

الف) پر شدن حافظه رم رایانه.
ب) باز بودن فایل اکسل مبدا در محیط سیستم‌عامل توسط کاربر.
ج) عدم تطابق فونت‌های دو فایل.
د) تغییر نام درایو ویندوز.

مشاهدهٔ پاسخ

پاسخ صحیح: گزینه (ب). باز بودن فایل سورس باعث قفل شدن
دسترسی انحصاری خواندن برای موتور پاور کوئری می‌گردد.

۵. (تستی) اگر نام یک ستون در جدول اول “Cost” و در جدول دوم “cost” باشد، پس از Append چه رخ
می‌دهد؟

الف) نرم‌افزار خطا داده و عملیات متوقف می‌شود.
ب) پاور کوئری آن‌ها را ادغام کرده و حروف را یکدست می‌کند.
ج) به دلیل Case Sensitive بودن، دو ستون جداگانه در جدول نهایی با مقادیر null متناظر ساخته می‌شود.
د) ستون جدول دوم حذف می‌گردد.

مشاهدهٔ پاسخ

پاسخ صحیح: گزینه (ج). پاور کوئری کاملاً به حروف کوچک
و بزرگ حساس است و ستون‌ها را بر اساس نام دقیق مطابقت می‌دهد.

۶. (کوتاه‌پاسخ) معادل پویا و داینامیک ابزار Flash Fill اکسل در ریبون پاور کوئری چیست؟

مشاهدهٔ پاسخ

پاسخ: ابزار Column From Examples در تب
Add Column.

۷. (کوتاه‌پاسخ) هنگام شکستن دسته‌بندی خودکار ماهانه در پیوت‌تیبل برای مشاهده تاریخ‌های هفتگی، چه
گزینه‌ای باید کلیک راست شود؟

مشاهدهٔ پاسخ

پاسخ: گزینهٔ Ungroup.

۸. (کوتاه‌پاسخ) پاور کوئری با سلول‌های ادغام‌شده (Merged Cells) در فایل‌های اکسل ورودی چگونه رفتار
می‌کند؟

مشاهدهٔ پاسخ

پاسخ: ادغام را لغو کرده، داده را در خانه بالا-چپ نگه
می‌دارد و مابقی سلول‌ها را با مقدار null پر می‌کند.

۹. (کوتاه‌پاسخ) کدام دستور در زبان SQL معادل عملکرد عملیات Append Queries در پاور کوئری است؟

مشاهدهٔ پاسخ

پاسخ: دستور UNION ALL.

۱۰. (تشریحی) چگونه می‌توان نوع داده یک ستون را از Date/Time به Date بدون افزودن یک استپ جدید در پنل
Applied Steps اصلاح کرد؟

مشاهدهٔ پاسخ

پاسخ: بر روی استپ Changed Type در پنل
مراحل کلیک کرده، به نوار فرمول‌بار اِم در بالای صفحه مراجعه می‌کنیم. در متن کد، عبارت
type datetime مربوط به آن ستون را به صورت دستی به type date تغییر داده و تیک تأیید
سمت چپ نوار فرمول را می‌زنیم تا استپ موجود درجا اصلاح شود.

۱۱. (تشریحی) تفاوت بنیادین میان استخراج اجزای تاریخ میلادی با تاریخ شمسی در پاور کوئری چیست؟

مشاهدهٔ پاسخ

پاسخ: تاریخ میلادی توسط موتور نرم‌افزار به عنوان جنس
تقویمی (Date) شناخته می‌شود و با منوی آماده Add Column -> Date می‌توان سال، ماه، نام ماه و روز را
استخراج کرد. اما تاریخ شمسی به عنوان متن ساده (Text) تلقی شده و توابع تقویمی روی آن کار نمی‌کنند؛ لذا برای
تفکیک آن باید از ابزار متنی Split Column by Delimiter با جداکننده اسلش (/) استفاده کرد.

۱۲. (تشریحی) چرا تغییر تنظیمات پیش‌فرض بارگذاری به “Only Create Connection” هنگام وارد کردن چندین جدول
ماهانه برای عملیات الحاق ضروری است؟

مشاهدهٔ پاسخ

پاسخ: زیرا در صورت باقی ماندن روی حالت پیش‌فرض، با
ورود هر جدول ماهانه (مثلاً ۱۲ ماه سال)، اکسل ۱۲ شیت مجزا باز کرده و جدول فیزیکی در آن رسم می‌کند که باعث
شلوغی، سنگینی بی‌مورد و اتلاف حافظه فایل می‌شود. با انتخاب Connection Only، جداول در حافظه موقت کوئری می‌مانند
و فقط جدول تجمیعی نهایی (Final Report) در قالب پیوت‌تیبل لود می‌گردد.

جلسه ۲-۱ از ۱۸ جلسه