I dagens episode snakket jeg om en enkel karakterbokapplikasjon i Excel. Selv om konseptet er enkelt, er det flere formler i karakterboka som kan snu deg opp. Karakterboka ser slik ut:
Vertikale overskrifter
Overskriftene i rad 2 er slått på siden ved å bruke Format - Cells - Alignment. Endre retningen til 90 grader i kategorien Justering.
Poeng mulig rad
Det er viktig å ha en rad på regnearket som viser de totale poengene som er mulige for hver oppgave. Ikke fyll inn poengene som er mulig for en oppgave før du har angitt karakterene for oppgaven. I den første figuren ovenfor er de mulige punktene i rad 17.
Totale formler i kolonne H
Skriv inn en = SUM-funksjon i H4 for å total score for den første studenten. Kopier denne formelen nedover kolonne H for hver elev og for poengene mulig linje. Excel endrer automatisk de relative referansene i formelen for å peke til neste rad osv.
Tricky: Gjennomsnittlige formler i kolonne I
Formelen for I4 kan virke som om den ville være enkel. Du kan prøve å gå inn =H4/H17, men dette vil føre til et problem.
Når du kopierer denne formelen ned noen rader, slutter den å fungere. Her i rad 6 endret formelen til =H6/H19. Det er riktig at Excel endret H4 til H6, men du vil at Excel alltid absolutt skal peke på H17 som deleren i beregningen.
Gå tilbake og rediger formelen i I4. Du kan velge I4 og trykke F2, eller bare dobbeltklikke I4.
Når innsettingspunktet er rett etter H17, trykker du på F4-tasten. Excel setter inn dollartegn for å endre H17 til $ H $ 17.
Dollartegnet før H sier at uansett hvilken retning du kopierer formelen, vil du alltid peke på kolonne H.
Dollartegnet før 17 sier at uansett hvilken retning du kopierer formelen, vil du alltid peke på rad 17.
Dette kalles en absolutt referanse.
Når du kopierer denne formelen til alle studenter, deler Excel alltid riktig med de totale poengene som er mulige i rad 17.
Konvertere prosent til bokstavkarakterer
Du vil bruke VLOOKUP-funksjonen til å konvertere prosenter til bokstavkarakter. Det er to måter å bruke VLOOKUP-funksjonen på, og 90% av tiden på dette nettstedet bruker jeg versjonen der Excel må finne et nøyaktig samsvar i oppslagstabellen. Denne versjonen bruker imidlertid versjonen av VLOOKUP der tabellen inneholder dataområder.
I denne versjonen må oppslagstabellen sorteres i lav til høy rekkefølge. Som du kan se nedenfor, krever dette at du tar skolens karakterskala og snur den på hodet. I tabellen nedenfor sier rad 4 at noen poeng over en 0 vil motta en F. Rad 5 tar over og sier at noen poeng over 65% vil motta en D. I rad 6 er alt på 69% eller høyere en D + . Grunnideen er at Excel vil finne tallet som er lik eller lavere enn studentens poengsum.
- For å bygge VLOOKUP i J4, start med
=VLOOKUP( - Det første argumentet er studentens poengsum i
I4. - Det neste argumentet er området med oppslagstabellen. I dette eksemplet er tabellen i
L4:M12. - Du vil at formelen alltid skal peke på det samme området, så trykk på F4-tasten for å endre den til
$L$4:$M$12. - Det siste argumentet er kolonnenummeret fra tabellen din med bokstavkarakteren. I dette eksemplet er det en
2. - Formelen er
=VLOOKUP(I4,$L$4:$M$12,2).
- Kopier formelen ned for alle studenter.
Versjonen jeg brukte på showet er liten - med få studenter og bare noen få oppgaver. Du kan laste ned denne mindre versjonen for å se hvordan formlene fungerer. Høyreklikk CFHGrades.zip .
