آموزش 18 روش حل مشکل محاسبه و عدم اجرای فرمول در اکسل

فرمول نویسی در اکسل ➗ یکی از کارهایی است که اغلب کاربران اکسل با آن سر و کار دارند. در حین اجرا فرمول های اکسل شما ممکن است مشکلاتی مانند تغییر ناگهانی فرمول، تبدیل متن به تاریخ، اجرا نشدن فرمول را تجربه کنید. برای غلبه بر این مشکلات می توانید آموزش ۱۸ روش حل مشکل محاسبه و عدم اجرای فرمول در اکسل امروز مجله خبری آی تی و دیجیتال را دنبال کنید.

رفع مشکل اجرا نشدن فرمول در اکسل

Excel Formulas Not Working, Not Updating, Not Calculating: Fixes & Solutions

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

حل مشکل عدم اجرای فرمول در اکسل با تغییر تنظیمات Calculation

به طور کلی سه گزینه محاسبه در اکسل وجود دارد:

Automatic Calculation به این معنا که اکسل تمام فرمول های وابسته را با تغییر مقدار سلول یا فرمول دوباره محاسبه می کند.

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

Automatic Except for Data Tables به این معنی است که اکسل به طور خودکار برای همه سلول ها (به جز آنهایی که در جداول داده استفاده می شوند) دوباره محاسبات را انجام می دهد. این به جداول عادی اکسل که ممکن است اغلب با آنها کار کنید، اشاره نمی کند، بلکه به یک ابزار تحلیل سناریو اشاره دارد که افراد زیادی از آن استفاده نمی کنند. شما می توانید آن را در سربرگ Data ، زیر دکمه What-If Scenarios پیدا کنید.

اگر نمی توانید مقداری را که وارد کرده اید را بروزرسانی کنید ممکن است گزینه Calculation اکسل روی Manual تنظیم شده باشد که باید آن را به Automatic تغییر دهیم.

فایل اکسلی که با آن مشکل دارید را باز کنید.

از نوار منو، به سربرگ Formulas بروید و Calculation را انتخاب کنید.

Calculation Options را انتخاب کنید و از منو گزینه Automatic را انتخاب کنید.

همچنین می توانید Calculation Options را از طریق Excel options تنظیم کنید.

برای انجام این کار، دکمه Office را در گوشه سمت چپ بالا انتخاب کنید و به مسیر Excel options > Formulas > Workbook Calculation > Automatic بروید.

گاهی اوقات مجبور هستیم که از حالت Manual استفاده کنیم. اگر می خواهید دلیل تغییر حالت خودکار به دستی را بدانید باید بگوییم که هنگام کار با فایل های حجیم محاسبات به کندی انجام خواهد شد. محاسبه مجدد فرمول ها هنگامی که تغییرات ایجاد می شود می تواند سیستم شما را کند کند. بنابراین گاهی اوقات مجبور هستید حالت را به Manual تغییر دهیم.

بنابراین از سربرگ Formulas روی Calculate Now کلیک کنید و یا از کلید میانبر F9 استفاده کنید که کل ورک بوک را محاسبه می کند. اگر می خواهید فقط ورک شیت فعلی را محاسبه کنید می توانید Calculate Sheet را انتخاب کنید و یا از کلیدهای میانبر Shift + F9 استفاده کنید. در ادامه لیستی از میانبرهای کاربردی را به شما معرفی می کنیم:

فرمت متنی سلول علت مشکل عدم محاسبه فرمول در اکسل

ممکن است به طور تصادفی سلول های حاوی فرمول ها را به عنوان متن فرمت بندی کرده باشید. متاسفانه اکسل وقتی روی فرمت متنی تنظیم می شود از محاسبه فرمول صرف نظر می کند و در عوض فرمول را به عنوان تکست یا متن نمایش می دهد.

بهترین روش برای بررسی فرمت این است که روی سلول کلیک کنید و از سربرگ Home گروه Number را انتخاب کنید. اگر در اینجا Text نمایش داده می شود روی آن کلیک کنید و General را انتخاب کنید. برای محاسبه مجدد فرمول در اکسل؛ روی سلول دوبار کلیک کنید و بعد روی کلید اینتر صفحه کلید بزنید.

