Уроки компьютера [ Excel ] [ Word ] [ Subscribe ] [ Photoshop ]

Уроки Excel для чайников:
Уроки Excel для среднего уровня:
Уроки Excel для продвинутых:

Как в Excel сделать тест с автоматическим подсчетом результатов / создание опросника, проверка заполнения ячеек, подсчет результатов, оформление результатов

В споре обе стороны приводят неотразимые аргументы, например, такие:

• Можно, если: это оправдано и необходимо; если есть четкое понимание целей использования; если данные интерпретирует специалист высокого уровня.
• Нельзя, потому что это бесполезная трата времени . как со стороны соискателя, так и со стороны работодателя; есть опасность подменить интервью тестом и т. д.

Итак, эта статья для тех HR-менеджеров, которые в своей работе используют (или хотят научиться использовать) специальные методики. Мой опыт работы по автоматизации обработки данных пригодится тем специалистам компании, которые:

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

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

По своему опыту знаю, что начинающие HR-менеджеры неизменно задают вопрос: А зачем автоматизировать работу с тестами? Приведу типичные ответы опытных эйчаров:

1. Хочу сократить затраты на тестирование персонала, а именно:

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

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

2. Хочу иметь возможность управлять процессом тестирования:

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

На самом деле, подобная работа не так уж сложна, ее вполне по силам освоить любому опытному пользователю компьютера . не обязательно быть программистом. Часто достаточно оказать HR-менеджеру небольшую методическую помощь, показать . что да как, и он сможет самостоятельно использовать и даже разрабатывать собственные автоматизированные решения.

Рассмотрим автоматизацию обработки данных на примере работы с тестом Опросник межличностных отношений . Лучше, если перед рассмотрением алгоритма вы скачаете оба файла.

Алгоритм автоматизации теста: разработка компьютерного варианта

Шаг 1. Подготовка опросника.

1. Открываем новый файл в программе Excel. Параллельно открываем исходный Word-файл, в котором содержатся описание методики, сам опросник, ключи к нему.

2. Переносим форму опросника из Word-файла в Excel (рис. 1). Для этого выделяем, затем копируем текст и переносим его в Excel-файл. Чтобы текст сохранил параметры абзаца, вставку нужно делать в строку формул (она находится в нижней части верхнего меню и обозначена символом f_x . рис. 1).

Нажмите на изображение для увеличения

Рис. 1

3. Сразу же сохраняем файл в предварительно созданную папку (назвав его, например, mejlichnostn.xls).

4. Редактируем инструкцию под компьютерный вариант . испытуемый должен будет не выбирать вариант ответа, а проставлять  1 в выбранной ячейке ответа.

5. Изменяем формат ячейки для удобства чтения. (Выделяем несколько ячеек . объединяем их, затем с помощью правой клавиши мыши вызываем меню Форматирование .)

6. Выравниваем ширину и высоту строк.

7. Далее переносим поля опросника . вопросы, варианты ответов (рис. 2).

8. Можно в самом начале добавить поле Фамилия. Имя. Отчество (рис. 2). Иногда полезно добавить некоторые социально-демографические характеристики (например . пол, возраст, занимаемая должность, стаж работы в компании и т. д.).

9. Ячейки, в которые опрашиваемый будет заносить ответы, можно выделить другим цветом (рис. 2).

10. Подбираем шрифты и размеры знаков (например, можно использовать шрифты Arial и Arial Narrov . рис. 2).

11. Переименовываем лист . можно назвать его Опросник (рис. 2).

Нажмите на изображение для увеличения

Рис. 2

Шаг 2. Используем опцию Автоматическая проверка правильности заполнения ячеек .

1. При заполнении опросника опрашиваемый может допустить ошибки:

• пропустить один из вопросов (то есть не отметить ни одного ответа);
• отметить больше ответов, чем можно (в данном случае . два и более).

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

2. В качестве ответа мы договорились проставлять в ячейке цифру 1. Значит, для проверки правильности заполнения полей нам нужно проанализировать в ячейках сумму ответов по каждому вопросу.

3. Рядом с вариантами ответов вводим формулы для проверки установленных нами правил (рис. 3), в данном случае это будет формула:

=ЕСЛИ(СУММ(C5:H5)=0;"ответ не выбран";ЕСЛИ(СУММ(C5:H5)=1;"";"лишние ответы"))

Теперь мы контролируем правильность заполнения ячеек с ответами.

4. Далее тиражируем эту формулу для всех вариантов ответов: копируем ее и вставляем напротив всех остальных ответов (рис. 3).

Таким образом, даже если опрашиваемый совершит ошибку, HR-менеджеру не придется вручную просматривать все ячейки с ответами. Можно обращать внимание только на сигналы ответ не выбран или лишние ответы , чтобы предложить опрашиваемому исправить выявленные ошибки.

Нажмите на изображение для увеличения

Рис. 3

Шаг 3. Задаем формулы подсчета результата.

1. Обратимся к исходному Word-файлу с описанием теста и ключей к нему. Наша задача . повторить таблицу ключей в Excel и автоматизировать подсчеты. Подсчет результатов можно организовать либо на этом же листе, либо на другом (рис. 4).

2. Создаем таблицу ключей . переносим ее из Word-файла в Excel (рис. 4). Полезно ключи сразу же выделить другим цветом.

Нажмите на изображение для увеличения

Рис. 4

3. В соответствии с ключами прописываем формулы подсчета ответов (рис. 5). В данном случае при совпадении ответа с одним из вариантов ответа ключа он оценивается в 1 балл, при несовпадении . в 0 баллов (рис. 5). Например, формула подсчета ответов по первому вопросу будет выглядеть следующим образом (рис. 5):

=ЕСЛИ((СУММ(O7:R7))>=1;1;0)

Нажмите на изображение для увеличения

Рис. 5

4. Такие же формулы нужно прописать для каждого из ключей. В итоге мы получим сводную таблицу подсчета результатов (рис. 6).

5. Для того чтобы опрашиваемый не видел этих подсчетов, в дальнейшем таблицу ключей необходимо скрыть.

Нажмите на изображение для увеличения

Рис. 6

Шаг 4. Избирательная защита ячеек от изменений.

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

2. Перед защитой всего листа нужно отметить ячейки/диапазоны, которые в дальнейшем можно будет изменять. В нашем случае это ячейки с вариантами ответов . они отмечены другим цветом (рис. 7).

Нажмите на изображение для увеличения

Рис. 7

3. Устанавливаем защиту на весь лист. В меню Сервис выбираем команды: Защита . Защитить лист . После этого изменять содержимое можно только в тех ячейках, где мы ранее разрешили (рис. 8).

4. Проверим защиту: попробуем изменить ячейки с ответами и другие ячейки. Чтобы снять защиту, в меню Сервис необходимо выбрать команды: Защита . Снять защиту . Теперь опять попробуем изменить ячейки с ответами и другие ячейки.

5. Кстати, при необходимости можно ввести пароль на снятие защиты, тогда при попытке изменения защищенных ячеек (например, ячеек с вопросами, формулами или ключами) компьютер будет выдавать сообщение о запрете на изменение содержимого этих ячеек (рис. 8).

Нажмите на изображение для увеличения

Рис. 8

Шаг 5. Оформление результатов.

1. Вновь обратимся к исходному Word-файлу, в котором дана форма вывода результата. В данном случае для наглядности можно использовать таблицу-диаграмму (рис. 9). Сами результаты будем подсчитывать на другом листе . назовем его, например Результат (рис. 9).

2. Вводим формулу вывода результатов с листа Опросник на лист Результат . В данном случае это будет формула:

=опросник!B4

Теперь на листе Результат в поле Тестируемый будет выводиться имя опрашиваемого, которое введено на листе Опросник в ячейке B4 (рис. 9).

Нажмите на изображение для увеличения

Рис. 9

