در هنگام کار با اکسل با خطای REF مواجه شده اید؟ 🔗 این خطا زمانی رخ می دهد که یک رفرنس، نامعتبر باشد.

روش های متعددی برای رفع این خطا وجود دارد. در این مطلب از کلاسیک وب به آموزش ۶ روش اساسی و سریع حل حل مشکل ارور #REF! در اکسل می پردازیم.

آموزش رفع ارور REF در اکسل

How To Fix REF Error in Excel

به طور خلاصه برای رفع خطای ref در اکسل همانطور که مایکروسافت در سایت خود پیشنهاد کرده است، از روش های زیر استفاده کنید:

• تشخیص سطر و ستون حذف شده و برگرداندن آن ها
• پاک کردن خطاهای رفرنس با استفاده از Replace با جای خالی
• تصحیح رفرنس فرمول به صورت دستی
• دقت در وارد کردن شماره سطر و ستون در توابع VLOOKUP و INDEX
• به دام انداختن ارور رفرنس با IFERROR
• ایجاد فرمول با مرجع مطلق

در ادامه جزئیات بیشتری را ارائه می کنیم.

۱- علت خطای REF در اکسل

دلیل ارور ref در اکسل رفرنس نامعتبر است. به عبارت دیگر، از اکسل خواسته اید که یک مقدار را از جایی بیاورد که آنجا وجود ندارد. در بسیاری از موارد، به این دلیل است که برگه ها، ردیف ها و یا ستون ها حذف شده اند. یا اینکه فرمولی با رفرنس های نسبی در یک مکان جدید کپی شده است که در آنجا، رفرنس ها نامعتبر هستند. برای مثال در تصویر زیر D10 حاصل فرمول sum(D5:D9) می باشد. اگر D10 را کپی کنیم و در جایی دیگر مانند F5 قرار دهیم خطای ref نمایش داده می شود.

برای یادگیری حل مشکل اجرای فرمول در اکسل، آموزش حل مشکل محاسبه و عدم اجرای فرمول در اکسل را مطالعه نمایید.

۲- جلوگیری از خطای REF#

برای جلوگیری از خطای ref# در اکسل باید پیش از حذف کردن ستونها، سطر ها و یا شیت ها (برگه ها)، مطمئن شوید که با فرمول های موجود ارجاع داده نشده است. همچنین اگر در حال کپی فرمول در جایی دیگر هستید، بهتر است که برخی از رفرنس های سلولی را به یک رفرنس مطلق تبدیل کنید تا از تغییرات در طول عملیات کپی جلوگیری کند.

اگر پس از حذف یک سطر و یا ستون، خطای REF# ایجاد شد، آن عمل را لغو کنید ( می توانید از Ctrl + Z استفاده کنید). پس از آن، فرمول ها را ویرایش کنید تا ستون مورد نظر را حذف کنید و در صورت نیاز داده ها را جابجا کنید.

۳- پاک کردن خطاهای REF#

برای حذف سریع خطاهای REF# از یک کاربرگ، می توانید از Find and Replace استفاده کنید. از میانبر Control + H برای باز کردن Find and Replace استفاده کنید. در بخش Find What عبارت REF# را وارد کنید  و ناحیه Replace With را خالی بگذارید.

سپس می توانید با Find next + Replace تغییرات موردی ایجاد کنید یا از Replace All برای جایگزینی تمام خطاهای REF# در یک مرحله استفاده کنید.

۴- رفع خطای ref در اکسل

رفع خطای رفرنس در اکسل، تا حدی دشوار است زیرا سلول رفرنس اصلی برای همیشه از بین رفته است. اگر می دانید که رفرنس باید چه باشد، آن را به صورت دستی وارد کنید. برای اینکار، فرمول را ویرایش کنید و REF# را با یک رفرنس معتبر جایگزین کنید. در مثال زیر فرمول موجود در سلول F5 که خطای SUM (#REF) دارد را با فرمول SUM(D5:D9) جایگزین کرده ایم.

اگر نمی دانید که مرجع سلول چه باید باشد، قبل از اینکه بتوانید فرمول را اصلاح کنید، باید کاربرگ را با جزییات بیشتری مطالعه کنید.

۵- ارورهای ref با VLOOKUP

شما ممکن است در تابع VLOOKUP خطای رفرنس را ببینید. این زمانی اتفاق می افتد که یک ستون را به اشتباه مشخص کرده اید. برای مثال در تصویر زیر چون ستون ۳ در محدوده جدول B3:C7 وجود ندارد، لذا تابع VLOOKUP خطای رفرنس را برمیگرداند.

برای یادگیری نحوه استفاده از تابع، آموزش فرمول و تابع VLOOKUP را مطالعه نمایید.

اگر ایندکس ستون به درستی وارد شود و به مقدار ۲ تغییر یابد، خطای ref برطرف می شود و تابع VLOOKUP به درستی کار می کند.

در تابع INDEX نیز اگر سطر یا ستون مرجع، معتبر نباشد، ارور ref در اکسل ایجاد خواهد شد.

۶- به دام انداختن ارور REF در اکسل با IFERROR

در بیشتر موارد، به دام انداختن ارور REF# در یک فرمول، منطقی نیست. زیرا ref نشان دهنده یک مشکل سطح پایین است. با این حال، یکی از موقعیت‌ هایی که IFERROR ممکن است برای گرفتن خطای REF# منطقی باشد، زمانی است که شما در حال ساخت رفرنس به صورت پویا با تابع INDIRECT هستید.

۷- فرمول های با رفرنس مطلق ایجاد کنید

در مثال های قبل، مراجع به صورت نسبی بودند و کپی کردن فرمول در یک مکان دیگر، منجر به مشکل ref در اکسل می شد. برای رفع این مورد شما می توانید به شرح زیر عمل کنید:

سلول حاوی فرمول را انتخاب کنید.

در ابتدای نام مرجع سلول (برای نمونه C3) کلیک کنید و F4 را فشار دهید. یا می توانید به صورت دستی نماد $ را قبل از اسم ستون و شماره سطر وارد کنید.

این کار فرمول را به یک مرجع مطلق تبدیل می کند.

این کار را برای هر یک از رفرنس ها انجام دهید. پس از این اگر فرمول را در جایی دیگر برای نمونه در E3 کپی کنید خطای ref ایجاد نخواهد شد.

سوالات متداول

جمع بندی

دلیل ارور ref در اکسل، رفرنس نامعتبر است. این زمانی رخ می دهد که شما یک سطر یا ستون مرجع را حذف می کنید و یا اینکه فرمول با رفرنس نسبی را در جایی دیگر کپی می نمایید. بهتر است فرمول های مطلق ایجاد کنید و یا اینکه سطرها و ستون ها مرجعی که حذف کرده اید را برگردانید.

نظرات و پیشنهادات شما؟

آموزش ۶ روش اساسی و سریع حل مشکل ارور REF# در اکسل را بررسی کردیم. با این روش ها می توانید در صورت بروز ارور رفرنس، آن را برطرف کنید. اگر شما از روش دیگری جهت رفع ارور ref در اکسل استفاده کرده اید، آن را به ما و دیگران در بخش نظرات این پست معرفی کنید.