گزینه text در Format Cells اکسل - دردسرها و راهکارها

در این مقاله قصد دارم با هم نگاهی دقیق به ظرافت­های گزینه Text در Format Cells بیندازیم و با انواع روش­های تبدیل یک متن به عدد شما را آشنا کنم. آنچه در این مقاله می‌خوانید:

• کاربرد گزینه Text در Format Cells چیست؟ 

• ویژگیهای Text چیست؟ 

• چرا Text خطرناکترین Format Cells اکسل است؟

• روش تبدیل متن به عدد 

  •  تبدیل متن به عدد – استفاده از Convert to Number

  •  تبدیل متن به عدد – فرمول نویسی

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

  •  تبدیل متن به عدد - استفاده از ابزار Text to Columns

  •  تبدیل متن به عدد - استفاده از امکانات Power Query 

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

• سایر نکته‌ها

کاربرد گزینه Text در Format Cells چیست؟

اکثر کاربران اکسل در روزهای اول شروع کارشان در اکسل به یک مشکل برخورد می­کنند و آن هم تایپ شماره تلفن است و به محض تایپ یک شماره تلفن مثلاً 0912123123 و زدن کلید Enter می­بینند که 0 ابتدای شماره تلفن حذف می­‌شود:

احتمالاً حذف 0 در ابتدای یک شماره تلفن چیز مهمی نباشد و شاید بتوان از آن صرف نظر کرد. اما به زودی چیزهای دیگری مانند تایپ «کد ملی» و یا تهیه «دیسکت بانک» پیش خواهد آمد که دیگر نمی­توان حذف صفر ابتدای اعداد را نادیده گرفت.

اینجاست که با کمک از همکاران، دوستان، اینترنت و ... راه حل را پیدا می‌کنید:

حالا مشکل کاملاً حل خواهد شد و خواهید دید که صفر ابتدای اعداد حذف نخواهد شد.

البته کاربرد گزینه Text در Format Cells به همین موارد ختم نمی‌شود و موارد زیر را هم می­توانید به آن اضافه کنید:

• اگر در سلولی بعد از نوشتن تاریخی شبیه 17/5/57 و زدن Enter متوجه شدیم که تاریخ به میلادی و مثلاً 17/5/1957 تبدیل شد، می­توانیم با Text کردن سلول، از این تبدیل ناخواسته جلوگیری کنیم.
• اگر بخواهیم چیزی مانند شماره کارت که 16 رقم دارد را در یک سلول وارد کنیم، متوجه می­شویم که اولین رقم (یکان) به صفر تبدیل می­شود. در اینجا بازهم علاج کار تغییر فرمت سلز سلول به گزینه Text است.

ترفند: گذاشتن علامت Single Quotes در ابتدای یک عدد، معادل فرمت سلز Text است:

(علامت Single Quotes یا همان آپستروف معمولاً بر روی کیبورد شما، بر روی حرف «گـ» است.)

ویژگی­های Text چیست؟

ویژگی ۱) اگر چیزی متن باشد، بنابراین بر روی آن نمی­توان محاسبات ریاضی انجام داد. بنابراین اگر فرمت سلز سلول­هایی روی Text باشد و سپس اعدادی در آن تایپ شوند، بر روی آن مقادیر نمی­توان محاسبه­ای مانند SUM را انجام داد و حاصل 0 خواهد شد.

ویژگی ۲)  از طرف دیگری کارهایی که بر روی اعداد مانند Sort و Filter انجام می­شود، بر روی سلول‌هایی که Text هستند، قابل انجام نیست. مثلاً در تصویر زیر نمی‌­توانیم اعداد بزرگتر از 19 را فیلتر کنیم:

نکته: جالب است بدانید که گاهی نیاز داریم فیلتر ما این گونه باشد! مثلاً شرکتی می­خواهد که مواد اولیه که با 00 شروع می­شوند را فیلتر کند به همین دلیل اعداد را به صورت متن در می­آوریم (تکنیک آن را در ادامه خواهم گفت) و از گزینه‌­هایی مانند Begins With استفاده می­کنیم (این گزینه در شرط‌های فیلتر اعداد اکسل نیست).

ویژگی ۳) همچنین به Sort (از کم به زیاد) مقادیر غیر عددی زیر دقت کنید:

نکته بسیار مهمی که باید بدانید این است که در توابعی مانند VLOOKUP برایشان فرمت سلول مهم است. یعنی اگر به دنبال عدد 19 در جایی بگردیم که فرمت آنها Text است، VLOOKUP قادر به یافتن 19 (که Text است) نیست:

همچنین عدد 1 با متن 1 در اکسل برابر نیستند. یعنی اگر در اکسل فرمول = "1" = 1  را بنویسید، به شما مقدار FALSE را نمایش می­دهد یعنی این دو مقدار یکی نیستند:

چرا Text خطرناک­ترین Format Cells اکسل است؟

بدون اغراق، کاربران اکسل همیشه درگیر Text هستند و شاید بتوان گفت که با آن مشکل دارند و به صورت دقیقی نمی­توانند رفتار آن را درک کنند. دلیش ساده است چون:

 Format Cellsدر اکسل را می­توانید «رنگ و لعاب سلول» و یا «قیافه ظاهری سلول» ترجمه کنید و هیچ ربطی به محتوای سلول ندارد. اما گزینه Text دقیقاً برعکس است. اگر چه TEXT در داخل پنجره فرمت سلز است اما هیچ ربطی به قیافه سلول ندارد و باید بدانید که نحوه ذخیره‌سازی داده را در حافظه اصلی کامپیوتر مشخص می‌کند . نکته) به نحوه ذخیره سازی داده در حافظه در زبان‌های برنامه نویسی Data Type (نوع داده) می‌گویند.

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

(توجه داشته باشید که ما از شکل دقیق نحوه ذخیره سازی اطلاعات در حافظه اکسل خبر نداریم و این تصویر برای درک بهتر آورده شده است و در کتاب­های مرجع برنامه نویسی این حافظه­‌ها به همین شکل نمایش داده می­شوند.)

حال وقتی که سلولی را در حالت Text می­گذارید، هر عددی در آن تایپ کنید، به صورت یک متن در قسمتی از حافظه که مخصوص متن‌­ها است، ذخیره خواهد شد و برعکس اگر سلولی متن نباشد، هر عددی که در آن تایپ شود، در قسمتی از حافظه که ویژه عددها است، ذخیره خواهد شد.

اتفاق مهم دیگری که می­افتد، نحوه تبدیل متفاوت عدد­ها و متن­ها به 0 و 1 (یعنی باینری) است. به صورت ساده شده‌­ای اگر عدد 20 را به باینری تبدیل کنیم، حاصل آن 10100 خواهد شد و اما اگر بخواهیم متن 20 (بخوانید 2 و 0) را باینری کنیم، حاصل 00110000  00110010 خواهد شد.

(آنچه در بالا گفته شد در دنیای کامپیوتر کاملاً صحیح است اما باید تأکید شود که این ممکن است متفاوت با چیزی باشد که واقعاً در اکسل رخ می­دهد.)

حال برویم به سراغ سوال اصلی که «چرا Text خطرناک­ترین Format Cells است؟»

پاسخ این است که با عوض کردن فرمت Text به Number (و یا هر چیز دیگری)، متن‌­ها به عدد تبدیل نمی­شوند.

بگذارید دقیق­تر توضیح دهم. همه گزینه­‌هایی که از بخش Number داریم (البته بجز Text)، فقط بر روی داده­‌های عددی تاثیر می­گذارد و «ظاهر» اعداد را تغییر می­دهند. بنابراین اگر مقدار یک سلول Text باشد، گزینه Number هیچ تاثیری روی آن ندارد.

اکثر کاربرهای تازه کار اکسل «گمان می­‌کنند» که با تغییر Format یک سلول از حالت Text به Number می­توانند سلولی که متن است را به عدد تبدیل کنند، که تصور کاملاً اشتباهی است. Formatها نمی­توانند باعث هیچ تبدیلی شوند و فقط و فقط و فقط بر روی ظاهر (یعنی چیزی که روی مانیتور و یا پرینت می­بینیم) تاثیر دارند.

در تصویر بالا سلول­هایی را می­بینید که فرمت آن­ها بر روی Number است اما در حافظه Text هستند. (به علامت سبز گوشه سلول توجه کنید.)

روش تبدیل متن به عدد

قبل از شروع، احتمال دارد شما بپرسید که اساساً ما به این کار (یعنی تبدیل متن به عدد) نیازی نداریم. زیرا لازم نیست محاسباتی عددی بر روی کد ملی و یا شماره کارت‌­ها انجام دهیم و باید همانطور در حالت Text بمانند و اگر عدد لازم داریم، بدیهی است که از همان ابتدا Text را انتخاب نمی‌­کنیم.

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

برای نمونه، خروجی صورت حساب بانک سامان تا همین اواخر همه چیز (تمامی واریزی­ها و برداشت­ها) را به صورت Text به ما می­داد و قبل از محاسبه باید همه آن­ها را به عدد تبدیل می­کردیم. همچنین تا آنجا که من می­دانم تمامی خروجی­‌های اکسل نرم افزار حسابداری همکاران سیستم (یا راهکاران) به صورت  متنی است.

نکته) باید بدانید که اگر بخواهیم داده‌هایی که به صورت متن ذخیره شده است را به یک عدد تبدیل کنیم، کامپیوتر مجبور است که پردازشی را انجام دهد که به این کار در دنیای برنامه نویسی و نرم افزارها اصطلاحاً Data Conversion و یا Casting می­گویند.

مجدد تأکید می­‌کنم که نمی­توان با عوض کردن Format Cells، این تبدیل را انجام داد. بنابراین برای تبدیل یک متن به عدد در اکسل باید یکی از روش­های زیر را استفاده کنیم:

1. استفاده از گزینه Convert to Number
2. فرمول نویسی
3. استفاده از ابزار Text to Columns
4. استفاده از امکانات Power Query

حال یک به یک این روش­ها را بررسی می­کنیم و مزایا و معایب هرکدام را برای شما کامل شرح خواهم داد:

۱) تبدیل متن به عدد – استفاده از Convert to Number

بدون شک این روش ساده‌­ترین روش است.

می‌دانیم اگر سلولی را در حالت Text بگذاریم و سپس در آن ارقامی را تایپ کنیم، اکسل آن ارقام را عدد نمی­داند و به صورت یک متن در حافظه ذخیره می­شوند و در نتیجه بر روی آن محاسبات ریاضی و دیگر ابزارهای عددی اکسل کار نخواهند کرد. در اینجا اکسل وارد عمل می­شود و به شما یک هشدار مانند تصویر زیر به شکل یک مثلث کوچک سبز رنگ در گوشه سلول نمایش می­دهد:

و اگر بر روی آن سلول کلیک کنید، گزینه­ای به شکل علامت هشدار زرد رنگ به شما نمایش داده می‌شود که با کلیک بر روی آن، پنجره زیر را خواهید دید:

معنی و مفهوم این هشدار این است:

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

خوشبختانه گزینه دوم این پیغام راهگشاست. یعنی اگر گزینه Convert to Number (به عدد تبدیل کن) را بزنیم، اکسل برای ما آن ارقام را به عددی تبدیل می­کند و نتیجه را در سلول برای ما درج می‌کند.

اجازه دهید قبل از ادامه پرسش­های احتمالی شما را پاسخ گویم:

پرسش 1) اگر 100 سلول داشتم که باید همه آن­ها به یکباره به عدد تبدیل شود، چه کار کنم.

پاسخ) ابتدا بر روی یک سلول که این خطا را دارد کلیک کنید تا انتخاب شود و سپس کلید CTRL + A را یک بار بزنید تا همه سلول­های دیگر انتخاب شوند. حال اگر به سلول­ها دقت کنید، همین گزینه را در اکسل خواهید یافت. (اگر CTRL را بگیرید و سپس A را 2 بار بزنید، تمامی سلول­های اکسل انتخاب خواهند شد. با کمی دقت می­توانید تفاوت این انتخاب و CTRL + A را متوجه شوید.)

پرسش 2) تعداد سلول­های من خیلی زیاد است و بعد از زدن این گزینه، اکسل هنگ می­کند و یا بسیار کند می­شود. چه باید کرد؟

پاسخ) بله، متاسفانه این روش، بسیار بسیار کند است و گاهی باعث هنگ کردن اکسل می­شود. برای داده­‌های بزرگ باید از سایر روش­ها استفاده کنید.

پرسش 3) من در کنار سلولی همین گزینه مثلث سبز را می­بینیم، اما در آن سلول اصلاً عددی تایپ نشده است، دلیلش چیست؟

پاسخ) هشدار مثلث سبز رنگ در اکسل 9 دلیل دارد که یکی از آن­ها Number Stored as Text است. من در اینجا مجال توضیح همه آنها را ندارم. اما یک توصیه بسیار مهم می­کنم. همیشه تکلیف این مثلث سبز رنگ را مشخص کنید زیرا اکسل دارد به شما هشداری مهم را می‌دهد.

پرسش 4) در فایلی من مطمئن هستم که اعدادی در فرمت Text هستند، اما این مثلث نمایش داده نمی‌­شود. چرا؟

پاسخ) احتمال دارد که کسی دیگری که قبلاً بر روی این فایل کار می­کرده است، گزینه Ignore Error (خطا را نمایش نده) را زده باشد توجه کنید که این گزینه فقط باعث مخفی شدن علامت سبز رنگ می­شود و تبدیلی انجام نمی­دهد. اگر خواستید که نمایش خطاهای آن فایل اکسل مجدد فعال شود، از File گزینه Options را انتخاب کنید و سپس به بخش Formula بروید و روی گزینه Reset Ignored Error کلیک کنید.

File --> Options --> Formula --> Reset Ignored Error

توجه داشته باشد که خطایابی اکسل از طریق گزینه Enable Background Error Checking مدیریت می‌شود. یعنی اگر تیک آن را بردارید، کلاً خطایابی را خاموش خواهید کرد و این مثلث­های سبز کوچک نمایش داده نخواهند شد. توصیه می­شود که همیشه خطایابی را فعال نگه دارید (تیک آن را هیچ وقت برندارید).

تذکر بسیار مهم: برخی از کاربران اکسل گمان می­کنند که اگر چیزهایی که متنی هستند را کپی کنند و سپس با گزینه Paste Value آن ها را Paste کنند، تبدیل به عدد می­شوند. این تصوری اشتباه است و همه چیز به صورت متن (و نه عدد) Paste خواهند شد.

۲) تبدیل متن به عدد – فرمول نویسی

در روش قبلی کار بسیار ساده بود و کافی بود که بر روی گزینه Convert to Number کلیک کنیم و تبدیل متن به عدد انجام  می­شد. اما این روش مشکل جدی داشت، بسیار در داده­های بزرگ کند است و تقریباً ممکن است که اکسل را از کار بیاندازد.

به همین دلیل در بسیاری از مواقع ما ترجیح می­دهیم که با نوشتن یک فرمول ساده این تبدیل (متن به عدد) را     انجام دهیم.

خوشبختانه اکسل در اینجا بسیار مهربان است و کافی است که بر روی سلولی که متن در آن است، یک عمل اصلی مثل جمع، ضرب و ... را انجام دهیم و خود اکسل برای ما تبدیل را انجام خواهد داد.

فرض کنید سلول A1 در حالت Text است و در آن 17 قرار دارد. در یکی از سلول­های اکسل (مثلاً B1) فرمولی مانند =A1 + 1 را می­نویسیم و می­بینیم که پاسخ 18 شده است!!!

در واقع اکسل به صورت خودکار و از آنجایی که مهربان است، این تبدیل را برای ما انجام داده است. یعنی متن 17 (بخوانید یک هفت) را به عدد 17 تبدیل کرده است و سپس آن را با عدد 1 جمع کرده است.

توجه داشته باشید که در اکثر زبان­های برنامه نویسی این فرمول (یعنی محاسبه بر روی متن) به ما خطا خواهد داد.

بسیاری از کاربران اکسل از این خاصیت استفاده می­کنند و برای تبدیل متن­ها به عدد، آن­ها را در عدد 1 مانند نمونه زیر ضرب می­کنند:

در اینجا باید بگویم که اکسل یک تابع هم برای این کار (یعنی تبدیل متن به عدد) به نام VALUE دارد. بنابراین به جای ضربدر 1 می­توانید این فرمول را اینگونه بنویسید:

۳) تبدیل خروجی متنی یک تابع به عدد

پیش از این به شما گفتم که متن­ها معمولاً خروجی نرم افزارهایی هستند و ما خودمان آن­ها را تولید نمی­کنیم. اما واقعیت این است که گاهی ما خودمان هم عددی را به صورت متن! تولید می­کنیم و باید آن­ متن­ها را به عدد تبدیل نماییم.

داستان از این قرار است که خروجی توابعی مانند LEFT، RIGHT و MID و ... همگی متن هستند و نه عدد. مثلاً خروجی فرمول LEFT(570517, 2) مقدار متنی 57 است و نه عدد 57. بنابراین ما در بسیاری از موارد مجبور به نوشتن فرمول­هایی هستیم که خروجی / نتیجه آنها متن خواهد شد.

یادآوری: تابع LEFT از ابتدای یک متن، به تعدادی که مشخص می­کنیم، نویسه را جدا می­کند و به ما می­دهد. مثلاً LEFT("ATAL" , 2) یعنی از ابتدای متن ATAL برای ما 2 حرف را جدا کن و نتیجه/خروجی این فرمول، AT خواهد شد.

حال اجازه دهید که کاربرد این نکته را (خروجی متن توابع) به صورت یک نمونه مشهور عملی به شما بگویم.

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

بگذارید با مثال زیر دقیق­تر توضیح دهم. ما سال­های استخدامی تعدادی از کارکنان را داریم و می­دانیم استخدامی‌هایی که قبل از سال 88 بوده­اند، «رسمی» هستند و بعد از آن به صورت «قراردادی». حال می­خواهی فرمولی بنویسم تا نوع قرارداد هرکس را با توجه به سال استخدامی او مشخص کنیم.

می­دانیم که برای جدا کردن 2 رقم اول سال از تاریخ­ها باید از تابع LEFT استفاده کنیم و خواهیم نوشت:

و انتظار داریم که LEFT(B2,2) مقدار 80 شود و بدیهی است که 80 کمتر از 88 است و باید خروجی/نتیجه این فرمول مقدار «رسمی» شود. اما برخلاف انتظار خواهید دید که نتیجه این فرمول «قراردادی» می­شود.

باید بدانیم که خروجی تابع LEFT یک عدد نیست و یک متن است و شما متن 80 را با عدد 88 مقایسه کرده­اید و طبق قواعد مقایسه در کامپیوتر، متن­ها بعد از اعداد هستند به همین نتیجه مقایسه "88" >88  مقدار True خواهد شد و بنابراین خروجی تابع IF مقدار «قراردادی» است.

راه حل اینجاست که ما باید ابتدا خروجی تابع LEFT که یک متن است را به عدد تبدیل کنیم و سپس آن را می­توانیم با عدد 88 مقایسه کنیم. یعنی فرمول صحیح اینگونه است:

