آموزش فرمول نویسی اکسل (Excel) | (قدم به قدم + فیلم آموزشی)

آموزش فرمول نویسی اکسل(Excel)

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

فرمول همانند متن، عدد و سایر داده ها به سلول وارد میشود با این تفاوت که قبل از نوشتن فرمول حتما باید علامت “=” قرار داده شود. در واقع ورود این علامت در ابتدای یک عبارت به اکسل می فهماند که عبارت وارد شده یک دستور یا فرمول است.

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

در ادامه آموزش فرمول نویسی اکسل فرمول نویسی مطلق یعنی فرمول متشکل از عدد و علائم ریاضی باشد. برای مثال فرمول ۲+۵= یک فرمول مطلق است زیرا در آن فقط علامت ریاضی و عدد وجود دارد. فرمول نویسی مطلق در سلولها کاری همانند ماشین حساب ساده انجام میدهد. فرض کنید میخواهیم در سلول A1 حاصل ضرب دو عدد ۵ و ۷ را محاسبه کنیم. برای این کار ابتدا این سلول را فعال میکنیم.

سپس برای نوشتن فرمول ابتدا علامت “=” را قرار میدهیم و بلافاصله بعد از آن عبارت ۵*۷ را مینویسیم و در پایان کلید ENTER را میفشاریم.

پس از فشردن کلید ENTER حاصل فرمول در سلول A1 نمایش داده میشود.

همچنین زمانی که روی سلول A1 کلیک کنیم، در نوار فرمول، فرمول موجود در این سلول نمایش داده میشود.

مثال ۱ برای آموزش فرمول نویسی مطلق در اکسل

بهای تمام شده یک دارایی ۱۰۰۰۰۰۰ ریال و عمر مفید آن ۵ سال میباشد. این دارایی در پایان عمر مفید خود بیارزش تلقی میشود. استهلاک این دارایی را به روش خط مستقیم در سلول A1 محاسبه کنید؟

برای این کار ابتدا سلول A1 را فعال میکنیم و سپس عبارت ۱۰۰۰۰۰۰/۵= را در آن نوشته و کلید ENTER را میفشاریم.

استهلاک سالانه این دارایی در سلول A1 بصورت زیر نشان داده میشود.

مثال ۲ برای آموزش فرمول نویسی مطلق در اکسل

میزان بهره دریافتنی بابت یک ورق قرضه ۵۰۰۰۰۰۰ ریالی با نرخ بهره اسمی ۱۵ درصد در سال را در پایان سال در سلول K3 محاسبه کنید؟

برای این کار ابتدا سلول K3 را فعال میکنیم و سپس عبارت ۰.۱۵*۵۰۰۰۰۰۰= را در آن نوشته و کلید ENTER را میفشاریم.

خواهیم دید در سلول K3 مبلغ بهره قابل دریافت در پایان یک سال و در نوار فرمول، فرمول این محاسبه نمایش داده شده است.

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

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

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

میخواهیم مبلغ استهلاک سالانه دارایی را به روش خط مستقیم در سلول B5 محاسبه کنیم. اگر از فرمول نویسی مطلق استفاده کنیم، در سلول B5 باید عبارت ۱۰۰۰۰۰۰/۵= را بنویسیم و کلید ENTER را بفشاریم. اما میخواهیم از فرمول نویسی نسبی برای این مثال استفاده کنیم.

این فرمول بصورت خطی به شکل  ۵/۰-۱۰۰۰۰۰۰ نوشته میشود. اگر همین فرمول را با یک علامت “=” در سلول B5 بصورت بنویسیم، همان نتیجه حاصل میشود. اما این نوع فرمول نویسی مطلق است و مدنظر ما نیست.

اگر بجای اعداد، آدرس سلول آنها را در فرمول بنویسیم، در واقع فرمول را بصورت نسبی نوشته ایم. فرمول نسبی این مثال بصورت =B1-B3)/B2) میباشد. حال اگر همین فرمول را در سلول B5 بنویسیم و کلید ENTER را فشار دهیم همان نتیجه حاصل میشود.

نوشتن نام سلول ها در فرمول وقتگیر است و احتمال اشتباه را افزایش میدهد. برای سهولت در امر نوشتن فرمول نسبی، میتوان بجای نوشتن آدرس سلول، با ماوس روی آن کلیک کرد. برای نمونه در همین مثال برای نوشتن فرمول ابتدا علامت “=” را قرار میدهیم و سپس پرانتز باز میکنیم و روی سلول B1 که مبلغ بهای تمام شده دارایی است کلیک میکنیم.

سپس علامت “-” قرار میدهیم و روی سلول B3 که ارزش اسقاط دارایی است کلیک میکنیم و سپس پرانتز را میبندیم.

و پس از آن علامت “/” را قرار داده و سپس روی سلول B2 که معرف عمر مفید دارایی است کلیک میکنیم و در پایان روی کلید ENTER کلیک میکنیم.

خواهیم دید استهلاک سالانه این دارایی در سلول B5 نمایش داده شده است.

حال اگر در سلول B1 مبلغ دارایی را تغییر دهیم و بجای ۱۰۰۰۰۰۰ ریال مبلغ ۱۵۰۰۰۰۰ ریال را وارد کنیم، خواهیم دید که مبلغ استهلاک جدید محاسبه و نمایش داده میشود.

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

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

با استفاده از فرمول نوشته شده در مثال فوق استهلاک یک دارایی با بهای تمام شده ۶۰۰۰۰۰۰ ریال و عمر مفید ۱۵ سال و ارزش اسقاط ۱۵۰۰۰۰۰ ریال را به روش خط مستقیم محاسبه کنید.

برای این کار تنها کافیست مبالغ و عمر مفید را تغییر دهیم. بهای تمام شده را به ۶۰۰۰۰۰۰ ریال و ارزش اسقاط را به ۱۵۰۰۰۰۰ ریال و عمر مفید را به ۱۵ تغیر میدهیم.

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

همانند تصویر زیر اطلاعاتی در مورد یک ورقه قرضه به مبلغ اسمی ۲۵۰۰۰۰۰ ریال و نرخ بهره سالانه ۱۲ درصد را در اختیار داریم. بهره این اوراق یک بار در سال و در پایان سال پرداخت میشود. مطلوبست محاسبه بهره قابل دریافت در پایان سال در سلول B3.

برای این کار پس از قراردادن علامت “=” در سلول B3 روی سلول B1 کلیک کرده و علامت “*” را قرار میدهیم.

در ادامه روی سلول B2 کلیک میکنیم و در پایان کلید ENTER را میفشاریم.

در سلول B3 شاهد بهره قابل دریافت در پایان سال خواهیم بود.

حال اگر بخواهیم بهره قابل دریافت ورقه قرضه ۱۰۰۰۰۰۰ ریالی با نرخ بهره اسمی ۱۱ درصد را محاسبه کنیم کافیست سلولهای B1 و B2 را به موارد مذکور تغییر دهیم.

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

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

در واقع میتوان فرمول خطی این محاسبه را بصورت (۰.۱۵/۲)*۴۰۰۰۰۰۰= نوشت. حال اگر بجای عدد ۴۰۰۰۰۰۰ ریال، آدرس سلول B1 و بجای نرخ بهره ۱۵ درصد، آدرس سلول B2 را در فرمول قرار دهیم، فرمولی ترکیبی بصورت =(B1*(B2/2 بدست میآید. اگر این فرمول را در سلول B3 بنویسیم نتیجه زیر را مشاهده خواهیم کرد.

خاصیت تطبیق فرمول ها در Excel

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

میخواهیم مبلغ بهره قابل دریافت از بابت هر کدام از اوراق را در ستون D محاسبه کنیم. برای این کار ابتدا در سلول D2 بهره قابل دریافت از اوراق قرضه شرکت الف را محاسبه میکنیم. برای این کار در این سلول پس از علامت “=” ابتدا روی سلول B2 کلیک کرده و علامت “*” قرار میدهیم.

سپس روی سلول C2 کلیک کرده و در پایان کلید ENTER را میفشاریم.

حاصل بصورت زیر است.

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

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

با این کار اکسل بصورت خودکار در سلول D3 فرمول =B3*C3، در سلول D4 فرمول =B4*C4 و در سلول D5 فرمول =B5*C5 را نوشته است. با کلیک بر هر کدام از این سلولها، فرمول آن را در نوار فرمول مشاهده میکنید.

حالا میخواهیم در سلول B6 جمع درآمد بهره از اوراق قرضه این شرکت را محاسبه کنیم. برای این کار میتوانیم روی سلول B6 کلیک کرده و فرمول =D2+D3+D4+D5 را نوشته و در پایان کلید ENTER فشرد. همچنین میتوان بجای نوشتن نام سلولها با ماوس روی آنها کلیک کرد.

ثابت کردن بخشی از فرمول در نرم افزار اکسل

در ادامه آموزش فرمول نویسی اکسل در مثال فوق با پایین کشیدن فرمول موجود در سلول D2، اکسل بصورت خودکار به ازای هر سلولی که به پایین کشیدیم، یک شماره به آدرس سلول اضافه کرد و فرمول جدید تولید کرد. فرمول سلول D2 برابر با =B2*C2 بود و زمانیکه این فرمول را با استفاده از خاصیت تطبیق اکسل یک سلول به پایین کشیدیم اکسل فرمول =B3*C3 را در سلول D3 نمایش داد.

گاهی لازم است در صورت انتقال و تطبیق یک فرمول بخشی از آن همیشه ثابت باشد و تغییر نکند .فرض کنید اوراق قرضهای که یک شرکت در اختیار دارد بصورت زیر است و نرخ بهره اسمی همه اوراق یکسان و ۱۵ درصد باشد.

میخواهیم مبلغ بهره قابل دریافت از هر اوراق را در ستون C محاسبه کنیم. برای این کار همانند مثال قبل ابتدا در سلول C2 فرمول مربوط به این سلول را مینویسیم. فرمول محاسبه بهره قابل دریافت از شرکت الف بصورت B2*B7= است.

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

خواهیم دید که اعداد بدست آمده در سلولهای C3 تا C5 اشتباه محاسبه شده است. با نگاهی به فرمول موجود در این سلولها مشاهده میکنیم که برای مثال در سلول C3 فرمول =B3*B8 نوشته شده (با انتقال یک سلول به پایین، شماره سطر عناصر فرمول هم یک واحد اضافه شد). اما چون سلول B8 خالی است، صفر در نظر گرفته میشود و حاصل ضرب عدد ۳۰۰۰۰۰۰ ضربدر صفر عدد صفر میباشد. برای دو سلول دیگر نیز همین اتفاق رخ داده است.

برای حل این مشکل دوباره به سلول C2 بازمیگردیم و فرمول آن را با فشردن کلید DELETE پاک میکنیم و فرمول جدید =B2*B7 را مینویسیم، اما قبل از فشردن کلید ENTER یک بار کلید F4 را فشار میدهیم تا شکل آدرس سلول B7 تبدیل به $B$7 شود و سپس کلید ENTER را میفشاریم.

حال اگر روی مربع کوچک توپر گوشه سلول C2 دبل کلیک کنیم با نتیجه زیر روبرو میشویم.

اگر فرمول سلولها را مشاهده کنیم خواهیم دید که قسمت اول فرمولها که مربوط به مبلغ اسمی اوراق است تغییر میکند اما بخش دوم همه فرمولها ثابت و یکسان است. برای مثال فرمول سلول C3 بصورت B3*$B$7= و فرمول سلول C5 بصورت B5*$B$7= میباشد.

حالتهای دیگر ثابت کردن به این صورت است که فرضا اگر روی سلول G12 در یک فرمول دوبار کلید F4 را فشار دهیم آدرس سلول بصورت G$12 و اگر سه بار کلید F4 را فشار دهیم آدرس سلول بصورت $G12 تغییر مییابد. در واقع علامت $ قبل از نام ستون و یا شماره سطر آن باعث ثابت شدن آن در فرمول می شود.

قواعد فرمول نویسی در اکسل(excel)

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

علائم ریاضی در اکسل بصورت زیر هستند:

• عمل جمع ⇐ +
• عمل تفریق ⇐ –
• توان ⇐ ^  یا SHIFT+6
• عمل تقسیم ⇐  /
• عمل ضرب ⇐ * یا SHIFT+8

نکته ۱ در فرمول نویسی اکسل : در فرمول نویسی حتماً ابتدا علامت مساوی (=) را بنویسید بعد از تایپ نام تابع، پرانتز را باز کنید تا آن تابع فعال شود.

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

دقت کنید علامت جدا کننده بین آرگومان‌ها سمی‌کالون (;) یا کاما (,) است که این علامت با توجه به رایانه‌های مختلف متفاوت است.جهت داشتن اینکه آیا فرمول اکسل شما کاما میپذیرد یا سمی‌کالون کافیست یک تابع مثل ” )Sum= ” را وارد نموده سپس به راهنمای فرمول که در پایین آن ظاهر می‌شود نگاه کنید.

نکته ۳ : دو نقطه (:) در آرگومان های اکسل به معنی فاصله (بازه) است.

نکته ۴ : شما میتوانید یک فرمول را هم  داخل یک سلول تایپ کنید و هم نوار فرمول بار(Bar Formula)

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

به عنوان مثال تابع SUBTOTAL حداقل ۲ آرگومان دارد ولی می‌تواند بیشتر هم شود بعبارتی چون از آرگومان سوم(ref2) به بعد کروشه دارد به معنای اختیاری بودن آن آرگومان‌ها است.

تابع دیگری مانند تابع IFERROR فقط ۲ آرگومان دارد نه کمتر نه بیشتر و آرگومان اختیاری ندارد.

نکته ۷ : در صورتی که آرگومانی کلمه num که مخفف number (عدد) است را داشته باشد به این معنی است که داخل آن آرگومان باید عدد نوشته شود:

پر کردن خودکار در نرم افزار اکسل

در ادامه آموزش فرمول نویسی در اکسل همانند تطبیق و کپی فرمولها در اکسل با استفاده از مربع کوچک گوشه سلولها میتوان متن و اعداد را نیز تطبیق یا ادامه داد. فرض کنید میخواهیم در سلول A1 تا A10 اعداد ۱ تا ۱۰ را بنویسیم. برای این کار میتوانیم تک تک در سلولها این اعداد را نوشت که کاری وقتگیر است. اما راه سریعتری برای این کار وجود دارد. برای این کار میتوان در سلول A1 عدد ۱ و در سلول A2 عدد ۲ را نوشت، سپس این دو سلول را با هم انتخاب کرد ،بصورت زیر.

حال اگر ماوس را روی مربع کوچک گوشه سلول A2 ببریم خواهیم دید شکل ماوس به صورت + تغییر مییابد. در این زمان باید کلیک کنیم و کلیک را نگه داریم و ماوس را تا سلول A10 بکشیم و رها کنیم.

مطلب مرتبط : روش هایی برای سریع وارد کردن داده ها در اکسل

آشنایی با خطاهای رایج در فرمول نویسی

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

الف)خطای ۱ام در اکسل  Not Available) #N/A)

N/A مخفف Not Available است و زمانی رخ می‌دهدکه داده‌ی مرتبط با فرمول مورد نظر، موجود نباشد.
مثلاً در فرمول Lookup اگر در آرگومان اول (بخش اول) که باید مقدار یا ارزش سرچ شدنی قرارگیرد، خالی باشد اکسل خطای N/A# میدهد، یعنی سلول مورد نظر خالی است و چیزی موجودنیست.

ب)خطای ۲ام در اکسل DIV/0 # (خطای تقسیم بر صفر)

این خطا زمانی رخ میدهد که فرمول تقسیم بر صفر نوشته شده باشد یا بعبارتی در ریاضیات همان اصطلاح “تعریف نشده” است. اگر درسلول C1 فرمول A1/B1 (A1تقسیم بر B1) نوشته شود خطای DIV/0 # ظاهر می‌شود.

ج) خطای ۳ام در اکسل NAME # (خطای NAME )

این خطا زمانی رخ میدهد که متن فرمول نوشته شده غلط باشد. معمولا جهت نوشتن توابع اکسل یا آدرس‌دهی به سلول‌های دیگر ابتدا باید یک مساوی (=) بنویسیم سپس شروع به نوشتن تابع مورد نظر می‌کنیم. درصورتی که متن بعد ازمساوی برای اکسل ناشناخته باشد این خطا ظاهر می‌شود. مثلا در سلول A1 اگر بنویسیم Sales= خطای NAME # ظاهر می‍‌شود چون نام این متن (Sales) برای اکسل تعریف نشده است.

د) خطای ۴ام در اکسل !NUM#(خطای عدد)

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

ه) خطای ۵ام در اکسل REF# (خطای مرجع)

این خطا مربوط به مرجع تابع است و در صورتی که مرجع یا تابع پاک شود این خطا ظاهر میشود. مثلا در فرمول (Sum (A1:A3 اگر ستون A حذف شود خطاای REF# ظاهر می‌شود چون محدوده جمع زده شده حذف شده است.

و) خطای ۶ام در اکسل VALUE# (خطای ارزش یا داده)

این خطا برای زمانی است که داده نامناسب استفاده شود. مثلا در سلول C1خطای VALUE# به دلیل ناهمخوانی داده‌های سلول A1 و B1 ظاهر شده است.

ز) خطای ۷ام در اکسل #### (خطای پهنای ستون)

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

تابع IFERROR در اکسل

این تابع برای کنترل پیغام خطای توابع است یعنی زمانی که تابعی پیغام خطا مانند #VALUE! ،#REF! ،#N/A و … نشان می دهد با استفاده از این تابع می‌توان این پیغام را به صورت دیگر ظاهر کرد.

Value: فرمول مورد نظر.

Value-if-error: پیغامی که می خواهید در صورت ظاهر شدن خطا نشان داده شود.

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

جدول ارزش مرکب یک ریال را برای یک تا ده سال و با نرخ بهره سالانه ۱ تا ۵ درصد در سال را تشکیل دهید. نتیجه حاصل به شکل زیر خواهد بود.

در ابتدا شماره دوره ها را مینویسیم. برای این کار در سلول A1 عبارت “دوره”، در سلول A2 عدد ۱ و در سلول A3 عدد ۲ را مینویسیم. حالا دو سلول A2 و A3 را با هم انتخاب میکنیم و سپس روی مربع کوچک سلول A3 کلیک کرده و تا سلول A11 میکشیم و رها میکنیم.

حالا برای نوشتن درصد ها در سطر ۱ ابتدا در سلول B1 عدد ۰.۰۱ و در سلول C1 عدد ۰.۰۱ را مینویسیم و سپس دو سلول B1 و C1 را با هم انتخاب کرده و سپس روی مربع کوچک گوشه سلول C1 کلیک کرده و نگه داشته و تا سلول F1 میکشیم و رها میکنیم.

تا اینجای کار شکل جدول مورد نظر بصورت زیر است.

حالا نوبت نوشتن فرمول ارزش مرکب است. فرمول ارزش مرکب بصورت زیر است:

(۱+i)ˆn

در سلول B2 پس از علامت “=” پرانتز باز کرده و عبارت “(۱+B1)” را مینویسیم و روی نام سلول B1 یک بار کلید F4 را فشار میدهیم تا بصورت =(۱+B$1) تغییر یابد و در بیرون پرانتز بعد از علامت توان “^” روی سلول A2 کلیک کرده و سه بار کلید F4 را بفشارید تا فرمول بصورت

=(۱+B$1)^$A2

تغییر یابد و در پایان کلید ENTER را بفشارید. تا اینجای کار شکل زیر حاصل شده است.

حالا یکبار روی مربع کوچک سلول B2 کلیک کرده و نگه داشته و تا سلول F2 میکشیم و کلیک را رها میکنیم.

و حالا در حالی که ۵ سلول B2 تا F2 با هم انتخاب شدهاند، روی مربع سلول F2 کلیک کرده و نگه میداریم و تا سطر ۱۱ میکشیم و رها میکنیم. در پایان شکل حاصل بصورت زیر است.

مثال برای فرمول نویسی مقدماتی Excel

فروش فصلی یک شرکت برای سه سال متوالی به شرح زیر است.

در ادامه آموزش فرمول نویسی در اکسل میخواهیم جمع فروش سالانه را در سطر ۶ و میانگین فروش هر فصل را در ستون E محاسبه کنیم. برای جمع فروش هر سال ابتدا برای سال ۱۳۸۹ روی سلول B6 کلیک میکنیم و فرمول B2+B3+B4+B5= را مینویسیم و کلید ENTER را میفشاریم.

حالا برای محاسبه جمع فروش دو سال دیگر کافیست روی مربع کوچک گوشه سلول B6 کلیک کنیم و نگهداریم و تا سلول D6 بکشیم و کلیک را رها کنیم. اعداد حاصل بصورت زیر نمایش داده میشوند.

برای محاسبه میانگین فروش فصل بهار در سه سال گذشته در سلول E2 فرمول (B1+C1+D1)/3)= را مینویسیم و کلید ENTER را میفشاریم.

حالا با کلیک بر مربع کوچک گوشه سلول E2 و نگهداشتن آن و کشیدن تا سلول E5 میانگین فروش سایر فصول نیز محاسبه خواهد شد.

فرمول موجود در این سلولها بصورت زیر است:

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

جدول استهلاک یک دارایی به روش خط مستقیم با بهای تمام شده ۵۰۰۰۰۰۰ ریال و عمر مفید ۴ سال با ارزش اسقاط ۲۰۰۰۰۰۰ ریال را تشکیل دهید. دقت کنید جدول حاصل بصورت زیر خواهد بود.

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

حالا در سلول B6 استهلاک سال اول را از فرمول زیر محاسبه میکنیم:

در ادامه آموزش فرمول نویسی در اکسل بنابراین فرمول محاسبه استهلاک سال اول بصورت  ۴/(۲۰۰۰۰۰۰۰-۵۰۰۰۰۰۰)= که اگر به جای عددها آدرس سلول آنها را قرار دهیم (فرمول را بصورت نسبی بنویسیم)، فرمول این سلول بصورت B1-B3)/B2)= خواهد بود.

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

ابتدا علامت “=” را قرار میدهیم و سپس یک پرانتز باز کرده و روی سلول B1 کلیک میکنیم، در این لحظه برای ثابت کردن این سلول در فرمول یک بار کلید F4 را میفشاریم.

حالا علامت “-” را قرار داده و روی سلول B3 کلیک کرده و سپس کلید F4 را برای ثابت شدن آن میفشاریم و پرانتز را میبندیم.

بعد از پرانتز هم علامت کسر “/” را قرار داده و روی سلول B2 کلیک کرده و کلید F4 و در پایان کلید ENTER را میفشاریم.

تا اینجا تنها استهلاک سال اول را محاسبه کردهایم. برای کپی این فرمول به سالها بعد کافیست روی مربع کوچک کادر سلول B6 دبل کلیک کنیم.

برای محاسبه استهلاک انباشته دارایی در سلول C6 چون استهلاک انباشته سال اول برابر با هزینه استهلاک همین سال است فرمول B6= را قرار داده و کلید ENTER را میفشاریم.

برای استهلاک انباشته سالهای بعد در سلول C7 فرمول C6+B7= را نوشته و کلید ENTER را میفشاریم .فرمول محاسبه استهلاک انباشته هر سال برابر است با هزینه استهلاک همان سال بعلاوه استهلاک انباشته سالهای قبل. بنابراین در سلول C7 هزینه استهلاک سال دوم (B7) و استهلاک انباشته سالهای قبل (C6) را با هم جمع کرده ایم.

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

برای نمونه فرمول موجود در سلول C9 بصورت C8+B9= است که به معنای جمع هزینه استهلاک سال چهارم (B9) و استهلاک انباشته سه سال گذشته(C8) می‌باشد.

حال برای محاسبه ارزش دفتری هر سال در سول D6 فرمول

=$B$1-C6

را مینویسیم و کلید ENTER را می‌فشاریم. ارزش دفتری هر سال برابر است با بهای تمام شده دارایی(B1، چون در همه سالها باید ثابت باشد روی آن کلید F4 را فشردهایم و بصورت $B$1 تغییر پیدا کرده است) منهای استهلاک انباشته همان سال (برای سال اول سلول C6 و چون هر سال باید تغییر کند آن را ثابت نمیکنیم.)

حالا با یک دبل کلیک روی مربع کوچک سلول C6 جدول ما کامل میشود.

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

میخواهیم جدول ارزش مرکب برای یک ریال با نرخهای بهره ۱ تا ۱۰ درصد و در دورهای ۱ تا ۱۵ سال را تشکیل دهیم. برای این کار ابتدا عناوین سطر و ستون جدول را بصورت زیر در اکسل تشکیل میدهیم.

حالا در سلول B2 برای محاسبه ارزش مرکب سال اول و با نرخ بهره ۱ درصد در سال، باید فرمول

=(۱+B1)^A2

را بنویسیم. اما میخواهیم این فرمول را برای همه سلولهای این جدول کپی کنیم. برای اینکه در هنگام جابجایی فرمول آدرس سلولها بهم نریزد باید دو سلول A2 و B1 را ثابت کرد. اما این بار بجای اینکه یک بار کلید F4 را روی هر کدام از آنها فشار دهیم، برای سلول B1 دوبار کلید F4 را فشار دهید تا آدرس این سلول بصورت B$1 تغییر یابد. این کار باعث میشود در هنگام کپی فرمول به پایین برای دورههای دیگر، همواره این قسمت سطر یک را نشان دهد و همچنین در هنگام کپی فرمول به سمت راست برای نرخهای بهره دیگر، ستونها تغییر کند.

