İçerik
Boş satırlar ve sütunlar birçok durumda tablolarda sıkıntı yaratabilir. Sıralama, filtreleme, özetleme, pivot tablolar oluşturma vb. için standart işlevler, boş satırları ve sütunları, arkalarında bulunan verileri almadan bir tablo sonu olarak algılar. Bu tür çok sayıda boşluk varsa, bunları manuel olarak kaldırmak çok maliyetli olabilir ve filtrelemeyi kullanarak hepsini bir kerede "toplu olarak" kaldırmak işe yaramaz, çünkü filtre molalarda da "tökezler".
Bu sorunu çözmenin birkaç yoluna bakalım.
Yöntem 1. Boş hücreleri arayın
Bu en uygun olmayabilir, ancak kesinlikle en kolay yoldan bahsetmeye değer.
İçinde birçok boş satır ve sütun içeren böyle bir tabloyla uğraştığımızı varsayalım (açıklık için vurgulanmıştır):
Tablomuzun ilk sütununun (B sütunu) her zaman bir şehrin adını içerdiğinden emin olduğumuzu varsayalım. Daha sonra bu sütundaki boş hücreler, gereksiz boş satırların bir işareti olacaktır. Hepsini hızlı bir şekilde kaldırmak için aşağıdakileri yapın:
- Şehirlerle aralığı seçin (B2:B26)
- Tuşuna basın F5 ve ardından tuşuna basın. Vurgulamak (Özel'e gidin) veya sekmede seçin Ana Sayfa — Bul ve Seç — Bir hücre grubu seçin (Ana Sayfa — Bul&Seç — Özele git).
- Açılan pencerede seçeneği seçin Boş hücreler (Boşluklar) ve basın OK – tablomuzun ilk sütunundaki tüm boş hücreler seçilmelidir.
- Şimdi sekmede seçin Ana Sayfa Komuta Sil – Sayfadaki satırları sil (Sil — Satırları sil) veya klavye kısayoluna basın Ctrl+eksi – ve görevimiz çözüldü.
Tabii ki tablo başlığını temel alarak boş sütunlardan da aynı şekilde kurtulabilirsiniz.
Yöntem 2: Boş satırları arayın
Halihazırda anlamış olabileceğiniz gibi, önceki yöntem yalnızca, verilerimiz mutlaka boş hücreler aranırken bağlanabilecek tamamen doldurulmuş satırlar ve sütunlar içeriyorsa işe yarayacaktır. Ama ya böyle bir güven yoksa ve veriler boş hücreler de içeriyorsa?
Örneğin, tam da böyle bir durum için aşağıdaki tabloya bir göz atın:
Burada yaklaşım biraz daha zor olacak:
- A2 hücresine işlevi girin COUNT (SAY), sağdaki satırdaki doldurulmuş hücrelerin sayısını hesaplayacak ve bu formülü tüm tabloya kopyalayacaktır:
- A2 hücresini seçin ve komutla filtreyi açın Veri – Filtre (Veri — Filtre) veya klavye kısayolu Ctrl+vardiya+L.
- Sıfırları hesaplanan sütuna göre filtreleyelim, yani veri olmayan tüm satırlar.
- Geriye filtrelenmiş satırları seçip komutla silmek kalıyor. Ana Sayfa — Sil -' Sayfadaki satırları sil (Ana Sayfa — Sil — Satırları sil) veya klavye kısayolu Ctrl+eksi.
- Filtreyi kapatıp boş satırlar olmadan verilerimizi alıyoruz.
Ne yazık ki, bu numara artık sütunlarla yapılamaz - Excel henüz sütunlara göre filtrelemeyi öğrenmedi.
Yöntem 3. Bir sayfadaki tüm boş satırları ve sütunları kaldırmak için makro
Bu görevi otomatikleştirmek için basit bir makro da kullanabilirsiniz. Klavye kısayoluna basın Ara Toplam+F11 veya sekmeden seçin geliştirici - Visual Basic (Geliştirici — Visual Basic Düzenleyicisi). sekmeler ise geliştirici görünür değil, aracılığıyla etkinleştirebilirsiniz Dosya – Seçenekler – Şerit Kurulumu (Dosya — Seçenekler — Şeridi Özelleştir).
Açılan Visual Basic düzenleyici penceresinde menü komutunu seçin. Ekle – Modül ve görünen boş modülde aşağıdaki satırları kopyalayıp yapıştırın:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows If(r)) = 0 rng Is Nothing O zaman Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) Son ise Sonraki r Değilse Hiçbir Şey O zaman rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 O Zaman rng Hiçbir Şey Değilse Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) Sıradakiyse End r rng Değilse Hiçbir Şey O zaman rng.Delete End Sub
Düzenleyiciyi kapatın ve Excel'e dönün.
Şimdi vuruş kombinasyonu Ara Toplam+F8 veya düğmesi Makrolar çıkıntı geliştirici. Açılan pencere, az önce oluşturduğunuz makro da dahil olmak üzere şu anda çalıştırabileceğiniz tüm makroları listeleyecektir. SilBoş. Seçin ve düğmeye tıklayın koşmak (koşmak) – sayfadaki tüm boş satırlar ve sütunlar anında silinecektir.
Yöntem 4: Güç Sorgusu
Sorunumuzu çözmenin başka bir yolu ve çok yaygın bir senaryo, Power Query'deki boş satırları ve sütunları kaldırmaktır.
Öncelikle Power Query Query Editor'a tablomuzu yükleyelim. Ctrl+T klavye kısayoluyla dinamik bir "akıllı"ya dönüştürebilir veya sadece veri aralığımızı seçip ona bir ad verebilirsiniz (örneğin Veri) formül çubuğunda, adlandırılmış olarak dönüştürülür:
Şimdi Veri – Veri al – Tablodan/aralıktan (Veri – Veri Al – Tablodan/aralıktan) komutunu kullanıyoruz ve her şeyi Power Query'ye yüklüyoruz:
O zaman her şey basit:
- Boş satırları Ana Sayfa – Satırları azalt – Satırları sil – Boş satırları sil (Ana Sayfa – Satırları Kaldır – Boş satırları kaldır) komutuyla siliyoruz.
- İlk Şehir sütununun başlığına sağ tıklayın ve içerik menüsünden Diğer Sütunları Özetle komutunu seçin. Tablomuz teknik olarak doğru bir şekilde adlandırıldığı gibi, normalize – üç sütuna dönüştürülür: şehir, ay ve orijinal tablodan şehrin ve ayın kesişiminden gelen değer. Power Query'deki bu işlemin özelliği, kaynak verilerdeki boş hücreleri atlamasıdır, bu da ihtiyacımız olan şeydir:
- Şimdi ters işlemi gerçekleştiriyoruz - ortaya çıkan tabloyu orijinal formuna döndürmek için iki boyutlu bir tabloya çeviriyoruz. Aylar içeren sütunu seçin ve sekmede Dönüşüm bir takım seç Pivot sütun (Dönüştür — Özet Sütun). Açılan pencerede, bir değerler sütunu olarak sonuncuyu (Değer) seçin ve gelişmiş seçeneklerde - işlemi seçin. Toplama (toplamayın):
- Komutla sonucu Excel'e geri yüklemek için kalır Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle…)
- Makro nedir, nasıl çalışır, makro metni nereye kopyalanır, makro nasıl çalıştırılır?
- Listedeki tüm boş hücrelerin ana hücrelerin değerleriyle doldurulması
- Belirli bir aralıktaki tüm boş hücreleri kaldırma
- PLEX eklentisi ile bir çalışma sayfasındaki tüm boş satırları kaldırma