آموزش 6 روش اساسی و سریع حل مشکل ارور #REF! در اکسل

آموزش ۶ روش اساسی و سریع حل مشکل ارور #REF! در اکسل

در هنگام کار با اکسل با خطای 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# در اکسل باید پیش از حذف کردن ستونها، سطر ها و یا شیت ها (برگه ها)، مطمئن شوید که با فرمول های موجود ارجاع داده نشده است. همچنین اگر در حال کپی فرمول در جایی دیگر هستید، بهتر است که برخی از رفرنس های سلولی را به یک رفرنس مطلق تبدیل کنید تا از تغییرات در طول عملیات کپی جلوگیری کند.

اگر پس از حذف یک سطر و یا ستون، خطای 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 در اکسل

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

رفع ارور رفرنس در اکسل

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

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

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

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

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

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

ارور ref در اکسل

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

رفع ارور ref در اکسل

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

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

رفع ارور رفرنس در اکسل

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

ارور Reference is not valid در اکسل چیست؟

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

چگونه از ارور ref جلوگیری کنیم؟

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

چگونه خطای REF# را در VLOOKUP برطرف کنم؟

خطای #REF در VLOOKUP زمانی رخ می دهد که آرگومان col_index_num نامناسب باشد. به عنوان مثال، اگر col_index_num را برای محدوده جستجویی که تنها 3 ستون عرض دارد، 4 مشخص کنید. تصحیح آرگومان col_index_num باید خطای #REF را در VLOOKUP برطرف کند.

جمع بندی

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

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

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

به این مطلب امتیاز دهید
ممنونیم، امتیاز شما ثبت شد 🙂

بیشتر بخوانید

آموزش 3 روش حل مشکل ارور Split در اکسل
آموزش ۳ روش حل مشکل ارور Split در اکسل
آموزش کامل تقسیم در اکسل و حل مشکل ارور #div/0 تقسیم اکسل
آموزش کامل تقسیم در اکسل و حل مشکل ارور #DIV/0! تقسیم اکسل
رفع مشکل VALUE و نوشتن فرمول در اکسل
۶ روش رفع مشکل VALUE و نوشتن فرمول در اکسل

نظرات

×