Cara mendeteksi kesalahan dalam rumus kompleks dengan mesin debugging cerdas Excel.

Bekerja dengan spreadsheet yang penuh dengan rumus bisa sangat menyenangkan… sampai terjadi sesuatu yang salah dan muncul masalah misterius. Kesalahan di Excel yang Anda tidak tahu asalnya. dan menyadari Kesalahan paling umum dalam rumus ExcelSeiring dengan semakin panjang dan kompleksnya rumus, mendeteksi sumber masalah secara visual menjadi hampir mustahil, dan banyak waktu terbuang untuk memeriksa sel demi sel.

Dengan mesin debugging cerdas dan alat pengecekan kesalahan yang baru, Excel menawarkan bantuan yang semakin banyak untuk... Menemukan, memahami, dan memperbaiki kesalahan dalam rumus-rumus kompleks.Namun, untuk mendapatkan hasil maksimal darinya, sangat penting untuk menguasai beberapa aturan dasar dalam menulis rumus, memahami arti berbagai pesan kesalahan, dan mengetahui cara mengotomatiskan koreksi massal, terutama ketika data yang diimpor dari program lain dan tersedia dalam berbagai format yang tidak biasa.

Konsep dasar untuk memastikan rumus tidak gagal sejak awal

Sebelum membahas mesin cerdas dan debugger tingkat lanjut, penting untuk memahami beberapa aturan mendasar yang mencegah sebagian besar kesalahan. Excel cukup ketat dengan sintaksis, dan detail kecil apa pun dapat menyebabkan masalah. Rumus tersebut diinterpretasikan sebagai teks atau gagal total..

Hal pertama dan terpenting adalah Setiap rumus harus diawali dengan tanda sama dengan (=).Jika tanda sama dengan dihilangkan, Excel tidak memahami bahwa Anda ingin melakukan perhitungan, melainkan memasukkan teks atau tanggal. Misalnya, jika Anda mengetik SUM(A1:A10) tanpa tanda sama dengan, sel tersebut akan menampilkan SUM(A1:A10) secara harfiah dan tidak akan melakukan perhitungan apa pun. Hal serupa terjadi dengan tanggal: jika Anda mengetik 11/2 dan format selnya Umum, Excel mungkin menampilkan 2-nov (2 November) alih-alih membagi 11 dengan 2, karena Excel menafsirkan bahwa Anda memberikan tanggal dan bukan perhitungan. Ini terkait dengan Kesalahan tanggal yang aneh di Excel.

Poin penting lainnya adalah penggunaan tanda kurung yang benar. Setiap fungsi yang menggunakannya harus memiliki tanda kurung buka dan tutup yang ditempatkan dengan baikSaat bekerja dengan fungsi bersarangSangat umum untuk meninggalkan satu tanda kurung tambahan atau satu tanda kurung yang hilang, yang menyebabkan kesalahan sintaks. Bayangkan rumus seperti =IF(B5<0,"Tidak valid",B5*1,05). Jika Anda secara tidak sengaja menulis =IF(B5<0,"Tidak valid",B5*1,05)) dengan tanda kurung tutup tambahan, rumus tersebut akan berhenti berfungsi karena jumlah tanda kurung tidak akan cocok. Selalu memeriksa bahwa jumlah tanda kurung buka dan tutup sama dan berada di posisi yang benar sangat penting untuk menghindari kesalahan yang sulit ditemukan.

Penting juga untuk menangani rentang dengan benar. Untuk merujuk ke beberapa sel yang berurutan, Anda harus menggunakan tanda titik dua (:) di antara sel pertama dan sel terakhirRentang tersebut ditulis, misalnya, sebagai A1:A5. Jika Anda menghilangkan titik dua dan menulis sesuatu seperti =SUM(A1 A5), Excel akan menginterpretasikan ini sebagai referensi yang tidak valid antara sel yang tidak terkait dan akan menampilkan kesalahan. #BATAL!, yaitu yang muncul ketika tidak ada persimpangan atau referensi yang tidak konsisten.

Terakhir, jumlah dan urutan argumen untuk setiap fungsi harus diperhatikan. Beberapa fungsi memerlukan argumen wajib dan dalam posisi tertentuBeberapa fungsi memerlukan banyak argumen, sementara yang lain memperbolehkan parameter opsional. Jika Anda kehabisan argumen, atau menambahkan lebih dari yang dibutuhkan, Excel akan memperingatkan Anda dengan pesan kesalahan yang terkait dengan fungsi tersebut. Mengetahui sintaks yang tepat dari setiap fungsi (jumlah argumen, tipe data yang diharapkan, dll.) membantu meminimalkan masalah sebelum muncul.

Tipe argumen: angka, teks, dan batasan penestingan.

Fungsi Excel tidak hanya memerlukan sejumlah argumen tertentu, tetapi juga mengharapkan untuk diberikan parameter yang sesuai. tipe data yang benar di setiap argumenFungsi yang dirancang untuk menjumlahkan angka tidak sama dengan fungsi yang dirancang untuk memanipulasi teks; jika keduanya ditukar, hasilnya bisa tidak terduga atau bahkan menghasilkan kesalahan.

Sebagai contoh, fungsi seperti SUM, AVERAGE, atau PRODUCT memerlukan argumen numerik. Jika Anda memberikan nilai teks di tempat yang seharusnya berupa angka, Excel mungkin akan mengembalikan 0, mengabaikan sel, atau menampilkan kesalahan tergantung pada konteksnya. Sebaliknya, fungsi seperti REPLACE, CONCAT, atau RIGHT dirancang untuk bekerja dengan string teks. Dalam fungsi-fungsi ini, setidaknya salah satu argumen harus berupa nilai teks atau referensi ke sel yang berisi teks; jika Anda memaksa angka untuk diperlakukan sebagai teks tanpa penanganan yang tepat, fungsi tersebut mungkin tidak berperilaku seperti yang diharapkan.

Selain itu, Excel memberikan batasan signifikan pada penggabungan fungsi satu sama lain. Anda tidak bisa Menyusun lebih dari 64 level fungsi dalam satu rumus.Ini berarti bahwa jika Anda memiliki rumus dengan pernyataan IF di dalam pernyataan IF di dalam pernyataan IF, dan seterusnya, ada titik di mana Excel tidak akan menerima level lebih lanjut. Meskipun dalam praktiknya hanya sedikit orang yang mencapai ekstrem ini, dalam model yang sangat kompleks atau dirancang dengan buruk, batas ini dapat dilampaui, menyebabkan kesalahan yang sulit diinterpretasikan. Di luar tingkat kompleksitas tertentu, biasanya lebih bijaksana untuk membagi logika ke dalam beberapa sel bantu daripada mencoba memusatkan semuanya ke dalam satu rumus yang sangat panjang; untuk ini, disarankan juga untuk meninjau Penyebab dan solusi untuk masalah kinerja di Excel.

Contoh umum kesalahan yang disebabkan oleh tipe dan jumlah argumen adalah fungsi ABS. Fungsi ini hanya menerima argumen numerik tunggal dan mengembalikan nilai absolutnya. Jika Anda mengetik sesuatu seperti =ABS(-2;134), Excel akan menampilkan kesalahan karena fungsi tersebut tidak tahu apa yang harus dilakukan dengan dua argumen yang dipisahkan oleh titik koma. Cara yang benar adalah seperti =ABS(-2134) atau =ABS(A1) jika sel A1 berisi angka negatif yang ingin Anda ubah menjadi positif.

Di sisi lain, penggunaan pemisah harus diperhatikan tergantung pada pengaturan regional. Di banyak instalasi Excel berbahasa Spanyol, berikut ini yang digunakan: titik koma (;) sebagai pemisah argumen dan koma (,) untuk bagian desimal. Jika sebuah rumus secara keliru mencampur koma dan titik koma pada posisi yang tidak tepat, hal itu dapat menghasilkan kesalahan sintaks tambahan yang mempersulit keterbacaan dan debugging rumus tersebut.

Singkatnya, memilih tipe data yang tepat untuk diteruskan ke setiap fungsi, memperhatikan jumlah argumen yang didefinisikan dalam sintaksnya, dan tidak melampaui batas penestingan adalah tiga pilar fundamental untuk mengurangi terjadinya kesalahan dalam rumus yang kompleks.

Referensi ke halaman dan buku lain: cara menghindari kesalahan tautan

Ketika rumus mulai terhubung ke lembar kerja lain dalam buku kerja yang sama atau bahkan ke buku kerja eksternal, sangat mudah untuk membuat kesalahan pengetikan kecil yang akhirnya menghasilkan pesan referensi tidak valid. Excel mengharuskan Anda untuk Nama-nama lembaran, buku, dan rute ditulis dengan sangat tepat. agar data dapat ditemukan dengan benar.

Jika dalam sebuah rumus Anda merujuk ke sebuah lembar kerja yang namanya mengandung spasi atau karakter non-huruf (angka, tanda hubung, simbol…), maka nama tersebut harus selalu disertakan. dalam tanda kutip tunggalSebagai contoh, jika Anda memiliki lembar kerja bernama Data Triwulanan, referensi yang benar untuk sel D3 pada lembar kerja tersebut adalah ='Data Triwulanan'!D3. Demikian pula, jika lembar kerja tersebut bernama 123, Anda akan menulis ='123'!A1 agar Excel memahami nama lembar kerja dan tidak salah mengira dengan angka acak.

Selain itu, setiap kali rumus mengarah ke lembar kerja lain, tanda hubung harus diletakkan tepat setelah namanya. tanda seru (!)Tanda seru (```) menunjukkan transisi antara pengidentifikasi lembar kerja dan referensi sel tertentu. Contoh lengkapnya adalah: ='Data Triwulanan'!D3. Jika tanda seru dihilangkan atau ditempatkan secara tidak benar, rumus akan menjadi tidak valid dan akan terjadi kesalahan referensi.

Ketika informasi tersebut berada di buku kerja lain, Excel membutuhkan konteks lebih lanjut. Dalam kasus ini, referensi eksternal harus mencakup... Nama file dalam tanda kurung, nama sheet, dan rentang.Sebagai contoh, jika Anda ingin menghitung jumlah baris dalam rentang A1:A8 pada buku kerja Operations T2.xlsx, di lembar Sales, Anda dapat menggunakan rumus seperti =ROWS('Sales'!A1:A8). Jika file tidak terbuka, Anda juga perlu memberikan jalur file lengkap, seperti 'C:\My Documents\Sales'!A1:A8, di dalam rumus tersebut.

Rumus umum dalam konteks ini adalah sesuatu seperti =ROWS('C:\My Documents\Sales'!A1:A8). Instruksi ini akan mengembalikan jumlah baris dalam rentang A1:A8 di buku kerja lain tersebut, yang dalam hal ini adalah 8. Jika terjadi kesalahan dalam mengetik jalur, nama buku kerja, nama lembar kerja, atau tanda kurung siku, hasilnya akan berupa kesalahan. kesalahan referensi atau nilai yang tidak diperbarui karena Excel tidak dapat menemukan sumber data eksternal.

Sebaiknya pantau juga apa yang terjadi ketika file yang berisi data tertaut dipindahkan, diganti namanya, atau dihapus. Mesin pemeriksa kesalahan mungkin mendeteksi beberapa masalah, tetapi jika file eksternal tidak lagi ada di lokasi yang diharapkan, Anda perlu memperbarui tautan secara manual atau mendefinisikan ulang jalur untuk mengembalikan fungsionalitas rumus.

Pemformatan angka dalam rumus: kesalahan yang disebabkan oleh simbol dan pemisah

Salah satu masalah paling umum dalam spreadsheet dengan banyak data, terutama ketika data tersebut berasal dari aplikasi lain, adalah penggunaan Format angka yang salah dalam rumusExcel membedakan dengan jelas antara nilai sebenarnya yang tersimpan dalam sel dan format yang digunakan untuk menampilkan nilai tersebut. Mencampuradukkan kedua hal ini seringkali menyebabkan kesalahan kecil.

Angka tidak boleh diformat dalam rumus. Artinya, jika suatu nilai adalah 1000 euro, nilai tersebut harus muncul dalam rumus sebagai 1000, tanpa simbol €, pemisah ribuan, atau koma. Jika Anda menulis 1.000 atau 1,000 (tergantung pada pengaturan regional), Excel kemungkinan akan menginterpretasikannya sebagai pemisah argumen atau nilai yang berbeda dari yang Anda maksudkan. Angka diformat dengan mata uang, pemisah ribuan, atau pemisah desimal. setelah ituGunakan opsi pemformatan sel, bukan di dalam ekspresi; untuk detail selengkapnya tentang cara menerapkan format dengan benar, lihat format data di Excel 365.

Bayangkan Anda ingin menambahkan 3100 ke isi sel A3. Jika Anda secara naluriah mengetik =SUM(3.100,A3) dengan berpikir Anda bermaksud 3100, Excel akan menginterpretasikan ini sebagai penjumlahan 3 dan 100 terlebih dahulu, lalu menambahkan nilai A3 ke hasilnya. Dengan kata lain, Excel akan menghitung (3 + 100) + A3, yang tidak sama dengan A3 + 3100. Rumus yang benar adalah =SUM(3100,A3), tanpa mencoba merepresentasikan format pemisah ribuan.

Hal serupa terjadi pada fungsi yang hanya menerima sejumlah argumen tertentu, seperti ABS, yang hanya menerima nilai numerik. Jika Anda mencoba menulis koma atau titik koma di dalam angka untuk mensimulasikan ribuan, Excel akan menganggapnya sebagai beberapa argumen terpisah, dan fungsi tersebut tidak akan berfungsi. akan mengembalikan kesalahan sintaksisOleh karena itu, ekspresi seperti =ABS(-2;134) tidak berfungsi, sedangkan =ABS(-2134) valid.

Hal ini sangat penting untuk diingat, terutama saat mengimpor data dari sistem akuntansi, ERP, atau program pembuatan faktur. Banyak dari program tersebut mengekspor jumlah dengan format, simbol mata uang, spasi, atau bahkan kata "EUR" di akhir. Semua ini mengubah nilai numerik menjadi string teks yang tidak dapat digunakan Excel secara langsung dalam perhitungannya, sehingga menyebabkan kesalahan pengetikan, hasil kosong, atau jumlah yang tidak sesuai.

Praktik terbaik adalah menyimpan data yang tersimpan sebagai angka mentah dalam sel tanpa format khusus Kemudian terapkan pemformatan visual untuk mata uang, persentase, atau pemisah ribuan dari menu pemformatan. Ini mencegah simbol mengganggu perhitungan dan memungkinkan mesin pendeteksi kesalahan untuk mendeteksi ketidakkonsistenan dengan lebih baik.

Pemeriksaan dan debugging kesalahan yang cerdas di Excel

Selain mengoreksi sintaks, Excel menyertakan sistem yang Pemeriksaan kesalahan yang menganalisis rumus dan menyarankan koreksi. Ketika mendeteksi sesuatu yang tidak biasa, mesin yang semakin cerdas ini membantu menemukan referensi yang hilang, ketidakkonsistenan, dan masalah tipe dalam rumus yang kompleks, sehingga mengurangi waktu yang dibutuhkan untuk menemukan sumber kesalahan.

Pada versi desktop Excel, pemeriksaan kesalahan dapat diakses dari tab Rumus, di dalam grup Pengeditan Rumus, tempat opsi Pemeriksaan Kesalahan muncul. Panduan ini memindai sel-sel dengan peringatan dan menampilkan pesan dengan kemungkinan penyebab masalah, serta menyarankan perubahan spesifik yang dapat diterapkan dengan sekali klik. Ini berguna untuk mendeteksi rentang yang tidak konsisten, rumus yang belum diperbarui saat diseret, referensi ke sel kosong, atau ketidakkonsistenan dalam total.

Namun, di Excel Online (versi web), saat ini tidak mungkin untuk mengkonfigurasi atau menggunakan aturan pemeriksaan kesalahan tingkat lanjut ini dengan cara yang sama. Layanan cloud menawarkan fungsi dasar, tetapi tidak mencakup aturan peninjauan rumus secara lengkap Tersedia di desktop. Oleh karena itu, saat mengerjakan buku kerja yang sangat kompleks, cara yang paling praktis adalah membukanya dengan aplikasi desktop untuk memanfaatkan sepenuhnya mesin debugging.

Jika Anda memiliki versi desktop, Anda dapat menggunakan tombol "Buka dengan Excel" di Excel Online untuk membuka buku kerja di aplikasi lengkap. Setelah itu, Anda dapat melakukan semua pemeriksaan yang diperlukan untuk mendeteksi potensi kesalahan rumus, mengaktifkan aturan tertentu, dan meninjau peringatan apa pun yang diberikan sistem. Ini adalah cara efektif untuk menggabungkan kemudahan bekerja secara online dengan kekuatan aplikasi desktop.

Untuk tetap mendapatkan informasi terbaru tentang kemampuan mesin verifikasi dan peningkatan pada alat debugging cerdas, disarankan untuk memeriksa dokumentasi dari waktu ke waktu. Blog Resmi Microsoft ExcelDi sinilah berita, pembaruan, dan perubahan pada versi desktop dan online dipublikasikan, membantu Anda mengetahui kapan fitur perbaikan bug baru tersedia.

Jika Anda memerlukan akses yang lebih komprehensif ke semua aplikasi Office (Word, Excel, PowerPoint, dll.) dan layanan terkaitnya, selalu ada opsi untuk mencoba atau membeli paket lengkap melalui Office.com, yang memastikan Anda memiliki alat pengecekan kesalahan tercanggih untuk semua spreadsheet Anda.

Kesalahan yang disebabkan oleh angka yang disimpan sebagai teks dan cara memperbaikinya secara massal.

Skenario yang sangat umum, terutama di lingkungan akuntansi atau keuangan, muncul ketika mengekspor buku besar terperinci (detailed GL) dari perangkat lunak manajemen. Dalam banyak kasus, jenis aplikasi ini Mereka mengekspor jumlah numerik tersebut sebagai teks.Akibatnya, ketika file dibuka di Excel, rumus yang mencoba menambah, mengurangi, atau menganalisis kolom-kolom tersebut tidak mengenali nilai-nilai tersebut sebagai angka, dan operasi tersebut gagal atau menghasilkan hasil yang salah.

Fitur pemeriksaan kesalahan bawaan Excel dapat mendeteksi masalah ini dan menampilkan segitiga hijau klasik di sudut sel, yang menunjukkan bahwa "angka tersebut disimpan sebagai teks." Mengklik ikon peringatan memberi Excel opsi untuk mengkonversi teks menjadi angka. Kekurangannya adalah, secara default, jika Anda menggunakan alat Pemeriksaan Kesalahan dari tab Rumus > Mengedit Rumus > Pemeriksaan Kesalahan, koreksi biasanya diterapkan. sel demi selyang mana dalam GL dengan ribuan baris bisa sangat menjengkelkan.

Selain itu, jika seluruh dokumen dipilih, pemeriksaan kesalahan tidak hanya berfokus pada kolom numerik, karena kolom lain yang berisi tanggal, teks, dan deskripsi juga dapat memicu peringatan, yang semakin memperumit proses koreksi massal. Pertanyaan logis dalam konteks ini adalah apakah ada cara untuk memilih seluruh lembar kerja dan meminta Excel untuk mengoreksi semua kesalahan ini dengan mengubah teks menjadi angka sebenarnya sekaligus.

Ada beberapa strategi yang dapat diterapkan langsung dari Excel desktop untuk menyelesaikan situasi seperti ini. Salah satu yang paling langsung melibatkan penggunaan lem khusus dengan operasi matematikaSebagai contoh, Anda dapat mengetikkan angka 1 ke dalam sel kosong, menyalin sel tersebut, memilih rentang sel yang berisi angka yang disimpan sebagai teks (misalnya, seluruh kolom jumlah), dan menggunakan Tempel Khusus > Kalikan. Operasi ini menyebabkan Excel menginterpretasikan setiap entri "teks" sebagai angka, mengalikannya dengan 1, dan menyimpan hasilnya sebagai nilai numerik nyata, dengan mempertahankan jumlah yang sama. Ini adalah cara yang sangat efisien untuk mengkonversi sejumlah besar data secara massal.

Alternatif lain adalah menggunakan fungsi VALUE di kolom bantu. Misalnya, jika jumlah teks berada di kolom B, Anda dapat membuat rumus seperti =VALUE(B2) di kolom C, menyalinnya ke bawah, lalu menyalin dan menempelkan hasilnya sebagai nilai ke kolom asli, mengganti teks. Teknik ini juga memungkinkan Anda untuk memverifikasi secara visual bahwa semuanya cocok sebelum menghapus data asli.

Dalam beberapa kasus, peringatan "angka tersimpan sebagai teks" memungkinkan Anda untuk memilih rentang yang lebih luas dan menerapkan konversi ke beberapa sel sekaligus dari menu konteks segitiga hijau kecil. Namun, efektivitasnya bergantung pada bagaimana data dihasilkan dan apakah Excel mendeteksi semua kasus sebagai jenis kesalahan yang sama.

Saat menangani data akuntansi dalam jumlah besar, disarankan juga untuk meninjau pengaturan regional Excel dan format ekspor program sumber. Menyesuaikan parameter ini untuk memastikan kompatibilitas pemisah desimal, simbol mata uang, dan format tanggal akan sangat mengurangi kemunculan angka dalam format teks dan kesalahan yang meluas, serta memungkinkan rumus berfungsi dengan benar sejak awal.

Singkatnya, meskipun pemeriksaan kesalahan standar cenderung bekerja baris demi baris, menggabungkan peringatannya dengan teknik seperti fungsi salin dan konversi khusus dimungkinkan. untuk memperbaiki sejumlah besar data yang salah format secara massal. dan mengembalikan statusnya sebagai angka tanpa harus memeriksa sel demi sel.

Seluruh rangkaian rekomendasi mengenai tanda sama dengan, tanda kurung, rentang, tipe argumen, pemformatan angka, referensi eksternal, dan alat pengecekan kesalahan ini memiliki tujuan yang sama: untuk memungkinkan mesin debugging cerdas Excel yang baru bekerja dengan efisiensi maksimal. Ketika rumus disusun dengan baik dan data menggunakan tipe dan format yang benar, sistem peringatan dan saran menjadi sekutu yang ampuh untuk dengan cepat menemukan ketidakkonsistenan, referensi yang rusak, atau sel yang bermasalah, bahkan di lembar kerja dengan rumus yang sangat kompleks dan data yang diimpor dari berbagai sumber.

Artikel terkait:

Kesalahan paling umum dalam rumus Excel dan cara memperbaikinya

Isaac

Penulis yang bersemangat tentang dunia byte dan teknologi secara umum. Saya suka berbagi ilmu melalui tulisan, dan itulah yang akan saya lakukan di blog ini, menunjukkan kepada Anda semua hal paling menarik tentang gadget, perangkat lunak, perangkat keras, tren teknologi, dan banyak lagi. Tujuan saya adalah membantu Anda menavigasi dunia digital dengan cara yang sederhana dan menghibur.