Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

كيفية فرز العناوين حسب اسم الشارع/الرقم في Excel؟

Author Sun Last modified

عندما تدير قائمة من العناوين في Excel، غالبًا ما يكون من الضروري تنظيم أو تحليل البيانات عن طريق فرز العناوين إما حسب اسم الشارع أو حسب رقم الشارع. على سبيل المثال، إذا كنت بحاجة إلى تجميع العملاء الذين يعيشون في نفس الشارع معًا، أو إذا كنت بحاجة إلى معالجة التسليم وفقًا لأرقام المنازل، فإن الفرز باستخدام هذه المكونات أمر أساسي. ومع ذلك، نظرًا لأن تنسيقات العناوين النموذجية تخلط بين أسماء الشوارع والأرقام داخل خلية واحدة، فإن الفرز المباشر لن يقدم النتائج المتوقعة. في هذه المقالة، سنناقش طرقًا عملية لفرز العناوين حسب اسم الشارع أو رقم الشارع في Excel، ونحلل مزاياها وسيناريوهات التطبيق، ونقدم حلولًا للトラブル وأخرى بديلة لتلبية احتياجات المستخدمين المختلفة.

فرز العناوين حسب اسم الشارع باستخدام عمود مساعد في Excel

فرز العناوين حسب رقم الشارع باستخدام عمود مساعد في Excel

فرز العناوين باستخدام VBA لاستخراج وفرز أسماء الشوارع أو الأرقام تلقائيًا

فرز العناوين حسب اسم الشارع أو الرقم باستخدام Power Query (بدون أعمدة مساعدة)

فرز العناوين حسب اسم الشارع باستخدام عمود مساعد في Excel

لفرز العناوين حسب اسم الشارع في Excel، ستحتاج أولاً إلى استخراج أسماء الشوارع فقط في عمود مساعد. هذا النهج بسيط ويعمل بشكل جيد عندما يكون تنسيق العنوان ثابتًا، مثل "123 شارع التفاح". وهو مناسب للمشاريع السريعة أو قوائم العناوين البسيطة.

1. حدد عمودًا فارغًا بجانب قائمة العناوين الخاصة بك. أدخل الصيغة التالية في الخلية الأولى من العمود المساعد لاستخراج اسم الشارع:

=MID(A1,FIND(" ",A1)+1,255)

(حيث يشير A1 إلى الخلية العلوية لبيانات العنوان الخاصة بك - قم بالتعديل إذا بدأت بياناتك في مكان آخر.)
بعد كتابة الصيغة، اضغط Enter ثم اسحب مقابض التعبئة لأسفل لتطبيق الصيغة على جميع الصفوف في نطاق العناوين. تعمل هذه الصيغة عن طريق العثور على أول مسافة في كل عنوان، ثم إرجاع كل شيء بعد تلك المسافة - اسم الشارع وأي لاحقة. تأكد من أن عناوينك تتبع نفس البنية؛ وإلا فقد لا تتمكن الصيغة من تقسيمها كما هو متوقع.

a screenshot of sorting addresses by street name with formula

2. قم بتمييز العمود المساعد بالكامل (العمود بأسماء الشوارع المستخرجة)، ثم انتقل إلى علامة التبويب البيانات وانقر على ترتيب من الألف إلى الياء. سيقوم هذا بترتيب أسماء الشوارع بترتيب تصاعدي (أبجدي).

a screenshot of sorting addresses by street name with formula step2 sort

3. في مربع الحوار تحذير الفرز الذي يظهر، حدد توسيع التحديد للتأكد من أن المعلومات الكاملة للعنوان تبقى معًا أثناء الفرز.

a screenshot of sorting addresses by street name with formula step3 expand selection

4. انقر على ترتيب. ستتم الآن إعادة ترتيب قائمة العناوين الخاصة بك بناءً على أسماء الشوارع، مما يجعل الشوارع المتشابهة تظهر معًا.

a screenshot of sorting addresses by street name with formula result

