استخراج النوع (ذكر/أنثى) من الرقم القومي في Excel بسهولة تامة
إذا كنت تعمل مع قوائم بيانات تحتوي على
أرقام قومية للمواطنين أو العملاء، وتحتاج إلى إضافة عمود "النوع" (ذكر/أنثى)
بشكل تلقائي ودقيق، فإن القيام بذلك يدويًا لكل صف سيكون مضيعة للوقت ومصدرًا محتملاً
للأخطاء.
لكن في Excel،
يمكنك إنجاز هذه المهمة في ثوانٍ معدودة باستخدام معادلة بسيطة تعتمد على موقع رقم
معين داخل الرقم القومي.
في هذا المقال، سنشرح:
- - كيف يحدد الرقم القومي النوع.
- - المعادلات المناسبة للاستخراج.
- - التعامل مع الأخطاء الشائعة.
- - أمثلة عملية وتطبيقات إضافية.
أولاً: كيف يعرف الرقم القومي النوع؟
الرقم القومي المصري (والمستخدم في العديد
من البلدان العربية بصيغ مشابهة) يتكون من 14 رقمًا.
كل رقم له دلالة معينة، لكن ما يهمنا هنا
هو الرقم الثالث عشر (من اليسار إلى اليمين).
| الرقم 13 | النوع |
|----------|--------|
| فردي (1،3،5،7،9) | ذكر |
| زوجي (0،2،4،6،8) | أنثى |
مثال:
الرقم القومي `30001011234567` – الرقم
13 هو `6` → أنثى
الرقم القومي `30001011234568` – الرقم 13 هو
`8` → أنثى
الرقم القومي `29901011234569` – الرقم 13 هو
`9` → ذكر
ملاحظة: الرقم القومي يُكتب كرقم أو كنص
في Excel. المعادلات التالية تتعامل
مع الحالتين.-
ثانياً: المعادلات الجاهزة للاستخدام
المعادلة الأساسية (الأكثر شيوعًا)
افترض أن الرقم القومي موجود في الخلية
C2، وتريد كتابة النوع في الخلية
D2:
=IF(ISEVEN(MID(C2,13,1((,"أنثى","ذكر"(
- مكونات المعادلة ببساطة:
- (`MID(C2,13,1` → تستخرج الحرف الأول (الرقم) بدءًا من الموضع 13 من النص في C2.
- `ISEVEN` → تتحقق إذا كان الرقم زوجيًا، فترجع `TRUE` (صحيح).
- `IF(..., "أنثى", "ذكر")` → إذا كان زوجيًا اكتب "أنثى"، وإلا اكتب "ذكر".
معادلة بديلة باستخدام MOD
إذا كنت تفضل الطريقة الرياضية:
=IF(MOD(MID(C2,13,1),2)=0("أنثى","ذكر":
معادلة متطورة تتحقق من طول الرقم
لتجنب الأخطاء عندما يكون الرقم القومي
ناقصًا:
=IF(LEN(C2)<14,"رقم ناقص",IF(ISEVEN(MID(C2,13,1((,"أنثى","ذكر"))
ثالثاً: تطبيق عملي خطوة بخطوة
لنفترض أن لديك جدولًا كالتالي:
| الصف | الاسم | الرقم القومي |
|------|-------|----------------|
| 2 | أحمد محمود | 30001011234567 |
| 3 | منى سامي | 28905121234562 |
| 4 | يوسف علي | 31210231234569 |
الخطوات:
1. حدد الخلية D2.
2. اكتب المعادلة:
excel
=IF(ISEVEN(MID(C2,13,1)),"أنثى","ذكر")
3. اضغط Enter.
4. استخدم المقبض الصغير أسفل الخلية واسحبه
إلى نهاية الجدول، أو اضغط `Ctrl + D`
بعد تحديد الخلايا من D2 إلى آخر صف.
النتيجة:
| الصف | الاسم | الرقم القومي | النوع
|
|------------------------ |--------------------------------|-------------------------------------|--------|
| 2 | أحمد محمود | 30001011234517 | ذكر|
| 3 | منى سامي | 28905121234562 | أنثى
|
| 4 | يوسف علي | 31210231234539 | ذكر
|
رابعاً: أخطاء شائعة وكيفية حلها
الخطأ السبب الحل
| `VALUE!` | الخلية تحتوي على أقل من 13 حرفًا، أو تحتوي على أحرف | استخدم المعادلة المتطورة
`LEN` أولاً |
| ظهور "أنثى" للكل | الرقم القومي
مخزن كرقم وليس نصًا، أو خانة 13 غير صحيحة | تأكد من أن الرقم مكون من 14 رقمًا |
| نتائج غير منطقية | موقع الرقم الثالث
عشر غير صحيح (بعض الأنظمة تختلف) | تأكد من ترقيم الخانات من اليسار |
حل سريع لتنظيف الأرقام:
إذا كان الرقم القومي مخزنًا كرقم (ويبدأ
بصفر محذوف)، فحوّله إلى نص باستخدام:
=TEXT(C2,"00000000000000"(
ثم استخرج النوع من النتيجة.
خامساً: توسيع الفكرة – استخراج معلومات أخرى من
الرقم القومي
بنفس المنطق، يمكنك استخراج:
| المعلومات | موقعها في الرقم القومي
| معادلة مثال |
|------------|------------------------|----------------|
| تاريخ الميلاد (سنة، شهر، يوم) | الأرقام
1-7 | `=DATE(MID(C2,1,4), MID(C2,5,2), MID(C2,7,2))`
|
| المحافظة (كود) | الأرقام 8-9 | `=MID(C2,8,2)` |
| الرقم التسلسلي | الأرقام 10-13 | `=MID(C2,10,4)` |
| رقم التحقق | الرقم 14 | `=RIGHT(C2,1)` |
يمكنك فيما بعد تحويل كود المحافظة إلى اسم المحافظة
باستخدام `XLOOKUP`
من جدول مرجعي.
سادساً: نصائح إضافية للمحترفين
1. تجنب الأخطاء المتسلسلة: استخدم `IFERROR` لتغليف المعادلة الأساسية:
=IFERROR(IF(ISEVEN(MID(C2,13,1)),"أنثى","ذكر"),"خطأ
في الرقم")
2. تطبيق على نطاق كبير: إذا كان لديك أكثر
من 10,000 صف، فكر في استخدام Power Query
لاستخراج النوع، فهو أسرع ولا يستهلك موارد الجهاز.
3. أتمتة كاملة باستخدام VBA: يمكنك كتابة دالة صغيرة
لتحديث العمود بالكامل بنقرة زر، لكن المعادلات البسيطة تكفي لمعظم المستخدمين.
خلاصة
استخراج النوع من الرقم القومي في Excel لا يحتاج
إلى خبرة متقدمة. بمعادلة واحدة بسيطة:
=IF(ISEVEN(MID(C2,13,1)),"أنثى","ذكر")
يمكنك توفير ساعات من العمل اليدوي والوصول
إلى نتائج دقيقة خالية من الأخطاء.
تذكير أخير:
جرب دائمًا على عينة صغيرة من البيانات
قبل تطبيق المعادلة على الجدول بأكمله، واحتفظ بنسخة أصلية من البيانات في حالة احتجت
إلى التراجع عن أي تعديل.
أسئلة شائعة (FAQ)
س: هل تعمل هذه المعادلة مع الأرقام القومية
القديمة (12 رقمًا)؟
ج: لا، لأن موقع الرقم 13 يتغير. للأرقام
القديمة، الرقم قبل الأخير (رقم 11) هو الذي يحدد النوع، وتحتاج إلى تعديل المعادلة
إلى `MID(C2,11,1)`.
س: ماذا لو الرقم القومي مكتوب بمسافات
أو شرطات؟
ج: استخدم دالة `SUBSTITUTE`
أولاً لتنظيف النص:
`=SUBSTITUTE(C2,"-","")`
ثم طبق المعادلة.
س: هل يمكنني استخدام نفس الفكرة في Google Sheets؟
ج: نعم، المعادلات متطابقة تمامًا.
هل استفدت من هذا المقال؟
جرب المعادلة على ملفك الخاص، وأخبرني في
التعليقات عن التطبيق العملي الذي استخدمتها فيه.
لا تنس مشاركة المقال مع زملائك الذين يعملون
مع Excel يوميًا
– فقد يوفرون وقتًا كبيرًا بفضل هذه المعلومة البسيطة.
في المقال القادم، سنشرح كيفية استخراج
العمر والمحافظة من نفس الرقم القومي خطوة بخطوة.
تابعني للحصول على المزيد من الشروحات المبسطة
لأدوات Excel المتقدمة.