Projekt ININ 2 Jednoduchý systém pro generování a vyhodnocování testů v Excelu
Vedoucí práce: Ing. Vladimír Hanta, CSc. Vypracovali:
Martin Šimeček Pavla Maršíková Martin Langer
V Praze, 20. května 2009
Projekt ININ 2
Obsah Obsah .......................................................................................................................................... 2 1.
Zadání................................................................................................................................. 3
2.
Příklad užití ......................................................................................................................... 3
3.
Dokumentace ..................................................................................................................... 3 3.1.
Soubory projektu ........................................................................................................ 3
3.2.
Požadavky ................................................................................................................... 3
3.3.
XML soubory ............................................................................................................... 4
3.3.1.
Struktura ................................................................................................................. 4
3.4.
Ovládací prvky............................................................................................................. 4
3.5.
Listy ............................................................................................................................ 5
3.5.1.
Základní údaje ......................................................................................................... 5
3.5.2.
Otázky ..................................................................................................................... 5
3.5.3.
Odpovědi ................................................................................................................ 6
3.5.4.
Zpracování .............................................................................................................. 6
3.6.
Vygenerovaný test ...................................................................................................... 7
3.7.
Kompatibilita testů ...................................................................................................... 9
3.8.
Zdrojový kód, postupy, algoritmy ................................................................................ 9
3.8.1.
Zdrojový kód ........................................................................................................... 9
3.8.2.
Výběr otázek ......................................................................................................... 11
3.8.3.
Výběr možností ..................................................................................................... 11
3.8.4.
Vyhodnocení ......................................................................................................... 12
4.
Možnosti rozšíření ............................................................................................................ 12
5.
Odpovědnost .................................................................................................................... 13 5.1.
Martin Šimeček ......................................................................................................... 13
5.2.
Pavla Maršíková ........................................................................................................ 13
5.3.
Martin Langer ........................................................................................................... 13
6.
Seznam obrázků................................................................................................................ 14
7.
Seznam tabulek ................................................................................................................ 14
2
Projekt ININ 2
Zadání
1. Zadání Jednoduchý systém pro generování a vyhodnocování testů v prostředí MS Excelu. Na první konzultaci nám vedoucí práce nastínil představu o výsledku. Dostali jsme tedy za úkol: · sestavit sadu 30 otázek z námi vybraného oboru, · ke každé otázce vymyslet tři možnosti s tím, že pouze jedna odpověď je správná, · otázky bodově ohodnotit, · generovat vždy do nového sešitu test s těmito parametry: o 10 otázek, o náhodně vybraných, o odpovědi ke každé otázce náhodně zamíchané, o dohromady 100 bodů, · následně test zpracovat a vyhodnotit výsledky (známku), · pracovat „v dobré víře“ – neklást zvláštní důraz na zabezpečení. Aby bylo zřejmé, jak systém funguje bez nutnosti probírat se podrobnou dokumentací, je připraven jeden příklad užití demonstrující, jak práce s generátorem probíhá.
2. Příklad užití Učitel připravuje test. Otevře generátor a na prvním listu vyplní základní údaje – název a popis testu. Klikne v panelu nástrojů na Vygenerovat test a zkontroluje výsledek. Ukládá s názvem varianty podle svého uvážení. Tento postup opakuje podle potřeby. Student píše písemku. Stahuje soubor se svou variantou testu a otvírá jej. Přečte si informace a instrukce, vyplní své jméno a studijní skupinu a začíná vyplňovat. Několikrát se splete, systém jej upozorní, ale nakonec žádnou otázku nevynechá a na každou označí právě jednu odpověď. Ukládá test pod svým jménem a odesílá učiteli. Později učitel začíná vyhodnocovat. Přesune všechny testy od studentů do jedné složky, otevře svůj sešit, přejde na list Zpracování a vyplní název prvního souboru s testem. Jedno tlačítko a test je zpracován – hned je vidět známka. Kliknutím na Nové vyhodnocení otvírá čistý list a načítá stejným postupem další soubor s testem. Nakonec má výsledky všech studentů pro daný test na listech v jednom sešitě.
3. Dokumentace 3.1. Soubory projektu Celý projekt tvoří dohromady tři soubory: · generator_2007.xlsm / generator_2003.xls · otazky.xml · odpovedi.xml
3.2. Požadavky Protože vývoj probíhal s pomocí Excelu 2007, je doporučeno používat .xlsm soubor. Verze .xls pro Office 2003 je také k dispozici. Data jsou uložena v XML souborech, které sešit pravidelně načítá a obnovuje. Jejich přítomnost ve stejné složce je tedy nutná. Zároveň jejich použití omezuje podporovanou verzi Office na 2003 a vyšší (dle nápovědy není mapování XML dostupné v nižších verzích). Další omezení nejsou známa. Testování probíhalo ve Windows XP a Vista na celkem třech různých strojích. 3
Projekt ININ 2
Dokumentace
3.3. XML soubory Otázky a varianty jejich odpovědí jsou uloženy v XML souborech (otazky.xml a odpovedi.xml). Tento způsob má několik výhod: 1. snadný import a obnovování v Excelu Díky tomu, že data jsou uložena externě, nehrozí, že dojde k jejich poškození manipulací v Excelu. To velmi usnadnilo testování maker. 2. možnost vytvoření externího nástroje pro správu otázek Škálovatelnost a přenositelnost XML formátu jej předurčuje pro více využití. Nabízí se vytvoření aplikace pro přehlednou správu otázek a odpovědí v mocnějším programovacím jazyce (například v některém z jazyků .NET). Dále je možné rozšířit náš generátor tak, že bude vybírat sady otázek z větší množiny rozdělené do více souborů atd…
3.3.1. Struktura Oba soubory nepoužívají DTD (vzhledem k rozsahu a způsobu použití). Nicméně struktura je takováto: Tabulka 1 Struktura souboru otazky.xml
Element otazky otazka
Atribut id spravne body
Význam Kořenový element dokumentu. Text otázky (zobrazuje se v testu). Identifikátor otázky. Dle něj jsou pak napojeny odpovědi. ID odpovědi, která je v dané otázce považována za správnou. Nabývá hodnot 1-3. Bodové ohodnocení otázky – počet bodů připsaných v případě správné odpovědi.
Tabulka 2 Struktura souboru odpovedi.xml
Element odpovedi odpoved moznost
Atribut otazka id
Význam Kořenový element. ID otázky, ke které daná možnost přísluší (viz výše). Text možnosti (zobrazuje se v testu). Identifikátor otázky. Představuje pořadové číslo. Nabývá hodnot 1-3 a je provázán s atributem spravne u otázek.
3.4. Ovládací prvky Systém se ovládá ze čtyř listů. Dva jsou interaktivní, dva obsahují načtené informace z XML. Popis následuje v části Listy na str. 5.
Obrázek 1 Listy systému
Dále je k dispozici panel nástrojů se třemi tlačítky: Reset, Generovat test a Nový list zpracování.
4
Projekt ININ 2
Obrázek 2 Panel nástrojů, Office 2007
· · ·
Dokumentace
Obrázek 3 Panel nástrojů, Office 2003
Generovat test vytvoří nový sešit a spustí generování testu. Reset obnoví data z XML souborů a seřadí otázky na listu Otázky. Nový list zpracování vytvoří nový list pro vyhodnocení testu v případě, že byly všechny smazány.
3.5. Listy 3.5.1. Základní údaje První list slouží k prostému zadání základních údajů o testu – název a případně popis. Kromě toho obsahuje dvě tlačítka pro rychlý přístup: Generovat a Reset. Dělají totéž, co tlačítka na panelu nástrojů.
Obrázek 4 List Základní údaje
3.5.2. Otázky Obsahuje tabulku s otázkami načtenými z XML souboru otazky.xml. Z hlediska Excelu reflektuje mapování otazky_Mapování. Tabulka obsahuje sloupce otazka, id, spravne a body. Je seřazena podle sloupce body vzestupně (zajišťuje makro Reset()). Z tohoto listu se při generování odebírají otázky, aby byly následně opět obnoveny (viz Zdrojový kód, postupy, algoritmy na str. 9).
Obrázek 5 List Otázky
5
Projekt ININ 2
Dokumentace
3.5.3. Odpovědi Obsahuje tabulku s odpověďmi pro jednotlivé otázky, která je, stejně jako u otázek, načtena z XML souboru (odpovedi.xml). Tabulka je seřazena podle ID otázky, které přísluší. Obsahuje sloupce otazka, moznost, id.
Obrázek 6 List Odpovědi
3.5.4. Zpracování K vyhodnocení slouží poslední list sešitu – Zpracování. Podle použití se dá rozdělit na dvě části: parametrizace a výsledky.
Obrázek 7 List Zpracování - horní část (parametrizace)
Obrázek 8 List Zpracování – dolní část (výsledky)
V horní části je výzva k zadání názvu souboru s testem. Ten se musí nacházet ve stejném adresáři jako generátor. Kliknutím na tlačítko Vyhodnotit se jej systém pokusí otevřít a v případě úspěchu zpracuje výsledky. Dolní část slouží k zobrazení výsledků otevřeného testu. Načte se název testu, jméno řešitele, jeho studijní skupina a následně se jedna po druhé zpracují odpovědi na otázky. Dle bodového hodnocení se vypočte výsledná známka. V praxi to ukazuje obrázek Vyhodnocený test.
6
Projekt ININ 2
Dokumentace
Za povšimnutí stojí několik detailů: Jméno řešitele se nejen načetlo do příslušného pole, ale i list byl přejmenován, aby bylo patrné, komu výsledek patří. Pro větší přehlednost jsou pole s body obarvena. Špatná odpověď červeně, správná zeleně. Známka v posledním řádku se dopočítává automaticky vzorcem. Editovatelnými buňkami jsou pouze základní údaje a bodová hodnocení.
Obrázek 9 Vyhodnocený test
Pod tabulkou jsou dvě tlačítka pro další práci s vyhodnocením. Nové vyhodnocení vytvoří nový, nevyplněný list Zpracování, Vyčistit odstraní z aktuálního listu všechna data a přejmenuje jej na Zpracování.
Obrázek 10 Dolní tlačítka na listu Zpracování
3.6. Vygenerovaný test Nový test se generuje do nového sešitu. Nový sešit obsahuje název testu, zde v ukázce je to „Testovací test“, a popisek. Následují pole pro zadání jména studenta a jeho studijní skupiny. Název a popis jsou od zbytku testu ještě odděleny tzv. oblastí upozornění (modrý kroužek). Vygenerovaný test obsahuje 10 otázek. Otázky jsou různé obtížnosti. Do testu jsou vybrány tři jednoduché otázky, čtyři středně obtížné a tři otázky největší obtížnosti. Každá otázka je modře zvýrazněna, je u ní uvedeno její pořadí a kolika body je ohodnocena (na obrázku v červeném kroužku). Ke každé otázce přísluší tři možnosti odpovědí a nalevo od otázek (v zeleném kroužku) a odpovědí jsou uvedeny pomocné hodnoty - jejich původní ID v seznamu všech otázek / odpovědí. Tento pomocný sloupec je viditelný pouze při testování. Dá se skrýt přepnutím konstanty DebugMode v mKonstanty na False.
7
Projekt ININ 2
Dokumentace
Obrázek 11 Ukázka vygenerovaného testu
Označení správných odpovědí je vyřešeno tak, že se do buňky vedle možností (na obrázku u druhé otázky zvýrazněno žlutě) vloží symbol „x“. Tento symbol lze vložit pouze do buněk určených pro odpovědi, ostatní buňky listu jsou zamčené. Lze také vybrat pouze jednu správnou odpověď. Pokud uživatel vybere více odpovědí nebo neoznačí žádnou, bude mu za příslušnou otázku přiděleno 0 bodů. Stejně tak dostane 0 bodů za špatnou odpověď, žádné body se neodečítají. Formálně správné vyplnění testu hlídá oblast upozornění. Využívá podmíněného formátování a pomocné oblasti ve sloupci G. Slouží pouze jako upozornění uživateli, že vynechal otázku, případně označil více možností. Z hlediska vyhodnocení nemá žádnou váhu (kontrola probíhá při zpracování znovu). Obrázek 12 Upozornění na nedokončený test
Jakmile je test dokončen, změní se oblast takto: Obrázek 13 Upozornění na dokončený test
Pokud by chtěl uživatel vybrat správnou odpověď vložením jiného symbolu, např. „y“, jak je na obrázku vyznačeno v červeném kroužku, tak se zobrazí okno se zprávou, že nelze vložit jiný symbol. Toto ošetření je nastavené pomocí ověření dat, kde je vybrán seznam možností a do něj je vložen pouze jeden symbol („x“). Rozevírací seznam je skryt.
8
Projekt ININ 2
Dokumentace
Obrázek 14 Ověření označení odpovědi
List s testem je uzamčen (pro naše účely bez hesla) s tím, že se dá pracovat pouze s těmito buňkami: · C4:C5 – jméno a studijní skupina studenta, · příslušná políčka ve sloupci B pro označení odpovědí. Z hlediska zabezpečení platí, že jakékoliv nepovolené úpravy testu mohou pouze uškodit: sešit neobsahuje žádný VBA kód, zpracování probíhá až zpět v generátoru; vyplnění jakékoliv jiné hodnoty než „x“ způsobí započtení 0 bodů; přidání buněk znemožní zpracování testu – opět 0 bodů. Po vyplnění se test uloží a odešle k vyhodnocení.
3.7. Kompatibilita testů Protože může v generátoru docházet ke změnám (přidávání dalších informačních polí apod.) a protože zpracovávání zcela závisí na umístění jednotlivých prvků na listu, je na každém vygenerovaném testu mimo viditelnou oblast umístěn identifikátor generátoru. Obsahuje uvozující řetězec a verzi. Například: ININ generator:1.0. Před zpracováním se pak tento řetězec hledá a porovnává se s aktuální verzí generátoru. Pokud nesouhlasí, zobrazí se chybové hlášení a sešit se zavře.
Obrázek 15 Kontrola verze generátoru
3.8. Zdrojový kód, postupy, algoritmy 3.8.1. Zdrojový kód Většina VBA kódu je logicky rozdělena do modulů. Zbytek se nachází v ThisWorkbook (vytvoření nástrojových panelů). Kód je důkladně okomentován, takže detaily jsou popsány přímo v něm. Rozdělení přehledně shrnuje následující tabulka:
9
Projekt ININ 2
Dokumentace
Tabulka 3 Moduly, makra, funkce
Modul mKonstanty
Makro / Funkce
Význam
Public Const DebugMode As Boolean = True
Nastavená na True zajistí zobrazení pomocných informací na listu s testem. Pro účely ladění a prezentace zapnuto. Verze generátoru. Pokud by došlo ke změnám ovlivňujícím vyhodnocení (např. pozice otázek, možností apod...), je nutno ji změnit. Určuje pozici první otázky na listu s testem. Z tohoto čísla vychází generování, formátování i vyhodnocení. Zároveň použití konstanty zjednodušuje úpravy rozložení testu. Druhá důležitá konstanta – udává, kolik buněk dělí první řádky jednotlivých otázek. Zvýšení = větší mezera mezi otázkami. Počet otázek. Používá se v některých výpočtech. Umístění sloupce s pomocnými informacemi na listu s testem. Naformátuje celý vygenerovaný test a nakonec jej uzamkne. Pomocné makro – zpracovává oblast upozornění testu. Hlavní makro pro generování testu. Dostává jako parametr nový sešit, do něhož se generuje. Vybere náhodnou otázku z listu Otázky v daném rozmezí, umístí ji do testu a odstraní z původního seznamu. Doplní k otázce její možnosti.
Public Const generatorVersion As String = "1.0" Public Const pozicePrvniOtazky As Integer = 7 Public Const vzdalenostOtazek As Integer = 5 Public Const pocetOtazek As Integer = 30 Public Const metaSloupec As Integer = 1
mFormatovani
mGenerovani
Sub Formatovani(listTest As Worksheet, startRow As Integer) Sub oblastUpozorneni(listTest As Worksheet) Sub VygenerovatTest(wb As Workbook) Sub VybratOtazku(dolniHranice, horniHranice, listOtazky, listTest As Worksheet) Sub PridatMoznosti(idOtazky As Integer, listTest As Worksheet) Sub Zamichej(moznosti() As String, oblast As Range) Sub PridatKontrolniVypocty()
mPodpurnaMakra
Sub Reset() Sub SeraditOtazky()
Sub generujTest_Click() Sub reset_Click() Sub newZprac_Click() Sub Import()
mZpracovani
Sub OpenTest() Sub Zpracuj(wb As Workbook, listZprac As Worksheet) Sub NacistOtazky()
Function NajitOtazku(ByVal ID As Integer) As Integer
Zpřehází možnosti tak, aby byly na testu v náhodném pořadí. Přidá do pomocné oblasti výpočty pro zjišťování vyplnění testu. Slouží k upozornění na vynechanou otázku. Obnoví připojení XML a znovu načte všechna data. Otázky navíc seřadí. Pomocné makro pro seřazení otázek podle bodového ohodnocení (nutné pro správné generování). Handlery tlačítek.
Nastavuje mapování XML tak, aby načítalo relativně k sešitu. Otevře test daného názvu a v případě úspěchu spustí vyhodnocení. Samotné vyhodnocení. Pomocné makro pro načtení podstatných informací (ID, správná odpověď a body) do pole otazky(). Vyhledá v poli otazky() otázku se zadaným ID a vrátí její pozici v poli (pomocná funkce).
10
Projekt ININ 2 Modul
Dokumentace Makro / Funkce
Význam
Function PraveJednaOdpoved(oblast As Range) As Boolean Sub Cleanup()
Zkontroluje, zda je označena právě jedna odpověď v oblast. Odstraní všechny informace z právě otevřeného listu Zpracování. Připraví nový list Zpracování.
Sub NoveVyhodnoceni()
3.8.2. Výběr otázek Otázky se vybírají náhodně tak, aby výsledný součet bodů byl 100. Znamená to tedy vytáhnout tři lehké (po pěti bodech), čtyři střední (po deseti bodech) a tři těžké (po patnácti bodech). Využívá se toho, že tabulka na listu Otázky je seřazená podle bodového hodnocení – program tak přesně ví, kde která sekce začíná a kde končí. Algoritmus je prostý – náhodně „sáhne“ do daného rozsahu otázek, vybere jednu a vloží ji do předpřipraveného nového sešitu. Aby se zajistilo, že se nevyberou dvě stejné otázky, je následně celý řádek odstraněn. Po skončení generování se zavolá makro Reset(), které seznam obnoví a připraví ho k dalšímu použití.
Obrázek 16 Náhled na algoritmus výběru otázky
3.8.3. Výběr možností Součástí zadání je vytváření permutací možných odpovědí. Zajišťuje jej makro Zamichej() v modulu mGenerovani. Vstupními parametry jsou: dvourozměrné pole moznosti(), v kterém jsou uloženy texty a ID možností, a oblast typu Range, v níž míchání probíhá. Základem je cyklus for o třech iteracích (i = 0-2). V každé iteraci proběhne tento sled kroků: 1. vygeneruje se náhodné číslo (x) v rozsahu od právě probíhající iterace (0, 1, 2) do 2, 2. do pomocné proměnné y se dosadí text možnosti na místě x, 3. do pomocné proměnné ydy se dosadí ID možnosti na místě x, 4. text možnosti na místě x se nahradí textem právě iterované možností (i), 5. totéž se provede s ID, 6. pomocná proměnná y přejde do textu právě iterované možnosti (i), 7. totéž s ydy a ID, 8. přechází se do další iterace. Následně se jen přeházené možnosti nahradí stávající na listu s testem.
11
Projekt ININ 2
Možnosti rozšíření
Obrázek 17 Náhled na algoritmus míchání odpovědí
3.8.4. Vyhodnocení Zpracování testu začíná otevřením jeho sešitu a kontrolou verze generátoru. Jakmile tyto dvě operace proběhnou úspěšně, přečte se název testu, jméno studenta a číslo jeho studijní skupiny. Poté začíná hlavní cyklus. Pro každou otázku se odehrává tento sled kroků: - zkontroluje se, zda je označena právě jedna odpověď; není-li, je otázka ohodnocena 0 body, - vyhledá se, která možnost je označena symbolem „x“ (z použití WorksheetFunction.Match), - z pomocného sloupce se přečte její ID, - z pomocného sloupce se přečte také ID otázky, - podle něj se vyhledá pozice otázky v poli otazky(), - a pak už se jen porovná, zda je ID označené odpovědi shodné s ID odpovědi uložené u otázky jako spravne, - pokud jsou čísla stejná, zapíše se počet bodů (body), jinak 0. Nakonec se aktivuje list Zpracování s vyplněnými a vypočtenými výsledky.
4. Možnosti rozšíření Protože komplexnost systému je vzhledem k času a náročnosti omezená, nabízí se prostor pro několik rozšíření. 1. Přidávání, odebírání a úprava otázek – pomocí vytvořené externí aplikace zpracovávající XML soubory. 2. Maximalizace univerzálnosti testů – odstranění omezení počtů otázek a možností, automatické přepočty bodů na škálu 0-100 s ohledem na konkrétní ohodnocení otázky atd… 3. Shrnutí výsledků všech studentů – nyní jsou jednotlivé výsledky na svých vlastních listech, rozšířením by bylo shrnout tyto údaje v jedné tabulce na samostatném listu. 4. Export výsledků do XML – umožnění uložit výsledky testu do XML a následně je zpracovat další aplikací.
12
Projekt ININ 2
Odpovědnost
5. Odpovědnost 5.1. Martin Šimeček · · · · · · · · · · ·
hlavní návrh, vedení a koordinace práce, rozdělování úkolů, veškerý zdrojový kód, návrh XML struktury, import dat, generování testu, algoritmus výběru otázek, implementace algoritmu míchání odpovědí, design, automatické formátování testu, zpracování a vyhodnocení testu, dokumentace (zpráva o projektu - tento dokument), prezentace
5.2. Pavla Maršíková · · · · · · ·
tvorba otázek a odpovědí, spolupráce na jejich bodovém ohodnocení, vytvoření XML souborů, návrh podoby testu, základ pro makro formátování testu, ověření označení odpovědi pouze pomocí „x“, vzorec pro výpočet výsledné známky testu, text – „Vygenerovaný test“
5.3. Martin Langer · · · · ·
způsob výběru otázek do testu dle bodového ohodnocení (schéma 3-4-3), vyhledávání algoritmů a informací na internetu, makro pro vytvoření panelu nástrojů (a jeho zrušení při uzavření sešitu), „oblast upozornění“ – dynamická kontrola validního vyplnění testu (podmíněné formátování, pomocné výpočty), návrh kódu pro vyhodnocení
13
Projekt ININ 2
Seznam obrázků
6. Seznam obrázků Obrázek 1 Listy systému ............................................................................................................... 4 Obrázek 2 Panel nástrojů, Office 2007.......................................................................................... 5 Obrázek 3 Panel nástrojů, Office 2003.......................................................................................... 5 Obrázek 4 List Základní údaje ....................................................................................................... 5 Obrázek 5 List Otázky ................................................................................................................... 5 Obrázek 6 List Odpovědi ............................................................................................................... 6 Obrázek 7 List Zpracování - horní část (parametrizace) ................................................................. 6 Obrázek 8 List Zpracování – dolní část (výsledky) ......................................................................... 6 Obrázek 10 Dolní tlačítka na listu Zpracování ............................................................................... 7 Obrázek 9 Vyhodnocený test........................................................................................................ 7 Obrázek 11 Ukázka vygenerovaného testu ................................................................................... 8 Obrázek 12 Upozornění na nedokončený test .............................................................................. 8 Obrázek 13 Upozornění na dokončený test .................................................................................. 8 Obrázek 14 Ověření označení odpovědi ....................................................................................... 9 Obrázek 15 Kontrola verze generátoru ......................................................................................... 9 Obrázek 16 Náhled na algoritmus výběru otázky ........................................................................ 11 Obrázek 17 Náhled na algoritmus míchání odpovědí .................................................................. 12
7. Seznam tabulek Tabulka 1 Struktura souboru otazky.xml ...................................................................................... 4 Tabulka 2 Struktura souboru odpovedi.xml .................................................................................. 4 Tabulka 3 Moduly, makra, funkce............................................................................................... 10
14