کار نکردن فرمول اکسل به علت فعال بودن دکمه Show Formulas

اگر مشکل محاسبه در اکسل را تجربه می کنید ممکن است دکمه Show Formulas را به طور تصادفی فعل کرده باشید که در این حالت فرمول های وارد شده کار نمی کنند.  می توانید این تنظیمات را در سربرگ Formula پیدا کنید.

دکمه Show Formula برای ممیزی فرمول ها طراحی شده است. بنابراین به جای نمایش نتیجه  فرمول را به شما نشان می دهد. بنابراین باید برای غیر فعال کردن این دکمه اقدام کنید که برای انجام این کار به مسیر Formula > Formula Auditing Group بروید و روی دکمه Show Formulas کلیک کنید.

رفع مشکل فرمول نویسی در اکسل با پاک کردن فاصله قبل از مساوی

وقتی قبل از = شما کاراکتری مانند فاصله را وارد می کنید محاسبات به درستی برای شما انجام نمی شوند و کاربر با مشکل نمایش نتیجه فرمول در اکسل مواجه می شود. این مشکل معمولا زمانی رخ می دهد که کاربر سعی می کند از اینترنت فرمولی را کپی و در اکسل پیست کند. برای حل این مشکل کافیست که به سلول بروید و آپاستروف یا فاصله قبل از علامت = را حذف کنید.

اکسل را مجبور به محاسبه مجدد کنید

برای حل مشکل فرمول در اکسل می توانید از تنظیمات محاسبه خودکار استفاده نکنید و فرمول ها را به صورت دستی محاسبه کنید. برای انجام این کار کلید F9 صفحه کلید را فشار دهید و یا در سربرگ Formula گزینه Calculate Now را انتخاب کنید. همچنین می توانید Shift+F9 روی صفحه کلید را فشار دهید یا Calculate Sheet از گروه Calculation در سربرگ Formula را انتخاب کنید.

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

استفاده از نحو نامناسب علت مشکل فرمول اکسل

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

به عنوان مثال اگر جدولی مانند زیر را داشته باشیم و بخواهیم نمره Exam 2 را بدست آوریم،

اگر از فرمول =VLOOKUP(A2,A2:C6,2,FALSE) استفاده کنیم نتیجه ای اشتباه به شما نشان داده می شود چون از مقدار اشتباهی برای آرگومان سوم استفاده کرده ایم و به جای برگرداندن نمره Exam 2 نمره Exam 1 را بر می گرداند.

حذف شدن سطر یا ستون خاص : علت پاک شدن فرمول در اکسل

یکی از مواردی که می تواند تأثیر مخربی بر فرمول اکسل داشته باشد، حذف سطر/ستونی است که در محاسبات استفاده شده است. هنگامی که این اتفاق می افتد، گاهی اوقات، اکسل خود رفرنس را تنظیم می کند و مطمئن می شود که فرمول ها درست کار می کنند. اما در برخی مواقع نمی تواند این کار را به درستی انجام دهد.

ارور #REF! معمولا در این شرایط به شما نمایش داده می شود که به شما می گوید در رفرنس و ارجاعات یک فرمول مشکلی به وجود آمده است. در زیر از یک فرمول SUM برای اضافه کردن سلول هی A2:A6 استفاده کرده ایم.

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

حل مشکل فرمول نویسی در اکسل با استفاده درست از پرانتزها

مشکل ضرب در اکسل یا مشکل تقسیم در اکسل گاهی اوقات به دلیل استفاده نامناسب از پرانتزها رخ می دهد. همانطور که فرمول های شما شروع به بزرگتر شدن و پیچیده تر شدن می کنند، ایده خوب این است که از پرانتز استفاده کنید تا مشخص کنید که کدام بخش ها به هم تعلق دارند. همچنین لازم است بدانید که عملگرها نسبت به هم اولویت دارند به طور مثال برای =۵+۱۰*۵۰ عملگر ضرب به جمع اولویت بیشتری دارد بنابراین برای اینکه به پاسخ درستی دست پیدا کنیم باید آن را به صورت =۵+(۱۰*۵۰) بنویسید. اگر می خواهید اولویت بیشتر را به عملگر جمع بدهید باید آن را به صورت =(۵+۱۰)*۵۰ بنویسید.

استفاده نادرست از مرجع سلول مطلق / نسبی

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

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

به طور مثال ما در اینجا مجموعه داده زیر را داریم و می خواهیم نمره Exam 1 را برای نام های موجود در ستون E واکشی کنیم. در سلول F2 از فرمول =VLOOKUP(E2,A2:B6,2,0) استفاده می کنیم و بعد در تمام سلول های زیر آن فرمول را کپی می کنیم.

همانطور که می بینید در برخی سلول ها این فرمول خطا می دهد. این خطا بدان خاطر اتفاق می افتد که به جای A2:B6 در سلول F2 باید $A$2:$B$6 را وارد می کردیم. با استفاده از علامت $ قبل از شماره ردیف و ستون، اکسل را مجبور می کنیم که این مراجع سلولی را ثابت نگه دارد. بنابراین حتی زمانی که این فرمول را کپی می کنیم آرایه جدول همچنان به A2:B6 اشاره می کند.

برای تبدیل یک مرجع نسبی به مطلق؛ سلول را انتخاب کنید و کلید F4 را فشار دهید. متوجه خواهید شد که با اضافه کردن علامت $ نوع مرجع تغییر می کند.

ارجاع اشتباه به نام ورک بوک یا شیت علت مشکل فرمول اکسل

هنگامی که به برگه یا ورک بوک های دیگری در فرمول ارجاع می دهید باید از فرمت خاصی پیروی کنید و در صورت اشتباه بودن این فرمت با خطا مواجه خواهید شد. برای مثال اگر بخواهیم به سلول A1 در Sheet2 اشاره کنیم ارجاع به صورت =Sheet2!A1 خواهد بود(بعد از نام برگه باید از ! استفاده کنید).

در صورتی که چندین کلمه در نام برگه وجود داشته باشد (مانند Data) ارجاع به صورت =’Example Data’!A1 خواهد بود که در آن نام در گیومه های تکی قرار می گیرد و بعد ! استفاده می شود.

در صورتی که به یک ورک بوک دیگر اشاره می کنید (مثلا به سلول A1 در شیت Example Sheet و ورک بوک Example Workbook ارجاع به صورت زیر خواهد بود:

='[Example Workbook.xlsx]Example Sheet’!$A$1

در صورتی که ورک بوک را ببندید، ارجاع تغییر می کند تا کل مسیر ورک بوک شما را در بر بگیرد:

=’C:UserssumitDesktop[Example Workbook.xlsx]Example Sheet’!$A$1

اگر در نهایت نام ورک بو یا شیتی که فرمول به آن اشاره دارد را تغییر دهید ارور #REF را مشاهده خواهید کرد.

Circular References یا ارجاع دوار

Circular References زمانی است که شما مستقیم یا غیر مستقیم به همان سلولی که فرمول در آن محاسبه می شود، مراجعه می کنید. در ادامه یک مثال ساده را آورده ایم که در آن از فرمول SUM در سلول A4 استفاده شده است در حالی که از آن در خود محاسبه استفاده شده است.

=SUM(A1:A4)

گرچه اکسل اعلانی به شما نشان می دهد که به شما در مورد Circular References اطلاعاتی می دهد، اما این کار را برای هر فرمولی انجام نمی دهد و ممکن است نتایج اشتباهی را به شما برگرداند. اگر به گرفتن نتیجه اشتباه مشکوک هستید می توانید روی سربرگ Formula کلیک کنید و در گروه Formula Auditing روی آیکن Error Checking (فلش کوچک رو به پایین) کلیک کنید.

مکان نما را روی گزینه Circular reference نگه دارید تا سلولی که مشکل ارجاع دوار دارد به شما نمایش داده شود.

مشکل بهم ریختگی فرمول در اکسل : متن به طور خودکار به تاریخ تبدیل می شود

اکسل این عادت بد را دارد که تاریخ را به یک تاریخ واقعی تبدیل می کند. به عنوان مثال، اگر ۱/۱ را وارد کنید اکسل آن را به Jan 01 سال جاری تبدیل می کند. از آنجایی که اکسل مقادیر تاریخ و زمان را به عنوان اعداد ذخیره می کند به محض وارد کردن ۱/۱ آن را به عددی تبدیل می کند که نشان دهنده ۱ ژانویه سال جاری است. در اینجا با وارد کردن عدد ۴۳۸۳۱ به ۰۱-۰۱-۲۰۲۰ تبدیل می شود. اگر از این سلول ها به عنوان آرگومان در فرمول استفاده کنید ممکن است مشکلاتی را تجربه کنید. برای حل این مشکل باید خودمان فرمت را مشخص کنیم پس:

سلول یا سلول هایی که می خواهید فرمت آن را تغییر دهید را انتخاب کنید.

روی سربرگ Home کلیک کنید.

در گروه Nymber روی منوی Format کلیک کنید.

روی Text کلیک کنید.

حالا اگر عددی را در سلول انتخاب شده وارد کنید به عنوان متن در نظر گرفته می شوند و به طور خودکار تغییر نمی کند.

مثال دیگری که در اینجا می تواند خسته کننده باشد،  زمانی است که متن یا عددی را وارد می کنید که صفرهایی را در ابتدای خود دارند. اکسل به طور خودکار این صفرهای اول را حذف می کند زیرا آنها را بی فایده می داند. به عنوان مثال اگر ۰۰۰۱ را در یک سلول وارد کنید اکسل ان را به ۱ تغییر می دهد. اگر می خواهید این صفرهای ابتدایی را داشته باشید از مراحل بالا استفاده کنید.

سطرها و ستون های پنهان علت کار نکردن فرمول اکسل

در اینجا فرض کنید که شما یک مجموعه داده مانند زیر را دارید و می خواهید مجموع تمام سلول های قابل مشاهده در ستون C را بدست بیاورید.

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

مشکل اینجاست که فرمول سلول C12 همچنان همان نتایج را نشان می دهد (یعنی مجموع تمام رکورد ها).

همانطور که گفتیم، فرمول نتیجه اشتباه نمی دهد و در واقع تابع SUM به خوبی کار می کند. مسئله این است که ما در اینجا از فرمول اشتباه استفاده کرده ایم. تابع SUM نمی تواند داده های فیلتر شده را حساب کند و نتیجه را برای همه سلول ها (چه مخفی و چه قابل مشاهده) ارائه می دهد. اگر فقط می خواهید مجموع یا میانگین سلول های قابل مشاهده را بدست آورید از توابع SUBTOTAL یا AGGREGATE استفاده کنید. اگر در هنگام نوشتن فرمول VALUE مشکل دارید می توانید از ۶ روش رفع مشکل VALUE و نوشتن فرمول در اکسل استفاده کنید.

همه پرانتز های باز و بسته استفاده شده در فرمول اکسل را مطابقت دهید

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

در یک فرمول بیشتر از ۶۴ تابع استفاده نکنید

هنگامی که دو یا چند تابع اکسل را در یکدیگر قرار می دهید (به عنوان مثال در هنگام استفاده از IF تو در تو) محدودیت های زیر را به خاطر داشته باشید:

در اکسل ۲۰۱۶، اکسل ۲۱۰۳، اکسل ۲۰۱۰ و اکسل ۲۰۰۷ می توانید تا ۶۴ تابع تو در تور استفاده کنید.

در اکسل ۲۰۰۳ به پایین فقط ۷ تابع تو در تو را می توانید استفاده کنید.

اعداد را در دو گیومه قرار ندهید

در فرمول های اکسل هر عددی که در داخل دو گیومه (“”) قرار می گیرد به عنوان یک رشته متنی تفسیر می شود. این بدان معناست که وقتی فرمولی مانند =IF(A1>0, “1”) را وارد می کنید اکسل عدد ۱ را به عنوان متن استفاده می کند. بنابراین نمی توانید از ۱ برگشتی در محاسبات دیگر استفاده کنید. برای رفع این مشکل باید گیومه های اطراف ۱ را حذف کنید تا چیزی مانند  =IF(A1>0, 1)را داشته باشید.

اجرا نشدن فرمول در اکسل : اعداد را بدون هیچ فرمتی وارد کنید

هنگام استفاده از یک فرمول اکسل هیچ جدا کننده اعشاری با علامت ارزی مانند دلار $ را اضافه نکنید. به خاطر داشته باشید که در فرمول های اکسل معمولا از کاما برای جدا کردن آرگومان های یک تابع استفاده می شود و علامت # یک مرجع سلول مطلق را ایجاد می کند. به عنوان مثال برای وارد کردن ۵۰۰۰۰ دلار در فرمول فقط ۵۰۰۰۰ را وارد کنید و از Format Cells (Ctrl + 1) استفاده کنید و خروجی را به دلخواه فرمت کنید.

آرگومان های تابع را با یک کاراکتر مناسب جدا کنید

اکثر ما به جدا کردن آرگومان های تابع با کاما عادت کرده ایم. با این حال برای همه توابع اکسل کاما به عنوان کاراکتری که برای جدا کردن آرگومان ها استفاده می کنید کار نمی کند. کامل جدا کننده لیست پیش فرض آمریکائی شمالی و برخی کشورهای دیگر است. در کشورهای اروپایی کاما به عنوان نماد اعشاری استفاده می شود و جدا کننده لیست معمولا به نقطه ویرگول تنظیم می شود. به عنوان مثال در آمریکای شمالی از =IF(A1>0, “OK”, “Not OK”) استفاده می کنند در حالی که کاربران اروپایی از فرمول =IF(A1>0; “OK”; “Not OK”) استفاده می کنند.

بنابراین اگر شما در حین اجرا فرمول اکسل خطایی مانند We found a problem with this formula… را مشاهده می کنید به تنظیمات منطقه ای خود بروید (از طریق مسیر Control Panel > Region and Language > Additional Settings) و بررسی کنید که چه کاراکتری به عنوان جدا کننده تنظیم شده است. سپس از این کاراکتر برای جداسازی آرگومان های خود در فرمول های اکسل استفاده کنید.

رفع مشکل اجرا نشدن فرمول در اکسل : تغییر ماکرو به حالت Manual Calculation Mode

اگر متوجه شدید که ورک بوک شما به طور خودکار محاسبه نمی شود علت عدم تغییر در اکسل ممکن است وجود ماکرو باشد. البته این مشکل توسط همه ماکروها ایجاد نمی شود. معمولا یک خط کد خاص است که یک توسعه دهنده ممکن است از آن برای کمک به اجرای سریعتر ماکرو استفاده کند. خط کد زیر به اکسل می گوید که به حالت محاسبه دستی تغییر کند:

Application.Calculation = xlCalculationManual

گاهی اوقات نویسنده ماکرو این خط را در اول کدها اضافه می کند تا اکسل در حین اجرای ماکرو اقدام به محاسبه نکند. تنظیمات باید در انتهای ماکرو با خط زیر خاتمه پیدا کند:

Application.Calculation = xlCalculationAutomatic

این تکنیک می‌تواند برای ورک بوک های بزرگی که محاسبه کندی دارند، به خوبی کار کند.

با این حال، مشکل زمانی ایجاد می‌شود که ماکرو به پایان نرسد (شاید به دلیل یک خطا، خرابی برنامه، یا مشکل سیستم غیر منتظره). ماکرو تنظیمات را به Manual تغییر می دهد و بعد آن را به Automatic تغییر نمی دهد.

بنابراین، اگر از این تکنیک با ماکروهای خود استفاده می کنید، توصیه می کنیم به روش هایی برای رهایی از این مشکل فکر کنید. همچنین توصیه می‌کنیم ویژگی Calculation را با کد تغییر ندهید، مگر اینکه مجبور باشید.

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

چه با مشکل Autosum در اکسل مواجه شده باشید و چه در هنگام اجرا فرمول های پیچیده ریاضی به کمک نیاز داشته باشید در آموزش ۱۸ روش حل مشکل محاسبه و عدم اجرای فرمول در اکسل امروز مجله خبری آی تی و دیجیتال راه حلی برای خود پیدا خواهید کرد. اگر علاوه بر روش های بالا راه حل کاربردی دیگری سراغ دارید می توانید در بخش نظرات این مطلب با ما به اشتراک بگذارید.