Какую формулу Excel можно использовать для выбора последнего не являющегося ошибкой значения в диапазоне (вертикальном)?

Question

Хорошо, например, если мой диапазон B1:B5000. Это временной ряд. В определенный момент времени все значения становятся ошибками, которые являются #NUM! Так есть ли какая-либо формула, которую я могу использовать, чтобы вернуть последнее значение, которое не является ошибкой, спускаясь вниз по диапазону? Или было бы проще, если бы я избавился от всех ошибок, используя :=IFERROR(B1,NA()) и по сути создал еще один диапазон без ошибок? В этом случае, какую формулу Excel я бы использовал?

Answer 1

Независимо от того, устраните ли вы ошибки или нет, вы можете найти последнее число в B1:B5000 (время — это число в Excel) с помощью этой формулы

=LOOKUP(9.9E+307,B1:B5000)

Answer 2

Короткий ответ: для поиска последнего не содержащего ошибку значения в диапазоне можно использовать следующую формулу:

=MATCH(ERROR.TYPE(#NUM!),B1:B5000,-1)

Это найдет первую #NUM!ошибку и даст вам последнюю строку, основанную на диапазоне B1:B5000. Предполагая, что ваше последнее хорошее значение было в Row10, он вернет значение 10.

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