المحتويات
أحد مقاطع الفيديو الأكثر مشاهدة على قناتي على YouTube هو مقطع فيديو حول Flash Fill في Microsoft Excel. يكمن جوهر هذه الأداة في أنه إذا كنت بحاجة إلى تحويل بيانات المصدر بطريقة ما ، فأنت تحتاج فقط إلى البدء في كتابة النتيجة التي تريد الحصول عليها في العمود المجاور. بعد عدة خلايا مكتوبة يدويًا (عادةً ما تكون 2-3 كافية) ، سيقوم Excel "بفهم" منطق التحولات التي تحتاجها وسيتابع تلقائيًا ما كتبته ، ويكمل كل العمل الرتيب نيابةً عنك:
جوهر الكفاءة. الزر السحري "افعلها بشكل صحيح" الذي نحبه جميعًا كثيرًا ، أليس كذلك؟
في الواقع ، هناك نظير لمثل هذه الأداة في Power Query - هناك يطلق عليها عمود من الأمثلة (عمود من الأمثلة). في الواقع ، هذا ذكاء اصطناعي صغير مدمج في Power Query يمكنه التعلم بسرعة من بياناتك ثم تحويلها. دعونا نلقي نظرة فاحصة على قدراتها في العديد من السيناريوهات العملية لفهم أين يمكن أن تكون مفيدة لنا في المهام الحقيقية.
مثال 1. لصق / قص النص
لنفترض أن لدينا مثل هذا الجدول "الذكي" في Excel مع بيانات عن الموظفين:
قم بتحميله في Power Query بالطريقة القياسية - باستخدام الزر من الجدول / النطاق علامة التبويب البيانات (بيانات - من جدول / نطاق).
لنفترض أننا بحاجة إلى إضافة عمود بأسماء العائلة والأحرف الأولى لكل موظف (إيفانوف إس في للموظف الأول ، وما إلى ذلك). لحل هذه المشكلة ، يمكنك استخدام إحدى طريقتين:
- انقر بزر الماوس الأيمن فوق عنوان العمود مع بيانات المصدر وحدد الأمر أضف عمودًا من الأمثلة (إضافة عمود من الأمثلة);
- حدد عمودًا واحدًا أو أكثر يحتوي على البيانات وعلى علامة التبويب إضافة عمود اختر فريق عمود من الأمثلة. هنا ، في القائمة المنسدلة ، يمكنك تحديد ما إذا كان يلزم تحليل كل الأعمدة المحددة فقط أم لا.
ثم يصبح كل شيء بسيطًا - في العمود الذي يظهر على اليمين ، نبدأ في إدخال أمثلة للنتائج المرجوة ، ويحاول الذكاء الاصطناعي المدمج في Power Query فهم منطق التحول الخاص بنا والمتابعة بشكل أكبر من تلقاء نفسه:
بالمناسبة ، يمكنك إدخال الخيارات الصحيحة في أي خلايا من هذا العمود ، أي ليس بالضرورة من أعلى لأسفل وفي صف واحد. يمكنك أيضًا إضافة أعمدة أو إزالتها بسهولة من التحليل لاحقًا باستخدام مربعات الاختيار في شريط العنوان.
انتبه إلى الصيغة الموجودة أعلى النافذة - هذا ما ينشئه Smart Power Query للحصول على النتائج التي نحتاجها. هذا ، بالمناسبة ، هو الاختلاف الأساسي بين هذه الأداة و ملء فوري في Excel. يعمل الملء الفوري مثل "الصندوق الأسود" - فهي لا تظهر لنا منطق التحولات ، ولكنها ببساطة تعطي نتائج جاهزة ونحن نأخذها كأمر مسلم به. كل شيء هنا شفاف ويمكنك دائمًا فهم ما يحدث بالضبط مع البيانات بوضوح تام.
إذا رأيت أن Power Query "اكتشفت الفكرة" ، فيمكنك الضغط على الزر بأمان OK أو اختصار لوحة المفاتيح CTRL+أدخل - سيتم إنشاء عمود مخصص بصيغة اخترعها Power Query. بالمناسبة ، يمكن تحريره لاحقًا بسهولة كعمود عادي تم إنشاؤه يدويًا (باستخدام الأمر إضافة عمود - عمود مخصص) من خلال النقر على رمز الترس على يسار اسم الخطوة:
مثال 2: الحالة كما في الجمل
إذا قمت بالنقر بزر الماوس الأيمن فوق عنوان العمود مع النص وحدد الأمر تحول (تحول)، ثم يمكنك رؤية ثلاثة أوامر مسؤولة عن تغيير السجل:
مريح ورائع ، ولكن في هذه القائمة ، على سبيل المثال ، كنت أفتقر دائمًا إلى خيار آخر - الحالة كما في الجمل ، عندما لا تصبح الأحرف الكبيرة (الكبيرة) هي الحرف الأول في كل كلمة ، ولكن فقط الحرف الأول في الخلية ، و باقي النص عند عرض هذا بأحرف صغيرة (صغيرة).
هذه الميزة المفقودة سهلة التنفيذ باستخدام الذكاء الاصطناعي أعمدة من الأمثلة - فقط أدخل خيارين لـ Power Query للاستمرار بنفس الروح:
كصيغة هنا ، يستخدم Power Query مجموعة من الوظائف النص и نص، وتحويل النص إلى الأحرف الكبيرة والصغيرة ، على التوالي ، والوظائف نص. ابدأ и نص منتصف - نظائرها من وظائف Excel LEFT و PSTR قادرة على استخراج سلسلة فرعية من النص من اليسار ومن المنتصف.
مثال 3. تقليب الكلمات
في بعض الأحيان ، عند معالجة البيانات المستلمة ، يصبح من الضروري إعادة ترتيب الكلمات في الخلايا في تسلسل معين. بالطبع ، يمكنك تقسيم العمود إلى أعمدة كلمات منفصلة بواسطة الفاصل ثم لصقها مرة أخرى بالترتيب المحدد (لا تنس إضافة مسافات) ، ولكن بمساعدة الأداة عمود من الأمثلة كل شيء سيكون أسهل بكثير:
مثال 4: أرقام فقط
مهمة أخرى حيوية للغاية هي سحب الأرقام (الأرقام) فقط من محتويات الخلية. كما كان من قبل ، بعد تحميل البيانات في Power Query ، انتقل إلى علامة التبويب إضافة عمود - عمود من الأمثلة واملأ خليتين يدويًا حتى يفهم البرنامج ما نريد الحصول عليه بالضبط:
بنغو!
مرة أخرى ، يجدر النظر إلى الجزء العلوي من النافذة للتأكد من أن الاستعلام قد أنشأ الصيغة بشكل صحيح - في هذه الحالة يحتوي على دالة نص. يختار، والتي ، كما قد تتخيل ، تستخرج الأحرف المعينة من النص المصدر وفقًا للقائمة. بعد ذلك ، يمكن بالطبع تحرير هذه القائمة بسهولة في شريط الصيغة إذا لزم الأمر.
مثال 5: نص فقط
على غرار المثال السابق ، يمكنك الانسحاب والعكس صحيح - فقط النص وحذف جميع الأرقام وعلامات الترقيم وما إلى ذلك.
في هذه الحالة ، يتم استخدام دالة معاكسة بالفعل للمعنى - Text.Remove ، والتي تزيل الأحرف من السلسلة الأصلية وفقًا لقائمة معينة.
مثال 6: استخراج البيانات من عصيدة أبجدية رقمية
يمكن أن يساعد Power Query أيضًا في الحالات الأكثر صعوبة ، عندما تحتاج إلى استخراج معلومات مفيدة من العصيدة الأبجدية الرقمية في خلية ، على سبيل المثال ، احصل على رقم الحساب من وصف غرض الدفع في كشف حساب بنكي:
لاحظ أن صيغة التحويل المُنشأة في Power Query يمكن أن تكون معقدة للغاية:
لسهولة القراءة والفهم ، يمكن تحويلها إلى نموذج أكثر عقلانية باستخدام خدمة مجانية عبر الإنترنت. منسق استعلام القوة:
شيء مفيد جدا - احترام المبدعين!
مثال 7: تحويل التواريخ
أداة عمود من الأمثلة يمكن تطبيقه على أعمدة التاريخ أو التاريخ والوقت أيضًا. عند إدخال الأرقام الأولى من التاريخ ، سيعرض Power Query بشكل مفيد قائمة بجميع خيارات التحويل الممكنة:
لذلك يمكنك بسهولة تحويل التاريخ الأصلي إلى أي تنسيق غريب ، مثل "عام-شهر-يوم":
مثال 8: التصنيف
إذا استخدمنا الأداة عمود من الأمثلة إلى عمود يحتوي على بيانات رقمية ، فإنه يعمل بشكل مختلف. لنفترض أن لدينا نتائج اختبار الموظف تم تحميلها في Power Query (الدرجات الشرطية في النطاق 0-100) ونستخدم التدرج الشرطي التالي:
- الماجستير - أولئك الذين سجلوا أكثر من 90
- الخبراء - درجات من 70 إلى 90
- المستخدمون - من 30 إلى 70
- المبتدئين - أولئك الذين سجلوا أقل من 30
إذا أضفنا عمودًا من الأمثلة إلى القائمة وبدأنا في ترتيب هذه التدرجات اللونية يدويًا ، فحينئذٍ ستلتقط Power Query فكرتنا قريبًا وتضيف عمودًا مع صيغة ، حيث تتداخل العوامل مع بعضها البعض if سيتم تنفيذ المنطق ، مشابه جدًا لما نحتاجه:
مرة أخرى ، لا يمكنك الضغط على الوضع حتى النهاية ، ولكن انقر فوق OK ثم قم بتصحيح قيم العتبة الموجودة بالفعل في الصيغة - إنها أسرع بهذه الطريقة:
استنتاجات
بالتأكيد أداة عمود من الأمثلة ليست "حبة سحرية" ، وعاجلاً أم آجلاً ، ستكون هناك مواقف غير قياسية أو حالات مهملة بشكل خاص لـ "المزرعة الجماعية" في البيانات ، عندما يفشل Power Query ولن يكون قادرًا على تحديد ما نريد بشكل صحيح بالنسبة لنا. ومع ذلك ، كأداة مساعدة ، فهي جيدة جدًا. بالإضافة إلى ذلك ، من خلال دراسة الصيغ التي أنشأها ، يمكنك توسيع معرفتك بوظائف لغة M ، والتي ستكون دائمًا مفيدة في المستقبل.
- تحليل النص باستخدام التعبيرات العادية (RegExp) في Power Query
- البحث عن نص غامض في Power Query
- ملء فلاش في Microsoft Excel