وظيفة "INDEX" في Excel: الوصف والتطبيق والأمثلة

حاليا ، اكسلشعبية هي الثانية بعد كلمة. يتيح لك تنفيذ مجموعة واسعة من الحسابات الاقتصادية والإحصائية بسهولة على كمية كبيرة من البيانات. لهذا الغرض ، فإنه يوفر عددًا كبيرًا من الوظائف المدمجة ، بما في ذلك الوظائف الإضافية. بعضها قادر على تنفيذ الإجراءات ، بما في ذلك صفائف البيانات. وظيفة "INDEX" تنتمي إليها أيضًا. في Excel ، يتم استخدامه بشكل منفصل ومع SEARCH ، والذي سيتم وصفه أدناه.

وصف

ترجع الدالة "INDEX" في Excel القيمة (مرجع إلى القيمة) لمحتويات الخلية المحددة بواسطة أرقام الصفوف والأعمدة في الجدول أو النطاق المحدد.

صيغتها بسيطة وتبدو كما يلي: INDEX (صفيف ، رقم صف ، رقم عمود).

يمكن أن تعمل هذه الوظيفة أيضًاصف واحد أو عمود واحد. في هذه الحالة ، بعد تحديد مصفوفة أحادية البعد ، يتم تعيين رقم واحد. يشير إلى رقم الصف إذا كان الصفيف عمودًا ، والعكس صحيح.

تنتج الدالة "INDEX" في Excel أحيانًا القيمة "#REF!". يحدث هذا غالبًا إذا كانت الخلية الموجودة عند تقاطع الصف والعمود المحدد خارج النطاق المحدد.

أمثلة التطبيق

دعونا نفكر في عدة حالات لاستخدام دالة INDEX في الممارسة.

افترض أن هناك صفيف من 4أعمدة و 4 صفوف (انظر الجدول). إذا أدخلت في إحدى خلايا الجدول عبارة "= INDEX (B2: E5، 2، 3)" (بدون علامتي الاقتباس) الموجودة خارج النطاق A1: E5 واضغطت على "Enter" ، فسيتم إرجاع القيمة "بيجونيا".

إذا كنت تريد معرفة عدد طلاب المجموعة 2 الذين حصلوا على تصنيف "غير مُرض" ، فينبغي إدخال العبارة: INDEX (C2: C5، 1) في الخلية المناسبة.

دالة SEARCH في Excel

لن يعمل المثالان أعلاهمع كميات كبيرة من البيانات. والحقيقة هي أن استخدام الدالة "INDEX" في Excel يتضمن إدخال أرقام الصفوف والأعمدة في مصفوفة البيانات وليس الجدول نفسه. يصعب القيام بذلك عندما يتعلق الأمر بعدد كبير من العناصر. يمكن أن تساعد وظيفة Excel أخرى في حل المشكلة.

ضع في اعتبارك الحالة عندما يتكون الصفيف من سلسلة واحدة.

نطاق القيم في هذه الحالة هو B3: B6.

حدد خلية في صف آخر ، على سبيل المثال ، D1.أدخل فيه اسم الفاكهة التي نريد أن نجد مكانها ، في هذه الحالة "البرتقال". في الخلية (E1) ، حيث نريد كتابة رقم السطر المقابل ، أدخل "= SEARCH (D1؛ B3: B6؛ 0)" (انظر الجدول). نتيجة لذلك ، يظهر الرقم 3. هناك مثل هذا الرقم في النطاق B3: B6 للتعبير "البرتقال".

يعني 0 الأخير أنك تريد العثور على تطابق تام بقيمة D1.

كيفية العثور على جميع القيم النصية التي تفي بمعيار معين

في النموذج المقدم أعلاه ، وظيفة "بحث"إرجاع قيمة واحدة فقط (أول قيمة ، أي أعلى). ولكن ماذا لو كان هناك تكرار في القائمة. في هذه الحالة ، تساعد صيغ الصفيف. لاستخدامها ، حدد نطاق البيانات بالكامل واستخدم تركيبة المفاتيح "Ctrl + Shift + Enter". ومع ذلك ، فإن النظر فيها ليس موضوع هذه المقالة.

الوظيفة "INDEX" و "SEARCH" في Excel: أمثلة

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

للقيام بذلك ، من الأفضل استخدام كليهماالمهام. لمعرفة ما تحتاج إلى إدخاله في H2 ، فكر أولاً في أبسط تعبير يمكن استخدامه لهذا الغرض. على وجه الخصوص ، يمكن الحصول على القيمة المطلوبة من خلال الكتابة في هذه الخلية "= INDEX (A2: E5؛ 1؛ 2)". استخدمنا هنا متغيرًا من الأمثلة السابقة ، عندما تم حساب أرقام الصفوف والأعمدة يدويًا. ومع ذلك ، فإن هدفنا هو أتمتة هذه العملية. للقيام بذلك ، بدلاً من اثنين وواحد ، التي تشير إلى الصف والعمود المطلوبين ، في الصفيف ، اكتب الدالات المقابلة "SEARCH" ، وأصدرت هذه الأرقام. يرجى ملاحظة أننا نبحث عن التعبير "ud" الموجود في الخلية G2 و "gr. 2 "من H2. بالإضافة إلى ذلك ، نحن بحاجة إلى مطابقات تامة ، وبالتالي ، في كلتا الحالتين ، الوسيطة الأخيرة والثالثة هي 0.

ثم بدلاً من 1 في صيغة INDEX (A2: E5؛ 1؛ 2) يجب أن تكتب: SEARCH (G2؛ A2: A5؛ 0) وبدلاً من 2 - SEARCH (H2؛ A2: E2؛ 0).

بعد الاستبدال ، لدينا: INDEX (A2: E5؛ SEARCH (G2؛ A2: A5؛ 0)؛ SEARCH (H2؛ A2: E2؛ 0)). نتيجة لذلك ، بالضغط على "أدخل" ، لدينا القيمة "10" في هذه الخلية.

كيفية تمديد تأثير الصيغة التي تم الحصول عليها إلى نطاق معين

كما تعلمون ، يمكن أن تكون الوظيفة "INDEX" في Excel"ممتد" إلى نطاق معين. في المثال أعلاه ، هذه جميعها 4 خلايا من H2: J3. في هذا الصدد ، من الضروري معرفة كيفية التأكد من أنه من خلال "تمديد" هذه الصيغة إلى اليمين والأسفل ، للحصول على القيم الصحيحة.

الصعوبة الرئيسية هي أن الصفيفA2: E5 له عنوان نسبي. لإصلاح ذلك ، يجب عليك تحويله إلى مطلق. للقيام بذلك ، تتم كتابة المصفوفة بالشكل $ A $ 2: $ E $ 5. يجب أن يتم نفس الشيء لكل من الوظائف المضمنة ، أي يجب أن تبدو مثل SEARCH ($ G $ 2 ؛ $ A $ 2: $ A $ 5 ؛ 0) و SEARCH ($ H $ 2 ؛ A $ 2: $ E2 ؛ 0).

سيكون الشكل النهائي للصيغة: INDEX ($ A $ 2: $ E $ 5؛ SEARCH ($ G $ 2؛ $ A $ 2: A $ 5؛ 0)؛ SEARCH ($ H $ 2؛ $ A $ 2: $ E $ 2؛ 0)).

نتيجة لذلك ، سيكون لدينا الجدول الموضح أدناه

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

الآن أنت تعرف كيف يتم استخدام الوظيفة"INDEX" في Excel. تعرف أيضًا أمثلة على استخدامه المشترك مع "SEARCH" ، ويمكنك تطبيقها بشكل صحيح لحل العديد من المشكلات العملية.