Excel'de adresleri cadde/sokak adına veya numarasına göre nasıl sıralayabilirim?
Excel’de bir adres listesi yönettiğinizde, verileri genellikle cadde/sokak adına veya cadde/sokak numarasına göre sıralamak ya da analiz etmek gerekir. Örneğin, aynı sokakta yaşayan müşterileri gruplandırmak veya ev numaralarına göre teslimatları işlemek isteyebilirsiniz; bu tür senaryolarda adres bileşenlerine göre sıralama kritik öneme sahiptir. Ancak standart adres biçimleri, cadde/sokak adı ve numarasını tek bir hücrede birleştirir; bu da doğrudan sıralamanın beklenen sonucu vermeyeceği anlamına gelir. Bu makalede, Excel’de adresleri cadde/sokak adına veya numarasına göre sıralamak için pratik yöntemleri, avantajlarını ve uygulama senaryolarını ele alacağız; ayrıca farklı kullanıcı ihtiyaçlarına yönelik sorun giderme ipuçları ve alternatif çözümler sunacağız.
Excel'de yardımcı sütun kullanarak adresleri cadde/sokak adına göre sıralama
Excel'de yardımcı sütun kullanarak adresleri cadde/sokak numarasına göre sıralama
Excel'de yardımcı sütun kullanarak adresleri cadde/sokak adına göre sıralama
Excel'de adresleri cadde/sokak adına göre sıralamak için öncelikle yalnızca cadde/sokak isimlerini bir yardımcı sütuna ayıklamanız gerekir. Bu yöntem, "123 Apple St" gibi tutarlı bir adres formatı olduğunda basit ve etkilidir; hızlı projeler veya temel Adres Listesi oluşturma ihtiyaçları için idealdir.
1. Adres listenizin yanındaki boş bir sütunu seçin. Yardımcı sütunun ilk hücresine cadde veya sokak adını ayıklayan aşağıdaki formülü girin:
=MID(A1,FIND(" ",A1)+1,255)(Burada A1, adres verilerinizin)Üstteki hücre’sini belirtir; verileriniz başka bir yerden başlıyorsa formülü buna göre 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ğı doğru sürükleyin. Bu formül, her adreste ilk boşluğu bularak ondan sonraki kısmı—yani cadde/sokak adını ve varsa eki—döndürür. Adreslerinizin aynı yapıya sahip olduğundan emin olun; aksi takdirde formül beklendiği gibi bölme işlemi yapmayabilir.
2. Yardımcı sütunun tamamını (çıkarılan cadde/sokak isimlerini içeren sütunu) seçin, ardından Veri sekmesine gidip Artan seçeneğine tıklayın. Böylece cadde/sokak isimleri artan (alfabetik) düzende sıralanacaktır.
3. Görünen Sıralama Uyarısı iletişim kutusunda, sıralama sırasında tam adres bilgilerinin birlikte kalmasını sağlamak için Seçimi genişlet seçeneğini işaretleyin.
4. Sırala'ya tıklayın. Adres Listesi'niz cadde/sokak isimlerine göre yeniden sıralanacak ve benzer sokaklar bir arada görünecek.
Not: Bu yöntem, standartlaştırılmış adres biçimlerinde en iyi şekilde çalışır. Adres hücrelerinizde cadde veya sokak adından önce tutarsız kalıplar ya da fazladan boşluklar varsa formülün ayarlanması gerekebilir. Formülü kullandıktan sonra her zaman birkaç sonucun doğruluğunu kontrol edin.
Avantajlar: Basit ve ek araç gerektirmez.
Dezavantajlar: Tutarlı biçimlendirmeye dayanır; adres biçimi değişkenlik gösterirse ek iş yükü oluşur.
Excel'de yardımcı sütun kullanarak adresleri cadde/sokak numarasına göre sıralama
Bir teslimat sırası atamak veya komşu adresleri belirlemek gibi amaçlarla adres listesini sayısal cadde/sokak numarasına göre sıralamanız gerekiyorsa, bunu Sayıları Çıkart ve sıralama için kullanmanız kolaydır. Bu yöntem, adresler farklı sokaklarda olsa bile etkilidir.
1. Adres Listesi'inizin yanında boş bir hücreye cadde/sokak numarasını ayıklayan aşağıdaki formülü girin:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(A1, listenizdeki ilk adresi ifade eder; gerekirse buna göre ayarlayın.) Yazdıktan sonra Enter tuşuna basın. Bu formül, ilk boşluğu bularak ondan önceki karakterleri alır ve bunları sayısal bir değere dönüştürür. Adresleriniz cadde/sokak numarasıyla başlıyorsa—yani baştaki rakamları içeriyorsa—bu formül sorunsuz çalışacaktır. Ardından formülü listenizin geri kalanına uygulamak için doldurma tutamacını aşağı doğru sürükleyin.
2. Az önce oluşturduğunuz yardımcı sütunu seçin, Veri sekmesine gidin ve Artan'a tıklayın (veya daha yeni Excel sürümleri için)En küçükten en büyüğe Sırala).
3. Görünen Sıralama Uyarısı iletişim kutusunda, tüm satırları sıralamak için Seçimi genişlet seçeneğini seçin.
4. Uygulamak için Sırala'ya tıklayın. Adresleriniz artık ayıklanan cadde/sokak numarasına göre sıralanmıştır.
İpucu:Cadde/sokak numarasını metin olarak saklamayı tercih ediyorsanız veya sayısal sıralamaya ihtiyacınız yoksa şu formülü de kullanabilirsiniz:
=LEFT(A1,FIND(" ",A1)-1)Bu sürüm, Sayıları Çıkart metin dizesi olarak ayıklayacaktır.
Dikkat edilmesi gerekenler: Adresleriniz rakamla değil de kelimeyle başlıyorsa (örneğin "Main Street5"), bu formüller beklendiği gibi çalışmayabilir. Formülü kullanmadan önce adres verilerinizi mutlaka kontrol edin.
Avantajlar: Adres biçimi basitse, kullanımı hızlı ve kullanıcı dostudur.
Dezavantajlar: Numaradan önce isim veya ek içeren adresleri ve birden fazla sayı içeren adresleri işleyemez.
VBA Kodu – Cadde/sokak isimlerini veya numaralarını ayıklayarak listeyi makro ile otomatik sıralama
Daha büyük ve karmaşık Adres Listesi ile çalışanlar veya verileri değişken adres yapıları içeren kullanıcılar için VBA kullanarak sıralama işlemini otomatikleştirmek oldukça etkili olabilir. VBA, cadde/sokak isimlerini veya numaralarını hızlıca ayıklamanıza, Adres Listesi'nizi otomatik sıralamanıza ve manuel adımları en aza indirmenize olanak tanır. Bu çözüm, sıralamayı periyodik olarak yapmanız gerektiğinde veya sıralamayı bir iş akışına entegre etmek istediğinizde uygundur.
Not: Bu VBA makrosu, A sütunundaki her bir adreste ilk boşluktan sonraki kısmı (cadde/sokak adı) çıkarır ve tüm listeyi bu isimlere göre sıralar. Cadde/sokak numarasını ayıklayıp sıralamak için de küçük değişikliklerle kullanılabilir.
1. Geliştirici sekmesine tıklayın > Visual Basic. Açılan pencerede Ekle > Modül seçeneğine 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 Sub2. Kodu çalıştırmak için Adres Listesi etkin durumdayken 
düğmesine tıklayın veya F5 tuşuna basın. A sütunundaki Adres Listesi'niz artık cadde/sokak adına göre alfabetik olarak sıralanacak.
Bu sürüm, ilk boşluktan önceki sayıyı ayıklayarak sayısal düzende sıralar.
Sorun Giderme:
- Adreslerin A sütununda olduğundan emin olun veya verilerinizin konumuna göre kodu güncelleyin.
- Verileriniz başlık içeriyorsa, başlık satırının sıralanmaması için Header = xlYes ayarını yapmanız gerekebilir.
- Toplu VBA kodu çalıştırmadan önce her zaman bir yedek alın.
Avantajlar: Yardımcı sütun gerektirmez; büyük veri kümeleri veya tekrarlayan sıralamalar için idealdir.
Dezavantajlar: İlk kurulumu, makro izinleri ve VBA konusunda temel düzeyde bilgi gerektirir.
Diğer Yerleşik Excel Yöntemleri – Power Query kullanarak adres sütunlarını bölme ve yardımcı sütun olmadan Power Query içinde doğrudan sıralama
Modern Excel sürümlerinde (Excel 2016 ve sonrası ile Microsoft 365) bulunan Power Query, adresleri cadde/sokak numarası ve cadde/sokak adı gibi bileşenlere bölmek için formül gerektirmeyen esnek bir yol sunar. Bu çözüm, formüllerden ve yardımcı sütunlardan kaçınmak istiyorsanız veya adresleriniz temel formüllerin etkin şekilde işleyemeyeceği kadar değişken biçimler içeriyorsa idealdir. Power Query ayrıca adımlarınızı kaydedebilir, böylece verileriniz büyüdükçe bunları güncelleyebilirsiniz.
1. Adres verilerinizi seçin ve Veri sekmesine gidin, ardından Tablodan/Aralıktan seçeneğini belirleyin (istemde bulunulursa bir tablo oluşturun).
2. Power Query penceresinde adres sütununuzu seçin ve ardından Sütunu Böl > Sınırlayıcıya Göre seçeneğine tıklayın. Boşluk sınırlayıcı olarak seçin , ve en soldaki ilk sınırlayıcıyı kullanarak bölmek için Bölme Noktası türünü belirleyin.
3. Bu işlem, adresi iki sütuna böler: cadde numarası ve kalan cadde adı/adres kısmı. Yeni sütunları gerektiği gibi yeniden adlandırın.
4. Sıralamak için cadde adı veya cadde numarası sütun başlıklarındaki oka tıklayın ve Artan Sırala veya Azalan Sırala seçeneklerinden birini seçin.
5. Sıralı sonuçları çalışma sayfanıza geri eklemek için Kapat ve Yükle seçeneğine tıklayın.
Ek İpuçları:
- Adres kalıbınız tutarlı değilse, Power Query’de özel bölme işlemleri veya dönüştürmeler kullanarak sütunlarınızı daha da düzenleyebilirsiniz.
- Power Query adımları otomatik olarak kaydedilir; kaynak veriniz değişirse verileri kolayca yenileyebilirsiniz.
- Bu yöntem orijinal verilerinizi değiştirmez ve bu da orijinal kayıtların güvenliğini artırır.
Avantajlar: Sayfanızda kalıcı değişiklik yapılmaz; karmaşık adres kalıpları için son derece güvenilirdir; yönetmeniz gereken hiçbir formül yoktur.
Dezavantajlar: Excel 2016 veya daha yeni bir sürüm gerektirir; arayüz yeni kullanıcılar için alışık olunmayan bir deneyim sunabilir.
Özet ve sorun giderme önerileri:
- Formül veya VBA uygulamadan önce adres formatınızın tutarlılığını kontrol etmeyi unutmayın.
- Yardımcı sütunlar veya kod kullandıktan sonra sıralama sonuçlarının doğruluğunu her zaman önizleyerek teyit edin.
- Beklenmedik yapıya sahip verilerde (örneğin eksik numara veya sonunda cadde adı olmayan kayıtlar) formülleri uyarlayın veya daha sağlam bir bölme için Power Query’yi değerlendirin.
- VBA veya gelişmiş veri araçlarını kullanmadan önce düzenli yedekler alın ve yanlışlıkla veri kaybını önleyin.
- Veri hacminiz, Excel sürümünüz ve aracın kullanım konusundaki rahatlık seviyenize en iyi uyum sağlayan çözümü (formüller, VBA, Power Query) seçin.
- Hangi yöntemin en iyi olduğunu bilmiyorsanız, Power Query genellikle en esnek çözümü sunar ve kalıcı olmayan düzenlemeler için en güvenlisidir.
İlgili Makaleler:
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar