Как сделать тест в экселе

Обновлено: 06.07.2024

Есть определенная функциональная область приложения: некая экспертная система, анализирующая состояние данных, и выдающая результат — множество рекомендаций на базе набора правил. Компоненты системы покрыты определенным набором юнит-тестов, но основная «магия» заключается в выполнении правил. Набор правил определен заказчиком на стадии проекта, конфигурация выполнена.
Более того, поскольку после первоначальной приемки (это было долго и сложно — потому, что “вручную") в правила экспертной системы регулярно вносятся изменения по требованию заказчика. При этом, очевидно, неплохо — бы проводить регрессионное тестирование системы, чтобы убедиться, что остальные правила все еще работают корректно и никаких побочных эффектов последние изменения не внесли.

Основная сложность заключается даже не в подготовке сценариев — они есть, а в их выполнении. При выполнении сценариев “вручную", примерно 99% времени и усилий уходит на подготовку тестовых данных в приложении. Время исполнения правил экспертной системой и последующего анализа выдаваемого результата — незначительно по сравнению с подготовительной частью. Сложность выполнения тестов, как известно, серьезный негативный фактор, порождающий недоверие со стороны заказчика, и влияющий на развитие системы («Изменишь что-то, а потом тестировать еще прийдется… Ну его. »).

Очевидным техническим решением было бы превратить все сценарии в автоматизированные и запускать их регулярно в рамках тестирования релизов или по мере необходимости. Однако, будем ленивыми, и попробуем найти путь, при котором данные для тестовых сценариев готовятся достаточно просто (в идеале — заказчиком), а автоматические тесты — генерируются на их основе, тоже автоматически.

Под катом будет рассказано об одном подходе, реализующим данную идею — с использованием MS Excel, XML и XSLT преобразований.

Тест — это прежде всего данные

А где проще всего готовить данные, особенно неподготовленному пользователю? В таблицах. Значит, прежде всего — в MS Excel.

Я, лично, электронные таблицы очень не люблю. Но не как таковые (как правило — это эталон юзабилити), а за то, что они насаждают и культивируют в головах непрофессиональных пользователей концепцию «смешивания данных и представления» (и вот уже программисты должны выковыривать данные из бесконечных многоуровневых «простыней», где значение имеет все — и цвет ячейки и шрифт). Но в данном случае — мы о проблеме знаем, и постараемся ее устранить.

Итак, постановка задачи

обеспечить подготовку данных в MS Excel. Формат должен быть разумным с точки зрения удобства подготовки данных, простым для дальнейшей обработки, доступным для передачи бизнес пользователям (последнее — это факультативно, для начала — сделаем инструмент для себя);
принять подготовленные данные и преобразовать их в код теста.

Решение

Пара дополнительных вводных:

Конкретный формат представления данных в Excel пока не ясен и, видимо, будет немного меняться в поисках оптимального представления;
Код тестового скрипта может со временем меняться (отладка, исправление дефектов, оптимизация).

Известная технология превращения данных в произвольное текстовое представление — шаблонизаторы, и XSLT преобразования, в частности — гибко, просто, удобно, расширяемо. В качестве дополнительного бонуса, использование преобразований открывает путь как к генерации самих тестов (не важно на каком языке программирования), так и к генерации тестовой документации.

Итак, архитектура решения:

Преобразовать данные из Excel в XML определённого формата
Преобразовать XML с помощью XSLT в финальный код тестового скрипта на произвольном языке программирования

Этап 1. Ведение данных в Excel

Здесь, честно говоря, я ограничился ведением данных в виде табличных блоков. Фрагмент файла — на картинке.

Блок начинается со строки, содержащей название блока (ячейка “A5"). Оно будет использовано в качестве имени xml-элемента, так что содержание должно соответствовать требованиям. В той же строе может присутствовать необязательный “тип” (ячейка “B5") — он будет использовано в качестве значения атрибута, так что тоже имеет ограничения.
Каждая колонка таблицы содержит помимо “официального” названия, представляющего бизнес-термины (строка 8), еще два поля для “типа” (строка 6) и “технического названия” (строка 7). В процессе подготовки данных технические поля можно скрывать, но во время генерации кода использоваться будут именно они.
Колонок в таблице может быть сколько угодно. Скрипт завершает обработку колонок как только встретит колонку с пустым значением “тип” (колонка D).
Колонки со “типом”, начинающимся с нижнего подчеркивания — пропускаются.
Таблица обрабатывается до тех пор, пока не встретиться строка с пустым значением в первой колонке (ячейка “A11”)
Скрипт останавливается после 3 пустых строк.

Этап 2. Excel -> XML

Преобразование данных с листов Excel в XML — несложная задача. Преобразование производится с помощью кода на VBA. Тут могут быть варианты, но мне так показалось проще и быстрее всего.

Ниже приведу лишь несколько соображений — как сделать финальный инструмент удобнее в поддержке и использовании.

Фрагмент XML который получается из данных в Excel с картинки выше

Этап 3. XML -> Code

Эта часть предельно специфична задачам которые решаются, поэтому ограничусь общими замечаниями.

Финальный комментарий

Через какое-то время файлов с тестовыми данными станет много, а отладка и «полировка» шаблонов генерации тестовых скриптов будет все продолжаться. Поэтому, прийдется предусмотреть возможность «массовой» генерации автотестов из набора исходных Excel файлов.

Заключение

Используя описанный подход можно получить весьма гибкий инструмент для подготовки тестовых данных или полностью работоспособных автотестов.

В нашем проекте удалось довольно быстро создать набор тестовых сценариев для интеграционного тестирования сложной функциональной области — всего на данный момент около 60 файлов, генерируемых примерно в 180 тестовых классов tSQLt (фреймворк для тестирования логики на стороне MS SQL Server). В планах — использовать подход для расширения тестирования этой и других функциональных областей проекта.

Формат пользовательского ввода остается как и раньше, а генерация финальных автотестов можно менять по потребностям.

Преобразование XSLT не включено в репозиторий, поскольку оно генерит код для конкретной задачи — у вас все равно будет свой. Буду рад комментариям и pull request'ам.

Шкала оценивания:

Меньше 6 баллов – оценка «неудовлетворительно»

От 6 до 7 баллов – «удовлетворительно»

От 8 до 9 баллов – «хорошо»

От 10 до 11 баллов – «отлично»

Алгоритм создания теста со списком:

2. Оформляем заголовки столбцов для теста, набирая в соответствующих ячейках текст:

В ячейке А1 - «№»

В ячейке В1 – «Вопрос»

Получаем таблицу (рис. 1)

3. В диапазон ячеек А2:А22 набираем номера вопросов от 1 до 11, оставляя нижнюю строку, после каждой ячейки с номером, пустой, то есть в А22 набираем "1.", а следующий номер набираем в ячейке А4

4. В диапазон ячеек В2:В22 набираем тексты вопросов, соответственно их номерам в ячейках А2:А22

ставим курсор в ячейку В3
в строке команд выбираем "Данные"
в группе команд "Работа с данными" выбираем "Проверка данных" (рис. 3)

8. Записываем формулы для определения правильности ответа ученика и ,соответственно, проставления полученного балла. Для выполнения аналогичного действия в алгоритме для "Простого теста" использовался дополнительный лист - Лист2. В алгоритме для "Теста со списком" мы предлагаем ввести оценивающие формулы на листе с таблицей теста, то есть на текущем листе. Итак:

В поле Логическое значение пишем условие сравнения правильного варианта ответа с ответом который набрал ученик.

B3=F2, где В3 - адрес ячейки для ввода ответа на первый вопрос, а F2 - адрес ячейки с правильным вариантом ответа.

В поле "Значение если истина " записываем 1

в поле "Значение если ложь" записываем 0

Аналогично заполняем формулами с использованием функции «Если» все ячейки для проставления баллов. Получаем (рис. 10), таблица в режиме отображения формул). В обычном режиме, после ввода формулы в ячейке появится число 0.

