كيفية استخراج البيانات من جدول بيانات باستخدام فلوكوب، و ماتش و إندكس

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

دالات ماتش و إندكس جيدة للاستخدام عندما تقلق من موقع بيانات محددة، مثل العمود أو الصف الذي يحتوي على اسم الشخص.

خيارات متميزة

قبل أن ندخل في دالات إكسيل، هل تعلم أن سوق إنفاتو لديها مجموعة من البرامج النصية إكسيل والمكونات الإضافية التي تمكنك من أداء وظائف متقدمة؟

على سبيل المثال، يمكنك:

• تصدير بيانات وردبريس إلى إكسيل
• تحليل واسترجاع البيانات من إكسيل باستخدام فئة PHP 
• تحويل جدول بيانات إكسيل إلى جدول هوتميل سريع الاستجابة
• تحويل ملفات إكسيل إلى جدول بيانات .NET 
• وأكثر بكثير

سكرينكاست

How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX

إذا كنت ترغب في المتابعة مع هذا البرنامج التعليمي باستخدام ملف إكسيل الخاص بك، يمكنك القيام بذلك. أو إذا كنت تفضل ، قم بتنزيل ملف مضغوط يشمل هذا البرنامج التعليمي، والذي يحتوي على مصنف نموذجي يسمى vlookup example.xlsx.

استخدام فلوكوب

عندما يجد فلوكوب المحدد الذي تحدده في بيانات المصدر، فإنه يمكن بعد ذلك العثور على أي خلية في هذا الصف وإرجاع المعلومات لك. لاحظ أنه في بيانات المصدر، يجب أن يكون المحدد في العمود الأول من الجدول.

بناء الجملة

بناء جملة الدالة فلوكوب هو:

= فلوكوب (قيمة البحث، نطاق الجدول، رقم العمود، [صحيح / خطأ])

إليك ما تعنيه هذه الحجج:

• ابحث عن القيمة. الخلية التي لها المحدد الفريد.
• نطاق الجدول. نطاق الخلايا التي لها المحدد في العمود الأول، متبوعا ببقية البيانات في الأعمدة الأخرى.
• رقم العمود. رقم العمود الذي يحتوي على البيانات التي تبحث عنها. لا تخلط مع المعنى الحرفي للعمود. وفي الرسم التوضيحي أعلاه، ترد الحالات في العمود 4.
• خطأ /صحيح. هذه الحجة اختيارية. صحيح يعني أن المطابقة التقريبية مقبولة، وتعني "خطأ" أن المطابقة التامة فقط مقبولة.    

نريد العثور على مبالغ المبيعات من الجدول الموضح في الرسم التوضيحي أعلاه، لذلك نستخدم هذه الحجج:

حدد اسم نطاق لإنشاء مرجع مطلق

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

تعريف اسم نطاق في إكسيل

1. قبل إدخال الصيغة، انتقل إلى ورقة عمل بيانات المصدر.
2. حدد جميع الخلايا من A4 (الرئيسي من  الأمر #العمود) وصولا إلى H203.  وهناك طريقة سريعة للقيام بذلك هي النقر فوق A4، ثم اضغط على كنترول-شيفت-إند (كوماند-شيفت-إند على ماك).
3. انقر داخل مربع الاسم أعلى العمود A (يعرض مربع الاسم الآن A4).
4. اكتب البيانات، ثم اضغط على إنتر.
5. يمكنك الآن استخدام بيانات الاسم في الصيغة بدلا من $A$4:$H$203.

تعريف اسم النطاق في جداول بيانات غوغل

في جداول بيانات غوغل، تحديد اسم مختلف قليلا.

1. انقر على رأس العمود الأول لبيانات المصدر، ثم اضغط على كنترل-شيفت-السهم الايمن (كوماند-شيفت- السهم الايمن على ماك). يؤدي ذلك إلى تحديد صف رؤوس الأعمدة.
2. اضغط على كترول-شيفت-سهم للاسفل (كوماند-شيفت- سهم للاسفل على ماك). الذي يختار البيانات الفعلية.
3. انقر على قائمة البيانات، ثم حدد النطاقات المسماة والمحمية.
4. في مربع الاسم والنطاقات المحمية على اليمين، اكتب البيانات، ثم انقر على تم.
   

إدخال الصيغة

لإدخال الصيغة، انتقل إلى ورقة عمل مبالغ المبيعات وانقر في B5.

أدخل الصيغة:

=VLOOKUP(A5,data,8,FALSE)

اضغط على إنتر.

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

انقر نقرا مزدوجا فوقه لملء القيم أسفل العمود.

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

استخدام ماتش

لا تقوم الدالة ماتش بإرجاع قيمة البيانات إليك؛ يمكنك تقديم القيمة التي تبحث عنها، وتقوم الدالة بإرجاع موضع تلك القيمة. انها مثل من يسأل أين هو # 135 في الشارع الرئيسي  ، والحصول على الجواب أنه المبنى الرابع في اخر الشارع.

بناء الجملة

بناء جملة الدالة ماتش هو:

= ماتش (قيمة البحث، نطاق الجدول، [نوع المطابقة])

والمناقشات هي:

• ابحث عن القيمة. الخلية التي لها المعرف الفريد.
• نطاق الجدول. نطاق الخلايا التي تبحث عنها.
• نوع الموافقة. اختياري إنها كيفية تحديد مدى قرب المطابقة التي تريدها، كما يلي:

كما هو الحال مع الدالة فلوكوب، سوف تجد على الأرجح وظيفة ماتش أسهل للاستخدام إذا قمت بتطبيق اسم نطاق.  انتقل إلى ورقة بيانات المصدر، وحدد من B4 (رأس العمود للترتيب #) إلى الأسفل، وانقر في مربع الاسم أعلى العمود A، وادعوه رقم الترتيب. لاحظ أن القيم بترتيب تصاعدي.

انتقل إلى علامة التبويب مطابقة في ورقة العمل. في B5، أدخل الدالة ماتش:

= ماتش (A5، order_number، 1)

إذا لم تكن تحدد اسم نطاق، يمكنك كتابة الدالة على النحو التالي:

=MATCH(A5,'Source Data'!A5:A203,0)

=MATCH(A5,'Source Data'!A5:A203,0)في كلتا الحالتين، يمكنك أن ترى أن هذا في الموقع 14 (مما يجعلها بالترتيب ال 13).

استخدام إندكس

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

بناء الجملة

بناء جملة الدالة إندكس هو:

= إندكس (نطاق البيانات، رقم الصف، [رقم العمود])

والوسائط هي:

• نطاق البيانات. تماما مثل الدالتين الأخريين، هذا هو جدول البيانات.    
• رقم الصف. رقم صف البيانات، وهو ليس بالضرورة صف ورقة العمل. إذا بدأ نطاق الجدول في الصف 10 من الورقة، فهذا الصف # 1.
• رقم العمود. رقم عمود نطاق البيانات. إذا بدأ النطاق في العمود E ، فهذا هو العمود رقم 1.

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

انتقل إلى ورقة الفهرس من المصنف وانقر في C6.  نريد أولا أن نجد ما هو وارد في الصف 9، العمود 3 من الجدول. في الصيغة، سنستخدم اسم النطاق الذي أنشأناه في وقت سابق.

أدخل الصيغة:

=INDEX(data,A6,B6)

ويعود للاسم الأخير للعميل: ستريفيل. غير قيم A6 و B6، وسوف تظهر النتيجة في C6 نتائج مختلفة (لاحظ أن العديد من الصفوف لها نفس الحالات وأسماء المنتجات).

الخاتمه

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