Công thức chung
=MAX(0,MIN(A1,1))
Tóm lược
Để giới hạn giá trị phần trăm sao cho nó nằm trong khoảng từ 0% đến 100%, bạn có thể sử dụng công thức dựa trên hàm MIN và MAX. Trong ví dụ được hiển thị, công thức trong C5, được sao chép xuống, là:
=MAX(0,MIN(B5,1))
Kết quả là các giá trị âm buộc phải bằng 0, các giá trị trên 1 được giới hạn ở 1 và các giá trị từ 0 đến 1 không bị ảnh hưởng.
Lưu ý: tất cả các giá trị được định dạng với định dạng số phần trăm.
Giải trình
Để hiểu vấn đề này, hãy đảm bảo bạn hiểu cách hoạt động của định dạng số phần trăm. Tóm lại, tỷ lệ phần trăm là giá trị thập phân: 0,1 là 10%, 0,2 là 20%, v.v. Số 1, khi được định dạng dưới dạng phần trăm, là 100%. Thêm về các định dạng số ở đây.
Mục tiêu của ví dụ này là giới hạn các giá trị phần trăm đến để chúng nằm trong ngưỡng trên và dưới. Giá trị âm và giá trị trên 100% không được phép, vì vậy kết quả cuối cùng phải là một số từ 0 đến 1 (0-100%), bao gồm.
Mặc dù có thể sử dụng hàm IF để giải quyết vấn đề này (xem bên dưới), nhưng kết quả sẽ hơi dài và dư thừa. Thay vào đó, ví dụ được hiển thị sử dụng kết hợp các hàm MIN và MAX trong một công thức rất nhỏ gọn:
=MAX(0,MIN(B5,1))
Đây là một ví dụ về lồng - hàm MIN được lồng bên trong hàm MAX. Lồng là một khối xây dựng chính cho các công thức nâng cao hơn.
Làm việc từ trong ra ngoài, hàm MIN được sử dụng để giới hạn các giá trị đến bằng 1 như sau:
MIN(B5,1) // get smaller value
Dịch: trả về giá trị nhỏ hơn của B5 và 1. Đối với bất kỳ giá trị nào trên 1, giá trị trong B5 được trả về. Trong ví dụ, B5 chứa -5% (-0.05), vì vậy MIN trả về -0.05. Kết quả này được trả về trực tiếp cho hàm MAX:
=MAX(0,-0.05) // get larger value
Ở đây, chúng ta thấy công thức thực hiện công việc của nó. Vì số không lớn hơn (lớn hơn) -0.05, nên MAX trả về số không như một kết quả cuối cùng. Giá trị ban đầu bị loại bỏ.
Hàm IF
Như đã đề cập ở trên, hàm IF cũng có thể được sử dụng để giải quyết vấn đề này. Để làm điều này, chúng ta cần hai hàm IF riêng biệt. Một IF buộc các giá trị âm về 0:
IF(B5<0,0,B5) // cap at zero
IF thứ hai giới hạn các giá trị lớn hơn ở 1:
=IF(B5>1,1,B5) // cap at 1
Khi chúng ta lồng IF đầu tiên vào bên trong IF thứ hai, chúng ta có công thức cuối cùng:
=IF(B5>1,1,IF(B5<0,0,B5))
Đây là một ví dụ về IF lồng nhau. Nó trả về kết quả chính xác như công thức MIN và MAX ở trên, nhưng hơi phức tạp và dư thừa. Lưu ý, ví dụ, tham chiếu đến B5 xảy ra ba lần riêng biệt.
Điểm mấu chốt - khi bạn cần lựa chọn dựa trên các giá trị nhỏ hơn hoặc lớn hơn, các hàm MIN và MAX có thể là một cách thông minh và thanh lịch để giữ cho công thức đơn giản.
Hàm MEDIAN
OK, bây giờ chúng ta đã nói về lồng và nói về sự sang trọng của MIN với MAX, tôi nên đề cập rằng có thể giải quyết vấn đề này mà không cần lồng với chức năng MEDIAN. Phiên bản chung của công thức trông giống như sau:
=MEDIAN(0,1,A1)
Điều này hoạt động vì hàm MEDIAN trả về giá trị trung bình (số giữa) trong một nhóm số. Khi một giá trị âm, số 0 sẽ trở thành số ở giữa. Khi một số lớn hơn 1, 1 trở thành số chính giữa. Tài giỏi!
Tuy nhiên, lưu ý MEDIAN chỉ trả về số giữa khi tổng số giá trị là số lẻ. Nếu số giá trị là chẵn, MEDIAN trả về giá trị trung bình của hai số ở giữa. Do đó, nếu ô đích (A1) trống, MEDIAN sẽ trả về giá trị trung bình của 1 và 0, là 0,5 hoặc 50% khi được định dạng dưới dạng phần trăm.
