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

نحوه جابجا کردن صفحه گسترده (اسپرید شیت) در اکسل

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

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

ردیف ها جعبه های افقی هستند که در سمت چپ شماره گذاری شده اند. ستون ها از چپ به راست اجرا شده و با حروف نامگذاری می شوند. هنگامی که ردیف ها و ستون ها به یکدیگر می رسند، یک سلول را تشکیل می دهند. محل تلاقی ستون C و ردیف ۳، یک سلول است که C3نامیده می شود.

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

سلول ها به عنوان محل تلاقی ردیف ها و ستون ها نامگذاری شده اند. از تلاقی ستون F و ردیف ۴ سلولی تشکیل می شود که آن را F4 می نامیم.

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

نحوه کار با فرمول ها و توابع اکسل

فرمول ها و توابع همان چیزی هستند که ما برای کار با داده های تایپ شده از آنها استفاده می کنیم. اکسل توابع داخلی مانند AVERAGE= را برای محاسبه میانگین اعداد ارائه می کند، فرمول ها در واقع عملکردهای ساده ای مانند اضافه کردن دو سلول به یکدیگر هستند. این دو اصطلاح می توانند به یک معنی باشند.

در این مقاله، من از اصطلاح فرمول برای اشاره به این موارد استفاده خواهم کرد، زیرا ما اغلب با ترکیبی از هر دو در یک صفحه گسترده کار می کنیم.

برای نوشتن اولین فرمول خود، روی یک سلول دوبار کلیک کرده و علامت = را تایپ کنید. اجازه دهید از یک مثال ساده به عنوان اولین فرمول استفاده کنیم: مانند جمع دو مقدار با هم.

=۴+۴

این فرمول به شکل زیر در Excel ظاهر می شود:

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

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

سلول حاوی “=۴+۴” به عنوان یک فرمول در نظر گرفته می شود، در حالی که صفحه گسترده نتیجه را چاپ می کند. با استفاده از فرمول های مشابه مانند تفریق، ضرب (با نماد *) و تقسیم (با نماد.) سعی کنید فرمول ها را در نوار فرمول یا در یک سلول تایپ کنید.

این مثال بظاهر ساده یک مفهوم مهم را نشان می دهد: در پشت سلول های صفحه گسترده خود، می توانید فرمول های قدرتمندی ایجاد کنید که با داده های شما کار می کنند.

فرمول های اکسل با مراجع سلولی

دو راه برای کار کردن با این فرمول ها وجود دارد:

• از داده های درون فرمول ها استفاده کنید مانند مثالی که در بالا نشان دادیم (۴ + ۴=)
• برای راحتی کار از فرمول هایی با مراجع سلولی استفاده کنید، مانند A2 + A3=

به مثال زیر نگاه کنید. در تصویر زیر لیست فروش شرکت در ۳ ماه اول سال ۲۰۱۶ را وارد کرده ام. اکنون فروش این سه ماه را با هم جمع می کنم.

در این مثال، از یک فرمول درون خطی با رفرنس دهی سلولی استفاده می کنم. سلول های B2 ، C2 و D2 را برای محاسبه فروش کل سه ماه اول سال با هم جمع کردم.

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

• AVERAGE= برای محاسبه میانگین.
• COUNT= برای شمارش تعداد آیتم های موجود در مجموعه ای از داده ها.
• TODAY= برای چاپ تاریخ امروز در یک سلول.
• TRIM= برای حذف فضاهای خالی از ابتدا و انتهای یک سلول.

نحوه کپی و چسباندن فرمول ها در اکسل

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

زمانیکه یک سلول را با فرمول آن کپی می کنیم و می چسبانیم، مقدار سلول کپی نمی شود– بلکه فرمول آن کپی می شود. اگر آن را در جای دیگری بچسبانیم، در واقع فرمول را در جای دیگری وارد کرده ایم.

در مثال زیر، می توانید مراحلی که انجام داده ام را مشاهده کنید:

1. سلول E2 را (Ctrl + C) کپی کردم (که فرمولی است که B2 ، C2 و D2 را با یکدیگر جمع می کند).
2. سپس، با کلیک کردن و کشیدن ستون، سایر سلول های موجود در ستون E را انتخاب کردم.
3. برای چسباندن همان فرمول در تمام سلول های ستون E کلیدهای Ctrl + V) ) را فشار دادم.

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

