Fungsi IF tersarang Formula dan mengelakkan perangkap

Fungsi IF tersarang Formula dan mengelakkan perangkap

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

Fungsi IF membolehkan anda membuat perbandingan logik antara nilai dan perkara yang anda jangkakan dengan menguji untuk syarat dan mengembalikan hasil jika True atau False.

  • = IF(Something is True, then do something, otherwise do something else)

Jadi penyata jika boleh mempunyai dua keputusan. Hasil pertama ialah jika perbandingan anda ialah True, kedua jika perbandingan anda adalah False.

Jika penyata adalah sangat kukuh dan borang asas banyak model hamparan, tetapi ia juga merupakan punca isu hamparan yang banyak. Secara idealnya, penyata jika patut menggunakan minimum syarat, seperti lelaki/perempuan, ya/tidak/mungkin, untuk nama beberapa, tetapi kadangkala anda mungkin perlu menilai senario yang lebih kompleks yang memerlukan penyarangan * fungsi IF lebih daripada 3 bersama-sama.

* "Menyarang" merujuk amalan menyertai pelbagai fungsi bersama-sama dalam satu formula.

Menggunakan fungsi IF, salah satu daripada fungsi logik, untuk mengembalikan satu nilai jika syarat adalah benar dan nilai yang lain jika syarat adalah palsu.

Sintaks

IF (logical_test, value_if_true, [value_if_false])

Contohnya:

  • =IF(A2>B2,"Melebihi Bajet","OK")

  • =IF(A2=B2,B4-A4,"")

Nama argumen

Perihalan

logical_test   

(diperlukan)

Syarat yang anda ingin uji.

value_if_true   

(diperlukan)

Nilai yang anda ingin dikembalikan jika hasil bagi logical_test ialah TRUE.

value_if_false   

(pilihan)

Nilai yang anda ingin dikembalikan jika hasil bagi logical_test ialah FALSE.

Catatan

Semasa Excel akan membenarkan anda menyarangkan sehingga 64 fungsi IF yang berlainan, ia adalah tidak digalakkan untuk berbuat demikian. Mengapa?

  • Berbilang jika penyata memerlukan banyak pemikiran untuk membina dengan betul dan pastikan logik mereka boleh dikira dengan betul melalui setiap syarat hingga hujung. Jika anda tidak menyarangkan anda formula 100% dengan tepat, maka ia mungkin berfungsi 75% masa, tetapi mengembalikan hasil yang tidak dijangka 25% masa. Malangnya, kemungkinan anda menangkap 25% adalah nipis.

  • Berbilang jika penyata boleh menjadi amat sukar untuk mengekalkan, terutamanya apabila anda kembali sedikit masa kemudian dan cuba mengenal pasti anda atau lebih teruk seseorang, cuba lakukan.

Jika anda mencari diri anda dengan kenyataan IF yang hanya kelihatan sentiasa berkembang dengan tiada akhir di sisi, Inilah masanya untuk meletakkan tetikus dan memikirkan semula strategi anda.

Mari kita lihat cara untuk mencipta kenyataan IF tersarang yang kompleks yang menggunakan berbilang IFs dengan betul, dan apabila dikenali Inilah masanya untuk menggunakan alat lain dalam gudang Excel anda.

Contoh

Berikut adalah contoh kenyataan IF tersarang agak standard untuk menukar skor ujian pelajar mereka gred huruf yang sepadan.

