یکی از مشکلات کاربران اکسل، علی الخصوص حسابداران، این است که نمی توانند به روش مطمئنی اعداد موجود در یک رشته متنی را از آن استخراج نمایند. فرض کنید که در یک سلول عبارتی شبیه به «چک شماره ۵۴۳۱۳۲ نزد بانک ملی شعبه ۱۳۲ مربوط به حساب ۱۳۲۱۳۵۱۲۱۵۱» دارید و قصد دارید اعداد موجود در آن را استخراج نمایید. برای جدا کردن عدد از متن در اکسل چند روش وجود دارد که به ترتیب در زیر شرح می دهم.
۱- استخراج دستی اعداد
اصلا جالب نیست، منظورم این است که کار بسیار سختی است که بخواهید تک تک اعداد را تایپ نموده و یا بصورت دستی از سلول مبداء کپی نموده و در سلول مقصد جایگذاری نمایید. البته این روش اگر تعداد سلول ها شما کمتر از ۱۰ مورد باشد بهترین روش است و توصیه میشود، اما تصور کنید که بخواهید ۱۲۳۱۲۴۴۲۳ عدد را به این روش از ۲۳۴۲۳۴ سلول استخراج کنید. نه، نمیخواد تصور کنید. تصورش هم کار خوبی نیست.
۲- استخراج با استفاده از فرمولهای متنی
این روش روشی بسیار خوب و خداپسندانه است ولی مشکلش این است که فقط در مواردی به درد می خورد که نظم خاصی در محتوای سلول ها قابل مشاهده باشد. یعنی مثلاً همه اعدادی که قصد استخراج آنها را داریم در از هشتمین کاراکتر شروع میشوند و همه ۶ رقمی هستند. یا مثلاً قبل و بعد از همه اعدادی که قصد استخراج آنها را داریم پرانتز یا علامت مشخصه دیگری وجود دارد و…
در این روش جدا کردن عدد از متن در اکسل از ترکیب توابعی مانند Search, MID, LEN, LEFT, RIGHT استفاده میشود و طوری این توابع را با هم ترکیب میکنیم که خروجی آنها به عدد مطلوب ما برسد
در مجموع این روش روش خوبیست اما محدودیت بزرگی دارد (همانی که اشاره شد) که بیشتر مواقع استفاده از این روش را غیر ممکن می کند.
۳- استفاده از قابلیت Flash Fill
این قابلیت که فکر میکنم از آفیس ۲۰۱۳ به بعد به اکسل اضافه شد، هرچند قابلیت بسیار جالبی است اما در این قابلیت هم مثل مورد بالا باید نظمی مشهود باشد. منتها مزیت این روش نسبت به روش قبلی در این است که در این روش نیازی به فرمول نویسی و ترکیب توابع با یکدیگر نداریم. اما استفاده مستمر از این روش توصیه نمی شود. یعنی به نظرم برای استفاده موقتی روش مناسبی است.
۴- استفاده از قابلیت Text to Column
همانطور که می دانید (امیدوارم که بدانید) با استفاده از این قابلیت میتوانید متن موجود در سلول را با استفاده از ضوابطی تفکیک کرده و هر قسمت را در یک سلول قرار دهید. این روش هم روش خوبی است اما مشکلش اینجاست که بعد از استخراج اعداد از سلول و تفکیک آنها از متن (مثلا با استفاده از ضابطه هر فاصله یک ستون)، هر یک از اعداد را به یک سلول پرت میکند و جمع آوری مجدد آنها خود زمان زیادی را میگیرد.
مشاهده ویدئویی از دوره آموزش اکسل ویژه حسابداران که به مبحث جداکردن اعداد از متن پرداخته است.
۵- استفاده از افزونههای مخصوص
مثل همیشه آخرین راهی که وجود دارد استفاده از کد نویسی و توابع افزوده است. البته ایراد این روش هم این است که بعضی سیستم ها را کند می کند و در برخی به لحاظ امنیتی اصلاً اجرا نمیشود و در نهایت برخی را هم می ترساند.
من قبلاً یک تابع افزوده بنام GETNumber منتشر کرده بودم که با استفاده از آن می شد همه اعداد موجود در یک سلول را استخراج کرد. منتها ایرادی که داشت این بود که اگر تعداد اعداد موجود در سلول بیشتر از یکی بود، همه را به یکدیگر میچسباند و تبدیل به یک عدد می کرد. امروز تصمیم گرفتم این تابع رو کمی گسترش بدم تا کار دوستان رو سادهتر کنه. لذا دو تابع جدید به این افزونه اضافه کردم که عبارتند از GetNthNumber و GetArayNumber اما اینکه این توابع چیستند؟ و چه میکنند؟ و چطور؟، را خواهم گفت.
GETNthNumber
این تابع n امین عدد موجود در یک رشته متنی را برمیگرداند. دو ورودی دارد که ورودی اول strText همان متن شامل عدد است که میتوانید به سلول خود ارجاع دهید و N که مشخص میکند چندمین عدد موجود در این رشته را نیاز دارید. مثلا اگر strTextهمان متن بالا باشد («چک شماره ۵۴۳۱۳۲ نزد بانک ملی شعبه ۱۳۲ مربوط به حساب ۱۳۲۱۳۵۱۲۱۵۱») و N را برابر با ۲ قرار دهید خروجی تابع عدد ۱۲۳ خواهد بود
GetArayNumber
این تابع یک تابع آرایهای است که میتوانید از آن در چند سلول بصورت همزمان و پیوسته استفاده کنید. در این صورت این تابع هر یک از اعداد موجود در متن را در یک سلول نمایش خواهد داد. همانطور که میدانید برای استفاده از توابع آرایهای شبیه به این تابع (برای اینکه چند خروجی داشته باشید) باید ابتدا چند (نهایتاً ۱۰ خروجی خواهید داشت) سلول را انتخاب نمایید و با زدن کلید f2 اقدام به ویرایش سلول نموده و از تابع GetArayNumber استفاده نمایید. در نهایت بجای زدن کلید اینتر از ترکیب کلید های Ctrl+Shift+Enter استفاده کنید تا خروجی مطلوب نمایش داده شود.
این تابع رو برای استفاده حسابداران و حسابرسان عزیز بصورت رایگان در سایت قرار میدم (برید حالشو ببرید)
یک راه دیگر حل بسیار عالی برای استخراج تاریخهای شمسی و اعداد (حتی اعشاری و منفی) از متن استفاده از مجموعه توابع افزوده اکسل تالانت است. در خصوص استخراج اعداد و تاریخها از داخل سلولهای متنی بیش از 10 تابع در این مجموعه وجود دارد.
[the_ad id=”28842″]
دوره های مرتبط
فایل راهنمای افزونه solver
[static_block_content id=”12519″]
از افزونه Solver در نرم افزار اکسل برای حل معادلات برنامه ریزی خطی استفاده میشود. با استفاده از این فایل راهنما میتوانید این مسائل را به کمک اکسل حل کنید.
صورت مغایرت بانکی هوشمند
در این فایل اکسل با استفاده از زبان برنامه نویسی vba و ماکروهای بسیار پیشرفته، تقریباً همه مراجل مغایرت گیری بصورت خودکار انجام میگیرد.
آموزش افزونه پاور کوئری در اکسل- طاهر خانی
آموزش افزونه پاور کوئری در اکسل مدرس: طاهر خانی [one_half] [our-team group=”Y.Taherkhani”] [/one_half] [one_half_last] [/one_half_last] یکی از ترفندهای…
آموزش برنامه نویسی در اکسل با استفاده از VBA
یکی از پیشرفته ترین امکانات آفیس که قابلیتهای فراوانی را در اختیار توسعه دهندگان فایلهای آفیس قرار میدهد، کدنویسی با…
آموزش اکسل مقدماتی- صابری
فیلمهای کوتاه و ده دقیقه ای آموزش اکسل
سطح آموزش این سری از فیلمها مبتدی است و از ابتدای اکسل شروع میشود
آموزش PowerPivot (قسمتهای 24-31)
آموزشی که در این بخش در اختیار شما قرار داده شده، فصل اول PowerPivot می باشد که جزئی از آموزش های هوش تجاری توسط اینجانب می باشد، آموزش هایی نظیر : Power View ، Power Query و Power BI
در بخش اول شما با این افزونه آشنا شده و فرا خواهید گرفت که چگونه داده ها را چندین منبع مختلف وارد پنجره PowerPivot کنید و بین آنها ارتباط برقرار کرده و براساس ارتباطات بوجود آمده گزارش های خود را تهیه کنید.
نکته ای که وجود دارد این است که PowerPivot یک ابزار بین داده های خام و گزارش های شماست که قراراست توسط PivotTable و Power View ایجاد شوند.
پری ناز توسلی( خریدار محصول )
سلام میشه یه نمونه از نوشتن دستورش بذارین؟
Bahman
سلام در پروژه عمرانی خیلی بهم کمک میکنه.این دستور عالی هست
Bahman
بسیار عالی و کابردی هست
بهروز دادراست( خریدار محصول )
با عرض سلام و خسته نباسید خدمت اساتید گرامی
افزونه را نصب کردم عالیه
حسین صابری(مدیریت)
سلام
ممنونم
لطف دارید
به نظرم افزونه جدید رو هم امتحان کنید که این افزونه هم بصورت کاملتر اونجا هست
طلوعی
سلام
لطفا بگین افزونه getnumber را از چه سایتی تهیه کنیم؟ البته با کرک
مسعود تربتیان( خریدار محصول )
سلام و عرض ادب
ضمن تشکر از افزونه ی بسیار کاربردی و عالی شما، بیش از یکسال است که از افزونه GetNumber استفاده میکنم.
متأسفانه طی 24 ساعت گذشته وقتی فایل اکسل را باز میکنم و برای فعال سازی افزونه فایل GetNumber.xlam را اجرا میکنم، فایل اکسل هنگ کرده و غیرفعال میشود. برای اجرای دوباره باید از Task Manager اکسل را متوقف کنم تا امکان باز کردن مجدد فایل های اکسل را داشته باشم. متأسفانه عملا افزونه از کار افتاده است.
تا پیش از این هر زمان افزونه غیر فعال میشد با اجرای فایل GetNumber.xlam و تایید اجرای ماکرو افزونه روی فایل های اکسل فعال میشد که متاسفانه اکنون به ترتیبی که اشاره شد غیر فعال شده است.
سپاسگزار میشم راهنمایی فرمایید.
حسین صابری(مدیریت)
سلام
متاسفانه اینها ایراداتی هست که به علت مشکل کرک ناقص آفیس اتفاق میفته.
در افزونه جدید سعی کردم این مدل ایرادات رو رفع کنم و البته امکانات بسیار زیاد دیگه هم بهش اضافه کنم.
افزونه توابع تالانت رو نگاه کنید
hearthope
با سلام و ادب
در یک سلول میخوام هم متن باشه هم یک عدد که از سلول دیگه مقدار میگیره
با این حالت که یک کلمه باشه ولی عدد بصورت تفکیک هزارگان دهگان یکان ممیز بگیرد.
=CONCATENATE(“کلمطالبات”,” “,(AB26))
از راست به چپ شده ولی برعکسه