روش نمایش فرمول در اکسل و دیدن فرمول های اکسل

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

گزینه نمایش فرمول در نوار ابزار اکسل

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

1. در نوار ابزار اکسل به سربرگ «Formulas» بروید.
2. در بخش «Formula Auditing» روی «Show Formulas» بزنید.

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

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

در اکسل ۲۰۱۰ به بعد، برای نمایش فرمول در سلول می‌‌توانید از پنجره امکانات اکسل استفاده کنید. برای این کار طبق مراحل زیر عمل کنید:

1. به سربرگ «File» بروید.
2. روی «Options» کلیک کنید.
3. در منوی سمت چپ پنجره باز شده، به بخش «Advanced» بروید .
4. در پایین بخش Advanced به «Display options for this worksheet» بروید.
5. گزینه «Show formulas in cells instead of their calculated results» را روشن کنید.

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

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

سریع‌ترین راه برای نمایش فرمول در اکسل میانبر Ctrl+` است. علامت ` روی صفحه کلید انگلیسی، در سمت چپ ردیف اعداد کنار 1 قرار دارد. این میانبر هربار بین نمایش فرمول و نتیجه می‌چرخد. دقت کنید هر یک از این روش‌ها در صفحه‌ای از اکسل که باز است، عمل می‌کند. برای سایر صفحات باید این عملیات را در آن صفحه تکرار کنید.

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

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

استفاده از تابع FORMULATEXT

با استفاده از تابع FORMULATEXT می‌توانید فرمول‌های موجود در سلول‌ها را به صورت رشته‌ای از داده متنی استخراج کنید. سینتکس این تابع تنها یک آرگومان دارد و در این یک آرگومان آدرس یک سلول یا گروهی از سلول‌ها وارد می‌‌شود. البته پیش‌تر در مجله فرادرس راجع به «سینتکس» صحبت کرده‌ایم و برای آشنایی بیشتر می‌توانید مطلب مربوط به آن را مطالعه کنید. خروجی این تابع در واقع فرمولی است که با انتخاب سلول حاوی فرمول، در نوار فرمول نمایش داده می‌‌شود.

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

کلیک کنید

=FORMULATEXT(reference)

در تصویر زیر، جدولی را مشاهده می‌‌کنید که ستون دوم آن نتیجه فرمول‌هایی را نشان می‌‌دهد که روی داده‌های موجود در ستون اول اعمال شده اند. با وارد کردن فرمول زیر در سلول D5 در ستون سوم و سپس کشیدن مربع کوچک کنار این سلول تا انتهای جدول، فرمول استفاده شده در هر ردیف نمایش داده می‌‌شود.

=FORMULATEXT(C5)

تابع FORMULATEXT در شرایط زیر خطای NA را برمی‌گرداند:

• سلول انتخابی، فاقد فرمول باشد.
• فرمول موجود در سلول انتخابی، بیش از ۸۱۹۲ کاراکتر داشته باشد.
• صفحه سلول انتخابی قفل شده باشد.
• صفحه‌ای که سلول انتخابی در آن قرار دارد، در اکسل باز نباشد.

همانطور که در تصویر زیر مشاهده می‌کنید، سلول‌های B8، B9 و B10 فاقد فرمول هستند، لذا با اعمال تابع FORMULATEXT روی این سلول ها،‌خطای NA برگردانده شده است. به علاوه در این مثال در ستون سوم تصویر در فرمول ISFORMULA استفاده کرده‌ایم، که فرمول بودن یا نبودن محتوای یک سلول را با دو مقدار True یا False نشان می‌دهد.

یادگیری ترفندهای کاربردی اکسل با فرادرس

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

• فیلم آموزش ترفند‌های کاربردی اکسل فرادرس
• فیلم آموزش رایگان اکسل در ۱۸۰ دقیقه فرادرس
• آموزش رایگان ترفندهای اکسل برای ناشنوایان فرادرس

اگر مایلید تمامی فیلم‌های آموزشی اکسل فرادرس را یکجا مشاهده کنید، روی لینک زیر کلیک کنید:

• مجموعه فیلم‌های آموزشی اکسل فرادرس از مقدماتی تا پیشرفته

نمایش فرمول‌ها با تغییر فرمت سلول

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

• سلول(های) مدنظر را که حاوی فرمول هستند، انتخاب کنید.
• به سربرگ «Home» بخش «Number» بروید.
• منوی بالای این بخش را باز کنید و «Text» را انتخاب کنید.

• سپس F2+Enter را بزنید تا تغییر فرمت روی سلول اعمال شود.

