برای ساخت کارنامه در اکسل کافی است نمرههای هر دانش‌آموز را در یک سطر بنویسید، ضریب هر درس را در یک سطر جدا قرار دهید و معدل وزنی را با تابع SUMPRODUCT حساب کنید. با ROUND معدل را گرد می‌کنید، با RANK رتبهٔ کلاسی را به دست می‌آورید و با قالب‌بندی شرطی درس‌های زیر حد قبولی را علامت می‌زنید.
این آموزش برای معلم یا معاونی است که می‌خواهد کارنامهٔ یک کلاس را بدون خطا در اکسل یا گوگلشیت بسازد. هر مرحله را با فرمولش توضیح می‌دهیم، یک مثال کامل می‌آوریم، خطاهای رایج را نشان می‌دهیم و در پایان می‌گوییم کی اکسل دیگر کافی نیست.
در یک نگاه
- چیدمان: درس‌ها در سطر اول، ضریبها در سطر دوم، هر دانش‌آموز در یک سطر.
- معدل وزنی: SUMPRODUCT نمره‌ها و ضریبها تقسیم بر جمع ضریبها.
- گرد کردن با ROUND، ROUNDDOWN یا ROUNDUP، مطابق قاعدهٔ مدرسه.
- رتبه با RANK و علامت‌گذاری با قالب‌بندی شرطی یا IF.
- اکسل برای یک کلاس خوب است؛ برای چند کلاس و چند معلم، خطا و نسخه‌های موازی زیاد می‌شود.
چرا هنوز کارنامه در اکسل ساخته می‌شود؟
اکسل در دسترس همه است، انعطاف زیادی دارد و برای یک کلاس، سریع‌ترین راه رسیدن به یک کارنامهٔ مرتب است. بسیاری از معلم‌ها هم فهرست نمرهٔ کلاسشان را از قبل در اکسل نگه می‌دارند. اگر فرمولها درست نوشته شوند، اکسل محاسبه را بدون خطا انجام می‌دهد؛ خطاها معمولاً از ورود داده، کپی فرمول و نسخه‌های متعدد فایل می‌آیند، نه از خود محاسبه.
همهٔ فرمول‌های این آموزش در گوگلشیت هم به همین شکل کار میکنند. اگر نسخهٔ فارسی اکسل دارید، نام تابع‌ها همچنان لاتین است، اما ممکن است جداکنندهٔ آرگومان‌ها به‌جای ویرگول، نقطه‌ویرگول باشد.
مرحله‌ها با فرمول
مرحلهٔ ۱: چیدمان جدول
در سطر اول نام درس‌ها را بنویسید و در سطر دوم ضریب هر درس را. از سطر سوم به بعد، هر سطر یک دانش‌آموز است: ستون A نام و ستون‌های بعدی نمرهٔ هر درس. این چیدمان ساده اجازه می‌دهد یک فرمول را برای همهٔ دانش‌آموزان به پایین بکشید.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| ۱ | نام | ریاضی | فیزیک | ادبیات | معدل |
| ۲ | ضریب | ۴ | ۳ | ۲ | |
| ۳ | سارا محمدی | ۱۹ | ۱۸٫۵ | ۱۷٫۲۵ | فرمول |
مرحلهٔ ۲: فرمول معدل وزنی
در خانهٔ E3 این فرمول را بنویسید و آن را برای سطرهای پایین بکشید:
=SUMPRODUCT(B3:D3,$B$2:$D$2)/SUM($B$2:$D$2)
تابع SUMPRODUCT هر نمره را در ضریب همان درس ضرب و نتیجه‌ها را جمع میکند و تقسیم بر جمع ضریبها، معدل وزنی را می‌دهد. علامت $ سطر ضریبها را هنگام کشیدن فرمول ثابت نگه می‌دارد؛ بدون آن، سطر بعدی ضریبها را از سطر ۳ می‌خواند و معدل غلط می‌شود.
مرحلهٔ ۳: گرد کردن معدل
برای گرد کردن تا دو رقم اعشار، فرمول را داخل ROUND بگذارید:
=ROUND(SUMPRODUCT(B3:D3,$B$2:$D$2)/SUM($B$2:$D$2),2)
قاعدهٔ گرد کردن هر مدرسه ممکن است متفاوت باشد. اگر مدرسه همیشه به پایین گرد میکند، از ROUNDDOWN و اگر به بالا، از ROUNDUP استفاده کنید. مهم این است که قاعده برای همهٔ کلاس‌ها یکی باشد.
مرحلهٔ ۴: رتبهٔ کلاسی و وضعیت
برای رتبه در ستون F از این فرمول استفاده کنید (فرض بر این است که فهرست تا سطر ۳۲ ادامه دارد):
=RANK(E3,$E$3:$E$32)
برای علامت زدن درس‌هایی که نمره‌شان زیر حد قبولی است، از Conditional Formatting یا تابع IF استفاده کنید. نمونهٔ زیر حد ۱۰ را فرض کرده است؛ حد قبولی را با آیین‌نامهٔ همان مقطع تطبیق دهید:
=IF(B3<10,"تجدید","قبول")
مثال کامل: سه دانش‌آموز، سه درس
با ضریب‌های ۴، ۳ و ۲ برای ریاضی، فیزیک و ادبیات، نتیجهٔ فرمولها برای سه دانش‌آموز این است:
| نام | ریاضی | فیزیک | ادبیات | معدل وزنی | رتبه |
|---|---|---|---|---|---|
| سارا | ۱۹ | ۱۸٫۵ | ۱۷٫۲۵ | ۱۸٫۵۸ | ۱ |
| امید | ۱۶ | ۱۸ | ۱۹ | ۱۷٫۲۲ | ۲ |
| نازنین | ۱۴ | ۱۵ | ۲۰ | ۱۵٫۵۶ | ۳ |
برای سارا: ۱۹ ضرب در ۴ برابر ۷۶، ۱۸٫۵ ضرب در ۳ برابر ۵۵٫۵ و ۱۷٫۲۵ ضرب در ۲ برابر ۳۴٫۵ است؛ جمع ۱۶۶ تقسیم بر ۹ می‌شود حدود ۱۸٫۵۸. به نازنین دقت کنید: بالاترین نمرهٔ ادبیات را دارد، اما چون ضریب ریاضی بیشتر است، معدلش پایین‌تر از دو نفر دیگر است. این همان اثری است که معدل وزنی باید داشته باشد. ضریب‌های این مثال فرضی‌اند.
قالب‌بندی شرطی، قدم‌به‌قدم
محدودهٔ نمره‌ها را انتخاب کنید، از منوی Conditional Formatting گزینهٔ Highlight Cells Rules و بعد Less Than را بزنید، عدد حد قبولی را وارد و رنگ را انتخاب کنید. از این به بعد هر نمرهٔ زیر حد، بدون فرمول اضافه رنگی می‌شود و پیش از چاپ دیده می‌شود.
کار هم‌زمان چند نفر روی یک فایل
اگر چند معلم باید نمرهٔ درس خودشان را در یک کارنامه وارد کنند، به‌جای فرستادن فایل برای هر نفر، از صفحه‌گستردهٔ آنلاین مشترک استفاده کنید و ستون هر درس را فقط برای معلم همان درس قابل ویرایش بگذارید. ستون معدل و رتبه را هم قفل کنید تا فرمولها ناخواسته عوض نشوند. این دو تنظیم ساده، بیشتر خطاهای «فایل چندنسخه‌ای» را حذف میکند.
کل کار، به ترتیب
- یک فایل اصلی بسازید و درس‌ها و ضریبها را در دو سطر اول وارد کنید.
- نام دانش‌آموزان و نمره‌ها را از سطر سوم وارد کنید.
- فرمول معدل وزنی را در ستون معدل بنویسید و به پایین بکشید.
- فرمول را داخل ROUND مطابق قاعدهٔ مدرسه بگذارید.
- ستون رتبه را با RANK بسازید.
- قالب‌بندی شرطی را برای نمرههای زیر حد قبولی اعمال کنید.
- پیش از چاپ، چک‌لیست شش‌مرحله‌ای را مرور کنید.
خطاهای رایج
فراموش کردن $
بدون $ در آدرس ضریبها، با کشیدن فرمول، ضریبها جابهجا می‌شوند و معدل همه به‌جز اولی غلط می‌شود.
خانهٔ خالی
SUMPRODUCT خانهٔ خالی را صفر حساب میکند و معدل پایین می‌آید. برای نمرههای واردنشده، با شمارش خانه‌های خالی هشدار بسازید.
اعشار فارسی
اگر نمره با «٫» تایپ شود، اکسل ممکن است آن را متن بشناسد و در محاسبه نیاورد. قالب خانه‌ها را عدد کنید و اعشار را یکسان تایپ کنید.
چند نسخه از یک فایل
فایلی که برای چند نفر فرستاده و هر کدام اصلاحش کرده‌اند، معلوم نیست کدام نسخه نهایی است.
مرتب کردن فقط یک ستون
اگر برای مرتب کردن بر اساس معدل فقط ستون معدل را انتخاب کنید، معدل‌ها از نام دانش‌آموزان جدا می‌شوند. همیشه کل جدول را انتخاب و مرتب کنید.
کپی فرمول از فایل دیگر
فرمولی که از کارنامهٔ پایهٔ دیگر کپی شود، ممکن است ضریب‌های آن پایه را با خودش بیاورد.
وقتی اکسل دیگر کافی نیست
کارنامهٔ اکسلی برای یک کلاس خوب کار میکند. وقتی چند معلم نمره وارد میکنند، چند کلاس هست و کارنامه باید به خانواده برسد، فایل‌ها از هم جدا می‌شوند و همان خطاهای بالا تکرار می‌شوند.
| معیار | کارنامه در اکسل | سامانهٔ کارنامه |
|---|---|---|
| ورود نمره | دستی، گاهی چند بار | یک بار، از آزمون‌ها |
| فرمول و قاعده | در هر فایل جدا | یک قاعده برای همه |
| نسخه‌ها | چند فایل | یک منبع |
| تحویل به خانواده | چاپ | پنل ولی |
| توضیح معلم | ستون اضافه | ذیل هر درس |
برای هر نقش، یک قدم عملی
اگر مدیر هستید
اگر کارنامه‌های مدرسه در اکسل ساخته می‌شود، یک فایل الگو با ضریبها و قاعدهٔ گرد کردن مشترک بسازید و برای همهٔ کلاس‌ها بفرستید؛ این کار کوچک بیشتر اختلاف‌های محاسبه را حذف میکند.
اگر معلم هستید
پیش از تحویل فایل، چک‌لیست شش‌مرحله‌ای را مرور کنید، به‌خصوص علامت $ و خانه‌های خالی؛ این دو خطا بیشترین کارنامهٔ غلط را می‌سازند.
اگر ولی هستید
اگر معدل کارنامه با حساب خودتان نمی‌خواند، ضریب درس‌ها را بپرسید؛ در معدل وزنی، درس با ضریب بیشتر اثر بیشتری دارد.
چک‌لیست
- ضریبها با برنامهٔ مصوب پایه می‌خوانند؟
- علامت $ در آدرس ضریبها هست؟
- خانهٔ خالی در نمره‌ها نیست؟
- نمره‌ها عدد شناخته شده‌اند، نه متن؟
- قاعدهٔ گرد کردن با سیاست مدرسه یکی است؟
- فقط یک نسخهٔ معتبر از فایل وجود دارد؟
- حد قبولی با آیین‌نامهٔ مقطع تطبیق داده شده است؟
محدودیت‌ها و پیش‌نیازها
- ضریب درس‌ها و حد قبولی را برنامهٔ مصوب و آیین‌نامهٔ هر مقطع تعیین میکنند؛ عددهای این آموزش نمونه‌اند.
- اکسل برای یک کلاس مناسب است؛ برای چند کلاس و چند معلم، خطای ورود و نسخه‌های موازی زیاد می‌شود.
- در نسخه‌های فارسی اکسل، جداکنندهٔ آرگومان‌ها ممکن است نقطه‌ویرگول باشد.
جمع‌بندی
با SUMPRODUCT، ROUND و RANK کارنامهٔ یک کلاس در اکسل آماده می‌شود، و بیشتر خطاها با علامت $ و بررسی خانه‌های خالی گرفته می‌شوند. اما وقتی چند معلم و چند کلاس درگیرند، مشکل اصلی دیگر فرمول نیست؛ ورود چندباره و نسخه‌های موازی است.
اگر کارنامهٔ مدرسه‌تان از چند فایل جمع می‌شود، درخواست دموی آستین را ثبت کنید تا سامانهٔ کارنامه را با نمرههای خودتان ببینید.
پرسش‌های مرتبط
فرمول معدل با ضریب در اکسل چیست؟
جمع حاصلضرب هر نمره در ضریبش، تقسیم بر جمع ضریبها. در اکسل: =SUMPRODUCT(B3:D3,$B$2:$D$2)/SUM($B$2:$D$2)
معدل را در اکسل چطور تا دو رقم اعشار گرد کنیم؟
فرمول معدل را داخل ROUND با آرگومان ۲ بگذارید. برای گرد کردن به پایین یا بالا از ROUNDDOWN یا ROUNDUP استفاده کنید.
در گوگلشیت هم همین فرمولها کار میکند؟
بله. SUMPRODUCT، ROUND، RANK و IF در گوگلشیت هم به همین شکل کار میکنند.
چرا معدل در اکسل کمتر از انتظار است؟
معمولاً به خاطر خانهٔ خالی که صفر حساب شده، یا نبود علامت $ که ضریبها را جابهجا کرده است.
رتبهٔ کلاسی را چطور حساب کنیم؟
با تابع RANK روی ستون معدل؛ مثلاً =RANK(E3,$E$3:$E$32).
نمرههای زیر حد قبولی را چطور علامت بزنیم؟
با Conditional Formatting و گزینهٔ Less Than، یا با تابع IF در یک ستون جدا.
کی باید از اکسل به سامانهٔ کارنامه رفت؟
وقتی چند معلم نمره وارد میکنند، چند کلاس هست یا کارنامه باید آنلاین به خانواده برسد.
دیدگاه‌ها
برای گذاشتن دیدگاه با حساب آستین وارد شوید.
ورود با حساب آستینهنوز دیدگاهی ثبت نشده — اولین نفر باشید.