Panduan praktis rumus Excel

Berikut kumpulan rumus dasar Excel yang dikelompokkan menurut tugasnya. Contoh memakai nama fungsi berbahasa Inggris dan koma sebagai pemisah argumen. Nama fungsi dan pemisah dapat berbeda pada Excel dengan pengaturan bahasa lain: versi Rusia, misalnya, umumnya memakai titik koma. Cara kerja perhitungannya tetap sama.

Jika Anda memerlukan contoh khusus untuk estimasi biaya renovasi, baca artikel 101 Cara menghitung di Excel (dalam bahasa Rusia). Artikel itu menunjukkan alur dasarnya: kuantitas × harga, menyalin rumus ke bawah, lalu membuat baris total.

Bagaimana membaca rumus Excel agar lebih sedikit melakukan kesalahan?

Rumus terlihat jelas selama berada di satu sel. Begitu Anda menyalinnya ke bawah, hal yang tak terduga bisa terjadi: rentang bergeser, referensi ke judul kolom terlepas, atau harga dari baris lain ikut terbaca. Dua kebiasaan dapat membantu.

Pertama, tentukan referensi mana yang harus bergeser ketika rumus disalin dan mana yang harus tetap. Tanda dolar mengunci referensi: $A$1 mengunci kolom dan baris; $A1 mengunci kolom; A$1 mengunci baris.

Kedua, simpan angka sebagai data numerik. Jika harga tanpa sengaja tersimpan sebagai teks, sebagian rumus dapat menghasilkan kesalahan atau nol. Petunjuk cepatnya: dalam format standar, angka biasanya rata kanan, sedangkan teks rata kiri.

Aritmetika dan referensi sel

Inilah rumus yang menjadi dasar hampir setiap tabel estimasi biaya atau pencatatan.

Ambil satu baris estimasi: B2 berisi kuantitas, C2 harga, dan D2 nilai baris.

• =B2*C2 — perkalian untuk menghitung nilai per baris.
• =B2+C2, =B2-C2, dan =B2/C2 — penjumlahan, pengurangan, dan pembagian.
• =ROUND(D2,0) — pembulatan ke rubel utuh pada contoh aslinya. Untuk kopek, yaitu dua angka desimal: =ROUND(D2,2).
• =ABS(D2) — nilai absolut, berguna jika ada nilai negatif.

Gambar berasal dari contoh berbahasa Rusia; rumus dalam teks ini memakai nama fungsi berbahasa Inggris. Tentukan pembulatan dengan cermat: membulatkan setiap baris dapat menghasilkan total yang berbeda dari membulatkan total keseluruhan. Dalam estimasi biaya, biasanya total akhir yang dibulatkan, sedangkan nilai per baris tetap disimpan hingga dua angka desimal.

Jumlah, rata-rata, nilai minimum dan maksimum

Fungsi ini menjawab pertanyaan “berapa semuanya?” dan “berapa harga yang umum?”. Pada estimasi biaya, hasilnya dapat berupa total satu bagian atau seluruh proyek. Pada tabel pengeluaran, hasilnya dapat berupa total satu minggu, satu bulan, atau satu proyek.

• =SUM(D2:D200) — jumlah seluruh rentang.
• =AVERAGE(C2:C200) — harga rata-rata.
• =MIN(C2:C200) dan =MAX(C2:C200) — nilai terendah dan tertinggi.
• =COUNT(D2:D200) — jumlah sel yang berisi angka.
• =COUNTA(D2:D200) — jumlah sel yang tidak kosong, termasuk teks serta sel berisi rumus yang menghasilkan string kosong.

Jika tabel panjang dan memiliki baris kosong, sediakan ruang tambahan dalam rentang dan hitung berdasarkan kolom yang selalu diisi, misalnya “Nilai”.

Bagaimana menghitung dan menjumlahkan berdasarkan kriteria?

Ketika baris dikelompokkan berdasarkan bagian, jenis pekerjaan, proyek, kontraktor, atau kategori pengeluaran, jumlah umum saja tidak cukup. Anda mungkin perlu “menghitung material saja”, “menjumlahkan proyek nomor 3 saja”, atau “melihat biaya untuk tukang pasang keramik”.

• =SUMIF(A:A,"Material",D:D) — jumlah dengan satu kriteria.
• =COUNTIF(A:A,"Pekerjaan") — jumlah baris yang memenuhi kondisi.
• =AVERAGEIF(A:A,"Pengiriman",C:C) — rata-rata berdasarkan kondisi.

Jika ada beberapa kriteria, gunakan fungsi yang menerima beberapa pasangan kondisi.

• =SUMIFS(D:D,A:A,"Material",C:C,">0") — jumlah material dengan harga positif.
• =COUNTIFS(A:A,"Material",C:C,">0") — jumlah baris material dengan harga positif.

Pendekatan serupa untuk mencatat pengeluaran dijelaskan dalam artikel 101 Cara mencatat pengeluaran di Excel (dalam bahasa Rusia): ketika pengeluaran banyak, angka akan cepat kehilangan makna tanpa pengelompokan berdasarkan kategori dan kondisi.

Logika dan kesalahan

Fungsi logika mengotomatiskan aturan yang biasanya diucapkan: “jika ada diskon, terapkan”, “jika kuantitas atau harga belum diisi, jangan hitung nilainya”, dan “jika pembagi nol, tampilkan kosong”.

