مروری بر فرمول ها در اکسل

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

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

1. یک سلول را انتخاب کنید.
2. علامت مساوی را تایپ کنید. (در اکسل تمام فرمول ها با = شروع می شوند)
3. یک سلول را انتخاب کنید یا آدرس آن را در سلول انتخاب شده تایپ کنید.
4. یک دستور را وارد کنید. به عنوان مثال ، – برای تفریق.
5. سلول بعدی را انتخاب کنید یا آدرس آن را در سلول انتخاب شده تایپ کنید.

6. Enter را فشار دهید. نتیجه محاسبه در سلول با فرمول ظاهر می شود.

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

وقتی فرمولی وارد سلول می شود ، در نوار فرمول (Formula bar) نیز ظاهر می شود.

برای دیدن فرمول ، یک سلول را انتخاب کنید ، و آن را در نوار فرمول ببنید.

آموزش نوشتن فرمول شامل عمل داخلی

1. یک سلول خالی را انتخاب کنید.
2. یک علامت مساوی بنویسید و سپس یک تابع تایپ کنید. به عنوان مثال ، SUM= برای عمل جمع.
3. یک پرانتز باز کنید.
4. محدوده سلول ها را انتخاب کرده و سپس پرانتز را ببندید.

5. Enter را فشار دهید تا نتیجه را ببینید.

اجزای یک فرمول اکسل

یک فرمول می تواند شامل هر یا همه موارد زیر باشد: توابع (functions)، منابع (references)، عملگرها(operators) و ثابت ها(constants).

1. Functions : تابع PI ()
2. References : مقدار را در سلول A2 در بر می گیرد.
3. Constants : اعداد یا مقادیر متن مستقیماً وارد فرمول می شوند.
4. Operators : عملگر ^ یک عدد را به توان می رساند و عملگر * (ستاره) تعداد را ضرب می کند.

مقادیر ثابت (Constants) در excel

ثابت مقداری است که محاسبه نمی شود. همیشه ثابت می ماند. به عنوان مثال ، تاریخ 10/9/2008 ، شماره 210 و متن “درآمد سه ماهه” همه ثابت هستند. یک عبارت یا یک مقدار حاصل از یک عبارت ثابت نیست. اگر از ثابت در فرمول به جای مراجعه به سلول استفاده کنید (به عنوان مثال ، = 30 + 70 + 110) ، نتیجه فقط در صورت تغییر فرمول تغییر می کند. به طور کلی ، بهتر است ثابت ها را در سلول های جداگانه قرار دهید که در صورت نیاز به راحتی قابل تغییر باشند ، سپس آن سلول ها را در فرمول ها منبع کنید.

آموزش استفاده از منابع (References) در اکسل

یک منبع سلول یا طیف وسیعی از سلول ها را در یک صفحه کار مشخص می کند و به اکسل می گوید که کجا مقادیر یا داده هایی را که می خواهید در فرمول استفاده کنید جستجو کند. برای استفاده از داده های موجود در قسمت های مختلف صفحه کار (sheets ) در یک فرمول می توانید از منابع استفاده کنید یا از مقدار یک سلول در چندین فرمول استفاده کنید. همچنین می توانید به سلول های موجود در برگه ای دیگر در همان workbook و سایر workbook ها مراجعه کنید. مراجعه به سلول ها در سایر workbook كار پیوند یا منبع خارجی نامیده می شود.

سبک منبع A1

به طور پیش فرض ، اکسل از سبک منبع A1 استفاده می کند ، که به ستون هایی با حروف (A تا XFD ، در مجموع 16384 ستون) اشاره دارد و به ردیف های دارای شماره (1 تا 1.048.576) اشاره دارد. به این حروف و اعداد عناوین ردیف و ستون گفته می شود. برای مراجعه به یک سلول ، حرف ستون و سپس شماره ردیف را وارد کنید. به عنوان مثال ، B2 به سلول در تقاطع ستون B و سطر 2 اشاره دارد.

آموزش مراجعه به سلول یا طیف وسیعی از سلول ها در یک worksheet دیگر یا همان workbook

در مثال زیر ، تابع (میانگین) AVERAGE مقدار متوسط دامنه B1: B10 را در worksheet  با نام بازاریابی در همان کتاب کار محاسبه می کند.

1. به worksheet  به نام بازاریابی (Marketing) اشاره دارد.
2. به محدوده سلولها از B1 تا B10 اشاره دارد.
3. علامت تعجب (!) منبع worksheet را از منبع محدوده سلول جدا می کند.

تفاوت بین منابع مطلق ، نسبی و مختلط در Excel

منابع نسبی (Relative references)

منابع سلول نسبی در فرمول ، مانند A1 ، بر اساس موقعیت نسبی سلول حاوی فرمول و سلول مورد اشاره است. اگر موقعیت سلول حاوی فرمول تغییر کند ، منبع هم تغییر می کند. اگر فرمول را در سطرها و یا ستون های دیگر کپی کنید، منبع به طور خودکار تنظیم می شود. به طور پیش فرض ، فرمول های جدید از منابع نسبی استفاده می کنند. به عنوان مثال ، اگر یک منبع نسبی را در سلول B2 به سلول B3 کپی کنید ، به طور خودکار از = A1 به = A2 تنظیم می شود.

منابع مطلق (Absolute references)

یک منبع سلول مطلق در فرمول ، مانند $ 1 $ A ، همیشه به یک سلول در یک مکان خاص اشاره دارد. اگر موقعیت سلول حاوی فرمول تغییر کند ، منبع مطلق ثابت می ماند. اگر فرمول را در ردیف ها یا ستون های دیگر کپی کنید ، منبع مطلق تنظیم نمی شود. به طور پیش فرض ، فرمول های جدید از منابع نسبی استفاده می کنند ، بنابراین ممکن است لازم باشد آن ها را به منابع مطلق تغییر دهید. به عنوان مثال ، اگر یک منبع مطلق را در سلول B2 به سلول B3 کپی کنید ، در هر دو سلول ثابت باقی می ماند: = $ 1 $ A.

منابع مختلط (Mixed references)

یک منبع مختلط یا یک ستون مطلق و یک ردیف نسبی دارد ، یا ردیف مطلق و ستون نسبی. یک منبع مطلق ستون شکل A1 $ ، B1 $ و غیره را دارد. یک منبع ردیف مطلق به شکل A $ 1 ، B $ 1 و غیره است. اگر موقعیت سلولی که حاوی فرمول است تغییر کند ، منبع نسبی تغییر می کند و منبع مطلق تغییر نمی کند. اگر فرمول را در ردیف ها یا ستون های پایین کپی کنید ، منبع نسبی به طور خودکار تنظیم می شود و منبع مطلق تنظیم نمی شود. به عنوان مثال ، اگر یک منبع مخلوط را از سلول A2 به B3 کپی یا پر کنید ، از = 1 $ A به = 1 $ B تنظیم می شود.

سبک منبع 3-D

مراجعه به چندین worksheets

اگر می خواهید داده های موجود در همان سلول یا محدوده سلول ها را در چندین worksheets در یک workbook تجزیه و تحلیل کنید ، از یک منبع 3-D استفاده کنید. یک منبع 3-D شامل منبع سلول یا محدوده است که قبل از آن طیف وسیعی از نام های worksheets وجود دارد. اکسل از هر worksheets ذخیره شده بین نام شروع و پایان منبع استفاده می کند. به عنوان مثال ، = SUM (Sheet2: Sheet13! B5) تمام مقادیر موجود در سلول B5 را در تمام workbook ها بین Sheet 2 و Sheet 13 اضافه می کند.

نکات:

با استفاده از توابع زیر می توانید از ارجاعات 3-D برای مراجعه به سلول های سایر صفحات ، تعریف اسامی و ایجاد فرمول استفاده کنید: SUM، AVERAGEA، AVERAGEA، COUNT، COUNTA، MAX، MAXA، MIN، MINA، PRODUCT، STDEV .P ، STDEV.S ، STDEVA ، STDEVPA ، VAR.P ، VAR.S ، VARA و VARPA.

از منابع 3-D نمی توان در فرمول های array استفاده کرد.

منابع 3-D را نمی توان با عملگر تقاطع (یک فضای واحد) یا در فرمول هایی که از تقاطع ضمنی استفاده می کنند ، استفاده کرد.

هنگام جابجایی ، کپی یا حذف worksheets ها چه اتفاقی می افتد؟

مثال های زیر توضیح می دهد که هنگام انتقال ، کپی ، وارد کردن یا حذف worksheets که در یک منبع 3-D موجود است ، چه اتفاقی می افتد. مثال از فرمول SUM(Sheet2:Sheet6!A2:A5) = استفاده می کند.

1. درج یا کپی (Insert or copy ) : اگر ورق هایی را بین Sheet2 و Sheet6 وارد یا کپی کنید (نقاط انتهایی در این مثال) ، Excel شامل تمام مقادیر موجود در سلول های A2 تا A5 از sheet های اضافه شده در محاسبات است.
2. حذف (Delete) : اگر ورق ها را بین Sheet2 و Sheet6 حذف کنید ، Excel مقادیر آن ها را از محاسبه حذف می کند.
3. جا به جایی (Move an endpoint) : اگر Sheet2 یا Sheet6 را در همان workbook به مکان دیگری منتقل کنید ، Excel محاسبه را متناسب با محدوده جدید Sheet ها بین آن ها تنظیم می کند.
4. حذف اندپوینت (Delete an endpoint) : اگر Sheet2 یا Sheet6 را حذف کنید ، Excel محاسبه را متناسب با محدوده Sheet های بین آن ها تنظیم می کند.

سبک منبع R1C1

با خرید آفیس 365 می توانید از رفرنس و ستون های worksheet شماره گذاری شده استفاده کنید. سبک منبع R1C1 برای محاسبه موقعیت سطر و ستون در ماکرو مفید است. در سبک R1C1 ، اکسل محل قرارگیری سلول با “R” و به دنبال آن یک شماره ردیف، و “C” و به دنبال آن شماره ستون را نشان می دهد.

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

با تنظیم یا پاک کردن کادر انتخاب سبک منبع R1C1 در بخش «Working with formulas» در بخش Formulas در کادر محاوره ای گزینه ها می توانید سبک منبع R1C1 را روشن یا خاموش کنید. برای نمایش این جعبه محاوره ای ، روی تب File کلیک کنید.

اگر این مقاله برای شما مفید بود لطفاً نظرات خود را در بخش دیدگاه ها با دیگران به اشتراک بگذارید.

منبع : مایکروسافت