I dagens avsnitt talade jag om en enkel klassboksprogram i Excel. Även om konceptet är enkelt finns det flera formler i betygsboken som kan hjälpa dig. Betygsboken ser ut så här:
Vertikala rubriker
Rubrikerna i rad 2 vänds på deras sida med hjälp av Format - Celler - Alignment. Ändra orienteringen till 90 grader på fliken Justering.
Poäng möjlig rad
Det är viktigt att ha en rad i kalkylbladet som visar de totala poängen som är möjliga för varje uppgift. Fyll inte i de möjliga poängen för en uppgift förrän du har angett betyg för den uppgiften. I den första figuren ovan är möjliga poäng i rad 17.
Totala formler i kolumn H
Ange en = SUM-funktion i H4 för att sammanställa poängen för den första eleven. Kopiera denna formel nedåt kolumn H för varje elev och för poängen möjlig rad. Excel ändrar automatiskt de relativa referenserna i formeln för att peka på nästa rad etc.
Tricky: Genomsnittsformler i kolumn I
Formeln för I4 kan verka som om den skulle vara enkel. Du kan försöka komma in =H4/H17, men detta kommer att orsaka problem.
När du kopierar den här formeln några rader slutar den fungera. Här i rad 6 ändrades formeln till =H6/H19. Det är korrekt att Excel ändrade H4 till H6, men du vill att Excel alltid ska absolut peka på H17 som delaren i beräkningen.
Gå tillbaka och redigera formeln i I4. Du kan välja I4 och trycka på F2 eller helt enkelt dubbelklicka på I4.
När insättningspunkten är precis efter H17, tryck på F4-tangenten. Excel sätter in dollartecken för att ändra H17 till $ H $ 17.
Dollartecknet före H säger att oavsett vilken riktning du kopierar formeln vill du alltid peka på kolumn H.
Dollartecknet före 17 säger att oavsett vilken riktning du kopierar formeln vill du alltid peka på rad 17.
Detta kallas en absolut referens.
När du kopierar den här formeln till alla elever delar Excel alltid korrekt med de totala poängen som är möjliga i rad 17.
Konvertera procent till bokstavsbetyg
Du använder VLOOKUP-funktionen för att konvertera procentsatser till bokstavsklass. Det finns två sätt att använda VLOOKUP-funktionen och 90% av tiden på den här webbplatsen använder jag versionen där Excel måste hitta en exakt matchning i uppslagstabellen. Denna version använder dock versionen av VLOOKUP där tabellen innehåller dataintervall.
I den här versionen måste uppslagstabellen sorteras i låg till hög ordning. Som du kan se nedan kräver detta att du tar din skolbetygsskala och vänder den upp och ner. I tabellen nedan säger rad 4 att alla poäng över en 0 kommer att få en F. Rad 5 tar över och säger att alla poäng över 65% kommer att få en D. I rad 6 är allt på 69% eller högre en D + . Grundidén är att Excel hittar det antal som är lika med eller lägre än studentens poäng.
- För att bygga VLOOKUP i J4, börja med
=VLOOKUP( - Det första argumentet är studentens poäng i
I4. - Nästa argument är intervallet med din uppslagstabell. I det här exemplet är tabellen i
L4:M12. - Du vill att formeln alltid ska peka på samma intervall, så tryck på F4-knappen för att ändra den till
$L$4:$M$12. - Det sista argumentet är kolumnnumret från din tabell med bokstavsbetyget. I det här exemplet är det en
2. - Formeln är
=VLOOKUP(I4,$L$4:$M$12,2).
- Kopiera formeln för alla elever.
Versionen som jag använde på showen är liten - med få studenter och bara några få uppgifter. Du kan ladda ner den här mindre versionen för att se hur formlerna fungerar. Högerklicka på CFHGrades.zip .
