کاربرد توابع MID-FIND-CELL

ترفند استخراج نام شیت ها در اکسل

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

تذکر: فرم فوق یک فرم حقوق و دستمزد می باشد که تماماً در محیط اکسل طراحی شده است.

کاربرد توابع MID-FIND-CELL

کاربرد توابع MID-FIND-CELL

من در این فایل اسم شیت ها را طوری انتخاب کرده ام که از آنها به عنوان کد پرسنلی استفاده کنم. مانند 101-102 و زمانی که نام شیت را تغییر می دهم همین نام به جای کد پرسنلی قرار می گیرد.

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

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

MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,255)

با نوشتن فرمول بالا در یک سلول اسم کاربرگ جاری در سلول درج می گردد.

تذکر: فایل باید قبلاً ذخیره شده باشد تا بتوان از فرمول فوق استفاده کرد. در فایل جدیدی که هنوز ذخیره نکرده اید و در حال کار بر روی آن هستید، فرمول کار نخواهد کرد.

این فرمول چگونه کار می کند؟

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

MID –CELL –FIND

ابتدا به صورت خلاصه هر کدام از فرمول های به کار رفته را توضیح می دهیم.

تابع MID

توابع Mid در دسته توابع متنی اکسل قرار دارد و از میان یک رشته تعدادی کاراکتر دلخواه را انتخاب می کند.

ساختار این تابع به شرح ذیل می باشد:

Mid(text,start_num,num chars)

text رشته ای است که قصد داریم تعدادی از کاراکترهای آن را استخراج کنیم

Start_num محل شروع جداسازی را مشخص می کند

Num_chars تعداد کاراکتری است که قصد داریم از رشته جدا کنیم

برای مطالعه بیشتر در مورد تابع Mid به این مقاله مراجعه کنید

تابع CELL

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

با نوشتن تابع Cell می بینید که آرگومان های زیرمجموعه این تابع ظاهر می شود. این تابع دارای 12 آرگومان است که در این مقاله ما از آرگومان Filename استفاده می کنیم.

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

تابع را به شکل زیر می نویسیم:

=CELL(“Filename”)

نتیجه این تابع در سیستم من به شکل زیر ظاهر می شود:

C:\Users\Electrical\Desktop\[22.xlsx]test

این خروجی آدرس مسیر ذخیره فایل (C:\Users\Electrical\Desktop\) ، نام فایل ( [22.xlsx]) و در انتها نام کاربرگ جاری(test) را نمایش می دهد.

مسیر ذخیره خروجی فوق برای سیستم شما متفاوت خواهد بود.

نکته مهم و قابل توجه در متن خروجی تابع(“filename”)  CELL، محل علامت “[”   می باشد که همیشه قبل از نام شیت قرار می گیرد.

با استفاده از محل قرارگیری علامت “[” که همیشه در ابتدای نام شیت می باشد، می توانیم با استفاده از تابع Find محل شروع نام شیت را پیدا کنیم.

تابع FIND

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

شکل کلی تابع Find به صورت زیر است:

Find(find_text,within_text,[start_num])

عبارت find_text رشته ای است که باید آن را جستجو کنیم

عبارت within_text رشته متنی است (سلولی) که عمل جستجو را در آن انجام می دهیم.

عبارت [start_num] آرگومانی اختیاری می باشد. اگر آن را خالی بگذاریم جستجو از ابتدای متن شروع می شود و اگر عددی مانند 3 را قرار دهیم جستجو از کاراکتر سوم به بعد شروع می شود.

 

=CELL(“Filename”)  اجرای تابع

C:\Users\Electrical\Desktop\[22.xlsx]test     خروجی تابع

در این مثال باید محل درج علامت “]  ” را در خروجی تابع پیدا کنیم  تا بعداً در تابع Mid به جای Start_num از این مقدار استفاده کنیم.

با فرض اینکه خروجی تابع Cell در سلول A1 قرار داشته باشد تابع Find را به شکل زیر استفاده می کنیم

=Find(“]”,A1)

نتیجه تابع فوق عدد 37 می باشد و به این معنی است که کاراکتر ” ”] در جایگاه سی و هفتم رشته متنی واقع در سلول A1 قرار دارد.

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

 

MID(A1,37+1,255)

چرا به 37 عدد 1 را اضافه کردیم؟

چون ما نمی خواهیم در نام فایل شیت علامت “]” قرار داشته باشد و تابع Find محل علامت “]” را پیدا می کند و ما باید یک کاراکتر اضافه کنیم تا به اولین حرف شروع نام شیت برسیم

دلیل اضافه کردن عدد 255 در انتهای فرمول نیز حداکثر طول نام شیت می باشد (چون نام شیت حداقل 1 و حداکثر 255 کاراکتر می باشد)

در فرمول بالا اگر به جای A1 مقدار آن یعنی (“CELL(“filename را قررار دهیم و به جای 37 معادل آن یعنی FIND(“]”,CELL(“filename”)) را قرار دهیم به فرمول زیر می رسیم که همان فرمولی است که در ابتدا معرفی کردیم.

 

MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,255)

در واقع ما برای استخراج نام شیت از تابع MID اسفاده می کنیم که ساختار آن را قبلاً توضیح دادیم:

 

Mid(text,start_num,num chars)

text رشته ای است که قصد داریم تعدادی از کاراکترهای آن را استخراج کنیم (در این مثال text همان خروجی تابع CELL(“filename”) می باشد.

Start_num محل شروع جداسازی را مشخص می کند (که با دستور FIND(“]”,CELL(“filename”))+1 بدست می آید )

Num_chars تعداد کاراکتری است که قصد داریم از رشته جدا کنیم.  در اینجا تعداد کاراکتر برابر نام شیت است و چون نام شیت متغییر است پس ما از حداکثر کارکتر یعنی 255 استفاده می کنیم.  (تعداد کاراکتر اسم شیت حداکثر 255 می باشد و با این عدد می توان نام شیتی هم که دارای 255 کاراکتر است را استخراج نمود)

برای مطالعه بیشتر در مورد تابه Cell به این مقاله مراجعه فرمایید