كيفية استخدام VLOOKUP ، INDEX ، ... في Excel يجب أن تعرف

وظائف VLOOKUP و INDEX و MATCH ... هي وظائف معقدة للغاية ولكنها فعالة في استخراج البيانات في Excel . في هذه المقالة ، سأوجهك بالتفصيل إلى كيفية استخدام VLOOKUP والوظائف ذات الصلة بشكل فعال.

1. وظيفة VLOOKUP

الاستخدام : يبحث عن قيمة محددة في العمود الأول ويسحب البيانات المطابقة من نفس الصف في عمود آخر. هذه واحدة من أكثر الوظائف شيوعًا وتوفر دعمًا كبيرًا لمهام المكتب المعقدة.

الصيغة : = VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

فيه :

+ Lookup_value : القيمة المطلوب البحث عنها.

+ Table_array : عمودان أو أكثر من أعمدة البيانات.

+ Col_index_num : عدد الأعمدة لسحب البيانات.

+ Range_lookup : حدد ما إذا كان البحث دقيقًا تمامًا (FALSE) أو دقيقًا نسبيًا (TRUE أو تم حذفه).

مثال : نستخدم وظيفة VLOOKUP للعثور على مكافأة كل موظف. القيمة التي يجب البحث عنها هي Cell Position C2؛ المصفوفة المراد اكتشافها هي المصفوفة F1: G3. عند إدخال الصيغة = VLOOKUP (C2، $ F $ 1: $ G $ 3،2،0) (1) تكون النتيجة هي STAFF 2000 و ROOMER 5000 (2).

2. دالة INDEX

الاستخدام : إرجاع مرجع إلى خلية داخل مصفوفة بناءً على عدد الصفوف والأعمدة التي تحددها.

الصيغة : = INDEX (array، row_num، [column_num])

منها : صفيف (مطلوب): نطاق خلايا أو ثابت صفيف.

مثال : عندما نحتاج إلى الحصول على قيمة الصف الأول ، العمود الثاني في الجدول ، نستخدم دالة INDEX . المصفوفة هي المصفوفة التي نحتاج إلى اكتشافها هي B2: C5 ؛ Row_num هو السطر 1 ؛ Column_num هو العمود 2. عند إدخال الصيغة INDEX (B2: C5،1،2،1) (1) تكون النتيجة "HEAD OF ROOM" (2).

3. وظيفة المباراة

الاستخدام : يبحث عن قيمة معينة في نطاق من الخلايا ، ويظهر الموضع النسبي لتلك القيمة.

الصيغة : = MATCH (lookup_value، lookup_array، [match_type])

فيه :

+ lookup_value (مطلوب) : القيمة التي تريد مطابقتها في صفيف البحث. يمكن أن يكون تغيير رقم قيمة البحث قيمة (رقم أو نص أو قيمة منطقية) أو مرجع خلية إلى رقم أو نص أو قيمة منطقية.

+ lookup_array (مطلوب) : نطاق الخلايا المراد البحث عنها.

+ match_type (اختياري) : رقم -1 أو 0 أو 1. تحدد وسيطة نوع المطابقة كيفية مطابقة Excel لقيمة البحث مع القيم الموجودة في صفيف البحث. القيمة الافتراضية لهذه الوسيطة هي 1.

على سبيل المثال : عندما نحتاج إلى معرفة موضع CLOTH في ، فإننا نستخدم وظيفة MATCH . عند إدخال الصيغة = MATCH (FF2 & "*"، "B2: B7"، 0) (1) ، تكون النتيجة 3 (2).

3. وظيفة غير مباشرة

الاستخدام : إرجاع مرجع خلية أو نطاق محدد بسلسلة نصية. استخدم الدالة INDIRECT عندما تريد تغيير المرجع إلى خلية في صيغة دون تغيير الصيغة نفسها.

الصيغة : = INDIRECT (ref_text، [a1])

فيه :

+ Ref_text (مطلوب) : مرجع إلى خلية تحتوي على نمط المرجع A1 أو نمط المرجع R1C1 أو مرجع للخلية كسلسلة نصية.

+ إذا لم يكن نص المرجع مرجع خلية صالحًا ، تُرجع الدالة INDIRECT قيمة الخطأ #REF! .

+ إذا كانت ref_text تشير إلى مصنف آخر (مرجع خارجي) ، فيجب أن يكون هذا المصنف مفتوحًا. إذا لم يكن المصنف المصدر مفتوحًا ، فتُرجع الدالة INDIRECT قيمة الخطأ #REF! .

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

= غير مباشر ("B2"، TRUE) * غير مباشر ("C2"، TRUE) (1) سيعطي النتيجة 700 * 100 = 70000 (2).

4. وظيفة OFFSET

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

الصيغة : = OFFSET (مرجع ، صفوف ، أعمدة ، [ارتفاع] ، [عرض])

فيه :

+ المرجع (مطلوب) : المنطقة المرجعية التي تريد أن تبني المسافة المرجعية عليها. يجب أن يشير النطاق المرجعي إلى خلية أو نطاق من الخلايا المجاورة ؛ وبخلاف ذلك ، تُرجع الدالة OFFSET قيمة الخطأ #VALUE!. .

+ الصفوف (مطلوب) : عدد الصفوف ، لأعلى أو لأسفل ، التي تريد أن تشير الخلية العلوية اليسرى إليها. يمكن أن تكون الصفوف موجبة (مما يعني أسفل مرجع البداية) أو سالبة (مما يعني أعلى مرجع البداية).

+ الأعمدة (مطلوب) : عدد الأعمدة ، إلى اليسار أو اليمين ، التي تريد أن تشير الخلية الموجودة في الزاوية اليسرى العلوية من النتيجة إلى. يمكن أن تكون الأعمدة موجبة أو سلبية.

+ الارتفاع (اختياري) : الارتفاع ، بعدد الصفوف ، الذي تريده للمرجع المرتجع. يجب أن يكون الارتفاع رقمًا موجبًا.

+ العرض (اختياري) : العرض ، في عدد الأعمدة ، الذي تريده للمرجع المرتجع. يجب أن يكون العرض رقمًا موجبًا.

مثال : نستخدم OFFSET لترجمة القيم ، ثم نستخدم الدالة SUM لحساب المجموع. الخلية التي نشير إليها هي A1 ؛ مقارنةً بـ A1 ، تكون صفوف النطاق المرجعي أقل من خلية واحدة ، وبالتالي فإن الصفوف هي 1 ؛ بالمقارنة مع A1 ، فإن Cols اليسرى هي خلية واحدة ، وبالتالي فإن Cols هي 1 ؛ ارتفاع نأخذ 3 خطوط. العرض نحصل على عمودين. إذن ، النتيجة عند جمع كل القيم في المنطقة هي 2025.

5. وظيفة TRANSPOSE

الاستخدام : يحول نطاقًا أفقيًا من الخلايا إلى نطاق رأسي والعكس صحيح ، أي يحول الصفوف إلى أعمدة وأعمدة إلى صفوف.

الصيغة : = TRANSPOSE (مجموعة)

فعل

+ الخطوة 1 : حدد خلايا فارغة.

+ الخطوة 2 : أدخل = TRANSPOSE (B2: C4).

+ الخطوة 3 : أدخل نطاق الخلايا الأصلية.

+ الخطوة 4 : أخيرًا ، اضغط على CTRL + SHIFT + ENTER.

مثال : عندما نحتاج إلى تحويل قائمة أسعار المنتج بالكامل من عمودي إلى أفقي ، نستخدم وظيفة TRANSPOSE مع مجموعة القيم B2 إلى C4. عند إدخال الصيغة = TRANSPOSE (B2: C4) (1) ستعطي النتيجة كما هو موضح (2).

6. وظيفة HYPERLINK

الاستخدامات : إنشاء ارتباط تشعبي إلى مستند مخزن على الإنترانت أو الإنترنت.

الصيغة : = HYPERLINK (link_location، [friendly_name]).

فيه :

يمكن أن يشير Link_location إلى موقع في مستند ، مثل خلية معينة أو نطاق مسمى في ورقة عمل أو في مصنف Excel ، أو إلى إشارة مرجعية في مستند Microsoft Word.

يمكن أن يكون المسار إلى ملف مخزن على القرص الصلب. يمكن أن يكون المسار أيضًا مسار اصطلاح تسمية عالمي (UNC) على الخادم (في Microsoft Excel لنظام التشغيل Windows).

مثال : الروابط الطويلة والمربكة سوف تكون مزعجة للمستخدم. لذا يرجى استخدام HYPERLINK لتسهيل استخدام الواجهة. إدخال الصيغة = HYPERLINK (A1، “LINK GOOGLE”) (1) سيؤدي إلى LINK GOOGLE (2).

توجه المقالة أعلاه VLOOKUP والوظائف ذات الصلة. آمل أن تكون المقالة أعلاه مفيدة لك.