مرتب سازی داده ها با استفاده از توابع متن

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

  • پیدا کردن الگوهای متن مورد نیاز با توابع متن.
  • استخراج مقادیر داده از متن.
  • داده های قالب بندی با توابع متن.
  • اجرای عملیات داده با توابع متن.

 

حذف موارد ناخواسته از متن

هنگام وارد کردن داده ها از برنامه دیگری، متن شما حتما دارای کاراکترهای غیر قابل چاپ و یا فضاهای اضافی می باشد:

  • فضاهای پیشرو. 
  • فضاهای اضافی بین کلمات.

اگر این اطلاعات را مرتب کنید یا تجزیه و تحلیل کنید، نتایج اشتباهی دریافت خواهید کرد.
مثال زیر را در نظر بگیرید 

این اطلاعات خام است، اطلاعات محصول حاوی شناسه محصول، توضیحات محصول و قیمت دریافتی است. کاراکتر "|" فیلد را در هر ردیف جدا می کند.

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

همانطور که مشاهده می کنید، تمام داده ها در یک ستون تک هستند. شما باید این داده ها را برای تجزیه و تحلیل داده ها ساختار بندی کنید . با این حال، ابتدا باید داده ها را پاک کنید.

شما باید هر کاراکتر غیر قابل چاپ و فضاهای اضافی که ممکن است در داده ها وجود داشته باشد را حذف کنید. برای این منظور می توانید از عملکرد CLEAN و TRIM استفاده کنید.

ردیف

تابع و شرح

1

مرتب  سازی

1-همه بخش های غیر قابل چاپ را از متن حذف می کند

2

مرتب

2-فاصله از متن را حذف می کند

 

سلول C3 - C11 را انتخاب کنید.

Type = TRIM (CLEAN (B3)) و سپس CTRL + Enter را فشار دهید.

فرمول در سلول C3 - C11 پر شده است.

 

پیدا کردن الگوهای متن مورد نیاز با توابع متن

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

ردیف

تابع و شرح

1

دقیق

1-چک کنید که آیا دو مقادیر متن یکسان هستند یا خیر

2

پیدا کردن

2-یک مقدار از متن را در متن دیگری (حساس به حروف) می یابد

3

جستجو کردن

3-یک مقدار از متن را درون دیگری (حساس به حروف نیست) می یاب

 

 

استخراج ارزش های داده از متن

شما باید اطلاعات لازم از متن را بخوانید تا ساختار یکسان باشد. در مثال فوق، می گویند، شما باید داده ها را در سه ستون قرار دهید :

  • شناسه محصول
  • قیمت
  • شرح تولید

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

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

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

شما می توانید از گزینه Convert Text to Columns Wizard برای استخراج مقادیر داده ها به ستون اکسل استفاده کنید.

ستون ها با فضاهای بین هر فیلد هماهنگ شده است.

 

در مثال بالا، فیلدها توسط شخصیت "|" تعریف می شوند. از این رو، می توانید از تبدیل متن به ستون ها استفاده کنید.

  • داده ها را انتخاب کنید.
  • کپی مقادیر و گذاشتن در همان مکان. در غیر این صورت، در هنگام تبدیل متن به ستون ها، توابع را به جای داده های خود و به عنوان ورودی به کار می برد.

  • داده ها را انتخاب کنید.
  • روی «Text to Columns » در «Data Tools » در زیر نوار داده روی متن کلیک کنید.

مرحله 1 - تبدیل متن به ستون - مرحله 1 از 3 به نظر می رسد.

  • انتخاب دلخواه
  • روی بعدی کلیک کنید.

مرحله 2 - تبدیل متن به ستون - مرحله 2 از 3 به نظر می رسد.

  • Delimiters، دیگر را انتخاب کنید.
  • در جعبه کنارother ، نوع شخصیت I را تایپ کنید
  • روی بعدی کلیک کنید.

مرحله 3 - تبدیل متن به ستون - مرحله 3 از 3 به نظر می رسد.

در این صفحه، می توانید درهر ستون داده خود را انتخاب کنید و فرمت آن ستون را تنظیم کنید.

  • برای مقصد، سلول D3 را انتخاب کنید.
  • شما می توانید Advanced را کلیک کنید و Separator Decimal Separator و Thousands Separator را در کادر گفتگوی تنظیمات ورود متن پیشرفته که ظاهر می شود تنظیم کنید.
  • روی Finish کلیک کنید.

داده های شما که به ستون تبدیل می شوند، در سه ستون ظاهر می شوندD، E و F.

  • نام هدر های ستون را به عنوان شناسه محصول ، قیمت ، شرح تولید نامگذاری کنید.

 

 

استخراج ارزش های داده ها با استفاده از توابع متن

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

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

ردیف

تابع و شرح

1

جداکردن

1-چپ ترین شخصیت ها را از یک مقدار متنی باز می گرداند

2

RIGHT

2-کاراکترهای سمت راست را از یک مقدار متن بازگردانید

3

MID

3-تعداد مشخصی از کاراکترها را از موقعیتی که شما مشخص می کنید می گیرد واز یک رشته متن شروع می کند

 

4

Lan

4-تعداد نویسه ها را در یک رشته متن باز می کند

شما همچنین می توانید دو یا چند از این توابع متن را به عنوان داده هایی که در دست دارید، ترکیب کنید تا بتوانید مقادیر داده مورد نیاز را استخراج کنید. برای مثال، با استفاده از ترکیبی از عملکردهایLEFT ، RIGHT و VALUE یا با استفاده از ترکیبی از توابع FIND، LEFT، LEN و MID.

در مثال فوق

  • به تمام کاراکترهای چپ در اولین | نام شناسه محصول را ذکر کنید.
  • به تمام کاراکترها به سمت راست | نام قیمت را بدهید.
  • به تمام کاراکترهایی که بین اولین | و دومین | نام توضیحات محصول را بدهید.
  • هر | فضای که در قبل و بعد دارد.

با مشاهده این اطلاعات می توانید مقادیر داده را با مراحل زیر استخراج کنید :

  • موقعیت اول را پیدا کنید - اول | موقعیت
  • شما می توانید از عملکرد FIND استفاده کنید
  • یافتن موقعیت دوم | - دوم | موقعیت.
  • شما می توانید از عملکرد FIND دوباره استفاده کنید.
  • شروع به (اول | موقعیت - 2)
  • شما می توانید از تابع LEFT استفاده کنید.
  • (اول | موقعیت + 2) به (دوم | موقعیت - 2) کاراکتر متن به Product_Description
  • شما می توانید از تابع MID استفاده کنید.
  • (دوم | موقعیت + 2) برای پایان دادن به کاراکترهای متن قیمت را می دهد.
  • شما می توانید از عملکرد RIGHT استفاده کنید.

:نتیجه این خواهد شد که در زیر نشان داده شده است 

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

 

 

استخراج ارزش های داده با پر کردن flash

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

مرحله 1 - سه ستون برای شرح محصول شناسه محصول و قیمت در کنار داده ها ایجاد کنید.

مرحله 2 - مقادیر C3، D3 و E3 را از B3 کپی و وارد کنید.

مرحله 3 - سلول C3 را انتخاب کنید و بر روی فلش پر کنید در گروه Data Tools در برگه Data کلیک کنید. تمام مقادیر ProductID پر می شود.

مرحله 4 - مراحل فوق را برای شرح محصول شناسه محصول و قیمت تکرار کنید. داده ها پر شده اند.

 

 

فرمت داده ها با توابع متن

اکسل دارای چندین توابع برای متن درونی است که می توانید برای قالب بندی اطلاعات حاوی متن استفاده کنید. این شامل :

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

ردیف

تابع و شرح

1

LOWER

1-متن را به حروف کوچک تبدیل می ک

ردیف

تابع و شرح

1

UPPER

1-متن را به حروف بزرگ تبدیل می کند

 

2

PROPER

2-اولین حرف در هر کلمه از یک مقدار متنی را وارد می کند.

تبدیل توابع و یا فرمت کردن اعداد به عنوان مت

ردیف

تابع و شرح

1

DOLLER

یک عدد را به متن تبدیل می کند، با استفاده از فرمت ارز دلار (دلار)

 

2

FIXED

یک عدد را به صورت متن با یک عدد ثابت از عدد نزولی فرمت می کند.

 

3

TEXT

یک عدد را فرمت می کند و آن را به متن تبدیل می کند.

تبدیل توابع متن به ا

ردیف

تابع و شرح

1

VALUE

1-یک استدلال متن را به یک عدد ت

 

 

اجرای عملیات داده ها با توابع متن

شما ممکن است مجبور شوید عملیات متنی خاص را در Data خود انجام دهید. به عنوان مثال، اگر شناسه ورود به سیستم برای کارمندان به یک فرمت جدید در یک سازمان تغییر پیدا کند، ممکن است تغییرات متن انجام شود.

توابع متن به شما در انجام عملیات متن کمک می کند.

ردیف

تابع و شرح

1

1-جایگزین کاراکتر در متن

جایگزین

 

2

جایگزین کردن

2-متن جدیدی را به جای متن قدیمی در یک رشته متن جایگزین می کند

 

3

به هم پیوستن

3-چندین آیتم متنی را به یک آیتم متنی متصل می کند

 

4

4-متن را از چندین محدوده و یا رشته ترکیب می کند، و جداول را خالی ارائه نمی دهد.

پیوند متن

 

5

پیوستن

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

 

6

تکرار

6- متن چند بار نشان داده شده است

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

نظرات کاربران

نظر خودتان را بنویسید