Menggunakan pengesahihan data pada sel

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Anda boleh menggunakan pengesahihan data untuk mengehadkan jenis data atau nilai yang oleh pengguna ke dalam sel.

Senarai Jabatan

Contohnya, dalam buku kerja yang belanjawan, anda boleh menyediakan julat sel untuk membenarkan hanya nombor akaun yang betul-betul tiga digit (huruf tiada). Apabila pengguna memilih sel, anda boleh menunjukkan mereka mesej input seperti ini:

Mesej input dipaparkan apabila pengguna memilih sel

Jika pengguna mengabaikan mesej ini dan jenis tidak sah data dalam sel, anda boleh juga menunjukkan mereka mesej ralat.

Contoh mesej input tidak sah

Untuk lebih banyak maklumat pengesahihan data dan senario, lihat lebih pada pengesahihan data.

Muat turun contoh kami

Anda boleh memuat turun contoh buku kerja dengan semua contoh pengesahihan data yang anda akan menemui dalam artikel ini. Anda boleh ikuti bersama-sama, atau mencipta senario pengesahihan data anda sendiri.

Contoh pengesahihan data Excel muat turun

Menambah pengesahihan data pada sel atau julat

Nota: Tiga langkah pertama dalam seksyen ini adalah untuk menambah sebarang jenis pengesahihan data. Langkah 4-8 adalah khusus untuk mencipta senarai juntai bawah.

  1. Pilih satu atau lebih sel untuk disahkan.

  2. Pada tab Data, dalam kumpulan Alat Data, klik Pengesahihan Data.

    Pengesahihan data terletak dalam tab Data, Kumpulan alat Data
  3. Pada tab seting , dalam kotak Benarkan , pilih senarai.

    Tab seting dalam kotak dialog Pengesahihan Data
  4. Dalam kotak sumber , taipkan nilai senarai anda, dipisahkan oleh koma. Contohnya:

    1. Untuk mengehadkan jawapan kepada dua pilihan ("Adakah anda mempunyai children?" contohnya), taipkan Ya, tiada.

    2. Untuk mengehadkan reputasi kualiti vendor kepada tiga penarafan, taipkan rendah, purata, tinggi.

      Nota: Langkah ini biasanya hanya disyorkan untuk senarai item yang tidak mungkin perlu berubah. Jika anda mempunyai senarai yang boleh berubah, atau jika anda perlu menambah atau mengalih keluar item mengikut masa, kemudian anda lebih mengikuti langkah amalan terbaik berikut.

      Amalan terbaik: anda juga boleh mencipta senarai entri dengan merujuk kepada julat sel di tempat lain dalam buku kerja. Cara yang paling berkesan adalah mencipta senarai anda, kemudian Formatkan sebagai satu Jadual Excel (dari tab rumah pilih gaya > Format sebagai Jadual > Pilih gaya jadual yang terbaik untuk anda). Seterusnya, pilih Jadual Data isi julat, yang merupakan sebahagian daripada jadual yang mempunyai hanya senarai anda, bukan Jadual pengepala (Jabatan dalam kes ini), dan berikannya nama yang bermakna dalam kotak Nama di atas lajur A.

      Masukkan nama yang bermakna bagi senarai dalam kotak Nama

    Sekarang, selain daripada menaip nilai senarai anda dalam kotak sumber pengesahihan data, anda menambah nama yang anda baru sahaja ditakrifkan didahului dengan tanda sama dengan (=).

    Mendahului nama Jadual dengan tanda =

    Perkara yang terbaik tentang menggunakan Jadual adalah bahawa apabila anda menambah atau mengalih keluar item daripada senarai, senarai pengesahihan data anda akan dikemas kini secara automatik.

    Nota: Anda digalakkan untuk meletakkan senarai anda pada lembaran kerja yang berasingan yang (tersembunyi jika perlu) agar tiada sesiapa boleh mengeditnya.

  5. Pastikan bahawa kotak semak Juntai ke bawah dalam sel dipilih. Jika tidak, anda tidak akan dapat melihat anak panah juntai bawah bersebelahan sel.

    Juntai bawah yang muncul di sebelah sel di dalam sel
  6. Untuk menentukan cara anda ingin mengendalikan nilai kosong (nol), pilih atau kosongkan kotak semak Abaikan kosong .

    Nota: Jika nilai yang dibenarkan anda adalah berdasarkan julat sel dengan nama yang ditentukan dan terdapat sel kosong di mana-mana dalam julat tersebut, memilih kotak semak Abaikan kosong membenarkan sebarang nilai untuk dimasukkan dalam sel yang disahkan. Perkara ini juga benar untuk sebarang sel yang dirujuk oleh formula pengesahihan: jika sel yang dirujuk adalah kosong, memilih kotak semak Abaikan kosong membenarkan sebarang nilai dimasukkan dalam sel yang disahkan.

  7. Uji pengesahihan data untuk memastikannya berfungsi dengan betul. Cuba masukkan data sah dan juga tidak sah dalam sel untuk memastikan sama ada seting anda berfungsi seperti yang anda inginkan dan mesej anda muncul apabila anda mengharapkannya.

Nota: 

  • Selepas anda mencipta senarai juntai bawah anda, pastikan ia berfungsi dengan cara yang anda inginkan. Contohnya, anda mungkin ingin menyemak untuk melihat sama ada dalam sel adalah cukup lebar untuk menunjukkan semua entri anda.

  • Jika senarai entri untuk senarai juntai bawah berada pada lembaran kerja lain dan anda ingin menghalang pengguna daripada melihatnya atau membuat perubahan, pertimbangkan untuk menyembunyikan dan melindungi lembaran kerja tersebut. Untuk maklumat lanjut mengenai cara melindungi lembaran kerja, lihat Mengunci sel untuk melindunginya.

Jadual berikut menyenaraikan jenis pengesahihan data yang lain dan menunjukkan anda cara untuk menambahnya pada lembaran kerja anda.

Untuk melakukan ini:

Ikuti langkah-langkah ini:

Mengehadkan entri data kepada nombor bulat dalam had.

  1. Ikuti langkah 1-3 dalam menambah pengesahihan data pada sel atau julat di atas.

  2. Daripada senarai Benarkan, pilih Nombor Bulat.

  3. Dalam kotak Data, pilih jenis pengehadan yang anda inginkan. Contohnya, untuk mengeset had atas dan bawah, pilih di antara.

  4. Masukkan nilai minimum, maksimum atau khusus untuk membenarkan.

    Kotak dialog kriteria pengesahihan

    Anda juga boleh memasukkan formula yang mengembalikan nilai nombor.

    Contohnya, katakan anda sedang mengesahkan data dalam sel F1. Untuk mengeset had pemotongan minimum kepada dua kali bilangan anak dalam sel, pilih lebih besar atau sama dengan dalam kotak Data dan masukkan formula, =2*F1, dalam kotak Minimum.

Mengehadkan entri data kepada nombor perpuluhan dalam had.

  1. Ikuti langkah 1-3 dalam menambah pengesahihan data pada sel atau julat di atas.

  2. Dalam kotak Benarkan, pilih Senarai.

  3. Dalam kotak Data, pilih jenis pengehadan yang anda inginkan. Contohnya, untuk mengeset had atas dan bawah, pilih di antara.

  4. Masukkan nilai minimum, maksimum atau khusus untuk membenarkan.

    Anda juga boleh memasukkan formula yang mengembalikan nilai nombor. Contohnya, untuk mengeset had maksimum untuk komisen dan bonus 6% gaji jurujual yang dalam sel E1, pilih kurang daripada atau sama dengan dalam kotak Data dan masukkan formula, = E1 * 6%, dalam kotak maksimum .

    Nota: Untuk membenarkan pengguna memasukkan peratusan, contohnya 20%, pilih Perpuluhan dalam kotak Benarkan, pilih jenis pengehadan yang anda inginkan dalam kotak Data, masukkan minimum, maksimum atau nilai tertentu sebagai perpuluhan, contohnya .2dan kemudian paparkan sel pengesahihan data sebagai peratusan dengan memilih sel dan mengklik Gaya Peratus Imej butang dalam kumpulan Nombor pada tab Rumah.

Mengehadkan entri data tarikh dalam julat tarikh.

  1. Ikuti langkah 1-3 dalam menambah pengesahihan data pada sel atau julat di atas.

  2. Dalam kotak Benarkan, pilih Tarikh.

  3. Dalam kotak Data, pilih jenis pengehadan yang anda inginkan. Contohnya, untuk membenarkan tarikh selepas hari tertentu, pilih lebih besar daripada.

  4. Masukkan mula, tamat atau tarikh tertentu untuk membenarkan.

    Anda juga boleh memasukkan formula yang mengembalikan tarikh. Contohnya, untuk mengesetkan rangka masa antara tarikh hari ini dan 3 hari dari tarikh hari ini, pilih antara dalam kotak Data , masukkan =TODAY() dalam kotak tarikh mula dan masukkan = TODAY () + 3 dalam kotak tarikh tamat .

    Seting kriteria pengesahihan untuk mengehadkan entri tarikh rangka masa tertentu

