Cara Fungsi Sarang Beberapa IF di Excel

01 06

Cara Kerja Fungsi Bertingkat Jarang

Kegunaan fungsi IF dapat diperluas dengan memasukkan atau menumpuk beberapa fungsi IF di dalam satu sama lain.

Fungsi IF bertingkat meningkatkan jumlah kemungkinan kondisi yang dapat diuji dan meningkatkan jumlah tindakan yang dapat diambil untuk menangani hasil ini.

Versi terbaru Excel memungkinkan 64 fungsi IF bersarang di dalam satu sama lain, sementara Excel 2003 dan sebelumnya hanya diizinkan tujuh.

Tutorial Fungsi Sarang IF

Seperti yang ditunjukkan pada gambar di atas, tutorial ini hanya menggunakan dua fungsi IF untuk membuat rumus berikut yang menghitung jumlah pengurangan tahunan untuk karyawan berdasarkan gaji tahunan mereka.

Rumus yang digunakan dalam contoh ditunjukkan di bawah ini. Fungsi IF yang bersarang bertindak sebagai argumen value_if_false untuk fungsi IF pertama.

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

Bagian-bagian berbeda dari rumus dipisahkan dengan koma dan melaksanakan tugas-tugas berikut:

1. Bagian pertama, D7, memeriksa untuk melihat apakah gaji karyawan kurang dari $ 30.000
2. Jika ya, bagian tengah, $ D $ 3 * D7 , mengalikan gaji dengan tingkat pemotongan 6%
3. Jika tidak, fungsi IF kedua: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) menguji dua kondisi lebih lanjut:
   • D7> = 50000 , periksa untuk melihat apakah gaji karyawan lebih dari atau sama dengan $ 50.000
   • Jika ya, $ D $ 5 * D7 mengalikan gaji dengan tingkat pemotongan 10%
   • Jika tidak, $ D $ 4 * D7 mengalikan gaji dengan tingkat pemotongan 8%

Memasukkan Data Tutorial

Masukkan data ke sel C1 ke E6 dari lembar kerja Excel seperti yang terlihat pada gambar di atas.

Satu-satunya data yang tidak dimasukkan pada titik ini adalah fungsi IF itu sendiri yang terletak di sel E7.

Bagi mereka yang tidak merasa ingin mengetik, data dan instruksi untuk menyalinnya ke Excel tersedia di tautan ini.

Catatan: Petunjuk untuk menyalin data tidak menyertakan langkah pemformatan untuk lembar kerja.

Ini tidak akan mengganggu menyelesaikan tutorial. Lembar kerja Anda mungkin terlihat berbeda dari contoh yang ditampilkan, tetapi fungsi IF akan memberi Anda hasil yang sama.

02 06

Memulai Fungsi IF Bersarang

Meskipun dimungkinkan untuk cukup memasukkan rumus lengkap

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

ke sel E7 dari lembar kerja dan membuatnya bekerja, seringkali lebih mudah untuk menggunakan kotak dialog fungsi untuk memasukkan argumen yang diperlukan.

Menggunakan kotak dialog agak sulit ketika memasuki fungsi bertingkat karena fungsi bertingkat harus diketik. Kotak dialog kedua tidak dapat dibuka untuk memasukkan rangkaian argumen kedua.

Untuk contoh ini, fungsi IF tersarang akan dimasukkan ke dalam baris ketiga kotak dialog sebagai argumen Value_if_false .

• Karena lembar kerja menghitung deduksi tahunan untuk beberapa karyawan, rumus tersebut pertama-tama akan dimasukkan ke dalam satu sel E7 menggunakan referensi sel absolut untuk tingkat deduksi dan kemudian menyalinnya ke sel E8: E11.

Langkah-langkah Tutorial

1. Klik pada sel E7 untuk membuatnya menjadi sel aktif. - lokasi untuk rumus IF tersarang.
2. Klik pada tab Rumus pada pita
3. Klik pada ikon Logical buka daftar drop-down fungsi.
4. Klik IF dalam daftar untuk memunculkan kotak dialog fungsi.

