في الآونة الأخيرة ، اتصل بي أحد أصدقائي وطلب مني المساعدة في توليد كل العبارات الممكنة التي تتكون من مجموعة من الكلمات المحددة. يمكن أن تنشأ مشاكل من هذا النوع عند تجميع قوائم الكلمات الرئيسية والعبارات للإعلان عبر الإنترنت والترويج لكبار المسئولين الاقتصاديين ، عندما تحتاج إلى متابعة جميع التباديل المحتمل للكلمات في استعلام البحث:
في الرياضيات ، تسمى هذه العملية المنتج الديكارتي. التعريف الرسمي هو كما يلي: المنتج الديكارتي للمجموعتين A و B هو مجموعة كل الأزواج ، المكون الأول منها ينتمي إلى المجموعة A ، والمكون الثاني ينتمي إلى المجموعة B. علاوة على ذلك ، يمكن أن تكون عناصر المجموعات كلاهما الأرقام والنصوص.
إذا تمت ترجمته إلى لغة بشرية ، فهذا يعني أنه إذا كان لدينا في المجموعة أ ، على سبيل المثال ، الكلمات "أبيض" و "أحمر" ، وفي المجموعة ب "BMW" و "مرسيدس" ، ثم بعد المنتج الديكارتي لهاتين المجموعتين الحصول على الإخراج هو مجموعة من جميع المتغيرات المحتملة للعبارات ، وتتألف من كلمات كلتا القائمتين:
- بي ام دبليو بيضاء
- بي ام دبليو حمراء
- مرسيدس بيضاء
- مرسيدس حمراء
... أي ما نحتاجه فقط. لنلقِ نظرة على طريقتين لحل هذه المهمة في Excel.
الطريقة الثالثة. الصيغ
لنبدأ بالصيغ. لنفترض أنه كبيانات أولية ، لدينا ثلاث قوائم بالكلمات الأصلية في الأعمدة A و B و C على التوالي ، ويمكن أن يختلف عدد العناصر في كل قائمة:
أولاً ، دعنا نصنع ثلاثة أعمدة مع فهارس ، أي الأعداد الترتيبية للكلمات من كل قائمة في جميع التركيبات الممكنة. سيتم إدخال الصف الأول من الوحدات (E2: G2) يدويًا ، وبالنسبة للباقي ، سنستخدم الصيغة التالية:
المنطق هنا بسيط: إذا كان الفهرس الموجود في الخلية السابقة العليا قد وصل بالفعل إلى نهاية القائمة ، أي يساوي عدد العناصر في القائمة المحسوبة بواسطة الوظيفة بالإحصاء (كونتا)، ثم نعيد الترقيم. بخلاف ذلك ، نزيد المؤشر بمقدار 1. انتبه بشكل خاص للإصلاح الذكي للنطاقات بعلامات الدولار ($) بحيث يمكنك نسخ الصيغة لأسفل وإلى اليمين.
الآن بعد أن أصبح لدينا الأرقام الترتيبية للكلمات التي نحتاجها من كل قائمة ، يمكننا استخراج الكلمات نفسها باستخدام الوظيفة INDEX (فهرس) إلى ثلاثة أعمدة منفصلة:
إذا لم تكن قد صادفت هذه الوظيفة في عملك من قبل ، فإنني أنصحك بشدة بدراستها على الأقل قطريًا - فهي تساعد في العديد من المواقف وهي مفيدة على الأقل (بل وأكثر!) VPR (البحث).
حسنًا ، بعد ذلك ، يبقى فقط لصق الأجزاء الناتجة سطراً بسطر باستخدام رمز التسلسل (&):
... أو (إذا كان لديك أحدث إصدار من Excel) بوظيفة يدوية الجمع بين (نص مشترك)، والتي يمكنها لصق المحتويات الكاملة للخلايا المحددة من خلال حرف فاصل معين (مسافة):
الطريقة الثانية. من خلال Power Query
Power Query هي وظيفة إضافية قوية لبرنامج Microsoft Excel تؤدي مهمتين رئيسيتين: 1. تحميل البيانات إلى Excel من أي مصدر خارجي تقريبًا ، و 2. جميع أنواع تحويلات الجداول المحملة. تم بالفعل تضمين Power Query في Excel 2016-2019 ، ويتم تثبيته في Excel 2010-2013 كوظيفة إضافية منفصلة (يمكنك تنزيله من موقع Microsoft الرسمي على الويب مجانًا). إذا لم تكن قد بدأت بعد في استخدام Power Query في عملك ، فقد حان الوقت للتفكير في الأمر ، لأن التحويلات مثل تلك الموضحة أعلاه تتم هناك بسهولة وبشكل طبيعي ، في بضع حركات فقط.
أولاً ، لنقم بتحميل قوائم المصدر كاستعلامات منفصلة في Power Query. للقيام بذلك ، لكل جدول ، قم بتنفيذ الخطوات التالية:
- لنحول الطاولات إلى "ذكية" باستخدام زر تنسيق كجدول علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - تنسيق كجدول) أو اختصار لوحة المفاتيح CTRL+T. سيتم إعطاء اسم كل جدول تلقائيًا جدول 1,2,3،XNUMX،XNUMX ...، والتي ، مع ذلك ، يمكن تغييرها إذا رغبت في ذلك في علامة التبويب منشئ (التصميم).
- بعد تعيين الخلية النشطة في الجدول ، اضغط على الزر من الطاولة (من الجدول) علامة التبويب البيانات (تاريخ) أو في علامة التبويب استفسار الطاقة (إذا كنت قد قمت بتثبيته كوظيفة إضافية منفصلة لبرنامج Excel 2010-2013).
- في نافذة محرر الاستعلام التي تفتح ، حدد الأمر الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ..) ثم الخيار فقط قم بإنشاء اتصال (إنشاء اتصال فقط). سيؤدي ذلك إلى ترك الجدول المحمل في الذاكرة والسماح بالوصول إليه في المستقبل.
إذا كنت تفعل كل شيء بشكل صحيح ، فيجب أن يكون الإخراج في اللوحة اليمنى ثلاثة طلبات في الوضع اتصال فقط بأسماء طاولتنا:
الآن انقر بزر الماوس الأيمن فوق الاستعلام الأول وحدد الأمر لينك (مرجع)لعمل نسخة منه قابلة للتحديث ، ثم أضف عمودًا إضافيًا إلى البيانات عبر الأمر إضافة عمود ž - عمود مخصص (إضافة عمود - عمود مخصص). في نافذة إدخال الصيغة ، أدخل اسم العمود الجديد (على سبيل المثال ، Fragment2) وتعبيرًا بسيطًا للغاية كصيغة:
= جدول 2
... بمعنى آخر ، اسم الاستعلام الثاني:
بعد النقر على OK سنرى عمودًا جديدًا ، في كل خلية سيكون هناك جدول متداخل به عبارات من الجدول الثاني (يمكنك رؤية محتويات هذه الجداول إذا نقرت في خلفية الخلية المجاورة للكلمة طاولات ومكاتب ):
يبقى توسيع جميع محتويات هذه الجداول المتداخلة باستخدام الزر ذي الأسهم المزدوجة في رأس العمود الناتج وإلغاء تحديد استخدم اسم العمود الأصلي كبادئة (استخدم اسم العمود الأصلي كبادئة):
... ونحصل على جميع مجموعات العناصر الممكنة من أول مجموعتين:
علاوة على ذلك ، كل شيء مشابه. أضف عمودًا محسوبًا آخر بالصيغة:
= جدول 3
... ، ثم قم بتوسيع الجداول المتداخلة مرة أخرى - والآن لدينا بالفعل جميع الخيارات الممكنة لتبديل الكلمات من المجموعات الثلاث ، على التوالي:
يبقى تحديد الأعمدة الثلاثة من اليسار إلى اليمين مع الاستمرار CTRL، وسلسلة محتوياتها مفصولة بمسافات باستخدام الأمر دمج الأعمدة (دمج الأعمدة) من علامة التبويب تحول (تحول):
يمكن تفريغ النتائج الناتجة مرة أخرى على الورقة باستخدام الأمر المألوف بالفعل الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ..):
إذا تغير شيء ما في المستقبل في جداول المصدر لدينا بأجزاء ، فسيكون ذلك كافيًا لتحديث الاستعلام الناتج عن طريق النقر بزر الماوس الأيمن على الجدول الناتج واختيار الأمر تحديث وحفظ (ينعش) أو بالضغط على اختصار لوحة المفاتيح CTRL+قديم+F5.
- ما هو Power Query و Power Pivot و Power Map و Power BI ولماذا يحتاجون إلى مستخدم Excel
- إنشاء مخطط جانت في Power Query
- 5 طرق لاستخدام وظيفة الفهرس