هل سبق لك أن كان لديك جدول بيانات كبير يحتوي على بيانات في Excel وتحتاج إلى طريقة سهلة لتصفية واستخراج معلومات محددة منه؟ إذا تعلمت كيفية استخدام VLOOKUP في Excel ، فيمكنك إجراء هذا البحث باستخدام وظيفة Excel^{(Excel function)} واحدة وقوية .

تخيف وظيفة VLOOKUP^{(VLOOKUP function)} في Excel الكثير من الأشخاص لأنها تحتوي على الكثير من المعلمات وهناك طرق متعددة لاستخدامها. ستتعلم في هذه المقالة جميع الطرق التي يمكنك من خلالها استخدام VLOOKUP في Excel ولماذا تكون الوظيفة قوية جدًا.

معلمات VLOOKUP في Excel^{(VLOOKUP Parameters In Excel)}

عندما تبدأ في الكتابة =VLOOKUP( في أي خلية في Excel ، سترى نافذة منبثقة تعرض جميع معلمات الوظائف المتاحة.

دعونا نفحص كل من هذه المعلمات وماذا تعني.

• lookup_value : القيمة التي تبحث عنها من جدول البيانات
• table_array : نطاق الخلايا في الورقة التي تريد البحث من خلالها
• col_index_num : العمود الذي تريد سحب النتيجة منه
• [range_lookup] : وضع المطابقة^{(Match mode)} ( TRUE = approximate ، FALSE = exact )

تتيح لك هذه المعلمات الأربعة إجراء الكثير من عمليات البحث المختلفة والمفيدة عن البيانات داخل مجموعات البيانات الكبيرة جدًا.

مثال بسيط على برنامج Excel VLOOKUP^{(A Simple VLOOKUP Excel Example)}

إن VLOOKUP ليست^{(VLOOKUP isn)} إحدى وظائف Excel الأساسية التي ربما تكون قد تعلمتها ، لذلك دعونا نلقي نظرة على مثال بسيط للبدء.

في المثال التالي ، سنستخدم جدول بيانات كبير من درجات SAT للمدارس في الولايات ^(United) المتحدة^(States) . يحتوي جدول البيانات هذا على أكثر من 450 مدرسة إلى جانب درجات SAT الفردية للقراءة والرياضيات والكتابة. لا تتردد^(Feel) في التنزيل للمتابعة. يوجد اتصال خارجي يقوم بسحب البيانات ، لذلك ستتلقى تحذيرًا عند فتح الملف ، لكنه آمن.

سيستغرق البحث في مجموعة البيانات الكبيرة هذه وقتًا طويلاً للعثور على المدرسة التي تهتم بها.

بدلاً من ذلك ، يمكنك إنشاء نموذج بسيط في الخلايا الفارغة على جانب الجدول. لإجراء هذا البحث ، ما عليك سوى إنشاء حقل واحد للمدرسة^(School) وثلاثة حقول إضافية للقراءة والرياضيات وكتابة الدرجات.

بعد ذلك ، ستحتاج إلى استخدام وظيفة VLOOKUP^{(VLOOKUP function)} في Excel لجعل هذه الحقول الثلاثة تعمل. في حقل القراءة ، قم بإنشاء ^(Reading)وظيفة VLOOKUP^{(VLOOKUP function)} على النحو التالي:

1. اكتب =VLOOKUP(
2. حدد حقل المدرسة^{(School field)} ، والذي في هذا المثال هو I2 . اكتب فاصلة.
3. حدد النطاق الكامل للخلايا التي تحتوي على البيانات التي تريد البحث عنها. اكتب فاصلة.

عند تحديد النطاق ، يمكنك البدء من العمود الذي تستخدمه للبحث (في هذه الحالة ، عمود اسم المدرسة^{(school name column)} ) ، ثم تحديد كافة الأعمدة والصفوف الأخرى التي تحتوي على البيانات.

ملاحظة^(Note) : يمكن لوظيفة VLOOKUP^{(VLOOKUP function)} في Excel البحث فقط من خلال الخلايا الموجودة على يمين عمود البحث^{(search column)} . في هذا المثال ، يجب أن يكون عمود اسم المدرسة^{(school name column)} على يسار البيانات التي تبحث عنها.

1. بعد ذلك ، لاسترداد درجة القراءة^{(Reading score)} ، ستحتاج إلى تحديد العمود الثالث من العمود المحدد في أقصى اليسار. لذلك ، اكتب 3 ثم اكتب فاصلة أخرى.
2. أخيرًا ، اكتب FALSE لمطابقة تامة ، وأغلق الدالة بعلامة أ ) .

يجب أن تبدو وظيفة VLOOKUP^{(VLOOKUP function)} النهائية كما يلي:

=VLOOKUP(I2,B2:G461,3,FALSE)

عندما تضغط أولاً على Enter وتنهي الوظيفة ، ستلاحظ أن حقل القراءة^{(Reading field)} سيحتوي على #N/A A.

هذا لأن حقل المدرسة^{(School field)} فارغ ولا يوجد شيء للعثور على وظيفة VLOOKUP . ^{(VLOOKUP function)}ومع ذلك ، إذا أدخلت اسم أي مدرسة ثانوية تريد البحث عنها ، فسترى النتائج الصحيحة من هذا الصف لدرجة القراءة^{(Reading score)} .

كيفية التعامل مع كون VLOOKUP حساسًا لحالة الأحرف^{(How To Deal With VLOOKUP Being Case- Sensitive)}

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

هذا لأن وظيفة VLOOKUP^{(VLOOKUP function)} حساسة لحالة الأحرف. قد يكون هذا مزعجًا ، خاصة بالنسبة لمجموعة بيانات كبيرة جدًا حيث يكون العمود الذي تبحث فيه غير متوافق مع كيفية كتابة الأشياء بأحرف كبيرة.

للتغلب على هذا ، يمكنك إجبار ما تبحث عنه على التبديل إلى الأحرف الصغيرة قبل البحث عن النتائج. للقيام بذلك ، قم بإنشاء عمود جديد بجوار العمود الذي تبحث عنه. اكتب الوظيفة:

=TRIM(LOWER(B2))

سيؤدي هذا إلى أحرف صغيرة لاسم المدرسة^{(school name)} وإزالة أي أحرف دخيلة (مسافات) قد تكون على الجانب الأيسر أو الأيمن من الاسم.

اضغط باستمرار على مفتاح Shift وضع^{(Shift key and place)} مؤشر الماوس^{(mouse cursor)} فوق الزاوية اليمنى السفلية للخلية الأولى حتى يتغير إلى خطين أفقيين. انقر نقرًا مزدوجًا^(Double) فوق الماوس لملء العمود بالكامل تلقائيًا.

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

الآن بعد أن تم تنظيف جميع بياناتك في هذا العمود الجديد ، قم بتعديل طفيف لوظيفة VLOOKUP^{(VLOOKUP function)} في Excel لاستخدام هذا العمود الجديد بدلاً من العمود السابق عن طريق بدء نطاق البحث^{(the lookup range)} في C2 بدلاً من B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

ستلاحظ الآن أنه إذا كنت تكتب بحثك دائمًا بأحرف صغيرة ، فستحصل دائمًا على نتيجة بحث^{(search result)} جيدة .

هذه نصيحة مفيدة من Excel^{(handy Excel tip)} للتغلب على حقيقة أن VLOOKUP حساس لحالة الأحرف.

مطابقة تقريبية لـ VLOOKUP

في حين أن مثال المطابقة التامة LOOKUP^{(LOOKUP example)} الموضح في القسم الأول من هذه المقالة واضح جدًا ، إلا أن المطابقة التقريبية أكثر تعقيدًا.

أفضل استخدام للمطابقة التقريبية هو البحث من خلال نطاقات الأرقام. للقيام بذلك بشكل صحيح ، يجب فرز نطاق البحث بشكل صحيح. ^{(search range)}أفضل مثال على ذلك هو وظيفة VLOOKUP^{(VLOOKUP function)} للبحث عن تقدير بالحرف^{(letter grade)} يتوافق مع تقدير رقمي^{(number grade)} .

إذا كان لدى المعلم قائمة طويلة من درجات الواجب المنزلي للطالب^{(student homework)} على مدار العام مع عمود متوسط^{(averaged column)} ​​نهائي ، فسيكون من الجيد الحصول على التقدير بالحرف المقابل لتلك الدرجة النهائية تلقائيًا.

هذا ممكن مع وظيفة VLOOKUP^{(VLOOKUP function)} . كل ما هو مطلوب هو جدول بحث^{(lookup table)} موجود على اليمين يحتوي على التقدير المناسب للحرف^{(letter grade)} لكل نطاق درجات^{(score range)} رقمي .

الآن ، باستخدام وظيفة VLOOKUP^{(VLOOKUP function)} والمطابقة التقريبية ، يمكنك العثور على الدرجة المناسبة للحرف المطابق للنطاق الرقمي الصحيح.

في وظيفة VLOOKUP هذه:

• lookup_value : F2 ، التقدير المتوسط ​​النهائي
• table_array : I2: J8 ، نطاق البحث عن الدرجة بالحرف^{(letter grade lookup range)}
• index_column : 2 ، العمود الثاني في جدول البحث^{(lookup table)}
• [range_lookup] : TRUE ، مطابقة تقريبية

بمجرد الانتهاء من وظيفة VLOOKUP^{(VLOOKUP function)} في G2 والضغط على Enter^{(G2 and press Enter)} ، يمكنك ملء بقية الخلايا باستخدام نفس الطريقة الموضحة في القسم الأخير. سترى جميع درجات الأحرف مملوءة بشكل صحيح.

لاحظ أن وظيفة VLOOKUP^{(VLOOKUP function)} في Excel تقوم بالبحث من النهاية السفلية لنطاق التقدير مع ^{(grade range)}درجة الحرف^{(letter score)} المعينة إلى أعلى نطاق درجة الحرف^{(letter score)} التالي .

لذلك ، يجب أن يكون الحرف "C" هو الحرف المخصص للنطاق الأدنى (75) ، ويتم تعيين B إلى أسفل (الحد الأدنى) من نطاق الحرف^{(letter range)} الخاص به . سيجد VLOOKUP^(VLOOKUP) نتيجة 60 (D) كأقرب قيمة تقريبية لأي شيء بين 60 إلى 75.

VLOOKUP في Excel هي وظيفة قوية جدًا كانت متاحة لفترة طويلة. من المفيد أيضًا العثور على قيم مطابقة في أي مكان في مصنف Excel^{(Excel workbook)} .

ومع ذلك ، ضع في اعتبارك أن مستخدمي Microsoft الذين لديهم اشتراك شهري في Office 365 يمكنهم الآن الوصول إلى وظيفة XLOOKUP أحدث. تحتوي هذه الوظيفة على المزيد من المعلمات والمرونة الإضافية. سيحتاج المستخدمون الذين لديهم اشتراك نصف سنوي إلى انتظار طرح التحديث في يوليو 2020^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you ever had a large spreadsheet with data in Excel and need an easy wаy to filter and extract specіfic information from it? If you learn how to usе VLOOKUP in Excеl, you can do this lookup with just a single, powerful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• إضافة خطي Regression Trendline إلى Excel Scatter Plot

• كيف اصنع Histogram في Excel

• كيفية تأمين Password Protect Excel File

• كيفية إنشاء Flowchart في Word and Excel

• كيفية Create Labels في Word من Excel Spreadsheet

• كيفية فصل الأسماء الأولى والأخيرة في إكسيل

• كيفية إنشاء Drop Down List في Excel

• كيفية استخدام Sparklines في Excel

• كيفية Group Worksheets في Excel

• رسم بيانات Excel الخاصة بك

• كيفية استخدام ميزات Excel AutoRecover و AutoBackup

• استخدم نافذة مراقبة Excel لمراقبة الخلايا المهمة في مصنف

• كيفية تغيير Background في Microsoft Teams

• كيفية مقارنة ملفين من ملفات Excel وإبراز الاختلافات

• البرنامج التعليمي لأساسيات Microsoft Excel - تعلم كيفية استخدام Excel

• كيفية إنشاء Distribution List في Outlook

• كيفية Automatically Backup A Word Document إلى onedrive

• دليل لجميع امتدادات ملفات Excel وماذا تعني

• كيفية إضافة وطباعة صور خلفية Excel

• كيفية عمل قائمة مرجعية في Excel