Fungsi IF dalam Excel. Contoh (dengan berbilang syarat)

Salah satu kelebihan utama hamparan Excel ialah keupayaan untuk memprogramkan kefungsian dokumen tertentu. Seperti yang diketahui kebanyakan orang daripada pelajaran sains komputer sekolah, salah satu komponen utama yang membolehkan anda mempraktikkannya ialah pengendali logik. Salah satunya ialah pengendali IF, yang memperuntukkan pelaksanaan tindakan tertentu apabila syarat tertentu dipenuhi. 

Contohnya, jika nilai sepadan dengan nilai tertentu, maka satu label dipaparkan dalam sel. Jika tidak, ia berbeza. Mari kita lihat alat yang berkesan ini dengan lebih terperinci dalam amalan.

Fungsi IF dalam Excel (maklumat am)

Mana-mana program, walaupun ia kecil, semestinya mengandungi urutan tindakan, yang dipanggil algoritma. Ia mungkin kelihatan seperti ini:

1. Semak keseluruhan lajur A untuk nombor genap.
2. Jika nombor genap ditemui, tambahkan nilai ini dan ini.
3. Jika nombor genap tidak dijumpai, maka paparkan tulisan "tidak dijumpai".
4. Semak sama ada nombor yang terhasil adalah genap. 
5. Jika ya, tambahkannya pada semua nombor genap yang dipilih dalam perenggan 1.

Dan walaupun ini hanya situasi hipotesis, yang tidak mungkin diperlukan dalam kehidupan sebenar, pelaksanaan apa-apa tugas semestinya membayangkan kehadiran algoritma yang serupa. Sebelum menggunakan fungsi JIKA, anda perlu mempunyai idea yang jelas dalam kepala anda tentang hasil yang ingin anda capai. 

Sintaks fungsi IF dengan satu syarat

Sebarang fungsi dalam Excel dilakukan menggunakan formula. Corak yang mana data mesti dihantar ke fungsi dipanggil sintaks. Dalam kes pengendali IF, formula akan berada dalam format ini.

=IF (ungkapan_logik, nilai_jika_benar, nilai_jika_salah)

Mari lihat sintaks dengan lebih terperinci:

1. Ungkapan Boolean. Ini adalah syarat itu sendiri, pematuhan atau ketidakpatuhan yang Excel semak. Kedua-dua maklumat berangka dan teks boleh disemak.
2. Nilai_jika_benar. Keputusan yang akan dipaparkan dalam sel jika data yang disemak memenuhi kriteria yang ditentukan.
3. nilai_jika_salah. Hasil yang dipaparkan dalam sel jika data yang diperiksa tidak sepadan dengan keadaan.

Berikut adalah contoh untuk kejelasan.

Di sini fungsi membandingkan sel A1 dengan nombor 20. Ini adalah perenggan pertama sintaks. Jika kandungan lebih besar daripada nilai ini, nilai "lebih daripada 20" dipaparkan dalam sel tempat formula ditulis. Jika keadaan tidak sepadan dengan keadaan ini - "kurang daripada atau sama dengan 20".

Jika anda ingin memaparkan nilai teks dalam sel, anda mesti melampirkannya dalam tanda petikan.

Berikut adalah satu lagi keadaan. Untuk layak mengikuti sesi peperiksaan, pelajar mesti lulus sesi ujian. Para pelajar berjaya memenangi kredit dalam semua mata pelajaran, dan kini yang terakhir kekal, yang ternyata menjadi penentu. Tugas kami adalah untuk menentukan pelajar yang mana yang diterima masuk ke peperiksaan dan yang tidak.

Oleh kerana kami ingin menyemak teks dan bukan nombor, hujah pertama ialah B2="cons.".

Sintaks Fungsi IF dengan Berbilang Syarat

Selalunya, satu kriteria tidak mencukupi untuk menyemak nilai terhadapnya. Jika anda perlu mempertimbangkan lebih daripada satu pilihan, anda boleh menyarangkan fungsi IF satu ke dalam yang lain. Akan ada beberapa fungsi bersarang.

Untuk menjadikannya lebih jelas, berikut ialah sintaksnya.

=IF(ungkapan_logik, nilai_jika_benar, JIKA(ungkapan_logik, nilai_jika_benar, nilai_jika_salah))

Dalam kes ini, fungsi akan menyemak dua kriteria sekaligus. Jika syarat pertama adalah benar, nilai yang diperoleh hasil daripada operasi dalam hujah pertama dikembalikan. Jika tidak, kriteria kedua diperiksa untuk pematuhan.

Berikut adalah contohnya.

Dan dengan bantuan formula sedemikian (ditunjukkan dalam tangkapan skrin di bawah), anda boleh menganalisis prestasi setiap pelajar.

Seperti yang anda lihat, satu lagi syarat telah ditambah di sini, tetapi prinsipnya tidak berubah. Jadi anda boleh menyemak beberapa kriteria sekaligus.

Cara memanjangkan fungsi IF menggunakan operator AND dan OR

Dari semasa ke semasa terdapat situasi untuk menyemak segera untuk pematuhan beberapa kriteria, dan tidak menggunakan operator bersarang logik, seperti dalam contoh sebelumnya. Untuk melakukan ini, gunakan sama ada fungsi И atau fungsi OR bergantung kepada sama ada anda perlu memenuhi beberapa kriteria sekaligus atau sekurang-kurangnya satu daripadanya. Mari kita lihat lebih dekat kriteria ini.

IF berfungsi dengan keadaan AND

Kadangkala anda perlu menyemak ungkapan untuk berbilang syarat sekaligus. Untuk ini, fungsi AND digunakan, ditulis dalam hujah pertama fungsi IF. Ia berfungsi seperti ini: jika a sama dengan satu dan a sama dengan 2, nilainya akan menjadi c.

Fungsi IF dengan keadaan "OR".

Fungsi OR berfungsi dengan cara yang sama, tetapi dalam kes ini, hanya satu daripada syarat yang benar. Seboleh-bolehnya, sehingga 30 syarat boleh disemak dengan cara ini. 

Berikut ialah beberapa cara untuk menggunakan fungsi И и OR sebagai hujah fungsi IF.

Membandingkan data dalam dua jadual

Dari semasa ke semasa adalah mungkin untuk membandingkan dua jadual yang serupa. Sebagai contoh, seseorang bekerja sebagai akauntan dan perlu membandingkan dua laporan. Terdapat tugas lain yang serupa, seperti membandingkan kos barangan kumpulan yang berbeza, kemudian, penilaian pelajar untuk tempoh yang berbeza, dan sebagainya.

Untuk membandingkan dua jadual, gunakan fungsi COUNTIF. Mari kita lihat dengan lebih terperinci.

Katakan kita mempunyai dua jadual yang mengandungi spesifikasi dua pemproses makanan. Dan kita perlu membandingkannya, dan menyerlahkan perbezaan dengan warna. Ini boleh dilakukan menggunakan pemformatan bersyarat dan fungsi COUNTIF. 

Meja kami kelihatan seperti ini.

Kami memilih julat yang sepadan dengan ciri teknikal pemproses makanan pertama.

Selepas itu, klik pada menu berikut: Pemformatan bersyarat – buat peraturan – gunakan formula untuk menentukan sel yang diformatkan.

Dalam bentuk formula untuk pemformatan, kami menulis fungsi =COUNTIF (julat untuk dibandingkan; sel pertama jadual pertama)=0. Jadual dengan ciri pemproses makanan kedua digunakan sebagai julat perbandingan.

Anda perlu memastikan bahawa alamat adalah mutlak (dengan tanda dolar di hadapan nama baris dan lajur). Tambah =0 selepas formula supaya Excel mencari nilai yang tepat.

Selepas itu, anda perlu menetapkan pemformatan sel. Untuk melakukan ini, di sebelah sampel, anda perlu mengklik pada butang "Format". Dalam kes kami, kami menggunakan isian, kerana ia adalah yang paling mudah untuk tujuan ini. Tetapi anda boleh memilih mana-mana pemformatan yang anda mahu.

Kami telah menetapkan nama lajur sebagai julat. Ini lebih mudah daripada memasukkan julat secara manual.

Fungsi SUMIF dalam Excel

Sekarang mari kita beralih kepada fungsi IF, yang akan membantu menggantikan dua titik algoritma sekaligus. Yang pertama ialah SUMMESLEY, yang menambah dua nombor yang memenuhi syarat tertentu. Sebagai contoh, kita berhadapan dengan tugas untuk menentukan jumlah wang yang perlu dibayar setiap bulan kepada semua penjual. Untuk ini adalah perlu.

1. Tambahkan baris dengan jumlah pendapatan semua penjual dan klik pada sel yang akan mengandungi hasil selepas memasukkan formula. 
2. Kami mencari butang fx, yang terletak di sebelah baris untuk formula. Seterusnya, tetingkap akan muncul di mana anda boleh mencari fungsi yang diperlukan melalui carian. Selepas memilih pengendali, anda perlu mengklik butang "OK". Tetapi input manual sentiasa mungkin.
   

   

3. Seterusnya, tetingkap untuk memasukkan argumen fungsi akan muncul. Semua nilai boleh ditentukan dalam medan yang sepadan, dan julat boleh dimasukkan melalui butang di sebelahnya.
   

   

4. Hujah pertama ialah julat. Di sini anda memasukkan sel yang anda ingin semak untuk pematuhan dengan kriteria. Jika kita bercakap tentang kita, ini adalah jawatan pekerja. Masukkan julat D4:D18. Atau pilih sahaja sel yang diminati.
5. Dalam medan "Kriteria", masukkan kedudukan. Dalam kes kami - "penjual". Sebagai julat penjumlahan, kami menunjukkan sel di mana gaji pekerja disenaraikan (ini dilakukan secara manual dan memilihnya dengan tetikus). Klik "OK", dan kami mendapat gaji yang dikira siap untuk semua pekerja yang merupakan penjual.

Setuju bahawa ia sangat mudah. bukan?

Fungsi SUMIFS dalam Excel

Fungsi ini membolehkan anda menentukan jumlah nilai yang memenuhi pelbagai syarat. Sebagai contoh, kami diberi tugas untuk menentukan jumlah gaji semua pengurus yang bekerja di cawangan selatan syarikat itu.

Tambah baris di mana hasil akhir akan berada, dan masukkan formula dalam sel yang dikehendaki. Untuk melakukan ini, klik pada ikon fungsi. Tetingkap akan muncul di mana anda perlu mencari fungsi tersebut SUMMESLIMN. Seterusnya, pilihnya daripada senarai dan tetingkap biasa dengan argumen terbuka. Tetapi bilangan hujah ini kini berbeza. Formula ini memungkinkan untuk menggunakan bilangan kriteria yang tidak terhingga, tetapi bilangan argumen minimum ialah lima. 

Hanya lima boleh ditentukan melalui dialog input argumen. Jika anda memerlukan lebih banyak kriteria, maka ia perlu dimasukkan secara manual mengikut logik yang sama seperti dua yang pertama.

Mari kita lihat hujah utama dengan lebih terperinci:

1. Julat penjumlahan. Sel untuk dijumlahkan.
2. Julat keadaan 1 – julat yang akan diperiksa untuk pematuhan dengan kriteria tertentu. 
3. Syarat 1 ialah syarat itu sendiri.
4. Julat kriteria 2 ialah julat kedua yang akan disemak terhadap kriteria.
5. Syarat 2 adalah syarat kedua.

Logik selanjutnya adalah serupa. Hasilnya, kami menentukan gaji semua pengurus Cawangan Selatan.

Fungsi COUNTIF dalam Excel

Jika anda perlu menentukan bilangan sel yang berada di bawah kriteria tertentu, gunakan fungsi tersebut COUNTIF. Katakan kita perlu memahami bilangan jurujual yang bekerja dalam organisasi ini:

1. Mula-mula, tambahkan baris yang mengandungi bilangan penjual. Selepas itu, anda perlu klik pada sel di mana hasilnya akan dipaparkan.
2. Selepas itu, anda perlu mengklik pada butang "Sisipkan Fungsi", yang boleh didapati dalam tab "Formula". Tetingkap akan muncul dengan senarai kategori. Kita perlu memilih item "Senarai abjad penuh". Dalam senarai, kami berminat dengan formula COUNTIF. Selepas kami memilihnya, kami perlu mengklik butang "OK".
   

   

3. Selepas itu, kami mempunyai bilangan jurujual yang diambil bekerja dalam organisasi ini. Ia diperoleh dengan mengira bilangan sel di mana perkataan "penjual" ditulis. Semuanya mudah. 

Fungsi COUNTSLIM dalam Excel

Sama dengan formula SUMMESLIMN, formula ini mengira bilangan sel yang sepadan dengan berbilang keadaan. Sintaks adalah serupa tetapi berbeza sedikit daripada formula SUMMESLIMN:

1. Julat keadaan 1. Ini adalah julat yang akan diuji terhadap kriteria pertama.
2. Syarat 1. Secara langsung kriteria pertama.
3. Julat Keadaan 2. Ini adalah julat yang akan diuji terhadap kriteria kedua. 
4. Syarat 2.
5. Keadaan julat 3.

Dan sebagainya.

Jadi fungsinya IF dalam Excel – bukan satu-satunya, terdapat beberapa lagi jenisnya yang secara automatik melakukan tindakan yang paling biasa, yang sangat memudahkan kehidupan seseorang. 

Sebahagian besarnya disebabkan oleh fungsi IF Hamparan Excel dianggap boleh diprogramkan. Ia lebih daripada sekadar kalkulator mudah. Jika anda memikirkannya, maka fungsinya IF adalah asas dalam sebarang jenis pengaturcaraan.

Jadi, jika anda belajar cara bekerja dengan sejumlah besar data dalam Excel, lebih mudah untuk mempelajari pengaturcaraan. Terima kasih kepada pengendali logik, kawasan ini benar-benar mempunyai banyak persamaan, walaupun Excel lebih kerap digunakan oleh akauntan. Tetapi mekanisme untuk bekerja dengan data sebahagian besarnya sama. 

Berfungsi di tangan kanan IF dan variasinya membolehkan anda menukar helaian Excel menjadi program lengkap yang boleh bertindak pada algoritma yang kompleks. Memahami bagaimana fungsi berfungsi IF ialah langkah pertama ke arah mempelajari makro – langkah seterusnya dalam kerja yang lebih fleksibel dengan hamparan. Tetapi ini sudah menjadi tahap yang lebih profesional.