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

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

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

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

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

با استفاده از فرمول‌ها، می‌توانید تنها با چند کلیک اطلاعات مورد نیاز خود را:

1. محاسبه کنید
2. گزارش‌ها را به صورت خودکار به‌روزرسانی کنید
3. حتی به پیش‌بینی داده‌ها بپردازید. 

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

آشنایی با محیط اکسل

 

 

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

برای دسترسی بهتر سطرها یا همان ردیف ها با اعداد و ستون‌ها با حروف انگلیسی نام‌گذاری می‌شوند.

برای مثال اگر در سطر شماره ۳ و ستون B ماوس را قرار دهیم، نام آن سلول B3 خواهد بود.

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

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

Home:

 

 

منوی اصلی اکسل است که شامل ابزارهای اساسی برای ویرایش، قالب‌بندی و استایل‌دهی به سلول‌ها، محدوده‌ها و داده‌ها می‌باشد.

 

Insert: 

 

 

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

 

Page Layout: 

 

 

در این منو، تمام ابزارهایی که مربوط به تنظیمات اندازه کاغذ، حاشیه‌ها، چاپ و … است قرار گرفته دارد.

 

Formulas: 

 

 

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

 

Data:

 

 

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

 

Review: 

 

 

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

 

View: 

 

 

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

 

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

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

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

 

فرمول‌ها در اکسل 

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

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

فرمول‌ها با علامت مساوی (=) آغاز می‌شوند و می‌توانند شامل اعداد، سلول‌ها، عملگرها و توابع باشند. 

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

= مقدار مورد نظر (مقدار سلول ۱ + سلول شماره ۲)

 

 

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

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

حتی یک ماشین حساب هم نمی‌تواند به این سرعت و دقت برای شما حساب کند.

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

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

اکسل چیزی در حدود ۴۰۰ فرمول و تابع از پیش تعریف‌شده دارد که تقریبا طیف بسیار گسترده‌ای از کارها حداقل چندتایی وجود دارد.

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

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

به عنوان مثال، اگر بخواهید جمع اعداد موجود در سلول‌های A1 تا A5 را به دست آورید، می‌توانید از فرمول 

=SUM(A1,A2,A3) یا =SUM(A1:A3)

استفاده کنید. ر

AVERAGE: این فرمول میانگین مقادیر موجود در یک محدوده سلولی را محاسبه می‌کند. برای مثال، میانگین اعداد موجود در سلول‌های B1 تا B5 با استفاده از فرمول

 =AVERAGE(B1:B5) 

به دست می‌آید.

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

=IF(C1>10, “بیشتر”, “کمتر”) 

بررسی می‌کند که آیا مقدار سلول C1 بیشتر از ۱۰ است یا خیر و بر اساس آن یکی از دو متن “بیشتر” یا “کمتر” را نمایش می‌دهد.

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

به عنوان مثال، برای پیدا کردن یک مقدار در ستون اول و بازگرداندن مقدار مرتبط از ستون دوم می‌توانید از 

=VLOOKUP(D1, A1 , 2, FALSE) 

استفاده کنید.

COUNT: این فرمول تعداد سلول‌های غیر خالی در یک محدوده را می‌شمارد. به عنوان مثال، با استفاده از فرمول

= COUNT(E1:E10) 

تعداد سلول‌های پر در محدوده E1 تا E10 محاسبه می‌شود.

MAX و MIN: این فرمول‌ها به ترتیب بزرگترین و کوچکترین مقدار در یک محدوده سلولی را پیدا می‌کنند. برای مثال، با فرمول 

=MAX(F1: F10) 

بزرگترین مقدار در محدوده F1 تا F10 به دست می‌آید.

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

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

برای وارد کردن فرمول در اکسل، ابتدا باید سلولی را که می‌خواهید نتیجه فرمول در آن نمایش داده شود، انتخاب کنید. سپس در نوار فرمول (که در بالای صفحه قرار دارد) فرمول خود را با علامت مساوی شروع کنید. به عنوان مثال، اگر می‌خواهید جمع دو عدد موجود در سلول‌های A1 و B1 را محاسبه کنید، باید فرمول “=A1+B1” را در نوار فرمول یا داخل خود  سلول مورد نظر وارد و سپس کلید Enter را فشار دهید. پس از وارد کردن فرمول، اکسل به طور خودکار محاسبه را انجام داده و نتیجه را در سلول نمایش می‌دهد.

تفاوت اصلی بین فرمول و تابع در اکسل این است که فرمول‌ها می‌توانند ترکیبی از اعداد، عملگرها و حتی توابع مختلف باشند، در حالی که توابع مجموعه‌ای از دستورات از پیش تعریف‌شده هستند که به منظور انجام محاسبات خاص طراحی شده‌اند. برای مثال، “=A1+B1” یک فرمول است، در حالی که

 “=SUM(A1:A10)” 

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

 “=SUM(A1) + B1” 

یک فرمول ترکیبی است که هم از تابع SUM و هم از عملگر جمع استفاده می‌کند.

 

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

تشخیص نیاز و انتخاب داده‌ها: 

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

نوشتن فرمول در سلول مورد نظر: 

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

=A1 + A2

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

استفاده از عملگر ها و توابع مناسب:

در فرمول نویسی اکسل، عملگرها و توابع مختلفی وجود دارند که بسته به نیازتان باید از آن‌ها استفاده کنید. برای مثال، برای محاسبات ریاضی می‌توانید از عملگرهای جمع (+)، تفریق (-)، ضرب (*)، و تقسیم (/) استفاده کنید. همچنین اکسل توابع از پیش تعریف شده‌ای مثل SUM برای جمع، AVERAGE برای میانگین و IF برای شرط‌ها دارد. برای مثال، فرمول محاسبه میانگین یک محدوده از سلول‌ها به این شکل است:

=AVERAGE(A1:A10)

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

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

به‌روزرسانی و اصلاح فرمول:

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

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

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

1. حتما برای شروع از علامت = استفاده شود.
2. اگر قصد فراخوانی یک تابع را دارید باید ابتدا نام تابع را بنویسید و داخل پرانتز آن، مقادیر آرگومان‌ها را وارد کنید. مانند همان تابع SUM که در ابتدا به آن اشاره شد.
3. حتما مرجعی که می‌خواهید از آن شروع کنید را باید با حرف بزرگ و شماره سلول بنویسید،‌ مثلا B3 و جلوی آن عملگر مربوطه و الی آخر.

قوانین کلی سینتکس فرمول نویسی:

• به حروف بزرگ و کوچک حساس است: به طور کلی، اسامی توابع به حروف بزرگ و کوچک حساس نیستند. اما نام سلول‌ها و محدوده‌ها به حروف بزرگ و کوچک حساس هستند.
• استفاده از پرانتز: برای مشخص کردن ترتیب انجام عملیات و گروه بندی آرگومان‌ها، از پرانتز استفاده می‌شود.
• آدرس‌دهی سلول‌ها: برای ارجاع به سلول‌ها، از آدرس‌های سلولی مانند A1، B2 و … استفاده می‌شود.
• محدوده‌ها: برای انتخاب یک محدوده از سلول‌ها، از علامت دو نقطه (:) استفاده می‌شود. برای مثال، A1:A5 محدوده‌ای از سلول A1 تا A5 را نشان می‌دهد.
• عملگرهای مرجع: برای ترکیب آدرس‌های سلولی از عملگرهای مرجع مانند $ (ثابت کردن سطر یا ستون) استفاده می‌شود.

 

معرفی عملگر ها و توابع پایه در اکسل

عملگر جمع (+): این عملگر برای جمع کردن دو یا چند مقدار استفاده می‌شود. برای مثال، 

“=A1+B1” 

دو سلول A1 و B1 را جمع می‌کند.

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

“=A1-B1” 

مقدار B1 را از A1 کم می‌کند.

عملگر ضرب (*): این عملگر برای ضرب کردن دو یا چند مقدار به کار می‌رود. برای مثال

 “=A1*B1” 

دو سلول A1 و B1 را در هم ضرب می‌کند.

عملگر تقسیم (/): برای تقسیم یک مقدار بر مقدار دیگر استفاده می‌شود. به عنوان مثال، 

“=A1/B1” 

مقدار A1 را بر B1 تقسیم می‌کند.

عملگر توان (^): این عملگر برای محاسبه توان یک عدد به کار می‌رود. برای مثال

 “=A1^2” 

مقدار A1 را به توان دو می‌رساند.

تابع SUM: این تابع برای جمع کردن مقادیر یک محدوده سلولی استفاده می‌شود. به عنوان مثال، 

“=SUM(A1)” 

مجموع مقادیر سلول‌های A1 تا A5 را محاسبه می‌کند.

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

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

یعنی می‌توانید شرط‌هایی را تعیین کنید که در صورت برآورده شدن، خروجی خاصی را نشان دهد. 

تابع IF

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

مثال: فرض کنید در ستون A نمرات دانش‌آموزان و در ستون B وضعیت پس شدن یا نشدن آن درس را نمایش دهیم. معیار ما برای نمره قبولی ۱۰ است. پس فرمول را به شکل زیر می‌نویسیم:

=IF(A2>=10,”پاس شده”,”موفق نبوده”)

نکته: همیشه برای نوشتن یک عبارت (رشته) باید آن را داخل ” ” قرار دهید.

اگر نمره دانش‌آموز در سلول A2 مساوی یا بیشتر از ۱۰ باشد، عبارت “پاس شده” و در غیر این صورت “موفق نبوده” نمایش داده می‌شود.

تابع SUMIF (جمع شرطی)

تابع SUMIF برای جمع کردن مقادیر در یک محدوده بر اساس یک شرط مشخص استفاده می‌شود.

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

=SUMIF(محدوده_شرط, شرط, محدوده_جمع)

 

مثال: فرض کنید در ستون A نام میوه‌ها و در ستون B تعداد آن‌ها را داریم. می‌خواهیم فقط  تعداد کل سیب‌ها را محاسبه کنیم. فرمول به صورت زیر خواهد بود:

=SUMIF(A2:A10,”سیب”,B2:B10)

 

این فرمول در محدوده A2 تا A10 به دنبال کلمه “سیب” می‌گردد و اگر پیدا کرد، مقادیر متناظر در محدوده B2 تا B10 را با هم جمع می‌کند.

تابع COUNTIF (شمارش شرطی)

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

ساختار کلی به شکل زیر است:

=COUNTIF(محدوده, شرط)

 

مثال: فرض کنید در ستون A نام کشورها را داریم و می‌خواهیم تعداد کشورهایی که با حرف “ب” شروع می‌شوند را بشماریم. فرمول به صورت زیر خواهد بود:

=COUNTIF(A2:A10,”ب*”)

 

علامت ستاره (*) در شرط به معنی هر کاراکتر است و به این معنی است که هر کلمه‌ای که با حرف “ب” شروع شود، شمرده می‌شود. مثلا برزیل، بوسنی، بنگلادش

جمع بندی

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

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