Пример:
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
Я хотел бы, начиная с семнадцатого столбца, просуммировать числа до последней 1 (16 столбцов). Однако для второй 1 и всего, что после, я не хочу, чтобы было какое-либо суммирование, так как оно не соответствует условию наличия нуля перед ним. Я использовал SUMIF
, но когда я пытаюсь расширить его на весь лист, он продолжает суммировать, если, например, - SUMIF(A1:A16, "=0", A17:A33)
, я все равно получу ненулевое значение для своей второй 1.
решение1
решение2
Это позволит найти первый неверный столбец в диапазоне 0
, а затем просуммировать следующие 16 столбцов.
=SUM(INDEX(A1:AH1,MATCH(0,A1:AH1)+1):INDEX(A1:AH1,MIN(MATCH(0,A1:AH1)+16,COLUMNS(A1:AH1))))
MATCH(0,A1:AH1)
возвращает относительный номер столбца, в котором находится текущая ячейка 0
, а следующая больше 0
. Мы добавляем единицу к началу и 17 к концу диапазона.
решение3
Я хотел бы предложить несколько хитрых методов с использованием функций SUMIF
& SUMPRODUCT
:
Как это работает:
- Оба метода требуют вспомогательных значений
1
в34
строке1
(см. снимок экрана).
Формула в ячейке
AV3:
=SUMIFS(N2:AU2,N1:AU1,">=17",N1:AU1,"<=32")
Введите эту формулу в ячейку
AV4:
=SUMPRODUCT(SMALL(N2:AU2,{17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32}))
Если вы имеете дело с огромным набором данных, то вы можете использовать эту формулу.
=SUM(INDEX(N2:AU2,0,COLUMNS(N2:AU2)-(L$6-3)):INDEX(N2:AU2,0,COLUMNS(N2:AU2)))
Ячейка
L6
содержит количество1s
, лучше использовать эту формулу в ячейкеL6
.=COUNTIF(N2:AU2,1)
При необходимости вы можете скорректировать ссылки на ячейки в формуле.