باستخدام صيغة صفيف في Excel ، يمكننا إنشاء صيغة بحث تستخدم معايير متعددة للعثور على معلومات في قاعدة بيانات أو جدول بيانات. تتضمن صيغة الصفيف تداخل الدالة MATCH داخل دالة INDEX.
يتضمن هذا البرنامج التعليمي مثالاً خطوة بخطوة لإنشاء صيغة بحث تستخدم معايير متعددة للعثور على مورد لـالحاجيات التيتانيوم في نموذج قاعدة البيانات. اتبع على طول لمعرفة مدى سهولة إنشاء صيغ البحث المخصصة الخاصة بك.
تحضير مصنف Excel الخاص بك مع البيانات
لمتابعة الخطوات الواردة في هذا البرنامج التعليمي ، ستحتاج إلى البدء بإدخال نموذج البيانات في الخلايا التالية ، كما هو موضح في الصورة أعلاه. يتم ترك الصفوف 3 و 4 فارغة من أجل استيعاب صيغة الصفيف التي تم إنشاؤها خلال هذا البرنامج التعليمي.
- أدخل النطاق الأعلى للبيانات في الخلايا D1 إلى F2
- أدخل النطاق الثاني في الخلايا D5 إلى F11
لا يتضمن البرنامج التعليمي التنسيق المرئي في الصورة ، ولكن هذا لن يؤثر في كيفية عمل صيغة البحث. تتوفر معلومات حول خيارات التنسيق المشابهة لتلك المذكورة أعلاه في هذا البرنامج التعليمي الأساسي لـ Excel Excel.
02 من 08إنشاء دالة INDEX في Excel
ال فهرس هي إحدى الوظائف القليلة في Excel التي تحتوي على نماذج متعددة. وظيفة لديها نموذج صفيف و نموذج مرجعي. يقوم نموذج الصفيف بإرجاع البيانات الفعلية من قاعدة بيانات أو جدول بيانات ، بينما يعطيك النموذج المرجعي مرجع الخلية أو موقع البيانات في الجدول.
في هذا البرنامج التعليمي ، سوف نستخدم نموذج المصفوفة لأننا نريد أن نعرف اسم المورد لحاجيات التيتانيوم بدلاً من إشارة الخلية إلى هذا المورد في قاعدة البيانات الخاصة بنا. اتبع هذه الخطوات لإنشاء دالة INDEX الخاصة بك:
- انقر فوق الخلية F3 لجعلها الخلية النشطة - هذا هو المكان الذي سنقوم بإدخال الدالة المتداخلة.
- اضغط على الصيغ التبويب من قائمة الشريط.
- أختر البحث والمراجع من شريط الشريط لفتح وظيفة المنسدلة.
- انقر فوق فهرس في القائمة لإحضار حدد الوسيطات صندوق المحادثة.
- اختر ال صفيف ، row_num ، col_num الخيار في مربع الحوار.
- انقر حسنا لفتح مربع حوار الدالة INDEX؛ سيؤدي هذا إلى فتح منشئ الصيغة في Excel.
- في ال منشئ الصيغه، اضغط على مجموعة مصفوفة خط.
- تظليل الخلايا D6 إلى F11، عن طريق النقر والسحب ، في ورقة العمل لإدخال النطاق في المنشئ.
في الإصدارات القديمة من Excel منشئ الصيغه سيتم استبداله من قبل وسيطات الدالة نافذة او شباك. تطبيق نفس الخطوات المذكورة في هذا البرنامج التعليمي في النافذة.
03 من 08بدء تشغيل الدالة MATCH المتداخل
عند تعشيق وظيفة واحدة داخل أخرى ، لا يمكن فتح منشئ صيغة الدالة الثانية أو المتداخلة o قم بإدخال الوسيطات اللازمة. يجب كتابة الدالة المتداخلة كواحدة من الفرز في الدالة الأولى.
عند إدخال الوظائف يدويًا ، يتم فصل وسيطات الدالة عن بعضها بواسطة فاصلة.
الخطوة الأولى في إدخال الدالة MATCH المتداخلة هي إدخال ابحث عن القيمة جدال. سيكون Lookup_value هو الموقع أو مرجع الخلية لمصطلح البحث الذي نطابقه في قاعدة البيانات.
عادةً ما يقبل Lookup_value معيار بحث واحد أو مصطلح واحد فقط. من أجل البحث عن معايير متعددة ، يجب علينا توسيع Lookup_value. تييتم إنجازه عن طريق وصل أو ضم مرجعين للخلية أو أكثر معًا باستخدام رمز العطف - &.
- في ال منشئ الصيغه، اضغط على ROW_NUM خط.
- اكتب اسم الوظيفة مباراة تليها قوس مستدير مفتوح.
- انقر فوق الخلية D3 لإدخال مرجع الخلية هذا في مربع الحوار.
- اكتب علامة العطف بعد مرجع الخلية D3 من أجل إضافة مرجع خلية ثانية.
- انقر فوق الخلية E3 لإدخال مرجع الخلية الثاني هذا.
- اكتب فاصلة بعد مرجع الخلية E3 لإكمال إدخال الدالة MATCH ابحث عن القيمة جدال.
في الخطوة الأخيرة من البرنامج التعليمي ، لدينا Lookup_values سيتم إدخالها في الخلايا D3 و E3 من ورقة العمل.
04 من 08أكمل وظيفة MATCH المتداخلة
هذه الخطوة تشمل إضافة Lookup_array حجة للمتداخلة مباراة وظيفة. Lookup_array هو نطاق الخلايا التي سيتم البحث عن الدالة MATCH للبحث عن الوسيطة Lookup_value المضافة في الخطوة السابقة من البرنامج التعليمي.
نظرًا لأننا حددنا حقلين بحث في وسيطة Lookup_array ، يجب أن نفعل الشيء ذاته لـ Lookup_array. تقوم دالة MATCH بالبحث عن مصفوفة واحدة فقط لكل مصطلح محدد ، وبالتالي لإدخال صفائف متعددة ، نستخدم علامة العطف ثانية لسلسلة المصفوفات معًا.
يجب إدخال هذه الخطوات بعد إدخال الفاصلة في الخطوة السابقة على ROW_NUM خط في فهرس وظيفة.
- اضغط على ROW_NUM بعد الفاصلة لوضع نقطة الإدراج في نهاية الإدخال الحالي.
- تظليل الخلايا D6 إلى D11 في ورقة العمل لدخول النطاق - وهذا هو أول صفيف وظيفة للبحث.
- اكتب علامة العطف بعد مراجع الخلية D6: D11 لأننا نريد أن تبحث الدالة عن صفحتين.
- تظليل الخلايا E6 إلى E11 في ورقة العمل لإدخال النطاق - وهذا هو الصفيف الثاني وظيفة البحث.
- اكتب فاصلة بعد مرجع الخلية E3 لاستكمال دخول مباراة المهام Lookup_array جدال.
- اترك ال فهرس فتح مربع الحوار الوظيفي للخطوة التالية في البرنامج التعليمي.
إضافة الوسيطة نوع MATCH
الوسيطة الثالثة والأخيرة للدالة MATCH هي نوع مباراة جدال؛ يخبر اكسل كيف يتطابق مع ابحث عن القيمة مع القيم في Lookup_array - الخيارات المتاحة هي 1 أو 0 أو -1.
هذه الوسيطة اختيارية. إذا تم حذفها تستخدم الدالة القيمة الافتراضية 1.
- إذا كان Match_type = 1 أو تم إغفال: العثور على MATCH أكبر قيمة أقل من أو يساوي Lookup_value. يجب فرز بيانات Lookup_array بترتيب تصاعدي.
- إذا كان Match_type = 0: تعثر MATCH على القيمة الأولى التي تساوي بالضبط Lookup_value. يمكن فرز بيانات Lookup_array بأي ترتيب.
- إذا كان Match_type = -1: MATCH يبحث عن أصغر قيمة أكبر من أو يساوي Lookup_value. يجب فرز بيانات Lookup_array بترتيب تنازلي.
يجب إدخال هذه الخطوات بعد إدخال الفاصلة في الخطوة السابقة على ROW_NUM خط في فهرس وظيفة.
- بعد الفاصلة على ROW_NUM خط ، اكتب صفر - 0 - نظرًا لأننا نريد أن تقوم الدالة المتداخلة بإرجاع التطابقات التامة مع المصطلحات التي أدخلناها في الخلايا D3 و E3.
- اكتب قوس دائري إغلاق - ) - لاستكمال مباراة وظيفة.
- اترك ال منشئ الصيغه مفتوحة للخطوة التالية في البرنامج التعليمي.
الانتهاء من وظيفة INDEX
الآن بعد أن مباراة تم الانتهاء من الوظيفة إلى السطر الثالث من Formula Builder وأدخل الوسيطة الأخيرة لـ فهرس وظيفة. هذه الحجة الثالثة والأخيرة هي Column_num الوسيطة التي تخبر Excel رقم العمود في النطاق D6 إلى F11. هذا هو المكان الذي ستجد فيه المعلومات التي نرغب في إرجاعها من خلال الوظيفة. في هذه الحالة ، مورد ل الحاجيات التيتانيوم.
- اضغط على Column_num خط.
- أدخل الرقم ثلاثة - 3 - في هذا السطر لأننا نبحث عن بيانات في العمود الثالث للنطاق D6 إلى F11.
- مرة أخرى ، ترك منشئ الصيغه مفتوحة للخطوة التالية في البرنامج التعليمي.
خلق صيغة صفيف
قبل إغلاق منشئ الصيغه، نحتاج إلى تحويل الدالة المتداخلة إلى صيغة صفيف ؛ هذا ما يسمح لوظيفة البحث عن مصطلحات متعددة في جدول البيانات. في هذا البرنامج التعليمي ، نتطلع إلى مطابقة فصلين دراسيين: الحاجيات من العمود 1 و التيتانيوم من العمود 2.
يتم إنشاء صيغة صفيف في Excel بالضغط على CTRL, تحولو أدخل مفاتيح على لوحة المفاتيح في نفس الوقت. وبمجرد الضغط عليه ، ستحيط الوظيفة بأقواس معقوفة ، مما يشير إلى أن الوظيفة أصبحت الآن مصفوفة.
- أقفل ال منشئ الصيغه من خلال النقر على فعله زر.
- المقبل ، حدد الخلية F4 واضغط على أدخل مفتاح لعرض الصيغة.
- لتحويل الصيغة إلى مصفوفة ، اضغط في نفس الوقت CTRL + SHIFT + ENTER على لوحة المفاتيح.
- إذا فعلت بشكل صحيح أ # N / A سيظهر الخطأ في الخلية F3 - الخلية حيث دخلنا الدالة.
- ال # N / A يظهر خطأ في الخلية F3 لأن الخلايا D3 و E3 فارغة. D3 و E3 هما الخلايا التي أخبرنا الدالة أن نجدها Lookup_values. بمجرد إضافة البيانات إلى هاتين الخاليتين ، سيتم استبدال الخطأ بمعلومات من قاعدة البيانات.
إضافة معايير البحث
وأخيرًا ، سنقوم بإضافة مصطلحات البحث إلى ورقة العمل الخاصة بنا. كما ذكرنا في الخطوة السابقة ، نتطلع إلى مطابقة الشروط الحاجيات من العمود 1 و التيتانيوم من العمود 2. إذا وجدت الصيغة الخاصة بنا تطابقًا لكلا المصطلحين في الأعمدة المناسبة في قاعدة البيانات ، فقط ، فستعطي القيمة من العمود الثالث.
- انقر فوق الخلية D3.
- نوع الحاجيات واضغط على أدخل المفتاح على لوحة المفاتيح.
- انقر فوق الخلية E3.
- نوع التيتانيوم واضغط على أدخل المفتاح على لوحة المفاتيح.
- اسم المورد Widgets Inc. يجب أن تظهر في الخلية F3 - موقع الدالة نظرًا لأنها المورد الوحيد المدرج الذي يبيع أدوات Titanium.
- عند النقر فوق الخلية F3 ، تظهر الدالة الكاملة في شريط الصيغة أعلى ورقة العمل.
{= INDEX (D6: F11، MATCH (D3 & E3، D6: D11 & E6: E11، 0)، 3)}
في مثالنا ، لم يكن هناك سوى مورد واحد لحاجيات التيتانيوم. إذا كان هناك أكثر من مورد واحد ، يتم إرجاع المورد المدرجة أولاً في قاعدة البيانات بواسطة الدالة.