9. В ячейке J23 находим сумму полученных баллов с помощью функции СУММ(J2:J22)

10. В ячейке J24 записываем формулу, в которой, согласно полученной сумме баллов, вычисляется оценка за тест. Удобно сначала сформулировать содержание формулы в виде предложения, а потом записать его используя возможности функций Excel .

Итак, по шкале оценивания для этого теста, получаем (рис. 11):

Сумма баллов не может быть < 0, но, для того, чтобы перед прохождением теста ячейка для оценки отображала "0" первым условием проверки будет равенство суммы баллов нулю.

если сумма баллов =0, то оценка "0", иначе
если суммабаллов<6 , то оценка «2», иначе
если сумма баллов>=6 и<=7 , то оценка «3», иначе
если сумма баллов>= 8 и<= 9 , то оценка «4», иначе оценка «5»

Полужирным начертанием в предложении выделены логические условия.

Записываем полученное с помощью функции ЕСЛИ. Так как формула получается достаточно сложной, одна функция ЕСЛИ вложена в другую, то далее подробно описывается последовательность набора формулы.

Ставим курсор в ячейку J24, вызываем окно Мастера функций, в Логических выбираем функцию ЕСЛИ
В полученном окне в поле Логическое значение вводим первое условие J23=0 , в поле «Значение если истина» набираем 0 (оценка), переходим в поле «Значение если ложь» и снова вызываем окно Мастера функций нажав f_x ( выбрать функцию).
Выбираем функцию ЕСЛИ и вновь получаем окно для ввода. В поле «Логическое значение» набираем второе условие J23<6, в поле «Значение если истина» набираем 2 (оценка), переходим в поле «Значение если ложь» и снова вызываем окно Мастера функций нажав f_x ( выбрать функцию).
Выбираем функцию ЕСЛИ и вновь получаем окно для ввода. В поле «Логическое значение» набираем третье условие И(J23 >=6;J2 3 <=7). Внимание! «И» выносится за скобки. В поле «Значение если истина» набираем «3», переводим курсор в поле «Значение если ложь».
Вновь вызываем окно функции ЕСЛИ и в поле «Логическое значение» набираем четвёртое условие проверки И(J23 >= 8 ;J2 3 <= 9 ). В поле «Значение если истина» ставим «4», в поле «Значение если ложь» набираем «5».
Нажимаем ОК.

В результате в ячейке J24 должна появиться оценка, соответствующая сумме баллов.

В ячейку I23 можно набрать текст «Сумма баллов», а в I24 – «Оценка».

11. Отображение результатов для ученика

в ячейке С24 набираем «=» и делаем щелчок на ячейке с формулой суммирования баллов, в нашем примере это J23. Нажимаем Enter .

в ячейке В25 набираем текст «Ваша оценка»

в ячейке С25 набираем «=» и делаем щелчок на ячейке с формулой определения оценки, в нашем примере это J24. Нажимаем Enter .

Теперь, при прохождении теста ученик будет видеть количество набранных баллов и свою оценку.

12. Переименовываем Лист1 в Тест. Подробное описание действия шаг 7 в алгоритме на предыдущей странице.

14. Оформление внешнего вида теста. Используем инструменты "Границы", "Заливка". Подробное описание шаг 6 алгоритма на предыдущей странице.

Внимание! Для того, чтобы ответы учащегося не сохранились, надо закрыть файл с тестом без его сохранения.

В качестве примера на Яндекс - диск загружен файл с тестом, алгоритм создания которого представлен выше.

Разработка содержит подробное поэтапное иллюстрированное описание создания теста в программе MS Excel с использованием стандартных команд и функций (на примере теста из пяти вопросов по теме: "Клавиатура").

Содержимое разработки

Создание теста в MS Excel

с использованием стандартных команд и функций

Создадим тест из пяти вопросов по теме: «Клавиатура», для этого нам нужен документ, состоящий из восьми листов.

Переименуем название листа на Начало.

С помощью автофир и картинок оформим лист.

На кнопку Начать назначить гиперссылку на Лист2.

Переименуем название листа на Вопрос1.

В ячейку В17 вводим текст вопроса.

В ячейку F20 вводим проверку данных.

Курсор установить в данную ячейку

Меню Данные/Проверка данных

Скрыть столбец J.

На фигуру Следующий вопрос устанавливаем гиперссылку на Лист 3.

3. Аналогичные действия проводим с Листами 2,4, 5, 6.

4. Лист7.

Переименуем название листа на Ответы.

В ячейки В5 введем текст Вопрос1,

В ячейку С5 введем формулу: = ЕСЛИ(Вопрос1!F20=Вопрос1!J17;1;0)

Аналогичные действия повторить для других вопросов (ячейки В6:С9)

В ячейку В11 ввести текст Сумма. В ячейку С11 ввести формулу: =СУММ(C5:C9)

Переименуем название листа на Результат.

В ячейку К25 введем формулу для определения оценки за тест: =ЕСЛИ(Ответы!C11=5;5;ЕСЛИ(Ответы!C11=4; 4;ЕСЛИ(Ответы!C11=3;3;2)))

-75%

Небольшая памятка для студентов и аналитиков всех отраслей: как использовать Microsoft Excel для вычисления критерия Стьюдента.

Microsoft Excel имеет мощные инструменты для анализа и визуализации статистики. В этой статье мы продемонстрируем их на примере классической статистической процедуры: теста Стьюдента или t-теста.

t-тест: что это такое, и зачем это нужно?

t-тест или тест Стьюдента был разработан английским математиком Уильямом Госсетом. В начале XX века он трудился на пивоваренном заводе «Гиннесс» в Ирландии, разрабатывая математические методы оценки качества сырья, из которого варят пиво.

По условиям контракта, Госсет не имел права публиковать свои разработки под собственным именем. Поэтому первая публикация методики теста появилась в журнале «Биометрика» под псевдонимом Student, что значит «студент». Так тест и остался в истории под названием теста Стьюдента.

Тест Стьюдента позволяет сравнивать случайные выборки данных — либо с некой нормой, либо между собой. Например, завод выпускает шурупы, и нужно оценить, соответствуют ли они в норме по длине. Или в больнице ведется клиническое исследование лекарства, и нужно оценить его эффект на пациентах до и после приема.

В обоих случаях должно выполняться требование нормальности распределения исследуемого признака в каждой из сравниваемых групп. Результатом выполнения теста является число, отражающее данный показатель — t-критерий, или критерий Стьюдента.

t-тест: как выполнить в MS Excel?

Вне зависимости от того, для чего вам может понадобиться вычислить критерий Стьюдента, в Microsoft Excel есть функция TTEST, которая позволяет это сделать. Она доступна в надстройке Пакет анализа. Рассмотрим, как использовать функцию на примере Microsoft Excel 2013 в Windows 7.

Как выполнить t-тест в Excel 2013

Синтаксис функции следующий: ТТЕСТ(массив1;массив2;хвосты;тип). Скопируйте эту формулу в любое поле вашего документа Excel.

Таким образом, данные автоматически введутся в окно аргументов. Осталось ввести без кавычек «3» с этим выражением и будет сравниваться ответ пользователя. Если он выберет «3» Функция «ЕСЛИ» определит совпадение этой цифры с вводом тестируемого и при удачном варианте или «истина», ответ будет считаться истинным, во всех иных вариантах будет «ложь» или не верно.

Что бы как то это увидеть в поле «Значение истина» ставим «1», аналогично в поле «Значение если ложь» ставим «0».
Если тестируемый ответит верно, в ячейке это отметится числом 1.
Если он мыслит не верно, то будет стоять «0». Заканчиваем произведенные действия функцией сохранить, то есть жмем «ОК».

