استخدم INDEX و MATCH لاستعلامات قاعدة البيانات البسيطة في Excel

في الأصل ، لم يكن Excel مصممًا ليكون قاعدة بيانات حقيقية. كانت وظائف قاعدة البيانات في وقت مبكر محدودة في الكمية والنوعية. ونظرًا لأن كل سجل في قاعدة بيانات Excel يكون مرئيًا على الشاشة في وقتٍ واحد ، وهو ما يعني وجود كل شيء في الذاكرة في وقت واحد ، يجب أن تكون قواعد بيانات Excel صغيرة جدًا: حقول متعددة تحتوي على سجلات قليلة ، أو حقول قليلة بها الكثير من السجلات ؛ والحد الأدنى للحسابات.

VLOOKUP (عمودي) و HLOOKUP (أفقي) كانت الوظائف الوحيدة المتاحة للاستعلام عن قاعدة بيانات للحصول على معلومات محددة. على سبيل المثال ، يمكنك الاستعلام للبحث عن واستخراج جميع السجلات التي تحتوي على مبيعات أكبر من $ 1000 ولكن أقل من $ 5000 - ولكن فقط على الملفات المسطحة (فقط مصفوفة قاعدة بيانات واحدة).

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

[المزيد من القراءة: يحتاج جهاز الكمبيوتر الجديد لديك إلى 15 برنامجًا ممتازًا وممتازًا]

فهرس ومطابقة ومطابقة الفهرس

في Excel ، ترجع الدالة INDEX عنصرًا من موضع محدد (في قائمة ، جدول ، قاعدة بيانات).

تقوم دالة MATCH بإرجاع موضع القيمة (في قائمة والجدول وقاعدة البيانات). ووظائف INDEX-MATCH المستخدمة معًا تجعل استخراج البيانات من جدول نسيمًا.

صيغة بناء الدالة INDEX هي: INDEX (صفيف ، row_num ، [column_num]). المصفوفة هي نطاق الخلايا التي تعمل بها. من الواضح أن Row_num هو رقم الصف في النطاق الذي يحتوي على البيانات التي تبحث عنها. Column_num هو رقم العمود في النطاق الذي يحتوي على البيانات التي تبحث عنها. لا تتعرف صيغة INDEX على أحرف الأعمدة ، لذلك يجب عليك استخدام الأرقام (العد من اليسار).

الصيغة الخاصة بوظيفة MATCH هي: MATCH (lookup_value ، lookup_array ، [match_type]). إن lookup_value هو الرقم أو النص الذي تبحث عنه ، والذي يمكن أن يكون قيمة أو قيمة منطقية أو مرجع خلية. يمثل lookup_array نطاق الخلايا التي تعمل بها. تحدد match_type وظيفة MATCH ، أي مطابقة تامة أو أقرب مطابقة.

A. دالة INDEX

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

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

2. أدخل الدالة INDEX (مسبوقة بعلامة يساوي) ، بالإضافة إلى قوس فتح ، ثم حدد (أو اكتب) نطاق قاعدة البيانات / الجدول كما يلي: = INDEX (A2: I16

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

3. بعد ذلك ، أدخل فاصلة لفصل الوسيطات (أي أجزاء الصيغة المنفصلة) ؛ ثم أدخل رقم الصف وفاصلة ، متبوعًا برقم العمود (نعم ، يجب أن يكون رقمًا وليس حرف العمود المعتاد) والقوس الأيمن (أو اضغط فقط على Enter والسماح لبرنامج Excel بإضافة قوس النهاية لك) .تبدو الصيغة الكاملة كما يلي: = INDEX ($ A $ 2: $ I $ 16 ، 15 ، 2).

ملاحظة: يبدأ ترقيم الصف بالرقم الأول في النطاق ، وليس أول رقم في جدول البيانات. على سبيل المثال ، على سبيل المثال ، على الرغم من أن سفينة القراصنة كافاليريا موجودة في صف إكسل 16 ، فهي في الواقع صف 15 في صيغتنا لأن نطاقنا يبدأ على A2 ويمر عبر I16. إذا كان A2 هو الصف 1 ، فإن A16 هو الصف 15).

4. لاحظ أن نوع السفينة Norrington التي تبحث عنها عبارة عن War Sloop.

JD Sartain

استخدم وظيفة الفهرس لتحديد موقع معلومات محددة في قاعدة البيانات الخاصة بك.

B. يتراوح مؤشر INDEX

الآن ، يمكننا القيام بالكثير مع جدول قاعدة البيانات هذا. لست بحاجة إلى إعادة تعريف النطاق في كل مرة تريد فيها معرفة شيء ما. لتسهيل الأمر ، سنحدد النطاق مرة واحدة ثم نسميه. ثم يمكننا فقط وضع اسم النطاق في صيغنا.

1. انتقل إلى A2 وقم بتمييز النطاق من A2 خلال I16.

2. من علامة التبويب صيغ ، حدد تحديد اسم من مجموعة الأسماء المعرفة.

3. في مربع الحوار المنبثق ، أدخل اسمًا لنطاقك في مربع حقل الاسم.

4. بعد ذلك ، أدخل النطاق (حيث يوجد النطاق) ، وهو إما المصنف أو إحدى أوراق العمل في المصنف.

5. أدخل تعليقًا ، إذا لزم الأمر.

6. وأخيرًا ، تحقق من أن الحقل Refers To يعرض الاسم والنطاق الصحيحين ، ثم انقر فوق موافق .

7. إذا كنت تريد التحقق من أن نطاقك محفوظ بالفعل في Excel ، فجرّب هذا الاختبار الصغير: اضغط Ctrl + G (أمر GoTo). حدد سفن في مربع حوار GoTo ، ثم انقر فوق موافق ، ويقوم Excel بإعادة تمييز النطاق A2: I16.

كيفية تحديد نطاق وحفظه

C. INDEX مع صيغ SUM & AVERAGE

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

1. = SUM (INDEX (Ships،، 8)) يساوي 334 ، إجمالي عدد المدافع ، و

2. = AVERAGE (INDEX (السفن ، 8)) يساوي 22.27 ، أو حوالي 22.27 مدفع لكل سفينة.

3. = SUM (INDEX (السفن ، 7)) يساوي 2350 ، إجمالي عدد جميع الطاقم على جميع السفن.

لماذا توجد فاصلة ، فضاء ، فاصلة بين السفن والرقم 8 ، وماذا تعني هذه الأرقام؟ السفن هي النطاق (متبوعًا بفاصلة) ، وسيطة الصف فارغة (أو مسافة) لأن نورنجتون يريد كل الصفوف ، ويمثل الرقم 8 العمود الثامن (وهو العمود H ، مدافع).

قد يسأل البعض ، لماذا لا تدخل فقط صيغ SUM و / أو AVERAGE في أسفل تلك الأعمدة؟ في هذا الجدول الصغير ، نعم ، سيكون الأمر بنفس السهولة. ولكن إذا كان جدول البيانات يحتوي على 5000 صف و 300 عمود ، فستحتاج إلى استخدام المعادلات INDEX

JD Sartain

03 INDEX باستخدام SUM و AVERAGE.

بمجرد تسمية النطاق ، يمكن لـ Norrington فتح جدول بيانات فارغ في هذا المصنف نفسه واكتب استعلاماته (الصيغ) في العمود B (الذي يعرض النتائج بدلاً من الصيغ) مع وصف يحدد تلك الاستعلامات في العمود A. (ملاحظة: يعرض العمود C الصيغ الفعلية الموجودة في العمود B).

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

JD Sartain

04 Commodore James Norrington's pirate ship information / query sheet.

D. INDEX MATCH مع MAX

الآن ، يريد Norrington معرفة عدد القراصنة على متن السفينة الأكثر كثافة سكانية ، وما هي السفينة؟ يستخدم INDEX مع MAX الصيغة للحصول على أكبر عدد من القراصنة ، لكنه يحتاج أيضا إلى معرفة أي سفينة تحملها. لذلك يستخدم INDEX / MATCH مع صيغة MAX لمعرفة أي سفينة لديها أكثر القراصنة على متنها.

1. = MAX (INDEX (السفن ، و 7)) يساوي 300 ، وهو أعلى عدد من القراصنة على واحدة من السفن

2. = INDEX ($ A $ 2: $ A $ 16 ، MATCH (MAX (السفن) ، $ G $ 2: G $ 16، 0)) تساوي Royal James ، السفينة التي بها معظم القراصنة على متن

3. = INDEX ($ F $ 2: $ F $ 16، MATCH (MAX (Ships)، $ G $ 2: G $ 16، 0)) يساوي Stede Bonnet، Captain of the Royal James مع طاقم قرصنة من 300

JD Sartain

استخدم INDEX-MATCH و MAX لاسترداد معلومات محددة من قاعدة البيانات.