Formula umum
=MAX(0,MIN(A1,1))
Ringkasan
Untuk membatasi nilai persentase agar berada di antara 0% dan 100% Anda dapat menggunakan rumus yang didasarkan pada fungsi MIN dan MAX. Dalam contoh yang ditunjukkan, rumus di C5, disalin ke bawah, adalah:
=MAX(0,MIN(B5,1))
Hasilnya adalah nilai negatif dipaksa menjadi nol, nilai di atas 1 dibatasi pada 1, dan nilai antara 0 dan 1 tidak terpengaruh.
Catatan: semua nilai diformat dengan format angka persentase.
Penjelasan
Untuk memahami masalah ini, pastikan Anda memahami cara kerja pemformatan angka persentase. Singkatnya, persentase adalah nilai desimal: 0,1 adalah 10%, 0,2 adalah 20%, dan seterusnya. Angka 1, jika diformat sebagai persentase, adalah 100%. Lebih lanjut tentang format angka di sini.
Tujuan dari contoh ini adalah untuk membatasi nilai persentase yang masuk sehingga berada dalam ambang batas atas dan bawah. Nilai dan nilai negatif di atas 100% tidak diperbolehkan, jadi hasil akhir harus berupa angka antara nol dan 1 (0-100%), inklusif.
Meskipun fungsi IF dapat digunakan untuk menyelesaikan masalah ini (lihat di bawah), hasilnya akan menjadi lebih panjang dan berlebihan. Sebaliknya, contoh yang ditampilkan menggunakan kombinasi fungsi MIN dan MAX dalam rumus yang sangat ringkas:
=MAX(0,MIN(B5,1))
Ini adalah contoh penumpukan - fungsi MIN bertumpuk di dalam fungsi MAX. Nesting adalah blok penyusun utama untuk formula yang lebih maju.
Bekerja dari dalam ke luar, fungsi MIN digunakan untuk membatasi nilai yang masuk ke 1 seperti ini:
MIN(B5,1) // get smaller value
Terjemahan: kembalikan yang lebih kecil dari B5 dan 1. Untuk setiap nilai di atas 1, nilai dalam B5 dikembalikan. Dalam contoh, B5 berisi -5% (-0.05), jadi MIN mengembalikan -0.05. Hasil ini langsung dikembalikan ke fungsi MAX:
=MAX(0,-0.05) // get larger value
Di sini, kita melihat rumus melakukan tugasnya. Karena nol lebih besar (lebih besar) dari -0,05, MAX mengembalikan nol sebagai hasil akhir. Nilai asli dibuang.
Fungsi IF
Seperti disebutkan di atas, fungsi IF juga dapat digunakan untuk menyelesaikan masalah ini. Untuk melakukan ini, kita membutuhkan dua fungsi IF yang terpisah. Satu IF memaksa nilai negatif menjadi nol:
IF(B5<0,0,B5) // cap at zero
IF kedua membatasi nilai yang lebih besar pada 1:
=IF(B5>1,1,B5) // cap at 1
Saat kita menumpuk IF pertama di dalam yang kedua, kita memiliki rumus akhir:
=IF(B5>1,1,IF(B5<0,0,B5))
Ini adalah contoh IF bertingkat. Ini mengembalikan hasil yang persis sama dengan rumus MIN dan MAX di atas, tetapi sedikit lebih kompleks dan berlebihan. Perhatikan, misalnya, referensi ke B5 muncul tiga kali terpisah.
Intinya - saat Anda perlu membuat pilihan berdasarkan nilai yang lebih kecil atau lebih besar, fungsi MIN dan MAX bisa menjadi cara yang cerdas dan elegan untuk menjaga rumus tetap sederhana.
Fungsi MEDIAN
Oke, sekarang kita telah membicarakan tentang nesting, dan berbicara tentang keanggunan MIN dengan MAX, saya harus menyebutkan bahwa adalah mungkin untuk menyelesaikan masalah ini tanpa bersarang sama sekali dengan fungsi MEDIAN. Versi umum rumusnya terlihat seperti ini:
=MEDIAN(0,1,A1)
Ini berfungsi karena fungsi MEDIAN mengembalikan median (angka tengah) dalam sekelompok angka. Jika suatu nilai negatif, nol menjadi angka tengah. Jika sebuah angka lebih besar dari 1, 1 menjadi angka tengah. Pintar!
Namun, catatan MEDIAN hanya mengembalikan angka tengah jika jumlah total nilainya ganjil. Jika jumlah nilainya genap, MEDIAN mengembalikan rata-rata dari dua angka di tengah. Akibatnya, jika sel target (A1) kosong, MEDIAN akan mengembalikan rata-rata 1 dan nol, yaitu 0,5, atau 50% jika diformat sebagai persentase.
