Excel

19 petua untuk formula IF bersarang

19 Tips Nested If Formulas

The JIKA berfungsi adalah salah satu fungsi yang paling banyak digunakan di Excel. JIKA adalah fungsi yang sederhana, dan orang suka JIKA kerana memberi mereka kekuatan untuk membuat Excel membalas kerana maklumat dimasukkan dalam hamparan. Dengan JIKA, anda dapat menghidupkan hamparan anda.





Tetapi satu JIKA sering membawa kepada yang lain, dan setelah anda menggabungkan lebih dari beberapa JIKA, formula anda boleh mula kelihatan seperti Frankensteins kecil :)

Adakah bersarang JIKA jahat? Adakah mereka kadang-kadang perlu? Apakah alternatifnya?





Teruskan membaca untuk mengetahui jawapan kepada soalan-soalan ini dan banyak lagi ...

1. JIKA asas

Sebelum kita membincangkan mengenai IF bersarang, mari kita cepat mengkaji struktur IF asas:



 
= IF (test,[true],[false])

Fungsi IF menjalankan ujian dan melakukan tindakan yang berbeza bergantung pada apakah hasilnya benar atau salah.

Perhatikan tanda kurung persegi ... ini bermaksud argumen adalah pilihan. Walau bagaimanapun, anda mesti membekalkan sama ada nilai untuk benar, atau nilai untuk palsu.

Untuk menggambarkan, di sini kita menggunakan IF untuk memeriksa skor dan mengira 'Lulus' untuk skor sekurang-kurangnya 65:

Fungsi IF asas - kembali

Sel D3 dalam contoh mengandungi formula ini:

 
= IF (C3>=65,'Pass')

Yang boleh dibaca seperti ini: jika skor di C3 sekurang-kurangnya 65, kembalikan 'Lulus'.

Walau bagaimanapun, perhatikan bahawa jika skornya kurang daripada 65, JIKA mengembalikan SALAH, kerana kami tidak memberikan nilai jika salah. Untuk memaparkan 'Gagal' untuk skor yang tidak lulus, kita dapat menambahkan 'Gagal' sebagai argumen palsu seperti:

 
= IF (C3>=65,'Pass','Fail')

Fungsi IF asas - dengan nilai tambah untuk false

Video: Cara membina formula logik .

2. Apa maksud bersarang

Bersarang bermaksud menggabungkan formula, satu di antara yang lain, sehingga satu formula mengatasi hasil yang lain. Contohnya, inilah formula di mana fungsi HARI INI bersarang di dalam fungsi BULAN:

 
= MONTH ( TODAY ())

Fungsi HARI INI mengembalikan tarikh semasa di dalam fungsi BULAN. Fungsi BULAN mengambil tarikh tersebut dan mengembalikan bulan semasa. Walaupun formula yang agak rumit menggunakan sarang secara kerap, jadi anda akan melihat bersarang di mana-mana dalam formula yang lebih kompleks.

3. JIKA bersarang sederhana

IF bersarang hanyalah dua lagi penyataan IF dalam formula, di mana satu penyataan IF muncul di dalam yang lain.

Sebagai gambaran, di bawah ini saya telah memperluaskan formula lulus / gagal asal di atas untuk menangani hasil 'tidak lengkap' dengan menambahkan fungsi IF, dan bersarang satu JIKA di dalam yang lain:

JIKA bersarang asas

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

IF luar berjalan pertama dan menguji untuk melihat apakah C3 kosong. Sekiranya demikian, IF luar mengembalikan 'Tidak Lengkap', dan IF dalaman tidak akan berjalan.

Sekiranya skornya tidak kosong , IF luar mengembalikan SALAH, dan fungsi IF asal dijalankan.

Belajar IF bersarang dengan latihan video yang jelas dan ringkas .

4. JIKA bersarang untuk timbangan

Anda akan sering melihat IF bersarang disiapkan untuk menangani 'timbangan' ... misalnya, untuk menetapkan nilai, kos penghantaran, kadar cukai, atau nilai lain yang berbeza-beza pada skala dengan input berangka. Selagi tidak ada terlalu banyak tahap dalam skala, IF bersarang berfungsi dengan baik di sini, tetapi anda mesti memastikan formula tersusun, atau sukar dibaca.

Caranya adalah dengan menentukan arah (tinggi ke rendah, atau rendah ke tinggi), kemudian susun keadaan sesuai. Sebagai contoh, untuk menetapkan nilai dalam urutan 'rendah hingga tinggi', kita dapat mewakili penyelesaian yang diperlukan dalam jadual berikut. Perhatikan tidak ada syarat untuk 'A', kerana setelah kami menjalani semua syarat lain, kami tahu skor mesti lebih besar dari 95, dan oleh itu 'A'.

Markah Gred Keadaan
0-63 F <64
64-72 D <73
73-84 C <85
85-94 B <95
95-100 KE

Dengan syarat yang difahami dengan jelas, kita dapat memasukkan pernyataan IF pertama:

cara mengesahkan data di excel
 
= IF (C5<64,'F')

Ini menjaga 'F'. Sekarang, untuk menangani 'D', kita perlu menambah syarat lain:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Perhatikan bahawa saya hanya memasukkan IF lain ke IF pertama untuk hasil 'false'. Untuk memperluaskan formula untuk menangani 'C', kami mengulangi prosesnya:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Kami meneruskan perjalanan ini sehingga mencapai tahap terakhir. Kemudian, bukannya menambah IF lain, tambah nilai akhir untuk false.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Berikut adalah formula IF bersarang terakhir dalam tindakan:

Contoh IF bersarang yang lengkap untuk mengira nilai

Video: Cara membuat IF bersarang untuk memberikan gred

5. IF bersarang mempunyai aliran logik

Banyak formula diselesaikan dari dalam ke luar, kerana fungsi atau ungkapan 'dalaman' mesti diselesaikan terlebih dahulu agar formula selebihnya diteruskan.

IF bersarang mempunyai aliran logik tersendiri, kerana IF 'luar' bertindak seperti pintu masuk ke IF 'dalaman'. Ini bermaksud bahawa hasil dari IF luar menentukan sama ada IF dalaman bahkan berjalan. Rajah di bawah menggambarkan aliran logik formula gred di atas.

Aliran logik IF bersarang

6. Gunakan Evaluate untuk melihat aliran logik

Di Windows, anda boleh menggunakan Menilai ciri untuk menonton Excel menyelesaikan formula anda, langkah demi langkah. Ini adalah kaedah yang baik untuk 'melihat' aliran logik formula yang lebih kompleks, dan untuk menyelesaikan masalah apabila perkara tidak berjalan seperti yang anda harapkan. Skrin di bawah menunjukkan tetingkap Evaluate terbuka dan siap untuk digunakan. Setiap kali anda mengklik butang Evaluasi, 'langkah seterusnya' dalam formula diselesaikan. Anda boleh menemui Evaluate pada tab Formula pada pita (Alt M, V).

Menggunakan Evaluate untuk melangkah melalui IF bersarang yang memberikan gred

Sayangnya, versi Mac Excel tidak mengandungi ciri Evaluate, tetapi anda masih boleh menggunakan trik F9 di bawah.

7. Gunakan F9 untuk melihat hasil pemeriksaan

Apabila anda memilih ungkapan di bar formula dan menekan kekunci F9, Excel menyelesaikan bahagian yang dipilih sahaja. Ini adalah kaedah yang kuat untuk mengesahkan apa formula sebenarnya dilakukan. Pada skrin di bawah, saya menggunakan tetingkap hujung skrin untuk memilih bahagian formula yang berbeza, kemudian mengklik F9 untuk melihat bahagian yang diselesaikan:

Dengan menggunakan F9, periksa JIKA bersarang yang memberikan nilai

Gunakan Control + Z (Command + Z) pada Mac untuk mengurungkan F9. Anda juga boleh menekan Esc untuk keluar dari editor formula tanpa perubahan.

Video: Cara debug formula dengan F9

8. Ketahui had anda

Excel mempunyai had seberapa dalam anda dapat menggunakan fungsi IF. Sehingga Excel 2007, Excel membenarkan sehingga 7 tahap IF bersarang. Dalam Excel 2007+, Excel membenarkan sehingga 64 tahap.

Namun, hanya kerana anda boleh bersarang banyak IF, itu tidak bermaksud anda semestinya . Setiap tahap tambahan yang anda tambahkan menjadikan rumus lebih sukar difahami dan diselesaikan. Sekiranya anda mendapati diri anda bekerja dengan JIKA bersarang lebih dari beberapa tahap, anda mungkin harus mengambil pendekatan yang berbeza - lihat alternatif berikut.

9. Padankan tanda kurung seperti pro

Salah satu cabaran dengan IF bersarang adalah memadankan atau 'menyeimbangkan' tanda kurung. Apabila tanda kurung tidak dipadankan dengan betul, formula anda rosak. Nasib baik, E xcel menyediakan beberapa alat untuk membantu anda memastikan tanda kurung 'seimbang' semasa menyunting formula.

Pertama, setelah anda mempunyai lebih daripada satu set tanda kurung, tanda kurung diberi kod warna sehingga tanda kurung pembukaan sesuai dengan tanda kurung penutup. Warna-warna ini agak sukar dilihat, tetapi warnanya ada jika anda melihat dengan teliti:

Tanda kurung formula sesuai dengan warna tetapi sukar dilihat

Kedua (dan lebih baik) apabila anda menutup tanda kurung, Excel secara ringkas akan menebalkan pasangan yang sepadan. Anda juga boleh mengklik formula dan menggunakan kekunci anak panah untuk beralih melalui tanda kurung, dan Excel akan menebalkan kedua-dua tanda kurung sebentar apabila ada pasangan yang sepadan. Sekiranya tidak ada padanan, anda tidak akan melihat tanda tebal.

Malangnya, tulisan itu adalah ciri khas Windows. Sekiranya anda menggunakan Excel pada Mac untuk mengedit formula yang rumit, kadang-kadang masuk akal untuk menyalin dan menampal formula ke dalam penyunting teks yang baik ( Wrangler Teks percuma dan cemerlang) untuk mendapatkan alat pencocokan kurungan yang lebih baik. Text Wrangler akan berkelip apabila tanda kurung dipadankan, dan anda boleh menggunakan Command + B untuk memilih semua teks yang terdapat dalam tanda kurung. Anda boleh menampal formula kembali ke Excel setelah anda menyelesaikan masalah.

10. Gunakan tetingkap hujung skrin untuk menavigasi dan memilih

Semasa menavigasi dan mengedit IF bersarang, hujung skrin fungsi adalah sahabat anda. Dengan itu, anda boleh menavigasi dan memilih dengan tepat semua argumen dalam JIKA bersarang:

Navigasi dan pilih argumen formula dengan hujung skrin

Anda boleh melihat saya menggunakan tetingkap hujung skrin dalam video ini: Cara membina IF bersarang .

11. Berhati-hati dengan teks dan nombor

Sama seperti peringatan ringkas, ketika bekerja dengan fungsi IF, berhati-hatilah agar nombor dan teks anda sesuai. Saya sering melihat formula JIKA seperti ini:

 
= IF (A1='100','Pass','Fail')

Adakah skor ujian di A1 sungguh teks dan bukan nombor? Tidak? Oleh itu, jangan gunakan tanda petik di sekitar nombor tersebut. Jika tidak, ujian logik akan mengembalikan SALAH walaupun nilainya adalah skor lulus, kerana '100' tidak sama dengan 100. Sekiranya skor ujian adalah berangka, gunakan ini:

 
= IF (A1=100,'Pass','Fail')

12. Tambah jeda baris menjadikan IF bersarang senang dibaca

Apabila anda menggunakan formula yang mengandungi banyak peringkat IF bersarang, sukar untuk memastikan semuanya berjalan lancar. Oleh kerana Excel tidak mementingkan 'ruang kosong' dalam formula (iaitu ruang tambahan atau jeda baris), anda dapat meningkatkan kebolehbacaan ifs bersarang dengan menambahkan jeda baris.

Sebagai contoh, skrin di bawah menunjukkan IF bersarang yang mengira kadar komisen berdasarkan nombor jualan. Di sini anda dapat melihat struktur IF bersarang khas, yang sukar untuk diuraikan:

IF bersarang tanpa jeda baris sukar dibaca

Walau bagaimanapun, jika saya menambah jeda baris sebelum setiap 'nilai jika salah', logik formula melompat keluar dengan jelas. Plus, formula lebih mudah diedit:

Jeda baris menjadikan IF bersarang lebih mudah dibaca

Anda boleh menambah jeda baris pada Windows dengan Alt + Enter, pada Mac, gunakan Control + Option + Return.

Video: Cara membuat IF bersarang lebih senang dibaca .

13. Hadkan IF dengan AND dan OR

IF bersarang sangat kuat, tetapi menjadi rumit dengan cepat apabila anda menambahkan lebih banyak tahap. Salah satu cara untuk mengelakkan tahap lebih banyak adalah dengan menggunakan IF dalam kombinasi dengan fungsi AND dan OR. Fungsi-fungsi ini mengembalikan hasil BENAR / SALAH sederhana yang berfungsi dengan sempurna di dalam JIKA, jadi anda dapat menggunakannya untuk memperluas logik satu JIKA.

Sebagai contoh, dalam masalah di bawah ini, kita ingin meletakkan 'x' di lajur D untuk menandakan baris di mana warnanya 'merah' dan ukurannya 'kecil'.

JIKA dengan fungsi AND lebih sederhana daripada dua IF bersarang

Kami boleh menulis formula dengan dua IF bersarang seperti ini:

 
= IF (B6='red', IF (C6='small','x',''),'')

Namun, dengan mengganti ujian dengan fungsi AND, kita dapat mempermudah formula:

 
= IF ( AND (B6='red',C6='small'),'x','')

Dengan cara yang sama, kita dapat dengan mudah memperluas formula ini dengan fungsi OR untuk memeriksa merah ATAU biru DAN kecil:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Semua ini boleh dilakukan dengan IF bersarang, tetapi formula akan menjadi lebih kompleks dengan cepat.

Video: JIKA ini ATAU itu

14. Ganti IF Bersarang dengan VLOOKUP

Apabila IF bersarang hanya memberikan nilai berdasarkan satu input, ia dapat diganti dengan mudah dengan Fungsi VLOOKUP . Sebagai contoh, IF bersarang ini memberikan nombor kepada lima warna yang berbeza:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Kita boleh menggantinya dengan mudah dengan VLOOKUP (lebih mudah) ini:

 
= VLOOKUP (E3,B3:C7,2,0)

Bersarang IF vs VLOOKUP

Sebagai bonus, VLOOKUP menyimpan nilai pada lembaran kerja (di mana ia dapat diubah dengan mudah) dan bukannya memasukkannya ke dalam formula.

Walaupun formula di atas menggunakan padanan tepat, anda boleh menggunakannya dengan mudah VLOOKUP untuk gred juga.

Lihat juga: 23 perkara yang perlu diketahui mengenai VLOOKUP

Video: Cara menggunakan VLOOKUP

Video: Mengapa VLOOKUP lebih baik daripada IF bersarang

15. Pilih PILIH

Fungsi PILIH dapat memberikan penyelesaian yang elegan apabila anda perlu memetakan nombor sederhana, berturut-turut (1,2,3, dll.) Kepada nilai sewenang-wenangnya.

Dalam contoh di bawah, PILIH digunakan untuk membuat singkatan hari biasa yang disesuaikan:

Bersarang IF vs fungsi PILIH

Pasti, anda boleh gunakan JIKA bersarang yang panjang dan rumit untuk melakukan perkara yang sama, tetapi jangan :)

16. Gunakan IFS dan bukannya IF bersarang

Sekiranya anda menggunakan Excel 2016 melalui Office 365, ada fungsi baru yang boleh anda gunakan dan bukannya IF bersarang: fungsi IFS. Fungsi IFS menyediakan struktur khas untuk menilai pelbagai keadaan tanpa bersarang:

Fungsi IFS - pelbagai keadaan tanpa bersarang

Formula yang digunakan di atas kelihatan seperti ini:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Perhatikan bahawa kami hanya mempunyai sepasang tanda kurung!

Apa yang berlaku apabila anda membuka spreadsheet yang menggunakan fungsi IFS dalam versi Excel yang lebih lama? Dalam Excel 2013 dan 2010 (dan saya percaya Excel 2007, tetapi tidak dapat menguji) anda akan melihat '_xlfn.' dilampirkan ke IFS di dalam sel. Nilai yang dikira sebelumnya akan tetap ada, tetapi jika sesuatu menyebabkan formula dikira semula, anda akan melihat ralat #NAME. Microsoft mempunyai maklumat lanjut di sini .

17. Keluar maksimum

Kadang kala, anda boleh menggunakan MAX atau MIN dengan cara yang sangat bijak yang mengelakkan penyataan IF. Sebagai contoh, anggap anda mempunyai pengiraan yang perlu menghasilkan angka positif, atau sifar. Dengan kata lain, jika pengiraan mengembalikan nombor negatif, anda hanya mahu menunjukkan sifar.

Fungsi MAX memberi anda cara yang bijak untuk melakukan ini tanpa JIKA di mana sahaja:

 
= MAX (calculation,0)

Teknik ini mengembalikan hasil pengiraan jika positif, dan sifar sebaliknya.

Saya suka pembinaan ini kerana begitu sederhana . Lihat artikel ini untuk maklumat lengkap .

18. Kesalahan perangkap dengan IFERROR

Penggunaan klasik IF adalah untuk memerangkap kesalahan dan memberikan hasil yang lain apabila kesalahan dilemparkan, seperti ini:

 
= IF ( ISERROR (formula),error_result,formula)

Ini jelek dan berlebihan, kerana formula yang sama masuk dua kali, dan Excel harus mengira hasil yang sama dua kali apabila tidak ada kesalahan.

Di Excel 2007, fungsi IFERROR diperkenalkan, yang membolehkan anda menjebak ralat dengan lebih elegan:

 
= IFERROR (formula,error_result)

Sekarang apabila formula melemparkan ralat, IFERROR hanya mengembalikan nilai yang anda berikan.

19. Gunakan logik boolean

Anda juga kadang-kadang dapat mengelakkan IF bersarang dengan menggunakan apa yang disebut 'logik boolean'. Kata boolean merujuk kepada nilai BENAR / SALAH. Walaupun Excel memaparkan perkataan TRUE dan FALSE dalam sel, secara dalaman, Excel menganggap TRUE sebagai 1, dan FALSE sebagai sifar. Anda boleh menggunakan fakta ini untuk menulis formula yang pintar dan cepat. Sebagai contoh, dalam contoh VLOOKUP di atas, kami mempunyai formula IF bersarang yang kelihatan seperti ini:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Dengan menggunakan logik boolean, anda boleh menulis semula formula seperti ini:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Setiap ungkapan melakukan ujian, dan kemudian menggandakan hasil ujian dengan 'nilai jika benar'. Oleh kerana ujian mengembalikan TRUE atau FALSE (1 atau 0), keputusan FALSE dengan berkesan membatalkan formula mereka.

Untuk hasil berangka, logik boolean sederhana dan sangat cepat, kerana tidak ada percabangan. Pada sisi negatifnya, logik boolean dapat membingungkan orang yang tidak terbiasa melihatnya. Namun, ini adalah teknik yang bagus untuk diketahui.

Video: Cara menggunakan logik boolean dalam formula Excel

Bilakah anda memerlukan JIKA bersarang?

Dengan semua pilihan ini untuk mengelakkan IF bersarang, anda mungkin tertanya-tanya bila masuk akal untuk menggunakan IF bersarang?

Saya rasa IF bersarang masuk akal apabila anda perlu menilai beberapa input yang berbeza untuk membuat keputusan.

Sebagai contoh, andaikan anda ingin mengira status invois 'Dibayar', 'Buka', 'Tertunda', dll. Ini memerlukan anda melihat usia invois dan baki tertunggak:

Mengira status invois dengan IF bersarang

Dalam kes ini, IF bersarang adalah penyelesaian yang sangat baik.

Pemikiran anda?

Bagaimana dengan awak? Adakah anda seorang IF-ster? Adakah anda mengelakkan IF bersarang? Adakah bersarang JIKA jahat? Kongsi pendapat anda di bawah.

Pelajari formula Excel dengan cepat latihan video ringkas . Pengarang Dave Bruns


^