Верните заполненный диапазон из ячейки в последнюю непустую ячейку под ней

#excel #excel-formula

Вопрос:

У меня есть следующий рабочий лист.

Я ищу формулу E4 для этого

содержит D5 (чтобы указать, с чего начинается возвращенный разлитый диапазон)
возвращает заполненный диапазон , который начинается D5 и заканчивается в последней непустой ячейке ниже D5 , в которой G указан текущий ввод.

Кто-нибудь знает, как этого добиться?

1. Примечание: Вы открыли много вопросов, но на самом деле не закрыли ни один из них. Это простая вежливость-сообщить людям, что сработало для вас, если это помогло, и закрыть тему, приняв ответы.

2. @JvdV Я знаю, я оставлю вопрос открытым на 1-2 дня и посмотрю, что скажут другие. Для такого рода вопросов я хотел бы быть исчерпывающим. Не волнуйтесь, я буду давать отзывы. Спасибо вам за вашу помощь.

Ответ №1:

Для смешанных типов данных попробуйте:

=D5:INDEX(D:D,MAX(MATCH(CHOOSE({1,2},99^99,"ω"),D:D)))amp;""

что будет во много раз быстрее, чем что-то такое, как

D5:INDEX(D:D,AGGREGATE(14,7,ROW(D:D)/(D:Dlt;gt;""),1))amp;""

последнее вынуждено перебирать более 2 миллионов ячеек, даже если, например, есть данные только до строки 100.

Ответ №2:

В E4:

 =D5:INDEX(D:D,MATCH("zzz",D:D))amp;""

С Office 365 и XLOOKUP:

 =D5:XLOOKUP("*",D:D,D:D,"",2,-1)amp;""

Два вышеприведенных примера найдут любую ячейку, которая возвращает пустую строку "" . Так что, если в D14 есть и то, и другое, выше будет ="" включено через D14.

Если это нежелательно, мы можем использовать эту формулу, которая работает для любого Excel с 2010 года:

 =D5:INDEX(D:D,AGGREGATE(14,7,ROW(D:D)/(D:Dlt;gt;""),1))amp;""

Следует отметить, что это будет немного медленнее, чем любой из вышеперечисленных методов.

1. Спасибо, zzz это не невозможно; есть ли у вас лучшая ценность, чем zzz ?

2. продолжайте добавлять больше z , пока не поймете, что у вас больше любого последовательного числа z s.

3. @SoftTimur дополнительные параметры см. в разделе редактирование.

4. Я только что понял, что =D5:INDEX(D:D,MATCH("zzz",D:D))amp;"" и =D5:XLOOKUP("*",D:D,D:D,"",2,-1)amp;"" игнорирую ячейки, содержащие числа.

5. Да, но этого не было в вашем вопросе. Если они смешаны с числом, я бы выбрал Совокупное распределение.