Bạn có thể muốn giới hạn giá trị phần trăm từ 0 đến 100 trong Bảng tính của mình để giải quyết một số vấn đề. Trong Google Sheets, bạn có thể sử dụng một số công thức để làm điều này.
Khi tôi tính thời gian trôi qua giữa hai ngày (ngày bắt đầu và ngày kết thúc) trong Google Sheets, tôi đã gặp một vấn đề.
Vấn đề đó là gì?
Khi ngày bắt đầu là một ngày trong tương lai, phần trăm trả về là một giá trị âm.
Mặt khác, nếu ngày kết thúc nhỏ hơn ngày đánh giá, phần trăm trả về sẽ lớn hơn 100%.
Điều này có thể gây vấn đề nếu bạn muốn tạo biểu đồ thanh SPARKLINE trong ô, như được hiển thị dưới đây.
Xem tác vụ 5 sparkline ở ô F8. Thực ra, chúng ta không muốn có một thanh ở đó vì tác vụ vẫn chưa bắt đầu.
Trong ví dụ trên, chúng ta có thể yêu cầu giới hạn phần trăm tại 100 trong ô E5.
Trong ô E8, chúng ta có thể muốn giới hạn phần trăm từ 0 đến 100.
Hãy xem cách giới hạn/giới hạn một phần trăm từ 0 đến 100 trong Google Sheets.
Công thức để giới hạn phần trăm từ 0 đến 100 trong Google Sheets
Chúng ta sẽ xem công thức phần trăm thời gian trôi qua và sparkline trong một hướng dẫn khác.
Ở đây, để giới hạn phần trăm, chúng ta chỉ quan tâm đến các giá trị phần trăm trong cột E, không phải các công thức ngày gốc.
Dưới đây là những tùy chọn công thức.
1. Kiểm tra logic IF
=TO_PERCENT(if(E3<0%,0%,if(E3>100%,100%,E3)))
Trong ô F3, nhập công thức này và sao chép và dán xuống.
Chú ý: Tôi đã bao gồm phần giải thích công thức như một phần riêng biệt bên dưới.
2. Công thức mảng kiểm tra logic IF
=ArrayFormula(TO_PERCENT(if(E3:E="",,if(E3:E<0%,0%,if(E3:E>100%,100%,E3:E)))))
Nó sẽ giới hạn các giá trị phần trăm cho tất cả các hàng xuống.
3. Min và Max
=max(min(E3,100%),0%)
Công thức này cũng áp dụng cho ô F3, cần được sao chép và dán xuống.
4. Median
=to_percent(median(0%,100%,F3))
Công thức khác để giới hạn giá trị phần trăm là Median ở ô F3. Bạn nên kéo/sao chép công thức này xuống.
Có bốn công thức trên để giới hạn giá trị phần trăm từ 0 đến 100 trong Google Sheets.
Tôi sẽ khuyên bạn nên sử dụng công thức thứ hai vì nó có thể mở rộng.
Hãy xem cách công thức giới hạn các giá trị phần trăm trong Google Sheets.
Giải thích các công thức
Tôi sẽ bắt đầu với các công thức 1 và 2, đó là các kiểm tra logic.
Sử dụng chức năng IF để giới hạn giá trị phần trăm từ 0 đến 100
1. Kiểm tra logic IF – Giải thích
Trong công thức đầu tiên, như bạn có thể thấy, tôi đã sử dụng chức năng IF của Google Sheets để kiểm tra xem giá trị % có nằm trong khoảng từ 0% đến 100% không.
Có hai kiểm tra logic được liên kết/nhúng vào nhau.
a. Nếu % trong E2 < 0%, trả về 0%.
b. Nếu % E2 > 100%, trả về 100%.
c. Ngược lại, trả về giá trị % của E2.
Với các kiểm tra logic, tôi đã sử dụng chức năng To_Percent để giữ định dạng phần trăm. Nếu không, kết quả có thể ở định dạng số.
2. Array Formula IF Logical Test – Giải thích**
Ở đây, tôi sử dụng thêm một kiểm tra logic nữa để loại trừ đánh giá trong các hàng trống. Vì phạm vi trong công thức này là không giới hạn/vô hạn, nên đã sử dụng chức năng ArrayFormula.
a. Nếu các giá trị phần trăm trong E2:E = “”, trả về trống.
b. Nếu các giá trị phần trăm trong E2:E < 0%, trả về 0%.
c. Nếu các giá trị phần trăm trong E2:E > 100%, trả về 100%.
d. Ngược lại, trả về các giá trị phần trăm của E2:E.
Giới hạn tối thiểu và tối đa
3. Công thức Min và Max – Giải thích
Nếu bạn không muốn sử dụng công thức mảng, cách đơn giản nhất để giới hạn giá trị phần trăm từ 0 đến 100 là sử dụng Min và Max, không phải kiểm tra logic.
Vì cặp này giữ định dạng phần trăm, chúng tôi không cần sử dụng To_Percent với nó.
MIN đánh giá hai giá trị là giá trị trong E2 và 100%. Sau đó trả về giá trị nhỏ nhất.
Nó đảm bảo rằng phần trăm trả về luôn nhỏ hơn hoặc bằng 100%. Như vậy, chúng ta có thể giới hạn phần trăm tại 100 trong Google Sheets.
Vậy MAX có vai trò gì?
Ở đây cũng có hai giá trị để đánh giá – Đầu ra Min và 0%.
Max sẽ trả về giá trị của E2 nếu nó lớn hơn hoặc bằng 0%, ngược lại là 0%.
Median để giới hạn các giá trị phần trăm trong Google Sheets
4. Median – Giải thích công thức
Hàm Median lấy một tập dữ liệu số để trả về số ở giữa.
Ở đây, tập dữ liệu số chỉ chứa ba giá trị phần trăm, đó là 0%, giá trị % trong E2 và 100%.
Vì vậy, giá trị trung bình luôn trả về một giá trị phần trăm nằm trong khoảng từ 0% đến 100%.
Bốn công thức trên là các tùy chọn công thức để giới hạn giá trị phần trăm từ 0 đến 100 trong Google Sheets.
Tài liệu tham khảo:
- Cách tạo thanh tiến trình phần trăm trong Google Sheets.
- Cách sử dụng giá trị phần trăm trong công thức IF logic trong Google Sheets.
- Truy vấn để lọc một cột chứa các giá trị phần trăm trong Google Sheets.
- Cách sử dụng chức năng UNARY_PERCENT trong Google Sheets.
- Tính phần trăm của tổng trong Google Sheets [Cách làm].
- Phân phối phần trăm của tổng chung trong Truy vấn Google Sheets.
- Công thức mảng thay đổi phần trăm trong Google Sheets.
- Làm thế nào để làm tròn các giá trị phần trăm trong Google Sheets.
- Lấy điểm số phần trăm cao nhất từ mỗi nhóm trong Google Sheets.
