ساخت تابع در اکسل – آموزش ایجاد دستور سفارشی

۳۵۵۷ بازدید
آخرین به‌روزرسانی: ۲۵ اردیبهشت ۱۴۰۲
زمان مطالعه: ۱۵ دقیقه
ساخت تابع در اکسل – آموزش ایجاد دستور سفارشی

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

روش‌های مختلفی برای ساخت تابع در اکسل وجود دارند اما یکی از آن‌ها را در این مطلب بررسی می‌کنیم. چنانچه نیاز به یادگیری بیشتر درباره نرم‌افزار اکسل دارید، بهتر است مقاله «آموزش اکسل رایگان از صفر» را نیز مطالعه کنید.

چرا به ساخت تابع سفارشی در اکسل نیاز داریم؟

در حال حاضر بیش از 450 تابع مختلف در اکسل موجود هستند که به کمک آن‌ها می‌توانیم کارهای بسیار زیادی انجام دهیم. با این حال هنوز هم نمی‌شود تمام مشکلات را در اکسل به کمک توابع حل کرد، چون غیر ممکن است سازندگان این برنامه بتوانند تمام مشکلات و نیازها را پیش‌بینی کنند. به همین دلیل اکثر کاربران اکسل با یکی از مشکلاتی که در ادامه می‌بینیم مواجه شده و احساس نیاز به تابع جدید کرده‌اند:

  • همه داده‌ها را نمی‌شود با توابع استاندارد اکسل پردازش کرد (به عنوان مثال، تاریخ‌های قبل از سال 1900).
  • فرمول‌های توابع معمولا بسیار طولانی و پیچیده هستند. حفط کردن آن‌ها تقریبا غیرممکن است، نمی‌شود آن‌ها را به راحتی یاد گرفت و همچنین تغییر دادن آن‌ها برای سازگاری با شرایط جدید سخت انجام می‌شود.
  • نمی‌شود تمام کارها را به کمک توابع استاندارد انجام داد (مثلا نمی‌توانیم URL یک لینک را استخراج کنیم).
  • خودکار کردن کارهای رایج و تکراری استاندارد غیر ممکن است (مانند وارد کردن داده از یک برنامه حساب‌داری به صفحات اکسل، قالب‌بندی تاریخ‌ها و اعداد یا حذف ستون‌های غیر ضروری).

تابع سفارشی در اکسل چیست؟

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

چند روش مختلف برای ساخت تابع در اکسل وجود دارد که در ادامه با هم می‌بینیم:

  • استفاده از زبان برنامه نویسی Visual Basic در اکسل یا VBA که در این مقاله به توضیح آن پرداخته‌ایم.
  • استفاده از تابع کبیر LAMBADA که در Office 365 معرفی شده است.
  • استفاده از اسکریپت‌های آفیس که در حال حاضر فقط روی اکسل وب در دسترس هستند.

در تصویر زیر، تفاوت بین دو روش استخراج عدد، یکی با استفاده از فرمول و دیگری با تابع سفارشی ExtractNumber()  را با هم می‌بینیم:

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

چطور تابع سفارشی در اکسل بسازیم؟

قبل از هرچیزی باید محیط کدنویسی VBA را باز کنیم. البته توجه داشته باشید این برنامه در پنجره جدید باز می‌شود و صفحه جاری اکسل را نخواهد بست. راحت‌ترین راه برای باز کردن VBA استفاده از کلیدهای میانبر Alt+F11 روی کیبورد است. این کار ساده و سریع انجام می‌شود و نیازی به تنظیم مجدد گزینه‌های نوار ابزار اکسل ندارد. البته اگر در صفحه VBA هستید، با زدن این دو کلید می‌توانید صفحه را ببندید. سپس باید کد مربوط به تابع را در این پنجره وارد کنیم. در نهایت با بستن VBA به شیت اکسل برمی‌گردیم و از تابع خود استفاده می‌کنیم.

ماژول نویسی VBA در اکسل

بعد از باز کردن VBA باید ابتدا یک ماژول ایجاد کنیم تا مکانی برای نوشتن توابع داشته باشیم. برای انجام این کار، روی شیت مورد نظر در سمت راست پنجره VBA کلیک راست و گزینه Insert > Module را انتخاب می‌کنیم. سپس یک پنجره ماژول خالی باز می‌شود که می‌توانیم کدهای خود را در آن وارد کنیم.

قوانین ساخت تابع در اکسل

یک تابع سفارشی همیشه با عبارت «Function» شروع و با عبارت «End Function» تمام می‌شود. پس از عبارت «Function»، نام تابع می‌آید. این نام، عنوانی است که برای تابع خود در نظر می‌گیریم تا بتوانیم بعدا آن را شناسایی و استفاده کنیم.

در بین حروف نام نباید هیچ فاصله‌ای باشد. برای جدا کردن کلمات باید از زیر خط (_) استفاده کنیم. مثلا Count_Words. همچنین نام تابع سفارشی نباید با توابع استاندارد همسان باشد. در غیر این‌صورت، هنگام استفاده از این نام، همیشه تابع استاندارد اجرا خواهد شد. نام تابع سفارشی نمی‌تواند شبیه آدرس سلول‌های شیت باشد. مثلا نام ABC1234 نامعتبر است.

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

انتخاب نام تابع سفارشی در اکسل

بعد از نام، آرگومان‌های تابع هستند که معمولا داخل پرانتز قرار می‌گیرند. آرگومان‌ها داده‌هایی هستند که تابع با آن‌ها کار می‌کند. همچنین ممکن است بیش از یک آرگومان داشته باشیم که در این شرایط باید آن‌ها را به کمک علامت «کاما» (,) از هم جدا کنیم. اگر نمی‌خواهیم تابع، آرگومان داشته باشد (مانند توابع TODAY ،NOW یا RAND)، می‌توانیم یک تابع بدون آرگومان ایجاد کنیم. همچنین اگر از تابع سفارشی برای ذخیره ثابت‌ها (مانند عدد pi) استفاده می‌شود نیز احتیاجی به آرگومان نیست.

آرگومان‌های تابع سفارشی

بعد از آرگومان باید متغیرهایی را که تابع از آن‌ها استفاده می‌کند، مشخص کنیم. نوع این متغیرها می‌تواند هرچیزی باشد، عدد، متن، تاریخ یا آرایه. سپس چند دستور به زبان VBA می‌نویسیم که محاسبات مورد نظر را روی آرگومان‌ها انجام می‌دهند.

در پایان باید دستوری بنویسیم که مقدار نهایی را درون متغیری هم نام با تابع، بریزد. این مقدار به فرمولی که تابع سفارشی فراخوانی کرده است، برمی‌گردد. البته کدهای ساخت تابع در اکسل می‌توانند حاوی یادداشت باشند. این کار در یادآوری عملکرد قسمت‌های مختلف کد به ما کمک می‌کند. به شکلی که اگر در آینده قصد تغییرات داشته باشیم، به کمک آن‌ها کارمان بسیار ساده‌تر خواهد شد. به یاد داشته باشید که یادداشت در VBA با علامت «آپستروف» (') شروع می‌شود. این علامت به اکسل می‌گوید که هرچیزی بعد از آن نوشته شده را تا آخر خط، نادیده بگیرد.

ساخت تابع سفارشی در اکسل

در ادامه می‌خواهیم اولین تابع سفارشی را در اکسل بسازیم. در اولین مثال، تابعی می‌سازیم که تعداد کلمات موجود در یک محدوده از سلول‌ها را شمارش می‌کند. برای انجام این کار کد زیر را درون VBA خواهیم نوشت:

1Function CountWords(NumRange As Range) As Long
2  Dim rCell As Range, lCount As Long
3
4  For Each rCell In NumRange
5    lCount = lCount + _
6    Len (Trim(rCell)) - Len (Replace (Trim(rCell), "", "")) + 1
7  Next rCell
8  CountWords = lCount
9End Function
محیط کد نویسی VBA

همان‌طور که در قوانین ساخت تابع گفتیم، کد ما با عبارت Function شروع می‌شود. در ادامه توضیحات تابع را نوشته و آن را با عبارت End Function به پایان رسانده‌ایم. سپس داده‌هایی که تابع قرار است استفاده کند را درون پرانتز قرار داده‌ایم. در این قسمت، NumRange As Range یعنی آرگومان تابع سفارشی، محدوده‌ای از مقادیر است. همچنین این تابع تنها یک آرگومان را برمی‌گرداند که محدوده‌ای از سلول‌ها است.

 

در خط دوم کد، متغیرها را تعیین کرده‌ایم. در این قسمت، As Long مشخص می‌کند که نتیجه تابع CountWords در قالب اینتجر یا عددی باشد. دستور Dim نیز دو متغیر از تابع را مشخص می‌کند:

  • rCell متغیر مربوط به محدوده‌ای از سلول‌ها است که می‌خواهیم تعداد کلمات موجود در آن را بشماریم.
  • lCount نیز به عنوان یک متغیر عددی یا اینتجر است که حاوی تعداد کلمات خواهد بود.

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

در داخل این حلقه، عملیاتی که برای شمردن تعداد کلمات است، به مقدار هر سلول، تکرار می‌شود:

1Len (Trim(rCell)) - Len(Replace(Trim(rCell), "", "")) + 1

همان‌طور که می‌بینید این یک فرمول ساده اکسل است که از توابع متنی مانند Len ،TRIM و REPLACE استفاده می‌کند. اما به جای ارجاع سلولی در آن از متغیر محدوده rCell استفاده کرده‌ایم. به این شکل، تعداد کلمات موجود در هر سلول از محدوده را به ترتیب می‌شماریم.

اعداد شمارش شده نیز جمع شده و در متغیر lCount ذخیره می‌شوند:

1lCount = lCount + Len (Trim(rCell)) - Len(Replace(Trim(rCell), "", "")) + 1

زمانی که حلقه تمام شود، مقدار متغیر درون تابع ریخته می‌شود:

1CountWords = lCount
2

در نهایت تابع، نتیجه این متغیر را که تعداد کلمات است، به سلول مورد نظر در صفحه اکسل می‌فرستد و نمایش می‌دهد. این خط از کد تضمین می‌کند که تابع، مقدار متغیر lCount را به سلولی که از آن فراخوانی شده است، برگرداند.

انتهای تابع Countword

همان‌طور که دیدید، کار چندان سختی نبود. برای ذخیره کردن تابع، دکمه Save را در پنجره VBA فشار می‌دهیم. سپس می‌توانیم پنجره VBA را ببندیم، برای این کار یا کلیدهای Alt+Q  یا کلیدهای Alt+F11 را روی کیبورد می‌زنیم.

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

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

برای اعمال کردن فرمول سفارشی در اکسل، دو گزینه داریم:

  • روی دکمه fx که در نوار فرمول است کلیک می‌کنیم. در این قسمت، بین فهرست دسته‌بندی توابع، گروه جدید User Defind را خواهیم دید که مربوط به توابع سفارشی است. در این دسته‌بندی نیز می‌توانیم تابع سفارشی جدید CountWords را ببینیم.
لیست دسته بندی توابع در اکسل
  • همچنین می‌توانیم تابع را به سادگی درون یک سلول بنویسیم، همان‌طور که توابع استاندارد را می‌نویسم. وقتی شروع به نوشتن کنیم، اکسل لیستی از توابعی که متناسب با نوشته ما هستند نشان می‌دهد که تابع سفارشی نیز در بین آن‌ها است. در مثال زیر، وقتی عبارت «Coun=» را نوشتیم، اکسل لیستی از توابع مشابه را نشان داده است که تابع CountWord نیز بین آن‌ها دیده می‌شود.
فرمول تابع سفارشی در اکسل

در ادامه می‌بینیم تابع سفارشی چطور عمل می‌کند. برای این کار آن را درون یک سلول می‌نویسیم:

1= CountWords(A1: A4)

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

ساخت تابع سفارشی در اکسل

انواع مختلف تابع سفارشی در ورد

حالا می‌خواهیم انواع مختلف تابع سفارشی در ورد را بر حسب آرگومان‌هایی که استفاده می‌کنند و نتیجه خروجی، بررسی کنیم.

ساخت تابع سفارشی بدون آرگومان

اکسل چند تابع استاندارد دارد که نیازی به آرگومان ندارند (مانند Rand ،TODAY و NOW).به عنوان مثال، تابع RAND یک عدد تصادفی بین «0» و »1» تولید می‌کند. تابع TODAY تاریخ روز را نشان می‌دهد و هنگام انجام محاسبات تاریخ در اکسل به کار می‌رود. همان‌طور که دیدید، نیازی نیست هیچ مقداری به این توابع اختصاص بدهیم.

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

1Function SheetName() as String
2
3  Application.Volatile
4
5  SheetName = Application.Caller.Worksheet.Name
6
7End Function

یا می‌توانیم به جای آن از کد زیر استفاده کنیم:

1SheetName = ActiveSheet.Name

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

فرمول بالا، نتیجه تابع را به عنوان یک داده رشته‌ای تعریف می‌کند (چون نتیجه مورد نظر، نام فایل و متن است). اگر نوع داده را مشخص نکرده بودیم، اکسل خودش آن را تعیین می‌کرد.

استفاده از تابع سفارشی Sheetname

ساخت تابع سفارشی با یک آرگومان

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

1Function ReturnLastWord(The_Text As String)
2  Dim stLastWord As String
3
4  'Extracts the LAST word from a text string
5  stLastWord = StrReverse(The_Text)
6  stLastWord = Left(stLastWord, InStr(1, stLastWord, " ", vbTextCompare))
7  ReturnLastWord = StrReverse(Trim(stLastWord))
8End Function

در این فرمول، The_Text مقدار موجود در سلول انتخاب شده است. ما تعیین می‌کنیم که این مقدار باید متنی باشد (As String ).

برای انجام این کار ابتدا تابع StrReverse متن را به صورت برعکس درآورده و به خروجی می‌فرستد. سپس تابع InStr مکان اولین فاصله بین حروف متن برعکس را تعیین می‌کند. در ادامه با استفاده از تابع Left تمام حروفی که به اولین فاصله ختم می‌شوند را استخراج و سپس با استفاده از Trim فاصله‌ها را حذف می‌کنیم. در نهایت دوباره ترتیب متن را با تابع StrReverse تغییر می‌دهیم تا به شکل اولیه دربیاید و حالا آخرین کلمه متن را در اختیار داریم.

کاربرد تابع ReturnLastWord

توجه کنید از آن‌جایی که این تابع تنها مقدار یک سلول را می‌گیرد، در این جا نیازی به استفاده از Application.Volatile نداریم. به محض اینکه آرگومان عوض شود، تابع به طور خودکار به‌روزرسانی خواهد شد.

استفاده از آرایه به عنوان آرگومان تابع سفارشی

بسیاری از توابع اکسل، از آرایه به عنوان آرگومان استفاده می‌کنند. مثل تابع‌های SUM ،SUMIF و SUMPRODUCT. این شرایط را در مثال اول که تابعی برای شمارش کلمه‌های یک محدوده ساختیم، بررسی کردیم. در ادامه مثال دیگری نیز در این زمینه می‌بینیم.

کد زیر تمام اعداد زوج موجود در یک محدوده را با هم جمع می‌کند:

1Function SumEven(NumRange as Range)
2  Dim RngCell As Range
3
4  For Each RngCell In NumRange
5    If IsNumeric(RngCell.Value) Then
6      If RngCell.Value Mod 2 = 0 Then
7        Result = Result + RngCell.Value
8      End If
9    End If
10  Next RngCell
11  SumEven = Result
12End Function

آرگومان NumRange برای تعیین محدوده نوشته شده است. این یعنی تابع سفارشی، از آرایه‌ای از داده‌ها استفاده می‌کند. لازم به ذکر است که در این قسمت از متغیر Variant نیز می‌شود به شکل زیر استفاده کرد:

1Function SumEven(NumRange as Variant)

نوع Variant یک نگهدارنده بدون بعد (non-dimensional) برای ذخیره داده‌ها فراهم می‌کند. چنین متغیری می‌تواند هر نوع داده‌ای که در VBA مجاز است را در خود ذخیره کند، از جمله داده‌های عددی، متنی، تاریخ و آرایه‌ها. به علاوه، یک متغیر مشابه در یک برنامه می‌تواند در زمان‌های مختلف، داده‌هایی از انواع مختلف را ذخیره کند. در چنین شرایطی خود اکسل تشخیص می‌دهد که کدام نوع داده‌ای وارد تابع شده است.

این کد همچنین آرگومانی با نام For Each   دارد که هر سلول را گرفته و بررسی می‌کند که آیا مقدار عددی دارد یا خیر. اگر مقدار عددی نداشت، اتفاقی نخواهد افتاد و به بررسی سلول بعدی مشغول می‌شود. اما اگر عددی درون سلول باشد، بررسی می‌کند که فرد است یا زوج (با استفاده از تابع Mod ).

سپس تمام عددهای زوج با هم جمع می‌شوند و درون متغیر Result قرار می‌گیرند. وقتی حلقه تمام شد، مقدار متغیر Result درون متغیر SumEven ریخته می‌شود و به تابع برمی‌گردد تا در خروجی نشان داده شود.

جمع اعداد زوح در اکسل

ساخت تابع سفارشی با چند آرگومان

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

کد زیر تابعی را می‌سازد که بزرگ‌ترین عدد یک محدوده را پیدا خواهد کرد و جزو توابع شمارشی و جمع بندی پیشرفته در اکسل محسوب می‌شود:

1Function GetMaxBetween(rngCells As Range, MinNum, MaxNum)
2  Dim NumRange As Range
3  Dim vMax
4  Dim arrNums()
5  Dim i As Integer
6
7  ReDim arrNums(rngCells.Count)
8  For Each NumRange In rngCells
9    vMax = NumRange
10    Select Case vMax
11      Case MinNum + 0.01 To MaxNum - 0.01
12        arrNums(i) = vMax
13        i = i + 1
14      Case Else
15        GetMaxBetween = 0
16      End Select
17  Next NumRange
18
19  GetMaxBetween = WorksheetFunction.Max(arrNums)
20
21End Function
ساخت تابع در اکسل برای یافتن بزرگ ترین و کوچک ترین عدد

این تابع سه آرگومان دارد: محدوده‌ای از مقادیر، یک کران پایین برای محدوده عددی و یک کران بالا برای محدوده عددی. اولین آرگومان rngCells به عنوان محدوده است. این آرگومان محدوده‌ای که می‌خواهیم بزرگ‌ترین عدد را در آن بیابیم، تعیین می‌کند. آرگومان‌های دوم و سوم (MinNum و MaxNum ) بدون اینکه نوع خاصی داشته باشند، مشخص شده‌اند. این یعنی نوع داده Variant به طور پیش فرض برای آن‌ها در نظر گرفته خواهد شد.

VBA از شش نوع داده عددی متفاوت استفاده می‌کند. مشخص کردن تنها یکی از آن‌ها، به معنی محدود کردن استفاده از تابع است. به همین دلیل بهتر است خود اکسل نوع داده را تعیین کند.

For Each حلقه‌ای برای تمام مقادیر موجود در محدوده انتخابی ایجاد می‌کند. اعدادی که در محدوده حداکثر (Maximum) تا حداقل (Minimum) هستند، در آرایه‌ای خاص با نام arrNums نوشته می‌شوند. سپس با استفاده از تابع استاندارد MAX، بزرگ‌ترین عدد این محدوده پیدا می‌شود. به همین ترتیب، برای استفاده از توابع منطقی (Logical Functions) در اکسل نیز می‌توانیم کد مخصوصی بنویسیم و تابع سفارشی بسازیم.

ساخت تابع سفارشی با آرگومان‌های اختیاری و ضروری

برای درک بهتر آرگومان اختیاری، تابع VLOOKUP را در نظر بگیرید. چهارمین آرگومان این تابع (range_lookup) اختیاری است. اگر یکی از آرگومان‌های ضروری را حذف کنیم، تابع خطا می‌دهد. اما اگر آرگومان اختیاری را ننویسیم، تابع مانند قبل به درستی کار خواهد کرد.

با این حال، آرگومان‌های اختیاری، چندان بی‌فایده نیز نیستند. بلکه این امکان را به ما می‌دهند که گزینه‌های مختلفی برای محاسبات خود انتخاب کنیم.

برای مثال اگر در تابع VLOOKUP، آرگومان چهارم را ننویسیم، یک جستجوی تقریبی انجام خواهد شد. در صورتی که اگر به جای آرگومان [range_lookup]، عبارت False یا عدد «0» را وارد کنیم، جستجو به صورت دقیق و تطبیق کامل انجام می‌شود.

اگر می‌خواهیم تابع سفارشی، حداقل یک آرگومان اختیاری داشته باشد، باید آن را در ابتدا بنویسیم. برای اختیاری کردن یک آرگومان کافی است عبارت «Optional» را قبل از آن وارد کنیم. در ادامه مثالی از یک تابع سفارشی همراه با آرگومان اختیاری را می‌بینیم:

Function GetText(textCell As Range, Optional CaseText = False) As String
  Dim StringLength As Integer
  Dim Result As String

  StringLength = Len(textCell)

  For i = 1 To StringLength
    If Not (IsNumeric(Mid(textCell, i, 1))) Then Result = Result & Mid(textCell, i, 1)
  Next i

  If CaseText = True Then
    Result = UCase(Result)

  GetText = Result
End Function

این تابع سفارشی، متن را از درون سلول بازیابی می‌کند. در این تابع، عبارت Optional CaseText = False یعنی آرگومان CaseText اختیاری است و به طور پیش فرض مقدار آن روی FALSE تنظیم شده.

اگر آرگومان اختیاری CaseText روی TRUE تنظیم شود، نتیجه نهایی با حروف بزرگ انگلیسی نشان داده می‌شود. از طرفی اگر این آرگومان روی FALSE باشد یا حذف شود، نتیجه به همان شکلی که هست باقی می‌ماند و حروف آن تغییری نمی‌کنند.

شاید برایتان سوال باشد که آیا می‌شود تنها از آرگومان‌های اختیاری در توابع سفارشی استفاده کرد؟ در ادامه به این سوال پاسخ داده‌ایم.

آیا ساخت تابع سفارشی فقط با آرگومان‌های اختیاری امکان‌پذیر است؟

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

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

1Function UserName(Optional Uppercase As Variant)
2
3  If IsMissing(Uppercase) Then Uppercase = False
4
5  UserName = Application.UserName
6
7  If Uppercase Then UserName = UCase(UserName)
8
9End Function

همان‌طور که می‌بینید، این تابع فقط یک آرگومان Uppercase دارد که آن هم اختیاری است.

اگر عبارت FALSE به جای این تابع قرار بگیرد یا به طور کلی حذف شود، نام کاربری، بدون تغییر باقی خواهد ماند. در مقابل اگر عبارت TRUE به جای این آرگومان نوشته شود، نام کاربری با حروف بزرگ انگلیسی نوشته می‌شود (با استفاده از تابع UCase ). اولین دستور، حاوی تابع IsMissing است که حضور یک آرگومان را تشخیص می‌دهد. اگر هیچ آرگومانی نوشته نشده باشد، این دستور، متغیر Uppercase را روی False تنظیم می‌کند.

در ادامه نگاهی داریم به نسخه دیگری از این تابع:

1Function UserName(Optional Uppercase = False)
2
3  UserName = Application.UserName
4
5  If Uppercase = True Then
6    UserName = UCase(UserName)
7
8End Function

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

ساخت تابع سفارشی در حالی که مقدار برگشتی یک آرایه است

VBA یک تابع بسیار کاربردی به نام Array دارد. این تابع یک نوع داده متفاوت را در خروجی نشان می‌دهد که در واقع یک آرایه است (به عبارت ساده‌تر، چندین مقدار را برمی‌گرداند). توابع سفارشی که یک آرایه را در خروجی نشان می‌دهند، در هنگام ذخیره آرایه‌های مقادیر، بسیار کاربردی هستند. به عنوان مثال تابع ()Months، آرایه‌ای از نام ماه‌های میلادی را نشان می‌دهد.

1Function Months() As Variant
2  Months = Array ("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
3End Function

توجه داشته باشید که این تابع، داده‌ها را به صورت افقی (در یک ردیف) در خروجی نشان می‌دهد.

اما اگر نیاز به آرایه عمودی داشته باشیم چه؟‌ قبل‌تر گفتیم که می‌شود توابع سفارشی اکسل را در همراه با توابع استاندارد در فرمول‌ها استفاده کرد. با این وجود، از تابع ()Months به عنوان آرگومان تابع TRANSPOSE استفاده می‌کنیم:

1= TRANSPOSE(Months())
ساده سازی فرمول های اکسل با تابع سفارشی

به این ترتیب می‌توانیم با ساخت تابع سفارشی در اکسل داده‌ها را سریع‌تر از حالت معمولی وارد جدول‌ها کنیم. به عنوان مثال هنگام ساخت گزارش فروش سالانه، نیازی نیست نام ماه‌ها را به طور دستی بنویسیم.

علاوه بر این می‌توانیم نام ماه را با نوشتن شماره آن، دریافت کنیم. به عنوان مثال، در تصویر زیر، سلول A1 حاوی شماره ماه است. می‌خواهیم با استفاده از فرمول، نام این ماه را به دست بیاوریم:

1= INDEX (Months(), 1, A1)

نسخه جایگزین این فرمول به شکل زیر است:

1=INDEX({"January", "February", "March", "April", "May", "June "," July "," August "," September "," October "," November "," December "}, 1, A1)

نتیجه می‌گیریم که تابع سفارشی با آرایه، کار یک فرمول اکسل را بسیار ساده‌تر می‌کند.

سخن نهایی

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

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

بر اساس رای ۱۶ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
ablebits
۲ دیدگاه برای «ساخت تابع در اکسل – آموزش ایجاد دستور سفارشی»

سلام
این تابع رو چطوری میتونم بنویسم؟

فرض کنید 4 سلول داریم a1 , a2 , a3 , a4
اگر a1 = 1 شد و a2 = 0 شد و a3 = 0 شد و a4 = 0 شد
a5 = 1000
درغیراینصورت
اگر a1 = 1 شد و a2 = 0 شد و a3 = 0 شد و a4 = 1 شد
a5 = 1000/2

درغیر اینصورت
اگر a1 = 0 شد و a2 = 1 شد و a3 = 0 شد و a4 = 0 شد
a5 = 2000
درغیراینصورت اگر
اگر a1 = 0 شد و a2 = 1 شد و a3 = 0 شد و a4 = 1 شد
a5 = 2000/2

درغیر اینصورت
اگر a1 = 0 شد و a2 = 0 شد و a3 = 1 شد و a4 = 0 شد
a5 = 3000
درغیراینصورت اگر
اگر a1 = 0 شد و a2 = 0 شد و a3 = 1 شد و a4 = 1 شد
a5 = 3000/2

و چطوری میتونم این رو به هر ردیف نسبت بدم؟
ممنون

بسیار عالی هستید

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *