چگونه در اکسل توابع جدید بنویسم

[B@RBOD]

چگونه در اکسل توابع جدید بنویسم

[B@RBOD]

مقدمه

دقیقا یادم است که روزی قرار ملاقاتی با یکی از اساتید اکسل داشتم ، ایشان استاد دانشگاه بودند و گویا دوتا هم مدرک دکتری . تقریبا یادم نیست که چه صحبتهایی شد اما این را خوب یادم است که بعد از آن ملاقات بود که می‌خواستم خودم در اکسل یک تابع بنویسم ، خوب از کجا می‌دانستم که می‌شود اینکار را کرد ؟
فکر کنم که روزی فایلی را از اینترنت گرفته بودم و بعد از نصب آن وقتی به Insert à function رفته بودم گزینه‌ای به نام User Function را دیدم و همان موقع متوجه شدم که می توان در اکسل توابع جدید را اضافه کرد.

[B@RBOD]

در دبیرستان ساعتها وقت ما را همین کلمه گرفت و انصافاٌ من با کامپیوتر بود که تازه فهمیدم تابع چیست. در واقع تابع یک عملگری است که چیزی را می‌گیرد و روی آن کاری را انجام می‌دهد و بعد چیز دیگری که خروجی می‌گوییم را به ما می‌دهد.
مثلا همین تابع SUM را در نظر بگیرید ، چند تا عدد می‌گیرد و جمع آنها را به ما می‌دهد.
در زیر چند اسلاید که مربوط به توابع است را می‌بینید.

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

البته این سوال ممکن است به ذهن شما متبادر شود که چرا باید تابع جدیدی اضافه کرد.
شاید دلایل زیر بتواند گوشه‌ای از ارزش تابع را برای ما بیان کند:
· جلوگیری از کارهای تکراری در اکسل
· انجام محاسبات پیچیده
· دسترسی به کلیه امکانات یک زبان برنامه نویسی مانند ویژوال بیسیک
· به اشتراک گذاشتن توابع با سایر کاربران
· استفاده سریعتر از نرم افزار
· جلوگیری از اشتباهات کاربران

[B@RBOD]

برای شروع بهتر است کمی در خصوص ویژوال بیسیک بدانیم . vb یک زبان برنامه نویسی بسیار متداول است. برای اینکه یک تابع جدید نوشته شود لازم است که کمی با برنامه نویسی با این زبان آشنا باشیم. برای این منظور پیشنهاد می‌کنم که نرم افزار vb را تهیه کنید و بعد از آن هم چند cd آموزشی و یک کتاب . با کمی تمرین با اصول ابتدایی این زبان آشنا خواهید شد و قول می‌دهم کار بسیار ساده تر از آنچه فکر می‌کنید باشد.
لازم به ذکر است که نسخه جدید نرم افزار vb به نام vb.net نیز وجود دارد که امکان استفاده آن در office 12 که نسخه بعدی آفیس است گنجانده شده و بد نیست بدانیم که vb.net در واقع قابلیتهای زبان برنامه نویسی c# را دارد.

شما برای استفاده از vb در اکسل نیاز ندارید که نرم افزار visual basic را نصب کنید ، همراه با نصب آفیس خود این نرم افزار نیز نصب می‌شود.

[B@RBOD]

ابتدا بایستی وارد محیط VB شویم. برای اینکار چندین راه وجود دارد که عبارتند از:
· زدن کلید ALT+F11
·از منوها : Tools à Macro à Visual Basic Editor
· از Toolbar :



محیط ویژوال بیسک

[B@RBOD]

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

[B@RBOD]

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

Private Function Test(Num As Integer) as Double

End Function

نام تابع ما test است و عبارت داخل پرانتز می‌گوید که این تابع یک ورودی دارد که نام آن ورودی Num است و integer بیانگر آن است که این ورودی عددی صحیح است . (-32,768 تا 32,767 )
خروجی تابع از نوع double است و البته گذاشتن آن در همه موارد الزامی نیست ، گرچه بهتر است که مشخص شود. (برای اطلاع بیشتر به کتابهای برنامه نویسی مراجعه کنید.)
عبارت Private Function نشانگر شروع تابع و End Function برای پایان تابع است.

[B@RBOD]

فرض کنید می‌خواهیم تابعی بنویسیم که یک عدد را بگیرد و آنرا در 10 ضرب کند!
اول باید تصمیم بگیریم که اسم این تابع را چه بگذاریم ، در حقیقت این اسم همان کلمه‌ای است که در اکسل برای استفاده از این تابع استفاده خواهیم کرد.
خوب اسم آنرا Test می‌گذاریم و می‌دانیم که این تابع باید یک ورودی داشته باشد و خوب چون به تازگی با نوع عدد Integer آشنا شدیم (عدد صحیح) نوع این ورودی را هم Integer می‌گذاریم.
باید نامی برای این ورودی در نظر بگیریم ، این نام نباید یک نام آشنا ! برای VB باشد و بهتر است نامی با مسما در نظر بگیریم ، اینجا اسم این ورودی را Num می‌گذاریم.
پس در ماژول خود خواهیم نوشت :

Private Function Test(Num As Integer)

Test = Num * 10

End Function

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

[B@RBOD]

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

= test(8)

= test(A1)

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

[B@RBOD]

می‌خواهیم تابعی بنویسیم که شماره رنگ یک سلول (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")