چگونه مي‌توان در اكسل تابعي جديد تعريف كرد؟

اكسل ، آموزش رايگان اكسل ، كتاب آموزش اكسل ، آموزش اكسل پيشرفته ، آموزش نرم افزار اكسل ، اكسل ۲۰۱۶ ، آموزش جديد اكسل

چگونه مي‌توان در اكسل تابعي جديد تعريف كرد؟

۱,۳۳۴ بازديد

چگونه مي‌توان در اكسل تابعي جديد تعريف كرد؟

چكيده :

در اكسل كاربر مي‌تواند توابعي را تعريف كند كه User Defined Function يا به اختصار UDF ناميده مي‌شوند، در اين مقاله نحوه تعريف اين توابع و بكار گيري آن توضيح داده شده است.

مطالب به صورت خود آموز و قدم به قدم توضيح داده شده است .

مقدمه

دقيقا يادم است كه روزي قرار ملاقاتي با يكي از اساتيد اكسل داشتم ، ايشان استاد دانشگاه بودند و گويا دوتا هم مدرك دكتري . تقريبا يادم نيست كه چه صحبتهايي شد اما اين را خوب يادم است كه بعد از آن ملاقات بود كه مي‌خواستم خودم در اكسل يك تابع بنويسم ، خوب از كجا مي‌دانستم كه مي‌شود اينكار را كرد ؟

فكر كنم كه روزي فايلي را از اينترنت گرفته بودم و بعد از نصب آن وقتي به Insert à function رفته بودم گزينه‌اي به نام User Function  را ديدم و همان موقع شصتم خبر دار شد كه بله مي‌شود خودمان توابع دلخواهمان را در اكسل بنويسيم . اما چطوري ؟

تابع چيست ؟ در دبيرستان ساعتها وقت ما را همين كلمه گرفت و انصافاٌ من با كامپيوتر بود كه تازه فهميدم تابع چيست. در واقع تابع يك عملگري است كه چيزي را مي‌گيرد و روي آن كاري را انجام مي‌دهد و بعد چيز ديگري كه خروجي مي‌گوييم را به ما مي‌دهد. مثلا همين تابع SUM را در نظر بگيريد ، چند تا عدد مي‌گيرد و جمع آنها را به ما مي‌دهد.     اكسل بيش از 300 تابع دارد كه اكثر كارهايي كه ممكن است بخواهيم انجام دهيم با اين توابع قابل انجام است ، در ضمن تركيب اين توابع نيز براي ما امكانات فراواني را بهمراه دارد و اين را هم مد نظر داشته باشيم كه شركتي به عظمت ماكروسافت و تجربه چندين ساله‌اش مطمئنا نيازهاي تمامي‌ كاربران در سطح دنيا را در نظر داشته و تا آنجايي كه امكان داشته توابع مختلف را پيش بيني كرده است . توابع جديد به چه كاري مي‌آيند البته اين سوال ممكن است به ذهن شما متبادر شود كه چرا بايد تابع جديدي اضافه كرد. شايد دلايل زير بتواند گوشه‌اي از ارزش تابع را براي ما بيان كند: جلوگيري از كارهاي تكراري در اكسل انجام محاسبات پيچيده دسترسي به كليه امكانات يك زبان برنامه نويسي مانند ويژوال بيسيك به اشتراك گذاشتن توابع با ساير كاربران استفاده سريعتر از نرم افزار جلوگيري از اشتباهات كاربران آشنايي با ويژوال بيسيك براي شروع بهتر است كمي در خصوص ويژوال بيسيك بدانيم . VB يك زبان برنامه نويسي بسيار متداول است. براي اينكه يك تابع جديد نوشته شود لازم است كه كمي با برنامه نويسي با اين زبان آشنا باشيم. براي اين منظور پيشنهاد مي‌كنم كه نرم افزار VB را تهيه كنيد و بعد از آن هم چند CD آموزشي و يك كتاب  . با كمي تمرين با اصول ابتدايي اين زبان آشنا خواهيد شد و قول مي‌دهم كار بسيار ساده تر از آنچه فكر مي‌كنيد باشد. لازم به ذكر است كه نسخه جديد نرم افزار VB به نام VB.NET نيز وجود دارد كه امكان استفاده آن در OFFICE 12 كه نسخه بعدي آفيس است گنجانده شده و بد نيست بدانيم كه VB.NET در واقع قابليتهاي زبان برنامه نويسي C# را دارد.   شما براي استفاده از VB در اكسل نياز نداريد كه نرم افزار VISUAL BASIC را نصب كنيد ، همراه با نصب آفيس خود اين نرم افزار نيز نصب مي‌شود.   گام اول ورود به محيط ويژوال بيسيك ابتدا بايستي وارد محيط VB شويم. براي اينكار چندين راه وجود دارد كه عبارتند از: ·          زدن كليد ALT+F11  ·          از منوها : Tools à Macro à Visual Basic Editor ·          از Toolbar  :  

محيط ويژوال بيسك

 

 

گام دوم ايجاد يك ماژول

شما بايد دستورات تابع خود را در يك Module (ماژول) بنويسيد ،  از منوي Insert گزينه Module را بزنيد . و اگر به project explorer نگاه كنيد متوجه خواهيد شد كه يك ماژول جديد ايجاد شده است.

 

 

 

گام سوم ايجاد يك تابع در ماژول

يك تابع در ويژوال بيسيك قواعد استانداردي دارد كه شما بايد از اين قواعد اطاعت كنيد .

اولين قانون آن اين است كه يك تابع با دستورات استانداردي شروع و به پايان مي‌رسد.

قانون دوم اين است كه هر تابع يك نوع دارد و وروديهاي يك تابع در داخل پرانتز مشخص مي‌شوند.

قانون سوم ، نوع داده وروديها (و خود تابع) بايد مشخص شود.

اين دستورات عبارتند از :

Private Function Test(Num As Integer) as Double

 

End Function

نام تابع ما test است و عبارت داخل پرانتز مي‌گويد كه اين تابع يك ورودي دارد كه نام آن ورودي Num است و integer  بيانگر آن است كه اين ورودي عددي صحيح است . (-32,768  تا 32,767 )

خروجي تابع از نوع double است و البته گذاشتن آن در همه موارد الزامي نيست ، گرچه بهتر است كه مشخص شود. (براي اطلاع بيشتر به كتابهاي برنامه نويسي مراجعه كنيد.)

عبارت Private Function نشانگر شروع تابع و End Function براي پايان تابع است.

 

 

گام چهارم – نوشتن تابع

فرض كنيد مي‌خواهيم تابعي بنويسيم كه يك عدد را بگيرد و آنرا در 10 ضرب كند!

اول بايد تصميم بگيريم كه اسم اين تابع را چه بگذاريم ، در حقيقت اين اسم همان كلمه‌اي است كه در اكسل براي استفاده از اين تابع استفاده خواهيم كرد.

خوب اسم آنرا Test مي‌گذاريم و مي‌دانيم كه اين تابع بايد يك ورودي داشته باشد و خوب چون به تازگي با نوع عدد Integer  آشنا شديم (عدد صحيح) نوع اين ورودي را هم Integer مي‌گذاريم.

بايد نامي براي اين ورودي در نظر بگيريم ، اين نام نبايد يك نام آشنا ! براي VB باشد و بهتر است نامي با مسما در نظر بگيريم ، اينجا اسم اين ورودي را Num مي‌گذاريم.

پس در ماژول خود خواهيم نوشت :

 

Private Function Test(Num As Integer)

 

    Test = Num * 10

 

End Function

 

 

 

 

 

حال از ويژوال بيسيك خارج مي‌شويم ( Alt + Q) و به اكسل بر مي‌گرديم .

 

گام پنجم - استفاده از تابع

مثل توابع استاندارد اكسل مي‌توان از اين تابع هم استفاده كرد مثلا بنويسيد :

= test(8)

= test(A1)

اگر به جاي كلمه  Private  ، Public بنويسيم، مي‌توانيم نام تابع جديدمان را در UserFunction ببينيم.

 

 

 

 

 

 

 

مثال 1) تابع بدست آوردن شماره رنگ يك سلول و رنگ قلم آن سلول

مي‌خواهيم تابعي بنويسيم كه شماره رنگ يك سلول (fill color) يا شماره رنگ قلم (font color) را مشخص كنيم.

(مي‌دانيم كه در اكسل از 56 رنگ مي‌توان استفاده كرد كه هر رنگ يك كد دارد مثلا كد رنگ قرمز 3 و آبي 5 است.)

·          نام تابع : CellColor

·          ورودي : تابع دو ورودي دارد ، ورودي اول آدرس سلول است و ورودي دوم مشخص مي‌كند كه ما مي‌خواهيم رنگ زمينه سلول را داشته باشيم يا رنگ قلم آنرا .
اگر ورودي دوم عبارت
fill بود رنگ زمينه مد نظر است و اگر font بود رنگ قلم.

·          نام ورودي اول MyRange و از نوع Range  است

·          نام ورودي دوم Mode و از نوع String است

·          اگر ورودي دوم داده نشده بود و يا مقاديري غير fill و font بود ، خروجي تابع يك خطا به شكل #Mistake  باشد .

 

براي نوشتن اين تابع از دستور شرطي IF به صورت زير استفاده مي‌كنيم :

Public Function Colorindex(MyRange As Range, Mode As String)

   

    Application.Volatile True

   

    If Mode = "font" Then

     Colorindex = MyRange.Font.Colorindex

    ElseIf Mode = "fill" Then

     Colorindex = MyRange.Interior.Colorindex

    Else

     Colorindex = "#Mistake"

    End If

End Function

عبارت As Range بيان مي‌كند كه ورودي اول يك خانه است.

عبارت String As بيان مي‌كند كه ورودي دوم يك رشته (متن – غير عدد) است .

 

دستور Application.Volatile True  به اكسل مي‌گويد كه هر وقت هر خانه‌اي را مجدد محاسبه كرد، بايد تابع ما را نيز مجدد محاسبه كند . ( اين حالت مانند تابع now() خود اكسل است كه زمان را مرتب محاسبه و نشان مي‌دهد.) زدن كليد F9 نيز باعث مي‌شود كه اين تابع مجدد محاسبه شود.

 

حال خانه A1 را به رنگ زرد و متن آنرا قرمز مي‌كنيم و تابع را روي آن آزمايش مي‌كنيم.

فرمولهاي بكار رفته در خانه B1 و B2 را به ترتيب در D1 و D2 مشاهده مي‌كنيد.

 

 

A

B

C

D

1

far

13

 

=colorindex(A1,"font")

2

 

6

 

=colorindex(A1,"fill")

مثال 2) سلولهايي را كه رنگ آنها . . . را با هم جمع بزنيد.

بهتر است از مثال قبل يك استفاده كاربردي كنيم. مثلا در يك محدوده مي‌خواهيم سلولهاي قرمز رنگ را با هم جمع بزنيم. (اين سلولها مي‌توانند با Conditional Formationg قرمز شده باشند.)

 

Public Function SumByColor(InRange As Range, WhatColorIndex As Integer) As Double

   

     Application.Volatile True

    

For Each C In InRange.Cells

       

    If C.Interior.Colorindex = WhatColorIndex Then

        SumByColor = SumByColor + C.Value

    End If

   

Next C

   

End Function

·          نام تابع : SumByColor و نوع آن را Double در نظر گرفتيم.

·          ورودي‌ها: محدوده كه نام آنرا InRange و شماره رنگ  كه نام آنرا  WhatColorIndex گذاشتيم و از نوع عدد صحيح است.

