المحتويات
يعد البحث عن الكلمات الأساسية في نص المصدر أحد أكثر المهام شيوعًا عند التعامل مع البيانات. دعونا نلقي نظرة على حلها بعدة طرق باستخدام المثال التالي:
لنفترض أنك وأنا لدينا قائمة بالكلمات الرئيسية - أسماء ماركات السيارات - وجدول كبير لجميع أنواع قطع الغيار ، حيث يمكن أن تحتوي الأوصاف أحيانًا على علامة تجارية واحدة أو أكثر في وقت واحد ، إذا كان قطع الغيار يناسب أكثر من واحدة ماركة السيارة. مهمتنا هي البحث عن جميع الكلمات الرئيسية المكتشفة وعرضها في الخلايا المجاورة من خلال حرف فاصل معين (على سبيل المثال ، فاصلة).
الطريقة الثالثة. استعلام الطاقة
بالطبع ، نحول طاولاتنا أولاً إلى ديناميكية ("ذكية") باستخدام اختصار لوحة المفاتيح CTRL+T أو أوامر الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول)، أعطهم أسماء (على سبيل المثال أختامи قطع غيار) وتحميل واحدًا تلو الآخر في محرر Power Query عن طريق التحديد في علامة التبويب البيانات - من جدول / نطاق (بيانات - من جدول / نطاق). إذا كانت لديك إصدارات أقدم من Excel 2010-2013 ، حيث تم تثبيت Power Query كوظيفة إضافية منفصلة ، فسيكون الزر المطلوب في علامة التبويب استفسار الطاقة. إذا كان لديك إصدار جديد تمامًا من Excel 365 ، فإن الزر من الجدول / النطاق دعا هناك الآن بأوراق الشجر (من الورقة).
بعد تحميل كل جدول في Power Query ، نعود إلى Excel بالأمر الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... - إنشاء اتصال فقط (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ... - إنشاء اتصال فقط).
لنقم الآن بإنشاء طلب مكرر قطع غيارعن طريق النقر بزر الماوس الأيمن فوقه والاختيار طلب مكرر (طلب بحث مكرر)، ثم أعد تسمية طلب النسخة الناتج إلى النتائج وسنواصل العمل معه.
منطق الأفعال هو كالتالي:
- في علامة التبويب خيارات متقدمة إضافة عمود اختر فريق عمود مخصص (إضافة عمود - عمود مخصص) وأدخل الصيغة = الماركات. بعد الضغط على OK سنحصل على عمود جديد ، حيث سيكون هناك جدول متداخل في كل خلية يحتوي على قائمة بالكلمات الرئيسية - العلامات التجارية لصناعة السيارات:
- استخدم الزر مع الأسهم المزدوجة في رأس العمود المضاف لتوسيع كل الجداول المتداخلة. في الوقت نفسه ، ستتضاعف الأسطر التي تحتوي على أوصاف لقطع الغيار في مضاعف عدد العلامات التجارية ، وسوف نحصل على جميع مجموعات الأزواج الممكنة من "علامة تجارية لقطع الغيار":
- في علامة التبويب خيارات متقدمة إضافة عمود اختر فريق العمود الشرطي (العمود الشرطي) وقم بتعيين شرط للتحقق من حدوث كلمة رئيسية (علامة تجارية) في النص المصدر (وصف الجزء):
- لجعل حالة البحث غير حساسة ، أضف الوسيطة الثالثة يدويًا في شريط الصيغة قارن إلى وظيفة التحقق من التواجد نص (إذا كان شريط الصيغة غير مرئي ، فيمكن تمكينه في علامة التبويب التقيم):
- نقوم بتصفية الجدول الناتج ، مع ترك الجداول الموجودة في العمود الأخير فقط ، أي المطابقات وإزالة العمود غير الضروري الحوادث.
- تجميع الأوصاف المتطابقة مع الأمر مجموعة من علامة التبويب تحول (تحويل - تجميع حسب). كعملية تجميع ، اختر كل الخطوط (كل الصفوف). عند الإخراج ، نحصل على عمود به جداول تحتوي على جميع التفاصيل الخاصة بكل قطعة غيار ، بما في ذلك العلامات التجارية لشركات صناعة السيارات التي نحتاجها:
- لاستخراج التقديرات لكل جزء ، أضف عمودًا محسوبًا آخر في علامة التبويب إضافة عمود - عمود مخصص (إضافة عمود - عمود مخصص) واستخدم صيغة تتكون من جدول (توجد في عمودنا التفاصيل) واسم العمود المستخرج:
- نضغط على الزر ذي الأسهم المزدوجة في رأس العمود الناتج ونحدد الأمر استخراج القيم (استخراج القيم)لإخراج الطوابع بأي حرف محدد تريده:
- إزالة عمود غير ضروري التفاصيل.
- لإضافة الأجزاء التي اختفت منه إلى الجدول الناتج ، حيث لم يتم العثور على علامات تجارية في الأوصاف ، نقوم بتنفيذ الإجراء لدمج الاستعلام نتيجة مع الطلب الأصلي قطع غيار زر دمج علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - دمج الاستعلامات). نوع الاتصال - الانضمام الخارجي الحق (صلة خارجية يمنى):
- كل ما تبقى هو إزالة الأعمدة الإضافية وإعادة تسمية ونقل الأعمدة المتبقية - ويتم حل مهمتنا:
الطريقة الثالثة. الصيغ
إذا كان لديك إصدار من Excel 2016 أو أحدث ، فيمكن حل مشكلتنا بطريقة مدمجة وأنيقة للغاية باستخدام الوظيفة الجديدة الجمع بين (نص مشترك):
المنطق وراء هذه الصيغة بسيط:
- المسمى الوظيفي ابحث (تجد) يبحث عن حدوث كل علامة تجارية بدورها في الوصف الحالي للجزء وإرجاع إما الرقم التسلسلي للرمز ، بدءًا من العثور على العلامة التجارية ، أو الخطأ #VALUE! إذا كانت العلامة التجارية غير موجودة في الوصف.
- ثم استخدم الوظيفة IF (إذا) и يوشيبكا (خطأ) نستبدل الأخطاء بسلسلة نصية فارغة "" ، والأرقام الترتيبية للأحرف بأسماء العلامات التجارية نفسها.
- يتم تجميع المصفوفة الناتجة من الخلايا الفارغة والعلامات التجارية التي تم العثور عليها في سلسلة واحدة من خلال حرف فاصل معين باستخدام الوظيفة الجمع بين (نص مشترك).
مقارنة الأداء والتخزين المؤقت لاستعلام Power Query من أجل التسريع
لاختبار الأداء ، دعنا نأخذ جدولًا من وصف 100 قطعة غيار كبيانات أولية. على ذلك نحصل على النتائج التالية:
- وقت إعادة الحساب بالصيغ (الطريقة 2) - 9 ثوانٍ. عند نسخ الصيغة لأول مرة إلى العمود بأكمله و 2 ثانية. عند التكرار (يؤثر التخزين المؤقت ، على الأرجح).
- وقت تحديث استعلام Power Query (الطريقة 1) أسوأ بكثير - 110 ثانية.
بالطبع ، يعتمد الكثير على الأجهزة الخاصة بجهاز كمبيوتر معين والإصدار المثبت من Office والتحديثات ، لكن الصورة العامة ، على ما أعتقد ، واضحة.
لتسريع استعلام Power Query ، دعنا نخزن جدول البحث مؤقتًا أختام، لأنه لا يتغير في عملية تنفيذ الاستعلام وليس من الضروري إعادة حسابه باستمرار (كما يفعل Power Query بحكم الأمر الواقع). لهذا نستخدم الوظيفة Table.Buffer من لغة Power Query المضمنة M.
للقيام بذلك ، افتح استعلامًا النتائج وعلى علامة التبويب التقيم اضغط الزر محرر متقدم (عرض - محرر متقدم). في النافذة التي تفتح ، أضف سطرًا بمتغير جديد ماركي 2، والتي ستكون نسخة مخزنة من دليل automaker الخاص بنا ، واستخدم هذا المتغير الجديد لاحقًا في أمر الاستعلام التالي:
بعد هذا التحسين ، تزداد سرعة التحديث لطلبنا بنحو 7 مرات - حتى 15 ثانية. شيء مختلف تماما 🙂
- البحث عن نص غامض في Power Query
- استبدال النص المجمع بالصيغ
- استبدال النص المجمع في Power Query باستخدام List.Accumulate الدالة