Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office
Computers

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

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Arabic (العربية/عربي) translation by Maryam Abbas (you can also view the original English article)

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

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

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

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

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

Excel scripts and plugins on Envato Market
إكسيل البرامج النصية والمكونات الإضافية على سوق إنفاتو

سكرينكاست

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

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

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

unique identifiers for vlookup
يجب أن يكون المحدد الفريد مثل الرقم التسلسلي، حيث لا يوجد اثنان في نفس الجدول.

بناء الجملة

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

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

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

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

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

syntax of vlookup
بناء الجملة من دالة فلوكوب

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

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

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

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

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

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

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

إدخال الصيغة

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

أدخل الصيغة:

=VLOOKUP(A5,data,8,FALSE)

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

entering the vlookup function
إدخال الدالة فلوكوب

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

mouse pointer on the autofill dot
عند وضع مؤشر الماوس على النقطة في الزاوية اليمنى السفلى من الخلية، يصبح تصالب صغير للملء التلقائي.

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

worksheet with data after using autofill
انقر نقرا مزدوجا فوق تصالب الملء التلقائي لنسخ الصيغة أسفل العمود

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

استخدام ماتش

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

بناء الجملة

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

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

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

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

أعلى قيمة تالية

1-

يجب أن تكون القيم بترتيب تنازلي.

قيمة الهدف

0

يمكن أن تكون القيم بأي ترتيب.

القيمة  الادنى التالية

1

النوع الافتراضي. يجب أن تكون القيم بترتيب تصاعدي.

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

name box with one column defined
يمكن أن يكون النطاق المسمى عمود واحد فقط، أو صف واحد فقط، أو حتى خلية واحدة فقط.

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

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

entering the match function
ادخال وظيفة الموافقه

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

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

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

completed match function
وظيفة الموافقة المكتملة

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

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

بناء الجملة

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

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

والوسائط هي:

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

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

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

أدخل الصيغة:

=INDEX(data,A6,B6)

entering the index function
ادخال وظيفة الفهرس

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

الخاتمه

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

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.