របៀបធ្វើតារាងសម្រង់វត្តមានក្នុង Excel - Student Attendance Tracking List in Excel (Video in Khmer)

Hello everyone! In today's video tutorial, you will learn a simple step-by-step method to create an automated student or employee attendance tracking table in Excel.

Tracking attendance manually takes a lot of time. By using simple Excel formulas like COUNTIF, you can calculate total Present (P), Absent (A), and Permission/Leave days automatically!

🛠️ Step-by-Step Guide: Student Attendance Tracking

Step 1: Set Up Attendance Column Headers

1. Create header columns for No., Student Name, Gender, and daily dates across the month (1, 2, 3, ..., 31).
2. Add summary columns at the end of your table:
   • Total Present (P)
   • Total Absent (A)
   • Permission (Leave)
   • Attendance Rate (%)

Step 2: Use Status Shortcodes

Enter shortcodes in daily attendance cells:

• P = Present
• A = Absent
• Permission = Permission / Leave

Step 3: Write COUNTIF Formulas to Calculate Totals

Assuming daily attendance data for a student is in range D2:AH2:

• Calculate Total Present Days:

  =COUNTIF(D2:AH2, "P")
  

• Calculate Total Absent Days:

  =COUNTIF(D2:AH2, "A")
  

• Calculate Total Permission Days:

  =COUNTIF(D2:AH2, "Permission")
  

💡 Pro Tips

1. Highlight Absent Cells Automatically (Conditional Formatting):
   • Select your daily attendance grid (D2:AH30).
   • Go to Home tab ➔ Conditional Formatting ➔ Highlight Cells Rules ➔ Equal To...
   • Type A and select Light Red Fill with Dark Red Text so absent entries stand out immediately!
2. Create Status Drop-Down Menu (Data Validation):
   • Select your attendance cells ➔ Go to Data tab ➔ Data Validation ➔ Allow List ➔ Type Source: P, A, Permission.
   • This creates a drop-down arrow in every cell so users can click to select status instead of typing manually!
3. Calculate Attendance Rate Percentage:
   • To get the total attendance percentage rate:

     =COUNTIF(D2:AH2, "P") / COUNTA(D2:AH2)
     

   • Change the cell format to Percentage (0.0%).