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

مقدمه

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

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

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

فهرست مطالب

1. فرمول در اکسل چیست؟
2. فرمول‌نویسی چه کاربردی دارد؟
3. تفاوت Formula و Function
4. ساختار یک فرمول در اکسل
5. روش نوشتن اولین فرمول
6. آشنایی با عملگرهای ریاضی
7. عملگرهای مقایسه‌ای و متنی
8. ترتیب انجام محاسبات
9. استفاده از آدرس سلول در فرمول
10. ارجاع نسبی در اکسل
11. ارجاع مطلق در اکسل
12. ارجاع ترکیبی در اکسل
13. کپی کردن فرمول‌ها با AutoFill
14. ویرایش و مشاهده فرمول‌ها
15. فرمول‌نویسی بین شیت‌ها
16. خطاهای رایج در فرمول‌نویسی
17. نکات مهم برای نوشتن فرمول‌های بهتر
18. تمرین عملی
19. جمع‌بندی و سؤالات متداول

۱. فرمول در اکسل چیست؟

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

تمام فرمول‌های اکسل با علامت مساوی = شروع می‌شوند. این علامت به اکسل اعلام می‌کند محتوایی که پس از آن نوشته می‌شود یک دستور محاسباتی است، نه یک متن یا عدد معمولی.

برای مثال، فرمول زیر دو عدد را با یکدیگر جمع می‌کند:

content_copy text

=10+20

پس از فشردن کلید Enter، نتیجه یعنی عدد 30 داخل سلول نمایش داده می‌شود. با انتخاب همان سلول، اصل فرمول را می‌توانید در Formula Bar یا نوار فرمول مشاهده کنید.

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

۲. فرمول‌نویسی در اکسل چه کاربردی دارد؟

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

برخی از مهم‌ترین کاربردهای فرمول در اکسل عبارت‌اند از:

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

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

۳. تفاوت Formula و Function در اکسل

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

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

content_copy text

=A1+B1+C1

این فرمول مقادیر موجود در سه سلول را با یکدیگر جمع می‌کند.

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

content_copy text

=SUM(A1:C1)

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

در واقع، هر تابع می‌تواند بخشی از یک فرمول باشد؛ اما هر فرمول الزاماً شامل تابع نیست. برای مثال، =A1*B1 یک فرمول بدون تابع است، درحالی‌که =SUM(A1:A10) فرمولی است که از تابع SUM استفاده می‌کند.

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

۴. ساختار یک فرمول در اکسل

یک فرمول می‌تواند از بخش‌های مختلفی تشکیل شود. برای مثال، فرمول زیر را در نظر بگیرید:

content_copy text

=(B2*C2)-D2

اجزای این فرمول عبارت‌اند از:

• علامت = که شروع فرمول را مشخص می‌کند.
• B2 و C2 که آدرس سلول‌ها هستند.
• علامت * که عملگر ضرب است.
• D2 که آدرس یک سلول دیگر است.
• علامت – که برای تفریق استفاده می‌شود.
• پرانتز که اولویت محاسبه را تعیین می‌کند.

اکسل ابتدا مقدار سلول B2 را در مقدار سلول C2 ضرب کرده و سپس مقدار D2 را از نتیجه کم می‌کند. آشنایی با این ساختار، پایه اصلی نوشتن فرمول‌های صحیح است.

۵. چگونه در اکسل فرمول بنویسیم؟

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

1. سلولی را که می‌خواهید نتیجه در آن نمایش داده شود، انتخاب کنید.
2. علامت مساوی = را تایپ کنید.
3. عددها یا آدرس سلول‌های موردنظر را وارد کنید.
4. عملگر محاسباتی مناسب را بنویسید.
5. کلید Enter را فشار دهید.

برای مثال، اگر عدد 15 در سلول A1 و عدد 20 در سلول B1 قرار دارد، می‌توانید در سلول C1 فرمول زیر را بنویسید

content_copy text

=A1+B1

پس از زدن Enter، عدد 35 در سلول C1 نمایش داده می‌شود. اگر مقدار A1 را از 15 به 25 تغییر دهید، نتیجه سلول C1 نیز به‌صورت خودکار به 45 تبدیل خواهد شد.

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

۶. عملگرهای ریاضی در اکسل

عملگرها علامت‌هایی هستند که نوع محاسبه را مشخص می‌کنند. مهم‌ترین عملگرهای ریاضی در اکسل عبارت‌اند از:

• علامت + برای جمع
• علامت – برای تفریق
• علامت * برای ضرب
• علامت / برای تقسیم
• علامت ^ برای توان
• علامت % برای درصد

مثال جمع در اکسل

برای جمع کردن مقدار دو سلول از فرمول زیر استفاده می‌کنیم:

content_copy text

=A1+B1

مثال تفریق در اکسل

برای کم کردن مقدار B1 از A1 می‌نویسیم:

content_copy text

=A1-B1

مثال ضرب در اکسل

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

content_copy text

=B2*C2

اگر B2 تعداد کالا و C2 قیمت هر واحد باشد، نتیجه فرمول برابر مبلغ کل خواهد بود.

مثال تقسیم در اکسل

برای تقسیم مقدار سلول A1 بر مقدار سلول B1 از فرمول زیر استفاده می‌شود:

content_copy text

=A1/B1

اگر مقدار سلول B1 صفر یا خالی باشد، ممکن است خطای #DIV/0! نمایش داده شود.

مثال توان در اکسل

برای محاسبه توان دوم مقدار سلول A1 می‌توان نوشت:

content_copy text

=A1^2

مثال محاسبه درصد

اگر مبلغ کالا در سلول A1 و درصد تخفیف در سلول B1 باشد، مبلغ تخفیف با فرمول زیر محاسبه می‌شود:

content_copy text

=A1*B1

در این حالت، بهتر است مقدار سلول B1 به‌شکل درصد، برای مثال 20%، وارد شده باشد. روش دیگر این است که درصد را مستقیماً داخل فرمول بنویسید:

content_copy text

=A1*20%

۷. عملگرهای مقایسه‌ای در اکسل

عملگرهای مقایسه‌ای برای مقایسه دو مقدار استفاده می‌شوند. نتیجه این مقایسه‌ها معمولاً یکی از مقادیر TRUE یا FALSE است.

مهم‌ترین عملگرهای مقایسه‌ای عبارت‌اند از:

• علامت = برای بررسی برابری
• علامت > برای بزرگ‌تر بودن
• علامت < برای کوچک‌تر بودن
• علامت >= برای بزرگ‌تر یا مساوی بودن
• علامت <= برای کوچک‌تر یا مساوی بودن
• علامت <> برای نابرابر بودن

برای مثال، فرمول زیر بررسی می‌کند که آیا مقدار سلول A1 از 100 بیشتر است یا خیر:

content_copy text

=A1>100

اگر مقدار A1 بیشتر از 100 باشد، نتیجه TRUE و در غیر این صورت نتیجه FALSE خواهد بود. این عملگرها در ساخت فرمول‌های شرطی، به‌ویژه همراه با تابع IF، کاربرد بسیار زیادی دارند.

۸. عملگر متنی در اکسل

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

فرض کنید نام فرد در سلول A1 و نام خانوادگی او در سلول B1 قرار دارد. برای اتصال این دو مقدار می‌توان نوشت:

content_copy text

=A1&B1

این فرمول نام و نام خانوادگی را بدون فاصله کنار یکدیگر قرار می‌دهد. برای اضافه کردن فاصله باید آن را داخل علامت نقل‌قول قرار دهیم:

content_copy text

=A1&” “&B1

هر متنی که مستقیماً داخل فرمول نوشته می‌شود باید میان علامت‌های نقل‌قول دوتایی ” ” قرار بگیرد. در این مثال، یک فاصله بین نام و نام خانوادگی ایجاد شده است.

۹. ترتیب انجام محاسبات در اکسل

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

1. عبارت‌های داخل پرانتز
2. توان
3. ضرب و تقسیم
4. جمع و تفریق

برای مثال، نتیجه فرمول زیر برابر 14 است:

content_copy text

=2+3*4

اکسل ابتدا 3*4 را محاسبه کرده و سپس عدد 2 را به آن اضافه می‌کند:

content_copy text

2+12=14

اگر بخواهیم ابتدا جمع انجام شود، باید از پرانتز استفاده کنیم:

content_copy text

=(2+3)*4

نتیجه این فرمول برابر 20 است؛ زیرا اکسل ابتدا عبارت داخل پرانتز را محاسبه می‌کند:

content_copy text

5*4=20

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

۱۰. استفاده از آدرس سلول‌ها در فرمول

هر سلول اکسل دارای یک آدرس منحصربه‌فرد است. آدرس سلول از حرف ستون و شماره ردیف تشکیل می‌شود. برای مثال، B3 به سلولی اشاره می‌کند که در ستون B و ردیف 3 قرار دارد.

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

content_copy text

=5*100000

اما اگر تعداد یا قیمت تغییر کند، باید خود فرمول را ویرایش کنید. روش بهتر این است که تعداد در B2 و قیمت در C2 قرار گیرد و فرمول زیر نوشته شود:

content_copy text

=B2*C2

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

۱۱. ارجاع نسبی در اکسل چیست؟

ارجاع نسبی یا Relative Reference حالت پیش‌فرض آدرس‌دهی در اکسل است. در این حالت، وقتی یک فرمول را به سلول دیگری کپی می‌کنید، آدرس سلول‌های داخل فرمول متناسب با موقعیت جدید تغییر می‌کند.

فرض کنید در سلول D2 فرمول زیر را نوشته‌ایم:

content_copy text

=B2*C2

اگر این فرمول را یک ردیف به پایین، یعنی سلول D3، کپی کنیم، اکسل آن را به‌شکل زیر تغییر می‌دهد:

content_copy text

=B3*C3

در ردیف بعدی نیز فرمول به =B4*C4 تبدیل خواهد شد. این رفتار برای محاسبه اطلاعات هر ردیف بسیار مفید است.

برای مثال، اگر ستون B شامل تعداد و ستون C شامل قیمت واحد باشد، کافی است فرمول را فقط در اولین ردیف بنویسید و سپس آن را به پایین بکشید. اکسل آدرس‌ها را متناسب با هر ردیف تغییر می‌دهد.

۱۲. ارجاع مطلق در اکسل چیست؟

گاهی نمی‌خواهیم آدرس یک سلول هنگام کپی کردن فرمول تغییر کند. در این شرایط باید از ارجاع مطلق یا Absolute Reference استفاده کنیم.

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

content_copy text

=$F$1

فرض کنید قیمت کالا در سلول C2 قرار دارد و نرخ مالیات 10% در سلول F1 نوشته شده است. برای محاسبه مالیات می‌توانیم در سلول D2 فرمول زیر را وارد کنیم:

content_copy text

=C2*$F$1

اگر این فرمول را به ردیف‌های پایین کپی کنیم، آدرس C2 به C3، C4 و سلول‌های بعدی تغییر می‌کند؛ اما آدرس $F$1 ثابت باقی می‌ماند. دلیل آن وجود علامت دلار قبل از ستون F و ردیف 1 است.

برای تغییر سریع نوع ارجاع، هنگام ویرایش فرمول روی آدرس سلول قرار بگیرید و کلید F4 را فشار دهید. با هر بار فشردن این کلید، حالت آدرس بین ارجاع نسبی، مطلق و ترکیبی تغییر می‌کند. در بعضی لپ‌تاپ‌ها ممکن است لازم باشد از Fn + F4 استفاده کنید.

۱۳. ارجاع ترکیبی در اکسل

