Fungsi JIKA di Excel. Contoh (dengan beberapa kondisi)

Salah satu keuntungan utama dari spreadsheet Excel adalah kemampuan untuk memprogram fungsionalitas dokumen tertentu. Seperti yang diketahui kebanyakan orang dari pelajaran ilmu komputer sekolah, salah satu komponen utama yang memungkinkan Anda mempraktikkannya adalah operator logika. Salah satunya adalah operator IF, yang menyediakan eksekusi tindakan tertentu ketika kondisi tertentu terpenuhi. 

Misalnya, jika nilainya cocok dengan nilai tertentu, maka satu label akan ditampilkan di sel. Jika tidak, itu berbeda. Mari kita lihat alat yang efektif ini secara lebih rinci dalam praktiknya.

Fungsi IF di Excel (informasi umum)

Setiap program, meskipun kecil, pasti berisi urutan tindakan, yang disebut algoritma. Ini mungkin terlihat seperti ini:

1. Periksa seluruh kolom A untuk bilangan genap.
2. Jika ditemukan bilangan genap, tambahkan nilai ini dan itu.
3. Jika tidak ditemukan angka genap, maka tampilkan tulisan “not found”.
4. Periksa apakah angka yang dihasilkan genap. 
5. Jika ya, tambahkan ke semua bilangan genap yang dipilih di paragraf 1.

Dan bahkan jika ini hanya situasi hipotetis, yang tidak mungkin diperlukan dalam kehidupan nyata, pelaksanaan tugas apa pun harus menyiratkan adanya algoritme serupa. Sebelum menggunakan fungsi JIKA, Anda harus memiliki gagasan yang jelas di kepala Anda tentang hasil apa yang ingin Anda capai. 

Sintaks fungsi IF dengan satu syarat

Setiap fungsi di Excel dilakukan menggunakan rumus. Pola dimana data harus diteruskan ke suatu fungsi disebut sintaks. Dalam kasus operator IF, rumusnya akan dalam format ini.

=IF (ekspresi_logis, nilai_if_true, nilai_if_false)

Mari kita lihat sintaks lebih detail:

1. ekspresi Boolean. Ini adalah kondisi itu sendiri, kepatuhan atau ketidakpatuhan yang diperiksa oleh Excel. Informasi numerik dan tekstual dapat diperiksa.
2. Nilai_jika_benar. Hasil yang akan ditampilkan dalam sel jika data yang diperiksa memenuhi kriteria yang ditentukan.
3. nilai_if_false. Hasil yang ditampilkan dalam sel jika data yang diperiksa tidak sesuai dengan kondisi.

Berikut adalah contoh untuk kejelasan.

Di sini fungsinya membandingkan sel A1 dengan angka 20. Ini adalah paragraf pertama dari sintaks. Jika konten lebih besar dari nilai ini, nilai "lebih besar dari 20" ditampilkan di sel tempat rumus ditulis. Jika situasinya tidak sesuai dengan kondisi ini – “kurang dari atau sama dengan 20”.

Jika Anda ingin menampilkan nilai teks dalam sel, Anda harus menyertakannya dalam tanda kutip.

Berikut adalah situasi lain. Agar memenuhi syarat untuk mengikuti sesi ujian, siswa harus lulus sesi tes. Para siswa berhasil memenangkan kredit di semua mata pelajaran, dan sekarang yang terakhir tetap ada, yang ternyata sangat menentukan. Tugas kita adalah menentukan siswa mana yang diterima dalam ujian dan mana yang tidak.

Karena kita ingin memeriksa teks dan bukan angka, argumen pertama adalah B2=”kontra.”.

Sintaks Fungsi IF dengan Beberapa Kondisi

Seringkali, satu kriteria tidak cukup untuk memeriksa nilainya. Jika Anda perlu mempertimbangkan lebih dari satu opsi, Anda dapat membuat sarang fungsi IF satu ke yang lain. Akan ada beberapa fungsi bersarang.

Agar lebih jelas, berikut sintaksnya.

=IF(ekspresi_logis, nilai_if_true, IF(ekspresi_logis, nilai_if_true, nilai_if_false))

Dalam hal ini, fungsi akan memeriksa dua kriteria sekaligus. Jika kondisi pertama benar, nilai yang diperoleh sebagai hasil dari operasi dalam argumen pertama dikembalikan. Jika tidak, kriteria kedua diperiksa kepatuhannya.

Inilah contohnya.

Dan dengan bantuan rumus seperti itu (ditunjukkan pada tangkapan layar di bawah), Anda dapat menganalisis kinerja setiap siswa.

Seperti yang Anda lihat, satu kondisi lagi ditambahkan di sini, tetapi prinsipnya tidak berubah. Jadi Anda bisa mengecek beberapa kriteria sekaligus.

Cara memperluas fungsionalitas IF menggunakan operator AND dan OR

Dari waktu ke waktu ada situasi untuk segera memeriksa kepatuhan terhadap beberapa kriteria, dan tidak menggunakan operator bersarang logis, seperti pada contoh sebelumnya. Untuk melakukan ini, gunakan salah satu fungsi И atau fungsi OR tergantung pada apakah Anda perlu memenuhi beberapa kriteria sekaligus atau setidaknya salah satunya. Mari kita lihat lebih dekat kriteria ini.

Fungsi IF dengan kondisi AND

Terkadang Anda perlu memeriksa ekspresi untuk beberapa kondisi sekaligus. Untuk ini, fungsi AND digunakan, ditulis dalam argumen pertama dari fungsi IF. Cara kerjanya seperti ini: jika a sama dengan satu dan a sama dengan 2, nilainya akan menjadi c.

Fungsi IF dengan kondisi “ATAU”

Fungsi OR bekerja dengan cara yang sama, tetapi dalam kasus ini, hanya satu kondisi yang benar. Sebisa mungkin, hingga 30 kondisi dapat diperiksa dengan cara ini. 

Berikut adalah beberapa cara untuk menggunakan fungsi И и OR sebagai argumen fungsi IF.

Membandingkan data dalam dua tabel

Dari waktu ke waktu dimungkinkan untuk membandingkan dua tabel yang serupa. Misalnya, seseorang bekerja sebagai akuntan dan perlu membandingkan dua laporan. Ada tugas serupa lainnya, seperti membandingkan harga pokok barang dari batch yang berbeda, kemudian, penilaian siswa untuk periode yang berbeda, dan sebagainya.

Untuk membandingkan dua tabel, gunakan fungsi COUNTIF. Mari kita lihat lebih detail.

Katakanlah kita memiliki dua tabel yang berisi spesifikasi dari dua pengolah makanan. Dan kita perlu membandingkannya, dan menyoroti perbedaannya dengan warna. Ini dapat dilakukan dengan menggunakan pemformatan bersyarat dan fungsi COUNTIF. 

Meja kami terlihat seperti ini.

Kami memilih kisaran yang sesuai dengan karakteristik teknis pengolah makanan pertama.

Setelah itu, klik menu berikut: Conditional formatting – create a rule – gunakan rumus untuk menentukan sel yang diformat.

Dalam bentuk formula untuk memformat, kami menulis fungsinya = COUNTIF (rentang untuk membandingkan; sel pertama dari tabel pertama)=0. Tabel dengan fitur food processor kedua digunakan sebagai rentang perbandingan.

Anda perlu memastikan bahwa alamatnya mutlak (dengan tanda dolar di depan nama baris dan kolom). Tambahkan =0 setelah rumus sehingga Excel mencari nilai yang tepat.

Setelah itu, Anda perlu mengatur format sel. Untuk melakukan ini, di sebelah sampel, Anda perlu mengklik tombol "Format". Dalam kasus kami, kami menggunakan isian, karena ini adalah yang paling nyaman untuk tujuan ini. Tetapi Anda dapat memilih format apa pun yang Anda inginkan.

Kami telah menetapkan nama kolom sebagai rentang. Ini jauh lebih nyaman daripada memasukkan rentang secara manual.

Fungsi SUMIF di Excel

Sekarang mari kita beralih ke fungsi IF, yang akan membantu mengganti dua titik algoritma sekaligus. Yang pertama adalah RINGKASAN, yang menambahkan dua angka yang memenuhi kondisi tertentu. Misalnya, kita dihadapkan pada tugas menentukan berapa banyak uang yang harus dibayarkan per bulan kepada semua penjual. Untuk ini perlu.

1. Tambahkan baris dengan total pendapatan semua penjual dan klik sel yang akan berisi hasil setelah memasukkan rumus. 
2. Kami menemukan tombol fx, yang terletak di sebelah baris rumus. Selanjutnya, sebuah jendela akan muncul di mana Anda dapat menemukan fungsi yang diperlukan melalui pencarian. Setelah memilih operator, Anda perlu mengklik tombol "OK". Tetapi input manual selalu memungkinkan.
   

   

3. Selanjutnya, jendela untuk memasukkan argumen fungsi akan muncul. Semua nilai dapat ditentukan di bidang yang sesuai, dan rentang dapat dimasukkan melalui tombol di sebelahnya.
   

   

4. Argumen pertama adalah rentang. Di sini Anda memasukkan sel yang ingin Anda periksa kepatuhannya dengan kriteria. Jika kita berbicara tentang kita, ini adalah posisi karyawan. Masukkan rentang D4:D18. Atau cukup pilih sel yang diinginkan.
5. Di bidang "Kriteria", masukkan posisi. Dalam kasus kita – “penjual”. Sebagai rentang penjumlahan, kami menunjukkan sel-sel tempat gaji karyawan terdaftar (ini dilakukan secara manual dan memilihnya dengan mouse). Klik "OK", dan kami mendapatkan upah yang dihitung selesai dari semua karyawan yang menjadi penjual.

Setuju bahwa itu sangat nyaman. Bukankah begitu?

Fungsi SUMIFS di Excel

Fungsi ini memungkinkan Anda untuk menentukan jumlah nilai yang memenuhi beberapa kondisi. Misalnya, kami diberi tugas untuk menentukan total gaji semua manajer yang bekerja di cabang selatan perusahaan.

Tambahkan baris di mana hasil akhirnya akan berada, dan masukkan rumus ke dalam sel yang diinginkan. Untuk melakukan ini, klik ikon fungsi. Sebuah jendela akan muncul di mana Anda perlu menemukan fungsinya RINGKASAN. Selanjutnya, pilih dari daftar dan jendela akrab dengan argumen terbuka. Tetapi jumlah argumen ini sekarang berbeda. Rumus ini memungkinkan untuk menggunakan jumlah kriteria yang tidak terbatas, tetapi jumlah minimum argumen adalah lima. 

Hanya lima yang dapat ditentukan melalui dialog input argumen. Jika Anda membutuhkan lebih banyak kriteria, maka mereka harus dimasukkan secara manual sesuai dengan logika yang sama seperti dua yang pertama.

Mari kita lihat argumen utama secara lebih rinci:

1. Rentang penjumlahan. Sel yang akan dijumlahkan.
2. Rentang kondisi 1 – rentang yang akan diperiksa kesesuaiannya dengan kriteria tertentu. 
3. Kondisi 1 adalah kondisi itu sendiri.
4. Rentang kriteria 2 adalah rentang kedua yang akan diperiksa terhadap kriteria.
5. Kondisi 2 adalah kondisi kedua.

Logika lebih lanjut serupa. Akibatnya, kami menentukan upah semua manajer Cabang Selatan.

Fungsi COUNTIF di Excel

Jika Anda perlu menentukan berapa banyak sel yang termasuk dalam kriteria tertentu, gunakan fungsi COUNTIF. Katakanlah kita perlu memahami berapa banyak tenaga penjualan yang bekerja di organisasi ini:

1. Pertama, tambahkan baris yang berisi jumlah penjual. Setelah itu, Anda perlu mengklik sel tempat hasilnya akan ditampilkan.
2. Setelah itu, Anda perlu mengklik tombol "Sisipkan Fungsi", yang dapat ditemukan di tab "Rumus". Sebuah jendela akan muncul dengan daftar kategori. Kita perlu memilih item "Daftar abjad lengkap". Dalam daftar, kami tertarik dengan rumus COUNTIF. Setelah kita memilihnya, kita perlu mengklik tombol "OK".
   

   

3. Setelah itu, kami memiliki jumlah tenaga penjualan yang dipekerjakan di organisasi ini. Itu diperoleh dengan menghitung jumlah sel di mana kata "penjual" ditulis. Semuanya sederhana. 

Fungsi COUNTSLIM di Excel

Mirip dengan rumus RINGKASAN, rumus ini menghitung jumlah sel yang cocok dengan beberapa kondisi. Sintaksnya mirip tetapi sedikit berbeda dari rumusnya RINGKASAN:

1. Rentang kondisi 1. Ini adalah rentang yang akan diuji terhadap kriteria pertama.
2. Kondisi 1. Langsung kriteria pertama.
3. Rentang Kondisi 2. Ini adalah rentang yang akan diuji terhadap kriteria kedua. 
4. Kondisi 2.
5. Kondisi jangkauan3.

Dan sebagainya.

Jadi fungsinya IF di Excel – bukan satu-satunya, ada beberapa varietas lainnya yang secara otomatis melakukan tindakan paling umum, yang sangat menyederhanakan kehidupan seseorang. 

Sebagian besar karena fungsinya IF Spreadsheet Excel dianggap dapat diprogram. Ini lebih dari sekadar kalkulator sederhana. Jika Anda memikirkannya, maka fungsinya IF adalah landasan dalam segala jenis pemrograman.

Jadi, jika Anda mempelajari cara bekerja dengan sejumlah besar data di Excel, akan jauh lebih mudah untuk mempelajari pemrograman. Berkat operator logika, area ini benar-benar memiliki banyak kesamaan, meskipun Excel lebih sering digunakan oleh akuntan. Tetapi mekanisme untuk bekerja dengan data sebagian besar sama. 

Fungsi di tangan kanan IF dan variasinya memungkinkan Anda mengubah lembar Excel menjadi program lengkap yang dapat bertindak berdasarkan algoritme kompleks. Memahami cara kerja fungsi IF adalah langkah pertama untuk mempelajari makro – langkah selanjutnya dalam pekerjaan yang lebih fleksibel dengan spreadsheet. Tapi ini sudah tingkat yang lebih profesional.