·          عبارت For Each C InRange دارد مي‌گويد كه C يك سلول از سلولهاي محدود InRange است .

مثال 4- تابعي ايجاد كنيد كه اختلاف بزرگترين و كوچكترين عدد يك محدوده را حساب كند.

البته اينكار با خود اكسل بسيار ساده است و كافي است كه فرمول =max(   ) – Min(   )  را بكار ببريم ، اما هدف اين مثال اينست كه نشان دهد چطور مي‌توان در محيط ويژوال بيسيك از توابع استاندارد اكسل استفاده كرد.

 

Public Function max_min(InRange As Range) As Double

 

    Application.Volatile True

 

MaxNum = Application.WorksheetFunction.Max(InRange)

MinNum = Application.WorksheetFunction.Min(InRange)

 

max_min = MaxNum - MinNum

 

End Function

همانطور كه مي‌بينيد با دستور application.worksheetfunction.xxx  كه به جاي xxx نام تابع استاندارد اكسل را مي‌نويسيم.

 

مثال 4- مي‌خواهيم تابعي بنويسيم كه بتواند مقدار هزار، ميليون و ميليارد را در يك عدد تشخيص دهد و سپس كلمه "تومان" يا "ريال" را هم  به آن عدد اضافه كند . در صورتي كه عدد معتبر نبود پيغام خطاي مناسبي را اعلام كند.

مثلا اگر عدد 15000  وارد شد بنويسيد : 15 هزار ريال (يا 15 هزار تومان)

با اين مثال قصد داريم وروديهاي Optional را مرور كنيم. مثلا اگر تابع Vlookup را در نظر بگيريم ، آخرين ورودي آن انتخابي (optional) است ، بدين معني كه اگر كاربر مقداري را وارد نكند به طور پيش فرض مقدار True را در نظر مي‌گيرد و در ضمن اگر كاربر خواست مي‌تواند ورودي را به تابع بدهد.

حالت ساده اين كار در VB به اين صورت است كه مي‌نويسيم :

 

Public Function Test(Num as Integer, optional Num as Integer = True)

 

تفسير  كد بالا به اين صورت است كه تابعي داريم به نام Test با دو ورودي.

ورودي اول آن مقداري است به نام Num و از نوع عدد صحيح است .

ورودي دوم آن مقداري است اختياري ، يعني كاربر مي‌تواند ورودي دوم را ندهد و با تعريف بالا مقدار پيش فرض آن True در نظر گرفته شده كه اگر كاربر مقداري را صريحاً به تابع اعلام نكند ، آنوقت مقدار دوم True در نظر گرفته خواهد شد و كار تابع با اين مقدار شروع مي‌شود.

اگر ورودي دوم داده نشود يا 1 يا True اعلام شود ، كلمه ريال در نظر گرفته خواهد شد.

اگر ورودي دوم False يا 0  داده شود ، كلمه تومان در نظر گرفته خواهد شد.

حال به نوشتن تابع مذكور مي‌پردازيم :

 

Public Function PersianCurrency(MyNumber As String, Optional Mode As Boolean = True)

   

    Application.Volatile True

 

If Val(MyNumber) >= 0 Then

    Temp$ = MyNumber

    Cur$ = ""

End If

 

If Val(MyNumber) >= 1000 Then

    Temp$ = Mid(Trim(MyNumber), 1, Len(MyNumber) - 3)

    Cur$ = "هزار"

End If

 

If Val(MyNumber) >= 1000000 Then

    Temp$ = Mid(Trim(MyNumber), 1, Len(MyNumber) - 6)

        Cur$ = "ميليون"

End If

 

If Val(MyNumber) >= 1000000000 Then

    Temp$ = Mid(Trim(MyNumber), 1, Len(MyNumber) - 9)

        Cur$ = "ميليارد"

End If

 

 

If Mode = True Then C$ = "ريال" Else C$ = "تومان"

 

PersianCurrency = Temp$ & Cur$ & " " & C$

 

If Val(MyNumber) = 0 Then

    PersianCurrency = "مقدار يافت نشد"

End If

 

End Function

تست و خروجي اين تابع را در مثال زير مشاهد مي‌كنيد .

تذكر : مقدار True همان 1 است و مقدار False همان 0 است . (يعني از هر كدام از اين مقادير مي‌توان استفاده كرد.)

 

 

A

B

C

1

10000

10هزار ريال

=Persiancurrency(A1,1)

2

250000

250هزار تومان

=Persiancurrency(A2,FALSE)

3

15000000

15ميليون ريال

=Persiancurrency(A3,1)

4

15000001

15ميليون تومان

=Persiancurrency(A4,FALSE)

5

 far

مقدار يافت نشد

=Persiancurrency(A5,1)

 

 اين تابع جنبه تمريني دارد و همه مقادير اعداد را پوشش نميدهد ،  فقط ارقامي كه با صفر همراه هستند را مي شناسد.

استفاده از توابع در تمامي فايلهاي اكسل

اما اين سوال پيش مي‌آيد كه چطور مي‌شود اين توابع را در همه جا – يعني همه فايلهاي اكسل – استفاده كنيم . وقتي شما يك تابع جديد را تعريف مي‌كنيد اين تابع فقط در همان فايل (البته همه sheet هاي آن) قابل استفاده است و اگر بخواهيد اين تابع را در فايل ديگري فراخواني كنيد، با پيغام #Name مواجه خواهيد شد. يك راه اين است كه اين فايل را بعنوان يك فايل Template ذخيره و بعد استفاده كنيم. اما راه اصولي اضافه كردن تابع در اكسل استفاده از Add-In ها است.   Add-In چيست؟ Add-In  توابعي هستند كه توسط كاربران نوشته مي‌شود و سپس روي اكسل اضافه مي‌گردند. هر بار كه اكسل اجرا شود، تمامي اين توابع نيز فراخواني خواهند شد و در واقع Add-In ها امكان بسط نرم افزار را بدون محدوديت به كاربران مي‌دهد.   ايجاد يك Add-In قدم اول ايجاد يك Add-In است كه كار بسيار ساده‌اي است. فايلي كه توابع در آن نوشته شده است را باز كنيد. تمام Sheetهاي اضافه آن را حذف كنيد. (فقط يك sheet خالي داشته باشيد.) از منوي File گزينه Save as را انتخاب كنيد. يك نام براي فايل انتخاب كنيد. در جلوي گزينه Save As type گزينه  MicroSoft Office Excel Add-In را انتخاب كنيد. دقت داشته باشيد كه فايل را در كجا Save مي‌كنيد.       نصب يك Add-In بعد از ساختن يك Add-In شما مي‌توانيد آنرا در هر كامپيوتري با هر نسخه‌اي از Office نصب كنيد.( مثلا Office2000) با نصب اين فايل تمامي‌توابعي كه نوشته‌ايد به اكسل اضافه خواهد شد و تا وقتي كه شما office را Uninstall يا Remove نكنيد، اين توابع همراه اكسل خواهند بود. براي نصب يك Add-In مراحل زير را دنبال كنيد. از منوي Tools گزينه Add-Ins… را انتخاب كنيد. با زدن گزينه Brower فايل Add-In را انتخاب كنيد. تا وقتي كه CheckBox در حالت فعال قرار دارد ، اين فايل با اكسل فراخواني مي‌شود و مي‌توان با تمامي توابع اين فايل كار كرد و بازدن كليد Alt+F11 مي‌توانيد وارد محيط ويژوال بيسيك شويد و فايل خود را با پسوند xla ببينيد.           از اينكه نظرات و تجربيات و ... را از طرف شما دريافت كنم خوشحال خواهم شد.   فرشيد ميداني

تا كنون نظري ثبت نشده است
ارسال نظر آزاد است، اما اگر قبلا در رویا بلاگ ثبت نام کرده اید می توانید ابتدا وارد شوید.