ارجاع ترکیبی یا Mixed Reference حالتی است که فقط ستون یا فقط ردیف ثابت می‌ماند. این ارجاع به دو شکل نوشته می‌شود:

content_copy text

=$A1

در این حالت، ستون A ثابت است؛ اما شماره ردیف هنگام کپی تغییر می‌کند.

content_copy text

=A$1

در این حالت، ردیف 1 ثابت است؛ اما حرف ستون هنگام کپی تغییر می‌کند.

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

برای مثال، در ساخت جدول ضرب می‌توان از فرمولی استفاده کرد که عدد ابتدای هر ردیف را از یک ستون ثابت و عدد بالای هر ستون را از یک ردیف ثابت دریافت کند:

content_copy text

=$A2*B$1

وقتی این فرمول را به سمت راست یا پایین کپی کنیم، ستون A و ردیف 1 ثابت می‌مانند؛ اما سایر بخش‌های آدرس متناسب با محل جدید تغییر می‌کنند.

۱۴. کپی کردن فرمول‌ها با AutoFill

پس از نوشتن فرمول در یک سلول، می‌توانید آن را با استفاده از Fill Handle به سلول‌های دیگر منتقل کنید. Fill Handle همان مربع کوچک گوشه پایین سمت راست سلول انتخاب‌شده است.

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

اگر در ستون کناری داده‌های پیوسته وجود داشته باشد، می‌توانید روی Fill Handle دو بار کلیک کنید. در این حالت، اکسل فرمول را معمولاً تا آخرین ردیف محدوده مجاور ادامه می‌دهد.

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

۱۵. مشاهده و ویرایش فرمول‌ها

پس از ثبت فرمول، نتیجه آن در سلول نمایش داده می‌شود؛ اما اصل فرمول در نوار فرمول قابل‌مشاهده است. برای ویرایش فرمول می‌توانید سلول را انتخاب کرده و داخل Formula Bar کلیک کنید.

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

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

content_copy text

Ctrl + `

کلید موردنظر معمولاً در کنار عدد 1 و زیر کلید Esc قرار دارد. با فشردن دوباره همین میانبر، نمایش نتایج فعال می‌شود.

اگر به‌جای نتیجه، خود فرمول داخل سلول نمایش داده می‌شود، ممکن است حالت Show Formulas فعال باشد یا فرمت سلول روی Text قرار گرفته باشد. در این شرایط، حالت نمایش فرمول‌ها را غیرفعال کنید یا فرمت سلول را به General تغییر دهید و فرمول را دوباره ثبت کنید.

۱۶. نوشتن فرمول بین شیت‌های مختلف

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

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

content_copy text

=Sheet2!A1

این فرمول مقدار سلول A1 از شیت Sheet2 را نمایش می‌دهد.

اگر نام شیت دارای فاصله باشد، نام آن باید داخل علامت نقل‌قول تکی قرار گیرد:

content_copy text

=’گزارش فروش’!B2

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

۱۷. خطاهای رایج در فرمول‌نویسی اکسل

هنگام نوشتن فرمول ممکن است اکسل پیام‌های خطای مختلفی نمایش دهد. شناخت این خطاها باعث می‌شود مشکل را سریع‌تر پیدا کنید.

خطای #DIV/0!

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

content_copy text

=A1/B1

اگر مقدار B1 صفر باشد، تقسیم امکان‌پذیر نیست. برای رفع مشکل باید مقدار مخرج را بررسی کنید.

خطای #NAME?

این خطا معمولاً زمانی رخ می‌دهد که نام یک تابع یا محدوده را اشتباه نوشته باشید. برای مثال:

content_copy text

=SMU(A1:A5)

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

خطای #VALUE!

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

content_copy text

=A1*B1

اگر یکی از سلول‌ها شامل یک متن غیرعددی باشد، احتمال نمایش خطای #VALUE! وجود دارد.

خطای #REF!

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

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

خطای #N/A

این خطا معمولاً در توابع جستجو نمایش داده می‌شود و به این معنی است که مقدار موردنظر پیدا نشده یا در دسترس نیست. در بخش توابع جستجوی اکسل بیشتر با این خطا آشنا خواهیم شد.

خطای #NUM!

خطای #NUM! زمانی ایجاد می‌شود که یک فرمول با عدد نامعتبر یا محاسبه‌ای خارج از محدوده قابل‌قبول روبه‌رو شود.

خطای #####

نمایش چند علامت مربع یا هشتگ همیشه به معنی خطای فرمول نیست. این حالت معمولاً زمانی رخ می‌دهد که عرض ستون برای نمایش عدد یا تاریخ کافی نباشد. با افزایش عرض ستون یا استفاده از AutoFit مشکل برطرف می‌شود.

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

خطای وابستگی دوری یا Circular Reference

Circular Reference زمانی اتفاق می‌افتد که یک فرمول به‌صورت مستقیم یا غیرمستقیم به سلول خودش وابسته باشد. برای مثال، اگر در سلول A1 فرمول زیر را بنویسید، یک ارجاع دوری ایجاد می‌شود:

content_copy text

=A1+10

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

۱۸. چرا فرمول به‌جای نتیجه نمایش داده می‌شود؟

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

1. فرمت سلول روی Text قرار گرفته است.
2. قبل از علامت مساوی یک آپاستروف قرار دارد.
3. گزینه Show Formulas فعال شده است.
4. فرمول بدون علامت = نوشته شده است.
5. قبل از مساوی یک فاصله وجود دارد.

برای رفع مشکل، فرمت سلول را روی General تنظیم کنید. سپس روی سلول کلیک کرده، کلید F2 را بزنید و Enter را فشار دهید. اگر مشکل به دلیل فعال بودن Show Formulas باشد، از تب Formulas آن را خاموش کنید یا میانبر `Ctrl + “ را بزنید.

۱۹. محاسبه خودکار و دستی در اکسل

اکسل معمولاً در حالت Automatic Calculation قرار دارد. در این حالت، با تغییر داده‌های ورودی، نتایج فرمول‌ها به‌صورت خودکار به‌روزرسانی می‌شوند.

اگر مقدار یک سلول را تغییر دادید اما نتیجه فرمول عوض نشد، ممکن است حالت محاسبه روی Manual قرار گرفته باشد. برای بررسی آن، وارد تب Formulas شوید و از بخش Calculation Options گزینه Automatic را انتخاب کنید.

در حالت Manual می‌توان با فشردن کلید F9 محاسبات را به‌روزرسانی کرد. این حالت بیشتر برای فایل‌های بسیار بزرگ و سنگین کاربرد دارد و برای کاربران مبتدی بهتر است محاسبات روی Automatic باقی بمانند.

۲۰. چند مثال کاربردی از فرمول‌نویسی ساده

برای درک بهتر موضوع، جدولی را در نظر بگیرید که ستون B تعداد کالا، ستون C قیمت واحد و ستون D درصد تخفیف را نگهداری می‌کند.

محاسبه مبلغ اولیه

در سلول E2 فرمول زیر را می‌نویسیم:

content_copy text

=B2*C2

محاسبه مبلغ تخفیف

در سلول F2 می‌نویسیم:

content_copy text

=E2*D2

محاسبه مبلغ پس از تخفیف

در سلول G2 فرمول زیر نوشته می‌شود:

content_copy text

=E2-F2

همچنین می‌توان محاسبه مبلغ پس از تخفیف را مستقیماً در یک فرمول انجام داد:

content_copy text

=(B2*C2)*(1-D2)

محاسبه مالیات با نرخ ثابت

اگر نرخ مالیات در سلول J1 قرار داشته باشد، فرمول زیر را در H2 وارد می‌کنیم:

content_copy text

=G2*$J$1

علامت‌های دلار باعث می‌شوند هنگام کپی فرمول به ردیف‌های پایین، آدرس نرخ مالیات ثابت بماند.

