How to create an interactive test. Creating interactive tests in MS Excel
Master Class
“Creation and use of interactive tests in the practice of teaching physics and preparing for the Unified State Exam”
The purpose of the master class : demonstrate one of the techniques for effectively using the capabilities of the educational portal “Solve the Unified State Exam”http:// phys. resolve. ru/ in the process of preparing students for the Unified State Exam in physics.
Tasks:
present the theoretical basis for the use of interactive tests in the practice of teaching physics;
demonstrate the teacher’s practical experience in creating interactive tests using the capabilities of the “Solve the Unified State Exam” portalhttp:// phys. resolve. ru/ ;
organize independent activities of students to master the techniques of an effective system for assessing knowledge in physics in the process of preparing for the Unified State Exam through distance learning;
to promote the development of professional competencies among teachers in the field of using information and communication technologies in the educational process;
conduct a reflection on the joint activities of the master teacher and students;
present master class materials to further disseminate the experience of the master teacher.
Expected learning outcomes: as a result of training, teachers should know the capabilities of the educational portal “Solve the Unified State Exam”; be able to technically and methodologically correctly structure a lesson using modern educational technologies.
Master class plan
Definition of the conceptual field, theoretical foundations of the use of interactive testing in the process of teaching physics.
Demonstration by a master teacher of the capabilities of the educational portal “Solving the Unified State Exam”.
Practical work of students (mastering the work experience of a master teacher).
Summing up the master class.
By teaching others, we learn ourselves.
L.A. Seneca
The most important task of education is to teach school graduates to live and work in the world of global information and communication networks, the Internet, and multimedia resources. By developing information competence, prepare the student to perceive, understand, and process various scientific information.
One of the tasks of everyday teaching is the need to monitor students' knowledge. The forms of control used by teachers are very diverse, but the most commonly used are written or oral surveys. Unfortunately, these forms are not without drawbacks. In modern schools, various forms of tests are becoming increasingly important, due to the fact that the main form of passing the Unified State Examination is testing. Reality requires students to develop the skill of working with test tasks throughout the entire school year. During such training, appropriate psychotechnical skills of self-regulation and self-control are developed.Testing puts all students on equal terms, practically eliminating the subjectivity of the teacher. One of the main advantages of testing is the minimum time required to obtain reliable control results. When testing, both paper and electronic versions are used. The latter are especially attractive because they allow you to get results almost immediately upon completion of the test.
Testing in pedagogy performs three main interrelated functions: diagnostic, teaching and educational.
Diagnostic function is to identify the level of knowledge, skills and abilities of the student. In terms of objectivity, breadth and speed of diagnosis, testing surpasses all other forms of pedagogical control.
Educational function testing is to motivate the student to intensify the work on mastering the educational material.
Educational function manifests itself in the frequency and inevitability of test control. This disciplines, organizes and directs the activities of students, helps to identify and eliminate gaps in knowledge, and creates a desire to develop their abilities.
The use of electronic publications in the educational process facilitates the lesson, allows the use of an individual, differentiated approach when providing feedback between the student and the teacher, and provides significant assistance to the teacher in preparing for the lesson. Interactive learning tasks have a positive impact on student motivation and interest in the material being studied. Ready-made electronic manuals do not always correspond to specific conditions: the characteristics of the class, the requirements of the program, the goals of the lesson, so the teacher can learn how to create interactive tests using available programs. In order to improve my level of computer literacy, I mastered the capabilities of the services of the educational portal “Solve the Unified State Exam” .
Onlinetesting allows you to interest students who like to do tasks on the computer, and there are many of them. The results of a student survey confirm the feasibility of using interactive tests: 91% of students responded that they liked working with interactive learning tasks, and 85% of students had increased interest in learning physics. Students note that learning is easier and the information received is assimilated quite firmly. Using the capabilities of the educational portal “Solve the Unified State Exam” allows, if you have computers, to work with tests not only in class, but also at home when doing homework and preparing for the Unified State Exam.
The use of interactive tests helps improve the level of information and communication literacy of teachers and students and is aimed at solving the most important task of education - to teach school graduates to work in the world of global informatization. The use of interactive tests is aimed at developing educational-cognitive, value-semantic, information and communication competencies. Conditions are created that allow students to develop the ability to answer questions, set goals, and make decisions. The competence of personal self-improvement is manifested in the aspect of intellectual self-development, emotional self-regulation, independence and self-esteem.
The student masters creative skills of productive activity: acquiring knowledge, mastering methods of action in non-standard situations, heuristic methods of solving problems. This contributes to the formation of educational and cognitive competence. This form of monitoring students’ knowledge arouses interest and contributes to solving the educational problems of the school.
Services of the educational portal “Solve the Unified State Exam” http:// phys. resolve. ru
The distance learning system for preparing for the exam “I WILL SOLVE the Unified State Exam” (http://reshuege.rf, http://reshuege.ru) was created by the creative association “Center for Intellectual Initiatives”. Head - mathematics teacher at gymnasium No. 261 of St. Petersburg, Honorary Worker of General Education of the Russian Federation, Teacher of the Year in Russia - 2007, member of the Federal Commission for the development of testing and measuring materials in mathematics for the unified state exam in mathematics (2009-2010), expert of the Federal subject Unified State Examination Commission in Mathematics (2011-2012), Deputy Chairman of the State State Examination Regional Subject Commission in Mathematics (2012-2013) Gushchin D. D.
The database of assignments on the educational portal is compiled on the basis of the following sources: assignments from open banks and official collections for preparing for the Unified State Exam; demo versions of the Unified State Examination and examination tasks developed by the Federal Institute of Pedagogical Measurements; diagnostic works prepared by the Moscow Institute of Open Education; training work carried out by educational authorities in various regions of the Russian Federation.
All tasks used in the system are provided with answers and detailed solutions.
The teacher can create an unlimited number of test papers he needs, using random test generation, selecting certain tasks from the catalog, or including his own tasks in the work. To do this, you need to go through a simple registration on the portal. Then go to the “Teacher” tab.
For each work, the system will issue an individual link containing the option number, which must be communicated to students via email. Students (at home or at school) enter the received link on the “Student” page, take the test and save the results by clicking the “Save Results” button. Students must also first register on the “Solve the Unified State Exam” portal.
If you want students to see the correct solutions to assignments after finishing their work, select “Compose homework.” When you select “Create a test”: assignment numbers will not be displayed in the text of the work, and the points scored, answers and solutions to assignments will appear in students’ statistics only after the teacher has checked the work.
There is no need to first enter students' names and surnames into the system: their results will appear in the system automatically as soon as they complete and save the work compiled by the teacher.
The system automatically checks the solutions to the tasks of parts A and B, and also displays the solutions to the tasks of part C uploaded by students on the screen to the teacher. The teacher can view, evaluate and comment on them.
The system remembers the created jobs and the results of their execution: .
The summary results of work for each group of students created by the teacher are automatically entered into . If students complete the same task several times, all their results will be recorded in the journal. Unnecessary entries can be deleted (recovery is not possible). Results can be exported to spreadsheetsExcel.
You can create a test from the required number of tasks randomly selected by the system on a particular topic. You can do the same , indicating their catalog numbers or adding your own tasks to the system.
You can also create your own course in the “School” section and interact remotely with students: post teaching materials, provide work numbers to control knowledge, receive questions and answer them.
In the “Expert” section, general criteria for assessing tasks with a detailed answer are presented. For each specific task, they are specified and clarified. The “Methodological Instructions” pages contain Part C assignments, criteria for checking solutions, and the student solutions themselves, which you can evaluate. By clicking the "Check" button, you will see comments about the rating. Testing practice exercises are available on the Go to Review pages. After the verification is completed, the results are summed up. Working in this section, the student independently tries himself in the role of an expert.
In the “Methodologist” section you can find options for early Unified State Examination, training work for MIOO, etc.
There are opportunities to find a tutor for additional classes in the “Tutor” section. However, there are no registered, qualified tutors in our region.
The educational portal “Solve the Unified State Exam” is constantly developing, updating, and adding new tasks. Any educational institution, teacher, student can use the program for free without any financial contributions. The only prerequisite is registration on the site. The registration procedure is extremely simple; a prerequisite is to have a valid email account.
It is difficult to immediately list all the capabilities of the educational portal “Solve the Unified State Exam” and point out all the little things that make working with the program easy and convenient. But you just have to try it out, create and offer several tests to students, and it will take its rightful place on the list of your favorite programs.
Reflection of students on the results of practical work with a master teacher
Dear Colleagues!
Thank you for participating in the master class.
It is interesting to know your opinion about the lesson.
Please continue with your suggestions (not necessarily all):
I was surprised that...
I found it interesting...
I did not like …..
It was difficult for me...
It seemed strange to me...
I didn't understand) ….
I could not)…
I wish …
Literature
Vladimirova L.I. Master class in physics
Kashlev. S.S. Interactive learning technology // “Modern teacher” series “Pedagogical workshop” Minsk Krasiko – Print, 2009
Kudasheva G.A. Master class "Application of interactive learning technology in a physics lesson"
Kulnevich S.V., Lakotsenina T.P. Modern lesson. Part 1. Scientific and practical manual. - Publishing house "Teacher", Rostov-on-Don, 2004.
Lomakin A.V. Master class “Searching for the ingredients of success”
Master class as a modern form of certification in the context of the implementation of the Federal State Educational Standard. Technology algorithm, models and examples of implementation, quality criteria / comp. N.V.Shirshina. – Volgograd: Teacher, 2014
Mednikova L.A. "Reflexive activistpersonality of a junior schoolchild" // "Science and school practice" No. 1, 2008
Modernization of the educational process in primary, secondary and high schools: solutions. Recommendations for experimental school work / edited by A.G. Kasprzak et al. – National Foundation for Personnel Training. Institute of New Educational Technologies. – M.: Education, 2004
Popularity of all kinds tests and surveys is now very large. They find their main application, of course, in education: conducting exams and tests, passing all kinds of tests and the learning process itself in the form of quizzes or situational tasks. But their use is not limited to this. Public opinion polls, marketing research and simply entertaining puzzles in your spare time - all this can be organized using such software.
On the Internet you can find many website builders for creating tests. Some of them allow embed tests on pages of another site Using special code, some generate a unique URL for each test that can be added as a link. But the main thing is that they all have a wide selection of various types of questions and tasks, save the results and provide detailed statistics on the results, and some even automatically issue users with certificates of successful completion (virtual, of course).
So that you don’t waste time searching, we have collected in this review the ten best, in our opinion, systems of the class under consideration. Each of them has its own highlights, among them there are paid and free solutions, so you will surely choose the most suitable option for yourself.
Revision Quiz Maker
Revision Quiz Maker - online tool for creating tests and quizzes. The developer has at his disposal various options for questions (from the simplest Yes/No to multiple choice questions, with independent answer entry, etc.), access via Facebook, e-mail or via a link, as well as obtaining various statistics.
Quizinator
Powerful tool for creating interactive tests, quizzes, exams. An ideal choice for the educational field as it supports various question types, the use of graphics and videos, various specific information such as geometric sketches, electrical diagrams, maps, chemical elements, formulas and much more. You can ask a random selection of questions from the data bank, sort them randomly, limit the time for taking the test, and receive various types of reports.
All The Tests
All The Tests is a whole portal for creating and posting tests, quizzes, online exams. The capabilities of the system are so great that they do not impose any restrictions either on the topic or on the composition and materials of the questions - in addition to the simplest “right/wrong” questions, these can be questions with several correct options, but a different number of points for each answer, answers indicating a certain category, etc.
Testmoz
Testmoz works great for organizing online testing of students. Supports four types of questions - yes/no, single and multiple choice from a list and manual entry. To create and take a test no registration required for either the test creator or test taker, however, if you wish, you can set the test to limit the audience. Each test has a unique URL that is easy to share. Using the basic functionality of the system is completely free and will satisfy most users, but if you need even more features, then for just $20 per year you will get email notifications, importing questions from other tests, authorization of test takers by ID and other features.
Play Buzz
Powerful system for creating tests, quizzes and surveys. Provides extensive use of animation, thanks to which each test or survey looks lively and attractive. The content of the question also allows the use of photo and video materials, which avoids monotony and monotony. Play Buzz generates a unique link to access the test, as well as a special code for embedding on a website or blog page. The interface has been translated into several languages, including Russian.
Class Marker
Modern system for creating online tests, conducting remote testing, determining professional skills, etc. Class Marker is equipped with all the necessary functions: authorization of the test taker, test time limit, indicating the availability of the test, a database of users and their certificates. Allows you to embed a test on a page of your website or blog.The free plan allows up to 100 tests per month. For more, you will have to purchase a regular subscription with monthly payment or a package of services for occasional use.
Flexi Quiz
Flexi Quiz - modern multifunctional system for testing and testing knowledge and skills. As befits any such system, it allows you to create public and closed tests, set the duration of the exam, use various types of questions and receive detailed reports and analytical materials.It has two tariff plans: free and free, and the free one has almost all the benefits of premium, having only a limit on the number of questions - 15.
GoConqr
Multifunctional knowledge testing and verification system, focused on various disciplines, including geography, algebra and physics.In addition to classic tests, it allows you to create flashcards, educational slideshows and mind maps for more effective preparation for taking an exam or test.
But we have never discussed services for creating tests online. But if you use your imagination, they can also be used in many projects.
First, attract potential clients, creating thematic tests that would immediately give the result to the test taker and encourage action.
Secondly, tests can be used when hiring new employees. You've probably heard about this.
Third, if you conduct trainings online and even offline, then try test your students. Below you will learn about a service suitable for these purposes. Knowledge monitoring helps you understand which topics were poorly understood and what needs to be improved. Do you agree?
And now about the services that help create online tests.
I'm glad there's plenty to choose from. And not only English-language sites can help here, because without knowledge of the language it can be difficult to understand them, and advanced functionality is only available in paid plans. We will also tell you about domestic services, which are no worse than foreign ones and are almost always free.
iSpring
The test bank is a free service where the function of creating new tests becomes available after registration. The materials are on the banktestov website.
Your test is another free test builder. In principle, there is everything you need to conduct full testing. The created tests are on the website, so you can create a group yourself and add all test takers to it (for example, course participants) or send them a code and a link where they will find the necessary material.
Test.fromgomel is a nice free service for creating tests online that transforms the test into html, that is, the code can then be easily inserted into the website. This is the simplest constructor we have come across. But without unnecessary hassles: enter the question and answer options, press the button and receive the test code.
Let’s test is an online test constructor that allows you to test students’ knowledge remotely. In the free version, you can create an unlimited number of tests, but you will have to pass them inside the service itself. If you want to embed a test on your website, then purchase the basic package for 990 rubles/month.
This is what the built-in widget on the site will look like
The find was the website Online Test Pad, which allows you to create tests, surveys, logic games and even crosswords online. And yes, it's free. From a large collection of tests, you can choose something on your topic or create a new test, and then receive a code for embedding on the site.
Gazimagomedov Malik
This work will be very interesting to those who want to diversify the forms of questioning in the classroom.
Download:
Preview:
Introduction.
There are different ways to involve students in the learning process. Interactive testing is one of them. Students prefer to test in dialogue mode with a computer and get results immediately, rather than wait for the teacher to check paper tests. This work allows teachers to create interactive tests that do not require special computer skills. MS Excel.
The work examines the non-standard use of spreadsheets and programs for creating computer tests.to control knowledge.
Thanks to the ease of creating (recording) macros, you can make tests interactive, even without knowing Visual Basic - the language in which they are written.
The work consists of three sections. In the first -basic information on MS Excel– only the most basic information on MS Excel is given that you need to know when creating tests. The second shows the ability to create an interactive test using standard Excel functions and macros - a set of commands used to automatically perform certain operations, which allows you to automate the transition to the next test question and return to the beginning of the test for further testing., and the third shows the research part in which this development was applied in a mathematics lesson.
Purpose of the study: consider methods interactively, study literature on this topic.
Research objectives:
- teach how to use basic techniques for working in a spreadsheet environment;
- use logical functions when creating various mathematical models;
- create simple macros such as “Cleaning”, “Result”, “Exit”, etc.
- Consider the effectiveness of using interactive tests in contrast to traditional methods of knowledge control.
Basic information about MS EXCEL.
The Excel document has the extension "*.xls" and is called a workbook. The workbook consists of sheets. By default, three of them are created. You can switch between sheets using bookmarks (shortcuts) at the bottom of the “Sheet 1” window, etc.
Each sheet is a table. The table consists of columns and rows. There are 256 columns in the sheet (2 to the 8th power),
and there are 65536 lines (2 to the 16th power). I suggest you calculate the number of cells yourself. Columns are designated by Latin letters
alphabet (in normal mode) from "A" to "Z", then goes "AA-AZ", "BB-BZ", etc. to "IV" (256). The lines are designated
ordinary Arabic numbers.
At the intersection of a column and a row is a cell. Each cell has its own unique (within a given sheet)
an address that is made up of a column letter (in normal mode) and a row number (for example, "A1").
Think about naval combat, chess, or how you find your seat in a movie theater. The cell address (cell reference) is used
in calculations (and not only) in order to “get” data from this cell and use it in the formula.
Only one of the sheet cells is active at the current time. A thick line is visible around the active cell.
a black frame with a square in the lower right corner (autofill marker). Even if a range of cells is selected,
anyway, one of the selected cells will be white. This does not mean that it is not highlighted, it means
that in the selected range it is active - it is in it that the text typed from the keyboard will be entered.
Microsoft Excel macros are simple, easily adaptable, yet powerful tools that allow the user to save time and be more productive.
Therefore, I would like to offer one of the test options using macros.
Creating an interactive test in MS EXCEL
Let's create a test - a test in mathematics, based on the materials of the demo version of KIM GIA 2013, the number of answers can be set arbitrarily for each question, but it is better not less than 4, otherwise the probability of “passing” the test with at least 3 becomes almost 100%.
Open Excel and rename the worksheet sheets:
Sheet1 – “Beginning”
Sheet2 – “Test”
Sheet3 – “Result”
On the “Start” sheet, select all the cells and give them some background color (CELL FORMAT - VIEW), or make a background for the sheet with a picture
Let’s color the “Test” and “Result” sheets in the same way.
Let’s draw a button on the “Start” sheet - START. More precisely, it will be a button, but just a drawing made, for example, using autoshapes from the Drawing panel. (In the future, this picture will become an active button, but after we assign a macro to it.
Let's go to the "Test" sheet and create questions with answer options there
Consider for one question (Question 1):
In cell C3 we write the question number
In cell D3 we write the question itself
In cell E3 you need to specify a list of answer options
To do this, go to E3 and go to the DATA – CHECK menu, where we select LIST and enter the answer options in the SOURCE field, separated by a colon.
Click OK and the list of answers is ready - when you click on cell E3, a button will appear that opens the list of answers and allows you to select one of them:
IF(= cell value;”true”;”false”) – this means that if the correct answer is selected in cell E3, then in cell F4 we will display the word “true”, otherwise we will display “incorrect”
Go to cell G3, where we write the formula: =IF(F3="true";1;0)
It is clear that this cell will have the value 1 if the answer is correct and 0 if it is incorrect. (You will need this on the next worksheet to count the number of correct answers.)
We will create all 10 questions in the same way. Finally, on this sheet we will draw 2 buttons: clear and result
Let's go to the "RESULT" sheet
Let's copy the columns with question numbers and the questions themselves from the TEST sheet (I have them in columns C and D, respectively.
In column E we write down the correct answers. In column F we will display those answers that were selected by the test taker, therefore, for example, in cell F3 there will be the formula F3 = test! E3 (i.e., the answers selected on the TEST sheet are displayed here - this is for comparison with the correct answer)
Let's fill in all the other cells in the same way.
Let's draw two buttons: AGAIN (to repeat the test) and EXIT (to exit the test)
In column I we will make two inscriptions to control the completion of the test: TOTAL and CORRECT, TOTAL, ASSESSMENT.
Nearby in column J we will write TOTAL: 10 (this is the number of test questions), and opposite TRUE - the formula =SUM(test!G3:G11) - that is, here the units of correct answers from the TEST sheet will be summed up, in cell K opposite TOTAL we will write formula =(K11*100)/10, which will calculate the percentage of quality of the work performed, and in cell L we will write a formula that will display the rating =IF(K9=100,5,IF(K9>70,4,IF(K9>40 ;3;2))).
All that remains is to create macros and assign them to buttons.
Let's start with the START button on the START sheet.
When you click on this button, a sheet with test questions (TEST) should open, the contents of column E with the correct answers should be cleared and cell E3 should be highlighted so that the button for selecting the first answer is visible.
Go to the menu VIEW – MACRO – START RECORDING. Give the macro a name and OK.
And the macro recording started, i.e. everything we do now will be written in VBA code
After this, open the TEST sheet, select cells E3 - E21 there, press Delete to clear the contents and click on cell E3 to select it.
Macro recorded. Stop recording. Let's assign this macro to the START button. To do this, right-click on it and select ASSIGN MACRO: In the window that appears, select our macro and OK - the macro is assigned. The button (and this is a button, not an autoshape) began to work. If the button does not work, then most likely the level
Excel security is high.
Then go to EXCEL Options – MACRO – SECURITY and
set the security level to low. Go to the TEST sheet and there you need to create 2 macros for the CLEAR and RESULT buttons. Create a macro CLEAR. We start recording the macro, select the column with the selected answer options and press Delete. Stop recording the macro, then assign it to the clear button.
Go to the RESULT sheet, you also need to create 2 macros to return to the beginning of the test (button AGAIN) and to exit the test (button EXIT)
For the AGAIN button in the macro it contains only the action of going to the sheet START.
For the EXIT button, the macro must go to the START sheet and then do the application shutdown.
Let's record only the transition to the START sheet, then open the macro and manually add the Application.Quit code - completing the work. Let's assign this macro to the EXIT button
As a result, the next time we run the test, it will open from the START sheet, which is what we want, and the answer choices from the previous test run will be cleared, regardless of whether we answered YES or NO to the question about saving changes when exiting.
We will remove the sheet labels so that you cannot immediately go and see the answers, we will remove the grid, the names of columns and rows, and scroll bars. We remove all this by going to the EXCEL SETTINGS - ADDITIONAL menu:
ALL. The test is ready. Launch and try (see disk)
Research part.
Interactive control methods in mathematics lessons.
To substantiate and confirm the theoretical principles presented in this work, an experimental study was organized, during which it was intended to prove the effectiveness of the proposed testing using interactive control methods for grade 9 using the example of an experimental class.
The experiment was carried out on the basis of 9 "B" and 9 "B" classes of the MKOU "Kizlyar Gymnasium No. 1 named after M.V. Lomonosov", and consisted of the following stages: ascertaining, formative and control-diagnostic.
The purpose of the experiment is to optimize the control of schoolchildren’s knowledge, to create the prerequisites for the realization of creative abilities through interactive methods.
Tasks:
1) analyze and assess the level of cognitive activity of students in the experimental and control groups according to certain criteria;
2) develop the ability to master MS EXCEL
3) develop intellectual abilities, emotional-volitional and moral qualities of the individual;
Two classes were given a test task
9 "B" - testing in the traditional form.
9 "B" - using an interactive test.
The test results can be expressed in the following table:
Conclusion : The use of interactive tests can significantly reduce test processing time.
CONCLUSION.
Interactive tests can be used at various stages of the lesson (introductory, current, final instruction), at various stages of control (input, current, milestone, final). They attract the attention of students with their variety, brightness, and the ability to independently create a mini program for a computer, which not only calculates the grade, but will also be used in lessons, acquiring practical significance for students.
No special software is required to create tests. The MS Office package (Excel in particular) is available on every personal computer. This explains the availability of the information offered.
Creating interactive tests does not require special knowledge and skills. The ease of making tests makes it possible for both experienced and novice users to try their hand.
Creating tests using Microsoft Excel (option 1) (rc.novokuybishevsk.ru)
Creating a test questionnaire with results processing using MS-Excel (sites.google.com)
Methodological material for a computer science lesson
on the topic “Creating interactive tests using an electronic processorEXCEL. »
Annotation.
This work is a test and training manual on the topic “Practical application of the spreadsheet electronic editor EXCEL”, namely the creation of interactive tests.
The work can be used in computer science lessons in grades 9-11 of a technical profile, as well as for beginner level users.
Introduction.
Microsoft Excel - program to work with spreadsheets, created by the corporation Microsoft For Microsoft Windows, Windows NT And MacOS. It provides economic statistical calculations, graphical tools and, with the exception of Excel 2008, Mac OS X, macro programming language VBA(Visual Basic for Applications). Microsoft Excel is included Microsoft Office and today Excel is one of the most popular applications in the world.
You can also create interactive tests in Microsoft Excel using standard functions or macros.
Creating interactive tests in MS Excel.
(instructions for learning the capabilities and use of MS Excel for creating interactive tests for high school students)
The development consists of three sections. The first - basic information on MS Excel - provides only the most basic information on MS Excel that you need to know when creating tests; experienced users can skip this section. The second shows the possibility of creating an interactive test using standard Excel functions, and the third using macros - a set of commands used to automatically perform certain operations, which allows you to automate the transition to the next test question and return to the beginning of the test.
Interactive tests can be used at various stages of the lesson (introductory, current, final instruction), at various stages of control (input, current, milestone, final). In my practice, students are happy to create tests themselves. They fill their course projects with them. They attract the attention of students with their variety, brightness, and the ability to independently create a mini program for a computer, which not only calculates the grade, but will also be used in lessons, acquiring practical significance for students.
No special software is required to create such tests. The MS Office package (Excel in particular) is available on every personal computer. This explains the availability of the information offered.
Creating interactive tests does not require special knowledge and skills. The ease of making tests makes it possible for both experienced and novice users to try their hand.
Basic information about MS Excel
To create a test, you need to know several features of the MS Excel program, which are referenced in this development.
The list of commands that control the work of Excel is located in the main menu (Fig. 1.1) Here you will find the command Insert, Data, Service.
Main menu items contain a drop-down list of commands, which can be opened by left-clicking on the menu item. This is how you find the commands Examination(menu item Data), Sheet(menu item Insert), Macro(menu item Service).
Each Excel cell has a unique address, consisting of a column name and a row (Fig. 1, 2).
The columns of the Excel table are designated by Latin letters (Fig. 1, 3), the rows by numbers (Fig. 1, 4). Please pay attention if you enter formulas from the keyboard.
We enter the formulas in the formula bar (Fig. 1.5), starting with the sign = (equality) .
To create a figure for the test, use the toolbar Drawing ( located at the bottom of the Excel window), or a menu item Insert-Drawing-AutoShapes
Creating a test using
standard MS Excel commands and functions
MS Excel contains a set of standard functions. Functions are predefined formulas that perform calculations on given quantities in a specified order. The user only needs to specify the name of the function (for example, SUM, IF) and the arguments of the function - the addresses of those cells that are subject to processing (addition, verification).
Let's create a test consisting of 5 questions.
For this:
1. In cells B1, B3, B5, B7, B9, type the text of the questions.
2. Type the answer options
* In column K we type the answer options to question No. 1 (cell B1)
* In column L we type the answer options to question No. 2 (cell B3)
* In column M - for question No. 3 (cell B5)
* In column N – to question No. 4 (cell B7)
* In column O - to question No. 5 (cell B9)
We design cells with answers
# In cell B2 we add data verification
# Place the cursor in cell B2
# Launch the menu item Data-Check
# On the Options tab (Fig. 1)
fill in data type List
And source data - indicate a column with answer options
# on tab Message To enter, write down a message
« Choose an answer!
# Click OK
We carry out similar actions with cells B4, B6, B8, B10.
Check Values Window
3. Indicate the correct answers
** In cell K5 we enter the formula =IF(B2=K1;1;0) - in this case in cell K1 - the correct answer
** We fill in cells L5, M5, N5, O5 in the same way.
4. In cell P5, enter the formula that calculates the total score =SUM(K5:O5)
5. In cell B11, enter the formula that determines the score for the completed test
=IF(P5=5,5,IF(P5=4,4,IF(P5=3,3,2)))
The test is ready!
PS : All that remains is to format it in a style convenient for you.
Answer options (columns K, L , M , N , O ) it is better to hide (Format-Column-Hide), or select a white font color.
Creating a Test Using Macros
Macro is a set of commands used to automatically perform certain operations. In our case, a macro is used to automate the transition to the next test question and return to the beginning of the test for further testing.
Let's create a test consisting of 5 questions using macros. The security level should be low (Service-Macro-Security-Low)
For this:
1. Create 7 sheets using the menu item Insert-Sheet.
2. Fill out the sheets with the following inscriptions:
* On the first sheet there is one figure “Start test”;
* On the last one there are two figures “Your rating” and “Return to top”
On the rest - one " content of the question» and figures with options
answers (how many there will be depends on you).
3. Now you need to create macros that make your test work.
3.1 Macro that runs the test
* Place the cursor on sheet 1 in cell A1
* Run the command Service-Macro-Start Recording
* In the window we set the name of the macro Start_test
Click on the “Start Test” shape, then on the second sheet label
Go to the menu
Choose Assign macro
In the window that opens, select Start_test
Click OK
To check, you can first place the cursor in any cell (let’s say A1) and click on the shape. As a result, you will find yourself on the second page of the book.
3.2 Macro that works with the correct answer option
Run the command Service-Macro-Start Recording
In the window, set a name for the macro Sheet_2_correct
(Please note: Spaces are not allowed in the macro name)
Click OK (the window closes and the macro is recorded)
Click on the figure " correct answer", then in cell A1, write the number 1 in it, then on the label of the third sheet
Go to the menu Service-Macro-Stop Recording
Right-click on the figure
Choose Assign macro
In the window that opens, select Sheet_2_correct
Click OK
3.3 Macro that works with the wrong answer option
Place the cursor on sheet 2 in cell A1
Run the command Service-Macro-Start Recording
In the window, set a name for the macro Sheet_2_wrong
(Please note! Spaces in the macro name
not allowed)
Click on the figure with the wrong answer with the right mouse button
Choose Assign macro
In the window that opens, select Sheet_2_wrong
Click OK
Click OK (the window closes and the macro is recorded)
Click on the figure " incorrect answer", then on the label of the third sheet
Go to the menu Service-Macro-Stop Recording
We repeat the last 4 points with the remaining figures with options for the wrong answer.
3.5 Macro that works with returning to the beginning of the test
Place the cursor on sheet 7 in cell A1
Run the command Service-Macro-Start Recording
In the window, set a name for the macro return_to_beginning_of_test
(note! Spaces in the macro name
not allowed)
then to sheet 6 in cell A1, on the Del key,
then to sheet 5 in cell A1, on the Del key,
then to sheet 4 in cell A1, on the Del key,
then to sheet 3 in cell A1, on the Del key,
then to sheet 2 in cell A1, on the Del key,
then on the label of the first sheet
Click OK (the window closes and the macro is recorded)
Click on the figure " return to the beginning of the test?»,
Go to the menu Service-Macro-Stop Recording
Click on the figure " return to the beginning of the test?» right
Mouse buttons
Choose Assign macro
In the window that opens, select return_to_beginning_of_test
Click OK
For this:
SUM(Sheet2!A1;Sheet3!A1;Sheet4!A1;Sheet5!A1;Sheet6!A1)
Column names are written in Latin letters!
Now, in the cell located next to the “Your rating” figure, insert the formula for calculating the rating
IF(A1=5,5,IF(A1=4,4, IF(A1=3,3,2)))
3.7 For ease of control and the inability of students to change the test, use the menu item command Service Parameters. On the tab View uncheck all the checkboxes on each sheet of the workbook individually.
The test is ready! All that remains is to format it to your liking!
Algorithm for creating an interactive test.
What are the advantages of this program?
Firstly, it is always “at hand”, as it is included in the office software package, both on my personal PC at home and at school on any PC loaded with Windows OS. Therefore, there is no need to waste your time downloading additional programs for creating tests, or time uninstalling such a program after working with the class. At the same time, no one violates copyrights, since school PCs, even if they are loaded with Windows OS, have a license for it.
Secondly, working in Excel is easy and simple; a test can be created in just a few minutes, for example, during a break.
Of course, any active PC user can find many reasons against it. However, again, in my opinion, Excel tests are easy to create and easy to use.
So, how do you create a test in which each subsequent question appears after the correct answer to the previous one?
Such tests can be successfully used when practicing students’ skills and abilities during a lesson - studying a new topic.
I will describe an algorithm for creating a test in which each subsequent question appears only after the previous one has been answered correctly:
EXCEL MICROSOFT OFFICE ALL PROGRAMS
1. Starting the program: START
2. Introduction to the main buttons of the program for creating tests (Fig. 1):
3
.Formulation of the test - work in cells (Fig. 2):
Format a cellTo format a cell, use the right mouse button (see Figure 3)
This is where your imagination can run wild! Vary as you wish: play with color - filling the cells of questions and answers, play with borders! Don't forget about the finished form of your dough. For example, I paint a table field that is not occupied by a test with a white background. This way, the Excel table grid doesn't distract students when working on the test.
4. In the first line of questions (cell B4) we enter the text of the first question.
5. In the second line (cell B5) enter a formula with which question No. 2 will appear, provided that the student answers question No. 1 correctly.
6. Introduction of the formula:
a) click the fk icon
b) a pop-up window will appear (Figure 4)
e) click OK
f 
) a pop-up window will appear (Figure 5)
LOG EXPRESSION: enter G4=2, where G4 is the cell of the student’s answer, 2 is the number of the correct answer.
Value if true: enter next question (i.e. enter next question right here by opening it and closing "").
Value if false: enter "" (i.e. empty quotes).
Those. if the student chooses the answer “4”, then the next question will appear for him, and if he chooses other options, then there will be no further questions.
Fill out all the lines of questions in the same way (Figure 6).
Figure 6
The test is ready!
How to process the test or how to grade it?
For the test, select 5 questions, the number of correct answers will correspond to the score. You can ask a number of questions that are multiples of 5.
List of sources.
1.http://dl.dropbox.com/u/74798925/3820998_tn.jpg
2.http://dl.dropbox.com/u/74798925/cm-31.jpg
3.http://dl.dropbox.com/u/74798925/Excel.jpg
4.http://dl.dropbox.com/u/74798925/i%20(1).jpg
5.http://dl.dropbox.com/u/74798925/i%20(2).jpg
6.http://dl.dropbox.com/u/74798925/i%20(3).jpg
7.http://dl.dropbox.com/u/74798925/i.jpg
8.http://dl.dropbox.com/u/74798925/%D0%91%D0%B5%D0%B7%20%D0%B8%D0%BC%D0%B5%D0%BD%D0%B8 -1.jpg
9. Miklyaev A. “User’s Handbook”, M., 1998.
Kursk pre-revolutionary: m
Economic importance of rivers
Paid linking of general survey plans (GMP)