Cara Menambah Sifar Utama dalam Excel

Tutorial ini merangkumi kaedah berbeza untuk menambah atau mengekalkan sifar utama serta mengalih keluar sifar utama dalam Excel.

Setiap kali anda memasukkan atau mengimport nombor dengan satu atau lebih sifar pendahuluan, seperti 000652, Excel secara automatik mengalih keluar sifar tersebut dan hanya nombor itu sendiri yang ditunjukkan dalam sel (652). Ini kerana sifar pendahuluan tidak diperlukan untuk pengiraan dan tidak dikira.

Walau bagaimanapun, ada kalanya sifar pendahuluan itu diperlukan, seperti apabila anda memasukkan nombor ID, nombor telefon, nombor kad kredit, kod produk atau poskod, dsb. Mujurlah, Excel memberi kita beberapa cara untuk menambah atau mengekalkan sifar pendahuluan dalam sel. Dalam artikel ini, Kami akan menunjukkan kepada anda cara berbeza untuk menambah atau mengekalkan sifar pendahuluan dan mengalih keluar sifar pendahuluan.

Menambahkan Sifar Utama dalam Excel

Pada asasnya, terdapat 2 kaedah yang boleh anda gunakan untuk menambah sifar pendahuluan: satu, format nombor anda sebagai 'Teks'; dua, gunakan pemformatan tersuai untuk menambah sifar utama. Kaedah yang anda ingin gunakan mungkin bergantung pada apa yang anda mahu lakukan dengan nombor tersebut.

Anda mungkin ingin menambah sifar pendahuluan apabila anda memasukkan nombor ID unik, nombor akaun, nombor keselamatan sosial, atau kod pos, dsb. Tetapi, anda tidak akan menggunakan nombor ini untuk pengiraan atau dalam fungsi, jadi sebaiknya anda menukar nombor tersebut nombor kepada teks. Anda tidak akan menjumlahkan atau purata nombor telefon atau nombor akaun.

Terdapat beberapa cara anda boleh menambah atau meletakkan sifar sebelum nombor dengan memformatkannya sebagai teks:

  • Menukar format sel kepada Teks
  • Menambah apostrof (‘)
  • Menggunakan fungsi TEXT
  • Menggunakan Fungsi REPT/LEN
  • Gunakan operator CONCATENATE Function/Ampersand (&)
  • Menggunakan fungsi RIGHT

Menukar Format Sel kepada Teks

Ini adalah salah satu cara paling mudah untuk menambah sifar pendahuluan pada nombor anda. Jika anda hanya akan memasukkan nombor dan anda mahu terus mendahului sifar semasa anda menaip, maka ini adalah kaedah untuk anda. Dengan menukar format sel daripada Umum atau Nombor kepada Teks, anda boleh memaksa Excel untuk menganggap nombor anda sebagai nilai teks dan apa sahaja yang anda taip dalam sel akan kekal sama. Begini cara anda melakukannya:

Pilih sel yang anda ingin tambahkan sifar pendahuluan. Pergi ke tab 'Rumah', klik pada kotak lungsur 'Format' dalam kumpulan Nombor, dan pilih 'Teks' daripada pilihan format.

Kini apabila anda menaip nombor anda, Excel tidak akan memadam sebarang sifar pendahuluan daripadanya.

Anda mungkin melihat segitiga hijau kecil (Penunjuk ralat) di penjuru kiri sebelah atas sel dan apabila anda memilih sel itu, ia akan menunjukkan kepada anda tanda amaran yang menunjukkan bahawa anda telah menyimpan nombor itu sebagai teks.

Untuk mengalih keluar mesej ralat, pilih sel, klik pada tanda amaran, dan kemudian pilih 'Abaikan Ralat' daripada senarai.

Anda juga boleh menaip nombor telefon dengan ruang atau tanda sempang antara nombor, Excel secara automatik akan menganggap nombor ini sebagai teks.

Menggunakan Leading Apostrof ( ‘ )

Satu lagi cara untuk menambah sifar utama dalam Excel ialah menambah tanda kutip (‘) pada permulaan nombor. Ini akan memaksa Excel untuk memasukkan nombor sebagai teks.

Hanya taip apostrof sebelum sebarang nombor dan tekan 'Enter'. Excel akan membiarkan sifar utama tetap utuh, tetapi (‘) tidak akan kelihatan dalam lembaran kerja melainkan anda memilih sel.

Menggunakan Fungsi Teks

Kaedah di atas menambah sifar pada nombor semasa anda menaipnya, tetapi jika anda sudah mempunyai senarai nombor dan anda ingin meletakkan sifar pendahuluan di hadapannya, maka fungsi TEXT ialah kaedah yang sesuai untuk anda. Fungsi TEKS membolehkan anda menukar nombor kepada rentetan teks semasa menggunakan pemformatan tersuai.

Sintaks fungsi TEXT:

= TEXT( nilai, format_teks)

di mana,

  • nilai - Ia ialah nilai berangka yang anda perlukan untuk menukar kepada teks dan menggunakan pemformatan.
  • format_teks – ialah format yang anda ingin gunakan.

Dengan fungsi TEXT, anda boleh menentukan bilangan digit yang perlu ada pada nombor anda. Sebagai contoh, jika anda mahu nombor anda menjadi 8 digit panjang, kemudian taip 8 sifar dalam hujah kedua fungsi: "00000000". Jika anda mempunyai 6 digit nombor dalam sel, maka fungsi tersebut akan menambah 2 sifar pendahuluan secara manual dan jika anda mempunyai 2 digit nombor seperti 56, selebihnya akan menjadi sifar (00000056).

Contohnya, untuk menambah sifar pendahuluan dan menjadikan nombor 6 digit panjang, gunakan formula ini:

=TEXT(A2,"000000")

Memandangkan kita mempunyai 6 sifar dalam hujah kedua formula, fungsi menukar rentetan nombor kepada rentetan teks dan menambah 5 sifar di hadapan untuk menjadikan rentetan 6 digit panjang.

Nota: Sila ingat untuk menyertakan kod format dalam tanda petikan berganda dalam fungsi.

Kini anda boleh menggunakan formula yang sama ke seluruh sel dengan menyeret pemegang isian. Seperti yang anda lihat, fungsi menukar nombor menjadi teks dan menambah sifar pendahuluan kepada nombor supaya jumlah digit ialah 6.

Fungsi TEKS akan sentiasa mengembalikan nilai sebagai rentetan teks, bukan nombor, jadi anda tidak akan dapat menggunakannya dalam pengiraan aritmetik tetapi anda masih boleh menggunakannya dalam formula carian seperti VLOOKUP atau INDEX/MATCH untuk mengambil butiran sesuatu produk menggunakan ID Produk.

Menggunakan Fungsi CONCATENATE/Operator Ampersand (&)

Jika anda ingin menambah nombor tetap sifar pendahuluan sebelum semua nombor dalam lajur, anda boleh menggunakan fungsi CONCATENATE atau operator ampersand (&).

Sintaks fungsi CONCATENATE:

=CONCATENATE(teks1, [teks2], ...)

di mana,

teks1 – Bilangan sifar yang perlu disisipkan sebelum nombor.

teks2 – Nombor asal atau rujukan sel

Sintaks pengendali Ampersand:

=Nilai_1 & Nilai_2

di mana,

Nilai_1 ialah sifar pendahuluan untuk dimasukkan sebelum nombor dan Nilai_2 ialah nombor.

Sebagai contoh, untuk menambah hanya dua sifar sebelum nombor, gunakan salah satu daripada formula ini:

=CONCATENATE("00",A2)

Argumen pertama ialah dua sifar (“00”) kerana kita ingin meletakkan dua sifar sebelum nombor dalam A2 (iaitu hujah kedua).

Atau, 

="00"&A2

Di sini, hujah pertama ialah 2 sifar, diikuti oleh operator '&', dan hujah kedua ialah nombor.

Seperti yang anda boleh lihat, formula menambah hanya dua sifar pendahuluan kepada semua nombor dalam lajur tanpa mengira bilangan digit yang mengandungi nombor itu.

Kedua-dua formula ini bergabung dengan bilangan sifar tertentu sebelum nombor asal dan menyimpannya sebagai rentetan teks.

Menggunakan Fungsi REPT/LEN

Jika anda ingin menambah sifar pendahuluan kepada data angka atau abjad angka dan menukar rentetan kepada teks, kemudian gunakan fungsi REPT. Fungsi REPT digunakan untuk mengulang aksara beberapa kali tertentu. Fungsi ini juga boleh digunakan untuk memasukkan nombor tetap sifar pendahuluan sebelum nombor tersebut.

=REPT(teks, bilangan_kali)

Di mana 'teks' ialah aksara yang ingin kita ulangi (dalam kes kita '0') dan hujah 'number_times' ialah bilangan kali kita mahu mengulang aksara itu.

Sebagai contoh, untuk menjana lima sifar sebelum nombor, formula akan kelihatan seperti:

=REPT(0,5)&A2

Apa yang formula lakukan ialah mengulangi 5 sifar dan mencantumkan rentetan nombor dalam A2 dan mengembalikan hasilnya. Kemudian, formula digunakan pada sel B2:B6 menggunakan pemegang isian.

Formula di atas menambah nombor tetap sifar sebelum nombor, tetapi jumlah panjang nombor berbeza-beza bergantung pada nombor.

Jika anda ingin menambah sifar utama di mana sahaja diperlukan untuk mencipta rentetan panjang aksara tertentu (panjang tetap), anda boleh menggunakan fungsi REPT dan LEN bersama-sama.

Sintaks:

=REPT(teks, nombor_kali-LEN(teks))&sel

Contohnya, untuk menambah sifar awalan pada nilai dalam A2 dan membuat rentetan panjang 5 aksara, cuba formula ini:

=REPT(0,5-LEN(A2))&A2

Di sini, 'LEN(A2)' mendapat jumlah panjang rentetan/nombor dalam sel A2. '5' ialah panjang maksimum rentetan/nombor yang perlu ada pada sel. Dan bahagian 'REPT(0,5-LEN(A2))' menambah bilangan sifar dengan menolak panjang rentetan dalam A2 daripada bilangan maksimum sifar (5). Kemudian, nombor 0 dicantumkan sebelum nilai A2 untuk membuat rentetan panjang tetap.

Menggunakan Fungsi RIGHT

Satu lagi cara untuk melapik sifar pendahuluan sebelum rentetan dalam Excel ialah menggunakan fungsi RIGHT.

Fungsi RIGHT boleh menambah bilangan sifar pada permulaan nombor dan mengekstrak aksara N paling kanan daripada nilai.

Sintaks:

= KANAN (teks, num_chars)
  • teks ialah sel atau nilai yang anda ingin ekstrak aksara.
  • num_chars ialah bilangan aksara untuk diekstrak daripada teks. Jika hujah ini tidak diberikan, maka hanya aksara pertama akan diekstrak.

Untuk kaedah ini, kami menggabungkan bilangan maksimum sifar dengan rujukan sel yang mengandungi rentetan dalam hujah 'teks'.

Untuk mencipta nombor 6 digit berdasarkan rentetan nombor dalam A dengan sifar pendahuluan, cuba formula ini:

=RIGHT("0000000"&A2,6)

Argumen pertama (teks) formula menambah 7 sifar pada nilai dalam A2 (“0000000”&A2), dan kemudian mengembalikan 7 aksara paling kanan, yang menghasilkan beberapa sifar pendahuluan.

Menambah Sifar Utama menggunakan Pemformatan Nombor Tersuai

Jika anda menggunakan mana-mana kaedah di atas untuk meletakkan sifar pendahuluan sebelum nombor, anda akan sentiasa mendapat rentetan teks, bukan nombor. Dan mereka tidak akan banyak digunakan dalam pengiraan atau dalam formula berangka.

Cara terbaik untuk menambah sifar utama dalam Excel ialah menggunakan pemformatan nombor tersuai. Jika anda menambah sifar pendahuluan dengan menambahkan format nombor tersuai pada sel, ia tidak mengubah nilai sel tetapi hanya cara ia dipaparkan. Nilai masih kekal sebagai nombor, bukan teks.

Untuk menukar pemformatan nombor sel, ikuti langkah berikut:

Pilih sel atau julat sel yang anda mahu tunjukkan sifar pendahuluan. Kemudian, klik kanan di mana-mana dalam julat yang dipilih itu dan pilih pilihan 'Format Sel' daripada menu konteks. Atau tekan kekunci pintasan Ctrl + 1.

Dalam tetingkap Format Sel, pergi ke tab 'Nombor' dan pilih 'Custom' di bawah pilihan Kategori.

Masukkan bilangan sifar dalam kotak 'Jenis:' untuk menentukan jumlah bilangan digit yang anda mahu tunjukkan dalam sel. Sebagai contoh, jika anda mahu nombor itu menjadi 6 digit panjang, kemudian masukkan '000000' sebagai kod format tersuai. Kemudian, klik 'OK' untuk memohon.

Ini akan menunjukkan sifar pendahuluan sebelum nombor dan jika nombor itu kurang daripada 6 digit, ia berlapik sifar sebelum itu.

Nombor hanya akan kelihatan mempunyai sifar pendahuluan manakala nilai asas akan kekal tidak berubah. Jika anda memilih sel dengan pemformatan tersuai, ia akan menunjukkan kepada anda nombor asal dalam bar formula

Terdapat banyak ruang letak digital yang boleh anda gunakan dalam format nombor tersuai anda. Tetapi terdapat hanya dua ruang letak utama yang boleh anda gunakan untuk menambah sifar pendahuluan dalam nombor.

  • 0 – Ia ialah pemegang tempat digit yang memaparkan sifar tambahan. Ia memaparkan digit paksa 0-9 sama ada digit itu berkaitan dengan nilai atau tidak. Sebagai contoh, jika anda menaip 2.5 dengan kod format 000.00, ia akan memaparkan 002.50.
  • # – Ia ialah pemegang tempat digit yang memaparkan digit pilihan dan tidak termasuk sifar tambahan. Contohnya, jika anda menaip 123 dengan kod pemformatan 000#, ia akan memaparkan 0123.

Selain itu, sebarang tanda baca atau aksara lain yang anda sertakan dalam kod format akan dipaparkan sebagaimana adanya. Anda boleh menggunakan aksara seperti tanda sempang (-), koma (,), garis miring ke hadapan (/), dsb.

Sebagai contoh, anda juga boleh memformat nombor sebagai nombor telefon dengan menggunakan format tersuai.

Kod Format dalam kotak dialog Format Sel:

Keputusan:

Mari gunakan kod pemformatan ini dalam contoh berikut:

##0000

Seperti yang anda lihat '0' akan menambah sifar tambahan manakala '#' tidak menambah sifar yang tidak penting:

Anda juga boleh menggunakan kod format yang dipratentukan dalam bahagian 'Format khas' kotak dialog sel Format untuk kod pos, nombor telefon dan nombor keselamatan sosial.

Jadual berikut menunjukkan nombor dengan sifar pendahuluan di mana kod format 'Khas' berbeza digunakan pada lajur berbeza:

Mengalih keluar Sifar Utama dalam Excel

Kini, anda telah mempelajari cara menambah sifar pendahuluan dalam Excel, mari lihat cara mengalih keluar sifar pendahuluan daripada bilangan rentetan. Kadangkala, apabila anda mengimport data daripada sumber luaran, nombor mungkin mempunyai awalan sifar dan diformatkan sebagai teks. Dalam kes sedemikian, anda perlu mengalih keluar sifar pendahuluan dan menukarnya kembali kepada nombor, supaya anda boleh menggunakannya dalam formula.

Terdapat pelbagai cara anda boleh mengalih keluar sifar utama dalam Excel dan kami akan melihatnya satu demi satu.

Alih Keluar Sifar Utama dengan Menukar Pemformatan Sel

Jika sifar pendahuluan ditambahkan dengan pemformatan nombor tersuai, maka anda boleh mengalih keluarnya dengan mudah dengan menukar format sel. Anda boleh mengetahui sama ada sel anda diformat tersuai dengan melihat bar alamat (sifar akan kelihatan dalam sel bukan dalam bar alamat).

Untuk mengalih keluar sifar awalan, pilih sel dengan sifar di hadapan, klik pada kotak 'Format Nombor' dan pilih pilihan pemformatan 'Umum' atau 'Nombor'.

Kini, sifar utama telah hilang:

Padamkan Sifar Utama dengan Menukar Teks kepada Nombor

Jika sifar pendahuluan anda telah ditambahkan dengan menukar format sel atau dengan menambah apostrof sebelum nombor atau ditambah secara automatik semasa mengimport data, cara paling mudah untuk menukarnya kepada nombor adalah dengan menggunakan Pilihan Semakan Ralat. Begini cara anda melakukannya:

Anda boleh menggunakan kaedah ini jika nombor anda dijajar ke kiri dan sel anda mempunyai segitiga hijau kecil (Penunjuk ralat) di penjuru kiri sebelah atas sel. Ini bermakna nombor diformatkan sebagai teks.

Pilih sel tersebut dan anda akan melihat amaran kuning di bahagian atas sebelah kanan pemilihan. Kemudian, klik pilihan 'Tukar kepada Nombor' daripada menu lungsur.

Sifar anda akan dialih keluar dan nombor akan ditukar kembali kepada format nombor (Dijajarkan ke kanan).

Mengeluarkan Sifar Utama oleh Darab/ Bahagi dengan 1

Satu lagi cara mudah dan terbaik untuk mengalih keluar pendahuluan ialah dengan mendarab atau membahagi nombor dengan 1. Membahagi atau mendarab nilai tidak mengubah nilai, ia hanya menukar nilai semula kepada nombor dan mengalih keluar sifar pendahuluan.

Untuk melakukan ini, taipkan formula dalam contoh di bawah dalam sel dan tekan ENTER. Sifar di hadapan akan dialih keluar dan rentetan akan ditukar kembali kepada nombor.

Kemudian, gunakan formula ini pada sel lain menggunakan pemegang isian.

Anda boleh mencapai hasil yang sama dengan menggunakan Perintah 'Tampal Khas'. Ini caranya:

Taipkan nilai berangka '1' dalam sel (katakan dalam B2) dan salin nilai tersebut.

Seterusnya, pilih sel di mana anda ingin mengalih keluar sifar utama. Kemudian, klik kanan pada pilihan dan kemudian pilih pilihan 'Tampal Khas'.

Dalam kotak dialog Tampal Khas, di bawah Operasi, pilih sama ada pilihan 'Darab' atau 'Bahagi' dan klik 'OK'.

Itu sahaja, sifar pendahuluan anda akan dialih keluar meninggalkan rentetan sebagai nombor.

Alih keluar Sifar Utama dengan menggunakan Formula

Satu lagi cara mudah untuk memadam sifar awalan adalah dengan menggunakan fungsi VALUE. Kaedah ini mungkin berguna sama ada sifar utama anda telah ditambahkan dengan menggunakan formula atau apostrof lain atau dengan pemformatan tersuai.

=VALUE(A1)

Hujah formula boleh menjadi nilai atau rujukan sel yang mempunyai nilai. Formula mengalih keluar sifar pendahuluan dan menukar nilai daripada teks kepada nombor. Kemudian gunakan formula ke seluruh sel.

Kadangkala, anda mungkin ingin mengalih keluar sifar pendahuluan tetapi mahu mengekalkan nombor dalam format teks. Dalam kes sedemikian, anda perlu menggunakan fungsi TEXT() dan VALUE () bersama-sama seperti ini:

=TEXT(VALUE(A1),"#")

Fungsi VALUE menukar nilai dalam A1 kepada nombor. Tetapi hujah kedua, '#' menukar nilai kembali kepada format teks tanpa sebarang sifar tambahan. Akibatnya, anda akan mendapat nombor tanpa sebarang sifar pendahuluan tetapi masih dalam format teks (dijajar kiri).

Alih keluar Sifar Utama menggunakan Ciri Teks ke Lajur Excel

Satu lagi cara untuk mengalih keluar sifar utama ialah menggunakan ciri Teks ke Lajur Excel.

Pilih julat sel yang mempunyai nombor dengan sifar pendahuluan.

Seterusnya, pergi ke tab 'Data' dan klik pada butang 'Teks ke Lajur' dalam kumpulan Alat Data.

Wizard 'Tukar Teks kepada Lajur' akan muncul. Dalam langkah 1 daripada 3, pilih 'Terhad' dan klik 'Seterusnya'.

Dalam langkah 2 daripada 3, nyahtanda semua pembatas dan klik 'Seterusnya'.

Pada langkah terakhir, biarkan pilihan format data Lajur sebagai 'Umum' dan pilih destinasi (sel pertama julat) di mana anda mahu nombor anda tanpa sifar pendahuluan. Kemudian, klik 'Selesai'

Dan anda akan mendapat nombor dengan pendahuluan dikeluarkan dalam lajur berasingan seperti yang ditunjukkan di bawah.

Itu sahaja.

Kategori: Apl