Power QueryExcel

خطوات Power Query لدمج ملفاتك الشهرية وتنظيفها

التسليم من 1 يومالتعديلات مشمولةفحص الجودة
حماية الدفعالتفاصيل
  • التعديلات ضمن الباقة مجانية.
  • إذا لم تردّ خلال 3 أيام من التسليم، يُقبل العمل تلقائيًا.
اطلب · ‏25 US$

يُحجز المبلغ عند الدفع ولا يُحوَّل إلا بعد قبولك العمل.

خطوات Power Query لدمج ملفاتك الشهرية وتنظيفها

مثال على النتيجة

مثال على الباقة القياسية

قالب جدول بيانات: متتبّع فواتير لوكالة صغيرة

تخطيط الورقة — تبويب "Invoices"

العمودالعنوانيملؤه
Aرقم الفاتورةأنت
Bالعميلأنت (قائمة منسدلة من تبويب "Clients")
Cتاريخ الإصدارأنت
Dالمبلغأنت
Eمهلة السداد (أيام)معادلة
Fتاريخ الاستحقاقمعادلة
Gتاريخ الدفعأنت (اتركه فارغًا حتى الدفع)
Hالحالةمعادلة
Iأيام التأخيرمعادلة

المعادلات (مقتطف، 6 من 10)

E2 — مهلة السداد من قائمة العملاء =IFERROR(VLOOKUP(B2, Clients!A:C, 3, FALSE), 30) تبحث عن مهلة العميل في تبويب "Clients"، وإن لم يكن العميل موجودًا تستخدم 30 يومًا.

F2 — تاريخ الاستحقاق =IF(C2="", "", C2 + E2) تضيف المهلة إلى تاريخ الإصدار، وتبقى الصفوف الفارغة فارغة.

H2 — الحالة =IF(C2="", "", IF(G2<>"", "مدفوعة", IF(TODAY()>F2, "متأخرة", "مفتوحة"))) "مدفوعة" إذا أُدخل تاريخ الدفع؛ وإلا "متأخرة" بعد تاريخ الاستحقاق، وإلا "مفتوحة".

I2 — أيام التأخير =IF(H2="متأخرة", TODAY() - F2, "")

Dashboard!B2 — إجمالي المستحقات =SUMIFS(Invoices!D:D, Invoices!H:H, "<>مدفوعة", Invoices!C:C, "<>")

Dashboard!A5 — الرصيد حسب العميل (معادلة واحدة تملأ الجدول) =QUERY(Invoices!B:H, "select B, sum(D) where H <> 'مدفوعة' and B is not null group by B order by sum(D) desc label sum(D) 'المستحق'", 1)

أين تلصقها

  • ضع E2 وF2 وH2 وI2 في الصف 2 من "Invoices"، ثم اسحبها حتى الصف 500
  • الصق معادلتَي Dashboard مرة واحدة؛ فهما تتحدّثان من تلقاء نفسيهما
  • تنسيق شرطي على H2:H500: إذا كان النص "متأخرة" ← تعبئة حمراء فاتحة

حالات خاصة تمت معالجتها

  • فاتورة مدفوعة جزئيًا: أضف صفًّا بالمبلغ المتبقي ورقمًا جديدًا مثل INV-041b
  • تغيّر اسم العميل: غيّره في "Clients" فقط؛ فالقائمة المنسدلة تحافظ على اتساق الصفوف
  • Excel بدلًا من Sheets: كل المعادلات تعمل عدا QUERY؛ استخدم جدولًا محوريًا على الأعمدة B وD وH

مثال للتحقق

الفاتورة INV-038، العميل عيادة الابتسامة لطب الأسنان، صدرت في 1 سبتمبر، مهلة 14 يومًا ← تستحق في 15 سبتمبر. اليوم 20 أكتوبر ولم تُدفع ← متأخرة، 35 يومًا.

العميل: استوديو إنتاج فيديو من 4 أشخاص. الأداة: Google Sheets. المهمة: "نفقد تتبّع من دفع ومن لم يدفع. نحتاج إلى ورقة واحدة تُظهر الفواتير المتأخرة وكم يدين كل عميل."

العميل وجميع التفاصيل افتراضية.

عن هذه الخدمة

إذا كنت تنسخ كل شهر عدة ملفات مُصدَّرة إلى ورقة واحدة، وتحذف صفوف العناوين، وتصلح التواريخ، وتزيل الفراغات، فإن Power Query يمكنه فعل ذلك عند التحديث. أرفق ملفًا أو ملفين نموذجيين من دفعتك الشهرية، ونكتب خطوات Power Query وشيفرة M لدمج الملفات من مجلد، وتنظيف الأعمدة، وتحديد أنواع البيانات، وإلحاقها أو دمجها في جدول واحد. وفي الشهر التالي تضع الملف الجديد في المجلد وتضغط Refresh.

ما المشمول

  • ما الذي تحصل عليهخطوات Power Query بالترتيب مع شيفرة M لكل استعلام، جاهزة للصق في Advanced Editor، وتعليمات لإعداد المجلد والتحديث. يأتي كل شيء كنص في صفحة الطلب، ولا نرسل ملف جدول بيانات جاهزًا.
  • الدمج من مجلدجميع الملفات الشهرية في استعلام واحد
  • خطوات التنظيفالعناوين والفراغات والتشذيب والتقسيم
  • أنواع البياناتالتواريخ والأرقام محددة بشكل صحيح
  • شيفرة Mجاهزة لـ Advanced Editor
  • الدمج والإلحاقبدءًا من القياسية: ربط عدة مصادر

كيف يعمل

  1. 1
    الرفعترفق ملفات نموذجية
  2. 2
    الاستعلاماتنكتب الخطوات وشيفرة M
  3. 3
    الإصلاحاتإن تعطل شيء على بياناتك، نصلحه ضمن باقتك

ما نحتاجه منك

ملف أو ملفان شهريان نموذجيان كمرفقات Excel (أو لقطات شاشة)، والشكل الذي يجب أن يبدو عليه الجدول النهائي، وإصدار Excel لديك.

سنسألك:

  • من أين تأتي الملفات الشهرية؟
  • كم تخطيطًا مختلفًا للملفات لديك؟
  • ماذا يجب أن يحتوي الجدول النهائي؟
  • أي إصدار من Excel تستخدم؟

قارن بين الباقات

الأساسيةاستعلام واحد‏12 US$القياسيةمسار شهري‏25 US$المميزةتحضير تقرير كامل‏49 US$
مدة التنفيذ1 يوم2 يوم3 أيام
التعديلات123
الاستعلامات1حتى 3حتى 6
شيفرة Mنعمنعمنعم
دمج المصادرلانعمنعم
اطلباطلباطلب

حماية الدفع

تدفع عند الطلب، لكن المبلغ لا يُحوَّل إلا مقابل عمل قبلته.

  • سعر ثابتلا يتغير سعر الباقة بعد الدفع.
  • المبلغ محجوزعند الدفع، يُحجز المبلغ فقط. ولا يُحوَّل إلى Dleex إلا بعد قبولك العمل.
  • فحص قبل التسليمتخضع كل نتيجة لفحص الجودة قبل أن تصل إليك، وتراها في صفحة الطلب قبل قبولها.
  • تعديلات مجانيةاطلب تعديلات ضمن متطلبات طلبك بالعدد الذي تسمح به الباقة.
  • القبول بعد 3 أيامإذا لم تقبل العمل ولم تطلب تعديلات خلال 3 أيام من التسليم، يُقبل تلقائيًا. وإذا كتبت في محادثة الطلب بعد التسليم، يبقى الطلب قيد الانتظار؛ وبعد ردّنا، قد نعيد بدء مهلة 3 أيام وسنُعلمك بذلك في المحادثة.
  • إعادة التنفيذ أو استرداد المبلغ كاملًاإذا لم تطابق النتيجة متطلبات طلبك ولم تُصلحها التعديلات، أو استغرق العمل أكثر من ضعف مدة التنفيذ، فاكتب في محادثة الطلب: سنعيد تنفيذ العمل أو نردّ إليك المبلغ كاملًا.
كيف يعمل →

الأسئلة والأجوبة

هل يعمل هذا في Google Sheets؟

لا، Power Query جزء من Excel لنظام Windows، ومع بعض القيود من Excel لنظام Mac. لـ Google Sheets، اختر عرض Apps Script لدينا.

ماذا لو تغيّر الملف المُصدَّر في الشهر التالي؟

الأعمدة التي يُعاد تسميتها أو نقلها تُعطّل الخطوات. نشير إلى الخطوات التي تعتمد على أسماء الأعمدة لتعرف أين تنظر.

هل يمكن أن تكون الملفات بصيغة CSV بدلًا من Excel؟

نعم، يقرأ Power Query ملفات CSV أيضًا. الصق بضعة أسطر من ملف CSV في متطلبات الطلب لنرى الفاصل والترميز.

اطلب · ‏25 US$· 2 يوم