Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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. 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
  2. Jika sel memiliki segitiga hijau
    • Klik pada sel
    • Pilih Konversi ke nomor dari drop-down peringatan
      Rumus tabel excel tidak menghitung
  3. 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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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;

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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?

Rumus tabel excel tidak menghitung

Masalahnya adalah karena perhitungan di Sel C3

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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 tabel excel tidak menghitung

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. “

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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)

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

Lihat tangkapan layar di atas. Pasti ada masalah dengan perhitungan di Sel D6

Klik dua kali pada sel, dan masalahnya akan segera terungkap

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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

Rumus tabel excel tidak menghitung

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


Rumus tabel excel tidak menghitung

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.