مایک معلم است و اغلب مجبور است به دانش آموزانش تست بدهد. در یک نمونه، او 20 نمره آزمون داشت که از 76 تا 12 متغیر بود. مایک می‌خواهد نمره برتر را در 76 بگذارد، اما نمره پایین را 5 (به 17) افزایش دهد و سپس تمام نمرات بین‌المللی را به تناسب تنظیم کند. او فکر می کند که آیا اکسل تابعی دارد که تنظیم را برای او انجام دهد.
هیچ تابعی وجود ندارد که این کار را انجام دهد، اما تعدادی فرمول وجود دارد که می توان از آنها برای بدست آوردن نمرات تست تنظیم شده استفاده کرد. در واقع، تعداد بسیار زیادی فرمول وجود دارد که می توانید استفاده کنید. دلیل اینکه تعداد بسیار زیادی وجود دارد این است که کلمه "متناسب" مبهم است - تعدیل متناسب دقیقاً چگونه است؟
به عنوان مثال، می تواند به معنای بخشی از تفاوت بین کمترین نمره و بالاترین نمره باشد. یا، ممکن است به این معنا باشد که نمرات پایین‌تر به نوعی «وزن» می‌شوند تا نسبت به نمرات بالاتر، تقویت بیشتری دریافت کنند. یا ممکن است به این معنی باشد که شما به سادگی محاسبه کنید که چند درصد امتیاز از حداکثر امتیاز است و سپس امتیاز را تا این حد افزایش دهید.
با در نظر گرفتن این موضوع، اجازه دهید به چند روش برای تنظیم نمرات نگاه کنیم. شما می خواهید به نتایج هر فرمول نگاه کنید و تعیین کنید که آیا آنها با انتظارات شما برای آنچه که باید برای نمرات اتفاق بیفتد مطابقت دارند یا خیر. این سه فرمول دقیقاً تنظیمات یکسانی را در نمرات ایجاد می کنند، با امتیازهای پایین تر نسبت به نمرات بالاتر:
=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
این فرمول ها بالاترین و کمترین امتیازات را طبق خواسته مایک و سایر امتیازات را به تناسب تنظیم می کنند. به عنوان مثال، نتیجه می شود که امتیاز میان رده (نیم راه بین 12 تا 76 که امتیاز 42 است) با 2.5 امتیاز تنظیم می شود.
فرمول ساده زیر فقط به حداکثر مقدار در محدوده امتیازهای اصلی توجه می کند. محاسبه می کند که چقدر از حداکثر امتیاز فاصله دارد و سپس امتیاز را به سمت بالا تنظیم می کند. این امتیاز به یک امتیاز تعدیل شده از پایین 17.33 منجر می شود، اما می توان آن را به راحتی گرد کرد تا به مقدار دلخواه رسید.
=((MAX(A:A)-A1)/A1)+A1
فرمول زیر رتبه‌بندی 19 امتیاز اصلی را در مقیاس 0 تا 19 بررسی می‌کند. سپس مشخص می‌کند که این رتبه‌بندی چند درصد از 5 را نشان می‌دهد و سپس مقدار را به امتیاز اصلی اضافه می‌کند:
=(RANK(A1,A:A)-1)*5/19+A1
همچنین می توانید از رگرسیون خطی برای حل مسئله استفاده کنید. اکسل دارای دو تابع داخلی است (SLOPE و INTERCEPT) که می توان از آنها برای یافتن فرمول تعیین امتیازهای تنظیم شده استفاده کرد. در مثال Mikes، محدوده امتیازات قبل از تنظیم بین 12 تا 76 است. این امتیازها باید در محدوده 17 تا 76 تنظیم شوند. اگر 12 را در سلول E1، 76 را در سلول E2، 17 را در سلول F1، و 76 را در سلول قرار دهید. سلول F2، سپس شما در هر دو محدوده شروع و پایان به پایین و بالاترین می رسید. سپس می توانید از این فرمول ها برای یافتن شیب و قطع استفاده کنید:
=SLOPE(F1:F2,E1:E2)
=INTERCEPT(F1:F2,E1:E2)
این شیب را 0.921875 و فاصله را در 5.9375 محاسبه می کند. سپس می توانید این مقادیر را به نمرات آزمون اصلی به این ترتیب اعمال کنید:
=0.921875 * A1 + 5.9375
به اندازه کافی جالب توجه است که این رویکرد رگرسیون خطی دقیقاً همان مقادیر بدست آمده توسط سه فرمول اول ارائه شده در ابتدای این نکته را برمی گرداند.
