Formula generik
=MAX(0,MIN(A1,1))
Ringkasan
Untuk mengehadkan nilai peratusan sehingga jatuh antara 0% dan 100%, anda boleh menggunakan formula berdasarkan fungsi MIN dan MAX. Dalam contoh yang ditunjukkan, formula di C5, disalin, adalah:
=MAX(0,MIN(B5,1))
Hasilnya adalah bahawa nilai negatif dipaksa untuk nol, nilai lebih dari 1 dibatasi pada 1, dan nilai antara 0 dan 1 tidak terpengaruh.
Catatan: semua nilai diformat dengan format nombor peratusan.
Penjelasan
Untuk memahami masalah ini, pastikan anda memahami bagaimana pemformatan nombor peratusan berfungsi. Ringkasnya, peratusan adalah nilai perpuluhan: 0.1 adalah 10%, 0.2 adalah 20%, dan seterusnya. Nombor 1, apabila diformat sebagai peratusan, adalah 100%. Lebih banyak mengenai format nombor di sini.
Matlamat contoh ini adalah untuk mengehadkan nilai peratusan masuk sehingga mereka berada dalam ambang atas dan bawah. Nilai dan nilai negatif melebihi 100% tidak dibenarkan, jadi hasil akhir mestilah angka antara sifar dan 1 (0-100%), inklusif.
Walaupun fungsi IF dapat digunakan untuk menyelesaikan masalah ini (lihat di bawah), hasilnya akan agak lama dan berlebihan. Sebaliknya, contoh yang ditunjukkan menggunakan gabungan fungsi MIN dan MAX dalam formula yang sangat padat:
=MAX(0,MIN(B5,1))
Ini adalah contoh bersarang - fungsi MIN bersarang di dalam fungsi MAX. Nesting adalah blok utama untuk formula yang lebih maju.
Berfungsi dari dalam ke luar, fungsi MIN digunakan untuk membatasi nilai masuk menjadi 1 seperti ini:
MIN(B5,1) // get smaller value
Terjemahan: kembalikan yang lebih kecil dari B5 dan 1. Untuk nilai lebih dari 1, nilai dalam B5 dikembalikan. Dalam contoh, B5 mengandungi -5% (-0.05), jadi MIN mengembalikan -0.05. Hasil ini dikembalikan terus ke fungsi MAX:
=MAX(0,-0.05) // get larger value
Di sini, kita melihat formula berfungsi. Kerana sifar lebih besar (lebih besar) daripada -0.05, MAX mengembalikan sifar sebagai hasil akhir. Nilai asalnya dibuang.
JIKA berfungsi
Seperti yang disebutkan di atas, fungsi IF juga dapat digunakan untuk menyelesaikan masalah ini. Untuk melakukan ini, kita memerlukan dua fungsi IF yang berasingan. Satu JIKA memaksa nilai negatif menjadi sifar:
IF(B5<0,0,B5) // cap at zero
IF kedua menghadkan nilai yang lebih besar pada 1:
=IF(B5>1,1,B5) // cap at 1
Apabila kita bersarang JIKA pertama di dalam yang kedua, kita mempunyai formula terakhir:
=IF(B5>1,1,IF(B5<0,0,B5))
Ini adalah contoh IF bersarang. Ia memberikan hasil yang sama persis dengan formula MIN dan MAX di atas, tetapi sedikit lebih kompleks dan berlebihan. Perhatikan, sebagai contoh, rujukan ke B5 berlaku tiga kali berasingan.
Intinya - apabila anda perlu membuat pilihan berdasarkan nilai yang lebih kecil atau lebih besar, fungsi MIN dan MAX dapat menjadi cara yang cerdas dan elegan untuk menjaga rumus sederhana.
Fungsi MEDIAN
OK, sekarang setelah kita bercakap mengenai bersarang, dan membincangkan keanggunan MIN dengan MAX, saya harus menyebutkan bahawa mungkin untuk menyelesaikan masalah ini tanpa bersarang sama sekali dengan fungsi MEDIAN. Versi generik formula kelihatan seperti ini:
=MEDIAN(0,1,A1)
Ini berfungsi kerana fungsi MEDIAN mengembalikan median (nombor tengah) dalam kumpulan nombor. Apabila nilai negatif, sifar menjadi nombor tengah. Apabila nombor lebih besar daripada 1, 1 menjadi nombor tengah. Pandai!
Walau bagaimanapun, perhatikan MEDIAN hanya mengembalikan nombor tengah apabila jumlah nilai ganjil. Sekiranya bilangan nilai genap, MEDIAN mengembalikan purata dua nombor di tengah. Akibatnya, jika sel sasaran (A1) kosong, MEDIAN akan mengembalikan rata-rata 1 dan sifar, iaitu 0,5, atau 50% ketika diformat sebagai peratusan.