Выделяем ячейку на указанном листе. Даем ей название «Результат». В нее вставляем ссылку на формулы с «Лист2» с адресом необходимой ячейки. В нашем примере это ячейка «авто сумма».

Таким образом, по данному варианту проведения теста в Excel сказано все. Существуют другие варианты в том же Excel проведения тестов, но это тема другой статьи.

Идёт приём заявок

Для учеников 1-11 классов и дошкольников

Выбранный для просмотра документ Создание теста по физике.doc

«Создание тестов по физике

в Microsoft Excel »

учитель физики и информатики

Открыть программу Microsoft Excel

В нужном месте открывшегося окна сделать заголовок «Тестовая программа по физике». Для этого можно заранее подготовить ячейку, путем объединения ячеек: выделить объединяемые ячейки, выполнить следующие команды: Формат – ячейки – выравнивание, проставить галочку напротив объединения ячеек и нажать ОК.

В объединенных ячейках записываете заголовок теста:

4. На листе «Тест» в ячейках таблицы сделаем заголовки столбцов, где будут размещены вопросы, ответы, проверка.

При создании теста со свободным ответом создается группа ячеек для ввода ответа.

При создании теста с выборочным ответом или теста на сопоставление выполняется следующая последовательность действий:

Оформим выбор: в ячейке В4 (напротив первого вопроса), выделим эту ячейку, нажмем меню Данные-Проверка

Из опций «Тип данных» нужно выбрать «список», после этого появится окно ввода «Источник». Нажать на кнопку , после чего выделить ячейки с ответами (в нашем случае это)

Теперь нажмем на кнопку и далее, на кнопку «ОК».

Мы увидим, что около ячейки В4 имеется кнопка для выбора. Нажав её, получим варианты.

Поместим в соседнюю ячейку С4 функцию проверки

где K4 –это адрес ячейки с правильным ответом на первый вопрос.

Смысл функции в следующем:

Если в тестируемой ячейке ответ не верный, то в ячейку, содержащую функцию, поместить пусто.

Иначе, если там не пусто, то, если ответ совпадает с верным, поместить «да».

Аналогично строим вопросы и ответы, копируем функцию и не забываем менять адрес ячейки с правильным ответом в функции.

Далее приводим страничку в надлежащий вид.

Для этого нужно:

с помощью меню Формат лишить защиты те ячейки, в которые вносится ответ , а также подсчет баллов:

выделить заголовки столбцов, в ячейках которых имеются ответы и нажать Формат-Скрыть. Можно также скрыть формулы.

Функция даст столько баллов, сколько раз слово «да» встретилось в ячейках с С4 по С8. Затем (Сервис) защитить лист.

Далее необходимо защитить лист.

Для этого нужно:

с помощью меню Формат лишить защиты те ячейки, в которые вносится ответ и реакция, а также подсчет баллов:

выделить заголовки столбцов, в ячейках которых имеются ответы и нажать Формат-Скрыть. Можно также скрыть формулы. Затем (Сервис) защитить лист.

Можно оценить тест. Для этой цели нужно выбрать ячейку, в которой создать формулу для получения оценки.

Например, для нашего примера в ячейку С11 поместить формулу

А теперь проверьте, как работает программа.

Если вы ответили на все вопросы теста правильно, то окно выполненных действий выглядит так:

Закрывая окно Excel , результат не сохраняйте.

Однако придать тесту подобие строгости можно.

Перейдем на лист «Результат».

В нужном месте открывшегося окна сделать заголовок «Результаты тестов». Для этого можно заранее подготовить ячейку, путем объединения ячеек: выделить объединяемые ячейки, выполнить следующие команды: Формат – ячейки – выравнивание, проставить галочку напротив объединение ячеек и нажать ОК.

В ячейку А3 поместить «Количество баллов»и в А4 «Оценка».

Оформим выбор: в ячейке В3 набираем «=ЛИСТ1С9», а в В4 набираем «=ЛИСТ1С11».

Далее приводим лист в надлежащий вид.

Для этого нужно:

выделить заголовки столбцов, в ячейках которых имеется проверка и нажать Формат-Скрыть. Можно также скрыть формулы.

выделить ячейки ЛИСТА1 «Твоя оценка» и «Количество баллов» и и нажать Формат-Скрыть.

Затем (Сервис) защитить ЛИСТ2.

А теперь проверьте, как работает программа.

Если вы ответили на все вопросы теста правильно, то окно выполненных действий выглядит так:

Закрывая окно Excel , результат не сохраняйте.

Федулов Андрей ЮрьевичНаписать 5706 03.01.2016

Номер материала: ДВ-303316

03.01.2016 10292

03.01.2016 472

03.01.2016 375

03.01.2016 2249

03.01.2016 362

03.01.2016 545

Не нашли то что искали?

Вам будут интересны эти курсы:

Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение редакции может не совпадать с точкой зрения авторов.

Ответственность за разрешение любых спорных моментов, касающихся самих материалов и их содержания, берут на себя пользователи, разместившие материал на сайте. Однако редакция сайта готова оказать всяческую поддержку в решении любых вопросов связанных с работой и содержанием сайта. Если Вы заметили, что на данном сайте незаконно используются материалы, сообщите об этом администрации сайта через форму обратной связи.

КАК СОЗДАТЬ ТЕСТЫ

1. Открыть программу Microsoft Excel

2. В нужном месте открывшегося окна сделать заголовок, «Тестовая программа по математике». Для этого можно заранее подготовить ячейку, путем объединения ячеек: выделить объединяемые ячейки, и выполнить следующие команды: Формат – ячейки – выравнивание, проставить галочку напротив объединение ячеек и нажать ОК.

В объединенных ячейках и записываете заголовок теста:

3. В ячейках таблицы сделаем заголовки столбцов, где будут размещены вопросы, ответы, проверка.

· При создании теста со свободным ответом создается группа ячеек для ввода ответа.

· При создании теста с выборочным ответом или теста на сопоставление выполняется следующая последовательность действий:

5. Оформим выбор: в ячейке В4 (напротив первого вопроса), выделим эту ячейку, нажмем меню Данные-Проверка

Теперь нажмем на кнопку и далее, на кнопку «ОК».

Мы увидим, что около ячейки В4 имеется кнопка для выбора. Нажав её, получим варианты.

6. Поместим в соседнюю ячейку С4 функцию проверки

где K4 –это адрес ячейки с правильным ответом на первый вопрос.

Смысл функции в следующем:

· Если в тестируемой ячейке пусто, то в ячейку, содержащую функцию, тоже поместить пусто (нет ответа – нет реакции).

· Иначе, если там не пусто, то, если ответ совпадает с верным, то поместить «да», иначе «нет».

7. Аналогично строим вопросы и ответы, копируем функцию и не забываем менять адрес ячейки с правильным ответом в функции.

Функция даст столько баллов, сколько раз слово «да» встретилось в ячейках с С4 по С8.

Роль такого теста закрепляюще-тренирующая, нежели контролирующая. Однако придать тесту подобие строгости можно. Для этого нужно:

с помощью меню Формат лишить защиты те ячейки, в которые вносится ответ и реакция, а также подсчет баллов: выделить заголовки столбцов, в ячейках которых имеются ответы и нажать Формат-Скрыть. Можно также скрыть формулы. Затем (Сервис) защитить лист.

Можно оценить тест. Для этой цели нужно выбрать ячейку, в которой создать формулу для получения оценки.

Например, для нашего примера в ячейку С11 поместить формулу

А теперь проверьте, как работает программа.

Если вы ответили на все вопросы теста правильно, то окно выполненных действий выглядит так:

Читайте также: