Cemerlang jika berfungsi dengan pelbagai syarat

Tutorial menunjukkan cara membuat pelbagai jika pernyataan dalam Excel dengan dan juga atau logik. Juga, anda akan belajar cara menggunakan jika bersama -sama dengan fungsi Excel yang lain.

Di bahagian pertama Excel kami jika tutorial, kami melihat cara membina pernyataan yang mudah jika satu syarat untuk teks, nombor, tarikh, kosong dan bukan kosong. Walau bagaimanapun, untuk analisis data yang kuat, anda mungkin perlu menilai pelbagai syarat pada satu masa. Contoh formula di bawah akan menunjukkan cara yang paling berkesan untuk melakukan ini.

Cara menggunakan jika berfungsi dengan pelbagai syarat

Pada dasarnya, terdapat dua jenis formula IF dengan pelbagai kriteria berdasarkan dan / atau logik . Oleh itu, dalam ujian logik formula IF anda, anda harus menggunakan salah satu fungsi ini:

• Dan fungsi - pulangan benar jika semua syarat dipenuhi; Palsu sebaliknya.
• Atau fungsi - pulangan benar jika ada keadaan tunggal dipenuhi; Palsu sebaliknya.

Untuk lebih menggambarkan perkara itu, mari kita menyiasat beberapa contoh formula kehidupan sebenar.

Excel jika pernyataan dengan pelbagai syarat (dan logik)

Formula generik Excel jika dengan dua atau lebih syarat ini:

Jika (dan ( syarat1 , syarat2 , ...), value_if_true, value_if_false)

Diterjemahkan ke dalam bahasa manusia, formula mengatakan: Jika keadaan 1 adalah benar dan keadaan 2 adalah benar, pulangan nilai_if_true ; lain kembali nilai_if_false .

Katakan anda mempunyai jadual yang menyenaraikan skor dua ujian dalam lajur B dan C. Untuk lulus peperiksaan akhir, pelajar mesti mempunyai kedua -dua skor lebih besar daripada 50.

Untuk ujian logik, anda menggunakan berikut dan pernyataan: dan (b2> 50, c2> 50)

Jika kedua -dua keadaan adalah benar, formula akan kembali "lulus"; Sekiranya keadaan palsu - "gagal".

=IF(AND(B2>50, B2>50), "Pass", "Fail")

Mudah, bukan? Tangkapan skrin di bawah membuktikan bahawa Excel kami jika /dan formula berfungsi dengan betul:

Dengan cara yang sama, anda boleh menggunakan Excel jika berfungsi dengan pelbagai keadaan teks .

Sebagai contoh, untuk mengeluarkan "baik" jika kedua -dua B2 dan C2 lebih besar daripada 50, "buruk" jika tidak, formula adalah:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")

Nota penting! Dan fungsi memeriksa semua syarat , walaupun sudah diuji satu (s) yang dinilai kepada palsu. Tingkah laku sedemikian agak luar biasa kerana dalam kebanyakan bahasa pengaturcaraan, keadaan berikutnya tidak diuji jika mana -mana ujian terdahulu telah kembali palsu.

Dalam praktiknya, yang seolah -olah betul jika pernyataan boleh mengakibatkan kesilapan kerana kekhususan ini. Sebagai contoh, formula di bawah akan kembali #div/0! ("Bahagikan dengan sifar" ralat) Jika sel A2 sama dengan 0:

=IF(AND(A20, (1/A2)>0.5),"Good", "Bad")

Mengelakkan ini, anda harus menggunakan fungsi bersarang jika:

=IF(A20, IF((1/A2)>0.5, "Good", "Bad"), "Bad")

Untuk maklumat lanjut, sila lihat apakah dan formula dalam Excel.

Excel jika berfungsi dengan pelbagai syarat (atau logik)

Untuk melakukan satu perkara jika ada syarat yang dipenuhi, jika tidak, lakukan sesuatu yang lain, gunakan gabungan ini jika dan atau fungsi ini:

Jika (atau ( syarat1 , syarat2 , ...), nilai_if_true, value_if_false)

Perbezaan dari IF / dan formula yang dibincangkan di atas adalah bahawa Excel mengembalikan benar jika mana -mana syarat yang ditentukan adalah benar.

Jadi, jika dalam formula sebelumnya, kita menggunakan atau bukan dan:

=IF(OR(B2>50, B2>50), "Pass", "Fail")

Kemudian sesiapa yang mempunyai lebih daripada 50 mata dalam peperiksaan sama ada akan mendapat "lulus" dalam lajur D. Dengan keadaan sedemikian, pelajar kami mempunyai peluang yang lebih baik untuk lulus peperiksaan akhir (Yvette yang sangat tidak bernasib baik dengan hanya 1 mata :)

Petua. Sekiranya anda membuat pelbagai pernyataan jika teks dan menguji nilai dalam satu sel dengan atau logik (iaitu sel boleh menjadi "ini" atau "itu"), maka anda boleh membina formula yang lebih padat menggunakan pemalar array.

Sebagai contoh, untuk menandakan jualan sebagai "ditutup" jika sel B2 sama ada "dihantar" atau "dibayar", formula adalah:

=IF(OR(B2={"delivered", "paid"}), "Closed", "")

Lebih banyak contoh formula boleh didapati dalam Excel jika atau fungsi.

Sekiranya dengan pelbagai dan & atau pernyataan

Jika tugas anda memerlukan menilai beberapa set pelbagai syarat, anda perlu menggunakan kedua -dua dan & atau berfungsi pada satu masa.

Dalam jadual sampel kami, katakan anda mempunyai kriteria berikut untuk memeriksa hasil peperiksaan:

• Keadaan 1: peperiksaan1> 50 dan peperiksaan> 50
• Keadaan 2: peperiksaan1> 40 dan peperiksaan> 60

Jika salah satu syarat dipenuhi, peperiksaan akhir dianggap diluluskan.

Pada pandangan pertama, formula itu kelihatan sedikit rumit, tetapi sebenarnya ia tidak! Anda hanya menyatakan setiap syarat di atas sebagai dan pernyataan dan sarangnya dalam fungsi atau (kerana tidak perlu memenuhi kedua -dua keadaan, sama ada cukup):

OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Kemudian, gunakan atau fungsi untuk ujian logik jika dan membekalkan nilai yang dikehendaki dan nilai nilai dan nilai . Hasilnya, anda mendapat yang berikut jika formula dengan pelbagai dan / atau syarat:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

Tangkapan skrin di bawah menunjukkan bahawa kami telah melakukan formula yang betul:

Sememangnya, anda tidak terhad kepada hanya menggunakan dua dan/atau fungsi dalam formula IF anda. Anda boleh menggunakan seberapa banyak yang diperlukan oleh logik perniagaan anda, dengan syarat:

• Dalam Excel 2007 dan lebih tinggi, anda tidak mempunyai lebih daripada 255 argumen, dan jumlah panjang formula IF tidak melebihi 8,192 aksara.
• Dalam Excel 2003 dan lebih rendah, tidak ada lebih daripada 30 argumen, dan jumlah panjang formula IF anda tidak melebihi 1,024 aksara.

Bersarang jika pernyataan untuk memeriksa beberapa ujian logik

Jika anda ingin menilai pelbagai ujian logik dalam formula tunggal, maka anda boleh bersarang beberapa fungsi satu ke satu sama lain. Fungsi sedemikian dipanggil bersarang jika fungsi . Mereka membuktikan sangat berguna apabila anda ingin mengembalikan nilai yang berbeza bergantung kepada keputusan ujian logik.

Berikut adalah contoh biasa: Katakan anda ingin memenuhi syarat pencapaian pelajar sebagai " baik ", " memuaskan " dan " miskin " berdasarkan skor berikut:

• Baik: 60 atau lebih (> = 60)
• Memuaskan: Antara 40 dan 60 (> 40 dan
• Miskin: 40 atau kurang (

Sebelum menulis formula, pertimbangkan susunan fungsi yang anda akan bersarang. Excel akan menilai ujian logik mengikut susunan yang muncul dalam formula. Sebaik sahaja keadaan menilai benar, keadaan berikutnya tidak diuji, yang bermaksud formula berhenti selepas hasil yang pertama.

Dalam kes kita, fungsi -fungsi diatur dari yang terbesar hingga terkecil:

=IF(B2>=60, "Good", IF(B2>40, "Satisfactory", "Poor"))

Sememangnya, anda boleh bersarang lebih banyak fungsi jika diperlukan (sehingga 64 dalam versi moden).

Untuk maklumat lanjut, sila lihat cara menggunakan pelbagai bersarang jika pernyataan dalam Excel.

Excel jika formula array dengan pelbagai syarat

Satu lagi cara untuk mendapatkan Excel jika untuk menguji pelbagai syarat adalah dengan menggunakan formula array.

Untuk menilai keadaan dengan dan logik, gunakan asterisk:

Jika ( syarat1 ) * ( syarat2 ) * ..., value_if_true, value_if_false)

Untuk menguji keadaan dengan atau logik, gunakan tanda tambah:

Jika ( syarat1 ) ( syarat2 ) ..., value_if_true, value_if_false)

Untuk melengkapkan formula array dengan betul, tekan CTRL Shift ENTER KEYS bersama -sama. Dalam Excel 365 dan Excel 2021, ini juga berfungsi sebagai formula biasa kerana sokongan untuk tatasusunan dinamik.

Sebagai contoh, untuk mendapatkan "lulus" jika kedua -dua B2 dan C2 lebih besar daripada 50, formula adalah:

=IF((B2>50) * (C2>50), "Pass", "Fail")

Dalam Excel 365 saya, formula biasa berfungsi dengan baik (seperti yang anda lihat dalam tangkapan skrin di atas). Dalam Excel 2019 dan lebih rendah, ingatlah untuk menjadikannya formula array dengan menggunakan peralihan CTRL Enter pintasan .

Untuk menilai pelbagai syarat dengan atau logik, formula adalah:

=IF((B2>50) (C2>50), "Pass", "Fail")

Menggunakan jika bersama -sama dengan fungsi lain

Bahagian ini menerangkan cara menggunakan jika digabungkan dengan fungsi Excel yang lain dan manfaat yang diberikan kepada anda.

Contoh 1. Jika #N/A Ralat dalam VLOOKUP

Apabila VLOOKUP atau fungsi carian lain tidak dapat mencari sesuatu, ia mengembalikan ralat #N/A. Untuk menjadikan jadual anda kelihatan lebih baik, anda boleh mengembalikan teks sifar, kosong, atau spesifik jika #n/a. Untuk ini, gunakan formula generik ini:

Jika (isna (vlookup (...)), value_if_na , vlookup (...))

Contohnya:

Jika #n/a pulangan 0:

Jika nilai carian dalam E1 tidak dijumpai, formula mengembalikan sifar.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))

Sekiranya #N/A kembali kosong:

Jika nilai carian tidak dijumpai, formula tidak mengembalikan apa -apa (rentetan kosong).

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))

Sekiranya #N/A mengembalikan teks tertentu:

Jika nilai carian tidak dijumpai, formula mengembalikan teks tertentu.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE))

Untuk lebih banyak contoh formula, sila lihat vlookup dengan jika pernyataan dalam Excel.

Contoh 2. Jika dengan fungsi jumlah, purata, min dan maksimum

Untuk nilai sel Jumlah berdasarkan kriteria tertentu, Excel menyediakan fungsi sumif dan sumifs.

Dalam sesetengah keadaan, logik perniagaan anda mungkin memerlukan termasuk fungsi jumlah dalam ujian logik jika. Sebagai contoh, untuk mengembalikan label teks yang berbeza bergantung kepada jumlah nilai dalam B2 dan C2, formula adalah:

=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))

Jika jumlahnya lebih besar daripada 130, hasilnya adalah "baik"; Jika lebih besar daripada 110 - "memuaskan ', jika 110 atau lebih rendah -" miskin ".

Dengan cara yang sama, anda boleh membenamkan fungsi purata dalam ujian logik jika dan mengembalikan label yang berbeza berdasarkan skor purata:

=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))

Dengan mengandaikan skor total dalam lajur D, anda boleh mengenal pasti nilai tertinggi dan terendah dengan bantuan fungsi maksimum dan min:

=IF(D2=MAX($D$2:$D$10), "Best result", "")

=IF(D2=MAX($D$2:$D$10), "Best result", "")

Untuk mempunyai kedua -dua label dalam satu lajur, sarang berfungsi di atas satu ke yang lain:

=IF(D2=MAX($D$2:$D$10), "Best result", IF(D2=MIN($D$2:$D$10), "Worst result", ""))

Begitu juga, anda boleh menggunakan jika bersama -sama dengan fungsi tersuai anda. Sebagai contoh, anda boleh menggabungkannya dengan GetCellColor atau GetCellFontColor untuk mengembalikan hasil yang berbeza berdasarkan warna sel.

Di samping itu, Excel menyediakan beberapa fungsi untuk mengira data berdasarkan syarat. Untuk contoh formula terperinci, sila lihat tutorial berikut:

• Countif - Count Cell yang memenuhi syarat
• COUNTIFS - Kira sel dengan pelbagai kriteria
• Sumif - Sel -sel Jumlah Kondisi
• Sumifs - Jumlah sel dengan pelbagai kriteria

Contoh 3. Jika dengan Isnumber, Istext dan Isblank

Untuk mengenal pasti teks, nombor dan sel kosong, Microsoft Excel menyediakan fungsi khas seperti Istext, Isnumber dan Isblank. Dengan meletakkannya dalam ujian logik tiga bersarang jika pernyataan, anda boleh mengenal pasti semua jenis data yang berbeza dalam satu perjalanan:

=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", "")))

Contoh 4. Jika dan Concatenate

Untuk mengeluarkan hasil IF dan beberapa teks ke dalam satu sel, gunakan concatenate atau concat (dalam Excel 2016 - 365) dan jika berfungsi bersama -sama. Contohnya:

=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

=CONCAT("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

Melihat tangkapan skrin di bawah, anda tidak memerlukan penjelasan mengenai formula yang dilakukannya:

Sekiranya formula Iserror / ISNA di Excel

Versi Moden Excel mempunyai fungsi khas untuk menjebak kesilapan dan menggantikannya dengan pengiraan lain atau nilai yang telah ditetapkan - IFError (dalam Excel 2007 dan kemudian) dan IFNA (dalam Excel 2013 dan kemudian). Dalam versi Excel terdahulu, anda boleh menggunakan IF ISError dan jika kombinasi ISNA sebaliknya.

Perbezaannya ialah Iferror dan IseRror mengendalikan semua kesilapan Excel yang mungkin, termasuk #Value!, #N/A, #Name?, #Ref!, #Num!, #Div/0!, Dan #Null!. Manakala IFNA dan ISNA mengkhususkan diri semata -mata dalam kesilapan #N/A.

Sebagai contoh, untuk menggantikan ralat "Dibahagikan dengan sifar" (#div/0!) Dengan teks tersuai anda, anda boleh menggunakan formula berikut:

=IF(ISERROR(A2/B2), "N/A", A2/B2)

Dan itu sahaja yang saya katakan tentang menggunakan fungsi IF dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Excel jika pelbagai kriteria - Contoh (fail .xlsx)

Atas ialah kandungan terperinci Cemerlang jika berfungsi dengan pelbagai syarat. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!