خطای “#REF!” در اکسل زمانی اتفاق می‌افتد که یک فرمول یا عبارت مرجع به یک سلول یا محدوده‌ای از سلول‌ها دارای مشکل است. این مشکل معمولاً به دلیل اینکه اکسل نمی‌تواند سلول یا محدوده مورد نظر را پیدا کند به وجود می‌آید. این ممکن است به دلیل عوامل زیر باشد:
- حذف سلول‌ها: اگر سلولی که یک فرمول به آن اشاره دارد حذف شود، خطای “#REF!” پدیدار می‌شود.
- تغییر مکان سلول‌ها: اگر سلول‌هایی که یک فرمول به آنها اشاره دارد به جای دیگری منتقل شوند، اکسل نمی‌تواند مراجع مورد نظر را پیدا کند و خطای “#REF!” را نمایش می‌دهد.
- تغییر نام‌های نام‌گذاری شده: اگر یک نام نام‌گذاری شده به یک سلول یا محدوده اشاره دارد و این نام تغییر کند یا حذف شود، خطای “#REF!” ظاهر می‌شود.
- تغییر ساختار صفحه: در صورتی که ساختار صفحه تغییر کند، ممکن است اشاره‌گرهای فرمول‌ها دیگر به صورت صحیح کار نکنند و خطای “#REF!” بوجود آید.
این عوامل می‌توانند باعث ایجاد خطای “#REF!” در اکسل شوند.
ارور #REF! در اکسل چگونه ایجاد می شود؟
در نظر بگیرید که در سلول A1 عدد ۵ و در سلول B1 عدد ۱۰ قرار دارد. اکنون فرض کنید که در سلول C1 می‌خواهیم مجموع عدد در سلول A1 و عدد در یک سلولی که وجود ندارد (برای مثال سلول D1) را محاسبه کنیم. این فرمول خطای “#REF!” ایجاد می‌کند.
فرمول زیر نمونه‌ای از چنین فرمولی است:
=C1+A1+D1در اینجا، اگر سلول D1 حذف شود یا تغییر مکان دهد، فرمول مجبور به اشاره به یک سلول نامعتبر است که باعث ایجاد خطای “#REF!” در اکسل می‌شود.
حل ارور #REF! در اکسل
برای حل خطای “#REF!” در اکسل، که معمولاً به دلیل تغییر مکان سلول‌ها یا حذف سلول‌هایی که به عنوان مراجع در فرمول‌ها استفاده شده‌اند، رخ می‌دهد، می‌توانید اقدامات زیر را انجام دهید:
بررسی فرمول‌ها: بررسی فرمول‌هایی که خطای “#REF!” را پدید آورده‌اند و تشخیص دادن کدام سلول یا محدوده در فرمول‌ها باعث ایجاد خطا شده است.
بررسی تاریخچه ویرایش: بررسی تاریخچه ویرایش فایل برای پیدا کردن زمانی که خطا ایجاد شده است.
بازیابی از Undo: از Ctrl+Z (بازیابی) برای بازگردانی تغییرات استفاده کنید تا سلول‌ها به حالت قبلی بازگردند.
بازنویسی فرمول‌ها: بازبینی و بازنویسی فرمول‌ها به گونه‌ای که از مراجع مطلق به جای مراجع نسبی استفاده کنید.
بازیابی از Undo AutoRecover: در صورتی که فایل اکسل برای شما ذخیره شده بود، اکسل ممکن است از AutoRecover برای بازیابی اطلاعات برای شما استفاده کند.
- استفاده از مراجع نسبی: استفاده از مراجع نسبی به جای مراجع مطلق می‌تواند از خطاهای “#REF!” جلوگیری کند.
- قفل کردن سلول‌ها: قفل کردن سلول‌هایی که به عنوان مراجع در فرمول‌ها استفاده می‌شوند می‌تواند از تغییر مکان آنها جلوگیری کند.
- بازبینی مرتب: بازبینی مرتب فرمول‌ها و ارتباطات بین سلول‌ها برای اطمینان از درستی آنها.
با رعایت این نکات و اقدامات، می‌توانید از خطای “#REF!” در فرمول‌های اکسل خود جلوگیری کرده و این خطا را رفع کنید.
آیا می‌توانم از توابع دیگری برای جلوگیری از این خطا استفاده کنم؟
بله، می‌توانید از توابع اکسل برای جلوگیری از خطای “#REF!” استفاده کنید. یکی از توابع مفید برای این کار تابع IFERROR است. این تابع به شما امکان می‌دهد تا یک مقدار خاص (مثلاً مقدار پیش‌فرض یا یک پیام خطا) را در صورت رویت خطای خاص (مانند “#REF!”) به جای خود خطا نمایش دهید.
مثال:
در ادامه مثال قبلی، می‌توانید تابع IFERROR را به شکل زیر استفاده کنید تا به جای نمایش خطای “#REF!”، یک پیام دلخواه نمایش داده شود:
=IFERROR(C1+A1+D1, "مقدار نامعتبر")در این حالت، اگر سلول D1 حذف شود یا تغییر مکان دهد و خطای “#REF!” ایجاد شود، به جای خطای “#REF!” پیام “مقدار نامعتبر” نمایش داده خواهد شد.
استفاده از توابع مانند IFERROR به شما کمک می‌کند که به صورت خودکار با خطاهای ممکن در فرمول‌های خود برخورد کنید و به جای نمایش خطاها، مقادیر یا پیام‌های دلخواهی را نمایش دهید.
چگونه می‌توانم خطای “#REF!” را در VLOOKUP برطرف کنم؟
وقتی از تابع VLOOKUP در اکسل استفاده می‌کنید، ممکن است خطای “#REF!” رخ دهد اگر سلول‌هایی که به عنوان مراجع در این تابع استفاده شده‌اند حذف شوند یا تغییر مکان دهند. این مشکل معمولاً زمانی پیش می‌آید که در تابع VLOOKUP محدوده مراجعی که به عنوان آرگومان انتخاب شده است، تغییر کند.
برای مثال، فرض کنید که از تابع VLOOKUP برای جستجوی مقداری در یک محدوده استفاده می‌کنید و اگر محدوده مراجع حذف شود یا تغییر کند، می‌توانید با خطای “#REF!” مواجه شوید.
تاکید مهمی که باید داشته باشید این است که محدوده مراجعی که به عنوان آرگومان در تابع VLOOKUP استفاده می‌شود، باید ثابت بماند یا به روز رسانی شود تا از ایجاد خطای “#REF!” جلوگیری کنید.
برای جلوگیری از خطای “#REF!” در استفاده از تابع VLOOKUP در اکسل، می‌توانید از توابع مختلفی مانند IFERROR یا IF به همراه ISERROR استفاده کنید تا مقدار دلخواهی را به جای خطاهای “#REF!” نمایش دهید.
یک روش معمول برای این کار استفاده از تابع IFERROR است. این تابع به شما امکان می‌دهد تا یک مقدار دلخواه را به جای خطاهای مشخصی که در تابع دیگری (مثل VLOOKUP) رخ داده‌اند، نمایش دهید.
در ادامه مثالی از استفاده از IFERROR با VLOOKUP را مشاهده می‌کنید:
=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), "مقدار دلخواه یا پیام خطا")در این فرمول، lookup_value مقداری است که شما می‌خواهید در محدوده جستجو شود، table_array محدوده داده‌هایی است که شما می‌خواهید در آن جستجو انجام شود، col_index_num شماره ستونی است که می‌خواهید مقدار متناظر با lookup_value را در آن بیابید، و range_lookup یک مقدار بولین است که نشان می‌دهد آیا باید به صورت تقریبی یا دقیق جستجو انجام شود.
با استفاده از IFERROR، اگر تابع VLOOKUP خطای “#REF!” را بازگرداند، مقدار “مقدار دلخواه یا پیام خطا” به جای خطا نمایش داده خواهد شد. این روش به شما کمک می‌کند تا از نمایش خطاهای “#REF!” در فرمول‌های خود جلوگیری کنید.
جمع بندی:
اشکال “#REF!” در اکسل به دلیل وجود رفرنس‌های نامعتبر ایجاد می‌شود. این مشکل زمانی پیش می‌آید که یک سطر یا ستون مرجع حذف می‌شود یا فرمولی با رفرنس‌های نسبی در جای دیگر کپی می‌شود. برای جلوگیری از این مشکل، بهتر است فرمول‌های مطلق استفاده شود و یا سطرها و ستون‌های حذف شده را بازگردانی کنید.
