توابع پایه اکسل - فرمول نویسی

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

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

فرمول عبارتی است که یک نتیجه خاص را برمی گرداند (حتی زمانی که نتیجه یک خطا باشد) . مثلا:

= 1 + 2  //  (مقدار 3 را باز می‌گرداند)
= 6 / 3  //  (مقدار 2 را باز می‌گرداند)

در اکسل می‌توانید برای نوشتن فرمول در یک سلول ، روی آن قرار گرفته و سپس مستقیما روی همان سلول یا در قسمت نوار فرمول (FORMULA BAR) شروع به تایپ فرمول کنید.

فرمول‌نویسی در اکسل با علامت = شروع می‌شود.

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

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

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

هر سلول در اکسل دارای یک نام  (آدرس) است که از تلاقی یک سطر و یک ستون تشکیل می‌شود :

 شماره سطر  نام ستون = نام سلول

برای نمونه عنوان سلول ، سطر اول ستون اول یک برگه اکسل به شکل زیر است:

 A1

در مثال فوق ، سلول C1 حاوی فرمول زیر است:

= A1 + A2 + A3  //  (مقدار 9 را باز می‌گرداند)

توجه داشته باشید ، چون از آدرس سلول‌ها برای A2 ، A1 و A3 استفاده می‌کنیم ، این مقادیر را می‌توان هر زمان تغییر داد و C1 همچنان نتیجه دقیقی را نشان می‌دهد.

فرمول‌ها را کپی و پیست کنید

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

این بدان معناست که نیازی نیست بارها و بارها همان فرمول اصلی را تایپ کنید. در مثال زیر فرمول موجود در سلول E1 با استفاده از کلیدهای Control + C در حافظه موقت کپی شده است:

در تصویر زیر: فرمول با استفاده از کلیدهای Control + V به سلول E2 جایگذاری شده است. توجه کنید آدرس سلول‌ها تغییر کرده است:

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

آدرس نسبی

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

فرمول سلول E1 مثال فوق به این صورت است:

= B1 + C1 + D1   //  (E1 فرمول سلول)

مفهوم فرمول این است:

"سلول در 1 ستون سمت چپ "+ "سلول در 2 ستون سمت چپ" + "سلول در  3 ستون سمت چپ "=

به همین دلیل است که وقتی فرمول در سلول E2 کپی می‌شود، به همان روش به کار خود ادامه می‌‎دهد. 

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

در مثال نشان داده شده ، فرمول سلول E4 شامل دو آدرس ‌نسبی است که با کپی در سلول‌های زیرین ستون E به صورت زیر تغییر می‌کند:

= C4 * D4
= C5 * D5
= C6 * D6
= C7 * D7
= C8 * D8

آدرس‌های نسبی بسیار مفید هستند و به طور پیش فرض ، همه آدرس‌ها در فرمول‌های اکسل نسبی هستند.

آدرس مطلق

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

می‌توانید با استفاده از نویسه‌های دلار ($) آدرس نسبی را به آدرس مطلق تبدیل کنید.

به عنوان مثال ، آدرس مطلق سلول A1 به شکل زیر است:

= $A$1

آدرس مطلق برای محدوده A1:A10 هم به این صورت است:

= $A$1:$A$10

در مثال نشان داده شده ، فرمول سلول D5 با کپی در سلول‌های ستون D به شکل زیر تغییر می‌کند:

= C5 * $C$2
= C6 * $C$2
= C7 * $C$2
= C8 * $C$2
= C9 * $C$2

توجه داشته باشید که آدرس مطلق C2 که نرخ ساعتی را نگه می‌دارد در سلول‌های دیگر تغییر نمی‌کند ، در حالی که آدرس C5 با هر سطر جدید تغییر می‌کند.

کلید میانبر تغییر آدرس نسبی به مطلق و بلعکس

هنگام وارد کردن فرمول‌ها ، می‌توانید از کلید های میانبر صفحه‌کلید زیر برای جابجایی بین گزینه‌های آدرس نسبی و مطلق بدون تایپ دستی نویسه دلار ($) استفاده کنید.

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

A1 --> $A$ 1--> A$1--> $A1--> A1

بسته به اینکه سطر یا ستون از کدام نوع باشد (نسبی یا مطلق) در کل 4 حالت پیش می آید:

شماره سطر $ نام ستون = نام سلول
شماره سطر نام ستون $ = نام سلول
شماره سطر $ نام ستون $ = نام سلول
شماره سطر  نام ستون = نام سلول

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

نحوه ویرایش فرمول

برای ویرایش فرمول ، 3 گزینه دارید:

1) سلول را انتخاب کنید، در نوار فرمول ویرایش کنید

2) روی سلول دوبار کلیک کنید، مستقیماً ویرایش کنید

3) سلول را انتخاب کنید ، F2 را فشار دهید و مستقیما ویرایش کنید

مهم نیست از کدام گزینه استفاده می کنید ، Enter را فشار دهید تا پس از انجام ویرایش ، تغییرات را تأیید کنید. اگر می‌خواهید ویرایش را لغو کنید و فرمول را بدون تغییر رها کنید، روی کلید Esc کلیک کنید.

آدرس مختلط

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

= $A1  //  (ستون قفل شده است) 
= A$1   //  (سطر قفل شده است)
= $A$1:A2  //  (اولین سلول قفل شده است)

از آدرس‌های مختلط می‌توان برای تنظیم فرمول‌هایی استفاده کرد که بدون نیاز به ویرایش دستی در سطر‌ها یا ستون‌ها کپی شوند. در برخی موارد (نمونه سوم مثال فوق) می‌توان از آنها برای ایجاد آدرسی استفاده کرد که هنگام کپی گسترش می‌یابد.

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

در مثال نشان داده شده فرمول سلول E5 به این صورت است:

= $C5 * (1 – E$4)

این فرمول با دو آدرس مختلط ساخته شده است تا بتوان آن را در محدوده E5:G7 بدون ویرایش دستی کپی کرد. ارجاع به $C5 ستون را قفل می‌کند تا مطمئن شود که فرمول همچنان که کپی می‌شود قیمت را از ستون C دریافت می‌کند. در آدرس E$4  سطر قفل شده است به طوری که با کپی شدن فرمول از سطر 5 به سطر 7 فرمول همچنان مقدار درصد را از سطر 4 انتخاب می‌کند.

عملگرهای ریاضی

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

عملگرهای منطقی

عملگرهای منطقی از مقایسه‌هایی مانند "بیشتر از"، "کمتر از" و .... پشتیبانی می‌کنند. عملگرهای منطقی موجود در اکسل در جدول زیر نشان داده شده است:

ترتیب عملیات ها

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

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

در نهایت ، عملگرهای منطقی را در صورت وجود ارزیابی خواهد کرد.

ترتیب عملیات به صورت زیر است:

1.    پرانتز
2.    توان
3.    ضرب و تقسیم
4.    جمع و تفریق
5.    عملگر الحاق
6.    عملگرهای منطقی

فرمول‌ها را به مقادیر تبدیل کنید

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

ساده‌ترین راه برای انجام این کار در اکسل این است که فرمول را کپی کنید، سپس با استفاده از Paste Special > Values پیست کنید.

این کار ، فرمول‌ها را با مقادیری که بر می‌گردانند بازنویسی می‌کند. می‌توانید از میانبر صفحه‌کلید Control + V نیز برای چسباندن مقادیر استفاده کنید ، یا از منوی Paste در زبانه HOME روبان اکسل استفاده کنید.

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

آیا می‌خواهید:

• برنامه‌های قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
• فرصت های شغلی جدید کسب کنید.

کتاب‌های معرفی شده ، راهنمای گام به گام جامع و عملی برای افرادی است که (مانند شما) می‌خواهند به این اهداف برسند.