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