U današnjoj epizodi govorio sam o jednostavnoj aplikaciji ocjena u Excelu. Iako je koncept jednostavan, u udžbeniku postoji nekoliko formula koje vas mogu spotaknuti. Knjiga ocjena izgleda ovako:
Okomiti naslovi
Naslovi u retku 2 okreću se na bok pomoću Oblikovanje - Stanice - Poravnanje. Na kartici Poravnanje promijenite orijentaciju na 90 stupnjeva.
Bodovi Mogući red
Važno je da u proračunskoj tablici postoji redak s popisom ukupnih bodova mogućih za svaki zadatak. Nemojte popunjavati bodove za zadatak dok ne unesete ocjene za taj zadatak. Na prvoj gornjoj slici mogući su bodovi u retku 17.
Ukupne formule u stupcu H
Unesite funkciju = SUM u H4 da biste zbrojili bodove za prvog učenika. Kopirajte ovu formulu dolje u stupac H za svakog učenika i za liniju mogućih bodova. Excel automatski mijenja relativne reference u formuli kako bi usmjerile na sljedeći redak itd.
Lukavo: Prosječne formule u stupcu I
Formula za I4 može se činiti kao da bi bila jednostavna. Možete pokušati ući =H4/H17, ali to će uzrokovati problem.
Kad kopirate ovu formulu u nekoliko redaka, ona prestaje raditi. Ovdje u retku 6, formula se promijenila u =H6/H19. Točno je da je Excel promijenio H4 u H6, ali želite da Excel uvijek apsolutno pokazuje H17 kao djelitelj u izračunu.
Vratite se i uredite formulu u I4. Možete odabrati I4 i pritisnuti F2, ili jednostavno dvokliknuti I4.
Kad je točka umetanja odmah nakon H17, pritisnite tipku F4. Excel ubacuje znakove dolara za promjenu H17 u 17 USD.
Znak dolara ispred H kaže da bez obzira u kojem smjeru kopirate formulu, uvijek želite pokazati na stupac H.
Znak dolara prije 17 govori da bez obzira u kojem smjeru kopirate formulu, uvijek želite pokazati na 17. red.
To se naziva apsolutnom referencom.
Kada kopirate ovu formulu na sve učenike, Excel uvijek pravilno dijeli s ukupnim brojem bodova u retku 17.
Pretvaranje postotaka u slovne ocjene
Pomoću funkcije VLOOKUP pretvorit ćete postotke u slovne ocjene. Postoje dva načina za upotrebu funkcije VLOOKUP, a 90% vremena na ovom mjestu koristim verziju u kojoj Excel mora pronaći točno podudaranje u tablici pretraživanja. Međutim, ova inačica koristi verziju VLOOKUP-a gdje tablica sadrži raspone podataka.
U ovoj se verziji tablica pretraživanja mora sortirati prema niskom prema visokom redoslijedu. Kao što možete vidjeti u nastavku, ovo zahtijeva da uzmete školsku ljestvicu za ocjenjivanje i okrenete je naopako. U tablici dolje, redak 4 kaže da će bilo koji rezultat iznad 0 dobiti F. redak 5, i da će svaki rezultat iznad 65% dobiti D. U 6. redu, sve što ima 69% ili više je D + . Osnovna je ideja da će Excel pronaći broj koji je jednak ili manji od studentskog rezultata.
- Za izgradnju VLOOKUP-a u J4 započnite s
=VLOOKUP( - Prvi argument je studentski rezultat u
I4. - Sljedeći je argument raspon s vašom tablicom pretraživanja. U ovom primjeru tablica je u
L4:M12. - Želite da formula uvijek pokazuje na isti taj raspon, pa pritisnite tipku F4 da biste je promijenili u
$L$4:$M$12. - Posljednji argument je broj stupca iz vaše tablice s slovnom ocjenom. U ovom primjeru to je a
2. - Formula je
=VLOOKUP(I4,$L$4:$M$12,2).
- Kopirajte formulu za sve studente.
Verzija koju sam koristio u emisiji je mala - s malo učenika i samo nekoliko zadataka. Možete preuzeti ovu manju verziju da biste vidjeli kako funkcioniraju formule. Desnom tipkom miša kliknite CFHGrades.zip .
