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

ماذا يعني المطابقة حسب ترتيب الظهور؟

لنفترض أن قائمتين للأصول الفنية تحتويان كلتاهما على عدة صفوف تحمل التسمية Arc Tile. مهمتك هي إلحاق كمية المصدر الأولى بأول صف Arc Tile في القائمة اليسرى، وكمية المصدر الثانية بالصف الثاني، وهكذا. قد تظهر تسميات أخرى بين تلك الصفوف؛ ويُحتسب ترتيب الظهور لكل تسمية على حدة.

العلاقة التي تعتمد على التسمية وحدها ملتبسة. إذا احتوت إحدى القائمتين على ثلاثة صفوف Arc Tile والأخرى على صفين، فستؤدي مطابقة كل الاحتمالات إلى ستة أزواج لهذه التسمية. وللبحث العادي قيد آخر: توثّق Microsoft أن XLOOKUP تُرجع أول تطابق تعثر عليه. لذلك، فإن تكرار البحث عن التسمية وحدها لا ينتقل إلى مرات الظهور التالية. دالة XLOOKUP

الحل هو جعل مفتاح البحث أكثر تحديدًا: Arc Tile|1 وArc Tile|2 وArc Tile|3. يحدد كل مفتاح مرقّم حالة ظهور واحدة. والطريقة أدناه تطبيق عملي يستخدم دوال Excel الموثّقة؛ إذ يشرح توثيق Microsoft سلوك الدوال.

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

ما إصدارات Excel for Mac التي تدعم هذه الطريقة؟

تستخدم الصيغ الأساسية COUNTIF وعامل الربط & وXLOOKUP. تسرد Microsoft إصدارات Excel for Microsoft 365 for Mac وExcel 2024 for Mac وExcel 2021 for Mac في صفحات الدوال ذات الصلة. وينص توثيق XLOOKUP صراحةً على أن الدالة غير متاحة في Excel 2016 وExcel 2019. دالة XLOOKUP، استخدام دالة COUNTIF في Microsoft Excel

ابدأ بصيغة البحث التي تُنسخ إلى الأسفل. ويعيد تنويع لاحق النتائج السبعة نفسها باستخدام صيغة مصفوفة واحدة منسدلة. توثّق Microsoft أن الصيغ المنسدلة توضع في شبكة ورقة العمل خارج جداول Excel. صيغ المصفوفات الديناميكية وسلوك انسكابها

إعداد قائمتين تجريبيتين للأصول

استخدم ورقة عمل فارغة في مصنف واحد. أدخل القائمة اليسرى التالية في A1:B8. هذه التسميات والكميات للتوضيح؛ وتظل الكمية اليسرى جزءًا من القائمة الأصلية ولا تُستخدم لتحديد المطابقة.

تسمية الأصل الكمية اليسرى
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

أدخل قائمة المصدر في G1:H7. أبقِ ترتيب الصفوف كما هو موضح كي تطابق نتائجك هذا الشرح.

تسمية الأصل كمية المصدر
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

أضف العناوين Occurrence وJoin key وMatched source quantity في C1 وD1 وE1. وأضف Occurrence وJoin key وUsed by left list في I1 وJ1 وK1. اترك العمود F فارغًا للفصل بصريًا بين القائمتين.

في هذا المثال، استخدم تسميات نصية غير فارغة أقصر من 255 حرفًا، مع توحيد الأحرف الكبيرة والمسافات، ومن دون أحرف بدل مثل * أو? أو~. أدخل قيمة كمية رقمية لكل صف مصدر، بما في ذلك الصفر عند الاقتضاء. تجعل هذه المتطلبات الأساسية تفسير الصيغ البسيطة واضحًا.

ترقيم مرات الظهور وإنشاء مفاتيح البحث

احتساب كل تسمية من أعلى قائمتها

أدخل هذه الصيغة في C2، واضغط Return، ثم انسخها إلى الأسفل حتى C8:

=COUNTIF($A$2:A2,A2)

يبقى الطرف الأول، $A$2، ثابتًا. ويتسع الطرف الثاني عند نسخ الصيغة إلى الأسفل. في كل صف، تحتسب الصيغة عدد مرات ظهور تسمية ذلك الصف حتى ذلك الموضع. تحتسب COUNTIF الخلايا التي تستوفي معيارًا محددًا. استخدام دالة COUNTIF في Microsoft Excel

ينبغي أن تكون أرقام مرات الظهور الناتجة في القائمة اليسرى 1, 1, 2, 1, 2, 3, 1. وعلى وجه الخصوص، يحصل Arc Tile الثالث على 3 رغم وجود تسميات أخرى بين صفوفه.

أدخل صيغة المصدر المقابلة في I2، ثم انسخها حتى I7:

=COUNTIF($G$2:G2,G2)

ينبغي أن تكون أرقام مرات الظهور في المصدر 1, 1, 1, 2, 2, 1. احتسب مرات الظهور في القائمتين كلٌّ على حدة: رقم ظهور الصف هو موضعه بين التسميات المطابقة ضمن قائمته.

دمج التسمية ورقم الظهور

أدخل هذه الصيغة في D2 وانسخها حتى D8:

=A2&"|"&C2

أدخل هذه الصيغة في J2 وانسخها حتى J7:

=G2&"|"&I2

تحتوي القائمة اليسرى الآن على Arc Tile|1 وArc Tile|2 وArc Tile|3. أما المصدر فلا يحتوي إلا على أول مفتاحين منها. يكشف ذلك عن غياب الظهور الثالث قبل إرجاع أي كميات.

إرجاع كمية مصدر واحدة لكل صف من القائمة اليسرى

أدخل الصيغة التالية في E2، ثم انسخها حتى E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

تبحث الصيغة في مفاتيح ضم المصدر، وتُرجع كمية المصدر المقابلة، وتعرض Unmatched عندما يكون المفتاح المرقّم غير موجود. يطلب الوسيط الأخير 0 صراحةً تطابقًا تامًا، وهو أيضًا الإعداد الافتراضي لـXLOOKUP. دالة XLOOKUP

ينبغي أن تكون نتيجة القائمة اليسرى المكتملة كما يلي:

تسمية الأصل الكمية اليسرى ترتيب الظهور مفتاح الضم كمية المصدر المطابقة
Arc Tile 4 1 Arc Tile\ 1
Dot Panel 2 1 Dot Panel\ 1
Arc Tile 6 2 Arc Tile\ 2
Line Print 3 1 Line Print\ 1
Dot Panel 5 2 Dot Panel\ 2
Arc Tile 1 3 Arc Tile\ 3
Ring Card 2 1 Ring Card\ 1

ما زال هناك سبعة صفوف في القائمة اليسرى. يتلقى Arc Tile الثاني 9 بدلًا من تكرار 8 الخاص بالظهور الأول. ولا يوجد مقابل في المصدر للظهور الثالث. كذلك لا يوجد مقابل لـRing Card، بينما يظل الصفر المطابق لـDot Panel الثاني ظاهرًا.

أبقِ Unmatched متميزًا عن الصفر. في هذا المثال، الصفر كمية حقيقية في المصدر؛ واستخدامه عند غياب المطابقة سيخفي هذا الفرق. وبالمثل، عالج كميات المصدر الفارغة قبل تفسير النتائج كي لا تُفهم خلية المصدر الفارغة على أنها كمية مسجّلة.

اختياري: إرجاع النتائج كمصفوفة واحدة

بعد التحقق من نتائج الصيغ المنسوخة إلى الأسفل، امسح E2:E8 فقط وأدخل هذا البديل في E2:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

يستخدم هذا مفاتيح الضم السبعة في القائمة اليسرى كمدخلات البحث. مع بيانات العينة، تشغل النتيجة النطاق E2:E8 بترتيب القائمة اليسرى. اضغط Return؛ ويجب أن تكون خلايا الإخراج المجاورة فارغة. تصف Microsoft هذا الوضع التلقائي لنتائج المصفوفة بأنه انسكاب. صيغ المصفوفات الديناميكية وسلوك انسكابها

اختر إصدار الصيغ المنسوخة إلى الأسفل إذا كنت تفضّل صيغة يمكن فحصها بصورة مستقلة في كل صف. واختر إصدار الانسكاب إذا كنت تفضّل صيغة واحدة تتحكم في عمود النتائج. يعتمد كلاهما على مفاتيح مرات الظهور نفسها.

التحقق من حالات عدم العثور على تطابق وصفوف المصدر غير المستخدمة

لا يعني الحفاظ على عدد صفوف القائمة اليسرى أن كل صف في المصدر قد استُخدم. أضف تحققًا عكسيًا كي تظل مرات ظهور المصدر الزائدة ظاهرة. في K2، أدخل ما يلي وانسخه حتى K7:

=COUNTIF($D$2:$D$8,J2)

