Cara mengesan ralat dalam formula kompleks dengan enjin penyahpepijatan pintar Excel

Bekerja dengan hamparan yang penuh dengan formula boleh menjadi sesuatu yang hebat… sehingga sesuatu yang tidak kena berlaku dan masalah misteri muncul. Ralat dalam Excel yang anda tidak tahu dari mana asalnya dan mengenali Ralat yang paling biasa dalam formula ExcelApabila formula menjadi lebih panjang dan kompleks, mengesan punca masalah dengan mata kasar menjadi hampir mustahil, dan banyak masa terbuang untuk memeriksa sel demi sel.

Dengan enjin penyahpepijatan pintar baharu dan alat semakan ralat, Excel menawarkan lebih banyak bantuan untuk mencari, memahami dan membetulkan ralat dalam formula kompleksWalau bagaimanapun, untuk memanfaatkannya sepenuhnya, adalah penting untuk menguasai beberapa peraturan asas untuk menulis formula, memahami maksud mesej ralat yang berbeza dan mengetahui cara mengautomasikan pembetulan pukal, terutamanya apabila data yang diimport daripada program lain dan ia datang dalam pelbagai format yang luar biasa.

Konsep asas untuk memastikan formula tidak gagal dari awal

Sebelum mendalami enjin pintar dan penyahpepijat lanjutan, adalah penting untuk memahami beberapa peraturan asas yang menghalang kebanyakan ralat. Excel agak ketat dengan sintaks, dan sebarang butiran kecil boleh menyebabkan masalah. formula ditafsirkan sebagai teks atau gagal sepenuhnya.

Perkara pertama dan paling penting ialah itu Setiap formula mesti bermula dengan tanda sama dengan (=)Jika tanda sama dengan diabaikan, Excel tidak akan memahami bahawa anda ingin melakukan pengiraan, tetapi sebaliknya anda sedang memasukkan teks atau tarikh. Contohnya, jika anda menaip SUM(A1:A10) tanpa tanda sama dengan, sel tersebut akan memaparkan SUM(A1:A10) secara literal dan tidak akan melakukan sebarang pengiraan. Perkara yang serupa berlaku dengan tarikh: jika anda menaip 11/2 dan format sel ialah Umum, Excel mungkin memaparkan 2-nov (2 November) dan bukannya membahagi 11 dengan 2, kerana ia mentafsirkan bahawa anda memberikannya tarikh dan bukan pengiraan. Ini berkaitan dengan Gangguan tarikh Excel yang pelik.

Satu lagi perkara penting ialah penggunaan kurungan yang betul. Setiap fungsi yang menggunakannya mesti mempunyai kurungan pembukaan dan penutup yang diletakkan dengan baikApabila bekerja dengan fungsi bersarangAdalah perkara biasa untuk meninggalkan satu kurungan tambahan atau satu kurungan yang hilang, yang menyebabkan ralat sintaks. Bayangkan formula seperti =IF(B5<0,"Not valid",B5*1,05). Jika anda secara tidak sengaja menulis =IF(B5<0,"Not valid",B5*1,05)) dengan kurungan penutup tambahan, formula tersebut akan berhenti berfungsi kerana bilangan kurungan tidak sepadan. Sentiasa menyemak bahawa terdapat bilangan kurungan pembuka dan penutup yang sama dan ia berada dalam kedudukan yang betul adalah penting untuk mengelakkan ralat yang sukar dicari.

Ia juga penting untuk mengendalikan julat dengan betul. Untuk merujuk kepada beberapa sel berturut-turut, anda mesti menggunakan kolon (:) antara sel pertama dan terakhirJulat tersebut ditulis, sebagai contoh, sebagai A1:A5. Jika anda menghilangkan titik bertindih dan berakhir dengan sesuatu seperti =SUM(A1 A5), Excel akan mentafsirkannya sebagai rujukan tidak sah antara sel yang tidak berkaitan dan akan mengembalikan ralat. #NULL!, yang muncul apabila terdapat persilangan yang tidak wujud atau rujukan yang tidak konsisten.

Akhir sekali, bilangan dan susunan argumen untuk setiap fungsi mesti dihormati. Sesetengah fungsi memerlukan hujah wajib dan dalam kedudukan tertentuSesetengah fungsi memerlukan berbilang argumen, manakala yang lain membenarkan parameter pilihan. Jika anda kehabisan argumen atau menambah lebih daripada yang diperlukan, Excel akan memberi amaran kepada anda dengan mesej ralat yang berkaitan dengan fungsi tersebut. Mengetahui sintaks tepat setiap fungsi (bilangan argumen, jenis data yang dijangkakan, dsb.) membantu meminimumkan masalah sebelum ia timbul.

Jenis argumen: nombor, teks dan had bersarang

Fungsi Excel bukan sahaja memerlukan bilangan argumen tertentu, tetapi ia juga dijangka akan diluluskan jenis data yang betul dalam setiap argumenFungsi yang direka untuk menambah nombor tidak sama dengan fungsi yang direka untuk memanipulasi teks; jika ia ditukar, hasilnya boleh menjadi tidak dijangka atau menghasilkan ralat.

Contohnya, fungsi seperti SUM, AVERAGE atau PRODUCT memerlukan argumen berangka. Jika anda memberikan nilai teks yang menjangkakan nombor, Excel mungkin mengembalikan 0, mengabaikan sel atau memaparkan ralat bergantung pada konteksnya. Sebaliknya, fungsi seperti REPLACE, CONCAT atau RIGHT direka bentuk untuk berfungsi dengan rentetan teks. Dalam fungsi ini, sekurang-kurangnya salah satu argumen mestilah nilai teks atau rujukan kepada sel yang mengandungi teks; jika anda memaksa nombor untuk dianggap sebagai teks tanpa pengendalian yang betul, fungsi tersebut mungkin tidak berfungsi seperti yang dijangkakan.

Tambahan pula, Excel meletakkan had yang ketara untuk menggabungkan fungsi antara satu sama lain. Anda tidak boleh sarang lebih daripada 64 tahap fungsi dalam satu formulaIni bermakna jika anda mempunyai formula dengan pernyataan IF di dalam pernyataan IF di dalam pernyataan IF, dan sebagainya, terdapat satu titik di mana Excel tidak akan menerima sebarang tahap lagi. Walaupun dalam praktiknya hanya sedikit orang yang mencapai tahap ekstrem ini, dalam model yang sangat kompleks atau direka bentuk dengan buruk, had ini boleh dilampaui, menyebabkan ralat yang sukar ditafsirkan. Melebihi tahap kerumitan tertentu, biasanya lebih bijak untuk membahagikan logik kepada beberapa sel tambahan daripada cuba menumpukan semuanya ke dalam satu formula yang sangat panjang; untuk ini, adalah dinasihatkan untuk menyemak semula Punca dan penyelesaian untuk masalah prestasi dalam Excel.

Satu contoh tipikal ralat disebabkan oleh jenis dan nombor argumen ialah fungsi ABS. Fungsi ini hanya menerima satu argumen berangka dan mengembalikan nilai mutlaknya. Jika anda menaip sesuatu seperti =ABS(-2;134), Excel akan memaparkan ralat kerana fungsi tersebut tidak tahu apa yang perlu dilakukan dengan dua argumen yang dipisahkan oleh titik koma. Cara yang betul adalah seperti =ABS(-2134) atau =ABS(A1) jika sel A1 mengandungi nombor negatif yang anda ingin tukar kepada positif.

Sebaliknya, penggunaan pemisah mesti diambil kira bergantung pada tetapan serantau. Dalam banyak pemasangan Excel dalam bahasa Sepanyol, perkara berikut digunakan: titik koma (;) sebagai pemisah argumen dan koma (,) untuk bahagian perpuluhan. Jika formula tersilap mencampurkan koma dan titik koma pada kedudukan yang tidak sesuai, ia boleh menghasilkan ralat sintaks tambahan yang merumitkan kebolehbacaan dan penyahpepijatan formula.

Secara ringkasnya, memilih jenis data yang betul untuk dihantar kepada setiap fungsi, menghormati bilangan argumen yang ditakrifkan dalam sintaksnya, dan tidak melebihi had sarang adalah tiga tonggak asas untuk mengurangkan berlakunya ralat dalam formula yang canggih.

Rujukan kepada halaman dan buku lain: cara mengelakkan ralat pautan

Apabila formula mula memautkan ke helaian lain dalam buku kerja yang sama atau ke buku kerja luaran, agak mudah untuk membuat ralat menaip kecil yang akhirnya menghasilkan mesej rujukan yang tidak sah. Excel memerlukan anda Nama helaian, buku dan laluan ditulis dengan cara yang sangat tepat untuk mencari data dengan betul.

Jika dalam formula anda merujuk kepada helaian yang namanya mengandungi ruang atau aksara bukan huruf (nombor, tanda hubung, simbol…), nama itu mesti sentiasa disertakan. dalam tanda petikan tunggalContohnya, jika anda mempunyai helaian yang dipanggil Data Suku Tahunan, rujukan yang betul kepada sel D3 pada helaian tersebut ialah ='Data Suku Tahunan'!D3. Begitu juga, jika helaian itu dipanggil 123, anda akan menulis ='123'!A1 supaya Excel memahami nama helaian tersebut dan tidak mengelirukannya dengan nombor rawak.

Di samping itu, apabila formula menunjuk ke helaian lain, tanda hubung mesti diletakkan sebaik sahaja selepas namanya. tanda seru (!)Tanda seru (```) menunjukkan peralihan antara pengecam helaian dan rujukan sel tertentu. Contoh lengkapnya ialah: ='Data Suku Tahunan'!D3. Jika tanda seru diabaikan atau diletakkan secara salah, formula akan menjadi tidak sah dan ralat rujukan akan berlaku.

Apabila maklumat berada dalam buku kerja lain, Excel memerlukan lebih banyak konteks. Dalam kes ini, rujukan luaran mesti merangkumi nama fail dalam kurungan, nama helaian dan julatnyaContohnya, jika anda ingin mengira bilangan baris dalam julat A1:A8 buku kerja Operasi T2.xlsx, pada helaian Jualan, anda boleh menggunakan formula seperti =ROWS('Sales'!A1:A8). Jika fail tidak dibuka, anda juga perlu memberikan laluan fail penuh, seperti 'C:\My Documents\Sales'!A1:A8, dalam formula tersebut.

Formula tipikal dalam konteks ini adalah seperti =ROWS('C:\My Documents\Sales'!A1:A8). Arahan ini akan mengembalikan bilangan baris dalam julat A1:A8 dalam buku kerja yang lain itu, yang dalam kes ini ialah 8. Jika sebarang kesilapan dibuat semasa menaip laluan, nama buku kerja, nama helaian atau kurungan siku, hasilnya akan menjadi ralat. ralat rujukan atau nilai yang tidak dikemas kini kerana Excel tidak dapat mencari sumber data luaran.

Anda juga dinasihatkan untuk memantau apa yang berlaku apabila fail yang mengandungi data yang dipautkan dipindahkan, dinamakan semula atau dipadam. Enjin semak ralat mungkin mengesan beberapa masalah, tetapi jika fail luaran tidak lagi wujud di lokasi yang dijangkakan, anda perlu mengemas kini pautan secara manual atau mentakrifkan semula laluan untuk memulihkan fungsi formula.

Pemformatan nombor dalam formula: ralat disebabkan oleh simbol dan pemisah

Salah satu masalah paling biasa dalam hamparan dengan banyak data, terutamanya apabila ia datang daripada aplikasi lain, ialah penggunaan Format nombor yang salah dalam formulaExcel membezakan dengan jelas antara nilai sebenar yang disimpan dalam sel dan pemformatan yang digunakan untuk memaparkan nilai tersebut. Mengelirukan kedua-dua aras ini sering menyebabkan ralat yang halus.

Nombor tidak boleh diformatkan dalam formula. Iaitu, jika nilai ialah 1000 euro, ia mesti muncul dalam formula sebagai 1000, tanpa simbol €, pemisah ribu atau koma. Jika anda menulis 1.000 atau 1,000 (bergantung pada tetapan serantau), Excel mungkin akan mentafsirkannya sebagai pemisah argumen atau nilai yang berbeza daripada yang anda inginkan. Nombor diformatkan dengan mata wang, pemisah ribu atau pemisah perpuluhan. selepasMenggunakan pilihan pemformatan sel, bukan dalam ungkapan; untuk butiran lanjut tentang cara menggunakan format dengan betul, lihat format data dalam Excel 365.

Bayangkan anda ingin menambah 3100 pada kandungan sel A3. Jika anda secara naluri menaip =SUM(3.100,A3) dengan berfikir anda bermaksud 3100, Excel akan mentafsirkannya sebagai menambah 3 dan 100 terlebih dahulu, dan kemudian menambah nilai A3 pada hasilnya. Dalam erti kata lain, ia akan mengira (3 + 100) + A3, yang tidak sama dengan A3 + 3100. Formula yang betul ialah =SUM(3100,A3), tanpa cuba mewakili format pemisah ribuan.

Sesuatu yang serupa berlaku dengan fungsi yang hanya menerima bilangan argumen tertentu, seperti ABS, yang hanya menerima nilai angka. Jika anda cuba menulis koma atau titik koma dalam nombor tersebut untuk mensimulasikan ribuan, Excel akan menganggapnya sebagai beberapa argumen berasingan dan fungsi tersebut tidak akan berfungsi. akan mengembalikan ralat sintaksOleh itu, ungkapan seperti =ABS(-2;134) tidak berfungsi, manakala =ABS(-2134) adalah sah.

Ini amat penting untuk diingat semasa mengimport data daripada sistem perakaunan, ERP atau program invois. Kebanyakan eksport ini mengandungi pemformatan, simbol mata wang, ruang atau perkataan "EUR" di hujungnya. Semua ini mengubah apa yang sepatutnya menjadi nilai berangka kepada rentetan teks yang tidak boleh digunakan secara langsung oleh Excel dalam pengiraannya, yang membawa kepada ralat jenis, hasil kosong atau jumlah yang tidak terjumlah.

Amalan terbaik adalah menyimpan data yang disimpan sebagai nombor mentah dalam sel tanpa pemformatan khas Kemudian, gunakan pemformatan visual untuk pemisah mata wang, peratusan atau ribuan daripada menu pemformatan. Ini menghalang simbol daripada mengganggu pengiraan dan membolehkan enjin ralat mengesan ketidakkonsistenan dengan lebih baik.

Pemeriksaan ralat dan penyahpepijatan pintar dalam Excel

Selain membetulkan sintaks, Excel turut merangkumi sistem pemeriksaan ralat yang menganalisis formula dan mencadangkan pembetulan Apabila ia mengesan sesuatu yang luar biasa, enjin yang semakin pintar ini membantu mencari rujukan yang hilang, ketidakkonsistenan dan masalah taip dalam formula kompleks, sekali gus mengurangkan masa yang diperlukan untuk mencari punca ralat.

Dalam versi desktop Excel, semakan ralat boleh diakses daripada tab Formula, dalam kumpulan Pengeditan Formula, di mana pilihan Semakan Ralat dipaparkan. Wizard ini mengimbas sel dengan amaran dan memaparkan mesej dengan kemungkinan punca masalah, serta mencadangkan perubahan khusus yang boleh digunakan dengan satu klik. Ia berguna untuk mengesan julat yang tidak konsisten, formula yang belum dikemas kini apabila diseret, rujukan kepada sel kosong atau ketidakkonsistenan dalam jumlah.

Walau bagaimanapun, dalam Excel Online (versi web), pada masa ini tidak mungkin untuk mengkonfigurasi atau menggunakan peraturan semakan ralat lanjutan ini dengan cara yang sama. Perkhidmatan awan menawarkan fungsi asas, tetapi tidak termasuk set lengkap peraturan semakan formula Tersedia pada desktop. Oleh itu, apabila bekerja dengan buku kerja yang sangat kompleks, adalah paling praktikal untuk membukanya dengan aplikasi desktop untuk memanfaatkan sepenuhnya enjin penyahpepijatan.

Jika anda mempunyai versi desktop, anda boleh menggunakan butang "Buka dengan Excel" dalam Excel Online untuk melancarkan buku kerja dalam aplikasi penuh. Sebaik sahaja di sana, anda boleh melakukan semua pemeriksaan yang diperlukan untuk mengesan potensi ralat formula, mengaktifkan peraturan tertentu dan menyemak sebarang amaran yang disediakan oleh sistem. Ia merupakan cara yang berkesan untuk menggabungkan kemudahan bekerja dalam talian dengan kuasa aplikasi desktop.

Untuk sentiasa mengikuti perkembangan keupayaan enjin pengesahan baharu dan penambahbaikan pada alat penyahpepijatan pintar, adalah dinasihatkan untuk menyemak dokumentasi dari semasa ke semasa. Blog Rasmi Microsoft ExcelDi sinilah berita, kemas kini dan perubahan pada versi desktop dan dalam talian diterbitkan, membantu anda mengetahui bila ciri pembetulan pepijat baharu tersedia.

Jika anda memerlukan akses yang lebih komprehensif kepada semua aplikasi Office (Word, Excel, PowerPoint, dll.) dan perkhidmatan yang berkaitan dengannya, anda sentiasa boleh mencuba atau membeli suit penuh melalui Office.com, memastikan anda mempunyai alat semakan ralat yang paling canggih untuk semua hamparan anda.

Ralat yang disebabkan oleh nombor yang disimpan sebagai teks dan cara membetulkannya secara pukal

Satu senario yang sangat biasa, terutamanya dalam persekitaran perakaunan atau kewangan, timbul apabila mengeksport lejar am terperinci (GL terperinci) daripada perisian pengurusan. Dalam banyak kes, aplikasi jenis ini Mereka mengeksport jumlah berangka sebagai teksHasilnya ialah, apabila fail dibuka dalam Excel, formula yang cuba menambah, menolak atau menganalisis medan tersebut tidak mengenali nilai sebagai nombor, dan operasi gagal atau menghasilkan hasil yang salah.

Pemeriksaan ralat terbina dalam Excel boleh mengesan masalah ini dan memaparkan segi tiga hijau klasik di sudut sel, menunjukkan bahawa "nombor disimpan sebagai teks." Mengklik ikon amaran memberi Excel pilihan untuk menukar teks kepada nombor. Kelemahannya ialah, secara lalai, jika anda menggunakan alat Pemeriksaan Ralat daripada tab Formula > Mengedit Formula > Pemeriksaan Ralat, pembetulan biasanya digunakan. sel demi selyang dalam GL dengan beribu-ribu baris boleh menjadi menjengkelkan.

Tambahan pula, jika keseluruhan dokumen dipilih, pemeriksaan ralat tidak hanya tertumpu pada lajur berangka, kerana lajur lain yang mengandungi tarikh, teks dan penerangan juga boleh mencetuskan amaran, seterusnya merumitkan proses pembetulan pukal. Persoalan logik dalam konteks ini ialah sama ada terdapat cara untuk memilih keseluruhan helaian dan meminta Excel membetulkan semua ralat ini dengan menukar teks kepada nombor sebenar dalam satu kejadian.

Terdapat beberapa strategi yang boleh digunakan secara langsung daripada desktop Excel untuk menyelesaikan situasi seperti ini. Salah satu strategi yang paling langsung melibatkan penggunaan gam khas dengan operasi matematikContohnya, anda boleh menaip nombor 1 ke dalam sel kosong, menyalin sel tersebut, memilih julat sel yang mengandungi nombor yang disimpan sebagai teks (contohnya, keseluruhan lajur jumlah), dan menggunakan Tampal Khas > Darab. Operasi ini menyebabkan Excel mentafsir setiap entri "teks" sebagai nombor, mendarabkannya dengan 1, dan menyimpan hasilnya sebagai nilai berangka sebenar, mengekalkan jumlah yang sama. Ia merupakan cara yang sangat cekap untuk menukar sejumlah besar data secara pukal.

Alternatif lain adalah dengan menggunakan fungsi VALUE dalam lajur tambahan. Jika, sebagai contoh, jumlah teks berada dalam lajur B, anda boleh mencipta formula seperti =VALUE(B2) dalam lajur C, salinnya, kemudian salin dan tampal hasilnya sebagai nilai ke dalam lajur asal, menggantikan teks. Teknik ini juga membolehkan anda mengesahkan secara visual bahawa semuanya sepadan sebelum memadam data asal.

Dalam sesetengah kes, amaran "nombor disimpan sebagai teks" membolehkan anda memilih julat yang lebih luas dan menggunakan penukaran kepada berbilang sel sekaligus daripada menu konteks segi tiga hijau kecil. Walau bagaimanapun, keberkesanannya bergantung pada cara data dijana dan sama ada Excel mengesan semua kes sebagai jenis ralat yang sama.

Apabila bekerja dengan jumlah data perakaunan yang besar, adalah dinasihatkan untuk menyemak tetapan serantau Excel dan format eksport program sumber. Melaraskan parameter ini untuk memastikan keserasian pemisah perpuluhan, simbol mata wang dan format tarikh dapat mengurangkan kejadian nombor dalam format teks dan ralat yang berleluasa, serta membolehkan formula berfungsi dengan betul dari awal.

Pendek kata, walaupun pemeriksaan ralat standard cenderung berfungsi baris demi baris, menggabungkan amarannya dengan teknik seperti fungsi tampal dan penukaran khas adalah mungkin. untuk membetulkan blok besar data yang salah format secara pukal dan memulihkan status berangka mereka tanpa perlu pergi sel demi sel.

Keseluruhan set cadangan mengenai tanda sama dengan, kurungan, julat, jenis argumen, pemformatan nombor, rujukan luaran dan alat semakan ralat ini berkongsi matlamat yang sama: untuk membolehkan enjin penyahpepijatan pintar baharu Excel berfungsi pada kecekapan puncak. Apabila formula dibina dengan baik dan data menggunakan jenis dan format yang betul, sistem amaran dan cadangan menjadi sekutu yang ampuh untuk mencari ketidakkonsistenan, rujukan yang rosak atau sel yang bermasalah dengan cepat, walaupun dalam lembaran kerja dengan formula dan data yang sangat kompleks yang diimport daripada pelbagai sumber.

Artikel berkaitan:

Ralat yang paling biasa dalam formula Excel dan cara membetulkannya

Ishak

Penulis yang bersemangat tentang dunia bait dan teknologi secara umum. Saya suka berkongsi pengetahuan saya melalui penulisan, dan itulah yang akan saya lakukan dalam blog ini, menunjukkan kepada anda semua perkara yang paling menarik tentang alat, perisian, perkakasan, trend teknologi dan banyak lagi. Matlamat saya adalah untuk membantu anda mengemudi dunia digital dengan cara yang mudah dan menghiburkan.