COUNTIFS для нескольких критериев на нескольких листах

Question

Помогите ребята!

Я пытаюсь придумать очень короткую формулу для расчета нескольких данных на нескольких листах.

У меня есть одна рабочая тетрадь, в которой я составляю отчеты об ошибках, полученные от нескольких человек. Отчеты об ошибках, которые они посылают, это те, которые они видят из 22 различных материалов. Я хотел бы сгруппировать отчеты об ошибках по месяцам, начиная с декабря 2014 года по декабрь 2015 года.

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

По сути, я бы хотел, чтобы подсчет ошибок по конкретному материалу с декабря 2014 года по декабрь 2015 года осуществлялся тогда и только тогда, когда ошибки подпадают под определенные "статусы", и ошибки были отправлены в течение определенного периода времени (еженедельные даты, например, С 23 февраля по 1 марта).

Пока что я использую эту очень длинную формулу:

= COUNTIFS("Декабрь 2014"!$ A:$ A, "> =" & $ A15, 'декабрь 2014'!$ A:$ A, "<=" & $ B15, 'декабрь 2014'!$ J:$ J, "Приложено", "Декабрь 2014"!$ G:$ G "=" & C $ 1)+COUNTIFS(январь!$ A:$ A, "> =" & $ A15, январь!$ A:$ A, "<=" & $ B15, январь!$ J:$ J, "Applied", январь!$ G:$ G "=" & C $ 1)+COUNTIFS(февраль!$ A:$ A, "> =" & $ A15, февраль!$ A:$ A, "<=" & $ B15, февраль!$ J:$ J, "Applied", февраль!$ G:$ G "=" & C $ 1)+COUNTIFS(март!$ A:$ A, "> =" & $ A15, март!$ A:$ A, "<=" & $ B15, март!$ J:$ J, "Applied", март!$ G:$ G, "=" & C $ 1)+COUNTIFS("Декабрь 2014"!$ A:$ A, "> =" & $ A15, 'декабрь 2014'!$ A:$ A, "<=" & $ B15, 'декабрь 2014'!$ J:$ J, "Отправлено переводчику", "Декабрь 2014"!$ G:$ G "=" & C $ 1)+COUNTIFS(январь!$ A:$ A, "> =" & $ A15, январь!$ A:$ A, "<=" & $ B15, январь!$ J:$ J, "Отправлено переводчику", январь!$ G:$ G "=" & C $ 1)+COUNTIFS(февраль!$ A:$ A, "> =" & $ A15, февраль!$ A:$ A, "<=" & $ B15, февраль!$ J:$ J, "Отправлено переводчику", февраль!$ G:$ G "=" & C $ 1)+COUNTIFS(март!$ A:$ A, "> =" & $ A15, март!$ A:$ A, "<=" & $ B15, март!$ J:$ J, "Отправлено переводчику", март!$ G:$ G, "=" & C $ 1)+COUNTIFS("Декабрь 2014"!$ A:$ A, "> =" & $ A15, 'декабрь 2014'!$ A:$ A, "<=" & $ B15, 'декабрь 2014'!$ J:$ J, "Ожидание загрузки", "Декабрь 2014"!$ G:$ G "=" & C $ 1)+COUNTIFS(январь!$ A:$ A, "> =" & $ A15, январь!$ A:$ A, "<=" & $ B15, январь!$ J:$ J, "Ожидание загрузки", январь!$ G:$ G "=" & C $ 1)+COUNTIFS(февраль!$ A:$ A, "> =" & $ A15, февраль!$ A:$ A, "<=" & $ B15, февраль!$ J:$ J, "Ожидание загрузки", февраль!$ G:$ G "=" & C $ 1)+COUNTIFS(март!$ A:$ A, "> =" & $ A15, март!$ A:$ A, "<=" & $ B15, март!$ J:$ J, "Ожидание загрузки", март!$ G:$ G, "=" & C $ 1)+COUNTIFS("Декабрь 2014"!$ A:$ A, "> =" & $ A15, 'декабрь 2014'!$ A:$ A, "<=" & $ B15, 'декабрь 2014'!$ J:$ J, "Загружено", "Декабрь 2014"!$ G:$ G "=" & C $ 1)+COUNTIFS(январь!$ A:$ A, "> =" & $ A15, январь!$ A:$ A, "<=" & $ B15, январь!$ J:$ J, "загруженный", январь!$ G:$ G "=" & C $ 1)+COUNTIFS(февраль!$ A:$ A, "> =" & $ A15, февраль!$ A:$ A, "<=" & $ B15, февраль!$ J:$ J, "загруженный", февраль!$ G:$ G "=" & C $ 1)+COUNTIFS(март!$ A:$ A, "> =" & $ A15, март!$ A:$ A, "<=" & $ B15, март!$ J:$ J, "загруженный", март!$ G:$ G, "=" & C $ 1)+COUNTIFS("Декабрь 2014"!$ A:$ A, "> =" & $ A15, 'декабрь 2014'!$ A:$ A, "<=" & $ B15, 'декабрь 2014'!$ J:$ J, "Аудио", "Декабрь 2014"!$ G:$ G "=" & C $ 1)+COUNTIFS(январь!$ A:$ A, "> =" & $ A15, январь!$ A:$ A, "<=" & $ B15, январь!$ J:$ J, "Audio", январь!$ G:$ G "=" & C $ 1)+COUNTIFS(февраль!$ A:$ A, "> =" & $ A15, февраль!$ A:$ A, "<=" & $ B15, февраль!$ J:$ J, "Audio", февраль!$ G:$ G "=" & C $ 1)+COUNTIFS(март!$ A:$ A, "> =" & $ A15, март!$ A:$ A, "<=" & $ B15, март!$ J:$ J, "Audio", март!$ G:$ G, "=" & C $ 1)+COUNTIFS("Декабрь 2014"!$ A:$ A, "> =" & $ A15, 'декабрь 2014'!$ A:$ A, "<=" & $ B15, 'декабрь 2014'!$ J:$ J, "Ожидание", "Декабрь 2014"!$ G:$ G "=" & C $ 1)+COUNTIFS(январь!$ A:$ A, "> =" & $ A15, январь!$ A:$ A, "<=" & $ B15, январь!$ J:$ J, "Ожидание", январь!$ G:$ G "=" & C $ 1)+COUNTIFS(февраль!$ A:$ A, "> =" & $ A15, февраль!$ A:$ A, "<=" & $ B15, февраль!$ J:$ J, "Ожидание", февраль!$ G:$ G "=" & C $ 1)+COUNTIFS(март!$ A:$ A, "> =" & $ A15, март!$ A:$ A, "<=" & $ B15, март!$ J:$ J, "Ожидание", март!$ G:$ G "=" & C $ 1)

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

Можно ли как-нибудь сократить эту формулу?

Заранее большое спасибо! :)

Answer 1

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

Вариант 1: Power Pivot
Power Pivot от Microsoft позволяет объединять несколько источников схожих данных и создавать из них одну сводную таблицу. Если вы добавляете каждый лист в качестве источника данных и добавляете его к своим данным, вы можете поворачивать и фильтровать в соответствии с вашими критериями.

Вариант 2: использовать опосредованное
Вы можете использовать функцию INDIRECT чтобы применить ваши критерии. Если я вас правильно понимаю, я бы сделал матрицу, содержащую месяцы (имена листов) на вертикальной оси и статус (Приложен, Отправлен, Ожидание загрузки, ...) на горизонтальной оси. Далее, вы можете заполнить матрицу с COUNTIF формулы, с помощью INDIRECT функции ссылаться на различные рабочие листы и применяются для различных состояний. Например, если у вас есть "Декабрь 2014" в A2, INDIRECT("'"&A2&"'!$A:$A") будет означать 'December 2014'!$A:$A Затем вы просто добавляете значения по горизонтали и вертикали для создания промежуточных итогов по состоянию и месяцу. Сумма всей матрицы - ваш желаемый результат.

---

Редактировать:
Вариант 2 объяснил:
В соответствии с запросом OP, здесь приведено более подробное объяснение второго варианта. В качестве иллюстрации я представлю Анну, Бена и Чарльза, которые проходят три курса. Они получают несколько оценок (от 1 до 10) за каждый курс. Каждый класс имеет значение для определенного триместра. Лектор курса хочет сравнить количество достаточных (> 5,5) оценок для каждого студента в каждом курсе для первого триместра. Вот различные курсы (каждый курс находится в отдельных рабочих листах, называемых "Курс1", "Курс2" и "Курс3").

Как и в случае с вашей проблемой, у нас есть несколько критериев (> 5,5, триместр = 1, рассчитывать как на несколько курсов, так и на нескольких студентов). Верхняя ячейка внутри матрицы (Course1 x Anna, на моем листе это B5) содержит следующую формулу:

=COUNTIFS(INDIRECT($A5&"!A:A");B$4;INDIRECT($A5&"!B:B");$C$2;INDIRECT($A5&"!C:C");$C$1)

Ссылки на ячейки:

• A5 указывает на ячейку, содержащую "Course1"
• B4 указывает на ячейку, содержащую "Анна"
• C2 указывает на ячейку, содержащую "1" (для триместра)
• C1 указывает на ячейку, содержащую «> 5,5» (для оценки)

Перевод вашей проблемы заключается в следующем:

• Курсы -> месяцев
• Имена учеников -> Статус
• А остальное тривиально

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

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