На OSZone завершилась праздничная викторина, и хочу рассказать вам, как ее результаты обрабатывались с помощью формул и других средств Microsoft Excel 2010. Напомню, что мы предложили участникам отправлять правильные варианты ответов в столбик, по одному на строку. Это было специально сделано, чтобы упросить анализ данных в Excel.

5
2
6
6
2
2
5
1
2
4
4
5
2
6
2

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

Как сравнить присланные ответы с ключом

Итак, первое письмо получено, и варианты ответов нужно сравнить с ключом, чтобы определить, сколько баллов набрал участник. Вы можете скачать мой шаблон, чтобы увидеть, как это все работает на практике.

Здесь вы видите фрагмент двух столбцов  данных – ключ и ответ участника. Задача заключается в том, чтобы сравнить значения, находящиеся в одном ряду обоих столбцов. Если значения совпали, участник получает 1 балл, если нет – 0. После чего остается получить сумму баллов.

Я обратился к функции SUMPRODUCT (СУММПРОИЗВ), которая вычисляет сумму произведений соответствующих друг другу диапазонов в массивах.

Давайте разберем формулу

=SUMPRODUCT(--($B3:$B17=D3:D17))

• $B3:$B17 – это диапазон ячеек с ключом, т.е. с правильными ответами. Символы $ нужны для того, чтобы зафиксировать диапазон в формуле, иначе Excel  автоматически изменит его при копировании формулы в другие ячейки.
• = — это оператор сравнения ключа с ответами участника.
• D3:D17 – это ответы участника. В данном случае диапазон не фиксируется, чтобы при копировании формулы в соседние столбцы Excel автоматически изменил D на E, F, G и т.д.
• Наконец, два унарных минуса перед скобками преобразуют логические значения TRUE/FALSE, возвращаемые функцией, в числовые — сначала  в -1/0, затем в +1/0. В итоге получается положительная сумма баллов.

На примере первых трех ответов формула сработает так:

1. Сверит ячейки попарно и получит {FALSE, TRUE, TRUE}
2. Преобразует логические значения в числовые {0, -1, -1}, это первый унарный минус в действии
3. Изменит знак числовых значений — {0, 1, 1}, это второй унарный минус в действии
4. Посчитает правильные ответы — 0+1+1=2

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

Подробнее о функции SUMPRODUCT вы можете прочесть в статье Николая Павлова (MVP Excel) Функция СУММПРОИЗВ – секретное оружие Excel, а также на английском языке здесь и здесь.

Как определить сложность вопросов

Викторина состояла из 15 вопросов, но все они представляли разную сложность для участников. С помощью функций COUNT (СЧЁТ) и COUNTIF (СЧЁТЕСЛИ) можно очень быстро определить самые легкие и сложные вопросы.

Задача сводится к тому, чтобы подсчитать количество правильных ответов на каждый вопрос и поделить это число на количество присланных ответов.

Для этого я использовал формулу

=(COUNTIF(D3:FY3,B3))/(COUNT(D3:FY3))

В этой формуле:

• В числителе подсчитывается, сколько ячеек в диапазоне между D3 и FY3 (взято с запасом) содержат такое же значение, как и ячейка B3, т.е. правильный ответ.
• В знаменателе подсчитывается, сколько ячеек в диапазоне между D3 и FY3 содержат значения. Если ячейка пуста, она не учитывается.
• Значение дроби – это доля правильных ответов на вопрос (например, 0.20 означает 20%).

Я скопировал формулу в остальные строки и сразу увидел, что проще всего был вопрос о том, с какой ИТ-компанией сотрудничает OSZone (93% правильных ответов). Самым сложным оказался вопрос о поисковом операторе (лишь 18% участников ответили верно). Это промежуточные данные, но в итоге мало что изменилось.

Как посмотреть, сколько правильных ответов нужно для получения приза

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

Я использовал формулу

=COUNTIF(D1:FY1,">7")

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

К середине викторины 8 правильных ответов было бы достаточно для получения приза, но в конце этот показатель поднялся до 9.

Как быстро увидеть, какие вопросы вызвали затруднения у конкретного участника

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

В Excel великое множество условий для форматирования, которые можно задать на вкладке Главная в группе Стили.

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

Обратите внимание, что ответы участников сравниваются не просто с цифрой, а с ячейкой ключа, содержащей правильный ответ. При этом столбец ключа зафиксирован символом $, а номер строки – нет. Это позволяет скопировать ячейку и сделать специальную вставку только форматирования в остальные строки и столбцы.

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

Как визуально сравить данные в большом диапазоне столбцов или строк

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

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

Я выделил на рисунке два момента:

• столбцы B и Z соседствуют друг с другом
• вертикальная линия обозначает границу закрепленной области

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

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

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

Я не слишком хорошо знаю Excel, но всегда прибегаю к нему, когда возникает подходящий случай для анализа данных. А вы используете Excel? Расскажите в комментариях, как вы его применяете!