محاسبه مبلغ نهایی

برای اضافه کردن مالیات به مبلغ پس از تخفیف می‌نویسیم:

content_copy text

=G2+H2

این مثال‌ها نشان می‌دهند چگونه می‌توان یک محاسبه چندمرحله‌ای را با فرمول‌های ساده انجام داد.

۲۱. نکات مهم برای فرمول‌نویسی بهتر

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

1. تمام فرمول‌ها را با علامت مساوی شروع کنید.
2. به‌جای اعداد ثابت، تا حد امکان از آدرس سلول‌ها استفاده کنید.
3. نرخ‌ها و درصدهای ثابت را در سلول جداگانه قرار دهید.
4. برای ثابت نگه داشتن آدرس‌ها از ارجاع مطلق استفاده کنید.
5. در محاسبات چندمرحله‌ای از پرانتز کمک بگیرید.
6. فرمول‌های طولانی را به چند مرحله ساده‌تر تقسیم کنید.
7. عنوان ستون‌ها را واضح و قابل‌فهم انتخاب کنید.
8. پس از کپی کردن فرمول، چند نتیجه را به‌صورت دستی بررسی کنید.
9. اعداد قابل‌محاسبه را به‌صورت Text ذخیره نکنید.
10. قبل از حذف سطر یا ستون، وابستگی فرمول‌ها را بررسی کنید.
11. از فاصله‌های غیرضروری و حروف فارسی داخل نام توابع خودداری کنید.
12. برای متن‌های مستقیم داخل فرمول از علامت نقل‌قول استفاده کنید.
13. فایل را در مراحل مختلف ذخیره کنید.
14. هنگام مشاهده خطا، آدرس‌ها و نوع داده‌ها را بررسی کنید.
15. از نوشتن یک عدد ثابت در تعداد زیادی فرمول خودداری کنید.

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

۲۲. تمرین عملی فرمول‌نویسی در اکسل

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

• ردیف
• نام کالا
• تعداد
• قیمت واحد
• درصد تخفیف
• مبلغ اولیه
• مبلغ تخفیف
• مبلغ پس از تخفیف
• مالیات
• مبلغ نهایی

سپس چند کالای فرضی وارد کنید و مراحل زیر را انجام دهید:

1. تعداد را در قیمت واحد ضرب کنید.
2. مبلغ تخفیف را بر اساس درصد تخفیف محاسبه کنید.
3. تخفیف را از مبلغ اولیه کم کنید.
4. نرخ مالیات را در یک سلول جداگانه قرار دهید.
5. آدرس نرخ مالیات را در فرمول مطلق کنید.
6. مبلغ مالیات را محاسبه کنید.
7. مبلغ مالیات را به مبلغ پس از تخفیف اضافه کنید.
8. فرمول‌ها را با AutoFill به ردیف‌های پایین انتقال دهید.
9. یکی از قیمت‌ها را تغییر دهید و به‌روزرسانی خودکار نتایج را بررسی کنید.
10. چند نتیجه را با ماشین‌حساب کنترل کنید.

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

جمع‌بندی

در این مقاله، آموزش فرمول نویسی در اکسل را از صفر شروع کردیم. یاد گرفتیم که فرمول یک عبارت محاسباتی است و باید با علامت مساوی آغاز شود. همچنین تفاوت Formula و Function را بررسی کردیم و دیدیم که تابع، یک دستور آماده است که می‌تواند داخل یک فرمول قرار بگیرد.

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

در ادامه، ارجاع نسبی، مطلق و ترکیبی را با مثال توضیح دادیم. ارجاع نسبی هنگام کپی فرمول تغییر می‌کند، ارجاع مطلق ثابت باقی می‌ماند و ارجاع ترکیبی فقط ستون یا ردیف را ثابت نگه می‌دارد. در پایان نیز خطاهای مهمی مانند #DIV/0!، #VALUE!، #REF! و Circular Reference را شناختیم.

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