Bagaimana cara mengaktifkan pengelompokan data di lembar yang dilindungi di excel?

Di sini, kami membahas salah satu pertanyaan paling umum yang diajukan dalam pemodelan keuangan. bagaimana Anda bisa mengelompokkan / mengelompokkan baris dan / atau kolom saat lembar kerja diproteksi?

Pertanyaan

Saya perlu melindungi lembar kerja saya untuk menghindari pengguna mengetik perhitungan penting. Namun, saya ingin pengguna dapat mengelompokkan dan memisahkan berbagai baris dan kolom. Apakah ini mungkin?

Nasihat

Saya telah menulis tentang perlindungan lembar sebelumnya (lihat Perlindungan Memakai)

Jika Anda membuka buku kerja apa pun dan memilih sel apa pun di lembar kerja tertentu, Anda akan melihat bahwa sel tersebut "dikunci" secara default (cukup buka sel, lalu tekan CTRL + 1 dan pilih tab 'Perlindungan'), yaitu

Sel dikunci secara default

Seperti yang tertulis dalam catatan kotak dialog, mengunci sel tidak berpengaruh sampai lembar kerja dilindungi. Ini sederhana untuk dibuktikan. semua sel dikunci secara default namun siapa pun masih dapat mengedit sel apa pun sesuka hati

Untuk melindungi konten, Anda harus melindungi lembar kerja (ALT + T + P + P di semua versi Excel, jika tidak tab 'Beranda' pada Pita, lalu pilih 'Format' di grup 'Sel' lalu pilih . Untuk versi Excel yang lebih baru, ini akan menampilkan kotak dialog berikut

Hanya ketika lembaran telah dilindungi maka sel-sel yang terkunci akan terlindungi. Tindakan lain mungkin atau mungkin tidak diizinkan dengan mencentang berbagai kotak centang yang ditampilkan di atas. Gambar di atas menunjukkan banyak opsi yang tersedia (mis. g. memformat sel, baris, kolom, dll. )

Namun, satu hal yang berguna – dan fitur umum yang hilang adalah pengelompokan…

Pengelompokan

Terkadang saat membuat spreadsheet, pemodel mungkin ingin menyembunyikan data/perhitungan tertentu. Ini dapat dilakukan dengan memformat sel (lihat Pemformatan Angka), atau jika baris atau kolom harus disembunyikan dengan menyembunyikan baris / kolom yang diperlukan (masing-masing CTRL + 9 dan CTRL + 0)

Ilustrasi ini menunjukkan bahwa sulit menemukan baris dan kolom tersembunyi;

Alternatif untuk bersembunyi adalah pengelompokan

Contoh Grup (Ciutkan)

Pengelompokan dilakukan dengan memilih baris atau kolom (mirip dengan menyembunyikan), tetapi kemudian buka tab 'Data' di Pita dan di bagian 'Garis Besar', pilih tombol 'Kelompokkan' dan tekan 'Kelompokkan…'. Pintasan keyboard lebih cepat dan lebih intuitif. ALT + SHIFT + Panah Kanan (sedangkan ALT + SHIFT + Panah Kiri menghapus pengelompokan)

Pengelompokan memperkenalkan 'level' ke spreadsheet. pada grafik di atas, perhatikan bagaimana tombol tambah disejajarkan dengan tombol bertanda 'Nomor 1'. Level 1 selalu semua bagian yang dikelompokkan diciutkan. Jika semua tombol tambah ditekan, contoh kita akan ditampilkan sebagai berikut

Tombol plus sudah menjadi tombol minus, masih sejajar dengan tombol 'Nomor 1'. Ini karena jika Anda menekan tombol angka semua baris / kolom yang dikelompokkan ke tingkat yang sama akan ditampilkan secara konsisten, daripada menekan banyak tombol plus dan minus

Baris dan kolom tersembunyi lebih mudah diidentifikasi, dan lebih mudah untuk diciutkan/diluaskan sesuai kebutuhan

Pengelompokan dapat dibawa ke delapan tingkat, yaitu

Baris 5 sampai 11 dalam ilustrasi ini telah dikelompokkan pada tingkat yang berbeda dengan menekan ALT + SHIFT sambil menekan tombol Panah Kanan (Anda dapat melihat titik-titik di sisi kiri gambar sebelum header baris). Di sini, karena barisnya bersebelahan, Excel tidak dapat menampilkan tombol tambah yang terpisah sehingga pengguna harus menggunakan tombol bernomor sebagai gantinya

Penempatan tombol tambah dapat membingungkan karena sebenarnya sejajar dengan baris/kolom setelah pengelompokan dalam pengaturan default

Pengaturan diubah dengan kembali ke bagian Garis Besar pada tab Data dan mengklik ikon kecil di pojok kanan bawah (pintasan keyboard, ALT + A + L)

Kemudian, kosongkan kedua kotak centang di kotak dialog yang muncul, lalu tekan OK

Ini memindahkan tombol ke baris / kolom sebelum pengelompokan yang, secara pribadi, lebih masuk akal bagi saya

Pengelompokan sangat berguna dalam praktek. Misalnya, bayangkan Anda adalah seorang akuntan manajemen yang harus memberikan analisis Laba Kotor untuk manajer lini Anda, Chief Financial Officer, dan Dewan. Sepertinya ketiga pemangku kepentingan akan membutuhkan tingkat detail yang berbeda. Di masa lalu, saya telah melihat banyak klien menyiapkan tiga spreadsheet berbeda untuk pelanggan ini, yang menyebabkan pengerjaan ulang yang tidak perlu dan potensi masalah rekonsiliasi

File Excel terlampir memberikan contoh bagaimana [sangat. ] solusi sederhana mungkin terlihat

Pelaporan pada Berbagai Tingkat Detail

Menekan tombol bernomor akan menghasilkan semua laporan yang diperlukan – sederhana

Kembali ke Masalah…

Jadi setelah memuji kebaikan pengelompokan lembar kerja, sekarang saya dapat kembali ke masalah pembaca. jika Anda melindungi pengelompokan lembar kerja secara efektif dikunci (mis. e. tidak ada opsi untuk mengaktifkan berbagai tombol plus, minus, dan angka)

Jadi bagaimana kita mengatasi ini?

Saya telah menjelaskan cara menambahkan makro sebelumnya dan untuk memastikan bahwa buku kerja disimpan sebagai buku kerja yang mendukung makro (silakan lihat Menemukan Tautan atau Mengotomatiskan Daftar Isi untuk detail lebih lanjut)

Untuk memproteksi semua sheet, saya sarankan kode berikut di Visual Basic Editor (ALT + F11)

Izinkan Pengelompokan pada Lembar yang Dilindungi

SubBuka_Otomatis()

Aplikasi. Pembaruan Layar = Salah

Untuk Setiap ws Dalam Lembaran

Dengan ws
Buka proteksi Kata Sandi. ="JumlahProduk"
Lindungi Kata Sandi. ="SumProduct", UserInterfaceOnly. = Benar
EnableOutlining = Benar
Berakhir dengan

Selanjutnya ws

Aplikasi. Pembaruan Layar = Benar

Akhir Sub

Saat ditempatkan di subrutin Buka_Otomatis , makro ini dijalankan setelah file dibuka (dengan asumsi Anda mengaktifkan makro). Melalui siklus With, ini akan memastikan semua lembar kerja terlindungi (dalam hal ini dengan kata sandi "SumProduct") dan perintah EnableOutlining = True adalah kode yang memungkinkan pengelompokan berfungsi. Aplikasi. ScreenUpdating = False lalu True perintah selanjutnya membekukan tampilan saat kode dijalankan untuk mencegah layar berkedip dan serangan epilepsi (kode ini digunakan secara teratur di banyak subrutin VBA)

Di sini, di SumProduct, kami cenderung memasukkan kode ini ke semua model klien akhir kami sebagai hal yang biasa. fleksibilitas adalah kunci untuk file Excel yang bagus

Bagaimana Anda memperluas grup dalam lembar kerja yang dilindungi?

Mengapa pengelompokan berwarna abu-abu di Excel?

Bagaimana cara mengaktifkan filter data di lembar yang dilindungi?

Bagaimana cara membuka mode grup di Excel?