İçerik
Oldukça acemi bir kullanıcı değilseniz, Excel'deki her şeyin% 99'unun parametrelerin veya niteliklerin (alanların) sütunlardan geçtiği ve nesneler veya olaylar hakkındaki bilgilerin bulunduğu dikey tablolarla çalışmak üzere tasarlandığını zaten fark etmiş olmalısınız. satırlarda. Pivot tablolar, ara toplamlar, formülleri çift tıklama ile kopyalama - her şey bu veri formatı için özel olarak uyarlanmıştır.
Ancak, istisnasız hiçbir kural yoktur ve oldukça düzenli bir sıklıkta, çalışmada yatay semantik yönelimli bir tablo veya satır ve sütunların anlam olarak aynı ağırlığa sahip olduğu bir tablo ile karşılaşırsanız ne yapacağım sorulur:
Ve Excel hala yatay olarak nasıl sıralanacağını biliyorsa (komutla Veri – Sırala – Seçenekler – Sütunları sırala), o zaman filtrelemeyle ilgili durum daha da kötüdür - Excel'de satırları değil, sütunları filtrelemek için yerleşik araçlar yoktur. Bu nedenle, böyle bir görevle karşı karşıya kalırsanız, değişen derecelerde karmaşıklığa sahip geçici çözümler bulmanız gerekecektir.
Yöntem 1. Yeni FILTER işlevi
Excel 2021'in yeni sürümü veya Excel 365 aboneliği kullanıyorsanız, yeni tanıtılan özellikten yararlanabilirsiniz. FILTRE (FİLTRE)kaynak verileri yalnızca satırlara göre değil, sütunlara göre de filtreleyebilen . Bu işlevin çalışması için, her değerin (DOĞRU veya YANLIŞ) tablodaki bir sonraki sütunu gösterip göstermeyeceğimizi veya tersine, gizleyip gizlemeyeceğimizi belirlediği, yardımcı bir yatay tek boyutlu dizi satırı gerekir.
Tablomuzun üstüne aşağıdaki satırı ekleyip içindeki her sütunun durumunu yazalım:
- Diyelim ki her zaman ilk ve son sütunları (başlıklar ve toplamlar) görüntülemek istiyoruz, bu nedenle dizinin ilk ve son hücrelerinde onlar için değeri = TRUE olarak ayarladık.
- Kalan sütunlar için, karşılık gelen hücrelerin içeriği, işlevleri kullanarak ihtiyacımız olan koşulu kontrol eden bir formül olacaktır. И (VE) or OR (TD). Örneğin, toplamın 300 ila 500 aralığında olduğunu.
Bundan sonra, sadece işlevi kullanmak için kalır FILTRE yardımcı dizimizin üzerinde TRUE değerine sahip olduğu sütunları seçmek için:
Benzer şekilde, sütunları belirli bir listeye göre filtreleyebilirsiniz. Bu durumda, işlev yardımcı olacaktır COUNTIF (EĞERSAY)izin verilenler listesindeki tablo başlığından bir sonraki sütun adının tekrarlanma sayısını kontrol eden :
Yöntem 2. Her zamanki tablo yerine özet tablo
Şu anda Excel, yalnızca pivot tablolarda sütunlara göre yerleşik yatay filtrelemeye sahiptir, bu nedenle orijinal tablomuzu bir pivot tabloya dönüştürmeyi başarırsak, bu yerleşik işlevi kullanabiliriz. Bunu yapmak için kaynak tablomuz aşağıdaki koşulları sağlamalıdır:
- boş ve birleştirilmiş hücreler olmadan "doğru" tek satırlık bir başlık satırına sahip olun - aksi takdirde bir pivot tablo oluşturmak işe yaramaz;
- satır ve sütun etiketlerinde kopyalar içermezler – özette yalnızca benzersiz değerler listesine “daralırlar”;
- yalnızca değerler aralığındaki sayıları içerir (satırların ve sütunların kesişiminde), çünkü pivot tablo kesinlikle onlara bir tür toplama işlevi uygulayacaktır (toplam, ortalama vb.) ve bu metinle çalışmayacaktır.
Tüm bu koşullar karşılanırsa, orijinal tablomuza benzeyen bir pivot tablo oluşturmak için (orijinal tablonun) çapraz tablodan düz bir tabloya (normalleştirilmiş) genişletilmesi gerekir. Bunu yapmanın en kolay yolu, 2016'dan beri Excel'de yerleşik olarak bulunan güçlü bir veri dönüştürme aracı olan Power Query eklentisidir.
Bunlar:
- Tabloyu “akıllı” bir dinamik komuta dönüştürelim Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir).
- Komutla Power Query'ye yükleme Veri – Tablodan / Aralıktan (Veri – Tablodan / Aralıktan).
- Toplamları içeren satırı filtreleriz (özetin kendi toplamları olacaktır).
- İlk sütun başlığına sağ tıklayın ve Diğer sütunları daralt (Diğer Sütunların Özetini Aç). Seçilmeyen tüm sütunlar ikiye dönüştürülür - çalışanın adı ve göstergesinin değeri.
- Sütunu, sütuna giren toplamlarla filtreleme özellik.
- Komutu ile ortaya çıkan düz (normalize) tabloya göre bir pivot tablo oluşturuyoruz Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle…).
Artık pivot tablolarda bulunan sütunları filtreleme özelliğini kullanabilirsiniz – adların ve öğelerin önündeki olağan onay işaretleri İmza Filtreleri (Etiket Filtreleri) or Değere göre filtreler (Değer Filtreleri):
Ve elbette, verileri değiştirirken, sorgumuzu ve özeti bir klavye kısayoluyla güncellemeniz gerekecektir. Ctrl+Ara Toplam+F5 veya takım Veri – Tümünü Yenile (Veri — Tümünü Yenile).
Yöntem 3. VBA'da Makro
Kolayca görebileceğiniz gibi, önceki tüm yöntemler tam olarak filtreleme değildir - orijinal listedeki sütunları gizlemiyoruz, ancak orijinal olandan belirli bir sütun kümesiyle yeni bir tablo oluşturuyoruz. Kaynak verilerdeki sütunları filtrelemek (gizlemek) gerekiyorsa, temelde farklı bir yaklaşıma, yani bir makroya ihtiyaç vardır.
Tablo başlığındaki yöneticinin adının sarı hücre A4'te belirtilen maskeyi karşıladığı sütunları anında filtrelemek istediğimizi varsayalım, örneğin, “A” harfiyle başlar (yani, “Anna” ve “Arthur olsun” " sonuç olarak).
İlk yöntemde olduğu gibi, önce her hücrede kriterimizin bir formülle kontrol edileceği ve sırasıyla görünür ve gizli sütunlar için DOĞRU veya YANLIŞ mantıksal değerlerinin görüntüleneceği bir yardımcı aralık satırı uygularız:
Ardından basit bir makro ekleyelim. Sayfa sekmesine sağ tıklayın ve komutu seçin Kaynak (Kaynak kodu). Açılan pencereye aşağıdaki VBA kodunu kopyalayıp yapıştırın:
Private Sub Worksheet_Change(ByVal Target As Range) Eğer Target.Address = "$A$4" ise Aralıktaki Her Hücre İçin("D2:O2") Eğer hücre = True ise cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Sonraki hücre End If End Sub Mantığı şu şekildedir:
- Genel olarak, bu bir olay işleyicisidir. Çalışma Sayfası_Değiştir, yani bu makro, geçerli sayfadaki herhangi bir hücrede yapılan herhangi bir değişiklikte otomatik olarak çalışır.
- Değiştirilen hücreye yapılan başvuru her zaman değişkende olacaktır. Hedef.
- İlk olarak, kullanıcının (A4) kriteri ile hücreyi tam olarak değiştirip değiştirmediğini kontrol ederiz – bu operatör tarafından yapılır if.
- Sonra döngü başlar Her biri için… her sütun için DOĞRU / YANLIŞ gösterge değerleriyle gri hücreleri (D2:O2) yinelemek için.
- Bir sonraki gri hücrenin değeri DOĞRU (doğru) ise, sütun gizlenmez, aksi takdirde onu gizleriz (özellik Gizli).
- Office 365'ten dinamik dizi işlevleri: FİLTRE, SIRALAMA ve UNIC
- Power Query kullanarak çok satırlı üstbilgi içeren pivot tablo
- Makrolar nedir, nasıl oluşturulur ve kullanılır