أخرى
#PowerQuery
#Excel
5 طرق يتفوق بها Power Query على أدوات Excel التقليدية
يمكن لأدوات إكسل القياسية عادةً إنجاز المهمة، لكنها غالبًا ما تفتقر إلى المرونة والقدرة على التحمل المطلوبة للبيانات المعقدة ومهام التحويل. لا يقوم Power Query بتنفيذ هذه المهام بشكل أسرع فحسب، بل يقوم بها بشكل أفضل. إليك خمس خطوات حيث يجب عليك التخلي عن أدوات شريط إكسل لصالح محرر Power Query.
قبل أن تبدأ: تحميل بياناتك
حدد أي خلية ضمن مجموعة بياناتك، وفي علامة التبويب البيانات، انقر على "من جدول/نطاق".
بمجرد الانتهاء من تنفيذ خطوات Power Query الخاصة بك، انقر على "إغلاق وتحميل" في علامة التبويب الرئيسية لإرجاع البيانات إلى ورقة عمل جديدة.
استخدم الأعمدة الشرطية (بدلاً من جمل IF المتداخلة)
تعتمد المنطق القياسي في إكسل غالبًا على سلاسل نصية طويلة ويدوية يصعب بناؤها وأصعب في الإصلاح.
السيناريو
تحتاج إلى تصنيف المبيعات إلى "صغيرة" و"متوسطة" و"كبيرة" بناءً على القيمة.
عادةً، ستضيف عمودًا جديدًا وتستخدم صيغة IF متداخلة:
=IF([@Sales]>1000,"كبيرة",IF([@Sales]>500,"متوسطة","صغيرة"))
ومع ذلك، يمكن أن يكسر فاصلة واحدة في غير محلها السلسلة بأكملها، وتدقيق المنطق في شريط صيغة طويل يعد صداعًا.
كيفية استخدام الأعمدة الشرطية
بعد تحميل بياناتك إلى محرر Power Query، في علامة التبويب إضافة عمود، انقر على "عمود شرطي".
في مربع الحوار، قم بتسمية عمودك الجديد، وحدد منطقك باستخدام قوائم السحب. في هذه الحالة، القاعدة الأولى هي إرجاع كلمة "كبيرة" إذا كانت قيمة في عمود المبيعات أكبر من 1,000.
ثم، انقر على "إضافة شرط" وكرر العملية للحالة المتوسطة.
أخيرًا، أدخل قيمة Else كـ صغيرة وانقر على "موافق".
بعد ذلك، ستظهر العمود الشرطي الجديد، وأنت جاهز لإغلاق وتحميل الجدول الجديد إلى ورقة العمل الخاصة بك.
لماذا الأعمدة الشرطية أفضل من جمل IF المتداخلة
تخلق هذه الطريقة خطوة مرئية يمكن لأي شخص تدقيقها بنظرة سريعة. لا تحتاج إلى كتابة أي كود، وإذا تغيرت عتباتك، يمكنك ببساطة النقر على أيقونة الترس في لوحة الخطوات المطبقة لتحديث القواعد دون لمس أي قوس.
دمج الاستعلامات (بدلاً من استخدام البحث)
ربط مجموعتين بيانات مختلفتين هو مهمة أساسية في Excel، لكن الاعتماد على الصيغ المعتمدة على الخلايا غالباً ما يؤدي إلى مراجع مكسورة وأداء بطيء.
السيناريو
لديك جدول مبيعات وقائمة منتجات رئيسية تقع في أوراق عمل منفصلة، وتحتاج إلى سحب سعر الوحدة من القائمة الرئيسية إلى جدول المبيعات الخاص بك.
بينما يمكن لـ XLOOKUP القيام بذلك، يصبح عبئاً إذا تم نقل ملف المصدر، أو تم حذف رؤوس الأعمدة، أو إذا نما مجموعة البيانات بشكل كبير.
كيفية دمج الاستعلامات
الخطوة الأولى هي تخزين الجدول الأول في ذاكرة Power Query. للقيام بذلك، قم بتحميل الجدول إلى محرر Power Query، وتحت سهم "إغلاق وتحميل"، انقر على "إغلاق وتحميل إلى".
ثم، في الحوار، اختر "إنشاء اتصال فقط"، وانقر على "موافق".
في هذه النقطة، تفتح لوحة "الاستعلامات والاتصالات" مع الاتصال الذي أنشأته للتو.
الآن، كرر هذه العملية للجدول الآخر بحيث تكون كلا الاتصالين مرئيين في تلك اللوحة.
انقر بزر الماوس الأيمن على جدول المبيعات في هذه اللوحة، وانقر على "تحرير" للعودة إلى المحرر.
الآن، انقر على "دمج الاستعلامات" في علامة التبويب الرئيسية، واختر جدول المنتجات الرئيسية من القائمة المنسدلة، وحدد عمود SKU المطابق في كلا المعاينات، ثم انقر على "موافق".
بعد ذلك، انقر على أيقونة "توسيع" في رأس العمود الجديد، وحدد العمود الذي ترغب في نقله من جدول إلى آخر (في هذه الحالة، "السعر")، ثم انقر على "موافق".
عندما تنقر على "إغلاق وتحميل"، ستظل استعلامات المبيعات مخزنة كاتصال فقط في لوحة الاستعلامات والاتصالات. لذا، انقر بزر الفأرة الأيمن عليها، واختر "تحميل إلى"، ثم حدد "جدول" واختر "ورقة عمل جديدة".
عند النقر على "موافق"، يتم تحميل الجدول المدمج إلى ورقة عمل جديدة.
لماذا دمج الاستعلامات أفضل
تعتبر دوال البحث حلولاً على مستوى الخلايا، لكن دمج الاستعلامات في Power Query هو حل على مستوى الجدول. إليك لماذا يعتبر ذلك مهمًا في دفاتر العمل المعقدة:
- غير مدمرة: يخلق دمج الاستعلامات نسخة جديدة محولة مع الحفاظ على جداول المصدر دون تغيير.
- يتعامل مع نتائج متعددة: إذا كان لديك 10 قطع بيانات مختلفة لسحبها من قائمة رئيسية، يمكنك دمجها مرة واحدة في Power Query ببساطة والتحقق من المربعات لجميع الأعمدة العشرة التي ترغب في توسيعها.
- يعمل بسلاسة أكبر: يعمل Power Query فقط عند النقر على "تحديث"، مما يجعله أسرع بكثير مع مجموعات البيانات التي تحتوي على آلاف الصفوف.
استخدم أداة "إلغاء التدوير" بدلاً من نقل البيانات يدويًا
تجعل أدوات Excel القياسية من الصعب تحويل البيانات العريضة إلى التنسيق الطويل المطلوب لجداول Pivot والتحليلات الحديثة.
السيناريو
تستلم تقريرًا يحتوي على فئات في العمود الأول، لكن الأشهر هي رؤوس منفصلة. لاستخدام هذه البيانات في جدول Pivot، تحتاج إلى دمج تلك الأشهر في عمود عمودي واحد.
في Excel العادي، سيتعين عليك نسخ ولصق وتحويل كل شهر يدويًا - وهي عملية مملة يجب عليك تكرارها في كل مرة تصل فيها بيانات جديدة.
كيفية استخدام أداة "إلغاء التدوير"
بعد تحميل جدولك العريض في محرر Power Query، حدد الأعمدة التي لا ترغب في عكسها (مع الضغط على Ctrl أثناء النقر على الرؤوس إذا كان هناك أكثر من واحد)، انقر بزر الفأرة الأيمن على أحد رؤوس تلك الأعمدة، واختر "إلغاء تدوير الأعمدة الأخرى".
يحول Power Query البيانات على الفور، مما ينشئ عمودًا للسمات وعمودًا للقيم.
انقر نقرًا مزدوجًا على الرؤوس لإعادة تسميتها إلى شيء مثل الشهر والإيرادات، وتأكد من تنسيقها بشكل صحيح من خلال النقر على أيقونات تنسيق الأرقام بجوار أسماء الأعمدة.
البيانات المحدثة جاهزة الآن ليتم إغلاقها وتحميلها مرة أخرى إلى دفتر العمل الخاص بك.
لماذا يعتبر إلغاء التدوير أفضل
تعتبر أداة "إلغاء التدوير" ديناميكية وغير مدمرة. في Excel القياسي، إذا أضفت عمودًا جديدًا، ستحتاج إلى إعادة هيكلة ورقة العمل يدويًا. ومع ذلك، يكتشف Power Query تلقائيًا أي رؤوس شهور جديدة تمت إضافتها إلى بيانات المصدر ويقوم بعكسها إلى قائمتك العمودية خلال التحديث التالي.
استخدم اتصالات "من مجلد" بدلاً من النسخ واللصق اليدوي
يعتبر النسخ اليدوي للبيانات من ملفات متعددة أحد أكثر الأسباب شيوعًا للأخطاء البشرية في جداول Excel.
السيناريو
كل يوم اثنين، عندما تتلقى أحدث تقرير مبيعات إقليمي، تفتح الملف، وتنسخ البيانات، وتلصقها في أسفل ورقة العمل الرئيسية. ومع ذلك، فإن هذه العملية بطيئة، ومن السهل أن تتجاوز صفًا أو تلصق بعض البيانات مرتين.
كيفية استيراد البيانات من مجلد
أولاً، تأكد من أن جميع ملفات المصدر لديك في مجلد واحد مخصص على جهاز الكمبيوتر الخاص بك. للحفاظ على العملية منظمة، تأكد من احتواء هذا المجلد على الملفات التي تنوي دمجها فقط.
ثم في Excel، انقر على بيانات > الحصول على بيانات > من ملف > من مجلد.
تصفح إلى المجلد المخصص لك، وانقر على "فتح".
قم بتوسيع قائمة "دمج" المنسدلة واختر "دمج وتحويل البيانات".
اختر ملف عينة أو ورقة لتستخدمها كقالب وانقر على "موافق". تأكد من أن جميع دفاتر العمل تستخدم نفس أسماء علامات الأوراق وعناوين الأعمدة، حيث إن ملف العينة يحدد الهيكل لكامل الدمج.
بمجرد أن تكون راضيًا عن النتيجة، انقر على "إغلاق وتحميل".
لماذا استيراد البيانات من مجلد أفضل
يعني دمج دفاتر العمل في Excel باستخدام Power Query أنك لن تضطر أبدًا لفتح ملف إقليمي مرة أخرى - بدلاً من ذلك، يمكنك ببساطة إسقاط التقارير الجديدة في المجلد والنقر على "تحديث" في ملف Excel الرئيسي الخاص بك.
استخدم أداة تقسيم العمود (بدلاً من طرق تقسيم الأعمدة القياسية)
هناك العديد من الطرق لتقسيم الأعمدة باستخدام واجهة Excel القياسية، ولكنها تأتي مع مشكلات قد تسبب صعوبات لاحقًا.
السيناريو
لديك عمود من الأسماء الكاملة التي تحتاج إلى تقسيمها إلى أسماء أولى وأسماء عائلية.
في شريط أدوات Excel القياسي، قد تستخدم معالج النص إلى أعمدة، لكن النتائج ثابتة، مما يعني أنه إذا أضفت أسماء جديدة أو قمت بتحديث اسم موجود، فلن يتم تحديث التقسيم. بدلاً من ذلك، يمكنك استخدام دالة TEXTSPLIT، لكن هذا يخلق أعمدة غير متسقة إذا كان الاسم يحتوي على حرف وسط.
كيفية تقسيم الأعمدة
بعد تحميل بياناتك في محرر Power Query، انقر بزر الماوس الأيمن على رأس العمود الذي تريد تقسيمه، وفي قائمة تقسيم العمود، انقر على "حسب الفاصل".
اختر "المسافة" كفاصل لك.
إذا كنت ترغب في التعامل مع الأسماء الوسطى بشكل متسق، اختر "الفاصل الأيمن" لضمان إنشاء عمودين فقط في كل مرة.
عند الانتهاء، انقر على "موافق"، ثم قم بإعادة تسمية الأعمدة، وبعد ذلك أغلق المحرر وقم بتحميل مجموعة البيانات الناتجة إلى ملف Excel الخاص بك.
ستظهر أي قيم فارغة كخلايا فارغة في جدول Excel النهائي. إذا كنت تفضل إصلاح ذلك داخل محرر Power Query، يمكنك النقر بزر الماوس الأيمن على رأس العمود واختيار "استبدال القيم" لتعبئتها تلقائيًا بصفر أو N/A.
لماذا يعتبر تقسيم الأعمدة في خطوات محرر Power Query أفضل
على عكس أداة الشريط، يتم تحديث تقسيم الأعمدة في Power Query كلما نقرت على "تحديث"، مما يعالج الصفوف الجديدة من البيانات على الفور. أيضًا، هو أكثر استقرارًا من دالة TEXTSPLIT، حيث ينشئ أعمدة جدول فعلية لا تعتمد على مساحة "تسرب" فارغة. أخيرًا، إذا احتجت يومًا لتغيير المنطق، يمكنك ببساطة النقر على أيقونة الترس في لوحة الخطوات المطبقة بدلاً من إعادة كتابة الصيغ أو إعادة تشغيل المعالج.
بمجرد أن تتقن هذه الإجراءات الخمس، يمكنك تبسيط عملياتك بشكل أكبر من خلال تعلم المزيد من أوامر Power Query الأساسية لتحويل البيانات للتعامل مع حتى أكثر مجموعات البيانات تعقيدًا بنقرة واحدة.
التعليقات 0
سجل دخولك لإضافة تعليق
لا توجد تعليقات بعد. كن أول من يعلق!