اكسل

ابحث عن قيم متعددة واستبدلها

Find Replace Multiple Values

صيغة Excel: البحث عن قيم متعددة واستبدالهاصيغة عامة
= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))
ملخص

للبحث عن قيم متعددة واستبدالها بصيغة ، يمكنك دمج دالات SUBSTITUTE متعددة معًا ، والتغذية في أزواج البحث / الاستبدال من جدول آخر باستخدام الدالة INDEX. في المثال الموضح ، نقوم بتنفيذ 4 عمليات بحث واستبدال منفصلة. الصيغة في G5 هي:



 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

حيث 'البحث' هو نطاق مسمى E5: E8 ، و 'استبدال' هو النطاق المسمى F5: F8. انظر أدناه للحصول على معلومات حول كيفية تسهيل قراءة هذه الصيغة.

مقدمة

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





تفسير

في جوهرها ، تستخدم الصيغة الدالة SUBSTITUTE لإجراء كل استبدال ، باستخدام هذا النمط الأساسي:

 
= SUBSTITUTE (text,find,replace)

'Text' هي القيمة الواردة ، و 'find' هي النص المطلوب البحث عنه ، و 'replace' هو النص الذي يجب استبداله. يتم تخزين النص المطلوب البحث عنه والاستبدال به في الجدول الموجود على اليمين ، في النطاق E5: F8 ، زوج واحد لكل صف. القيم الموجودة على اليسار في نطاق مسمى 'find' والقيم الموجودة على اليمين موجودة في النطاق المسمى 'replace'. تُستخدم وظيفة INDEX لاسترداد نص 'البحث' ونص 'الاستبدال' مثل هذا:



عد عدد الحالات في التفوق
 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

لذلك ، لإجراء الاستبدال الأول (ابحث عن 'أحمر' ، استبدل بـ 'وردي') نستخدم:

 
= SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1))

في المجموع ، نقوم بتشغيل أربعة استبدالات منفصلة ، ويبدأ كل بديل لاحق بالنتيجة من البديل السابق:

ماذا يعني العمود في Excel
 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

فواصل الأسطر لسهولة القراءة

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

 
=  SUBSTITUTE (  SUBSTITUTE (  SUBSTITUTE (  SUBSTITUTE ( B5,  INDEX (find,1), INDEX (replace,1)),  INDEX (find,2), INDEX (replace,2)),  INDEX (find,3), INDEX (replace,3)),  INDEX (find,4), INDEX (replace,4))

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

تمت إضافة فواصل الأسطر في شريط الصيغة لتسهيل القراءة والصيانة

بالمناسبة ، هناك ملف اختصار لوحة المفاتيح لتوسيع وطي شريط الصيغة.

المزيد من البدائل

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

استخدامات اخرى

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

المؤلف ديف برونز


^