Data yang dimasukkan ke dalam garis kosong di kotak dialog membentuk argumen fungsi IF.

Argumen-argumen ini memberi tahu fungsi kondisi yang sedang diuji dan tindakan apa yang harus diambil jika kondisinya benar atau salah.

Opsi Shortcut Tutorial

Untuk melanjutkan contoh ini, Anda bisa

• masukkan argumen ke dalam kotak dialog seperti yang ditunjukkan pada gambar di atas dan kemudian lompat ke langkah terakhir yang mencakup menyalin rumus ke baris 7 hingga 10;
• atau ikuti hingga lima halaman berikutnya yang memberikan instruksi dan penjelasan terperinci untuk memasukkan tiga argumen.

03 06

Memasukkan Argumen Logical_test

Argumen Logical_test selalu merupakan perbandingan antara dua item data. Data ini bisa berupa angka, referensi sel , hasil rumus, atau bahkan data teks.

Untuk membandingkan dua nilai, Logical_test menggunakan operator perbandingan antara nilai-nilai.

Dalam contoh ini, ada tiga tingkat gaji yang menentukan deduksi tahunan karyawan.

• kurang dari $ 30.000
• antara $ 30.000 dan $ 49.999
• $ 50.000 atau lebih

Satu fungsi IF dapat membandingkan dua level, tetapi level gaji ketiga membutuhkan penggunaan fungsi IF kedua yang disarangkan.

Perbandingan pertama adalah antara gaji tahunan karyawan, yang terletak di sel D, dengan ambang gaji $ 30.000.

Karena tujuannya adalah untuk menentukan apakah D7 kurang dari $ 30.000, operator Less Than "<" digunakan di antara nilai-nilai.

Langkah-langkah Tutorial

1. Klik pada baris Logical_test di kotak dialog
2. Klik pada sel D7 untuk menambahkan referensi sel ini ke baris Logical_test
3. Tekan kurang dari tombol "<" pada keyboard
4. Ketik 30000 setelah simbol kurang dari
5. Tes logis yang lengkap harus dibaca: D7 <30000

Catatan: Jangan masukkan tanda dolar ($) atau pemisah koma (,) dengan 30000.

Pesan kesalahan yang tidak valid akan muncul di akhir baris Logical_test jika salah satu dari simbol ini dimasukkan bersama dengan data.

04 06

Memasukkan Value_if_true Argument

Argumen Value_if_true memberitahukan fungsi IF apa yang harus dilakukan ketika Logical_test benar.

Argumen Value_if_true dapat berupa rumus, blok teks, nilai , referensi sel , atau sel dapat dibiarkan kosong.

Dalam contoh ini, ketika data dalam sel D7 kurang dari $ 30.000. Excel mengalikan gaji tahunan karyawan di sel D7 dengan tingkat pengurangan 6% yang terletak di sel D3.

Relatif vs. Referensi Sel Absolut

Biasanya, ketika formula disalin ke sel lain referensi sel relatif dalam rumus berubah untuk mencerminkan lokasi baru rumus. Ini memudahkan penggunaan rumus yang sama di beberapa lokasi.

Kadang-kadang, bagaimanapun, memiliki referensi sel berubah ketika suatu fungsi disalin akan menghasilkan kesalahan.

Untuk mencegah kesalahan ini, referensi sel dapat dibuat Absolute yang mencegahnya berubah ketika disalin.

Referensi sel absolut dibuat dengan menambahkan tanda dolar di sekitar referensi sel reguler, seperti $ D $ 3 .

Menambahkan tanda dolar mudah dilakukan dengan menekan tombol F4 pada keyboard setelah referensi sel dimasukkan ke dalam kotak dialog.

Dalam contoh, tingkat deduksi yang terletak di sel D3 dimasukkan sebagai referensi sel absolut ke dalam baris Value_if_true dari kotak dialog.

Langkah-langkah Tutorial

