Excel VBA'da Verileri Sıralama

Excel VBA'da Verileri Sıralama

Excel, Excel ön ucundaki şeridi kullanarak bir dizi tablo verisini sıralamak için mükemmel bir araca sahiptir ve bir noktada, muhtemelen bu işlevi VBA kodunuz içinde kullanmak isteyeceksiniz. Neyse ki, bunu yapmak çok kolaydır.

Ön uç iletişim kutusu, Excel şeridindeki "Veri" sekmesinin "Sırala ve Filtrele" grubundaki "Sırala" simgesine tıklanarak bulunur. Önce bir dizi tablo verisi seçmeniz gerekir.

Özel bir sıralama için iletişim kutusunu göstermek için Alt-A-S-S'yi de kullanabilirsiniz.

Sıralama yöntemi, Excel'in sonraki sürümlerinde büyük ölçüde geliştirilmiştir. Sıralama, eskiden üç düzeyle sınırlıydı, ancak artık istediğiniz kadar düzey girebilirsiniz ve bu, VBA içinde de geçerlidir.

Excel Sıralama iletişim kutusunda sunulan tüm sıralama işlevlerini VBA kodunuza dahil edebilirsiniz. Excel'deki sıralama işlevi hızlıdır ve VBA'da kendiniz yazabileceğiniz her şeyden daha hızlıdır, bu nedenle işlevsellikten yararlanın.

VBA'da bir sıralama yaptığınızda, sıralama parametrelerinin ön uç sıralama iletişim kutusunda aynı kaldığını unutmayın. Çalışma kitabı kaydedildiğinde de kaydedilirler.

Bir kullanıcı aynı tablo verisi aralığını seçer ve Sırala simgesine tıklarsa, VBA kodunuz tarafından girilen tüm parametrelerinizi görecektir. Bir tür kendi tasarımlarını yapmak istiyorlarsa, önce tüm sıralama seviyelerinizi silmek zorunda kalacaklar, bu da onlar için çok can sıkıcı olacak.

Ayrıca, kodunuzdaki parametreleri değiştirmezseniz ve varsayılan değerlere güvenirseniz, kullanıcının VBA sıralamanıza yansıyacak değişiklikler yaptığını ve hata ayıklaması çok zor olabilecek beklenmedik sonuçlar verdiğini görebilirsiniz. .

Neyse ki, kullanıcının temiz bir sıralama iletişim kutusu görmesi için tüm sıralama parametrelerini yeniden ayarlamak için VBA'da bir Clear yöntemi vardır.

1	Worksheets("Sayfa1").Sort.SortFields.Clear

Sıralama tamamlandıktan önce ve sonra VBA'daki sıralama parametrelerini temizlemek iyi bir uygulamadır.

VBA'da Sıralama Yönteminin Pratik Kullanımı

Tablo verileri Excel'e aktarıldığında, genellikle çok rastgele bir sıradadır. Bir CSV (virgülle ayrılmış değerler) dosyasından içe aktarılabilir veya bir veritabanına veya web sayfasına bir bağlantıdan gelebilir. Bir içe aktarmadan diğerine belirli bir sırada olmasına güvenemezsiniz.

Bu verileri çalışma sayfanızda bir kullanıcıya sunuyorsanız, kullanıcı, düzen açısından her yerde olan büyük miktarda veriye bakmakta ve anlamakta zorluk çekebilir. Verileri gruplamak veya belirli bölümlerini kesip başka bir uygulamaya yapıştırmak isteyebilirler.

Örneğin, en yüksek ücretli çalışanı veya en uzun hizmete sahip çalışanı görmek isteyebilirler.

VBA'daki Sıralama yöntemini kullanarak, kullanıcıya kolay sıralamaya izin verecek seçenekler sunabilirsiniz.

VBA ile Excel Sıralamasını Göstermek için Örnek Veriler

Kodun VBA'daki tüm olanakları gösterebilmesi için önce bir çalışma sayfasına girilecek bazı örnek verilere ihtiyacımız var.

Bu verileri tam olarak gösterildiği gibi bir çalışma sayfasına ("Sayfa1" olarak adlandırılır) kopyalayın.

Sıralama parametreleri olarak da kullanılabileceğinden, farklı hücre arka plan renkleri ve yazı tipi renklerinin kullanıldığını unutmayın. Hücre ve yazı tipi renklerini kullanarak sıralama, makalenin ilerleyen bölümlerinde gösterilecektir. Ayrıca, E3 hücresinde departman adının tamamen küçük harf olduğunu unutmayın.

Hücre ve yazı tipi rengine göre sıralama örneklerini kullanmak istemiyorsanız hücre içi ve yazı tipi renklerine ihtiyacınız yoktur.

VBA Sıralaması için Makro Kaydetme

Sıralama için VBA kodu oldukça karmaşık olabilir ve bazen sıralamayı Excel'in ön ucunda yapmak ve kodun nasıl çalıştığını göstermek için bir makro kaydetmek iyi bir fikir olabilir.

Ne yazık ki, kayıt işlevi çok büyük miktarda kod üretebilir, çünkü birçok parametrenin varsayılan değerleri sıralama işleminiz için kabul edilebilir olsa da, mevcut hemen hemen her parametreyi ayarlar.

Ancak, size VBA sıralama kodunun yazılmasıyla ilgili olarak çok iyi bir fikir verir ve bir avantajı, kaydedilen kodun her zaman sizin için çalışmasıdır. Kendi kodunuzun düzgün çalışması için test etmeye ve hata ayıklamaya ihtiyacı olabilir.

VBA'da yapılan bir işlem için geri alma özelliği olmadığını unutmayın, bu nedenle sıralama kodunuzu yazmaya başlamadan önce tablo verilerinin bir kopyasını başka bir çalışma sayfasına almak iyi bir fikirdir.

Örnek olarak, yukarıdaki örnek veriler üzerinde Çalışana göre basit bir sıralama yaptıysanız, kayıt aşağıdaki kodu üretecektir:

123456789101112131415161718

Alt Makro1()Aralık("A1:E6").SeçActiveWorkbook.Worksheets("Sayfa1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _SortOn:=xlSortOnValues, Order:=xlArtan, DataOption:=xlSortNormalActiveWorkbook.Worksheets("Sayfa1") ile.Sırala.SetRange Aralığı("A1:E6").Başlık = xlEvet.MatchCase = Yanlış.Oryantasyon = xlTopToBottom.SortMethod = xlPinYin.Uygulamakİle bitmekAlt Bitiş

Bu oldukça büyük bir kod yığınıdır ve kullanılan varsayılan parametreler nedeniyle çoğu gereksizdir. Ancak, bir projeyi tamamlamak için zaman baskısı altındaysanız ve hızlı çalışan bazı kodlara ihtiyacınız varsa, bunu kolayca kendi VBA kodunuza yapıştırabilirsiniz.

Ancak, kodunuzu anlaşılır ve daha zarif hale getirmek istiyorsanız, başka seçenekler de mevcuttur.

Tek Düzeyli Sıralama yapmak için VBA Kodu

Örnek kodu yalnızca bir makro kaydederken olduğu gibi Çalışan bazında sıralamak istiyorsanız, kod çok basittir:

1234567

Alt SingleLevelSort()Çalışma Sayfaları("Sayfa1").Sort.SortFields.ClearRange("A1:E6").Sort Key1:=Range("A1"), Header:=xlEvetAlt Bitiş

Bunu anlamak, kaydedilen koddan çok daha kolaydır, çünkü varsayılanları kabul eder, örneğin artan şekilde sıralar, bu nedenle parametreleri varsayılan değerlere ayarlamaya gerek yoktur. Bu, önceden bir 'Clear' ifadesi kullandığınızı varsayar.

Başlangıçta, o çalışma sayfası için her sıralama parametresinin varsayılan değerlere ayarlanmasını sağlamak için 'Clear' yöntemi kullanılır. Bir kullanıcı, parametreleri daha önce farklı değerlere ayarlamış olabilir veya VBA'daki daha önceki bir sıralama bunları değiştirmiş olabilir. Sıralama sırasında varsayılan bir konumdan başlamak önemlidir, aksi takdirde kolayca yanlış sonuçlarla karşılaşabilirsiniz.

Clear yöntemi Header parametresini sıfırlamaz ve bunu kodunuza eklemeniz önerilir, aksi takdirde Excel bir başlık satırının bulunup bulunmadığını tahmin etmeye çalışabilir.

Bu kodu örnek verilere karşı çalıştırın ve çalışma sayfanız şöyle görünecektir:

Çok Düzeyli Sıralama yapmak için VBA Kodu

Kodunuzda gerektiği kadar sıralama düzeyi ekleyebilirsiniz. Önce departmana, ardından başlangıç ​​tarihine göre, ancak departman için artan düzende ve başlangıç ​​tarihi için azalan düzende sıralamak istediğinizi varsayalım:

12345678

Alt MultiLevelSort()Worksheets("Sayfa1").Sort.SortFields.ClearAralık("A1:E6").Sort Anahtar1:=Aralık("E1"), Anahtar2:=Aralık("C1"), Başlık:=xlEvet, _Sıra1:=xlArtan, Sıra2:=xlAzalanAlt Bitiş

Sıralama ifadesinde artık iki anahtar olduğuna dikkat edin (Key1 ve Key2). İlk olarak Anahtar1 (Departman sütunu E) sıralanır ve ardından Anahtar2 (Başlangıç ​​Tarihi sütunu C) ilk sıralamaya göre sıralanır.

Ayrıca iki sipariş parametresi vardır. Order1, Key1 (Departman) ile ve Order2 Key2 (Başlangıç ​​Tarihi) ile ilişkilendirilir. Anahtarların ve siparişlerin birbiri ile uyumlu tutulmasını sağlamak önemlidir.

Bu kodu örnek verilere karşı çalıştırın ve çalışma sayfanız şöyle görünecektir:

Departman sütunu (E) artan sırada ve Başlangıç ​​Tarihi sütunu (C) azalan sıradadır.

Bu türün etkisi en çok Jane Halfacre (3. sıra) ve John Sutherland'a (4. sıra) bakıldığında fark edilir. İkisi de Finans'ta ama Jane Halfacre, John Sutherland'dan önce başladı ve tarihler azalan sırada gösteriliyor.

Tablo verilerinin aralığı herhangi bir uzunlukta olabilirse, sıralama aralığını tanımlamak için UseRange nesnesini kullanabilirsiniz. Bu, yalnızca çalışma sayfasında yalnızca tablo verileri varsa işe yarar, çünkü verilerin dışındaki herhangi bir değer satır ve sütun sayısı için yanlış sonuçlar verir.

1234567

Alt Çok DüzeyliSıralama()Worksheets("Sayfa1").Sort.SortFields.ClearWorksheets("Sheet1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlEvet, _Sıra1:=xlArtan, Sıra2:=xlAzalanAlt Bitiş

Sıralama aralığını tanımlamak için 'End(xlDown)' yöntemini kullanırsanız bu sorunu önler. Verinin ortasında boş bir hücre varsa, boş hücreden sonraki hiçbir şey dahil edilmeyecektir, buna karşın UseRange çalışma sayfasındaki son aktif hücreye iner.

Hücre Rengine Göre Sıralama

Excel 2007'den bu yana, bir hücrenin arka plan rengine göre sıralama yapmak artık mümkün, bu da sıralama kodunuzu VBA'da tasarlarken muazzam bir esneklik sağlıyor.

123456789101112

Sub SingleLevelSortByCellColor()Çalışma Sayfaları("Sayfa1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _SortOn:=xlSortOnCellColor, Order:=xlArtan, DataOption:=xlSortNormalActiveWorkbook.Worksheets("Sayfa1") ile.Sırala.SetRange Aralığı("A1:E6").Uygulamakİle bitmekAlt Bitiş

Bu kod, hücre arka plan rengine göre örnek veri aralığını (A2:A6) sıralayacaktır. Artık 'xlSortOnCellColor' değerine sahip 'SortOn' adlı ek bir parametre olduğunu unutmayın.

'SortOn' parametresinin bir aralık nesnesi tarafından değil, yalnızca bir çalışma sayfası nesnesi tarafından kullanılabileceğini unutmayın.

Bu nedenle kod, hücre değerlerini kullanan bir sıralamadan daha karmaşıktır.

Bu kod, sıralama için tüm veri aralığını kapsayan bir anahtar değeri kullanır, ancak arka plan rengi sıralama anahtarı olarak tek tek sütunlar belirtebilir ve daha önce gösterildiği gibi birden çok düzey kullanabilirsiniz.

Bu kodu çalıştırdıktan sonra çalışma sayfanız şu şekilde görünecektir:

Yazı Tipi Rengine Göre Sıralama

Excel VBA'daki sıralama işlevi, yazı tipi renklerine göre sıralayabilmeniz için daha da fazla esneklik sunar:

1234567891011121314

Sub SingleLevelSortByFontColor()Worksheets("Sayfa1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A6"), _xlSortOnFontColor, xlArtan, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)ActiveWorkbook.Worksheets("Sayfa1") ile.Sırala.SetRange Aralığı("A1:E6").Başlık = xlEvet.Oryantasyon = xlTopToBottom.Uygulamakİle bitmekAlt Bitiş

Yazı tipi rengine göre sıralama kodu, hücre arka plan renginden çok daha karmaşıktır. 'SortOn' parametresi artık 'xlSortOnFontColor' değerini tutar.

Yönlendirmeyi 'xlTopToBottom' olarak belirtmeniz ve sıralama için bir renk belirtmeniz gerektiğini unutmayın. Bu, 0 ile 255 arasında değişen değerlerle RGB terimleriyle (Kırmızı, Yeşil, Siyah) belirtilir.

Bu kodu örnek verilere karşı çalıştırdıktan sonra çalışma sayfanız şu şekilde görünecektir:

VBA'da renkleri kullanarak sıralama, çok düzeyli sıralamadan çok daha karmaşıktır, ancak sıralama kodunuz çalışmıyorsa (bu, bir parametre eksikse veya kodu doğru girmediyseniz olabilir), o zaman her zaman kayda geri dönebilirsiniz. bir makro ve kaydedilen kodu VBA'nıza entegre etme.

VBA Sıralamasında Diğer Parametreleri Kullanma

Sıralamanızı özelleştirmek için VBA kodunuzda kullanabileceğiniz bir dizi isteğe bağlı parametre vardır.

Sıralama Açık

SortOn, sıralamanın hücre değerlerini mi, hücre arka plan renklerini mi yoksa hücre yazı tipi renklerini mi kullanacağını seçer. Varsayılan ayar Hücre Değerleri'dir.

1	SortOn = xlSortOnValues

Emir

Sıralama, sıralamanın artan veya azalan düzende yapılıp yapılmayacağını seçer. Varsayılan, Artan'dır.

1	Sıra = xl Artan

Veri Seçeneği

DataOption, metin ve sayıların nasıl sıralanacağını seçer. xlSortNormal parametresi, sayısal ve metin verilerini ayrı ayrı sıralar. xlSortTextAsNumbers parametresi, metni sıralama için sayısal veriler olarak ele alır. Varsayılan, xlSortNormal'dir.

1	DataOption = xlSortNormal

başlık

Başlık, tablo veri aralığının bir başlık satırına sahip olup olmadığını seçer. Bir başlık satırı varsa, bunun sıralamaya dahil edilmesini istemezsiniz.

Parametre değerleri xlYes, xlNo ve xlYesNoGuess. xlYesNoGuess, kolayca tutarsız sonuçlara yol açabilecek bir başlık satırı olup olmadığını belirlemeyi Excel'e bırakır. Bu değerin kullanılması önerilmez.

Varsayılan değer XNo'dur (veri içinde başlık satırı yoktur). İçe aktarılan verilerde genellikle bir başlık satırı vardır, bu nedenle bu parametreyi xlYes olarak ayarladığınızdan emin olun.

1	Başlık = xlEvet

kibrit kutusu

Bu parametre, sıralamanın büyük/küçük harfe duyarlı olup olmadığını belirler. Seçenek değerleri Doğru veya Yanlış'tır. Değer False ise, küçük harf değerleri büyük harf değerleri ile aynı kabul edilir. Değer True ise, sıralama, sıralama içindeki büyük ve küçük harf değerleri arasındaki farkı gösterir. Varsayılan değer False'dır.

1	MatchCase = Yanlış

Oryantasyon

Bu parametre, sıralamanın satırlar arasında mı yoksa tüm sütunlar arasında mı gerçekleştirileceğini belirler. Varsayılan değer xlTopToBottom'dur (satırlara göre sıralama). Yatay olarak sıralamak istiyorsanız xlLeftToRight kullanabilirsiniz. xlRows ve xlColumns gibi değerler bu parametre için çalışmaz.

1	Oryantasyon = xlTopToBottom

SortMethod

Bu parametre yalnızca Çince dilleri sıralamak için kullanılır. xlPinYin ve xlStroke olmak üzere iki değeri vardır. xlPinYin varsayılan değerdir.

xlPinYin, karakterler için fonetik Çince sıralama düzenini kullanarak sıralama yapar. xlStroke, her karakterdeki vuruş miktarına göre sıralar.

Bir sıralama makrosu kaydederseniz, bu parametre her zaman koda dahil edilir ve bunun ne anlama geldiğini merak etmiş olabilirsiniz. Ancak, Çince verilerle uğraşmadığınız sürece çok az faydası olur.

1	SortMethod = xlPinYin

Tablo Verilerini Sıralamak için Çift Tıklama Olayını Kullanma

Microsoft'un VBA için sıralama yöntemlerine dahil ettiği tüm işlevlerde, bir sütun başlığına çift tıklamak ve tüm tablo verilerini o belirli sütuna göre sıralamak için basit bir araç içermiyordu.

Bu gerçekten faydalı bir özelliktir ve bunu yapmak için kodu yazmak kolaydır.

12345678910111213141516171819202122232425262728293031323334

Özel Alt Çalışma Sayfası_BeforeDoubleClick(Aralık Olarak ByVal Hedef, Boole Olarak İptal Et)'Verilerin A1 hücresinde başladığı varsayılır'Seçilen hedef sütunu ve maksimum sütun ve _ satırını yakalamak için üç değişken oluşturun.' tablo verileriDim Col As Integer, RCol As Long, Rrow As Long'Kullanıcının başlık satırına çift tıkladığını kontrol edin - satır 1, aksi takdirde alttan çıkınEğer Target.Row 1 ise Sub'dan Çıkın'UsedRange' nesnesini kullanarak tablo veri aralığındaki maksimum satırları yakalayınRCol = ActiveSheet.UsedRange.Columns.Count'UsedRange' nesnesini kullanarak tablo veri aralığındaki maksimum sütunları yakalayınRRow = ActiveSheet.UsedRange.Rows.Count'Kullanıcının tablo veri aralığının dışındaki bir sütuna çift tıklamadığını kontrol edin.Eğer Target.Column > RCol ise Sub Exit'Kullanıcının çift tıkladığı sütunu yakalayınSütun = Hedef.Sütun'Önceki sıralama parametrelerini silActiveSheet.Sort.SortFields.Clear'UsedRange' nesnesinden maksimum satır ve sütunlarla tanımlanan tablo aralığını sıralayın'Sıralama anahtarı olarak kullanıcı tarafından çift tıklanan sütunu kullanarak tablo verilerini sıralayınActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow))).Sort Key1:=Cells(1, Col), Header:=xlEvet'A1 hücresini seçin - bu, sıralama _ yapıldıktan sonra kullanıcının düzenleme modunda kalmamasını sağlamak içindir.' TamamlandıActiveSheet.Range("A1").SeçAlt Bitiş

Bu kodun, tablo verilerini içeren sayfada çift tıklama olayına yerleştirilmesi gerekir. Bunu, Proje Gezgini penceresindeki (VBE ekranının sol üst köşesi) çalışma sayfası adına tıklayarak ve ardından kod penceresindeki ilk açılır menüden 'Çalışma Sayfası'nı seçerek yapabilirsiniz. İkinci açılır menüde 'BeforeDoubleClick'i seçin ve ardından kodunuzu girebilirsiniz.

İmleci kodun sonundaki A1 hücresine taşımak dışında, bu koda hiçbir ad, aralık veya hücre başvurusu sabit kodlanmadığını unutmayın. Kod, kullanıcının çift tıkladığı hücre koordinatlarından ve tablo veri aralığının boyutundan gerekli tüm bilgileri almak için tasarlanmıştır.

Tablo veri aralığının ne kadar büyük olduğu önemli değildir. Kod, gerekli tüm bilgileri almaya devam edecek ve çalışma kitabınızın herhangi bir yerinde tutulan verilerde, değerleri sabit kodlamak zorunda kalmadan kullanılabilir.

Yapılan tek varsayım, tablo verisinde bir başlık satırı olduğu ve veri aralığının A1 hücresinden başladığı, ancak veri aralığının başlangıç ​​konumunun kod içinde kolayca değiştirilebildiğidir.

Herhangi bir kullanıcı, bu yeni sıralama işlevinden uygun şekilde etkilenecektir!

VBA Kullanarak Sıralama İşlevini Genişletme

Microsoft, çok çeşitli parametreleri kullanarak sıralamada muazzam bir esnekliğe izin verdi. Ancak, VBA içinde bunu daha da ileri götürebilirsiniz.

Herhangi bir değeri kalın yazı tipiyle verilerinizin en üstüne sıralamak istediğinizi varsayalım. Bunu Excel'de yapmanın bir yolu yoktur, ancak bunu yapmak için VBA kodunu yazabilirsiniz:

123456789101112131415161718192021222324252627282930313233343536373839404142

Alt SıralamaByBold()'Tablo verileri için satır ve sütun sayısını tutacak değişkenler oluşturunRrow Kadar Uzun, RCol Kadar Uzun, N Kadar Uzun'Kullanıcının neler olduğunu görememesi için ekran güncellemesini kapatın - görebilirler _' değerler değiştiriliyor ve nedenini merak ediyorumApplication.ScreenUpdating = Yanlış'Tablo veri aralığındaki sütun sayısını yakalayınRCol = ActiveSheet.UsedRange.Columns.Count'Tablo veri aralığındaki satır sayısını yakalayınRRow = ActiveSheet.UsedRange.Rows.Count'Başlık satırını yok sayarak tablo veri aralığındaki tüm satırları yineleyinN = 2'den Rrow'a'Bir hücrede kalın yazı tipi varsa, hücre değerinin önüne 0 değeri koyunActiveSheet.Cells(N, 1).Font.Bold = True iseActiveSheet.Cells(N, 1).Value = "0" & ​​ActiveSheet.Cells(N, 1).ValueBitirSonraki N'Önceki sıralama parametrelerini temizleActiveSheet.Sort.SortFields.Clear'Tablo veri aralığını sıralayın. Başında 0 değerine sahip tüm değerler en üste taşınırActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow))).Sort Key1:=Cells(1, 1), Header:=xlEvet'Başlık satırını yok sayarak tablo veri aralığındaki tüm satırları yineleyinN = 2'den Rrow'a'Bir hücrede kalın bir yazı tipi varsa, hücre değerinden _'ye kadar olan 0 değerini kaldırın.' orijinal değerleri geri yükleActiveSheet.Cells(N, 1).Font.Bold = True iseActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)BitirSonraki N'Ekran güncellemesini tekrar açApplication.ScreenUpdating = DoğruAlt Bitiş

Kod, 'UsedRange' nesnesini kullanarak tablo veri aralığının boyutunu hesaplar ve ardından içindeki tüm satırları yineler. Kalın bir yazı tipi bulunduğunda, hücre değerinin önüne bir sıfır yerleştirilir.

Daha sonra bir sıralama gerçekleşir. Sıralama artan düzende olduğundan, önünde sıfır olan herhangi bir şey listenin en üstüne gider.

Kod daha sonra tüm satırları yineler ve baştaki sıfırları kaldırarak verileri orijinal değerlerine geri yükler.

Bu kod, ölçüt olarak kalın yazı tiplerini kullanarak sıralar, ancak diğer hücre özelliklerini aynı şekilde kolayca kullanabilirsiniz, örneğin italik yazı tipi, metnin punto boyutu, alt çizgi yazı tipi, yazı tipi adı vb.