ویژه دانشجویان

فرمول نویسی در اکسل را از کجا شروع کنیم؟

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

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

بیاید قبل از هرچیز به این سوال پاسخ دهیم که چه زمانی به فرمول نویسی در اکسل نیاز پیدا می‌کنیم؟ فرمول نویسی را به چشم حل کردن یک معادله ریاضی در نظر نگیرید. همیشه قرار نیست با نوشتن یک فرمول در اکسل به جواب یک حساب و کتاب ریاضی برسیم. خیلی از موارد فرمولی که می نویسیم قرار است اطلاعاتی از جنس متن به ما نشان دهد. بعضی اوقات کارهای تکراری و زمان بر ما را در کسری از ثانیه انجام می‌دهد و بسیاری از تصمیمات منطقی و شروط مورد نیاز را اجرا می کند. حتی از فرمول نویسی در اکسل می توان در ابزارهای این نرم افزار هم استفاده کرد. مثلاً ترکیب آن با conditional formatting یا Data validation.

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

طرح مساله

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

راهم بخوانید...
نکاتی درباره آموزش تایپ سریع ده انگشتی

طراحی یک فاکتور فروش با اکسل

ابتدا تمامی اطلاعات خود را در ستون‌های اکسل وارد کنید. اگر می خواهد جدولتان راست به چپ باشد، از منوی page layout گزینه sheet right to left را بزنید.

جایی که ما باید فرمول را در آن بنویسیم در واقع ستون جمع فروش است. دقت کنید که نیازی نیست به ازای هر ردیف یک فرمول نوشته شود. اکسل قابلیتی دارد به نام auto-fill که این امکان را به ما می‌دهد که بعد از نوشتن یک فرمول با آدرس دهی صحیح، آن را به ردیف های بعدی هم تعمیم دهیم.

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

در سلول E2 (یعنی اولین سلول خالی زیر جمع فروش) کلیک کنید و یک علامت مساوی تایپ نمایید. علامت مساوی شروع کننده فرمول نویسی در اکسل است. بعد از آن روی سلول C2 (یعنی قیمت کالای اول) کلیک کنید تا اکسل به طور خودکار آدرس این سلول را درج کند. حالا علامت ستاره (*) را از روی کیبرد بزنید و روی سلول D2 (یعنی تعداد فروخته شده کالای اول) کلیک کنید و سپس enter بزنید. به همین راحتی می بینید که حاصل ضرب تعداد فروخته شده کالای اول ( خودکار ) در قیمت آن ضرب می‌شود.

پر کردن خودکار همه ردیف ها با یک فرمول

حالا به جایی می رسیم که از ابتدا به خاطرش فرمول نویسی در اکسل را شروع کردیم. فرض می‌کنیم این لیست فروش محصولات تا ۱۰۰۰ ردیف ادامه پیدا کرده است. ما میخواهیم همین فرمول برای تمام محصولات دیگر نیز قرار بگیرد. برای اینکار کافیست ماوس را گوشه سلولی که در آن فرمول نوشتید ببرید. یک مربع کوچک در کنج پایینی سلول آن می بینید. (اگر شیت را راست به چپ کرده باشید این مربع کوچک سمت چپ پایین قرار گرفته است. ) حالا روی آن کلیک کنید، کلیک خود را نگه دارید و به سمت پایین بکشید. تا جایی که لازم است.
می بینید که فرمول در سایر ردیف ها نیز کپی شد و حاصلضرب هر کدام به طور مجزا محاسبه گشت. استفاده از این تکنیک در شرایطی امکان پذیر است که تمام آدرس های وارد شده در یک فرمول، به ترتیب افزایش پیدا کنند. مثلاً در فرمول اول ما آدرس دو سلول C2 و D2 را داشتیم و برای ردیف های بعدی نیز همین آدرس ها یک شماره اضافه تر می شدند. این اضافه تر شدن اعداد آدرس را خود اکسل با auto fill کردن برای ما انجام می‌دهد و نتیجه به این سادگی که می بینید خواهد شد.

راهم بخوانید...
تفاوت پروپوزال و پایان ­نامه چیست؟

فرمول نویسی با یک سلول ثابت

حالا اگر با یک سلولی سر و کار داشته باشیم که در تمام معادلات نباید تغییر کند چه؟ مثلاً فرض کنید فاکتوری تهیه می کنید از سفارشات مختلف مشتریان برای یک کالای خاص. و قیمت این کالا متغیر است. یعنی مدام تغییر می کند. شما هم می‌خواهید با تغییر فیلد قیمت این محصول به طور خودکار فاکتور هم تغییر کند.در این مثال، می خواهیم فرمولی بنویسیم که با تغییر سلول سبز رنگ، یعنی قیمت محصول، لیست جمع فروش آپدیت شود. کاری که باید انجام بگیرد این است که برای هر ردیف از جمع فروش، تعداد فروخته شده ضربدر در قیمت محصول شود. پس فرمولی که در سلول C2 (یعنی اولین سطر از جمع فروش) می نویسیم باید این باشد : =B2*F2 یعنی تعداد فروخته شده اول ضربدر در سلول سبز رنگ که همان قیمت محصول است. حالا طبق چیزی که در بخش قبلی یاد گرفتیم، از گوشه سلول می گیریم و می کشیم به سمت پایین. یعنی auto fill می‌کنیم.

چه اتفاقی افتاد؟ چرا همه چیز به غیر از سطر اول صفر شد؟ توضیح می دهیم.

منطق کار autofill

اگر یادتان باشد گفتیم کاری که اکسل با autofill کردن انجام می‌دهد این است که شماره سطر تمام آدرس ها را یکی یکی زیاد می کند. یعنی در فرمول اول که ما داشتیم =B2*F2 بعد از auto fill کردن، در سطر بعدی داریم =B3*F3 . در مثال قبلی چنین موقعیتی هیچ مشکلی نداشت. چرا که هر دو سلول باید زیاد می شدند تا قیمت و تعداد فروخته شده کالای بعدی در فاکتور با هم ضرب شود. اما در این مثال، ما نمی خواهیم سلول F2 تغییر کند. یعنی می خواهیم B2 تغییر کند و زیاد شود اما قیمت محصول فقط همان سلول F2 باقی بماند.

راهم بخوانید...
آموزش کار با ورد گوشی

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

مطلق کردن سلول در اکسل

برای ثابت نگه داشتن آدرس یک سلول هنگام فرمول نویسی در اکسل می بایست از علامت $ استفاده کنید. به این شیوه مطلق کردن آدرس می گویند. کافیست به جای نوشتن آدرس f2 ، آن را به این صورت بنویسید $f$2 . این علامت به اکسل نشان می‌دهد که شما نمی‌خواهید در هنگام auto fill کردن این آدرس تغییر کند. در این صورت همیشه به یک سلول اشاره خواهید کرد.

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

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

امتیاز شما به مطلبی که خواندید!

۰

لایک کنید

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

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

دکمه بازگشت به بالا