3. Точно так же вводим формулы, устанавливающие связь ячеек с баллами на листе Опросник с аналогичными ячейками на листе Результат (рис. 10). В итоге все баллы по всем шкалам будут выводиться на следующий лист автоматически.

4. Для наглядности введем формулы отображения результатов в строках шкал. Наберем формулу (рис. 10):

=ЕСЛИ($D$4>=E3;"X";"")

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

5. Усилить наглядность можно с помощью формул условного форматирования. Ячейки, в которых суммируются результаты по шкалам, можно анализировать.

Выделим другим цветом те ячейки, которые отмечены символом  Х . Например, если балл равен 5, то выделим другим цветом ячейки с баллом от 1 до 5. Для этого используем команды: Формат . Условное форматирование . Вводим формулу условия: если значение ячейки равно X выделяем эту ячейку другим цветом (например, светло-желтым . рис. 10).

6. Тиражируем условное форматирование на все ячейки результатов. Для этого используем команду: Формат по образцу (рис. 10). В итоге получим таблицу-диаграмму, в которой наглядно представлены все результаты тестирования (в данном случае . профиль поведенческих проявлений в трех областях межличностных отношений).

Нажмите на изображение для увеличения

Рис. 10

7. Добавляем в отдельные поля описания каждой из шкал. Для этого обратимся к исходному Word-файлу и перенесем все описания в файл Excel на лист Результат (рис. 11).

Точно так же можно добавить подсказку-примечание в название каждой шкалы (рис. 11). Теперь при наведении курсора на ячейку, содержащую название шкалы, будет всплывать примечание с кратким ее описанием (рис. 11).

Нажмите на изображение для увеличения

Рис. 11

12. В данной методике можно подсчитать не только данные по всем шкалам, но и индексы . комбинации оценок (рис. 12):

• индексы объема интеракции (е + w) . внутри отдельных областей межличностных потребностей;
• индексы противоречивости межличностного поведения (е . w) . между отдельными областями межличностных потребностей.

Для этого в ячейки, отображающие индексы по каждой области, введем формулы для подсчета суммы и разницы баллов. Например, в ячейку, в которой отображается индекс объема интеракции (е + w) для области Включенность , нужно вставить формулу:

=D4+D5

Соответственно, в ячейку, в которой отображается индекс противоречивости межличностного поведения (е . w) для области Включенность , нужно вставить формулу:

=D4-D5

Аналогично вводим формулы для остальных индексов (рис. 12).

Нажмите на изображение для увеличения

Рис. 12

13. Кроме табличной формы отображения данных, для наглядности можно использовать диаграммы, поскольку программа Excel известна именно развитым инструментарием для их построения. Рассмотрим возможности диаграмм на примере работы с результатами тестирования по нашему опроснику.

Выделяем область ячеек, содержащих данные для построения диаграммы, . в данном случае это оценки поведенческих проявлений (рис. 13). Затем используем опцию Мастер диаграмм . Из предложенных типов диаграмм для этого опросника выберем лепестковую (рис. 13). Далее, следуя инструкциям, заполняем необходимые параметры в диалоговом окне Мастера диаграмм (рис. 13). В результате получаем наглядную информативную диаграмму, в которой сразу виден профиль поведенческих проявлений.

Нажмите на изображение для увеличения

Рис. 13

14. Теперь, когда все формулы вычислений и диаграммы готовы, постараемся скрыть от опрашиваемого некоторые технические подробности. Например, таблицу ключей на листе Опросник .

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

Нажмите на изображение для увеличения

Рис. 14

Итак, Excel начинает работать на эйчара! Используя предложенный автором алгоритм в качестве образца, HR-менеджеры смогут самостоятельно автоматизировать множество очень полезных в работе инструментов. Это позволит им сэкономить свое время и силы, чтобы использовать их для решения более творческих задач.

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

P.S.: Автор благодарит редакцию журнала Менеджер по персоналу за методическую помощь при работе над статьей.

Редакциея журнала Менеджер по персоналу
Автор: Типатов Николай