Jika Anda bukan pengguna pemula, maka Anda pasti sudah memperhatikan bahwa 99% dari semua yang ada di Excel dirancang untuk bekerja dengan tabel vertikal, di mana parameter atau atribut (bidang) melewati kolom, dan informasi tentang objek atau peristiwa berada di baris. Tabel pivot, subtotal, menyalin rumus dengan klik dua kali – semuanya disesuaikan secara khusus untuk format data ini.
Namun, tidak ada aturan tanpa pengecualian dan dengan frekuensi yang cukup teratur saya ditanya apa yang harus dilakukan jika tabel dengan orientasi semantik horizontal, atau tabel di mana baris dan kolom memiliki bobot makna yang sama, muncul dalam pekerjaan:
Dan jika Excel masih tahu cara mengurutkan secara horizontal (dengan perintah Data – Urutkan – Opsi – Urutkan kolom), maka situasi dengan pemfilteran lebih buruk - tidak ada alat bawaan untuk memfilter kolom, bukan baris di Excel. Jadi, jika Anda dihadapkan dengan tugas seperti itu, Anda harus menemukan solusi dengan berbagai tingkat kerumitan.
Metode 1. Fungsi FILTER baru
Jika Anda menggunakan versi baru Excel 2021 atau langganan Excel 365, Anda dapat memanfaatkan fitur yang baru diperkenalkan FILTER (SARING), yang dapat memfilter data sumber tidak hanya menurut baris, tetapi juga menurut kolom. Agar berfungsi, fungsi ini memerlukan baris array satu dimensi horizontal bantu, di mana setiap nilai (TRUE atau FALSE) menentukan apakah kita menampilkan atau, sebaliknya, menyembunyikan kolom berikutnya dalam tabel.
Mari tambahkan baris berikut di atas tabel kita dan tulis status setiap kolom di dalamnya:
- Katakanlah kita selalu ingin menampilkan kolom pertama dan terakhir (header dan total), jadi untuk mereka di sel pertama dan terakhir dari array kita menetapkan nilai = TRUE.
- Untuk kolom yang tersisa, isi sel yang sesuai akan menjadi rumus yang memeriksa kondisi yang kita butuhkan menggunakan fungsi И (DAN) or OR (OR). Misalnya, totalnya berkisar antara 300 hingga 500.
Setelah itu, tinggal menggunakan fungsi FILTER untuk memilih kolom yang di atasnya array bantu kami memiliki nilai TRUE:
Demikian pula, Anda dapat memfilter kolom menurut daftar yang diberikan. Dalam hal ini, fungsinya akan membantu COUNTIF (HITUNGAN), yang memeriksa jumlah kemunculan nama kolom berikutnya dari header tabel dalam daftar yang diizinkan:
Metode 2. Pivot table bukan yang biasa
Saat ini, Excel memiliki pemfilteran horizontal bawaan berdasarkan kolom hanya di tabel pivot, jadi jika kita berhasil mengubah tabel asli kita menjadi tabel pivot, kita dapat menggunakan fungsionalitas bawaan ini. Untuk melakukan ini, tabel sumber kami harus memenuhi kondisi berikut:
- memiliki baris header satu baris yang "benar" tanpa sel kosong dan gabungan – jika tidak, tidak akan berfungsi untuk membuat tabel pivot;
- jangan mengandung duplikat dalam label baris dan kolom - mereka akan "runtuh" dalam ringkasan menjadi daftar hanya nilai unik;
- hanya berisi angka dalam rentang nilai (di persimpangan baris dan kolom), karena tabel pivot pasti akan menerapkan semacam fungsi agregasi padanya (jumlah, rata-rata, dll.) dan ini tidak akan berfungsi dengan teks
Jika semua kondisi ini terpenuhi, maka untuk membuat tabel pivot yang terlihat seperti tabel asli kita, tabel tersebut (yang asli) perlu diperluas dari tab silang menjadi tabel datar (dinormalisasi). Dan cara termudah untuk melakukannya adalah dengan add-in Power Query, alat transformasi data canggih yang ada di Excel sejak 2016.
Ini adalah:
- Mari kita ubah tabel menjadi perintah dinamis "pintar" Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel).
- Memuat ke Power Query dengan perintah Data – Dari Tabel / Rentang (Data – Dari Tabel / Rentang).
- Kami memfilter baris dengan total (ringkasan akan memiliki totalnya sendiri).
- Klik kanan pada judul kolom pertama dan pilih Batalkan ciutkan kolom lainnya (Batalkan Pivot Kolom Lainnya). Semua kolom yang tidak dipilih diubah menjadi dua – nama karyawan dan nilai indikatornya.
- Memfilter kolom dengan total yang masuk ke kolom Atribut.
- Kami membangun tabel pivot sesuai dengan tabel datar (dinormalisasi) yang dihasilkan dengan perintah Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup & Muat — Tutup & Muat ke…).
Sekarang Anda dapat menggunakan kemampuan untuk memfilter kolom yang tersedia di tabel pivot – tanda centang biasa di depan nama dan item Filter Tanda Tangan (Filter Label) or Filter menurut nilai (Filter Nilai):
Dan tentu saja, saat mengubah data, Anda perlu memperbarui kueri dan ringkasan kami dengan pintasan keyboard Ctrl+lain+F5 atau tim Data – Segarkan Semua (Data — Segarkan Semua).
Metode 3. Makro di VBA
Semua metode sebelumnya, seperti yang dapat Anda lihat dengan mudah, tidak benar-benar memfilter – kami tidak menyembunyikan kolom dalam daftar asli, tetapi membentuk tabel baru dengan kumpulan kolom tertentu dari yang asli. Jika diperlukan untuk memfilter (menyembunyikan) kolom dalam data sumber, maka diperlukan pendekatan yang berbeda secara mendasar, yaitu makro.
Misalkan kita ingin memfilter kolom dengan cepat di mana nama manajer di header tabel memenuhi topeng yang ditentukan dalam sel kuning A4, misalnya, dimulai dengan huruf "A" (yaitu, dapatkan "Anna" dan "Arthur " hasil dari).
Seperti pada metode pertama, pertama-tama kami menerapkan baris rentang tambahan, di mana di setiap sel kriteria kami akan diperiksa oleh rumus dan nilai logika TRUE atau FALSE akan ditampilkan untuk kolom yang terlihat dan tersembunyi, masing-masing:
Lalu mari kita tambahkan makro sederhana. Klik kanan pada tab lembar dan pilih perintah sumber (Kode sumber). Salin dan tempel kode VBA berikut ke jendela yang terbuka:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Kemudian Untuk Setiap sel Dalam Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub Logikanya adalah sebagai berikut:
- Secara umum, ini adalah event handler Lembar Kerja_Ubah, yaitu makro ini akan secara otomatis berjalan pada setiap perubahan ke sel mana pun pada lembar saat ini.
- Referensi ke sel yang diubah akan selalu ada dalam variabel target.
- Pertama, kami memeriksa apakah pengguna telah mengubah sel persis dengan kriteria (A4) – ini dilakukan oleh operator if.
- Kemudian siklus dimulai Untuk setiap… untuk mengulangi sel abu-abu (D2:O2) dengan nilai indikator TRUE / FALSE untuk setiap kolom.
- Jika nilai sel abu-abu berikutnya adalah TRUE (benar), maka kolom tidak disembunyikan, jika tidak kami menyembunyikannya (properti Tersembunyi).
- Fungsi array dinamis dari Office 365: FILTER, SORT, dan UNIC
- Tabel pivot dengan header multibaris menggunakan Power Query
- Apa itu makro, cara membuat dan menggunakannya