نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، گرافیک و فناوری اطلاعات فعالیت دارم.

  • جدا کردن کارکترهای یک سلول

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

تابع Left

تابع Left جزء توابع رشته‌ای می‌باشد که تعداد مشخصی از کاراکترهای ابتدای یک سلول یا عبارت را جدا می‌کند.

ساختار تابع

LEFT(text,[num_char])
آرگومانشرح
textاختصاص این آرگومان ضروری بوده و می‌تواند شامل رشته یا آدرس سلول باشد.
num_charاختصاص این آرگومان اختیاری بوده و مشخص کننده تعداد کاراکترهایی است که باید از ابتدای آرگومان text استخراج شود.

آرگومان‌های تابع Left

ویژگی‌های تابع

  1. آرگومان num_char باید بزرگتر یا مساوی صفر باشد.
  2. پیش‌فرض آرگومان return_type عدد 1 می‌باشد.
  3. اگر آرگومان num_char بزرگتر از طول آرگومان text باشد، همه عبارت متنی به عنوان نتیجه تابع برگردانده می‌شود.

مثال

فرض کنید مقدار سلول A1 را برابر با رشته نیما آریان‌پور قرار داده‌ایم و می‌خواهیم عبارت نیما را از این سلول استخراج کنیم. در یک سلول به دلخواه تابع Left را به صورت زیر تعریف می‌کنیم. این تابع 4 کاراکتر اول سلول A1 را استخراج کرده و رشته نیما را به عنوان نتیجه برمی‌گرداند.

=LEFT(A1,4)

بیشتر بخوانید: ابزار جدول (Table)

تابع Right

تابع Right جزء توابع رشته‌ای می‌باشد که تعداد مشخصی از کاراکترهای انتهای یک سلول یا عبارت را جدا می‌کند.

ساختار تابع

RIGHT(text,[num_char])
آرگومانشرح
textاختصاص این آرگومان ضروری بوده و می‌تواند شامل رشته یا آدرس سلول باشد.
num_charاختصاص این آرگومان اختیاری بوده و مشخص کننده تعداد کاراکترهایی است که باید از انتهای آرگومان text استخراج شود.

آرگومان‌های تابع RIGHT

ویژگی‌های تابع

  1. آرگومان num_char باید بزرگتر یا مساوی صفر باشد.
  2. پیش‌فرض آرگومان return_type عدد 1 می‌باشد.
  3. اگر آرگومان num_char بزرگتر از طول آرگومان text باشد، همه عبارت متنی به عنوان نتیجه تابع برگردانده می‌شود.

مثال

فرض کنید مقدار سلول A1 را برابر با رشته نیما آریان‌پور قرار داده‌ایم و می‌خواهیم عبارت آریان‌پور را از این سلول استخراج کنیم. در یک سلول به دلخواه تابع Right را به صورت زیر تعریف می‌کنیم. این تابع 9 کاراکتر آخر سلول A1 را استخراج کرده و رشته آریان‌پور را به عنوان نتیجه برمی‌گرداند.

=RIGHT(A1,9)

در همه توابع رشته‌ای، معیار اصلی ابتدای سلول می‌باشد. اگر عبارت متنی فارسی باشد، شروع سلول از سمت راست و اگر عبارت متنی انگلیسی باشد، شروع سلول از سمت چپ خواهد بود.

جدا کردن کاراکترهای یک سلول تا قبل از یک کاراکتر خاص

فرض کنید نام و نام‌خانوادگی افراد در یک ستون با فاصله (Space) از یکدیگر جدا شده‌اند و می‌خواهیم قسمت نام را از این عبارت‌ها جدا کنیم. برای این‌کار ابتدا باید مکان فاصله (Space) را پیدا کرده و سپس با تابع Left قسمت نام را جدا کنیم. تابع Find را در سلول B2 به صورت زیر تعریف می‌کنیم. این تابع مکان قرارگیری فاصله (Space) در سلول A2 را پیدا کرده و مقدار 5 را به عنوان نتیجه برمی‌گرداند. سلول ‌B2 را با استفاده از ویژگی AutoFill برای دیگر سلول‌ها درگ می‌کنیم.

=FIND(" ",A2)

پیدا کردن مکان فاصله (Space) در یک عبارت متنی با تابع Find

پیدا کردن مکان فاصله (Space) در یک عبارت متنی با تابع Find

می‌خواهیم از ابتدای سلول تعداد مکان فاصله (Space) را از نام و نام خانوادگی جدا می‌کنیم. برای این‌کار تابع Left را به صورت زیر در سلول C2 تعریف می‌کنیم. این تابع ابتدا مقدار مکان فاصله (Space) را از عدد 1 کم می‌کند، زیرا نمی‌خواهیم فاصله (Space) به همراه نام استخراج شود. سپس رشته نیما را به عنوان نتیجه برمی‌گرداند. سلول ‌C2 را با استفاده از ویژگی AutoFill برای دیگر نام‌ها درگ می‌کنیم.

