فرمول نویسی در اکسل و نکات کاربردی برای مبتدیان
مقدمه ای بر فرمول نویسی در اکسل
فرمول نویسی در اکسل یکی از مباحثی هست که یادگیری آن بسیار ضروری و مهم می باشد.اکثر کسانی که با اکسل کار میکنند و نیاز به محاسبات هرچندساده دارند، حداقل چند تابع مهم آن را می شناسند.خیلی از افراد چه در سطح مقدماتی و چه در سطح پیشرفته استفاده های فراوانی از توابع میکنند اما موضوعی که اینجا مطرح هست آیا این افراد به راهنمای توابع و علائم آنها توجه می کنند..!؟ آیا آن چیزی را که تابع می گوید را می شنوند…!؟ یا صرفا طریقه استفاده از آن را حفظ کرده اند.
ما اینجا قصد داریم نکات فرمول نویسی با توابع را به شما بگوییم که بدون نیاز به هیچ منبع آموزشی بتوانید هرنوع تابعی را تحلیل کرده و نحوه استفاده از آن را بیابید.اما قبل از شروع بحث ناگفته های توابع بهتر است اول تعریف تابع را بدانیم.
تعریف تابع:
از دید ریاضیات تابع عملکردی است که به ازای هر ورودی داده شده فقط و فقط یک خروجی تولید کند و اگر دو خروجی تولید کند تابع محسوب نمی شود.برای مثال تابع را یک ماشین حساب درنظر بگیرید.حال شما تعداد زیادی عدد وارد می کنید و بعد از تعریف اینکه چه عملیاتی روی آن اعداد انجام دهد فقط و فقط یک خروجی به شما نشان می دهد.مثلا من اعداد ۲و۶ را به ماشین حساب می دهم و تعیین می کنم که بعد از جمع زدن این دو عدد حاصل آن در عدد ۲ ضرب کند که در نتیجه عدد ۱۶ به عنوان خروجی نهایی برای ما نشان داده می شود.
حال در اکسل ماشین حساب های از پیش تعریف شده ای وجود دارد که دیگر نیازی نیست برای هرنوع عملیات محاسباتی پروسه پیچیده ای را انجام دهید.کافیست اسم ماشین حساب از پیش تعریف شده توسط اکسل را فراخوانی کنید و مقادیر را به ماشین حساب معرفی کنید .ما به این ماشین حساب های از پیش تعریف شده که هرکدام محاسبات خاصی انجام میدهند را اصطلاحا “تابع” می گوییم.که در این مثال برای جمع اعداد می توانیم ماشین حساب یا بعبارتی تابع ()SUM را فراخوانی کنیم.
نکات فرمول نویسی در اکسل که باید بدانیم
۱) رنگی شدن آدرس ها در فرمول به چه معناست؟
زمانی که شما فرمولی می نویسید و آدرسی داخل آن تعریف میکنید هر محدوده از آدرس به یک رنگ متفاوت ظاهر می شود.همچنین محدوده در شیت ها با یک کادر به همان رنگ مشخص می شود .این کار برای خوانایی بهتر فرمول ها و شناسایی و ارتباط سریع بین آدرس در فرمول و شیت می باشد.
۲) پرانتزهای رنگی در فرمول به چه معناست؟
همانطور که می دانید در فرمول نویسی در اکسل توابع با یک پرانتز شروع و با یک پرانتز به اتمام می رسند.زمانی که شما از توابع تو در تو یا ترکیبی استفاده می کنید اکسل برای اینکه به شما کمک کنه تا محدوده هر تابع را از هم تشخیص بدید، پرانتز شروع و پایان هر تابع را با یک رنگ متفاوت نشان می دهد.یک رنگ متعلق به تابع خاصی نیست و ممکن است پرانتز یک تابع در فرمول ها رنگ های مختلفی به خود بگیرند.
در شکل بالا رنگ تابع INDEX مشکی، MATCH با رنگ قرمز، large با رنگ بنفش و تابع row با رنگ سبز مشخص شده است.
نکته: همیشه رنگ پرانتز اولین تابع مشکی می باشد.پس هرموقع رنگ پرانتزبسته تابع مشکی شد بدین معنی هست که محدوده کل فرمول را به اتمام رساندید.بعد از پرانتز مشکی هیچ پرانتز رنگی دیگری نباید قرار بگیرد.
۳) وجود آکولاد {} در فرمول به چه معناست؟
{INDEX(A4:F18,MATCH(LARGE(F4:F18,ROW(F4:F18)-3),$F$4:$F$18,0),2)=}
هرگاه فرمول یا مقداری داخل { } قرار گیرد بدین معناست که تابع بصورت آرایه ای ثبت شده است.فرمول نویسی آرایه ای همان فرمول نویسی عادی می باشد و فقط منطق آن فرق می کند و در انتها برای ثبت فرمول بجای زدن کلید Enter کلیدهای Ctrl+Shift را نگه داشته و کلید Enter را زده سپس همه کلید ها را رها کنید(بحث مربوط به فرمول نویسی آرایه ای را مطالعه کنید).
نکته: آکولاد ممکن است داخل توابع به روش های مختلفی استفاده شوند و قرار نیست همیشه ابتدای تابع قرار بگیرند.
۴) معنای استفاده از کروشه [ ] در برخی آرگومانهای توابع چیست؟
هرگاه آرگومان یک تابع داخل [] قرار بگیرد بدین معنی می باشد که وارد کردن مقدار به آن آرگومان اختیاری می باشد و می توانید مقداری وارد نکنید.
نکته: اختیاری بودن آرگومان بدین معنا نیست که هیچ مقداری به آن اختصاص داده نمی شود.درواقع در آرگومانهای اختیاری اگر شما مقداری وارد نکنید خود اکسل یک مقدار پیشفرض از قبل تعیین شده برای آن درنظر می گیرد. هر تابع پیشفرض های مربوط به خود را دارد.
۵) پر رنگ شدن نام آرگومان هنگام تایپ فرمول به چه معناست؟
در هنگام مقداردهی به آرگومانهای یک تابع پررنگ شدن نام آرگومان مشخص می کند که شما درحال وارد کردن مقدار برای کدام آرگومان تابع هستید.درواقع آرگومان فعال را مشخص می کند.
۶) در فرمول نویسی های پیچیده چگونه تشخیص بدهم که کدام مقادیر مربوط به کدام آرگومان می باشد؟
در فرمول نویسی های پیشرفته و پیچیده تشخیص اینکه کدام مقادیر مربوط به کدام آرگومان تابع می باشد کمی کار کلافه کننده ایست.برای این کار ما سه روش به شما پیشنهاد میدهیم:
- رنگهای پرانتز تابع را دنبال کنید: همانطور که گفتیم رنگ پرانتز هرتابع متمایز از سایر پرانتزهاست.
- از رابط فرمول نویسی اکسل کمک بگیرید: ابتدا با دبل کلیک رو سلول به حالت ویرایش برید سپس هرتابعی که می خواهید آرگومانهای آن تفکیک شود را انتخاب کنید() سپس روی fx کلیک چپ کنید.با این روش پنجره رابط فرمول نویسی باز می شود و مقادیر در آرگومانهای خودشان قرار می گیرند.
- از toolbox خود تابع کمک بگیرید: زمانی که تابع را تایپ می کنید تا به حالت ویرایش می روید یک پنجره راهنما برای شما ظاهر می شود که قسمت های مختلف تابع را نشان می دهد.شما با بردن موس روی هر آرگومان تابع و کلیک روی آن، مقادیر مربوط به آن آرگومان را در تابع های لایت می کند(مناسب ترین روش).
مثل شکل زیر:
نکته: اگر درمورد یک تابع نیاز به کمک داشتید می توانید خیلی سریع از Help اکسل استفاده کنید.برای مثال در شکل بالا اگر موس را روی تابع INDEX ببرید و کلیک کنید Help اکسل سریع باز خواهد شد و می توانید از آن استفاده کنید.این یکی از قابلیت های خوب اکسل هست که در اختیار کاربران قرار داده.
۷) آیا با پنجره رابط فرمول نویسی اکسل آشنا هستید؟
شما علاوه بر اینکه می توانید یک فرمول را بصورت مستقیم داخل سلول تایپ کنید، اکسل این امکان را به شما می دهد تا از یک رابط فرمول نویسی کمک بگیرید.این رابط بسیار مفید می باشدو اطلاعات ارزشمندی از توابع به ما می دهد.بخصوص برای کسانی که تازه شروع به فرمول نویسی کرده اند(در ادامه بیشتر با این رابط آشنا خواهیم شد).
نکته: برای دستیابی به این پنجره کافیست بعد از نوشتم نام تابع و پرانتز باز( مثلا: )SUM )، کلید Ctrl را نگه داشته و دکمه A را بزنید یا آیکون fx کنار نوار فرمول را بزنید.
۸) در پنجره رابط فرمول نویسی در اکسل چندین نتیجه دیده می شود.معنای هرکدام چیست؟
شما یک فرمول ترکیبی مثل فرمول زیر را درنظر بگیرید:
IF(AND(I10>0,I11<>””),2,MAX(H3:H9))=
- معرف نام تابعی هست که ما انتخاب کردیم و درحال حاضر فعال است.
- نتیجه نهایی مربوط به آرگومان روبرویی آن می باشد.
- نتیجه نهایی فرمول فعال می باشد.یعنی مقدار بدست آمده ماکزیمم مقدار محدوده ای هست که در شماره ۲ تعریف کردیم.
- توضیحی مختصر درمورد تابع فعال که اینجا همان تابع MAX هست.
- توضیحات مربوط به آرگومان فعال تابع.اگر شما داخل باکس هریک از آرگومانها کلیک کنید توضیحات مهمی درمورد آن آرگومان می دهد.ازجمله نحوه عملکرد آن آرگومان و مقادیر مجاز قابل دریافت.
- نتیجه نهایی کل فرمول ها که به عنوان خروجی نهایی در سلول نشان داده خواهد شد.
نکته:
در فرمول نویسی های ترکیبی شما میتوانید روی هر تابعی که میخواهید نتیجه آن را موقتا مشاهده کنید کلیک کرده و fx را بزنید.
تحلیل عبارت های استفاده شده در پنجره فرمول نویسی:
۱) در این قسمت مشخص می شود که ورودی های یک آرگومان به چه شکل خواهد بود.
شما برای تشخیص نوع ورودی آرگومان هم می توانید به راهنمای آرگومان مراجعه کنید و هم از روش کلمات کلیدی استفاده کنید. به روش زیر توجه کنید:
- Lookup: اگر از این عبارت استفاده شود یعنی قرار هست به یک چیزی نگاه کنه و اونو درنظر بگیره.ادامه نوشته تعیین کننده این خواهد بود که به چه نوع داده ای قرار است نگاه شود.
- Value: اشاره به یک مقدار دارد.
- Index: اگر از این عبارت استفاده شود یعنی به یک شماره اشاره دارد.پس مسلما باید یک مقدار عددی باشد.
- Range: اگر از این عبارت استفاده شود یعنی شما با یک محدوده سروکار دارید.
- Array: یعنی شما با یک محدوده بصورت ماترسی سروکار دارید.
- criteria: یعنی شما با یک شرط سروکار دارید.
- table: شما با محدوده ای سروکار دارید که باید قوانین جدول یا دیتابیسی در آن رعایت شده باشد.
- logical: شما با یک مقدار منطقی سروکار دارید که مقدار True , False یا ۰ و ۱ را دربر دارد.
- Col: اشاره به ستون یک محدوده نسبی یا محدوده شیت دارد.
- Row: اشاره سطر یک محدوده نسبی یا محدوده شیت دارد.
- num: اشاره به یک عدد دارد.
- text: اشاره به یک مقدار متنی دارد.
- chars: اشاره به کاراکتر یک مقدار دارد.
- match: اشاره به نوع جست و جو دارد.
- nuumber: اشاره به یک مقدار از نوع عددی دارد.
حال شما می توانید با کنار هم قرار دادن شاه کلیدهای بالا تشخیص بدهید که آرگومان از شما چه چیزی و از چه نوع داده ای می خواهد.برای مثال ما تابع Vlookup را اینجا تحلیل میکنیم.
مفهوم آرگومان های توابع:
lookup_value: خب ما گفتیم lookup یعنی قراره عمل نگاه کردن و درنظر گرفتن رو انجام بده و value یعنی به یک مقدار دارد.پس تحلیل این آرگومان این می شود که : مقداری که تابع vlookup باید اونو درنظر بگیره
Table_Array : خب Table یعنی یک محدوده جدولی منظم و Array یعنی یک محدوده آرایه ای یا ماتریسی.پس تحلیل این آرگومان این می شود که: ما باید یک محدوده ای از مقادیر برای تابع تعریف کنیم.
col_index_num: خب col اشاره به ستون دارد و index به شماره یا امتیاز یک مقدار در یک جدول دارد و num که مشخصه باید یک عدد باشد.پس تحلیل این آرگومان این می شود که : ما باید در یک محدوده شماره ستون از نوع عدد را به تابع بدهیم.
Range_Lookup: گاهی اتفاق می افتد که تفسیر ها کمی گنگ بنظر میرسد و تحلیل هدف آرگومان سخت می شود.مثل این مورد.که توصیه ما در این مواقع رجوع به راهنمای آرگومان هست.
۲) در این قسمت مشخص می شود که خروجی های یک آرگومان با چه فرمتی خواهد بود.
زمانی که شما مقداری به آرگومان می دهید آن آرگومان یک خروجی دارد و آن خروجی یک فرمت مشخصی دارد که در ادامه به این بحث می پردازیم:
- any: می تواند هرچیزی باشد.
- number: خروجی از نوع عددی خواهد بود.
- logical: خروجی از نوع منطقی یعنی True , Falseخواهد بود.
- reference: خروجی یک آرایه ای از مقادیر خواهد بود.
- text: خروجی از نوع متنی خواهد بود.
نکته:
اگر یک مقداری داخل ” ” قرار بگیرد بدین معنی هست که آن مقدار از نوع رشته ای یا متنی هست.برای مثال مقدار “۵” با مقدار ۵ برابر نیست.زیرا اولی متن هست ولی دومی عدد.حتما به این موضوع دقت کنید.
منبع:
درباره جاوید
عاشق اکسل هستم و بیش از 12 ساله دارم اکسل کار می کنم. در این مسیر آموزش ها و فایل های حرفه ای ارائه دادم و همچنان در این مسیر با علاقه پیش میرم چون امروزه اکسل به یک مهارت انکار نشدنی تبدیل شده
سایر نوشته های جاویدمطالب زیر را حتما مطالعه کنید
۴ گام تا شماره گذاری صفحه چاپ در اکسل با یک نکته عالی
ایجاد لیست شیت ها در ComboBox اکسل با دستورات VBA
در کمتر از ۱ دقیقه چک لیست کارهای روزانه در اکسل بساز
فایل اکسل محاسبه مالیات حقوق سال ۱۴۰۱ + آموزش ویدئویی فرمولنویسی مالیات
برای چاپ اطلاعات داخل فرم از قبل پرینت شده در اکسل چکار کنیم؟
فقط با ۳ سوت فرم لاگین در اکسل طراحی کن
2 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
همکاری تون عالی بود تشکر
چند سوال دیگه دارم لطفا جواب بدین ممنون میشم
اگر نمره شخص = یا بیشتر از ۱۰ باشه قبوله
اگر نمره شخص زیر ۱۰ باشه مردود است
فرمولش چی میشه
سلام. وقت بخیر
=IF(A1>=۱۰,"gabol","mardod")
در فرمول بالا A1 سلولی است که نمره داخل اون وارد شده
اگر سلول A1 خالی بود برای اینکه چیزی نشون نده، فرمول بالارو به شکل زیر کامل کنید:
=IF(A1<>"",IF(A1>=۱۰,"gabol","mardod"),"")