الدالة INDEX في Microsoft Excel
واحدة من أكثر الميزات المفيدة لبرنامج Excel هي مشغل INDEX. وهي تبحث عن البيانات في النطاق عند تقاطع الصف والعمود المحددين ، مما يؤدي إلى إرجاع النتيجة إلى خلية محددة مسبقًا. ولكن يتم الكشف عن الاحتمالات الكاملة لهذه الوظيفة عند استخدامه في صيغ معقدة في تركيبة مع المشغلين الآخرين. دعونا النظر في مختلف الخيارات لتطبيقه.
محتوى
باستخدام وظيفة INDEX
يشير عامل التشغيل INDEX إلى مجموعة من الوظائف من الفئة "المراجع والمصفوفات" . لديها نوعين: لصفائف والمراجع.
يحتوي المتغير للصفائف على الصيغة التالية:
=ИНДЕКС(массив;номер_строки;номер_столбца)
في هذه الحالة ، يمكن استخدام الوسيطتين الأخيرتين في الصيغة ، إما معًا ، أو أيًا منهما ، إذا كان الصفيف أحادي البعد. للحصول على نطاق متعدد الأبعاد ، يجب استخدام كلتا القيمتين. وينبغي أيضا أن تؤخذ في الاعتبار أنه تحت عنوان الصف ورقم العمود لا يعني الرقم على إحداثيات الورقة ، ولكن الترتيب داخل الصفيف المحدد.
تبدو صيغة صيغة المرجع كما يلي:
=ИНДЕКС(ссылка;номер_строки;номер_столбца;[номер_области])
يمكنك استخدام وسيطة واحدة فقط من اثنتين هنا: "رقم السطر" أو "رقم العمود" . تعد وسيطة "Area number" اختيارية بشكل عام ويتم تطبيقها فقط عند تضمين عدة نطاقات في العملية.
وبالتالي ، يقوم المشغل بالبحث عن البيانات في النطاق المحدد عند تحديد صف أو عمود. هذه الوظيفة مشابهة جدا ل مشغل CDF ، ولكن على عكس ذلك يمكن البحث في كل مكان تقريبا ، وليس فقط في العمود الموجود في أقصى اليسار من الجدول.
الطريقة الأولى: استخدام عامل التشغيل INDEX للصفائف
أولاً ، دعونا نلقي نظرة على أبسط مثال لخوارزمية لاستخدام عامل INDEX للصفائف.
لدينا جدول الرواتب. في العمود الأول ، يتم عرض أسماء الموظفين ، في الثانية - تاريخ الدفع ، وفي الثالث - مبلغ الأرباح. نحن بحاجة إلى إخراج اسم الموظف في السطر الثالث.
- حدد الخلية التي سيتم عرض نتيجة المعالجة. نضغط على أيقونة "Insert function" (الوظيفة Insert) التي توضع مباشرة على يسار خط الصيغة.
- هناك إجراء لتنشيط المعالج الدالة . في فئة "المراجع والمصفوفات" لهذه الأداة أو "قائمة أبجدية كاملة" ، نبحث عن اسم "INDEX" . بعد العثور على عامل التشغيل هذا ، حدده وانقر فوق الزر "موافق" الموجود أسفل النافذة.
- يتم فتح نافذة صغيرة تحدد فيها أحد أنواع الدوال: "Array" أو "Link" . نحن بحاجة إلى خيار "صفيف" . وهو موجود أولاً ويتم تمييزه بشكل افتراضي. لذلك نحن بحاجة فقط إلى النقر على زر "موافق" .
- يفتح نافذة الوسائط لوظيفة INDEX . كما ذكر أعلاه ، فإنه يحتوي على ثلاث حجج ، وبالتالي ثلاثة حقول للتعبئة.
في الحقل "صفيف" تحتاج إلى تحديد عنوان نطاق البيانات التي تمت معالجتها. يمكن أن يكون مطروق باليد. ولكن لتبسيط المهمة ، فإننا نمضي بشكل مختلف. ضع المؤشر في الحقل المناسب ، ثم ضع دائرة حول النطاق الكامل للبيانات المجدولة في الورقة. بعد ذلك ، سيظهر عنوان النطاق على الفور في الحقل.
في حقل "رقم الخط" نضع الرقم "3" ، لأننا بشرط أن نعين الاسم الثالث في القائمة. في الحقل "Column Number" ، قم بتعيين الرقم "1" ، لأن العمود الذي يحمل الأسماء هو الأول في النطاق المحدد.
بعد الانتهاء من جميع الإعدادات ، انقر فوق الزر "موافق" .
- يتم عرض نتيجة المعالجة في الخلية التي تم تحديدها في الفقرة الأولى من هذا الدليل. هو الاسم المشتق الذي هو الثالث في القائمة في نطاق البيانات المحدد.
نحن تفكيك استخدام الدالة INDEX في صفيف متعدد الأبعاد (عدة أعمدة وصفوف). إذا كان النطاق ذا بعد واحد ، فسيكون ملء البيانات في نافذة الوسيطة أسهل. في الحقل "صفيف" نفس الطريقة المذكورة أعلاه ، نحدد عنوانها. في هذه الحالة ، يتكون نطاق البيانات فقط من القيم في عمود "الاسم" . في الحقل "رقم الخط" نحدد القيمة "3" ، لأننا بحاجة إلى معرفة البيانات من السطر الثالث. يمكن ترك حقل "رقم العمود" فارغًا على الإطلاق ، نظرًا لأن لدينا نطاقًا أحادي البعد يستخدم فيه عمود واحد فقط. انقر على زر "موافق" .
ستكون النتيجة مطابقة تمامًا لما سبق.
وكان أبسط مثال لك أن ترى كيف تعمل هذه الوظيفة ، ولكن في الممارسة العملية لا يزال يستخدم هذا الإصدار من استخدامها إلا نادرا.
الطريقة 2: التطبيق بالاقتران مع المشغل MATCH
في الممارسة العملية ، غالبًا ما يتم استخدام دالة INDEX بالاقتران مع حجة MATCH . Bundle INDEX - MATCHING هو أداة قوية عند العمل في Excel ، وهو أكثر مرونة من خلال وظيفتها أكثر من نظيرتها الأقرب - مشغل VPR .
تتمثل الوظيفة الرئيسية لدالة MATCH في تحديد الرقم حسب قيمة معينة في النطاق المحدد.
صيغة جملة POSITION هي:
=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- القيمة المطلوبة هي قيمة موضعها في النطاق الذي نبحث عنه ؛
- المصفوفة الممسوحة ضوئياً هي النطاق الذي تقع فيه هذه القيمة ؛
- نوع التعيين هو معلمة اختيارية تحدد ما إذا كنت تريد العثور على القيم بدقة أو تقريبًا. سنبحث عن القيم الدقيقة ، لذلك لا يتم استخدام هذه الوسيطة.
باستخدام هذه الأداة ، يمكنك أتمتة مقدمة الوسيطتين "رقم الخط" و "رقم العمود" في دالة INDEX .
دعونا نرى كيف يمكن القيام بذلك مع مثال ملموس. نحن نعمل مع نفس الجدول كما هو موضح أعلاه. بشكل منفصل ، لدينا حقلين إضافيين - "الاسم" و "المجموع" . من الضروري القيام بذلك ، أنه عند إدخال اسم العامل ، يتم عرض مبلغ المال الذي حصل عليه تلقائيًا. دعونا نرى كيف يمكن تنفيذ ذلك في الممارسة العملية ، وذلك باستخدام وظائف INDEX و MATCH .
- أولاً ، نكتشف نوع الأجور التي يتلقاها موظف في Parfenov DF ، ونحن ندخل اسمه في الحقل المناسب.
- حدد الخلية في حقل "المبلغ" ، حيث سيتم عرض النتيجة النهائية. نبدأ نافذة الوسيطة للدالة INDEX للصفائف.
في حقل "Array" ندخل إحداثيات العمود الذي توجد فيه أجور العاملين.
يتم ترك حقل "رقم العمود" فارغًا ، لأننا نستخدم النطاق أحادي البعد كمثال.
ولكن في حقل "رقم الخط" ، نحتاج فقط إلى كتابة الدالة MATCH . لسجله ، نحن نلتزم بناء الجملة المذكور أعلاه. على الفور في هذا المجال ، ندخل اسم المشغل "SEARCH" بدون علامات اقتباس. ثم افتح على الفور قوس وتعيين إحداثيات القيمة المطلوبة. هذه هي إحداثيات الخلية التي سجلنا عليها بشكل منفصل اسم العامل بارفينوف. ضع فاصلة منقوطة وقم بتحديد إحداثيات النطاق الذي تقوم بعرضه. في حالتنا ، هذا هو عنوان العمود مع أسماء الموظفين. بعد ذلك ، نغلق القوس.
بعد إدخال جميع القيم ، انقر فوق الزر "موافق" .
- يتم عرض نتيجة لمقدار الأرباح Parfenov DF بعد المعالجة في حقل "المبلغ".
- الآن ، إذا كان في حقل "الاسم" نقوم بتغيير المحتوى من "Parfenov DF" ، على سبيل المثال ، "Popova MD" ، عندها تتغير قيمة الأجور في حقل "Sum" تلقائيًا.
الطريقة الثالثة: معالجة جداول متعددة
الآن دعنا نرى كيف يمكن بمساعدة العديد من الطاولات ، بمساعدة مشغل INDEX . لهذه الأغراض ، سيتم تطبيق الوسيطة الإضافية "رقم المنطقة" .
لدينا ثلاثة جداول. كل جدول يوضح أجور الموظفين لمدة شهر واحد. مهمتنا هي معرفة الراتب (العمود الثالث) للموظف الثاني (السطر الثاني) للشهر الثالث (المجال الثالث).
- حدد الخلية التي سيتم إخراج النتيجة وفتح معالج الدالة بالطريقة المعتادة ، ولكن عند اختيار نوع المشغل ، نختار العرض المرجعي. نحتاج هذا لأن هذا النوع يدعم العمل باستخدام الوسيطة "Area number" .
- يفتح نافذة الحجج. في حقل "الرابط" ، نحتاج إلى تحديد عناوين جميع النطاقات الثلاثة. للقيام بذلك ، ضع المؤشر في الحقل وحدد النطاق الأول مع الضغط على زر الماوس الأيسر. ثم نضع فاصلة منقوطة. هذا أمر مهم للغاية ، حيث إذا قمت بالانتقال مباشرة إلى المجموعة التالية ، فسيحل محلها ببساطة محل إحداثيات المجموعة السابقة. لذلك ، بعد إدخال فاصلة منقوطة ، نختار النطاق التالي. ثم ضع فاصلة منقوطة مرة أخرى وحدد المجموعة الأخيرة. كل تعبير موجود في الحقل "مرجع" مدرج بين قوسين.
في حقل "رقم الخط" نشير إلى الرقم "2" ، لأننا نبحث عن اللقب الثاني في القائمة.
في حقل "رقم العمود" ، نشير إلى الرقم "3" ، حيث أن عمود الراتب هو العمود الثالث في كل جدول.
في حقل "رقم المنطقة" نضع الرقم "3" ، حيث نحتاج إلى العثور على البيانات في الجدول الثالث ، والتي تحتوي على معلومات عن الأجور للشهر الثالث.
بعد إدخال جميع البيانات ، انقر على زر "موافق" .
- بعد ذلك ، يتم عرض نتائج الحساب في الخلية المحددة مسبقًا. ويبين مبلغ الأجور للموظف الثاني (Safronov VM) للشهر الثالث.
الطريقة الرابعة: حساب المجموع
لا يتم استخدام النموذج المرجعي في كثير من الأحيان كنموذج مصفوفة ، ولكن يمكن استخدامه ليس فقط عند العمل مع عدة نطاقات ، ولكن أيضًا لاحتياجات أخرى. على سبيل المثال ، يمكن استخدامه لحساب المجموع بالاشتراك مع مشغل SUM .
عند إضافة المجموع ، يحتوي SUM على الصيغة التالية:
=СУММ(адрес_массива)
في حالتنا المحددة ، يمكن حساب مبلغ أرباح جميع الموظفين في الشهر باستخدام الصيغة التالية:
=СУММ(C4:C9)
ولكن يمكنك تعديله قليلاً باستخدام الدالة INDEX . ثم سيبدو مثل هذا:
=СУММ(C4:ИНДЕКС(C4:C9;6))
في هذه الحالة ، في إحداثيات بداية المصفوفة ، تبدأ الخلية التي تبدأ بها. ولكن في إحداثيات الإشارة إلى نهاية الصفيف ، يتم استخدام مشغل INDEX . في هذه الحالة ، تشير الوسيطة الأولى من عامل INDEX إلى النطاق ، والثاني - إلى الأخير - السادس.
كما ترى ، يمكن استخدام الدالة INDEX في Excel لحل مهام متنوعة تمامًا. على الرغم من أننا نظرنا بعيدًا عن كل الخيارات الممكنة لتطبيقه ، ولكن فقط الأكثر شعبية. هناك نوعان من هذه الوظيفة: مرجعية ولصفائف. الأكثر فعالية يمكن استخدامه في تركيبة مع المشغلين الآخرين. يمكن للصيغ التي تم إنشاؤها بهذه الطريقة أن تحل أصعب المشاكل.