Dari Excel ke Pesawat Luar Angkasa dalam Dua Add-in 😎

Excel, yang digunakan di 90% perusahaan dan sering dicaci maki, sebenarnya hanya menggunakan sedikit dari kemampuannya. Data dimasukkan secara manual, tipe data diedit dengan tangan, dan pertanyaan "apakah ini akan diperbarui sendiri?" membuat orang menghela napas berat. Karena dua tab di menu masih belum tersentuh.

Jadwal kebanyakan orang seperti ini: unduh file, bersihkan manual, tambahkan rumus, kirim ke bos. Besok ulangi lagi. Lusa ulangi lagi. Membosankan sekali. 😔
Empat puluh menit setiap hari untuk pekerjaan yang seharusnya hanya butuh tiga klik.

Sekarang tambahkan 2 juta baris. Excel mentok di satu juta baris dan crash. Oke, bagi file menjadi beberapa bagian. Kehilangan hubungan antar tabel, pivot mulai berbohong.

Tapi Anda bisa mengaktifkan 2 add-in dan menggunakan Excel secara maksimal.
Saya berkenalan dengan add-in ini pada tahun 2017. Saya kerja freelance. Situs WordPress dan rutinitas lainnya. Tiba-tiba ada tugas. Harus mengekstrak hanya grup produk tertentu dari 60 file Excel besar dan menggabungkannya dalam satu file. Diberi waktu dua hari. Saya coba gabung 2 file manual. Mengerikan, membosankan. Saya mulai mencari di internet, mungkin ada solusi ajaib. Dan saya menemukan Power Query. Setelah sedikit bermain dengan antarmuka, saya menghubungkan semua file dari folder dan mengumpulkan file target dalam 10 menit. Saya hampir berdiri. Baiklah, mari saya ceritakan apa itu add-in ini 💪

1️⃣ Power Query: data tanpa sentuhan tangan

Ini sudah terintegrasi. Tab "Data", bagian "Dapatkan & Transformasi". Hubungkan sumber sekali, atur transformasi, lalu klik "Refresh". Proses yang dulu memakan waktu satu jam, sekarang hanya sepuluh detik.

Tapi ada masalah. Power Query "menebak" tipe data hanya dengan melihat 200–1000 baris pertama, tergantung sumbernya. Untuk CSV cukup 200, untuk format lain sedikit lebih. Tapi intinya sama: jika data tidak homogen, angka diam-diam berubah menjadi teks tanpa peringatan.

Karena itu, agar tidur nyenyak, selalu atur tipe data secara manual. Jangan percaya deteksi otomatis. Ya, lebih lama. Tapi nanti tidak perlu mengulang pengaturan lama dan mencari masalah.

Dan satu lagi. Jangan buat satu query raksasa. Selalu gunakan pendekatan iteratif dalam bekerja.
Rantai bekerja lebih baik: langkah terpisah untuk pembersihan, langkah terpisah untuk transformasi. Pemuatan juga terpisah. Ketika sesuatu rusak (dan itu pasti terjadi), Anda akan menemukan masalah dalam satu menit, bukan satu jam.

Dan ya, PQ memungkinkan menghubungkan database secara langsung ✅

2️⃣ Power Pivot: Excel tanpa batas

1.048.576 baris adalah batas Excel biasa. Power Pivot menangani puluhan juta baris melalui kompresi VertiPaq (mesin yang sama di dalam Power BI). File xlsx itu sendiri menjadi lebih berat, karena di dalamnya dikemas database. Tapi di memori, data memakan ruang jauh lebih sedikit: VertiPaq mengompres sekitar 10 kali lipat (yah, kira-kira begitu — angka pastinya tergantung data), menyimpan secara kolom, bukan baris. Data besar melesat.

Yang utama di sini adalah measures. Jangan menjejalkan semuanya ke kolom terhitung: kolom terhitung selalu dihitung dan membengkakkan model, sementara measure hanya dihitung saat Anda melihatnya di pivot, dalam konteks filter tertentu. Perbedaannya signifikan.

Jantung Power Pivot adalah fungsi CALCULATE. Fungsi ini mengubah konteks perhitungan. "Penjualan tanpa retur", "rencana hanya untuk Moskow", "bagaimana jika harga naik 10%". Semua itu CALCULATE.

Untuk pecinta Apple, yang juga saya sendiri 😁
Power Pivot tidak tersedia di macOS. Sedih, tapi begitulah. Jika ada rekan tim yang menggunakan Mac, mereka tidak akan melihat keajaiban ini.

Jika laporan Anda berkutat di Excel — coba dua add-in ini 👇

#excel

@data_dzen 🙂