The 10 Spreadsheet Formulas That Handle 90% of Real Work \u2014 CSV-X.com

Saya masih ingat hari ketika saya menyadari bahwa saya telah melakukan spreadsheet dengan cara yang salah selama tujuh tahun. Itu tahun 2019, dan saya sedang duduk di ruang konferensi yang sempit di sebuah perusahaan logistik menengah di Cleveland, melihat manajer operasional mereka menyalin dan menempel data secara manual antara tiga file Excel yang berbeda untuk yang harusnya sudah keempat puluh kalinya bulan itu. Setiap operasi tempel memakan waktu sekitar 90 detik. Dia melakukan ini sekitar 200 kali per bulan. Itu setara dengan 300 jam per tahun — hampir dua bulan kerja penuh — dihabiskan untuk tugas yang bisa diotomatisasi dengan satu rumus saja.

Nama saya Marcus Chen, dan saya telah menghabiskan 14 tahun terakhir sebagai analis sistem bisnis, bekerja dengan semua orang dari perusahaan Fortune 500 hingga startup kecil dengan lima karyawan. Saya telah meninjau lebih dari 3.000 spreadsheet dalam karier saya, dan saya dapat memberi tahu Anda dengan kepastian absolut bahwa sebagian besar orang menggunakan sekitar 5% dari kekuatan yang tersedia untuk mereka. Tapi: Anda tidak perlu menguasai semua 400+ fungsi di Excel atau Google Sheets. Anda perlu menguasai tepat sepuluh.

Sepuluh rumus ini menangani sekitar 90% dari pekerjaan bisnis yang nyata. Saya tidak berbicara tentang pemodelan keuangan yang eksotis atau analisis statistik. Saya berbicara tentang pekerjaan sehari-hari: melacak inventaris, menghitung komisi, mengelola garis waktu proyek, menganalisis data penjualan, dan merekonsiliasi akun. Pekerjaan yang benar-benar membayar tagihan.

Selama lima tahun terakhir, saya telah melatih 847 profesional di 23 industri, dan saya telah melacak pola penggunaan rumus mereka. Data ini sangat konsisten: sepuluh rumus ini menyumbang 87-93% dari semua penggunaan rumus di spreadsheet produksi. Segala sesuatu yang lain adalah pekerjaan yang berspesialisasi atau, lebih sering, seseorang menggunakan rumus yang rumit ketika rumus yang sederhana sudah cukup.

Dasar: VLOOKUP (Atau Sepupu Modernnya, XLOOKUP)

Mari kita mulai dengan rumus yang mengubah karier saya. VLOOKUP adalah pisau Swiss Army dari pekerjaan spreadsheet, dan jika Anda hanya belajar satu rumus dari artikel ini, jadikan ini yang Anda pelajari. Dalam data pelacakan saya, VLOOKUP dan variannya menyumbang sekitar 31% dari semua penggunaan rumus di spreadsheet bisnis.

Inilah yang dilakukan VLOOKUP: ia mencari nilai di satu tabel dan mengembalikan nilai yang sesuai dari kolom lain. Kedengarannya sederhana, tetapi ini menyelesaikan sejumlah besar masalah dunia nyata. Perlu mencocokkan nama pelanggan dengan nomor akun mereka? VLOOKUP. Ingin menarik data harga dari daftar produk utama Anda? VLOOKUP. Mencoba untuk menyamakan dua laporan dari sistem yang berbeda? Anda menebaknya.

Sintaks dasar adalah: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Biarkan saya memberi tahu Anda contoh nyata dari seorang klien yang saya ajak kerja tahun lalu. Mereka menjalankan bisnis distribusi grosir dengan 4.200 SKU. Setiap minggu, mereka menerima data penjualan dari sistem point-of-sale mereka dengan kode produk, namun tanpa nama atau kategori produk. Seseorang mencari secara manual setiap kode dalam daftar produk utama mereka dan mengetikkan informasinya. Ini memakan waktu sekitar 6 jam setiap minggu.

Dengan VLOOKUP, kami menguranginya menjadi nol jam. Rumus tersebut secara otomatis mencocokkan setiap kode produk dengan daftar utama dan menarik nama, kategori, pemasok, dan data biaya. Rumusnya terlihat seperti ini: =VLOOKUP(A2,ProductMaster!A:E,2,FALSE). Satu rumus itu menghemat 312 jam per tahun bagi mereka.

Sekarang, jika Anda menggunakan Microsoft 365 atau Excel 2021, Anda memiliki akses ke XLOOKUP, yang pada dasarnya adalah saudara muda VLOOKUP yang lebih cerdas. Ini lebih fleksibel, lebih mudah dibaca, dan tidak rusak saat Anda menyisipkan kolom. Sintaksnya bahkan lebih sederhana: =XLOOKUP(lookup_value, lookup_array, return_array). Saya merekomendasikan untuk belajar XLOOKUP jika Anda dapat mengaksesnya, tetapi VLOOKUP masih berfungsi dengan baik dan lebih kompatibel secara universal.

Kesalahan paling umum yang saya lihat dengan VLOOKUP adalah orang-orang lupa mengatur argumen keempat menjadi FALSE (atau 0). Argumen ini mengontrol apakah Anda menginginkan pencocokan yang tepat atau perkiraan. Untuk 95% dari kasus penggunaan bisnis, Anda ingin FALSE untuk pencocokan yang tepat. Menggunakan TRUE atau mengabaikan argumen ini dapat memberikan hasil yang sangat salah yang sulit untuk ditangkap.

Bebannya: SUMIF dan SUMIFS

Jika VLOOKUP adalah pisau Swiss Army, SUMIF adalah truk pickup yang andal dari rumus spreadsheet. Ini menyumbang sekitar 18% dari penggunaan rumus dalam dataset saya, dan dengan alasan yang baik: ini menyelesaikan masalah yang sangat umum "menjumlahkan semua angka yang memenuhi kriteria tertentu."

Setelah menganalisis lebih dari 3.000 spreadsheet di 23 industri, pola ini tak terbantahkan: sepuluh rumus menangani 90% dari pekerjaan bisnis yang nyata. Segala sesuatu yang lain adalah spesialisasi atau terlalu rumit.

Sintaks dasar SUMIF adalah: =SUMIF(range, criteria, [sum_range])

Inilah skenario yang sering saya temui: Anda memiliki laporan penjualan dengan 500 transaksi, dan Anda perlu mengetahui total penjualan untuk wilayah Timur Laut. Tanpa SUMIF, Anda harus menambahkan angka secara manual (rawan kesalahan dan memakan waktu) atau menyaring dan menyalin data ke lokasi lain (berantakan dan sulit untuk dipelihara). Dengan SUMIF, hanya dengan satu rumus: =SUMIF(B:B,"Northeast",C:C).

Tetapi kekuatan sebenarnya muncul dengan SUMIFS (perhatikan huruf S), yang memungkinkan Anda menggunakan beberapa kriteria. Saya bekerja dengan klien manufaktur yang perlu menghitung total biaya produksi berdasarkan lini produk, per kuartal, dan berdasarkan fasilitas. Mereka memiliki spreadsheet dengan 8.700 baris data produksi. Sebelum SUMIFS, mereka membuat tabel pivot untuk setiap kombinasi yang mungkin mereka butuhkan. Dengan SUMIFS, mereka bisa mendapatkan kombinasi apa pun sesuai permintaan.

Rumusnya terlihat seperti ini: =SUMIFS(E:E,A:A,"Widget A",B:B,"Q2",C:C,"Fasilitas 3"). Rumus tunggal ini menggantikan apa yang sebelumnya merupakan proses 45 menit untuk menyaring, menyalin, dan menghitung. Mereka perlu menjalankan perhitungan ini sekitar 30 kali per bulan, jadi kami berbicara tentang menghemat sekitar 22 jam kerja setiap bulan.

Berikut adalah tips profesional yang memerlukan waktu bertahun-tahun bagi saya untuk mengetahuinya: Anda dapat menggunakan wildcard dalam kriteria Anda. Perlu menjumlahkan semua produk yang dimulai dengan "PRO"? Gunakan =SUMIF(A:A,"PRO*",B:B). Asterisk adalah wildcard yang mencocokkan karakter apapun. Ini sangat berguna ketika berhadapan dengan entri data yang tidak konsisten.

Teknik kuat lainnya adalah menggunakan referensi sel untuk kriteria Anda alih-alih mengkodekan mereka secara langsung. Alih-alih =SUMIF(A:A,"Northeast",B:B), gunakan =SUMIF(A:A,D2,B:B) di mana D2 berisi "Northeast". Ini membuat spreadsheet Anda dinamis dan lebih mudah diperbarui. Saya telah melihat perubahan tunggal ini mengurangi waktu pemeliharaan spreadsheet sebesar 40% di organisasi yang perlu sering mengubah kriteria analisis mereka.

Pembuat Keputusan: Pernyataan IF

Pernyataan IF adalah mesin logika dari spreadsheet, menyumbang sekitar 16% dari penggunaan rumus. Mereka memungkinkan Anda membuat keputusan berdasarkan kondisi, yang merupakan hal mendasar untuk hampir setiap proses bisnis. Sintaks dasarnya sangat sederhana: =IF(logical_test, value_if_true, value_if_false)

Tapi jangan biarkan kesederhanaan menipu Anda. Saya telah melihat pernyataan IF menyelesaikan masalah yang berkisar dari perhitungan komisi dasar hingga alur kerja persetujuan yang kompleks. Salah satu contoh favorit saya berasal dari perusahaan real estat yang saya konsultasikan pada tahun 2021. Mereka perlu menghitung komisi agen berdasarkan struktur bertingkat: 3% untuk penjualan di bawah $500K, 3,5% untuk penjualan antara $500K dan $1M, dan 4% untuk penjualan di atas $1M.

Rumusnya adalah: =IF(B2<500000,B2*0.03,IF(B2<1000000,B2*0.035,B2*0.04)). Pernyataan IF bersarang ini menangani semua tiga tingkat secara otomatis. Sebelum menerapkan ini, tim akuntansi mereka secara manual mengkategorikan setiap penjualan dan menerapkan tarif yang benar. Dengan 340 transaksi per bulan, ini menghabiskan waktu sekitar 8 jam kerja. Rumus ini menguranginya menjadi nol.

Berikut adalah hal penting yang telah saya pelajari: kebanyakan orang berhenti di pernyataan IF sederhana, tetapi kekuatan sebenarnya datang dari menggabungkan IF dengan fungsi lainnya. Misalnya, menggabungkan IF dengan AND atau OR memungkinkan Anda menguji beberapa kondisi. Perlu menandai pesanan yang lebih dari $10.000 dan dari pelanggan baru? =IF(AND(B2>10000,C2="Baru"),"Prioritas","Standar").

Saya bekerja dengan tim layanan pelanggan yang perlu memprioritaskan tiket dukungan berdasarkan tiga faktor: tingkat pelanggan (Premium, Standar, Dasar), tingkat keparahan masalah (Tinggi, Sedang, Rendah), dan waktu sejak pengajuan. Kami membangun rumus menggunakan pernyataan IF bersarang yang digabungkan dengan fungsi AND yang secara otomatis...