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

پس مانند پست قبل یک فایل با حالت macro enabled ایجاد کرده و با کلید alt+f11 وارد محیط vb شده و تابع زیر را مینویسیم :

Public Function CalculateTax97(ByVal mashmoolMaliat As Double) As Double

  Dim tx As Double

  If mashmoolMaliat > 161000000 Then
    tx = (mashmoolMaliat - 161000000) * 0.35 + 11500000 + 3450000 + 6900000
  Else
    If mashmoolMaliat > 115000000 Then
      tx = (mashmoolMaliat - 115000000) * 0.25 + 3450000 + 6900000
    Else
      If mashmoolMaliat > 92000000 Then
        tx = (mashmoolMaliat - 92000000) * 0.15 + 6900000
      Else
        If mashmoolMaliat > 23000000 Then
          tx = (mashmoolMaliat - 23000000) * 0.1
        Else
          tx = 0
        End If
      End If
    End If
  End If

  CalculateTax97 = tx

End Function

سپس از منوی فایل گزینه save را انتخاب کرده و با کلید alt+f11 به محیط اکسل برمیگردیم و در یک سلول خالی عبارت CalculateTax97(92000000)= را تایپ میکنیم. تابع، عدد 6.900.000 را بازمیگرداند.

 

فرمول خطی این تابع (بدون نیاز به نوشتن تابع) بصورت زیر میباشد که میتوان در یک سلول خالی آنرا تایپ کرد. (با فرض اینکه عدد مشمول مالیات یا 92.000.000 ما در ستون B9 تایپ شده باشد، در ستون B10 این فرمول را مینویسیم :

=IF(B9>161000000;((B9-161000000)*0.35)+11500000+3450000+6900000;IF(B9>115000000;((B9-115000000)*0.25)+3450000+6900000;IF(B9>92000000;((B9-92000000)*0.15)+6900000;IF(B9>23000000;((B9-23000000)*0.1)+0;0))))

همانطور که مشاهده میکنید، جواب مانند مثالی که با تابع نوشته شد، برابر است.

 

در اینجا باید درنظر گرفت که در بعضی شرایط که میتوان تابع خود را بصورت خطی (در محیط اکسل و نه در vb) نوشت، قطعا" اینکار بهتر است. اما در شرایطی بدلیل طولانی شدن فرمول خطی و یا پیچیده شدن آن و یا استفاده از دستوراتی مانند حلقه ها و یا if های مکرر، استفاده از توابع شخصی اجتناب ناپذیر میباشد.

 

موفق باشید.

طراحی توابع شخصی (UDF) در نرم افزار اکسل (Excel) یکی از پرکاربردترین ابزارهای مورد نیاز افراد در کار با نرم افزار فوق میباشد. در بسیاری از موارد پیش میآید که ما برای نوشتن یک فرمول پیچیده در یک سلول در برنامه اکسل سردرگم میشویم. مخصوصا زمانی که متن فرمول طولانی باشد. البته پیش نیاز نوشتن توابع شخصی در اکسل، داشتن دانش برنامه نویسی در حد عادی با برنامه ویژوال بیسیک (Visual Basic or VB) میباشد.

برای شروع کار، نرم افزار اکسل را باز کرده و از منوی فایل، گزینه Save As را انتخاب کرده و سپس از گزینه Excel Macro Enabled Workbook را انتخاب کرده و سپس نام فایل مورد نظرمان را وارد میکنیم . (به پسوند xlsm دقت کنید)

برای ورود به محیط طراحی توابع از دکمه ترکیبی Alt+F11 و یا از تب Developer، گزینه Visual Basic را انتخاب میکنیم. در این مرحله پنجره جدیدی با نام Microsoft Visual Basic باز میشود.

برای نوشت دستورات، ابتدا از منوی Insert، گزینه Module را انتخاب میکنیم. در این حالت آماده نوشتن کد خواهیم بود. برای مثال دو تابع کوچک مینویسیم :

Function sayHello()

sayHello = "Hello Excel"

End Function

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

در خط دوم نام تابع را برای برگرداندن مقدار دلخواه، تایپ کرده و سپس با علامت =، مقدار "Hello Excel" را به آن نسبت داده ایم.

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

 

** توجه ** برای استفاده از توابع در اکسل، حتما در محیط VB از منوی فایل، گزینه save را باید انتخاب کنیم تا توابع طراحی شده در اکسل ثبت شوند. سپس با کلید ترکیبی Alt+F11 به محیط اکسل برگشته و در یکی از سلول ها عبارت ()sayHello= را تایپ میکنیم. در اینجا اکسل فرمان داخل سلول را اجرا کرده و عبارت "Hello Excel" نمایش داده میشود. به عبارت دیگر، تابع sayHello به این فایل اکسل به عنوان یک تابع شخصی اضافه شده است.

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

حال نمونه تابع دیگری مینویسیم : (با کلید ترکیبی Alt+F11 با محیط VB بازمیگردیم)

(درست در زیر تابع قبلی دستورات زیر را اضافه میکنیم و بعد از پایان دستورات حتما گزینه save را انتخاب میکنیم)

این تابع جدید، پارامتر داده شده را در عدد 2 ضرب کرده و حاصل را باز میگرداند :

Function zarbX2(x)

zarbX2 = x * 2

End Function

در خط اول کلمه Function و سپس نام تابع zarbX2 و سپس پارامتر x بکار رفته است. پارامتر x برای دریافت عدد از درون برنامه اکسل و ارجاع به تابع ما استفاده شده است.

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

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

سپس از منوی فایل گزینه save را انتخاب کرده و با کلید ترکیبی Alt+F11 به محیط اکسل برمیگردیم.

در یک سلول خالی عبارت (5)zarbX2= را تایپ میکنیم. همانطور که مشاهده میکنید عدد 10 از تابع طراحی شده ما برمیگردد. یا مثلا در سلول A1 عدد 20 را تایپ کرده و در سلول B1 عبارت (A1)zarbX2= را تایپ کنید. همانطور که مشاهده میکنید، عدد 40 بازگردانده خواهد شد.

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