وبلاگ

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

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

فرمول نویسی در اکسل یکی از مباحثی هست که یادگیری آن بسیار ضروری و مهم می باشد.اکثر کسانی که با اکسل کار میکنند و نیاز به محاسبات هرچندساده دارند، حداقل چند تابع مهم آن را می شناسند.خیلی از افراد چه در سطح مقدماتی و چه در سطح پیشرفته استفاده های فراوانی از توابع میکنند اما موضوعی که اینجا مطرح هست آیا این افراد به راهنمای توابع و علائم آنها توجه می کنند..!؟ آیا آن چیزی را که تابع می گوید را می شنوند…!؟ یا صرفا طریقه استفاده از آن را حفظ کرده اند.

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

تعریف تابع:

از دید ریاضیات تابع عملکردی است که به ازای هر ورودی داده شده فقط و فقط یک خروجی تولید کند و اگر دو خروجی تولید کند تابع محسوب نمی شود.برای مثال تابع را یک ماشین حساب درنظر بگیرید.حال شما تعداد زیادی عدد وارد می کنید و بعد از تعریف اینکه چه عملیاتی روی آن اعداد انجام دهد فقط و فقط یک خروجی به شما نشان می دهد.مثلا من اعداد 2و6 را به ماشین حساب می دهم و تعیین می کنم که بعد از جمع زدن این دو عدد حاصل آن در عدد 2 ضرب کند که در نتیجه عدد 16 به عنوان خروجی نهایی برای ما نشان داده می شود.

حال در اکسل ماشین حساب های از پیش تعریف شده ای وجود دارد که دیگر نیازی نیست برای هرنوع عملیات محاسباتی پروسه پیچیده ای را انجام دهید.کافیست اسم ماشین حساب از پیش تعریف شده توسط اکسل را فراخوانی کنید و مقادیر را به ماشین حساب معرفی کنید .ما به این ماشین حساب های از پیش تعریف شده که هرکدام محاسبات خاصی انجام میدهند را اصطلاحا “تابع” می گوییم.که در این مثال برای جمع اعداد می توانیم ماشین حساب یا بعبارتی تابع ()SUM را فراخوانی کنیم.

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

1) رنگی شدن آدرس ها در فرمول به چه معناست؟

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

رنگ های آدرس سلول در تابع اکسل

2) پرانتزهای رنگی در فرمول به چه معناست؟

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

پرانتز در توابع اکسل

در شکل بالا رنگ تابع INDEX مشکی، MATCH با رنگ قرمز، large با رنگ بنفش و تابع row با رنگ سبز مشخص شده است.

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

3) وجود آکولاد {} در فرمول به چه معناست؟

{INDEX(A4:F18,MATCH(LARGE(F4:F18,ROW(F4:F18)-3),$F$4:$F$18,0),2)=}

هرگاه فرمول یا مقداری داخل { } قرار گیرد بدین معناست که تابع بصورت آرایه ای ثبت شده است.فرمول نویسی آرایه ای همان فرمول نویسی عادی می باشد و فقط منطق آن فرق می کند و در انتها برای ثبت فرمول بجای زدن کلید Enter کلیدهای Ctrl+Shift را نگه داشته و کلید Enter را زده سپس همه کلید ها را رها کنید(بحث مربوط به فرمول نویسی آرایه ای را مطالعه کنید).

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

4) معنای استفاده از کروشه [ ] در برخی آرگومانهای توابع چیست؟

کروشه در توابع اکسل

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

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

5) پر رنگ شدن نام آرگومان هنگام تایپ فرمول به چه معناست؟

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

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

6) در فرمول نویسی های پیچیده چگونه تشخیص بدهم که کدام مقادیر مربوط به کدام آرگومان می باشد؟

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

  • رنگهای پرانتز تابع را دنبال کنید: همانطور که گفتیم رنگ پرانتز هرتابع متمایز از سایر پرانتزهاست.
  • از رابط فرمول نویسی اکسل کمک بگیرید: ابتدا با دبل کلیک رو سلول به حالت ویرایش برید سپس هرتابعی که می خواهید آرگومانهای آن تفکیک شود را انتخاب کنید() سپس روی fx کلیک چپ کنید.با این روش پنجره رابط فرمول نویسی باز می شود و مقادیر در آرگومانهای خودشان قرار می گیرند.
  • از toolbox خود تابع کمک بگیرید: زمانی که تابع را تایپ می کنید تا به حالت ویرایش می روید یک پنجره راهنما برای شما ظاهر می شود که قسمت های مختلف تابع را نشان می دهد.شما با بردن موس روی هر آرگومان تابع و کلیک روی آن، مقادیر مربوط به آن آرگومان را در تابع های لایت می کند(مناسب ترین روش).

مثل شکل زیر:

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

نکته: اگر درمورد یک تابع نیاز به کمک داشتید می توانید خیلی سریع از Help اکسل استفاده کنید.برای مثال در شکل بالا اگر موس را روی تابع INDEX ببرید و کلیک کنید Help اکسل سریع باز خواهد شد و می توانید از آن استفاده کنید.این یکی از قابلیت های خوب اکسل هست که در اختیار کاربران قرار داده.

7) آیا با پنجره رابط فرمول نویسی اکسل آشنا هستید؟

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

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

نکته: برای دستیابی به این پنجره کافیست بعد از نوشتم نام تابع و پرانتز باز( مثلا:  )SUM )، کلید Ctrl را نگه داشته و دکمه A را بزنید یا آیکون fx کنار نوار فرمول را بزنید.

8) در پنجره رابط فرمول نویسی در اکسل چندین نتیجه دیده می شود.معنای هرکدام چیست؟

شما یک فرمول ترکیبی مثل فرمول زیر را درنظر بگیرید:

IF(AND(I10>0,I11<>””),2,MAX(H3:H9))=

کار با پنجره فرمول نویسی در اکسل
  1. معرف نام تابعی هست که ما انتخاب کردیم و درحال حاضر فعال است.
  2. نتیجه نهایی مربوط به آرگومان روبرویی آن می باشد.
  3. نتیجه نهایی فرمول فعال می باشد.یعنی مقدار بدست آمده ماکزیمم مقدار محدوده ای هست که در شماره 2 تعریف کردیم.
  4. توضیحی مختصر درمورد تابع فعال که اینجا همان تابع MAX هست.
  5. توضیحات مربوط به آرگومان فعال تابع.اگر شما داخل باکس هریک از آرگومانها کلیک کنید توضیحات مهمی درمورد آن آرگومان می دهد.ازجمله نحوه عملکرد آن آرگومان و مقادیر مجاز قابل دریافت.
  6. نتیجه نهایی کل فرمول ها که به عنوان خروجی نهایی در سلول نشان داده خواهد شد.

نکته:

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

تحلیل عبارت های استفاده شده در پنجره فرمول نویسی:

1) در این قسمت مشخص می شود که ورودی های یک آرگومان به چه شکل خواهد بود.

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

  • Lookup: اگر از این عبارت استفاده شود یعنی قرار هست به یک چیزی نگاه کنه و اونو درنظر بگیره.ادامه نوشته تعیین کننده این خواهد بود که به چه نوع داده ای قرار است نگاه شود.
  • Value: اشاره به یک مقدار دارد.
  • Index: اگر از این عبارت استفاده شود یعنی به یک شماره اشاره دارد.پس مسلما باید یک مقدار عددی باشد.
  • Range: اگر از این عبارت استفاده شود یعنی شما با یک محدوده سروکار دارید.
  • Array: یعنی شما با یک محدوده بصورت ماترسی سروکار دارید.
  • criteria: یعنی شما با یک شرط سروکار دارید.
  • table: شما با محدوده ای سروکار دارید که باید قوانین جدول یا دیتابیسی در آن رعایت شده باشد.
  • logical: شما با یک مقدار منطقی سروکار دارید که مقدار True , False یا 0 و 1  را دربر دارد.
  • 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: گاهی اتفاق می افتد که تفسیر ها کمی گنگ بنظر میرسد و تحلیل هدف آرگومان سخت می شود.مثل این مورد.که توصیه ما در این مواقع رجوع به راهنمای آرگومان هست.

2) در این قسمت مشخص می شود که خروجی های یک آرگومان با چه فرمتی خواهد بود.

زمانی که شما مقداری به آرگومان می دهید آن آرگومان یک خروجی دارد و آن خروجی یک فرمت مشخصی دارد که در ادامه به این بحث می پردازیم:

  • any: می تواند هرچیزی باشد.
  • number: خروجی از نوع عددی خواهد بود.
  • logical: خروجی از نوع منطقی یعنی True , Falseخواهد بود.
  • reference: خروجی یک آرایه ای از مقادیر خواهد بود.
  • text: خروجی از نوع متنی خواهد بود.

نکته:

اگر یک مقداری داخل ” ” قرار بگیرد بدین معنی هست که آن مقدار از نوع رشته ای یا متنی هست.برای مثال مقدار “5” با مقدار 5 برابر نیست.زیرا اولی متن هست ولی دومی عدد.حتما به این موضوع دقت کنید.

منبع:

blog.hubspot.com

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

9 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • با عرض پوزش . محدوده تابع ایندکس را می توان یک تابع داد؟ چطور؟ مثلا” بجای مختصات شروع و انتهای محدوه سلول از تابع آدرس سلول استفاده کرد ؟

    • سلام.

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

  • سلام و خیلی ممنون توضیخ بیشتر اینکه مثلا” چطور بجای محدوده A1:A4 در تابع ایندکس (index( A1:A4;2;0= که A1:A4 محدوده که با سلولهای قطری مستطیل موردجستجوتعریف شده بتوانیم بجای آن از مثلا” بجای A1 از (Address( 1;1) و بجای A4 از (Address( 1;4 استفاده کنیم چون اکسل با این جایگزینی آرگومانها با توابع را غلط میگیره . شما راه حلی یا جایگزینی برای این کار من میتوانید راهنمائی کنید ؟

    • سلام.

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

      مثل کد زیر :


      INDEX(INDIRECT(ADDRESS(2,3)&":"&ADDRESS(13,4)),2,2)=

  • ممنون بابت آموزش های مفیدتون در مورد اکسل بویژه توابع. موفق باشید.

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

    • سلام.وقت بخیر
      حتما وجود داره.

      احتمالا نوشته های روی کیبورد شما کمی فرق داره.

  • با سلام،چطور میتونم یه فرمول رو توی سلول های مختلف درگ کنم،مثلا:a1,b3,c4,f7,….

    • سلام.وقت بخیر

      راه های مختلفی وجود داره:

      1- تو یک سلول فرمول رو نوشته و اون سلول رو کپی کنید.بعد روی سلول های دلخواه کیک راست کرده و گزینه Formulas رو بزنید تا فرمول پست بشه.
      2- سلول های دلخواه رو انتخاب کرده و فرمول موردنظر را نوشته و کلید ترکیبی Alt+Enter رو بزنید.

دیدگاهتان را بنویسید