خواهید دید که فرمول‌ها نمایش داده می‌شوند. برای نمایش مجدد نتیجه فرمول کافیست فرمت سلول را به حالت قبل برگردانید و سپس مجدد F2+Enter را بزنید تا فرمت جدید سلول فعال شود. سلول هایی که در فرمت تکست تعریف شده اند،‌ با وارد کردن فرمول در آن‌ها، نتیجه نمایش داده نخواهد شد و متن فرمول به عنوان یک داده متنی تعریف می‌‌شود.

انتخاب همه سلول‌های حاوی فرمول

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

1. به سربرگ Home بروید.
2. در بخش «Editing» روی گزینه «Find and select» بزنید.
3. در منوی باز شده Formulas را انتخاب کنید.

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

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

روش دیگری نمایش فرمول،‌ دستکاری فرمول است به‌طوریکه اکسل دیگر آن را به عنوان فرمول تشخیص ندهد و مثل یک داده متنی نمایش دهد. می‌دانیم اگر در سلولی، وارد کردن داده را با علامت تساوی شروع کنیم، اکسل محتوای آن سلول را به عنوان فرمول تشخیص می‌‌دهد. در این روش با استفاده از ابزار جستجو تمامی علامت‌های تساوی را می‌یابیم و قبل آن یک آپوستروف « '  » قرار می‌دهیم، تا فرمول‌ها از حالت فرمول خارج شوند. حسن علامت آپوستروف در این است که تنها در نوار فرمول نمایش داده می‌شود و در صفحه اکسل در سلول‌ها نمایش داده نخواهد شد. برای این کار طبق مراحل زیر عمل کنید:

1. سلول‌های مدنظر را انتخاب کنید.
2. با زدن میان بر Ctrl+H پنجره «Find and Replace» را باز کنید. این پنجره در منوی بخش Editing سربرگ Home نیز باز می‌شود.
3. در پنجره باز شده در نوار «Find what»، علامت « =  » را وارد کنید
4. در نوار «Replace with»، علامت‌های « '=  » را وارد کنید.
5. در بخش «Look at» گرینه Formulas را انتخاب کنید تا تنها این جایگزینی در فرمول‌ها اتفاق بیوفتد.
6. روی «Replace All» کلیک کنید.

خواهید دید که در سلول‌های انتخابی به‌جای نتیجه فرمول،‌ خود فرمول بدون '  نمایش داده می‌‌شود.

برای برعکس کردن این عملیات متاسفانه نمی‌توانید مجدد '= را با = جایگزین کنید. چرا که اکسل ' را نمی‌تواند بیابد تا آن را با مقدار دلخواه جابجا کند. اگر قصد برعکس کردن این عملیات را دارید، لازم است از علامتی به غیر از ' استفاده کنید. مثل |  .

در این حالت علامت در سلول نیز نمایش داده خواهد شد.

باز کردن قفل نمایش فرمول‌ها

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

1. در سربرگ Home بخش «Cell Group» روی Format کلیک کنید
2. از منوی باز شده گزینه «Unprotect Sheet» را انتخاب کنید.
3. رمز را وارد کنید و اوکی را بزنید.

یا می‌توانید همین کار را از طریق سربرگ «Review» ،‌بخش «Change group» و سپس با کلیک بر «Unprotect Sheet» انجام دهید.

نمایش فرمول‌ها در چندین صفحه اکسل با VBA

برای کاربران حرفه‌ای اکسل که با چندین صفحه داده روبرو هستنند، زدن دکمه Show Formulas در تک‌تک این صفحات زمانبر و کسل‌کننده خواهد بود. خوشبختانه روش سریع‌تری با استفاده از کد‌نویسی وی‌بی‌ای وجود دارد تا به طور خودکار در تمامی صفحات، فرمول نمایش داده شود و در انتهای این متن از مجله فرادرس به آن می‌پردازیم. برای این کار به سربرگ «Developer» بروید و روی گزینه «Visual Basic» کلید کنید تا ویرایشگر کد VBA باز شود. هم چنین این کار از طریق میانبر Alt+F11 نیز ممکن است.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

در ویرایشگر VBA به منوی «Insert» بروید و «Module» را انتخاب کنید. با این کار یک ماژول جدید افزوده می‌شود که می‌توانید کد زیر را در آن وارد کنید.

1Sub ShowFormulas()
2Dim sheet As Worksheet
3Dim IsShown As Boolean
4IsShown = Not (ActiveWindow.DisplayFormulas)
5'Loop through each sheet and set formula display
6For Each sheet In Worksheets
7    sheet.Activate
8    ActiveWindow.DisplayFormulas = IsShown
9Next sheet
10End Sub

این کد در میان تمامی صفحات اکسل می‌چرخد و گزینه Show Formulas را در آن‌ها روشن می‌کند. دقت کنید که اعمال این کد روی صفحاات مختلف به وضعیت آن صفحه نیز بستگی دارد.