Gelişmiş Filtre - Excel İpuçları

Mort'un problemini çözmek için Excel'de Gelişmiş Filtre kullanmak. Normal filtreler daha güçlü hale gelse de, Gelişmiş Filtrenin bazı hileleri diğerlerinin yapamayacağı şekilde yapabildiği zamanlar vardır.

Video izle

• Gelişmiş Filtre, normal filtreden daha "gelişmiş" tir çünkü:
• 1) Yeni bir aralığa kopyalayabilir
• 2) Alan 1 = A veya Alan 2 = A gibi daha karmaşık ölçütler oluşturabilirsiniz.
• 3) Hızlıdır
• Mort, kayıtlar arasında döngü yaparak veya bir dizi kullanarak VBA'da 100 bin satırı işlemeye çalışıyor
• Yerleşik Excel özelliklerini kullanmak, kendi kodunuzu yazmaktan her zaman daha hızlı olacaktır.
• Bir Giriş aralığına ve ardından bir Kriter Aralığına ve / veya bir Çıkış Aralığına ihtiyacınız var
• Giriş aralığı için: verilerin üzerinde tek satırlık başlıklar
• Başlıklar için geçici bir satır ekleyin
• Çıktı aralığı için: ayıklamak istediğiniz sütunlar için bir başlık satırı
• Ölçüt aralığı için: 1. satırdaki başlıklar, 2. satırdan başlayan değerler
• Komplikasyon: Excel'in eski sürümleri çıktı aralığının başka bir sayfada olmasına izin vermiyor
• 2003'te çalıştırılabilecek bir makro yazıyorsanız, atlatmak için giriş aralığı olarak adlandırılmış bir aralık kullanın

Video Metni

Podcast'ten Excel Öğrenin, Bölüm 2060: Excel Gelişmiş Filtresi

İnternet yayınına tekrar hoş geldiniz, ben Bill Jelen. Mort tarafından gönderilen bugünün sorusu. Mort, 100.000 satırlık veriye sahip ve Sütun C'nin belirli bir yılla eşleştiği A, B ve D Sütunlarıyla ilgileniyor. Bu yüzden bir kişinin bir yıla girmesini ve ardından Sütun A, B ve D'yi almasını istiyor. Ve Mort, bunu yapmak için dizileri kullandığı bir VBA'ya sahip ve ben, "Bir saniye, biliyorsun, gelişmiş filtre bunu yapardı çok daha iyi." Pekala, şimdi sadece gözden geçirmek için geri döndüm, videolarıma baktım. Gelişmiş filtreyi uzun süredir ele almadığım için bunun hakkında konuşmalıyız.

Gelişmiş filtre, bir giriş aralığı ve ardından şunlardan en az birini gerektirir: bir ölçüt aralığı veya bir çıktı aralığı. Bugün her ikisini de kullanacağız. Pekala, giriş aralığı sizin verileriniz ve verilerin üzerinde başlıklara sahip olmalısınız. Yani, Mort'un başlıkları yok ve bu yüzden buraya geçici olarak bir satır ekleyeceğim ve Alan 1 gibi yapacağım. Mort, verilerinin ne olduğunu biliyor ve böylece oraya gerçek başlıklar koyabilir. Ve biz hiçbir şeyi kullanmıyoruz - bu verileri Sütun E'den O'ya kadar, bu yüzden oraya başlık eklemem gerekmiyor, tamam mı? Yani şimdi A1'den D'ye, 100000 benim girdi aralığım oluyor. Ve sonra çıktı aralığı ve kriter aralığı - Pekala, çıktı aralığı sadece istediğiniz başlıkların bir listesidir. Bu yüzden çıktı aralığını buraya koyacağım ve Alan 3'e ihtiyacımız yok, bu yüzden benOnu bir kenara çekeceğim. Şimdi, burada A1'den C1'e kadar olan bu aralık, Excel'e girdi aralığından hangi alanları istediğimi söyleyen çıktı aralığım oluyor. Ve, şeyleri yeniden sıralamak isterseniz, farklı bir sırada olabilirler, mesela önce Alan 4'ü ve sonra Alan 1'i sonra Alan 2'yi istersem, bunlar fatura numarası gibi gerçek başlıklar olur. Mort'un verilerinin neye benzediğini bilmiyorum.

Ve sonra, ölçüt aralığı bir başlık ve istediğiniz değerdir. Diyelim ki 2014 yılında bir şey almaya çalışıyordum. Bu, böyle kriter aralığı haline geliyor. Pekala, burada sadece bir uyarı. Excel 2016'dayım ve Excel 2016'da iki sayfa arasında gelişmiş bir filtre yapmak mümkün, ancak geri dönerseniz ve geri dönmenin ne olduğunu hatırlamıyorum, belki 2003, emin değilim. Geçmişte bir noktada, bir sayfadan başka bir sayfaya gelişmiş bir filtre yapamazdınız, bu yüzden buraya gelip giriş aralığınızı adlandırmanız gerekirdi. Burada bir isim oluşturmanız gerekir. MyName veya onun gibi bir şey, tamam mı? Ve bunu başarabilmenin yolu bu olacak, pekala. Excel 2016'da değil, yine de, ben 'Mort'un bunu eski veri sürümlerinde çalıştırıp çalıştırmayacağından emin değilim.

Pekala, Data'ya geri döndüğümüzde, Advanced Filter'a gidiyoruz, pekala. Ve oradaki çıktı aralığımızı etkinleştiren başka bir konuma kopyalayacağız. Pekala, liste aralığı, veriler nerede? Excel 2016'da olduğum için, ad aralığını kullanmak yerine Verileri işaret edeceğim - Yani bu benim girdi aralığım. Ölçüt aralığı, tam oradaki hücrelerdir ve sonra, çıkış yapacağımız yer, oradaki bu üç hücre olacaktır. Ve sonra Tamam'ı tıklıyoruz. Pekala, BAM! İşte bu kadar hızlı. Ya farklı bir yıl istersek? Farklı bir yıl isteseydik, sonuçları siler, 2015'e koyar ve sonra tekrar gelişmiş bir filtre uygularız, Başka bir konuma kopyalayın, Tamam'ı tıklayın ve tüm 2015 kayıtları var. Şimşek hızında.

Tamam şimdi, normal Excel'de gelişmiş filtre hayranıyken, VBA'da büyük bir gelişmiş filtre hayranıydım, tamam, çünkü VBA gelişmiş filtreyi gerçekten, gerçekten çok basit hale getiriyor. Pekala, Mort'un verilerinin başlık içermediğini ve başlıkları geçici olarak eklememiz gerektiğini varsayarak Mort için buraya bir kod yazacağız, tamam mı? Bu yüzden, VBA, Alt + F11'e geçeceğim ve bunu verileri içeren çalışma sayfasından çalıştıracağız. Yani: WS'yi Çalışma Sayfası Olarak Kıs, WS = ActiveSheet olarak ayarlayın. Ve sonra, 1. Satırı ekleyin ve sadece birkaç başlık ekleyin: A, B, Yıl ve D. Bugün kaç satır veriye sahip olduğumuzu ve ardından A1 hücresinden başlayarak 4 sütundan son satıra kadar giriş aralığı olabilir. Pekala, ve sonra bu aslında Mort'un InputBox'ı istediği kod.İstedikleri yılı alır ve sonra yeni sayfaya hangi yıl veya ne isim vermek istediklerini sorar, tamam. Bu yüzden, aslında Fly'a bir sayfa ekleyecek ve ardından ActiveSheet olarak yeni bir sayfa olan WSN'yi I- Boyutlandıracak. Bu yüzden WS'nin orijinal sayfa olduğunu biliyorum, WSN yeni eklenen sayfadır. Yeni sayfada, ölçüt aralığını, Sütun E'nin altına burada bu başlıkla eşleşen başlık var ve sonra bize verdikleri cevap E2'ye girecek. Çıktı aralığı benim diğer üç başlığım olacak: A, B ve D. Ve yine, siz veya Mort bunları gerçek başlıklara değiştirirseniz, bu muhtemelen A, B, D'den daha iyi bir şeydir ve siz de bunları gerçek başlıklarla değiştirin, tamam mı? Yani tüm bunlar burada biraz ön çalışma. Bu harika kod satırı, tüm gelişmiş filtreyi yapacak. Yani,InputRange'den bir AdvancedFilter yapıyoruz, kopyalayacağız. Bu bizim seçim filtremiz ya da kopyadır. CriteriaRange E1'den E2'ye, CopyToRange ise A'dan C'ye kadardır. Benzersiz değerler -Hayır, tüm değerleri istiyoruz. Pekala, bu bir kod satırı, tüm kayıtlar arasında döngü yapma ya da tüm kayıtlarda döngü yapma ya da dizileri yapma sihrini yerine getiriyor. Ve sonra işimiz bitti, kriter aralığını temizleyeceğiz ve ardından 1. Satırı orijinal çalışma sayfasında sileceğiz.Ve sonra işimiz bitti, kriter aralığını temizleyeceğiz ve ardından 1. Satırı orijinal çalışma sayfasında sileceğiz.Ve sonra işimiz bitti, kriter aralığını temizleyeceğiz ve ardından 1. Satırı orijinal çalışma sayfasında sileceğiz.

Tamam, o halde buradan verilerimize geri dönelim. Bunu çalıştırmayı kolaylaştıracağız, bu nedenle: Bir Şekil Ekle ve bu Filter, Home, Center, Center, Larger, Larger, Larger, sağ tıklayın, Makro Ata ve MacroForMort'a atayın. Pekala, işte başlıyoruz. Bir test yapacağız. Veri sayfasındayız, Filtre'ye tıklayın, hangi yıl istiyoruz? 2015'i istiyoruz. Ne demek istiyorum? 2015 olarak adlandırmak istiyorum, tamam. Ve BAM! İşte bitti. İşte bu kadar hızlı, bu kadar hızlı.

