Excel'de adresleri sokak adı/numarasına göre nasıl sıralanır?
Excel'de bir adres listesi yönettiğinizde, genellikle verileri sokak adına veya sokak numarasına göre sıralayarak düzenlemeniz veya analiz etmeniz gerekir. Örneğin, aynı sokağa yaşayan müşterileri gruplamak istiyorsanız veya teslimatları ev numaralarına göre işlemek zorundaysanız, bu bileşenlere göre sıralama yapmak çok önemlidir. Ancak tipik adres formatları sokak adlarını ve numaralarını tek bir hücrede birleştirir, bu nedenle düz sıralama beklenen sonuçları vermeyecektir. Bu makalede, Excel'de adresleri sokak adına veya sokak numarasına göre sıralamak için pratik yöntemler tartışacağız, faydalarını ve uygulama senaryolarını analiz edeceğiz ve çeşitli kullanıcı ihtiyaçları için sorun giderme ve alternatif çözümler sunacağız.
Excel'de yardımcı bir sütun kullanarak adresleri sokak adına göre sıralayın
Excel'de yardımcı bir sütun kullanarak adresleri sokak numarasına göre sıralayın
Yardımcı sütunlar olmadan Power Query ile adresleri sokak adına veya numarasına göre sıralayın
Excel'de yardımcı bir sütun kullanarak adresleri sokak adına göre sıralayın
Excel'de adresleri sokak adına göre sıralamak için önce sokak adlarını yalnızca yardımcı bir sütuna çıkarmanız gerekecek. Bu yaklaşım basit ve adres formatı tutarlı olduğunda iyi çalışıyor, örneğin "123 Apple St" gibi. Hızlı projeler veya basit adres listeleri için uygundur.
1. Adres listenizin yanındaki boş bir sütunu seçin. Yardımcı sütunun ilk hücresine sokak adını çıkarmak için aşağıdaki formülü girin:
=MID(A1,FIND(" ",A1)+1,255)(Burada A1, adres verilerinizin üst hücresini ifade eder — verileriniz başka bir yerden başlıyorsa ayarlayın.)
Formülü yazdıktan sonra Enter tuşuna basın ve ardından formülü adres aralığınızdaki tüm satırlara uygulamak için doldurma tutamacını aşağı çekin. Bu formül, her adresdeki ilk boşluğu bulur ve o boşluğun ardından gelen her şeyi — yani sokak adını ve herhangi bir ek bilgiyi — döndürür. Adreslerinizin aynı yapıya sahip olduğundan emin olun; aksi takdirde formül beklendiği gibi bölünmeyebilir.
2. Yardımcı sütunun tamamını (çıkarılan sokak adlarıyla birlikte) vurgulayın, ardından Veri sekmesine gidin ve A'dan Z'ye Sırala düğmesine tıklayın. Bu, sokak adlarını artan (alfabetik) sıraya göre sıralayacaktır.
3. Görüntülenen Sıralama Uyarısı iletişim kutusunda, sıralama sırasında tüm adres bilgilerinin birlikte kalmasını sağlamak için Seçimi Genişlet seçeneğini belirleyin.
4. Sırala'ya tıklayın. Adres listeniz artık sokak adlarına göre yeniden sıralanmış olacaktır, böylece benzer sokaklar bir araya gelecektir.
Not: Bu yöntem standartlaştırılmış adres formatları için en iyi şekilde çalışır. Adres hücreleriniz düzensiz desenler içeriyorsa veya sokak adından önce birden fazla boşluk varsa, formülünüz ayarlanması gerekebilir. Formülü kullandıktan sonra birkaç sonucu doğruluk açısından kontrol edin.
Avantajlar: Basit, ek araç gerektirmez.
Dezavantajlar: Tutarlı formatlamaya dayanır; adres formatı değişirse ekstra iş gerektirir.
Excel'de yardımcı bir sütun kullanarak adresleri sokak numarasına göre sıralayın
Adres listesini sokak numarasına göre sıralamanız gerekiyorsa — örneğin teslimat sırası atamak veya komşu adresleri belirlemek için — numarayı çıkarmak ve sıralamak için kullanmak kolaydır. Adresler farklı sokaklarda olsa bile bu yöntem etkilidir.
1. Adres listenizin yanındaki boş bir hücreye sokak numarasını çıkarmak için aşağıdaki formülü girin:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Burada A1 listedeki ilk adresinizdir — gerektiğinde ayarlayın.) Yazdıktan sonra Enter tuşuna basın. Bu formül, ilk boşluğu bulur ve ondan önceki karakterleri döndürür, bunları sayısal değere dönüştürür. Adreslerinizde sokak numaraları olarak önde gelen rakamlar varsa, bu formül doğru çalışacaktır. Ardından formülü listenizin geri kalanına uygulamak için doldurma tutamacını aşağı çekin.
2. Yeni oluşturduğunuz yardımcı sütunu seçin, Veri sekmesine gidin ve A'dan Z'ye Sırala (veya daha yeni Excel sürümleri için En Küçükten En Büyüğe Sırala) seçeneğine tıklayın.
3. Sıralama Uyarısı iletişim kutusunda, tam satırları sıralamak için Seçimi Genişlet seçeneğini seçin.
4. Sıralamayı uygulamak için Sırala'ya tıklayın. Adresleriniz artık çıkarılan sokak numarasına göre sıralanmış olacaktır.
İpucu: Sokak numarasını metin olarak tutmayı tercih ediyorsanız veya sayısal sıralama yapmanız gerekmiyorsa, şu yöntemi de kullanabilirsiniz:
=LEFT(A1,FIND(" ",A1)-1)Bu sürüm, sayıyı metin dizesi olarak çıkarır.
Önlemler: Adresler sayılarla değil, sözcüklerle başlıyorsa (örneğin "Main Street5"), bu formüller beklendiği gibi çalışmaz. Formülü kullanmadan önce adres verilerinizi mutlaka kontrol edin.
Avantajlar: Adres formatı basitse hızlı ve kullanıcı dostu.
Dezavantajlar: Numaradan önce gelen isimler/ekler içeren adresleri veya birden fazla numara içeren adresleri işlemez.
VBA Kodu - Sokak adlarını/numaralarını çıkarmak ve makro ile sıralama listesini otomatikleştirmek
Daha büyük ve karmaşık adres listeleriyle çalışanlar veya veri yapıları değişken olan kişiler için, VBA kullanarak sıralama işlemini otomatikleştirmek oldukça etkili olabilir. VBA, sokak adlarını veya numaralarını hızlı bir şekilde çıkarmak, adres listenizi otomatik olarak sıralamak ve manuel adımları en aza indirmek için size izin verir. Bu çözüm, periyodik olarak sıralama yapmanız gerektiğinde veya sıralamayı bir iş akışına entegre etmek istediğinizde uygundur.
Not: Bu VBA makrosu, sütun A'daki her adresten sokak adını (ilk boşluğun sonrasındaki kısım) çıkarır ve bu adlara göre tüm listeyi sıralar. Ayrıca küçük ayarlarla sokak numarasına göre çıkarma ve sıralama için de çalışır.
1. Geliştirici sekmesine tıklayın > Visual Basic'i açın. Açılan pencerede Ekle > Modül'e tıklayın ve aşağıdaki VBA kodunu modül penceresine yapıştırın:
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 Sub2Kodu çalıştırmak için adres listesini aktif hale getirin ve 
düğmesine tıklayın veya F5tuşuna basın. Sütun A'daki adres listeniz şimdi alfabetik olarak sokak adına göre sıralanmış olacaktır.
Bu sürüm, ilk boşluktan önceki sayıyı çıkarır ve sayısal sıraya göre sıralar.
Sorun Giderme:
- Adreslerin sütun A'da olduğundan emin olun veya kodu verilerinizin konumu için güncelleyin.
- Verilerinizde bir başlık varsa, Header = xlYes başlık satırını sıralamaktan kaçınmak için ayarlamanız gerekebilir.
- Toplu VBA kodunu çalıştırmadan önce her zaman yedek oluşturun.
Avantajlar: Yardımcı sütunlara gerek yoktur; büyük veri setleri veya tekrarlayan sıralama işlemleri için uygundur.
Dezavantajlar: Başlangıç kurulumu, makro izinleri ve VBA hakkında temel bir anlayış gerektirir.
Diğer Yerleşik Excel Yöntemleri - Yardımcı sütunlar olmadan Power Query kullanarak adres sütunlarını bölün ve doğrudan Power Query içinde sıralayın
Modern Excel sürümlerinde (Excel 2016 ve sonrası, ayrıca Microsoft 365) bulunan Power Query, sokak numarası ve sokak adı gibi bileşenlere adresleri bölmek için esnek, formülsüz bir yöntem sağlar. Bu çözüm, formüllerden ve yardımcı sütunlardan kaçınmak istiyorsanız veya temel formüllerin verimli bir şekilde işleyemediği değişken formatlardaki adresleriniz varsa idealdir. Power Query ayrıca adımlarınızı kaydeder, böylece verileriniz büyüdükçe güncelleştirebilirsiniz.
1. Adres verilerinizi seçin ve Veri sekmesine gidin, ardından Tablodan/Aralıktan seçeneğini seçin (istendiğinde bir tablo oluşturun).
2. Power Query penceresinde, adres sütununuzu seçin, ardından Sütun Böl > Ayraç'a Göreseçeneğine tıklayın. Ardından Boşluk seçeneğini ayırıcı olarak seçin, ve ilk en soldaki ayırıcıyı seçin Bölme Türü için.
3. Bu işlem, adresi iki sütuna bölecektir: sokak numarası ve kalan sokak adı/adres. Gerektiğinde yeni sütunları yeniden adlandırın.
4. Sıralamak için, sokak adı veya sokak numarası sütununun başlığındaki oku tıklayın ve Artan Sıralama veya Azalan Sıralama.
5seçin. Ardından Kapat & Yükle düğmesine tıklayarak sıralanmış sonuçları çalışma sayfanıza geri yükleyin.
Ek İpuçları:
- Adres deseniniz tutarsızsa, Power Query'de özel bölme veya dönüşümler kullanarak sütunları daha da değiştirebilirsiniz.
- Power Query adımları otomatik olarak kaydedilir; kaynak verileriniz değişirse verileri kolayca yenileyebilirsiniz.
- Bu yöntem orijinal verilerinizi değiştirmez, orijinal kayıtlar için güvenliği artırır.
Avantajlar: Çalışma sayfanızda kalıcı değişiklik yapmaz; karmaşık adres desenleri için sağlamdır; yönetilecek formül yoktur.
Dezavantajlar: Excel 2016 veya daha yeni sürümler gerektirir; yeni kullanıcılar için arayüz yabancı gelebilir.
Özet ve sorun giderme önerileri:
- Formüller veya VBA uygulamadan önce adres formatınızın tutarlılığını kontrol etmeyi unutmayın.
- Özellikle yardımcı sütunlar veya kod kullandıktan sonra sıralama sonuçlarını her zaman önizleyerek doğruluğunu onaylayın.
- Beklenmedik yapıdaki veriler için (örneğin eksik numaralar veya sondaki sokak adları gibi), formülleri ayarlayın veya daha güçlü bölme için Power Query'yi düşünün.
- VBA veya gelişmiş veri araçları kullanmadan önce düzenli olarak yedek alarak kazara veri kaybını önlemeye çalışın.
- Veri hacminize, Excel sürümünüze ve aracı kullanma konusundaki rahatlık düzeyinize en uygun çözümü (formüller, VBA, Power Query) seçin.
- Hangi yöntemin en iyi olduğundan emin değilseniz, Power Query genellikle en esnek ve yıkıcı olmayan düzenleme için en güvenli seçenektir.
İlgili Makaleler:
- Excel'de e-posta adreslerini domainlere göre sıralayın
- Excel'de verileri yazı tipi rengine göre sıralayın
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin