استخدم بحثًا عن تطابق تام لإضافة بيانات الأصول الوصفية إلى كل صف في المخزون، بما في ذلك معرّفات الأصول المتكررة. في Excel لنظام Mac، يمكنك إنشاء نتيجة واحدة ممتدة باستخدام HSTACK وXLOOKUP، أو استخدام أعمدة بحث منفصلة في Excel 2021.

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

ما إصدار Excel لنظام Mac الذي يدعم هذا الأسلوب؟

تحقق من التثبيت لديك عبر Excel → About Microsoft Excel. توضّح إرشادات Microsoft لنظام Mac أن مربع الحوار «حول» في التطبيق يعرض رقم الإصدار ونوع الترخيص. تحقّق من كليهما عند اختيار صيغة. About Office: What version of Office am I using?

إصدار Mac الأسلوب في هذه المقالة
Excel for Microsoft 365 for Mac نتيجة واحدة مجمّعة باستخدام HSTACK وXLOOKUP
Excel 2024 for Mac نتيجة واحدة مجمّعة باستخدام HSTACK وXLOOKUP
Excel 2021 for Mac ثلاثة أعمدة منفصلة ممتدة باستخدام XLOOKUP
Excel 2019 أو 2016 تتطلب هذه الصيغ إصدارًا أحدث

تدرج Microsoft إصداري Microsoft 365 for Mac وExcel 2024 for Mac في وثائق HSTACK. وتؤكد وثائق ميزات Excel 2021 for Mac دعم XLOOKUP والمصفوفات الديناميكية. وتستثني وثائق XLOOKUP صراحةً Excel 2016 و2019.

إذا كان لديك Excel 2021، فاستخدم طريقة الأعمدة المنفصلة أدناه. فهي تحقق النتيجة نفسها لإثراء البيانات من دون الحاجة إلى HSTACK.

إعداد بيانات المخزون والبيانات الوصفية باستخدام معرّف أصل مشترك

أنشئ ورقتي عمل في المصنف نفسه وسمّهما Inventory وMetadata. أدخل بيانات المخزون التالية في Inventory!A1:B6. في هذا المثال، اترك هذه الخلايا نطاقًا عاديًا في ورقة العمل.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

أدخل البيانات الوصفية في Metadata!A1:D4:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

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

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

تحقّق من وجود تطابق واحد بالضبط لكل معرّف في المخزون

في Inventory، أدخل MetadataMatches في C1. أدخل هذه الصيغة في C2، ثم انسخها حتى C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

فسّر النتيجة قبل إنشاء الناتج:

  • 0: معرّف الأصل غير موجود في ورقة البيانات الوصفية.
  • 1: للأصل صف واحد من البيانات الوصفية، كما تتوقع هذه العملية.
  • أي رقم أكبر من 1: تحتوي البيانات الوصفية على معرّفات مكررة يجب حلّها.

تحسب COUNTIF عدد الخلايا التي تستوفي معيارًا. في البيانات المقدمة، تُرجع كل عمليات التحقق 1. استخدم معرّفات بسيطة مثل المعروضة هنا: تتعامل COUNTIF مع النجوم وعلامات الاستفهام بوصفها أحرف بدل، لذا تتطلب المعرّفات التي تحتوي على هذه الأحرف معالجة إضافية. Use the COUNTIF function in Microsoft Excel

عالج معرّفات البيانات الوصفية المكررة قبل المتابعة. تُرجع XLOOKUP أول تطابق افتراضيًا، لذا لا تكفي النتيجة المعبأة لإثبات تفرد مفتاح البيانات الوصفية. XLOOKUP function

إنشاء مصفوفة واحدة مُثرية في Microsoft 365 أو Excel 2024

أنشئ ورقة عمل باسم Enriched. أدخل العناوين الخمسة التالية في A1:E1: AssetID وQuantity وTitle وFormat وPalette. اترك A2:E6 فارغة.

حدد Enriched!A2، وأدخل الصيغة كاملة أدناه، ثم اضغط Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

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

تطلب القيمة الأخيرة 0 في كل عملية بحث تطابقًا تامًا. ويُعرض النص MISSING METADATA عند عدم العثور على معرّف مطابق. يساعدك إبقاء هذه القيمة ظاهرة على تحديد التطابقات الناقصة بدلًا من ترك خلية فارغة بلا تفسير. XLOOKUP function

يشغل الناتج المتوقع النطاق Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

لاحظ أن قيدي ARC-01 يظلان موجودين، بكميتي 3 و2. ويحصل كلاهما على Arc Study وVector وTeal. لقد أثرت البيانات الوصفية هذين القيدين من دون دمجهما.

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

استخدام ثلاثة أعمدة بحث في Excel 2021 for Mac

