Excel: динамический именованный диапазон — последнее нетекстовое значение

У меня есть следующая таблица в моей электронной таблице:

    A              B   C
1   R1             1   2
2   R2             2   3
3   R3             4   5
4   Grand Total    7   10
5   0              0   0
6   0              0   0
7   0              0   0

Я хочу создать 4 динамических именованных диапазона, которые будут выбирать данные в соответствующих столбцах до тех пор, пока не найдут текст «Общая сумма» в столбце A.

До сих пор я использовал

=OFFSET(Analysis!$A$1,0,0,MATCH("*",Analysis!$A:$A,-1),1)

который возвращает A1: A4.

Мне нужно, чтобы он возвращал A1: A3. В этом примере мне также нужны именованные диапазоны, чтобы возвращать B1:B3, C1:C3 и т. д. Они должны быть динамическими, чтобы при расширении списка до предыдущей строки 6 он продолжал искать общий итог.

Заранее благодарю за любую помощь!


excel named-ranges formulas
person Robert Prager    schedule 23.08.2016    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Я бы предложил использовать:

=INDEX(Analysis!$A:$A,1):INDEX(Analysis!$A:$A,Match("Grand Total", Analysis!$A:$A, 0)-1)

Проблема со смещением заключается в волатильности, что означает, что он будет пересчитываться каждый раз при обновлении.

РЕДАКТИРОВАТЬ: добавлен $ для блокировки этих диапазонов по причинам...

EDIT2: второй индекс изменен на соответствие из-за обновления комментариев.

person Kevin    schedule 23.08.2016
comment
Извините - только что уточнил свой вопрос. В строках 5 и далее в этом примере все ячейки содержат 0. Пробовал вашу формулу, но я думаю, что она не работает с нулями. - person Robert Prager; 23.08.2016
comment
Попробуйте заменить COUNTA()-1 на Match(Общий итог,Анализ!$A:$A,0)-1. Динамические диапазоны для B и C также будут использовать Match в столбце A. - person Kevin; 23.08.2016
comment
Вы можете заменить первую функцию INDEX на $A1, так как она всегда возвращает: =$A1:INDEX(Analysis!$A:$A,Match("Grand Total",Analysis!$A:$A,0)-1) - person Darren Bartrup-Cook; 23.08.2016
comment
@DarrenBartrup-Cook Я предпочитаю версию, изначально предоставленную Кевином, хотя бы потому, что в вашей версии удаление строки 1 вызовет ошибки. Звучит как излишество, но опыт научил меня, что защита от нежелательных вставок/удалений строк/столбцов более чем стоит небольшой дополнительной работы с формулой. - person XOR LX; 23.08.2016
comment
@XORLX - Правда, я не считал, что строка удаляется. Я пытаюсь защититься от этого, блокируя электронную таблицу, используя формы, применяя электрошоковую терапию для сотрудников, когда они делают что-то неправильно... все обычные вещи. :) - person Darren Bartrup-Cook; 23.08.2016
comment
@DarrenBartrup-Cook Я знаю - вы просто не можете защититься от всех случайностей! Все, что вы можете сделать, это попробовать! :-) - person XOR LX; 23.08.2016