آموزش جامع فرمول نویسی در اکسل (فیلم + آموزش تصویری)

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

 

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

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

 

 

ما هم برای شما یک مثال داریم. فرض کنید می‌خواهید در یک خانه فرمول مساحت دایره را بنویسید. آن را به این شکل می‌نویسید: =PI () *A۲^۲

همانطور که می‌بینید این فرمول با یک علامت مساوی شروع شده است. اکسل به این شکل تشخیص می‌دهد که این یک فرمول است. PI () یکی از همان توابع آماده‌ای است که گفتیم. این تابع مقدار عدد پی یا ۳.۱۴ را برمی‌گرداند. A۲ هم آدرس خانه‌ای است که در ستون A و سطر دوم قرار دارد و محتوی شعاع دایره است. «^» هم عملگر توان است. در این فرمول توان ۲ ثابت است.

 

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

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

 

 

برای آگاهی بیشتر از نحوه محاسبه درصد در اکسل همراه ستاره باشید.

به جز این عملگر‌ها یک عملگر رشته‌ای هم در اکسل داریم که تنها عملگر رشته‌ای اکسل است و برای ترکیب رشته‌ها استفاده می‌شود: &. کاری که این عملگر می‌کند این است که رشته موجود در خانه‌ای که آدرسش داده شده را به رشته بعدی می‌چسباند. در این مثال ما می‌خواهیم رشته setare را به رشته Internet magazine بچسبانیم. توجه کنید که هر کلمه را جدا بین” ” تایپ می‌کنیم.

 

 

و این هم حاصل فرمول خواهد بود بعد از اینکه Enter می‌زنیم:

 

 

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

• دسته‌ای دیگر از عملگر‌ها در اکسل هم هستند که علمگر‌های مقایسه‌ای نام دارند و در جدول زیر آن را می‌بینید. این عملگر‌ها همانطور که از اسم‌شان پیداست امکان مقایسه بین دو مقدار را فراهم می‌کنند و حاصل‌شان می‌تواند True یا False (درست یا غلط) باشد.

 

 

آدرس‌دهی خانه‌ها در اکسل برای فرمول نویسی

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

• اگر یک ناحیه مشخص مدنظرتان است از «:» استفاده کنید. مثلا اگر بنویسید C۳:E۷ این عبارت اشاره به خانه‌های بین C۳ و E۷ دارد. به همین شکل می‌توانید خانه‌های یک ستون یا یک سطر را مشخص کنید. مثلا F۱:F۱۰ یا A۴:F۴. اگر بنویسید H:H اکسل تمام خانه‌های ستون H را در نظر می‌گیرد و اگر بنویسید ۵:۵ اکسل تمام خانه‌های سطر پنجم را در نظر خواهد گرفت. عبارت ۵:۱۰ اشاره به تمام خانه‌های سطر‌های پنجم تا دهم دارد به گونه‌ای که شامل تمام ستون‌ها می‌شود و عبارت H:J اشاره به تمام خانه‌های ستون‌های H تا J دارد به گونه‌ای که شامل تمام سطر‌ها شود.
• اگر چند ناحیه مدنظرتان است از «:» و «‚» استفاده کنید. مثلا فرمول (sum (A۱:B۴‚G۳:G۷= می‌گوید مقادیر خانه‌های بین A۱ تا B۴ را با هم جمع کن و سپس حاصل را با حاصل جمع مقادیر بین خانه‌های G۳ تا G۷ جمع ببند. اینجا تابع sum بر روی دو ناحیه اعمال شده است.
• گاهی هم ممکن است ما فقط با ناحیه مشترک بین دو محدوده کار داشته باشیم. در این حالت باید بین این دو محدوده یک فاصله جا بگذاریم. یعنی مثلا در عبارت B۷:D۷ C۶:C۹ منظور ناحیه مشترک بین خانه‌های B۷ تا D۷ و C۶ تا C۹ است.
• در آدرس‌دهی شما حتی می‌توانید خانه‌های یک کاربرگ دیگر را هم آدرس بدهید. این کار با کمک علامت تعجب «!» صورت می‌گیرد. مثلا در فرمول (AVERAGE (Sheet۲! B۱:B۱۰= اکسل از خانه‌های بین B۱ تا B۱۰ از کاربرگ Sheet۲ میانگین می‌گیرد. همانطور که می‌بینید بین نام کاربرگ و آدرس محدوده یک علامت تعجب باید گذاشت.
• خوب است بدانید در اکسل به دو شکل آدرس‌دهی می‌کنیم: مطلق و نسبی. آدرس‌دهی نسبی این است که اگر در خانه C۱ فرمول =A۱+B۱ باشد و ما این فرمول را به خانه C۲ کپی کنیم فرمول متناسب با مکان جدیدی که در آن قرار گرفته تغییر می‌کند و می‌شود A۲+B۲=. تمام آدرس‌دهی‌هایی که تا اینجا داشتیم نسبی بود.
  اما اگر بخواهیم آدرس ما مطلق باشد و با جابه‌جا کردنش تغییری در آن به وجود نیاید، باید از «$» استفاده کنیم. به این شکل که آن را قبل از نام ستون و قبل از شماره سطر می‌گذاریم: $A$۱. اینجا خانه A۱ به شیوه مطلق آدرسش نوشته شده است و با کپی کردن این آدرس در جای دیگر هیچ تغییری در آن به وجود نمی‌آید.
  آدرس‌دهی می‌تواند کاملا ترکیبی هم باشد. یعنی در یک فرمول هم از شیوه نسبی و هم مطلق استفاده شده باشد. مثلا در فرمول $A$۱+B۲= که در خانه C۱ تایپ شده است اگر این فرمول به خانه C۲ کپی شود خواهد شد: $A$۱+B۳= همانطور که می‌بینید آدرس A۱ تغییری نمی‌کند، ولی B۲ به B۳ تبدیل می‌شود.

 

توابع در اکسل

همانطور که قبلا گفتیم، تابع‌ها یکی از اجزای عبارت در فرمول‌ها هستند. هر تابع دارای نام است و ممکن است اصلا ورودی نداشته باشد. هر تابع یک عملیات را انجام می‌دهد. به طور کلی در اکسل تابع به دو صورت وجود دارد: تابع‌های درونی (Built in) و تابع‌های تعریف شده به وسیله کاربر (User Defined).

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

۱- توابع مالی (Financial)
۲- توابع تاریخ و زمان (Date and time)
۳- توابع ریاضی و مثلثاتی (Math and trig)
۴- توابع رشته‌ای (Text)
۵- توابع منطقی (Logical)
۶- توابع آماری (Statistical)
۷- توابع جستوجو و مرجع (Lookup and Refrences)
۸- توابع بانک اطلاعاتی (Date base)
۹- توابع بررسی اطلاعات (Information)
۱۰- توابع هوش تجاری (Cube)
۱۱- توابع مهندسی (Engineering)

هنگام فرمول نویسی در اکسل استفاده از توابع کار‌ها را ساده‌تر می‌کند. مثلا فرض کنید می‌خواهیم خانه‌های A۱ تا A۷ را با هم جمع کنیم. اولین چیزی که به ذهن ما می‌رسد تایپ این عبارت باشد: =A۱+A۲+A۳+A۴+A۵+A۶+A۷

در حالی‌که با استفاده از تابع جمع می‌توان نوشت: (SUM (A۱:A۷=

 

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

تابع‌ها را می‌توان به دو روش استفاده کرد: تایپ خود تابع یا استفاده از گزینه Insert Function.
تایپ تابع مستلزم دانستن املای تابع و تعداد آرگومان‌های مورد نیاز تابع است. هرچند اکسل برای آسان کردن تایپ تابع قابلیت Auto Complete دارد.

گزینه Insert Function هم از زبانه Formulas در دسترس است:

 

 

پس از انتخاب این گزینه پنجره‌ای برای انتخاب تابع مورد نظر باز می‌شود:

 

 

همانطور که می‌بینید در اینجا به انواع تابع که قبلا آن‌ها را برشمردیم دسترسی دارید. زیر کادر Select a function هم توضیحاتی درباره هر تابعی که روی آن کلیک کنید نمایش داده می‌شود که به شما اطلاعاتی در مورد نوع یا تعداد آرگومان‌ها و یا خروجی تابع می‌دهد.

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

 

این گزینه در اکسل‌های جدید به طور خودکار فعال است. در غیر این صورت باید از طریق Options به تنظیمات اکسل رفته و در قسمت Advanced به دنبال چنین گزینه‌ای در مورد Formula بگردید!

پیشنهاد می‌شود آموزش‌های سایت ستاره در مورد کار با ورد را نیز مطالعه کنید: آموزش تبدیل ورد به عکس، آموزش ساخت فهرست در ورد (Microsot Word) و آموزش تصویری و گام به گام درج نیم فاصله در ورد

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