Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

كيفية استخدام VLOOKUP للبحث عن الأرقام المخزنة كنص في Excel؟

Author Xiaoyang Last modified

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

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

A screenshot showing an error when using VLOOKUP due to mismatched number formats in Excel

استخدام VLOOKUP للبحث عن الأرقام المخزنة كنص باستخدام الصيغ

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

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

أدخل الصيغة التالية في الخلية الفارغة حيث تريد عرض النتيجة:

=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)

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

A screenshot showing VLOOKUP working correctly with the VALUE formula to match number formats

شرح المعلمات والنصائح:

  • G1: الخلية التي تحتوي على القيمة التي تريد البحث عنها (يمكن أن تكون نصًا أو رقمًا).
  • A2:D15: نطاق جدول البيانات الخاص بك الذي يتضمن عمود البحث والأعمدة التي تحتوي على المعلومات التي تريد إرجاعها.
  • 2: رقم العمود (من أقصى يسار نطاق الجدول) للنتيجة التي تريد إرجاعها.

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

إذا كانت قيمة البحث رقماً حقيقياً (تنسيق الرقم)، ولكن الحقل المقابل في الجدول مخزن كنص، فأنت بحاجة إلى تحويل الرقم إلى نص قبل إجراء البحث. تُعد دالة TEXT مناسبة لهذا السيناريو:

=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)

أدخل هذه الصيغة في الخلية المستهدفة، واضغط على Enter وسيتم إرجاع النتيجة الصحيحة كما هو موضح أدناه:

A screenshot showing VLOOKUP working correctly with the TEXT formula to match number formats

هنا، يضمن رمز تنسيق الرقم “0” داخل دالة TEXT تحويل الرقم إلى قيمة نصية عادية قبل مطابقته.

إذا لم تكن متأكدًا من التنسيقات المحتملة لقيم البحث الخاصة بك أو تتوقع حدوث كل من النصوص والأرقام في عمود البحث، يمكنك دمج الطريقتين باستخدام دالة IFERROR للتعامل مع جميع الاحتمالات بسلاسة:

=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))

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

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

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

إصلاح سريع للاختلافات في التنسيق باستخدام Kutools لـ Excel

إذا كنت تفضل حلًا أسرع بدون صيغ، يقدم Kutools لـ Excel أداة سهلة الاستخدام تُسمى "تحويل بين النص والرقم". تتيح لك هذه الميزة تحويل الأرقام المخزنة كنص إلى أرقام حقيقية أو العكس ببضع نقرات فقط. وهي مفيدة بشكل خاص عند حل مشكلات التنسيق قبل إجراء عمليات البحث مثل VLOOKUP أو MATCH.

بعد تثبيت Kutools لـ Excel، يرجى القيام بالآتي:

  1. حدد النطاق الذي يحتوي على بياناتك المشكلة (مثل الأرقام المخزنة كنص).
  2. انتقل إلى "Kutools" > "نص" > "تحويل بين النص والرقم".
  3. في مربع الحوار المنبثق:
    1. اختر "النص إلى رقم" إذا كنت تقوم بإصلاح حالات فشل البحث بسبب الأرقام المنسقة كنص.
      (أو حدد "الرقم إلى نص" إذا كانت قيم البحث مخزنة كنص.)
    2. انقر فوق "موافق" لتحويل تنسيق البيانات فورًا.
      A screenshot showing VLOOKUP working correctly with the TEXT formula to match number formats

بعد تحويل النص إلى أرقام، ستصبح الخلايا المحولة أرقامًا حقيقية ولن تظهر مؤشرات مثلثات خضراء تشير إلى التباين.

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

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن

ماكرو VBA: توحيد التنسيقات قبل استخدام VLOOKUP

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

المزايا: أتمتة التنسيق لمجالات البيانات الكبيرة أو سير العمل المتكرر؛ تقليل مخاطر فقدان أو تباين التنسيق؛ مناسبة للمهام المتكررة.
العيوب: غير مناسبة للمستخدمين الذين لديهم قيود على الماكرو أو غير معتادين على استخدام ماكرو VBA.

إليك كيفية استخدام ماكرو لتوحيد تنسيقات الخلايا:

1. انتقل إلى علامة التبويب Developer وانقر على Visual Basic لفتح محرر VBA. في النافذة الجديدة، انقر على Insert > Module، ثم انسخ والصق الكود التالي في منطقة الوحدة:

Sub StandardizeLookupFormats()
    ' Ask the user to select the lookup column and choose a target format
    Dim rng As Range
    Dim userChoice As Integer
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
    If rng Is Nothing Then Exit Sub
    
    userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
    
    If userChoice = vbYes Then
        For Each cell In rng
            If IsNumeric(cell.Value) Then
                cell.Value = Val(cell.Value)
                cell.NumberFormat = "General"
            End If
        Next
    ElseIf userChoice = vbNo Then
        For Each cell In rng
            If Not IsEmpty(cell.Value) Then
                cell.Value = CStr(cell.Value)
                cell.NumberFormat = "@"
            End If
        Next
    Else
        Exit Sub
    End If
End Sub

2. أغلق محرر VBA. لتشغيل الماكرو، عد إلى Excel، واضغط على Alt+F8، حدد StandardizeLookupFormats، وانقر على Run.

تفاصيل العملية والنصائح:

  • سيطلب هذا الماكرو منك تحديد العمود (سواء نطاق LOOKUP أو نطاق TABLE) الذي ترغب في توحيد تنسيقه.
  • بعد التحديد، سيطلب منك تحويل النطاق إلى أرقام (انقر على نعم) أو إلى نص (انقر على لا). حدد نفس التنسيق لكل من عمود البحث والجدول لضمان مطابقة VLOOKUP بشكل موثوق.
  • بعد تشغيل هذا الماكرو، قد تحتاج إلى إعادة حساب ورقة العمل (اضغط على F9) أو إعادة تطبيق صيغ VLOOKUP إذا لم تظهر النتائج فورًا.
  • إذا ظهر خطأ بأن الماكرو معطل، فقم بتفعيل الماكرو في إعدادات Excel قبل المتابعة.

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

طرق أخرى مضمنة في Excel: استخدام 'تحويل النص إلى أعمدة' لإصلاح تنسيقات البيانات

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

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

لاستخدام هذه الطريقة لتحويل الأرقام المخزنة كنص (أو العكس) في عمود:

  • حدد العمود الذي يحتوي على التنسيق غير المتطابق المشتبه به (مثل عمود البحث أو العمود المشار إليه بواسطة VLOOKUP).
  • على علامة التبويب Data، انقر على Text to Columns.
  • في معالج التحويل، اختر Delimited، ثم انقر على Next.
  • ألغِ تحديد جميع خانات الخيارات الخاصة بالفاصل (حيث أنك لا تقوم بتقسيم البيانات)؛ انقر على Next.
  • في تنسيق بيانات العمود، حدد General (لإجبار Excel على التعرف على الأرقام كأرقام) أو حدد Text (لتحويل الأرقام إلى نص).
  • انقر على Finish لإكمال العملية.

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

تذكيرات عملية: يعدل "Text to Columns" البيانات مباشرة، لذلك قد يؤدي إلى الكتابة فوق محتويات الخلية إذا كان لديك بيانات موجودة على الفور إلى اليمين. فكر في نسخ العمود الخاص بك إلى منطقة فارغة أولاً إذا لم تكن متأكدًا، واحرص دائمًا على حفظ نسخة احتياطية من ملفك قبل استخدام أدوات معالجة البيانات بالجملة.

أفضل أدوات الإنتاجية لمكتب العمل

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات اعتمادًا على: التنفيذ الذكي |  توليد الأكواد |  إنشاء الصيغ المخصصة |  تحليل البيانات وتوليد الرسوم البيانية |  استخدام Kutools Functions...
ميزات شائعة: البحث، تمييز أو وضع علامة على المكررات | حذف الصفوف الفارغة | دمج الأعمدة أو الخلايا بدون فقدان البيانات | تقريب بدون الصيغة...
بحث متقدم: بحث بمعايير متعددة VLookup | بحث بقيم متعددة VLookup | بحث في عدة ورقات VLookup | مطابقة غامضة...
قائمة منسدلة متقدمة: إنشاء قائمة منسدلة بسرعة | قائمة منسدلة معتمدة | قائمة منسدلة متعددة الاختيارات...
مدير الأعمدة: إضافة عدد محدد من الأعمدة | نقل الأعمدة | تبديل حالة إظهار الأعمدة المخفية | مقارنة النطاقات والأعمدة...
ميزات مميزة: التركيز على الشبكة | عرض التصميم | شريط الصيغ المحسن | مدير أدوات المصنف وورقة العمل | مكتبة النص التلقائي | منتقي التاريخ | دمج البيانات | تشفير/فك تشفير الخلايا | إرسال البريد الإلكتروني حسب الجدول | مرشح متقدم | تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...)...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ...) | أكثر من50 نوع رسم بياني (مخطط جانت، ...) | أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...) |19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ...) |12 أداة تحويل (تحويل إلى كلمات، تحويل العملة، ...) |7 أدوات دمج وتقسيم (دمج متقدم للصفوف، تقسيم الخلايا، ...) | ... والمزيد
استخدم Kutools بلغتك المفضلة – يدعم الإنجليزية، الإسبانية، الألمانية، الفرنسية، الصينية، وأكثر من40 لغة أخرى!

عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...

Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير

  • تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
  • افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
  • يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!

جميع إضافات Kutools. مثبت واحد

حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.

Excel Word Outlook Tabs PowerPoint
  • حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
  • مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
  • الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
  • تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
  • قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد