Mike là một giáo viên và thường xuyên phải trả bài kiểm tra cho học sinh của mình. Trong một trường hợp, anh ấy có 20 điểm kiểm tra từ 76 xuống 12. Mike muốn để lại điểm cao nhất là 76 nhưng lại tăng điểm cuối lên 5 (lên 17)

và sau đó điều chỉnh tất cả các điểm ở giữa một cách tương ứng. Anh ấy tự hỏi liệu Excel có một hàm nào đó sẽ thực hiện điều chỉnh cho anh ấy không.

Không có chức năng nào làm điều này, nhưng có bất kỳ công thức nào có thể được sử dụng để lấy điểm kiểm tra đã điều chỉnh. Trên thực tế, có một số lượng lớn các công thức mà bạn có thể sử dụng. Lý do tại sao có nhiều như vậy là bởi vì từ “theo tỷ lệ” không rõ ràng – chính xác thì điều chỉnh theo tỷ lệ trông như thế nào?

Ví dụ, nó có thể có nghĩa là một phần của sự khác biệt giữa điểm thấp nhất và điểm cao nhất. Hoặc, nó có thể có nghĩa là điểm số thấp hơn được “cân nhắc” theo một cách nào đó để chúng nhận được sự thúc đẩy lớn hơn điểm số cao hơn. Hoặc, nó có thể có nghĩa là bạn chỉ cần tính toán bao nhiêu phần trăm điểm từ điểm tối đa và sau đó tăng điểm lên bao nhiêu.

Với ý nghĩ đó, hãy xem xét một số cách bạn có thể điều chỉnh điểm số.

Bạn sẽ muốn xem kết quả của từng công thức và xác định xem chúng có phù hợp với mong đợi của bạn về những gì sẽ xảy ra với điểm số hay không. Ba công thức này tạo ra những điều chỉnh hoàn toàn giống nhau đối với điểm số, với điểm số thấp hơn được ưu tiên hơn điểm số cao hơn:

=A1+(1-(A1-MIN(A:A))/(MAX(A:A)-MIN(A:A)))5 =A1+(5(MAX(A:A)-A1))/(MAX(A:A)-MIN(A:A))

=A1+(MAX(A:A)-A1)/(MAX(A:A)-MIN(A:A))*5

Các công thức này điều chỉnh điểm số cao nhất và thấp nhất theo yêu cầu của Mike, và tất cả các điểm số khác theo tỷ lệ. Nó cho kết quả, ví dụ, ở điểm số tầm trung (một nửa từ 12 đến 76, tức là điểm 42)

được điều chỉnh 2,5 điểm.

Công thức đơn giản sau đây chỉ chú ý đến giá trị lớn nhất trong dải điểm gốc. Nó tính toán khoảng cách từ điểm số tối đa, sau đó điều chỉnh điểm số lên. Nó dẫn đến điểm điều chỉnh dưới cùng là 17,33, nhưng có thể dễ dàng làm tròn để đạt được số tiền mong muốn.

=((MAX(A:A)-A1)/A1)+A1

Công thức sau đây kiểm tra thứ hạng của 19 điểm ban đầu, trên thang điểm từ 0 đến 19. Sau đó, nó tính ra phần trăm của 5 thứ hạng đó đại diện và sau đó thêm giá trị vào điểm ban đầu:

=(RANK(A1,A:A)-1)*5/19+A1

Bạn cũng có thể sử dụng hồi quy tuyến tính để giải quyết vấn đề. Excel có hai hàm tích hợp (SLOPE và INTERCEPT) có thể được sử dụng để tìm công thức xác định điểm đã điều chỉnh. Trong ví dụ của Mike, phạm vi điểm trước khi điều chỉnh là 12 đến 76. Chúng cần được điều chỉnh thành phạm vi từ 17 đến 76. Nếu bạn đặt 12 vào ô E1, 76 vào ô E2, 17 vào ô F1 và 76 vào ô F2, sau đó bạn kết thúc bằng các mức thấp và cao trong cả phạm vi bắt đầu và kết thúc. Sau đó, bạn có thể sử dụng các công thức này để tìm độ dốc và điểm chặn:

=SLOPE(F1:F2,E1:E2)

=INTERCEPT(F1:F2,E1:E2)

Điều này tính toán độ dốc ở 0,921875 và điểm chặn là 5,9375. Sau đó, bạn có thể áp dụng các giá trị này cho điểm kiểm tra gốc theo cách sau:

=0.921875 * A1 + 5.9375

Điều thú vị là, phương pháp hồi quy tuyến tính này trả về các giá trị giống hệt nhau do ba công thức đầu tiên được trình bày ở phần đầu của mẹo này.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (1024) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: