كيفية استخراج رقم من سلسلة في Excel
مايكروسوفت أوفيس مكتب مايكروسوفت اكسل بطل تفوق / / June 07, 2023
نشرت
عند استيراد البيانات إلى Excel ، قد تحتاج إلى فصلها. تعرف على كيفية استخراج رقم من سلسلة في Excel باستخدام هذا الدليل.
يعد Excel جدول بيانات قويًا ، لكنه لا يمكنه عمل المعجزات. إذا كنت تستورد البيانات ، وكانت هذه البيانات في حالة من الفوضى ، فلن يتمكن Excel من فعل الكثير بها. على سبيل المثال ، إذا كانت لديك قيم مرتبطة بأحرف أخرى كجزء من سلسلة ، فلن تتمكن من إجراء العمليات الحسابية عليها إلا إذا قمت باستخراجها أولاً.
لحسن الحظ ، من الممكن أن تفعل ذلك بالضبط ، على الرغم من أنه قد يكون أكثر تعقيدًا مما قد تعتقد في البداية. إليك كيفية استخراج رقم من سلسلة في Excel.
كيفية استخراج رقم من نهاية سلسلة باستخدام التعبئة السريعة
طريقة سريعة وسهلة لاستخراج رقم من نهاية سلسلة هي استخدام Excel التعبئة السريعة ميزة. تتيح لك هذه الميزة ملء نطاق من الخلايا بسرعة بناءً على النمط المكتشف في خلية واحدة محددة. يتمثل أحد الجوانب السلبية لهذه الطريقة في أنها تتطلب منك إجراء الاستخراج الأول بنفسك ، وإذا كنت تريد فقط استخراج رقم من سلسلة واحدة ، فلن يكون ذلك مفيدًا.
في معظم الحالات ، يمكن لـ Flash Fill استخراج الرقم بشكل صحيح من نهاية السلسلة ، على الرغم من أنه ليس معصومًا عن الخطأ ، لذلك يجب عليك دائمًا التحقق من النتائج بعناية. إذا لم تعمل بشكل صحيح ، فحاول ملء أول خليتين أو ثلاث خلايا يدويًا بدلاً من الأولى فقط.
لاستخراج رقم من نهاية سلسلة باستخدام Flash Fill:
- حدد الخلية المجاورة للخلية الأولى التي تحتوي على السلاسل الخاصة بك.
- أدخل الرقم الذي يظهر في نهاية السلسلة.
- إذا كانت جميع بياناتك في عمود متصل بدون فجوات ، فحدد الخلية أسفل الخلية التي أدخلت الرقم فيها مباشرةً.
- إذا كانت بياناتك تحتوي على فجوات في بعض الصفوف ، فحدد الخلية الأولى وجميع الخلايا التي تريد استخراج الأرقام منها ، بما في ذلك أي صفوف فارغة.
- يضعط السيطرة + E. على Windows أو كمد + إي على نظام Mac لتشغيل ميزة Flash Fill.
- سيتم ملء الخلايا المتبقية تلقائيًا بناءً على النمط الذي اكتشفه Excel. في معظم الحالات ، سيتم استخراج الأرقام من نهاية السلسلة بشكل صحيح.
كيفية استخراج رقم من نهاية سلسلة باستخدام صيغة
أحد الجوانب السلبية لاستخدام طريقة Flash Fill هو أنه يتطلب منك استخراج الأرقام من السلسلة للصف الأول بنفسك. من الممكن استخراج رقم من نهاية سلسلة باستخدام صيغة ، على الرغم من أن الصيغة معقدة للغاية.
لاستخراج رقم من نهاية سلسلة باستخدام صيغة:
- انقر في الخلية حيث تريد أن يظهر الرقم المستخرج.
- أدخل الصيغة التالية ، مع استبدال "B2" بالخلية التي تحتوي على السلسلة الخاصة بك:
= VALUE (RIGHT (B2، LEN (B2) -MAX (IF (ISNUMBER (MID (B2، ROW (INDIRECT ("1:" & LEN (B2)))، 1) * 1) = FALSE، ROW (INDIRECT (") 1: "& LEN (B2)))، 0))))
- يضعط يدخل.
- يجب استخراج الرقم من السلسلة.
- لتطبيق الصيغة على أي خلايا متبقية ، انقر فوق مقبض السحب في الركن الأيمن السفلي من الخلية.
- اسحب لأسفل فوق الخلايا حيث تريد تكرار الصيغة.
- حرر الماوس وسيتم نسخ الصيغة إلى الخلايا المحددة.
- يجب أن تحتوي هذه الخلايا الآن على الأرقام المستخرجة.
- ستُرجع أي خلايا بدون سلسلة للاستخراج منها خطأ. إذا كنت تريد إرجاع خلية فارغة بدلاً من ذلك ، فيمكنك بدلاً من ذلك استخدام الصيغة:
= IFERROR (VALUE (RIGHT (B2، LEN (B2) -MAX (IF (ISNUMBER (MID (B2، ROW (INDIRECT ("1:" & LEN (B2)))، 1) * 1) = FALSE، ROW (INDIRECT ("1:" & LEN (B2)))، 0))))، "")
- ستعيد أي خلايا فارغة الآن مسافة فارغة.
قد تتساءل عما تفعله هذه الصيغة. باختصار ، فإنه يولد مصفوفة بنفس طول السلسلة. يضع كل حرف من الأحرف من السلسلة في هذه المجموعة بالترتيب. ثم يقوم بتقييم كل من هذه الأحرف لمعرفة ما إذا كانت رقمًا أم لا. يتم تحويل أي أحرف عبارة عن أرقام إلى صفر. يتم تحويل أي أحرف ليست أرقامًا إلى رقم يمثل موضعه في السلسلة.
على سبيل المثال ، AB5HG23 سيتحول إلى 1 ، 2 ، 0 ، 4 ، 5 ، 0 ، 0. ثم يبحث عن أعلى قيمة في هذه القائمة ، وهو موضع الحرف الأخير الذي ليس رقمًا. أخيرًا ، تقوم بإرجاع جميع الأحرف بعد هذه النقطة ، وهي الأرقام الموجودة في نهاية السلسلة. تقوم الدالة VALUE بعد ذلك بتحويل هذا مرة أخرى إلى رقم. بسيط ، أليس كذلك؟
كيفية استخراج رقم من بداية سلسلة باستخدام التعبئة السريعة
يمكنك أيضًا استخدام Flash Fill لاستخراج رقم من بداية سلسلة عن طريق ملء المثال الأول بنفسك والسماح لبرنامج Excel بإيقاف النمط.
لاستخراج رقم من بداية سلسلة باستخدام Flash Fill:
- حدد الخلية الموجودة على يمين السلسلة الأولى التي تحتوي على رقم تريد استخراجه.
- أدخل الرقم الذي يظهر في بداية السلسلة.
- إذا كانت جميع بياناتك في عمود متصل بدون فجوات ، فحدد الخلية الموجودة أدناه مباشرةً.
- إذا كانت بياناتك تحتوي على فجوات ، فحدد الخلية الأولى وجميع الخلايا التي تريد استخراج الأرقام منها ، بما في ذلك أي صفوف فارغة.
- يضعط السيطرة + E. على Windows أو كمد + إي على نظام Mac لبدء تشغيل Flash Fill.
- يجب أن تعرض الخلايا التي حددتها الآن الأرقام المستخرجة.
كيفية استخراج رقم من بداية سلسلة باستخدام صيغة
يمكنك أيضًا استخدام صيغة لاستخراج رقم من بداية سلسلة. يعمل هذا بطريقة مشابهة للصيغة الواردة في القسم السابق ولكنه يجد موضع الحرف الأول غير الرقمي ويعيد جميع الأحرف قبل ذلك.
لاستخراج رقم من بداية سلسلة باستخدام صيغة:
- حدد الخلية حيث تريد أن يظهر الرقم المستخرج.
- أدخل الصيغة التالية ، مع استبدال "B18" بالخلية التي تحتوي على السلسلة الخاصة بك:
= VALUE (LEFT (B18، MATCH (FALSE، ISNUMBER (MID (B18، ROW (INDIRECT ("1:" & LEN (B18) +1))، 1) * 1)، 0) -1))
- يضعط يدخل وسيتم استخراج الرقم.
- أنت تستطيع استخدام الملء التلقائي في Excel لنسخ الصيغة إلى أي خلايا أخرى تحتاجها.
- إذا كنت تريد أن تعرض أي صفوف فارغة نتيجة فارغة ، فاستخدم الصيغة التالية بدلاً من ذلك:
= IFERROR (VALUE (LEFT (B18، MATCH (FALSE، ISNUMBER (MID (B18، ROW (INDIRECT ("1:" & LEN (B18) +1))، 1) * 1)، 0) -1))، " ")
كيفية استخراج الأرقام من أي مكان في سلسلة باستخدام التعبئة السريعة
كما ذكرنا سابقًا ، قد لا يعمل Flash Fill إذا كانت الخلية الأولى لا تتطابق مع تنسيق بعض الخلايا الأخرى. في هذه الحالة ، قد تحتاج إلى حساب خليتين أو ثلاث يدويًا قبل أن يمنحك Flash Fill النتيجة التي تريدها.
لاستخراج الأرقام من أي مكان في سلسلة باستخدام Flash Fill:
- في الخلية المجاورة للسلسلة الأولى ، اكتب الأرقام الموجودة في السلسلة يدويًا.
- إذا كانت جميع بياناتك في عمود متصل ، فحدد الخلية الموجودة أدناه مباشرةً.
- إذا كانت بياناتك تحتوي على بعض الفجوات ، فحدد الخلية الأولى وجميع الخلايا الموجودة تحتها التي تريد استخراج الأرقام منها ، بما في ذلك أي صفوف فارغة.
- يضعط السيطرة + E. على Windows أو كمد + إي على نظام Mac لبدء تشغيل Flash Fill.
- يجب أن تملأ الخلايا الآن بالأرقام المستخرجة.
كيفية استخراج الأرقام من أي مكان في سلسلة باستخدام صيغة
يمكنك استخدام صيغة لاستخراج جميع الأرقام من سلسلة ، بغض النظر عن مكان ظهورها.
لاستخراج الأرقام من أي مكان في سلسلة باستخدام الصيغة:
- حدد الخلية حيث تريد أن يظهر الرقم المستخرج.
- أدخل الصيغة التالية ، مع استبدال "B9" بالخلية التي تحتوي على السلسلة الخاصة بك:
= SUMPRODUCT (MID (0 & B9، LARGE (INDEX (ISNUMBER (- MID (B9، ROW (INDIRECT ("1:" & LEN (B9)))، 1)) * ROW (INDIRECT ("1:" & LEN (B9) )) ، 0) ، ROW (INDIRECT ("1:" & LEN (B9)))) + 1،1) * 10 ^ ROW (INDIRECT ("1:" & LEN (B9))) / 10)
- يجب أن تستخرج صيغتك جميع الأرقام من السلسلة.
- إذا كنت تريد أن ترجع أي خلايا فارغة خلية فارغة ، بدلاً من الصفر ، فاستخدم هذه الصيغة بدلاً من ذلك:
= IFERROR (SUMPRODUCT (MID (0 & B9، LARGE (INDEX (ISNUMBER (- MID (B9، ROW (INDIRECT ("1:" & LEN (B9)))، 1)) * ROW (INDIRECT ("1:" & LEN ( B9))) ، 0) ، ROW (INDIRECT ("1:" & LEN (B9)))) + 1،1) * 10 ^ ROW (INDIRECT ("1:" & LEN (B9))) / 10) ، " ")
- أنت تستطيع استخدام الملء التلقائي لتطبيق الصيغة على أي خلايا أخرى في جدول البيانات.
- أي سلاسل لا تحتوي على أي أرقام ستعرض صفرًا. يمكنك أن تتعلم كيف تحويل صفر إلى شرطة في Excel إذا كنت تريد تغيير هذا.
قم بتوسيع مهاراتك في Excel
يمكن أن يساعدك تعلم كيفية استخراج رقم من سلسلة في Excel في استخراج البيانات التي تحتاجها بسرعة من قائمة السلاسل. في حين أن الصيغ المطلوبة قد تكون معقدة ، إلا أنك لست بحاجة إلى فهم كيفية عملها بالضبط ؛ فقط ما يفعلونه!
هناك المزيد من الحيل المفيدة في Excel التي يمكنك تعلمها لنقل مهاراتك إلى المستوى التالي. يمكنك أن تتعلم كيفية عد الخلايا بالنص فيها ، أو كيف احسب عمر شخص ما من تاريخ ميلاده. يمكنك حتى إنشاء مولد رقم عشوائي في Excel.