ملاحظة: تعمل هذه الطريقة بشكل أفضل مع تنسيقات العناوين الموحدة. إذا كانت خلايا العناوين تحتوي على أنماط غير منتظمة أو مسافات متعددة قبل اسم الشارع، فقد تحتاج الصيغة إلى تعديل. تحقق دائمًا من بعض النتائج للتأكد من الدقة بعد استخدام الصيغة.

المميزات: بسيطة ولا تتطلب أدوات إضافية.
العيوب: تعتمد على التنسيق الثابت؛ يتطلب عمل إضافي إذا تغير تنسيق العنوان.

فرز العناوين حسب رقم الشارع باستخدام عمود مساعد في Excel

إذا كنت بحاجة إلى فرز قائمة من العناوين حسب الرقم العددي للشارع - مثل لتحديد ترتيب التسليم أو تحديد العناوين المجاورة - فمن السهل استخراج الرقم واستخدامه للفرز. هذا أيضًا فعال حتى عندما تكون العناوين في شوارع مختلفة.

1. في خلية فارغة بجانب قائمة العناوين الخاصة بك، أدخل الصيغة التالية لاستخراج رقم الشارع:

=VALUE(LEFT(A1,FIND(" ",A1)-1))

(حيث A1 هو العنوان الأول في قائمتك - قم بالتعديل حسب الحاجة.) اضغط Enter بعد كتابته. تعمل هذه الصيغة عن طريق تحديد أول مسافة وإرجاع الحروف التي تسبقها، وتحويلها إلى قيمة عددية. إذا كانت عناوينك تحتوي على أرقام رئيسية كأرقام الشوارع، ستعمل هذه الصيغة بشكل صحيح. ثم اسحب مقابض التعبئة لأسفل لتطبيق الصيغة على باقي قائمتك.

a screenshot of sorting addresses by street name with formula2

2. حدد العمود المساعد الذي قمت بإنشائه، انتقل إلى علامة التبويب البيانات، وانقر على ترتيب من الألف إلى الياء (أو ترتيب من الأصغر إلى الأكبر لإصدارات Excel الأحدث).

a screenshot of sorting addresses by street name with formula2 step2 sort

3. في مربع الحوار تحذير الفرز، اختر توسيع التحديد لفرز الصفوف بالكامل.

a screenshot of sorting addresses by street name with formula2 step3 expand selection

4. انقر على ترتيب لتطبيقه. ستتم الآن فرز عناوينك حسب رقم الشارع المستخرج.

a screenshot of sorting addresses by street name with formula2 result

نصيحة: إذا كنت تفضل الاحتفاظ برقم الشارع كنص، أو إذا لم يكن لديك حاجة لإجراء فرز عددي، يمكنك أيضًا استخدام:

=LEFT(A1,FIND(" ",A1)-1)

ستقوم هذه النسخة باستخراج الرقم كسلسلة نصية.

احتياطات: إذا بدأت العناوين بالكلمات بدلاً من الأرقام (مثل "Main Street5")، فلن تعمل هذه الصيغ كما هو مخطط لها. تحقق من بيانات عنوانك قبل استخدام الصيغة.

المميزات: سريعة وسهلة الاستخدام إذا كان تنسيق العنوان بسيطًا.
العيوب: لا يتعامل مع العناوين التي تحتوي على أسماء/لاحقات قبل الرقم، أو العناوين التي تحتوي على أرقام متعددة.

رمز VBA - أتمتة فرز العناوين عن طريق استخراج أسماء الشوارع/الأرقام وفرز القائمة باستخدام الماكرو

بالنسبة لأولئك الذين يعملون مع قوائم عناوين أكبر وأكثر تعقيدًا، أو الذين تشمل بياناتهم هياكل عناوين متغيرة، يمكن أن تكون أتمتة عملية الفرز باستخدام VBA فعالة للغاية. يتيح لك VBA استخراج أسماء الشوارع أو الأرقام بسرعة، وفرز قائمة العناوين تلقائيًا وتقليل الخطوات اليدوية. هذا الحل مناسب عندما تحتاج بشكل دوري إلى تنفيذ الفرز، أو تريد دمج الفرز في سير عمل.

ملاحظة: يقوم هذا الماكرو VBA باستخراج اسم الشارع (الجزء بعد أول مسافة) من كل عنوان في العمود A ويرتب القائمة بأكملها بناءً على هذه الأسماء. كما يعمل على استخراج وفرز حسب رقم الشارع مع تعديلات طفيفة.

1. انقر فوق علامة التبويب Developer > Visual Basic. في النافذة التي تظهر، انقر فوق Insert > Module، والصق الكود VBA التالي في نافذة الوحدة:

Sub SortAddressesByStreetName()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim tempCol As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
    
    ' Create helper column with street names
    For i = 1 To lastRow
        ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
    Next i
    
    ' Sort the whole data range by the helper column
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
                           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ws.Sort
        .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
        .Header = xlNo
        .Apply
    End With
    
    ' Delete helper column
    ws.Columns(tempCol).Delete
End Sub

2. لتشغيل الكود، مع تفعيل قائمة العناوين، انقر فوق الزر Run button أو اضغط F5. ستتم الآن فرز قائمة العناوين الخاصة بك في العمود A أبجديًا حسب اسم الشارع.

تستخرج هذه النسخة الرقم قبل أول مسافة فقط وترتّبه بترتيب عددي.

استكشاف الأخطاء وإصلاحها:
- تأكد من أن العناوين موجودة في العمود A أو حدث الكود لموقع بياناتك.
- إذا كانت بياناتك تتضمن رأسًا، قد تحتاج إلى ضبط Header = xlYes لتجنب فرز صف الرأس.
- قم دائمًا بإنشاء نسخة احتياطية قبل تشغيل كود VBA بالجملة.

المميزات: لا تتطلب أعمدة مساعدة؛ تعمل مع مجموعات بيانات كبيرة أو فرز متكرر.
العيوب: يتطلب الإعداد الأولي إذن الماكرو وفهمًا أساسيًا لـ VBA.

طرق أخرى مضمنة في Excel - استخدم Power Query لتقسيم أعمدة العناوين وفرزها مباشرة داخل Power Query بدون أعمدة مساعدة

Power Query، المتوفر في إصدارات Excel الحديثة (Excel 2016 وما بعده، وكذلك Microsoft 365)، يوفر طريقة مرنة وغير صيغية لتقسيم العناوين إلى مكونات مثل رقم الشارع واسم الشارع. هذا الحل مثالي إذا كنت تفضل تجنب الصيغ والأعمدة المساعدة، أو إذا كانت عناوينك تتبع تنسيقات متغيرة لا يمكن للصيغ الأساسية التعامل معها بكفاءة. يمكن لـ Power Query أيضًا حفظ خطواتك بحيث يمكنك تحديثها عندما تنمو بياناتك.

1. حدد بيانات العنوان الخاص بك واذهب إلى علامة التبويب Data ، ثم اختر From Table/Range (أنشئ جدولًا إذا طُلب منك ذلك).
2. في نافذة Power Query، حدد عمود العنوان الخاص بك، ثم انقر فوق Split Column > By Delimiter. اختر Space كفاصل, واختر ال أول فاصل من اليسار لل Split at النوع.
3. سيقوم هذا بتقسيم العنوان إلى عمودين: رقم الشارع وبقية اسم الشارع/العنوان. أعد تسمية الأعمدة الجديدة حسب الحاجة.
4. لفرز، انقر فوق السهم في رأس العمود لكل من عمود اسم الشارع أو رقم الشارع وحدد Sort Ascending أو Sort Descending.
5. انقر Close & Load لإدراج نتائج الفرز مرة أخرى في ورقة العمل الخاصة بك.

نصائح إضافية:

  • إذا كان نمط عنوانك غير متسق، يمكنك التلاعب بالأعمدة بشكل أكبر في Power Query باستخدام تقسيمات مخصصة أو تحويلات.
  • تتم تسجيل خطوات Power Query تلقائيًا؛ يمكنك تحديث البيانات بسهولة إذا تغيرت مصدر البيانات.
  • لا تؤثر هذه الطريقة على بياناتك الأصلية، مما يعزز سلامة السجلات الأصلية.

المميزات: لا يتم تعديل ورقة العمل الخاصة بك بشكل دائم؛ قوي للأنماط المعقدة للعناوين؛ لا صيغ لإدارتها.
العيوب: يتطلب Excel 2016 أو أحدث؛ قد تكون الواجهة غير مألوفة للمستخدمين الجدد.

الخلاصة واقتراحات استكشاف الأخطاء وإصلاحها:
- تذكر التحقق من تناسق تنسيق عنوانك قبل تطبيق الصيغ أو VBA.
- قم دائمًا بمراجعة نتائج الفرز للتأكد من صحتها، خاصة بعد استخدام الأعمدة المساعدة أو الكود.
- بالنسبة للبيانات ذات الهيكل غير المتوقع (مثل الأرقام المفقودة أو أسماء الشوارع في النهاية)، قم بتعديل الصيغ أو فكر في استخدام Power Query لتقسيم أكثر قوة.
- قم بعمل نسخ احتياطية منتظمة قبل استخدام VBA أو أدوات بيانات متقدمة لتجنب فقدان البيانات عن طريق الخطأ.
- اختر الحل (الصيغ، VBA، Power Query) الذي يتماشى بشكل أفضل مع حجم بياناتك، إصدار Excel ومستوى راحتك مع الأداة.
- إذا لم تكن متأكدًا من الطريقة الأفضل، غالبًا ما تقدم Power Query أكبر قدر من المرونة وهي الأكثر أمانًا لتحرير غير مدمر.

مقالات ذات صلة:

أفضل أدوات الإنتاجية لمكتب العمل

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات اعتمادًا على: التنفيذ الذكي |  توليد الأكواد |  إنشاء الصيغ المخصصة |  تحليل البيانات وتوليد الرسوم البيانية |  استخدام Kutools Functions...
ميزات شائعة: البحث، تمييز أو وضع علامة على المكررات | حذف الصفوف الفارغة | دمج الأعمدة أو الخلايا بدون فقدان البيانات | تقريب بدون الصيغة...
بحث متقدم: بحث بمعايير متعددة VLookup | بحث بقيم متعددة VLookup | بحث في عدة ورقات VLookup | مطابقة غامضة...
قائمة منسدلة متقدمة: إنشاء قائمة منسدلة بسرعة | قائمة منسدلة معتمدة | قائمة منسدلة متعددة الاختيارات...
مدير الأعمدة: إضافة عدد محدد من الأعمدة | نقل الأعمدة | تبديل حالة إظهار الأعمدة المخفية | مقارنة النطاقات والأعمدة...
ميزات مميزة: التركيز على الشبكة | عرض التصميم | شريط الصيغ المحسن | مدير أدوات المصنف وورقة العمل | مكتبة النص التلقائي | منتقي التاريخ | دمج البيانات | تشفير/فك تشفير الخلايا | إرسال البريد الإلكتروني حسب الجدول | مرشح متقدم | تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...)...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ...) | أكثر من50 نوع رسم بياني (مخطط جانت، ...) | أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...) |19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ...) |12 أداة تحويل (تحويل إلى كلمات، تحويل العملة، ...) |7 أدوات دمج وتقسيم (دمج متقدم للصفوف، تقسيم الخلايا، ...) | ... والمزيد
استخدم Kutools بلغتك المفضلة – يدعم الإنجليزية، الإسبانية، الألمانية، الفرنسية، الصينية، وأكثر من40 لغة أخرى!

عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...

Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير

  • تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
  • افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
  • يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!

جميع إضافات Kutools. مثبت واحد

حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.

Excel Word Outlook Tabs PowerPoint
  • حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
  • مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
  • الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
  • تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
  • قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد