كيف يمكن فرز العناوين حسب اسم الشارع أو رقم الشارع في Excel؟
عند إدارة قائمة عناوين في Excel، غالبًا ما يكون من الضروري تنظيم البيانات أو تحليلها بفرز العناوين إما حسب اسم الشارع أو حسب رقم الشارع. على سبيل المثال، إذا كنت بحاجة إلى تجميع العملاء الذين يعيشون في نفس الشارع معًا، أو كان عليك معالجة عمليات التوصيل بحسب ترتيب أرقام المنازل، فإن الفرز حسب هذه المكونات أمرٌ ضروري. ومع ذلك، وبما أن تنسيقات العناوين النموذجية تمزج بين أسماء الشوارع وأرقامها ضمن خلية واحدة، فإن الفرز المباشر لن يعطي النتائج المتوقعة. في هذه المقالة، سنناقش طرقًا عملية لفرز العناوين حسب اسم الشارع أو رقم الشارع في Excel، ونحلل مزاياها وسيناريوهات التطبيق، ونقدّم حلول استكشاف الأخطاء والبدائل المناسبة لمختلف احتياجات المستخدمين.
فرز العناوين حسب اسم الشارع باستخدام عمود مساعد في Excel
فرز العناوين حسب رقم الشارع باستخدام عمود مساعد في Excel
فرز العناوين باستخدام VBA لاستخراج اسم الشارع أو الرقم وفرزها تلقائيًا
فرز العناوين حسب اسم الشارع أو الرقم باستخدام Power Query (دون أعمدة مساعدة)
فرز العناوين حسب اسم الشارع باستخدام عمود مساعد في Excel
لفرز العناوين حسب اسم الشارع في Excel، ابدأ باستخراج أسماء الشوارع إلى عمود مساعد. يُعد هذا الأسلوب مباشرًا وفعّالًا عندما يكون تنسيق العناوين متسقًا، كأن تكون على شكل «123 شارع آبل»، وهو مثالي للمشاريع السريعة أو قوائم العناوين البسيطة.
1. حدد عمودًا فارغًا مجاورًا لقائمتك من العناوين، ثم أدخل الصيغة التالية في الخلية الأولى من العمود المساعد لاستخراج اسم الشارع:
=MID(A1,FIND(" ",A1)+1,255)(هنا، تشير A1 إلى الخلية التي تحتوي على العنوان مباشرةً فوق بياناتك—عدّلها إذا كانت بياناتك تبدأ من خلية مختلفة.)
بعد كتابة الصيغة، اضغطEnter، ثم اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على جميع الصفوف في نطاق عناوينك. تعمل هذه الصيغة بالعثور على أول مسافة في كل عنوان، ثم إرجاع كل ما يليها—أي اسم الشارع وأي لاحقة مرتبطة به. تأكد من أن جميع عناوينك تتبع نفس البنية؛ وإلا فقد لا تُقسّم الصيغة البيانات كما هو متوقع.
2. حدد العمود المساعد بالكامل (العمود الذي يحتوي على أسماء الشوارع المستخرجة)، ثم انتقل إلى علامة التبويبData وانقرتصاعدي. سيؤدي ذلك إلى فرز أسماء الشوارع بترتيب تصاعدي (أبجدي).
3. في مربع الحوارSort Warning الذي يظهر، حددExpand the selection لضمان بقاء معلومات العنوان الكاملة معًا أثناء الفرز.
4. انقرSort. ستُعاد الآن ترتيب قائمة العناوين الخاصة بك بناءً على أسماء الشوارع، بحيث تظهر الشوارع المتشابهة معًا.
ملاحظة:تعمل هذه الطريقة بشكل أفضل مع تنسيقات العناوين الموحّدة. إذا احتوت خلايا العناوين على أنماط غير منتظمة أو مسافات متعددة قبل اسم الشارع، فقد تحتاج الصيغة إلى تعديل. تأكد دائمًا من مراجعة بعض النتائج للتحقق من دقتها بعد استخدام الصيغة.
المزايا:بسيطة ولا تتطلب أدوات إضافية.
العيوب:تعتمد على تنسيق متسق، ويتطلب الأمر جهدًا إضافيًا إذا اختلف تنسيق العنوان.
فرز العناوين حسب رقم الشارع باستخدام عمود مساعد في Excel
إذا كنت بحاجة إلى فرز قائمة عناوين حسب الرقم العددي لاسم الشارع—مثل تحديد ترتيب التوصيل أو العثور على العناوين المجاورة—فمن السهل استخراج هذه الأرقام واستخدامها للفرز، حتى لو كانت العناوين تقع في شوارع مختلفة.
1. في خلية فارغة بجانب قائمة العناوين الخاصة بك، أدخل الصيغة التالية لاستخراج رقم الشارع:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(حيث إن A1 هو أول عنوان في قائمتك—عدّله حسب الحاجة.) اضغطEnter بعد كتابتها. تعمل هذه الصيغة بتحديد أول مسافة وإرجاع الأحرف التي تسبقها، ثم تحويلها إلى قيمة رقمية. إذا كانت عناوينك تبدأ بأرقام—مثل أرقام الشوارع—فستعمل هذه الصيغة بشكل صحيح. بعد ذلك، اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على باقي القائمة.
2. حدد العمود المساعد الذي أنشأته للتو، ثم انتقل إلى علامة التبويبData، وانقرتصاعدي(أو)Sort من الأصغر إلى الأكبرلإصدارات Excel الأحدث).
3. في مربع الحوارSort Warning، اخترExpand the selection لفرز الصفوف الكاملة.
4. انقرSort للتطبيق. أصبحت عناوينك الآن مرتبة حسب رقم الشارع المستخرج.
تلميح:إذا كنت تفضل الاحتفاظ برقم الشارع كنص، أو لم تكن بحاجة إلى فرز رقمي، يمكنك أيضًا استخدام:
=LEFT(A1,FIND(" ",A1)-1)سيقوم هذا الإصدار باستخراج الأرقام كسلسلة نصية.
احتياطات:إذا بدأت العناوين بكلمات بدلًا من أرقام (مثل «شارع مين5»)، فلن تعمل هذه الصيغة كما هو متوقع. تحقَّق مرتين من بيانات عناوينك قبل استخدام الصيغة.
المزايا:سريعة وسهلة الاستخدام إذا كان تنسيق العنوان بسيطًا.
العيوب:لا تعالج العناوين التي تحتوي على أسماء أو لواحق قبل الرقم، ولا تلك التي تتضمّن أرقامًا متعددة.
رمز 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 Sub2. لتشغيل الكود، بعد تنشيط قائمة العناوين، انقر الزر
أو اضغطF5. ستُفرز الآن قائمة العناوين في العمود A حسب اسم الشارع.
يستخرج هذا الإصدار الرقم الموجود قبل أول مسافة ويرتب النتائج ترتيبًا عدديًّا.
استكشاف الأخطاء وإصلاحها:
- تأكد من أن العناوين موجودة في العمود A، أو حدّث الكود ليتوافق مع موقع بياناتك.
- إذا احتوت بياناتك على رأس جدول، فقد تحتاج إلى تعديلHeader = xlYes لتجنب فرز صف الرأس.
- أنشئ دائمًا نسخة احتياطية قبل تشغيل كود VBA الجماعي.
المزايا:لا تحتاج إلى أعمدة مساعدة، وتعمل بسلاسة مع مجموعات البيانات الكبيرة أو الفرز المتكرر.
العيوب:يتطلب الإعداد الأولي تمكين ماكرو وفهمًا أساسيًا لـ VBA.
طرق Excel المدمجة الأخرى - استخدام Power Query لتقسيم أعمدة العناوين والفرز مباشرةً داخل Power Query دون أعمدة مساعدة
يوفّر Power Query، المتاح في إصدارات Excel الحديثة (Excel 2016 فما بعد، بالإضافة إلى Microsoft 365)، طريقةً مرنة وخاليةً من الصيغ لتقسيم العناوين تلقائيًا إلى مكوناتها الأساسية، مثل رقم الشارع واسمه. وهو الحل الأمثل إذا كنت تفضّل تجنّب استخدام الصيغ والأعمدة المساعدة، أو إذا كانت عناوينك تتبع تنسيقات متنوعة يصعب على الصيغ التقليدية التعامل معها بكفاءة. كما يتيح لك Power Query حفظ خطوات المعالجة الخاصة بك، لتتمكن من تحديث البيانات بسلاسة كلما نمت قاعدة بياناتك.
1. حدد بيانات العنوان الخاصة بك، ثم انتقل إلى علامة التبويببيانات، واخترمن جدول/نطاق (أنشئ جدولًا إذا طُلب منك ذلك).
2. في نافذة Power Query، حدد عمود العنوان الخاص بك، ثم انقر فوقتقسيم العمود > باستخدام المحدد. اخترمسافةكمحدد،واخترأول محدد من اليسارلخيارالانقسام عندالنوع.
3. سيؤدي هذا إلى تقسيم العنوان إلى عمودين: رقم الشارع واسم الشارع/العنوان المتبقي. أعد تسمية الأعمدة الجديدة حسب الحاجة.
4. للفرز، انقر على السهم الموجود في رأس العمود الخاص باسم الشارع أو رقم الشارع، ثم اخترفرز تصاعديأوفرز تنازلي.
5. انقر فوقإغلاق وتحميللإدراج النتائج المرتبة مرة أخرى في ورقة العمل الخاصة بك.
نصائح إضافية:
- إذا لم يكن نمط عناوينك متسقًا، يمكنك معالجة الأعمدة بشكل إضافي في Power Query باستخدام تقسيمات مخصصة أو تحويلات.
- تُسجَّل خطوات Power Query تلقائيًا، مما يمكّنك من تحديث البيانات بسهولة فور تغيّر المصدر.
- لا تغيّر هذه الطريقة بياناتك الأصلية، مما يحافظ على سلامة السجلات الأصلية.
المزايا:لا يتم تعديل ورقة العمل بشكل دائم، وهو مثالي للأنماط المعقدة للعناوين، ولا يتطلب إدارة الصيغ.
العيوب:يتطلب Excel 2016 أو إصدارًا أحدث، وقد تبدو الواجهة غير مألوفة للمستخدمين الجدد.
ملخص واقتراحات استكشاف الأخطاء وإصلاحها:
- تذكّر دائمًا التحقق من اتساق تنسيق العناوين قبل تطبيق الصيغ أو التعليمات البرمجية (VBA).
- راجع نتائج الفرز بعناية للتأكد من دقتها، خاصةً بعد استخدام أعمدة مساعدة أو كود برمجي.
- في حالة البيانات ذات البنية غير المتوقعة (مثل غياب الأرقام أو وجود أسماء الشوارع في النهاية)، عدّل الصيغ أو فكّر في استخدام Power Query لتحقيق تقسيم أكثر موثوقية.
- احفظ نسخًا احتياطية بانتظام قبل استخدام VBA أو أدوات البيانات المتقدمة لتجنب فقدان البيانات عن غير قصد.
- اختر الحل الأنسب (صيغ، VBA، Power Query) بناءً على حجم بياناتك، وإصدار Excel الذي تستخدمه، ومدى إتقانك للأداة.
- إذا لم تكن متأكدًا من أفضل طريقة، فإن Power Query غالبًا ما يوفّر أعلى درجة من المرونة ويُعد الخيار الأكثر أمانًا للتحرير غير التدميري.
مقالات ذات صلة:
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل