Формула для расчета последовательных ячеек с одинаковыми значениями с переменным диапазоном

Question

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

Я перечисляю зарплату в журнал чековой книжки и делю деньги на разные категории. Я хочу, чтобы вычисление в F5 суммировало только сумму ячеек в столбце E, которые непрерывно запускают описание "Проверить депозит" в столбце C. Как только ячейка в столбце C изменится на что-то другое, независимо от того, это 5 строк или 15 строк вниз, я хочу, чтобы вычисления на этом остановились.

Таким образом, в этом примере F5 будет =F4-SUMIF(C4:C7,C4,E4:E7) . Но мне нужно, чтобы C4:C7 и E4:E7 менялись автоматически, если в следующий раз, когда я его использую, описание "Check Deposit" в столбце C станет еще ниже.

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

Answer 1

Делая некоторые предположения о том, что вы пытаетесь сделать, следующая формула в F5 может решить вашу проблему.

=F$4-(E$4+SUM((C$4:C$20=C$4)*(C$4:C$20=C$3:C$19)*(E$4:E$20)))

Это формула массива, поэтому ее нужно вводить с помощью CTRL-Shift Enter, а не просто Enter. Если введено правильно, Excel окружит формулу фигурными скобками {} в строке формул.

Это работает путем создания массива чисел, содержащего суммы в столбце E, которые удовлетворяют условиям, которые вы ищете.

Первая часть (C$4:C$20=C$4) имеет значение ИСТИНА, где диапазон C4:C20 равен "Чековый депозит". Вторая часть, (C$4:C$20=C$3:C$19) , имеет значение ИСТИНА, где ячейка в C4:C20 равна ячейке над ней.

Умножение этих двух массивов логических значений является эквивалентом операции AND() и также преобразует значения TRUE/FALSE в 1 или 0. Таким образом, результатом этого умножения является массив {0;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0} . Теперь умножая на (E$4:E$20) массив значений в столбце E, получаем {0;50;50;50;0;0;0;0;0;0;0;0;0;0;0;0;0}

Далее мы суммируем эти значения и добавляем обратно E $ 4. (Он отсутствовал в массиве результатов, потому что C4 не равен ячейке над ним). Наконец, вычитание всего этого из суммы в F4 дает окончательный результат.

Заметки:

1. Вы можете расширить или сократить диапазоны в соответствии с вашими потребностями. Эта формула проверяет до строки 20.
2. Вы не дали понять, могут ли быть более непрерывные значения "Check Deposit" после первого прерывания. Если в приведенной выше таблице указано "Чековый депозит" в C10, будет подсчитано значение в E10.

Надеюсь, это поможет.

Answer 2

Твоя формула отлично сработала, Блэквуд.

= F $ 4-(E $ 4+SUM((C $ 4:C $ 20 = C $ 4)(C $ 4:C $ 20 = C $ 3:C $ 19)(E $ 4:E $ 20)))

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

Еще раз спасибо!