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

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

آموزش گام‌به‌گام فرمول‌ نویسی در اکسل از صفر تا صد

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

گام ۱: اصول اولیه طریقه فرمول نویسی در اکسل

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

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

• =۱۰۰-۵۰

به‌جای واردکردن مقادیر به‌طور مستقیم در فرمول اکسل می‌توانید به سلول‌هایی که حاوی آن مقادیر هستند، ارجاع (Reference) دهید.

به‌عنوان‌مثال، اگر می‌خواهید مقدار سلول B2 را از مقدار سلول A2 کم کنید، فرمول تفریق زیر را می‌نویسید:

• =A2-B2

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

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

سه نوع ارجاع به سلول‌ها در اکسل وجود دارد:

• ارجاع نسبی (Relative Reference): هنگام کپی فرمول، آدرس سلول تغییر می‌کند. مثال: A1؛
• ارجاع مطلق (Absolute Reference): با کپی فرمول، آدرس سلول ثابت می‌ماند. مثال: $A$1؛
• ارجاع ترکیبی (Mixed Reference): یک بخش از آدرس ثابت و بخش دیگر متغیر است. مثال: $A1 یا A$1؛

به‌عنوان‌مثال:

• =$A$1*B2

این فرمول، مقدار سلول A1 را در مقدار سلول B2 ضرب می‌کند. اگر این فرمول را به سلول‌های دیگر کپی کنید، بخش $A$1 ثابت می‌ماند، اما B2 تغییر می‌کند. این نوع فرمول معمولاً زمانی استفاده می‌شود که می‌خواهید یک مقدار ثابت (مثلاً یک ضریب در $A$1) را در مقادیر متغیر (ستون B) ضرب کنید.

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

در شکل زیر، دو نام ایجاد کرده‌ایم:

• revenue برای سلول A2
• expenses برای سلول B2

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

• =revenue-expenses

در ادامه شما را دعوت به مطالعه آموزش شماره صفحه در ورد می کنیم.

گام ۲: آشنایی با توابع پایه در اکسل

برای  فرمول نويسي در اكسل باید با توابع پایه این نرم‌افزار آشنا باشید. توابع اکسل چیزی جز فرمول‌های ازپیش‌تعریف‌شده که محاسبات مورد نیاز را در پس‌زمینه انجام می‌دهند، نیستند.

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

تابع SUM

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

=SUM(B1:B5)

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

تابع AVERAGE

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

• =AVERAGE(number1, [number2], …)

که number1، number2،…،  مقادیر عددی هستند که می‌خواهید میانگین آن‌ها را پیدا کنید.

به‌عنوان‌مثال، برای میانگین ۲ محدوده و ۱ سلول جداگانه، فرمول به‌صورت زیر است:

=AVERAGE(B4:B6، B8:B10، B12)

توابع MAX و MIN

این توابع به‌ترتیب بزرگ‌ترین و کوچک‌ترین عدد در یک محدوده را پیدا می‌کنند.

• =MAX(A1:A6)

مثال بالا بزرگ‌ترین عدد در محدوده A1 تا A6 را پیدا می‌کند.

تابع COUNT

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

• =COUNT(D2:D21)

برای مثال، تعداد سلول‌های حاوی عدد در محدوده D2 تا D21 را می‌شمارد.

تابع IF

تابع IF برای ایجاد شرط‌های منطقی استفاده می‌شود. ساختار این تابع به‌صورت زیر است:

=IF(logical_test, [value_if_true], [value_if_false])

برای مثال:

=IF(E1>100, “up”,“down”)

اگر مقدار E1 بیشتر از ۱۰۰ باشد، “up” و درغیراین‌صورت “down” را نمایش می‌دهد.

تابع VLOOKUP

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

• =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

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

• =VLOOKUP($E2, $A$2:$C$11, 2, FALSE)

در محدوده A2:C11 به‌دنبال مقدار E2 می‌گردد و مقدار متناظر در ستون دوم را برمی‌گرداند.

گام ۳- آموزش فرمول نويسي در اكسل با استفاده از جادوگر تابع

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

مرحله اول: روی دکمه “Insert Function” در تب “Formulas” کلیک کنید یا علامت مساوی (=) را در سلول تایپ کنید.

به‌طور پیش‌فرض، منوی کشویی ۱۰ تابع اخیر استفاده‌شده را نشان می‌دهد، برای دسترسی به لیست کامل، روی More Functions… کلیک کنید.

مرحله دوم: نام تابع موردنظر را تایپ کنید یا توضیح کوتاهی از کاری که می‌خواهید انجام دهید، بنویسید.

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

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

مرحله چهارم: روی “OK” کلیک کنید تا فرمول در سلول وارد شود یا اینتر بزنید.

اگر با سینتکس فرمول‌ها آشنا هستید، می‌توانید فرمول را مستقیماً در سلول یا نوار فرمول تایپ کنید. اکسل به‌طور خودکار پیشنهاداتی برای تکمیل فرمول به شما ارائه می‌دهد. اگر فرمول شما طولانی شد، طبق آنچه که در وب‌سایت ۲۴ وب لرن منتشر شده است، کافی است کلیدهای جانبی Alt + Enter را فشار دهید تا بتوانید بدون انتقال به سلول بعدی به خط بعدی در همان سلول بروید.

گام ۴: تکنیک‌های پیشرفته فرمول نويسي در اكسل

حال که با توابع پایه آشنا شدید، در گام سوم آموزش فرمول نويسي در اكسل به تکنیک‌های فرمول نویسی اکسل پیشرفته می‌پردازیم:

فرمول‌های آرایه‌ای

طبق تعریفی که در وب‌سایت ablebits.com آورده شده است:

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

توابع ترکیبی

ترکیب چند تابع می‌تواند محاسبات پیچیده‌تری را امکان‌پذیر کند. برای مثال:

• =SUMIF(A1:A10,”>5″,B1:B10)

این فرمول مجموع مقادیر در B1:B10 را برای سلول‌های متناظر در A1:A10 که بزرگ‌تر از ۵ هستند، محاسبه می‌کند.

استفاده از توابع منطقی AND و OR

این توابع برای ایجاد شرط‌های پیچیده‌تر استفاده می‌شوند. مثالی از این توابع را می‌توانید در ادامه مشاهده کنید:

• =IF(AND(A1>10,B1<20),”قبول”,”رد”))

اگر A1 بزرگ‌تر از ۱۰ و B1 کوچک‌تر از ۲۰ باشد، “قبول” و درغیراین‌صورت “رد” را نمایش می‌دهد.

در ادامه نیز اصول فرمول نویسی در اکسل را در قالب یک جدول اطلاعاتی به شما نشان خواهیم داد:

مرحله	توضیحات
گام ۱: اصول اولیه	شروع هر فرمول با علامت مساوی (=)؛ استفاده از ارجاع‌های نسبی، مطلق، و ترکیبی در فرمول‌ها.
گام ۲: توابع پایه	آشنایی با توابع پرکاربرد مانند SUM، AVERAGE، MAX، MIN، COUNT، IF، VLOOKUP.
گام ۳: جادوگر تابع	استفاده از ابزار “Insert Function” برای ایجاد فرمول‌ها بدون نیاز به دانستن سینتکس دقیق.
گام ۴: تکنیک‌های پیشرفته	استفاده از فرمول‌های آرایه‌ای، توابع ترکیبی مانند SUMIF، و توابع منطقی AND و OR برای محاسبات پیچیده‌تر.
حسابداری	استفاده از فرمول‌هایی مانند SUM، VLOOKUP، SUMIF در حسابداری برای محاسبات مالی و گزارش‌ها.

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

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

به‌عنوان‌مثال، در حسابداری، فرمول‌های پرکاربرد شامل SUM و SUMIF برای جمع‌کردن مقادیر با شرایط خاص، VLOOKUP و XLOOKUP برای جستجو و استخراج اطلاعات از جداول بزرگ و PMT و PV برای محاسبات مالی مانند اقساط وام و ارزش فعلی می‌شوند.

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

کجا فرمول نويسي در اكسل را از صفر تا صد بیاموزیم؟

در دنیای پرشتاب امروز، تسلط بر نرم‌افزار اکسل به یک مهارت حیاتی تبدیل شده است. سایت ۲۴weblearn.com با درک این نیاز، دوره آموزش اکسل را ارائه می‌دهد که با امتیاز چشمگیر ۴.۹۲ از ۵، مورد استقبال کاربران قرار گرفته است.

این دوره که توسط استاد برجسته، پدرام تقی‌زاده، تدریس می‌شود، بیش از ۹ ساعت محتوای آموزشی را در قالب ۱۸ ویدیو ارائه می‌دهد. از مزایای ویژه این دوره می‌توان به دسترسی مادام‌العمر به تمام فایل‌ها و به‌روزرسانی‌ها، همراه با پشتیبانی دائمی اشاره کرد.

دوره آموزش اکسل ۲۴ وب لرن با الگوبرداری از برترین آموزش‌های Udemy، مسیری منسجم از مبتدی تا پیشرفته را طی می‌کند. در سطح مقدماتی، مفاهیم پایه مانند ورود و فرمت‌بندی داده‌ها آموزش داده می‌شود. سطح متوسط به فرمول‌نویسی و ایجاد نمودارها می‌پردازد و در سطح پیشرفته، مباحثی چون جداول Pivot، برنامه‌نویسی با Macro و تحلیل داده‌ها ارائه می‌شود.

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

با قیمت ویژه ۱۹۹,۰۰۰ تومان (با تخفیف ویژه از قیمت اصلی ۴۹۹,۰۰۰ تومان)، این دوره سرمایه‌گذاری ارزشمندی برای آینده حرفه‌ای شماست. فراگیری اکسل شما را در دنیای فناوری اطلاعات به جایگاهی ممتاز می‌رساند.

سخن پایانی

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

 

منابع:

ablebits

سوالات متداول

فرق بین VLOOKUP و XLOOKUP چیست؟

XLOOKUP نسخه جدیدتر و انعطاف‌پذیرتر VLOOKUP است. XLOOKUP می‌تواند در هر دو جهت جستجو کند و نیازی به مرتب‌سازی داده‌ها ندارد.

چگونه می‌توانم یک فرمول را به کل ستون اعمال کنم؟

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

چطور می‌توانم خطاهای #N/A را در فرمول‌هایم حل کنم؟

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

آیا می‌توانم از اکسل برای تحلیل داده‌های بزرگ استفاده کنم؟

بله، اکسل ابزارهایی مانند Power Query و Power Pivot دارد که برای کار با داده‌های بزرگ طراحی شده‌اند.

چگونه می‌توانم از فرمول‌هایم در برابر تغییرات ناخواسته محافظت کنم؟

می‌توانید سلول‌ها یا کل شیت را قفل کنید و سپس از منوی Review گزینه Protect Sheet را انتخاب کنید.