Excel COUNTIF с переменными критериями, а затем добавление всех результатов в ячейки над ними

Для создания нескольких столбцов чисел использовались различные формулы.

Суммы значений столбца B и столбца C создали значения в столбце D. Номера столбца B расположены в порядке убывания от B2 до B58. Поскольку те, что указаны в C, не являются нисходящими, некоторые значения D не являются такими же.

Образец:
Б В Г
79,3 2,5 81,8
76,7 1,5 78,2
75,3 2,1 77,4
73,2 6,3 79,5*
66,9 1,6 68,5
65,2 0,6 65,8
64,6 3*7 66,
62,9 2,2 65,1

В этом примере два значения в D выходят больше, чем ячейки над ними.

В столбце E я хотел бы найти все подобные ячейки в столбце D, где D (n + 1) > D (n). Где бы это ни происходило, я хотел бы добавить соседние C(n+1) ко всему диапазону ячеек D$2:D(n+1).

Проще говоря, если D34 > D33, то я хотел бы добавить C34 ко всем ячейкам выше этого, в D$2:D33. Идеи?


arrays excel range countif sum
person Dave M    schedule 05.06.2015    source источник
comment
С точки зрения функции/формулы рабочего листа это явно похоже на циклическую ссылку, где изменение результата предыдущих ячеек приведет к пересчету исходной формулы. Изоляция значений ячеек с помощью макроса VBA может быть лучшим путем.   -  person    schedule 06.06.2015

Ответы (1)


arrow_upward
1
arrow_downward

Я думаю, что это сделает это: -

=D2+SUM((D3:D$9>D2:D$8)*C3:C$9)

для ввода в E2 с помощью Ctrl Shift Enter и вытягивания вниз, предполагая, что исходные данные начинаются со строки 2.

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

введите здесь описание изображения

ИЗМЕНИТЬ

Формула выдаст неправильные результаты в последней строке столбца E, если последняя пара чисел в столбце D не по порядку (поскольку в примере она пытается сравнить D$9:D10 с D8:D$9). Если мы можем предположить, что за данными следует пустая строка, это можно легко исправить.

=D2+IF(D3<>"",SUM((D3:D$9>D2:D$8)*C3:C$9))

С другой стороны, если мы можем только предположить, что D$2:D$9 является фиксированным диапазоном, формула должна быть

=D2+IF(ROWS(D$2:D2)<ROWS(D$2:D$9),SUM((D3:D$9>D2:D$8)*C3:C$9))

Спасибо @Dave M за указание на это.

person Tom Sharpe    schedule 06.06.2015
comment
Идеальный!! Большое спасибо, Том! - person Dave M; 09.06.2015
comment
Привет @TomSharpe ... пересматриваю эту формулу. Реализован недостаток, когда я добираюсь до последнего значения. Он хочет суммировать с пустой ячейкой, которая следует за ним, делая окончательное значение неверным (стоит больше, чем должно быть). Я транспонировал свои данные по горизонтали и добавил шаг PRODUCT разрешения конфликтов, включающий COLUMN()... Текущая формула в B8: ={SUM(GGP8+SUM((GGQ8:$GKK8›GGP8:$GKJ8)*ELG8:$EPA8 ),PRODUCT(COLUMN(A1),0.0000001))} ... и как только я доберусь до CW8, вы увидите ={SUM(GKK8+SUM(($GKK8:GKL8›$GKJ8:GKK8)*$EPA8: EPB8),PRODUCT(COLUMN(AYF1),0.0000001))} .. (проходит от GKK8 до GKL8 и от EPA8 до EPB8) - person Dave M; 14.06.2017
comment
... т.е. я полагаю, что для этого требуется какой-то шаг ЕСЛИ, чтобы предотвратить суммирование за пределами последней ячейки. Пример из моего Исходного поста выше показывает столбец B в определенной последовательности (B2 всегда будет больше, чем B3 и т. д.), что также произойдет с полученной формулой, которую мы поместили в столбец E. То же самое относится к тому, где мы находимся сегодня. - person Dave M; 14.06.2017
comment
ок, попробую на днях посмотреть - person Tom Sharpe; 15.06.2017
comment
Да, ты прав! Точнее, если последние два числа расположены не по порядку, значение последнего столбца C будет неправильно добавлено к значению последнего столбца D, чтобы получить значение последнего столбца E. Таким образом, должен быть способ определить, вычисляется ли формула в последней строке — можете ли вы предположить, что следующая ячейка в столбце D пуста, или вы можете работать с фиксированным диапазоном ($B$2:$D$9 в столбце D). оригинальный пример) ? - person Tom Sharpe; 15.06.2017
comment
спасибо, @TomSharpe. Я транспонировал это по горизонтали, и в настоящее время у меня есть данные в ячейках, следующих за каждым диапазоном. Если есть какой-то способ поговорить напрямую, может быть, так быстрее обсудить/показать, что я сделал. - person Dave M; 15.06.2017
comment
Я бы не хотел раскрывать свой адрес электронной почты, обычным делом было бы продолжить общение в чате (ссылка внизу страницы), но для участия вам понадобится представитель из 20 человек. Если вы можете поделиться своим листом через Google Sheets или Excel Online, я был бы рад взглянуть на него и, возможно, смог бы добавить некоторые предложения. - person Tom Sharpe; 16.06.2017
comment
Я понял. Я не знал о правиле обмена сообщениями, минимум 20 повторений. Есть ли другие варианты, чтобы говорить напрямую? У меня есть большая формула работы, которую я хотел бы обсудить, но это займет больше времени и, возможно, соглашение о неразглашении. @томшарп - person Dave M; 20.06.2017