Использование COUNTIF в Excel для подсчета количества значений "Больше чем" в массиве значений

Question

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

            % of            67%
            Students Met

**Student Name    Week 1 Goal:         Week 1 Actual:        Goal Met?:**
  Example A       23                    32                    Y
  Example B       45                    44                    N
  Example C       53                    55                    Y

Я использую формулу для «Цель встретилась?msgstr "столбец для обозначения Y или N в зависимости от того, соответствует ли Фактический столбец цели или превышает его. Затем я использую другую формулу, чтобы определить процент встреченных студентов, выполнив COUNTIF со значением "Y" в столбце "Цель", разделив его на COUNTA этого столбца и умножив на 100.

Что мне интересно, так это ... это очень громоздко для учителей, и чем больше столбцов и формул я добавлю, тем больше риск, что учителя наберут в неправильном месте и испортят формулы ... есть ли способ Я могу просто сделать COUNTIF массива столбцов Еженедельная цель и Еженедельная фактическая и подсчитать только те строки, где фактическая>> цель? Например, есть способ сделать COUNTIF(E5:F17, F> = E) и поместить некоторый вид символа в качестве переменной с помощью E и F в критерии, чтобы он проходил строка за строкой и сравнивал значения, а затем подсчитывал те, где значение F больше, чем значение E?

Любые предложения были бы хорошими, потому что тогда я могу исключить столбец "Цель достигнут" (который они неизменно вводят, и я должен возвращаться и исправлять каждую неделю). Раздражает необходимость защищать каждую третью колонну на массивном трекере!

Большое спасибо за любой совет!

Brendan

Answer 1

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

Чтобы ответить на ваш вопрос, вам нужно будет ввести формулу массива. Введите следующую команду, предполагая, что имена столбцов начинаются с A3, а затем нажмите Ctrl+Shift+Enter.

=SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)

Если вы все сделали правильно, фигурные скобки появятся вокруг всей формулы.

={SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)}

Answer 2

Другой подход "формулы массива" выглядит следующим образом:

=AVERAGE(IF(E5:E17<>"",IF(F5:F17>=E5:E17,1,0)))

подтверждено с помощью CTRL+SHIFT+ENTER

Это дает вам фактический% как 0,5 - если вы хотите 50 умножить на 100

Answer 3

Мое решение

Следующая формула массива с правильно настроенными ссылками на ячейки должна сделать то, что вы ищете:

=SUM(1*((OFFSET(C2,0,0,COUNT(C:C),1)-OFFSET(B2,0,0,COUNT(C:C),1))>=0))/COUNT(C:C)

Пример:

Установите ссылку C2 в первом OFFSET для верхней ячейки данных «Фактические» (F5 в вашем примере), ссылку B2 во втором OFFSET для верхней ячейки данных «Goal» (E5 в вашем) и установите все ссылки C:C на столбец, содержащий те или иные данные «фактических» или «целей» (E:E или F:F в вашем). Убедитесь, что единственными ячейками с числовыми данными в столбце, выбранном вами для замены ссылок C:C, являются те ячейки со значениями «фактические» или «цели», в противном случае формула либо выдаст неточный результат, либо полностью потерпит неудачу. ,

Чтобы ввести формулу в виде формулы массива, введите ее соответствующим образом, а затем нажмите Ctrl-Shift-Enter, а не просто Enter. (Смотрите здесь для получения дополнительной информации о формулах массива или просто ищите в Интернете "формулу массива Excel".) Каждый раз, когда вы вносите изменения в формулу, вам всегда нужно вводить ее с помощью Ctrl-Shift-Enter, чтобы она работала правильно.

Как это устроено

• Каждый COUNT извлекает количество строк данных.
• Два вызова OFFSET извлекают два представляющих интерес массива:«фактические» и «цели». В каждом звонке:
  • Первый аргумент отмечает верхнюю левую ячейку массива;
  • Второй и третий аргументы - это смещения строк и столбцов соответственно (здесь оба равны нулю);
  • Четвертый аргумент указывает количество строк, включаемых в массив; а также
  • Пятый аргумент указывает количество столбцов для включения.
• Поскольку это формула массива, вычитание массивов 'Actual' и 'Goal' возвращает массив того же размера, но содержащий поэлементную разницу между ними.
• Сравнение >=0 преобразует массив различий в массив значений TRUE/FALSE указывающих, была ли достигнута цель.
• 1* здесь для преобразования логических значений TRUE и FALSE в числовые 1 и 0 , которые затем будут подсчитаны функцией SUM .
  • В Excel значения TRUE и FALSE неявно содержат числовые значения, равные единице и нулю.
  • НО, если вы попытаетесь передать диапазон или массив, содержащий значения TRUE/FALSE в функцию, такую как SUM , он будет игнорировать значения TRUE и вернет ноль.
  • Итак, умножение всего массива TRUE/FALSE на 1 - это способ обмануть Excel, чтобы он интерпретировал массив полезным способом.
• SUM деленная на COUNT представляет собой желаемую долю учащихся, отвечающих поставленным целям.

РЕДАКТИРОВАТЬ: Согласно ответу wbeard52, в Excel 2010 или более поздней версии должна быть возможность заменить хак 1*... структурой IF . В старых версиях Excel IF неправильно обрабатывал входные данные массива.

Answer 4

Еще пара параметров формулы массива, поэтому вам нужно нажать Ctrl+Shift+Enter, чтобы зафиксировать их.

Чтобы узнать количество достигнутых целей:

=SUM(--(C4:C6>B4:B6))

Чтобы узнать процент достигнутых целей:

=SUM(--(C4:C6>B4:B6))/count(C4:C6)

Вы можете использовать их самостоятельно. Не забудьте ввести их в виде формул массива, нажав Ctrl+Shift+Enter.