Найти номер строки результата суммы в диапазоне
Я ломаю голову над следующим:
У меня есть 15 в качестве значения поиска в B1
В A1:A4 для каждой ячейки значение равно 5. (Я действительно это все разные значения).
Я хочу, чтобы excel суммировал значения A1:A4 одно за другим, и если значение поиска достигается этой суммой, верните номер строки. Итак, для этого примера мне нужен результат 3 (= 5 + 5 + 5)
Если точного совпадения нет, верните ближайшее совпадение, меньшее, чем искомое значение.
Я пробовал что-то вроде =MATCH(1,B1=SUM(A1:A4),1)
что явно не работает, но я не знаю, возможно ли это вообще без VBA.
Кто-нибудь знает способ (без вспомогательного столбца).
Ответы 2
Вы можете попробовать:
=MATCH(TRUE,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4)))>=B1,0)
Я использую Excel O365, но для любой другой версии может потребоваться CSE-ввод вышеуказанного (я не уверен). Обратите внимание, OFFSET() изменчив!
Приведенное выше вернет строку, в которой накопленная сумма находится на первом месте >=B1. Если ваша цель в соответствии с вашим вопросом состояла в том, чтобы получить ближайшее совпадение, но меньшее, чем B1, в случае отсутствия точного совпадения:
=MATCH(B1,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4))))
Стоит отметить, что в Office 365 это можно сделать с помощью SCAN:
=XMATCH(B1,SCAN(0,A1:A4,LAMBDA(a,b,a+b)),1)
Именно то, что я искал. Спасибо!