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

آموزش توابع اکسل و فرمول نویسی در محیط اکسل برای مبتدی تا پیشرفته نهیه شده است. فرض بر این است پژوهشگر درس اول آشنایی با محیط اکسل را مطالعه کرده است. برای مطالعه بیشتر می‌توانید به آموزش افزونه Solver و افزونه Analysis ToolPak رجوع کنید.

چهار عمل اصلی در اکسل

عملگرهای مربوط به چهار عمل اصلی عبارتند از:

• عملگر جمع +
• عملگر منها –
• عملگر ضرب .
• عملگرتقسیم /

فرض کنید می‌خواهید جمع سلول A1 و B1 را در سلول C1 نشان دهید. در سلول C1 کلیک کنید سپس در کادر FX عبارت A1+ B1 = را وارد کنید ( ابتدا علامت مساوی را وارد کنید ) سپس Enter کنید تا نتیجه جمع را ببینید.

برای تفریق در اکسل نیز بعد از اجرای برنامه مطابق روش جمع دو عدد پیش می‌رویم. ولی  به جای علامت جمع بین A1 و B1 علامت تفریق می‌گذاریم و Enter می‌کنیم. فرمول تفریق در اکسل به شکل A1-B1= است.

اولویت عملگرهای ریاضی در فرمول‌  Operator Precedence

اکسل به طور پیش فرض محاسبات را با اولویت‌هایی به مانند اولویت‌های ریاضی انجام می‌دهد:

• پرانتز
• ضرب و تقسیم
• جمع و تفریق

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

عملگرهای منطقی در اکسل

از یک عملگر منطقی در اکسل برای مقایسه دو مقدار استفاده می‌شود. عملگرهای مقایسه‌ای در بعضی مواقع عملگرهای BOOLEAN گفته میشوند زیرا نتیجه یک مقایسه در هر شرایطی مقادیر TRUE/FALSE می‌شود. در اکسل شش عملگر مقایسه‌ای وجود دارد. در جدول زیر کاری که هر کدام از این عملگرها انجام میدهند و فرمول هایی از هرکدام از آنها نشان داده است.

• عملگر مساوی =
• عملگر نامساوی <>
• بزرگتر < کوچکتر > بزرگتر مساوی =< کوچکتر مساوی =>

عملگرهای منطقی اکسل

تابع SUM و Product

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

برای جمع چند سلول متوالی ( دنبال هم ) برای فرمول نویسی از علامت ( : ) استفاده می‌شود. مثلا اگر از سلول B1 تا B4 را انتخاب کنیم بصورت (B1:B4)Sum= نوشته می‌شود و نشان دهنده انتخاب سلول‌های پشت سر هم برای فرمول می‌باشد.

اگر هدف انتخاب چند سلول تامتوالی باشد از علامت ( ; ) در بین نام سلول‌های انتخابی استفاده می‌شود. مثلا اگر فقط سلول B1 و D4 را برای فرمول نویسی انتخاب کنیم، فرمول به شکل (B1;D4)= نوشته می‌شود.

برای ضرب از عملگر * و برای تقسیم از عملکرد / استفاده می‌شود.

فرض کنید می‌خواهید ضرب سلول A1 و B1 را در سلول C1 نشان دهید. در سلول C1 کلیک کنید سپس در کادر FX عبارت A1* B1 = را وارد کنید سپس Enter کنید تا نتیجه جمع را ببینید.

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

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

تابع sumproduct در اکسل

توابع اکسل AND و OR

ابتدا باید بدانید عملگر اَمپِرسَند (Ampersand) یا & برای نمایش همزمان مقادیر چند سلول استفاده می‌شود و کاربرد آن با تابع AND تفاوت دارد. تابع CONCATENATE نیز نتایجی مشابه عملگر & دارد و برای ترکیب محتویات چندین سلول استفاده می‌شود.

تابع منطقی AND دو یا چند شرط را به صورت همزمان بررسی می‌کند و اگر همه برقرار باشند مقدار TRUE و اگر تنها یکی برقرار نباشد مقدار FALSE را بر می‌گرداند.

=AND ( شرط دوم , شرط اول, … )

= AND(A1>10,B1>10)

همچنین تابع منطقی OR دو یا چند شرط را به صورت همزمان بررسی می‌کند و اگر تنها یکی برقرار باشد مقدار TRUE و اگر هیچکدام برقرار نباشد مقدار FALSE را بر می‌گرداند.

تابع IF در اکسل

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

ساختار تابع if در اکسل به صورت زیر است:

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

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

فرض کنید میخواهید نشان دهید اگر عدد از ۷ بزرگتر است مطلوب است و اگر ۷ یا کوچکتر باشد نامطلوب است:

=IF (B1>7,”رد”,”پذیرش”)

اگر بخواهید چند شرط را همزمان بررسی کنید از ترکیب دو تابع IF و AND استفاده کنید.

=IF(AND(A1>0,A1<5), “Approved”, “Denied”)

تابع TRUNC

از تابع TRUNC برای انتخاب بخش صحیح یا Integer یک عدد استفاده می‌شود:

= TRUNC (number)

اگر بخواهید فقط بخش اعشاری یک عدد را انخاب کنید از تابع TRUNC استفاده کنید:

= number – TRUNC (number)

= B6 – TRUNC (B6)

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

تابع TRIM

تابع TRIM جزء توابع متنی بوده به منظور حذف فاصله زائد مورد استفاده قرار میگیرد. این تابع تنها یک ورودی دریافت میکند و تمامی فاصله‌های زائد در ورودی را حذف کرده و سایر کاراکتر‌ها را به همان ترتیب به عنوان خروجی ارائه می‌دهد.

=TRIM(B3)

اگر فرض کنیم یک رشته متنی در خانه B3 درج شده باشد با استفاده از این تابع تمامی اسپیس‌های اضافی متن این خانه پاک می‌شود.

تابع CLEAN و کاراکتر Enter در اکسل
برای آنکه در یک سلول کلید Enter را بزنید تا بتوانید چندین سطر داشته باشد، باید از کلید ALT+Enter استفاده کنید.

برای پاک کردن Enter باید از تابع CLEAN استفاده کنید مثلا بنویسید CLEAN (A1) تا هرچه کاراکتر اینتر در سلول A1 است حذف شود.

تابع SUBSTITUTE

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

=SUBSTITUTE (text, old text, new text)

در این تابع به جای آزگومان text به خانه موردنظر رفرنس دهید. به جای old text آن بخش از متن که مایل هستید حذف شود را بنویسید و برای آنکه با رشته جدیدی جایگزین شود از آرگومان new text استفاده کنید. اگر هم نمی‌خواهید چیزی جایگزین شود در قسمت new text از “” استفاده کنید.

تابع SUBSTITUTE در اکسل

این تابع دارای یک آرگومان دیگر به نام [Instance_Num] است. این آرگومان اختیاری و از جنس عدد هست. در صورتی که خالی بگذاریم، همه عبارت‌های معادل موردی که جستجو میکنید را با عبارت جدید جایگزین میکند. اما اگر عدد بذاریم، مثلا ۳، فقط سومین عبارت مورد نظر رو پیدا میکند و جایگزین میکند.

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

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

ابتدا روی اعداد مورد نظرتان (محورها یا سری‌ها) راست کلیک کرده و سپس Format Axis را انتخاب ‌کنید. سپس در پنجره باز شده منوی Number را انتخاب کرده و در قسمت Category روی گزینه Custom کلیک ‌کنید. بعد در کادر خالی، کد فرمت زیر را وارد کرده و گزینه Add را انتخاب کنید .

کد فرمت :

۰[$-۳۰۱۰۰۰۰]

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

۰.۰۰[$-۳۰۱۰۰۰۰]

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

جایگزینی (Replace) علامت سوال (؟)، ستاره (*) و … در اکسل

برخی نمادها و علائم مانند علامت سوال ؟ ستاره * و مواردی مانند این در اکسل قابل جایگزینی ساده نیستند و کل واژگان جایگزین می‌شود. برای این منظور قبل از علائم موردنظر از نماد مدک ~ استفاده کنید. مشکل به سادگی حل می‌شود. در کیبورد استاندارد فارسی نماد مدک با فشردن شیفت و پ قابل درج است.

حذف اعداد از متن

در دیالوگ جایزگینی (Ctrl+H) از ^# استفاده کنید. البته این روش بیشتر در مایکروسافت ورد عمل می‌کند و در اکسل گاهی عمل نمی‌کند.

توابع اکسل: تقسیم محتوای یک سلول به دو سلول

اگر یک ستون دارای محتویاتی است که باید به دو ستون تقسیم شود از ویژگی Text to Columns استفاده می‌شود.

فرض کنید در یک ستون نام و نام‌خانوادگی درج شده است و با یک خط فاصله (Space) از هم جدا شده‌اند.

• محتویات ستون مورد نظر را انتخاب کنید.
• دقت کنید ستون بعدی باید خالی باشد
• از تب Data گزینه Text to Columns را انتخاب کنید.
• در پنجره باز شده Delimited را انتخاب کنید. سپس روی Next بزنید.
• در پنجره بعدی Space را انتخاب کنید.

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

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