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

به نظرم رسیده است که مقاله‌ای بسیار ساده و ابتدایی برای آموزش اصول و مقدمات اولیه اکسل بنویسیم که البته برای من کار ساده‌ای نیست زیرا نوشتن از بدیهیات بسیار دشوار است اماا این گونه مقالات است که ذهن شما را سامان می‌دهد و به آنها کمک می‌کند تا این نرم افزار را بهتر بشناسند. راستی اکسل یک ویژگی دارد که من آن را «ویژگی مادر بزرگ» نامیده‌ام. یعنی اکسل مانند «مادر بزرگ‌ها» است و سعی می‌کند که سخت‌گیری بسیار کمی داشته باشد‌ و وقت که نوه‌ها خواستند هر چند تا 🍬 که خواستند را به آن‌ها می‌دهد. (در این مقاله از ایموجی🍬 برای نشان دادن این حالت استفاده کرده‌ام.)

شروع فرمول اکسل

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

+ فرمول شما پاسخ دارد و اکسل آن را در سلول نمایش می‌دهد.

+ فرمول شما خطا می‌شود که اکسل پیغام خطا‌یی را نمایش می‌دهد. (شرح خطاهای اکسل را مطالعه کنید)

+ فرمول شما یک اشکال جزئی دارد مثلا پرانتز آخر را نبسته‌اید،‌ اکسل به شما پیغامی می‌دهد و آن را درست می‌کند🍬. 

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

فاصله در فرمول نویسی

اکسل هم مانند بسیاری از زبان‌های برنامه نویسی است و برایش فاصله‌های فرمول مهم نیست. مثلا تمامی ۳ فرمول زیر برای اکسل یکسان هستند:

=2+2
=2 + 2
=2   +    2

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

فرمول‌های چند خطی اکسل

با زدن کلید Alt+ Enter در هنگام تایپ یک نوشته یا فرمول در یک سلول، به سطر بعدی می‌رویم و اگر فرمول‌ها طولانی شوند،‌ ترجیح می‌دهیم که برای خواناتر شدن فرمول این‌کار را انجام دهیم:

= 2000 + 3000 +
  8000 + 8000

انواع خروجی‌های یک فرمول اکسل

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

• عدد
  • تاریخ و زمان (یک نوع عدد هستند)
• متن
• خطا (انواع خطاها DIV, REF, N/A, NAME
• خاص ... (از اشاره به آنها خودداری می‌کنم زیرا می‌خواهم که ذهن شما متمرکز بر روی همین اصول ساده بماند)

حتما لینک مقاله انواع خطاهای اکسل را قسمت «مقالات پیشنهادی» انتهای همین صفحه مطالعه فرمایید.

استفاده از آدرس سلول‌ها در یک فرمول

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

= A1 + A2

🍬  اکسل بر خلاف بسیاری از زبان‌های برنامه نویسی است و حروف کوچک و بزرگ در فرمول نویسی تفاوت نمی‌کند.

محاسبه بر روی سلول‌های یک شیت دیگر

هر فرمول در یک شیت نوشته می‌شود و اگر بنویسیم = A1 + A2  یعنی سلول A1‌  و A2 همین شیتی که در آن فرمول نوشته شده است را با هم جمع کن. اگر بخواهیم فرمولی بنویسیم که مقادیر را از سلول‌های یک شیت دیگر بخواند،‌ باید نام شیت و سپس علامت ! و سپس نام سلول را بنویسیم. مثلا فرمول زیر سلول A1 را از شیت sales با سلول B2 از شیت cost جمع می‌زند:

= sales!A1 + cost!B2

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

از آنجایی که نام شیت‌ها در فرمول‌ها ظاهر می‌شود، باید برای شیت‌ها نام‌های مناسبی بگذارید مانند sales و forosh_1401 و ....  . نام مناسب یعنی:  
  - کوتاه باشد.  
  - با معنی باشد.  
  ـ انگلیسی یا فینگلیش باشد و نه فارسی.  
  ـ به جای فاصله از underline استفاده شود. 

آدرس نسبی در فرمول و خاصیت کپی کردن فرمول

این ویژگی اکسل بسیار ساده و پیش پا افتاده به نظر می‌آید و  همگان از آن استفاده می‌کنند اما کمتر کسی پی به حقیقت آن برده است. بگذارید از شما بخواهم که فرمول زیر را بخوانید:

= C3 + 2

تقریبا همه شما این فرمول را خواهید خواند «سلول سی سه بعلاوه ۲» اما باید بگویم که اشتباه می‌کنید 🙄 یکبار دیگر سعی کنید آن را بخوانید. 

این فرمول را هیچ کسی نمی‌تواند بخواند 😵 . باور کنید. 

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

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

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

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

حال برای درک فرمول = C3 + 2 بیایید تصویر زیر را در نظر بگیریم و ببینیم که این فرمول را چگونه باید خواند.

اگر فرمول = C3 + 2 در سلول زرد بنویسیم، آنگاه جای C3 در سمت راست آن است. بنابراین فرمول = C3 + 2 را فقط باید بخوانیم  
 «سلول سمت راستیش بعلاوه ۲»

اگر فرمول = C3 + 2 در سلول قرمز بنویسیم، آنگاه جای C3 در ۲ سلول بالای آن است . بنابراین فرمول = C3 + 2 را فقط باید بخوانیم  
«دو سلول بالائیش بعلاوه ۲»

اگر فرمول = C3 + 2 در سلول سبز بنویسیم، آنگاه جای C3 در ۲ سلول قبل آن است . بنابراین فرمول = C3 + 2 را فقط باید بخوانیم  
«دو سلول قبلیش بعلاوه ۲»

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

شاید بگویید که ای کاش اکسل کمی واضح‌تر آدرس نسبی را برای ما می‌نوشت که متوجه می‌شدیم و فرمول = C3 + 2 واقعا مبهم است و همه را به اشتباه می‌اندازد!

باید بگویم که خاصیت 🍬 اکسل است که این حقیقت را از شما پنهان می‌کند. باید بدانید که روش فرمول نویسی در اکسل (و البته سایر صفحه گسترده‌های ابتدایی مانند LOTUS) وجود دارد به نام R1C1 که در این روش آدرس‌های نسبی کاملا مشخص هستند. مثلا برای آنکه بخواهیم مقدار سلول قبلی را با عدد ۲ جمع کنیم، می‌نویسیم  = RC[-1] + 2  . یعنی در همین سطر و ۱ ستون قبلی را با ۲ جمع کن. همانطور که می‌بینید این روش کمی سخت است و دقت می‌خواهد به همین دلیل در اکسل تصمیم گرفته شد که آدرس‌ها به سادگی جدول‌ روزنامه‌ها نوشته شود تا همگان آن را بتوانند بخوانند.

در ادامه برخی از نکات دیگر مفاهیم و اصول اولیه فرمول نویسی اکسل در ویدئوی زیر آمده است:

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

بر روی ۲ مقدار با اکسل عملیاتی را انجام می‌دهیم‌، مثلا ۲ عدد را با هم جمع می‌کنیم و اینکار با عملگر + انجام می‌شود. در اینجا می‌خواهیم نگاهی به عملگرهای اکسل بیندازیم.

عملگر + در اکسل برای جمع زدن، عملگر - برای تفریق، عملگر * برای ضرب و عملگر / برای تقسیم در اکسل بکار می‌رود.

هشدار: اگر Control Panel -> region کامپیوتر شما بر روی فارسی تنظیم شده باشد، آنگاه «/» نشان دهنده قسمت اعشاری یک عدد است و عملیات تقسیم را انجام نمی‌دهد. در این صورت حتما به Region‌ بروید و گزینه Additional Settings را بزنید و سپس مانند تصویر زیر Decimal Symbol‌ را بر روی نقطه « . » تنظیم کنید. همچنین توصیه می‌شود که List seprator را علامت کاما یعنی « , »بگذارید.

در اکسل برای توان عملگر  ^ را داریم و برای چسباندن دو مقدار به یکذیگر عملگر&  بکار می‌رود. مثلا برای چسباندن نام و نام خانوادگی فرمول زیر را می‌نویسیم:

= "Kaveh" & " Irani"

نکته ۱) برای جذر عملگر جداگانه‌ای در اکسل نداریم اما کافی است که عدد را به توان 1/2 برسانیم. مثلا جذر عدد 25 در اکسل اینگونه محاسبه می‌شود:

= 25 ^ (1/2)

نکته ۲) برای باقی مانده در اکسل عملگری نداریم و باید با تابع mod (مخفف modulo) اینکار انجام شود. در فرمول زیر باقیمانده تقسیم عدد ۱۳ بر ۲ را محاسبه می‌کنیم.

= MOD(13, 2)

اکسل عملگر اجتماع دو مجموعه را ندارد و برای اینکار کافی است که در تابعی مانند SUM از علامت کاما استفاده کنید. مثلا برای جمع اجتماع ستون A با ستون X فرمول زیر را می‌نویسیم:

=SUM( A:A, X:X)

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

=SUM(A:A 2:2)

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

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

توجه) ریاضی و فرمول‌های اکسل را از چپ به راست باید بخوانید بنابراین عملگر  « < » در فرمول یعنی بزرگتر و عملگر « > » یعنی کوچکتر.  عملگر « =< » را بزرگتر یا مساوی است و عملگر « =>  » است. در فرمول نویسی اکسل عملگر نامساوی به شکل « <>  » نوشته می‌شود. همچنین عملگر مساوی هم « = » است.

همچنین توجه داشته باشید که مقدار TRUE و یا FLASE از نظر کامپیوتر متن نیستند و مقادیر بولین تلقی می‌شوند که روی آن‌ها می‌توانیم محاسبات ریاضی را انجام دهیم و در این حالت TRUE معادل عدد ۱ خواهد شد و FALSE هم معادل عدد 0 است. برای مثال حاصل فرمول زیر عدد ۲ خواهد شد:

= TRUE + TRUE

نکته مهم) توجه داشته باشید که در دنیای اکسل عدد ۱ با متن ۱ برابر نیست و حاصل فرمول زیر FALSE خواهد شد. این نکته در نوشتن IF و همچنین جستجو توسط توابعی مانند VLOOKUP بسیار مهم است:

= 1 = "1"

پرسش ۱) کاربرد عملگرهای منطقی چیست؟
پاسخ) با این عملگرها می‌توانیم فرمول‌های شرطی را بنویسم. مانند فرمول زیر که می‌خواهیم اگر فروش (سلول A1) بیشتر از 10000 بود، %5 تخفیف در نظر بگیریم وگرنه %0 تخفیف:

=IF( A1 > 10000, 5% , 0)

برای نوشتن فرمول‌های شرطی مقاله «تابع IF اکسل - نوشتن فرمول شرطی در اکسل» را بخوانید.

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

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

برای خاموش کردن موتور محاسباتی اکسل از تب Formulas گزینه Calculation Options را بزنید و حالت Manual را انتخاب کنید. از این لحظه دیگر اکسل شما هیچ فرمولی را تازه سازی نمی‌کند. البته هر گاه که خواستید می‌توانید از همین جا مجدد حالت محاسبات فرمول‌ها را به Automatic تغییر دهید.