في هذه العينة، تُرجع صفوف المصدر الخمسة الأولى 1، ويُرجع Grid Sheet|1 القيمة 0. يشير هذا الصفر إلى صف مصدر بلا مقابل في القائمة اليسرى. أما القيمة الأكبر من 1 فتعني أن مفتاح مصدرًا ما مشار إليه من مفاتيح يسارية متعددة ويحتاج إلى التحقق.

استخدم عمليات التحقق هذه معًا:

  • تأكد من أن الإخراج يشغل الصفوف السبعة الأصلية للقائمة اليسرى بالضبط.
  • تحقق من تسلسل الكميات: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • تأكد من بقاء Arc Tile الثالث وRing Card ظاهرين بوصفهما غير مطابقين.
  • تأكد من وضع علامة «غير مستخدم» على Grid Sheet|1 في تدقيق المصدر.

تم التحقق من التسلسل المتوقع ونتيجة المصدر غير المستخدم باستخدام حساب حتمي صغير لمنطق المطابقة حسب ترتيب الظهور. وفُحصت صيغ Excel بمقارنتها بالصياغة الموثّقة لدى Microsoft؛ ولم تُنفّذ في Excel for Mac.

استكشاف أخطاء النتيجة وإصلاحها قبل توسيع المصنف

تتغير المطابقات بعد الفرز

تستخدم صيغ مرات الظهور ترتيب الصفوف الحالي. وقد يغيّر فرز أي من القائمتين وإعادة الحساب الصف الذي يتلقى رقم الظهور 1 أو 2. حدّد الترتيب المقصود قبل الترقيم. وإذا كانت المطابقة الدائمة مطلوبة، فاحتفظ بمعرّف ثابت يسجلها بدلًا من الاعتماد على موضع يعاد احتسابه وحده.

تبدو التسميات متشابهة لكن المطابقة غير متوقعة

تشير Microsoft إلى أن COUNTIF تتجاهل حالة الأحرف، وتتعرّف على معايير أحرف البدل، وقد تؤدي إلى نتائج غير متوقعة عند وجود مسافات بادئة أو لاحقة أو محارف أخرى غير مرغوبة. كما توثّق قيودًا عند مطابقة سلاسل أطول من 255 حرفًا. وحّد التسميات في القائمتين قبل ترقيمها؛ فالتسميات القصيرة والمنسقة باتساق أعلاه تتجنب هذه الحالات. استخدام دالة COUNTIF في Microsoft Excel

تعرض صيغة المصفوفة #SPILL!

تحقق من وجود محتوى في E3:E8، بما في ذلك الصيغ السابقة المنسوخة إلى الأسفل. انقل أي شيء تريد الاحتفاظ به، ثم امسح ما يعيق الانسكاب. وإذا كانت الصيغة داخل جدول Excel، فاستخدم أسلوب النسخ إلى الأسفل أو انقل الإخراج المنسكب إلى خارج الجدول. هذه قيود موثّقة للانسكاب. صيغ المصفوفات الديناميكية وسلوك انسكابها

تحتاج إلى بحث من دون XLOOKUP

يمكن لمفاتيح المساعدة نفسها تشغيل بحث باستخدام INDEX/MATCH. وكبديل لأي من الصيغتين أعلاه، أدخل هذه الصيغة في E2 وانسخها حتى E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

هنا، يحدد MATCH مع 0 موضع مفتاح الضم المطابق تمامًا، وتُرجع INDEX الكمية في ذلك الموضع. وتظل المفاتيح المفقودة ظاهرة عمدًا على هيئة #N/A. توثّق Microsoft كلتا الدالتين لإصدارات Mac الحالية. لا تحتاج هذه الصيغة المفردة إلى إخراج منسكب. دالة MATCH، دالة INDEX

لا تظهر الصفوف الجديدة في النتائج

يستخدم هذا الشرح نطاقات ثابتة. عند إضافة صفوف، وسّع صيغ مرات الظهور ومفاتيح الضم ونطاقي البحث والإرجاع في المصدر ونطاق التدقيق العكسي معًا. وفي إصدار الانسكاب، وسّع أيضًا مدخل البحث في القائمة اليسرى إلى ما بعد D8. لا يؤدي إدخال بيانات أسفل هذه النطاقات وحده إلى تضمينها في الحساب.

بعد توافق تسلسل الإخراج والتدقيق العكسي مع الترتيب المقصود، يصبح لديك ضم صالح يعتمد على ترتيب الظهور: يحتفظ كل صف أصل في القائمة اليسرى بموضعه، ويتلقى كمية مصدر واحدة على الأكثر، ويظل غياب الظهور المطابق ظاهرًا.

المصادر