=IF( LEFT(B2, 2) * 1 < 88, "رسمی" , "قراردادی")

و یا اگر مایلید آن را با تابع VALUE اینگونه بنویسید:

=IF( VALUE( LEFT(B2, 2) ) < 88, "رسمی" , "قراردادی")

راستی اگر خواستید در اکسل خیلی خفن به نظر برسید می­توانید به جای ضربدر 1 از دوبار ضربدر -1 استفاده کنید، یعنی بنویسید:

=IF( --LEFT(B2, 2) < 88, "رسمی" , "قراردادی")

اگر هم خواستید که مدیرتان از فرمول به این سادگی­ها سر در نیاورد بنویسید:

=IF( LEFT(B2, 2) * FACT(0) < 88, "رسمی" , "قراردادی")

(نکته ریاضی: فاکتوریل عدد 0 مقدار 1 است)

نکته مهم: می­دانیم برای تابع VLOOKUP عدد و متن­ها متفاوت هستند. بنابراین اگر در جایی فرمول زیر را دیدید، باید بدانید که در این فرمول ابتدا متنی به عدد تبدیل شده است و سپس جستجوی آن انجام می­شود:

=VLOOKUP( A1 * 1, … , … , … )

۴) تبدیل متن به عدد - استفاده از ابزار Text to Columns

گاهی نوشتن فرمول در فایل­های بسیار بزرگ و با تعداد ستون­های زیاد، کاری سریع و ساده نیست. زیرا تعداد ستون­ها زیاد خواهد شد و یا اگر بخواهیم که آن­ها را COPY/PASTE کنیم، وقت­گیر است. مخصوصاً اگر ده­ها ستون باشد که باید تبدیل به عدد شوند.

یکی از ابزارهای بسیار سریع و ساده برای تبدیل استفاده از Text to Columns است. اول آنکه باید بدانید که این ابزار کاربرد دیگری دارد و اساساً برای تبدیل متن به عدد ساخته نشده است. اما روش کار (الگوریتم داخلی آن) به­گونه­ای است که برای ما این تبدیل را با سرعتی باور نکردنی انجام می­دهد.

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

قدم یکم) ابتدا کل ستون B را انتخاب کنید.

قدم دوم) بر روی گزینه Text to Column که در تب Data وجود دارد کلیک کنید تا یک پنجره باز شود.

قدم سوم) فقط!!! بر روی Finish کلیک کنید. (کاری به محتویات این پنجره نداشته باشید). خواهید دید که همه­ی مقادیر آن ستون به عدد تبدیل می­شوند.

نکته 1) کاربرد ابزار Text to Column تجزیه داده­های سلول­های یک ستون است. که آموزش آن از موضوع این مقاله خارج است.

نکته 2) برای هر ستون باید یکبار جداگانه این کار را انجام دهید. یعنی نمی­توانید ستون­های B تا H را انتخاب کنید و به یکباره آنها را به عدد تبدیل کنید و اگر این کار را انجام دهید به شما پیغام زیر را می­دهد:

۵) تبدیل متن به عدد - استفاده از امکانات Power Query

Power Query (بخوانید پاور کوئری) یکی از ابزارهای بسیار قوی و کاملی است که اخیراً به اکسل افزوده شده است و می­توان گفت که اکسل را وارد افق جدیدی در کار با داده­ها کرده است. یکی از وظایف اصلی پاورکوئری «تر و تمیز کردن داده‌ها» است. اگر اکسل شما 2016 و یا 2019 است، این ابزار را در Data به نام Get & Transform می­توانید بیابید.

توجه: در این مقاله نمی­توانم به آموزش جزئیات مفصل این ابزار معجزه­آسای اکسل بپردازم و توصیه می­کنم تا ویدئوهای آموزشی که در سایت فرساران برای شما آماده کرده­ام را از سایت دانلود کنید و ببیند:

www.farsaran.com/powerquery      