همانطور که در تصویر بالا مشاهده می کنید، با چسباندن فرمول مقدار جایگذاری نمی شود (۲۱۹۳۳ دلار). بلکه، خود فرمول کپی می شود.

فرمولی که کپی کردیم در سلول E2 بود. و سلول های B2 ، C2 و D2 را با هم جمع می کرد. وقتی آن را درسلول E3 چسباندیم، همان کار را انجام نداد- بلکه، مقادیر B3 ، C3 و D3 را با یکدیگر جمع کرد.

در واقع، اکسل فکر می کند که شما می خواهید سه سلول واقع در سمت چپ سلول فعلی را با یکدیگر جمع کنید. هنگامی که یک فرمول را کپی و پیست می کنید، به جای کپی کردن و چسباندن همان مرجع ها، مراجع نسبی را کپی خواهد کرد.

نحوه Autofill کردن فرمول ها در اکسل

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

برای autofill در اکسل، با فرمول خود روی سلول حرکت کنید. با قرار دادن مکان نما در گوشه پایین سمت راست، مکان نما به شکلی شبیه یک علامت به علاوه تبدیل خواهد شد. برای autofill کردن فرمول ها، کافی است دوبار کلیک کنید.

مکان نما را در گوشه سمت راست پایین یک سلول بچرخانید تا یک علامت “+” ظاهر شود سپس برای autofill کردن فرمولها، دو بار کلیک کنید.

توجه: مسئله ای که باید هنگام استفاده از autofill به آن توجه کنید این است که اکسل همیشه نمی تواند فرمول های صحیح را حدس بزند. بنابراین حتماً باید مطمئن شوید که autofill فرمولهای صحیح را در صفحه گسترده شما قرار داده است.

مراجع نسبی در مقابل مراجع مطلق

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

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

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

در این قسمت می خواهیم پاداش فروش کارمندان خود را در سه ماه اول سال محاسبه کنیم. براساس فروش انجام شده، پاداش فروش ۲٫۵٪ را محاسبه می کنیم. اجازه دهید کار خود را با ضرب تعداد دفعات فروش در درصد پاداش برای اولین کارمند شروع کنیم.

یک جعبه را به درصد پاداش (bonus percentage) اختصاص می دهیم، و تعداد فروش را در درصد پاداش ضرب می کنیم:

ما یک فرمول برای ضرب تعداد فروش در درصد پاداش نوشته ایم.

اکنون اولین پاداش فروش را محاسبه کردیم، بنابراین فرمول را برای محاسبه سایر پاداش ها به سمت پایین می کشیم:

با اینکه محاسبه اولین پاداش فروش خوب پیش رفت، اما فرمول برای محاسبه سایر پاداش ها پاسخگو نبود.

متاسفانه، فرمول کار نکرد. از آنجا که درصد پاداش همیشه در سلول – H2 باقی می ماند؛ بنابراین وقتی فرمول را به سمت پایین می کشیم، کار نمی کند. و با عدد صفر در سلول ها روبرو می شویم.

در اینجا نحوه عملکرد اکسل برای محاسبه F3 و F4 را مشاهده می کنیم:

فرمول ما برای ضرب شدن در سلول H2 به جای کشیده شدن، به یک مرجع مطلق نیاز دارد.

در واقع، ما باید فرمول را تغییر دهیم تا اکسل از سلول H2 برای ضرب مقدار پاداش استفاده کند. اینجاست که باید از یک مرجع مطلق استفاده کنیم.

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

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

=E2*H2

برای فریز کردن این فرمول برای استفاده از سلول H2، باید آن را به یک مرجع مطلق تبدیل کنیم:

=E2*$H$2

توجه کنید که ما در مرجع سلول علامت دلار را اضافه می کنیم. این به اکسل این پیام را می دهد که مهم نیست فرمول خود را در کدام سلول قرار داده باشیم، برای استفاده از درصد پاداش باید به سلول H2 مراجعه کند. ما قسمت E2 را بدون تغییر رها می کنیم، زیرا وقتی فرمول را به پایین می کشیم، می خواهیم با فروش همه کارمندان سازگار شود.

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

نحوه ترسیم جدول در ورد word