Şimdi, Mort'un orijinal verilerinin başlıkları olmadığı için, belki bu verilerin başlıkları olmamalıydı. O halde Alt + F11 gidelim, burada ölçüt aralığını temizlemek istiyoruz. Ayrıca Satırlar (1). Silin. Pekala, şimdi bir dahaki sefere bu konudayken, bu başlıklardan kurtulacak. Ve hadi sadece - Her şeyi hızlı bir şekilde yürütmek yerine, buraya 2014'e bir göz atalım. Bu yüzden, Veri'de bir hücre seçeceğim, Alt + F11 ve tam da bunu yaptığımız noktaya gelişmiş filtre. Böylece tüm makronun burada ne yaptığını görebiliriz. Bu yüzden Çalıştır'ı tıklayacağız ve 2014'ü almak istiyorum. 2014, tamam. Ve F8'e basın, gelişmiş filtreyi yapmak üzereyiz. Burada Excel'e geri dönebilir ve ne olduğunu görebiliriz.

Olan ilk şey - Şimdi, olan ilk şey, başlıklarla birlikte yeni bir geçici satır ekledik. Bu çalışma sayfasını ekledim, bir başlık ve hangi yıl girdiklerini içeren bir kriter aralığı oluşturdu, yapmak istediğimiz alanları seçti ve sonra VBA'ya geri döneceğim, bir sonraki kod satırını çalıştıracağım, işte gelişmiş filtreyi orada yapan F8 . İnanılmaz derecede hızlı ve bunun aslında şimdi bize tüm kayıtları getirdiğini göreceksiniz. Oradan, sadece biraz temizlik, bunu silin, bunu silin. Verilere geri dönüp 1. Satırı sileceğim ve gitmekte fayda var. Öyleyse geri kalanının çalışmasına izin vereceğim, bu kesme noktasını kaldıracağım, tamam mı? Demek VBA var. Benim için bu, gitmenin en hızlı, en hızlı yolu.

Pekala, bölüm özeti: Gelişmiş filtre, yeni bir aralığa kopyalayabildiği için normal filtreden daha gelişmiş. Ve şimdi, bunu bu videoda göstermedim, ancak Alan 1 = A veya Alan 2 = A olduğunda karmaşık kriterler oluşturabilirsiniz. Normal otomatik filtre bunu yapamaz ve hızlıdır. Mort, bir dizi kullanarak veya döngü yaparak VBA'da 100.000 satırı işlemeye çalışıyor, ancak Excel oluşturma özelliklerini kullanmak, kendi kodunuzu yazmaktan her zaman daha hızlı olacaktır. Bir girdi aralığı, ölçüt aralığı, çıktı aralığı tanımlamanız gerekir. Bugün her ikisini de kullanmama rağmen, bunlardan en az birinde her zaman bir giriş aralığına ihtiyacınız var. Giriş aralığı için, verilerin üstünde tek satırlık başlıklar. Bu yüzden geçici bir başlık satırı ekleyeceğiz. Çıktı aralığı için, çıkarmak istediğiniz aynı başlıklar, tamam. Yani, eğer A, B olsaydı,Yıl ve D, çıktı aralığı olarak sadece A, B ve D'yi koyacağız. Ölçüt aralığı için 1. Sıradaki başlıklar. Bu, bir ölçüt oluşturmak istediğim alan ve aradığım değer bu. Komplikasyonlar: Excel'in eski sürümleri, çıktı aralığının başka bir sayfada olmasına izin vermez, bu nedenle kodunuz muhtemelen o zaman çalışacaktır. Giriş aralığı için adlandırılmış bir aralık kullanmak istiyorsunuz, çünkü bu sayfadan, adlandırılmış aralık, başka bir sayfada olsa bile, sayfa, geçerli sayfadaki ad dallarına inanıyor. Bu, gelişmiş filtrenin çalışmasına izin verir.Excel'in eski sürümleri, çıktı aralığının başka bir sayfada olmasına izin vermez, bu nedenle, muhtemelen kodunuz o zaman çalışacaktır. Giriş aralığı için adlandırılmış bir aralık kullanmak istiyorsunuz, çünkü bu sayfadan, adlandırılmış aralık, başka bir sayfada olsa bile, sayfa, geçerli sayfadaki ad dallarına inanıyor. Bu, gelişmiş filtrenin çalışmasına izin verir.Excel'in eski sürümleri, çıktı aralığının başka bir sayfada olmasına izin vermez, bu nedenle, muhtemelen kodunuz o zaman çalışacaktır. Giriş aralığı için adlandırılmış bir aralık kullanmak istiyorsunuz, çünkü bu sayfadan, adlandırılmış aralık, başka bir sayfada olsa bile, sayfa, geçerli sayfadaki ad dallarına inanıyor. Bu, gelişmiş filtrenin çalışmasına izin verir.

Pekala, işte orada var. Bu soruyu gönderdiği için Mort'a teşekkür etmek istiyorum. Uğradığınız için teşekkür etmek istiyorum. Bir dahaki sefere başka bir internet yayını için görüşürüz.

Dosyayı indir

Örnek dosyayı buradan indirin: Podcast2060.xlsm