جدا کردن عدد از متن در اکسل
یکی از مشکلات کاربران اکسل، علی الخصوص حسابداران، این است که نمی توانند به روش مطمئنی اعداد موجود در یک رشته متنی را از آن استخراج نمایند. فرض کنید که در یک سلول عبارتی شبیه به «چک شماره ۵۴۳۱۳۲ نزد بانک ملی شعبه ۱۳۲ مربوط به حساب ۱۳۲۱۳۵۱۲۱۵۱» دارید و قصد دارید اعداد موجود در آن را استخراج نمایید. برای جدا کردن عدد از متن در اکسل چند روش وجود دارد که به ترتیب در زیر شرح می دهم.
۱- استخراج دستی اعداد
اصلا جالب نیست، منظورم این است که کار بسیار سختی است که بخواهید تک تک اعداد را تایپ نموده و یا بصورت دستی از سلول مبداء کپی نموده و در سلول مقصد جایگذاری نمایید. البته این روش اگر تعداد سلول ها شما کمتر از ۱۰ مورد باشد بهترین روش است و توصیه میشود، اما تصور کنید که بخواهید ۱۲۳۱۲۴۴۲۳ عدد را به این روش از ۲۳۴۲۳۴ سلول استخراج کنید. نه، نمیخواد تصور کنید. تصورش هم کار خوبی نیست.
۲- استخراج با استفاده از فرمولهای متنی
این روش روشی بسیار خوب و خداپسندانه است ولی مشکلش این است که فقط در مواردی به درد می خورد که نظم خاصی در محتوای سلول ها قابل مشاهده باشد. یعنی مثلاً همه اعدادی که قصد استخراج آنها را داریم در از هشتمین کاراکتر شروع میشوند و همه ۶ رقمی هستند. یا مثلاً قبل و بعد از همه اعدادی که قصد استخراج آنها را داریم پرانتز یا علامت مشخصه دیگری وجود دارد و…
در این روش جدا کردن عدد از متن در اکسل از ترکیب توابعی مانند 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 استفاده کنید تا خروجی مطلوب نمایش داده شود.
این تابع رو برای استفاده حسابداران و حسابرسان عزیز بصورت رایگان در سایت قرار میدم (برید حالشو ببرید)
برای دانلود تابع از دکمه زیر استفاده نمایید
درباره حسین صابری
عضو هیئت علمی دانشگاه آزاد - ارشد حسابداری (دانشجوی دکتری حسابداری) - کارشناس رسمی قوه قضاییه (حسابداری و حسابرسی) - برنامه نویس و مدرس کامپیوتر
نوشته های بیشتر از حسین صابریمطالب زیر را حتما مطالعه کنید
فایل حقوق دستمزد 98 و اکسل محاسبه مالیات حقوق 98
معرفی توابع جدید در اکسل ۲۰۱۹
همانطور که می دانید مجموعه آفیس هر سه سال یک بار نسخه جدید خود را عرضه می کند و در ورژن های جدید امکانات جدیدی را قرار می دهند که کار با نرم افزارها را برای کاربران راحت تر کند
در اکسل ۲۰۱۹ چندین ابزار کاربردی اضافه شده است که در این قسمت مب خواهیم به معرفی چند تابع جالب و بسیار کاربردی آن بپردازیم. توابعی که در این بخش یاد خواهیم گرفت در نسخه OFFICE 365 وجود داشتند اما چون این نسخه به صورت غیررایگان عرضه شده بود بسیاری از کاربران امکان پرداخت هزینه آن را نداشته و از این توابع محروم بودند
توابعی که به معرفی آنها می پردازیم شامل موارد زیر می باشند:
CONCAT
TEXTJOIN
MINIFS
MAXIFS
IFS
SWITCH
محاسبه مالیات حقوق تحت اکسل
ورود سریع تر اطلاعات
ورود سریعتر داده ها در اکسل به کمک قابلیت Auto Correctشاید برای شما پیش آمده باشد که در یک زمان ورود اطلاعات یک فهرست، به صورت مداوم مجبور به تکرار یک کلمه یا جمله هستید. بهترین راه برای این کار استفاده از ویژگی Auto Correct است. به عنوان نمونه ما در ورود اطلاعات یک جدول کلمه @talant_ir را دائما تکرار می کنیم، و تصمیم داریم با ورود عبارت @t به صورت خودکار کلمه @talant_ir تکرار شود. برای اینکار از مسیر ذیل استفاده می کنیم.
محاسبه بهای تمام شده به روش FIFO
بسیاری از حسابداران محاسبات مربوط به بهای تمام شده را به صورت دستی و به صورت کاردکس بالا انجام می دهند که این کار زمانبر و پر از اشتباه خواهد بوددر این آموزش می خواهیم محاسبات را با استفاده از نرم افزار اکسل انجام دهیم و دقت و سرعت محاسبات را بالا ببریم.
ساخت Pivot Table
1 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
احسنت . خدا خیرت بده