جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
ربات پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_bot

خلاصه آمار

تبليغات

پربحث ترين ها

جدا کردن عدد از متن در اکسل

اکسل ماکرونویسی

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

برای اینکار دو حالت وجود دارد:

حالت اول) در همه سلول‌ها عدد یا حروف در یک جهت قرار داشته باشد مثلا در تمام سلول‌ها عدد در سمت راست باشد و اینکه محتویات سلول‌ها مثل ۱ش۲س بصورت ترکیبی نباشند. البته باید یکی از شرایط زیر را نیز دارا باشد:

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

۲- یا اینکه یکی از دو مورد عدد یا حروف درون تمام سلول‌ها تعداد کاراکتر یکسانی داشته باشد.

مثال ۱) در شکل زیر در همه سلول‌ها، اعداد در سمت چپ قرار دارند و ۴ رقمی هستند.

فرمول‌های زیر را به ترتیب در سلول B1 و C1 کپی کنید:

=LEFT(A1,4)

=MID(A1,5,LEN(A1)-4)

نحوه استفاده از توابع LEFT ،MID و LEN قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های A1 تا A4، نتیجه زیر حاصل می‌شود.

 

مثال ۲) در شکل زیر در همه سلول‌ها، اعداد در سمت راست قرار دارند و ۴ رقمی هستند.

فرمول‌های زیر را به ترتیب در سلول B1 و C1 کپی کنید:

=RIGHT(A1,4)

=MID(A1,1,LEN(A1)-4)

نحوه استفاده از توابع RIGHT ،MID و LEN قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های A1 تا A4، نتیجه زیر حاصل می‌شود.


حالت دوم) در حالتی مثل شکل زیر که تعداد کاراکترهای عدد و حروف درون سلول‌ها متفاوت و بصورت ترکیبی می‌باشند و همچنین فاصله بین متن و عدد در سلول‌ها متفاوت است، دیگر نمی‌توان از روش‌های حالت ۱ (بالا) استفاده کرد.

برای این حالت باید از روش‌های زیر استفاده نمود.

۱- با استفاده از فرمول نویسی

بوسیله فرمول زیر می‌توان عدد را از ترکیب جدا کرد.

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

نتیجه آن در شکل زیر قابل مشاهده است.

۲- با استفاده از ماکرو

  • استخراج عدد از ترکیب

کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Sub GetNumbers()
'pctarfand.ir & tarfandha.blog.ir'
Dim Rng As Range
Dim WorkRng As Range
Dim xValue As String
Dim OutValue As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    OutValue = ""
    xValue = Rng.Value
    For i = 1 To VBA.Len(xValue)
        If VBA.IsNumeric(VBA.Mid(xValue, i, 1)) Then
            OutValue = OutValue & VBA.Mid(xValue, i, 1)
        End If
    Next
    Rng.Value = OutValue
Next
WorkRng.NumberFormat = "@"
End Sub

سپس دکمه  یا کلید F5 را فشار دهید. پس از این کار پنجره KutoolsforExcel ظاهر می‌شود.

محدوده مورد نظرتان را انتخاب کنید و روی Ok کلیک کنید تا نتیجه زیر حاصل شود.

 

با تعریف یک تابع نیز می‌توان این جداسازی را انجام داد که در اینجا ارائه شده است.

  • استخراج حروف از ترکیب

بجای کد بالا از کد زیر استفاده کنید.

Function TextOnly(pWorkRng As Range) As String
'pctarfand.ir & tarfandha.blog.ir'
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
    If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
        OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
    End If
Next
TextOnly = OutValue
End Function

کلیدهای Ctrl+S را بفشارید تا کد ذخیره شود و سپس پنجره Microsoft Visual Basic را ببندید. دستور زیر را در یک سلول تایپ کنید و در سایر سلول‌ها اعمال کنید.

=TextOnly(A1)

۳- با استفاده از افزونه Kutools for Excel:

نحوه استفاده از افزونه Kutools for Excel برای استخراج عدد و استخراج حروف در اینجا و اینجا مشاهده کنید.

منبع : extendoffice.com

 

عضویت در خبرنامه:

نظرات (۱۳)

  1. سلام و عرض ادب
    نمودار خطی حاوی 1870 در دو ستون باید رسم کنم با داده های بسیار نزدیک به هم .اما نمیتونم.چند داده اول راخدمتتون ارسال می کنم امیدوارم راهنمایی بفرمایید.
    90.086385 399.237422
    90.086386 401.166106
    90.086387 403.094789
    90.086388 405.023472
    90.086389 406.952155
    90.086390 408.880838
    90.240486 410.809522
    90.267012 412.738205
    90.290848 414.666888
    • پاسخ:

      سلام
      مشکلتان چیست؟

  2. اگردریک لیست ودرهرسط تاریخ باشد وبخواهیم تاریخ راازمتن حذف کنیم چگونه اقدام کنیم .باتشکر
    • پاسخ:

      سلام
      مراحل مشابه مراحل گفته شده در مطلب فوق است. اگر مورد شما متفاوت است یک مثال بزنید.
  3. با سلام من میخوام 2 تا سلول در دو شیت مجزا را باهم مقایسه کنم و اگر در شیت اول سلول a با شیت دوم سلول a برابر بود سلول b شیت 2 را برابر با سلول b شیت اول بکنه
    راحتتر بگم
    من کارم طوری هستش که یک فایل اکسل برای من میفرستند تا من با مشترکین تماس بگیرم و بعد از تماس حاصل صحبت را در یکی از سلول های جلوی اسم مشترک مینویسم تا اینجا که مشکلی نیست ولی بعد از گذشت 3 ساعت دوباره یک فایل دیگه برای تماس به من میدهند که ممکنه قبلا باهاش تماس گرفته باشم و این دوباره کاری میشه و باعث مزاحمت مشترک میشه حالا میخوام سلول بعد از تکراری ها رو نتیجه صحبت قبلی رو بذاره مثلا یک مشترک به نام شجاعی حاصل تماس شده درخواست وام و بعد در لیست دیگه میخوان خود اکسل بگرده ببینه اگه سلول شجاعی برابر با سلول شجاعی دارای نتیجه بود همون نتیجه رو دوباره جلوی این یکی لیست هم بنویسه
    ببخشید اگه طولانی شد
    ممنونم
    • پاسخ:

      سلام
      به نظرم راحت ترین راه استفاده از تابع Vlookup است که در این مطلب آموزش داده شده است. مثلا اگر محدوده داده‌های شما در شیت اول A1:B4 باشد (در ستون A اسامی افراد و در ستون B نتیجه تماس باشد) و در شیت دوم در ستون A اسامی افراد باشد کافیست فرمول زیر را در سلول B1 بنویسید و در سایر سلول‌های ستون B اعمال کنید.

      =VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)

      موفق باشید.
  4. سلام
    با تشکر از اقدامات شما
    مشکلی که برای اینجانب در اکسل به وجود آمده این است که
    در هنگام وارد کردن عدد در اکسل به عنوان مثال ۱ بعد از اینتر عدد به صورت ۰.۰۱ می شود به طوری که هر عددی که وارد می شود به صورت تقسیم به ۱۰۰ ظاهر می شود مثال
    ۱ می شود ۰.۰۱
    ۲ می شود ۰.۰۲
    خواهشمند است اینجانب را راهنمایی فر مایید

    • پاسخ:

      سلام
      در تنظیمات اکسل تیک گزینه Automatically Insert a Decimal Point را بردارید. تصویر راهنما
      موفق باشید.
  5. با سلام از زحمات بی دریغی که انجام می دهید ، یک سوال خدمتتان داشتم در اکسل قصد دارم یک تاریخ نوشته شده رو با روز دیگر در سلول دیگر جمع کنم  مثلا 94/07/28 رو با 7 روز دیگر جمع کنم که نتیجه میشه 94/08/04 مشکلات موجود از چه نمادی بین عداد استفاده کنم و طریقه فرمول نویسیش به چه صورت باشد ./

    ممنون و سپاس گذارم

    • پاسخ:

      سلام
      متاسفانه اکسل از تاریخ شمسی پشتیبانی نمی‌کند. روش حل این مشکل در این مطلب گفته شده است. در برخی سایت‌های معرفی در مطلب فوق‌الذکر، امکان اضافه کردن چند روز به یک تاریخ نیز وجود دارد.
      اما راه کلی اضافه کردن چند روز به یک تاریخ در این سایت و این سایت توضیح داده شده است.
      موفق باشید.
  6. با عرض سلام
    من یک رشته عددی در یک سل دارم میخواهم این رشته در یک سل خالی پنج رقم پتج رقم بوسله یک کارکتر خاص جدا بشن مثلا
    1233456789 در یک سل
    در یک سل دیگه بشه مثل نمونه زیر
    56789;12334
    • پاسخ:

      سلام
      با فرض اینکه عدد مورد نظر در سلول A1 است در سلول B1 و C1 دستور زیر را بنویسید:

      =MID(A1,1,5)

      =MID(A1,6,5)

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

      سلام
      با فرض اینکه این اعداد در ستون B قرار دارند از فرمول زیر در ستون C استفاده کنید:

      =IF(AND(ISERR(FIND("<",B1)),ISERR(FIND(">",B1))),B1,MID(B1,2,LEN(B1)))

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

      سلام
      احتمالا راهنمایی ارائه شده در اینجا مشکل شما را حل می‌کند.
      موفق باشید.

ارسال نظر

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

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

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن