Masalah dengan menghitung rumus di Microsoft Excel. Mode halaman lembar kerja di Excel. Informasi di komentar

Salah satu fitur Excel yang paling banyak diminta adalah bekerja dengan rumus. Berkat fungsi ini, program secara mandiri melakukan berbagai jenis perhitungan dalam tabel. Tetapi kadang-kadang terjadi bahwa pengguna memasukkan formula ke dalam sel, tetapi tidak memenuhi tujuan langsungnya - untuk menghitung hasilnya. Mari kita cari tahu apa yang mungkin terkait dengan ini, dan bagaimana memecahkan masalah ini.

Alasan masalah dengan perhitungan rumus di Excel bisa sangat berbeda. Mereka dapat disebabkan baik oleh pengaturan buku tertentu atau bahkan rentang sel yang terpisah, atau oleh berbagai kesalahan dalam sintaksis.

Metode 1: ubah format sel

Salah satu alasan paling umum mengapa Excel tidak menghitung sama sekali atau tidak menghitung rumus dengan benar adalah format sel yang tidak diatur dengan benar. Jika rentang memiliki format teks, maka perhitungan ekspresi di dalamnya tidak dilakukan sama sekali, yaitu ditampilkan sebagai teks biasa. Dalam kasus lain, jika format tidak sesuai dengan esensi data yang dihitung, hasil yang ditampilkan di sel mungkin salah ditampilkan. Mari cari tahu cara memperbaiki masalah ini.



Sekarang rumus akan dihitung dalam urutan standar dengan output dari hasil di sel yang ditentukan.


Metode 2: matikan mode "Tampilkan rumus"

Tapi mungkin alasan bahwa alih-alih hasil perhitungan yang Anda tampilkan adalah karena program telah mengaktifkan mode "Tampilkan rumus".




Metode 3: perbaiki kesalahan sintaks

Rumus juga dapat ditampilkan sebagai teks jika ada kesalahan dalam sintaksnya, misalnya, ada huruf yang hilang atau diubah. Jika Anda memasukkannya secara manual, dan tidak melalui Penyihir fungsi, maka ini sangat mungkin. Kesalahan yang sangat umum saat menampilkan ekspresi sebagai teks adalah adanya spasi sebelum tanda «=» .


Dalam kasus seperti itu, Anda perlu hati-hati meninjau sintaks rumus tersebut yang ditampilkan dengan tidak benar, dan membuat penyesuaian yang sesuai.

Metode 4: aktifkan perhitungan ulang rumus

Itu juga terjadi bahwa rumus tampaknya menampilkan nilai, tetapi ketika sel yang terkait dengannya berubah, itu tidak mengubah dirinya sendiri, yaitu, hasilnya tidak dihitung ulang. Ini berarti Anda telah salah mengonfigurasi parameter perhitungan dalam buku ini.




Semua ekspresi dalam buku kerja ini sekarang akan dihitung ulang secara otomatis setiap kali ada perubahan nilai terkait.

Metode 5: kesalahan dalam rumus

Jika program masih menghitung, tetapi hasilnya menunjukkan kesalahan, maka kemungkinan pengguna hanya melakukan kesalahan saat memasukkan ekspresi. Rumus yang salah adalah rumus yang saat menghitung nilai berikut muncul di sel:

  • #NOMOR!;
  • #NILAI !;
  • # KOSONG !;
  • # DIV / 0 !;
  • # T / A

Dalam hal ini, Anda perlu memeriksa apakah data dalam sel yang dirujuk oleh ekspresi ditulis dengan benar, apakah ada kesalahan sintaksis, atau apakah rumus itu sendiri berisi tindakan yang salah (misalnya, pembagian dengan 0).


Jika fungsinya kompleks, dengan sejumlah besar sel yang terhubung, lebih mudah untuk melacak perhitungan menggunakan alat khusus.




Seperti yang Anda lihat, alasan mengapa Excel tidak mempertimbangkan atau tidak menghitung rumus dengan benar bisa sangat berbeda. Jika, alih-alih menghitung, pengguna menampilkan fungsi itu sendiri, maka dalam kasus ini, kemungkinan besar, sel diformat untuk teks, atau mode tampilan ekspresi diaktifkan. Juga, kesalahan sintaks mungkin terjadi (misalnya, keberadaan spasi sebelum tanda «=» ). Jika, setelah mengubah data di sel yang ditautkan, hasilnya tidak diperbarui, maka Anda perlu melihat bagaimana pembaruan otomatis dikonfigurasi dalam parameter buku. Juga, cukup sering, alih-alih hasil yang benar, kesalahan ditampilkan di sel. Di sini Anda perlu melihat semua nilai yang dirujuk oleh fungsi tersebut. Jika kesalahan ditemukan, itu harus dihilangkan.

Pembaca Lifehacker sudah akrab dengan Denis Batyanov yang berbagi dengan kami. Hari ini Denis akan berbicara tentang cara menghindari masalah paling umum dengan Excel, yang sering kita buat sendiri.

Saya akan segera membuat reservasi bahwa materi artikel ditujukan untuk pengguna Excel pemula. Pengguna berpengalaman telah menari membara di penggaruk ini lebih dari sekali, jadi tugas saya adalah melindungi "penari" muda dan tidak berpengalaman dari ini.

Anda tidak memberikan judul pada kolom tabel

Banyak alat Excel, seperti pengurutan, pemfilteran, tabel cerdas, tabel pivot, menganggap bahwa data Anda berisi header kolom. Jika tidak, Anda tidak akan dapat menggunakannya sama sekali, atau tidak akan berfungsi dengan benar. Selalu pastikan tabel Anda berisi judul kolom.

Kolom dan baris kosong di dalam tabel Anda

Ini membingungkan untuk Excel. Setelah menemukan baris atau kolom kosong di dalam tabel Anda, ia mulai berpikir bahwa Anda memiliki 2 tabel, bukan satu. Anda harus terus-menerus memperbaikinya. Juga, jangan sembunyikan baris / kolom yang tidak Anda butuhkan di dalam tabel, lebih baik untuk menghapusnya.

Beberapa meja terletak di satu lembar

Jika ini bukan tabel kecil yang berisi nilai referensi, maka Anda tidak boleh melakukan ini.

Akan merepotkan bagi Anda untuk sepenuhnya bekerja dengan lebih dari satu meja per lembar. Misalnya, jika satu tabel berada di sebelah kiri dan yang kedua di sebelah kanan, maka pemfilteran pada satu tabel akan memengaruhi tabel lainnya. Jika tabel terletak satu di bawah yang lain, maka tidak mungkin untuk menggunakan penyematan area, dan salah satu tabel harus terus mencari dan melakukan manipulasi yang tidak perlu untuk berdiri di atasnya dengan kursor tabel. Apakah Anda membutuhkannya?

Data dari jenis yang sama secara artifisial diatur dalam kolom yang berbeda

Sangat sering, pengguna yang mengetahui Excel secara dangkal lebih menyukai format tabel ini:

Tampaknya kita dihadapkan pada format yang tidak berbahaya untuk mengumpulkan informasi tentang penjualan agen dan dendanya. Tata letak meja seperti itu dirasakan dengan baik oleh seseorang secara visual, karena kompak. Namun, percayalah, itu adalah mimpi buruk yang nyata untuk mencoba mengekstrak data dari tabel tersebut dan mendapatkan subtotal (informasi agregat).

Faktanya adalah format ini berisi 2 dimensi: sehingga Anda harus memutuskan garis, memilah cabang, grup, dan agen. Ketika Anda menemukan stok yang tepat, maka Anda harus mencari kolom yang diperlukan, karena ada banyak di sini. Dan "dua dimensi" ini sangat memperumit pekerjaan dengan tabel seperti itu untuk alat Excel standar - rumus dan tabel pivot.

Jika Anda membuat tabel pivot, Anda akan menemukan bahwa tidak ada cara mudah untuk mendapatkan data berdasarkan tahun atau kuartal, karena indikator dibagi menjadi beberapa bidang. Anda tidak memiliki satu bidang penjualan yang dapat dengan mudah dimanipulasi, tetapi ada 12 bidang terpisah. Anda harus membuat kolom kalkulasi terpisah dengan tangan untuk kuartal dan tahun, meskipun, baik semuanya dalam satu kolom, tabel pivot akan melakukannya untuk Anda.

Jika Anda ingin menggunakan rumus penjumlahan standar seperti SUMIF, SUMIFS, SUMPRODUCT, Anda juga akan menemukan bahwa rumus tersebut tidak akan bekerja secara efektif dengan tata letak tabel ini.

Distribusi informasi pada lembar buku yang berbeda "untuk kenyamanan"

Kesalahan umum lainnya adalah, memiliki semacam format tabel standar dan memerlukan analitik berdasarkan data ini, menyebarkannya ke lembar terpisah dari buku kerja Excel. Misalnya, mereka sering membuat lembar terpisah untuk setiap bulan atau tahun. Akibatnya, jumlah pekerjaan analisis data sebenarnya dikalikan dengan jumlah lembar yang dibuat. Jangan lakukan itu. Kumpulkan informasi pada SATU lembar.

Informasi di komentar

Seringkali pengguna menambahkan informasi penting mereka mungkin perlu dalam komentar sel. Perlu diingat bahwa apa yang ada di komentar, Anda hanya dapat melihat (jika Anda menemukannya). Menariknya keluar ke dalam sel itu sulit. Saya sarankan lebih baik memiliki kolom terpisah untuk komentar.

Berantakan dengan pemformatan

Jelas tidak akan menambah kebaikan ke meja Anda. Ini terlihat tidak menyenangkan bagi orang yang menggunakan spreadsheet Anda. Paling-paling, mereka tidak akan mementingkan ini, paling buruk - mereka akan berpikir bahwa Anda tidak terorganisir dan ceroboh dalam bisnis. Upayakan hal-hal berikut:

Menggabungkan sel

Gunakan penggabungan sel hanya jika Anda tidak dapat melakukannya tanpanya. Sel yang digabungkan membuatnya sangat sulit untuk memanipulasi rentang yang mereka masuki. Masalah terjadi saat memindahkan sel, memasukkan sel, dll.

Gabungkan teks dan angka dalam satu sel

Kesan menyakitkan dihasilkan oleh sel yang berisi angka, di belakang oleh konstanta teks "RUB." atau "USD" dimasukkan secara manual. Apalagi jika bukan berupa cetakan, melainkan meja biasa. Operasi aritmatika dengan sel seperti itu secara alami tidak mungkin.

Angka sebagai teks dalam sel

Hindari menyimpan data numerik dalam sel dalam format teks. Seiring waktu, beberapa sel dalam kolom seperti itu akan memiliki format teks, dan beberapa akan memiliki format biasa. Akan ada masalah dengan formula karena ini.

Jika meja Anda akan ditampilkan melalui proyektor LCD

Pilih kombinasi warna dan latar belakang yang paling kontras. Terlihat bagus di proyektor latar belakang gelap dan huruf ringan. Kesan paling mengerikan dibuat oleh merah di atas hitam dan sebaliknya. Kombinasi ini terlihat sangat kontras pada proyektor - hindari.

Lembar kerja mode halaman di Excel

Ini adalah mode yang sama di mana Excel menunjukkan bagaimana lembar akan diberi halaman saat dicetak. Batas halaman disorot dengan warna biru. Saya tidak merekomendasikan terus-menerus bekerja dalam mode ini, yang banyak dilakukan, karena driver printer terlibat dalam proses menampilkan data di layar, dan ini, tergantung pada banyak alasan (misalnya, printer jaringan tidak tersedia saat ini ), penuh dengan pembekuan dalam proses rendering dan penghitungan ulang formula. Bekerja seperti biasa.

Untuk informasi yang lebih berguna tentang Excel, kunjungi

Cukup sering data dari program pihak ketiga diunggah ke Excel untuk pemrosesan lebih lanjut, dan seringkali penggunaan lebih lanjut dari data ini dalam formula memberikan hasil yang tidak terduga: angka tidak dijumlahkan, tidak mungkin untuk menghitung jumlah hari di antara tanggal, dll.

Artikel ini membahas penyebab masalah tersebut dan cara yang berbeda eliminasi mereka

Alasan satu ... Nomor disimpan sebagai teks

Dalam hal ini, Anda dapat melihat bahwa angka atau tanggal ditekan ke tepi kiri sel (sebagai teks) dan, sebagai aturan, di sudut kiri atas sel ada penanda kesalahan (segitiga hijau) dan tag itu, ketika Anda mengarahkan mouse, menjelaskan bahwa nomor tersebut disimpan sebagai teks.

Tidak ada perubahan dalam format Numerik, Umum atau Tanggal tidak memperbaiki situasi, tetapi jika Anda mengklik di bilah rumus (atau tekan F2) lalu Enter, angka menjadi angka, dan tanggal menjadi tanggal. Dengan sejumlah besar angka seperti itu, opsinya, Anda lihat, tidak dapat diterima.

Ada beberapa cara untuk mengatasi masalah ini.

  • Menggunakan penanda dan tag kesalahan... Jika Anda melihat penanda kesalahan ( segitiga hijau) dan tag, lalu pilih sel, klik tag dan pilih opsi Ubah ke angka
  • Menggunakan operasi Temukan / Ganti... Misalkan ada angka dengan titik desimal dalam tabel, disimpan sebagai teks. Pilih rentang dengan angka - klik Ctrl + h(atau temukan di tab rumah atau di menu Sunting untuk versi sebelum 2007 perintah Mengganti) -- di lapangan Menemukan memperkenalkan , (koma) - di lapangan Digantikan oleh kami juga memperkenalkan , (koma) - Menggantikan semua... Jadi, dengan mengganti koma dengan koma, kami mensimulasikan pengeditan sel dengan cara yang sama F2 - Masuk


Operasi serupa dapat dilakukan dengan tanggal, dengan satu-satunya perbedaan bahwa Anda perlu mengubah titik ke titik.

Selain itu, program pihak ketiga dapat menurunkan angka dengan titik sebagai pemisah desimal, kemudian mengganti titik dengan koma akan membantu.

Penggantian serupa dapat dilakukan dengan rumus (lihat di bawah) menggunakan fungsi SUBSTITUTE ()

  • Menggunakan Tempel Spesial... Metode ini lebih fleksibel, karena bekerja dengan bilangan pecahan, bilangan bulat, dan tanggal. Pilih sel kosong apa pun - jalankan perintah Salinan- pilih rentang dengan nomor masalah - Tempel spesial -- Melipat-- oke... Jadi, kami menambahkan 0 ke angka (atau tanggal), yang tidak memengaruhi nilainya dengan cara apa pun, tetapi mengonversi ke format angka


Variasi dari teknik ini adalah mengalikan rentang dengan 1

  • Menggunakan alat Text by Columns... Teknik ini mudah digunakan jika Anda perlu mengubah satu kolom, karena jika ada beberapa kolom, maka langkah-langkahnya harus diulang untuk setiap kolom secara terpisah. Jadi, pilih kolom dengan angka atau tanggal yang disimpan sebagai teks, atur format sel Umum(untuk angka, Anda dapat mengatur, misalnya, numerik atau Keuangan). Selanjutnya, kita jalankan perintah Data-- Teks Kolom -- Siap


  • Menggunakan rumus... Jika tabel memungkinkan kolom tambahan, Anda bisa menggunakan rumus untuk mengonversi menjadi angka. Untuk mengonversi nilai teks menjadi angka, Anda dapat menggunakan minus ganda, penjumlahan dengan nol, perkalian dengan satu, fungsi VALUE (), SUBSTITUTE (). Anda dapat membaca lebih detail. Setelah transformasi, kolom yang dihasilkan dapat disalin dan ditempel sebagai nilai menggantikan data asli


  • Dengan menggunakan... Sebenarnya, salah satu metode yang terdaftar dapat dilakukan dengan makro. Jika Anda harus sering melakukan konversi semacam ini, maka masuk akal untuk menulis makro dan menjalankannya sesuai kebutuhan.

Berikut adalah dua contoh makro:

1) kalikan dengan 1

Sub conv () Dim c Sebagai Range Untuk Setiap c Dalam Pilihan If IsNumeric (c.Value) Kemudian c.Value = c.Value * 1 c.NumberFormat = "#, ## 0.00" End If Next End Sub

2) teks dengan kolom

Sub conv1() Selection.TextToColumns Selection.NumberFormat = "#, ## 0.00" End Sub

Alasan dua ... Nomor tersebut berisi karakter asing.

Paling sering, karakter asing ini adalah spasi. Mereka dapat ditempatkan baik di dalam angka sebagai pemisah angka, dan sebelum / sesudah angka. Dalam hal ini, tentu saja, nomor tersebut menjadi teks.

Menyimpan ruang ekstra juga dapat dilakukan dengan menggunakan operasi Temukan / Ganti... di lapangan Menemukan memasuki ruang, dan bidang Digantikan oleh biarkan kosong, lalu Menggantikan semua... Jika ada spasi biasa dalam angka, maka tindakan ini sudah cukup. Tetapi nomor tersebut mungkin berisi apa yang disebut spasi tanpa putus (simbol dengan kode 160). Ruang seperti itu harus disalin langsung dari sel, dan kemudian ditempelkan ke bidang Menemukan kotak dialog Temukan / Ganti... Atau bisa di lapangan Menemukan tekan pintasan keyboard Alt + 0160(angka diketik pada keypad numerik).

Spasi juga bisa dihilangkan dengan rumus. Pilihannya adalah:

Untuk ruang reguler: = - PENGGANTI (B4; ""; "")

Untuk ruang non-breaking:= - PENGGANTI (B4, SIMBOL (160), "")

Segera untuk itu dan ruang lainnya: = - PENGGANTI (PENGGANTI (B4; SIMBOL (160); ""); ""; "")

Terkadang, untuk mencapai hasil yang diinginkan, Anda harus menggabungkan metode yang tercantum. Misalnya, hapus spasi terlebih dahulu lalu ubah format sel



Direkomendasikan untuk dibaca