Cara Menganalisis Spreadsheet yang Dibuat Orang Lain (Tanpa Melakukan Rekayasa Balik)
Sebelum Anda memercayai angka dalam buku kerja warisan, Anda memerlukan tiga hal. Pertama adalah lembar mana yang merupakan sumber aslinya. Kedua adalah sel mana yang berisi nilai yang diketik secara manual, bukan rumus. Ketiga adalah di mana file tersebut terhubung ke luar. Hal lainnya hanyalah detail.
Kebanyakan orang langsung melompat ke tab ringkasan dan mulai membaca. Begitulah cara penimpaan manual dari sebelas bulan lalu bisa berakhir di paket laporan direksi.
Panduan ini membahas mengapa buku kerja warisan sulit dibaca, tiga cara yang biasa digunakan orang untuk memecahkan kodenya, dan batas dari setiap pendekatan tersebut.
Mengapa spreadsheet yang dibuat orang lain sulit dibaca
Sebuah buku kerja merekam keputusan, bukan sekadar data. Keputusan-keputusan tersebut tidak terlihat, dan orang yang membuatnya biasanya sudah keluar dari tim.
Masalah tersulit adalah sel yang menampilkan 48,200 tidak memberikan petunjuk tentang asal-usulnya. Itu bisa berupa rumus, nilai yang ditempel (paste), atau rumus yang ditimpa seseorang saat mengejar tenggat waktu. Ketiganya terlihat persis sama.
Struktur juga bisa tersembunyi. Lembar kerja (sheet) bisa disembunyikan, baris bisa dikelompokkan dan diciutkan, dan rentang bernama (named range) bisa merujuk ke tempat yang sama sekali berbeda dari yang ditunjukkan namanya. Tautan eksternal ke file yang tidak Anda miliki akan terus menampilkan hasil cache terakhirnya tanpa ada peringatan kesalahan.
Lalu ada masalah versi. Ketika sebuah folder berisi model_v3, model_final, dan model_final_USE_THIS, nama file bukanlah bukti dari apa pun.
Kerugian yang Harus Anda Tanggung
Butuh waktu sehari sebelum Anda bisa menjawab pertanyaan. Permintaan pertama biasanya sederhana, seperti mengapa angka totalnya berubah. Menjawabnya dengan jujur berarti Anda harus memetakan seluruh buku kerja terlebih dahulu, karena Anda tidak bisa mengabaikan kemungkinan adanya penimpaan manual yang belum Anda cari.
Keyakinan yang semu. Alternatif dari pemetaan adalah memercayai tab ringkasan begitu saja. Hal itu memang menghasilkan jawaban dengan cepat, tetapi membuat Anda tidak punya cara untuk mempertahankannya saat seseorang mempertanyakannya.
Kerusakan yang baru muncul belakangan. Mengedit buku kerja yang belum Anda petakan dapat memutuskan dependensi secara diam-diam. Angkanya masih terhitung, jadi tidak ada yang terlihat salah sampai peninjau menyadari bahwa angka tersebut tidak lagi berubah.
Kerugian terbesar ditanggung oleh siapa pun yang memegang file tersebut paling akhir. Ketika spreadsheet yang dibuat orang lain berpindah tangan hingga tiga kali, setiap pemilik akan menambahkan tambalan (patch) dan tidak ada satu pun dari mereka yang mendokumentasikannya.
Solusi Sementara yang Biasa Dicoba
Opsi 1: Pisahkan angka yang diketik manual dari angka hasil perhitungan
Sebelum membaca logika apa pun, cari tahu sel mana yang merupakan input. ISFORMULA mengembalikan nilai TRUE untuk sel mana pun yang berisi rumus, sehingga kolom pembantu di seluruh lembar kerja dapat langsung mengungkap nilai-nilai yang dimasukkan secara manual (hardcoded).
Jika Anda ingin melihat logikanya, bukan sekadar menandainya, FORMULATEXT akan mengembalikan rumus tersebut sebagai teks. Dengan menyajikannya di samping nilai-nilai tersebut, blok data yang membingungkan akan berubah menjadi sesuatu yang mudah dibaca.
Ini adalah langkah pertama yang paling bernilai tinggi dan benar-benar cepat. Batasannya adalah cakupan: Anda harus menerapkannya lembar demi lembar, dan buku kerja yang besar memiliki jumlah lembar yang lebih banyak daripada kesabaran Anda.
Opsi 2: Lacak dependensinya
Alat Formula Auditing di Excel dapat menggambarkan hubungan tersebut. Microsoft mendokumentasikan cara menampilkan hubungan antara rumus dan sel, di mana Trace Precedents menunjukkan apa yang mengisi suatu sel dan Trace Dependents menunjukkan sel mana saja yang dipengaruhinya.
Warna panah membawa informasi tertentu. Panah biru menunjukkan sel tanpa kesalahan, dan panah merah menunjuk ke sel yang menyebabkan kesalahan. Panah hitam yang mengarah ke ikon lembar kerja berarti referensi tersebut berada di lembar kerja lain atau di buku kerja lain. Panah hitam inilah cara Anda menemukan dependensi eksternal.
Untuk satu rumus yang padat, mengevaluasinya langkah demi langkah akan menunjukkan setiap hasil perantara. Cara ini lambat namun andal.
Batasannya adalah jumlah perhitungan. Pelacakan adalah operasi per sel, dan model dengan empat ratus rumus berarti empat ratus operasi.
Opsi 3: Jalankan inventarisasi tingkat buku kerja
Alih-alih membaca sel satu per satu, buatlah katalog dari file tersebut. Buat daftar setiap lembar kerja termasuk yang tersembunyi, setiap tautan eksternal, setiap rentang bernama, dan setiap tempat di mana pola rumus terputus di tengah kolom.
Microsoft mendokumentasikan add-in khusus untuk hal ini, yaitu Spreadsheet Inquire, yang menganalisis struktur dan hubungan buku kerja. Ketersediaannya bergantung pada edisi Office Anda, jadi periksalah halaman tersebut sebelum merencanakannya. Referensi melingkar (circular references) memerlukan penanganan tersendiri, dan Microsoft membahas cara menemukan dan menanganinya secara terpisah.
Inventarisasi adalah opsi yang paling lengkap sekaligus yang paling membutuhkan banyak pekerjaan. Cara ini juga menjawab pertanyaan yang berbeda dari pertanyaan yang diajukan kepada Anda.
Batasan yang sama. Ketiga cara tersebut menjelaskan bagaimana buku kerja melakukan perhitungan. Tidak ada yang memberi tahu Anda apakah angka-angkanya benar, dan tidak ada satu pun dari pekerjaan tersebut yang tetap berguna ketika versi empat tiba.
Cara menganalisis buku kerja warisan dengan Powerdrill Bloom
Langkah 1: Unggah buku kerja
Unggah file apa adanya saat Anda menerimanya, tanpa merapikannya terlebih dahulu. Powerdrill Bloom akan memprofilkan setiap lembar kerja saat diunggah. Jumlah lembar kerja, tipe kolom, blok kosong, dan tipe nilai yang tidak konsisten akan langsung terlihat bahkan sebelum Anda membaca satu rumus pun.
Langkah 2: Ajukan pertanyaan struktural dalam bahasa sehari-hari
Mulailah dengan peta alurnya, bukan dengan angkanya. Tanyakan lembar mana yang terlihat seperti input mentah dan mana yang terlihat seperti ringkasan turunan, serta di mana bidang (field) yang sama muncul dengan nilai yang berbeda di berbagai lembar kerja.
Kemudian ajukan pertanyaan tentang keandalan data secara langsung. Tanyakan kolom mana yang polanya terputus di tengah jalan, dan total mana yang tidak cocok dengan baris di bawahnya. Dua jawaban tersebut akan menemukan sebagian besar penimpaan manual.
Langkah 3: Ekspor bagan, laporan, atau draf presentasi
Ekspor ringkasan struktural dari buku kerja tersebut, atau bagan dari lembar kerja yang Anda pilih untuk dipercayai. Catatan tertulis singkat yang merekam apa saja yang telah Anda verifikasi juga bisa digunakan.
Mengapa cara ini lebih baik daripada membaca rumus sel demi sel
| Rute manual | Powerdrill Bloom | |
|---|---|---|
| Menemukan nilai hardcode | Kolom pembantu per lembar kerja | Tanyakan nilai mana yang merusak pola |
| Memahami hubungan | Panah pelacak, sel demi sel | Tanyakan lembar mana yang mengisi lembar lainnya |
| Memeriksa apakah angka total itu asli | Membangun ulang secara manual | Tanyakan apakah cocok dengan baris di bawahnya |
| Versi empat tiba | Ulangi semuanya | Unggah file baru |
Baris terakhir adalah hal yang mengubah kebiasaan. Memetakan buku kerja sekali saja adalah hal yang wajar dilakukan dalam satu sore. Namun, memetakan ulang setiap kali rekan kerja mengirimkan revisi adalah alasan mengapa orang-orang akhirnya malas memeriksa kembali.
Kesalahan Umum
Memercayai tab ringkasan begitu saja. Ini adalah lembar kerja yang paling sering diedit dalam buku kerja apa pun dan paling mungkin berisi tambalan manual. Verifikasi tab ini dengan data detail sebelum Anda mengutipnya.
Mengedit sebelum memetakan. Mengubah sel dalam struktur yang belum Anda pahami dapat merusak dependensi secara diam-diam. Petakan terlebih dahulu, baru kemudian edit.
Mengasumsikan kolom yang konsisten. Rumus yang berjalan lancar selama dua ratus baris bisa saja ditimpa secara manual pada baris ke-201. Periksa pola di sepanjang kolom, bukan hanya di bagian atasnya saja.
Mengabaikan lembar kerja yang tersembunyi. Lembar kerja yang tersembunyi sering kali berisi tabel referensi (lookup table) yang menjadi tumpuan segalanya. Tampilkan semua lembar kerja terlebih dahulu sebelum menyimpulkan bahwa file tersebut sederhana.
Menganggap nama file sebagai versi. File bernama "final" bukanlah bukti. Bandingkan angka sebenarnya di antara file-file kandidat sebelum memilih salah satu — panduan kami tentang menganalisis beberapa file Excel sekaligus membahas perbandingan tersebut.
Membangun ulang dari awal. Memang menggoda, tetapi biasanya merupakan kesalahan. Membangun ulang akan menghilangkan aturan-aturan tidak terdokumentasi yang ada pada file asli, padahal aturan-aturan tersebut sering kali menjadi satu-satunya alasan mengapa angka-angkanya bisa cocok (reconciled).
Membersihkan data sebelum memahaminya. Menghapus sel yang digabungkan (merged cells) dan baris kosong memang membuat file lebih mudah dibaca, tetapi hal itu merusak bukti tentang bagaimana file tersebut dibangun. Buat salinannya terlebih dahulu.
Kesimpulan
Buku kerja warisan adalah masalah pembacaan sebelum menjadi masalah analisis. Temukan lembar sumber yang sebenarnya, pisahkan nilai yang diketik manual dari nilai hasil perhitungan, ikuti referensinya ke luar, dan barulah setelah itu jawab pertanyaan yang diajukan kepada Anda.
All ini bukan tentang tidak memercayai orang yang membuatnya. Spreadsheet yang dibuat orang lain adalah rekaman keputusan yang diambil di bawah tekanan tenggat waktu, dan membacanya dengan cermat adalah harga yang harus dibayar untuk menggunakannya.
Yang membuatnya mahal adalah ketika Anda harus melakukannya lagi untuk setiap revisi. Jika waktu kerja Anda habis untuk hal tersebut, cobalah Powerdrill Bloom pada file tersebut persis seperti yang Anda terima. Lihat juga panduan kami tentang menganalisis Excel dengan AI dan membersihkan dan menghapus duplikasi data, serta halaman asisten AI Excel dan pembersihan data AI.
Pertanyaan yang sering diajukan
Bagaimana cara menemukan nilai hardcode dalam spreadsheet yang dibuat orang lain?
Tambahkan kolom pembantu menggunakan ISFORMULA, yang mengembalikan nilai TRUE untuk sel rumus dan FALSE untuk sel yang diketik manual. Setiap nilai FALSE di dalam blok perhitungan adalah penimpaan manual yang patut diselidiki.
Bagaimana cara melihat rumus di balik sel sebagai teks?
Gunakan FORMULATEXT di sel sebelahnya. Fungsi ini mengembalikan rumus sebagai string yang dapat dibaca, sehingga Anda dapat memindai logika satu kolom tanpa harus mengeklik setiap sel.
Bagaimana cara mengetahui dependensi suatu sel?
Gunakan Trace Precedents pada tab Formulas untuk melihat apa yang mengisi sel tersebut, dan Trace Dependents untuk melihat sel mana saja yang dipengaruhinya. Panah hitam yang mengarah ke ikon lembar kerja berarti referensi tersebut berada di luar lembar kerja saat ini.
Haruskah saya membersihkan buku kerja warisan sebelum menganalisisnya?
Jangan sebelum Anda memetakannya. Pembersihan akan menghapus bukti tentang bagaimana file tersebut dibangun, termasuk sel yang digabungkan dan blok kosong yang menandai struktur. Bagaimanapun juga, simpanlah salinan yang belum disentuh.
Apa cara tercepat untuk memeriksa apakah suatu angka total dapat dipercaya?
Bangun ulang dari baris-baris di bawahnya lalu bandingkan. Jika keduanya tidak cocok, berarti angka total tersebut berisi penimpaan manual, rentang yang difilter, atau referensi ke lembar kerja yang belum Anda periksa.