KutoolsforOffice — حل واحد، خمس أدوات قوية.تحقيق المزيد بجهد أقل.

كيف تُجري عملية VLOOKUP للأرقام المحفوظة كنص في Excel؟

المؤلفXiaoyangتاريخ التعديل

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

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

لقطة شاشة تُظهر خطأً عند استخدام VLOOKUP بسبب عدم تطابق تنسيقات الأرقام في Excel

بحث القيم (VLOOKUP) للأرقام المحفوظة كنص باستخدام الصيغ

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

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

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

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

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

لقطة شاشة تُظهر عمل VLOOKUP بشكل صحيح باستخدام صيغة VALUE لتوحيد تنسيقات الأرقام

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

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

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

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

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

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

لقطة شاشة تُظهر عمل VLOOKUP بشكل صحيح باستخدام صيغة TEXT لتوحيد تنسيقات الأرقام

هنا، يضمن رمز تنسيق الرقم "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. انقر على «موافق» لتحويل تنسيق البيانات فورًا.
      لقطة شاشة تُظهر عمل VLOOKUP بشكل صحيح باستخدام صيغة TEXT لتوحيد تنسيقات الأرقام

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

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

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

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

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

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

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

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

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 وانقر علىتشغيل.

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

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

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

طرق Excel المدمجة الأخرى: استخدم ‹النص إلى أعمدة› لإصلاح تنسيقات البيانات

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

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

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

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

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

تذكيرات عملية:تقوم ميزة «النص إلى أعمدة» بتعديل البيانات مباشرةً، لذا قد تُستبدل محتويات الخلايا إذا كانت هناك بيانات موجودة في الخلية المجاورة مباشرةً على اليمين. يُفضَّل نسخ عمودك إلى منطقة فارغة أولًا إذا لم تكن متأكدًا، واحفظ دائمًا نسخة احتياطية من ملفك قبل استخدام أدوات معالجة البيانات الجماعية.

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

🤖KUTOOLS AI مساعد: ثوّر تحليل البيانات استنادًا إلى:التنفيذ الذكي   |  إنشاء الكود|  إنشاء الصيغ المخصصة  |  تحليل البيانات وإنشاء المخططات البيانية|  استدعاء الوظائف المحسنة…
الميزات الشائعة:البحث، التمييز أو وضع علامة على المكررات   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |  التقريب بدون استخدام الصيغة...
بحث متقدم:VLookup متعدد المعايير  |  VLookup متعدد القيم  |   VLookup عبر أوراق متعددة   |   مطابقة غامضة....
قائمة منسدلة متقدمة:إنشاء قائمة منسدلة بسرعة   |  قائمة منسدلة تابعة   |  قائمة منسدلة متعددة الاختيار....
مدير الأعمدة:إضافة عدد محدد من الأعمدة|نقل الأعمدة|تبديل حالة ظهور الأعمدة المخفية|مقارنة النطاقات والأعمدة...
ميزات مميزة:التركيز على الشبكة   |  عرض التصميم   |شريط الصيغ المحسن   | مدير الدفتر والورقة   |  مكتبة الموارد(نص تلقائي)|  أداة اختيار التاريخ   |  تجميع ورقات العمل  |  تشفير/فك تشفير الخلايا   | إرسال رسائل البريد الإلكتروني حسب القائمة   |  مرشح متقدم   |   تصفية خاصة(تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
أفضل 15 مجموعات الأدوات:12 أدواتالنصوص(إضافة نص،حذف الأحرف المحددة، ...)|   50+أنواعالمخططات البيانية(مخطط جانت، ...)|   40+ صيغعملية(حساب العمر بناءً على تاريخ الميلاد، ...)|   19 أدواتالإدراج(إدراج رمز QR،إدراج صورة من المسار، ...)|   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.

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