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 Show
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
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
#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
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
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 _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
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
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
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
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 tanggalNamun, 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
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 _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 KesimpulanDalam 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
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. |