رفع خطای جستجو و ویلوکاپ در اکسل ناشی از اختلاف حروف عربی ك و ي با فارسی

یک نمونه رایج و گیج کننده در گزارش های بیمارستانی؛ نام یک بیمارستان یا حتی یک اسم پرسنل در دو ردیف مختلف کاملا یکسان دیده می شود، اما فرمول VLOOKUP یکی را پیدا می کند و دیگری را با خطای #N/A جواب می دهد. علت این است که دو حرف ك و ي در زبان عربی، از نظر کد کاراکتر یونیکد، کاملا با دو حرف ک و ی در زبان فارسی متفاوتند؛ حتی اگر روی صفحه نمایش تقریبا شبیه هم دیده شوند.

چرا این اتفاق می افتد؟

کیبورد استاندارد فارسی ویندوز، حرف کاف را با کد یونیکد U+06A9 (کاف فارسی) و حرف یای آخر را با کد U+06CC (یای فارسی) تولید می کند. اما بسیاری از سامانه های دولتی، سامانه های بیمه، نرم افزارهای HIS قدیمی، یا فایل هایی که از یک منبع عربی زبان یا با تنظیمات ویندوز عربی گرفته و کپی شده اند، همان حرف را با کد عربی U+0643 (کاف عربی) و U+064A (یای عربی) ذخیره می کنند. این دو جفت حرف در بیشتر فونت ها تقریبا یکسان رسم می شوند، اما برای اکسل دو کاراکتر کاملا متفاوت هستند؛ درست مثل اینکه حرف «الف» را با حرف «ب» مقایسه کنید.

علامت VLOOKUP این مشکل چیست؟

فرض کنید لیست بیمارستان های طرف قرارداد را از سامانه بیمه دریافت کرده اید. برخی نام ها در همان سامانه با کیبورد عربی تایپ شده اند؛ در ظاهر هیچ فرقی با نسخه فارسی همان اسم ندارند.

حالا همین نام ها را که با کیبورد فارسی معمولی تایپ کرده اید (همان چیزی که در گزارش خودتان می نویسید)، با VLOOKUP و تطابق دقیق (FALSE) در این لیست جستجو کنید:

دقیقا همین اتفاق در یک فایل حقوق و دستمزد هم رخ می دهد؛ کد پرسنلی یا نام پرسنلی که در یک سامانه دولتی یا بیمه ای با کیبورد عربی تایپ شده باشد، در VLOOKUP یا حتی در فیلتر و COUNTIF ساده هم پیدا نمی شود یا در PivotTable به عنوان یک ردیف کاملا جدا و تکراری ظاهر می شود؛ در حالی که چشم شما هیچ فرقی بین دو نام نمی بیند.

تشخیص قطعی با تابع UNICODE

به جای حدس زدن، می توانید با تابع =UNICODE(حرف) کد دقیق هر حرف را ببینید. کاف عربی همیشه کد 1603 و کاف فارسی همیشه کد 1705 را برمی گرداند؛ اگر این دو عدد با هم فرق داشتند، مطمئن می شوید که مشکل از همین جاست.

راه حل؛ نرمال سازی با SUBSTITUTE یا Find & Replace

دو روش عملی برای رفع این مشکل وجود دارد:

1. ستون کمکی با SUBSTITUTE: فرمول =SUBSTITUTE(SUBSTITUTE(B2,"ك","ک"),"ي","ی") را در یک ستون کمکی کنار لیست اصلی بنویسید. این فرمول هر کاف عربی را به کاف فارسی و هر یای عربی را به یای فارسی تبدیل می کند، بدون اینکه به داده اصلی HIS دست بزنید. VLOOKUP یا INDEX/MATCH را روی همین ستون کمکی نرمال شده اجرا کنید.
2. اصلاح یکجای کل لیست با Find & Replace: کلید Ctrl+H را بزنید، در کادر Find حرف عربی «ك» را (با کپی مستقیم از همان سلول مشکل دار، نه با تایپ از کیبورد فارسی) وارد کنید، در کادر Replace حرف فارسی «ک» را بگذارید و Replace All بزنید؛ همین کار را برای «ي» و «ی» هم تکرار کنید. این روش داده اصلی را برای همیشه یکدست می کند، اما باید حتما روی یک نسخه کپی انجام شود، نه روی فایل خام دریافتی از سامانه بیمه.

در فایل نمونه پایین همین دو راه حل، هم با ستون کمکی SUBSTITUTE و هم با یک VLOOKUP نهایی که روی ستون نرمال شده کار می کند، به صورت فرمول زنده پیاده شده است.

کجا باید مراقب این مشکل بود؟

• فایل هایی که از سامانه های دولتی یا بیمه ای (مثل سامانه تدارکات دولت یا سامانه های استعلام بیمه) خروجی گرفته می شوند.
• کد پرسنلی، نام پرسنل یا نام بیمارستان که از یک HIS یا نرم افزار قدیمی وارد اکسل شده اند.
• فایل هایی که از یک سند PDF یا Word عربی زبان کپی و پیست شده اند.
• هر جایی که VLOOKUP، MATCH، COUNTIF یا فیلتر روی متن فارسی، بدون دلیل ظاهری #N/A یا نتیجه نادرست می دهد.

نکات کاربردی

• اگر VLOOKUP روی دو متن ظاهرا یکسان خطا داد، همیشه اول با UNICODE کد حرف های کاف و یای هر دو متن را چک کنید؛ خیلی سریع تر از حدس زدن است.
• برای Find & Replace، حرف عربی مشکل دار را از خود سلول کپی کنید و در کادر Find بچسبانید؛ تایپ مستقیم از کیبورد فارسی همیشه حرف فارسی تولید می کند، نه عربی.
• این مشکل فقط مخصوص کاف و یاست؛ سایر حروف فارسی و عربی معمولا کد یکسانی دارند، پس نیازی به بررسی کل الفبا نیست.
• اگر این مشکل در یک گزارش بارها تکرار می شود، بهتر است یک ستون کمکی SUBSTITUTE را به صورت دائمی در قالب استاندارد گزارش قرار دهید تا هر بار داده جدید وارد شود، خودکار نرمال شود.

📄 دانلود فایل اکسل نمونه «رفع خطای حروف عربی ك و ي در جستجو» — شامل لیست بیمارستان نمونه با حروف عربی و فارسی مخلوط، نتیجه VLOOKUP قبل از اصلاح، برگه تشخیص با UNICODE، و برگه نهایی با ستون کمکی SUBSTITUTE و VLOOKUP اصلاح شده، برای تمرین گام به گام.

منبع : سایت حسابداری بیمارستان
کانال تلگرام @hospitalaccounting