I dagens episode talte jeg om en simpel klassifikationsapplikation i Excel. Selvom konceptet er simpelt, er der flere formler i karakterbogen, der kan slå dig op. Karakterbogen ser sådan ud:
Lodrette overskrifter
Overskrifterne i række 2 vendes på deres side ved hjælp af Format - Celler - Alignment. Under fanen Justering skal du ændre retning til 90 grader.
Point mulig række
Det er vigtigt at have en række på regnearket, der viser de samlede mulige point for hver opgave. Udfyld ikke de mulige point for en opgave, før du har indtastet karaktererne for den opgave. I den første figur ovenfor er de mulige punkter i række 17.
Samlede formler i kolonne H
Indtast en = SUM-funktion i H4 for at samle score for den første elev. Kopier denne formel ned i kolonne H for hver elev og for den mulige pointlinje. Excel ændrer automatisk de relative referencer i formlen for at pege på den næste række osv.
Tricky: Gennemsnitlige formler i kolonne I
Formlen til I4 kan virke som om den ville være enkel. Du kan prøve at komme ind =H4/H17, men dette vil medføre et problem.
Når du kopierer denne formel ned et par rækker, holder den op med at arbejde. Her i række 6 blev formlen ændret til =H6/H19. Det er korrekt, at Excel ændrede H4 til H6, men du vil have, at Excel altid absolut peger på H17 som deleren i beregningen.
Gå tilbage og rediger formlen i I4. Du kan vælge I4 og trykke på F2 eller blot dobbeltklikke på I4.
Når indsættelsespunktet er lige efter H17, skal du trykke på F4-tasten. Excel indsætter dollartegn for at ændre H17 til $ H $ 17.
Dollartegnet før H siger, at uanset hvilken retning du kopierer formlen, vil du altid pege på kolonne H.
Dollartegnet før 17 siger, at uanset hvilken retning du kopierer formlen, vil du altid pege på række 17.
Dette kaldes en absolut reference.
Når du kopierer denne formel til alle studerende, deler Excel altid korrekt med de samlede mulige point i række 17.
Konvertering af procentdele til bogstavkarakterer
Du bruger VLOOKUP-funktionen til at konvertere procenter til en bogstavkarakter. Der er to måder at bruge VLOOKUP-funktionen på, og 90% af tiden på dette websted bruger jeg den version, hvor Excel skal finde et nøjagtigt match i opslagstabellen. Denne version bruger dog versionen af VLOOKUP, hvor tabellen indeholder dataintervaller.
I denne version skal opslagstabellen sorteres i lav til høj rækkefølge. Som du kan se nedenfor, kræver dette, at du tager din skolekarakterskala og vender den på hovedet. I nedenstående tabel siger række 4, at alle scoringer over en 0 vil modtage en F. Række 5 overtager og siger, at enhver score over 65% vil modtage en D. I række 6 er alt ved 69% eller derover en D + . Grundideen er, at Excel finder det tal, der er lig med eller lavere end den studerendes score.
- For at opbygge VLOOKUP i J4 skal du starte med
=VLOOKUP( - Det første argument er den studerendes score i
I4. - Det næste argument er området med din opslagstabel. I dette eksempel er tabellen i
L4:M12. - Du vil have, at formlen altid skal pege på det samme interval, så tryk på F4-tasten for at ændre den til
$L$4:$M$12. - Det sidste argument er søjlenummeret fra din tabel med bogstavkarakteren. I dette eksempel er det en
2. - Formlen er
=VLOOKUP(I4,$L$4:$M$12,2).
- Kopier formlen ned for alle studerende.
Den version, jeg brugte på showet, er lille - med få studerende og kun få opgaver. Du kan downloade denne mindre version for at se, hvordan formlerne fungerer. Højreklik på CFHGrades.zip .
