II. Алгоритм автоматизации обработки методик в ms excel
Материалы и инструменты: персональный компьютер, программа MS Excel, тестовая методика и ключ к её обработке.
Рассмотрим алгоритм на примере создания автоматизированного обсчёта теста мотивации достижения Мехрабиана (Б) и опросника MBTI Майерс-Бриггс.
1. Для начала необходимо создать таблицу, в которую мы будем вводить данные. Она зависит от типа опросника, количества вопросов, вариантов ответа. Так же таблицу можно просто перекопировать со стандартных бланков ответов.
В нашем случае примеры бланков ответов представлены на рисунках 1 и 2.
Рисунок 1 – Бланк ответов для теста мотивации достижения Мехрабиана (Б)
Рисунок 2 – Бланк ответов для опросника MBTI Майерс-Бриггс
Таблицы-бланки можно строить как удобно.
Ячейки, подготовленные для ответов, следует отметить как числовые в «Формате ячеек» (щелкаем по выделенным ячейкам правой кнопкой мыши Формат ячеек Число). Их заполнение производится в соответствии с ответами испытуемого в его бланке. Но вместо «галочек» и «крестиков» мы ставим цифру «1» в отмеченные поля.
Рядом с бланком ответов разметить графу результатов. Это может быть небольшая табличка со шкалами или несколько подписанных ячеек. Причем цифровые ячейки, то есть те, в котором вы хотите видеть результаты, не должны содержать текста (см. рис. 3).
Мехрабиан MBTI
Рисунок 3 – Примеры оформления ячеек с итоговыми результатами
2. Следующим этапом идёт прописывание ключа. То есть прописывание подсчёта результатов по каждой шкале. В большинстве случаев это просто сумма всех баллов по указанным вопросам.
Для этого мы ставим курсор в необходимую нам ячейку и выделяем её. Дальше идёт стандартное прописывание функции «Сумма» в строке формул: мы ставим знак «=», выделяем нужную ячейку, соответствующую номеру вопроса и букве, выбранной испытуемым, ставим знак «+», выделяем следующую ячейку и т.д., всё по ключу прописываем, и так по каждой шкале.
Например, для шкалы Е MBTI в соответствии с ключом формула будет выглядеть следующим образом:
=В5+В9+В12+С12+С21+F2+F9+F13+F17+G22+J9+K13+J17+J21+N5+09+013+N21+R5
Прописываем ее в ту ячейку, где планируем отразить итоговый результат по шкале (рис. 4). Так поступаем со всеми шкалами.
Рисунок 4 – Прописывание формулы «ключа» для шкалы Е MBTI
Допустим в нашем случае по шкале Е баллы начислялись за ответы 1а, 5а, 9а, 13б и т.д. (варианты прописаны в ключе к методике). Возможно несколько способов начисления баллов. Например, для MBTI за разные вопросы ставится неодинаковое количество баллов, значит по ходу написания формулы мы прописываем это:
Если за вопрос начисляется 1 балл, то мы просто складываем выделяемые ячейки, например: =А1+А2+А3;
Если начисляется 2 и более балла, то мы после выделения ячейки ставим знак умножения «*» и ставим цифру равную нужному количеству баллов, например: =А1*2+А2*2+А3*3+А4*7.
При подсчёте получается итоговая сумма баллов, потому что мы в саму таблицу ставим единички (1) вместо галочек на места ответов. Единицы, поставленное не в то место, просто не учитываются.
В случае с тестом мотивации достижения Мехрабиана, где за определённый выбор от +3 до -3 начисляется от 1 до 7 баллов в соответствии с ключом необходимо поступить следующим образом (рис. 5).
Рисунок 5 – Внешний вид таблицы расчета «сырых» баллов теста Мехрабиана
На соседних строках, которые потом скрывают, прописывают подсчёт «сырых» баллов. В каждой строчке, после бланкового поля, было прописано, сколько баллов каждая из ячеек таблицы присваивает.
Говоря языком примеров, это выглядит так:
В первой строчке №1, соответствующей 1 вопросу, есть 7 ячеек. Сверху надписаны ответы согласия или несогласия: +3 - согласен с утверждением, -3 - не согласен с утверждением. Отметка «1» будет поставлена под соответствующим выбором. Соответственно, получаем, что каждая ячейка несёт определённый выбор: В4=+3, С4=+2, D4=+l, Е4=0, F4=-l, G4=-2, Н4=-3.
После таблицы-бланка в ячейках J4, К4, L4, М4, N4, 04, Р4 прописываем перевод в семибалльную шкалу. В ячейке J4 пишем: =В4*7 , в К4 =С4*6 , L4 =D4*5 , М4 =Е4*4 , N4 =F4*3 , 04 =G4*2 , Р4 =Н4*1 .
Таким образом, при проставлении цифры 1 в первую строку в таблицу-бланк, в нашей скрытой таблице уже сразу отображается количество баллов, присвоенное этому ответу по семибалльной шкале.
Так прописывается в каждой строке, соответственно ключу, где есть прямые шкалы от 1 до 7 и обратные, как в случае, который мы рассмотрели.
Прописывать вручную все строки необязательно. Достаточно сделать это только в двух: один раз для прямой шкалы, один раз для обратной. Потом нужно просто выделить строку нашей скрытой таблицы с этим прописанным алгоритмом, скопировать и вставить на место строки с аналогичной шкалой.
Общий индекс по методике считается просто: в подготовленную ячейку ставим равно «=», выбираем среди функций сумму «СУММ», в строке формул указываем диапазон ячеек нашей скрытой таблицы, в данном случае формула выглядит так:
=CYMM(J4:P33). После всего этого выделяем столбцы с расчётами и скрываем их, нажав по выделенному правой кнопкой мыши и выбрав Скрыть. Видимыми остается только таблица-бланк и после нее строка результата.
3. Таким образом, мы получаем бланк ответов, который считает результаты автоматически, после ввода в него данных. Данные вводятся только цифрой 1.
Можно сделать еще так, что бы при подсчёте группы людей, их результаты выдавались в конце в виде сводной таблицы.
В этом случае нужно создать в Excel в нашем файле множество листов по числу испытуемых в группе (назвать их соответственно «Испытуемый 1», «Испытуемый 2» и т.д., последний лист называем «Сводная таблица»). На каждый лист копируем созданный нами, автоматически работающий бланк ответов. На последнем листе «Сводная таблица» создаем таблицу со столбцами «номер испытуемого», «результат». В ячейке «результат» первого испытуемого ставим =, возвращаемся на 1 лист с его результатами, щелкаем на ячейку, где в цифрах указаны (или будут указаны) результаты по методике. И так делаем по каждому испытуемому (рис. 6).
Рисунок 6 – Сводная таблица результатов
В результате мы получаем программу, способную выдать сразу результат после заполнения, а так же итоговую таблицу в конце при обсчёте группы.