Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

Saya masih ingat momen saat saya menyadari bahwa saya telah melakukan spreadsheet dengan cara yang salah selama tujuh tahun. Itu tahun 2019, dan saya sedang duduk di ruang konferensi bersama tim saya di sebuah perusahaan logistik menengah, menyaksikan CFO kami menyalin dan menempel data secara manual antara tiga file Excel yang berbeda untuk menghasilkan laporan triwulan kami. Proses itu memakan waktu empat jam. Empat jam dengan tarif $150/jam, setiap kuartal, karena tidak ada yang mau belajar VLOOKUP.

Hari itu mengubah segalanya bagi saya. Saya Sarah Chen, dan saya telah menghabiskan 11 tahun terakhir sebagai konsultan operasi keuangan, bekerja dengan lebih dari 200 perusahaan untuk menyederhanakan alur kerja data mereka. Saya telah melihat bisnis membuang ribuan jam secara harfiah untuk tugas spreadsheet yang dapat diotomatisasi dengan hanya beberapa rumus. Ironisnya? Kebanyakan orang berpikir mereka perlu menguasai ratusan fungsi untuk "baik dalam Excel." Mereka tidak perlu. Dari pengalaman saya, 20 rumus menangani sekitar 95% skenario bisnis dunia nyata.

Ini bukan ensiklopedia komprehensif dari setiap fungsi spreadsheet yang pernah dibuat. Ini adalah arsenal yang telah teruji dalam pertempuran yang telah saya gunakan untuk menghemat waktu kerja manual klien saya yang diperkirakan lebih dari 12.000 jam. Ini adalah rumus yang benar-benar penting saat Anda mencoba menutup buku, menganalisis data penjualan, atau mencari tahu mengapa angka inventaris Anda tidak cocok.

Dasar: Aritmatika Dasar yang Menggerakkan Segalanya

Sebelum kita menjadi rumit, mari kita bicarakan tiga rumus yang membentuk tulang punggung dari hampir setiap model keuangan yang pernah saya buat. Ini mungkin terlihat jelas, tetapi saya telah melihat analis senior mengacaukannya dengan cara yang menghabiskan uang perusahaan.

SUM adalah kuda kerja. Sintaksnya sangat sederhana: =SUM(A1:A10) menjumlahkan segala sesuatu dalam rentang tersebut. Tetapi inilah yang kebanyakan orang tidak tahu: Anda dapat menggunakan SUM dengan beberapa rentang tidak berdekatan. =SUM(A1:A10, C1:C10, E5) bekerja dengan sempurna dan jauh lebih bersih daripada menulis referensi sel individu. Saya pernah mengaudit sebuah model keuangan di mana seseorang menulis =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 alih-alih menggunakan SUM. Ketika mereka perlu menyisipkan satu baris, rumus tersebut rusak. Jangan jadi orang itu.

AVERAGE juga sangat sederhana tetapi sangat kuat. =AVERAGE(B2:B50) memberikan Anda rata-rata dataset Anda. Insight kunci di sini adalah memahami kapan harus menggunakan AVERAGE versus AVERAGEIF (yang akan kita bahas nanti). Dalam pekerjaan konsultasi saya, saya menemukan bahwa sekitar 60% dari waktu orang menggunakan AVERAGE, mereka sebenarnya perlu menggunakan rata-rata bersyarat. Jika Anda menghitung penjualan rata-rata tetapi ingin mengecualikan nol atau outlier, AVERAGE biasa akan menipu Anda.

COUNT dan COUNTA adalah saudara kembar dengan kepribadian yang berbeda. COUNT hanya menghitung sel dengan angka, sementara COUNTA menghitung sel yang tidak kosong. Perbedaan ini lebih penting daripada yang Anda kira. Ketika saya mengaudit kualitas data, saya menggunakan =COUNTA(A:A)-COUNT(A:A) untuk segera menemukan berapa banyak sel di kolom A yang berisi teks daripada angka. Jika angka tersebut lebih tinggi dari yang diharapkan, saya tahu ada masalah entri data. Cek sederhana ini telah menangkap ribuan dolar dalam kesalahan akuntansi untuk klien saya.

Fungsi Pencarian: The s

Jika saya hanya bisa mengajarkan seseorang tiga rumus, dua di antaranya adalah fungsi pencarian. Ini adalah perbedaan antara menghabiskan 30 menit mencocokkan data secara manual dan menghabiskan 30 detik menulis rumus yang melakukannya secara instan.

"Dalam pengalaman saya bekerja dengan lebih dari 200 perusahaan, 20 rumus menangani sekitar 95% skenario bisnis dunia nyata. Kebanyakan orang membuang waktu mencoba menguasai ratusan fungsi yang tidak akan pernah mereka gunakan."

VLOOKUP adalah yang klasik, dan meskipun ada alternatif yang lebih baru, ini masih sangat berguna. Sintaksnya adalah =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Berikut adalah contoh nyata dari bulan lalu: Saya memiliki klien dengan 5.000 catatan pelanggan di satu lembar dan 5.000 catatan transaksi di lembar lain. Mereka perlu mencocokkan nama pelanggan dengan ID transaksi. =VLOOKUP(A2, Customers!A:D, 3, FALSE) melakukan dalam tiga detik apa yang akan memakan waktu berjam-jam secara manual.

Hal kritis yang salah dipahami oleh semua orang: argumen keempat itu. FALSE berarti kecocokan yang tepat, TRUE berarti kecocokan mendekati. Untuk 99% kasus penggunaan bisnis, Anda menginginkan FALSE. Saya telah melihat seluruh laporan keuangan menjadi korup karena seseorang meninggalkannya sebagai TRUE (atau menghilangkannya, yang secara default menjadi TRUE) dan mendapatkan kecocokan mendekati alih-alih yang tepat.

XLOOKUP adalah pengganti modern, dan sejujurnya, ini lebih baik di hampir setiap cara. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) terdengar rumit, tetapi dalam praktiknya lebih bersih. Fitur unggulannya? Ini dapat mencari ke kiri, yang tidak dapat dilakukan VLOOKUP. Saya baru-baru ini memigrasi seluruh sistem pelaporan klien dari VLOOKUP ke XLOOKUP dan mengurangi kesalahan rumus mereka sekitar 40% karena XLOOKUP menangani data yang hilang lebih baik.

INDEX-MATCH adalah pilihan pengguna profesional. Sebenarnya ini adalah dua rumus yang digabungkan: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Kombinasi ini lebih fleksibel daripada VLOOKUP dan lebih cepat dengan dataset besar. Ketika saya bekerja dengan spreadsheet lebih dari 50.000 baris, INDEX-MATCH memberi kinerja yang lebih baik. Kurva pembelajarannya lebih curam, tetapi setelah Anda menguasainya, Anda tidak akan pernah kembali. Saya menggunakannya untuk model keuangan yang kompleks di mana saya perlu mencari nilai berdasarkan dua kriteria secara bersamaan.

Logika Kondisional: Membuat Spreadsheet Anda Berpikir

Ini adalah tempat spreadsheet bertransformasi dari kalkulator menjadi alat pengambilan keputusan. Rumus kondisional memungkinkan Anda membangun logika ke dalam data Anda, dan mereka sangat penting untuk setiap jenis pelaporan otomatis.

IF adalah rumus pemula untuk otomatisasi spreadsheet. =IF(logical_test, value_if_true, value_if_false) memungkinkan Anda membuat logika bercabang. Contoh sederhana: =IF(B2>1000, "Nilai Tinggi", "Standar") mengkategorikan pelanggan berdasarkan jumlah pembelian. Tetapi kekuatan sejatinya datang dari menampung IF. Saya telah membangun kalkulator komisi dengan lima pernyataan IF bersarang yang secara otomatis menentukan bonus perwakilan penjualan berdasarkan beberapa kriteria. Kuncinya adalah menjaga agar tetap terbaca—gunakan indentasi dan komentar yang tepat.

IFS adalah alternatif yang lebih bersih untuk IF bersarang, tersedia di versi spreadsheet yang lebih baru. Alih-alih =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F"))), Anda menulis =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F"). Ini lebih mudah dibaca dan kurang rentan terhadap kesalahan tanda kurung. Saya beralih ke IFS sekitar tiga tahun lalu dan waktu debugging rumus saya berkurang setengahnya.

SUMIF dan SUMIFS adalah kekuatan penjumlahan bersyarat. =SUMIF(range, criteria, [sum_range]) menjumlahkan nilai yang memenuhi suatu kondisi. =SUMIFS memperluas ini ke beberapa kondisi. Contoh nyata: Saya mempunyai klien ritel yang perlu menghitung total penjualan berdasarkan wilayah dan kategori produk. =SUMIFS(Sales!C:C, Sales!A:A, "Barat", Sales!B:B, "Elektronik") memberikan mereka tepat itu. Sebelum mempelajari SUMIFS, mereka menggunakan tabel pivot untuk segalanya, yang berlebihan dan lebih lambat untuk diperbarui.

COUNTIF dan COUNTIFS berfungsi dengan cara yang sama tetapi menghitung alih-alih menjumlah. Saya terus-menerus menggunakan ini untuk memeriksa kualitas data. =COUNTIF(A:A, "Error") memberi tahu saya berapa banyak sel di kolom A yang berisi kata "Error." =COUNTIFS(A:A, ">100", B:B, "<50") menghitung baris di mana kolom A di atas 100 DAN kolom B di bawah 50. Rumus-rumus ini telah menghemat waktu saya yang tak terhitung untuk validasi data manual.