Kita semua pernah mengalaminya, dan seringkali pada saat yang paling buruk. Untuk alasan apa pun, rumus Excel tidak menghitung dengan benar. Kabar baiknya adalah biasanya sesuatu yang sederhana. Setelah kami mengetahui penyebab yang paling mungkin, lebih mudah untuk memecahkan masalah. Jadi, dalam posting ini, kami melihat alasan yang paling mungkin mengapa rumus Excel tidak menghitung
14 alasan dan perbaikan dalam posting ini, seharusnya memberi Anda apa yang perlu Anda ketahui. Jadi, jika Anda khawatir Excel berhenti menghitung atau angka tidak diperbarui, kami punya jawabannya untuk Anda
Tonton videonya
Tonton videonya di YouTube
Isi
Opsi perhitungan – otomatis atau manual (#1)
Excel memiliki dua opsi perhitungan, Otomatis dan Manual. Sebagian besar pengguna bahkan tidak tahu bahwa kedua mode ini ada. Namun, yang membuat frustrasi, dalam beberapa keadaan, mereka dapat berubah tanpa kita sadari
Penghitungan otomatis adalah mode default. Di sinilah rumus menghitung ulang setelah perubahan apa pun yang memengaruhi hasil perhitungan
Rumus Excel efisien dalam menangani perhitungan. Di latar belakang, Excel mengetahui sel mana yang memengaruhi rumus mana. Oleh karena itu, mode penghitungan Otomatis menghitung ulang jumlah minimum sel. Ini memastikan Excel cepat sekaligus menjaga semua nilai tetap mutakhir. Ini adalah perilaku yang kami pahami dan harapkan
Namun, karena ukuran dan kerumitan beberapa spreadsheet atau karena add-in pihak ketiga, kecepatan perhitungan bisa menjadi sangat lambat. Ini mengarahkan beberapa pengguna untuk mengubah opsi Excel ke mode perhitungan Manual. Akibatnya, Excel tidak menghitung ulang apa pun. Dalam mode kalkulasi Manual, kita dapat mengubah nilai sel, tetapi tidak terjadi apa-apa. Oleh karena itu, kita harus secara eksplisit memberi tahu Excel kapan harus menghitung ulang
Oleh karena itu, jika Excel dalam mode penghitungan manual, akan muncul masalah karena kita melihat rumus Excel tidak menghitung
CATATAN. Sayangnya, Excel dapat berubah antara mode penghitungan Manual dan Otomatis tanpa kita sadari. Jadi lihat posting ini. Mengapa mode perhitungan Excel terus berubah?
Untuk menentukan mode perhitungan mana yang diaktifkan, klik Rumus > Opsi Perhitungan (turun bawah). Centang di dalam drop-down mengidentifikasi opsi perhitungan mana yang diterapkan
Klik Otomatis untuk memastikan perhitungan terjadi secara otomatis
Mode perhitungan adalah pengaturan tingkat aplikasi;
Jika Anda ingin tetap dalam mode kalkulasi Manual, berikut adalah beberapa pintasan yang bermanfaat
- F9. Menghitung rumus yang telah berubah sejak perhitungan terakhir, dan rumus yang bergantung padanya, di semua buku kerja yang terbuka
- Geser + F9. Menghitung rumus yang telah berubah sejak perhitungan terakhir, dan rumus yang bergantung padanya, di lembar kerja aktif
- Ctrl + Alt + F9. Menghitung semua rumus di semua buku kerja yang terbuka, terlepas dari apakah rumus tersebut telah berubah sejak terakhir kali atau tidak
- Ctrl + Shift + Alt + F9. Memeriksa ulang rumus dependen lalu menghitung semua rumus di semua buku kerja yang terbuka, terlepas dari apakah rumus tersebut telah berubah sejak terakhir kali atau tidak
Ini mungkin alasan #1 mengapa kita melihat rumus Excel tidak menghitung
TIPS ATAS. Tambahkan opsi penghitungan Otomatis/Manual ke QAT Anda. Ini adalah cara mudah untuk beralih antar mode dan mengidentifikasi mode mana yang aktif
Format angka salah (#2)
Saat melihat nilai sel, kami memutuskan jenis datanya. Kami tahu secara naluriah bahwa angka dapat dikumpulkan, tetapi teks tidak bisa. Sayangnya, Excel tidak begitu pintar
Di dalam Home > Number, kita dapat memilih format sel
Jika sel diformat sebagai teks dan bukan angka, Excel mungkin tidak menghitung dan tidak menampilkan nilai yang kita harapkan
Lihat tangkapan layar di bawah ini
Di Sel B4, JUMLAH dari B2. B3 sama dengan 1; . Ini karena B3 diformat sebagai teks, dan fungsi SUM mengabaikan teks. Jadi, meskipun perhitungannya terlihat seperti 1+1 = 1 (yang salah), sebenarnya 1+0 = 1 (yang benar)
Untuk memperbaikinya, berikut adalah 3 opsi
- Perubahan manual
- Ubah format sel dari Teks ke format lain (Umum adalah pilihan yang baik untuk memulai)
- Klik dua kali pada sel yang bermasalah untuk masuk ke mode edit
- Tekan kembali untuk melakukan rumus
- Jika sel memiliki segitiga hijau
- Klik pada sel
- Pilih Konversi ke nomor dari drop-down peringatan
- Menambahkan nol ke nomor
- Pilih sel kosong mana pun
- Salin sel
- Pilih sel yang akan dikonversi menjadi angka
- Klik Beranda > Tempel > Tempel Spesial…
- Di jendela Tempel Spesial, pilih Tambah, lalu klik OK
#1 secara khusus berkaitan dengan angka yang diformat sebagai teks, tetapi #2 dan #3 dapat digunakan dalam banyak skenario di mana angka disimpan sebagai teks
Apostrof terkemuka (#3)
Apostrof ( ' ) adalah karakter khusus di Excel. Setiap kali apostrof dimasukkan di awal nilai atau rumus, ini memberi tahu Excel bahwa semua yang mengikutinya adalah teks
Karakter khusus ini memastikan kita dapat menyimpan angka sebagai teks. Misalnya, jika kami ingin menyimpan nomor karyawan yang berisi angka nol di depan, Excel mungkin menghapus angka nol tersebut pada entri data
Oleh karena itu, kami menyisipkan apostrof untuk memastikan Excel memahaminya sebagai teks
Namun, terkadang apostrof dapat muncul saat kita tidak menginginkannya. Ini sering terjadi ketika data diimpor dari sistem eksternal
Tangkapan layar di bawah menunjukkan perhitungan yang tidak berfungsi seperti yang diharapkan. Rumus di Sel B4 adalah SUM(B2. B3), jadi 1 + 1 = 2, padahal nilai B4 sama dengan 1. Ini karena sel B3 berisi nilai dengan apostrof yang mendahuluinya
Hapus apostrof secara manual atau gunakan teknik #2 dan #3 yang tercantum di bagian atas untuk memperbaiki masalah
Ada dampak serupa pada formula. Misalnya, pada tangkapan layar di bawah, fungsi SUM memiliki apostrof di depan;
Karena ini rumus, Anda harus menghapus apostrof secara manual. Maka Anda baik untuk pergi
Spasi awal dan akhir (#4)
Spasi awal dan akhir adalah masalah besar, sering terjadi pada teks yang diimpor, tetapi kita tidak dapat melihatnya dengan mata kita
Mari kita lihat sebuah contoh
Pada tangkapan layar di atas, rumus di Sel B13 adalah
=SUMIFS($B$2:$B$9,$A$2:$A$9,A13)Kami melakukan fungsi SUMIFS dasar untuk menambahkan nilai produk Alpha. Berdasarkan data, nilainya harus 50, tetapi dihitung sebagai nol
Masalahnya adalah semua nilai di Sel A2. A9 memiliki spasi tambahan. Alih-alih “Alfa”, yang digunakan di Sel A13 untuk fungsi SUMIFS, nilai dalam data adalah “Alfa “ (dengan spasi tambahan). Excel memandang ini sebagai nilai yang berbeda. Akibatnya, tidak ada kecocokan
Untuk memperbaikinya, ada banyak opsi. Beberapa saran adalah
- Menghapus ruang dari data secara manual
- Menambahkan spasi tambahan ke dalam kriteria SUMIFS kami
- Menggunakan fungsi TRIM untuk menghapus spasi tambahan dari awal/akhir teks
- Jika tidak ada spasi di tengah teks, kita dapat menggunakan Temukan dan Ganti untuk menghapus spasi ekstra
Angka yang terkandung dalam tanda kutip ganda (#5)
Masalah campur aduk teks/nomor lainnya terjadi saat angka disertakan dalam tanda kutip ganda
Lihat tangkapan layar di bawah ini. Ada masalah;
Dalam skenario ini, ada volume penjualan minimal 50; . Akibatnya, totalnya harus 250. Jadi, apa yang terjadi?
Masalahnya adalah karena perhitungan di Sel C3
Formulanya adalah
=IF(B3<50,"50",B3)_Di Sel B3, nilainya kurang dari 50; . Tanda kutip ganda memberi tahu Excel bahwa ini adalah teks dan bukan angka
Untuk memperbaikinya, hapus tanda kutip ganda di sekitar angka 50. Kemudian total yang benar akan dihitung
Argumen formula salah (#6)
Fungsi Excel adalah bahasa pemrograman untuk menghitung hasil. Setiap fungsi memiliki sintaksnya sendiri (mis. e. , argumen yang diperlukan untuk menghitung hasil). Jika sintaks ini salah, ini dapat menyebabkan hasil yang tidak terduga
VLOOKUP adalah fungsi yang rawan kesalahan;
Mari kita lihat sebuah contoh
Pada contoh di atas, rumus di Sel E3 adalah
=VLOOKUP(D3,A2:B4,2)Ini menghasilkan 40 untuk Charlie, yang benar
Sekarang mari kita ubah nilainya di D3 menjadi Bravo
Masih mengembalikan 40. Bravo harus 20. Bagaimana mungkin?
Dalam rumus kami, kami mengecualikan argumen ke-4 VLOOKUP, yaitu argumen Range_Lookup
- Jika kami memasukkan BENAR atau mengecualikan argumen ini, kami memberi tahu Excel itu
- kolom pertama dalam data kami dalam urutan menaik
- kami ingin mengembalikan nilai yang paling dekat, tetapi tidak lebih besar, dari nilai pencarian (dikenal sebagai pencocokan perkiraan)
- Jika kita memasukkan FALSE sebagai argumen keempat, Excel hanya mengembalikan hasil yang sama persis
Kolom pertama kami tidak dalam urutan menaik, tetapi dengan mengecualikan argumen ke-4, Excel yakin kolom tersebut diurutkan. Oleh karena itu, Excel menghitung hasil yang salah
Jika kita kembali dan mengubah rumus kita, maka argumen ke-4 adalah SALAH, itu berhasil
Ini menunjukkan pentingnya memahami apa yang dilakukan oleh semua argumen fungsi
Karakter yang tidak dicetak (#7)
Karakter non-cetak adalah huruf yang digunakan dalam kode komputer yang tidak dapat dilihat seseorang. Sebagai contoh sederhana, karakter ganti baris yang dapat kita masukkan ke dalam Excel menggunakan Alt + Enter tidak dapat dicetak
Pada tangkapan layar di atas, fungsi LEN menampilkan 6 karakter di Sel C2. Namun kata “Halo” hanya memiliki 5 karakter
Tidak ada spasi di Sel C2, jadi apa yang terjadi?
Kami dapat menghapus karakter yang tidak dicetak menggunakan fungsi CLEAN
Lihat tangkapan layar di atas. Setelah kami menggunakan fungsi CLEAN, nilainya sekarang benar lagi
Referensi melingkar (#8)
Referensi melingkar adalah tempat rumus merujuk ke sel dalam rantai perhitungannya sendiri. Ini sebuah contoh
Rumus di Sel B5 adalah
=SUM(B2:B5)Jika Anda perhatikan, referensi sel dari rumus (B5) disertakan dalam rentang sel (B2. B5). Akibatnya, Excel tidak dapat menghitung hasil (kecuali penghitungan berulang telah diaktifkan)
Saat kita membuat atau membuka buku kerja dengan referensi melingkar, Excel akan sering menampilkan pesan error
“Ada satu atau lebih referensi melingkar di mana rumus merujuk ke selnya sendiri baik secara langsung maupun tidak langsung. Hal ini dapat menyebabkan mereka salah menghitung
Coba hapus atau ubah referensi ini, atau pindahkan rumus ke sel lain. “
Namun, pesan kesalahan tidak menghentikan kami untuk mengklik OK dan melanjutkan seolah-olah tidak ada yang salah
Jika ada referensi melingkar, sulit untuk menemukannya secara manual. Untungnya, Excel telah memberi kami alat di dalam Rumus > Formula Auditing > Pemeriksaan Kesalahan > Referensi Melingkar yang menampilkan referensi melingkar di salah satu buku kerja terbuka kami
Seperti yang ditunjukkan pada tangkapan layar di atas, Sel B5 memiliki referensi melingkar. Setelah kami menghapusnya, itu akan dihitung dengan benar
Tampilkan rumus (#9)
Di Excel, kami biasanya melihat hasil perhitungan. Namun, dengan satu klik mouse atau satu pintasan, kita dapat beralih dengan cepat untuk menampilkan rumus daripada hasil
Mengaktifkan rumus acara dapat dengan mudah dilakukan secara tidak sengaja atau disimpan dalam keadaan ini oleh pengguna sebelumnya
Untuk menampilkan kembali hasil rumus, klik Formulas > Formula Auditing> Show Formulas, atau gunakan tombol shortcut Ctrl + ` (tombol di sebelah kiri tombol 1 pada keyboard windows standar)
Referensi sel salah (#10)
Satu hal yang kita pelajari di Excel sejak awal adalah penggunaan simbol dolar ($) untuk mengunci referensi sel. Sebagai contoh
- Jika kita memasukkan =A1 ke dalam sel dan menyalinnya, itu akan berubah menjadi =A2
- Jika kita memasukkan =A$1 dan menyalin sel ke bawah, itu akan tetap sebagai =A$1
Kami terbiasa dengan sintaks referensi ini dengan cepat. Namun, hal ini memudahkan kesalahan saat menyalin rumus ke sel lain
Lihat tangkapan layar di atas. Pasti ada masalah dengan perhitungan di Sel D6
Klik dua kali pada sel, dan masalahnya akan segera terungkap
Ya… ada yang lupa bahwa simbol $ pernah digunakan pada rumus di Sel C6, lalu mereka menyalin rumus tersebut ke Sel D6 hingga F6
Sayangnya, itu mudah dilakukan;
Bagaimana kita dapat menemukan jenis kesalahan ini? . Lebih baik meninjau sendiri dan menemukan kesalahan daripada duduk dalam rapat dengan manajer Anda, dan mereka menemukannya
Konversi entri data otomatis (#11)
Untuk membantu mengurangi jumlah kesalahan yang kita buat, Excel diprogram untuk mengoreksi/mengubah otomatis beberapa data saat kita memasukkannya. Ini bagus untuk sebagian besar waktu, dan siapa yang tahu seberapa sering itu menyelamatkan kita dari kesalahan yang memalukan
tanggal
Namun, koreksi otomatis dapat menyebabkan masalah di lain waktu. Misalnya, apa yang kita inginkan jika kita memasukkan 1/1 ke dalam Excel?
Tanggal adalah jenis konversi otomatis yang paling umum. Tanggal di Excel sebenarnya adalah angka, berdasarkan jumlah hari sejak 31 Desember 1899. Jadi, dengan asumsi tahun ini adalah 2022, jika kita memasukkan 1/1, Excel mengira kita ingin 1 Januari 2022 (yaitu angka 44562). Kita bisa melihat ini ketika kita mengubah tanggal ke format angka
Kami mencoba memasukkan nilai 1/1 dan berakhir dengan nilai 44562. Hmmm… itu bisa menyebabkan masalah bagi kita
Leading Zero (#12)
Kita telah melihat di atas pada #3 bahwa angka nol di depan bisa menjadi masalah
Jika kami mencoba memasukkan angka dengan nol di depan, seperti 0005321, Excel menganggap kami tidak menginginkan nol di depan dan mengonversi angka menjadi 5321
Untuk mempertahankan angka nol di depan, kita juga harus melakukannya
- Tambahkan apostrof di awal
- Ubah tipe data menjadi teks sebelum memasukkan nilai
Baris dan kolom tersembunyi (#13)
Ini adalah salah satu kencing hewan peliharaan saya. Baris dan kolom tersembunyi menyebabkan begitu banyak masalah bagi pengguna Excel. Menurut pendapat saya, mereka harus dihindari dengan cara apa pun
Mari kita lihat sebuah contoh
Lihat tangkapan layar di atas. Rumus di Sel B5 adalah
=SUM(B2:B4)_Apakah hasil ini benar? . Tapi baris tersembunyi membuat hasilnya terlihat salah. Ini karena baris tersembunyi berisi nilai tambahan, seperti yang bisa kita lihat saat kita memperlihatkan baris 3
Jika kita menginginkan perhitungan yang mengecualikan sel tersembunyi, kita harus mempertimbangkan untuk menggunakan fungsi AGGREGATE atau SUBTOTAL
Konversi biner ke desimal (#14)
Komputer menyimpan angka menggunakan biner (campuran 1 dan 0). Namun kita belajar angka menggunakan sistem basis 10 desimal. Akibatnya, Excel harus mengonversi antara bilangan biner dan desimal, yang dapat menyebabkan beberapa perbedaan yang tidak terduga
Kita tahu sepertiga (1/3) tidak dapat dinyatakan sebagai desimal; . 333333… berulang hingga tak terhingga. Jadi kami cenderung menggunakan perkiraan dengan pengurangan jumlah digit
Dalam biner, ada juga bilangan yang memiliki bilangan berulang hingga tak terhingga. Sepersepuluh (1/10) dapat dengan mudah dinyatakan sebagai desimal. 0. 1. Tapi dalam biner, itu adalah 00011001100110011… berulang hingga tak terhingga
Excel menghitung hingga 15 digit signifikan. Oleh karena itu, perbedaan pembulatan kecil mungkin terjadi karena konversi dari biner ke desimal. Meskipun ini mungkin tidak signifikan, jika ini digunakan dalam pernyataan logika, ini dapat menyebabkan hasil alternatif
Lihat posting ini untuk detail lebih lanjut. Excel dapat menghitung hasil yang salah. PERINGATAN
Kesimpulan
Dalam posting ini kami telah memberi Anda alasan yang paling mungkin mengapa kami menemukan rumus Excel tidak menghitung seperti yang diharapkan. Saya percaya 14 teknik ini telah membantu memecahkan masalah Anda
Jika Anda menemukan masalah sederhana lainnya yang menyebabkan perhitungan salah, silakan tambahkan di komentar di bawah
Pos terkait
- Mengapa mode perhitungan Excel terus berubah?
- 7 cara untuk menghapus spasi tambahan di Excel
Tentang Penulis
Hai, saya Mark, dan saya menjalankan Excel Off The Grid
Orang tua saya memberi tahu saya bahwa pada usia 7 tahun saya menyatakan akan menjadi akuntan yang berkualitas. Saya adalah paranormal atau tidak memiliki imajinasi, karena itulah yang sebenarnya terjadi. Namun, baru pada usia 35 tahun perjalanan saya benar-benar dimulai
Pada 2015, saya memulai pekerjaan baru, di mana saya bekerja secara teratur setelah jam 10 malam. Akibatnya, saya jarang melihat anak-anak saya selama seminggu. Jadi, saya mulai mencari rahasia mengotomatisasi Excel. Saya menemukan bahwa dengan membuat sejumlah kecil alat sederhana, saya dapat menggabungkannya dengan berbagai cara untuk mengotomatiskan hampir semua tugas rutin saya. Ini berarti saya dapat bekerja lebih sedikit (dan saya mendapat kenaikan gaji. ). Hari ini, saya mengajarkan teknik-teknik ini kepada para profesional lain dalam program pelatihan kami sehingga mereka juga dapat menghabiskan lebih sedikit waktu di tempat kerja (dan lebih banyak waktu dengan anak-anak mereka dan melakukan hal-hal yang mereka sukai)
Apakah Anda memerlukan bantuan untuk menyesuaikan pos ini dengan kebutuhan Anda?
Saya menduga contoh dalam posting ini tidak sesuai dengan situasi Anda. Kita semua menggunakan Excel secara berbeda, jadi tidak mungkin menulis postingan yang memenuhi kebutuhan semua orang. Dengan meluangkan waktu untuk memahami teknik dan prinsip dalam posting ini (dan di tempat lain di situs ini), Anda harus dapat menyesuaikannya dengan kebutuhan Anda.