آیا تاکنون برایتان پیش آمده عددی مانند 000123 را در اکسل بنویسید؟ اگر سعی در نوشتن صفر در اکسل کنید، بلافاصله متوجه می‌شوید که این برنامه صفرهای آغازین را به طور خودکار از ابتدای تمامی اعداد حذف می‌کند. با چند بار امتحان، درمی‌یابید که قرار است نوشتن صفر اول اعداد در اکسل، تبدیل به یک مسئله شود.
پس چگونه در اول عدد، صفر بنویسیم؟ بخصوص اگر بخواهیم لیستی از شماره تلفن‌ها، کد ملی، تاریخ تولد و … را تهیه کنیم و واقعا این صفرهای حیاتی را نیاز داشته باشیم. در خلال این تلاش‌ها برای حفظ صفر است که این عدد هیچ انگاشته شده، ناگهان شخصیت می‌یابد.
خوشبختانه، چندین ترفند برای نوشتن صفر قبل از عدد در اکسل وجود دارد. توضیح و آموزش 7 ترفند برای نوشتن صفر قبل از عدد در اکسل، موضوع امروزمان در مجله تکنولوژی ایده آل تک است. با ما این آموزش را دنبال کنید.
فرمت سفارشی
اگر کشوی فرمت را در گروه Number باز کنیم، فرمت‌های مختلفی را مییابیم که با کلیک روی هر یک، فرمت محدوده انتخابی تغییر می‌کند.
هنگام استفاده از فرمت سفارشی، تغییری در داده‌ها ایجاد نمی‌شود. با انتخاب یک سلول فرمت شده، مشاهده می‌کنیم که در نوار فرمول، عدد بدون صفر پیشین و در صفحه کار، با صفرهای پیشین خود نمایش داده می‌شود.
حال اگر این عدد را از سلول خود کپی کرده و در جای دیگری به صورت مقادیر پیست کنیم، صفرهای پیشین حذف می‌شوند.
نوشتن صفر در اکسل: ایجاد فرمت سفارشی
برای ایجاد یک فرمت سفارشی، به منظور نوشتن صفر اول اعداد در اکسل، مراحل زیر را طی می‌کنیم:
- ابتدا محدوده مورد نظر را برای ایجاد فرمت سفارشی انتخاب می‌کنیم؛
- کلید میانبر Ctrl+1 را فشار می‌دهیم یا اینکه روی محدوده انتخاب شده، راست کلیک کرده، سپس گزینه Format Cells را انتخاب می‌کنیم؛
- به زبانه Number رفته و روی فلش گوشه پایین سمت راست کلیک می‌کنیم.
- در لیست Category، گزینه Custom را انتخاب کنید؛
- در کادر موجود در زیر Type، فرمت مورد نظرمان را وارد می‌کنیم. به طور مثال، اگر می‌خواهیم تعداد کل ارقام به احتساب صفرهای آغازین، 6 رقم باشد، شش عدد صفر (000000) را به عنوان قالب سفارشی، در کادر خالی تایپ می‌کنیم. می‌توانیم نتیجه را در کادر Sample ببینیم.
- در پایان، روی OK کلیک می‌کنیم.
تغییر فرمت عدد به حروف
در صورت تغییر فرمت عدد و تبدیل آن به حروف، می‌توانیم هر تعداد صفر که بخواهیم، قبل از عدد وارد کنیم. اما باید بدانیم این روش همان‌طور که مزیت نوشتن صفر اول اعداد در اکسل را ممکن می‌کند، یک ایراد دارد و آن اینکه، دیگر نمی‌توان در اکسل روی این اعداد عملیات ریاضی مانند جمع زدن انجام داد.
چند روش برای افزودن صفر به اول اعداد در اکسل وجود دارد که در آن، نتیجه نهایی حروف است و نه عدد. در ادامه به این روش‌ها می‌پردازیم.
نوشتن صفر آغازین در اکسل: تغییر فرمت عدد به حروف
در اکسل گزینه‌ای برای تغییر فرمت هر سلول، وجود دارد. با اعمال تغییر فرمت محدوده، اکسل هر آنچه را که در محدوده مورد نظر وارد می‌شود، حتی اگر عدد هم باشند، به عنوان متن تلقی می‌کند. این کار ساده، نوشتن صفر قبل از عدد در اکسل را ممکن می‌کند.
برای تبدیل عدد به حروف، به منظور نوشتن صفر قبل از عدد در اکسل، مراحل زیر را طی می‌کنیم:
- محدوده‌‌ای از سلول‌هایی را که باید به متن تبدیل شوند، انتخاب می‌کنیم
- روی زبانه Home کلیک می‌کنیم
- در گروه Number، روی کشویی Format کلیک می‌کنیم
- میان گزینه‌ها، Text را که آخرین گزینه است، انتخاب می‌کنیم
اکنون اگر بخواهیم اقدام به نوشتن صفر قبل از عدد در اکسل کنیم، این صفرها دیگر حذف نمی‌شوند؛ زیرا آن‌ها به عنوان متن در سلول وارد شده‌اند.
درج آپاستروف در ابتدای عدد
می‌توانیم به اکسل دستور بدهیم که یک عدد را به عنوان مقادیر متنی شناسایی کند. می‌توان این کار را با تایپ یک آپاستروف « ’ » پیش از عدد انجام داد. به این ترتیب، هنگام تایپ عدد، صفرهای قبل از آن حفظ می‌شود.
فقط کافی است که هنگام تایپ، قبل از عدد، یک آپاستروف بگذاریم. این امر به اکسل دستور می‌دهد که داده‌ای را که قرار است تایپ شود، یک متن تلقی کند و نه عدد.
پس از گذاشتن آپاستروف قبل از صفر، وقتی Enter می‌زنیم، صفرهای پیشین قابل مشاهده هستند. با این‌حال، « ’ » دیده نمی‌شود. نامرئی بودن آپاستروف به این معنی نیست که پاک یا حذف شده است. هنگامی که روی سلول حاوی « ’ » کلیک می‌کنیم، در نوار فرمول ظاهر می‌شود.
این روش سریع و آسان است ولی یک ایرادی دارد: باید در هر سلول، تک به تک، پیش از عدد آپاستروف اضافه کرد.
استفاده از تابع Text
این روش مخصوص زمانی است که لیستی از اعداد بدون صفر وارد شده باشند و بخواهیم، در ابتدای اعداد، یک یا چند صفر اضافه کنیم. این کار را می‌توانیم با کمک فرمول‌نویسی یک تابع متنی در کادر فرمول انجام دهیم.
تابع TEXT این اجازه را به کاربر می‌دهد که یک فرمت سفارشی را برای هر تعداد داده موجود در صفحه اعمال کند. فرمول آن به شکل زیر است.
|
=TEXT(Value, “Format”) |
در فرمول بالا دو مقدار وجود دارد که باید به صورت زیر جای‌گذاری شوند.
- Value : نام سلول حاوی مقدارعددی است که می خواهیم آن را به مقدار متنی تبدیل کنیم. با کلیک روی سلول، اکسل به طور خودکار نام سلول را وارد می‌کند.
- Format : قالبی است که می‌خواهیم اعمال کنیم. این قالب می‌تواند تعدادی رقم یا قالب تاریخ باشد. این قالب حتماً باید داخل گیومه نوشته شود. به طور مثال “mm / dd / yy”.
مثال برای نوشتن صفر قبل از عدد در اکسل با تابع Text
در تصویر بالا ستون A از 5 سلول تشکیل شده است. می‌خواهیم با استفاده از فرمول تابع text، این اعداد را تبدیل به متن کنیم؛ همچنین، به ابتدای ارقام همه این 5 سلول، صفر اضافه کنیم، به طوری که همه اعداد موجود در این ستون را 6 رقمی کنیم.
- ابتدا سلول B2 را انتخاب می‌کنیم. در این سلول یک مساوی، سپس کلمه TEXT و یک پرانتز تایپ می‌کنیم؛
- روی سلول (A2) کلیک می‌کنیم. به طور خودکار نام سلول با رنگ آبی، به فرمول اضافه می‌شود. سپس یک کاما تایپ می‌کنیم.
- قالب یا فرمت مورد نظرمان را داخل دو گیومه وارد می‌کنیم. در اینجا ما قصد داریم با افزودن صفر به ابتدای اعداد، آن‌ها را شش رقمی کنیم. پس قالب ما 6 صفر است: “000000”. پس از وارد کردن قالب، پرانتز را می‌بندیم و اینتر می‌زنیم.
- در کادری که ظاهر می‌شود، Yesمی‌زنیم.
قالب بندی سلول‌ها به طور گروهی با تابع Text
حالا می‌خواهیم فرمت بقیه سلول‌ها را نیز، با این فرمول، تغییر دهیم.
- روی سلول B2، کلیک می‌کنیم.
- راست کلیک کرده Copy یا کلید میانبر Ctrl+C را می‌زنیم.
- اکنون سلول‌های خالی روبروی سلول‌های اعداد مورد نظر را انتخاب می‌کنیم و اینتر می‌زنیم.
فرمول تبدیل عدد به یک عدد شش رقمی با صفرهای آغازین:
| = TEXT ( B3, “000000” ) |
استفاده از تابع Right
راه دیگر برای نوشتن صفر قبل از عدد در اکسل، استفاده از تابع «رایت» است. در اکسل، می‌توان یک رشته صفر را به یک عدد اضافه کرد؛ سپس با استفاده از تابع RIGHT موارد اضافی را حذف کرد. در نتیجه صفرها در آغاز عدد باقی می‌مانند.
تابع رایت، عدد یا حروف مورد نظر را از یک رقم یا عبارت استخراج می‌کند.
تابع Right : چگونه در اول عدد صفر بنویسیم؟
- یک سلول، به طور مثال B3 را انتخاب و در سلول کنارش (C3)، این فرمول را تایپ می‌کنیم :
| =”000000”& |
- روی سلول B3 کلیک می‌کنیم تا به طور اتوماتیک به فرمول اضافه شود، سپس اینتر می‌زنیم.
- اکنون در سلول C3، عدد سلول B3 با 6 صفر در آغازش تولید می‌شود.
- سلول C3 را کپی می‌کنیم. سپس روی سلول‌هایی که می‌خواهیم این فرمول پیاده شود، درگ کرده، اینتر می‌زنیم.
حال چگونه با تابع RIGHT، صفرهای اضافی را حذف و ارقام مورد نظرمان را استخراج کنیم؟
نحوه اجرای تابع رایت برای نوشتن صفر اول اعداد در اکسل
در قسمت قبل، توانستیم 6 صفر در اول کدهایمان قرار دهیم. اکنون می‌خواهیم کدهای تولید شده را با تابع رایت، 6 رقمی کنیم.
- در نوار فرمول، این فرمول را تایپ می‌کنیم.
| = RIGHT ( Text, [Number]) |
مقادیر در این فرمول به صورت زیر است.
- Text: همان سلولی است که می‌خواهیم از آن، عدد مورد نظر را استخراج کنیم.
- Number: تعداد ارقامی مورد نظر ماست. وارد کردن این مقدار اختیاری است. ولی اگر این مقدار وارد نشود، فقط اولین عدد استخراج می‌شود.
حال برای فهم بهتر، فرمول را روی مثال قبل پیاده می‌کنیم.
- روی سلول C3 کلیک می‌کنیم.
- بعد از علامت مساوی، واژه RIGHT را به همراه یک پرانتز باز و بعد از B3، یک کاما و 6(به معنای 6 رقم) و بعد یک پرانتز بسته را مطابق فرمول زیر تایپ کرده، سپس اینتر می‌زنیم.
| = RIGHT (”000000”&6) |
- نویسه‌ها دقیقاً شش‌تایی می‌شوند.
- برای اعمال این فرمول روی بقیه سلول‌ها، سلول B3 را کپی می‌کنیم و بعد با درگ موس، سلول‌های دیگر را انتخاب کرده و اینتر می‌زنیم.
استفاده از تابع BASE
تابع مبنا در نسخه‌های اکسل 2013 به بالا یافت می‌شود. برای استفاده از تابع مبنا، ابتدا باید بدانیم که تبدیل مبنا چیست. با یک مثال به این پرسش پاسخ می‌دهیم: به طور مثال تبدیل مبنای ۱۰ به ۲.
برای تبدیل مبنای ۱۰ به ۲، باید عدد در مبنای ۱۰ را مرتبا تقسیم بر ۲ کنیم و باقیمانده‌ها را نگه‌داریم. این عمل را آنقدر ادامه می‌دهیم تا خارج قسمت صفر شود. در پایان باقیمانده‌ها را از راست به چپ کنار هم می‌نویسیم.
تابع BASE به منظور تبدیل مبنا است. این تابع برای تبدیل کردن اعداد از مبنای ۱۰ به اعداد در مبناهای دیگر استفاده می‌شود.
این تابع سه ورودی (Number، Base ، MinLength ) به شرح زیر دریافت می‌کند.
| = BASE ( Number, Base, [MinLength]) |
- Number : ورودی اول است. این همان عدد مورد نظر است که می‌خواهیم مبنای آن را تغییر دهیم. این عدد در مبنای ۱۰ وارد می‌شود.
- Base : ورودی دوم مبنا است. در این ورودی مشخص می‌کنیم عدد خروجی در چه مبنایی قرار گیرد.
- MinLength: ورودی سوم مبناست. حداقل رقمی است که تابع موظف به تولید آن است.
نحوه درج تابع BASE برای نوشتن صفر اول اعداد در اکسل
اکنون می‌خواهیم با استفاده از تابع BASE چند صفر در آغاز عدد بگذاریم.
| = BASE ( B3, 10, 6) |
فرمول بالا، عدد سلول B3 را بر مبنای 10 تبدیل می‌کند. در واقع این عدد، از همان ابتدا، بر مبنای 10 بوده است. این فرمول عدد را در مبنای 10 نگه می‌دارد و آن را تبدیل به مقدار متنی با حداقل شش رقم می‌کند. درصورتی که مقدار تبدیل شده، کمتر از 6 رقم باشد، این فرمول قبل از عدد، چند صفر می‌گذارد تا آن را 6 رقمی کند.
سخن پایانی
مشاهده حذف همه صفرها از اول عدد، ناامید کننده است. وقتی در مییابیم چگونه در اول عدد صفر بنویسیم، دیگر دچار سردرگمی نمیشویم.
در این مقاله 7 روش معرفی کردیم که با استفاده از آن‌ها می توان صفرهای ابتدای عدد را از زیر قیچی اکسل بیرون کشید. البته تنها با روش فرمت سفارشی که در ابتدای مقاله، به عنوان اولین روش معرفی شد؛ می‌توان ماهیت عددی صفر را حفظ کرد. سایر روش‌ها، عدد ما را تبدیل به متن می‌کند.
این بستگی به نیاز شما دارد که از کدام روش استفاده کنید. البته روش‌های پیشرفته تری نیز مانند استفاده همزمان از ویراشگر پاور کوئری (Power Query ) و تابع تکس پد استارت (Text.PadStart) نیز وجود دارد. البته استفاده از این روش‌ها نیازمند این است که یک کاربر حرفه‌ای اکسل باشید
به هر حال نیاز شما هر چه که باشد، راه حل خوبی در این مقاله از مجله تکنولوژی ایده آل تک پیدا خواهید کرد.