=LEFT(A2,B2-1)

جدا کردن نام از رشته نام و نام خانوادگی با تابع Left

جدا کردن نام از رشته نام و نام خانوادگی با تابع Left

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

=LEFT(A2,FIND(" ",A2)-1)

فاصله (Space) یک کاراکتر محسوب شده و مثل همه کاراکترها به روی خروجی توابع رشته‌ای تاثیر می‌گذارد. این کاراکتر چون دیده نمی‌شود ممکن است خروجی فرمول‌ها را با اشتباه مواجه کند. بنابراین باید تعداد کاراکترهای هر سلول را محاسبه کرده، سپس اقدام به فرمول‌نویسی کنیم.

جدا کردن N کاراکتر آخر یک سلول

فرض کنید می‌خواهیم تاریخ را از نام و نام خانوادگی جدا کنیم. چون تاریخ‌ها تعداد کاراکتر ثابتی ندارند، بنابراین باید الگویی پیدا کنیم که بتوانیم بر اساس آن فرمول‌نویسی کنیم. در داده‌های نشان داده شده در تصویر 3، تاریخ‌ها بعد از کاراکتر − قرار گرفته‌اند. پس باید ابتدا مکان قرارگیری − را پیدا کرده و کاراکترهای پس از آن را از عبارت متنی موجود در سلول جدا کنیم.

برای پیدا کردن مکان − باید از تابع Find استفاده کنیم. این تابع یک عدد که نشان‌دهنده محل قرارگیری − است را به عنوان نتیجه برمی‌گرداند. تابع Find را به صورت زیر در سلول B1 تعریف می‌‌کنیم. این تابع مکان قرارگیری − را پیدا کرده و عدد 15 را به عنوان نتیجه برمی‌گرداند. سلول ‌B2 را با استفاده از ویژگی AutoFill برای دیگر سلول‌ها درگ می‌کنیم.

=FIND("−",A2)

پیدا کردن مکان − در یک عبارت متنی با تابع Find

پیدا کردن مکان − در یک عبارت متنی با تابع Find

مبنای محاسبات توابع رشته‌ای ابتدای سلول می‌باشد، بنابراین نتیجه تابع Find را باید از تعداد کل کاراکترهای سلول کم کنیم تا تعداد کاراکترهای تاریخ فراخوانی شود. برای این‌کار از تابع Len استفاده می‌کنیم. تابع Len را در سلول C2 به صورت زیر تعریف می‌کنیم. این تابع طول رشته در سلول A2 را محاسبه کرده و مقدار 24 را به عنوان نتیجه برمی‌گرداند. سلول ‌C2 را با استفاده از ویژگی AutoFill برای دیگر سلول‌ها درگ می‌کنیم.

=LEN(A2)

محاسبه طول رشته با تابع Len

محاسبه طول رشته با تابع Len

حالا باید تعداد کاراکترها را از مکان − کم کنیم تا تعداد کاراکترهای تاریخ محاسبه شود. مثلا برای ردیف اول، تعداد کاراکترها 24 و مکان − کاراکتر 15 می‌باشد. بنابراین 24-15 برابر است با 9 که همان کاراکترهای تاریخ 1390/3/18 می‌باشد.

این تفریق را در تابع Right قرار داده تا از انتهای سلول این تعداد کاراکتر را استخراج کنیم. تابع Right را در سلول D2 به صورت زیر تعریف می‌کنیم. این تابع مقدار سلول B2 را از سلول C2 برای محاسبه تعداد کاراکترهای تاریخ کم کرده و رشته 1390/3/18 را به عنوان نتیجه برمی‌گرداند. سلول ‌D2 را با استفاده از ویژگی AutoFill برای دیگر تاریخ‌ها درگ می‌کنیم.

=RIGHT(A2,C2-B2)

جدا کردن تاریخ از عبارت متنی با تابع Right

جدا کردن تاریخ از عبارت متنی با تابع Right

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

=RIGHT(A2,LEN(A2)-FIND("−",A2))

توابع رشته‌ای به روی مقادیر عددی

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

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

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

=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

تبدیل تابع رشته‌ای به عدد با تابع Value

تبدیل تابع رشته‌ای به عدد با تابع Value

بنابراین اگر خروجی توابع رشته‌ای درون تابع Value قرار بگیرند، نتیجه فرمول به عدد تبدیل شده و قابلیت انجام محاسبات را دارند.