فرمولها و توابع در اکسل
هر فرمول عبارتی است که مقدار سلول را محاسبه میکند. توابع، فرمولهای ازپیش تعریف شدهای هستند که از قبل در اکسل وجود دارند.
بهعنوان مثال، سلول A3 در شکل زیر شامل فرمولی است که مقادیر سلولهای A1 و A2 را باهم جمع میکند.
بهعنوان مثال، سلول A3 در شکل زیر دربرگیرنده تابع حاصلجمعی است که مجموع A1:A2 را محاسبه میکند.
اگر تایپ فرمول دارید ثبت سفارش نمایید.
واردکردن یک فرمول
برای وارد نمودن یک فرمول مراحل ذیل را دنبال کنید.
- یک سلول انتخاب کنید.
- برای آنکه اکسل بداند قصد دارید فرمول وارد کنید یک علامت تساوی (=) تایپ کنید.
- برای نمونه فرمول A1+A2 را تایپ کنید.
نکته: بجای تایپ A1 و A2، سلولهای A1 و A2 را انتخاب کنید.
- مقدار سلول A1 را به 3 تغییر دهید.
اکسل بصورت خودکار، مقدار سلول A3 را محاسبه میکند. این یکی از قدرتمندترین ویژگیهای این نرمافزار است.
ویرایش یک فرمول
وقتی که سلولی را انتخاب میکنید، اکسل مقدار فرمول سلول را در نوار ابزار فرمول نشان میدهد.
- برای ویرایش یک فرمول، در نوار ابزار فرمول کلیک کرده و فرمول را تغییردهید.
- کلید Enter را بفشارید.
اولویت اپراتور
در اکسل یک اولویتبندی پیش فرض برای انجام عملیات تعریف شده است که محاسبات براساس آن انجام میشود. اگر بخشي از فرمول در پرانتز باشد، آن بخش اول محاسبه خواهد شد. سپس آن محاسبات ضرب یا تقسیم را انجام می دهد. بعد از کامل شدن، اکسل باقی مانده فرمول را جمع و تفریق میکند. به مثال زیر دقت کنید.
اکسل نخست ضرب (A1 * A2) را انجام می دهد. سپس مقدار سلول A3 را به حاصلضرب اضافه میکند.
مثالی دیگر:
اکسل ابتدا بخش داخل پرانتز (A2+A3) را حساب میکند. سپس نتیجه بدست آمده را در مقدار سلول A1 ضرب میکند.
جهت اطلاع از قیمت تایپ لطفا کلیک نمایید.
کپی/پِیست یک فرمول
وقتی فرمولی را کپی میکنید، اکسل بصورت اتوماتیک سلول مرجع را در سول جدید تنظیم می کند. برای درک این مطلب مراحل زیر را دنیال کنید.
- فرمول نشان داده شده زیر را در سلول A4 وارد کنید.
2الف. سلول A4 را انتخاب کنید. کلیک راست کرده و سپس روی copy کلیک کنید (یا CTRL+C را بگیرید).
سپس، سلول B4 را انتخاب کنید و از زیرمجموعه ‘Paste Options’ روی گزینه Pasteکلیک کنید (یا CTRL+V را بگیرید).
2ب. همچنین میتوانید فرمول مورد نظر را به سلول B4 بکشید (درگ کنید)، سلول A4 را انتخاب کنید، روی گوشه پایین سمت راست سلول Aکلیک کنید و آن را تا سلول B4 بکشید. این روش بسیار سادهتر بوده و دقیقا به همان نتیجه منتج میشود!
نتیجه. فرمول سلول B4 به مقدار ستون B رفرنس میدهد.
برای اطلاع از سفارش تایپ آنلاین کلیک نمایید.
وارد کردن (اینسرت کردن) یک تابع
هر تابعی دارای ساختار و ضابطهای است. (SUM(A1:A4 نمونه ای از این ساختار است. نام این تابع، SUM (جمع) است. بخش درون پرانتز (ضابطه) به این معنی است که بازه A1:A4 ورودی تابع است. این تابع مقادیر درون سلولهای #A1، A2، A و$A را باهم جمع میکند. بخاطر سپردن که برای هر کاری کدام تابع و کدام ضابطه استفاده میشود، ساده نیست. خوشبختانه، ویژگی Insert Function اکسل به کاربر در این جهت کمک می کند.
برای وارد کردن یک تابع، مراحل ذیل را دنبال کنید.
- یک سلول را انتخاب کنید
- روی کلید Insert Function کلیک کنید.
باکس ‘Insert Funxtion’ ظاهر میشود.
- برای یافتن یا انتخاب تابع از یک دستهبندی جستجو انجام دهید. برای مثال، از دستهبندی statistical (آماری)، COUNTIF را انتخاب کنید.
- روی OK کلیک کنید.
باکس یا پنجره ‘Function Arguments’ ظاهر میشود.
- در پنجرهRange کلیک کرده و A1:C2 را انتخاب کنید.
- در پنجره Criteria کلیک کرده و 5< را تایپ کنید.
- روی OK کلیک کنید.
نتیجه: تابع COUNTIF تعداد سلولهایی که دارای عدد بزرگتر از 5 هستند را محاسبه میکند.
توجه: بجای استفاده از ویژگی تابع Insert Function تایپ کنید (”COUNT(A1:C2,”>5=. وقتی که به COUNTIF= میرسید بجای تایپ کردن A1:C2، در پنجره range، A1:C2 را انتخاب کنید.
در اکسل تابع تفریق وجود ندارد. با این وجود روشهای متعددی برای تفریق اعداد در اکسل تعریف شده است. آیا برای ارتقای سطح مهارت اکسل خود آمادگی دارید؟
- برای نمونه، فرمول زیر اعداد را خیلی ساده با استفاده از علامت منها (-) در یک سلول تفریق میکند، فراموش نکنید که همواره یک فرمول با علامت تساوی (=) آغاز میشود.
- فرمول زیر مقدار درون سلولهای A2 و A3 را از مقدار درون سلول A1 کم میکند.
- همانطور که بدرستی در نظرتان شکل گرفته است؛ این فرمول بسیار طولانی است. استفاده از تابع جمع می تواند به کوتاه تر شدن این فرمول کمک کند. برای نمونه، فرمول زیر مقادیر بازه A2:A9 را از مقدار سلول A1 کم میکند.
به اسکرین شات زیر نگاه کنید. برای کم کردن اعداد ستون B از اعداد ستون A مراحل ذیل را دنبال کنید.
4الف. نخست مقدار سلول B1 را از مقدار سلول A1 کم کنید.
4ب. سپس، سلول C1 را انتخاب کنید. روی گوشه سمت راست پایین سلول C1 کلیک کرده و آن را تا سلول C6 بکشید.
به اسکرین شات زیر نگاه کنید. برای کم کردن یک عدد از یک دسته سلول، مراحل زیر را دنبال کنید.
5الف. نخست مقدار درون سلول A8 را از مقدار درولن سلول A1 کم کنید. با قراردادن علامت $ در جلوی حرف ستون و عدد ردیف، مبدا را روی سلول A8 ثابت کنید. ($A$8)
5ب. سپس، سلول B1 را انتخاب کنید. روی گوشه سمت راست پایین سلول B1 کلیک کرده و آن را تا سلول B6 پایین بکشید.
توضیح: وقتی که فرمول را پایین میکشید، مبدا مطلق (8$A$) بدون تغییر باقی میماند درحالی که مبدا نسبی (A1) به A2، A3، A4 و غیره تغییر میکند. شاید این گام در این مرحله برای شما بسیار دور از دسترس بنظر بیاید اما یکی از قدرتمندترین ویژگیهای اکسل را به شما نشان میدهد.
مزایای مرکز ترجمه و تایپ آنلاین شامل: تضمین کیفیت، سرعت، نیروی کار ماهر، تحویل به موقع سفارش و ... است.
ضرب
برای ضرب کردن اعداد در اکسل، از علامت ستاره (*) یا تابع PRODUCT (ضرب) استفاده کنید. چگونگی ضرب ستونها و ضرب یک ستون در یک عدد ثابت را بیاموزید.
- فرمول زیر، اعداد را در یک سلول ضرب میکنید و بدین منظور از علامت ستاره بهعنوان اپراتور ضرب استفاده میکند. فراموش نشود که همیشه فرمول با علامت تساوی (=) آغاز میشود.
- فرمول زیر اعداد را در سلولهای A1، A2 و A3 ضرب میکند.
- همانطور که تصور میکنید، این فرمول طولانی است، استفاده از تابع PRODUCT به کوتاهتر شدن این فرمول کمک می کند. برای نمونه، تابع PRODUCT زیر مقادیر را در بازه A1:A7 ضرب میکند.
- و حالا مثالی دیگر
توضیح: حاصلضرب =A1*A2*A3*A4*A5*A6*A7*B1*B2*B3*B4*C1*8 دقیقا برابر این مقدار است.
به اسکرین شات زیر نگاه کنید. برای ضرب دو ستون، مراحل زیر را دنبال کنید.
5الف. ابتدا، مقدار درون سلول A1 را در مقدار درون سلول B1 ضرب کنید.
5ی. سپس، سلول C1 را انتخاب کرده، روی گوشه سمت راست پایین سلول C1 کلیک کرده و آن را به سمت پایین تا سلول C6 بکشید.
به اسکرین شات زیر نگاه کنید. برای ضرب اعداد در یک عدد ثابت، مراحل ذیل را دنبال کنید.
6الف. ابتدا، مقدار درون سلول A1 را در مقدار درون سلول A8 ضرب کنید. با قراردادن علامت $ در جلوی حرف ستون و عدد ردیف (8$A$) مبدا را روی سلول A8 ثابت کنید.
6ب. سپس، سلول B1 را انتخاب کرده روی گوشه سمت راست پایین سلول B1 کلیلک رکده و آن را بسمت پایین تا سلول B6 بکشید.
توضیح: زمان پایین کشیدن فرمول، مبدا مطلق (8$A$) بدون تغییر باقی میماند، درحالی که مبدا نسبی (A1) به A2، A3، A4 و ... تغییر میکند. برای ضرب سریع گسترهای از سلوله در یک عدد ثابت میتوانید از Paste Special نیز استفاده کنید.
ریشه دوم
ریشه دوم عددی است که اگر درخوش ضرب شود حاصلضرب همان عدد مورد نظر گردد. تابع SQRT در اکسل ریشه دوم عدد را بدست میآورد.
- ابتدا، بدست آوردن مجذور عدد، یعنی ضرب عدد در خودش، برای نمونه 4*4=16 یا 4^2 = 16
نکته: برای استفاده از علامت ^ ، SHIFT+6 را بگیرید.
- ریشه دوم 16، میشود 4
- بجای استفاده از تابع SQRT، میتوانید از توان نیز استفاده کنید، پرانتز را فراموش نکنید.
- اگر عددی منفی باشد، تابع SQR خطای #NUM! میدهد.
- برای حذف علامت منفی (-) از یک عدد میتوان از تابع ABS استفاده نمود.
اکسل، تابعی که ریشه nام عددی را محاسبه کند، ندارد. برای محاسبه ریشه nام، کافی است آن عدد را در مخرج کسر قراردهید.
- برای مثال، 5 * 5 * 5 یا 5^3 برابر است با 5 به توان سه.
- ریشه سوم 125 برابر است با 5.
- مثلا، 2 * 2 * 2 * 2 * 2 * 2 یا 2^6 توان ششم 2 است.
- ریشه ششم 64 میشود 2.
محاسبه درصد در اکسل ساده است. درصد به زبان ساده یعنی مقداری از کل 100، پس 72% یعنی 72 قسمت از 100قسمت و 4% یعنی 4 از 100 و به همینترتیب.
وارد کردن درصد
برای وارد کردن یک عدد بصورت درصدی، مراحل ذیل را دنبال کنید.
- ابتدا، یک عدد اعشاری را وارد کنید.
- در تب Homeف از گروه اعداد، روی % کلیک کنید تا فرمت درصد اعمال شود.
نتیجه
نکته: برای تغییردادن درصد در سلول A1، سلول A1 را انتخاب و درصد جدید تایپ کنید (عدد اعشاری تایپ نکنید).
درصد مجموع
برای محاسبه درصد مجموع در اکسل، مراحل ذیل را دنبال کنید.
- فرمول نشان داده شده زیر را وارد کنید. این فرمول مقدار درون سلول A1 را بر مقدار سلول B1 تقسیم میکند. از علامت (/) به عنوان اپراتور تقسیم استفاده کنید. فراموش نکنید که فرمول همیشه با علامت تساوی (=) شروع میشود.
- از تب Homeف در گروه اعداد، روی علامت درصد کلیک تا فرمت درصد اعمال شود.
نتیجه.
- از تب Home در گروه اعداد، روی کلید افزایش Decimal کلیک کنید.
نتیجه.
توجه: اکسل همواره در محاسبات، فارغ از نحوه انتخاب اعشار برای نمایش، تا مقدار دقت عددی خواسته شده محاسبه را انجام میدهد.
افزایش درصدی
برای افزایش درصدی یک عدد در اکسل، مراحل ذیل را دنبال کنید.
- یک عدد در سلول A1 وارد کنید.یک عدد اعشاری (0.2) در سلول B1 وارد کرده و فرمت درصد را اعمال کنید.
- برای افزایش 20درصدی عدد سلول A1، این عدد را در 1.2 (1+0.2) ضرب کنید. این فرمول که در ادامه دارای نکات ریزی است.
نکته: ترتیب محاسبات در اکسل ازپیش تعریف شده است. اگر بخشی از فرمول در در پرانتز باشد؛ محاسبه آن بخش در اولویت قراردارد.
- به منظور کاهش درصدی کافی است علامت مثبت را به منفی تغییر دهید.
تغییر درصدی
برای محاسبه تغییر درصدی بین دو عدد در اکسل، مراحل ذیل را دنبال کنید.
- یک عدد قدیمی را در سلول A1 و یک عدد جدید در سلول B1 وارد کنید.
- ابتدا، اختلاف میان این دو عدد را محاسبه کنید.
- سپس نتیجه بدست آمده را بر عدد سلول A1 تقسیم کنید.
نکته: ترتیب محاسبات بصورت ازپیش تعریف شده در اکسل انجام میشود. بخشی از فرمول که در پرانتز است ابتدا محاسبه خواهد شد.
- در تب Home، در گروه اعداد، روی علامت درصد کلیک کرده و فرمت درصد را اعمال کنید.
نتیجه.
- فرمول (New-Old)/Old همیشه جواب میدهد.
نکته: برای مطاللعه مثالی در این خصوص، به پیج ما مراجعه و بخش فرمول تغییر درصدی را مطالعه نمایید.
بازه یا ثابتی نامگذاری شده ایجاد کرده و از این نامها در فرمولهایتان استفاده کنید. از این طریق درک روابط سادهتر میگردد.
بازه نامگذاری شده
برای ایجاد یک بازه نامگذاری شده مراحل ذیل را دنبال کنید.
- بازه A1:A4 را انتخاب کنید.
- در تب Formulas، در گروه نامهای تعریف شده، روی تعریف نام کلیک کنید.
- نامی را وارد کرده و روی آن کلیک کنید.
راه سریعتری برای انجام این کار نیز وجود دارد.
- بازه را انتخاب کنید. نام مورد نظر را در پنجره Name نوشته و Enter کنید.
- حال میتوان از این گستره نامگذاری شده در فرمول استفاده کرد. برای مثال، مجموع قیمتها.
ثابت نامگذاری شده
برای ایجاد یک ثابت نامگذاری شده، مراحل ذیل را دنبال کنید.
- در تب Formulas، در گروه نامهای تعریف شده، روی تعریف نام (Definr Name) کلیک کنید.
- نامی را وارد کنید، مقدار را تایپ کرده و روی OK کلیک کنید.
- حال میتوانید از این ثابت نامگذاری شده در فرمولها استفاده کنید.
نکته: درصورت تغییر tax rate ، برای ویرایش نام از Name Manager استفاده کرده و اکسل بصورت خودکار تمامی فرمولهایی را که از tax rate استفاده میکنند تغییر خواهد داد.
مدیریت نام
برای ویرایش و پاک کردن نامهای تعریف شده، مراحل ذیل را دنبال کنید.
- در تب Formulas، در گروه نامهای تعریف شده، روی Name Manager کلیک کنید.
- مثلاً، TaxRate را انتخاب کرده و روی Edit کلیک کنید.
بازه نامگذاری شده دینامیکی
یک محدوده (بازه) نامگذاری شده دینامیکی، درصورت اضافه کردن یک عدد به آن، بصورت خودکار بسط پیدا خواهد کرد.
- برای مثال، بازه A1:A4 را انتخاب و نام آن را Prices(قیمتها) بگذارید.
- حاصل جمع را محاسبه کنید،
- وقتی عددی به بازه اضافه میشود، اکسل حاصل جمع را آپدیت نمیکند.
برای بسط خودکار بازه نامگذاری شده در زمان اضافه شدن عدد به بازه، مراحل ذیل را دنبال کنید.
- در تب Formulas در گروه نامهای تعریف شده، روی Name Manager کلیک کنید.
- روی Edit کلیک کنید.
- روی پنجره Refers to کلیک کرده و فرمول (OFFSET($A$1,0,0,COUNTA($A:$A),1= را وارد کنید.
توضیح: تابع OFFSET 5 ضابطهای است. مرجع: :$A$1 ، ردیفهای offset:0 ، ستونهای Offset: 0 ارتفاع: (COUNTA($A:$A ، عرض: (COUNTA($A:$A1 تعداد سلولهای پرشده ستون A را حساب میکند. وقتی که مقداری به این بازه اضافه میکنید، (COUNTA($A:$A افزایش پیدا میکند. درنتیجه، بازهای که تابع OFFSET نشان میدهد بسط پیدا میکند.
- روی OK کلیک کرده و صفحه را ببندید.
- حال، اکر مقداری به این بازه اضافه کنید، اکسل، حاصل جمع را بصورت خودکار آپدیت میکند.
گزینههای چسباندن
این مثال، گزینههای مختلف چسباندن در اکسل ار نشان میدهد. سلول B5 در شکل زیر دربرگیرنده تابع حاصل جمع است که مجموع گستره B2:B4 را حساب میکند. رنگ زمینه این سلول را زرد کرده و و دور آن کادر کشیدهایم.
چسباندن
گزینه Paste هرچیزی را میچسباند.
- سلول B5 را انهتخاب کنید، راست کلید کرده و سپس روی Copy کلیک کنید (یا CNT+c را بفشارید).
- سپس، سلول F5 را انتخاب کرده، راست کلیک کنید و ذیل گزینه Paste Options، روی Paste کلیک کنید (یا CTRL+v را بگیرید).
نتیجه.
مقادیر
گزینه Values نتیجه فرمول را paste میکند.
- سلول B5 را انتخاب کرده، راست کلیک کنید و سپس روی Copy کلیک کنید (یا CRL+c را بگیرید)
- سپس، سلول D5 را انتخاب کرده، راست کلیک کنید و سپس ذیل گزینه Paste Options روی Values کلیک کنید.
نتیجه.
نکته: برای جایگزین کردن سریع فرمول در سلول B5 همراه با نتیجه آن، سلول B5 را انتخاب کرده، F2 را (برای ویرایش فرمول) فشار داده و F9 را بگیرید.
فرمولها
گزینه Formulas فقط فرمول را paste میکند.
- سلول B5 را انتخاب کرده، راست کلیک کنید و سپس روی Copy کلیک کنید (یا CTRL+c را بگیرید).
- سپس F5 را انتخاب کنید، راست کلیک کرده و ذیل گزینه Paste Options، روی Formulas کلیک کنید.
نتیجه.
فرمت کردن
گزینه Formatting صرفا فرمت و قالب را paste میکند.
- سلول B5 را انتخاب کرده، راست کلیک کنید و سپس روی Copy کلیک کنید (یا CTRL+c را بگیرید).
- سپس، سلول D5 را انتخاب کنید، راست کلیک کرده و سپس ذیل گزینه Paste Options روی Formatting کلیک کنید.
نتیجه.
نکته: Format Painter، قالببندی را سریعتر از این کپی/پیست میکند.
Paste Special
این پنجره گزینههای بیشتری را برای چسباندن پیشنهاد میکند. برای بالاآوردن این پنجره مراحل ذیل را دنبال کنید.
- سلول B5 را انتخاب کنید، راست کلیک کرده و سپس روی Copy کلیک کنید (یا CTRL+c را بگیرید)
- سپس سلول D5 را انتخاب کنید، راست کلیک کرده و سپس روی Paste Special کلیک کنید.
گزینه Paste Spcial ظاهر میشود.
نکته: حال می توان گزینههای چسباندن یاد شده را یافت. همچنین میتوان صرفا کانتها یا فقط معیارهای اعتبارسنجی، سورس تم استفاده شده، حاشیههای موردنظر، پهنای تعریف شده ستون، فرمت اعداد و روابط و مقادیر را به تنهایی paste کرد. همچنین میتوان از پنجره Paste Special برای اجرای عملیاتهای سریع، عبور از جاهای خالی و انتقال دادهها نیز استفاده نمود.
نوار ابزار Status
این نوار ابزار سریعترین راه برای مشاهده میانگین، شمارش تعداد، مینیممف ماکزیمم یا حاصل جمع سلولهای انتخاب شده محسوب میشود.
- محدودهای از سلولها را انتخاب کنید.
- به نوار ابزار در قسمت پایین پنجره، برای مشاهده حاصل جمع این سلولها نگاه کنید.
- برای اضافه کردن میانگین، شمارش، مینیمم و ماکزیمم، روی این نوار ابزار راست کلیک کنید.
نتیجه.
عملیاتهای سریع
برای اجرای سریع عملیات روی چند سلول در اکسل از 'Paste Special Operations' استفاده میشود.
- سلول D3 را انتخاب کنید.
- راست کلیک کرده و سپس روی Copy کلیک کنید.
- محدوده A1:A3 را انتخاب کنید.
- راست کلیک کرده و سپس روی paste special کلیک کنید.
- روی Multiply کلیک کنید.
نکته: همچنین میتوانید تقسیم، جمع یا تفریق نیز انجام دهید.
- روی Ok کلیک کنید.
نکته: تمامی مقادیر تا 10درصد افزای پیدا کردهاند.بدون این ویژگی مجبور خواهید شد یک محدوده موفت (با فرمولهایی که مقادیر محدوده A1:B8 را در 1/1 ضرب میکنند) ایجاد کرده و سپس با کپی پیست کردن این محدوده موقت آن را جایگزین محدوده A1:B8کنید.
نامساوی
در اکسل، علامت <> به معنای نابرابری است. اپراتور <> درصورت برابر نبودن دو مقدار استفاده میگردد. به چند مثال توجه کنید.
- فرمول درون سلول C1 درست است زیرا مقدار درون سلول A1 با مقدار درون سلول B1 برابر نیست.
- فرمول درون سلول C1 اشتباه است چون مقدار درون A1 برابر مقدار درون سلول B1 است.
- تابع IF فرآیند بین مقادیر استارت و پایان را درصورتی که مقدار پایان برابر رشته خالی ("") نباشد محاسبه میکند و درغیراینصورت یک رشته سلول خالی نمایش میدهد (ردیف5 را ببینید)
نکته: برای اطلاعات بیشتر درمورد تابع اکسل IF function را مطالعه کنید.
- تابع COUNTIF تعداد سلولهای محدوده A1:A5 را که معادل "red" نسیتند را شمارش میکند.
توجه: برای اطلاعات بیشتر درمورد تابع اکسل COUNTIF function را مطالعه کنید.
- تابع COUNTIF نتیجهای دقیقا مشابه ایجاد میکند. اپراتور & اپراتور نامساوی و مقدار را در سلول C1 پشت سرهم میآورد.
- تابع COUNTIFSA تعداد سلولهایی را که در محدوده A1:A5 نه معادل red و نه معادل blue باشند را میشمارد.
توضیح: تابع COUNTIFS در اکسل، سلولهای با دست کم دو معیار را میشمارد. این تابع دارای دو جفت range/criteria است.
- تابع AVERAGEIF میانگین مقادیر در محدوده A1:A5 را که برابر صفر نیستند حساب میکند.
نکته: بهعبارت دیگر، تابع AVERAGEIF فوق میانگین مقادیر غیرصفر را حساب میکند.