Tetapkan kondisi ex-fur-tree. Bagaimana di excel menggunakan fungsi nested if () menghitung bonus penjualan

IF () fungsi , versi bahasa Inggris JIKA (), digunakan saat memeriksa kondisi. Contohnya, \u003d IF (A1\u003e 100; "Anggaran terlampaui"; "Oke!")... Bergantung pada nilai di dalam sel A1 hasil rumus akan menjadi "Anggaran Terlampaui" atau "OK!"

Fungsi IF () adalah salah satu fungsi yang paling umum digunakan.

Sintaks fungsi

IF (boolean_test, value_if_true, [value_if_false])

Log_Expression - nilai atau ekspresi apa pun yang mengambil nilai BENAR atau SALAH.
\u003d IF (A1\u003e \u003d 100; "Anggaran terlampaui"; "Oke!")
Itu. jika di dalam sel A1 berisi nilai yang lebih besar dari atau sama dengan 100, maka rumus akan dikembalikan BAIK!,dan jika tidak, maka Anggaran terlampaui.

Rumus dapat digunakan sebagai argumen untuk fungsi tersebut, misalnya:
\u003d IF (A1\u003e 100, SUM (B1: B10), SUM (C1: C10))
Itu. jika di dalam sel A1 berisi nilai\u003e 100, kemudian penjumlahan dilakukan di atas kolom B , dan jika kurang, maka di sepanjang kolom DARI .

IF bertingkat

Dalam EXCEL 2007, sebagai nilai argumen value_if_true dan nilai_jika_false Anda bisa menggunakan hingga 64 fungsi IF () yang bertumpuk di dalam satu sama lain untuk membuat pemeriksaan yang lebih kompleks.
\u003d IF (A1\u003e \u003d 100; "Anggaran Terlampaui"; IF (A1\u003e \u003d 90; "Proyek Besar"; IF (A1\u003e \u003d 50; "Proyek Sedang"; "Proyek Kecil")))

LIHAT (A1; (0; 50; 90; 100); ("Proyek kecil"; "Proyek menengah"; "Proyek besar"; "Anggaran terlampaui"))

VLOOKUP (A1; A3: B6; 2)

Untuk fungsi VLOOKUP (), Anda perlu membuat dalam rentang A3: B6 tabel nilai:

Jika Anda ingin menampilkan teks yang berbeda jika ada di dalam sel A1 nilai negatif, nilai positif, atau 0, maka Anda dapat menulis rumus berikut:

LOOKUP (A1; (- 1E + 307; 0; 1E-307); ("<0";"=0";">0"})

atau, jika Anda ingin menampilkan rumus dan bukan nilai teks, Anda bisa menggunakan referensi ke rentang sel (berisi rumus)

LOOKUP (A24; (- 1E + 307; 0; 1E-307); A27: A29) (lihat contoh file)

Argumen ketiga dihilangkan [value_if_false]

Argumen ketiga dari fungsi ini opsional, jika Anda menghilangkannya, fungsi akan mengembalikan FALSE (jika kondisinya tidak terpenuhi).
\u003d IF (A1\u003e 100; "Anggaran terlampaui")
Jika di dalam sel A1 berisi nilai 1, rumus di atas akan mengembalikan FALSE.

Angka dimasukkan sebagai ganti TRUE atau FALSE di argumen pertama

Karena nilai FALSE sama dengan 0, maka rumusnya
\u003d IF (0, "Melebihi anggaran"; "Oke!")
atau (jika di dalam sel A1 berisi nilai 0)
\u003d IF (A1, "Melebihi anggaran"; "Oke!")
akan kembali BAIK!

Jika di dalam sel A1 angka apa pun selain 0 ditemukan, maka rumus akan kembali Anggaran terlampaui... Pendekatan ini nyaman saat menguji persamaan nilai ke nol.

Menautkan fungsi IF () dengan fungsi lain menggunakan kondisi

EXCEL juga berisi fungsi lain yang dapat Anda gunakan untuk menganalisis data menggunakan kondisi. Misalnya, fungsi COUNTIF () digunakan untuk menghitung jumlah kemunculan angka dalam rentang sel, dan fungsi SUMIF () digunakan untuk menambahkan nilai yang memenuhi kondisi tertentu.

Fungsi IF (), sebagai alternatif, juga dapat digunakan untuk menghitung dan menambahkan nilai menggunakan kondisi. Contoh ilustratif diberikan di bawah ini.

Biarkan data berada dalam jangkauan A6: A11 (lihat contoh file)


Mari kita hitung jumlah nilai yang lebih besar dari 10 menggunakan fungsi SUMIF () dengan menulis \u003d SUMIF (A6: A11, "\u003e 10")... Hasil serupa (23) dapat diperoleh dengan menggunakan
\u003d SUM (IF (A6: A11\u003e 10, A6: A11))
(untuk memasukkan rumus ke dalam sel, bukan MEMASUKKAN perlu ditekan CTRL + SHIFT + ENTER)

Sekarang mari kita hitung jumlah kemunculan angka yang lebih besar dari 10 dalam rentang sel A6: A11 \u003d COUNTIF (A6: A11, "\u003e 10")... Hasil serupa (2) dapat diperoleh dengan menggunakan
\u003d JUMLAH (JIKA (A6: A11\u003e 10, A6: A11))

Sekarang prinsipnya jelas, rumus lain dengan kondisi dapat dibuat menggunakan fungsi IF (). Misalnya, mencari nilai minimum di antara angka yang lebih besar dari 10:
\u003d MIN (JIKA (A6: A11\u003e 10, A6: A11))

Kemampuan untuk melakukan pemeriksaan logis pada sel adalah alat yang ampuh. Anda akan menemukan kegunaan yang tak ada habisnya untuk JIKA SEBUAH() dalam dokumen sehari-hari Anda. Masalahnya adalah seringkali pemeriksaan yang diperlukan berada di luar kemampuan fungsi sederhana. JIKA SEBUAH() ... Ini terjadi jika perlu untuk memeriksa dua atau lebih kondisi sebelum menerima hasilnya.

Untuk kasus seperti itu, Excel menyediakan beberapa opsi: gunakan JIKA SEBUAH() di dalam yang lain JIKA SEBUAH(), fungsi DAN() dan IL DAN()... Selanjutnya, kita akan mengenal metode-metode ini.

Menggunakan JIKA SEBUAH() di dalam fungsi lain JIKA SEBUAH()

Mari pertimbangkan varian berdasarkan fungsi yang dipelajari sebelumnya \u003d IF (A1\u003e 1000; "banyak"; "sedikit"). Bagaimana jika Anda perlu mencetak string lain ketika angka di A1, misalnya, lebih besar dari 10.000? Dengan kata lain, jika A1\u003e 1000 benar, Anda akan ingin menjalankan tes lain dan melihat apakah A1\u003e 10000 benar. Anda dapat membuat varian seperti itu dengan menerapkan fungsi kedua JIKA SEBUAH() di dalam yang pertama sebagai argumen, nilai _if_true: \u003d IF (A1\u003e 1000; IF (A1\u003e 10000; "very much"; "many"); "little").

Jika A1\u003e 1000 benar, fungsi lain dipicu JIKA SEBUAH(), yang mengembalikan nilai "sangat banyak" saat A1\u003e 10000. Jika dalam kasus ini A1 kurang dari atau sama dengan 10000, nilai "a lot" dikembalikan. Jika selama pemeriksaan pertama nomor A1 kurang dari 1000, nilai "kecil" akan ditampilkan.

Perhatikan bahwa Anda juga dapat menjalankan pemeriksaan kedua jika yang pertama salah (yaitu, dalam argumen value_if_false dari fungsi ifo). Berikut adalah contoh kecil yang mengembalikan nilai "sangat kecil" jika angka di A1 kurang dari 100: \u003d IF (A1\u003e 1000; "many"; IF (A1<100;"очень мало"; "мало")) .

Perhitungan bonus penjualan

Contoh yang baik untuk menggunakan satu cek di dalam cek lain adalah menghitung bonus penjualan kepada staf. yang bekerja di Klub - hotel Heliopark Talasso, Zvenigorod. Dalam kasus ini, jika nilainya X, Anda menginginkan satu hasil, jika Y adalah hasil lain, jika Z
- ketiga. Misalnya, dalam kasus menghitung bonus untuk penjualan yang sukses, ada tiga opsi yang mungkin:

  1. Penjual belum mencapai nilai yang direncanakan, bonusnya 0.
  2. Penjual melebihi nilai yang direncanakan kurang dari 10%, bonusnya 1.000 rubel.
  3. Penjual melebihi nilai yang direncanakan lebih dari 10%, bonusnya sama dengan 10.000 rubel.

Berikut rumus untuk menghitung contoh tersebut: \u003d IF (E3\u003e 0; IF (E3\u003e 0.1; 10000; 1000); 0). Jika nilai di E3 negatif, maka 0 dikembalikan (tidak ada bonus). Jika hasilnya positif, diperiksa apakah lebih dari 10%, dan tergantung pada ini, 1.000 atau 10.000 dikeluarkan. 4.17 menunjukkan contoh cara kerja rumus.

AND ()

Seringkali perlu untuk hanya memeriksa ketepatan dua kondisi pada saat yang bersamaan. Misalnya, bonus dibayarkan hanya jika penjualan produk tertentu telah melebihi rencana dan total penjualan telah melampaui rencana. Jika salah satu dari ketentuan ini tidak terpenuhi (atau kedua syarat tersebut), maka premi tidak dibayarkan. Dalam logika Boolean, ini disebut logika AND karena kedua ketentuan harus benar agar seluruh ekspresi menjadi benar.

Di Excel, ekspresi boolean DAN diproses menggunakan fungsi tersebut DAN(): AND (boolean_value1; boolean_value2; ...). Setiap argumen adalah nilai boolean untuk diuji. Anda dapat memasukkan argumen sebanyak yang Anda suka.

Sekali lagi, mari perhatikan bagaimana fungsinya bekerja:

  • Jika semua ekspresi mengembalikan TRUE (atau bilangan positif apa pun), DAN() mengembalikan TRUE.
  • Jika satu atau lebih argumen mengembalikan FALSE (atau 0), DAN() mengembalikan FALSE.

Paling sering DAN() diterapkan di dalam suatu fungsi JIKA SEBUAH()... Dalam hal ini, ketika semua argumen ada di dalam DAN() akan mengembalikan TRUE, fungsi JIKA SEBUAH() akan mengikuti nilai cabangnya jika benar. Jika satu atau lebih ekspresi dalam DAN() akan mengembalikan FALSE, fungsinya JIKA SEBUAH() akan mengikuti nilai cabang value_if_false.

Berikut adalah contoh kecilnya: \u003d IF (AND (C2\u003e 0; B2\u003e 0); 1000; "no bonus"). Jika nilai dalam B2 lebih besar dari nol dan nilai dalam C2 lebih besar dari nol, rumus akan mengembalikan 1000, jika tidak, baris "tidak ada bonus" akan ditampilkan.

Memisahkan nilai ke dalam kategori

Penggunaan yang berguna dari fungsi and () adalah untuk mengkategorikan berdasarkan nilai. Misalnya, Anda memiliki tabel dengan hasil polling atau voting, dan Anda ingin membagi semua suara ke dalam kategori sesuai dengan rentang usia berikut: 18-34.35-49, 50-64.65 atau lebih. Dengan asumsi bahwa usia responden berada di sel B9, argumen fungsi berikut dan () melakukan pemeriksaan logis untuk usia yang termasuk dalam rentang: \u003d AND (B9\u003e \u003d 18; B9


Jika jawaban orang tersebut ada di sel C9, rumus berikut akan mengeluarkan hasil pemungutan suara orang tersebut jika pemeriksaan kepatuhan dengan kelompok usia 18-34 dipicu: \u003d IF (AND (B9\u003e \u003d 18; B9

  • 35-49: \u003d IF (AND (B9\u003e \u003d 35; B9
  • 50-64: \u003d IF (AND (B9\u003e \u003d 50; B9
  • 65+: \u003d IF (B9\u003e \u003d 65; C9; "")

OR ()

Ada situasi ketika Anda membutuhkan keputusan hanya ketika salah satu kondisinya positif (BENAR). Misalnya, Anda mungkin memutuskan untuk membayar bonus kepada karyawan saat total penjualan melebihi target atau saat penjualan item individu lebih tinggi dari perkiraan. Dalam logika boolean, ini disebut boolean ATAU.

Kondisi seperti itu diperiksa di Excel menggunakan fungsi ATAU(): ATAU (boolean1, boolean2, ...). Setiap argumen adalah nilai boolean untuk diuji. Anda dapat memasukkan argumen sebanyak yang Anda suka. Hasil pekerjaan ATAU() tergantung pada kondisi berikut:

  • Jika satu atau beberapa argumen mengembalikan TRUE (bilangan positif apa pun), ATAU() mengembalikan TRUE.
  • Jika semua argumen mengembalikan FALSE (nol), hasilnya adalah ATAU() akan SALAH.

Sebaik DAN(), paling sering fungsinya ATAU() digunakan di dalam cek JIKA SEBUAH()... Dalam hal ini, ketika salah satu argumen di dalam ATAU() akan mengembalikan TRUE, fungsi JIKA SEBUAH() akan mengikuti value_if_true cabangnya. Jika semua ekspresi dalam ATAU() akan mengembalikan FALSE, fungsinya JIKA SEBUAH() akan mengikuti cabang nilai_jika_false... Berikut adalah contoh kecilnya: \u003d IF (OR (C2\u003e 0; B2\u003e 0); 1000; "no bonus").

Jika ada angka positif di salah satu sel (C2 atau B2), fungsi akan mengembalikan 1000. Hanya jika kedua nilai negatif (atau sama dengan nol), fungsi akan mengembalikan string "tidak ada bonus".

Artikel ini akan berfokus pada fungsi "Jika" dari editor spreadsheet Excel. Ini berkaitan dengan kemampuan logis aplikasi. Fungsi ini dapat dikaitkan dengan yang paling umum, yang sering digunakan selama operasi.


Fitur utama

DI program Excel rumus "Jika" memungkinkan berbagai jenis tugas yang mengharuskan Anda membandingkan nilai tertentu dan mendapatkan hasil. Solusi ini memungkinkan untuk menggunakan algoritma percabangan, membuat pohon keputusan.

Contoh Penerapan

Fungsinya terlihat seperti: \u003d IF (task; true; false). Bagian pertama adalah ekspresi boolean. Ini bisa muncul sebagai frase atau angka. Misalnya, "10" atau "tanpa PPN". Parameter ini harus diisi. Benar adalah nilai yang ditampilkan sebagai hasil jika ekspresi benar. False merepresentasikan data yang akan dikembalikan jika tugasnya salah.

Kesetaraan parameter dua sel

Untuk lebih memahami kapabilitas fungsi If di Excel, Anda perlu memberikan contoh. Oleh karena itu, lebih lanjut ada baiknya mulai mempertimbangkannya. Anda harus memasukkan nilai 8 di sel C1. Setelah itu, di bidang di alamat D1, Anda perlu memasukkan rumus dalam bentuk: \u003d IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Contoh lain dapat dipertimbangkan. Ada beberapa siswa dan nilai mereka diperoleh untuk ujian. Data-datanya adalah sebagai berikut: 5, 4, serta 3 dan 2. Menurut ketentuan tugas, diharapkan untuk membuat untuk setiap siswa teks komentar “lulus” atau “tidak lulus”. Jadi, jika seorang siswa mendapat nilai tiga atau lebih tinggi, dia dianggap telah lulus ujian. Jika nilainya kurang dari 3, siswa tersebut gagal melakukannya dengan baik. Untuk mengatasi masalah seperti itu, tulis rumus berikut: \u003d IF (C1<3; «не справился»; «сдал»).

Program mulai membandingkan hasil setiap siswa. Jika indikatornya kurang dari tiga, tulisan "gagal" akan muncul di sel yang sesuai. Jika nilainya 3 atau lebih tinggi, di kolom wajib Anda dapat melihat komentar bahwa siswa tersebut tidak lulus ujian. Perlu diperhatikan bahwa komentar teks harus selalu diapit tanda kutip. Saat menggunakan fungsi ini, Anda dapat menggunakan operator perbandingan berikut:< >, =, >, <, >=, <=.

Contoh menggunakan kondisi "ATAU", "DAN"

Penting untuk mempertimbangkan lebih lanjut kemungkinan logis dari aplikasi ini. Fungsi If dapat digabungkan dengan operator pembanding. Ini adalah parameter berikut:

"ATAU";
"DAN".

Penting untuk menunjukkan kondisi penting di Excel: ketika nilai siswa sama dengan atau kurang dari 5, tetapi lebih dari 3. Dalam kasus ini, komentar harus ditampilkan: "lulus" atau "tidak". Dengan demikian, hanya siswa yang mendapat lima dan empat yang lulus. Untuk menulis tugas ini di editor spreadsheet, Anda harus menerapkan rumus khusus. Ini terlihat seperti ini: \u003d IF (AND (A1<=5; A1>3); "Passes", "no").

Jika Anda mempertimbangkan contoh yang lebih kompleks, Anda perlu menggunakan "ATAU" atau "DAN". Dengan demikian, Anda dapat membiasakan diri menerapkan rumus di Excel jika ada beberapa kondisi dalam pekerjaan. Misalnya: \u003d IF (OR (A1 \u003d 5; A1 \u003d 10); 100; 0). Dalam kasus ini, kita dapat menyimpulkan bahwa ketika nilai dalam sel A1 adalah 5 atau 10, program akan menampilkan hasil 100. Jika tidak, hasilnya 0. Dimungkinkan untuk menggunakan operator ini untuk menemukan solusi untuk masalah yang lebih kompleks.

Misalnya, dalam database Anda perlu menghitung debitur yang perlu membayar lebih dari 10.000 rubel. Anda dapat menetapkan kondisi bahwa pinjaman tersebut belum dilunasi selama lebih dari enam bulan, yaitu enam bulan. Berkat fungsi "If" dari editor spreadsheet Excel, Anda dapat secara otomatis mendapatkan tanda "klien masalah" di sebelah nama yang sesuai. Misalkan sel A1 berisi data yang menunjukkan jangka waktu hutang (bulan). Bidang B1 berisi jumlahnya.

Dalam kasus ini, rumus disajikan dalam bentuk berikut: \u003d IF (AND (A1\u003e \u003d 6; B1\u003e 10000); "problem client"; ""). Jadi, jika seseorang diidentifikasi memenuhi kondisi yang ditentukan, program akan menampilkan komentar yang sesuai di depan namanya. Untuk anggota lain dalam daftar, sel ini akan tetap kosong.

Anda juga dapat melihat contoh saat situasinya kritis. Komentar yang sesuai harus dimasukkan. Hasilnya, rumusnya adalah sebagai berikut: \u003d IF (OR (A1\u003e \u003d 6; B1\u003e 10000); "situasi kritis"; ""). Namun, jika program mendeteksi kecocokan untuk setidaknya satu parameter, catatan terkait akan ditampilkan.

Tugas yang menantang

Fungsi If Excel digunakan untuk menghindari pembagian bawaan dengan nol kesalahan. Selain itu, ini juga digunakan dalam beberapa situasi lain. Kasus pertama ditetapkan sebagai "DIV / 0". Ini dapat ditemukan cukup sering. Ini biasanya terjadi ketika rumus "A / B" perlu disalin. Pada saat yang sama, indikator B masuk sel individu adalah 0. Untuk menghindari situasi seperti itu, ada baiknya menggunakan kemampuan operator yang bersangkutan. Jadi, rumus yang dibutuhkan adalah: \u003d IF (B1 \u003d 0; 0; A1 / B1). Jadi, jika sel B1 diisi dengan nilai "nol", editor akan menampilkan "0". Jika tidak, program akan membagi indikator A1 dengan data B1 dan memberikan hasil yang diinginkan.

Diskon

Seperti yang diperlihatkan oleh latihan, situasi sering muncul, yang dibahas di bawah ini. Anda perlu menghitung diskon berdasarkan jumlah total yang dibelanjakan untuk pembelian barang tertentu. Matriks yang digunakan adalah sebagai berikut: kurang dari 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; lebih dari 5001 - 7%. Anda dapat melihat situasi saat Excel memiliki database pengunjung dan informasi tentang jumlah yang dibelanjakan untuk pembelian. Langkah selanjutnya adalah menghitung diskon untuk setiap pelanggan. Untuk melakukan ini, Anda perlu menerapkan ekspresi berikut: \u003d IF (A1\u003e \u003d 5001; B1 * 0.93; IF (A1\u003e \u003d 3001; B1 * 0.95; ..).

Sistem memeriksa jumlah total pembelian. Jika melebihi nilai 5001 rubel, perkalian dilakukan dengan 93 persen dari harga item. Jika tanda 3001 unit terlampaui, tindakan serupa terjadi, tetapi 95% sudah diperhitungkan.



Disarankan untuk membaca