يغطي هذا البرنامج التعليمي الطرق المختلفة لإضافة أو الاحتفاظ بالأصفار البادئة وكذلك إزالة الأصفار البادئة في Excel.
عندما تقوم بإدخال أرقام أو استيرادها باستخدام واحد أو أكثر من الأصفار البادئة ، مثل 000652 ، يقوم Excel تلقائيًا بإزالة هذه الأصفار ، ولا يظهر سوى الرقم نفسه في الخلايا (652). هذا لأن الأصفار البادئة ليست ضرورية للحسابات ولا تعد.
ومع ذلك ، هناك أوقات تكون فيها هذه الأصفار البادئة ضرورية ، على سبيل المثال عند إدخال أرقام الهوية أو أرقام الهواتف أو أرقام بطاقات الائتمان أو رموز المنتجات أو الرموز البريدية ، وما إلى ذلك. لحسن الحظ ، يوفر لنا Excel عدة طرق لإضافة أو الاحتفاظ بالأصفار البادئة في الخلايا. في هذه المقالة ، سنوضح لك الطرق المختلفة لإضافة أو الاحتفاظ بالأصفار البادئة وإزالة الأصفار البادئة.
إضافة الأصفار البادئة في Excel
بشكل أساسي ، هناك طريقتان يمكنك استخدامهما لإضافة الأصفار البادئة: الأولى ، تنسيق رقمك كـ "نص" ؛ ثانيًا ، استخدم التنسيق المخصص لإضافة الأصفار البادئة. قد تعتمد الطريقة التي تريد استخدامها على ما تريد القيام به بالرقم.
قد ترغب في إضافة الصفر البادئ عندما تقوم بإدخال أرقام معرفات فريدة أو أرقام حسابات أو أرقام ضمان اجتماعي أو رموز بريدية ، وما إلى ذلك ، لكنك لن تستخدم هذه الأرقام للحسابات أو في الوظائف ، لذلك من الأفضل تحويل هذه الأرقام أرقام إلى نص. لن تقوم أبدًا بجمع أو متوسط أرقام الهواتف أو أرقام الحسابات.
هناك عدة طرق يمكنك من خلالها إضافة أو حشو الأصفار قبل الأرقام عن طريق تنسيقها كنص:
- تغيير تنسيق الخلية إلى نص
- إضافة فاصلة عليا (')
- باستخدام وظيفة TEXT
- باستخدام وظيفة REPT / LEN
- استخدم دالة CONCATENATE / عامل العطف (&)
- باستخدام وظيفة RIGHT
تغيير تنسيق الخلية إلى نص
هذه إحدى أبسط الطرق لإضافة الأصفار البادئة إلى أرقامك. إذا كنت ستدخل أرقامًا فقط وتريد الاحتفاظ بالأصفار البادئة أثناء الكتابة ، فهذه هي الطريقة المناسبة لك. من خلال تغيير تنسيق الخلية من عام أو رقم إلى نص ، يمكنك إجبار Excel على التعامل مع أرقامك كقيم نصية وسيظل أي شيء تكتبه في الخلية كما هو تمامًا. إليك كيف تفعل ذلك:
حدد الخلية (الخلايا) التي تريد إضافة أصفار بادئة فيها. انتقل إلى علامة التبويب "الصفحة الرئيسية" ، وانقر على المربع المنسدل "تنسيق" في مجموعة الأرقام ، وحدد "نص" من خيارات التنسيق.
الآن عند كتابة الأرقام الخاصة بك ، لن يقوم Excel بحذف أي صفر بادئ منه.
قد ترى مثلثًا أخضر صغيرًا (مؤشر خطأ) في الزاوية العلوية اليسرى من الخلية وعندما تحدد تلك الخلية ، ستظهر لك علامة تحذير تشير إلى أنك قمت بتخزين الرقم كنص.
لإزالة رسالة الخطأ ، حدد الخلية (الخلايا) ، وانقر فوق علامة التحذير ، ثم حدد "تجاهل الخطأ" من القائمة.
يمكنك أيضًا كتابة أرقام الهواتف بمسافة أو واصلة بين الرقم ، وسيعامل Excel تلقائيًا هذه الأرقام كنص.
باستخدام البادئة الفاصلة العليا (‘)
هناك طريقة أخرى لإضافة الأصفار البادئة في Excel وهي إضافة فاصلة عليا (') في بداية الرقم. سيؤدي هذا إلى إجبار Excel على إدخال رقم كنص.
ما عليك سوى كتابة علامة اقتباس أحادية قبل أي أرقام واضغط على "إدخال". سيترك Excel الأصفار البادئة كما هي ، لكن (') لن تكون مرئية في ورقة العمل إلا إذا قمت بتحديد الخلية.
استخدام وظيفة النص
تضيف الطريقة المذكورة أعلاه الأصفار إلى الأرقام أثناء كتابتها ، ولكن إذا كان لديك بالفعل قائمة بالأرقام وتريد وضع الأصفار البادئة قبلها ، فإن وظيفة TEXT هي الطريقة المناسبة لك. تتيح لك وظيفة TEXT تحويل الأرقام إلى سلاسل نصية أثناء تطبيق تنسيق مخصص.
صيغة الدالة TEXT:
= TEXT (القيمة ، format_text)أين،
- القيمة - إنها القيمة العددية التي تحتاجها للتحويل إلى نص وتطبيق التنسيق.
- format_text - هو التنسيق الذي تريد تطبيقه.
باستخدام وظيفة TEXT ، يمكنك تحديد عدد الأرقام التي يجب أن يحتوي عليها رقمك. على سبيل المثال ، إذا كنت تريد أن تتكون أرقامك من 8 أرقام ، فاكتب 8 أصفار في الوسيطة الثانية للدالة: "00000000". إذا كان لديك رقم مكون من 6 أرقام في خلية ، فستضيف الوظيفة يدويًا صفرين بادئين وإذا كان لديك رقمان مثل 56 ، فسيكون الباقي أصفار (00000056).
على سبيل المثال ، لإضافة الأصفار البادئة وجعل الأرقام تتكون من 6 أرقام طويلة ، استخدم هذه الصيغة:
= TEXT (A2، "000000")نظرًا لأن لدينا 6 أصفار في الوسيطة الثانية للصيغة ، فإن الوظيفة تحول سلسلة الأرقام إلى سلسلة نصية وتضيف 5 أصفار بادئة لجعل السلسلة تتكون من 6 أرقام.
ملحوظة: يرجى تذكر وضع أكواد التنسيق بين علامتي اقتباس مزدوجتين في الوظيفة.
يمكنك الآن تطبيق نفس الصيغة على باقي الخلايا عن طريق سحب مقبض التعبئة. كما ترى ، تقوم الوظيفة بتحويل الأرقام إلى نصوص وإضافة الأصفار البادئة إلى الأرقام بحيث يكون العدد الإجمالي للأرقام هو 6.
ستُرجع الدالة TEXT القيمة دائمًا كسلسلة نصية ، وليس رقمًا ، لذلك لن تتمكن من استخدامها في العمليات الحسابية ولكن لا يزال بإمكانك استخدامها في صيغ البحث مثل VLOOKUP أو INDEX / MATCH لجلب تفاصيل المنتج باستخدام معرفات المنتج.
استخدام وظيفة CONCATENATE / عامل العارضة (&)
إذا كنت تريد إضافة عدد ثابت من الأصفار البادئة قبل كل الأرقام في عمود ، فيمكنك استخدام دالة CONCATENATE أو عامل تشغيل علامة العطف (&).
بناء جملة الدالة CONCATENATE:
= CONCATENATE (text1، [text2]، ...)أين،
النص 1 - عدد الأصفار المطلوب إدخالها قبل الرقم.
النص 2 - الرقم الأصلي أو مرجع الخلية
صيغة عامل العلامة:
= Value_1 & Value_2 أين,
Value_1 هي الأصفار البادئة المطلوب إدخالها قبل الرقم و Value_2 هي الرقم.
على سبيل المثال ، لإضافة صفرين فقط قبل رقم ، استخدم أيًا من هذه الصيغة:
= CONCATENATE ("00"، A2)الوسيطة الأولى هي صفرين (“00”) لأننا نريد وضع صفرين قبل الرقم في A2 (وهي الوسيطة الثانية).
أو،
= "00" & A2هنا ، الوسيطة الأولى هي 2 صفرين ، متبوعة بعامل التشغيل "&" ، والوسيطة الثانية هي الرقم.
كما ترى تضيف الصيغة صفرين فقط إلى جميع الأرقام في عمود بغض النظر عن عدد الأرقام التي يحتوي عليها الرقم.
تنضم كلتا الصيغتين إلى عدد معين من الأصفار قبل الأرقام الأصلية وتخزنها كسلاسل نصية.
باستخدام وظيفة REPT / LEN
إذا كنت تريد إضافة أصفار بادئة إلى البيانات الرقمية أو الأبجدية الرقمية وتحويل السلسلة إلى نص ، فاستخدم وظيفة REPT. تُستخدم وظيفة REPT لتكرار حرف (أحرف) عدد معين من المرات. يمكن أيضًا استخدام هذه الوظيفة لإدخال أرقام ثابتة من الأصفار البادئة قبل الرقم.
= REPT (نص ، number_times)حيث "text" هو الحرف الذي نريد تكراره (في حالتنا "0") ووسيطة "number_times" هي عدد المرات التي نريد تكرار هذا الحرف.
على سبيل المثال ، لإنشاء خمسة أصفار قبل الأرقام ، ستبدو الصيغة كما يلي:
= REPT (0،5) & A2ما تفعله الصيغة هو تكرار 5 أصفار وربط السلسلة الرقمية في A2 وإرجاع النتيجة. بعد ذلك ، يتم تطبيق الصيغة على الخلية B2: B6 باستخدام مقبض التعبئة.
تضيف الصيغة أعلاه عددًا ثابتًا من الأصفار قبل الرقم ، لكن الطول الإجمالي للرقم يختلف اعتمادًا على الرقم.
إذا كنت ترغب في إضافة أصفار بادئة عند الحاجة لإنشاء سلاسل أحرف طويلة (بطول ثابت) ، فيمكنك استخدام وظائف REPT و LEN معًا.
بناء الجملة:
= REPT (نص ، number_times-LEN (نص)) & خليةعلى سبيل المثال ، لإضافة أصفار مسبوقة إلى القيمة في A2 وإنشاء سلسلة مكونة من 5 أحرف ، جرب هذه الصيغة:
= REPT (0،5-LEN (A2)) & A2هنا ، تحصل "LEN (A2)" على إجمالي طول السلسلة / الأرقام في الخلية A2. "5" هو الحد الأقصى لطول السلسلة / الأرقام التي يجب أن تحتويها الخلية. ويضيف الجزء "REPT (0،5-LEN (A2))" عدد الأصفار عن طريق طرح طول السلسلة في A2 من الحد الأقصى لعدد الأصفار (5). بعد ذلك ، يتم ضم عدد من 0 قبل قيمة A2 لإنشاء سلسلة ذات طول ثابت.
باستخدام وظيفة RIGHT
هناك طريقة أخرى لتعبئة الأصفار البادئة قبل سلسلة في Excel وهي استخدام الدالة RIGHT.
يمكن للدالة RIGHT إضافة عدد من الأصفار إلى بداية الرقم واستخراج الأحرف N الموجودة في أقصى اليمين من القيمة.
بناء الجملة:
= RIGHT (text، num_chars)- نص هي الخلية أو القيمة التي تريد استخراج الأحرف منها.
- num_chars هو عدد الأحرف المراد استخراجها من النص. إذا لم يتم تقديم هذه الوسيطة ، فسيتم استخراج الحرف الأول فقط.
بالنسبة لهذه الطريقة ، نقوم بربط الحد الأقصى لعدد الأصفار بمرجع الخلية الذي يحتوي على السلسلة في وسيطة "النص".
لإنشاء رقم مكون من 6 أرقام استنادًا إلى سلسلة الأرقام في A بأصفار بادئة ، جرب هذه الصيغة:
= RIGHT ("0000000" & A2،6)تضيف الوسيطة الأولى (نص) الصيغة 7 أصفار إلى القيمة الموجودة في A2 (“0000000” & A2) ، ثم تُرجع الأحرف السبعة الموجودة في أقصى اليمين ، مما ينتج عنه بعض الأصفار البادئة.
إضافة الأصفار البادئة باستخدام تنسيق الأرقام المخصص
إذا استخدمت أيًا من الطرق المذكورة أعلاه لوضع الأصفار البادئة قبل الأرقام ، فستحصل دائمًا على سلسلة نصية ، وليس رقمًا. ولن تستخدم كثيرًا في العمليات الحسابية أو في الصيغ الرقمية.
أفضل طريقة لإضافة الأصفار البادئة في Excel هي تطبيق تنسيق أرقام مخصص. إذا أضفت أصفارًا بادئة عن طريق إضافة تنسيق أرقام مخصص إلى الخلية ، فلن يغير ذلك قيمة الخلية ولكن طريقة عرضها فقط. ستظل القيمة كرقم وليس نصًا.
لتغيير تنسيق رقم الخلايا ، اتبع الخطوات التالية:
حدد الخلية أو نطاق الخلايا حيث تريد إظهار الأصفار البادئة. ثم ، انقر بزر الماوس الأيمن في أي مكان داخل النطاق المحدد وحدد خيار "تنسيق الخلايا" من قائمة السياق. أو اضغط على مفاتيح الاختصار Ctrl + 1.
في نافذة تنسيق الخلايا ، انتقل إلى علامة التبويب "الرقم" وحدد "مخصص" ضمن خيارات الفئة.
أدخل عدد الأصفار في مربع "النوع:" لتحديد العدد الإجمالي للأرقام التي تريد عرضها في الخلية. على سبيل المثال ، إذا كنت تريد أن يتكون الرقم من 6 أرقام ، فأدخل "000000" كرمز تنسيق مخصص. ثم ، انقر فوق "موافق" للتطبيق.
سيعرض هذا الأصفار البادئة قبل الأرقام وإذا كان الرقم أقل من 6 أرقام ، فسيتم ترك الصفر قبله.
ستظهر الأرقام فقط على أنها تحتوي على أصفار بادئة بينما ستبقى القيمة الأساسية دون تغيير. إذا حددت خلية بتنسيق مخصص ، فستظهر لك الرقم الأصلي في شريط الصيغة
هناك الكثير من العناصر النائبة الرقمية التي يمكنك استخدامها في تنسيق الأرقام المخصص الخاص بك. ولكن لا يوجد سوى عنصرين نائبين أساسيين يمكنك استخدامهما لإضافة أصفار بادئة في الأرقام.
- 0 – إنه العنصر النائب للرقم الذي يعرض أصفارًا زائدة. يعرض الأرقام المفروضة من 0 إلى 9 سواء كان الرقم ذا صلة بالقيمة أم لا. على سبيل المثال ، إذا قمت بكتابة 2.5 برمز التنسيق 000.00 ، فسيتم عرض 002.50.
- # – إنه العنصر النائب للرقم الذي يعرض أرقامًا اختيارية ولا يتضمن أصفارًا إضافية. على سبيل المثال ، إذا قمت بكتابة 123 برمز التنسيق 000 # ، فسيتم عرض 0123.
وأيضًا ، سيتم عرض أي علامة ترقيم أو أي حرف آخر تقوم بتضمينه في كود التنسيق كما هو. يمكنك استخدام أحرف مثل الواصلة (-) ، الفاصلة (،) ، الشرطة المائلة للأمام (/) ، إلخ.
على سبيل المثال ، يمكنك أيضًا تنسيق الأرقام كأرقام هواتف باستخدام تنسيق مخصص.
تنسيق التعليمات البرمجية في مربع الحوار تنسيق الخلايا:
النتائج:
دعنا نطبق رمز التنسيق هذا في المثال التالي:
##0000
كما ترى ، فإن الرقم "0" سيضيف أصفارًا إضافية بينما لا يضيف "#" أصفارًا غير ذات دلالة:
يمكنك أيضًا استخدام رموز التنسيق المحددة مسبقًا في قسم "التنسيقات الخاصة" في مربع حوار تنسيق الخلايا للرموز البريدية وأرقام الهواتف وأرقام الضمان الاجتماعي.
يوضح الجدول التالي الأرقام ذات الأصفار البادئة حيث يتم تطبيق رموز تنسيق "خاصة" مختلفة على أعمدة مختلفة:
إزالة الأصفار البادئة في Excel
الآن ، لقد تعلمت كيفية إضافة الأصفار البادئة في Excel ، دعنا نرى كيفية إزالة الأصفار البادئة من عدد السلاسل. في بعض الأحيان ، عند استيراد بيانات من مصدر خارجي ، قد ينتهي الأمر بالأرقام باحتوائها على بادئة أصفار ويتم تنسيقها كنص. في مثل هذه الحالات ، تحتاج إلى إزالة الأصفار البادئة وتحويلها مرة أخرى إلى أرقام ، حتى تتمكن من استخدامها في الصيغ.
هناك طرق مختلفة يمكنك من خلالها إزالة الأصفار البادئة في Excel وسنراها واحدة تلو الأخرى.
قم بإزالة الأصفار البادئة عن طريق تغيير تنسيق الخلية
إذا تمت إضافة الأصفار البادئة عن طريق تنسيق الأرقام المخصص ، فيمكنك إزالتها بسهولة عن طريق تغيير تنسيق الخلايا. يمكنك معرفة ما إذا كانت الخلايا الخاصة بك منسقة بشكل مخصص من خلال النظر إلى شريط العناوين (ستكون الأصفار مرئية في الخلية وليس في شريط العناوين).
لإزالة الأصفار المسبوقة ، حدد الخلايا ذات الأصفار البادئة ، وانقر على مربع "تنسيق الأرقام" ، وحدد خيار التنسيق "عام" أو "رقم".
الآن ، اختفت الأصفار البادئة:
احذف الأصفار البادئة بتحويل النص إلى أرقام
إذا تمت إضافة الأصفار البادئة عن طريق تغيير تنسيق الخلية أو عن طريق إضافة الفواصل العليا قبل الأرقام أو إضافتها تلقائيًا عند استيراد البيانات ، فإن أسهل طريقة لتحويلها إلى أرقام هي استخدام خيار التحقق من الخطأ. إليك كيف تفعل ذلك:
يمكنك استخدام هذه الطريقة إذا كانت الأرقام الخاصة بك محاذاة إلى اليسار وكانت خلاياك بها مثلث أخضر صغير (مؤشر خطأ) في الزاوية العلوية اليسرى من الخلايا. هذا يعني أن الأرقام منسقة كنص.
حدد هذه الخلايا وسترى تحذيرًا أصفر اللون في الجزء العلوي الأيمن من التحديد. ثم انقر فوق الخيار "التحويل إلى رقم" من القائمة المنسدلة.
ستتم إزالة الأصفار الخاصة بك وسيتم تحويل الأرقام مرة أخرى إلى تنسيق الأرقام (محاذاة إلى اليمين).
إزالة الأصفار البادئة بواسطة اضرب / قسمة على 1
طريقة أخرى سهلة وأفضل لإزالة المسافة البادئة هي ضرب أو قسمة الأرقام في 1. قسمة أو ضرب القيمة لا يغير القيمة ، إنه ببساطة يحول القيمة إلى رقم ويزيل الأصفار البادئة.
للقيام بذلك ، اكتب الصيغة في المثال أدناه في خلية واضغط على ENTER. ستُزال الأصفار البادئة وتحول السلسلة إلى رقم.
بعد ذلك ، قم بتطبيق هذه الصيغة على الخلايا الأخرى باستخدام مقبض التعبئة.
يمكنك تحقيق نفس النتائج باستخدام أمر "لصق خاص". إليك الطريقة:
اكتب القيمة الرقمية "1" في خلية (دعنا نقول في B2) وانسخ هذه القيمة.
بعد ذلك ، حدد الخلايا التي تريد إزالة الأصفار البادئة فيها. ثم ، انقر بزر الماوس الأيمن فوق التحديد ثم حدد الخيار "لصق خاص".
في مربع الحوار "لصق خاص" ، ضمن "العملية" ، اختر إما خيار "ضرب" أو "تقسيم" وانقر على "موافق".
هذا كل شيء ، ستتم إزالة الأصفار البادئة تاركًا السلاسل كأرقام.
قم بإزالة الأصفار البادئة باستخدام الصيغ
هناك طريقة أخرى سهلة لحذف الأصفار مسبوقة باستخدام الدالة VALUE. قد تكون هذه الطريقة مفيدة سواء تمت إضافة الأصفار البادئة باستخدام صيغة أخرى أو علامة اقتباس أحادية أو بتنسيق مخصص.
= VALUE (A1)يمكن أن تكون وسيطة الصيغة قيمة أو مرجع الخلية الذي يحتوي على القيمة. تزيل الصيغة الأصفار البادئة وتحول القيمة من نص إلى رقم. ثم قم بتطبيق الصيغة على باقي الخلايا.
في بعض الأحيان ، قد ترغب في إزالة الأصفار البادئة ولكنك تريد الاحتفاظ بالأرقام بتنسيق النص. في مثل هذه الحالات ، يجب عليك استخدام الدالتين TEXT () و VALUE () معًا مثل هذا:
= TEXT (VALUE (A1)، "#")تحوّل الدالة VALUE القيمة الموجودة في A1 إلى رقم. لكن الوسيطة الثانية ، "#" تحول القيمة مرة أخرى إلى تنسيق نصي بدون أي أصفار إضافية. نتيجة لذلك ، ستحصل على الأرقام بدون أي أصفار بادئة ولكن لا تزال بتنسيق النص (محاذاة إلى اليسار).
إزالة الأصفار البادئة باستخدام ميزة Text to Columns في Excel
هناك طريقة أخرى لإزالة الأصفار البادئة وهي استخدام ميزة Text to Columns في Excel.
حدد نطاق الخلايا التي تحتوي على أرقام بأصفار بادئة.
بعد ذلك ، انتقل إلى علامة التبويب "البيانات" وانقر على الزر "نص إلى أعمدة" في مجموعة أدوات البيانات.
سيظهر معالج "تحويل النص إلى أعمدة". في الخطوة 1 من 3 ، حدد "محدد" وانقر على "التالي".
في الخطوة 2 من 3 ، قم بإلغاء تحديد كل المحددات وانقر على "التالي".
في الخطوة الأخيرة ، اترك خيار تنسيق بيانات العمود كـ "عام" واختر الوجهة (الخلية الأولى من النطاق) حيث تريد أرقامك بدون الأصفار البادئة. ثم ، انقر فوق "إنهاء"
وستحصل على الأرقام ذات المسافة البادئة التي تمت إزالتها في عمود منفصل كما هو موضح أدناه.
هذا كل شيء.