مزیت اصلی پاورکوئری نسبت به سایر روش­های تبدیل متن به عدد در اینجاست که لازم نیست همیشه آن­ را انجام دهیم، بلکه یکبار کوئری را می­سازیم و برای دفعات بعدی کافی است که آن کوئری را اجرا/Refresh کنیم تا همه آن تبدیل­ها، مجدد انجام شوند.

فقط در اینجا در تصویر زیر می­خواهم به شما نشان ­دهم که اگر از گزینه From Text/CSV پاورکوئری برای خواندن یک فایل متنی (مانند گزارش بانک) استفاده کنید، به صورت اتوماتیک نوع داده­ها را تشخیص می­دهد و تبدیل لازم را انجام خواهد داد.

و همچنین در محیط ویرایشگر پاورکوئری می‌توانید نوع داده­های هر ستون را به صورت دقیق­تری با پاورکوئری مشخص کنید و تبدیل نهایی برای شما توسط پاورکوئری انجام خواهد شد.

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

گاهی در اکسل اعدادی دارید و می­خواهید که آن­ها تبدیل به متن شوند. توجه داشته باشید که این مقادیر در حافظه کامپیوتر به صورت عدد ذخیره شده­­اند و اگر  Format Cells این اعداد را به Text تغییر دهید، هیچ اتفاقی در حافظه نمی­افتد و آن اعداد کماکان به صورت عدد (و نه متن) هستند.

تذکر مهم: هیچ وقت به آنچه در پنجره Format Cells می­بینید، اعتماد نکنید. یعنی Text بیانگر این نیست که حتماً مقادیر در حافظه به صورت متن هستند و همچنین دیدن Number به این معنی نیست که مقادیر در حافظه به صورت عددی هستند.

در اینجا یک پرسش بسیار رایج مطرح می­شود:

چطور از روی ظاهر سلول­ها می‌توان متن و یا عدد بودن سلول‌ها را تشخیص داد؟

مجدد تأکید می­کنم که از روی ظاهر سلول و یا دیدن تنظیمات Format Cells به هیچ طریقی نمی­توانید متوجه شوید که عدد هستند یا متن. راهکارهای متفاوتی دارید. مثلاً کل آن سلول­ها را انتخاب کنید و در Status Bar ببیند که چند تا از آن­ها عدد هستند و چند تا متن:

در تصویر قبل تعداد سلول­های پُر 12 عدد است که 6 تای آن عددی است و حتماً 6 تای دیگر متن است.

نکته: می­دانیم که در تنظیمات پیش فرض اکسل اگر چیزی عدد باشد راست چین خواهد شد (تا یکان و دهگان آن زیر هم قرار بگیرد) و اگر متن انگلیسی باشد، چپ چین. همچنین در تنظیمات پیش فرض خطای سبز رنگ را در صورتی که عددی به صورت متن ذخیره شده باشد، را خواهیم دید. اینکه تأکید می­کنم که از روی ظاهر قضاوت نکنید، زیرا ممکن است که فایل قبلاً ویرایش شده باشد و شما نمی‌­دانید که آیا پیش فرض­های اکسل تغییر کرده‌­اند یا نه.

یادآوری: در اینجا Count به معنای کل سلول­های پُر است و Numerical Count به معنای تعداد سلول‌هایی است که عددی دارند.

یادآوری: به نوار پایین صفحه اکسل اصطلاحاً Status Bar (نوار وضعیت) می­گویند.

نکته: اگر شما در Status Bar گزینه Count و ... را ندارید، بر روی Status Bar، Right Click کنید و در پنجره نمایش داده شده، بر روی آن­ها یک بار کلیک کنید تا نمایش داده شوند.

تذکر مهم: اگر داده­‌های شما بسیار زیاد است و شما شک دارید که آیا متن هستند و یا عدد، باید در این حالت حتماً با یکی از روش­های گفته شد (مثلاً فرمول نویسی) مطمئن شوید که همگی یکسان شده­‌اند.

