آموزش استفاده از ۱۷ فرمول کاربردی اکسل

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

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

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

مطالب مرتبط:

برگرداندن تنظیمات اکسل به حالت اولیه

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

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

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

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

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

• یک سلول خالی را انتخاب کنید.
• روی سلول کلیک کنید و علامت = را تایپ کنید.
• نام تابع موردنظرتان را بنویسید.
• اینتر بزنید تا تابع فراخوانی شود.
• فرمول تابع موردنظر را وارد کنید.
• دکمه inter را فشار دهید تا خروجی تابع به شما داده شود.

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

فرمول‌ها و توابع اکسل بسیار متنوع هستند. کافیست بسته به نوع عملیات موردنظرتان تابع مناسب را انتخاب کنید. در این بخش نگاهی به ۲۵ مورد از مهم‌ترین توابع اکسل می‌اندازیم.

آموزش تابع SUM در اکسل

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

به عنوان مثال، تصور کنید می‌خواهید داده دو سلول A1 و A2 را با هم جمع کنید. کافیست فرمول زیر را در یک سلول خالی وارد و سپس دکمه اینتر را بزنید.

A1+A2=

آموزش تابع AVERAGE در اکسل

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

• یک سلول خالی را انتخاب کنید.
• = و سپس average را تایپ کنید.
• سلول‌های موردنظرتان را با موس انتخاب کنید.
• اینتر بزنید.

آموزش تابع count در اکسل

این تابع اکسل در محدوده موردنظر ما، تعداد کل سلول‌هایی که دارای مقادیر عددی هستند را می‌شمارد. یعنی این تابع، سلول‌های خالی و سلول‌هایی که مقدار غیرعددی دارند را حساب نمی‌کند. در مثال زیر، تعداد کل سلول‌های دارای مقادیر عددی ستونِ «مجموع دانشجویان کلاس» با تابع count محاسبه شده و حاصل، ۵ است.

برای استفاده از این فرمول، علامت = و سپس count را در سلول موردنظرتان تایپ کنید. سپس با موس محدوده سلول‌های موردنظرتان را انتخاب کنید و اینتر بزنید.

آموزش تابع modulus در اکسل

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

MOD(A1,A2)=

در مثال زیر، باقی‌مانده تقسیم عدد ۱۰ بر ۳، ۱ و باقی‌مانده تقسیم دو عدد دیگر بر هم ۰ است.

آموزش تابع POWERدر اکسل

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

POWER(A1,3)=

اعداد ستون سفیدرنگ را به توان ۳ رسانده‌ایم.

آموزش تابع CEILING در اکسل

این تابع، یکی از پرکاربردترین توابع اکسل است و برای گرد کردن اعداد به کار می‌رود. در نظر داشته باشید که این تابع، گرد کردن را به نزدیک‌ترین مضرب یک عدد و رو به بالا انجام می‌دهد. در مثال زیر ۳۵ مضرب ۵ است و اگر رو به بالا گرد شود، حاصل ۴۰ خواهد بود که توسط فرمول (CEILING A1,5)= محاسبه شده است.

آموزش تابع FLOOR در اکسل

این تابع، درست برعکس تابع قبلی عمل می‌کند و عمل گرد کردن را رو به پایین انجام می‌دهد. در مثال زیر، ۳۵/۳۱۶ مضرب ۵ است و اگر رو به پایین گرد شود، حاصل ۳۵ می‌شود که با فرمول FLOOR(A1,5)= محاسبه شده است.

آموزش تابع CONCATENATE در اکسل

تابع CONCATENATE عباراتی که در دو سلول مجاور نوشته شده‌اند را ادغام می‌کند. به مثال زیر توجه کنید.

در این مثال، نوشته‌های سلول‌های A1 و B1 با هم و سلول‌های A3 و B3 نیز با هم ادغام شده است. برای استفاده از این تابع، فرمول‌نویسی را به یکی از دو روش زیر انجام دهید.

CONCATENATE(A1, ” “, B1)=

CONCATENATE(A1&” “&B2)=

آموزش تابع LEN در اکسل

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

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

آموزش تابع SUBSTITUTE در اکسل

SUBSTITUTE در زبان انگلیسی، به معنای «جایگزین کردن» است. بنابراین این تابع، همان‌طورکه از نامش پیداست، بخشی از داده‌های متنی یا عددی سلول‌ موردنظر شما را با متن یا عددی دیگر جایگزین می‌کند.

در این مثال، با کمک فرمول زیر I LIKE را با HE LIKES جایگزین کرده‌ایم.

SUBSTITUTE(A1, “I like”,”He likes”)=

در این مثال نیز عدد ۲۰۱۰ با ۲۰۱۶ جایگزین شده است اما همان‌طورکه مشاهده می‌کنید، در متن اولیه دوتا ۲۰۱۰ داریم. به همین دلیل از آن‌جایی‌که به جایگزین کردن ۲۰۱۰ اولی نیاز داریم، فرمول کلی را اندکی تغییر می‌دهیم.

SUBSTITUTE(A21,2010, 2016,2)=

آموزش تابع LEFT، RIGHT و MID در اکسل

تصور کنید لیستی از شماره تلفن مشتریان خود جمع‌آوری کرده‌اید و به استخراج ۴ رقم آخر، وسط یا سمت راست این شماره‌ها نیاز دارید. در چنین مواردی، توابع RIGHT، left و MID در خدمت شما خواهند بود! در مثال زیر ۴ شماره اول، آخر و وسط شماره تلفن را با فرمول‌های مربوطه جدا کرده‌ایم.

فرمول تابع right

RIGHT(A4,4)=

فرمول تابع mid

MID(A4,4,4)=

فرمول تابع left

LEFT(A4,4)=

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

آموزش تابع UPPER, LOWER, PROPER در اکسل

این تابع، جز، توابع فوق‌العاده پرکاربرد اکسل هستند. تابع UPPER حروف متن را بزرگ و تابع LOWER حروف متن را کوچک می‌کند. تابع PROPER نیز حرف اول را بزرگ و بقیه حروف را کوچک می‌کند. در مثال زیر حروف ردیف سبز، با تابع lower کوچک و حروف تابع زرد نیز با تابع upper بزرگ شده‌اند. روی مقدار ردیف نارنجی نیز تابع propper اعمال شده است.

فرمول‌ها:

LOWER(A6)=

PROPER(A7)=

UPPER(A8)=

آموزش تابع NOW در اکسل

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

آموزش تابع TODAY در اکسل

این تابع یکی از توابع ساده و در عین حال کاربردی اکسل است و همان‌طورکه از نامش پیداست، تاریخ روز را در سلول انتخابی شما وارد می‌کند. توابعِ DAY، YEAR و MONTH نیز کارکرد مشابهی دارند و به ترتیب روز، سال و ماه را وارد می‌کنند.

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

=()TODAY

=DAY(TODAY())

=MONTH(TODAY())

=YEAR(TODAY())

آموزش تابع DATEDIF در اکسل

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

DATEDIF(B2,B1,”y”)=

نکته: در فرمول‌نویسی ابتدا تاریخ کوچک‌تر و سپس تاریخ بزرگ‌تر را وارد کنید.

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

آموزش تابع VLOOKUP در اکسل

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

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

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

در جدول زیر قصد داریم تا با وارد کردن کد دانشجو، به نام و نام خانوادگی او دسترسی داشته باشیم. بنابراین فرمول‌نویسی را در اولین سلول ستون «نام و نام خانوادگی» انجام می‌دهیم. مثل همیشه علامت = و سپس نام فرمول را تایپ می‌کنیم اما این بار، با کلید میانبر ctrl+a پنجره جدیدی باز می‌کنیم و وروردی‌های فرمول را در این پنجره می‌نویسیم.

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

خب! شروع می‌کنیم و به ترتیب متغیرهای این پنجره را وارد می‌کنیم.

• در خانه اول (Lookup-value)، داده‌ای که به دنبال داده متناظرش هستید را وارد کنید. به عنوان مثال ما در اینجا به دنبال نام و نام خانوادگیِ مربوط به کدهای دانشجویی هستیم. یعنی نام و نام خانوادگی، یکی از داده‌های متناظر کدهای دانشجویی است. بنابراین کد دانشجویی ۵۰۱ را در این خانه وارد می‌کنیم.
• در خانه دوم (table-array)، محدوده‌ای که جستجو در آن انجام می‌شود را وارد می‌کنیم. کافیست روی علامت فلش کلیک کنید و سپس با موس محدوده موردنظر را انتخاب کنید. در این مثال محدوده مورد نظر ما از A1 تا D10 است.
• در خانه بعدی (Col-index-num)، شماره ستونی که قصد دارید جستجو در آن انجام شود را وارد کنید. در این مثال ستون نام و نام خانوادگی (که قصد جستجو در آن را داریم)، ستون دوم است.
• خانه آخر را نیز همیشه با عبارت false پر کنید.

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

VLOOKUP(501,A1:D10,2,FALSE)=

آموزش تابع COUNTIF در اکسل

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

برای نوشتن فرمول این تابع نیز توصیه می‌کنیم از کلید میانبر ctrl+a استفاده کنید. چراکه فرمول COUNTIF نیز قدری پیچیده است. پس بعد از علامت = و نام تابع، کلید میانبر ترکیبی را فشار دهید و ورودی‌های پنجره بازشده را طبق دستور زیر پر کنید.

• خانه اول (range) به محدوده موردنظر شما اختصاص می‌یابد. پس روی فلش کلیک کنید و محدوده موردنظرتان را با موس انتخاب کنید. در این مثال محدوده B3 تا B12 موردنظر ما است.
• خانه دوم (Criteria) به شرط موردنظر شما اختصاص دارد. در این مثال، قصد داریم تا کارمندان متاهل را بشماریم. پس شرط ما «متاهل» است.

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

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

(B3:B12,”متاهل”)COUNTIF=

(B4:B13,”مجرد”)COUNTIF=

مطالب مرتبط:

کلیدهای ترکیبی اکسل

جمع‌بندی

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

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

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