edubun
Bahasa Indonesia
English한국어EspañolPortuguêsFrançaisDeutschItalianoРусскийBahasa Indonesia日本語
Beranda›Lembar kerja dan data›Rumus spreadsheet

AND, OR dan IF bersarang

Nilai 81 dengan kehadiran 70% lolos uji nilai dan gagal uji kehadiran. AND memerlukan keduanya; OR cukup satu.

Latar belakangContoh rekaan. Seorang guru membuat lembar nilai pada malam sebelum nilai harus diserahkan. Aturannya A untuk 90 ke atas, B untuk 80, C untuk 70, D untuk 60 dan F di bawahnya. Ia menulis uji-ujinya sesuai urutan yang terpikir, dari yang terendah: =IF(B2>=60, "D", IF(B2>=70, "C", ...)). Setiap siswa dengan 60 atau lebih mendapat D, termasuk yang mendapat 93. Tidak ada yang menampilkan galat. Seorang siswa menunjukkannya keesokan paginya. Perbaikannya adalah membalik urutannya, dan alasannya diuraikan di bawah.

Dua syarat sekaligus: AND dan OR

Guru yang sama punya aturan kedua. Untuk lulus, siswa perlu nilai paling sedikit 60 dan kehadiran paling sedikit 80%. AND menerima satu uji atau lebih (Excel mengizinkan sampai 255) dan mengembalikan TRUE hanya jika semuanya bernilai TRUE: =AND(B2>=60,C2>=80). OR mengembalikan TRUE jika setidaknya satu uji bernilai TRUE, jadi =OR(B2>=90,C2=100) bisa menandai siswa untuk hadiah yang nilainya sangat tinggi atau tidak pernah absen. NOT membalik TRUE menjadi FALSE dan sebaliknya, cocok untuk aturan yang memakai kata "kecuali".

Sendirian, AND dan OR hanya memberi TRUE atau FALSE. Taruh di dalam IF untuk mengubahnya menjadi kata: =IF(AND(B2>=60, C2>=80), "Yes", "No"). Uji di dalamnya bisa menunjuk ke kolom yang berbeda, membandingkan teks, atau memakai perhitungan. AND dan OR melewati teks dan sel kosong dalam rentang, dan jika tidak menemukan apa pun yang bisa diuji, keduanya mengembalikan #VALUE!. Tabel di bawah menjalankan dua uji, nilai paling sedikit 60 dan kehadiran paling sedikit 80, pada empat siswa.

Ucapkan aturannya dengan kata-kata biasa sebelum menulisnya. "Lulus jika nilainya paling sedikit 60 dan kehadirannya paling sedikit 80" langsung cocok dengan AND; "lulus jika salah satu uji lolos" cocok dengan OR. Aturan yang mencampur keduanya, seperti "paling sedikit 60, dan kehadiran 80% atau surat dokter", memerlukan satu fungsi di dalam fungsi lain: =AND(B2>=60, OR(C2>=80, D2="note")). Tulis bagian dalamnya dulu. Periksa bahwa bagian itu memberi TRUE dan FALSE di tempat yang kamu harapkan, baru kemudian bungkus.

NilaiHadir %ANDOR
9395TRUETRUE
8170FALSETRUE
6688TRUETRUE
5570FALSEFALSE

Mengapa 93 dalam cerita menjadi D

IF memberi dua hasil. Untuk lebih dari itu, taruh IF lain di tempat jawaban salah, dan skala nilai menjadi =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))). Spreadsheet mencoba uji pertama dan baru pindah ke uji berikutnya jika uji itu gagal. Persis itulah yang salah dalam cerita tadi. Dengan B2>=60 di depan, nilai 93 lolos uji pertama dan berhenti di D. Uji yang lebih tinggi tidak pernah dibaca. Mulailah dari ambang tertinggi lalu turun.

Hitung kurungnya: empat IF memerlukan empat kurung tutup di akhir. Halaman batas Excel mengizinkan 64 tingkat fungsi bersarang, tetapi setelah tiga atau empat tingkat, rumus menjadi sulit dibaca dan mudah rusak. Versi yang lebih baru dari kedua program punya IFS, yang mendaftar pasangan uji dan jawaban satu demi satu tanpa bersarang, dan bantuan IF dari Google menautkan ke sana. Untuk skala yang panjang, tabel pencarian di unit berikutnya biasanya lebih jelas lagi, karena ambangnya ada di sel yang bisa dilihat dan diubah siapa pun.

Di lab di bawah, kolom nilai huruf memakai empat IF bersarang dan kolom kelulusan memakai IF dengan AND. Isi keduanya ke bawah dan bandingkan Ben dengan Dev. Ben punya nilai yang lebih baik tetapi gagal karena kehadiran. Dev rajin hadir tetapi nilainya di bawah 60.

Uji batas-batasnya. Untuk skala nilai, coba tepat 90, 89.9, 60 dan 59.9 sebelum memercayai rumusnya. Sebagian besar kesalahan bersembunyi di batas, tempat > dan >= memberi jawaban berbeda.
Memuat

Seret untuk memutar. Gulir atau cubit untuk memperbesar.

Tulis rumus nilai huruf di D2 dan isi ke bawah. Coba taruh uji 60 di depan dan lihat Ana turun ke D.
Kesalahan umum
  • Memulai IF bersarang dengan ambang terendah. Uji pertama yang bernilai TRUE yang menang. Dengan B2>=60 di depan, setiap nilai 60 atau lebih berhenti di D.
  • Menulis =IF(B2>=60 AND C2>=80, ...). AND adalah fungsi yang menerima uji-uji sebagai argumen, bukan kata yang diletakkan di antara uji. Tulis AND(B2>=60, C2>=80).
  • Memakai OR padahal aturannya menyebut keduanya. Siswa dengan 81 poin dan kehadiran 70% lulus dengan OR dan gagal dengan AND. Ucapkan aturannya: kata "dan" dalam kalimat berarti AND.
  • Salah menghitung kurung tutup. Empat IF bersarang memerlukan empat ) di akhir. Jika kurang satu, rumusnya tidak diterima.
Catatan untuk guruIF bersarang sering menjadi rumus pertama yang tidak bisa dibaca pelajar dalam sekali lihat. Bangun bertahap di papan tulis: satu IF untuk A atau "bukan A", lalu ganti "bukan A" dengan IF kedua, dan seterusnya, sambil menguji setelah setiap langkah. Minta pelajar menebak nilai huruf untuk 93, 85, 60 dan 59.9 sebelum mengisi ke bawah. Cerita urutan yang salah layak diceritakan. Rumusnya gagal tanpa galat apa pun, dan perbaikannya jelas begitu terlihat.
  1. B2 bernilai 81 dan C2 bernilai 70. Apa yang dikembalikan =AND(B2>=60,C2>=80)?
    • FALSE
    • TRUE
  2. Sel yang sama. Apa yang dikembalikan =OR(B2>=60,C2>=80)?
    • TRUE
    • FALSE
  3. Apa yang dikembalikan =NOT(5>3)?
    • FALSE
    • TRUE
  4. B2 bernilai 93. Apa yang dikembalikan =IF(B2>=90, "A", IF(B2>=80, "B", "C"))?
  5. B2 bernilai 85. Rumusnya sama. Apa yang dikembalikan?
  6. B2 bernilai 93. Apa yang dikembalikan rumus dengan urutan salah =IF(B2>=60, "D", IF(B2>=90, "A", "F"))?
  7. Berapa kurung tutup yang mengakhiri rumus berisi empat IF bersarang dan tidak ada yang lain?
  8. Berapa tingkat fungsi bersarang yang diizinkan Excel?
  9. B2 bernilai 66 dan C2 bernilai 88. Apa yang dikembalikan =IF(AND(B2>=60, C2>=80), "Yes", "No")?
  10. Nilai mana yang paling baik untuk menguji rumus "60 atau lebih lulus"?
    • 60 dan 59.9
    • 100 dan 0
    • 75 dan 76
    • 30 dan 90
Poin penting
  • AND: setiap uji TRUE. OR: paling sedikit satu TRUE. NOT: kebalikannya.
  • Pakai di dalam IF untuk mengubah TRUE atau FALSE menjadi kata.
  • IF bersarang: taruh IF berikutnya di posisi jawaban salah, mulai dari ambang tertinggi.
  • Uji batas-batasnya. Untuk skala yang panjang, tabel pencarian lebih jelas.
Sumber kursus ini (19)
  • Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
  • Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
  • Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
  • Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
  • Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
  • Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
  • Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
  • Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
  • Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
  • Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
  • Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
  • Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
  • Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
  • Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
  • Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
  • Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
  • Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
  • Ablebits (IF without value_if_false returns FALSE)
  • Spreadsheet Class (Sheets circular dependency shows #REF!)

Diperiksa: 2026-10-19