СЧЁТЕСЛИМН Excel: как посчитать вопросы учеников по куратору и статусу

Определите единицу подсчёта

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

Создайте в A1:C1 заголовки «Код», «Куратор» и «Статус». Строки со второй по седьмую заполните так:

• Q01 — Анна — Открыт.

• Q02 — Борис — Открыт.

• Q03 — Анна — Закрыт.

• Q04 — Анна — Открыт.

• Q05 — Борис — Закрыт.

• Q06 — Анна — Ожидание.

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

Запишите два условия одновременно

Для русской локализации в отдельной ячейке введите =СЧЁТЕСЛИМН(B2:B7;"Анна";C2:C7;"Открыт"). Английское имя функции — COUNTIFS; в окружении с разделителем-запятой формула выглядит так: =COUNTIFS(B2:B7,"Анна",C2:C7,"Открыт"). Используйте имя и разделитель, которые принимает ваша установка Excel.

Microsoft описывает COUNTIFS как подсчёт строк, для которых выполняются все заданные критерии в соответствующих диапазонах. Диапазоны должны иметь одинаковые размеры. В нашем случае первая пара проверяет куратора, вторая — статус той же записи.

Ожидаемый ответ равен 2: подходят Q01 и Q04. Q02 имеет нужный статус, но другого куратора. Q03 принадлежит Анне, но уже закрыт. Q06 тоже принадлежит Анне, однако находится в ожидании. Такой разбор полезнее одной проверки цифры: он показывает, почему каждая неподходящая строка исключена.

Сделайте условия видимыми для коллеги

В E1 напишите «Куратор», в F1 — «Статус», а в E2 и F2 внесите «Анна» и «Открыт». Формулу запишите через ссылки: =СЧЁТЕСЛИМН(B2:B7;E2;C2:C7;F2). Теперь человек меняет условия в подписанных ячейках и сразу видит, какой вопрос задаёт отчёту.

Проверьте ещё два сочетания. Для Бориса и открытого статуса результат равен 1. Для Анны и закрытого — тоже 1. Затем верните исходные условия и убедитесь, что снова получено 2. Если ответ расходится, сначала вручную отметьте подходящие коды и только потом меняйте формулу.

Не допускайте нескольких написаний одного статуса: «Открыт», «Новый» и «Открыт » могут скрывать разные правила или случайные пробелы. Для общего словаря значений полезна проверка данных Excel. Она помогает организовать ввод, а смысл статусов и порядок их смены всё равно должна определить команда.

Разведите условия И и ИЛИ

Две пары критериев в одной СЧЁТЕСЛИМН означают совместное выполнение условий. Если потребовать от одной ячейки статуса одновременно «Открыт» и «Ожидание», в нашем журнале не найдётся подходящих записей. Это не ошибка Excel, а противоречивый вопрос к данным.

Чтобы узнать, сколько вопросов Анны открыты или ожидают ответа, можно сложить два отдельных подсчёта: =СЧЁТЕСЛИМН(B2:B7;"Анна";C2:C7;"Открыт")+СЧЁТЕСЛИМН(B2:B7;"Анна";C2:C7;"Ожидание"). В примере получим 2 + 1 = 3.

Сложение здесь корректно потому, что одна запись имеет ровно один из этих статусов. Для пересекающихся групп тот же приём может дать двойной счёт. Например, количество вопросов Анны плюс количество всех открытых вопросов посчитает Q01 и Q04 дважды. Перед объединением условий назовите коды, которые входят в обе группы.

Проверьте границы диапазона и ручной итог

В копию журнала добавьте Q07: Анна, Открыт. При диапазонах B2:B7 и C2:C7 новая восьмая строка останется вне расчёта. Расширьте оба диапазона до восьмой строки и проверьте результат 3. Расширять только один диапазон нельзя: их размеры должны совпадать.

Затем удалите испытательную запись и верните исходный контрольный набор. В нём всего три открытых вопроса, два закрытых и один в ожидании. Сумма статусов равна шести записям. У Анны четыре вопроса, у Бориса два. Эти независимые сверки помогают обнаружить пропуск строки или опечатку в условии.

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

Используйте число для конкретного действия

Результат 2 означает два открытых обращения Анны в выбранных строках. Он не показывает сложность вопросов, число учеников или скорость ответа. Для последней задачи нужен отдельный расчёт, например медиана времени ответа куратора. Не делайте вывод о качестве сопровождения по одному количеству.

Обсудите с командой действие после подсчёта: просмотреть незакрытые обращения, проверить назначенного ответственного или уточнить причину ожидания. В нашем примере Q06 требует другого следующего шага, чем Q01, поэтому объединённое число 3 не заменяет просмотра записей.

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