برای سلول A2 سه بار کلید F4 را بفشارید تا به شکل A2$ تغییر یابد. این کار باعث میشود تا در هنگام کپی فرمول به سمت راست، همواره فرمول در قسمت توان در ستون A ثابت باشد و در هنگام کپی به پایین، بدلیل ثابت نبودن عدد این سلول، اعداد توان تغییر کند.

در پایان کلید ENTER را بفشارید. حالا در سلول B2 ارزش مرکب یک ریال در یک سال بعد با نرخ بهره ۱ درصد در سال نمایش داده شده است.

حالا برای محاسبه سایر سلولها، روی مربع سلول B2 کلیک کرده و نگه میداریم و تا سلول K2 میکشیم و رها میکنیم.

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

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

در یک شرکت تولیدی، در دایره مشترک آن که دایره اول تولید است، سه محصول اصلی با نامهای الف، ب و ج تولید میشود. تعداد تولید هر محصول بترتیب ۲۵۰۰، ۳۵۰۰ و ۴۵۰۰ واحد و هزینه این دایره مشترک ۱۲۰۰۰۰۰۰۰ ریال است. سایر اطلاعات بشرح زیر در اکسل موجود است.

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

=B2/($B$2+$B$3+$B$4)

ENTER را نوشته و کلید را میفشاریم.

با دبل کلیک بر مربع توپر سلول D2 نسبت تولید برای دو محصول دیگر نیز محاسبه میشود.

حالا در ستون “سهم از هزینه مشترک” باید برای هر محصول عدد ۱۲۰۰۰۰۰۰ را در نسبت تولید هر محصول ضرب کنیم. برای این کار در سلول E2 فرمول =۱۲۰۰۰۰۰۰*D2 را نوشته و کلید ENTER را میفشاریم. سپس با دبل کلیک بر مربع توپر گوشه سلول E2 سهم هر محصول از هزینه مشترک محاسبه میشود.

برای محاسبه بهای تمام شده هر محصول باید “مبلغ پرداخت اضافی” و “سهم از هزینه مشترک” هر محصول را جمع کرده و در ستون F وارد کنیم.

برای محاسبه بهای تمام شده هر واحد کافیست بهای تمام شده کل محصول را بر تعداد تولید آن تقسیم کرد. برای این کار در سلول G2 فرمول F2/B2= را نوشته و ENTER را میفشاریم. سپس با دبل کلیک بر مربع توپر سلول G2 بهای تمام شده همه محصولات محاسبه میشود.

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

بخش FORMULA AUDITING

این بخش برای ابزارهایی را برای مشاهده فرمول ها در اختیار ما قرار می دهد. فرض کنید فرمول محاسبه مبلغ قسط وام و اصل و فرع قسط اول را بصورت زیر در اکسل داشته باشید.

برای اینکه اکسل به ما نشان دهد در فرمول سلول E1 از چه سلولهای دیگری استفاده شده، گزینه TRACE PRECEDENTS() را انتخاب می کنیم.

مشاهده میشود که برای محاسبه مبلغ هر قسط در سلول E1 از سلولهای B2 ،B1 و B3 استفاده شده است.
برای پاک کردن این خطوط از گزینه REMOVE ARROWS( ) استفاده میکنیم.

حال برای اینکه ببینیم سلول E3 در کدام سلولها بکار رفته است، ابتدا روی آن کلیک میکنیم و سپس گزینه TRACE DEPENDENTS()استفاده می کنیم.

نمایش فرمول ها در سلول در اکسل ک ابزار SHOW FORMULAS

این ابزار برای نمایش فرمول درون سلول ها کاربرد دارد. فرض کنید در مثال قبل میخواهیم فرمول درون سلول ها نمایش داده شود. برای این کار روی گزینه SHOW FORMULAS () کلیک می کنیم

برای لغو این حالت کافیست دوباره روی همین گزینه کلیک کنیم.

مثال جامع

شرکت گلبرگ قصد سرمایه گذاری در یکی از سه پروژه زیر را دارد. نرخ بازده مورد انتظار شرکت برای این سرمایهگذاریها ۱۲ درصد است. سرمایهگذاری اولیه و عواید پنج ساله این سه پروژه در محیط اکسل همانند زیر در دسترس است.

میخواهیم در سلول های C10 ،B10 و D10 نرخ بازده داخلی این سه پروژه را محاسبه کنیم. برای این کار در سلول B11 تابع IRR را فرامیخوانیم و در کادر VALUES، محدوده اطلاعات سرمایهگذاری پروژه الف (B4:B9) را با ماوس انتخاب و روی OK کلیک میکنیم.

با کلیک بر گزینه OK نرخ بازده داخلی پروژه الف در سلول B11 نمایش داده میشود.

حالا اگر با ماوس روی مربع توپر این سلول کلیک کنیم و نگهداریم و تا سلول D11 بکشیم و رها کنیم، نرخ بازده داخلی سایر پروژهها نیز محاسبه میشود.

حالا میخواهیم در زیر سلول نرخ بازده داخلی، در مقابل سلول وضعیت سوددهی شرطی بنویسیم که در صورتی که نرخ بازده داخلی کمتر از نرخ بازده مورد انتظار مدیریت(B1) باشد، عبارت “سودده”، در صورتی که نرخ بازده داخلی بیشتر از نرخ بازده مورد انتظار مدیریت باشد عبارت “زیانده” و در صورتی که نرخ بازده داخلی دقیقا برابر با نرخ بازده مورد انتظار مدیریت باشد، عبارت “بیتفاوت” را نمایش دهد.

برای این کار در سلول B12 تابع IF را فرامیخوانیم. در کادر LOGICAL_TEST باید قسمت اول شرط را بنویسیم (اگر سلول B11 کوچکتر از B1 باشد). این شرط در اکسل بصورت B11<B1 نوشته میشود. اما چون قصد داریم فرمول این سلول را به سلولهای کناری برای سایر پروژهها منتقل کنیم و سلول نرخ بازده مدیریت در هر پروژه یکسان است، سلول B1 را با فشردن کلید F4 ثابت میکنیم.

در کادر صحت شرط عبارت “سودده” را مینویسیم.

در کادر عدم صحت شرط(VALUE_IF_FALSE) چون دو حالت دیگر از شرط باقی مانده است، یک تابع IF جدید باز میکنیم.

در تابع جدید IF در قسمت شرط، حالت دوم را مینویسیم. اگر نرخ بازده داخلی از نرخ بازده مورد انتظار مدیدیت بزرگتر بود را بصورت B11>B1 و چون B1 باید ثابت باشد، روی آن کلید F4 را میفشاریم.

در کادر صحت شرط عبارت “زیانده” و در کادر عدم صحت شرط، حالت آخر یعنی “بیتفاوت” را وارد و روی گزینه OK کلیک میکنیم.

فرمول موجود در سلول B12 بصورت زیر است.

=IF(B11<$B$1,”سود ده”,IF(B11>$B$1,”زیان ده”۰″بی تفاوت”))

حالا اگر روی مربع توپر کادر سلول B12 کلیک کرده و نگهداریم و تا سلول D12 بکشیم و رها کنیم ،این شرط برای سایر پروژهها هم نمایش داده میشود.

حال میخواهیم در سلول B14 نرخ بازده داخلی پروژه منتخب نمایش داده شود. برای این کار باید پروژه دارای کمترین نرخ بازده داخلی از میان پروژههای سودده انتخاب شود. برای این کار ابتدا سلول B14 را فعال کرده و تابع IF را فرامیخوانیم. در کادر LOGICAL_TEST میخواهیم بررسی کنیم که آیا در کل پروژهها، پروژه سود دهی وجود دارد یا نه. برای این کار در این کادر تابع OR را فرامیخوانیم(این تابع بررسی میکند که حداقل یکی از شروط وارده صحت دارند یا نه). در کادر پنجره OR همانند تصویر زیر شروط را وارد میکنیم.

در تابع OR در حال بررسی وجود حداقل یک گزینه سودده در میان پروژهها هستیم. حال قبل از کلیک بر OK در نوار فرمول روی تابع IF کلیک میکنیم تا به تابع IF بازگردیم.

حالا در ادامه تابع IF در کادر VALUE_IF_TRUE برای پیدا کردن کمترین نرخ بازده داخلی، تابع MIN را فرامیخوانیم. در کادر NUMBER1 این تابع منطقه هر سه نرخ بازده داخلی(B11 تا D11) را با ماوس انتخاب میکنیم.

حالا قبل از کلیک بر گزینه OK روی نوار فرمول روی تابع IF کلیک میکنیم تا دوباره به تابع IF بازگردیم.

حالا در کادر VALUE_IF_FALSE باید عبارتی را که در هنگام زیانده بودن همه پروژهها نمایش داده شود را بنویسیم. در این کادر عبارت “همه پروژها زیانده هستند” را مینویسیم و روی OK کلیک میکنیم.

فرمول موجود در سلول B14 بصورت زیر است.

=IF(OR(B12=”سود ده”,C12=”سود ده”,D12=”سود ده”),MIN(B11:D11),”همه پروژها زیانده هستند”)

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

در ادامه آموزش فرمول نویسی در اکسل گاهی اوقات میخواهیم اطلاعات و دادهها در اکسل در یک کاربرگ و تجزیه تحلیلها، محاسبات و فرمولها در کاربرگی دیگر وارد شوند. فرض کنید مبلغ ۵۰۰۰۰۰۰ ریال را در حسابی در بانک با نرخ بهره سالانه ۷ درصد سپردهگذاری کردهاید. میخواهید بدانید در پایان دوره ۴ ساله سرمایه گذاری موجودی حساب شما با احتساب بهره متعلقه به آن، چه مقدار خواهد بود. اطلاعات سوال را در کاربرگ اول (SHEET1) بصورت زیر وارد کرده ایم.

حالا میخواهیم فرمول محاسبه ارزش آتی این سرمایه گذاری در سلول A1 در SHEET2 وارد شود و نتیجه در کاربرگ دوم نمایش داده شود. برای این کار ابتدا به کاربرگ دوم میرویم و روی سلول A1 کلیک میکنیم تا فعال شود. سپس علامت “=” را قرار میدهیم. حالا برای کلیک بر سلول حاوی مبلغ وام (سلول B1 در کاربرگ اول) ابتدا روی نام کاربرگ اول در پایین صفحه کلیک میکنیم تا به کاربرگ اول منتقل شویم، سپس روی سلول B1 کلیک میکنیم.

مشاهده میکنید نام سلول B1 در کاربرگ اول بصورت SHEET1!B1 نمایش داده شده است. حالا ادامه فرمول را در نوار فرمول مینویسیم. فرمول نهایی بصورت زیر است.

=Sheet1!B1*(1+Sheet1!B2)^Sheet1!B3

اگر این فرمول را در کاربرگ اول مینوشتیم بصورت B1*(1+B2)^B3= نمایش داده میشد.

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

این تابع معکوس یک ارزش را باز می گرداند و خروجی آن به صورت True و False است.

توابع TRUE و FALSE به ندرت استفاده می شوند و مایکروسافت برای سازگاری بیشتر با سایر نرم افزارها آنها را اضافه کرده است.

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

این تابع تعداد مشخصی از کاراکترهای یک رشته متنی را از سمت چپ آن جدا می‌کند.

آموزش مرتبط : آموزش تابع AND و OR در اکسل

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

این تابع تعداد مشخصی از کاراکترهای یک رشته متنی را از سمت راست آن جدا می‌کند.

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

این تابع تعداد مشخصی از کاراکترهای یک رشته متنی را از هر جای دلخواه از متن جدا می‌کند.
Start-num: از کدام کاراکتر شروع شود (در اینجا کاراکتر ۴ می‌شود).
Num-chars: چند کاراکتر جدا کند (در اینجا ۲ کاراکتر می‌شود).

جدا کردن تاریخ در اکسل(توابع LEFT-RIGHT-MID)

فرمولی در سلولهای B3 تا B6 بنویسید که قسمتهای مختلف تاریخ وارد شده در سلول B2 را از هم جدا کند.

آموزش تابع LEN در EXCEL

این تابع فقط یک آرگومان دارد و تعداد کاراکترهای یک رشته متنی را می‌شمارد.

(Len مخفف length است)

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

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

این تابع قسمتی از یک رشته متنی را با یک متن جدید جایگزین می‌کند
old-text: متن قدیمی
stars-num: (کاراکتر شروع) باید به صورت عدد باشد.
num-chars: (تعداد کاراکتر) باید به صورت عدد باشد.
new-text: (متن جدید) باید داخل دابل کوتیشن باشد.

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

این تابع نیز مانند Replace قسمتی از یک رشته متنی را با یک متن جدید جایگزین می‌کند.
Text: متن اولیه
Old-text: متنی که می‌خواهیم عوض شود.
New-text: متن جدید
Instance-num: چندمین کاراکتر تکراری (این آرگومان اختیاری است)

نکته مهم: تابع SUBSTITUTE به حروف بزرگ و کوچک حساس می‌باشد.

شماره تلفن بدون خط تیره در اکسل(توابع RIGHT – SUBSTITUTE)

با توجه به شکل زیر در سلول D2 فرمولی بنویسید که اولاً صفر اول ثانیاً دو خط تیره وسط شماره تلفن نوشته شده را در سلول C2 را حذف نماید.

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

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

نکته ۱: توجه داشته باشید چون کاراکتر ( / )برای اکسل ناشناخته است باید داخل کوتیشن گذاشته شود. “/”
نکته ۲: به جای تابع CONCATENATE میتوانید از عملگر (& +Shift) نیز استفاده کنید.

A2&B2&C2=941023=

A2&”/”&B2&”/”&C2=94/10/23=

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

این تابع یک متن را به تعداد مشخص تکرار می‌کند.
Text: متن مورد نظر

Number-times: تعداد دفعات تکرار
این تابع مخفف Repeat است.

نکته: آرگومان اول که text است باید متن مورد داخل دابل کوتیشن ” ” باشد.

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

• حرف n در فونت windings به صورت مربع است
• حرف O بزرگ در فونت windings2 به صورت ضربدر است x
• حرف P بزرگ در فونتwindings2  به صورت تیک است ✔

ستاره گذاری بعد از عدد – ۱۲ رقم(توابع LEFT- RIGHT-REPT و عملگر &)

فرض کنید یک عدد میتواند حداکثر ۱۲ رقم داشته باشد فرمول سلول B2 را بگونه ای بنویسید که اکسل بعد از عدد مورد نظر، ستاره بگذارد. مثلا سلول A2 حاوى عدد ۱۲۰۰۰۰ است که این عدد ۶ رقم دارد. میخواهیم فرمولی بنویسیم که بعد از این عدد تا رسیدن به ۱۲ رقم، تعدادی ستاره بگذارد. ( مثلا ******۱۲۰۰۰۰ که این عدد با تعداد ستاره هایش ۱۲ رقم است.)

ترسیم نمودار درون سلولی با استفاده از تابع REPT

برای داده های زیر در ستون Cنمودار درون سلولی ترسیم کنید.

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

REPT(“|”,B2)

سپس محدوده C2:C13 را انتخاب نموده و برای این محدوده فونت Playbiil را انتخاب کنید.

آموزش مرتبط : آموزش ابزار CONDITIONAL FORMATTING

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

این تابع یک مقدار عددی را می‌گیرد و به نوع متنی با فرمت دلخواه تبدیل می‌کند.
Value: این آرگومان عددی است که به صورت متن تبدیل می‌شود.
Format-text:فرمت خروجی تابع را نشان می‌دهد.

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

تابع FIND یک رشته متنی را درون یک رشته متنی دیگر جستجو می‌کند و عدد موقعیت متن در حال جستجو (متنی که در آرگومان اول نوشته می‌شود) را از اولین کاراکتر برمی‌گرداند.
Find-text: متن یا کاراکتری است که آن را جستجو می‌کنیم.
Within-text: متنی است که آرگومان اول را در آن جستجو می‌کنیم.
Start-num: عددی است که مشخص میکند جستجو از چندمین کاراکتر شروع شود.

نکته مهم: این تابع نسبت به حروف بزرگ و کوچک حساس است.

سوال: اگر کاراکتر مورد نظر تکراری بود خروجی تابع FIND چگونه خواهد بود؟

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

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

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

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

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

یکسان بودن یا نبودن دو متن را چک می کند. اگر دو متن یکسان باشد جواب True و اگر یکسان نباشد False ظاهر می شود.

نکته ۱: تابع EXACT نسبت به حروف کوچک و بزرگ حساس است.

نکته ۲: بجای این تابع میتوان از دو مساوی (= ) نیز استفاده کرد.

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

این تابع در صورتی که مقدار موجود در یک سلول بصورت متن (text) بود، همان مقدار را بر می گرداند و در غیر این صورت اگر عدد یا علائم دیگر بود مقدار خالی را نشان می دهد.

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

آموش تابع SEARCH اکسل

این تابع نیز مانند تابع FIND میباشد با این تفاوت که این تابع نسبت به بزرگ یا کوچک بودن حروف حساس نمی باشد.

آموزش مرتبط : گروه بندی داده‌ها (GROUP) در اکسل (تصویری + مثال واقعی)

آموش اکسل CODE در Excel

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

نکته ۱: خروجی این تابع عددی بین ۱ تا ۲۵۵ میباشد.
نکته ۲: اگر متنی که به این تابع میدهیم بیش از یک کاراکتر باشد این تابع کد اولین کاراکتر را باز می گرداند.

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

این تابع عکس تابع CODE عمل می کند. آرگومان آن عددی بین ۱ تا ۲۵۵ است، و خروجی این تابع، کاراکتر متناظر با عدد وارد شده است.

مثال ۱ برای تابع CHAR در اکسل

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

نکته ۱: خروجی تابع (۱۰)Char در مثال فوق ایجاد یک خط جدید در درون سلول است به عبارتی همان کار Alt+Enter را انجام می دهد.

نکته ۲: سلول D2 که در آن فرمول نوشته شده است حتما باید در حالت Wrap شده باشد.

مثال ۲ برای تابع CHAR در اکسل

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

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

این تابع نیز مانند تابع CHAR عمل می کند. با این تفاوت که دامنه اعداد آرگومان آن بین ۱ تا بیش از ۱۰۰۰۰۰ باشد.

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

این تابع عکس تابع UNICHAR عمل می کند.

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

این تابع کلیه کاراکترهای غیرقابل چاپ را از متن مورد نظر حذف می کند. منظور از کاراکترهای غیرقابل، چاپ ۳۲ کد اول کدهای اسکی است که در قسمت تابع CODE ذکر شده است.

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

این تابع یک آرگومان دارد و حرف آغازین هر کلمه را به حرف بزرگ تبدیل می کند.

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

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

Number: عدد مورد نظر که میخواهیم آن را گرد کنیم.

Decimals: این آرگومان مشخص کننده تعداد ارقام اعشاری می باشد.

no_commas: این آرگومان مقدار منطقی TRUE یا FALSE را می پذیرد. در صورت FALSE بودن اعداد صحیح را سه رقم، سه رقم با کاما (,) جدا می کند در صورت TRUE بودن از گذاشتن کاما (,) به عنوان جدا کننده صرف نظر می کند.

نکته در صورتی که مقدار Decimals منفی باشد قسمت صحیح عدد وارد شده به مضربی از توان ۱۰ گرد می شود.

آموزش مرتبط : آموزش تابع HYPERLINK(هایپرلینک)

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

یک عدد را به نوع متنی با فرمت واحد پولی تبدیل می کند. به عبارتی، این تابع یک عدد را به فرمت متنی تبدیل نموده و علامت واحد پولی (ریال) یا ($) را به آن اضافه می کند.

Number: این آرگومان می تواند یک عدد یا ارجاع به سلولی که حاوی عدد است باشد.

decimal: این آرگومان اختیاری است و تعداد ارقام اعشاری را مشخص می کند.

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

این تابع رشته های متنی را به عدد تبدیل می کند.

text: عدد در حالت متنی

decimal_separator: جداکننده ارقام اعشاری از غیر اعشاری

group_separator: جداکننده ارقام هزارگان

توجه داشته باشید که در برخی فرمت های اروپایی جای جداکننده ارقام اعشاری با جداکننده ارقام هزارگان جابجا است. مانند سلول A3 و A4

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

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

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

این تابع عکس تابع match عمل میکند یعنی از یک بازه یا جدول در صورتی که شماره سطر یا ستون آن را بدهیم می تواند محتویات سلول طبقه شغلی
مربوطه را فراخوانی کند
الف) حالت آرایه ای
Array: سطر یا ستون یا جدول اطلاعات
Row-num شماره سطر Column-num شماره ستون

دقت کنید بحث index 3 آرگومانه آرایه ای همان بحث ماتریس هاست بعبارتی شما ابتدا ماتریس مورد نظر را در ناحیه سلولهای خالی ایجاد می کنید در نهایت وظیفه تابع index پرکردن محتویات این ماتریس است مثلا در قسمت سطری آن ابتدا یک ماتریس ۵ × ۱ در ناحیه خالی از سلول ها انتخاب می کنیم سپس فرمول index را تایپ میکنیم قبل از زدن Enter به این نکته توجه کنید که در قسمت آرایه ای به جای Enter ترکیب Ctrl+Shift+Enter را بزنید.

ب) حالت رفرنسی Reference یک یا چند جدول
Row-num شماره سطر Column-num شماره ستون
Area-num: شماره جدول مورد نظر

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

این تابع مقدار یک سلول را از یک سطر یا ستون میگردد و میگوید که این مقدر چندمین مقدار این سطر یا ستون است. Lookup-value: مقدار یا ارزشی که قرار است جستجو شود.

Lookup-array: یک سطر یا ستون از سلولها
Match-type: این آرگومان یکی از اعداد ۱ را میپذیرد اگر مقدار این آرگومان ۱ باشد یا حذف شود تابع نزدیک ترین حد پایین را انتخاب میکند البته در صورتی که سطر یا ستون مورد نظر صعودی مرتب شده باشد. اگر مقدار این آرگومان صفر شود، تابع دقیقاً عبارت مورد نظر را جستجو میکند در اینجا صعودی یا نزولی بودن مهم نیست اگر مقدار این آرگومان ۱- شود تابع نزدیک ترین حد بالا را انتخاب میکند البته در صورتی که سطر یا ستون مورد نظر نزولی مرتب شده باشد.

MATCH( 18; A1:A5; +1)= 1

یعنی مقدار ۱۸ را از بازه A1:A5 بگرد و چون ترتیب بازه ۱ تا A5 صعودی است و مقدار ۱۸ در این بازه یافت نمیشود اکسل نزدیک ترین حد پایین را انتخاب میکند (۱۷) و میگوید که این عدد (۱۷) چندمین عدد این بازه است.

MATCH(19; A1:A5; 0) =2

MATCH( 22; A1:A5; -1)=#N/A

چون مقدار ۲۲ در این بازه وجود ندارد و ترتیب آن نیز نزولی نیست بنابراین اکسل خطای N/A# را برمیگرداند.

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

از تابع RANK برای مشخص کردن رتبه اعداد استفاده می شود.

ساختار تابع RANK:

آرگومان اول (number): در این قسمت عددی که می خواهیم رتبه آن را تعیین کنیم، معرفی می کنیم.

آرگومان دوم (ref): در این قسمت محدوده اعداد را باید وارد کنیم.

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

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

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

Reference: ادرس یک سلول (سلول مرجع)
Rows: تعداد سطرهایی که با توجه به سلول مرجع تغییر میکند.
Cols: تعداد ستونهایی که با توجه به سلول مرجع تغییر میکند.
Height: تعداد سطرهای ماتریس خروجی
Width: تعداد ستونهای ماتریس خروجی

نکته ۱: در آرگومان دوم  (rows) عدد مثبت جهت حرکت رو به پایین و عدد منفی جهت حرکت رو به بالا است. در آرگومان سوم (cols) نیز عدد مثبت یعنی حرکت رو به جلو و عدد منفی یعنی حرکت رو به عقب است.

آموزش تابع (INDIRECT) در اکسل

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

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

این تابع از بین یک سری اعداد، kامین عدد بزرگ را نشان می دهد.

Array: یک بازه از اعداد
k: رتبه عدد

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

این تابع نیز مانند تابع LARGE است با این تفاوس که k امین عدد کوچک را نشان میدهد.

آموزش تابع سابتوتال (SUBTOTAL) در اکسل

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

ساختار تابع Subtotal:

آرگومان اول ← function_num، در این تابع برای هر کدام از عملیات ها یک شماره در نظر گرفته شده است و در آرگومان اول باید شماره تابع مورد نظر را وارد کنیم مثلا اگر بخواهیم عملیات جمع را انجام دهیم باید از شماره ۹ یا ۱۰۹ استفاده کنیم.
آرگومان دوم ← refl در این قسمت باید مشخص کنیم که عملیات جمع، میانگین… از کدام قسمت باید انجام شود.

سوال: چرا به عنوان مثال تابع ۱ و ۱۰۱ یک عمل را انجام می دهند؟ تفاوت این دو در چیست؟

فرض کنید بعضی از سطرها را Hide کرده باشید و بخواهید(نخواهید) آن ها را هنگام فیلتر کردن در محاسبه دخیل کنید. برای همین منظور دو کد قرار داده شده است. کد های ۱ تا ۱۱ برای دادههایی میباشد که اگر سطرهای آن توسط شما Hide شده باشند باز هم در محاسبات Subtotal در نظر گرفته خواهد شد.
کدهای ۱۰۱ تا ۱۱۱ برای داده هایی میباشد که اگر سطرهای آن Hide شده باشد در محاسبات Subtotal در نظر گرفته نمی شود.

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

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

آموزش تابع (COLUMN) در اکسل

این تابع شماره ستون مربوط به یک سلول را مشخص می کند.

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

این تابع میانه اعداد را حساب می کند.

آموزش مرتبط برای شما : آموزش اکسل مقدماتی تا پیشرفته کاملا رایگان

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

این تابع عددی که بیشترین تکرار یا فراوانی را داشته باشد نشان می دهد. در صورتی که هیچ عدد تکراری وجود نداشته باشد تابع خطای #N/A باز می گردد.

آموزش تابع RAND اکسل

این تابع یک عدد تصادفی بین صفر و یک تولید می کند و مخفف Random است.

نکته ۱: این تابع آرگومان ندارد.

نکته ۲: این تابع اعداد تصادفی را براساس توزیع یکنواخت تولید می کند.

نکته ۳: این تابع با هر بار اجرای اکسل یا فشردن دکمه F9 عدد تصادفی جدید تولید می کند.

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

این تابع عملیات گرد کردن را به نزدیکترین مضرب آرگومان دوم روبه بالا انجام میدهد.
این تابع به معنی سقف است.
Number: عدد موردنظر
Signification: عددی که قرار است مضرب آن حساب شود.

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

این تابع عملیاس گرد کردن را به نزدیکترین مضرب آرگومان دوم رو به پایین انجام می دهد. این تابع به معنای کف است و عکس تابع Ceiling عمل می کند.
Number: عدد موردنظر
Signification: عددی که قرار است مضرب آن حساب شود

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

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

Index-num: در این آرگومان باید یک عدد بنویسید. این عدد بیانگر فرمولی خاص از یک لیست است.

Value1، فرمول ۱

Value2، فرمول ۲

تابع COUNT در نرم افزار اکسل

با کمک این تابع میتوان تعداد سلولهای دارای عدد (عددی) در یک منطقه از سلول ها را شمرد. فرض کنید در اکسل همانند تصویر زیر سلولهایی حاوی متن و عدد وجود دارد.

میخواهیم در سلول A6 تعداد سلولهای حاوی عدد در این محدوده را شمارش کنیم. برای این کار در این سلول تابع COUNT را از بخش توابع آماری( STATISTICAL) فرامیخوانیم. در کادر VALUE1 محدوده مورد نظر(A1:C4) را با ماوس انتخاب (و یا مینویسیم) و در پایان روی OK کلیک میکنیم.

پس از کلیک روی OK خواهید دید در این محدوده ۸ سلول حاوی عدد وجود دارد. همچنین تابع COUNTA تعداد سلولهای غیر خالی را میشمارد.

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

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

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

این تابع تعداد سلول هایی که از یک شرط خاص پیروی می کنند می شمارد.

تفاوتی که این تابع با تابع COUNT دارد این است که این تابع میتواند عمل شمارش را با شرطی که شما به آن میدهید انجام دهد. این تابع دارای دو کادر ورودی اطلاعات به نام RANGE(آدرس منطقه مورد نظر برای شمارش) و CRITERIA (شرط شمارش) است. برای مثال اگر بخواهید در یک منطقه از سلول ها اعداد بزرگتر از ۵ را بشمارید، باید در کادر CRITERIA عبارت “۵<” را وارد کنید.

مثال برای تابع COUNTIF

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

میخواهیم تعداد ماههایی را که فروش بیشتر از ۶۱ ریال بود را بیابیم. برای این کار تابع COUNTIF را فرامیخوانیم و ورودیهای آن را بصورت زیر وارد میکنیم.

مشاهده میکنید فروش ۶ ماه بیش از ۶۰ ریال است. حال برای شمارش تعداد ماههایی که فروش ۶۰ ریال و بیشتر از ۶۰ ریال بود، در کادر CRITERIA این تابع عبارت “۰۶=<” را قرار میدهیم. همچنین تابع COUNTIFS برای شمارش سلولها با چند شرط بکار میرود.

مطلب مفید برای شما : آموزش و لیست توابع مالی در اکسل + مثال و تمرین

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

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

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

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

Range: بازه یا ناحیهای که قرار است شرط در آنجستجو شود.
Criteria: معیار یا شرط
Sum-range: بازه یا ناحیه ای که عدد در آن وجود دارد و قرار است جمع شود.

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

این تابع عمل جمع کردن را براساس چند شرط انجام میدهد. Sum_range بازه یا ناحیه ای که عدد در آن وجود دارد و قرار است جمع شود. criteria_range1: بازه یا ناحیه ای که قرار است شرط ۱ در آن جستجو شود.
creterial: معیار یا شرط ۱
creteria2: معیار یا شرط ۲
criteria_range2 بازه یا ناحیه ای که قرار است شرط ۲ در آن جستجو شود.

مثال برای تابع SUMIF : جمع براساس یک شرط

در سلول F2 فرمولی بنویسید که جمع محصول وارد شده در سلول F1 را محاسبه کند:

مثال برای تابع SUMIFS : جمع براساس دو شرط 

مثال برای تابع SUMIFS : محاسبه فروش ۳۱روز گذشته

در سلول G5 فرمولی بنویسید که فروش IDهای مختلف را در ۳۰روز گذشته محاسبه کند.
فرمول زیر را در سلول G5 نوشته و رو به پایین کپی نمایید.

=SUMIFS($D$5:$D$104,$C$5:$C$104,”>=”&TODAY()-30,$B$5:$B$104,F5)

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

 

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

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

این تابع میانگین اعداد را محاسبه میکند با این تفاوت که جمع اعداد را بر تعداد سلول های حاوی عدد و متن (بعبارتی سلول پر) تقسیم میکند.

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

این تابع فاصله های خالی (space) را از یک متن مورد نظر به جز فاصله بین کلمات یک عبارت حذف می کند از این تابع بیشتر برای ویرایش متن‌هایی که از سایر برنامه‌ها وارد اکسل میشود استفاده می‌شود.

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

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

نکته: آرگومان این تابع باید بصورت عدد تاریخ یا زمان باشد که قابل تبدیل به عدد هستند در غیر اینصورت خطای (Value) ظاهر می‌شود.

تابع ROUND در اکسل

این تابع برای رند کردن اعداد کاربرد دارد .برای مثال فرض کنید عدد ۵۵۳۴.۶۵۲ را داریم. می خواهیم این عدد را تا دو رقم اعشار رند کنیم. برای این کار تابع ROUND را فرامیخوانیم و در کادر NUMBER این عدد را می نویسیم. در کادر NUM_DIGITS، اگر عدد مثبت وارد شود، تعداد ارقام اعشار را مشخص می کند و اگر عدد منفی نوشته شود اعداد قبل از اعشار را رند میکند (عدد ۱- آخرین رقم قبل از اعشار عدد را صفر میکند، عدد ۲- دو عدد آخر عدد را صفر میکند و.)…
در این مثال برای رند کردن عدد به دو رقم اعشار، عدد ۲ را در این کادر وارد می‌کنیم.

عدد وارد شده پس از کلیک بر گزینه OK بصورت زیر نمایش داده میشود.

حال اگر در کادر NUM_DIGITS عدد ۲- قرار دهیم، عدد وارد شده بصورت زیر نمایش داده میشود.

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

پس از انجام محاسبات مربوطه، تعداد فروش در نقطه سربهسر یک شرکت در سلول A2 عدد ۲۱۵۰.۸۴ نمایش داده شده است. قیمت فروش هر محصول این شرکت ۵۰۰۰ ریال است. میخواهیم در سلول C2 مبلغ فروش در نقطه سربه سر را محاسبه کنیم.

چون تعداد ۲۱۵۰.۸۴ واحد غیر ممکن است و تعداد فروش محصول باید یک عدد صحیح باشد، تصمیم گرفته شده که این عدد به نزدیکترین عدد صحیح گرد شود .اگر روی سلول A2 کلیک کنیم و سپس از زبانه HOME در بخش NUMBER دوبار روی گزینه DECREASE DECIMAL کلیک کنیم، عدد موجود در سلول A2 به ۲۱۵۱ تغییر پیدا میکند.

حال اگر در سلول C2 فرمول محاسبه مبلغ فروش کل در نقطه سربهسر(A2*B2=) را بنویسیم، خواهیم دید که عدد ۱۰,۷۵۴,۲۰۰ نمایش داده شده است.

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

=۲۱۵۰.۸۴×۵۰۰۰=۱۰,۷۵۴,۲۰۰

مشاهده میکنید، با اینکه تعداد محصول در نقطه سربهسر ۲۱۵۱ نشان داده میشود، اما در واقع این عدد همان ۸۴/۲۱۵۱ میباشد که فقط ظاهر آن ۲۱۵۱ است. برای اینکه این عدد واقعا به ۲۱۵۱ تغییر پیدا کند و در محاسبات نیز ۲۱۵۱ حساب شود، باید از تابع ROUND استفاده کنیم. برای این کار دوباره به عقب بازمیگردیم.

حالا در سلول C2 تابع ROUND را فرامیخوانیم و در کادر NUMBER بجای نوشتن عدد، آدرس سلول A2 را قرار میدهیم و از آنجایی که این عدد باید عددی صحیح باشد، در کادر NUM_DIGITS عدد صفر )به معنی عدد بدون اعشار( را وارد و روی گزینه OK کلیک میکنیم.

حالا در سلول C2 بخش اول ضرب را خواهید داشت.

اما عدد بدست آمده در سلول C2 باید در مبلغ ۵۰۰۰ ریال ضرب شود. برای ادامه نوشته فرمول در سلول C2 ابتدا آن را فعال کرده و سپس در نوار فرمول با قرار دادن علامت ضرب “*” ادامه فرمول را مینویسیم.

حالا با فشردن کلید ENTER نتیجه را مشاهده می‌کنید.

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

این تابع یک عدد اعشاری را به بالا گرد میکند.

Number: عددی که میخواهیم گرد کنیم.
num_digits: تعداد ارقامیکه میخواهیم گرد کنیم.

آموزش تابع

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

این تابع یک عدداعشاری را به پایین گرد میکند.
Number: عددی که میخواهیم گرد کنیم.
num_digits: تعداد ارقامی که میخواهیم گرد کنیم.

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

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

اضافه کردن تعدادی صفر به ابتدای اعداد

فرض کنید یک عدد میتواند حداکثر ۱۰ رقم داشته باشد. فرمول سلول B2 را بگونه ای بنویسید که اکسل قبل از عدد مورد نظر، صفر بگذارد. مثلا سلول A2 حاوی عدد ۳ است که این عدد ۱ قم دارد می خواهیم فرمولی بنویسیم که قبل از این عدد تا رسیدن به ۱۰ رقم تعدادی صفر بگذارد. فرمول زیر را در سلول B2 بنویسید.

=CONCATENATE(REPT(0,10-LEN(A2)),A2)

۱. فرمول نویسی اکسل برای کارهای اداری : نمودار ۵ ستاره

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

فرمول زیر را در سلول C2 بنویسید و در سلول های پایین کپی نمایید:

=IF(B2=””,REPT($F$2,5),REPT($E$2,B2)&REPT($F$2,5-B2))

توجه: فونت محدوده C2:C14 را بعد از نوشتن فرمول بر روی Wingdings قرار دهید.

۲. فرمول نویسی اکسل برای کارهای اداری : گذاش‎تن خط تیره

در سلول C2 فرمولی بنویسید که برای عابر بانک وارد شده در سلول A2 را چهار رقم چهار رقم خط تیره بگذارد.

فرمول زیر را در سلول C2بنویسید.

=LEFT(A2,4)&”-“&MID(A2,5,4)&”-“&MID(A2,9,4)&”-“&RIGHT(A2,4)

۳. فرمول نویسی اکسل برای کارهای اداری : محاسبه جمع بین دو تاریخ

در سلول E4 فرمولی بنویسید که جمع فروش باین دو تاریخ را محاسابه کند.

محاسبه جمع سلول هایی که عنوان آن ها خالی است.

شمارش سلول هایی که بین دو تاریخ قرار دارند.

=COUNTIFS($B$2:$B$9,”>=”&DATE(D2,1,1),$B$2:$B$9,”<=”&DATE(D2,12,31))

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

=COUNTIFS(B:B,”>=500″,B:B,”<=800″)

شمارش تعداد سلول های بزرگتر از یک مقدار

=COUNTIF(B:B,”>900″)

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

=COUNNTIF(D4:D10, “<>Complete”)

شمارش تعداد سلول هایی که با عبارس خاصی شروع می شوند

=COUNTIF(B4:B11,”app*”)

شمارش براساس تعداد کاراکتر

COUNTIF(B4:B9, “?????”)=

۴. فرمول نویسی اکسل برای کارهای اداری : پیدا کردن نمره Brad در درس Brad

=VLOOKUP(“Brad”,$A$2:$E$10,2,0)

همان طور که در تصویر مقابل مشخص است تابع VLOOKUP اسم مورد نظر را از ستون اول جستجو کرده و نمره مربوط به آن شخص را از ستون دوم جدول (Math در ستون دوم جدول قرار دارد) به ما نشان می دهد.

=VLOOKUP(“Maria”,$A$2:$E$10,4,0)

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

=VLOOKUP(G4,$A$2:$E$10,MATCH(H3,$A$2:$E$2,0),0)

۶. فرمول نویسی اکسل برای کارهای اداری : جستجو براساس ۳ شرط در اکسل

فرمول زیر را در سلول H4 بنویسید:

=VLOOKUP(G4,CHOOSE(IF(H2=”UnitTest”,1,IF(H2=”Midterm”,2,3)),$A$2:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

کاربرد Wilcard ها

در جدول زیر داده های موجود در ستون C را می خواهیم از ستون A جستجو کنیم تا متن کامل آن را برای ما نمایش دهد.

=VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE)

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

جستجو براساس چند شرط

اکنون فرمول زیر را در سلول G3 نوشته و سپس در سلول های دیگر کپی نمایید.

=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0)

Wildcard در تابع وی لوک آپ

با توجه به شکل زیر فرمولی در سلول E2 بنویسید که با وارد کردن کد نماینده در سلول، D2 ضریب نماینده مربوطه را نشان دهد.

 

آموزش مرتبط : آموزش تابع وی لوکاپ(Vlookup) در اکسل

آموزش تابع (HLOOKUP) در اکسل

Hlookup یعنی Horizontal lookup (جستجوی افقی)!

ین تابع یک ارزش را از اولین سطر یک جدول جستجو می‌کند و از سطر دلخواه جواب مربوطه را برمی‌گرداند.
Lookup-value: مقدار یا شرطی که قرار است جستجو شود.
Table-array: جدولی که lookup-valueدر آن جستجو میشود.
Row-index-num: در این آرگومان باید عدد بنویسید. این عدد بیانگر شماره ساطری اسات کاه می‌خواهید جواب از این ساطر انتخاب شود.
Rang-lookup: این آرگومان نیز مانند vlookup دو گزینه دارد و بقیه قسمتهای آن مانند تابع vlookup است.

استفاده از تابع (Wildcard Character با COUNTIFS)

=COUNTIFS(B2:B10,”*”,D2:D10,”<>”&””))

شمارش سلول هایی که یک یک عبارس خاص را شامل می شوند.

=COUNTIF(C2:C10,”*REPORT*”)

پیدا کردن داده تکراری – حالت اول

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

پیدا کردن داده تکراری – حالت دوم

با توجه به شکل زیر در سلول B1 فرمولی بنویسید که اسامی تکراری را از ستون A مشخص کند تفاوت آن با سوال قبل این است که اسم اول که در ادمه تکرار شده است کاری نداریم.

پیدا کردن داده تکراری در اکسل حالت سوم

با توجه به شکل زیر فرمولی در جدول سمت راست (در سلول J2) بنویسید که با وارد کردن کد پرسنلی در سلول H2 اطلاعات
پرداختی به آن شخص به حالت های زیر ظاهر شود.
(الف) به ترتیب صعودی     ب) به ترتیب نزولی

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

نکته: این تابع فاصله یا فضای خالی قبل و بعد متن را حذف می کند و فاصله بین کلمات را به اندازه یک فضای خالی قرار می‌دهد

شاخص‌های منابع انسانی

در سلول H5 فرمولی بنویسید که شاخص‌های مورد نظر را از فصل مربوطه که در سمت راست جدول قرار دارد بخواند.

سپس فرمول نوشته شده را به کل ناحیه سلولهای H5 تا O8 کپی کنید.

 

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

با توجه به جدول زیر در سلول A2 فرمولی بنویسید که وقتی به سمت راست کپی میشود اسامی بصورت برعکس خودکار ظاهر شود.

تبدیل ستون به سطر در اکسل

فرمولی در سلول C1 بنویسید که وقتی به سمت سلول F1 کپی شود اسامی که در ستون A1 تا A4 هستند بصورت افقی مرتب شوند.

 

آموزش مرتبط : آموزش تبدیل سطر به ستون و برعکس(تابع TRANSPOSE)

آخرین عدد یک ستون در اکسل

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

 

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