1. Klik pada baris Value_if_true di kotak dialog
2. Klik pada sel D3 di lembar kerja untuk menambahkan referensi sel ini ke baris Value_if_true
3. Tekan tombol F4 pada keyboard untuk membuat D3 referensi sel absolut ($ D $ 3)
4. Tekan tombol tanda bintang ( * ) pada keyboard - tanda bintang adalah simbol perkalian dalam Excel
5. Klik pada sel D7 untuk menambahkan referensi sel ini ke baris Value_if_true
6. Garis Value_if_true selesai harus membaca: $ D $ 3 * D7

Catatan : D7 tidak dimasukkan sebagai referensi sel absolut karena perlu diubah saat rumus disalin ke sel E8: E11 untuk mendapatkan jumlah pengurangan yang benar untuk setiap karyawan.

05 06

Memasukkan Fungsi IF Bersarang sebagai argumen Value_if_false

Biasanya, argumen Value_if_false memberitahukan fungsi IF apa yang harus dilakukan ketika Logical_test salah, tetapi dalam kasus ini, fungsi IF tersarang dimasukkan sebagai argumen ini.

Dengan demikian, hasil berikut terjadi:

• Argumen Logical_test dalam fungsi IF tersarang ( D7> = 50000 ) menguji semua gaji yang tidak kurang dari $ 30.000.
• Untuk gaji yang lebih besar dari atau sama dengan $ 50.000, argumen Value_if_true mengalikannya dengan tingkat deduksi 10% yang terletak di sel D5.
• Untuk gaji yang tersisa - gaji yang lebih besar dari $ 30.000 tetapi kurang dari $ 50.000 - argumen Value_if_false mengalikannya dengan tingkat deduksi 8% yang terletak di sel D4.

Langkah-langkah Tutorial

Seperti yang disebutkan di awal tutorial, kotak dialog kedua tidak dapat dibuka untuk memasukkan fungsi bertingkat sehingga harus diketik ke dalam Value_if_false line.

Catatan: fungsi bertingkat tidak dimulai dengan tanda yang sama - tetapi dengan nama fungsi.

1. Klik pada Value_if_false line di kotak dialog
2. Masukkan fungsi IF berikut
   IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Klik OK untuk menyelesaikan fungsi IF dan tutup kotak dialog
4. Nilai $ 3,678.96 seharusnya muncul di sel E7 *
5. Ketika Anda mengklik pada sel E7, fungsi lengkap
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   muncul di bilah rumus di atas lembar kerja

* Karena R. Holt menghasilkan lebih dari $ 30.000 tetapi kurang dari $ 50.000 per tahun, rumus $ 45,987 * 8% digunakan untuk menghitung deduksi tahunannya.

Jika semua langkah telah diikuti, contoh Anda saat ini harus cocok dengan gambar pertama dalam artikel ini.

Langkah terakhir melibatkan menyalin rumus IF ke sel E8 hingga E11 menggunakan pegangan isian untuk melengkapi lembar kerja.

06 06

Menyalin Fungsi IF Bersarang menggunakan Penanganan Isi

Untuk melengkapi lembar kerja, rumus yang berisi fungsi IF tersarang perlu disalin ke sel E8 hingga E11.

Saat fungsi tersebut disalin, Excel akan memperbarui referensi sel relatif untuk mencerminkan lokasi baru fungsi sambil menjaga referensi sel absolut tetap sama.

Satu cara mudah untuk menyalin rumus di Excel adalah dengan Penanganan Isi.

Langkah-langkah Tutorial

1. Klik pada sel E7 untuk membuatnya menjadi sel aktif .
2. Tempatkan pointer mouse di atas kotak hitam di sudut kanan bawah sel yang aktif. Penunjuk akan berubah menjadi tanda tambah "+".
3. Klik tombol kiri mouse dan seret gagang isian ke bawah ke sel E11.
4. Lepaskan tombol mouse. Sel E8 hingga E11 akan diisi dengan hasil rumus seperti yang ditunjukkan pada gambar di atas.