تابع Mid و چند کاربرد در اکسل

تابع Mid در اکسل

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

 =Mid(Text, Start_Num, Num_Chars)

Text: سلول یا رشته ای که میخوایم قسمتی از اون رو جدا کنیم.

Start_Num: عددی که تعیین میکنه جدا کردن از کدوم کاراکتر شروع بشه.

Num_Char: عددی که تعداد کاراکتری که میخوایم جدا کنیم رو تعیین میکنه.

همونطور که مشخصه (هیچ آرگومانی در براکت [ ] قرار نگرفته)، هر سه آرگومان قطعی هستن و نمیتونیم آرگومانی رو حذف کنیم.

مثلا فرض کنیم میخوایم در رشته تاریخی (شمسی) زیر، ماه رو جدا کنیم.(توجه داشته باشیم که این رشته، متنی هست و فقط ظاهر تاریخ داره) برای این کار باید از کاراکتر ششم (۱۳۹۸/ پنج کاراکتر هست و ۰، میشه ششمین کاراکتر) به تعداد دو کاراکتر جدا کنیم. یعنی ۰۹. (شکل ۱)

شکل ۱- تابع Mid- جدا کردن ماه از یک رشته تاریخی شمسی

برای جدا کردن ماه در تاریخ میلادی، این مقاله رو مطالعه کنید.

۵ نکته ای که باید راجع به تابع Mid بدونیم:

1. تابع Mid همیشه خروجی از جنس متن داره. حتی اگر این خروجی ظاهر عددی داشته باشه، متن هست و قابل محاسبه نیست. مثلا در مثال بالا، خروجی تابع ۰۹ هست که با توجه به اینکه صفر پشت عدد معنی نداره و هیچ وقت نشون داده نمیشه، مشخصه که جنس متنی داره.

برا یاینکه خاصیت عددی به خروجی تابع برگرده باید خروجی تابع Mid رو داخل یک تابع Value قرار بدیم. مطابق الگوی زیر:

=Value( Mid (…..))

2. اگر آرگومان Start_Num بزرگتر از تعداد کل کاراکترهای رشته مورد نظر باشه، خروجی تابع، خالی “” خواهد بود.
3. اگر آرگومان Start_Num کمتر از ۱ باشه، خروجی خطای #Value! خواهد بود.
4. اگر Num_Chars عدد منفی باشه، خروجی تابع خطای #Value! خوهد بود و اگر این آرگومان صفر باشه، خروجی تابع، خالی “” خواهد بود.
5. اگر جمع Num_Chars و Start_Num از تعداد کل کاراکترهای موجود بیشتر باشه، خروجی تابع برابر است با کاراکترهای بعد از Start_Num به بعد. مثلا در همون رشته تاریخ شمسی ۱۵/۰۹/۱۳۹۸ فرمول مطابق شکل ۲ مینویسیم. تعداد کل کاراکترهای موجود در سلول ۱۰ تاست. جمع آرگومان دوم و سوم برابر است با ۱۱. پس خروجی تابع کل کاراکترهای موجود از ششمین کارامتر به بعد خواهد بود. یعنی ۱۵/۰۹.

شکل ۲- تابع Mid و نکات مهم

چند مثال کاربردی برای آشنایی بیشتر

یکی از مثال های معروف و کاربردی این تابع رو میتونید در مقاله مربوط به جدا کردن متن از عدد مشاهده کنید.

مثال ۱ – تجزیه کاراکترهای موجود در یک سلول

فرض کنید میخواهیم کاراکترهای موجود در یک رشته متنی رو تجزیه کنیم که بعدا برای مسائل مختلفی ازش استفاده کنیم. برا یاین کار باید تابعی بنویسیم که با درگ کردن، کاراکترها رو یکی یکی تفکیک کنه و جلوی هر سلول بنویسه.

فرمولی که میتونه اولین کاراکتر از یک رشته رو جدا کنه هم میتونه تابع Left باشه و هم تابع Mid. اینجا از تابع Mid استفاده میکنیم که برای بقیه هم قابل استفاده باشه. به فرمول زیر دقت کنید:

=Mid (A5 , ۱ , ۱)

این تابع میاد داده موجود در سلول A5 رو از اولین کاراکتر به تعداد یکی جدا میکنه. حالا میخوایم درگ کنیم و از دومین کاراکتر، یکی جدا کنیم و همینطور الی آخر. پس بجای آرگومان Start_Num از تابعی استفاده میکنیم که با درگ کردن به سمت راست، یکی یکی اضافه بشه. برای این کار از تابع Column استفاده میکنیم. یعنی:

=Mid ($A5 , Column(A1) , ۱)

شکل ۳- تابع Mid – تفکیک کاراکترهای موجود در یک رشته متنی

همونطور که در شکل ۳ نمایش داده شده، تفاوتی بین حروف فارسی و انگلیسی وجود نداره و فقط وجود یک کاراکتر مهمه. کاراکترهای فارسی، انگلیسی، عدد، فاصله و …

مثال ۲- تغییر الگوی یک رشته متنی

فرض کنیم میخواهیم الگوی یک رشته متنی، مثلا یک کد محصول رو تغییر بدیم. مثلا میخواهیم کد Asd123456 رو به Asd/1/2/3/4/56 الگو تغییر بدیم. همونطور که در الگوی جدید مشاهده میکنیم، سه رقم اول جدا شده و بعد کاراکترها یکی یکی و در انتها نیز دو رقم آخر جدا شده اند و بین همه اونها / قرار گرفته.

این مسئله رو میخوایم با تابع Mid حل کنیم:

برای حل این مسئله اول باید تفکیک ها رو مطابق الگو انجام بدیم و بعد با استفاده از توابع چسباندن اطلاعات، / رو بین اجزای جدا شده اضافه کنیم.

در نسخه ۲۰۱۹ آفیس توابع جدیدی اضافه شدند که از بین اونا میتونیم از تابع Textjoin برای چسباندن اطلاعات استفاده کنیم. برای این کار کافیه که اول تجزیه ها رو انجام بدیم. در شکل ۴ مراحل انجام کار نمایش داده شده.

=Textjoin (“/” , True , C2:C7)

شکل ۴ – تابع Mid – تغییر الگوی یک رشته متنی

در صورتیکه از ورژن های قبلی اکسل استفاده کنیم، باید بجای Textjoin یکی از روش های چسباندن اطلاعات رو انتخاب کنیم و استفاده کنیم.

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

=Textjoin (“/”, True , Mid(A2,1,3) , Mid(A2,4,1) , Mid(A2,5,1) , Mid(A2,6,1) , Mid(A2,7,1) , Mid(A2,8,2) )

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

مثال ۳- جدا کردن ارقام موجود در رشته متنی

فرض کنید شرح های متفاوتی داریم که مبالغ مختلفی رو شامل می شوند. حالا میخواهیم مقادیر عددی رو جدا کنیم. (دقت کنید که اعدادی که مبلغ هستند رو میخوایم و اعداد مربوط به شماره فاکتور رو نیازی نداریم).

برای حل این قبیل مسائل اول باید الگوی مناسبی بین داده ها پیدا کنیم تا بتونیم فرمول متناسب با الگوی داده ها بنویسیم. بین داده های موجود، الگویی که میبینیم اینه که مبالغ مورد نیاز همگی , جدا کننده سه رقم دارند. پس میتونیم روی پیدا کردن , حساب کنیم و بگیم هر قسمت از رشته متنی که شامل , هست رو نمایش بده.

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

=Trim(Mid(Substitute(A2,” “,Rept(” “,۹۹)),Max(1,Find(“$”,Substitute(A2,” “,Rept(” “,۹۹)))-۵۰),۹۹))

شکل ۵ – تابع Mid – جدا کردن اعداد مبالغ موجود در یک رشته متنی

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

شکل ۶- تابع Mid – جدا کردن یک کلمه بخصوص

میدونیم که تابع Substitute یک کاراکتر رو با کاراکتر دیگه ای جایگزین میکنه و تابع Find مکان کاراکتری که جستجو میکنیم رو میده.

• تابع Substitute(A1,” “,Rept(” “,۹۹)) فاصله های موجود در رشته متنی رو با ۹۹ تا Space جایگزین میکنه.
• تابع Find(“,”,Substitute(A1,” “,Rept(” “,۹۹))) مکان “,” یعنی چیزی که داریم جستجو میکنیم رو تعیین میکنه.
• نتیجه تابع Find رو منهای ۵۰ میکنیم. این کار باعث میشه که ۵۰ کاراکتر برگردیم عقب و بیفتیم وسط ۹۹ فاصله ای که بعدش کلمه ی شامل کاراکتر مورد جستجوی ما قرار گرفته.
• تابع Max برای زمانی در نظر گرفته شده که اگه کاراکتر مورد نظر اول سلول بود و نتیجه Find()-50 منفی شد، بجاش ۱ در نظر گرفته بشه و تفکیک کردن از اول سلول شروع بشه.
• آرگومان آخر تابع Mid رو هم ۹۹ قرار دادیم که میدونیم میفته مابین فاصله های موجود در بعد از کلمه مورد نظر.
• تابع Trim هم کمک میکنه که Space های کلمه نهایی حذف بشه چون میدونیم که خروجی تابع Mid تعداد زیادی Space در قبل و بعدش داره.

به تصویر زیر و نحوه دیباگ کردن فرمول دقت کنید:

توجه داشته باشید اگر میدونیم که داده های مورد نظر از ۹۹ کاراکتر بیشتر هستن، عدد ۹۹ و ۵۰ رو میتونیم با ۱۰۰۰ و ۵۰۰ جایگزین کنیم.

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