Тепловая карта Excel для онлайн-курса: как увидеть затруднения по заданиям и критериям

После проверки курса куратор видит несколько повторяющихся затруднений, но длинный список строк мешает сравнить задания. Тепловая карта помогает представить одну и ту же величину на пересечении двух признаков. Для нашего примера строки будут заданиями, столбцы — критериями, а цвет покажет долю работ с ошибкой. Это отдельный отчёт в Excel; автоматическая выгрузка такой аналитики из Umhub здесь не предполагается.

Определите единицу наблюдения

Возьмём первые проверенные попытки по двум заданиям курса. Задание А сдали десять участников, задание Б — восемь. Для каждой работы куратор отмечает три независимых признака: пропущено условие, неверен расчёт, отсутствует обоснование. Одна работа может иметь несколько признаков одновременно, поэтому сумма процентов по строке не обязана равняться ста.

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

Посчитайте доли до выбора цвета

В условных данных по заданию А отмечены две работы с пропуском условия, шесть с неверным расчётом и одна без обоснования. Делим на десять: получаем 20%, 60% и 10%. По заданию Б соответствующие количества равны четырём, двум и нулю; при восьми проверенных работах это 50%, 25% и 0%. Сохраните рядом с каждой строкой число проверенных попыток.

Для проверки механики введите в ячейку формулу =2/10 и задайте процентный формат: на экране должно появиться 20%, а числовое значение останется 0,2. Не вводите 20 с последующим процентным форматированием: получится другая величина. Разместите шесть долей в отдельном диапазоне B2:D3. Заголовки, знаменатели и пояснения не должны попасть в диапазон цветовой шкалы.

Задайте постоянные границы шкалы

Выделите B2:D3 и откройте «Главная → Условное форматирование». Создайте правило форматирования всех ячеек по значениям и выберите двухцветную шкалу. Для минимума установите тип Number и значение 0, для максимума — Number и значение 1. Светлая заливка будет соответствовать отсутствию ошибок, более насыщенная — большей доле. Порядок настройки описан в документации Microsoft.

Не оставляйте границы «наименьшее» и «наибольшее значение», если планируете сравнивать несколько отчётов. Иначе максимальная насыщенность сегодня может означать 60%, а завтра 25%. Фиксированные границы сохраняют значение оттенка. Тип Percent в настройках правила не следует выбирать только потому, что в ячейках показаны проценты: в нашем расчёте используются именно числовые пределы ноль и один.

Отделите ноль от отсутствия данных

Добавим задание В, по которому ещё нет проверенных попыток. Для него нельзя вычислить долю ошибок: знаменатель равен нулю. Не записывайте 0%, чтобы избавиться от ошибки деления. Это создало бы впечатление, что все справились. В данном небольшом отчёте оставьте числовые клетки пустыми, исключите эту строку из цветового диапазона и подпишите «Нет проверенных работ».

Числа внутри окрашенных клеток сохраняйте видимыми. Добавьте легенду «Доля первых проверенных попыток с данным затруднением». Если отчёт приходится изучать без цвета, он должен оставаться понятным. Для оформления пригодятся принципы контрастности текста: заливка не должна мешать чтению значения. Не используйте только противопоставление красного и зелёного как единственный носитель смысла.

Проверьте карту контрольными значениями

В тестовой копии временно замените одну долю на 0, другую на 1, третью на 0,5. Убедитесь, что края и середина шкалы соответствуют легенде. Затем верните исходные данные. В диспетчере правил посмотрите поле диапазона применения: оно должно охватывать всю числовую матрицу, но не столбец количества работ и не итоговые подписи.

Сравните вручную две клетки: 60% должны выглядеть насыщеннее, чем 25%, а одинаковые значения в разных строках — одинаково. Если это не так, проверьте пересекающиеся правила и типы границ. После добавления нового задания повторите проверку диапазона. Цветовая карта легко выглядит правдоподобной даже тогда, когда часть новых строк вообще не попала под правило.

Используйте карту для выбора следующего разбора

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

Запишите действие: например, уточнить условие и добавить один пример выбора формулы. Сохраните дату среза и старую версию задания. При следующем отчёте используйте прежние критерии, знаменатели и шкалу; изменения состава группы тоже учитывайте. Результатом работы должна стать проверяемая гипотеза об улучшении курса, а не публичный рейтинг учеников по цвету клеток.