• =IF(OR(B2="",C2=""),"",B2*C2) — menghitung nilai hanya jika kuantitas dan harga sama-sama terisi.
• =AND(A2<>"",C2>0) — memeriksa dua kondisi sekaligus.
• =OR(A2="Material",A2="Pekerjaan") — memeriksa apakah salah satu kondisi terpenuhi.
• =IFERROR(B2/C2,"") — menghasilkan string kosong jika pembagian menimbulkan kesalahan.

Pembagian sering muncul saat estimasi biaya menghitung persentase diskon atau margin. Jika penyebutnya nol, Excel menghasilkan kesalahan. IFERROR membantu menghindari pekerjaan membersihkan kesalahan yang sudah diperkirakan secara manual.

Pencarian dan tabel referensi

Tabel referensi adalah lembar yang menyimpan data acuan: daftar harga, daftar pekerjaan, norma, atau koefisien. Pada lembar perhitungan, Anda cukup memasukkan kode atau nama, lalu harga diambil secara otomatis. Cara ini mengurangi entri manual dan membantu menjaga harga tetap konsisten antarproyek. Dalam contoh berikut, E2 berisi kode item. Untuk VLOOKUP dan MATCH, kode berada di kolom pertama lembar “DaftarHarga”; untuk HLOOKUP, kode berada di baris pertama.

• =VLOOKUP(E2,DaftarHarga!A:D,4,FALSE) — mencari kode di kolom pertama tabel referensi dan mengembalikan nilai dari kolom keempat.
• =HLOOKUP(E2,DaftarHarga!A1:Z3,3,FALSE) — pencarian horizontal ketika tabel referensi disusun per baris.
• =INDEX(DaftarHarga!D:D,MATCH(E2,DaftarHarga!A:A,0)) — gabungan INDEX dan MATCH yang fleksibel dan kerap menggantikan VLOOKUP.

Versi Excel modern juga memiliki XLOOKUP. Fungsi ini mencari di satu rentang dan mengembalikan nilai yang sesuai dari rentang lain tanpa nomor kolom. Jika XLOOKUP tidak tersedia, gabungan INDEX dan MATCH tetap menjadi pilihan yang kompatibel dengan banyak versi.

Jika daftar harga dan estimasi biaya Anda sudah dibuat di Excel, Anda tidak perlu mengetik ulang semuanya saat beralih ke Aplikasi 101. Baca Impor cepat estimasi biaya dari Excel ke Aplikasi 101 (dalam bahasa Rusia).

Teks, tanggal, dan array dinamis

Rumus teks membantu saat data yang masuk belum rapi: spasi berlebih, nama lengkap yang digabung, kode barang, atau komentar. Tanggal diperlukan untuk periode pengeluaran, tenggat pekerjaan, dan perbandingan rencana dengan realisasi.

• =LEN(A2) — panjang teks.
• =LEFT(A2,5), =RIGHT(A2,5), dan =MID(A2,3,4) — mengambil sebagian teks.
• =TRIM(A2) — menghapus spasi berlebih.
• =TEXT(DATE(2026,2,3),"dd.mm.yyyy") — mengubah tanggal menjadi teks dengan format tersebut; berguna untuk ekspor data.
• =TODAY() dan =NOW() — tanggal saat ini serta tanggal dan waktu saat ini.
• =DATE(G2,H2,I2) — membuat tanggal dari tahun di G2, bulan di H2, dan hari di I2.

Jika versi Excel Anda mendukung array dinamis, Anda dapat membuat daftar yang ikut berubah saat data sumber berubah. Anda bisa menyaring, mengurutkan, dan mengambil nilai unik tanpa membuat tabel pivot.

• =FILTER(A2:D200,A2:A200="Material","") — mengembalikan baris yang memenuhi kondisi.
• =SORT(A2:D200,4,-1) — mengurutkan berdasarkan kolom keempat dari nilai terbesar ke terkecil.
• =UNIQUE(A2:A200) — daftar nilai unik.

Bagaimana membuat template estimasi biaya kecil dengan rumus?

Langkah singkat berikut menghasilkan estimasi biaya yang dapat digunakan di Excel. Anda dapat mengembangkannya dengan bagian pekerjaan, diskon, markup, biaya aktual, dan pembayaran.

1. Buat kolom Bagian, Item, Satuan, Kuantitas, Harga, Nilai, dan Komentar.
2. Di kolom “Nilai”, masukkan rumus perkalian =D2*E2 lalu salin ke bawah.
3. Tambahkan total tabel: =SUM(F2:F200).
4. Tambahkan subtotal per bagian dengan SUMIF: =SUMIF(A:A,"Pekerjaan kasar",F:F).
5. Jika ada daftar harga, ambil harga dengan VLOOKUP atau gabungan INDEX dan MATCH.
6. Tangani kesalahan saat mencari harga: =IFERROR(VLOOKUP(B2,DaftarHarga!A:D,4,FALSE),"").

Jika berkas seperti ini semakin banyak, menentukan “versi terbaru”, mengelola hak akses, dan menjaga keutuhan rumus menjadi lebih sulit. Kami membahasnya dalam Excel atau Aplikasi 101 untuk estimasi biaya dan pencatatan keuangan (dalam bahasa Rusia) dan melanjutkannya dalam Excel atau Aplikasi 101 untuk pengelolaan proyek (dalam bahasa Rusia).

Jika ingin menunjukkan perhitungan yang jelas kepada klien tanpa menyusun laporan secara manual, lihat bagaimana perhitungan estimasi biaya daring bekerja (dalam bahasa Rusia) dan cara membuat estimasi biaya dari awal di Aplikasi 101 (dalam bahasa Rusia).