Kompleks tersarang kenyataan IF - Formula dalam E2 = IF (B2 > 97, "A +", jika (B2 > 93 "A", jika (B2 > 89, "A-", jika (B2 > 87, "B +", jika (B2 > 83, "B", jika (B2 > 79, "B", jika (B2 > 77, "C +", IF(B2>73 , "C", jika (B2 > 69, "C", jika (B2 > 57, "D +", jika (B2 > 53, "D", jika (B2 > 49, "D", "F")))
  • = IF (D2 > 89, "A", jika (D2 > 79, "B", jika (D2 > 69, "C", jika (D2 > 59, "D", "F")))

    Kenyataan IF tersarang ini kompleks mengikuti logik mudah:

  1. Jika skor ujian (dalam sel D2) adalah lebih besar daripada 89, kemudian pelajar mendapat A

  2. Jika skor ujian adalah lebih besar daripada 79, kemudian pelajar memperoleh a B

  3. Jika skor ujian adalah lebih besar daripada 69, kemudian pelajar mendapat C

  4. Jika skor ujian yang lebih besar daripada 59, kemudian pelajar mendapat D

  5. Jika tidak pelajar memperoleh F ada

Contoh tertentu ini adalah agak selamat kerana ia mungkin korelasi antara menguji skor dan gred huruf akan berubah, jadi ia tidak memerlukan penyelenggaraan banyak. Tetapi berikut adalah pemikiran – Bagaimana jika anda perlu segmen gred antara A + A dan A - (dan sebagainya)? Sekarang anda empat syarat jika kenyataan perlu ditulis semula mempunyai syarat 12! Berikut ialah formula anda rupa sekarang:

  • = IF (B2 > 97, "A +", jika (B2 > 93 "A", jika (B2 > 89, "A", jika (B2 > 87, "B +", jika (B2 > 83, "B", jika (B2 > 79, "B", jika (B2 > 77, "C +", jika (B2 > 73, "C", jika (B2 > 69, "C-", IF(B2>57 "D +", jika (B2 > 53, "D", jika (B2 > 49, "D", "F")))

Ia masih dari segi fungsi tepat dan akan berfungsi seperti yang dijangkakan, tetapi ia mengambil masa yang lama untuk menulis dan lebih lama untuk menguji untuk memastikan ia tidak apa yang anda inginkan. Isu amat nyata yang lain yang anda telah memasukkan skor dan gred huruf yang sepadan secara manual. Apakah kemungkinan yang anda akan secara tidak sengaja mempunyai kesilapan ejaan? Sekarang bayangkan cuba lakukan ini 64 kali dengan syarat yang lebih kompleks! Pastikan, ia mungkin, tetapi Adakah anda benar-benar ingin subjek diri anda kepada jenis ini usaha dan ralat berkemungkinan yang akan benar-benar sukar untuk tempat?

Petua: Setiap fungsi dalam Excel memerlukan ada pembuka dan penutup tanda kurung (). Excel akan cuba untuk membantu anda mengetahui apa pergi tempat dengan mewarna bahagian berlainan formula anda apabila anda sedang mengedit. Contohnya, jika anda mengedit formula di atas, apabila anda mengalihkan kursor melepasi setiap tanda kurung penutup ")", tanda kurung yang sepadan akan bertukar warna yang sama. Ini boleh sangat berguna dalam Formula tersarang kompleks apabila anda cuba untuk mengetahui jika anda mempunyai tanda kurung cukup sepadan.

Contoh tambahan

Berikut adalah contoh yang sangat biasa pengiraan komisen jualan yang berdasarkan pada tahap pencapaian hasil.

Formula dalam sel D9 adalah jika (C9 > 15000,20%, IF (C9 > 12500,17.5%,IF (C9 > 10000,15%, IF (C9 > 7500,12.5%,IF (C9 > 5000,10%, 0)))
  • = IF (C9 > 15000,20%, IF (C9 > 12500,17.5%,IF (C9 > 10000,15%, IF (C9 > 7500,12.5%,IF (C9 > 5000,10%, 0)))

Formula ini menyatakan jika (C9 lebih besar daripada 15,000 kemudian kembali 20%, jika (C9 lebih besar daripada 12,500 kemudian kembali 17.5% dan sebagainya …

Semasa adalah sangat serupa dengan yang terdahulu gred contoh, formula ini adalah contoh yang hebat cara sukar mungkin untuk mengekalkan besar jika penyata – apa yang anda perlu lakukan jika organisasi anda memutuskan untuk menambah aras pampasan baru dan mungkin mengubah yang nilai peratusan atau dolar sedia ada? Anda mempunyai banyak kerja tangan anda!

Petua: Anda boleh menyelitkan pemisah baris dalam bar formula untuk menjadikan panjang formula yang lebih mudah dibaca. Hanya tekan ALT + ENTER sebelum teks yang anda ingin Balut untuk baris baru.

Berikut adalah contoh senario komisen dengan logik daripada perintah:

Formula dalam D9 adalah daripada perintah seperti = IF (C9 > 5000,10%, IF (C9 > 7500,12.5%,IF (C9 > 10000,15%, IF (C9 > 12500,17.5%,IF (C9 > 15000,20%, 0)))

Anda boleh lihat apa yang berlaku? Bandingkan tertib perbandingan pendapatan untuk contoh sebelumnya. Cara yang adalah pergi satu ini? Ini adalah betul, ia akan dari bawah ke atas ($5,000 untuk $15,000), tidak sebaliknya. Tetapi mengapa yang sepatutnya seperti masalah besar? Ia adalah masalah besar kerana formula tidak dapat menghantar penilaian pertama untuk sebarang nilai lebih daripada $5,000. Katakan anda mempunyai $12,500 hasil – kenyataan IF akan mengembalikan 10% kerana ia adalah lebih besar daripada $5,000, dan ia akan berhenti di sana. Ini boleh sangat bermasalah kerana dalam banyak situasi jenis ini ralat pergi diperhatikan sehingga ia telah mempunyai kesan negatif. Jadi mengetahui bahawa terdapat beberapa perangkap serius dengan kenyataan IF tersarang kompleks, apakah yang boleh anda lakukan? Dalam kebanyakan kes, anda boleh menggunakan fungsi VLOOKUP selain membina formula kompleks dengan fungsi IF. Menggunakan VLOOKUP, anda perlu mencipta jadual rujukan:

Formula dalam sel D2 adalah =VLOOKUP(C2,C5:D17,2,TRUE)
  • =VLOOKUP(C2,C5:D17,2,TRUE)

Formula ini menyatakan untuk mencari nilai dalam C2 dalam julat C5:C17. Jika nilai ditemui, kemudian mengembalikan nilai yang sepadan daripada baris yang sama dalam lajur D.

Formula dalam sel C9 adalah =VLOOKUP(B9,B2:C6,2,TRUE)
  • =VLOOKUP(B9,B2:C6,2,TRUE)

Begitu juga, formula ini mencari nilai dalam sel B9 dalam julat B2:B22. Jika nilai ditemui, kemudian mengembalikan nilai yang sepadan daripada baris yang sama dalam lajur C.

Nota: Kedua-dua VLOOKUPs ini menggunakan argumen TRUE di hujung Formula, bermakna kami ingin mereka untuk mencari padanan approxiate. Dengan kata lain, ia akan sepadan dengan nilai tepat dalam Jadual carian dan sebarang nilai yang jatuh antara mereka juga. Dalam kes ini Jadual carian perlu Diisih dalam tertib menaik, daripada terkecil kepada terbesar.

VLOOKUP dirangkumi dalam lebih banyak butiran di sini, tetapi ini adalah pasti banyak lebih mudah daripada aras 12, Kompleks jika kenyataan tersarang! Terdapat manfaat kurang jelas lain juga:

  • Jadual rujukan VLOOKUP adalah kanan dalam terbuka dan mudah untuk dilihat.

  • Nilai Jadual boleh dikemas kini dengan mudah dan anda tidak perlu sentuhan formula jika syarat yang anda ubah.

  • Jika anda tidak ingin orang lain melihat atau mengganggu Jadual rujukan anda, hanya meletakkannya pada lembaran kerja lain.

Adakah anda tahu?

Terdapat sekarang ada IFS fungsi yang boleh menggantikan berbilang, tersarang jika penyata dengan fungsi tunggal. Jadi dan bukannya contoh gred awal kami, yang mempunyai fungsi IF tersarang 4:

  • = IF (D2 > 89, "A", jika (D2 > 79, "B", jika (D2 > 69, "C", jika (D2 > 59, "D", "F")))

Ia boleh dibuat lebih mudah dengan fungsi IFS tunggal:

  • = IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

Fungsi IFS adalah hebat kerana anda tidak perlu bimbang tentang semua tersebut jika penyata dan tanda kurung.

Nota: Ciri ini hanya tersedia jika anda mempunyai langganan Office 365. Jika anda pelanggan Office 365, pastikan anda mempunyai versi Office yang terkini.

Cuba Office 365 atau versi Excel yang terkini

Perlukan bantuan lanjut?

Anda sentiasa boleh tanya pakar dalam Komuniti teknikal Excel, dapatkan sokongan dalam komuniti jawapanatau cadangkan ciri baru atau peningkatan pada Excel pengguna suara.

Topik Berkaitan

Video: Fungsi IF lanjutan
fungsi IFS (Office 365, Excel 2016 dan lebih baru)
fungsi COUNTIF dalam akan mengira nilai berdasarkan kriteria tunggal
fungsi COUNTIFS dalam akan mengira nilai berdasarkan berbilang kriteria
fungsi SUMIF akan menjumlahkan nilai berdasarkan kriteria tunggal
fungsi SUMIFS dalam akan menjumlahkan nilai berdasarkan berbilang kriteria
fungsi AND
fungsi OR
Fungsi VLOOKUP
gambaran keseluruhan Formula dalam Excel
cara untuk mengelakkan formula yang rosak
menggunakan penyemakan ralat untuk mengesan ralat dalam Formula
fungsi logik
Fungsi Excel (berabjad)
fungsi Excel (mengikut kategori)

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.

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.

×