حال برگردیم به موضوع اصلی این بخش یعنی تبدیل عدد به متن.

برای تبدیل اعداد به متن باید فرمول نویسی کنیم و تقریباً هر فرمولی که خروجی آن Text را می­توانید به کار ببرید.

مثلاً عدد را به چیزی بچسبانید. زیرا چسباندن، یک عملیات ریاضی نیست و نتیجه متن خواهد شد. فرض کنید در سلول A1 عدد 100 را داریم و سپس فرمول زیر را در سلول B1 بنویسیم:

=A1 & "salam"

حاصل این فرمول 100salam است و شکی نیست که یک متن است.

و اگر خواستید که عدد 100 به متن 100 تبدیل شود، (چیزی به آن اضافه نشود)، آن را به «رشته­ای به طول صفر» بچسبانید.

= A1 & ""

نکته: ما در دنیای کامپیوتر مقادیر عجیب و غریبی داریم مانند Zero Length String که در اکسل همان "" است و توجه داشته باشید که برابر Null و یا NaN که در سایر زبان­های برنامه نویسی وجود دارد، نیست.

البته می­توانید فرمول­‌ نامفهومی مانند زیر هم بنویسید که کاملاً صحیح است:

=LEFT(A1, 1000)

یعنی از ابتدای A1 برای من 1000 تای اول آن را جدا کن که همان کل مقدار 100 می‌شود و می­دانیم که خروجی LEFT یک متن است

سایر نکته‌ها

نکته 1) برای تشخیص متن یا عدد بودن یک سلول در اکسل به ترتیب توابع ISTEXT و ISNUMBER را داریم.

نکته 2) اگر در قسمت Custom از پنجره Format Cells فقط از نویسه «@» استفاده، معادل همان TEXT است.

نکته 3) در اکسل 2013 به بعد، ما ابزاری به نام Flash Fill داریم. این ابزار با کمک چند نمونه اولیه می­تواند الگویی را حدس بزند و آن الگو را برای سایر سلول­ها تکرار کند. از این ابزار برای تبدیل متن به عدد می­توان استفاده کرد اما به دلیل خطاهایی که محتمل است در تشخیص الگو و تبدیل داده­ها رخ دهد، من استفاده از این ابزار را فعلاً برای این کار پیشنهاد نمی­کنم.

سخن پایانی

به نظر من، می­توانیم اکسل را مانند یک زبان برنامه نویسی بدانیم بدون آنکه لازم باشد واقعاً برنامه‌­ای بنویسیم و یا از جزئیات برنامه نویسی مطلع باشیم. در مقدمات زبان­های برنامه نویسی شما با مفاهیم Data Type آشنا می­شوید و آنچه در این مقاله آموختید، بخشی از همین مفاهیم بود که در اکسل «به عمد و با هدف ساده سازی» از نظر شما مخفی شده است. دانستن مفاهیم برنامه نویسی به شما کمک می­کندعلت و یا دلیل بسیاری از چیزها را در اکسل درک کنید. اگر از کاربران حرفه‌ای اکسل هستید به شما پیشنهاد می‌کنم که با یادگیری زبان VBA‌ اکسل وارد دنیای برنامه نویسی گردید.

همچنین پیشنهاد می‌کنم که به فرزندان خودتان مهارت برنامه نویسی که از مهارت­های الزامی برای زندگی در قرن 21 است را بیاموزید.

برای شروع می­توانید از اسکرچ شروع کنید. اسکرچ زبان برنامه نویسی بسیار ساده و جالبی است که بچه­ها در آن بازی و انیمیشن و ... می­سازند و توسط دانشگاه MIT ساخته شده است.

من با هدف توسعه بچه­‌های ایران به صورت آزاد و رایگان ویدئوها و جزوه­ای برای آموزش زبان برنامه‌نویسی اسکرچ بر روی وب سایت قرار داده­‌ام که آن­ها را می­توانید از آدرس زیر دانلود کنید:

www.farsaran.com/scratch