Формула Excel для подсчета при изменении значения?

Question

У меня есть загадка Excel, которую я не могу взломать.

В столбце AI есть длинный список округов, и каждый из этих округов является членом большой группы округов, которые относятся к конкретному запросу на финансирование нашей организации. В столбце B указана общая сумма финансирования по этому запросу - например, если организация получила 3000 долларов США и заявляет, что обслуживает округа Смит, Робсон, Макон и Дуплин, электронная таблица выглядит следующим образом:

Col A    Col B   (data starts in row 1) 
Smith    3000
Robeson  3000
Macon    3000
Duplin   3000

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

Я пытаюсь настроить функцию, которая позволяет делить общую сумму (3000) на количество ячеек до тех пор, пока значение B не изменится. Так, например, если следующее значение B равно 6500, формула будет считать только А1-А4 в знаменателе. Это теоретически позволило бы мне перетаскивать формулу по всему столбцу, в отличие от ручного изменения диапазона каждый раз, когда появляется новая группа округов. (Записи уже упорядочены таким образом, что две группы округов из запроса с одинаковым значением (например, два запроса по 10 000 долл. США) не являются смежными друг с другом).

Я не смог построить формулу COUNT, которая может учитывать неизвестный диапазон на основе изменения значения, а не вхождения. Буду признателен за любую помощь! Ох, и счастливого Хэллоуина.

Answer 1

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

1. Добавьте строку заголовка. Вам не нужно , но это значительно упрощает формулу, потому что вам не нужно проверять ошибки, когда вы находитесь в первом ряду.
2. Используйте эту формулу в ячейке C2 и скопируйте / вставьте по мере необходимости:
   =IF(B2=B1,C1,B2/(MATCH(TRUE,INDEX(B2:B1001<>B2,),0)-1))
   Если значение совпадает с предыдущим, просто скопируйте его вычисления. В противном случае подсчитайте, сколько одинаковых значений находится в строке, а затем разделите значение на это количество.
   Обратите внимание, что я предположил, что будет меньше 1000 подряд, потому что это должно покрыть это.

Answer 2

Это было бы намного проще, если бы вы добавили столбец для идентификации запросов на финансирование.  Это может быть вне поля зрения; например, столбец Z  Установите Z1 в 1 и Z2 в

=IF(B1=B2, Z1, Z1+1)

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

COUNTIF(Z$1:Z$9999, Z1)

(настройка 9999 чтобы охватить все ваши данные), и поэтому C1 может быть

=B1 / COUNTIF(Z$1:Z$9999, Z1)

Answer 3

Установите C2 в 1 и запишите эту формулу в C2: =IF(B3=B2,(C2+1),1)