Mengehadkan entri data kepada masa dalam rangka masa.

  1. Ikuti langkah 1-3 dalam menambah pengesahihan data pada sel atau julat di atas.

  2. Dalam kotak Benarkan, pilih Masa.

  3. Dalam kotak Data, pilih jenis pengehadan yang anda inginkan. Contohnya, untuk membenarkan masa sebelum masa tertentu dalam hari, pilih kurang daripada.

  4. Masukkan masa mula, tamat atau masa khusus untuk dibenarkan. Jika anda ingin memasukkan masa khusus, gunakan format masa jj:mm.

    Contohnya, katakan anda mempunyai sel E2 yang disediakan dengan masa mula (8:00 AM), dan sel F2 dengan masa tamat (5:00 PM), dan anda ingin mengehadkan Mesyuarat masa antara masa tersebut kemudian pilih antara dalam kotak Data , masukkan = E2 dalam kotak masa mula dan kemudian masukkan = F2 dalam kotak masa tamat .

    Seting pengesahihan untuk mengehadkan entri masa dalam rangka masa

Mengehadkan entri data kepada teks dengan kepanjangan yang ditentukan.

  1. Ikuti langkah 1-3 dalam menambah pengesahihan data pada sel atau julat di atas.

  2. Dalam kotak Benarkan, pilih Kepanjangan Teks.

  3. Dalam kotak Data, pilih jenis pengehadan yang anda inginkan. Contohnya, untuk membenarkan sehingga bilangan aksara tertentu, pilih kurang daripada atau sama dengan.

  4. Dalam kes ini kami ingin mengehadkan entri untuk 25 aksara, jadi pilih kurang daripada atau sama dengan dalam kotak Data dan masukkan 25 dalam kotak maksimum .

    Contoh Pengesahihan data dengan kepanjangan teks terhad

Mengira nilai yang dibenarkan berdasarkan kandungan sel lain.

  1. Ikuti langkah 1-3 dalam menambah pengesahihan data pada sel atau julat di atas. Dalam kotak Benarkan , pilih jenis data yang anda inginkan.

  2. Dalam kotak Data, pilih jenis pengehadan yang anda inginkan.

  3. Dalam kotak di bawah kotak Data, klik sel yang anda ingin gunakan untuk menentukan benda yang dibenarkan.

    Contohnya, untuk membenarkan entri untuk akaun hanya jika hasil tidak pergi terlampau bajet dalam sel E1, pilih Benarkan >nombor bulat, Data, kurang daripada atau sama dengan, dan maksimum > = = E1.

    Seting pengesahihan untuk mengira berdasarkan kandungan sel lain

Nota: Contoh berikut menggunakan opsyen tersuai yang anda menulis formula untuk mengesetkan syarat anda. Anda tidak perlu bimbang tentang apa-apa sahaja kotak Data menunjukkan, seperti yang dinyahdayakan dengan opsyen tersuai.

Untuk memastikan bahawa

Masukkan formula ini

Sel yang mengandungi ID produk (C2) sentiasa bermula dengan awalan standard "ID-" dan ialah sekurang-kurangnya 10 (lebih besar daripada 9) aksara.

= DAN (kiri (C2, 3) ="ID-",LEN(C2) > 9)

Contoh 6: Formula dalam pengesahihan data

Sel yang mengandungi nama produk (D2) hanya mengandungi teks.

=ISTEXT(D2)

Contoh 2: Formula dalam pengesahihan data

Sel yang mengandungi seseorang hari lahir (B6) perlu lebih besar daripada bilangan tahun yang disetkan dalam sel B4.

= IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

Contoh pengesahihan data untuk mengehadkan entri usia minimum

Semua data dalam julat sel a2: A10 mengandungi nilai unik.

= COUNTIF($A$2:$A$10,A2) = 1

Contoh 4: Formula dalam pengesahihan data

Nota: Anda mesti masukkan formula pengesahihan data sel A2 terlebih dahulu, kemudian Salin A2 untuk A3:A10 agar argumen kedua COUNTIF yang akan sepadan dengan sel semasa. That is yang A2) = 1 bahagian akan mengubah kepada A3) = 1, A4) = 1 dan sebagainya.

Untuk maklumat lanjut

Memastikan bahawa entri alamat e-mel dalam sel B4 mengandungi yang simbol @.

=ISUMBER(FIND("@",B4)

Contoh pengesahihan data yang memastikan alamat e-mel mengandungi yang simbol @

  • Mengapa perintah Pengesahihan Data tidak didayakan pada reben? Perintah mungkin tidak tersedia kerana:

    • Jadual A Microsoft Excel mungkin dipautkan ke laman SharePoint Anda tidak boleh menambah pengesahihan data jadual Excel yang dipautkan ke laman SharePoint. Untuk menambah pengesahihan data, anda mesti Nyahpaut jadual Excel atau menukar jadual Excel kepada julat.

    • Anda mungkin sedang memasukkan data Perintah Pengesahihan Data tidak didayakan pada Data tab semasa anda memasukkan data dalam sel. Untuk selesai memasukkan data, tekan ENTER atau ESC.

    • Lembaran kerja mungkin dilindungi atau dikongsi Anda tidak boleh mengubah seting pengesahihan data jika buku kerja anda dikongsi atau dilindungi. Untuk mengetahui cara untuk menghentikan perkongsian atau melindungi buku kerja, lihat melindungi buku kerja.

  • Bolehkah saya mengubah saiz fon? Tidak, saiz fon tetap. Satu-satunya cara untuk mengubah saiz paparan adalah untuk melaraskan zum skrin anda di penjuru bawah kanan tetingkap Excel. Anda boleh menggunakan kotak kombo ActiveX Walau bagaimanapun. Lihat Tambah kotak senarai atau kotak kombo pada lembaran kerja.

  • Terdapat cara untuk menjadikan data pengesahihan auto-isian atau auto-Pilih saya semasa saya menaip? Tidak, tetapi jika anda menggunakan yang Kotak kombo ActiveX anda mempunyai kefungsian tersebut.

  • Bolehkah saya membuat berbilang pilihan dalam senarai pengesahihan data? Tidak melainkan anda menggunakan ActiveX kombo atau Kotak senarai.

  • Bolehkah saya memilih item dalam senarai pengesahihan data dan mempunyai mengisi senarai lain? Ya! Ini dipanggil Bersandaran Pengesahihan Data. Untuk maklumat lanjut, lihat Cipta Bersandaran juntai ke bawah senarai.

  • Bagaimana saya boleh mengalih keluar semua pengesahihan data pada lembaran kerja? Anda boleh menggunakan yang Pergi ke > dialog Khas . Pada tab rumah > pengeditan > Cari & Pilih (atau tekan F5 atau Ctrl + G pada papan kekunci), kemudian istimewa > pengesahihan Data dan pilih sama ada Semua (untuk mencari semua sel dengan pengesahihan data) atau sama (untuk mencari sel yang sepadan dengan seting pengesahihan data khusus). Untuk memilih semua sel dengan atribut pengesahihan data yang sama, anda perlu pertama pilih satu sel dengan seting pengesahihan data tersebut.

    Kotak dialog pergi ke istimewa
  • saya boleh memaksa seseorang untuk menjadikan entri dalam sel dengan pengesahihan data? Tidak, tetapi anda boleh menggunakan VBA (Visual Basic for Applications) untuk menyemak sama ada seseorang menulis entri dalam syarat tertentu, seperti sebelum ia Simpan dan tutup buku kerja. Jika ia masih belum membuat pilihan, anda boleh membatalkan peristiwa dan tidak membenarkan mereka teruskan sehingga pemilihan telah dibuat.

  • Bagaimana saya boleh warna sel berdasarkan pilihan senarai pengesahihan data? Anda boleh menggunakan Pemformatan bersyarat. Dalam kes ini, anda akan ingin menggunakan opsyen Format hanya sel yang mengandungi .

    Format hanya sel yang mengandungi opsyen
  • Bagaimanakah saya mengesahkan alamat e-mel? Anda boleh menggunakan tersuai > kaedah Formula dan semak jika yang simbol @ wujud dalam entri. Dalam kes ini, formula yang digunakan ialah =ISNUMBER(FIND("@",D2)). Fungsi Cari mencari yang simbol @, dan jika ditemui, ia mengembalikan kedudukan berangka dalam rentetan teks dan membenarkan entri. Jika ia tidak ditemui, kemudian Cari mengembalikan ralat dan menghalang entri.

Adakah anda mempunyai soalan fungsi tertentu?

Kiriman soalan dalam forum komuniti Excel

Membantu kami menambah baik Excel

Adakah anda mempunyai cadangan tentang cara kami boleh meningkatkan versi Excel yang seterusnya? Jika ya, sila lihat topik di Excel pengguna suara.

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Lihat Juga

Lebih pada pengesahihan data

Video: mencipta dan menguruskan senarai juntai bawah

Tambah atau alih keluar item daripada senarai juntai bawah

Mengalih keluar senarai juntai bawah

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×