دمج قائمتين بدون تكرارات

موقف كلاسيكي: لديك قائمتان يجب دمجهما في قائمة واحدة. علاوة على ذلك ، في القوائم الأولية يمكن أن يكون هناك عناصر فريدة وعناصر متطابقة (سواء بين القوائم أو في الداخل) ، ولكن عند الإخراج تحتاج إلى الحصول على قائمة بدون تكرارات (التكرارات):

دعونا نلقي نظرة تقليدية على عدة طرق لحل مثل هذه المشكلة الشائعة - من البدائية "على الجبهة" إلى الأكثر تعقيدًا ، ولكن الأنيقة.

الطريقة الأولى: إزالة التكرارات

يمكنك حل المشكلة بأبسط طريقة - نسخ عناصر كلتا القائمتين يدويًا في واحدة ثم تطبيق الأداة على المجموعة الناتجة. إزالة التكرارات من علامة التبويب البيانات (البيانات - إزالة التكرارات):

بالطبع ، لن تعمل هذه الطريقة إذا كانت البيانات الموجودة في قوائم المصدر تتغير كثيرًا - سيتعين عليك تكرار الإجراء بالكامل بعد كل تغيير مرة أخرى. 

الطريقة 1 أ. جدول محوري

هذه الطريقة ، في الواقع ، هي استمرار منطقي للطريقة السابقة. إذا لم تكن القوائم كبيرة جدًا وكان الحد الأقصى لعدد العناصر فيها معروفًا مسبقًا (على سبيل المثال ، لا يزيد عن 10) ، فيمكنك دمج جدولين في جدول واحد عن طريق الروابط المباشرة ، وإضافة عمود مع تلك الموجودة على اليمين و بناء جدول ملخص بناءً على الجدول الناتج:

كما تعلم ، يتجاهل الجدول المحوري التكرار ، لذلك سنحصل في الإخراج على قائمة مجمعة بدون تكرارات. العمود الإضافي الذي يحتوي على 1 مطلوب فقط لأن Excel يمكنه إنشاء جداول موجزة تحتوي على عمودين على الأقل.

عندما يتم تغيير القوائم الأصلية ، ستنتقل البيانات الجديدة إلى الجدول المدمج عبر روابط مباشرة ، ولكن يجب تحديث الجدول المحوري يدويًا (انقر بزر الماوس الأيمن - تحديث وحفظ). إذا لم تكن بحاجة إلى إعادة الحساب أثناء التنقل ، فمن الأفضل استخدام خيارات أخرى.

الطريقة 2: صيغة الصفيف

يمكنك حل المشكلة بالصيغ. في هذه الحالة ، ستتم إعادة حساب النتائج وتحديثها تلقائيًا وفوريًا ، فور إجراء التغييرات في القوائم الأصلية. للراحة والإيجاز ، دعنا نعطي أسماء قوائمنا. قائمة 1 и قائمة 2استخدام مدير الاسم علامة التبويب معادلة (الصيغ - مدير الاسم - إنشاء):

بعد التسمية ، ستبدو الصيغة التي نحتاجها كما يلي:

للوهلة الأولى ، يبدو الأمر مخيفًا ، لكن في الواقع ، كل شيء ليس مخيفًا جدًا. اسمح لي بتوسيع هذه الصيغة على عدة أسطر باستخدام تركيبة المفاتيح Alt + Enter والمسافة البادئة بمسافات ، كما فعلنا ، على سبيل المثال هنا:

المنطق هنا كالتالي:

• تحدد الصيغة INDEX (List1؛ MATCH (0؛ COUNTIF ($ E $ 1: E1؛ List1)؛ 0) جميع العناصر الفريدة من القائمة الأولى. بمجرد نفادها ، تبدأ بإعطاء الخطأ # N / A:

  

• تستخرج الصيغة INDEX (List2؛ MATCH (0؛ COUNTIF ($ E $ 1: E1؛ List2)؛ 0)) العناصر الفريدة من القائمة الثانية بنفس الطريقة.
• تقوم دالتان IFERROR المتداخلة في بعضها البعض بتنفيذ الإخراج أولاً من الوظائف الفريدة من القائمة 1 ، ثم من القائمة 2 واحدة تلو الأخرى.

لاحظ أن هذه صيغة صفيف ، أي بعد الكتابة ، يجب إدخالها في خلية غير عادية أدخل، ولكن باختصار لوحة المفاتيح CTRL+تغير+أدخل ثم انسخ (اسحب) إلى الخلايا الفرعية بهامش.

في الإصدار الإنجليزي من Excel ، تبدو هذه الصيغة كما يلي:

= IFERROR (IFERROR (INDEX (List1، MATCH (0، COUNTIF ($ E $ 1: E1، List1)، 0)))، INDEX (List2، MATCH (0، COUNTIF ($ E $ 1: E1، List2)، 0)) ) ، "") 

يتمثل الجانب السلبي لهذا الأسلوب في أن صيغ الصفيف تبطئ بشكل ملحوظ العمل مع الملف إذا كانت الجداول المصدر تحتوي على عدد كبير (عدة مئات أو أكثر) من العناصر. 

الطريقة الثالثة. استعلام الطاقة

إذا كانت قوائم المصدر الخاصة بك تحتوي على عدد كبير من العناصر ، على سبيل المثال ، عدة مئات أو آلاف ، فبدلاً من صيغة صفيف بطيئة ، من الأفضل استخدام نهج مختلف تمامًا ، وهو أدوات الوظيفة الإضافية Power Query. هذه الوظيفة الإضافية مضمنة في Excel 2016 بشكل افتراضي. إذا كان لديك Excel 2010 أو 2013 ، فيمكنك تنزيله وتثبيته بشكل منفصل (مجانًا).

خوارزمية الإجراءات هي كما يلي:

1. افتح علامة تبويب منفصلة للوظيفة الإضافية المثبتة استفسار الطاقة (إذا كان لديك Excel 2010-2013) أو انتقل إلى علامة التبويب البيانات (إذا كان لديك Excel 2016).
2. حدد القائمة الأولى واضغط على الزر من الجدول / النطاق (من النطاق / الجدول). عندما سئلنا عن إنشاء "جدول ذكي" من قائمتنا ، نتفق على:

   

3. تفتح نافذة محرر الاستعلام ، حيث يمكنك رؤية البيانات المحملة واسم الاستعلام الجدول 1 (يمكنك تغييرها بنفسك إذا أردت).
4. انقر نقرًا مزدوجًا فوق رأس الجدول (word قائمة 1) وإعادة تسميته إلى أي اسم آخر (على سبيل المثال مجتمع). ما يجب تسميته بالضبط ليس مهمًا ، ولكن يجب تذكر الاسم المخترع ، لأنه. سيتعين استخدامه مرة أخرى لاحقًا عند استيراد الجدول الثاني. لن يعمل دمج جدولين في المستقبل إلا في حالة تطابق عناوين الأعمدة الخاصة بهم.
5. قم بتوسيع القائمة المنسدلة في الزاوية اليسرى العليا إغلاق وتحميل واختر إغلاق وتحميل ... (إغلاق وتحميل إلى ...):

   

6. في مربع الحوار التالي (قد يبدو مختلفًا قليلاً - لا تنزعج) ، حدد فقط قم بإنشاء اتصال (فقط إنشاء اتصال):

   

7. نكرر الإجراء بأكمله (النقاط 2-6) للقائمة الثانية. عند إعادة تسمية عنوان عمود ، من المهم استخدام نفس الاسم (الأشخاص) كما في الاستعلام السابق.
8. في نافذة Excel في علامة التبويب البيانات أو في علامة التبويب استفسار الطاقة اختار إحضار البيانات - دمج الطلبات - إضافة (إحضار البيانات - دمج الاستعلامات - إلحاق):

   

9. في مربع الحوار الذي يظهر ، حدد طلباتنا من القوائم المنسدلة:

   

10. نتيجة لذلك ، سوف نحصل على استعلام جديد ، حيث سيتم ربط قائمتين تحت بعضهما البعض. يبقى إزالة التكرارات باستخدام الزر حذف الصفوف - إزالة التكرارات (حذف الصفوف - حذف التكرارات):

    

11. يمكن إعادة تسمية الاستعلام النهائي على الجانب الأيمن من لوحة الخيارات ، مع إعطائه اسمًا سليمًا (سيكون هذا هو اسم جدول النتائج في الواقع) ويمكن تحميل كل شيء على الورقة باستخدام الأمر إغلاق وتحميل (إغلاق وتحميل):

    

في المستقبل ، مع أي تغييرات أو إضافات إلى القوائم الأصلية ، يكفي النقر بزر الماوس الأيمن لتحديث جدول النتائج.

• كيفية تجميع جداول متعددة من ملفات مختلفة باستخدام Power Query
• استخراج العناصر الفريدة من القائمة
• كيفية مقارنة قائمتين مع بعضهما البعض للمطابقات والاختلافات