في Excel 2021، اعمل مباشرةً بجوار المخزون الأصلي. اترك فحص عدد التطابقات في العمود C، ثم أدخل Title وFormat وPalette في D1:F1. اترك D2:F6 فارغة.

أدخل هذه الصيغة في Inventory!D2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

أدخل هذه الصيغة في Inventory!E2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

أدخل هذه الصيغة في Inventory!F2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

اضغط Return بعد كل صيغة. تملأ كل نتيجة خمسة صفوف أسفل خلية بدايتها. تبقى معرّفات الأصول وكمياتها في العمودين A:B، ويبقى عدّاد التشخيص في C، وتظهر البيانات الوصفية المضافة في D:F. يتضمن Excel 2021 for Mac سلوك المصفوفات الديناميكية اللازم لهذا الأسلوب. What’s new in Excel 2021 for Mac

تكييف المصنف لاستيعاب مزيد من صفوف المخزون

تستخدم الأمثلة نطاقات ثابتة كي يكون كل مرجع ظاهرًا. عندما يكبر مخزونك، وسّع كل مرجع للمخزون إلى الصف الأخير نفسه. على سبيل المثال، إذا انتهى المخزون في الصف 501، فستحتاج الصيغة المجمّعة إلى Inventory!A2:B501 وثلاثة مراجع لـInventory!A2:A501. وسّع فحص عدد التطابقات حتى الصف نفسه.

عندما تكبر البيانات الوصفية، وسّع جميع مراجعها معًا. إذا أصبح صفها الأخير 101، فاستخدم Metadata!A2:A101 للمفاتيح ونطاقات الإرجاع المقابلة B2:B101 وC2:C101 وD2:D101. أدرج السجلات الفعلية بدلًا من إضافة كتلة كبيرة من الصفوف غير المستخدمة.

إذا كان المصنف ينمو بانتظام، ففكّر في تحويل النطاقات المصدر إلى جداول Excel واستخدام المراجع المنظمة. توضح Microsoft أن هذه المراجع تتكيف عند إضافة صفوف المصدر أو حذفها. أبقِ الناتج الممتد في شبكة ورقة العمل خارج أي جدول. Dynamic array formulas and spilled array behavior

إذا كنت تحتاج إلى إثراء البيانات داخل جدول Excel، فاستخدم صيغة منفصلة تُرجع نتيجة واحدة لكل عمود بيانات وصفية ولكل صف في المخزون. تدعم الجداول الأعمدة المحسوبة التي تطبق صيغة على العمود بأكمله؛ أما النتيجة الممتدة متعددة الخلايا فمكانها خارج الجدول. Overview of Excel tables

تشخيص المشكلات والتحقق من النتيجة النهائية

إذا عرض Excel الخطأ #SPILL!، فافحص خلايا الوجهة المقصودة. انقل أي محتوى موجود تريد الاحتفاظ به، وتأكد من أن الصيغة خارج جدول Excel. لا يمكن تحرير سوى الخلية العلوية اليسرى في النتيجة الممتدة؛ غيّر الصيغة فيها. توثّق Microsoft حدود الامتداد وأخطاء عرقلة الخلايا. Dynamic array formulas and spilled array behavior

إذا ظهر MISSING METADATA في صف، فارجع إلى فحص عدد التطابقات الخاص به. تعني القيمة صفرًا أنك بحاجة إلى إضافة سجل البيانات الوصفية الناقص أو تصحيح معرّف الأصل. تحقّق من وجود مسافات بادئة أو لاحقة غير مقصودة إذا لم تتصرف التسميات المتطابقة بصريًا كما تتوقع؛ إذ تحدد Microsoft هذه المسافات سببًا لنتائج COUNTIF غير المتوقعة. Use the COUNTIF function in Microsoft Excel

أبقِ ورقتي المصدر في المصنف نفسه لهذا الإعداد. إذا نقلت المصدر لاحقًا إلى مصنف آخر، فضع في الحسبان القيد الذي توثقه Microsoft: تتطلب المصفوفات الديناميكية المرتبطة فتح المصنفين، وقد يؤدي التحديث مع إغلاق المصنف المصدر إلى ظهور #REF!. Dynamic array formulas and spilled array behavior

تحقّق من المثال الاصطناعي باستخدام ثلاثة فحوص ملموسة:

  1. هناك خمسة صفوف بيانات في الناتج، وبترتيب المخزون الأصلي.
  2. تبقى الكميات 3 و5 و2 و4 و1، ومجموعها 15.
  3. تحصل المعرّفات المتكررة على بيانات وصفية متطابقة، وتكون نتيجة كل فحوص عدد التطابقات 1.

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

المصادر